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)
