def makeHmSettlementErrorExcelDurationFile(queryData, rootPath):
excelZip = None
jobStatus = 0
batchId = None
try:
fileCnt = 1
makeDate = datetime.datetime.now().strftime('%Y%m%d')
makeTime = str(int(round(time.time() * 1000)))
uploads = os.path.join(rootPath, "fileDownload", "excel", makeTime)
if not os.path.isdir(uploads):
os.makedirs(uploads)
zipFileName = u'HM대사_불일치_' + makeDate + '.zip'
#작업 시작 메시지 추가
batchId = postBatchMng({
"reqId": queryData['empId'],
"status": "BAT-0001", # 진행중
"filePath": os.path.join(uploads, zipFileName),
"content": "HM대사_불일치",
"errMsg": ""
})["data"]["batchId"]
fileName = 'HM대사_불일치_' + makeDate + '_' + str(fileCnt) + '.xlsx'
workbook = xlsxwriter.Workbook(
os.path.join(uploads, setUnicodeEncodeTypeToEucKr(fileName)))
money_format = workbook.add_format()
money_format.set_num_format('_- #,##0_-;[red]- #,##0_-;_- "-"_-;_-@_-')
worksheet = workbook.add_worksheet()
row = 0
worksheet.write(row, 0, "거래처")
worksheet.write(row, 1, "영업일자")
worksheet.write(row, 2, "지불상태")
worksheet.write(row, 3, "주문번호")
worksheet.write(row, 4, "승인번호")
worksheet.write(row, 5, "거래금액")
worksheet.write(row, 6, "불일치내역")
while True:
searchData = getData("/v1/settlement/hmStatistics/inconsistency",
queryData, API_SERVER_BILLINGSERVICE)
print searchData
for data in searchData["resultList"]:
row += 1
worksheet.write(row, 0, data["jobDivider"])
worksheet.write(row, 1, data["saleDt"])
worksheet.write(row, 2, data["dealType"])
worksheet.write(row, 3, data["dealNo"])
worksheet.write(row, 4, data["approvalNo"])
worksheet.write_number(row, 5, long(data["dealAmt"]),
money_format)
worksheet.write(row, 6, data["statusNm"])
if row >= EXCEL_FILE_MAKE_LIMT_COUNT:
row = 0
fileCnt += 1
fileName = 'HM대사_불일치_' + makeDate + '_' + str(
fileCnt) + '.xlsx'
# 디비 조회건수 * 2 row 생성시 파일 재생성
workbook.close()
workbook = xlsxwriter.Workbook(
os.path.join(uploads,
setUnicodeEncodeTypeToEucKr(fileName)))
money_format = workbook.add_format()
money_format.set_num_format(
'_- #,##0_-;[red]- #,##0_-;_- "-"_-;_-@_-')
worksheet = workbook.add_worksheet()
worksheet.write(row, 0, data["jobDivider"])
worksheet.write(row, 1, data["saleDt"])
worksheet.write(row, 2, data["dealType"])
worksheet.write(row, 3, data["dealNo"])
worksheet.write(row, 4, data["approvalNo"])
worksheet.write_number(row, 5, long(data["dealAmt"]),
money_format)
worksheet.write(row, 6, data["statusNm"])
queryData[
"offset"] = queryData["offset"] + EXCEL_FILE_DOWNLOAD_COUNT
if len(searchData["resultList"]) < EXCEL_FILE_DOWNLOAD_COUNT:
break
workbook.close()
excelZip = zipfile.ZipFile(os.path.join(uploads, zipFileName), 'w')
for folder, subfolders, files in os.walk(uploads):
for file in files:
if file.endswith('.xlsx'):
excelZip.write(os.path.join(folder, file),
setUnicodeFormatToEucKr(file),
compress_type=zipfile.ZIP_DEFLATED)
except Exception as err:
#에러 메시지 추가
putBatchMng({
"batchId": str(batchId),
"reqId": queryData['empId'],
"status": "BAT-0003", # 진행중
"errMsg": str(err)
})
jobStatus = 1
print("Error on line {}".format(sys.exc_info()[-1].tb_lineno))
print(err)
finally:
if excelZip != None:
excelZip.close()
if jobStatus == 0:
putBatchMng({
"batchId": str(batchId),
"reqId": queryData['empId'],
"status": "BAT-0002", # 진행중
"errMsg": ""
})
#성공 메시지 추가
print "성공"
def makeBillingExcelFile(queryData, rootPath):
excelZip = None
jobStatus = 0
batchId = None
try:
fileCnt = 1
makeTime = str(int(round(time.time() * 1000)))
uploads = os.path.join(rootPath, "fileDownload", "excel", makeTime)
if not os.path.isdir(uploads):
os.makedirs(uploads)
zipFileName = u'정산명세서_' + datetime.datetime.now().strftime(
'%Y%m%d') + '.zip'
#작업 시작 메시지 추가
batchId = postBatchMng({
"reqId": queryData['empId'],
"status": "BAT-0001", # 진행중
"filePath": os.path.join(uploads, zipFileName),
"content": "정산명세서 엑셀 배치작업",
"errMsg": ""
})["data"]["batchId"]
fileName = '정산명세서_' + datetime.datetime.now().strftime(
'%Y%m%d') + '_' + str(fileCnt) + '.xlsx'
workbook = xlsxwriter.Workbook(
os.path.join(uploads, setUnicodeEncodeTypeToEucKr(fileName)))
worksheet = workbook.add_worksheet()
money_format = workbook.add_format({'align': 'right'})
money_format.set_num_format('_- #,##0_-;[red]- #,##0_-;_- "-"_-;_-@_-')
title_format = workbook.add_format({
'align': 'center',
'valign': 'vcenter',
'bold': True,
'border': 1,
'fg_color': '#A9D0F5'
})
string_format = workbook.add_format({
'align': 'center',
'valign': 'vcenter'
})
row = 1
worksheet.write(0, 0, "순번", title_format)
worksheet.write(0, 1, "거래처명", title_format)
worksheet.write(0, 2, "서비스명", title_format)
worksheet.write(0, 3, "정산기간", title_format)
worksheet.write(0, 4, "정산지급일", title_format)
worksheet.write(0, 5, "거래구분", title_format)
worksheet.write(0, 6, "거래금액", title_format)
worksheet.write(0, 7, "거래 취소 금액", title_format)
worksheet.write(0, 8, "총 거래금액", title_format)
worksheet.write(0, 9, "결제금액", title_format)
worksheet.write(0, 10, "할인금액", title_format)
worksheet.write(0, 11, "정산 타입", title_format)
worksheet.write(0, 12, "정산주기", title_format)
worksheet.write(0, 13, "거래건수", title_format)
worksheet.write(0, 14, "정산 수수료", title_format)
worksheet.write(0, 15, "수수료 타입", title_format)
worksheet.write(0, 16, "부가세 타입", title_format)
worksheet.write(0, 17, "수수료", title_format)
worksheet.write(0, 18, "부가세", title_format)
worksheet.write(0, 19, "수수료 합계", title_format)
worksheet.write(0, 20, "정산금액", title_format)
worksheet.write(0, 21, "은행", title_format)
worksheet.write(0, 22, "예금주", title_format)
worksheet.write(0, 23, "계좌번호", title_format)
while True:
searchData = getData("/billings", queryData)
for data in searchData["resultList"]:
row += 1
if (data["merchantCommision"] is not None):
merchantComission = data["merchantCommision"] + "%" if data[
"merchantCommType"] == "FEE-0001" else data[
"merchantCommision"] + "원" if data[
"merchantCommType"] == "FEE-0002" else "-"
else:
merchantComission = "-"
worksheet.write(row, 0, (row - 1), string_format)
worksheet.write(row, 1, data["submerchantName"], string_format)
worksheet.write(row, 2, data["serviceName"], string_format)
worksheet.write(
row, 3,
data["approvalDtMin"] + " ~ " + data["approvalDtMax"],
string_format)
worksheet.write(row, 4, data["billingDt"], string_format)
worksheet.write(row, 5, data["typeName"], string_format)
worksheet.write(row, 6, data["amount"], money_format)
worksheet.write(row, 7, data["cancelAmount"], money_format)
worksheet.write(row, 8, data["totalAmount"], money_format)
worksheet.write(row, 9, data["payAmount"], money_format)
worksheet.write(row, 10, data["dcAmount"], money_format)
worksheet.write(row, 11, data["billingCommType"],
string_format)
worksheet.write(row, 12, data["billingDuration"],
string_format)
worksheet.write(row, 13, data["cnt"], string_format)
worksheet.write(row, 14, merchantComission, string_format)
worksheet.write(row, 15, data["commTypeName"], string_format)
worksheet.write(row, 16, data["taxTypeName"], string_format)
worksheet.write(row, 17, data["commision"], money_format)
worksheet.write(row, 18, data["tax"], money_format)
worksheet.write(row, 19, data["commTotal"], money_format)
worksheet.write(row, 20, data["billingAmount"], money_format)
worksheet.write(row, 21, data["bankNm"], string_format)
worksheet.write(row, 22, data["bankHolder"], string_format)
worksheet.write(row, 23, data["bankAccNo"], string_format)
if row >= EXCEL_FILE_MAKE_LIMT_COUNT:
row = 2
fileCnt += 1
fileName = '거래내역_결제_' + datetime.datetime.now().strftime(
'%Y%m%d') + '_' + str(fileCnt) + '.xlsx'
# 디비 조회건수 * 2 row 생성시 파일 재생성
workbook.close()
workbook = xlsxwriter.Workbook(
os.path.join(uploads,
setUnicodeEncodeTypeToEucKr(fileName)))
worksheet = workbook.add_worksheet()
money_format = workbook.add_format()
money_format.set_num_format(
'_- #,##0_-;[red]- #,##0_-;_- "-"_-;_-@_-')
worksheet.write(0, 0, "순번", title_format)
worksheet.write(0, 1, "거래처명", title_format)
worksheet.write(0, 2, "서비스명", title_format)
worksheet.write(0, 3, "정산기간", title_format)
worksheet.write(0, 4, "정산지급일", title_format)
worksheet.write(0, 5, "거래구분", title_format)
worksheet.write(0, 6, "거래금액", title_format)
worksheet.write(0, 7, "거래 취소 금액", title_format)
worksheet.write(0, 8, "총 거래금액", title_format)
worksheet.write(0, 9, "결제금액", title_format)
worksheet.write(0, 10, "할인금액", title_format)
worksheet.write(0, 11, "정산 타입", title_format)
worksheet.write(0, 12, "정산주기", title_format)
worksheet.write(0, 13, "거래건수", title_format)
worksheet.write(0, 14, "정산 수수료", title_format)
worksheet.write(0, 15, "수수료 타입", title_format)
worksheet.write(0, 16, "부가세 타입", title_format)
worksheet.write(0, 17, "수수료", title_format)
worksheet.write(0, 18, "부가세", title_format)
worksheet.write(0, 19, "수수료 합계", title_format)
worksheet.write(0, 20, "정산금액", title_format)
worksheet.write(0, 21, "은행", title_format)
worksheet.write(0, 22, "예금주", title_format)
worksheet.write(0, 23, "계좌번호", title_format)
queryData[
"offset"] = queryData["offset"] + EXCEL_FILE_DOWNLOAD_COUNT
if len(searchData["resultList"]) < EXCEL_FILE_DOWNLOAD_COUNT:
break
workbook.close()
excelZip = zipfile.ZipFile(os.path.join(uploads, zipFileName), 'w')
for folder, subfolders, files in os.walk(uploads):
for file in files:
if file.endswith('.xlsx'):
excelZip.write(os.path.join(folder, file),
setUnicodeFormatToEucKr(file),
compress_type=zipfile.ZIP_DEFLATED)
except Exception as err:
#에러 메시지 추가
putBatchMng({
"batchId": str(batchId),
"reqId": queryData['empId'],
"status": "BAT-0003", # 진행중
"errMsg": str(err)
})
jobStatus = 1
print err
finally:
if excelZip != None:
excelZip.close()
if jobStatus == 0:
putBatchMng({
"batchId": str(batchId),
"reqId": queryData['empId'],
"status": "BAT-0002", # 진행중
"errMsg": ""
})
#성공 메시지 추가
print "성공"
def makeSettlementErrorExcelFile(queryData, rootPath):
excelZip = None
jobStatus = 0
batchId = None
jobDivider = str(queryData.get('jobDivider'))
if jobDivider is None:
jobDivider = ""
try:
fileCnt = 1
makeDate = datetime.datetime.now().strftime('%Y%m%d')
makeTime = str(int(round(time.time() * 1000)))
uploads = os.path.join(rootPath, "fileDownload", "excel", makeTime)
if not os.path.isdir(uploads):
os.makedirs(uploads)
zipFileName = u'GS대사_불일치(' + jobDivider + u')_' + makeDate + '.zip'
#작업 시작 메시지 추가
batchId = postBatchMng({
"reqId": queryData['empId'],
"status": "BAT-0001", # 진행중
"filePath": os.path.join(uploads, zipFileName),
"content": "GS대사 불일치",
"errMsg": ""
})["data"]["batchId"]
fileName = 'GS대사_불일치(' + jobDivider + ')_' + makeDate + '_' + str(
fileCnt) + '.xlsx'
workbook = xlsxwriter.Workbook(
os.path.join(uploads, setUnicodeEncodeTypeToEucKr(fileName)))
worksheet = workbook.add_worksheet()
money_format = workbook.add_format()
money_format.set_num_format('_- #,##0_-;[red]- #,##0_-;_- "-"_-;_-@_-')
row = 0
worksheet.write(row, 0, "거래처")
worksheet.write(row, 1, "영업일자")
worksheet.write(row, 2, "점포코드")
worksheet.write(row, 3, "점포명(사용처)")
worksheet.write(row, 4, "지불상태")
worksheet.write(row, 5, "상태")
worksheet.write(row, 6, "주문번호")
worksheet.write(row, 7, "승인번호")
worksheet.write(row, 8, "승인일자")
worksheet.write(row, 9, "승인시간")
worksheet.write(row, 10, "거래금액")
worksheet.write(row, 11, "카드번호")
worksheet.write(row, 12, "불일치내역")
while True:
searchData = getData("/v1/settlement/gsStatistics/errorDetailAll",
queryData, API_SERVER_BILLINGSERVICE)
for data in searchData["resultList"]:
row += 1
worksheet.write(row, 0, data["jobDivider"])
worksheet.write(row, 1, data["saleDt"])
worksheet.write(row, 2, data["storeCd"])
worksheet.write(row, 3, data["storeNm"])
worksheet.write(row, 4, data["dealType"])
worksheet.write(row, 5, data["dealDivider"])
worksheet.write(row, 6, data["dealNo"])
worksheet.write(row, 7, data["approvalNo"])
worksheet.write(
row, 8, parseDate(data["approvalDt"], '%Y%m%d',
'%Y-%m-%d'))
if data["approvalTime"] is None:
worksheet.write(row, 9, "")
else:
worksheet.write(
row, 9,
parseDate(
data["approvalDt"] + " " + data["approvalTime"],
'%Y%m%d %H%M%S', '%H:%M:%S'))
worksheet.write_number(row, 10, long(data["dealAmt"]),
money_format)
worksheet.write(row, 11, data["cardNo"])
worksheet.write(row, 12, data["statusNm"])
if row >= EXCEL_FILE_MAKE_LIMT_COUNT:
row = 0
fileCnt += 1
fileName = 'GS대사_불일치(' + jobDivider + ')_' + makeDate + '_' + str(
fileCnt) + '.xlsx'
# 디비 조회건수 * 2 row 생성시 파일 재생성
workbook.close()
workbook = xlsxwriter.Workbook(
os.path.join(uploads,
setUnicodeEncodeTypeToEucKr(fileName)))
money_format = workbook.add_format()
money_format.set_num_format(
'_- #,##0_-;[red]- #,##0_-;_- "-"_-;_-@_-')
worksheet = workbook.add_worksheet()
worksheet.write(row, 0, data["jobDivider"])
worksheet.write(row, 1, data["saleDt"])
worksheet.write(row, 2, data["storeCd"])
worksheet.write(row, 3, data["storeNm"])
worksheet.write(row, 4, data["dealType"])
worksheet.write(row, 5, data["dealDivider"])
worksheet.write(row, 6, data["dealNo"])
worksheet.write(row, 7, data["approvalNo"])
worksheet.write(
row, 8,
parseDate(data["approvalDt"], '%Y%m%d', '%Y-%m-%d'))
if data["approvalTime"] is None:
worksheet.write(row, 9, "")
else:
worksheet.write(
row, 9,
parseDate(
data["approvalDt"] + " " +
data["approvalTime"], '%Y%m%d %H%M%S',
'%H:%M:%S'))
worksheet.write_number(row, 10, long(data["dealAmt"]),
money_format)
worksheet.write(row, 11, data["cardNo"])
worksheet.write(row, 12, data["statusNm"])
queryData[
"offset"] = queryData["offset"] + EXCEL_FILE_DOWNLOAD_COUNT
if len(searchData["resultList"]) < EXCEL_FILE_DOWNLOAD_COUNT:
break
workbook.close()
excelZip = zipfile.ZipFile(os.path.join(uploads, zipFileName), 'w')
for folder, subfolders, files in os.walk(uploads):
for file in files:
if file.endswith('.xlsx'):
# excelZip.write(os.path.join(folder ,file), os.path.relpath(os.path.join(folder ,file) , uploads), compress_type=zipfile.ZIP_DEFLATED)
excelZip.write(os.path.join(folder, file),
setUnicodeFormatToEucKr(file),
compress_type=zipfile.ZIP_DEFLATED)
except Exception as err:
#에러 메시지 추가
putBatchMng({
"batchId": str(batchId),
"reqId": queryData['empId'],
"status": "BAT-0003", # 진행중
"errMsg": str(err)
})
jobStatus = 1
print err
finally:
if excelZip != None:
excelZip.close()
if jobStatus == 0:
putBatchMng({
"batchId": str(batchId),
"reqId": queryData['empId'],
"status": "BAT-0002", # 진행중
"errMsg": ""
})
#성공 메시지 추가
print "성공"
def makeAggregateExcelFile(queryData, rootPath):
jobStatus = 0
batchId = None
try:
uploads = os.path.join(rootPath, "docs", "excel", "aggregate")
if not os.path.isdir(uploads):
os.makedirs(uploads)
makeTime = str(int(round(time.time() * 1000)))
outputPath = os.path.join(rootPath, "fileDownload", "excel", makeTime)
if not os.path.isdir(outputPath):
os.makedirs(outputPath)
fileName = u'aggregate.xlsx'
saveFileName = u'매출데이터' + datetime.datetime.now().strftime(
'%Y%m%d') + '.xlsx'
#작업 시작 메시지 추가
batchId = postBatchMng({
"reqId":
queryData['empId'],
"status":
"BAT-0001", # 진행중
"filePath":
os.path.join(outputPath, saveFileName),
"content":
"매출데이터 생성",
"errMsg":
""
})["data"]["batchId"]
searchData = getData("/api/admin/v1/aggreagate", queryData,
API_SERVER_KPC_PAYMENT)
excel_document = openpyxl.load_workbook(
filename=os.path.join(uploads, fileName))
sheet = excel_document.get_sheet_by_name('sales')
row = 2
# dailySum = {}
sheet.cell(row=1, column=7, value=queryData["searchDate"])
# 매출 집계
if searchData["aggregateDaily"] != None:
for data in searchData["aggregateDaily"]:
sheet.cell(row=row, column=1, value=data["gubun"])
sheet.cell(row=row, column=2, value=data["name"])
sheet.cell(row=row, column=3, value=data["aplonLineId"])
sheet.cell(row=row, column=4, value=data["payMethod"])
sheet.cell(row=row, column=5, value=data["barCode"])
sheet.cell(row=row, column=6, value=data["payKind"])
sheet.cell(row=row, column=7, value=data["totalAmount"])
row = row + 1
# if data["aplonLineId"] in dailySum :
# dailySum[data["aplonLineId"]] = dailySum[data["aplonLineId"]] + data["payAmount"]
# else :
# dailySum[data["aplonLineId"]] = data["payAmount"]
# 해피캐시
if searchData["aggregateDailyHappyCash"] != None:
sheet.cell(row=row,
column=1,
value=searchData["aggregateDailyHappyCash"]["gubun"])
sheet.cell(row=row, column=2, value="해피캐시 전환")
sheet.cell(
row=row,
column=4,
value=searchData["aggregateDailyHappyCash"]["aplonLineId"])
sheet.cell(
row=row,
column=3,
value=searchData["aggregateDailyHappyCash"]["payMethod"])
sheet.cell(row=row,
column=5,
value=searchData["aggregateDailyHappyCash"]["barCode"])
sheet.cell(row=row,
column=6,
value=searchData["aggregateDailyHappyCash"]["payKind"])
sheet.cell(
row=row,
column=7,
value=searchData["aggregateDailyHappyCash"]["totalAmount"])
# 우측 해피 캐시
# sheet.cell(row = 6 , column=11 , value=searchData["aggregateDailyHappyCash"]["payAmount"])
# 우측 데이터 GS25 POP
# if "gspopcards" in dailySum : # GS25 pop 충전
# sheet.cell(row = 3 , column=11 , value=dailySum["gspopcards"])
# if "gspopcardp" in dailySum : # GS25 결제
# sheet.cell(row = 4 , column=11 , value=dailySum["gspopcardp"])
# gs25Etc = 0
# if "gspopcardb2b" in dailySum :
# gs25Etc = gs25Etc + dailySum["gspopcardb2b"]
# if "gswork" in dailySum :
# gs25Etc = gs25Etc + dailySum["gswork"]
# if "dmlife" in dailySum :
# gs25Etc = gs25Etc + dailySum["dmlife"]
# sheet.cell(row = 7 , column=11 , value=gs25Etc)
# # 우측 데이터 GSSM POP
# if "gssmpops" in dailySum : # GSSM 충전
# sheet.cell(row = 8 , column=11 , value=dailySum["gssmpops"])
# if "gssmpopp" in dailySum : # GSSM 결제
# sheet.cell(row = 9 , column=11 , value=dailySum["gssmpopp"])
# # Mobile POP (*App 내 충전)
# if "srpoprbank" in dailySum : # M POP_계좌
# sheet.cell(row = 11 , column=11 , value=dailySum["srpoprbank"])
# if "srpopcard" in dailySum : # M POP_카드
# sheet.cell(row = 12 , column=11 , value=dailySum["srpopcard"])
# if "kcppayco" in dailySum : # 간편결제_신용
# sheet.cell(row = 13 , column=11 , value=dailySum["kcppayco"])
# if "checkpay" in dailySum : # 간편결제_계좌
# sheet.cell(row = 14 , column=11 , value=dailySum["checkpay"])
# if "gsrpoppoint" in dailySum : # M POP_포인트
# sheet.cell(row = 15 , column=11 , value=dailySum["gsrpoppoint"])
# if "hmcharge" in dailySum : # 상품권 충전
# sheet.cell(row = 16 , column=11 , value=dailySum["hmcharge"])
# if "popcharge" in dailySum : # POP 충전권
# sheet.cell(row = 17 , column=11 , value=dailySum["popcharge"])
# if "poppluscharge" in dailySum : # POP 재충전권
# sheet.cell(row = 18 , column=11 , value=dailySum["poppluscharge"])
# if "happymoney_pop" in dailySum : # M_온라인 결제
# sheet.cell(row = 19 , column=11 , value=dailySum["happymoney_pop"])
# # 우측 데이터 기타 충전
# if "nasmedia" in dailySum : # 무료충전소(나스미디어)
# sheet.cell(row = 21 , column=11 , value=dailySum["nasmedia"])
# if "nasmedia" in dailySum : # 무료충전소(나스미디어)
# sheet.cell(row = 22 , column=11 , value=dailySum["nasmedia"])
# if "gsmobilepop" in dailySum : # M POP_이벤트 캐시백
# sheet.cell(row = 23 , column=11 , value=dailySum["gsmobilepop"])
# if "popcenter" in dailySum : # popcenter_고객센터
# sheet.cell(row = 24 , column=11 , value=dailySum["popcenter"])
# if "happymall" in dailySum : # 해피전산(Test)
# sheet.cell(row = 25 , column=11 , value=dailySum["happymall"])
# # 매출 집계 잔액 환불 & poppay
# if searchData["aggregateDailyEtc"] != None :
# for data in searchData["aggregateDailyEtc"]:
# aplonLineId = data["aplonLineId"]
# if aplonLineId == "gspopcardp" :
# sheet.cell(row = 5 , column=11 , value=data["payAmount"]) # GS25 잔액환불
# if aplonLineId == "gssmpopp" :
# sheet.cell(row = 10 , column=11 , value=data["payAmount"]) # GSSM 잔액환불
# if aplonLineId == "poppay" :
# sheet.cell(row = 26 , column=11 , value=data["payAmount"]) # poppay
# sheet 2 요일별 데이터
# 모바일팝 제휴처 일별 정산대상
sheet2 = excel_document.get_sheet_by_name('monthly')
row = 4
if searchData["aggregateMerchantBilling"] != None:
for data in searchData["aggregateMerchantBilling"]:
sheet2.cell(row=row, column=2, value=data["tMemo"])
sheet2.cell(row=row, column=3, value=data["aplDate"])
sheet2.cell(row=row, column=4, value=data["cpId"])
sheet2.cell(row=row, column=5, value=data["svcConnId"])
sheet2.cell(row=row, column=6, value=data["paymentCount"])
sheet2.cell(row=row, column=7, value=data["paymentAmount"])
sheet2.cell(row=row, column=8, value=data["cancelCount"])
sheet2.cell(row=row, column=9, value=data["cancelAmount"])
row += 1
# 체크페이
row = 4
if searchData["aggregateCheckPay"] != None:
for data in searchData["aggregateCheckPay"]:
sheet2.cell(row=row, column=14, value=data["userId"])
sheet2.cell(row=row, column=15, value=data["aplDate"])
sheet2.cell(row=row, column=16, value=data["quantity"])
sheet2.cell(row=row, column=17, value=data["amount"])
sheet2.cell(row=row, column=18, value=data["userGubun"])
sheet2.cell(row=row, column=19, value=data["chargeType"])
row = row + 1
# aggregateMonthGsPopCard
row = 4
if searchData["aggregateMonthGsPopCard"] != None:
for data in searchData["aggregateMonthGsPopCard"]:
sheet2.cell(row=row, column=23, value=data["salesDate"])
sheet2.cell(row=row, column=26, value=data["totCount"])
sheet2.cell(row=row, column=27, value=data["totAmt"])
row = row + 1
# aggregateMonthGsSmPop
row = 37
if searchData["aggregateMonthGsSmPop"] != None:
for data in searchData["aggregateMonthGsSmPop"]:
sheet2.cell(row=row, column=23, value=data["salesDate"])
sheet2.cell(row=row, column=26, value=data["totCount"])
sheet2.cell(row=row, column=27, value=data["totAmt"])
row = row + 1
# aggregateMonthGshb
row = 70
print(searchData.get("aggregateMonthGshb"))
if searchData["aggregateMonthGshb"] != None:
for data in searchData["aggregateMonthGshb"]:
sheet2.cell(row=row, column=23, value=data["salesDate"])
sheet2.cell(row=row, column=26, value=data["totCount"])
sheet2.cell(row=row, column=27, value=data["totAmt"])
row = row + 1
excel_document.save(filename=os.path.join(outputPath, saveFileName))
except Exception as err:
#에러 메시지 추가
putBatchMng({
"batchId": str(batchId),
"reqId": queryData['empId'],
"status": "BAT-0003", # 진행중
"errMsg": str(err)
})
jobStatus = 1
print err
print sys.exc_info()[-1].tb_lineno
finally:
#성공 메시지 추가
if jobStatus == 0:
putBatchMng({
"batchId": str(batchId),
"reqId": queryData['empId'],
"status": "BAT-0002", # 진행중
"errMsg": ""
})
print "종료"