def __call__(self, conns): os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK' b = Base() self.erp = conns['erp'] self.ms = conns['offline'] now = datetime.datetime.now() # for month in range(6): year = now.year month = (now.month - 1) % 12 if month == 0: month = 12 year -= 1 this_quarter_start = datetime.datetime(year, month, 1) lastm = str(this_quarter_start)[:7] with open(b.path1 + 'sqls/资产负债表.sql', 'r') as f: sql1 = f.read().replace('thismonth', str(lastm)) res1 = self.erp.doget(sql1) res1.columns = [ '货币资金', '应收票据', '应收账款', '预付款项', '应收利息', '其他应收账款', '存货', '原材料', '库存商品_产成品', '库存商品_半成品', '其他流动资产', '可供出售金融资产', '长期股权投资', '固定资产原值', '减_累计折旧', '在建工程', '无形资产', '长期待摊费用', '递延所得税资产', '短期借款', '应付票据', '应付账款', '预收账款', '应付职工薪酬', '应交税费', '其中_应交税金', '应付利息', '其他应付款', '其他流动负债', '长期借款', '递延所得税负债', '实收资本', '资本公积', '专项储备', '盈余公积', '其中_法定公积金', '未分配利润' ] res1['upmonth'] = lastm self.ms.dopost("delete from zichanfz where upmonth='" + lastm + "'") b.batchwri(res1, 'zichanfz', self.ms) del res1, sql1
class LossYuanfanInput(object): def __init__(self): super(LossYuanfanInput, self).__init__() def __call__(self,conns): self.base=Base() self.ms =conns['offline'] self.ora=conns['mes'] with open(self.base.path1+'sqls/原返损耗投入.sql','r') as f: sql=f.read() # self.ms.dopost('truncate table loss_yuanfan_input') # for day in range(1,3): # today = datetime.datetime(2018,7,day) # todaystr = datetime.datetime.strftime(today, '%Y/%m/%d') # sql0 = sql.replace('thisdate', todaystr) # res = self.ora.doget(sql0) # res['業務日期'] = todaystr # self.ms.dopost("delete from loss_yuanfan_input where 業務日期='"+todaystr+"'") # self.base.batchwri(res, 'loss_yuanfan_input', self.ms) yesterday = datetime.datetime.now() - datetime.timedelta(days=1) #yesterday=datetime.datetime(2018,6,5) yesterdaystr = datetime.datetime.strftime(yesterday, '%Y/%m/%d') sql0 = sql.replace('thisdate', yesterdaystr) res = self.ora.doget(sql0) res['業務日期']=yesterdaystr self.ms.dopost("delete from loss_yuanfan_input where 業務日期='" + yesterdaystr + "'") self.base.batchwri(res, 'loss_yuanfan_input', self.ms)
class Qlt(object): def __init__(self): super(Qlt, self).__init__() def __call__(self,conns): os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK' self.base=Base() self.ora =conns['mes'] self.ms = conns['offline'] day = self.base.getYesterday() day = day+' 00:00:00' # today = base.gettoday() # day='2018/01/01 00:00:00' with open(self.base.path1 + 'sqls/品质.sql', 'r') as f: sql = f.read().replace('someday',day) res = self.ora.doget(sql) res.columns = ['LOT', 'OPERATION', 'VISUALRESULT','S','dat'] # res['dat']=res.apply(lambda r:self.trandate(r),axis=1) self.ms.dopost("delete from quality where dat>str_to_date('" + day.replace('/','-') + "','%Y-%m-%d %H:%i:%s')") self.base.batchwri(res, 'quality', self.ms) # base = Base() # erp = base.conn('erp') # offline = base.conn('offline') # wms = base.conn('wms') # mes = base.conn('mes') # conns = {'offline': offline, 'erp': erp, 'wms': wms, 'mes': mes} # hz=Qlt() # hz(conns) # offline.close() # erp.close() # wms.close() # mes.close()
def __call__(self,conns): os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK' b = Base() self.conn = cx_Oracle.connect( str('BDATA') + '/' + str('BDATA') + '@' + str('10.232.1.101') + ':' + str('1521') + '/' + str('KSERP')) self.ms = conns['offline'] # self.ms.dopost("drop table should_pay_mon") cursor = self.conn.cursor() day=b.getYesterday(b.gettoday()[:8]+'01') sql = open(b.path1 + 'sqls/应付账款.sql', 'r', encoding='utf8').read().replace('thisday',day) try: self.Func(self.conn) except Exception: self.Func(self.conn) cursor.execute(sql) res = pd.DataFrame(cursor.fetchall()) res.columns = ['公司名称', '币种', '逾期情况', '逾期款'] res['riqi'] =b.gettoday()[:7] self.ms.dopost("delete from should_pay_mon where riqi='"+b.gettoday()[:7]+"'") b.batchwri(res, 'should_pay_mon', self.ms) cursor.close() self.conn.close() # base = Base() # # erp = base.conn('erp') # offline = base.conn('offline') # # wms = base.conn('wms') # # mes = base.conn('mes') # conns = {'offline': offline} # zc = ShouldPay() # zc(conns) # offline.close() # # erp.close() # # wms.close() # # mes.close()()
def __call__(self, conns): os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK' b = Base() self.erp = conns['erp'] self.ms = conns['offline'] now = datetime.datetime.now() month = (now.month - 1) % 12 # for month in range(7): year = now.year if month == 0: month = 12 year -= 1 this_quarter_start = datetime.datetime(year, month, 1) lastm = str(this_quarter_start)[:7] with open(b.path1 + 'sqls/利润表.sql', 'r') as f: sql1 = f.read().replace('thismonth', str(lastm)) res1 = self.erp.doget(sql1) res1.columns = [ '营业收入', '减_营业成本', '税金及附加', '销售费用', '管理费用', '财务费用', '资产减值损失', '投资收益', '加_营业外收入', '减_营业外支出', '减_所得税费用' ] res1['upmonth'] = lastm self.ms.dopost("delete from lirun_new where upmonth='" + lastm + "'") b.batchwri(res1, 'lirun_new', self.ms) del res1, sql1
def __call__(self, conns): sql = "select * from APPS.CST_ITEM_COST_TYPE_V" b = Base() self.ora = conns['erp'] self.ms = conns['offline'] res = self.ora.doget(sql) self.ms.dopost('truncate table materialprice') b.batchwri(res, 'materialprice', self.ms)
def __call__(self, conns): os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK' base = Base() self.ms = conns['offline'] self.ora = conns['erp'] now = datetime.datetime.now() # for month in range(7): year = now.year month = (now.month - 1) % 12 if month == 0: month = 12 year -= 1 this_quarter_start = datetime.datetime(year, month, 1) lastm = str(this_quarter_start)[:7] tables = {'manage_fee1': open(base.path1 + 'sqls/费用/管理费用.sql', 'r').read().replace('thismonth', lastm), \ 'sale_fee1': open(base.path1 + 'sqls/费用/销售费用.sql', 'r').read().replace('thismonth', lastm), \ 'made_fee1': open(base.path1 + 'sqls/费用/制造费用.sql', 'r').read().replace('thismonth', lastm), \ 'finance_fee1': open(base.path1 + 'sqls/费用/财务费用.sql', 'r').read().replace('thismonth', lastm)} for k, v in tables.items(): print('正在进行' + k) res = self.ora.doget(v) if k == 'sale_fee1': res.columns = [ '日期', '工资', '福利', '保险费_人事', '住房公积金', '工会经费', '职工教育经费', '股份支付', '职工薪酬其他', '文具用品', '旅费', '运费', '邮电费', '修缮费', '广告费', '动力费', '交际费', '折旧', '各项摊提', '权力费', '职工福利', '研究发展费', '训练费', '保全费', '出口费用', '加工费', '包装费', '间接材料', '检测费', '保险费资产', '什项购置', '书报杂志', '劳务费', '进口费用', '会议费', '租金支出', '残保金', '外包人力派遣', '交通费', '其他费用' ] if k == 'manage_fee1': res.columns = [ '日期', '工资', '福利', '保险费', '住房公积金', '工会经费', '职工教育经费', '股份支付', '职工薪酬其他', '文具用品', '旅费', '运费', '邮电费', '修缮费', '广告费', '动力费', '交际费', '折旧', '各项摊提', '权力费', '职工福利', '研究发展费', '训练费', '保全费', '加工费', '包装费', '间接材料', '检测费', '保险费资产', '什项购置', '书报杂志', '劳务费', '进口费用', '会议费', '租金支出', '残保金', '外包人力派遣', '交通费', '其他费用' ] if k == 'finance_fee1': res.columns = [ '日期', '利息收入', '集团内部利息收入', '贷款利息支出', '集团内部利息支出', '金融机构手续费', '汇兑损益', '财务顾问费', '担保费', '贴现利息支出', '融资租赁利支出', '利息支出资本化' ] if k == 'made_fee1': res.columns = [ '日期', '薪资支出_直接', '社会保险费_直接', '伙食费_直接', '外包人力派遣_直接', '薪资支出', '保险费_人事', '文具用品', '旅费', '运费', '邮电费', '修缮费', '广告费', '动力费', '交际费', '折旧', '各项摊提', '权利金', '伙食费', '职工福利', '研究发展费', '训练费', '保全费', '出口费用', '加工费', '包装费', '间接材料', '检测费', '保险费资产', '什项购置', '书报杂志', '劳务费', '进口费用', '会议费', '租金支出', '残保金', '外包人力派遣', '交通费', '其他费用' ] # res['日期'] = res.apply(lambda r: self.trandate(r), axis=1) self.ms.dopost("delete from " + k + " where 日期='" + lastm + "'") print(res) base.batchwri(res, k, self.ms)
def __call__(self, conns): base = Base() self.ora = conns['mes'] sql = open(base.path1 + 'sqls/良品.sql', 'r').read() self.ms = conns['offline'] res = self.ora.doget(sql) res.columns = ['liangpin_qty', 'touru_qty', 'class', 'WEEK'] self.ms.dopost('truncate table liangpin') base.batchwri(res, 'liangpin', self.ms)
def __call__(self, conns): b = Base() self.ora = conns['wms'] sql = open(b.path1 + 'sqls/直通率/Shipments_now.sql', 'r').read() self.ms = conns['offline'] res = self.ora.doget(sql) res.columns = ['amt', 'num', 'time'] self.ms.dopost('truncate table ShipmentsNow') b.batchwri(res, 'ShipmentsNow', self.ms)
def __call__(self, conns): base=Base() erp=conns['erp'] offline=conns['offline'] with open(base.path1+'sqls/销售面积.sql','r') as f: sql=f.read() res=erp.doget(sql) offline.dopost("truncate table sold_area") base.batchwri(res,'sold_area',offline)
def __call__(self,conns): b=Base() self.ora =conns['erp'] sql = open(b.path1+'sqls/直通率/Shipments_statistics.sql', 'r', encoding='utf-8').read() self.ms =conns['offline'] res = self.ora.doget(sql) res.columns = ['ITEM_CODE', 'ONHAND_QUANTITY', 'UNIT_LENGTH', 'UNIT_WIDTH'] self.ms.dopost('truncate table ShipmentsStatistics') b.batchwri(res, 'ShipmentsStatistics',self.ms)
def __call__(self, conns): base = Base() erp = conns['erp'] offline = conns['offline'] with open(base.path1 + 'sqls/应付账款账龄.sql', 'r') as f: sql = f.read() res = erp.doget(sql) offline.dopost("truncate table should_pay_age") base.batchwri(res, 'should_pay_age', offline)
def __call__(self, conns): base = Base() self.ora = conns['mes'] self.ms = conns['offline'] with open(base.path1 + 'sqls/损耗.sql', 'r') as f: sql = f.read() res = self.ora.doget(sql) res['wotype'] = res.apply(lambda r: self.getttype(r), axis=1) self.ms.dopost('truncate table sunhao') base.batchwri(res, 'sunhao', self.ms)
def __call__(self, conns): sql = '''SELECT * FROM MES.CMMT_CCD_INFO''' b = Base() self.ora = conns['mes'] self.ms = conns['offline'] res = self.ora.doget(sql) self.ms.dopost('truncate table CMMT_CCD_INFO') b.batchwri(res, 'CMMT_CCD_INFO', self.ms)
def __call__(self, conns): b = Base() self.ora = conns['mes'] self.ms = conns['offline'] sql = open(b.path1 + 'sqls/vlm_mwh.sql', 'r').read() res = self.ora.doget(sql) res.columns = [ 'lot', 'device', 'wo', 'newquantity', 'transactiontime', 'resourcename', 'oldoperation' ] self.ms.dopost('truncate table vlm_mwh') b.batchwri(res, 'vlm_mwh', self.ms)
def __call__(self, conns): b = Base() self.ora = conns['mes'] self.ms = conns['offline'] with open(b.path1 + 'sqls/良率/良率统计分析.sql', 'r') as f: sql = f.read() res = self.ora.doget(sql) res.columns=['pva_lot','psa_trantime','psa_sub','pva_trantime','slt_lot','hd_lot','sub_lot',\ 'cust_lot','checkout_time','device','hd_wo',\ 'qd_wo','arr_qty','Agui','reason','descr','quantity','bancheng_lot'] self.ms.dopost('truncate table lianglv') b.batchwri(res, 'lianglv', self.ms)
def __call__(self, conns): b = Base() self.ora = conns['mes'] sql = open(b.path1 + 'sqls/良率/良率日报.sql', 'r').read() self.ms = conns['offline'] res = self.ora.doget(sql) res.columns = [ 'qd_wo', 'hd_wo', 'materialno', 'qd_lot', 'hd_lot', 'pro_state', 'thisdate', 'facno', 'class', 'thistype', 'touru_qty', 'liangpin_qty', 'dianque_qty', 'dianque_item', 'sort', 'speed' ] self.ms.dopost('truncate table lianglvribao') b.batchwri(res, 'lianglvribao', self.ms)
def __call__(self, conns): base = Base() mes = conns['mes'] offline = conns['offline'] with open(base.path1 + 'sqls/原返损耗产出.sql', 'r') as f: sql = f.read() # offline.dopost("truncate table loss_yuanfan_output") for date in [base.gettoday(), base.getYesterday()]: res = mes.doget(sql.replace('thistime', date)) res['業務日期'] = date offline.dopost("delete from loss_yuanfan_output where 業務日期='" + date + "'") base.batchwri(res, 'loss_yuanfan_output', offline)
def __call__(self,conns): sql = ''' SELECT "DEFECTTYPE", "DEVICE" , "LOT", "LOTQUANTITY", "OPERATION" , "PVACHECKOUTTIME", "PVARESNAME", "QCRESULTCUST", "QCRESULTSYS" , "QUANTITY", "REMARK", "RESOURCENAME" , replace("UPDATETIME",'/','-') AS dat, "VISUALRESULT", "WO" FROM MES.qcview''' b=Base() self.ora =conns['mes'] self.ms =conns['offline'] res = self.ora.doget(sql) self.ms.dopost('truncate table qcview') b.batchwri(res, 'qcview',self.ms)
def __call__(self, conns): b = Base() yday = b.getYesterday() self.ora = conns['erp'] sql = open(b.path1 + 'sqls/itemcost.sql', 'r').read().replace('yesterday', yday) self.ms = b.conn('offline') res = self.ora.doget(sql) res.columns = [ 'ROW_ID', 'ITEM_NUMBER', 'ITEM_COST', 'LAST_UPDATE_DATE' ] self.ms.dopost("delete from item_cost where LAST_UPDATE_DATE>'" + yday + ' 00:00:00' + "'") b.batchwri(res, 'item_cost', self.ms)
def __call__(self, conns): base = Base() mes = conns['mes'] offline = conns['offline'] with open(base.path1 + 'sqls/缺点top3.sql', 'r') as f: sql0 = f.read() for day in base.datelist(base.getYesterday(base.gettoday()), base.gettoday()): date = day.replace('/', '-') sql1 = sql0.replace('${dateEditor1}', date) res = mes.doget(sql1) res['日期'] = date offline.dopost("delete from top3_shortage where 日期='" + date + "'") base.batchwri(res, 'top3_shortage_test', offline)
def __call__(self, conns): b = Base() self.ora = conns['wms'] self.ms = conns['offline'] sql = ''' select SUM(QTY)/1000 QTY,ITEM_CODE FROM WMS.A_PALLET_ITEM WHERE SUBINVENTORY_CODE IN ('F1FGA','F1RDA','F1CAA') AND QC_STATUS NOT IN ('B','D') GROUP BY ITEM_CODE''' res = self.ora.doget(sql) day = base.gettoday() res['日期'] = day self.ms.dopost("delete from swgkc where 日期='" + day + "'") b.batchwri(res, 'swgkc', self.ms)
def __call__(self, conns): os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK' b = Base() self.erp = conns['erp'] self.ms = conns['offline'] with open(b.path1 + 'sqls/成本计算.sql', 'r') as f: sql1 = f.read() res1 = self.erp.doget(sql1) res1.columns = [ 'cpdm', 'wlbm', 'UOM', 'bibie', 'MSIZE', 'FROZEN_COST', 'CURRENT_COST', 'COMPONENT_QUANTITY' ] self.ms.dopost("truncate table chengbenjisuan") b.batchwri(res1, 'chengbenjisuan', self.ms) del res1, sql1
def __call__(self, conns): base = Base() self.ora = conns['mes'] self.ms = conns['offline'] # self.ms.dopost('truncate table pkg_cap_calcu') # for day in base.datelist('20180101','20180611'): day = base.getYesterday() today = base.gettoday() with open(base.path1 + 'sqls/包装产能计算.sql', 'r') as f: sql = f.read().replace('today', today).replace('yesterday', day) res = self.ora.doget(sql) res.columns = ['BAN', 'LEI', 'NUM'] res['riqi'] = today self.ms.dopost("delete from pkg_cap_calcu where riqi=str_to_date('" + today + "','%Y/%m/%d')") base.batchwri(res, 'pkg_cap_calcu', self.ms)
def __call__(self, conns): base = Base() mes = conns['mes'] offline = conns['offline'] with open(base.path1 + 'sqls/良率/整体良率.sql', 'r') as f: sql = f.read() # offline.dopost("delete from OverallGoodRatio00 where date_format(CHECKOUTTIME,'%Y-%m-%d %H:%i:%s')>date_format('" + "2018-03-03 08:00:00" + "','%Y-%m-%d %H:%i:%s') ") # for day in base.datelist('20180101', '20180704')[::-1]: # todaybeg = day + ' 08:00:00' # todayend = datetime.datetime.strptime(todaybeg, '%Y/%m/%d %H:%M:%S') + datetime.timedelta(1) # todayend = todayend.strftime('%Y/%m/%d %H:%M:%S') # thissql = sql.replace('thisbegtime', todaybeg).replace('thisendtime', todayend) # res = mes.doget(thissql) # if not res.empty: # lotmu = res['LOT'].apply(self.findmu) # lotmures = [] # for idx in lotmu.index: # if lotmu[idx] in lotmu.values[:idx] or lotmu[idx] in lotmu.values[idx+1:]: # lotmures.append(lotmu[idx]) # else: # lotmures.append(res.loc[idx, 'LOT']) # res['LOT_MU'] = lotmures # res['week'] = res.apply(lambda r: self.trandate(r), axis=1) # base.batchwri(res, 'OverallGoodRatio00', offline) todaybeg = datetime.datetime.now().strftime('%Y/%m/%d') + ' 08:00:00' todayend = datetime.datetime.strptime( todaybeg, '%Y/%m/%d %H:%M:%S') + datetime.timedelta(1) todayend = todayend.strftime('%Y/%m/%d %H:%M:%S') thissql = sql.replace('thisbegtime', todaybeg).replace('thisendtime', todayend) res = mes.doget(thissql) if not res.empty: lotmu = res['LOT'].apply(self.findmu) offline.dopost( "delete from OverallGoodRatio00 where date_format(CHECKOUTTIME,'%Y-%m-%d %H:%i:%s')>date_format('" + todaybeg + "','%Y-%m-%d %H:%i:%s') ") lotmures = [] for idx in lotmu.index: if lotmu[idx] in lotmu.values[:idx] or lotmu[ idx] in lotmu.values[idx + 1:]: lotmures.append(lotmu[idx]) else: lotmures.append(res.loc[idx, 'LOT']) res['LOT_MU'] = lotmures res['week'] = res.apply(lambda r: self.trandate(r), axis=1) base.batchwri(res, 'OverallGoodRatio00', offline)
def __call__(self, conns): sql = '''select DESCR,sum(a.dianque) qty from ( SELECT DISTINCT DESCR,dianque,CHECKOUTTIME FROM OverallGoodRatio WHERE STR_TO_DATE(REPLACE(CHECKOUTTIME ,'/','-'),'%Y-%m-%d %H:%i:%s') BETWEEN STR_TO_DATE(CURDATE(),'%Y-%m-%d 07:00:00') AND STR_TO_DATE(date_add(CURDATE(), interval 1 day),'%Y-%m-%d 07:00:00') ) a GROUP BY DESCR''' b = Base() self.ms = conns['offline'] res = self.ms.doget(sql).dropna() self.sumnow = 0.0 self.total = sum([float(x[1]) for x in res.values]) self.ms.dopost('truncate table dianque_sumrate') if self.total != 0: res['rate'] = res.apply(lambda r: self.calrate(r), axis=1) #print(res) b.batchwri(res, 'dianque_sumrate', self.ms)
def __call__(self, conns): base = Base() offline = conns['offline'] # offline.dopost("drop table CostSum") for dir in ['量产','综合','量产TD']: sqldcit={'PVA':open(base.path1+'sqls/损耗分析晨会/'+dir+'/pva.sql','r').read(),'PET':open(base.path1+'sqls/损耗分析晨会/'+dir+'/pet.sql','r').read(),\ '保护膜':open(base.path1+'sqls/损耗分析晨会/'+dir+'/protectshell.sql','r').read(),'上TAC':open(base.path1+'sqls/损耗分析晨会/'+dir+'/tac_on.sql','r').read(),\ '下TAC':open(base.path1+'sqls/损耗分析晨会/'+dir+'/tac_under.sql','r').read()} for day in base.datelist('20180501','20180531')[::-1]: fin = [] offline.dopost("delete from CostSum where rq='"+day+"' and item='"+dir+"'") for k,v in sqldcit.items(): oneday=offline.doget("select * from ("+v+") s where rq='"+day.replace('/','-')+"'") if oneday.empty: sunhao_money,touru_money=0,0 else: sunhao_money=oneday['moneysh'][0] touru_money=oneday['moneytr'][0] if sunhao_money==None or sunhao_money=='null': sunhao_money=0 if touru_money==None or touru_money=='null': touru_money=0 mon=offline.doget("select sum(moneysh) sh,sum(moneytr) tr from ("+v+") s where rq>='"+day[:8].replace('/','-')+"01"+"' and rq<='"+day.replace('/','-')+"'") sh_sum=mon['sh'][0] tr_sum=mon['tr'][0] fin.append([day,sunhao_money,touru_money,sh_sum,tr_sum,k]) res=pd.DataFrame(fin,columns=['rq','moneysh','moneytr', 'moneysh_mon', 'moneytr_mon','type']) res['item']=dir # offline.dopost("truncate table CostSum") # print(res) base.batchwri(res, 'CostSum', offline) # base = Base() # erp = base.conn('erp') # offline = base.conn('offline') # wms = base.conn('wms') # mes = base.conn('mes') # conns = {'offline': offline, 'erp': erp, 'wms': wms, 'mes': mes} # hz=CostSum() # hz(conns) # offline.close() # erp.close() # wms.close() # mes.close()
def __call__(self,btime,etime,conns): b=Base() with open(b.path1+'sqls/翘角信息查询.sql','r') as f1: sql1 =f1.read().replace('btime',btime).replace('etime',etime) with open(b.path1+'sqls/翘角信息查询2.sql', 'r') as f2: self.sql2 = f2.read() self.ora=conns['mes'] self.ms=conns['offline'] data=self.ora.doget(sql1) print(data) res=[] for one in data.values: data2=self.dolot(list(one)[4]) for v in data2.values: fin=list(one)+list(v)[:-1] res.append(fin) res_pd=pd.DataFrame(res,columns=["pva_outtime","psa_outtime","rtx_outtime","checktime","psa_lot",\ "slt_lot","rtx_lot","data1","PARAMETER","SAMPLESEQ","OPERATION"]) b.batchwri(res_pd,"all_qiaojiao",self.ms)
def __call__(self, conns): os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK' b = Base() self.wms = conns['wms'] self.ms = conns['offline'] self.conn = cx_Oracle.connect( str('BDATA') + '/' + str('BDATA') + '@' + str('10.232.1.101') + ':' + str('1521') + '/' + str('KSERP')) # self.ora =conns['erp'] try: self.Func(self.conn) except Exception: print('erp连接成功!') finally: self.Func(self.conn) #for day in [b.getYesterday(),b.gettoday()]: for day in b.datelist('20180629', '20180704'): day = day.replace('/', '-') print(day) self.cursor = self.conn.cursor() # self.ms.dopost("truncate table CHTJZJ") # day=b.getYesterday().replace('/','-') with open(b.path1 + 'sqls/出货统计含折让.sql', 'r') as f: sql = f.read().replace('thisday', day) self.cursor.execute(sql) res = pd.DataFrame(self.cursor.fetchall()) if not res.empty: res.columns = [ 'SALETYPE', 'Size', 'SHIPMENTDATE', 'TRIPNO', 'SOURCEID', 'LINEID', 'SHIPQUANTITY', 'DELIVERYNO', 'SHIPAERA', 'ORDEREDITEM', 'NONTAXAMOUNT', 'TAXAMOUNT', 'LINE_TYPE', 'ORDER_NUMBER', 'UNITSELLPRICE', 'SUBINVENTORY', 'DESCRIPTION', 'CUSTOMERNAME', 'FRUD', 'UNITAERA', 'TAXPRICE' ] res['class'] = res.apply(lambda r: self.caltype(r), axis=1) b.batchwri(res, 'CHTJZJ', self.ms) self.cursor.close() self.conn.close()
def __call__(self, conns): b = Base() self.ora = conns['erp'] self.ms = conns['offline'] sql = open(b.path1 + 'sqls/应收账款.sql', 'r').read() res = self.ora.doget(sql) self.ms.dopost('truncate table should_get_mon') b.batchwri(res, 'should_get_mon', self.ms) # base = Base() # erp = base.conn('erp') # offline = base.conn('offline') # wms = base.conn('wms') # mes = base.conn('mes') # conns = {'offline': offline, 'erp': erp, 'wms': wms, 'mes': mes} # zc=ShouldGet() # zc(conns) # offline.close() # erp.close() # wms.close() # mes.close()