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)
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()
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, 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):
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):
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)