from django.shortcuts import render # Create your views here. import json import re from django import http from django.shortcuts import render # Create your views here. from django.views import View from django.conf import settings from django.db import connection, transaction import datetime from utils.cust_data import customer_data from utils.et_CACC_NO import et_CACC_NO from utils.examine_ood import examine_ood from utils.if_account import if_account from utils.usr_data import usr_data from utils.monthly_odd import monthly_odd from utils.executeQuery import executeQuery # 进货单 class PcOrder(View): @transaction.atomic def get(self, request): context = { 'a': '进货单' } #HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码) return http.JsonResponse(context) @transaction.atomic def post(self, request): # 1获取参数 req_data = json.loads(request.body.decode()) PS_DD = req_data.get("PS_DD") # 单据日期 CUS_NO = req_data.get("CUS_NO") # 客户编码 CUS_NO_NAME = req_data.get("CUS_NO_NAME",'') # 客户名称 PS_NO = req_data.get("PS_NO") # 单据号码 TAX_ID = req_data.get("TAX_ID") # 扣税类别 ZHANG_ID = req_data.get("ZHANG_ID") # 立账方式 VOH_ID = req_data.get("VOH_ID",'') # 凭证模板 USR = req_data.get("USR") # 制单人编码 USR_NAME = req_data.get("USR_NAME",'') # 制单人名称 # TAX_RTO = req_data.get("TAX_RTO") # 税率 直接取货品资料里面的 AMT = req_data.get("AMT",0) # 金额 DEP = req_data.get("DEP",'') # 部门代号 SAL_NO = req_data.get("SAL_NO",'') # 业务员代号 KPF = req_data.get("KPF", '') # 开票否 # # print(PS_DD) # print(CUS_NO) # print(CUS_NO_NAME) # print(PS_NO) # print(TAX_ID) # print(ZHANG_ID) # print(VOH_ID) # print(USR) # print(USR_NAME) # # print(TAX_RTO) # print(AMT) # print(DEP) # print(SAL_NO) # 2校验参数 # 校验日期格式 # 判断是否有输入单号 if PS_DD is None: return http.HttpResponseForbidden('请输入进货日期') # 判断是否有输入单号 if PS_NO is None: return http.HttpResponseForbidden('请输入进货单号') if PS_NO[:3] != 'PCA' or len(PS_NO) != 11: #判断单号是否合法 return http.HttpResponseForbidden('进货单号输入不合法，单号必须是PCA开头且长度必须是11位') #判断金额是否输入正确 if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMT)) is None: return http.HttpResponseForbidden('金额输入不正确') # 判断是否有输入供应商 if CUS_NO is None: return http.HttpResponseForbidden('请输入供应商') if TAX_ID is None: return http.HttpResponseForbidden('请输入扣税类别') # 判断扣税类别是否输入正确 if int(TAX_ID) != 1 and int(TAX_ID) != 2 and int(TAX_ID) != 3: return http.HttpResponseForbidden('扣税类别只能是1（不计税）,2（应该税内行），3（应税外加）') if ZHANG_ID is None: return http.HttpResponseForbidden('请输入立账方式') # 判断立账方式是否输入正确 if int(ZHANG_ID) != 1 and int(ZHANG_ID) != 2 and int(ZHANG_ID) != 3: return http.HttpResponseForbidden('立账方式只能是1（单张立账）,2（不立账），3（收到发票才立账）') # #判断凭证模板是否输入 # if VOH_ID is None: # return http.HttpResponseForbidden('请输入凭证模板') # 判断制单人是否输入 if USR is None: return http.HttpResponseForbidden('请输入制单人') # 判断税率是否输入 # if TAX_RTO is None: # return http.HttpResponseForbidden('请输入税率') # 判断进货单是否存在 row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'PC') if row_PS_NO > 0: return http.HttpResponseForbidden('进货单号已存在') # 判断供应商是否哦存在，不存在的话创建 try: row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '2', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),0) except Exception: return http.HttpResponseForbidden('创建厂商失败') # 判断用户是否存在 # row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d')) try: # 判断用户是否存在，不存在则创建 row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d')) except Exception: return http.HttpResponseForbidden('创建用户失败') #判断单号是否存在 #判断供应商是否存在 #判断凭证模板是否存在--------------------------------------------待完成 #判断字段制单人是否存在 # 3数入库 with connection.cursor() as cursor: # 单张立账或者不立账需要判断凭证模板是否存在，直接判断立账方式不等于3即可 row_usr=0 try: if VOH_ID !=0: row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='PC'", [VOH_ID]).fetchall()[0][0] except Exception: return http.HttpResponseForbidden('查询凭证模板异常') if row_usr <= 0: return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板') # 获取税率 try: PRDT = cursor.execute("SELECT ISNULL(SPC_TAX,0),[NAME] FROM PRDT WHERE PRD_NO='AQ001'").fetchall()[0] SPC_TAX=PRDT[0] PRD_NAME=PRDT[1] except Exception: return http.HttpResponseForbidden('获取安装费税率异常') # 计算本位币 AMTN_NET：本位币， TAX：税金 ，SPC_TAX：税率 TAX=0 AMTN_NET=0 if int(TAX_ID)==1: TAX=0 AMTN_NET = AMT if int(TAX_ID)==2: print(SPC_TAX) TAX = float(AMT)/(1+float(SPC_TAX)/100)*float(SPC_TAX)/100 AMTN_NET = float(AMT) - TAX if int(TAX_ID)==3: TAX = float(AMT) / 100 * float(SPC_TAX) AMTN_NET = float(AMT) sid = transaction.savepoint() # 开启事物 try: # 插入进货单表头-----------------------改CUR_ID RMB if int(ZHANG_ID) == 1: cursor.execute("""INSERT INTO MF_PSS(PS_ID,PS_NO,PS_DD,CUS_NO,ZHANG_ID,USR,CHK_MAN,CLS_DATE,SYS_DATE,EXC_RTO,LZ_CLS_ID,CLSLZ,TAX_ID,ARP_NO,VOH_ID,DEP,SAL_NO) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", ['PC', 'PC' + PS_NO[2::], datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), CUS_NO, ZHANG_ID, USR, USR, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 1, 'F', '', TAX_ID, 'AP' + PS_NO[2::], VOH_ID, DEP, SAL_NO]) # 插入自定义栏位表 if KPF=='T': cursor.execute("""INSERT INTO MF_PSS_Z(PS_ID,PS_NO,KPF)VALUES(%s,%s,%s)""",['PC','PC' + PS_NO[2::],'T']) # 插入立账单MF_MRP------------------改CUR_ID RMB cursor.execute("""INSERT INTO MF_ARP(ARP_ID,OPN_ID,ARP_NO,BIL_NO,CUS_NO,PAY_DD,AMT,AMTN,AMTN_NET,EXC_RTO,CLOSE_ID,BIL_ID,SYS_DATE,BIL_DD,DEP,ZHANG_ID)VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", [2, 2, 'AP' + PS_NO[2::], 'PC' + PS_NO[2::], CUS_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0, AMT, AMTN_NET, 1, 'F', 'PC', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), DEP, ZHANG_ID]) if int(ZHANG_ID) !=1: print(3) cursor.execute("""INSERT INTO MF_PSS(PS_ID,PS_NO,PS_DD,CUS_NO,ZHANG_ID,USR,CHK_MAN,CLS_DATE,SYS_DATE,EXC_RTO,LZ_CLS_ID,CLSLZ,TAX_ID,ARP_NO,VOH_ID,DEP,SAL_NO) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", ['PC', 'PC' + PS_NO[2::], datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), CUS_NO, ZHANG_ID, USR, USR, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 1, 'F', '', TAX_ID, '', VOH_ID, DEP, SAL_NO]) # 插入进货单表身 print(4) cursor.execute("""INSERT INTO TF_PSS(PS_ID,PS_NO,PS_DD,WH,PRD_NO,QTY,UP,AMT,AMTN_NET,TAX_RTO,ITM,UNIT,CSTN_SAL,PRE_ITM,TAX,EST_ITM,PRD_NAME) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", ['PC', 'PC' + PS_NO[2::] , datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), '0000', 'AQ001', 1, AMT, AMT, AMTN_NET, SPC_TAX, 1, 1, AMTN_NET, 1, TAX, 1, PRD_NAME ]) except Exception: transaction.savepoint_rollback(sid) # 语句执行错误，进行语句回滚 return http.HttpResponseForbidden("新增进货单sql语句执行错误") transaction.savepoint_commit(sid) # 提交事物 # { # "PS_DD": "2019-12-09", # "CUS_NO": "AQ1111", # "CUS_NO_NAME": "天心客户", # "PS_NO": "PCA19C09001", # "TAX_ID": "1", # "ZHANG_ID": "1", # "VOH_ID": "01", # "USR": "a00001", # "USR_NAME": "楠楠", # "AMT": "200", # "DEP": "0000", # "SAL_NO": "A00002", # "user": "123", # "password": "123" # } context = { "errmsg": '新增进货单成功', "code":200 } # HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码) return http.JsonResponse(context) # return http.JsonResponse({"code": RET.OK, "errmsg": "ok"}) @transaction.atomic def put(self, request): # 1获取参数 req_data = json.loads(request.body.decode()) PS_DD = req_data.get("PS_DD") # 单据日期 CUS_NO = req_data.get("CUS_NO") # 客户编码 CUS_NO_NAME = req_data.get("CUS_NO_NAME",'') # 客户名称 PS_NO = req_data.get("PS_NO") # 单据号码 TAX_ID = req_data.get("TAX_ID") # 扣税类别 ZHANG_ID = req_data.get("ZHANG_ID") # 立账方式 VOH_ID = req_data.get("VOH_ID") # 凭证模板 # TAX_RTO = req_data.get("TAX_RTO") # 税率 直接取货品资料里面的 AMT = req_data.get("AMT",0) # 金额 DEP = req_data.get("DEP",'') # 部门代号 SAL_NO = req_data.get("SAL_NO",'') # 业务员代号 KPF = req_data.get("KPF", '') # 开票否 # { # "PS_DD": "2019-12-09", # "CUS_NO": "AQ1111", # "CUS_NO_NAME": "天心客户", # "PS_NO": "PCA19C09001", # "TAX_ID": "1", # "ZHANG_ID": "1", # "VOH_ID": "01", # "USR": "a00001", # "USR_NAME": "楠楠", # "AMT": "200", # "DEP": "0000", # "SAL_NO": "A00002", # "user": "123", # "password": "123" # } # 2校验参数 # 判断是否有输入单号 if PS_DD is None: return http.HttpResponseForbidden('请输入进货日期') # 校验日期格式 try: datetime.datetime.strptime(PS_DD, '%Y-%m-%d') except Exception: return http.HttpResponseForbidden('日期格式输入不正确') # 判断是否有输入单号 if PS_NO is None: return http.HttpResponseForbidden('请输入进货单号') if PS_NO[:3] != 'PCA' or len(PS_NO) != 11: # 判断单号是否合法 return http.HttpResponseForbidden('进货单号输入不合法，单号必须是PCA开头且长度必须为11位') # 判断金额是否输入正确 if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMT)) is None: return http.HttpResponseForbidden('金额输入不正确') # 判断是否有输入供应商 if CUS_NO is None: return http.HttpResponseForbidden('请输入供应商') if TAX_ID is None: return http.HttpResponseForbidden('请输入扣税类别') # 判断扣税类别是否输入正确 if int(TAX_ID) != 1 and int(TAX_ID) != 2 and int(TAX_ID) != 3: return http.HttpResponseForbidden('扣税类别只能是1（不计税）,2（应该税内行），3（应税外加）') if ZHANG_ID is None: return http.HttpResponseForbidden('请输入立账方式') # 判断立账方式是否输入正确 if int(ZHANG_ID) != 1 and int(ZHANG_ID) != 2 and int(ZHANG_ID) != 3: return http.HttpResponseForbidden('立账方式只能是1（单张立账）,2（不立账），3（收到发票才立账）') # # 判断凭证模板是否输入 # if VOH_ID is None: # return http.HttpResponseForbidden('请输入凭证模板') # 判断进货单是否存在 row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'PC') if row_PS_NO <= 0: return http.HttpResponseForbidden('进货单号不存在') # 判断供应商是否哦存在，不存在的话创建 try: row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '2', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0) except Exception: return http.HttpResponseForbidden('创建厂商失败') #判断进货单是否产生后续单据===========待完成 # 3数入库 with connection.cursor() as cursor: # 判断单号是否产生后续单据，立账里面的一冲金额 try: # 获取原来单据的立账方式 ZHANG_ID_OLD = cursor.execute("SELECT ZHANG_ID FROM MF_PSS WHERE PS_ID='PC' AND PS_NO=%s",['PC' + PS_NO[2::]]).fetchall()[0][0] if int(ZHANG_ID_OLD)==1: print('AP' + PS_NO[2::]) row_no = cursor.execute("SELECT ISNULL(AMTN_RCV,0) FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='PC'", ['AP' + PS_NO[2::]]).fetchall()[0][0] if float(row_no)>0 and row_no is not None: return http.HttpResponseForbidden('进货单已产生后续单据不允许修改') if int(ZHANG_ID_OLD) != 1: row_no = cursor.execute("SELECT ISNULL(ACC_FP_NO,'') FROM MF_PSS WHERE PS_NO=%s AND PS_ID='PC'",['PC' + PS_NO[2::]]).fetchall()[0][0] if row_no!='': return http.HttpResponseForbidden('进货单已产生后续单据不允许修改') except Exception: return http.HttpResponseForbidden('判断进货单是否产生后续单据异常') # 判断传过来的凭证模板在数据库里面是否存在 row_usr=0 try: if VOH_ID !=0: row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='PC'", [VOH_ID]).fetchall()[0][0] except Exception: return http.HttpResponseForbidden('查询凭证模板异常') if row_usr <= 0: return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板') # 获取税率 try: PRDT = cursor.execute("SELECT ISNULL(SPC_TAX,0),[NAME] FROM PRDT WHERE PRD_NO='AQ001'").fetchall()[0] SPC_TAX = PRDT[0] PRD_NAME = PRDT[1] except Exception: return http.HttpResponseForbidden('获取安装费税率异常') # 计算本位币 AMTN_NET：本位币， TAX：税金 ，SPC_TAX：税率 TAX = 0 AMTN_NET = 0 if int(TAX_ID) == 1: TAX = 0 AMTN_NET = AMT if int(TAX_ID) == 2: print(SPC_TAX) TAX = float(AMT) / (1 + float(SPC_TAX) / 100) * float(SPC_TAX) / 100 AMTN_NET = float(AMT) - TAX if int(TAX_ID) == 3: TAX = float(AMT) / 100 * float(SPC_TAX) AMTN_NET = float(AMT) sid = transaction.savepoint() # 开启事物 try: # 插入进货单表头-----------------------改CUR_ID RMB # 修改自定义栏位表 if KPF == 'T': cursor.execute("""UPDATE MF_PSS_Z SET KPF=%s WHERE PS_ID='PC' AND PS_NO=%s""",['T','PC' + PS_NO[2::]]) if KPF == 'F': cursor.execute("""UPDATE MF_PSS_Z SET KPF=%s WHERE PS_ID='PC' AND PS_NO=%s""",['F','PC' + PS_NO[2::]]) if int(ZHANG_ID) == 1: print(1) cursor.execute("""UPDATE MF_PSS SET PS_DD=%s,CUS_NO=%s,ZHANG_ID=%s,TAX_ID=%s,VOH_ID=%s,DEP=%s,SAL_NO=%s,MODIFY_DD=%s,ARP_NO=%s WHERE PS_ID='PC' AND PS_NO=%s """, [ datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), CUS_NO, ZHANG_ID, TAX_ID, VOH_ID, DEP, SAL_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 'AP' + PS_NO[2::], 'PC' + PS_NO[2::] ]) print(2) # 如果旧的立账方式是1的话那么会有立账单，那么可以直接修改 print(DEP) print(ZHANG_ID) if int(ZHANG_ID_OLD)==1: cursor.execute("""UPDATE MF_ARP SET CUS_NO=%s,PAY_DD=%s,AMTN=%s,AMTN_NET=%s,BIL_DD=%s,DEP=%s,ZHANG_ID=%s WHERE BIL_ID='PC' AND ARP_NO=%s""", [CUS_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), AMT, AMTN_NET, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), DEP, ZHANG_ID, 'AP' + PS_NO[2::] ]) #如果旧是立账方式四不为1，然后改为1那么系统会在生成一张立账单 if int(ZHANG_ID_OLD) != 1: cursor.execute("""INSERT INTO MF_ARP(ARP_ID,OPN_ID,ARP_NO,BIL_NO,CUS_NO,PAY_DD,AMT,AMTN,AMTN_NET,EXC_RTO,CLOSE_ID,BIL_ID,SYS_DATE,BIL_DD,DEP,ZHANG_ID)VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", [2, 2, 'AP' + PS_NO[2::], 'PC' + PS_NO[2::], CUS_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0, AMT, AMTN_NET, 1, 'F', 'PC', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), DEP, ZHANG_ID]) if int(ZHANG_ID) != 1: print(3) cursor.execute( """UPDATE MF_PSS SET PS_DD=%s,CUS_NO=%s,ZHANG_ID=%s,TAX_ID=%s,VOH_ID=%s,DEP=%s,SAL_NO=%s,MODIFY_DD=%s,ARP_NO=%s WHERE PS_ID='PC' AND PS_NO=%s """, [ datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), CUS_NO, ZHANG_ID, TAX_ID, VOH_ID, DEP, SAL_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), '', 'PC' + PS_NO[2::] ]) # 如果立账方式不为1的时候需要删除，立账单 cursor.execute( """DELETE FROM MF_ARP WHERE BIL_ID='PC' AND ARP_NO=%s""", [ 'AP' + PS_NO[2::] ]) # 插入进货单表身 print(4) cursor.execute("""UPDATE TF_PSS SET PS_DD=%s,UP=%s,AMT=%s,AMTN_NET=%s,CSTN_SAL=%s,TAX=%s,TAX_RTO=%s WHERE PS_ID='PC' AND PS_NO=%s""", [datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), AMT, AMT, AMTN_NET, AMTN_NET, TAX, SPC_TAX, 'PC' + PS_NO[2::] ]) except Exception: transaction.savepoint_rollback(sid) # 语句执行错误，进行语句回滚 return http.HttpResponseForbidden("修改进货单sql语句执行错误") transaction.savepoint_commit(sid) # 提交事物 context = { "errmsg": '修改进货单成功', "code": 200 } return http.JsonResponse(context) @transaction.atomic def delete(self, request): # 1获取参数 req_data = json.loads(request.body.decode()) PS_NO = req_data.get("PS_NO") # 单据日期 # { # "PS_NO": "PCA19C09001", # "user": "123", # "password": "123" # } # 判断是否有输入单号 if PS_NO is None: return http.HttpResponseForbidden('请输入进货单号') if PS_NO[:3] != 'PCA' or len(PS_NO) != 11: # 判断单号是否合法 return http.HttpResponseForbidden('进货单号输入不合法，单号必须是PCA开头且长度必须为11位') # 判断进货单是否存在 row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'PC') if row_PS_NO <= 0: return http.HttpResponseForbidden('进货单号不存在') # 3数入库 with connection.cursor() as cursor: # 判断单号是否产生后续单据，立账里面的一冲金额 try: # 获取原来单据的立账方式 ZHANG_ID_OLD = cursor.execute("SELECT ZHANG_ID FROM MF_PSS WHERE PS_ID='PC' AND PS_NO=%s", ['PC' + PS_NO[2::]]).fetchall()[0][0] if int(ZHANG_ID_OLD) == 1: print('AP' + PS_NO[2::]) row_no = cursor.execute("SELECT ISNULL(AMTN_RCV,0) FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='PC'", ['AP' + PS_NO[2::]]).fetchall()[0][0] if float(row_no) > 0 and row_no is not None: return http.HttpResponseForbidden('进货单已产生后续单据不允许删除') if int(ZHANG_ID_OLD) != 1: row_no = cursor.execute("SELECT ISNULL(ACC_FP_NO,'') FROM MF_PSS WHERE PS_NO=%s AND PS_ID='PC'", ['PC' + PS_NO[2::]]).fetchall()[0][0] if row_no != '': return http.HttpResponseForbidden('进货单已产生后续单据不允许删除') except Exception: return http.HttpResponseForbidden('判断进货单是否产生后续单据异常') sid = transaction.savepoint() # 开启事物 try: cursor.execute("""DELETE FROM MF_PSS WHERE PS_NO=%s AND PS_ID='PC'""", ['PC' + PS_NO[2::]]) cursor.execute("""DELETE FROM TF_PSS WHERE PS_NO=%s AND PS_ID='PC'""", ['PC' + PS_NO[2::]]) cursor.execute("""DELETE FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='PC'""", ['AP' + PS_NO[2::]]) except Exception: transaction.savepoint_rollback(sid) # 语句执行错误，进行语句回滚 return http.HttpResponseForbidden("删除进货单失败") transaction.savepoint_commit(sid) # 提交事物 context = { "errmsg": '删除进货单成功', "code": 200 } return http.JsonResponse(context) # 销货单 class SaOrder(View): @transaction.atomic def get(self, request): context = { 'a': '销货单' } # HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码) return http.JsonResponse(context) @transaction.atomic def post(self, request): # 1获取参数 req_data = json.loads(request.body.decode()) PS_DD = req_data.get("PS_DD") # 单据日期 CUS_NO = req_data.get("CUS_NO") # 客户编码 CUS_NO_NAME = req_data.get("CUS_NO_NAME", '') # 客户名称 PS_NO = req_data.get("PS_NO") # 单据号码 TAX_ID = req_data.get("TAX_ID") # 扣税类别 ZHANG_ID = req_data.get("ZHANG_ID") # 立账方式 VOH_ID = req_data.get("VOH_ID", '') # 凭证模板 USR = req_data.get("USR") # 制单人编码 USR_NAME = req_data.get("USR_NAME", '') # 制单人名称 # TAX_RTO = req_data.get("TAX_RTO") # 税率 直接取货品资料里面的 AMT = req_data.get("AMT", 0) # 金额 DEP = req_data.get("DEP", '') # 部门代号 SAL_NO = req_data.get("SAL_NO", '') # 业务员代号 # # print(PS_DD) # print(CUS_NO) # print(CUS_NO_NAME) # print(PS_NO) # print(TAX_ID) # print(ZHANG_ID) # print(VOH_ID) # print(USR) # print(USR_NAME) # # print(TAX_RTO) # print(AMT) # print(DEP) # print(SAL_NO) # 2校验参数 # 校验日期格式 # { # "PS_DD": "2019-12-09", # "CUS_NO": "AQ2222", # "CUS_NO_NAME": "收款", # "PS_NO": "SAB19C09001", # "TAX_ID": "1", # "ZHANG_ID": "1", # "VOH_ID": "01", # "USR": "a00002", # "USR_NAME": "楠楠", # "AMT": "200", # "DEP": "0000", # "SAL_NO": "A00002", # "user": "123", # "password": "123" # } if PS_DD is None: return http.HttpResponseForbidden('请输入日期') try: datetime.datetime.strptime(PS_DD, '%Y-%m-%d') except Exception: return http.HttpResponseForbidden('日期格式输入不正确') # 判断是否有输入单号 if PS_NO is None: return http.HttpResponseForbidden('请输入销货单号') if PS_NO[:3] != 'SAB' or len(PS_NO) != 11: # 判断单号是否合法 return http.HttpResponseForbidden('销货单号输入不合法，单号必须是SAB开头且长度必须为11位') # 判断金额是否输入正确 if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMT)) is None: return http.HttpResponseForbidden('金额输入不正确') # 判断是否有输入供应商 if CUS_NO is None: return http.HttpResponseForbidden('请输入客户') if TAX_ID is None: return http.HttpResponseForbidden('请输入扣税类别') # 判断扣税类别是否输入正确 if int(TAX_ID) != 1 and int(TAX_ID) != 2 and int(TAX_ID) != 3: return http.HttpResponseForbidden('扣税类别只能是1（不计税）,2（应该税内行），3（应税外加）') if ZHANG_ID is None: return http.HttpResponseForbidden('请输入立账方式') # 判断立账方式是否输入正确 if int(ZHANG_ID) != 1 and int(ZHANG_ID) != 2 and int(ZHANG_ID) != 3: return http.HttpResponseForbidden('立账方式只能是1（单张立账）,2（不立账），3（收到发票才立账）') # #判断凭证模板是否输入 # if VOH_ID is None: # return http.HttpResponseForbidden('请输入凭证模板') # 判断制单人是否输入 if USR is None: return http.HttpResponseForbidden('请输入制单人') # 判断税率是否输入 # if TAX_RTO is None: # return http.HttpResponseForbidden('请输入税率') # 判断进货单是否存在 row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'SA') if row_PS_NO > 0: return http.HttpResponseForbidden('销货单号已存在') # 判断供应商是否哦存在，不存在的话创建 try: row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '1', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0) except Exception: return http.HttpResponseForbidden('创建客户失败') # 判断用户是否存在 # row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d')) try: # 判断用户是否存在，不存在则创建 row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d')) except Exception: return http.HttpResponseForbidden('创建用户失败') # 判断单号是否存在 # 判断供应商是否存在 # 判断凭证模板是否存在--------------------------------------------待完成 # 判断字段制单人是否存在 # 3数入库 with connection.cursor() as cursor: # 单张立账或者不立账需要判断凭证模板是否存在，直接判断立账方式不等于3即可 row_usr = 0 try: if VOH_ID != 0: row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='SA'", [VOH_ID]).fetchall()[0][0] except Exception: return http.HttpResponseForbidden('查询凭证模板异常') if row_usr <= 0: return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板') # 获取税率 try: PRDT = cursor.execute("SELECT ISNULL(SPC_TAX,0),[NAME] FROM PRDT WHERE PRD_NO='AQ001'").fetchall()[0] SPC_TAX = PRDT[0] PRD_NAME = PRDT[1] except Exception: return http.HttpResponseForbidden('获取安装费税率异常') # 计算本位币 AMTN_NET：本位币， TAX：税金 ，SPC_TAX：税率 TAX = 0 AMTN_NET = 0 if int(TAX_ID) == 1: TAX = 0 AMTN_NET = AMT if int(TAX_ID) == 2: print(SPC_TAX) TAX = float(AMT) / (1 + float(SPC_TAX) / 100) * float(SPC_TAX) / 100 AMTN_NET = float(AMT) - TAX if int(TAX_ID) == 3: TAX = float(AMT) / 100 * float(SPC_TAX) AMTN_NET = float(AMT) sid = transaction.savepoint() # 开启事物 try: # 插入销货单表头-----------------------改CUR_ID RMB if int(ZHANG_ID) == 1: cursor.execute("""INSERT INTO MF_PSS(PS_ID,PS_NO,PS_DD,CUS_NO,ZHANG_ID,USR,CHK_MAN,CLS_DATE,SYS_DATE,EXC_RTO,LZ_CLS_ID,CLSLZ,TAX_ID,ARP_NO,VOH_ID,DEP,SAL_NO,PRT_SW,YD_ID) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", ['SA', 'SA' + PS_NO[2::], datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), CUS_NO, ZHANG_ID, USR, USR, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 1, 'F', '', TAX_ID, 'AP' + PS_NO[2::], VOH_ID, DEP, SAL_NO, 'N', 'T' ]) # 插入立账单MF_MRP------------------改CUR_ID RMB print(222222222) cursor.execute("""INSERT INTO MF_ARP(ARP_ID,OPN_ID,ARP_NO,BIL_NO,CUS_NO,PAY_DD,AMT,AMTN,AMTN_NET,EXC_RTO,CLOSE_ID,BIL_ID,SYS_DATE,BIL_DD,DEP,ZHANG_ID)VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", [1, 2, 'AP' + PS_NO[2::], 'SA' + PS_NO[2::], CUS_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0, AMT, AMTN_NET, 1, 'F', 'SA', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), DEP, ZHANG_ID]) if int(ZHANG_ID) != 1: print(3) cursor.execute("""INSERT INTO MF_PSS(PS_ID,PS_NO,PS_DD,CUS_NO,ZHANG_ID,USR,CHK_MAN,CLS_DATE,SYS_DATE,EXC_RTO,LZ_CLS_ID,CLSLZ,TAX_ID,ARP_NO,VOH_ID,DEP,SAL_NO,PRT_SW,YD_ID) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", ['SA', 'SA' + PS_NO[2::], datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), CUS_NO, ZHANG_ID, USR, USR, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 1, 'F', '', TAX_ID, '', VOH_ID, DEP, SAL_NO, 'N', 'T' ]) # 插入进货单表身 print(4) cursor.execute("""INSERT INTO TF_PSS(PS_ID,PS_NO,PS_DD,WH,PRD_NO,QTY,UP,AMT,AMTN_NET,TAX_RTO,ITM,UNIT,CSTN_SAL,PRE_ITM,TAX,EST_ITM,PRD_NAME) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", ['SA', 'SA' + PS_NO[2::], datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), '0000', 'AQ002', 1, AMT, AMT, AMTN_NET, SPC_TAX, 1, 1, AMTN_NET, 1, TAX, 1, PRD_NAME]) except Exception: transaction.savepoint_rollback(sid) # 语句执行错误，进行语句回滚 return http.HttpResponseForbidden("新增销货单sql语句执行错误") transaction.savepoint_commit(sid) # 提交事物 context = { "errmsg": '新增销货单成功', "code": 200 } # HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码) return http.JsonResponse(context) # return http.JsonResponse({"code": RET.OK, "errmsg": "ok"}) @transaction.atomic def put(self, request): # 1获取参数 req_data = json.loads(request.body.decode()) PS_DD = req_data.get("PS_DD") # 单据日期 CUS_NO = req_data.get("CUS_NO") # 客户编码 CUS_NO_NAME = req_data.get("CUS_NO_NAME", '') # 客户名称 PS_NO = req_data.get("PS_NO") # 单据号码 TAX_ID = req_data.get("TAX_ID") # 扣税类别 ZHANG_ID = req_data.get("ZHANG_ID") # 立账方式 VOH_ID = req_data.get("VOH_ID") # 凭证模板 # TAX_RTO = req_data.get("TAX_RTO") # 税率 直接取货品资料里面的 AMT = req_data.get("AMT", 0) # 金额 DEP = req_data.get("DEP", '') # 部门代号 SAL_NO = req_data.get("SAL_NO", '') # 业务员代号 # { # "PS_DD": "2019-12-09", # "CUS_NO": "AQ2222", # "CUS_NO_NAME": "收款", # "PS_NO": "SAB19C09001", # "TAX_ID": "3", # "ZHANG_ID": "3", # "VOH_ID": "01", # "USR": "a00002", # "USR_NAME": "楠楠", # "AMT": "200", # "DEP": "0000", # "SAL_NO": "A00002", # "user": "123", # "password": "123" # } # 2校验参数 # 校验日期格式 if PS_DD is None: return http.HttpResponseForbidden('请输入日期') try: datetime.datetime.strptime(PS_DD, '%Y-%m-%d') except Exception: return http.HttpResponseForbidden('日期格式输入不正确') # 判断是否有输入单号 if PS_NO is None: return http.HttpResponseForbidden('请输入销货单号') if PS_NO[:3] != 'SAB' or len(PS_NO) != 11: # 判断单号是否合法 return http.HttpResponseForbidden('销货单号输入不合法，单号必须是SAB开头且长度必须为11位') # 判断金额是否输入正确 if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMT)) is None: return http.HttpResponseForbidden('金额输入不正确') # 判断是否有输入供应商 if CUS_NO is None: return http.HttpResponseForbidden('请输入客户') if TAX_ID is None: return http.HttpResponseForbidden('请输入扣税类别') # 判断扣税类别是否输入正确 if int(TAX_ID) != 1 and int(TAX_ID) != 2 and int(TAX_ID) != 3: return http.HttpResponseForbidden('扣税类别只能是1（不计税）,2（应该税内行），3（应税外加）') if ZHANG_ID is None: return http.HttpResponseForbidden('请输入立账方式') # 判断立账方式是否输入正确 if int(ZHANG_ID) != 1 and int(ZHANG_ID) != 2 and int(ZHANG_ID) != 3: return http.HttpResponseForbidden('立账方式只能是1（单张立账）,2（不立账），3（收到发票才立账）') # # 判断凭证模板是否输入 # if VOH_ID is None: # return http.HttpResponseForbidden('请输入凭证模板') # 判断进货单是否存在 row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'SA') if row_PS_NO <= 0: return http.HttpResponseForbidden('销货单号不存在') # 判断供应商是否哦存在，不存在的话创建 try: row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '2', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0) except Exception: return http.HttpResponseForbidden('创建客户失败') # 判断进货单是否产生后续单据===========待完成 # 3数入库 with connection.cursor() as cursor: # 判断单号是否产生后续单据，立账里面的一冲金额 try: # 获取原来单据的立账方式 ZHANG_ID_OLD = cursor.execute("SELECT ZHANG_ID FROM MF_PSS WHERE PS_ID='SA' AND PS_NO=%s", ['SA' + PS_NO[2::]]).fetchall()[0][0] print('AP' + PS_NO[2::]) if int(ZHANG_ID_OLD) == 1: print('AP' + PS_NO[2::]) row_no = cursor.execute("SELECT ISNULL(AMTN_RCV,0) FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='SA'", ['AP' + PS_NO[2::]]).fetchall()[0][0] if float(row_no) > 0 and row_no is not None: return http.HttpResponseForbidden('销货单已产生后续单据不允许修改') if int(ZHANG_ID_OLD) != 1: row_no = cursor.execute("SELECT ISNULL(ACC_FP_NO,'') FROM MF_PSS WHERE PS_NO=%s AND PS_ID='SA'", ['SA' + PS_NO[2::]]).fetchall()[0][0] if row_no != '': return http.HttpResponseForbidden('销货单已产生后续单据不允许修改') except Exception: return http.HttpResponseForbidden('判断销货单是否产生后续单据异常') # 判断传过来的凭证模板在数据库里面是否存在 row_usr = 0 try: if VOH_ID != 0: row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='PC'", [VOH_ID]).fetchall()[0][0] except Exception: return http.HttpResponseForbidden('查询凭证模板异常') if row_usr <= 0: return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板') # 获取税率 try: PRDT = cursor.execute("SELECT ISNULL(SPC_TAX,0),[NAME] FROM PRDT WHERE PRD_NO='AQ001'").fetchall()[0] SPC_TAX = PRDT[0] PRD_NAME = PRDT[1] except Exception: return http.HttpResponseForbidden('获取安装费税率异常') # 计算本位币 AMTN_NET：本位币， TAX：税金 ，SPC_TAX：税率 TAX = 0 AMTN_NET = 0 if int(TAX_ID) == 1: TAX = 0 AMTN_NET = AMT if int(TAX_ID) == 2: print(SPC_TAX) TAX = float(AMT) / (1 + float(SPC_TAX) / 100) * float(SPC_TAX) / 100 AMTN_NET = float(AMT) - TAX if int(TAX_ID) == 3: TAX = float(AMT) / 100 * float(SPC_TAX) AMTN_NET = float(AMT) sid = transaction.savepoint() # 开启事物 try: # 插入进货单表头-----------------------改CUR_ID RMB if int(ZHANG_ID) == 1: print(1) cursor.execute( """UPDATE MF_PSS SET PS_DD=%s,CUS_NO=%s,ZHANG_ID=%s,TAX_ID=%s,VOH_ID=%s,DEP=%s,SAL_NO=%s,MODIFY_DD=%s,ARP_NO=%s WHERE PS_ID='SA' AND PS_NO=%s """, [ datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), CUS_NO, ZHANG_ID, TAX_ID, VOH_ID, DEP, SAL_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 'AP' + PS_NO[2::], 'SA' + PS_NO[2::] ]) print(2) # 如果旧的立账方式是1的话那么会有立账单，那么可以直接修改 if int(ZHANG_ID_OLD) == 1: cursor.execute( """UPDATE MF_ARP SET CUS_NO=%s,PAY_DD=%s,AMTN=%s,AMTN_NET=%s,BIL_DD=%s,DEP=%s,ZHANG_ID=%s WHERE BIL_ID='SA' AND ARP_NO=%s""", [CUS_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), AMT, AMTN_NET, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), DEP, ZHANG_ID, 'AP' + PS_NO[2::] ]) # 如果旧是立账方式四不为1，然后改为1那么系统会在生成一张立账单 if int(ZHANG_ID_OLD) != 1: cursor.execute("""INSERT INTO MF_ARP(ARP_ID,OPN_ID,ARP_NO,BIL_NO,CUS_NO,PAY_DD,AMT,AMTN,AMTN_NET,EXC_RTO,CLOSE_ID,BIL_ID,SYS_DATE,BIL_DD,DEP,ZHANG_ID)VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", [1, 2, 'AP' + PS_NO[2::], 'SA' + PS_NO[2::], CUS_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0, AMT, AMTN_NET, 1, 'F', 'SA', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), DEP, ZHANG_ID]) if int(ZHANG_ID) != 1: print(3) cursor.execute( """UPDATE MF_PSS SET PS_DD=%s,CUS_NO=%s,ZHANG_ID=%s,TAX_ID=%s,VOH_ID=%s,DEP=%s,SAL_NO=%s,MODIFY_DD=%s,ARP_NO=%s WHERE PS_ID='SA' AND PS_NO=%s """, [ datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), CUS_NO, ZHANG_ID, TAX_ID, VOH_ID, DEP, SAL_NO, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), '', 'SA' + PS_NO[2::] ]) # 如果立账方式不为1的时候需要删除，立账单 cursor.execute( """DELETE FROM MF_ARP WHERE BIL_ID='SA' AND ARP_NO=%s""", [ 'AP' + PS_NO[2::] ]) # 插入进货单表身 print(4) cursor.execute( """UPDATE TF_PSS SET PS_DD=%s,UP=%s,AMT=%s,AMTN_NET=%s,CSTN_SAL=%s,TAX=%s,TAX_RTO=%s WHERE PS_ID='SA' AND PS_NO=%s""", [datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), AMT, AMT, AMTN_NET, AMTN_NET, TAX, SPC_TAX, 'SA' + PS_NO[2::] ]) except Exception: transaction.savepoint_rollback(sid) # 语句执行错误，进行语句回滚 return http.HttpResponseForbidden("新增进货单sql语句执行错误") transaction.savepoint_commit(sid) # 提交事物 context = { "errmsg": '修改进货单成功', "code": 200 } return http.JsonResponse(context) @transaction.atomic def delete(self, request): # 1获取参数 req_data = json.loads(request.body.decode()) PS_NO = req_data.get("PS_NO") # 单据日期 # 判断是否有输入单号 if PS_NO is None: return http.HttpResponseForbidden('请输入销货单号') if PS_NO[:3] != 'SAB' or len(PS_NO) != 11: # 判断单号是否合法 return http.HttpResponseForbidden('销货单号输入不合法，单号必须是SAB开头且长度必须为11位') # 判断进货单是否存在 row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'SA') if row_PS_NO <= 0: return http.HttpResponseForbidden('销货单号不存在') # 3数入库 with connection.cursor() as cursor: # 判断单号是否产生后续单据，立账里面的一冲金额 try: # 获取原来单据的立账方式 ZHANG_ID_OLD = cursor.execute("SELECT ZHANG_ID FROM MF_PSS WHERE PS_ID='SA' AND PS_NO=%s", ['SA' + PS_NO[2::]]).fetchall()[0][0] if int(ZHANG_ID_OLD) == 1: print('AP' + PS_NO[2::]) row_no = cursor.execute("SELECT ISNULL(AMTN_RCV,0) FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='SA'", ['AP' + PS_NO[2::]]).fetchall()[0][0] if float(row_no) > 0 and row_no is not None: return http.HttpResponseForbidden('销货单已产生后续单据不允许删除') if int(ZHANG_ID_OLD) != 1: row_no = cursor.execute("SELECT ISNULL(ACC_FP_NO,'') FROM MF_PSS WHERE PS_NO=%s AND PS_ID='SA'", ['SA' + PS_NO[2::]]).fetchall()[0][0] if row_no != '': return http.HttpResponseForbidden('销货单已产生后续单据不允许删除') except Exception: return http.HttpResponseForbidden('判断销货单是否产生后续单据异常') sid = transaction.savepoint() # 开启事物 try: cursor.execute("""DELETE FROM MF_PSS WHERE PS_NO=%s AND PS_ID='SA'""", ['SA' + PS_NO[2::]]) cursor.execute("""DELETE FROM TF_PSS WHERE PS_NO=%s AND PS_ID='SA'""", ['SA' + PS_NO[2::]]) cursor.execute("""DELETE FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='SA'""", ['AP' + PS_NO[2::]]) except Exception: transaction.savepoint_rollback(sid) # 语句执行错误，进行语句回滚 return http.HttpResponseForbidden("删除销货单失败") transaction.savepoint_commit(sid) # 提交事物 context = { "errmsg": '删除销货单成功', "code": 200 } return http.JsonResponse(context) #预付款单 class PtPayment(View): @transaction.atomic def get(self, request): context = { 'a': '客户预收款' } return http.JsonResponse(context) @transaction.atomic def post(self, request): # 获取参数 req_data = json.loads(request.body.decode()) RP_NO = req_data.get("RP_NO") # 预付款单号 字符类型 ----------------1 RP_DD = req_data.get("RP_DD") # 预付款日期 字符类型 --------------------1 CUS_NO = req_data.get("CUS_NO") # 预付款客户编码 字符类型 -------------------------1 CUS_NO_NAME = req_data.get("CUS_NO_NAME",'') # 预付款客户名称 字符类型 ---------------------1 AMTN_BC = req_data.get("AMTN_BC") # 预付款金额 数字类型 ---------------------------1 CACC_NO = req_data.get("CACC_NO") # 预付款银行编码 字符类型 ------------1 USR = req_data.get("USR") # 制单人编码 字符类型 ------------------1 USR_NAME = req_data.get("USR_NAME",'') # 制单人姓名 字符类型 -----------------------1 DEP = req_data.get("DEP",'') # 部门 字符类型 -----------------------1 SAL_NO = req_data.get("SAL_NO",0) # 业务员 字符类型 -----------------------1 VOH_ID = req_data.get("VOH_ID",0) # 凭证模板 字符类型 -----------------------1 # { # "RP_DD": "2019-12-06", # "CUS_NO": "AQ2222", # "CUS_NO_NAME": "收款", # "RP_NO": "RTF9C0202", # "AMTN_BC": "150", # "CACC_NO": "01", # "VOH_ID": "01", # "USR": "a00002", # "USR_NAME": "楠楠", # "DEP": "0000", # "SAL_NO": "A00002", # "user": "123", # "password": "123" # } # 判断单号的合法性 if RP_NO[:3] != 'RTF' or len(RP_NO) != 11: return http.HttpResponseForbidden('预收款单号输入不合法，单号必须是RTF开头且长度长度必须为11位') if RP_DD is None: return http.HttpResponseForbidden('请输入日期') # 校验日期格式 try: datetime.datetime.strptime(RP_DD, '%Y-%m-%d') except Exception: return http.HttpResponseForbidden('日期格式输入不正确') if RP_NO is None: return http.HttpResponseForbidden('请输入预收款单号') if CACC_NO is None: return http.HttpResponseForbidden('请输入银行代号') # //判断有没有输入银行账号 try: sum_CACC_NO = et_CACC_NO(CACC_NO) except Exception: return http.HttpResponseForbidden('请检查银行账号是否存在或者是否输入正确') if sum_CACC_NO == 'NO': return http.HttpResponseForbidden('请检查银行账号是否存在或者是否输入正确') # 判断金额输入是否正确 if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMTN_BC)) is None: return http.HttpResponseForbidden('金额输入不正确') # 判断单号是否存在 row_ZL_NO = examine_ood('TF_MON', 'RP_NO', RP_NO, 'RP_ID', '1') if row_ZL_NO > 0: return http.HttpResponseForbidden('预付款单号已存在') # 判断供应商是否哦存在，不存在的话创建 try: row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '1', datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), 0) except Exception: return http.HttpResponseForbidden('创建客户失败') # 判断用户是否存在 # row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d')) try: # 判断用户是否存在，不存在则创建 row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(RP_DD, '%Y-%m-%d')) except Exception: return http.HttpResponseForbidden('创建用户失败') # 判断账号类别 try: tp_CACC_NO = if_account(CACC_NO) except Exception: return http.HttpResponseForbidden("账户类型异常(现金)、（银行）") if tp_CACC_NO == 'NO': return http.HttpResponseForbidden('账户类型不存在(现金)、（银行）') with connection.cursor() as cursor: row_usr = 0 try: if VOH_ID != 0: row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='RT'", [VOH_ID]).fetchall()[0][0] except Exception: return http.HttpResponseForbidden('查询凭证模板异常') if row_usr <= 0: return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板') # 获取银行账号的会计科目 try: Acc_No_km = cursor.execute("""SELECT ISNULL(Acc_No,'') FROM BACC WHERE BACC_NO=%s""", [CACC_NO]).fetchall()[0][0] except Exception: return http.HttpResponseForbidden("银行会计科目异常") if Acc_No_km == '': return http.HttpResponseForbidden('银行会计科目不存在') sid = transaction.savepoint() # 开启事物 try: # 银行账户 if int(tp_CACC_NO) == 1: print(11112222) # 插入预收款表头 cursor.execute("""INSERT INTO TF_MON(IRP_ID,RP_NO,ITM,RP_ID,CLS_ID,RP_DD,CUS_NO,INCLUDESON,AMTN_BB,AMTN_CLS,BC_NO,BACC_NO,EXC_RTO,DEP,USR,CHK_MAN,CLS_DATE,SYS_DATE,IEA_ID,IOR_ID,SK_TYPE,VOH_ID,USR_NO)VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", [ 'T', RP_NO, 1, 1, 'F', datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CUS_NO, 'F', AMTN_BC, 0, 'BT' + RP_NO[2::], CACC_NO, 1, DEP, USR, USR, datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), 'F', 'F', 1, VOH_ID, SAL_NO ]) print(2323) # 现金账户 if int(tp_CACC_NO) == 2: print(1122) print(VOH_ID) print(SAL_NO) # 插入预收款表头 cursor.execute("""INSERT INTO TF_MON(IRP_ID,RP_NO,ITM,RP_ID,CLS_ID,RP_DD,CUS_NO,INCLUDESON,AMTN_BC,AMTN_CLS,BC_NO,CACC_NO,EXC_RTO,DEP,USR,CHK_MAN,CLS_DATE,SYS_DATE,IEA_ID,IOR_ID,SK_TYPE,VOH_ID,USR_NO)VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", ['T',RP_NO,1,1,'F',datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CUS_NO,'F',AMTN_BC,0,'BT' + RP_NO[2::],CACC_NO,1,'0000',USR,USR,datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),'F','F',1,VOH_ID,SAL_NO]) print(1) # 插入语收款立账金额 cursor.execute("""INSERT INTO MF_MON(RP_ID,RP_NO,RP_DD,DEP,AMTN,AMTN_ARP,AMTN_REST,FJ_NUM)VALUES (%s,%s,%s,%s,%s,%s,%s,%s)""", [1, RP_NO, datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), DEP, AMTN_BC, 0, 0, 0]) print(3) # 插入账户收支单表头 cursor.execute("""INSERT INTO MF_BAC(BB_ID,BB_NO,BB_DD,BACC_NO,ACC_NO,DEP,BIL_NO,EXC_RTO,AMTN,USR,CHK_MAN,OPN_ID,CLS_DATE,SYS_DATE,BIL_ID_N,BIL_NO_N)VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", ['BT', 'BT' + RP_NO[2::], datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CACC_NO, Acc_No_km, DEP, 'BT' + RP_NO, 1, float(AMTN_BC), USR, USR, 'F', datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), 'RT', RP_NO]) print(4) # 插入收支单表身 cursor.execute("""INSERT INTO TF_BAC(BB_ID,BB_NO,ITM,BB_DD,EXC_RTO,AMTN,DEP,CUS_NO,ADD_ID,PRE_ITM)values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", ['BT', 'BT' + RP_NO[2::], 1, datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), 1, AMTN_BC, DEP, CUS_NO, '+', 1 ]) except Exception: transaction.savepoint_rollback(sid) return http.HttpResponseForbidden("预收款单新增sql语句执行异常") transaction.savepoint_commit(sid) context = { "errmsg": '新增客户预付款单成功', "code": 200 } return http.JsonResponse(context) @transaction.atomic def put(self, request): # 获取参数 # 获取参数 req_data = json.loads(request.body.decode()) RP_NO = req_data.get("RP_NO") # 预付款单号 字符类型 ----------------1 RP_DD = req_data.get("RP_DD") # 预付款日期 字符类型 --------------------1 CUS_NO = req_data.get("CUS_NO") # 预付款客户编码 字符类型 -------------------------1 CUS_NO_NAME = req_data.get("CUS_NO_NAME", '') # 预付款客户名称 字符类型 ---------------------1 AMTN_BC = req_data.get("AMTN_BC") # 预付款金额 数字类型 ---------------------------1 CACC_NO = req_data.get("CACC_NO") # 预付款银行编码 字符类型 ------------1 USR = req_data.get("USR") # 制单人编码 字符类型 ------------------1 USR_NAME = req_data.get("USR_NAME", '') # 制单人姓名 字符类型 -----------------------1 DEP = req_data.get("DEP", '') # 部门 字符类型 -----------------------1 SAL_NO = req_data.get("SAL_NO", 0) # 业务员 字符类型 -----------------------1 VOH_ID = req_data.get("VOH_ID", 0) # 凭证模板 字符类型 -----------------------1 UP_DD = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') # { # "RP_DD": "2019-12-06", # "CUS_NO": "AQ2222", # "CUS_NO_NAME": "收款", # "RP_NO": "RTF9C0201", # "AMTN_BC": "100", # "CACC_NO": "01", # "VOH_ID": "01", # "USR": "a00002", # "USR_NAME": "楠楠", # "DEP": "0000", # "SAL_NO": "A00002", # "user": "123", # "password": "123" # } if RP_DD is None: return http.HttpResponseForbidden('请输入时间') if RP_NO[:3] != 'RTF' or len(RP_NO) !=11: return http.HttpResponseForbidden('预收款单号输入不合法，单号必须是RTF开头且长度必须是11位') # 校验日期格式 try: datetime.datetime.strptime(RP_DD, '%Y-%m-%d') except Exception: return http.HttpResponseForbidden('日期格式输入不正确') if RP_NO is None: return http.HttpResponseForbidden('请输入预收款单号') if CACC_NO is None: return http.HttpResponseForbidden('请输入银行代号') # //判断有没有输入银行账号 try: sum_CACC_NO = et_CACC_NO(CACC_NO) except Exception: return http.HttpResponseForbidden('请检查银行账号是否存在或者是否输入正确') if sum_CACC_NO == 'NO': return http.HttpResponseForbidden('请检查银行账号是否存在或者是否输入正确') # 判断金额输入是否正确 if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMTN_BC)) is None: return http.HttpResponseForbidden('金额输入不正确') # 判断单号是否存在 try: row_ZL_NO = examine_ood('TF_MON', 'RP_NO', RP_NO, 'RP_ID', '1') except Exception: return http.HttpResponseForbidden('判断单号是否存在，单号查询异常') if row_ZL_NO <= 0: return http.HttpResponseForbidden('预付款单号不存在') # 判断单号是不是已经产生付款单 try: row_ZL_NO = examine_ood('TF_MON1', 'IRP_NO', RP_NO, 'RP_ID', '1') except Exception: return http.HttpResponseForbidden('判断单号是否产生后续单据异常') if row_ZL_NO > 0: return http.HttpResponseForbidden('预付款单已经产生付款单') # 判断账号类别 try: tp_CACC_NO = if_account(CACC_NO) except Exception: return http.HttpResponseForbidden("账户类型异常(现金)、（银行）") if tp_CACC_NO == 'NO': return http.HttpResponseForbidden('账户类型不存在(现金)、（银行）') # 判断供应商是否哦存在，不存在的话创建 try: row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '1', datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), 0) except Exception: return http.HttpResponseForbidden('创建客户失败') with connection.cursor() as cursor: row_usr = 0 try: if VOH_ID != 0: row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='RT'", [VOH_ID]).fetchall()[ 0][0] except Exception: return http.HttpResponseForbidden('查询凭证模板异常') if row_usr <= 0: return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板') # 获取银行账号的会计科目 try: Acc_No_km = cursor.execute("""SELECT ISNULL(Acc_No,'') FROM BACC WHERE BACC_NO=%s""", [CACC_NO]).fetchall()[0][0] except Exception: return http.HttpResponseForbidden("银行会计科目异常") if Acc_No_km == '': return http.HttpResponseForbidden('银行会计科目不存在') sid = transaction.savepoint() # 开启事物 try: # 银行账户 if int(tp_CACC_NO) == 1: # 修改预付款单 print(11111) cursor.execute( """UPDATE TF_MON SET RP_DD=%s,CUS_NO=%s,AMTN_BB=%s,BC_NO=%s,BACC_NO=%s,MODIFY_DD=%s,MODIFY_MAN=%s,VOH_ID=%s,USR_NO=%s,AMTN_BC=NULL,CACC_NO='' WHERE RP_NO=%s""", [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CUS_NO, AMTN_BC, 'BT' + RP_NO[2::], CACC_NO, UP_DD,USR,VOH_ID,SAL_NO, RP_NO]) # 现金账户 if int(tp_CACC_NO) == 2: print(11222) # 修改预付款单 cursor.execute( """UPDATE TF_MON SET RP_DD=%s,CUS_NO=%s,AMTN_BC=%s,BC_NO=%s,CACC_NO=%s,MODIFY_DD=%s,MODIFY_MAN=%s ,VOH_ID=%s,USR_NO=%s,AMTN_BB=NULL,BACC_NO='' WHERE RP_NO=%s""", [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CUS_NO, AMTN_BC, 'BT' + RP_NO[2::], CACC_NO, UP_DD,USR,VOH_ID,SAL_NO, RP_NO]) # 修改立账单金额 print(2) cursor.execute("""UPDATE MF_MON SET RP_DD=%s,AMTN=%s WHERE RP_NO=%s""", [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), AMTN_BC, RP_NO]) print(3) # 修改账户收支单表头 cursor.execute("""UPDATE MF_BAC SET BB_DD=%s,BACC_NO=%s,ACC_NO=%s,BIL_NO=%s,AMTN=%s,DEP=%s WHERE BB_NO=%s""", [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CACC_NO, Acc_No_km, 'BT' + RP_NO, float(AMTN_BC),DEP, 'BT' + RP_NO[2::]]) print(4) # 修改账户收支单表身 cursor.execute("""UPDATE TF_BAC SET BB_DD=%s,AMTN=%s,CUS_NO=%s WHERE BB_NO=%s""", [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), AMTN_BC, CUS_NO, 'BT' + RP_NO[2::]]) except Exception: transaction.savepoint_rollback(sid) return http.HttpResponseForbidden("预收款单修改sql语句执行异常") transaction.savepoint_commit(sid) context = { "errmsg": '修改客户预付款单成功', "code": 200 } return http.JsonResponse(context) @transaction.atomic def delete(self, request): # 获取参数 req_data = json.loads(request.body.decode()) RP_NO = req_data.get("RP_NO") # 预付款单号 # { # "RP_NO": "RTX9A300003" # } # 判断单号的合法性 if RP_NO[:3] != 'RTF' or len(RP_NO) != 11: return http.HttpResponseForbidden('预收款单号输入不合法，单号必须是RTF开头且长度必须为11位') if RP_NO is None: return http.HttpResponseForbidden('请输入预收款单号') # 判断单号是不是已经产生付款单 try: row_ZL_NO = examine_ood('TF_MON1', 'IRP_NO', RP_NO, 'RP_ID', '1') except Exception: return http.HttpResponseForbidden('判断单号是否产生后续单据异常') if row_ZL_NO > 0: return http.HttpResponseForbidden('预付款单已经产生付款单') # 判断单号是否存在 row_ZL_NO = examine_ood('TF_MON', 'RP_NO', RP_NO, 'RP_ID', '1') if row_ZL_NO == 0: return http.HttpResponseForbidden('预付款单号不存在') with connection.cursor() as cursor: sid = transaction.savepoint() # 开启事物 try: # 删除付款单 cursor.execute("""DELETE FROM TF_MON WHERE RP_NO=%s AND RP_ID='1'""", [RP_NO]) # 删除金额立账单 cursor.execute("""DELETE FROM MF_MON WHERE RP_NO=%s AND RP_ID='1'""", [RP_NO]) # 删除账户收支单表头 cursor.execute("""DELETE FROM MF_BAC WHERE BB_NO=%s""", ['BT' + RP_NO[2::]]) # 删除账户收支单表身 cursor.execute("""DELETE FROM TF_BAC WHERE BB_NO=%s""", ['BT' + RP_NO[2::]]) except Exception: transaction.savepoint_rollback(sid) return http.HttpResponseForbidden("预收款单删除sql语句执行异常") transaction.savepoint_commit(sid) context = { "errmsg": '删除客户预付款单成功', "code": 200 } return http.JsonResponse(context) #回传进货票 class PcInvoice(View): def post(self, request): with connection.cursor() as cursor: # 判断表是否存在 is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='MF_LZ1_Z01'""", []).fetchall() if len(is_table)==0: return http.HttpResponseForbidden("MF_LZ1_Z01,此表不存在请先创建此表") #判断表中是否存在数据 pc_count=cursor.execute("""SELECT COUNT(*) FROM MF_LZ1_Z01""", []).fetchall()[0][0] if pc_count==0: return http.HttpResponseForbidden("表数据为空请先增加数据") #查询未被拉取的数据 pc_date = cursor.execute("""SELECT ISNULL(A_LZ_DD,'') AS A_LZ_DD,ISNULL(A_CUS_NO ,'') AS A_CUS_NO,ISNULL(C_NAME,'') AS C_NAME,ISNULL(A_LZ_NO,'') AS A_LZ_NO,ISNULL(A_INV_NO,'') AS A_INV_NO, ISNULL(A_AMT,0) AS A_AMT,ISNULL(A_AMTN_NET,0) AS A_AMTN_NET,ISNULL(A_TAX,0) AS A_TAX,ISNULL(A_TAX_ID,'') AS A_TAX_ID,ISNULL(A_ZHANG_ID,'') AS A_ZHANG_ID, ISNULL(B_PS_NO,'') AS B_PS_NO,ISNULL(B_QTY,0) AS B_QTY,ISNULL(B_UP,0) AS B_UP,ISNULL(B_AMT,0) AS B_AMT,ISNULL(B_TAX_RTO,0) AS B_TAX_RTO,OK FROM MF_LZ1_Z01 WHERE OK ='N'""", []).fetchall() # print(pc_date) # print(len(pc_date)) if len(pc_date)==0: return http.HttpResponseForbidden("没有需要拉取的数据") data=[] LZ_NO=[] for i in pc_date: # print(1111) LZ_NO.append(i[3]) # data.append({"A_LZ_DD": i[0], "A_CUS_NO": i[1]}) data.append({"A_LZ_DD":str(i[0]),"A_CUS_NO":i[1],"C_NAME":i[2],"A_LZ_NO":i[3], "A_INV_NO":i[4],"A_AMT":float(i[5]),"A_AMTN_NET":float(i[6]),"A_TAX":float(i[7]),"A_TAX_ID":i[8], "A_ZHANG_ID":i[9],"B_PS_NO":i[10],"B_QTY":float(i[11]),"B_UP":float(i[12]),"B_AMT":float(i[13]),"B_TAX_RTO":float(i[14]),"OK":i[15].strip()}) for i in LZ_NO: cursor.execute("""UPDATE MF_LZ1_Z01 SET OK='Y' WHERE A_LZ_NO=%s""", [i]) context = { "data":data, "errmsg": "进货开票数据回传app成功", "code":200 } return http.JsonResponse(context) #回传销货票 class SaInvoice(View): def post(self, request): with connection.cursor() as cursor: # 判断表是否存在 is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='MF_LZ_Z01'""", []).fetchall() if len(is_table)==0: return http.HttpResponseForbidden("MF_LZ1_Z01,此表不存在请先创建此表") #判断表中是否存在数据 sa_count=cursor.execute("""SELECT COUNT(*) FROM MF_LZ_Z01""", []).fetchall()[0][0] if sa_count==0: return http.HttpResponseForbidden("表数据为空请先增加数据") # 查询未被拉取的数据 sa_date = cursor.execute("""SELECT ISNULL(A_LZ_DD,'') AS A_LZ_DD,ISNULL(A_CUS_NO,'') AS A_CUS_NO,ISNULL(C_NAME,'') AS C_NAME,ISNULL(A_LZ_NO,'') AS A_LZ_NO,ISNULL(A_INV_NO,'') AS A_INV_NO, ISNULL(A_AMT,0) AS A_AMT,ISNULL(A_AMTN_NET,0) AS A_AMTN_NET,ISNULL(A_TAX,0) AS A_TAX,ISNULL(A_TAX_ID,'') AS A_TAX_ID,ISNULL(A_ZHANG_ID,'') AS A_ZHANG_ID, ISNULL(B_CK_NO,'') AS B_CK_NO,ISNULL(B_QTY,0) AS B_QTY,ISNULL(B_UP,0) AS B_UP,ISNULL(B_AMT,0) AS B_AMT,ISNULL(B_TAX_RTO,0) AS B_TAX_RTO,OK FROM MF_LZ_Z01 WHERE OK ='N'""",[]).fetchall() if len(sa_date)==0: return http.HttpResponseForbidden("没有需要拉取的数据") data = [] LZ_NO = [] for i in sa_date: # print(1111) LZ_NO.append(i[3]) # data.append({"A_LZ_DD": i[0], "A_CUS_NO": i[1]}) data.append({"A_LZ_DD": str(i[0]), "A_CUS_NO": i[1], "C_NAME": i[2], "A_LZ_NO": i[3], "A_INV_NO": i[4], "A_AMT": float(i[5]), "A_AMTN_NET": float(i[6]), "A_TAX": float(i[7]), "A_TAX_ID": i[8],"A_ZHANG_ID": i[9], "B_CK_NO": i[10], "B_QTY": float(i[11]), "B_UP": float(i[12]), "B_AMT": float(i[13]), "B_TAX_RTO": float(i[14]), "OK": i[15].strip()}) for i in LZ_NO: cursor.execute("""UPDATE MF_LZ_Z01 SET OK='Y' WHERE A_LZ_NO=%s""", [i]) context = { "data": data, "errmsg": "销货开票数据回传app成功", "code": 200 } return http.JsonResponse(context) #客户预收冲应收 # SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD, # ISNULL(C_RP_NO,'') AS C_RP_NO,ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS, # ISNULL(OK,'') AS OK,ISNULL(IRP_NO,'') AS IRP_NO,ISNULL(AMTN_IRP,0) AS AMTN_IRP,ISNULL(B_BIL_ID,'') AS B_BIL_ID # FROM TC_MON_PC WHERE C_RP_ID=1 AND ISNULL(IRP_NO,'')<>'' class yrt(View): def post(self, request): with connection.cursor() as cursor: # 判断表是否存在 is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='TC_MON_PC'""", []).fetchall() if len(is_table)==0: return http.HttpResponseForbidden("TC_MON_PC,此表不存在请先创建此表") #判断表中是否存在数据 sa_count=cursor.execute("""SELECT COUNT(*) FROM TC_MON_PC""", []).fetchall()[0][0] if sa_count==0: return http.HttpResponseForbidden("表数据为空请先增加数据") # 查询未被拉取的数据 sa_date = cursor.execute("""SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD, ISNULL(C_RP_NO,'') AS C_RP_NO,ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS, ISNULL(OK,'') AS OK,ISNULL(IRP_NO,'') AS IRP_NO,ISNULL(AMTN_IRP,0) AS AMTN_IRP,ISNULL(B_BIL_ID,'') AS B_BIL_ID FROM TC_MON_PC WHERE C_RP_ID=1 AND ISNULL(IRP_NO,'')<>'' AND OK='N'""",[]).fetchall() if len(sa_date)==0: return http.HttpResponseForbidden("没有需要拉取的数据") data = [] RT_NO = [] for i in sa_date: # print(1111) RT_NO.append(i[3]) data.append({"C_CUS_NO": i[0], "D_NAME": i[1], "C_RP_DD": i[2], "C_RP_NO": str(i[3]), "B_BIL_NO": i[4], "A_AMTN_CLS": i[5], "OK": i[6], "IRP_NO": i[7].strip(), "AMTN_IRP": i[8], "B_BIL_ID": i[9]}) for i in RT_NO: cursor.execute("""UPDATE TC_MON_PC SET OK='Y' WHERE C_RP_NO=%s""", [i]) context = { "data": data, "errmsg": "客户预收冲应收数据回传app成功", "code": 200 } return http.JsonResponse(context) #客户收款单 # SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD, # ISNULL(C_CACC_NO,'') AS C_CACC_NO,ISNULL(C_AMTN_BC,0) AS C_AMTN_BC,ISNULL(C_RP_NO,'') AS C_RP_NO, # ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(B_BIL_ID,'') AS B_BIL_ID,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS, # ISNULL(OK,'') AS OK # FROM TC_MON_PC WHERE C_RP_ID=1 AND ISNULL(B_BIL_ID,'')<>'' AND ISNULL(IRP_NO,'')='' class rt(View): def post(self, request): with connection.cursor() as cursor: # 判断表是否存在 is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='TC_MON_PC'""", []).fetchall() if len(is_table)==0: return http.HttpResponseForbidden("TC_MON_PC,此表不存在请先创建此表") #判断表中是否存在数据 sa_count=cursor.execute("""SELECT COUNT(*) FROM TC_MON_PC""", []).fetchall()[0][0] if sa_count==0: return http.HttpResponseForbidden("表数据为空请先增加数据") # 查询未被拉取的数据 sa_date = cursor.execute("""SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD, ISNULL(C_CACC_NO,'') AS C_CACC_NO,ISNULL(C_AMTN_BC,0) AS C_AMTN_BC,ISNULL(C_RP_NO,'') AS C_RP_NO, ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(B_BIL_ID,'') AS B_BIL_ID,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS, ISNULL(OK,'') AS OK FROM TC_MON_PC WHERE C_RP_ID=1 AND ISNULL(B_BIL_ID,'')<>'' AND ISNULL(IRP_NO,'')='' AND OK='N'""",[]).fetchall() if len(sa_date) == 0: return http.HttpResponseForbidden("没有需要拉取的数据") data = [] RT_NO = [] for i in sa_date: # print(1111) RT_NO.append(i[5]) data.append({"C_CUS_NO": i[0], "D_NAME": i[1], "C_RP_DD": i[2], "C_CACC_NO": str(i[3]), "C_AMTN_BC": float(i[4]), "C_RP_NO": i[5], "B_BIL_NO": i[6], "B_BIL_ID": i[7].strip(), "A_AMTN_CLS": float(i[8]), "OK": i[9]}) for i in RT_NO: cursor.execute("""UPDATE TC_MON_PC SET OK='Y' WHERE C_RP_NO=%s""", [i]) context = { "data": data, "errmsg": "客户收款单数据回传app成功", "code": 200 } return http.JsonResponse(context) # 厂商付款单 # SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD, # ISNULL(C_CACC_NO,'') AS C_CACC_NO,ISNULL(C_AMTN_BC,0) AS C_AMTN_BC,ISNULL(C_RP_NO,'') AS C_RP_NO, # ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(B_BIL_ID,'') AS B_BIL_ID,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS, # ISNULL(OK,'') AS OK # FROM TC_MON_PC WHERE C_RP_ID=2 AND ISNULL(B_BIL_ID,'')<>'' AND ISNULL(IRP_NO,'')='' class pt(View): def post(self, request): with connection.cursor() as cursor: # 判断表是否存在 is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='TC_MON_PC'""", []).fetchall() if len(is_table)==0: return http.HttpResponseForbidden("TC_MON_PC,此表不存在请先创建此表") #判断表中是否存在数据 sa_count=cursor.execute("""SELECT COUNT(*) FROM TC_MON_PC""", []).fetchall()[0][0] if sa_count==0: return http.HttpResponseForbidden("表数据为空请先增加数据") # 查询未被拉取的数据 sa_date = cursor.execute("""SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD, ISNULL(C_CACC_NO,'') AS C_CACC_NO,ISNULL(C_AMTN_BC,0) AS C_AMTN_BC,ISNULL(C_RP_NO,'') AS C_RP_NO, ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(B_BIL_ID,'') AS B_BIL_ID,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS, ISNULL(OK,'') AS OK FROM TC_MON_PC WHERE C_RP_ID=2 AND ISNULL(B_BIL_ID,'')<>'' AND ISNULL(IRP_NO,'')='' AND OK='N'""",[]).fetchall() if len(sa_date) == 0: return http.HttpResponseForbidden("没有需要拉取的数据") data = [] RT_NO = [] for i in sa_date: # print(1111) RT_NO.append(i[5]) data.append({"C_CUS_NO": i[0], "D_NAME": i[1], "C_RP_DD": i[2], "C_CACC_NO": str(i[3]), "C_AMTN_BC": float(i[4]), "C_RP_NO": i[5], "B_BIL_NO": i[6], "B_BIL_ID": i[7].strip(), "A_AMTN_CLS": float(i[8]), "OK": i[9]}) for i in RT_NO: cursor.execute("""UPDATE TC_MON_PC SET OK='Y' WHERE C_RP_NO=%s""", [i]) context = { "data": data, "errmsg": "客户收款单数据回传app成功", "code": 200 } return http.JsonResponse(context) # 渲染order.html模板 class index(View): def get(self, request): return render(request, 'order.html') # order.html模板模板功数据 class listtb(View): def get(self, request): # req_data = json.loads(request.body.decode())["params"] # print(req_data) st_time = request.GET["st_time"] # 开始时间 sh_time = request.GET['sh_time'] cus_no = request.GET['cus_no'] cls_id = request.GET['cls_id'] PMC_REM = request.GET['pmc_ok'] sort = request.GET['sort'] th_time = datetime.datetime.now().strftime('%Y-%m') # 获取本月时间 page = settings.PAGE_SIZE print(page) # 处理客户代号 if cus_no: cus_no = ' AND '+ 'A.CUS_NO='+"'"+cus_no+"'" print(cus_no) # 处理结案否 if cls_id: if cls_id =='1': cls_id = '' elif cls_id == '2': cls_id = 'T' cls_id = ' AND '+ 'A.CLS_ID ='+"'"+cls_id+"'" else: cls_id = ' AND ' + 'A.CLS_ID !=' + "'" + 'T' + "'" # 处理是否回复 if PMC_REM: if PMC_REM =='1': PMC_REM = '' elif PMC_REM == '2': PMC_REM = ' AND '+ "ISNULL(C.PMC_REM,'') != ''" else: PMC_REM = ' AND ' + "ISNULL(C.PMC_REM,'') = ''" if sort: # F.SPC_NO if sort == '1': sort = ' ORDER BY F.SPC_NO DESC' if sort == '2': sort = ' ORDER BY A.CUS_NO DESC' else: sort = ' ORDER BY C.PMC_REM DESC' if st_time =='': conditional = ' ORDER BY A.OS_NO DESC' else: conditional = ' AND ' + 'left(Convert(varchar(100), A.OS_DD, 23), 11) >=' + "'" + st_time + "'" + ' AND ' + 'left(Convert(varchar(100), A.OS_DD, 23), 11) <=' + "'" + sh_time + "'" + cus_no + cls_id + PMC_REM + sort # 定义sql语句 sql = """SELECT TOP 1000 CASE WHEN F.NAME LIKE '%%返工%%' THEN '返工' ELSE '订单' END AS ORDER0 ,ISNULL(B.OS_DD,'') AS OS_DD, ISNULL(D.SNM,'') AS CUS_SNM,B.OS_NO AS OS_NO,B.PRD_NO AS PRD_NO,ISNULL(E.DZRL,'') AS DZRL,'' AS RQ,ISNULL(E.YSSL,'') AS YSSL,'' AS YMXH, ISNULL(B.QTY,0) AS QTY,ISNULL(E.YSSL *B.QTY,0) AS GZSL,B.EST_DD AS EST_DD,'' AS PD_DEP,ISNULL(B.REM,'') AS REM,CONVERT(CHAR(10), C.PMC_REM, 23) AS PMC_DD, C.WH_REM AS WH_REM, B.ITM AS ITM FROM MF_POS A LEFT JOIN TF_POS B ON A.OS_NO=B.OS_NO LEFT JOIN TF_POS_Z C ON B.OS_NO=C.OS_NO AND B.ITM=C.ITM LEFT JOIN CUST D ON A.CUS_NO=D.CUS_NO LEFT JOIN PRDT_Z E ON B.PRD_NO=E.PRD_NO LEFT JOIN BIL_SPC F ON A.BIL_TYPE=F.SPC_NO AND F.BIL_ID='SA' WHERE A.OS_ID='SO' AND B.OS_ID=A.OS_ID AND C.OS_ID=A.OS_ID {0}""".format(conditional) with connection.cursor() as cursor: no_data = cursor.execute(sql,[]).fetchall() data = [ {"ORDER0": "类别", "OS_DD": '下单日期', "CUS_SNM": "客户名称", "OS_NO":"计划单号","PRD_NO":"产品编码","DZRL":"容量（ML）", "QY":"容器","YSSL":"套装颜色数","YMXH":"研磨品类型", "QTY":"订单数量","GZSL":"灌装数量","EST_DD":"业务交期", "PD_DEP":"生产部门","REM":"备注","PMC_DD":"PMC回复交期","WH_REM":"大货备注", "operation":"操作","ITM":"项次"}, ] # OS_NO = [] 啊啊撒打算梵蒂冈git add . for i in no_data: # print(1111) # OS_NO.append(i[5]) data.append({"ORDER0": i[0], "OS_DD": str(i[1])[:10] , "CUS_SNM": i[2], "OS_NO": str(i[3]), "PRD_NO": i[4], "DZRL": i[5], "QY": i[6], "YSSL": i[7], "YMXH": i[8], "QTY": float(i[9]),"GZSL": float(i[10]),"EST_DD": str(i[11])[:10], "PD_DEP": i[12],"REM":i[13],"PMC_DD": str(i[14]),"WH_REM": i[15],"operation": "保存","ITM":i[16]}) # print(data) context = { "data": data, "errmsg": "一览表获取成功", "code": 200 } return http.JsonResponse(context) def put(self, request): # req_data = json.loads(request.body.decode()) try: eq_data = json.loads(request.body.decode())["params"] except Exception: return http.HttpResponseForbidden('参数不全') req_data = json.loads(request.body.decode())["params"] OS_NO = req_data.get("OS_NO") PMC_DD = req_data.get("PMC_DD",'') WH_REM = req_data.get("WH_REM",'') ITM = req_data.get("ITM") with connection.cursor() as cursor: cursor.execute( """UPDATE TF_POS_Z SET PMC_REM=%s,WH_REM=%s WHERE OS_ID='SO' AND OS_NO=%s AND ITM=%s """, [PMC_DD,WH_REM,OS_NO,ITM]) print(OS_NO) print(PMC_DD) print(WH_REM) print(ITM) context = { "errmsg": "保存成功", "code": 200 } return http.JsonResponse(context) # 渲染dep_ps.html模板 class depps(View): def get(self, request): return render(request, 'dep_ps.html') # return render(request, 'xx.html') # 获取部门数据 class dept(View): def get(self, request): with connection.cursor() as cursor: dep_data = cursor.execute("""SELECT DEP,NAME FROM DEPT""",[]).fetchall() # print(dep_data) data = [] # print(dep_data) for i in dep_data: # print(1111) data.append({"DEP": i[0], "DEP_NAME": i[1]}) # print(data) context = { "data": data, "errmsg": "获取部门成功", "code": 200 } return http.JsonResponse(context) # 查询单号 class oddNo(View): def get(self, request): sql = ("SELECT TOP 5 MO_NO FROM MF_MO ") result = executeQuery(sql) context = { "data": result, "errmsg": "获取单号成功", "code": 200 } return http.JsonResponse(context) #获取客户信息 class Cust(View): def get(self, request): sql = ("SELECT CUS_NO,NAME FROM CUST WHERE OBJ_ID ='1' OR OBJ_ID = '3'") result = executeQuery(sql) context = { "data": result, "errmsg": "获取部门成功", "code": 200 } return http.JsonResponse(context) # 生成月度产能评估表单号 class monthly(View): def get(self, request): monthly_odd_old='20200219009' monthly_odd_new = monthly_odd(monthly_odd_old) # data = {"monthly_odd_new":monthly_odd_new} context = { "data": monthly_odd_new, "errmsg": "获取部门成功", "code": 200 } return http.JsonResponse(context) # 测试 class text(View): def get(self, request): return render(request, 'text.html')