def Login(qcorderid): #定义接口函数
'''读取表头信息'''
# list_info=[]
# for row in range(0,cols):
# list_info.append(sh.cell_value(0,row))
# #print list_info
# info=[]
# num=0
# info.append(sh.row_values(i))
# text_content=sh.row_values(i)
'''数据库连接查询分类ID'''
warehousesystem_db = 'warehousesystem'
qcid_sql = "select [Id] from [dbo].[InRequest] where [RelatedCode]='" + qcorderid + "'"
qcid = database_api.sql_conn(warehousesystem_db, qcid_sql)
'''数据库连接查询站点ID'''
# site_db='systemmanagementsystem'
purchase_sql = "select [PurchaseId] from [dbo].[InRequest] where [RelatedCode]='" + qcorderid + "'"
purchaseid = database_api.sql_conn(warehousesystem_db, purchase_sql)
#实例化接口对象
login = postRequest("" + login_api.erp_url + "InRequest/ReturnToRequest", {
"id": qcid,
"purchaseId": purchaseid,
"remark": "bbb"
}, "test")
return login.post()
for i in range(1, rows):
text_content = sh.row_values(i)
url_ScanSerialNumber = "" + login_api.pda_url + "ajax/Distribute/ScanSerialNumber"
postdata_ScanSerialNumber = urllib.urlencode(
{"SerialNumber": text_content[1]})
req_ScanSerialNumber = urllib2.Request(url_ScanSerialNumber,
postdata_ScanSerialNumber, header)
print "扫描批次卡返回信息:" + pda.open(req_ScanSerialNumber).read()
db_warehouse = "warehousesystem"
'''获取拣货单id'''
sql_pickingid = "select [Id] from [dbo].[PickingList] where [Code]='" + text_content[
0] + "'"
pickingListId = database_api.sql_conn(db_warehouse, sql_pickingid)
'''获取拣货单对应的SKUID'''
sql_skuids = "select [SkuId] from [dbo].[DistributeSkuList] where [PickingListId]='" + pickingListId + "'"
skuids = database_api.sql_multconn(db_warehouse, sql_skuids)
# skulist=[]
for j in skuids:
skuid = str(j[0])
'''获取SKUID对应的SKUCODE'''
sql_skucode = "select [Code] from [dbo].[Sku] where [Id]='" + skuid + "'"
skucode = database_api.sql_conn(db_warehouse, sql_skucode)
print skuid
url_scansku = "" + login_api.pda_url + "ajax/Distribute/ScanSkuCode"
postdata_scansku = urllib.urlencode({
"SkuCode": skucode,
"PickingListId": pickingListId,
"ShippingOrderID": ""
print "添加包裹：" + caigou_info
pg = pg + 1
'''生成到货单'''
url_daohuo = "" + login_api.erp_url + "Purchase/SubmitToArrivalOrder"
postdata_daohuo = {"orderIdList": cgids}
req_daohuo = login.post(url_daohuo, data=postdata_daohuo)
print "生成到货单：" + req_daohuo.text
'''签收并上架到货单'''
for j in caigouid:
cgid = str(j[0])
# print cgid
text_content = sh.row_values(area)
sql_areaid = "Select [Id] From [dbo].[Area] Where [Name]='" + text_content[
1] + "'"
areaid = database_api.sql_conn(db_warehousesystem, sql_areaid)
sql_daohuoid = "Select [Id] From [dbo].[InRequest] Where [PurchaseId]='" + cgid + "'"
daohuoid = database_api.sql_conn(db_warehousesystem, sql_daohuoid)
# print daohuoid
'''到货单签收包裹'''
url_sign = "" + login_api.erp_url + "InRequest/BatchSignLogisticsPackage"
postdata_sign = {
"requestModel": [{
"PurchaseId": daohuoid,
"listPackgeId": baoguo
}],
"areaId": areaid,
"areaCode": text_content[1]
}
req_sign = urllib2.Request(url=url_sign,
'''根据产品编码查询任务id'''
db_edit = "editingsystem"
sql_Id = "select [Id] from [dbo].[EditingTask] where [EntityCode]='" + text_content[
0] + "'"
editTaskid = database_api.sql_multconn(db_edit, sql_Id)
for taskid in editTaskid:
taskid_str = str(taskid[0])
sql_imageid = "select [Id] from [dbo].[EditingTask_Image] where [Id]='" + taskid_str + "'"
editimage_id = database_api.sql_multconn(db_edit, sql_imageid)
db_package = "systemmanagementsystem"
sql_packageId = "select [Id] from [dbo].[Package] where [Name]='" + text_content[
5] + "'"
packageId = database_api.sql_conn(db_package, sql_packageId)
sql_pgWeight = "select [Weight] from [dbo].[Package] where [Id]='" + packageId + "'"
pgWeight = database_api.sql_conn(db_package, sql_pgWeight)
if editimage_id:
sql_entityId = "select [EntityId] from [dbo].[EditingTask] where [Id]='" + taskid_str + "'"
entityId = database_api.sql_conn(db_edit, sql_entityId)
sql_detailId = "select [Id] from [dbo].[ImageTaskDetail] where [ImageTaskId]='" + taskid_str + "'"
sql_subid = "select [SubEntityId] from [dbo].[ImageTaskDetail] where [ImageTaskId]='" + taskid_str + "'"
imagedetailid = database_api.sql_multconn(db_edit, sql_detailId)
subid = database_api.sql_multconn(db_edit, sql_subid)
for i in range(0, len(subid)):
submitId = str(subid[i][0])
imagetaskdetailid = str(imagedetailid[i][0])
'''产品头图'''
'''读取表头信息'''
list_info = []
for row in range(0, cols):
list_info.append(sh.cell_value(0, row))
#print list_info
info = []
num = 0
for i in range(1, rows):
info.append(sh.row_values(i))
#print info
'''数据库连接查询分类ID'''
category_db = 'productsystem'
category_sql = "select [Id] from [dbo].[Category] where [Name]='" + info[
num][1] + "'"
info[num][1] = database_api.sql_conn(category_db, category_sql)
'''数据库连接查询站点ID'''
site_db = 'systemmanagementsystem'
site_sql = "select [Id] from [dbo].[Site] where [Name]='" + info[num][
4] + "'"
info[num][4] = database_api.sql_conn(site_db, site_sql)
'''数据库连接查询供应商ID'''
supplier_db = 'purchasesystem'
supplier_sql = "select [Id] from [dbo].[Supplier] where [Name]='" + info[
num][13] + "'"
info[num][13] = database_api.sql_conn(supplier_db, supplier_sql)
'''搜索仓库ID'''
db_warehouse = 'systemmanagementsystem'
sql_warehouse = "select [Id] from [dbo].[Warehouse] where [Name]='" + info[
num][26] + "'"
info[num][26] = database_api.sql_conn(db_warehouse, sql_warehouse)
excel=xlrd.open_workbook('./data_config.xls')
sh=excel.sheet_by_name("addpurchase添加采购单")
rows=sh.nrows
for i in range(1,rows):
text_conten=sh.row_values(i)
db_productsystem="productsystem"
product_id=text_conten[0].split(',')
product_num=text_conten[2].split(',')
product_price=text_conten[3].split(',')
details=[]
'''添加新品采购单'''
for j in product_id:
sql_detailId="select [Id] from [dbo].[NewProductDetail] where [Code]='"+j+"'"
detailId=database_api.sql_conn(db_productsystem,sql_detailId)
details.append(detailId)
url_addpurchase=""+login_api.erp_url+"Purchase/BatchGeneratedPurchaseBorrow"
postdata_addpurchase={"warehouseId":"01602662-74ce-4ab8-aabb-3f6c5e5c9f34","generatedPurchase":"true","detailIds":details}
req_add=edit_req.post(url_addpurchase,data=postdata_addpurchase)
print "添加新品采购单返回信息:"+req_add.text
'''保存编辑采购单'''
db_purchase="purchasesystem"
sql_orderid="select [OrderId] from [dbo].[NewProductPurchaseOrderDetail] where [SkuCode]='"+product_id[0]+"'"
orderId=database_api.sql_conn(db_purchase,sql_orderid)
pdetails_list=[]
for num in range(0,len(product_id)):
pdetails_dic={}
for taskid in editTaskid:
taskid_str = str(taskid[0])
sql_textid = "select [Id] from [dbo].[EditingTask_Text] where [Id]='" + taskid_str + "'"
edittext_id = database_api.sql_multconn(db_edit, sql_textid)
sql_physicalid = "select [Id] from [dbo].[EditingTask_Physical] where [Id]='" + taskid_str + "'"
editphysical_id = database_api.sql_multconn(db_edit, sql_physicalid)
sql_imageid = "select [Id] from [dbo].[EditingTask_Image] where [Id]='" + taskid_str + "'"
editimage_id = database_api.sql_multconn(db_edit, sql_imageid)
db_package = "systemmanagementsystem"
sql_packageId = "select [Id] from [dbo].[Package] where [Name]='" + text_content[
5] + "'"
packageId = database_api.sql_conn(db_package, sql_packageId)
sql_pgWeight = "select [Weight] from [dbo].[Package] where [Id]='" + packageId + "'"
pgWeight = database_api.sql_conn(db_package, sql_pgWeight)
'''判断是否为文字编辑ID'''
if edittext_id:
sql_editMeasurement = "select [MustEditMeasurement] from [dbo].[EditingTask_Text] where [Id]='" + taskid_str + "'"
editMeasurement = database_api.sql_multconn(
db_edit, sql_editMeasurement)
'''请求文字编辑连接地址'''
textEdit_url = "" + login_api.erp_url + "Products/UpdateTextTask"
'''判断来源是否需要编辑实物属性'''
#print type(editMeasurement[0][0])
if editMeasurement[0][0]:
sql_entityId = "select [EntityId] from [dbo].[EditingTask] where [Id]='" + taskid_str + "'"
entityId = database_api.sql_conn(db_edit, sql_entityId)
postdata2_edittext = {
db_user = "******"
'''判断订单类型'''
if text_content[0] == "单发":
printType = "0"
'''运输类型ID'''
if text_content[1] == "全部":
sql_shippingIDs = "select [Id] from [dbo].[ShippingMethod] where [WarehouseId]='01602662-74ce-4ab8-aabb-3f6c5e5c9f34' and [Enabled]=1 and ([ShippingType]=1 or [ShippingType]=0)"
shippingIDs = database_api.sql_multconn(db_shippingsystem,
sql_shippingIDs)
else:
shippingIDs = []
shippingType_list = text_content[1].split(',')
for shippingType in shippingType_list:
sql_shippingID = "select [Id] from [dbo].[ShippingMethod] where [WarehouseId]='01602662-74ce-4ab8-aabb-3f6c5e5c9f34' and [Enabled]=1 and ([ShippingType]=1 or [ShippingType]=0) and [Name]='" + shippingType + "'"
shippingID = database_api.sql_conn(db_shippingsystem,
sql_shippingID)
shippingIDs.append(shippingID)
#print shippingIDs
'''拣单数量'''
orderAmount = int(text_content[2])
'''拣货区ID'''
locations_list = text_content[3].split(',')
locationIDs = []
for location in locations_list:
sql_locationID = "select [Id] from [dbo].[Area] where [Name]='" + location + "'"
locationID = database_api.sql_conn(db_warehouse, sql_locationID)
locationIDs.append(locationID)
# print locationIDs
'''拣货区格数ID'''
rowIDs = []
for AreaId in locationIDs:
reload(sys)
sys.setdefaultencoding("utf-8")
'''设置登陆session'''
new = login_api.login_requests()
'''读取excel表格'''
excel = xlrd.open_workbook('./data_config.xls')
sh = excel.sheet_by_name("newproduct新品开发")
rows = sh.nrows
for i in range(1, rows):
text_content = sh.row_values(i)
'''搜索中国仓ID'''
db_warehouse = 'systemmanagementsystem'
sql_warehouse = "select [Id] from [dbo].[Warehouse] where [Name]='中国仓库'"
id_warehouse = database_api.sql_conn(db_warehouse, sql_warehouse)
#print id_warehouse
'''搜索产品id'''
db_newproduct = 'productsystem'
sql_newproduct = "select [Id] from [dbo].[NewProduct] where [WarehouseId]='" + id_warehouse + "' and [Title]='" + text_content[
0] + "' order by [LastModified] desc "
id_newproduct = database_api.sql_conn(db_newproduct, sql_newproduct)
#print id_newproduct
'''发送提审请求'''
check_url = "" + login_api.erp_url + "Purchase/BatchSubmitAuditor"
postdata_check = json.loads('{"idList":["' + id_newproduct + '"]}')
req_check = new.post(check_url, data=postdata_check)
print "提审返回信息:" + req_check.text
'''通过审核'''
pass_url = "" + login_api.erp_url + "Purchase/BatchPass"
postdata_pass = json.loads('{"idList":["' + id_newproduct +
header = {
'Host': login_api.erp_url,
'Connection': ' keep-alive',
'Accept': 'application/json, text/javascript, */*; q=0.01',
'Origin': 'http://192.168.1.220:8881',
'X-Requested-With': 'XMLHttpRequest',
'User-Agent':
' Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.112 Safari/537.36',
'Content-Type': 'application/json',
'Accept-Encoding': 'gzip, deflate',
'Accept-Language': 'zh-CN,zh;q=0.8,en;q=0.6'
}
'''设置登陆'''
edit = login_api.login_urllib()
'''读取excel表格'''
excel = xlrd.open_workbook('./data_config.xls')
sh = excel.sheet_by_name("signpurchase")
rows = sh.nrows
for i in range(1, rows):
text_conten = sh.row_values(i)
db_editingsystem = "editingsystem"
sql_batchid = "select [Id] from [dbo].[EntityReceivingBatch] where [ReferencedNumber]='" + text_conten[
0] + "'"
batchid = database_api.sql_conn(db_editingsystem, sql_batchid)
url_sign = "" + login_api.erp_url + "Products/SignInEntityReceivingBatch"
postdata_sign = {"batchIdList": [batchid], "isSign": "true"}
req_sign = urllib2.Request(url=url_sign,
data=json.dumps(postdata_sign),
headers=header)
print "签收采购单返回信息:" + edit.open(req_sign).read()
edit=login_api.login_urllib()
edit_req=login_api.login_requests()
'''读取excel表格'''
excel=xlrd.open_workbook('./data_config.xls')
sh=excel.sheet_by_name("editproduct产品编辑")
rows=sh.nrows
for i in range(1,rows):
text_content=sh.row_values(i)
'''根据产品编码查询任务id'''
db_edit="editingsystem"
sql_Id="select [Id] from [dbo].[EditingTask] where [EntityCode]='"+text_content[0]+"'"
editTaskid=database_api.sql_multconn(db_edit,sql_Id)
for taskid in editTaskid:
taskid_str=str(taskid[0])
sql_physicalid="select [Id] from [dbo].[EditingTask_Physical] where [Id]='"+taskid_str+"'"
editphysical_id=database_api.sql_multconn(db_edit,sql_physicalid)
db_package="systemmanagementsystem"
sql_packageId="select [Id] from [dbo].[Package] where [Name]='"+text_content[5]+"'"
packageId=database_api.sql_conn(db_package,sql_packageId)
sql_pgWeight="select [Weight] from [dbo].[Package] where [Id]='"+packageId+"'"
pgWeight=database_api.sql_conn(db_package,sql_pgWeight)
if editphysical_id:
editphysical_url=""+login_api.erp_url+"Products/UpdatePhysicalTask"
postdata_editphysical={"taskId":""+taskid_str+"","taskStatus":2,"netWeight":""+text_content[7]+"","length":""+text_content[8]+"","width":""+text_content[9]+"","height":""+text_content[10]+"","grossWeight":""+text_content[6]+"","package":{"id":""+packageId+"","packageWeight":""+pgWeight+"","storageId":"01602662-74ce-4ab8-aabb-3f6c5e5c9f34"}}
req_editphysical=urllib2.Request(url=editphysical_url,data=json.dumps(postdata_editphysical),headers=header)
print "完成实物编辑返回信息:"+edit.open(req_editphysical).read()
'Accept-Language': 'zh-CN,zh;q=0.8,en;q=0.6'
}
'''设置登陆'''
edit = login_api.login_urllib()
'''读取excel表格'''
excel = xlrd.open_workbook('./data_config.xls')
sh = excel.sheet_by_name("submitentity入库")
rows = sh.nrows
for i in range(1, rows):
text_content = sh.row_values(i)
'''查询采购单id'''
db_editingsystem = "editingsystem"
sql_batchid = "select [Id] from [dbo].[EntityReceivingBatch] where [ReferencedNumber]='" + text_content[
0] + "'"
batchid = database_api.sql_conn(db_editingsystem, sql_batchid)
'''查询库管员id'''
db_usersystem = "usersystem"
sql_operatorid = "select [Id] from [dbo].[User] where [Name]='" + text_content[
1] + "'"
operatorid = database_api.sql_conn(db_usersystem, sql_operatorid)
'''提交库管'''
url_submitEntity = "" + login_api.erp_url + "Products/BatchSubmitEntityReceivingBatchToWareHouse"
postdata_submitEntity = {
"batchIdList": [batchid],
"operatorId": operatorid,
"operatorName": text_content[1]
}
req_submitEntity = urllib2.Request(url=url_submitEntity,
data=json.dumps(postdata_submitEntity),
headers=header)
login = login_api.login_requests()
'''读取excel表格'''
excel = xlrd.open_workbook('./data_config.xls')
sh = excel.sheet_by_name("delivery发货")
rows = sh.nrows
for i in range(1, rows):
text_content = sh.row_values(i)
db_warehouse = "warehousesystem"
'''根据发货单发货'''
if text_content[1]:
delivery_num = text_content[1].split(',')
for k in delivery_num:
'''查询发货单ID'''
sql_orderId = "select [Id] from [dbo].[ShippingOrder] where [Code]='" + k + "'"
orderId = database_api.sql_conn(db_warehouse, sql_orderId)
'''查询重量'''
sql_weight = "select [Weight] from [dbo].[ShippingOrder] where [Code]='" + k + "'"
weight = database_api.sql_conn(db_warehouse, sql_weight)
'''打印发货'''
url_print = "" + login_api.erp_url + "ShippingOrder/GetOrderForPrint"
postdata_print = {"orderIds": [orderId]}
req_print = login.post(url_print, data=postdata_print)
print "打印发货返回信息:" + req_print.text
'''发货'''
url_delivery = "" + login_api.erp_url + "ShippingOrder/ShippingOrderDelivery"
postdata_delivery = {"orderIds": [orderId], "weight": weight}
req_delivery = login.post(url_delivery, data=postdata_delivery)
print "发货返回信息:" + req_delivery.text
print "\n"
