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()()
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): 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): 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): 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
def __call__(self, conns): os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK' base = Base() self.ora = conns['mes'] self.ms = conns['offline'] day = base.getYesterday() day = day.replace('/', '-') + ' 00:00:00' # today = base.gettoday() with open(base.path1 + 'sqls/PSA喷墨率.sql', 'r') as f: sql = f.read().replace('someday', day) res = self.ora.doget(sql) res.columns = ['日期', 'DATA05', 'LOT'] self.ms.dopost("delete from psa_penmo where 日期>str_to_date('" + day + "','%Y-%m-%d %H:%i:%s')") base.batchwri(res, 'psa_penmo', self.ms) with open(base.path1 + 'sqls/检反喷墨率.sql', 'r') as f: sql = f.read().replace('someday', day) res = self.ora.doget(sql) res.columns = ['日期', 'DATA05', 'LOT'] self.ms.dopost("delete from jianfan_penmo where 日期>str_to_date('" + day + "','%Y-%m-%d %H:%i:%s')") base.batchwri(res, 'jianfan_penmo', self.ms)
def __call__(self, conns): b = Base() sql = open(b.path1 + 'sqls/月损耗金额百分比.sql', 'r').read() self.ms = conns['offline'] # day='2018/05/20' day = str(b.getYesterday()) data = self.ms.doget(sql) data_up_day = data[(data['rq'].str.contains(day[:7])) & (data['rq'] <= day)] pva = ['(上TAC)表面处理膜一般TAC', '补偿膜(下TAC)', '保护膜', 'PVA'] psa = ['离型膜'] sunhao=65*sum([float(x) for x in data_up_day[data_up_day['ml'].isin(pva)]['rsh'].dropna().values])+\ 2.6*sum([float(x) for x in data_up_day[data_up_day['ml'].isin(psa)]['rsh'].dropna().values]) all=65*sum([float(x) for x in data_up_day[data_up_day['ml'].isin(pva)]['rtr'].dropna().values])+\ 2.6*sum([float(x) for x in data_up_day[data_up_day['ml'].isin(psa)]['rtr'].dropna().values]) if all == 0: rate = 0 else: rate = sunhao / all res = pd.DataFrame([[day, rate]], columns=['thisdate', 'rate']) self.ms.dopost( "delete from month_cost_rate where str_to_date(thisdate,'%Y/%m/%d')=str_to_date('" + day + "','%Y/%m/%d')") b.batchwri(res, 'month_cost_rate', self.ms)
def __call__(self, conns): sql_chanchu = ''' select sum(outqty) as rcc from (select *,DATE_FORMAT(date_sub(UPDATETIME,interval 7 hour),'%Y-%m-%d') as rq from offline.sunhao where DATE_FORMAT(UPDATETIME, '%Y-%m-%d %H:%i:%s') between DATE_FORMAT('${startime}', '%Y-%m-%d 07:00:00') and DATE_FORMAT('${endtime}','%Y-%m-%d 07:00:00') and MATERIALNO LIKE '_thiscode%' AND WO NOT LIKE 'KP1%' AND OPERATION IN ('PSA','TAC-PVA','PVA','TAC','AGING') AND EQUIPMENT IS NOT NULL AND WOTYPE='量产' AND (WO like '___M%' OR WO like '___P%' OR WO like '___T%' OR WO like '___E%') ) s''' sql_touru = '''select sum(MLOTCONSUMEQTY) as rtr from (select *,DATE_FORMAT(date_sub(UPDATETIME,interval 7 hour),'%Y-%m-%d') as rq from offline.sunhao where DATE_FORMAT(UPDATETIME, '%Y-%m-%d %H:%i:%s') between DATE_FORMAT('${startime}', '%Y-%m-%d 07:00:00') and DATE_FORMAT('${endtime}','%Y-%m-%d 07:00:00') AND OPERATION IN ('PSA','TAC-PVA','PVA','TAC','AGING') AND MATERIALNO LIKE '_thiscode%' AND WOTYPE='量产' AND (WO like '___M%' OR WO like '___P%' OR WO like '___T%' or WO like '___E%') ) s''' sql_yuanfan_chanchu = '''select SUM(數量) as qty from loss_yuanfan_output where DATE_FORMAT(更新時間, '%Y-%m-%d %H:%i:%s') between DATE_FORMAT('${startime}', '%Y-%m-%d 07:00:00') and DATE_FORMAT('${endtime}','%Y-%m-%d 07:00:00') AND (工單 like '___M%' OR 工單 like '___P%' OR 工單 like '___T%')''' sql_yuanfan_touru = ''' select SUM(數量) as qty from loss_yuanfan_input where DATE_FORMAT(查询日期, '%Y-%m-%d') between DATE_FORMAT('${startime}', '%Y-%m-%d') and DATE_FORMAT('${endtime}','%Y-%m-%d') AND (工單 like '___M%' OR 工單 like '___P%' OR 工單 like '___T%')''' code_dict = { 'PVA': '0103', '保护膜': '0104', '上T': '0102', 'PET': '011', '下T': '0107', '离型膜': '0105' } b = Base() self.ms = conns['offline'] for day in b.datelist('20180611', '20180625'): print(day) for k, v in code_dict.items(): chanchu15 = self.ms.doget( sql_chanchu.replace('thiscode', v).replace('${endtime}', day).replace( '${startime}', b.getYesterday(day, 15)))['rcc'][0] touru15 = self.ms.doget( sql_touru.replace('thiscode', v).replace('${endtime}', day).replace( '${startime}', b.getYesterday(day, 15)))['rtr'][0] chanchu30 = self.ms.doget( sql_chanchu.replace('thiscode', v).replace('${endtime}', day).replace( '${startime}', b.getYesterday(day, 30)))['rcc'][0] touru30 = self.ms.doget( sql_touru.replace('thiscode', v).replace('${endtime}', day).replace( '${startime}', b.getYesterday(day, 30)))['rtr'][0] if touru15 == None or touru15 == 'null' or touru30 == None or touru30 == 'null': res = pd.DataFrame( [[b.getYesterday(day), 0, 0, k]], columns=['riqi', 'costrate15', 'costrate30', 'type']) else: if chanchu15 == None or chanchu15 == 'null': chanchu15 = 0 if chanchu30 == None or chanchu30 == 'null': chanchu30 = 0 res = pd.DataFrame( [[ b.getYesterday(day), 1 - float(chanchu15) / float(touru15), 1 - float(chanchu30) / float(touru30), k ]], columns=['riqi', 'costrate15', 'costrate30', 'type']) b.batchwri(res, 'pastcost', self.ms) chanchu_yuanfan15 = self.ms.doget( sql_yuanfan_chanchu.replace('${endtime}', day).replace( '${startime}', b.getYesterday(day, 15)))['qty'][0] touru_yuanfan15 = self.ms.doget( sql_yuanfan_touru.replace('${endtime}', day).replace( '${startime}', b.getYesterday(day, 15)))['qty'][0] chanchu_yuanfan30 = self.ms.doget( sql_yuanfan_chanchu.replace('${endtime}', day).replace( '${startime}', b.getYesterday(day, 30)))['qty'][0] touru_yuanfan30 = self.ms.doget( sql_yuanfan_touru.replace('${endtime}', day).replace( '${startime}', b.getYesterday(day, 30)))['qty'][0] if touru_yuanfan15 == None or touru_yuanfan15 == 'null' or touru_yuanfan30 == None or touru_yuanfan30 == 'null': res = pd.DataFrame( [[b.getYesterday(day), 0, 0, '原反']], columns=['riqi', 'costrate15', 'costrate30', 'type']) else: if chanchu_yuanfan30 == None or chanchu_yuanfan30 == 'null': chanchu_yuanfan30 = 0 if chanchu_yuanfan15 == None or chanchu_yuanfan15 == 'null': chanchu_yuanfan15 = 0 res = pd.DataFrame([[ b.getYesterday(day), 1 - float(chanchu_yuanfan15) / float(touru_yuanfan15), 1 - float(chanchu_yuanfan30) / float(touru_yuanfan30), '原反' ]], columns=[ 'riqi', 'costrate15', 'costrate30', 'type' ]) b.batchwri(res, 'pastcost', 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(), \ '离型膜': open(base.path1 + 'sqls/损耗分析晨会/' + dir + '/lixingmo.sql', 'r').read()} for day in base.datelist('20180630', '20180705')[::-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 mi_sh = 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 mi_sh = 0 else: if k == 'PVA': mi_sh = 1 - (float(touru_money) - float(sunhao_money)) / ( 5.88 * float(touru_money)) else: mi_sh = 1 - (float(touru_money) - float( sunhao_money)) / float(touru_money) mon = offline.doget( "select sum(moneysh) sh,sum(moneytr) tr from (" + v + ") s where rq>='" + day[:8].replace('/', '-') + "01" + "' and rq<='" + day.replace('/', '-') + "'") mon30 = offline.doget( "select sum(moneysh) sh,sum(moneytr) tr from (" + v + ") s where rq>='" + base.getYesterday(day, 30).replace('/', '-') + "' and rq<='" + day.replace('/', '-') + "'") sh30 = mon30['sh'][0] tr30 = mon30['tr'][0] sh_sum = mon['sh'][0] tr_sum = mon['tr'][0] if tr30 == 0 or tr30 == None: mi30 = 0 else: if k == 'PVA': mi30 = float(sh30) / (5.88 * float(tr30)) else: mi30 = float(sh30) / float(tr30) fin.append([ day, sunhao_money, touru_money, sh_sum, tr_sum, sh30, tr30, mi_sh, mi30, k ]) res = pd.DataFrame(fin, columns=[ 'rq', 'moneysh', 'moneytr', 'moneysh_mon', 'moneytr_mon', 'sh30', 'tr30', 'mi_sh', 'mi30', 'type' ]) res['item'] = dir # offline.dopost("truncate table CostSum") # print(res) base.batchwri(res, 'CostSum', offline)
def __call__(self, conns): os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK' b = Base() self.erp = conns['erp'] self.ms = conns['offline'] #暂收存货 day = str(b.getYesterday()) # for day in b.datelist('20180101','20180630'): with open(b.path1 + 'sqls/存货明细/存货明细-暂收存货SQL_new.sql', 'r') as f: sql1 = f.read().replace('yestoday', day) res1 = self.erp.doget(sql1) res1.columns=['JE_SOURCE_NAME','VENDOR_NAME','ITEM_NUMBER','ITEM_DESCRIPTION',\ 'ACCOUNTING_DATE','CURRENCY_CODE','CURRENCY_CONVERSION_RATE',\ 'ENTERED_AMOUNT','ACCOUNTED_AMOUNT','QUANTITY','RECEIPT_NUM','PO_NUMBER'] self.ms.dopost( "delete from zanshoucunhuo where date_format(ACCOUNTING_DATE,'%Y-%m-%d')=date_format('" + day.replace('/', '-') + "','%Y-%m-%d')") b.batchwri(res1, 'zanshoucunhuo', self.ms) del res1, sql1 # now = datetime.datetime.now() # for month in range(7): # year = now.year # # month = (now.month - 1) % 12 # if month == 0: # month = 12 # year -= 1 # if month>=10: # thism=str(year)+'-'+str(month) # else: # thism=str(year)+'-0'+str(month) #在制品 thism = str(datetime.date.today())[:-3].replace('/', '-') with open(b.path1 + 'sqls/存货明细/存货明细-在制品进耗存SQL_NEW.sql', 'r') as f: sql2 = f.read().replace('thism', thism.replace('-', '')) res2 = self.erp.doget(sql2) res2.columns=['WIP_ENTITY_NAME','ITEM_CODE','STATUS_TYPE','DATE_CLOSED',\ 'TOTALSUMMARY_SUM','OVERHEADSUMMARY_SUM','RESROUCESUMMARY_SUM','MATERIALSUMMARY_SUM',\ 'COSTUPDATESUMMARY_SUM','COSTUPDATEOVERHEAD_SUM','COSTUPDATERESROUCE_SUM','COSTUPDATEMATERIAL_SUM', 'CURSCRAPSUMMARY_SUM','CURSCRAPOVERHEAD_SUM','CURSCRAPRESROUCE_SUM','CURSCRAPMATERIAL_SUM',\ 'CURCOMPLETESUMMARY_SUM','CURCOMPLETEOVERHEAD_SUM','CURCOMPLETERESROUCE_SUM',\ 'CURCOMPLETEMATERIAL_SUM','CURISSUESUMMARY_SUM','CURISSUEOVERHEAD_SUM','CURISSUERESROUCE_SUM',\ 'CURISSUEMATERIAL_SUM','BEFSUMMARY_SUM','BEFOVERHEAD_SUM','BEFRESOURCE_SUM','BEFMATERIAL_SUM','WIPQTY_SUM'] res2['upmonth'] = thism self.ms.dopost("delete from zaizhipin where upmonth='" + thism + "'") b.batchwri(res2, 'zaizhipin', self.ms) del res2, sql2 # 进耗存 with open(b.path1 + 'sqls/存货明细/存货明细-进耗存SQL_NEW.sql', 'r') as f: sql3 = f.read() res3 = self.erp.doget(sql3.replace('thismonth', thism)) res3.columns = ['ITEM_NUMBER','ITEM_DESCRIPTION','TRANSACTION_DATE_FM','TRANSACTION_DATE_TO',\ 'CATEGORY_NAME','BEG_STK','BEG_AMT','PO_STK_IN','PO_IN_AMT','WIP_STK_IN','WIP_IN_AMT','WIP_STK_OUT',\ 'WIP_OUT_AMT','SO_STK_OUT','SO_OUT_AMT','DEPT_STK_OUT',\ 'DEPT_OUT_AMT','OTHER_STK_OUT','OTHER_OUT_AMT','END_STK','END_AMT','END_UP','STANDARD_COST'] self.ms.dopost( "delete from jinhaocun where date_format(TRANSACTION_DATE_FM,'%Y-%m')='" + thism + "'") b.batchwri(res3, 'jinhaocun', self.ms) del sql3, res3 # 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=CunHuo() # hz(conns) # offline.close() # erp.close() # wms.close() # mes.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
def __call__(self, conns): base = Base() ms = conns['offline'] # ms.dopost("truncate table mocost1") for day in base.datelist('20180630', '20180704')[::-1]: day = day.replace('/', '-') ms.dopost("delete from mocost1 where day='" + day + "'") day30 = base.getYesterday(day, 29).replace('/', '-') day15 = base.getYesterday(day, 14).replace('/', '-') tomorrow = base.getTomorrow(day).replace('/', '-') print(tomorrow) for item in ['PET损耗', 'PVA损耗', '上T损耗', '下T损耗', '保护膜', '离型膜损耗']: for type in ['量产', 'TD', 'RD']: res = [] with open( base.path1 + 'sqls/各大膜损耗/' + item + '-' + type + '-' + '产出.sql', 'r') as f: sql_chanchu = f.read() with open( base.path1 + 'sqls/各大膜损耗/' + item + '-' + type + '-' + '投入.sql', 'r') as f: sql_touru = f.read() sql_chanchu_mon = sql_chanchu.replace( '${startime}', day[:8] + '01').replace('${endtime}', tomorrow) sql_touru_mon = sql_touru.replace('${startime}', day[:8] + '01').replace( '${endtime}', tomorrow) sql_chanchu15 = sql_chanchu.replace( '${startime}', day15).replace('${endtime}', tomorrow) sql_touru15 = sql_touru.replace('${startime}', day15).replace( '${endtime}', tomorrow) sql_chanchu30 = sql_chanchu.replace( '${startime}', day30).replace('${endtime}', tomorrow) sql_touru30 = sql_touru.replace('${startime}', day30).replace( '${endtime}', tomorrow) touru_mon = ms.doget(sql_touru_mon) chanchu_mon = ms.doget(sql_chanchu_mon) touru15 = ms.doget(sql_touru15) chanchu15 = ms.doget(sql_chanchu15) touru30 = ms.doget(sql_touru30) chanchu30 = ms.doget(sql_chanchu30) mon = pd.merge(touru_mon, chanchu_mon, how='inner', on=['rq', 'ITEM_COST']) m15 = pd.merge(touru15, chanchu15, how='inner', on='rq') m30 = pd.merge(touru30, chanchu30, how='inner', on='rq') if m15.empty or m30.empty: pass elif mon.empty: tourucost_mon = 0 mishu_mon = 0 sunhao_mon = 0 sun_money15 = 1 - sum( chanchu15.apply( lambda r: self.calchanchu(r), axis=1)) / sum( touru15.apply(lambda r: self.caltouru(r), axis=1)) sun_money30 = 1 - sum( chanchu30.apply( lambda r: self.calchanchu(r), axis=1)) / sum( touru30.apply(lambda r: self.caltouru(r), axis=1)) res.append([ day, item, type, sunhao_mon, tourucost_mon, mishu_mon, sun_money15, sun_money30 ]) else: tourucost_mon = sum( list( touru_mon.apply(lambda r: self.caltouru(r), axis=1))) chanchusum_mon = sum( list( chanchu_mon.apply(lambda r: self.calchanchu(r), axis=1))) sunhao_mon = tourucost_mon - chanchusum_mon if item == 'PVA损耗': mishu_mon = 1 - sum(chanchu_mon['rcc']) / ( 5.88 * sum(touru_mon['rtr'])) else: mishu_mon = 1 - sum(chanchu_mon['rcc']) / sum( touru_mon['rtr']) sun_money15 = 1 - sum( chanchu15.apply( lambda r: self.calchanchu(r), axis=1)) / sum( touru15.apply(lambda r: self.caltouru(r), axis=1)) sun_money30 = 1 - sum( chanchu30.apply( lambda r: self.calchanchu(r), axis=1)) / sum( touru30.apply(lambda r: self.caltouru(r), axis=1)) res.append([ day, item, type, sunhao_mon, tourucost_mon, mishu_mon, sun_money15, sun_money30 ]) res = pd.DataFrame(res, columns=[ 'day', 'item', 'type', 'sunhao_mon', 'tourucost_mon', 'mishu_mon', 'sun_money15', 'sun_money30' ]) base.batchwri(res, 'mocost1', ms)
def __call__(self, conns): base = Base() ms = conns['offline'] with open(base.path1 + 'sqls/良率30日累计/综合.sql', 'r') as f: sql1 = f.read() with open(base.path1 + 'sqls/良率30日累计/量产TD.sql', 'r') as f: sql2 = f.read() with open(base.path1 + 'sqls/良率30日累计/量产重庆.sql', 'r') as f: sql3 = f.read() sqldict = {'综合': sql1, '量产+TD': sql2, '量产+重庆': sql3} devicedict = {'R': ['R', 'D'], 'F': ['F', 'U']} sizelst = [55, 50, 31.5, 38.5, 23.6] # for day in base.datelist('20180101', '20180628')[::-1]: # day = day.replace('/', '-') # day30 = base.getYesterday(day, 30).replace('/','-') # for rangename, sql in sqldict.items(): # res = [] # thissql = sql.replace('starttime', "'" + day30 + "'").replace('endtime', "'" + day + "'") # for device in devicedict.values(): # for size in sizelst: # thissql = thissql.replace('thisdevice1', device[0]).replace('thisdevice2', device[1]).replace('thissize', str(size)) # sqlres = ms.doget(thissql) # sum_input = sum(sqlres['sum(投入)']) # if sum_input >= 1: # ljlv30 = sum(sqlres['sum(A规)'])/sum_input # res.append([day, ljlv30, size, device[0], rangename]) # if not res == []: # res = pd.DataFrame(res, columns=['rq', 'ljlv30', 'chicun', 'type', 'range'] ) # base.batchwri(res, 'sum_goodratio', ms) today = datetime.datetime.now() tomorrow = today + datetime.timedelta(1) #ms.dopost("truncate table sum_goodratio") #for day in base.datelist('20180101', '20180627')[::-1]: for day in [today.strftime('%Y/%m/%d'), tomorrow.strftime('%Y/%m/%d')]: day = day.replace('/', '-') day30 = base.getYesterday(day, 30).replace('/', '-') ms.dopost("delete from sum_goodratio where rq=" + "'" + day + "'") for rangename, sql in sqldict.items(): res = [] thissql = sql.replace('starttime', "'" + day30 + "'").replace( 'endtime', "'" + day + "'") thissql2 = sql.replace('starttime', "'" + day[:8] + "01'").replace( 'endtime', "'" + day + "'") for device in devicedict.values(): for size in sizelst: thissql = thissql.replace( 'thisdevice1', device[0]).replace( 'thisdevice2', device[1]).replace('thissize', str(size)) thissql2 = thissql2.replace('thisdevice1', device[0]).replace( 'thisdevice2', device[1]).replace( 'thissize', str(size)) sqlres = ms.doget(thissql) sqlres2 = ms.doget(thissql2) sum_input = sum(sqlres['sum(投入)']) sum_input2 = sum(sqlres2['sum(投入)']) if sum_input >= 1: ljlv30 = sum(sqlres['sum(A规)']) / sum_input else: ljlv30 = -1 if sum_input2 >= 1: ljlv_mon = sum(sqlres2['sum(A规)']) / sum_input2 else: ljlv_mon = -1 res.append([ day, ljlv30, ljlv_mon, size, device[0], rangename ]) if not res == []: res = pd.DataFrame(res, columns=[ 'rq', 'ljlv30', 'ljlv_mon', 'chicun', 'type', 'range' ]) base.batchwri(res, 'sum_goodratio', ms)