def main(): db = cx_Oracle.connect('%s/%s@%s' % (os.environ['DBUSER'], os.environ['DBPWD'], os.environ['TNSNAME']),encoding='gb18030') stlm_month = sys.argv[1] print('hostDate %s expRptPayTxnCost begin' % stlm_month) filePath = '%s/%s/' % (os.environ['RPT7HOME'], stlm_month) filename = filePath + 'Sand_PayTxn_%s.xlsx' % (stlm_month) sql = "select TXN_NUM, REAL_TRANS_AMT, DEST_CHNL_ID from TBL_STLM_TXN_BILL_DTL where stlm_date like '%s%%' and " \ "CHNL_ID = 'A002' order by DEST_CHNL_ID" % stlm_month print(sql) wb = Workbook() ws = wb.active newPayTxnCostFileHead(ws) cursor = db.cursor() cursor.execute(sql) chnlId = '' for ltData in cursor: if chnlId == '': chnlId = ltData[2] count = 0 allamt = 0 allcost = 0 elif chnlId != ltData[2]: #登记文件 newPayTxnCostFileBody(ws, stlm_month, chnlId, count, allamt, allcost) chnlId = ltData[2] count = 0 allamt = 0 allcost = 0 if fabs(ltData[1]) <= 1000: cost = 0.05 elif fabs(ltData[1]) <= 50000: cost = fabs(ltData[1]) * 0.005 / 100 else: cost = 3.5 if ltData[0] == '1801': count = count + 1 allamt = allamt + fabs(ltData[1]) allcost = allcost + cost else: count = count - 1 allamt = allamt - fabs(ltData[1]) allcost = allcost - cost if chnlId != '': newPayTxnCostFileBody(ws, stlm_month, chnlId, count, allamt, allcost) cursor.close() wb.save(filename) wb.close() print('hostDate %s expRptPayTxnCost end' % stlm_month)
def main(): # 数据库连接配置 dbbat = cx_Oracle.connect('%s/%s@%s' % (os.environ['DBUSER'], os.environ['DBPWD'], os.environ['TNSNAME']),encoding='gb18030') dbacc = cx_Oracle.connect('%s/%s@%s' % (os.environ['ACCDBUSER'], os.environ['ACCDBPWD'], os.environ['TNSNAME']), encoding='gb18030') # 获取清算日 if len(sys.argv) == 1: cursor = dbbat.cursor() sql = "select BF_STLM_DATE from TBL_BAT_CUT_CTL" cursor.execute(sql) x = cursor.fetchone() stlm_date = x[0] cursor.close() else: stlm_date = sys.argv[1] print('hostDate %s genRptAcqBalance begin' % stlm_date) filePath = '%s/%s/' % (os.environ['RPT7HOME'], stlm_date) #查找机构 sql = "select trim(INS_ID_CD) from TBL_INS_INF where INS_TP ='01'" cursor = dbbat.cursor() cursor.execute(sql) for ltData in cursor: if ltData[0] is not None: #查看信息 insIdCd = ltData[0] mchtBal = MchtBalance(insIdCd) mchtBal.getAcctInfo(dbbat, dbacc, stlm_date) agentBal = AgentBalance(insIdCd, dbbat, dbacc, stlm_date) agentBal.getAcctInfo() filename = filePath + 'AcqBalanceInf_%s_%s.xlsx' % (insIdCd,stlm_date) wb = Workbook() ws = wb.active genRptFunc(stlm_date, dbbat, ws, mchtBal, agentBal) wb.save(filename) wb.close() cursor.close() dbbat.commit()
def test_close_write(wo): from openpyxl.workbook import Workbook wb = Workbook(write_only=wo) wb.close()
def main(): db = cx_Oracle.connect('%s/%s@%s' % (os.environ['DBUSER'], os.environ['DBPWD'], os.environ['TNSNAME']), encoding='gb18030') dbacc = cx_Oracle.connect('%s/%s@%s' % (os.environ['ACCDBUSER'], os.environ['ACCDBPWD'], os.environ['TNSNAME']), encoding='gb18030') # 获取清算日 if len(sys.argv) == 1: cursor = db.cursor() sql = "select BF_STLM_DATE from TBL_BAT_CUT_CTL" cursor.execute(sql) x = cursor.fetchone() stlm_date = x[0] cursor.close() else: stlm_date = sys.argv[1] print('hostDate %s genStlmPvsnRpt begin' % stlm_date) filePath = '%s/%s/' % (os.environ['RPT7HOME'], stlm_date) filename = filePath + 'StlmPvsnRpt_%s.xlsx' % stlm_date stlmPvsnAcct = stlmPvsnAcctInfo(db, stlm_date) wb = Workbook() ws = wb.active rptxls = rptFile(ws) rptxls.setTitle('明细报表') rptxls.head() rptxls.recordInitAmt(stlmPvsnAcct) #当天交易 txnInf = txnInfo(db, stlm_date) rptxls.recordTodayTxn(stlmPvsnAcct, txnInf.mchtStlmAmt, txnInf.companyIncome, txnInf.insIncome) #交易-异常,未识别商户 rptxls.recordUnknownMchtTxn(stlmPvsnAcct, txnInf.curFundAmt) #交易 - 异常核销,识别商户 rptxls.recordAbnormalWriteOff(stlmPvsnAcct, txnInf.lastMchtStlmAmt, txnInf.lastCompanyIncome, txnInf.lastInsIncome) chnlPayAmt = chnlPayAmtInfo(db, stlm_date) #出金 rptxls.recordMchtStlmAmtOutS0(stlmPvsnAcct, txnInf.mchtPayAmtS0, chnlPayAmt.othLoanS0, chnlPayAmt.sandLoanS0) # 发卡退单,传入值为负 rptxls.recordChargeBack(stlmPvsnAcct, 0) #发卡退单核销清算款 rptxls.recordChargeBackOff(stlmPvsnAcct, 0) #交易-银联代理清算未清(不明,暂时空填) rptxls.recordChnlUnstlm() #商户打款(手工退货保证金或发卡退单资金) rptxls.recordMchtDeposit(stlmPvsnAcct, 0) #商户打款(手工退货保证金或发卡退单资金)挂账 rptxls.recordMchtDepositHanging() #商户打款(手工退货保证金或发卡退单资金)挂账确认 rptxls.recordMDepConfirm() #商户打款退回(手工退货核销) rptxls.recordMchtDepositReturn() #风控发起冻结 lockInf = lockInfo(dbacc, stlm_date) rptxls.recordLockTxn(stlmPvsnAcct, lockInf.lockAmt) #风控发起解冻 rptxls.recordUnlockTxn(stlmPvsnAcct, lockInf.unlockAmt) #垫资发起 rptxls.recordOthLoan() #垫资追回 rptxls.recordOthLoanOff() #入金 chnlFile = chnlAmtInfo(db, stlm_date) rptxls.recordInAmt(stlmPvsnAcct, chnlFile.intxnAmt) #商户清算款出金,T1出款金额 rptxls.recordMchtStlmAmtOutT1(stlmPvsnAcct, txnInf.mchtPayAmtT1, chnlPayAmt.othLoanT1, chnlPayAmt.sandLoanT1) #我司收入出 rptxls.recordCompanyIncomePayOut(stlmPvsnAcct, txnInf.companyIncomeOff) #代理商收入出 rptxls.recordInsIncomePayOut(stlmPvsnAcct, txnInf.agentPayAmt, chnlPayAmt.agentIncomePay) #客结退汇-对公代付的代付退单 rptxls.recordPayReturnPublic() #银联代付退汇 rptxls.recordPayReturnPrivate() #资金渠道扣代付 rptxls.recordChnlPayAmt(stlmPvsnAcct, chnlPayAmt.othAllLoan) #期末 rptxls.recordFinalAmt(stlmPvsnAcct) col = ws.column_dimensions['B'] col.width = 30.0 rptxls2 = rptFile(wb.create_sheet()) rptxls2.setTitle('列表校验') rptxls2.recordCheckCol(stlmPvsnAcct) wb.save(filename) wb.close() stlmPvsnAcct.insertFinalInfo() print('hostDate %s genStlmPvsnRpt end' % stlm_date)