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
