def get_count(count_sql):
oracleHelper = OracleHelper.OracleHelper()
row_count = oracleHelper.queryRow(count_sql)
count = row_count[0]
logging.info("数据总数:" + str(count))
oracleHelper.close()
return count
def get_data(query_sql, insert_sql, cls_size):
insert_values = insert_sql
oracleHelper = OracleHelper.OracleHelper()
rows = oracleHelper.query(query_sql)
logging.info("分页获取数据:" + str(oracleHelper.rowcount()))
currrent_time = time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time()))
for row in rows:
str_row = "("
for index in range(0, cls_size):
if isinstance(row[index], int) or isinstance(row[index], float):
if str(row[index]) == "None":
str_row += "null,"
else:
str_row += str(row[index]) + ","
else:
if str(row[index]) == "None":
str_row += "null,"
else:
str_row = str_row + "'" + str(row[index]) + "',"
str_row += "'" + currrent_time + "')\n,"
insert_values = insert_values + str_row
oracleHelper.close()
return insert_values[0:len(insert_values) - 1]
def SetData():
orcale = OracleHelper.Oracle('dsjky/[email protected]:1521/DSJKY_P')
ret = orcale.ExecuteData("select * from TB_FDN_MATERIALS_CARD")
for temp in ret:
# print(temp[18].strftime('%Y-%m-%d'))
# print(datetime.datetime.strptime(temp[18],'%Y-%m-%d'))
# return
Http = HttpHelper.Http()
params = {
"data": {
"data": [{
"param":
"KyJuItem",
"data": [{
"itemnum": temp[2],
"description": temp[1],
"issueunit": temp[5],
"cModel": temp[4],
"itemtype":
'ITEM', # if temp[7] == '64001' else 'TOOL',
"createDate": temp[18].strftime('%Y-%m-%d'),
"businessCategory": 'keyun',
# TODO：这个是材料分类 目前咱们的库里没有
"itemcategory": 0,
"rotating": '0' # if temp[7] == '64001' else '1'
}],
"pkId":
" itemnum "
}],
"method":
"saveReturnDataParam",
"type":
"keyun"
}
}
ret = Http.Get(params)
# print(ret)
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + ' | ' +
temp[2] + " | " + ret[8:10])
# return
time.sleep(1)
def SetData():
orcale = OracleHelper.Oracle('dsjky/[email protected]:1521/DSJKY_P')
ret = orcale.ExecuteData("select * from TB_FDN_MATERIALS_WAREHOUSE")
for temp in ret:
# print(temp[18].strftime('%Y-%m-%d'))
# print(datetime.datetime.strptime(temp[18],'%Y-%m-%d'))
# return
Http = HttpHelper.Http()
params = {
"data": {
"data": [{
"param":
"KyJuLocation",
"data": [{
"locationId": temp[0],
"locationName": temp[1],
"locationStation": temp[6],
"locationType": 'STOREROOM',
"businessCategory": 'keyun',
"itemAttribute": '1'
}],
"pkId":
"locationId"
}],
"method":
"saveReturnDataParam",
"type":
"keyun"
}
}
ret = Http.Get(params)
# print(ret)
# print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + ' | ' + temp[0] + " | " + "None" if ret is None else ret)
Log.info(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +
' | ' + temp[0] + " | " + "None" if ret is None else ret)
return
time.sleep(1)
def GetData():
orcale = OracleHelper.Oracle('*')
try:
# 先取出来所有的单据 TODO：添加时间过滤
sql = "select * from TB_FDN_MATERIALS_INTAKE WHERE YHDW_CODE='DEPT000073'"
order_data = orcale.ExecuteData(sql)
print(len(order_data))
# 遍历所有的单据 去找物资信息
for order in order_data:
# 取出该订单的物资信息
sql = '''
select TB_FDN_MATERIALS_DETAIL.*,
TB_FDN_MATERIALS_CARD.WZM,
TB_FDN_MATERIALS_CARD.WZBM,
TB_FDN_MATERIALS_CARD.WZLB,
TB_FDN_MATERIALS_CARD.GGXH,
TB_FDN_MATERIALS_CARD.JLDW,
TB_FDN_MATERIALS_CARD.HSDJ,
TB_FDN_MATERIALS_CARD.WZLB_CODE,
TB_FDN_MATERIALS_CARD.WZLB_NAME,
TB_FDN_MATERIALS_CARD.MAX,
TB_FDN_MATERIALS_CARD.MIN,
TB_FDN_MATERIALS_CARD.ITEMCATEGORY
from TB_FDN_MATERIALS_DETAIL left join TB_FDN_MATERIALS_CARD
on TB_FDN_MATERIALS_CARD.ID=TB_FDN_MATERIALS_DETAIL.MATERIALS_ID
where PARENT_CODE='%s'
''' % (order[12])
# print(sql)
detail = orcale.ExecuteData(sql)
# print(detail)
# 合并该订单物资信息
mat_detail = []
for d in detail:
if len(mat_detail) == 0:
mat_detail.append(list(d))
continue
for mat in mat_detail:
# print(mat[8])
# print(d[8])
# print('------------')
if mat[4] == d[4]:
# print(type( mat))
mat[10] = int(mat[10]) + int(d[10])
break
else:
# print(d[9].strftime('%Y-%m-%d %H:%M'))
# print(type(d[9]))
mat_detail.append(list(d))
# print(mat_detail)
# 按订单提交数据
SetData(mat_detail)
# return
except BaseException as e:
print(e)
# Log.error(e)
pass
finally:
pass
def ReadDb():
orcale = OracleHelper.Oracle('dsjky/[email protected]:1521/DSJKY_P')
ret = orcale.ExecuteData("select * from TB_FDN_MATERIALS_CARD")
# -*- coding: utf-8 -*-
# Author zfCode
import xlrd
import uuid
import datetime
from OracleHelper import *
h = OracleHelper('dsjky/[email protected]:1521/DSJKY')
data = xlrd.open_workbook(
'D:\公司文档\客运物资管理信息系统\文档\物资物料管理-料库名称对应关系（料库字典中料库名称-物资卡片中料库名称）.xlsx')
print(data.sheets())
table = data.sheets()[4]
# print(len(data.sheets()))
nrows = table.nrows
ncols = table.ncols
print(nrows, ncols)
file_object = open('sql.txt', 'w+')
try:
for i in range(nrows):
# 仓库插入语句
# sql='''
# insert into RAILWAY.KY_JU_LOCATION(LOCATION_ID,LOCATION_NAME,LOCATION_STATION,LOCATION_TYPE,BUSINESS_CATEGORY,ITEM_ATTRIBUTE)
# VALUES ('%s','%s','%s','STOREROOM','keyun','1');
# '''%(str(table.row_values(i)[0]).replace('\xa0','').strip(),str(table.row_values(i)[1]).replace('\xa0','').strip(),
# str(table.row_values(i)[2]).replace('\xa0', '').strip())
# 物资卡片
# sql='''
import OracleHelper as oh
db = oh.OracleHelper()
# sql="select * from person where name like :sname and depart_id = :did"
# paras=[]
# paras.append("%王%")
# paras.append(21)
# queryresult=db.ExecuteSelect(sql,paras)
# for p in queryresult:
# print(f"{p[1]}")
sql = "update TEST_0320CLASSES t set t.classname='五年一班' where t.classid=6"
paras = []
paras.append(7)
paras.append("信息化小组")
dmlcount = db.ExecuteNonSelect(sql)
print(f"删除行数： {dmlcount}")
queryresult = db.ExecuteSelect("select * from TEST_0320CLASSES t ")
for p in queryresult:
for s in p:
print(s)
# -*- coding: utf-8 -*-
# Author zfCode
import HttpHelper
import json
import xlrd
import OracleHelper
# base params
FilePath = 'D:\公司文档\客运物资管理信息系统\各站段基础数据\import.xlsx'
# item in sheet 0
sheet = 6
orcale = OracleHelper.Oracle('mat/[email protected]:1521/MATTEST')
params = {'Action': 'Login', 'username': '******', 'password': '******'}
def ReadExcel():
ExcelData = xlrd.open_workbook(FilePath)
table = ExcelData.sheets()[sheet]
nrows = table.nrows
ncols = table.ncols
print('rows：%s | cols: %s' % (nrows, ncols))
return table
def CreatSql():
pass
def Start():
table = ReadExcel()
def GetData():
'''
从数据库获取用料数据，用料数据-退料数据=接口用料数据
:return:
'''
orcale = OracleHelper.Oracle('***')
sql = "select * from TB_FDN_MATERIALS_USE WHERE YHDW_CODE='DEPT000073' and TIME>to_date('2017-07-20','yyyy-MM-dd') order by TIME"
use_data = orcale.ExecuteData(sql)
print(len(use_data))
for use in use_data:
# print(use[6])
# 获取备品用料详情
detail_sql = '''
select * from (
select max(ID)new_id,MATERIALS_ID,PC,sum(COUNT)COunt from (
select TB_FDN_MATERIALS_DETAIL.*,SUBSTR(Materials_Code,13,8)PC from TB_FDN_MATERIALS_DETAIL
where PARENT_CODE='{0}' and Materials_Type_Code='64002' and Materials_Code not in (
select Materials_Code from TB_FDN_MATERIALS_DETAIL where PARENT_CODE=
(select CODE from TB_FDN_MATERIALS_RETURN where P_ID='{0}'))) group by PC,MATERIALS_ID) A
left join TB_FDN_MATERIALS_CARD on TB_FDN_MATERIALS_CARD.ID=A.MATERIALS_ID
'''.format(use[6])
bp_use_detail_data = orcale.ExecuteData(detail_sql)
if len(bp_use_detail_data) > 0:
for bp in bp_use_detail_data:
# def SetParams(id, num, name, loca, model, count, price, duan, date, peo, zhichu, pc)
param = SetParams(
bp_use_detail_data[0],
bp[6],
bp[5],
use[4], # ck
bp[8],
bp[3],
bp[10],
use[14], # duan
use[1], # date
use[2], # peo
use[13], # zhichu
bp[2])
print(param)
SetData(param, use[6])
# 获取消耗品用料详情
detail_sql = '''select *from(
select MATERIALS_ID,ID as NEW_ID,(NVL(A.COUNT,0)-NVL(B.T_COUNT,0))NEW_COUNT,A.MATERIALS_CODE from (
select TB_FDN_MATERIALS_DETAIL.*,SUBSTR(Materials_Code,13,8)PC from TB_FDN_MATERIALS_DETAIL
where PARENT_CODE='{0}' and Materials_Type_Code='64001')A
left join
(select MATERIALS_CODE,sum(count)T_COUNT from TB_FDN_MATERIALS_DETAIL where PARENT_CODE in
(select CODE from TB_FDN_MATERIALS_RETURN where P_ID='{0}') and Materials_Type_Code='64001'
group by MATERIALS_CODE
)B on B.MATERIALS_CODE=A.MATERIALS_CODE)C
left join TB_FDN_MATERIALS_CARD on TB_FDN_MATERIALS_CARD.ID=C.MATERIALS_ID
'''.format(use[6])
print(detail_sql)
xh_use_detail_data = orcale.ExecuteData(detail_sql)
if len(xh_use_detail_data) > 0:
for xh in xh_use_detail_data:
# def SetParams(id, num, name, loca, model, count, price, duan, date, peo, zhichu, pc)
param = SetParams(
xh[1],
xh[6],
xh[5],
use[4], # ck
xh[8],
xh[2],
xh[10],
use[14], # duan
use[1], # date
use[2], # peo
use[13], # zhichu
xh[3][12:20])
print(param)
SetData(param, use[6])