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): 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): 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)
class PvaChan(object): def __init__(self): super(PvaChan, self).__init__() def trandate(self,df): if int(df['日期'][11:13])>6: return df['日期'][:11] else: y=self.base.getYesterday(day=df['日期'][:11]) return y[:4]+'-'+y[5:7]+'-'+y[8:10] def __call__(self,conns): self.base=Base() self.ms =conns['offline'] self.ora=conns['mes'] with open(self.base.path1+'sqls/pva产能.sql','r') as f: sql=f.read() res=self.ora.doget(sql) res['日期'] = res.apply(lambda r: self.trandate(r), axis=1) res.columns=['lot','device','wo','qty','riqi','jitai','OLDOPERATION'] with open(self.base.path1 + 'sqls/psa产能.sql', 'r') as f: sql = f.read() res3 = self.ora.doget(sql) res3['日期'] = res3.apply(lambda r: self.trandate(r), axis=1) res3.columns = ['lot', 'device', 'wo', 'qty', 'riqi', 'jitai', 'OLDOPERATION'] res2 = [] for i in self.base.datelist('20180101', self.base.gettoday().replace('/','')): i = i.replace('/', '-') for t in ['PVA','TAC-PVA','PSA','換貼保護膜']: res2.append([i, 0,t]) res2 = pd.DataFrame(res2, columns=['riqi', 'qty','OLDOPERATION']) self.ms.dopost("truncate table pvachanneng") self.base.batchwri(res2, 'pvachanneng', self.ms) self.base.batchwri(res,'pvachanneng',self.ms) self.base.batchwri(res3,'pvachanneng',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=PvaChan() # zc(conns) # offline.close() # erp.close() # wms.close() # mes.close()
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): 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()]: 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): base = Base() self.offline = conns['offline'] self.erp = conns['erp'] res = [] # res.columns[] # self.offline.dopost("truncate table cost_yuanfan_test") with open(base.path1 + 'sqls/原反损耗/sql1.sql', 'r') as f: sql1 = f.read() with open(base.path1 + 'sqls/原反损耗/sql2.sql', 'r') as f: self.sql2 = f.read() with open(base.path1 + 'sqls/原反损耗/sql3.sql', 'r') as f: self.sql3 = f.read() for date in [base.getYesterday(), base.gettoday()]: res = self.offline.doget(sql1.replace('yewudate', date)) self.offline.dopost("delete from cost_yuanfan_test where 業務日期='" + date + "'") if not res.empty: res['用量'] = res.apply(lambda r: self.transth(r), axis=1) res['单价'] = res.apply(lambda r: self.transth2(r), axis=1) res['業務日期'] = date base.batchwri(res, 'cost_yuanfan_test', self.offline) del res
import sys, os sys.path.append('/home/openstack/data_offline/data_factory/') from dataprocess.oracleprocess.mes.base import Base base = Base() sql = ''' SELECT NVL(SUM(CASE WHEN OPERATION = 'PSA' THEN QUANTITY END),0) PSA ,NVL(SUM(CASE WHEN OPERATION = '檢查_1' THEN QUANTITY END),0) VIC ,NVL(SUM(CASE WHEN OPERATION = '內包裝' THEN QUANTITY END),0) 内包 from MES.mes_wip_lot where STATUS NOT IN ('Terminated','Finished')''' ora = base.conn('mes') ms = base.conn('offline') ms_test = base.conn('offline_test') res = ora.doget(sql) res['日期'] = base.gettoday() # ms.dopost('truncate table PsaInnerPkg') base.batchwri(res, 'PsaInnerPkg', ms) base.batchwri(res, 'PsaInnerPkg', ms_test) ora.close() ms.close() ms_test.close()
def __call__(self, conns): b = Base() sqldict = { 'PSA产量': ''' SELECT NVL(SUM(CASE WHEN substr(vlm.wo,4,1) IN ('E','D') AND mwh.newquantity < 1000 THEN 0 ELSE mwh.newquantity END),0) 產出數量 FROM mes.view_lotlist_main vlm INNER JOIN mes.mes_wip_hist mwh ON mwh.lot = vlm.lot AND mwh.TRANSACTION = 'CheckOut' AND mwh.oldoperation = 'PSA' WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM mes.mes_wip_hist WHERE lot = vlm.lot AND TRANSACTION = 'CheckOut' AND oldoperation = 'PSA') AND replace(mwh.transactiontime,'/','-') >= 'yesterday'||' 07:00:00' AND replace(mwh.transactiontime,'/','-') < 'today'||' 07:00:00' ''', 'PVA 1330M': ''' SELECT NVL(SUM(CASE WHEN substr(vlm.wo,4,1) IN ('E','D') AND mwh.newquantity < 1000 THEN 0 ELSE mwh.newquantity END),0) 產出數量 FROM mes.view_lotlist_main vlm INNER JOIN mes.mes_wip_hist mwh ON mwh.lot = vlm.lot AND mwh. TRANSACTION = 'CheckOut' AND mwh.oldoperation IN ('PVA', 'TAC-PVA') WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM mes.mes_wip_hist WHERE lot = vlm.lot AND TRANSACTION = 'CheckOut' AND oldoperation IN ('PVA', 'TAC-PVA')) AND vlm.device LIKE '_000%' AND replace(mwh.transactiontime,'/','-') >= 'yesterday'||' 07:00:00' AND replace(mwh.transactiontime,'/','-') < 'today'||' 07:00:00' ''', 'PVA 1490M': ''' SELECT NVL(SUM(CASE WHEN substr(vlm.wo,4,1) IN ('E','D') AND mwh.newquantity < 1000 THEN 0 ELSE mwh.newquantity END),0) 產出數量 FROM mes.view_lotlist_main vlm INNER JOIN mes.mes_wip_hist mwh ON mwh.lot = vlm.lot AND mwh. TRANSACTION = 'CheckOut' AND mwh.oldoperation IN ('PVA', 'TAC-PVA') WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM mes.mes_wip_hist WHERE lot = vlm.lot AND TRANSACTION = 'CheckOut' AND oldoperation IN ('PVA', 'TAC-PVA')) AND vlm.device LIKE '_001%' AND replace(mwh.transactiontime,'/','-') >= 'yesterday'||' 07:00:00' AND replace(mwh.transactiontime,'/','-') < 'today'||' 07:00:00' ''', 'RTC-KS': ''' SELECT SUM(mwh.newquantity) 產出數量 FROM mes.view_lotlist_main vlm INNER JOIN mes.mes_wip_hist mwh ON mwh.lot = vlm.lot AND mwh.oldoperation = 'RTC-QC' WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM mes.mes_wip_hist WHERE lot = vlm.lot AND oldoperation = 'RTC-QC') and vlm.operationseq IN('002','003') AND replace(mwh.transactiontime,'/','-') >= 'yesterday'||' 08:30:00' AND replace(mwh.transactiontime,'/','-') < 'today'||' 08:30:00' ''', 'RTP-XY': ''' SELECT NVL(SUM(mwh.newquantity),0)/2 產出數量 FROM mes.view_lotlist_main vlm INNER JOIN mes.mes_wip_hist mwh ON mwh.lot = vlm.lot AND mwh.oldoperation = 'RTP' WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM mes.mes_wip_hist WHERE lot = vlm.lot AND oldoperation = 'RTP') and vlm.operationseq = '002' AND replace(mwh.transactiontime,'/','-') >= 'yesterday'||' 08:30:00' AND replace(mwh.transactiontime,'/','-') < 'today'||' 08:30:00' ''', 'RTS-CQ': ''' SELECT NVL(SUM(mwh.newquantity),0) 產出數量 FROM mes.view_lotlist_main vlm INNER JOIN mes.mes_wip_hist mwh ON mwh.lot = vlm.lot AND mwh.oldoperation = 'RTS-QC' WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM mes.mes_wip_hist WHERE lot = vlm.lot AND oldoperation = 'RTS-QC') AND vlm.operationseq = '002' AND replace(mwh.transactiontime,'/','-') >= 'yesterday'||' 08:30:00' AND replace(mwh.transactiontime,'/','-') < 'today'||' 08:30:00' ''', 'RTS-KS': ''' SELECT NVL(SUM(mwh.newquantity),0) 產出數量 FROM mes.view_lotlist_main vlm INNER JOIN mes.mes_wip_hist mwh ON mwh.lot = vlm.lot AND mwh.oldoperation = 'RTS-QC' WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM mes.mes_wip_hist WHERE lot = vlm.lot AND oldoperation = 'RTS-QC') AND vlm.operationseq = '003' AND replace(mwh.transactiontime,'/','-') >= 'yesterday'||' 08:30:00' AND replace(mwh.transactiontime,'/','-') < 'today'||' 08:30:00' ''', 'VIC': ''' SELECT NVL(SUM(mwh.newquantity),0) 產出數量 FROM mes.view_lotlist_main vlm INNER JOIN mes.mes_wip_hist mwh ON mwh.lot = vlm.lot AND mwh.TRANSACTION = 'CheckOut' AND mwh.oldoperation LIKE '檢查%' WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM mes.mes_wip_hist WHERE lot = vlm.lot AND TRANSACTION = 'CheckOut' AND oldoperation LIKE '檢查%') and vlm.wo like 'K%' and vlm.operation <> '凹凸靜置站' AND vlm.operation <> 'D規內包裝' AND vlm.operation <> '翹曲靜置' AND replace(mwh.transactiontime,'/','-') >= 'yesterday'||' 08:30:00' AND replace(mwh.transactiontime,'/','-') < 'today'||' 08:30:00' ''', 'WIP-psa': ''' SELECT NVL(SUM(CASE WHEN OPERATION = 'PSA' THEN QUANTITY END),0) PSA from MES.mes_wip_lot where STATUS NOT IN ('Terminated','Finished') ''', 'WIP-vic': ''' SELECT NVL(SUM(CASE WHEN OPERATION = '檢查_1' THEN QUANTITY END),0) VIC from MES.mes_wip_lot where STATUS NOT IN ('Terminated','Finished') ''', 'WIP-内包': ''' SELECT NVL(SUM(CASE WHEN OPERATION = '內包裝' THEN QUANTITY END),0) 内包 from MES.mes_wip_lot where STATUS NOT IN ('Terminated','Finished') ''', '分条': ''' select NVL(sum(qty),0) 產出數量 from (SELECT mwh.device,mwh.newquantity qty,substr(mwh.transactiontime,0,10) dat FROM MES.view_lotlist_main vlm INNER JOIN (select * from MES.mes_wip_hist where replace(transactiontime,'/','-') >= 'yesterday'||' 08:30:00' AND replace(transactiontime,'/','-') < 'today'||' 08:30:00') mwh ON mwh.lot = vlm.lot AND mwh.oldoperation = 'Slitter_Inventory' WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM MES.mes_wip_hist WHERE lot = vlm.lot AND oldoperation = 'Slitter_Inventory') AND substr(vlm.wo,4,1) not in ('E','D') or (substr(vlm.wo,4,1) in ('E','D') and mwh.newquantity >'1000') group by mwh.device,mwh.newquantity,substr(mwh.transactiontime,0,10)) ''', '内包': ''' SELECT NVL(SUM(mwh.newquantity),0) 產出數量 FROM mes.view_lotlist_main vlm INNER JOIN mes.mes_wip_hist mwh ON mwh.lot = vlm.lot AND mwh.TRANSACTION = 'CheckOut' AND mwh.oldoperation LIKE '%內包裝' WHERE SEQUENCE = (SELECT MAX(SEQUENCE) FROM mes.mes_wip_hist WHERE lot = vlm.lot AND TRANSACTION = 'CheckOut' AND mwh.oldoperation LIKE '%內包裝') and vlm.wo like 'K%' AND replace(mwh.transactiontime,'/','-') >= 'yesterday'||' 08:30:00' AND replace(mwh.transactiontime,'/','-') < 'today'||' 08:30:00' ''' } self.ora = conns['mes'] self.ms = conns['offline'] # self.ms.dopost("truncate table mwhvlm1") # for day in [b.getYesterday(),b.gettoday()]: for day in [b.getYesterday(), b.gettoday()]: self.ms.dopost("delete from mwhvlm1 where riqi='" + b.getYesterday(day).replace('/', '-') + "'") for k, v in sqldict.items(): sqlthis = v.replace('yesterday', b.getYesterday(day).replace( '/', '-')).replace('today', day.replace('/', '-')) res = self.ora.doget(sqlthis) res.columns = ['qty'] res['type'] = k res['riqi'] = b.getYesterday(day).replace('/', '-') b.batchwri(res, 'mwhvlm1', self.ms) del res