def execAddWorksheetInvalidRPT01StoreInv(objSheet, listInvalidRPT01StoreInv, objColumnFormat, objRowCenterFormat, objRowLeftFormat, objRowRightFormat):
objSheet.set_default_row(20)
objSheet.set_row(0, 40)
objSheet.set_column(0, 0, 8)
objSheet.set_column(1, 1, 10)
objSheet.set_column(2, 2, 15)
objSheet.set_column(3, 3, 30)
objSheet.set_column(4, 4, 15)
objSheet.set_column(5, 5, 15)
intRowIndex = 0
objSheet.write(intRowIndex, 0, "系统代号", objColumnFormat)
objSheet.write(intRowIndex, 1, "系统名称", objColumnFormat)
objSheet.write(intRowIndex, 2, "系统门店代号", objColumnFormat)
objSheet.write(intRowIndex, 3, "系统门店名称", objColumnFormat)
objSheet.write(intRowIndex, 4, "最早未匹配数据\n库存日期", objColumnFormat)
objSheet.write(intRowIndex, 5, "最晚未匹配数据\n库存日期", objColumnFormat)
intRowIndex += 1
for dictInvalidRPT01StoreInv in listInvalidRPT01StoreInv:
objSheet.write(intRowIndex, 0, dictInvalidRPT01StoreInv["KA_SYSTEM_CODE"], objRowCenterFormat)
objSheet.write(intRowIndex, 1, dictInvalidRPT01StoreInv["KA_SYSTEM_NM"], objRowCenterFormat)
objSheet.write(intRowIndex, 2, dictInvalidRPT01StoreInv["SYSTEM_STORE_CODE"], objRowLeftFormat)
objSheet.write(intRowIndex, 3, dictInvalidRPT01StoreInv["SYSTEM_STORE_NM"], objRowLeftFormat)
objSheet.write(intRowIndex, 4, Common.formatDateString(dictInvalidRPT01StoreInv["INV_DATE_MIN"]), objRowCenterFormat)
objSheet.write(intRowIndex, 5, Common.formatDateString(dictInvalidRPT01StoreInv["INV_DATE_MAX"]), objRowCenterFormat)
intRowIndex += 1
return True
def execAddWorksheetInvalidRPT01ProdPos(objSheet, listInvalidRPT01ProdPos, objColumnFormat, objRowCenterFormat, objRowLeftFormat, objRowRightFormat):
objSheet.set_default_row(20)
objSheet.set_row(0, 40)
objSheet.set_column(0, 0, 8)
objSheet.set_column(1, 1, 10)
objSheet.set_column(2, 2, 15)
objSheet.set_column(3, 3, 30)
objSheet.set_column(4, 4, 15)
objSheet.set_column(5, 5, 15)
intRowIndex = 0
objSheet.write(intRowIndex, 0, "系统代号", objColumnFormat)
objSheet.write(intRowIndex, 1, "系统名称", objColumnFormat)
objSheet.write(intRowIndex, 2, "系统产品销售编码", objColumnFormat)
objSheet.write(intRowIndex, 3, "系统产品名称", objColumnFormat)
objSheet.write(intRowIndex, 4, "最早未匹配数据\n销售日期", objColumnFormat)
objSheet.write(intRowIndex, 5, "最晚未匹配数据\n销售日期", objColumnFormat)
intRowIndex += 1
for dictInvalidRPT01ProdPos in listInvalidRPT01ProdPos:
objSheet.write(intRowIndex, 0, dictInvalidRPT01ProdPos["KA_SYSTEM_CODE"], objRowCenterFormat)
objSheet.write(intRowIndex, 1, dictInvalidRPT01ProdPos["KA_SYSTEM_NM"], objRowCenterFormat)
objSheet.write(intRowIndex, 2, dictInvalidRPT01ProdPos["SYSTEM_PROD_CODE"], objRowLeftFormat)
objSheet.write(intRowIndex, 3, dictInvalidRPT01ProdPos["SYSTEM_PROD_NM"], objRowLeftFormat)
objSheet.write(intRowIndex, 4, Common.formatDateString(dictInvalidRPT01ProdPos["POS_DATE_MIN"]), objRowCenterFormat)
objSheet.write(intRowIndex, 5, Common.formatDateString(dictInvalidRPT01ProdPos["POS_DATE_MAX"]), objRowCenterFormat)
intRowIndex += 1
return True
def execWriteWorkbook(strJobPathRES, listWeekNum, listPOSRPT01, listPOSRPT01System, listPOSRPT01SystemPCS, listPOSRPT01SystemSKU, listPOSRPT01ProdH1PCS, listPOSRPT01ProdH1SKU):
if len(listWeekNum) == 0 or len(listPOSRPT01) == 0 or len(listPOSRPT01ProdH1PCS) != len(listPOSRPT01ProdH1SKU) or len(listPOSRPT01SystemPCS) != len(listPOSRPT01SystemSKU):
return False
else:
objExcelWorkbook = None
objExcelWorksheet = None
objExcelRowCenterFormat = None
objExcelRowLeftFormat = None
objExcelRowRightFormat = None
strKA_SYSTEM_ACT_NM = None
strKA_SYSTEM_CODE = None
strKA_SYSTEM_NM = None
intPOSRPT01SystemIndex = 0
intPOSRPT01ProdH1Index = 0
intExcelRow = 0
intNowDates = (Common.getDate(listPOSRPT01[0]["POS_DATE"]) - Common.getDate(listWeekNum[0]["WEEK_FIRST_DATE"])).days + 1
while intPOSRPT01ProdH1Index < len(listPOSRPT01ProdH1PCS):
if strKA_SYSTEM_CODE != listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_CODE"]:
strKA_SYSTEM_ACT_NM = listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_ACT_NM"]
strKA_SYSTEM_CODE = listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_CODE"]
strKA_SYSTEM_NM = listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_NM"]
if objExcelWorksheet != None:
objExcelWorksheet.freeze_panes("K8")
objExcelWorksheet.autofilter("A7:CF" + str(intExcelRow))
objExcelWorksheet.set_column('F:G', None, None, {'hidden': True})
if objExcelWorkbook != None:
objExcelWorkbook.close()
objExcelWorkbook = xlsxwriter.Workbook(strJobPathRES.replace(".dat", "-" + strKA_SYSTEM_CODE + ".dat"))
objExcelTitleFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "left", "valign": "vcenter", "font_size": 16, "font_color": "#305496",
"fg_color": "#D6DCE4",
"border": 0})
objExcelSubTitleFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "left", "valign": "vcenter", "font_size": 11, "font_color": "#305496",
"fg_color": "#D6DCE4",
"border": 0})
objExcelColumnFormat = objExcelWorkbook.add_format({
"text_wrap": 1,
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#FFFFFF",
"fg_color": "#305496",
"border": 0})
objExcelWeekFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#FFFFFF",
"fg_color": "#305496",
"border": 1, "top_color": "#305496", "bottom_color": "#FFFFFF", "left_color": "#305496", "right_color": "#305496"})
objExcelRowFilterFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000",
"fg_color": "#BFBEBF",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#BFBEBF", "right_color": "#BFBEBF"})
objExcelRowCenter1Format = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000",
"fg_color": "#FFFFFF",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#305496", "right_color": "#305496"})
objExcelRowCenter2Format = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000",
"fg_color": "#D9E1F2",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#305496", "right_color": "#305496"})
objExcelRowLeft1Format = objExcelWorkbook.add_format({
"bold": 1, "align": "left", "valign": "vcenter", "font_size": 11, "font_color": "#000000",
"fg_color": "#FFFFFF",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#305496", "right_color": "#305496"})
objExcelRowLeft2Format = objExcelWorkbook.add_format({
"bold": 1, "align": "left", "valign": "vcenter", "font_size": 11, "font_color": "#000000",
"fg_color": "#D9E1F2",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#305496", "right_color": "#305496"})
objExcelRowRight1Format = objExcelWorkbook.add_format({
"num_format": "#,##0",
"bold": 1, "align": "right", "valign": "vcenter", "font_size": 11, "font_color": "#000000",
"fg_color": "#FFFFFF",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#FFFFFF"})
objExcelRowRight2Format = objExcelWorkbook.add_format({
"num_format": "#,##0",
"bold": 1, "align": "right", "valign": "vcenter", "font_size": 11, "font_color": "#000000",
"fg_color": "#D9E1F2",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#D9E1F2"})
objExcelRowIcon1Format = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000",
"fg_color": "#FFFFFF",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#305496"})
objExcelRowIcon1UpFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#B02317",
"fg_color": "#FFFFFF",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#305496"})
objExcelRowIcon1DownFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#4EAD5B",
"fg_color": "#FFFFFF",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#305496"})
objExcelRowIcon1FlatFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#EBA064",
"fg_color": "#FFFFFF",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#305496"})
objExcelRowIcon2Format = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000",
"fg_color": "#D9E1F2",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#305496"})
objExcelRowIcon2UpFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#B02317",
"fg_color": "#D9E1F2",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#305496"})
objExcelRowIcon2DownFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#4EAD5B",
"fg_color": "#D9E1F2",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#305496"})
objExcelRowIcon2FlatFormat = objExcelWorkbook.add_format({
"bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#EBA064",
"fg_color": "#D9E1F2",
"border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#305496"})
objExcelWorksheet = objExcelWorkbook.add_worksheet(strKA_SYSTEM_CODE + "-" + strKA_SYSTEM_NM)
objExcelWorksheet.set_default_row(20)
objExcelWorksheet.set_row(3, 30)
objExcelWorksheet.set_column(0, 6, 8)
objExcelWorksheet.set_column(7, 7, 10)
objExcelWorksheet.set_column(8, 8, 20)
objExcelWorksheet.set_column(9, 9, 11)
objExcelRowCenterFormat = objExcelRowCenter1Format
objExcelRowLeftFormat = objExcelRowLeft1Format
objExcelRowRightFormat = objExcelRowRight1Format
objExcelRowIconFormat = objExcelRowIcon1Format
intExcelRow = 0
intTemp = 0
for dictWeekNum in listWeekNum:
objExcelWorksheet.set_column(10 + intTemp * 2, 10 + intTemp * 2, 11)
objExcelWorksheet.set_column(10 + intTemp * 2 + 1, 10 + intTemp * 2 + 1, 2.5)
intTemp += 1
objExcelWorksheet.merge_range("A1:I1", "现代渠道发展营业部业绩－通路系统品类周别POS推移", objExcelTitleFormat)
objExcelWorksheet.write(intExcelRow, 8, "", objExcelTitleFormat)
objExcelWorksheet.write(intExcelRow, 9, "", objExcelTitleFormat)
intTemp = 0
for dictWeekNum in listWeekNum:
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "", objExcelTitleFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelTitleFormat)
intTemp += 1
intExcelRow += 1
objExcelWorksheet.merge_range("A2:H2", Common.formatDateString(listPOSRPT01[0]["POS_DATE"]) + " 销售数量为最小单位加总", objExcelSubTitleFormat)
objExcelWorksheet.write(intExcelRow, 8, "", objExcelTitleFormat)
objExcelWorksheet.write(intExcelRow, 9, "", objExcelTitleFormat)
intTemp = 0
for dictWeekNum in listWeekNum:
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "", objExcelTitleFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelTitleFormat)
intTemp += 1
intExcelRow += 1
objExcelWorksheet.merge_range("A3:A4", "通路", objExcelColumnFormat)
objExcelWorksheet.merge_range("B3:B4", "系统编码", objExcelColumnFormat)
objExcelWorksheet.merge_range("C3:C4", "系统名称", objExcelColumnFormat)
objExcelWorksheet.merge_range("D3:D4", "分公司\n编码", objExcelColumnFormat)
objExcelWorksheet.merge_range("E3:E4", "分公司\n名称", objExcelColumnFormat)
objExcelWorksheet.merge_range("F3:F4", "统仓地\n编码", objExcelColumnFormat)
objExcelWorksheet.merge_range("G3:G4", "统仓地\n名称", objExcelColumnFormat)
objExcelWorksheet.merge_range("H3:H4", "品类编码", objExcelColumnFormat)
objExcelWorksheet.merge_range("I3:I4", "品类名称", objExcelColumnFormat)
objExcelWorksheet.write(intExcelRow, 9, "周数", objExcelColumnFormat)
intTemp = 0
for dictWeekNum in listWeekNum:
objExcelWorksheet.merge_range(getExcelColumnName(65 + 10 + intTemp * 2) + "3:" + getExcelColumnName(65 + 10 + intTemp * 2 + 1) + "3", int(dictWeekNum["WEEK_NUM"][-2:]), objExcelWeekFormat)
intTemp += 1
intExcelRow += 1
objExcelWorksheet.write(intExcelRow, 9, "KPI", objExcelColumnFormat)
intTemp = 0
for dictWeekNum in listWeekNum:
objExcelWorksheet.merge_range(getExcelColumnName(65 + 10 + intTemp * 2) + "4:" + getExcelColumnName(65 + 10 + intTemp * 2 + 1) + "4", Common.formatDateString(dictWeekNum["WEEK_FIRST_DATE"]), objExcelColumnFormat)
intTemp += 1
intExcelRow += 1
objExcelWorksheet.write(intExcelRow, 0, strKA_SYSTEM_ACT_NM, objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 1, strKA_SYSTEM_CODE, objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 2, strKA_SYSTEM_NM, objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 3, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 4, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 7, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 8, "合计", objExcelRowLeftFormat)
objExcelWorksheet.write(intExcelRow, 9, "销售数量", objExcelRowCenterFormat)
intIndexCount = 0
intIndexTemp = 0
intTemp = 0
for dictWeekNum in listWeekNum:
intIndexTemp = intPOSRPT01SystemIndex + intIndexCount
if intIndexTemp < len(listPOSRPT01SystemPCS) and \
listPOSRPT01SystemPCS[intIndexTemp]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \
listPOSRPT01SystemPCS[intIndexTemp]["POS_WEEK_NUM"] == listWeekNum[intTemp]["WEEK_NUM"]:
strDirection = ""
decTarget = 0
objExcelRowIconDirFormat = objExcelRowIconFormat
if (intIndexTemp + 1) < len(listPOSRPT01SystemPCS) and (intTemp + 1) < len(listWeekNum) and \
listPOSRPT01SystemPCS[intIndexTemp + 1]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \
listPOSRPT01SystemPCS[intIndexTemp + 1]["POS_WEEK_NUM"] == listWeekNum[intTemp + 1]["WEEK_NUM"]:
if intTemp == 0:
decTarget = round(listPOSRPT01SystemPCS[intIndexTemp + 1]["POS_QTY_PCS"] / 7 * intNowDates, 0)
else:
decTarget = listPOSRPT01SystemPCS[intIndexTemp + 1]["POS_QTY_PCS"]
if listPOSRPT01SystemPCS[intIndexTemp]["POS_QTY_PCS"] > decTarget:
strDirection = "▲"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1UpFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2UpFormat
elif listPOSRPT01SystemPCS[intIndexTemp]["POS_QTY_PCS"] < decTarget:
strDirection = "▼"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1DownFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2DownFormat
else:
strDirection = "━"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1FlatFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2FlatFormat
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, listPOSRPT01SystemPCS[intIndexTemp]["POS_QTY_PCS"], objExcelRowRightFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, strDirection, objExcelRowIconDirFormat)
intIndexCount += 1
intTemp += 1
else:
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "-", objExcelRowRightFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowIconFormat)
intTemp += 1
intExcelRow += 1
objExcelWorksheet.write(intExcelRow, 0, strKA_SYSTEM_ACT_NM, objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 1, strKA_SYSTEM_CODE, objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 2, strKA_SYSTEM_NM, objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 3, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 4, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 7, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 8, "合计", objExcelRowLeftFormat)
objExcelWorksheet.write(intExcelRow, 9, "品项数", objExcelRowCenterFormat)
intIndexCount = 0
intIndexTemp = 0
intTemp = 0
for dictWeekNum in listWeekNum:
intIndexTemp = intPOSRPT01SystemIndex + intIndexCount
if intIndexTemp < len(listPOSRPT01SystemSKU) and \
listPOSRPT01SystemSKU[intIndexTemp]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \
listPOSRPT01SystemSKU[intIndexTemp]["POS_WEEK_NUM"] == listWeekNum[intTemp]["WEEK_NUM"]:
strDirection = ""
decTarget = 0
objExcelRowIconDirFormat = objExcelRowIconFormat
if (intIndexTemp + 1) < len(listPOSRPT01SystemSKU) and (intTemp + 1) < len(listWeekNum) and \
listPOSRPT01SystemSKU[intIndexTemp + 1]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \
listPOSRPT01SystemSKU[intIndexTemp + 1]["POS_WEEK_NUM"] == listWeekNum[intTemp + 1]["WEEK_NUM"]:
decTarget = listPOSRPT01SystemSKU[intIndexTemp + 1]["POS_SKU"]
if listPOSRPT01SystemSKU[intIndexTemp]["POS_SKU"] > decTarget:
strDirection = "▲"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1UpFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2UpFormat
elif listPOSRPT01SystemSKU[intIndexTemp]["POS_SKU"] < decTarget:
strDirection = "▼"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1DownFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2DownFormat
else:
strDirection = "━"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1FlatFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2FlatFormat
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, listPOSRPT01SystemSKU[intIndexTemp]["POS_SKU"], objExcelRowRightFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, strDirection, objExcelRowIconDirFormat)
intIndexCount += 1
intTemp += 1
else:
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "-", objExcelRowRightFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowIconFormat)
intTemp += 1
intExcelRow += 1
intPOSRPT01SystemIndex += intIndexCount
objExcelWorksheet.write(intExcelRow, 0, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 1, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 2, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 3, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 4, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 7, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 8, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 9, "筛选", objExcelRowFilterFormat)
intTemp = 0
for dictWeekNum in listWeekNum:
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "", objExcelRowFilterFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowFilterFormat)
intTemp += 1
intExcelRow += 1
if objExcelRowCenterFormat == objExcelRowCenter2Format:
objExcelRowCenterFormat = objExcelRowCenter1Format
objExcelRowLeftFormat = objExcelRowLeft1Format
objExcelRowRightFormat = objExcelRowRight1Format
objExcelRowIconFormat = objExcelRowIcon1Format
else:
objExcelRowCenterFormat = objExcelRowCenter2Format
objExcelRowLeftFormat = objExcelRowLeft2Format
objExcelRowRightFormat = objExcelRowRight2Format
objExcelRowIconFormat = objExcelRowIcon2Format
objExcelWorksheet.write(intExcelRow, 0, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_ACT_NM"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 1, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_CODE"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 2, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_NM"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 3, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["SALES_COM_ID_SA"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 4, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["SALES_COM_ABR_SA"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 7, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["PROD_H1_ID"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 8, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["PROD_H1_NM"], objExcelRowLeftFormat)
objExcelWorksheet.write(intExcelRow, 9, "销售数量", objExcelRowCenterFormat)
intIndexCount = 0
intIndexTemp = 0
intTemp = 0
for dictWeekNum in listWeekNum:
intIndexTemp = intPOSRPT01ProdH1Index + intIndexCount
if intIndexTemp < len(listPOSRPT01ProdH1PCS) and \
listPOSRPT01ProdH1PCS[intIndexTemp]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \
listPOSRPT01ProdH1PCS[intIndexTemp]["PROD_H1_ID"] == listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["PROD_H1_ID"] and \
listPOSRPT01ProdH1PCS[intIndexTemp]["POS_WEEK_NUM"] == listWeekNum[intTemp]["WEEK_NUM"]:
strDirection = ""
decTarget = 0
objExcelRowIconDirFormat = objExcelRowIconFormat
if (intIndexTemp + 1) < len(listPOSRPT01ProdH1PCS) and (intTemp + 1) < len(listWeekNum) and \
listPOSRPT01ProdH1PCS[intIndexTemp + 1]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \
listPOSRPT01ProdH1PCS[intIndexTemp + 1]["PROD_H1_ID"] == listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["PROD_H1_ID"] and \
listPOSRPT01ProdH1PCS[intIndexTemp + 1]["POS_WEEK_NUM"] == listWeekNum[intTemp + 1]["WEEK_NUM"]:
if intTemp == 0:
decTarget = round(listPOSRPT01ProdH1PCS[intIndexTemp + 1]["POS_QTY_PCS"] / 7 * intNowDates, 0)
else:
decTarget = listPOSRPT01ProdH1PCS[intIndexTemp + 1]["POS_QTY_PCS"]
if listPOSRPT01ProdH1PCS[intIndexTemp]["POS_QTY_PCS"] > decTarget:
strDirection = "▲"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1UpFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2UpFormat
elif listPOSRPT01ProdH1PCS[intIndexTemp]["POS_QTY_PCS"] < decTarget:
strDirection = "▼"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1DownFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2DownFormat
else:
strDirection = "━"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1FlatFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2FlatFormat
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, listPOSRPT01ProdH1PCS[intIndexTemp]["POS_QTY_PCS"], objExcelRowRightFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, strDirection, objExcelRowIconDirFormat)
intIndexCount += 1
intTemp += 1
else:
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "-", objExcelRowRightFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowIconFormat)
intTemp += 1
intExcelRow += 1
objExcelWorksheet.write(intExcelRow, 0, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["KA_SYSTEM_ACT_NM"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 1, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["KA_SYSTEM_CODE"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 2, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["KA_SYSTEM_NM"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 3, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["SALES_COM_ID_SA"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 4, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["SALES_COM_ABR_SA"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 7, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["PROD_H1_ID"], objExcelRowCenterFormat)
objExcelWorksheet.write(intExcelRow, 8, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["PROD_H1_NM"], objExcelRowLeftFormat)
objExcelWorksheet.write(intExcelRow, 9, "品项数", objExcelRowCenterFormat)
intIndexCount = 0
intIndexTemp = 0
intTemp = 0
for dictWeekNum in listWeekNum:
intIndexTemp = intPOSRPT01ProdH1Index + intIndexCount
if intIndexTemp < len(listPOSRPT01ProdH1SKU) and \
listPOSRPT01ProdH1SKU[intIndexTemp]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \
listPOSRPT01ProdH1SKU[intIndexTemp]["PROD_H1_ID"] == listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["PROD_H1_ID"] and \
listPOSRPT01ProdH1SKU[intIndexTemp]["POS_WEEK_NUM"] == listWeekNum[intTemp]["WEEK_NUM"]:
strDirection = ""
decTarget = 0
objExcelRowIconDirFormat = objExcelRowIconFormat
if (intIndexTemp + 1) < len(listPOSRPT01ProdH1SKU) and (intTemp + 1) < len(listWeekNum) and \
listPOSRPT01ProdH1SKU[intIndexTemp + 1]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \
listPOSRPT01ProdH1SKU[intIndexTemp + 1]["PROD_H1_ID"] == listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["PROD_H1_ID"] and \
listPOSRPT01ProdH1SKU[intIndexTemp + 1]["POS_WEEK_NUM"] == listWeekNum[intTemp + 1]["WEEK_NUM"]:
decTarget = listPOSRPT01ProdH1SKU[intIndexTemp + 1]["POS_SKU"]
if listPOSRPT01ProdH1SKU[intIndexTemp]["POS_SKU"] > decTarget:
strDirection = "▲"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1UpFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2UpFormat
elif listPOSRPT01ProdH1SKU[intIndexTemp]["POS_SKU"] < decTarget:
strDirection = "▼"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1DownFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2DownFormat
else:
strDirection = "━"
if objExcelRowIconFormat == objExcelRowIcon1Format:
objExcelRowIconDirFormat = objExcelRowIcon1FlatFormat
else:
objExcelRowIconDirFormat = objExcelRowIcon2FlatFormat
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, listPOSRPT01ProdH1SKU[intIndexTemp]["POS_SKU"], objExcelRowRightFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, strDirection, objExcelRowIconDirFormat)
intIndexCount += 1
intTemp += 1
else:
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "-", objExcelRowRightFormat)
objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowIconFormat)
intTemp += 1
intExcelRow += 1
intPOSRPT01ProdH1Index += intIndexCount
if objExcelWorksheet != None:
objExcelWorksheet.freeze_panes("K8")
objExcelWorksheet.autofilter("A7:CF" + str(intExcelRow))
objExcelWorksheet.set_column('F:G', None, None, {'hidden': True})
if objExcelWorkbook != None:
objExcelWorkbook.close()
return True
def execWriteWorkbook(strJobPathRES, listStoreMatlD):
if len(listStoreMatlD) == 0:
return False
else:
objBook = None
strKA_SYSTEM_CODE = None
intRowIndex = 0
for dictStoreMatlD in listStoreMatlD:
if dictStoreMatlD["KA_SYSTEM_CODE"] != strKA_SYSTEM_CODE:
strKA_SYSTEM_CODE = dictStoreMatlD["KA_SYSTEM_CODE"]
if objBook != None:
objBook.close()
objBook = xlsxwriter.Workbook(
strJobPathRES.replace(
".dat",
"-" + dictStoreMatlD["KA_SYSTEM_CODE"] + ".dat"))
objColumnFormat = objBook.add_format({
"text_wrap": 1,
"align": "center",
"valign": "vcenter",
"bold": 1,
"font_size": 11,
"font_color": "#FFFFFF",
"fg_color": "#305496",
"border": 0
})
objRowCenterFormat = objBook.add_format({
"align": "center",
"valign": "vcenter",
"bold": 1,
"font_size": 11,
"font_color": "#000000",
"border": 0
})
objRowLeftFormat = objBook.add_format({
"align": "left",
"valign": "vcenter",
"bold": 1,
"font_size": 11,
"font_color": "#000000",
"border": 0
})
objRowRightFormat = objBook.add_format({
"align": "right",
"valign": "vcenter",
"bold": 1,
"font_size": 11,
"font_color": "#000000",
"border": 0
})
objRowRightFormat.set_num_format("0.0000_ ")
objSheet = objBook.add_worksheet(
dictStoreMatlD["KA_SYSTEM_CODE"] +
dictStoreMatlD["KA_SYSTEM_NM"])
objSheet.set_default_row(20)
objSheet.set_column(0, 0, 8)
objSheet.set_column(1, 1, 10)
objSheet.set_column(2, 2, 15)
objSheet.set_column(3, 3, 30)
objSheet.set_column(4, 4, 10)
objSheet.set_column(5, 5, 10)
objSheet.set_column(6, 6, 10)
objSheet.set_column(7, 7, 10)
objSheet.set_column(8, 8, 10)
objSheet.set_column(9, 9, 20)
objSheet.set_column(10, 10, 40)
objSheet.set_column(11, 11, 12)
objSheet.set_column(12, 12, 10)
objSheet.set_column(13, 13, 10)
objSheet.set_column(14, 14, 10)
objSheet.set_column(15, 15, 10)
objSheet.set_column(16, 16, 10)
intRowIndex = 0
objSheet.write(intRowIndex, 0, "系统代号", objColumnFormat)
objSheet.write(intRowIndex, 1, "系统名称", objColumnFormat)
objSheet.write(intRowIndex, 2, "门店编号", objColumnFormat)
objSheet.write(intRowIndex, 3, "门店名称", objColumnFormat)
objSheet.write(intRowIndex, 4, "门店大仓别", objColumnFormat)
objSheet.write(intRowIndex, 5, "业绩拆分地", objColumnFormat)
objSheet.write(intRowIndex, 6, "大仓所在地", objColumnFormat)
objSheet.write(intRowIndex, 7, "产品大类", objColumnFormat)
objSheet.write(intRowIndex, 8, "PM线", objColumnFormat)
objSheet.write(intRowIndex, 9, "物料编号", objColumnFormat)
objSheet.write(intRowIndex, 10, "物料名称", objColumnFormat)
objSheet.write(intRowIndex, 11, "销售库存日期", objColumnFormat)
objSheet.write(intRowIndex, 12, "销售数量", objColumnFormat)
objSheet.write(intRowIndex, 13, "销售件数", objColumnFormat)
objSheet.write(intRowIndex, 14, "销售金额", objColumnFormat)
objSheet.write(intRowIndex, 15, "库存数量", objColumnFormat)
objSheet.write(intRowIndex, 16, "库存件数", objColumnFormat)
intRowIndex += 1
objSheet.write(intRowIndex, 0, dictStoreMatlD["KA_SYSTEM_CODE"],
objRowCenterFormat)
objSheet.write(intRowIndex, 1, dictStoreMatlD["KA_SYSTEM_NM"],
objRowCenterFormat)
objSheet.write(intRowIndex, 2, dictStoreMatlD["KA_STORE_WH_CODE"],
objRowCenterFormat)
objSheet.write(intRowIndex, 3, dictStoreMatlD["KA_STORE_WH_NM"],
objRowLeftFormat)
objSheet.write(intRowIndex, 4, dictStoreMatlD["STORE_WH_TYPE_NM"],
objRowCenterFormat)
objSheet.write(intRowIndex, 5, dictStoreMatlD["SALES_COM_ABR_SA"],
objRowCenterFormat)
objSheet.write(intRowIndex, 6, dictStoreMatlD["SALES_COM_ABR_WH"],
objRowCenterFormat)
objSheet.write(intRowIndex, 7, dictStoreMatlD["PROD_H1_NM"],
objRowCenterFormat)
objSheet.write(intRowIndex, 8, dictStoreMatlD["PROD_H2_NM"],
objRowCenterFormat)
objSheet.write(intRowIndex, 9, dictStoreMatlD["PROD_MATL_ID"],
objRowCenterFormat)
objSheet.write(intRowIndex, 10, dictStoreMatlD["PROD_MATL_NM"],
objRowLeftFormat)
objSheet.write(
intRowIndex, 11,
Common.formatDateString(dictStoreMatlD["POS_INV_DATE"]),
objRowCenterFormat)
objSheet.write(intRowIndex, 12, dictStoreMatlD["POS_QTY_PCS"],
objRowRightFormat)
objSheet.write(intRowIndex, 13, dictStoreMatlD["POS_QTY_PKG"],
objRowRightFormat)
objSheet.write(intRowIndex, 14, dictStoreMatlD["POS_AMT"],
objRowRightFormat)
objSheet.write(intRowIndex, 15, dictStoreMatlD["INV_QTY_PCS"],
objRowRightFormat)
objSheet.write(intRowIndex, 16, dictStoreMatlD["INV_QTY_PKG"],
objRowRightFormat)
intRowIndex += 1
objBook.close()
return True
def execWriteWorkbook(strJobPathRES, listEMPSales):
if len(listEMPSales) == 0:
return False
else:
objExcelWorkbook = None
objExcelWorksheet = None
objExcelWorkbook = xlsxwriter.Workbook(strJobPathRES)
objExcelColumnFormat = objExcelWorkbook.add_format({
"bold": 1,
"align": "center",
"valign": "vcenter",
"font_size": 11,
"font_color": "#FFFFFF",
"fg_color": "#305496",
"border": 0
})
objExcelRowCenterFormat = objExcelWorkbook.add_format({
"bold": 1,
"align": "center",
"valign": "vcenter",
"font_size": 11,
"font_color": "#000000",
"border": 0
})
objExcelRowLeftFormat = objExcelWorkbook.add_format({
"bold": 1,
"align": "left",
"valign": "vcenter",
"font_size": 11,
"font_color": "#000000",
"border": 0
})
objExcelRowRightFormat = objExcelWorkbook.add_format({
"bold": 1,
"align": "right",
"valign": "vcenter",
"font_size": 11,
"font_color": "#000000",
"border": 0
})
objExcelRowRightFormat.set_num_format("0.00")
objExcelRow = 0
objExcelWorksheet = objExcelWorkbook.add_worksheet("业务人员花名册")
objExcelWorksheet.set_default_row(20)
objExcelWorksheet.set_column(0, 0, 10)
objExcelWorksheet.set_column(1, 1, 10)
objExcelWorksheet.set_column(2, 2, 6)
objExcelWorksheet.set_column(3, 3, 15)
objExcelWorksheet.set_column(4, 4, 8)
objExcelWorksheet.set_column(5, 5, 15)
objExcelWorksheet.set_column(6, 6, 15)
objExcelWorksheet.set_column(7, 7, 18)
objExcelWorksheet.set_column(8, 8, 18)
objExcelWorksheet.set_column(9, 9, 15)
objExcelWorksheet.set_column(10, 10, 10)
objExcelWorksheet.set_column(11, 11, 15)
objExcelWorksheet.set_column(12, 12, 10)
objExcelWorksheet.set_column(13, 13, 6)
objExcelWorksheet.write(objExcelRow, 0, "人员编号", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 1, "人员姓名", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 2, "性别", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 3, "加入日期", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 4, "年资", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 5, "所属部门", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 6, "分公司", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 7, "营业所", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 8, "职位", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 9, "职位类型", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 10, "职位注记", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 11, "职务", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 12, "职等编号", objExcelColumnFormat)
objExcelWorksheet.write(objExcelRow, 13, "职等", objExcelColumnFormat)
objExcelRow += 1
for dictEMPSales in listEMPSales:
objExcelWorksheet.write(objExcelRow, 0, dictEMPSales["EMP_ID"],
objExcelRowCenterFormat)
objExcelWorksheet.write(objExcelRow, 1, dictEMPSales["EMP_NM"],
objExcelRowLeftFormat)
objExcelWorksheet.write(objExcelRow, 2,
dictEMPSales["EMP_GENDER_NM"],
objExcelRowCenterFormat)
objExcelWorksheet.write(
objExcelRow, 3,
Common.formatDateString(dictEMPSales["ONBOARD_YMD"]),
objExcelRowCenterFormat)
objExcelWorksheet.write(objExcelRow, 4, dictEMPSales["SENIORITY"],
objExcelRowRightFormat)
objExcelWorksheet.write(objExcelRow, 5,
dictEMPSales["EMP_POS_PROP_NM"],
objExcelRowLeftFormat)
objExcelWorksheet.write(objExcelRow, 6, dictEMPSales["HR_COM_NM"],
objExcelRowLeftFormat)
objExcelWorksheet.write(objExcelRow, 7, dictEMPSales["HR_OFF_NM"],
objExcelRowLeftFormat)
objExcelWorksheet.write(objExcelRow, 8, dictEMPSales["EMP_POS_NM"],
objExcelRowLeftFormat)
objExcelWorksheet.write(objExcelRow, 9,
dictEMPSales["EMP_POS_TYPE_NM"],
objExcelRowLeftFormat)
objExcelWorksheet.write(objExcelRow, 10,
dictEMPSales["EMP_POS_FLAG"],
objExcelRowCenterFormat)
objExcelWorksheet.write(objExcelRow, 11,
dictEMPSales["EMP_POS_TITLE_NM"],
objExcelRowLeftFormat)
objExcelWorksheet.write(objExcelRow, 12,
dictEMPSales["EMP_POS_LEVEL_ID"],
objExcelRowCenterFormat)
objExcelWorksheet.write(objExcelRow, 13,
dictEMPSales["EMP_POS_LEVEL_NM"],
objExcelRowCenterFormat)
objExcelRow += 1
objExcelWorkbook.close()
return True