def __call__(self,conns):
self.base=Base()
self.ms =conns['offline']
self.ora=conns['mes']
with open(self.base.path1+'sqls/原返损耗投入.sql','r') as f:
sql=f.read()
# self.ms.dopost('truncate table loss_yuanfan_input')
# for day in range(1,3):
# today = datetime.datetime(2018,7,day)
# todaystr = datetime.datetime.strftime(today, '%Y/%m/%d')
# sql0 = sql.replace('thisdate', todaystr)
# res = self.ora.doget(sql0)
# res['業務日期'] = todaystr
# self.ms.dopost("delete from loss_yuanfan_input where 業務日期='"+todaystr+"'")
# self.base.batchwri(res, 'loss_yuanfan_input', self.ms)
yesterday = datetime.datetime.now() - datetime.timedelta(days=1)
#yesterday=datetime.datetime(2018,6,5)
yesterdaystr = datetime.datetime.strftime(yesterday, '%Y/%m/%d')
sql0 = sql.replace('thisdate', yesterdaystr)
res = self.ora.doget(sql0)
res['業務日期']=yesterdaystr
self.ms.dopost("delete from loss_yuanfan_input where 業務日期='" + yesterdaystr + "'")
self.base.batchwri(res, 'loss_yuanfan_input', self.ms)
def __call__(self,conns):
self.base=Base()
self.ms =conns['offline']
self.ora=conns['erp']
# for month in range(7):
# now = datetime.datetime.now()
# year = now.year
# if month == 0:
# month = 12
# year -= 1
# this_quarter_start = datetime.datetime(year, month, 1)
# lastm = str(this_quarter_start)[:7]
# with open(self.base.path1 + 'sqls/主营业务收入.sql', 'r') as f:
# sql = f.read().replace('thismonth', lastm)
# res = self.ora.doget(sql)
# res['日期'] = lastm
# self.base.batchwri(res, 'main_bussiness_income1', self.ms)
now = datetime.datetime.now()
year = now.year
month = (now.month - 1)%12
if month == 0:
month = 12
year -= 1
this_quarter_start = datetime.datetime(year, month, 1)
lastm = str(this_quarter_start)[:7]
with open(self.base.path1+'sqls/主营业务收入.sql','r') as f:
sql=f.read().replace('thismonth', lastm)
res = self.ora.doget(sql)
res['日期'] = lastm
self.ms.dopost("delete from main_bussiness_income1 where 日期='" + lastm + "'")
self.base.batchwri(res, 'main_bussiness_income1', self.ms)
def __call__(self, conns):
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK'
b = Base()
self.erp = conns['erp']
self.ms = conns['offline']
now = datetime.datetime.now()
# for month in range(6):
year = now.year
month = (now.month - 1) % 12
if month == 0:
month = 12
year -= 1
this_quarter_start = datetime.datetime(year, month, 1)
lastm = str(this_quarter_start)[:7]
with open(b.path1 + 'sqls/资产负债表.sql', 'r') as f:
sql1 = f.read().replace('thismonth', str(lastm))
res1 = self.erp.doget(sql1)
res1.columns = [
'货币资金', '应收票据', '应收账款', '预付款项', '应收利息', '其他应收账款', '存货', '原材料',
'库存商品_产成品', '库存商品_半成品', '其他流动资产', '可供出售金融资产', '长期股权投资', '固定资产原值',
'减_累计折旧', '在建工程', '无形资产', '长期待摊费用', '递延所得税资产', '短期借款', '应付票据',
'应付账款', '预收账款', '应付职工薪酬', '应交税费', '其中_应交税金', '应付利息', '其他应付款',
'其他流动负债', '长期借款', '递延所得税负债', '实收资本', '资本公积', '专项储备', '盈余公积',
'其中_法定公积金', '未分配利润'
]
res1['upmonth'] = lastm
self.ms.dopost("delete from zichanfz where upmonth='" + lastm + "'")
b.batchwri(res1, 'zichanfz', self.ms)
del res1, sql1
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 main():
print('------' +
time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())) +
',运行在线程序' + '------')
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}
config = conf.configs
lasttime = conf.total_T
newpoint = strftime("%Y-%m-%d %H:%M:%S", localtime())
for k, v in config.items():
if lasttime % int(v['T']) == 0:
k2func(k, v['checkpoint'], newpoint, conns)
config[k]['checkpoint'] = newpoint #修改checkpoint
if lasttime == minx([v['T'] for v in config.values()]):
new_T = 1 # 周期循环
else:
new_T = lasttime + 1
with open(base.path1 + 'config.py', 'w') as f:
f.write('configs=' + str(config) + '\n' + 'total_T=' +
str(new_T)) #写入配置文件
offline.close()
erp.close()
wms.close()
mes.close()
class LossYuanfanInput(object):
def __init__(self):
super(LossYuanfanInput, self).__init__()
def __call__(self, conns):
self.base = Base()
self.ms = conns['offline']
self.ora = conns['mes']
with open(self.base.path1 + 'sqls/原返损耗投入.sql', 'r') as f:
sql = f.read()
# self.ms.dopost('truncate table loss_yuanfan_input')
# for day in range(1,26):
# today = datetime.datetime(2018,6,day)
# todaystr = datetime.datetime.strftime(today, '%Y/%m/%d')
# sql0 = sql.replace('thisdate', todaystr)
# res = self.ora.doget(sql0)
# self.base.batchwri(res, 'loss_yuanfan_input', self.ms)
yesterday = datetime.datetime.now() - datetime.timedelta(days=1)
#yesterday=datetime.datetime(2018,6,5)
yesterdaystr = datetime.datetime.strftime(yesterday, '%Y/%m/%d')
sql0 = sql.replace('thisdate', yesterdaystr)
res = self.ora.doget(sql0)
res['業務日期'] = yesterdaystr
self.base.batchwri(res, 'loss_yuanfan_input_test', self.ms)
def __call__(self, conns):
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK'
b = Base()
self.erp = conns['erp']
self.ms = conns['offline']
now = datetime.datetime.now()
month = (now.month - 1) % 12
# for month in range(7):
year = now.year
if month == 0:
month = 12
year -= 1
this_quarter_start = datetime.datetime(year, month, 1)
lastm = str(this_quarter_start)[:7]
with open(b.path1 + 'sqls/利润表.sql', 'r') as f:
sql1 = f.read().replace('thismonth', str(lastm))
res1 = self.erp.doget(sql1)
res1.columns = [
'营业收入', '减_营业成本', '税金及附加', '销售费用', '管理费用', '财务费用', '资产减值损失',
'投资收益', '加_营业外收入', '减_营业外支出', '减_所得税费用'
]
res1['upmonth'] = lastm
self.ms.dopost("delete from lirun_new where upmonth='" + lastm + "'")
b.batchwri(res1, 'lirun_new', self.ms)
del res1, sql1
def __call__(self, conns):
sql = "select * from APPS.CST_ITEM_COST_TYPE_V"
b = Base()
self.ora = conns['erp']
self.ms = conns['offline']
res = self.ora.doget(sql)
self.ms.dopost('truncate table materialprice')
b.batchwri(res, 'materialprice', self.ms)
def __call__(self, conns):
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK'
base = Base()
self.ms = conns['offline']
self.ora = conns['erp']
now = datetime.datetime.now()
# for month in range(7):
year = now.year
month = (now.month - 1) % 12
if month == 0:
month = 12
year -= 1
this_quarter_start = datetime.datetime(year, month, 1)
lastm = str(this_quarter_start)[:7]
tables = {'manage_fee1': open(base.path1 + 'sqls/费用/管理费用.sql', 'r').read().replace('thismonth', lastm), \
'sale_fee1': open(base.path1 + 'sqls/费用/销售费用.sql', 'r').read().replace('thismonth', lastm), \
'made_fee1': open(base.path1 + 'sqls/费用/制造费用.sql', 'r').read().replace('thismonth', lastm), \
'finance_fee1': open(base.path1 + 'sqls/费用/财务费用.sql', 'r').read().replace('thismonth', lastm)}
for k, v in tables.items():
print('正在进行' + k)
res = self.ora.doget(v)
if k == 'sale_fee1':
res.columns = [
'日期', '工资', '福利', '保险费_人事', '住房公积金', '工会经费', '职工教育经费',
'股份支付', '职工薪酬其他', '文具用品', '旅费', '运费', '邮电费', '修缮费', '广告费',
'动力费', '交际费', '折旧', '各项摊提', '权力费', '职工福利', '研究发展费', '训练费',
'保全费', '出口费用', '加工费', '包装费', '间接材料', '检测费', '保险费资产',
'什项购置', '书报杂志', '劳务费', '进口费用', '会议费', '租金支出', '残保金',
'外包人力派遣', '交通费', '其他费用'
]
if k == 'manage_fee1':
res.columns = [
'日期', '工资', '福利', '保险费', '住房公积金', '工会经费', '职工教育经费', '股份支付',
'职工薪酬其他', '文具用品', '旅费', '运费', '邮电费', '修缮费', '广告费', '动力费',
'交际费', '折旧', '各项摊提', '权力费', '职工福利', '研究发展费', '训练费', '保全费',
'加工费', '包装费', '间接材料', '检测费', '保险费资产', '什项购置', '书报杂志',
'劳务费', '进口费用', '会议费', '租金支出', '残保金', '外包人力派遣', '交通费',
'其他费用'
]
if k == 'finance_fee1':
res.columns = [
'日期', '利息收入', '集团内部利息收入', '贷款利息支出', '集团内部利息支出', '金融机构手续费',
'汇兑损益', '财务顾问费', '担保费', '贴现利息支出', '融资租赁利支出', '利息支出资本化'
]
if k == 'made_fee1':
res.columns = [
'日期', '薪资支出_直接', '社会保险费_直接', '伙食费_直接', '外包人力派遣_直接', '薪资支出',
'保险费_人事', '文具用品', '旅费', '运费', '邮电费', '修缮费', '广告费', '动力费',
'交际费', '折旧', '各项摊提', '权利金', '伙食费', '职工福利', '研究发展费', '训练费',
'保全费', '出口费用', '加工费', '包装费', '间接材料', '检测费', '保险费资产',
'什项购置', '书报杂志', '劳务费', '进口费用', '会议费', '租金支出', '残保金',
'外包人力派遣', '交通费', '其他费用'
]
# res['日期'] = res.apply(lambda r: self.trandate(r), axis=1)
self.ms.dopost("delete from " + k + " where 日期='" + lastm + "'")
print(res)
base.batchwri(res, k, self.ms)
def __call__(self, conns):
base = Base()
self.ora = conns['mes']
sql = open(base.path1 + 'sqls/良品.sql', 'r').read()
self.ms = conns['offline']
res = self.ora.doget(sql)
res.columns = ['liangpin_qty', 'touru_qty', 'class', 'WEEK']
self.ms.dopost('truncate table liangpin')
base.batchwri(res, 'liangpin', self.ms)
def __call__(self, conns):
b = Base()
self.ora = conns['wms']
sql = open(b.path1 + 'sqls/直通率/Shipments_now.sql', 'r').read()
self.ms = conns['offline']
res = self.ora.doget(sql)
res.columns = ['amt', 'num', 'time']
self.ms.dopost('truncate table ShipmentsNow')
b.batchwri(res, 'ShipmentsNow', self.ms)
def __call__(self, conns):
base=Base()
erp=conns['erp']
offline=conns['offline']
with open(base.path1+'sqls/销售面积.sql','r') as f:
sql=f.read()
res=erp.doget(sql)
offline.dopost("truncate table sold_area")
base.batchwri(res,'sold_area',offline)
def __call__(self, conns):
base = Base()
erp = conns['erp']
offline = conns['offline']
with open(base.path1 + 'sqls/应付账款账龄.sql', 'r') as f:
sql = f.read()
res = erp.doget(sql)
offline.dopost("truncate table should_pay_age")
base.batchwri(res, 'should_pay_age', offline)
def __call__(self,conns):
b=Base()
self.ora =conns['erp']
sql = open(b.path1+'sqls/直通率/Shipments_statistics.sql', 'r', encoding='utf-8').read()
self.ms =conns['offline']
res = self.ora.doget(sql)
res.columns = ['ITEM_CODE', 'ONHAND_QUANTITY', 'UNIT_LENGTH', 'UNIT_WIDTH']
self.ms.dopost('truncate table ShipmentsStatistics')
b.batchwri(res, 'ShipmentsStatistics',self.ms)
def __call__(self, conns):
base = Base()
self.ora = conns['mes']
self.ms = conns['offline']
with open(base.path1 + 'sqls/损耗.sql', 'r') as f:
sql = f.read()
res = self.ora.doget(sql)
res['wotype'] = res.apply(lambda r: self.getttype(r), axis=1)
self.ms.dopost('truncate table sunhao')
base.batchwri(res, 'sunhao', self.ms)
def __call__(self, conns):
sql = '''SELECT
*
FROM MES.CMMT_CCD_INFO'''
b = Base()
self.ora = conns['mes']
self.ms = conns['offline']
res = self.ora.doget(sql)
self.ms.dopost('truncate table CMMT_CCD_INFO')
b.batchwri(res, 'CMMT_CCD_INFO', self.ms)
def __call__(self, conns):
b = Base()
self.ora = conns['mes']
self.ms = conns['offline']
with open(b.path1 + 'sqls/良率/良率统计分析.sql', 'r') as f:
sql = f.read()
res = self.ora.doget(sql)
res.columns=['pva_lot','psa_trantime','psa_sub','pva_trantime','slt_lot','hd_lot','sub_lot',\
'cust_lot','checkout_time','device','hd_wo',\
'qd_wo','arr_qty','Agui','reason','descr','quantity','bancheng_lot']
self.ms.dopost('truncate table lianglv')
b.batchwri(res, 'lianglv', self.ms)
def __call__(self, conns):
b = Base()
self.ora = conns['mes']
self.ms = conns['offline']
sql = open(b.path1 + 'sqls/vlm_mwh.sql', 'r').read()
res = self.ora.doget(sql)
res.columns = [
'lot', 'device', 'wo', 'newquantity', 'transactiontime',
'resourcename', 'oldoperation'
]
self.ms.dopost('truncate table vlm_mwh')
b.batchwri(res, 'vlm_mwh', self.ms)
def __call__(self, conns):
b = Base()
self.ora = conns['mes']
sql = open(b.path1 + 'sqls/良率/良率日报.sql', 'r').read()
self.ms = conns['offline']
res = self.ora.doget(sql)
res.columns = [
'qd_wo', 'hd_wo', 'materialno', 'qd_lot', 'hd_lot', 'pro_state',
'thisdate', 'facno', 'class', 'thistype', 'touru_qty',
'liangpin_qty', 'dianque_qty', 'dianque_item', 'sort', 'speed'
]
self.ms.dopost('truncate table lianglvribao')
b.batchwri(res, 'lianglvribao', self.ms)
def __call__(self,conns):
sql = '''
SELECT "DEFECTTYPE", "DEVICE"
, "LOT", "LOTQUANTITY", "OPERATION"
, "PVACHECKOUTTIME", "PVARESNAME", "QCRESULTCUST", "QCRESULTSYS"
, "QUANTITY", "REMARK", "RESOURCENAME"
, replace("UPDATETIME",'/','-') AS dat, "VISUALRESULT", "WO"
FROM MES.qcview'''
b=Base()
self.ora =conns['mes']
self.ms =conns['offline']
res = self.ora.doget(sql)
self.ms.dopost('truncate table qcview')
b.batchwri(res, 'qcview',self.ms)
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):
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK'
b = Base()
self.erp = conns['erp']
self.ms = conns['offline']
with open(b.path1 + 'sqls/成本计算.sql', 'r') as f:
sql1 = f.read()
res1 = self.erp.doget(sql1)
res1.columns = [
'cpdm', 'wlbm', 'UOM', 'bibie', 'MSIZE', 'FROZEN_COST',
'CURRENT_COST', 'COMPONENT_QUANTITY'
]
self.ms.dopost("truncate table chengbenjisuan")
b.batchwri(res1, 'chengbenjisuan', self.ms)
del res1, sql1
def __call__(self, conns):
b = Base()
self.ora = conns['wms']
self.ms = conns['offline']
sql = '''
select
SUM(QTY)/1000 QTY,ITEM_CODE
FROM WMS.A_PALLET_ITEM
WHERE SUBINVENTORY_CODE IN ('F1FGA','F1RDA','F1CAA') AND QC_STATUS NOT IN ('B','D')
GROUP BY ITEM_CODE'''
res = self.ora.doget(sql)
day = base.gettoday()
res['日期'] = day
self.ms.dopost("delete from swgkc where 日期='" + day + "'")
b.batchwri(res, 'swgkc', self.ms)
def __call__(self,btime,etime,conns):
b=Base()
sql1 =open(b.path1+'sqls/翘角信息查询.sql','r').read()
self.ora=conns['mes']
self.ms=conns['offline']
self.mess2('2018-06-01 00:00:00','2018-06-04 00:00:00')
# print('创建输出表...')
# createtables(sqlc1)
# createtables(sqlc2)
print('取表1...')
# for lot in ms.getdata('check_mess')['LOT'].drop_duplicates().dropna().values:
# sql1=sql1.replace('thislot',lot)
# mess1(sql1)
# print('取表2...')
# for i in range(1,5):
# print('正在查询第'+str(i)+'月的信息...')
# if i<9:
# bd='0'+str(i)+'-01'
# ed='0'+str(i+1)+'-01'
# elif i==9:
# bd='09'+'-01'
# ed='10'+'-01'
# elif i==12:
# bd='12-01'
# ed='12-31'
# else:
# bd=str(i)+'-01'
# ed=str(i+1)+'-01'
sql1=sql1.replace('begintime','2018-06-01').replace('endtime','2018-06-04')
self.mess1(sql1)
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("delete from OverallGoodRatio00 where date_format(CHECKOUTTIME,'%Y-%m-%d %H:%i:%s')>date_format('" + "2018-03-03 08:00:00" + "','%Y-%m-%d %H:%i:%s') ")
# for day in base.datelist('20180101', '20180704')[::-1]:
# todaybeg = day + ' 08:00:00'
# todayend = datetime.datetime.strptime(todaybeg, '%Y/%m/%d %H:%M:%S') + datetime.timedelta(1)
# todayend = todayend.strftime('%Y/%m/%d %H:%M:%S')
# thissql = sql.replace('thisbegtime', todaybeg).replace('thisendtime', todayend)
# res = mes.doget(thissql)
# if not res.empty:
# lotmu = res['LOT'].apply(self.findmu)
# lotmures = []
# for idx in lotmu.index:
# if lotmu[idx] in lotmu.values[:idx] or lotmu[idx] in lotmu.values[idx+1:]:
# lotmures.append(lotmu[idx])
# else:
# lotmures.append(res.loc[idx, 'LOT'])
# res['LOT_MU'] = lotmures
# res['week'] = res.apply(lambda r: self.trandate(r), axis=1)
# base.batchwri(res, 'OverallGoodRatio00', offline)
todaybeg = datetime.datetime.now().strftime('%Y/%m/%d') + ' 08:00:00'
todayend = datetime.datetime.strptime(
todaybeg, '%Y/%m/%d %H:%M:%S') + datetime.timedelta(1)
todayend = todayend.strftime('%Y/%m/%d %H:%M:%S')
thissql = sql.replace('thisbegtime',
todaybeg).replace('thisendtime', todayend)
res = mes.doget(thissql)
if not res.empty:
lotmu = res['LOT'].apply(self.findmu)
offline.dopost(
"delete from OverallGoodRatio00 where date_format(CHECKOUTTIME,'%Y-%m-%d %H:%i:%s')>date_format('"
+ todaybeg + "','%Y-%m-%d %H:%i:%s') ")
lotmures = []
for idx in lotmu.index:
if lotmu[idx] in lotmu.values[:idx] or lotmu[
idx] in lotmu.values[idx + 1:]:
lotmures.append(lotmu[idx])
else:
lotmures.append(res.loc[idx, 'LOT'])
res['LOT_MU'] = lotmures
res['week'] = res.apply(lambda r: self.trandate(r), axis=1)
base.batchwri(res, 'OverallGoodRatio00', offline)
def __call__(self, conns):
sql = '''select DESCR,sum(a.dianque) qty from (
SELECT DISTINCT DESCR,dianque,CHECKOUTTIME FROM OverallGoodRatio
WHERE STR_TO_DATE(REPLACE(CHECKOUTTIME ,'/','-'),'%Y-%m-%d %H:%i:%s')
BETWEEN STR_TO_DATE(CURDATE(),'%Y-%m-%d 07:00:00')
AND STR_TO_DATE(date_add(CURDATE(), interval 1 day),'%Y-%m-%d 07:00:00')
) a
GROUP BY DESCR'''
b = Base()
self.ms = conns['offline']
res = self.ms.doget(sql).dropna()
self.sumnow = 0.0
self.total = sum([float(x[1]) for x in res.values])
self.ms.dopost('truncate table dianque_sumrate')
if self.total != 0:
res['rate'] = res.apply(lambda r: self.calrate(r), axis=1)
#print(res)
b.batchwri(res, 'dianque_sumrate', 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()}
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, btime, etime, conns):
# 取数据
print('Data Loading...')
self.ora = conns['mes']
# self.wms=conns['wms']
# hist=ora.getdata('MES.MES_WIP_HIST',pars=['LOT','OLDOPERATION','TRANSACTIONTIME', 'NEWQUANTITY','TRANSACTION','ACTIVITY'],elimit=100000)
self.nonact = self.ora.getdata(
'MES.MES_WIP_LOT_NONACTIVE',
['LOT', 'MATERIAL', 'PALLET', 'QUANTITY', 'CUSTLOT'],
tjs=[
"to_date(WMS_SHIP_DATE,'yyyy-mm-dd hh24:mi:ss')>=to_date('" +
btime + "','yyyy-mm-dd hh24:mi:ss')",
"to_date(WMS_SHIP_DATE,'yyyy-mm-dd hh24:mi:ss')<to_date('" +
etime + "','yyyy-mm-dd hh24:mi:ss')"
])
# slt = ora.getdata('MES.MES_WIP_SPLIT', ['SUBLOT', 'PARENTLOT', 'OPERATION'], elimit=10)
# merge=ora.getdata('MES.MES_WIP_MERGE',['SUBLOT','PARENTLOT'],elimit=10)
# mms=ora.getdata('MES.VIEW_MMSLOTLIST_MAIN',elimit=10)
print('Data Loading finish!')
print('Data processing...')
# ms=mysql2pd()
custlot = 'KA180200879'
nonflag = self.nonact['MATERIAL'].fillna('null')
custlot = self.nonact[nonflag.str.contains(
'-S-')]['PALLET'].drop_duplicates().dropna()
base = Base()
self.ms = conns['offline']
# ms.dopost('truncate table trace_production')
for c in custlot.values:
print('开始：' + str(c))
res = self.doit(c)
res = pd.DataFrame([x for x in res if len(x) > 2],
columns=['trace_id', 'front_device', 'mat_cuslot', 'mat_type', 'mat_device', 'mat_lot', \
'pva_inqty', 'pva_intime', 'pva_lot', 'pva_outtime', 'pva_outqty', 'psa_lot',
'psa_inqty', 'psa_outqty', 'psa_intime', 'psa_outtime', \
'aging_inqty', 'aging_outqty', 'aging_intime', 'aging_outtime', 'slt_lot',
'slt_inqty', 'slt_outqty', 'slt_intime', 'slt_outtime', 'rtx_lot', 'rtx_enqu',
'rtx_inqty',
'rtx_outqty', 'rtx_intime', 'rtx_outtime', 'pkg_pallet', 'pkg_custlot', 'pkg_qty','pkg_outlot'
]).drop_duplicates()
# print(res['pva_lot']+'|'+res['psa_lot']+'|'+res['slt_lot']+'|'+res['rtx_lot'])
self.ms.write2mysql(res, 'trace_production')
# 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=LvLi()
# zc('2018-06-20 00:00:00','2018-06-28 00:00:00',conns)
# offline.close()
# erp.close()
# wms.close()
# mes.close()
def __call__(self,btime,etime,conns):
b=Base()
with open(b.path1+'sqls/翘角信息查询.sql','r') as f1:
sql1 =f1.read().replace('btime',btime).replace('etime',etime)
with open(b.path1+'sqls/翘角信息查询2.sql', 'r') as f2:
self.sql2 = f2.read()
self.ora=conns['mes']
self.ms=conns['offline']
data=self.ora.doget(sql1)
print(data)
res=[]
for one in data.values:
data2=self.dolot(list(one)[4])
for v in data2.values:
fin=list(one)+list(v)[:-1]
res.append(fin)
res_pd=pd.DataFrame(res,columns=["pva_outtime","psa_outtime","rtx_outtime","checktime","psa_lot",\
"slt_lot","rtx_lot","data1","PARAMETER","SAMPLESEQ","OPERATION"])
b.batchwri(res_pd,"all_qiaojiao",self.ms)
def __call__(self,conns):
self.base = Base()
self.ms = conns['offline']
self.ora = conns['erp']
# for month in range(7):
now = datetime.datetime.now()
year = now.year
month = (now.month-1)%12
if month == 0:
month = 12
year -= 1
this_quarter_start = datetime.datetime(year, month, 1)
lastm = str(this_quarter_start)[:7]
with open(self.base.path1 + 'sqls/损益分析.sql', 'r') as f:
sql = f.read().replace('thismonth', lastm)
res = self.ora.doget(sql)
res['日期'] = lastm
self.ms.dopost("delete from " + 'gains_analysis' + " where 日期='" + lastm + "'")
self.base.batchwri(res, 'gains_analysis', self.ms)
