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)
