def generate_project_used_list(result):
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet("project_used")
report_title = str(result['year']) + '年消防在建工程材料表'
report_title_xf = ezxf('font: bold on, height 400; align: wrap on, vert centre, horiz center')
sheet.write_merge(0, 2, 0, 17, report_title, report_title_xf)
write_two_lines(sheet, 3, 17)
write_project_used_list(sheet, result, 4)
rowx = 4 + len(result['lines']) + 1
write_line(sheet, rowx, 17)
report_subtitle = u'合计'
report_subtitle_xf = ezxf('font: height 240; align: wrap on, vert centre, horiz center')
rowx = rowx+1
sheet.write_merge(rowx, rowx+1, 0, 2, report_subtitle, report_subtitle_xf)
data_format = 'font: height 240; align: wrap on, vert centre, horiz right'
report_subtitle_xf = ezxf(data_format, num_format_str='#,##0.00')
#write 合计
col = 3
for data in result['sum_line']:
sheet.write_merge(rowx, rowx+1, col, col, data, report_subtitle_xf)
col += 1
return book
def generate_payment_summary(result, exportDetail):
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet("payment_summary")
report_title = str(result['year']) + '年' + str(result['month']) + '月付款金额汇总表'
report_title_xf = ezxf('font: bold on, height 400; align: wrap on, vert centre, horiz center')
maxCol = 11
if exportDetail:
maxCol = 12
sheet.write_merge(0, 2, 0, maxCol, report_title, report_title_xf)
write_two_lines(sheet, 3, maxCol)
line = write_payment_summary_list(sheet, result, 4, exportDetail)
rowx = 4 + line
write_line(sheet, rowx, maxCol)
report_subtitle = u'合计'
report_subtitle_xf = ezxf('font: height 240; align: wrap on, vert centre, horiz center')
rowx = rowx+1
mergeCol = 2
if exportDetail:
mergeCol = 3
sheet.write_merge(rowx, rowx+1, 0, mergeCol, report_subtitle, report_subtitle_xf)
data_format = 'font: height 240; align: wrap on, vert centre, horiz right'
report_subtitle_xf = ezxf(data_format, num_format_str='#,##0.00')
#write 合计
col = 3
if exportDetail:
col = 4
for data in result['lines_total']:
sheet.write_merge(rowx, rowx+1, col, col, data, report_subtitle_xf)
col += 1
return book
def generate_payment_summary(result, exportDetail):
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet("payment_summary")
report_title = str(result['year']) + '年' + str(
result['month']) + '月付款金额汇总表'
report_title_xf = ezxf(
'font: bold on, height 400; align: wrap on, vert centre, horiz center')
maxCol = 11
if exportDetail:
maxCol = 12
sheet.write_merge(0, 2, 0, maxCol, report_title, report_title_xf)
write_two_lines(sheet, 3, maxCol)
line = write_payment_summary_list(sheet, result, 4, exportDetail)
rowx = 4 + line
write_line(sheet, rowx, maxCol)
report_subtitle = u'合计'
report_subtitle_xf = ezxf(
'font: height 240; align: wrap on, vert centre, horiz center')
rowx = rowx + 1
mergeCol = 2
if exportDetail:
mergeCol = 3
sheet.write_merge(rowx, rowx + 1, 0, mergeCol, report_subtitle,
report_subtitle_xf)
data_format = 'font: height 240; align: wrap on, vert centre, horiz right'
report_subtitle_xf = ezxf(data_format, num_format_str='#,##0.00')
#write 合计
col = 3
if exportDetail:
col = 4
for data in result['lines_total']:
sheet.write_merge(rowx, rowx + 1, col, col, data, report_subtitle_xf)
col += 1
return book
def generate_project_receiving_list(result):
column = 10
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet("receiving_detail")
report_title = u'到货单'
report_title_xf = ezxf(
'font: bold on, height 400; align: wrap on, vert centre, horiz center')
sheet.write_merge(0, 2, 0, column, report_title, report_title_xf)
write_two_lines(sheet, 3, column)
data_format = 'font: height 240; align: wrap on, vert centre, horiz left'
report_subtitle_xf = ezxf(data_format)
report_subtitle = u'项目名称：' + result['project_name']
sheet.write_merge(4, 5, 0, 6, report_subtitle, report_subtitle_xf)
report_subtitle = u'日期：' + result['start_date'] + ' - ' + result[
'end_date']
sheet.write_merge(4, 5, 7, column, report_subtitle, report_subtitle_xf)
write_line(sheet, 6, column)
write_receiving_list(sheet, result, 7)
rowx = 7 + len(result['lines']) + 1
write_line(sheet, rowx, column)
report_subtitle = u'合计'
report_subtitle_xf = ezxf(
'font: height 240; align: wrap on, vert centre, horiz center')
rowx = rowx + 1
sheet.write_merge(rowx, rowx + 1, 0, 6, report_subtitle,
report_subtitle_xf)
data_format = 'font: height 240; align: wrap on, vert centre, horiz right'
report_subtitle_xf = ezxf(data_format, num_format_str='#,##0.00')
sheet.write_merge(rowx, rowx + 1, 7, 7, result['sum'], report_subtitle_xf)
rowx = rowx + 2
write_line(sheet, rowx, column)
rowx = rowx + 1
report_subtitle = u'项目经理确认：'
data_format = 'font: height 240; align: wrap on, vert centre, horiz left'
report_subtitle_xf = ezxf(data_format)
sheet.write_merge(rowx, rowx + 1, 0, column, report_subtitle,
report_subtitle_xf)
return book
def generate_project_receiving_list(result):
column = 10
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet("receiving_detail")
report_title = u'到货单'
report_title_xf = ezxf('font: bold on, height 400; align: wrap on, vert centre, horiz center')
sheet.write_merge(0, 2, 0, column, report_title, report_title_xf)
write_two_lines(sheet, 3, column)
data_format = 'font: height 240; align: wrap on, vert centre, horiz left'
report_subtitle_xf = ezxf(data_format)
report_subtitle = u'项目名称：' + result['project_name']
sheet.write_merge(4, 5, 0, 6, report_subtitle, report_subtitle_xf)
report_subtitle = u'日期：' + result['start_date'] + ' - ' + result['end_date']
sheet.write_merge(4, 5, 7, column, report_subtitle, report_subtitle_xf)
write_line(sheet, 6, column)
write_receiving_list(sheet, result, 7)
rowx = 7 + len(result['lines']) + 1
write_line(sheet, rowx, column)
report_subtitle = u'合计'
report_subtitle_xf = ezxf('font: height 240; align: wrap on, vert centre, horiz center')
rowx = rowx+1
sheet.write_merge(rowx, rowx+1, 0, 6, report_subtitle, report_subtitle_xf)
data_format = 'font: height 240; align: wrap on, vert centre, horiz right'
report_subtitle_xf = ezxf(data_format, num_format_str='#,##0.00')
sheet.write_merge(rowx, rowx+1, 7, 7, result['sum'], report_subtitle_xf)
rowx = rowx+2
write_line(sheet, rowx, column)
rowx = rowx+1
report_subtitle = u'项目经理确认：'
data_format = 'font: height 240; align: wrap on, vert centre, horiz left'
report_subtitle_xf = ezxf(data_format)
sheet.write_merge(rowx, rowx + 1, 0, column, report_subtitle, report_subtitle_xf)
return book
def generate_order(order_id):
order = Order.objects.filter(order_id=order_id)[0]
lines = OrderLine.objects.filter(order__order_id=order_id).order_by("id")
index = 1
data = []
total = 0
has_brand_name = False
has_comments = False
for line in lines:
row = []
row.append(index)
name = line.documentLineItem.getMaterial()
row.append(name)
brand = line.brand.name if line.brand else ""
if len(brand) > 0:
has_brand_name = True
row.append(brand)
name = line.documentLineItem.getSpecification()
row.append(name)
name = line.documentLineItem.getUnit()
row.append(name)
quantity = line.purchase_quantity or 0
price = line.price or 0
row.append(quantity)
row.append(price)
row.append(price * quantity)
total = total + price * quantity
row.append(line.expected_date)
comments = line.documentLineItem.comments or ""
if len(comments) > 0:
has_comments = True
comments += " " + (line.documentLineItem.approval_comments or "")
elif len(line.documentLineItem.approval_comments or "") > 0:
has_comments = True
comments += line.documentLineItem.approval_comments or ""
row.append(comments)
data.append(row)
index = index + 1
book = xlwt.Workbook(encoding="utf-8")
sheet = book.add_sheet("order")
max_column = 8
if has_brand_name and has_comments:
max_column = 9
report_title = order.company.name
report_title_xf = ezxf("font: bold on, height 500; align: wrap on, vert centre, horiz center")
sheet.write_merge(0, 2, 0, max_column, report_title, report_title_xf)
report_subtitle_xf = ezxf("font: height 240; align: wrap on, vert centre, horiz left")
report_subtitle = u"地址：" + order.company.address
sheet.write_merge(3, 4, 0, 5, report_subtitle, report_subtitle_xf)
report_subtitle = u"邮编: " + order.company.zip
sheet.write_merge(3, 4, 6, max_column, report_subtitle, report_subtitle_xf)
report_subtitle = u"电话: " + order.company.phone
sheet.write_merge(5, 6, 0, 5, report_subtitle, report_subtitle_xf)
if bool(order.company.fax):
report_subtitle = u"传真: " + order.company.fax
sheet.write_merge(5, 6, 6, max_column, report_subtitle, report_subtitle_xf)
write_two_lines(sheet, 7, max_column)
report_subtitle = u"采购单"
report_title_xf = ezxf("font: bold on, height 400; align: wrap on, vert centre, horiz center")
sheet.write_merge(8, 9, 0, max_column, report_subtitle, report_title_xf)
report_subtitle = u"采购单号: " + order_id
sheet.write_merge(10, 11, 0, 2, report_subtitle, report_subtitle_xf)
report_subtitle = u"内部编号: " + order.document.document_id
sheet.write_merge(10, 11, 3, 5, report_subtitle, report_subtitle_xf)
report_subtitle = u"订购日期: " + str(order.create_time)
sheet.write_merge(10, 11, 6, max_column, report_subtitle, report_subtitle_xf)
write_line(sheet, 12, max_column)
report_subtitle = u"供应商：" + order.vendor.name if order.vendor is not None else ""
sheet.write_merge(13, 14, 0, max_column, report_subtitle, report_subtitle_xf)
report_subtitle = u"联系人：" + order.vendor.contact if order.vendor is not None else ""
sheet.write_merge(15, 16, 0, 2, report_subtitle, report_subtitle_xf)
report_subtitle = u"电话：" + order.vendor.telephone if order.vendor is not None else ""
sheet.write_merge(15, 16, 3, 5, report_subtitle, report_subtitle_xf)
if bool(order.vendor.fax):
report_subtitle = u"传真: " + order.vendor.fax
sheet.write_merge(15, 16, 6, max_column, report_subtitle, report_subtitle_xf)
write_line(sheet, 17, max_column)
report_subtitle = u"送货地点：" + order.project.name
sheet.write_merge(18, 19, 0, max_column, report_subtitle, report_subtitle_xf)
report_subtitle = u"联系人：" + str(order.project.users.all()[0])
sheet.write_merge(20, 21, 0, 2, report_subtitle, report_subtitle_xf)
report_subtitle = u"电话：" + order.project.users.all()[0].phone
sheet.write_merge(20, 21, 3, max_column, report_subtitle, report_subtitle_xf)
write_line(sheet, 22, max_column)
rowx = 23
write_order_lines(sheet, data, rowx, has_brand_name, has_comments)
rowx = rowx + len(data) + 1
write_line(sheet, rowx, max_column)
report_subtitle = u"合计"
report_subtitle_xf = ezxf("font: height 240; align: wrap on, vert centre, horiz centre")
rowx = rowx + 1
if has_brand_name:
sheet.write_merge(rowx, rowx + 1, 0, 6, report_subtitle, report_subtitle_xf)
else:
sheet.write_merge(rowx, rowx + 1, 0, 5, report_subtitle, report_subtitle_xf)
data_format = "font: height 240; align: wrap on, vert centre, horiz right"
report_subtitle_xf = ezxf(data_format, num_format_str="#,##0.00")
if has_brand_name:
sheet.write_merge(rowx, rowx + 1, 7, 7, total, report_subtitle_xf)
else:
sheet.write_merge(rowx, rowx + 1, 6, 6, total, report_subtitle_xf)
report_subtitle_xf = ezxf("font: height 240; align: wrap on, vert centre, horiz left")
rowx = rowx + 2
report_subtitle = u"合同总价: "
if total > 0:
report_subtitle += to_rmb_upper(total)
else:
report_subtitle += "负" + to_rmb_upper(total * -1)
sheet.write_merge(rowx, rowx + 1, 0, max_column, report_subtitle, report_subtitle_xf)
note = ""
if order.note:
note = order.note.note
# note = u"""注意事项：
# 1. 签单回传： 供方应于二日内就本合同签章确认回传，逾期未签回视同本合同作废。
# 2. 交货事宜： 请供方务必遵守本合同交货日期、数量，如有变动应事先以书面传真
# 调整交货期。供方延迟交货对本公司造成重大损失的，其损失供方应付全责。
# 3. 质量要求： 符合国家标准及我方订货要求，如出现质量与订单不符现象同供方负责
# 退换，另扣除本合同总价的10%作为违约金。
# 4. 请款手续： 凭现场验收合格签收的送货单在每月的25日前至我司进行对账，次月
# 中旬付款。如当有不前来对账的将延至下个月，付款也如此。
# 5. 其它约定： 送货时请附产品检验报告及合格证原件。"""
rowx = rowx + 2
sheet.write_merge(rowx, rowx + 11, 0, max_column, note, getNewBorder(Alignment.HORZ_LEFT))
rowx = rowx + 12
report_subtitle = u"采购"
sheet.write_merge(rowx, rowx + 1, 0, 3, report_subtitle, getNewBorder(Alignment.HORZ_CENTER))
report_subtitle = u"供应商签章"
sheet.write_merge(rowx, rowx + 1, 4, max_column, report_subtitle, getNewBorder(Alignment.HORZ_CENTER))
rowx = rowx + 2
sheet.write_merge(rowx, rowx + 3, 0, 3, "", getNewBorder(Alignment.HORZ_CENTER))
sheet.write_merge(rowx, rowx + 3, 4, max_column, "", getNewBorder(Alignment.HORZ_CENTER))
return book
def generate_payment(self, payment_id):
payment = Payment.objects.get(id = payment_id)
if payment:
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet("payment")
max_column = 3
rowx = 0
report_subtitle = u'付款单'
report_title_xf = ezxf('font: bold on, height 400; align: wrap on, vert centre, horiz center')
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_title_xf)
rowx += 2
write_two_lines(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf('font: height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'公司: ' + payment.company.name
sheet.write_merge(rowx, rowx+1, 0, 1, report_subtitle, report_subtitle_xf)
report_subtitle = u'付款单号: ' + payment.payment_id
sheet.write_merge(rowx, rowx+1, 2, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle = u'供应商: ' + payment.vendor.name
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
applied_amount = payment.applied_amount
report_subtitle = u'本次付款金额: ' + str(applied_amount) + " "
# 加上 大写金额
if applied_amount > 0:
report_subtitle += "( " + to_rmb_upper(applied_amount) + " )"
else:
report_subtitle += "( " + '负' + to_rmb_upper(applied_amount * -1) + " )"
sheet.write_merge(rowx, rowx+1, 0, 1, report_subtitle, report_subtitle_xf)
report_subtitle = u'采购物资内容: ' + payment.content
sheet.write_merge(rowx, rowx+1, 2, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
report_subtitle = u'付款方式: ' + payment.paymentType.name
sheet.write_merge(rowx, rowx+1, 0, 1, report_subtitle, report_subtitle_xf)
report_subtitle = u'付款经办人: ' + str(payment.payment_user)
sheet.write_merge(rowx, rowx+1, 2, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'本产品采取月结采购方式'
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf('font: height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'现欠款额: ' + str(payment.owed_amount)
sheet.write_merge(rowx, rowx+1, 0, 1, report_subtitle, report_subtitle_xf)
report_subtitle = u'本期支付后欠款额: ' + str(payment.owed_amount_after_payment)
sheet.write_merge(rowx, rowx+1, 2, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
report_subtitle = u'款项属性: ' + payment.paymentProperty.name
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'本产品采取临时采购方式'
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf('font: height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'采购金额: ' + (str(payment.purchase_amount) if payment.purchase_amount else '')
sheet.write_merge(rowx, rowx+1, 0,1, report_subtitle, report_subtitle_xf)
report_subtitle = u'采购经手人: ' + (str(payment.purchase_user) if payment.purchase_user else '')
sheet.write_merge(rowx, rowx+1, 2, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle = u'备注: ' + (payment.comments if payment.comments else '')
sheet.write_merge(rowx, rowx+2, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 3
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'审批流程'
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
data = []
histories = TaskHistory.objects.filter(item__document__document_id=payment.payment_id)
for history in histories:
row = []
row.append(history.actor.actor_name if history.actor else '')
row.append(AUDIT_HISTORY_STATUS[history.status][1])
row.append(str(history.user))
row.append(history.create_date.strftime("%Y-%m-%d %H:%M:%S"))
data.append(row)
write_histories(sheet, data, rowx)
return book
def generate_order(order_id):
order = Order.objects.filter(order_id=order_id)[0]
lines = OrderLine.objects.filter(order__order_id=order_id).order_by('id')
index = 1
data = []
total = 0
has_brand_name = False
has_comments = False
for line in lines:
row = []
row.append(index)
name = line.documentLineItem.getMaterial()
row.append(name)
brand = line.brand.name if line.brand else ''
if len(brand) > 0:
has_brand_name = True
row.append(brand)
name = line.documentLineItem.getSpecification()
row.append(name)
name = line.documentLineItem.getUnit()
row.append(name)
quantity = line.purchase_quantity or 0
price = line.price or 0
row.append(quantity)
row.append(price)
row.append(price * quantity)
total = total + price * quantity
row.append(line.expected_date)
comments = line.documentLineItem.comments or ''
if len(comments) > 0:
has_comments = True
comments += ' ' + (line.documentLineItem.approval_comments
or '')
elif len(line.documentLineItem.approval_comments or '') > 0:
has_comments = True
comments += (line.documentLineItem.approval_comments or '')
row.append(comments)
data.append(row)
index = index + 1
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet("order")
max_column = 8
if has_brand_name and has_comments:
max_column = 9
report_title = order.company.name
report_title_xf = ezxf(
'font: bold on, height 500; align: wrap on, vert centre, horiz center')
sheet.write_merge(0, 2, 0, max_column, report_title, report_title_xf)
report_subtitle_xf = ezxf(
'font: height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'地址：' + order.company.address
sheet.write_merge(3, 4, 0, 5, report_subtitle, report_subtitle_xf)
report_subtitle = u'邮编: ' + order.company.zip
sheet.write_merge(3, 4, 6, max_column, report_subtitle, report_subtitle_xf)
report_subtitle = u'电话: ' + order.company.phone
sheet.write_merge(5, 6, 0, 5, report_subtitle, report_subtitle_xf)
if bool(order.company.fax):
report_subtitle = u'传真: ' + order.company.fax
sheet.write_merge(5, 6, 6, max_column, report_subtitle,
report_subtitle_xf)
write_two_lines(sheet, 7, max_column)
report_subtitle = u'采购单'
report_title_xf = ezxf(
'font: bold on, height 400; align: wrap on, vert centre, horiz center')
sheet.write_merge(8, 9, 0, max_column, report_subtitle, report_title_xf)
report_subtitle = u'采购单号: ' + order_id
sheet.write_merge(10, 11, 0, 2, report_subtitle, report_subtitle_xf)
report_subtitle = u'内部编号: ' + order.document.document_id
sheet.write_merge(10, 11, 3, 5, report_subtitle, report_subtitle_xf)
report_subtitle = u'订购日期: ' + str(order.create_time)
sheet.write_merge(10, 11, 6, max_column, report_subtitle,
report_subtitle_xf)
write_line(sheet, 12, max_column)
report_subtitle = u'供应商：' + order.vendor.name if order.vendor is not None else ""
sheet.write_merge(13, 14, 0, max_column, report_subtitle,
report_subtitle_xf)
report_subtitle = u'联系人：' + order.vendor.contact if order.vendor is not None else ""
sheet.write_merge(15, 16, 0, 2, report_subtitle, report_subtitle_xf)
report_subtitle = u'电话：' + order.vendor.telephone if order.vendor is not None else ""
sheet.write_merge(15, 16, 3, 5, report_subtitle, report_subtitle_xf)
if bool(order.vendor.fax):
report_subtitle = u'传真: ' + order.vendor.fax
sheet.write_merge(15, 16, 6, max_column, report_subtitle,
report_subtitle_xf)
write_line(sheet, 17, max_column)
report_subtitle = u'送货地点：' + order.project.name
sheet.write_merge(18, 19, 0, max_column, report_subtitle,
report_subtitle_xf)
report_subtitle = u'联系人：' + str(order.project.users.all()[0])
sheet.write_merge(20, 21, 0, 2, report_subtitle, report_subtitle_xf)
report_subtitle = u'电话：' + order.project.users.all()[0].phone
sheet.write_merge(20, 21, 3, max_column, report_subtitle,
report_subtitle_xf)
write_line(sheet, 22, max_column)
rowx = 23
write_order_lines(sheet, data, rowx, has_brand_name, has_comments)
rowx = rowx + len(data) + 1
write_line(sheet, rowx, max_column)
report_subtitle = u'合计'
report_subtitle_xf = ezxf(
'font: height 240; align: wrap on, vert centre, horiz centre')
rowx = rowx + 1
if has_brand_name:
sheet.write_merge(rowx, rowx + 1, 0, 6, report_subtitle,
report_subtitle_xf)
else:
sheet.write_merge(rowx, rowx + 1, 0, 5, report_subtitle,
report_subtitle_xf)
data_format = 'font: height 240; align: wrap on, vert centre, horiz right'
report_subtitle_xf = ezxf(data_format, num_format_str='#,##0.00')
if has_brand_name:
sheet.write_merge(rowx, rowx + 1, 7, 7, total, report_subtitle_xf)
else:
sheet.write_merge(rowx, rowx + 1, 6, 6, total, report_subtitle_xf)
report_subtitle_xf = ezxf(
'font: height 240; align: wrap on, vert centre, horiz left')
rowx = rowx + 2
report_subtitle = u'合同总价: '
if total > 0:
report_subtitle += to_rmb_upper(total)
else:
report_subtitle += '负' + to_rmb_upper(total * -1)
sheet.write_merge(rowx, rowx + 1, 0, max_column, report_subtitle,
report_subtitle_xf)
note = ''
if order.note:
note = order.note.note
# note = u"""注意事项：
# 1. 签单回传： 供方应于二日内就本合同签章确认回传，逾期未签回视同本合同作废。
# 2. 交货事宜： 请供方务必遵守本合同交货日期、数量，如有变动应事先以书面传真
# 调整交货期。供方延迟交货对本公司造成重大损失的，其损失供方应付全责。
# 3. 质量要求： 符合国家标准及我方订货要求，如出现质量与订单不符现象同供方负责
# 退换，另扣除本合同总价的10%作为违约金。
# 4. 请款手续： 凭现场验收合格签收的送货单在每月的25日前至我司进行对账，次月
# 中旬付款。如当有不前来对账的将延至下个月，付款也如此。
# 5. 其它约定： 送货时请附产品检验报告及合格证原件。"""
rowx = rowx + 2
sheet.write_merge(rowx, rowx + 11, 0, max_column, note,
getNewBorder(Alignment.HORZ_LEFT))
rowx = rowx + 12
report_subtitle = u'采购'
sheet.write_merge(rowx, rowx + 1, 0, 3, report_subtitle,
getNewBorder(Alignment.HORZ_CENTER))
report_subtitle = u'供应商签章'
sheet.write_merge(rowx, rowx + 1, 4, max_column, report_subtitle,
getNewBorder(Alignment.HORZ_CENTER))
rowx = rowx + 2
sheet.write_merge(rowx, rowx + 3, 0, 3, '',
getNewBorder(Alignment.HORZ_CENTER))
sheet.write_merge(rowx, rowx + 3, 4, max_column, '',
getNewBorder(Alignment.HORZ_CENTER))
return book
def generate_payment(self, payment_id):
payment = Payment.objects.get(id=payment_id)
if payment:
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet("payment")
max_column = 3
rowx = 0
report_subtitle = u'付款单'
report_title_xf = ezxf(
'font: bold on, height 400; align: wrap on, vert centre, horiz center'
)
sheet.write_merge(rowx, rowx + 1, 0, max_column, report_subtitle,
report_title_xf)
rowx += 2
write_two_lines(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf(
'font: height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'公司: ' + payment.company.name
sheet.write_merge(rowx, rowx + 1, 0, 1, report_subtitle,
report_subtitle_xf)
report_subtitle = u'付款单号: ' + payment.payment_id
sheet.write_merge(rowx, rowx + 1, 2, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle = u'供应商: ' + payment.vendor.name
sheet.write_merge(rowx, rowx + 1, 0, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
applied_amount = payment.applied_amount
report_subtitle = u'本次付款金额: ' + str(applied_amount) + " "
# 加上 大写金额
if applied_amount > 0:
report_subtitle += "( " + to_rmb_upper(applied_amount) + " )"
else:
report_subtitle += "( " + '负' + to_rmb_upper(
applied_amount * -1) + " )"
sheet.write_merge(rowx, rowx + 1, 0, 1, report_subtitle,
report_subtitle_xf)
report_subtitle = u'采购物资内容: ' + payment.content
sheet.write_merge(rowx, rowx + 1, 2, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
report_subtitle = u'付款方式: ' + payment.paymentType.name
sheet.write_merge(rowx, rowx + 1, 0, 1, report_subtitle,
report_subtitle_xf)
report_subtitle = u'付款经办人: ' + str(payment.payment_user)
sheet.write_merge(rowx, rowx + 1, 2, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf(
'font: bold on, height 240; align: wrap on, vert centre, horiz left'
)
report_subtitle = u'本产品采取月结采购方式'
sheet.write_merge(rowx, rowx + 1, 0, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf(
'font: height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'现欠款额: ' + str(payment.owed_amount)
sheet.write_merge(rowx, rowx + 1, 0, 1, report_subtitle,
report_subtitle_xf)
report_subtitle = u'本期支付后欠款额: ' + str(
payment.owed_amount_after_payment)
sheet.write_merge(rowx, rowx + 1, 2, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
report_subtitle = u'款项属性: ' + payment.paymentProperty.name
sheet.write_merge(rowx, rowx + 1, 0, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf(
'font: bold on, height 240; align: wrap on, vert centre, horiz left'
)
report_subtitle = u'本产品采取临时采购方式'
sheet.write_merge(rowx, rowx + 1, 0, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf(
'font: height 240; align: wrap on, vert centre, horiz left')
report_subtitle = u'采购金额: ' + (str(payment.purchase_amount)
if payment.purchase_amount else '')
sheet.write_merge(rowx, rowx + 1, 0, 1, report_subtitle,
report_subtitle_xf)
report_subtitle = u'采购经手人: ' + (str(payment.purchase_user)
if payment.purchase_user else '')
sheet.write_merge(rowx, rowx + 1, 2, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle = u'备注: ' + (payment.comments
if payment.comments else '')
sheet.write_merge(rowx, rowx + 2, 0, max_column, report_subtitle,
report_subtitle_xf)
rowx += 3
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle_xf = ezxf(
'font: bold on, height 240; align: wrap on, vert centre, horiz left'
)
report_subtitle = u'审批流程'
sheet.write_merge(rowx, rowx + 1, 0, max_column, report_subtitle,
report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
data = []
histories = TaskHistory.objects.filter(
item__document__document_id=payment.payment_id)
for history in histories:
row = []
row.append(history.actor.actor_name if history.actor else '')
row.append(AUDIT_HISTORY_STATUS[history.status][1])
row.append(str(history.user))
row.append(history.create_date.strftime("%Y-%m-%d %H:%M:%S"))
data.append(row)
write_histories(sheet, data, rowx)
return book
def generate_account_details(result):
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet("account_detail")
hasBrandName = has_brand_name(result)
max_column = 9
if hasBrandName:
max_column = 10
company_name = result['company'].name if result['company'] else ''
rowx = 0
report_title = company_name
report_title_xf = ezxf('font: bold on, height 300; align: wrap on, vert centre, horiz left')
sheet.write_merge(rowx, rowx+1, 0, max_column, report_title, report_title_xf)
# rowx += 2
# report_title = nanxiao_company.name
# sheet.write_merge(rowx, rowx+1, 0, max_column, report_title, report_title_xf)
rowx += 2
report_title = u'对帐单'
report_title_xf = ezxf('font: bold on, height 400; align: wrap on, vert centre, horiz center')
sheet.write_merge(rowx, rowx+2, 0, max_column, report_title, report_title_xf)
rowx += 3
write_two_lines(sheet, rowx, max_column)
report_subtitle_style = 'font: height 240; align: wrap on, vert centre, horiz left'
report_subtitle_xf = ezxf(report_subtitle_style)
rowx += 1
report_subtitle = u'供应商名称：'
sheet.write_merge(rowx, rowx+len(result['vendors'])*2 -1, 0, 1, report_subtitle, report_subtitle_xf)
report_subtitle = u'传真：' + result['vendors'][0].fax
sheet.write_merge(rowx, rowx+len(result['vendors'])*2 -1, 7, max_column, report_subtitle, report_subtitle_xf)
for vendor in result['vendors']:
sheet.write_merge(rowx, rowx+1, 2, 6, vendor.name, report_subtitle_xf)
rowx +=2
report_subtitle = u'对帐日期：' + str(result['start_date']) + ' - ' + str(result['end_date'])
sheet.write_merge(rowx, rowx+1, 0, 6, report_subtitle, report_subtitle_xf)
report_subtitle = u'对帐编号：' + result['check_account_id']
sheet.write_merge(rowx, rowx+1, 7, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
write_total_amounts(sheet, result, rowx, hasBrandName)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle = u'送货清单'
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
write_receiving_list(sheet, result, rowx, hasBrandName)
rowx = rowx + len(result['receiving_list']) + 1
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle = u'合计'
data_format = ezxf('font: height 240; align: wrap on, vert centre, horiz centre')
sheet.write_merge(rowx, rowx, 0, max_column-2, report_subtitle, data_format)
col = max_column-1
report_subtitle_style = 'font: height 240; align: wrap on, vert centre, horiz right'
sheet.write_merge(rowx, rowx, col, col, result['receiving_sum'], ezxf(report_subtitle_style , num_format_str='#,##0.00'))
rowx += 1
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle = u'统计分类'
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx +=1
#统计分类
write_totals_by_project(sheet, result, rowx)
rowx += len(result['totals_by_project']['rows']) + 1
write_line(sheet, rowx, max_column)
if len(result['invoices']) > 0:
rowx += 1
report_subtitle = u'发票清单'
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
write_invoices_list(sheet, result, rowx)
rowx = rowx + len(result['invoices']) + 1
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle = u'合计'
sheet.write_merge(rowx, rowx, 0, 7, report_subtitle, data_format)
sheet.write_merge(rowx, rowx, 8, 8, result['invoices_sum'], ezxf(report_subtitle_style , num_format_str='#,##0.00'))
rowx += 1
write_line(sheet, rowx, max_column)
if len(result['payments']) > 0:
rowx += 1
report_subtitle = u'付款明细'
sheet.write_merge(rowx, rowx+1, 0, max_column, report_subtitle, report_subtitle_xf)
rowx += 2
write_line(sheet, rowx, max_column)
rowx += 1
write_payments_list(sheet, result, rowx)
rowx = rowx + len(result['payments']) + 1
write_line(sheet, rowx, max_column)
rowx += 1
report_subtitle = u'合计'
sheet.write_merge(rowx, rowx, 0, 8, report_subtitle, data_format)
sheet.write_merge(rowx, rowx, 9, 9, result['payments_sum'], ezxf(report_subtitle_style , num_format_str='#,##0.00'))
rowx += 1
write_line(sheet, rowx, max_column)
col = 6
if hasBrandName:
col = 7
data_format = ezxf('font: height 240; align: wrap on, vert centre, horiz left')
rowx += 1
report_subtitle = u'供应商签章'
sheet.write_merge(rowx, rowx+1, 0, col, report_subtitle, data_format)
hehe_company = Company.objects.filter(short_name = '合和')[0]
report_subtitle = u'请确认后回传至：' + hehe_company.fax
sheet.write_merge(rowx, rowx+1, col+1, max_column, report_subtitle, data_format)
return book
