from django.shortcuts import render # Create your views here. import json import math import re from django import http from django.shortcuts import render # restful from django_filters.rest_framework import DjangoFilterBackend from rest_framework import status, filters from rest_framework.response import Response from rest_framework.views import APIView from rest_framework.viewsets import ModelViewSet # from django_filters.rest_framework import DjangoFilterBackend # Create your views here. from django.views import View from django.conf import settings from django.db import connection, transaction import datetime from utils.sqlinjection import sqlinjections 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 from utils.MyPageNumber import MyPageNumberPagination # 进货单 from .models import View_ydzpcjh,View_pcydscjh,view_tf_pos from .serializers import YdzpcjhZserializer,YdCourseFilterSet,PcydpcjhZserializer,PcCourseFilterSet,YmpdpcjhZserializer,YmpCourseFilterSet 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 = { "message": '新增进货单成功', "code":200 } # HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码) return http.JsonResponse(context) # return http.JsonResponse({"code": RET.OK, "message": "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 = { "message": '修改进货单成功', "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 = { "message": '删除进货单成功', "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 = { "message": '新增销货单成功', "code": 200 } # HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码) return http.JsonResponse(context) # return http.JsonResponse({"code": RET.OK, "message": "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 = { "message": '修改进货单成功', "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 = { "message": '删除销货单成功', "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 = { "message": '新增客户预付款单成功', "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 = { "message": '修改客户预付款单成功', "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 = { "message": '删除客户预付款单成功', "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, "message": "进货开票数据回传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, "message": "销货开票数据回传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, "message": "客户预收冲应收数据回传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, "message": "客户收款单数据回传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, "message": "客户收款单数据回传app成功", "code": 200 } return http.JsonResponse(context) # 获取部门数据 class dept(View): def get(self, request): search_no = request.GET["search_no"] # 校验参数合法性 sqlinjection =sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") if search_no != '': search_no = ' DEP LIKE ' + "'" + '%%' + search_no + '%%' + "'" + ' OR ' + '[NAME] LIKE ' + "'" + '%%' + search_no + '%%' + "'" # search_no = ' PRD_NO LIKE '+"'"+'%%'+search_no+'%%'+"'" sql = """SELECT DEP,NAME FROM DEPT WHERE {0}""".format(search_no) else: sql = """SELECT top 100 DEP, NAME FROM DEPT """ data = [] result = executeQuery(sql) # print(result) if len(result) != 0: for i in result: data.append({"id": i["DEP"].replace(" ", ""), "text": i["DEP"] + '->' + i["NAME"].replace(" ", "")}) # print(data) context = { "data": data, "message": "获取部门成功", "code": 200 } return http.JsonResponse(context) # 获取货品资料 class Prdt(View): def get(self, request): search_no = request.GET["search_no"] # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") if search_no != '': search_no = ' PRD_NO LIKE ' + "'" + '%%' + search_no + '%%' + "'" + ' OR ' + '[NAME] LIKE ' + "'" + '%%' + search_no + '%%' + "'" # search_no = ' PRD_NO LIKE '+"'"+'%%'+search_no+'%%'+"'" sql = """SELECT PRD_NO,NAME FROM PRDT WHERE {0}""".format(search_no) else: sql = """SELECT top 100 PRD_NO,NAME FROM PRDT """ data = [] # print(sql) with connection.cursor() as cursor: try: dep_data = cursor.execute(sql, []).fetchall() except Exception: context = { "data": data, "message": "没有获取到数据", "code": 510 } return http.JsonResponse(context) if len(dep_data) != 0: for i in dep_data: data.append({"id": i[0].replace(" ", ""), "text": i[0].replace(" ", "") + '->' + i[1]}) # print(data) context = { "data": data, "message": "获取货品成功", "code": 200 } return http.JsonResponse(context) # 获取客户信息 class Cust(View): def get(self, request): search_no = request.GET["search_no"] # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") if search_no != '': search_no = ' CUS_NO LIKE ' + "'" + '%%' + search_no + '%%' + "'" + ' OR ' + '[NAME] LIKE ' + "'" + '%%' + search_no + '%%' + "'" # search_no = ' PRD_NO LIKE '+"'"+'%%'+search_no+'%%'+"'" sql = """SELECT CUS_NO,NAME FROM CUST WHERE {0}""".format(search_no) else: sql = """SELECT top 100 CUS_NO,NAME FROM CUST """ data = [] # print(sql) with connection.cursor() as cursor: try: dep_data = cursor.execute(sql, []).fetchall() except Exception: context = { "data": data, "message": "没有获取到数据", "code": 510 } return http.JsonResponse(context) if len(dep_data) != 0: for i in dep_data: data.append({"id": i[0].replace(" ", ""), "text": i[0].replace(" ", "") + '->' + i[1]}) # print(data) context = { "data": data, "message": "获取部门成功", "code": 200 } return http.JsonResponse(context) # 获取BOM信息 class Bom(View): def get(self, request): search_no = request.GET["search_no"] # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") if search_no != '': search_no = ' BOM_NO LIKE ' + "'" + '%%' + search_no + '%%' + "'" + ' OR ' + '[NAME] LIKE ' + "'" + '%%' + search_no + '%%' + "'" # search_no = ' PRD_NO LIKE '+"'"+'%%'+search_no+'%%'+"'" sql = """SELECT BOM_NO,NAME FROM MF_BOM WHERE {0}""".format(search_no) else: sql = """SELECT top 100 BOM_NO,NAME FROM MF_BOM """ data = [] # print(sql) with connection.cursor() as cursor: try: dep_data = cursor.execute(sql, []).fetchall() except Exception: context = { "data": data, "message": "没有获取到数据", "code": 510 } return http.JsonResponse(context) if len(dep_data) != 0: for i in dep_data: data.append({"id": i[0].replace(" ", ""), "text": i[0].replace(" ", "") + '->-' + i[1]}) # print(data) context = { "data": data, "message": "BOM配方成功", "code": 200 } return http.JsonResponse(context) # 查询缴库单号 class MfmmooddNo(View): def get(self, request): search_no = request.GET["search_no"] # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") if search_no != '': search_no = ' A.MM_NO LIKE ' + "'" + '%%' + search_no + '%%' + "'" + ' OR ' + 'B.NAME LIKE ' + "'" + '%%' + search_no + '%%' + "'"+"ORDER BY A.MM_NO DESC" # search_no = ' PRD_NO LIKE '+"'"+'%%'+search_no+'%%'+"'" sql = """SELECT A.MM_NO AS MM_NO,B.NAME AS NAME FROM MF_MM0 A,DEPT B WHERE A.DEP=B.DEP AND {0}""".format(search_no) else: sql = """SELECT TOP 20 A.MM_NO AS MM_NO,B.NAME AS NAME FROM MF_MM0 A,DEPT B WHERE A.DEP=B.DEP ORDER BY A.MM_NO DESC""" # print(sql) data = [] result = executeQuery(sql) # print(result) if len(result) != 0: for i in result: data.append({"id": i["MM_NO"], "text": i["MM_NO"] + '->' + i["NAME"]}) # print(data) context = { "data": data, "message": "获取缴库单号成功", "code": 200 } return http.JsonResponse(context) # 查询计划单和受订单 class Jhsd(View): def get(self, request): search_no = request.GET["search_no"] # # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") if search_no != '': sql = """select a.* from ( select a.os_no as os_no,b.name as name,1 as os_id from mf_pos a,cust b where a.cus_no=b.cus_no and a.os_id='SO' and (a.os_no like '%%{0}%%' or b.name like '%%{1}%%') union all select a.jh_no as os_no,b.name as name,2 as os_id from MF_JH a,cust b where a.cus_no=b.cus_no and (a.jh_no like '%%{2}%%' or b.name like '%%{3}%%') ) a order by os_id,os_no desc"""\ .format(search_no,search_no,search_no,search_no) else: sql = """select top 20 a.* from ( select a.os_no as os_no,b.name as name,1 as os_id from mf_pos a,cust b where a.cus_no=b.cus_no and a.os_id='SO' union all select a.jh_no as os_no,b.name as name,2 as os_id from MF_JH a,cust b where a.cus_no=b.cus_no ) a order by os_id,os_no desc""" # print(sql) data = [] result = executeQuery(sql) if len(result)>3000 : return http.HttpResponseForbidden("查询记录数超过3000请精确查询") # print(result) if len(result) != 0: for i in result: data.append({"id": i["os_no"], "text": i["os_no"] + '->' + i["name"]}) # print(data) context = { "data": data, "message": "计划受订单成功", "code": 200 } return http.JsonResponse(context) # 查询IE工艺文件号 class IeFileNo(View): def get(self, request): search_no = request.GET["search_no"] # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") if search_no != '': search_no = ' iefile_no LIKE' + "'" + '%%' + search_no + '%%' + "'" + ' OR ' + 'file_serial LIKE ' + "'" + '%%' + search_no + '%%' + "'" + "ORDER BY iefile_no DESC" # search_no = ' PRD_NO LIKE '+"'"+'%%'+search_no+'%%'+"'" sql = """select iefile_no,file_serial from MF_IEFILE WHERE {0}""".format( search_no) else: sql = """select iefile_no,file_serial from MF_IEFILE ORDER BY iefile_no DESC""" # print(sql) data = [] result = executeQuery(sql) # print(result) if len(result) != 0: for i in result: data.append({"id": i["iefile_no"], "text": i["iefile_no"] + '->' + i["file_serial"]}) # print(data) context = { "data": data, "message": "获取缴库单号成功", "code": 200 } return http.JsonResponse(context) # 查询单号 class oddNo(View): def get(self, request): search_no = request.GET["search_no"] # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") if search_no != '': search_no = ' NO LIKE ' + "'" + '%%' + search_no + '%%' + "'" + ' OR ' + 'DEP_NAME LIKE ' + "'" + '%%' + search_no + '%%' + "'"+"ORDER BY NO DESC" # search_no = ' PRD_NO LIKE '+"'"+'%%'+search_no+'%%'+"'" sql = """SELECT NO,DEP_NAME FROM MCAF WHERE {0}""".format(search_no) else: sql = """SELECT top 20 NO, DEP_NAME FROM MCAF ORDER BY NO DESC""" # print(sql) data = [] result = executeQuery(sql) # print(result) if len(result) != 0: for i in result: data.append({"id": i["NO"], "text": i["NO"] + '->' + i["DEP_NAME"]}) # print(data) context = { "data": data, "message": "获取单号成功", "code": 200 } return http.JsonResponse(context) # 渲染order.html模板数据订单一览表 class index(View): def get(self, request): return render(request, 'order.html') # 订单一览表数据接口 class listtb(View): def get(self, request): # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") st_time = request.GET["st_time"] # 开始时间 sh_time = request.GET['sh_time'] cus_no = request.GET['cus_no'].split('->')[0]#客户编码->这里需要进行切割，因为传过来的是编码+名称（’编码->名称‘） cls_id = request.GET['cls_id'] PMC_REM = request.GET['pmc_ok'] sort = request.GET['sort'] business_time = request.GET['business_time'] #业务交期 prd_no = request.GET['prd_no'].split('->')[0] #货品编码->这里需要进行切割，因为传过来的是编码+名称（’编码->名称‘） sc_dep = request.GET['sc_dep'].split('->')[0] #生产部门 # print(sc_dep) ym_type = request.GET['ym_type'] #研磨类型 os_type = request.GET['os_type'] #订单类型 capacity = request.GET['capacity'] #容量 vessel = request.GET['vessel'] #容器 select = request.GET['select'] #用于判断是否查询 ifmerge = request.GET['ifmerge']#是否合并 # print(ifmerge) th_time = datetime.datetime.now().strftime('%Y-%m') # 获取年月 t_today = datetime.datetime.now().strftime('%Y-%m-%d') # 获取年月日 page_size = settings.PAGE_SIZE #每页记录数 page = int(request.GET['page'])-1 #页码 total_page = 0 #总页数 total_count = 0 #总记录数 # print(page) # 处理客户代号 if len(cus_no)>0: cus_no = ' AND '+ 'CUS_NO='+"'"+cus_no+"'" # 处理结案否 if cls_id: if cls_id =='1': cls_id = '' elif cls_id == '2': cls_id = 'T' cls_id = ' AND '+ 'CLS_ID ='+"'"+cls_id+"'" else: cls_id = ' AND ' + '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 business_time !='': business_time = ' AND '+ 'EST_DD='+"'"+business_time+"'" # 处理货品编码 if prd_no != '': prd_no = ' AND ' + 'PRD_NO=' + "'" + prd_no + "'" # 处理部门============================================================ if sc_dep != '': sc_dep = ' AND ' + 'DEP=' + "'" + sc_dep + "'" # 处理研磨类型 # if ym_type !='': # ym_type = ' AND ' + 'B.PRD_NO=' + "'" + ym_type + "'" # 订单类型 if os_type == '1': os_type='' if os_type == '2': os_type = ' AND ' + "ORDER0 LIKE '%%订单%%'" if os_type == '3': os_type = ' AND ' + "ORDER0 LIKE '%%返工%%'" # 处理容量 if capacity != '': capacity = ' AND ' + 'DZRL=' + "'" + capacity + "'" # # 处理容器 # if vessel != '': # vessel = ' AND ' + 'B.PRD_NO=' + "'" + vessel + "'" if select=='2': conditional = ' left(Convert(varchar(100), OS_DD, 23), 7) >=' + "'" + th_time + "'" else: conditional = ' left(Convert(varchar(100), OS_DD, 23), 11) >=' + "'" + st_time + "'" + ' AND ' + 'left(Convert(varchar(100), OS_DD, 23), 11) <=' + "'" + sh_time + "'" + cus_no + cls_id + PMC_REM +\ business_time + prd_no + sc_dep + os_type + capacity # 定义sql语句 # 这里是不合并的处理 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":"项次","WH":"订单库位","BAT_QTY":"现存量","QTY_FIN":"已完工量","W_QTY_FIN":"未完工量"}, ] if ifmerge=='1': # 排序规则 if sort: # F.SPC_NO if sort == '1': sort = ' ORDER BY OS_NO,OS_NO+Convert(varchar(100), ITM, 23) DESC' sort_if = '1' if sort == '2': sort = ' ORDER BY SPC_NO,OS_NO+Convert(varchar(100), ITM, 23) DESC' if sort == '3': sort = ' ORDER BY CUS_NO,OS_NO+Convert(varchar(100), ITM, 23) DESC' if sort == '4': sort = ' ORDER BY PMC_DD,OS_NO+Convert(varchar(100), ITM, 23) DESC' else: sort = ' ORDER BY OS_NO,OS_NO+Convert(varchar(100), ITM, 23) DESC' # conditional where条件 # 排序 sort print(conditional) sql_count = ("""SELECT COUNT(OS_NO) AS total FROM View_aa WHERE {0}""".format(conditional)) # print(sql_count) result = executeQuery(sql_count) if len(result) == 0 or result[0]["total"]==0: context = { "data": data, "total_page": 0, "total_count": 0, "message": "没有获取到数据", "code": 510 } return http.JsonResponse(context) total_count = result[0]["total"] #总记录数 total_page = math.ceil(result[0]["total"]/page_size) #计算总页数,总记录数除以每页记录数 # print(total_page) sql = """SELECT TOP {0} ORDER0 AS ORDER0 ,ISNULL(OS_DD,'') AS OS_DD, ISNULL(CUS_SNM,'') AS CUS_SNM,OS_NO AS OS_NO,PRD_NO AS PRD_NO,ISNULL(DZRL,'') AS DZRL,'' AS RQ,ISNULL(YSSL,'') AS YSSL,'' AS YMXH, ISNULL(QTY,0) AS QTY,ISNULL(GZSL,0) AS GZSL,left(Convert(varchar(100), EST_DD, 23), 11) AS EST_DD,PD_DEP AS PD_DEP,ISNULL(REM,'') AS REM,PMC_DD AS PMC_DD,WH_REM AS WH_REM, ITM AS ITM,SPC_NO,CUS_NO,PMC_REM,WH FROM View_aa WHERE {1} AND OS_NO+PRD_NO+Convert(varchar(100), ITM, 23) NOT IN(SELECT TOP {2} OS_NO+PRD_NO+Convert(varchar(100), ITM, 23) FROM View_aa WHERE {3} {4}) {5}""".\ format(page_size,conditional,page_size*page,conditional,sort,sort) print(sql) # 这里是合并处理 else: # 排序规则 if sort: # F.SPC_NO if sort == '1': sort = ' ORDER BY OS_NO,OS_NO+PRD_NO DESC' sort_if = '1' if sort == '2': sort = ' ORDER BY SPC_NO,OOS_NO+PRD_NO DESC' if sort == '3': sort = ' ORDER BY CUS_NO,OS_NO+PRD_NO DESC' if sort == '4': sort = ' ORDER BY PMC_DD,OS_NO+PRD_NO DESC' else: sort = ' ORDER BY OS_NO,OS_NO+PRD_NO DESC' # =这里需要加分组gurup_by 然后加 sort # conditional 条件 # gurup_by 分组 # sort 排序 gurup_by = ' GROUP BY OS_NO,PRD_NO,OS_DD,PMC_DD,ORDER0,CUS_SNM,DZRL,RQ,PD_DEP,REM,WH ' conditional = ' left(Convert(varchar(100), OS_DD, 23), 11) >=' + "'" + st_time + "'" + ' AND ' + 'left(Convert(varchar(100), OS_DD, 23), 11) <=' + "'" + sh_time + "'" + cus_no + cls_id + PMC_REM + \ business_time + prd_no + sc_dep + os_type + capacity sql_count = ("""SELECT COUNT(OS_NO) AS total FROM (SELECT OS_NO FROM View_aa WHERE {0} {1}) A""".format(conditional,gurup_by)) # sql_count = ("""SELECT COUNT(OS_NO) AS total FROM View_aa WHERE {0} {1}""".format(conditional,gurup_by)) # print(sql_count) result = executeQuery(sql_count) if len(result) == 0 or result[0]["total"]==0: context = { "data": data, "total_page": 0, "total_count": 0, "message": "没有获取到数据", "code": 510 } return http.JsonResponse(context) total_count = result[0]["total"] # 总记录数 # print(total_count) total_page = math.ceil(result[0]["total"] / page_size) # 计算总页数,总记录数除以每页记录数 # print(total_page) sql = """SELECT TOP {0} ORDER0 AS ORDER0 ,ISNULL(OS_DD,'') AS OS_DD, ISNULL(CUS_SNM,'') AS CUS_SNM,OS_NO AS OS_NO,PRD_NO AS PRD_NO,ISNULL(DZRL,'') AS DZRL,'' AS RQ,SUM(ISNULL(YSSL,0)) AS YSSL,'' AS YMXH, SUM(ISNULL(QTY,0)) AS QTY,SUM(ISNULL(GZSL,0)) AS GZSL, stuff((SELECT ';' + replace(CONVERT(CHAR(19), left(Convert(varchar(100), B.EST_DD, 23), 11), 120),' ','') FROM View_aa B WHERE A.OS_NO=B.OS_NO AND B.PRD_NO=A.PRD_NO FOR xml path('')),1,1,'')AS EST_DD ,PD_DEP AS PD_DEP,ISNULL(REM,'') AS REM,PMC_DD AS PMC_DD, stuff((SELECT ';' + CAST(B.WH_REM AS VARCHAR(500)) FROM View_aa B WHERE A.OS_NO=B.OS_NO AND B.PRD_NO=A.PRD_NO FOR xml path('')),1,1,'')AS WH_REM, stuff((SELECT ';' + CAST(B.ITM AS VARCHAR(500)) FROM View_aa B WHERE A.OS_NO=B.OS_NO AND B.PRD_NO=A.PRD_NO FOR xml path('')),1,1,'')AS ITM,'','','',WH FROM View_aa A WHERE {1} AND OS_NO+PRD_NO NOT IN(SELECT TOP {2} OS_NO+PRD_NO FROM View_aa WHERE {3} {4} {5}){6} {7}""".\ format(page_size,conditional,page_size*page,conditional,gurup_by,sort,gurup_by,sort) # print(sql) with connection.cursor() as cursor: no_data = cursor.execute(sql,[]).fetchall() # OS_NO = [] 啊啊撒打算梵蒂冈git add . for i in no_data: # print(1111) # OS_NO.append(i[5]) # 增量已完工未完工，现存量 sql_qty = """SELECT ISNULL(SUM(A.BAT_QTY),0) AS BAT_QTY,ISNULL(CAST(SUM(ISNULL(B.QTY_FIN,0)) AS FLOAT),0) AS QTY_FIN FROM (SELECT CAST(SUM(ISNULL(QTY_IN,0)-ISNULL(QTY_OUT,0)) AS FLOAT) AS BAT_QTY,PRD_NO,WH FROM BAT_REC1 WHERE PRD_NO='{0}' AND WH='{1}' GROUP BY PRD_NO,WH)A left join MF_MO B ON A.PRD_NO=B.MRP_NO AND B.SO_NO='{2}'""".format(i[4],i[20],i[3]) print(sql_qty) try: result = executeQuery(sql_qty)[0] except Exception: return http.HttpResponseForbidden("查询库存异常") # print(result) # 定义现存量BAT_QTY,"QTY_FIN":"已完工量","W_QTY_FIN":"未完工量" BAT_QTY=0 QTY_FIN=0 W_QTY_FIN=0 if len(result)>0: BAT_QTY=result["BAT_QTY"] QTY_FIN=result["QTY_FIN"] W_QTY_FIN=float(i[9])-result["QTY_FIN"] 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]), "PD_DEP": i[12],"REM":i[13],"PMC_DD": str(i[14]),"WH_REM": i[15],"operation": "保存", "ITM":i[16],"WH":i[20],"BAT_QTY":BAT_QTY,"QTY_FIN":QTY_FIN,"W_QTY_FIN":W_QTY_FIN}) # print(data)啊 # print(sql) context = { "data": data, "total_page":total_page, "total_count":total_count, "message": "一览表获取成功", "code": 200 } return http.JsonResponse(context) def put(self, request): req_data = json.loads(request.body.decode())["data"] # 校验参数合法性 sqlinjection = sqlinjections().ifsql(req_data) if sqlinjection: return http.HttpResponseForbidden("参数非法") OS_NO = req_data.get("OS_NO") WH_REM = req_data.get("WH_REM",'') ITM = req_data.get("ITM") try: PMC_DD = req_data.get("PMC_DD", '') if len(PMC_DD)<=0: return http.HttpResponseForbidden("请输选择日期") except Exception: return http.HttpResponseForbidden("日期格式有问题或者日期不能为空") if OS_NO is None or ITM is None: return http.HttpResponseForbidden("参数不能为空") 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]) context = { "message": "保存成功", "code": 200 } return http.JsonResponse(context) # 渲染dep_ps.html月度产能评估模板 class depps(View): def get(self, request): return render(request, 'dep_ps.html') # 月度产能评估功能类 class DepMacf(View): def get(self,request): # st_time = request.GET["st_time"] # 开始时间 # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") data_timeqi = request.GET["data_timeqi"] data_timezhi = request.GET["data_timezhi"] serch_dep = request.GET["serch_dep"].split('->')[0] #部门编码->这里需要进行切割，因为传过来的是编码+名称（’编码->名称‘） serch_no = request.GET["serch_no"].split('->')[0] #单号->这里需要进行切割，因为传过来的是编码+名称（’单号->成品名称‘） if len(data_timeqi)==10: data_time = "WHERE "+"left(Convert(varchar(100), dd, 23), 11)>="+"'"+data_timeqi+"'"+' AND '+"left(Convert(varchar(100), dd, 23), 11)<="+"'"+data_timezhi+"'" else: return http.HttpResponseForbidden("输入的时间有问题") if len(serch_dep)>0: serch_dep =" AND " + "DEP="+"'"+serch_dep+"'" else: serch_dep = '' if len(serch_no)>0: serch_no = " AND "+"NO="+"'"+serch_no+"'" else: serch_no = '' conditional = data_time+serch_dep+serch_no+" ORDER BY NO,ITM ASC" sql = """SELECT itm,no as no,dep,dep_name as dep_name,cast(ok_qty as float) as ok_qty,cast(un_qty as float) as un_qty, cast(s_capacity as float) as s_capacity ,cast(s_d_staffing as float) as s_d_staffing ,cast(e_capacity as float) as e_capacity ,cast(tolf_days as float) as tolf_days ,cast(epl_personnel as float) as epl_personnel ,cast(p_d_f_e_configurations as float) as p_d_f_e_configurations, a_strength as a_strength ,cast(e_a_workers as float) as e_a_workers ,cast(work_sum as float) as work_sum,cast(zj_274_24 as float) as zj_274_24 ,cast(a_274_24 as float) as a_274_24 ,cast(new_personnel as float) as new_personnel ,rem as rem ,cast(need_hours as float) as need_hours, cast(a_25 as float) as a_25 , cast(e_d_workers as float) as e_d_workers ,cast(p_gap as float) as p_gap from MCAF {0}""".format(conditional) print(sql) data = executeQuery(sql) # print(sql) if len(data)<=0: return http.HttpResponseForbidden("没有查询单数据") print(data) context = { "data":data, "message": "查询成功", "code": 200 } return http.JsonResponse(context) def post(self,request): # 获取数据 req_data = json.loads(request.body.decode())["data"] # 校验参数合法性 print(req_data) for i in req_data["data_list"]: sqlinjection = sqlinjections().ifsql(i) if sqlinjection: return http.HttpResponseForbidden("参数非法") # print(req_data["data_list"]) # t_today = datetime.datetime.now().strftime('%Y-%m-%d') # 获取年月日 t_today = req_data.get("data_time_no") # 获取年月日 itm = 1 #定义项次 with connection.cursor() as cursor: # 获取数据里面当天最大的单号，然后处理生产单号 sql_no = """SELECT MAX(NO) AS NO FROM MCAF WHERE DD='{0}'""".format(t_today) # print(sql_no) monthly_odd_new = monthly_odd(sql_no) # 生产单号 for i in req_data["data_list"]: # 计算订单可维持天数 try : tolf_days = float(i["un_qty"])/float(i["s_capacity"]) except Exception: return http.HttpResponseForbidden('计算订单可维持天数，被除数不能为零') #计算现有配置可生产天数 try: p_d_f_e_configurations = float(i["un_qty"])/float(i["e_capacity"]) except Exception: return http.HttpResponseForbidden('计算现有可配置天数，被除数不能为零') #计算现有出勤人员合计 work_sum = float(i["e_a_workers"])+float(i["e_d_workers"]) # 计算直接工人274小时/25天 zj_274_24 = (float(i["ok_qty"])/(float(i["s_capacity"])+float(i["s_d_staffing"])))/25 #计算按274小时/25天配置人员 a_274_24 = (float(i["ok_qty"])/(float(i["s_capacity"])+float(i["s_d_staffing"])))/25+float(i["e_a_workers"]) #计算计划新增人员配置 new_personnel = (float(i["ok_qty"])/(float(i["s_capacity"])+float(i["s_d_staffing"])))/25-float(i["e_d_workers"]) #计算需求工时 need_hours = float(i["s_d_staffing"])*(float(i["un_qty"])/float(i["s_capacity"]))*11 #计算按25个工作日计算人员需求 a_25 = float(i["s_d_staffing"])*(float(i["un_qty"])/float(i["s_capacity"]))*11/274*1.2 #计算人员缺口 p_gap = float(i["s_d_staffing"])*(float(i["un_qty"])/float(i["s_capacity"]))*11/274*1.2-float(i["e_d_workers"]) sql = """INSERT INTO mcaf(itm,dep,no,dep_name,dd,un_qty,s_capacity,s_d_staffing,e_capacity,tolf_days,epl_personnel,p_d_f_e_configurations, a_strength,e_a_workers,work_sum,zj_274_24,a_274_24,new_personnel,rem,need_hours,a_25,e_d_workers,p_gap,ok_qty) VALUES ({0},'{1}','{2}','{3}','{4}',{5},{6},{7},{8},{9},{10},{11},'{12}',{13},{14},{15},{16},{17},'{18}',{19},{20},{21},{22},{23}) """.format(itm,i["dep"],monthly_odd_new,i["dep_name"],t_today,i["un_qty"],i["s_capacity"],i["s_d_staffing"],i["e_capacity"] , tolf_days,i["epl_personnel"],p_d_f_e_configurations,i["a_strength"],i["e_a_workers"] ,work_sum, zj_274_24,a_274_24,new_personnel,i["rem"],need_hours,a_25,i["e_d_workers"],p_gap,i["ok_qty"]) itm +=1 # print(sql) cursor.execute(sql,[]) # data = {"monthly_odd_new":monthly_odd_new} context = { "data": monthly_odd_new, "message": "保存成功", "code": 200 } return http.JsonResponse(context) def put(self,request): # with connection.cursor() as cursor: req_data = json.loads(request.body.decode())["data"]["data"] # 校验参数合法性 sqlinjection = sqlinjections().ifsql(req_data[0]) if sqlinjection: return http.HttpResponseForbidden("参数非法") # print(req_data) with connection.cursor() as cursor: for i in req_data: print(i) # 计算订单可维持天数 try: tolf_days = float(i["un_qty"]) / float(i["s_capacity"]) except Exception: return http.HttpResponseForbidden('计算订单可维持天数，被除数不能为零') # 计算现有配置可生产天数 try: p_d_f_e_configurations = float(i["un_qty"]) / float(i["e_capacity"]) except Exception: return http.HttpResponseForbidden('计算现有可配置天数，被除数不能为零') # 计算现有出勤人员合计 work_sum = float(i["e_a_workers"]) + float(i["e_d_workers"]) # 计算直接工人274小时/25天 zj_274_24 = (float(i["ok_qty"]) / (float(i["s_capacity"]) + float(i["s_d_staffing"]))) / 25 # 计算按274小时/25天配置人员 a_274_24 = (float(i["ok_qty"]) / (float(i["s_capacity"]) + float(i["s_d_staffing"]))) / 25 + float( i["e_a_workers"]) # 计算计划新增人员配置 new_personnel = (float(i["ok_qty"]) / (float(i["s_capacity"]) + float(i["s_d_staffing"]))) / 25 - float( i["e_d_workers"]) # 计算需求工时 need_hours = float(i["s_d_staffing"]) * (float(i["un_qty"]) / float(i["s_capacity"])) * 11 # 计算按25个工作日计算人员需求 a_25 = float(i["s_d_staffing"]) * (float(i["un_qty"]) / float(i["s_capacity"])) * 11 / 274 * 1.2 # 计算人员缺口 p_gap = float(i["s_d_staffing"]) * ( float(i["un_qty"]) / float(i["s_capacity"])) * 11 / 274 * 1.2 - float(i["e_d_workers"]) cursor.execute("""UPDATE MCAF SET ok_qty=%s, un_qty=%s, s_capacity=%s, s_d_staffing=%s, e_capacity=%s, tolf_days=%s, epl_personnel=%s, p_d_f_e_configurations=%s, a_strength=%s, e_a_workers=%s, work_sum=%s, zj_274_24=%s, a_274_24=%s, new_personnel=%s, rem=%s,need_hours=%s, a_25=%s, e_d_workers=%s, p_gap=%s WHERE dep=%s AND no=%s AND itm=%s""", [i["ok_qty"],i["un_qty"],i["s_capacity"],i["s_d_staffing"],i["e_capacity"],tolf_days,i["epl_personnel"] , p_d_f_e_configurations,i["a_strength"],i["e_a_workers"],work_sum,zj_274_24,a_274_24,new_personnel,i["rem"],need_hours,a_25 ,i["e_d_workers"],p_gap,i["dep"], i["no"], i["itm"]]) context = { "message": "修改成功", "code": 200 } return http.JsonResponse(context) def delete(self,request): # 获取参数 req_data = json.loads(request.body.decode()) dep = req_data["dep"] no = req_data["no"] itm = req_data["itm"] data = req_data["data"] # print(dep) # print(no) # print(itm) # print(data) with connection.cursor() as cursor: # 删除行 if dep!='': cursor.execute("""DELETE FROM MCAF WHERE dep=%s AND no=%s AND itm=%s""", [dep,no,itm]) #删除整张单 for i in data: cursor.execute("""DELETE FROM MCAF WHERE dep=%s AND no=%s AND itm=%s""", [i["dep"], i["no"], i["itm"]]) context = { "message": "删除成功", "code": 200 } return http.JsonResponse(context) # 月度产能评估表更具部门获取数据 class DepMacfDepData(View): def get(self, request): # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") try: dep = request.GET["dep"].split('->')[0] #部门编码->这里需要进行切割，因为传过来的是编码+名称（’编码->名称‘） except Exception: dep = request.GET["dep"] dep_ie = dep #这里是用于 try: selected = executeQuery(f"SELECT selected FROM DSCSETTING WHERE DEP='{dep}'")[0]["selected"] except Exception: return http.HttpResponseForbidden("部门不存在") if selected == 'T': dep = "AND b.dep=" + "'" + dep + "' " else: dep = "AND b.UP=" + "'" + dep + "' " +"AND b.selected='T'" sql = ("""SELECT b.dep,b.dep_name as dep_name,cast(sum(isnull(a.QTY_FIN,0)) as decimal(14,2)) as ok_qty,cast(sum(ISNULL(a.QTY,0)-isnull(a.QTY_FIN,0)) as decimal(14,2)) as un_qty, cast(max(B.s_capacity) as float) as s_capacity ,cast(max(C.total_manpower) as float) as s_d_staffing ,0 as e_capacity ,0 as tolf_days ,0 as epl_personnel ,0 as p_d_f_e_configurations, '' as a_strength ,0 as e_a_workers ,0 as work_sum,0 as zj_274_24 ,0 as a_274_24 ,0 as new_personnel ,'' as rem ,0 as need_hours, 0 as a_25 , 0 as e_d_workers ,0 as p_gap from mf_mo a left join DSCSETTING b on a.dep=b.dep left join MF_IEFILE c on a.dep=c.dep and iefile_no in(select max(iefile_no) from MF_IEFILE where dep='{0}') where isnull(a.CLOSE_ID,'')!='T' {1} group by b.dep,b.dep_name""".format(dep_ie,dep)) print(sql) try: result = executeQuery(sql) print(result) except Exception: return http.HttpResponseForbidden("没有查询到数据") if len(result)<=0: return http.HttpResponseForbidden("数据量为0，") context = { "data": result, "message": "获取部门数据成功", "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, "message": "获取部门成功", "code": 200 } return http.JsonResponse(context) #车间标准产能设置渲染模板 class DesSettingtm(View): def get(self,request): return render(request,'dessetting.html') # 车间标准产能设置渲染功能接口 class DesSetting(View): def get(self, request): # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") try: dep = request.GET["dep"].split('->')[0] except Exception: dep = request.GET["dep"] if len(dep) > 0: try: up = executeQuery(f"SELECT ISNULL(up,'') as up FROM DSCSETTING WHERE dep='{dep}'")[0]["up"] # print(up) except Exception: return http.HttpResponseForbidden("部门不存在") if up: dep = "UP=" + "'" + up + "'" else: dep = "dep=" + "'" + dep + "'" sql = """SELECT isnull(dep,'') as dep,isnull(dep_name,'') as dep_name,isnull(up_dd,'') as up_dd, cast(isnull(s_capacity,0) as varchar(20)) as s_capacity,cast(isnull(s_d_staffing,0) as varchar(20)) as s_d_staffing,up,selected FROM DSCSETTING WHERE {} """.format( dep) else: sql = """SELECT isnull(dep,'') as dep,isnull(dep_name,'') as dep_name,isnull(up_dd,'') as up_dd, isnull(s_capacity,0) as s_capacity,isnull(s_d_staffing,0) as s_d_staffing,up,selected FROM DSCSETTING""" try: result = executeQuery(sql) except Exception: return http.HttpResponseForbidden('没有查询到数据') data = [{"dep":"部门编码","dep_name":"部门名称","up_dd":"上次修改时间","s_capacity":"标准/满线产能","s_d_staffing":"标准直接人员配置","operation":"操作","up":"所属部门","selected":"产线否"}] if len(result) != 0: for i in result: data.append({"dep": i["dep"], "dep_name": i["dep_name"] ,"up_dd": i["up_dd"].strftime('%Y-%m-%d'),"s_capacity": float(i["s_capacity"]),"s_d_staffing": float(i["s_d_staffing"]),"up":i["up"],"selected":i["selected"]}) # print(data) context = { "data":data, "message":'获取信息成功', "code":'200' } return http.JsonResponse(context) def put(self,request): req_data = json.loads(request.body.decode())["data"] print(req_data) # 校验参数合法性 sqlinjection = sqlinjections().ifsql(req_data) if sqlinjection: return http.HttpResponseForbidden("参数非法") dep = req_data.get("dep") s_capacity = req_data.get("s_capacity") s_d_staffing = req_data.get("s_d_staffing") # up_dd = datetime.datetime.now() up_dd = datetime.datetime.now().strftime('%Y-%m-%d') selected = 'T' if str(req_data.get("selected"))=='True' else '' print(selected) if dep is None: return http.HttpResponseForbidden("部门不能为空值") sql = f"""UPDATE DSCSETTING SET s_capacity={s_capacity},s_d_staffing={s_d_staffing},up_dd=convert(datetime,'{up_dd}', 20),selected = '{selected}' WHERE DEP='{dep}'""" print(sql) with connection.cursor() as cursor: cursor.execute(sql,[]) context = { "message": "修改成功", "code": 200 } return http.JsonResponse(context) # ie文件号管理模板渲染 class IeFiletm(View): def get(self,request): return render(request,'iefile.html') # ie文件号管理功能接口 class IeFile(View): def get(self,request): try: iefile_no = request.GET["iefile_no"] except Exception: return http.HttpResponseForbidden("参数不存在") # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") # 查询表头 sql1 = """select iefile_no,cast(hr_up as float) as hr_up,left(Convert(varchar(100), ae_date, 23), 11)as ae_date,cast(postil_up as float)as postil_up,rem,file_serial, cast(total_manpower as float)as total_manpower,user_no,workshop_name,cast(point as float)as point,dep,dep_name, cast(job_time as float)as job_time,left(Convert(varchar(100), sys_date, 23), 11)as sys_date from MF_IEFILE WHERE iefile_no='{0}'""".format(iefile_no) try: mfiefile = executeQuery(sql1) except Exception: return http.HttpResponseForbidden("数据查询有问题mf") # print(len(mfiefile)) if len(mfiefile)>1: return http.HttpResponseForbidden("数据查询有问题mf") #查询表身BOM sql2 = """SELECT iefile_no,bom_no,cast(itm as FLOAT ) as itm FROM TF_IEFILE_BOM WHERE iefile_no='{}' ORDER BY ITM ASC""".format(iefile_no) # print(sql2) try: tfiefilebom = executeQuery(sql2) except Exception: return http.HttpResponseForbidden("数据查询异常bom") # 查询表身qty sql3 = """SELECT iefile_no,cast(qty as FLOAT ) as qty,cast(up as FLOAT ) as up,cast(itm as FLOAT ) as itm FROM TF_IEFILE_UP WHERE iefile_no='{}' ORDER BY ITM ASC""".format(iefile_no) try: tfiefileqty = executeQuery(sql3) except Exception: return http.HttpResponseForbidden("数据查询查询非法qty") content = { "datamf":mfiefile, "tfiefilebom":tfiefilebom, "tfiefileqty":tfiefileqty, "mssage":"ie文件查询成功", "code":200 } return http.JsonResponse(content) @transaction.atomic def post(self,request): req_data = json.loads(request.body.decode())["data"] hr_up = req_data["hr_up"] #1小时管理费用（元/小时） da_date = req_data["da_date"] #收件日期 postil_up = req_data["postil_up"] #文件批注单价 rem = req_data["rem"] #文件批注单价 file_serial = req_data["file_serial"] #IE工艺文件编号 total_manpower = req_data["total_manpower"] #标准总人力(人) workshop_name = req_data["workshop_name"] #工段名称 point = req_data["point"] #瓶颈时间（秒） print(req_data["dep"]) dep = req_data["dep"].split('->')[0] #作业线体(部门) dep_name = req_data["dep"].split('->')[1] #作业线体(部门名称) job_time = req_data["job_time"] #文作业时间（秒） username = req_data["username"] #输单电脑 sys_date = req_data["sys_date"] #输单日期 list_bom = req_data["list_bom"] #选择bom print(list_bom) list_qty = req_data["list_qty"] #阶梯单价 # 校验参数合法性表头 data_list = ['iefile_no', 'hr_up', 'da_date', 'postil_up', 'rem', 'file_serial', 'total_manpower', 'workshop_name', 'point', 'dep', 'dep_name', 'job_time', 'username', 'sys_date'] data_dict = {} for i, (k, v) in enumerate(req_data.items()): if k in data_list: data_dict[k] = str(v) sqlinjection = sqlinjections().ifsql(data_dict) if sqlinjection: return http.HttpResponseForbidden("mf参数非法") print(data_dict) # 校验参数合法性bom for i in list_bom: sqlinjection = sqlinjections().ifsql(i) if sqlinjection: return http.HttpResponseForbidden("bom参数非法") for i in list_qty: # 校验参数合法性qty sqlinjection = sqlinjections().ifsql(i) if sqlinjection: return http.HttpResponseForbidden("qty参数非法") # 判断部门 if len(req_data["dep"])<=0: return http.HttpResponseForbidden("请选择部门") t_today = datetime.datetime.now().strftime('%Y-%m-%d') # 获取年月日,用于插叙单号 with connection.cursor() as cursor: # 获取数据里面当天最大的单号，然后处理生产单号 try: sql_no = """SELECT MAX(right(iefile_no,LEN(iefile_no)-2)) AS NO FROM MF_IEFILE WHERE sys_date='{0}'""".format(t_today) monthly_odd_new = monthly_odd(sql_no) # 生产单号 monthly_odd_new = 'IE' + str(monthly_odd_new) except Exception: return http.HttpResponseForbidden("生成单号异常，请检查数据") # print(monthly_odd_new) sid = transaction.savepoint() # 开启事物 try: # 插入ie文件号表头 sql = """INSERT INTO MF_IEFILE(IEFILE_NO,HR_UP,AE_DATE,postil_up,rem,file_serial,total_manpower,user_no,workshop_name,point,dep,dep_name,job_time,sys_date) VALUES('%s',%s,'%s',%s,'%s','%s',%s,'%s','%s',%s,'%s','%s',%s,'%s')"""\ %(str(monthly_odd_new),hr_up,da_date,postil_up,rem,file_serial,total_manpower,username,workshop_name,point,dep,dep_name,job_time,sys_date) cursor.execute(sql, []) # 插入表身BOM数据 if len(list_bom)>0: bom_itm = 1 for i in list_bom: sql1 = """INSERT INTO TF_IEFILE_BOM(IEFILE_NO,BOM_NO,ITM)VALUES('%s','%s','%s')"""%(monthly_odd_new,i["bom_no"],bom_itm) cursor.execute(sql1, []) bom_itm +=1 # print(sql1) # 插入表身阶梯单价 if len(list_qty)>0: qty_itm = 1 for i in list_qty: sql2 = """INSERT INTO TF_IEFILE_UP(IEFILE_NO,QTY,UP,ITM)VALUES('%s','%s','%s','%s')""" % ( monthly_odd_new, i["qty"], i["up"],qty_itm) cursor.execute(sql2, []) qty_itm += 1 # print(sql1) except Exception: # print(111) transaction.savepoint_rollback(sid) return http.HttpResponseForbidden("数据增加失败") transaction.savepoint_commit(sid) # 提交事物 # print(111) content = { "data": monthly_odd_new, "mssage":"ie文件新增成功", "code":200 } return http.JsonResponse(content) @transaction.atomic def put(self,request): req_data = json.loads(request.body.decode())["data"] iefile_no = req_data.get("iefile_no") hr_up = req_data.get("hr_up") # 1小时管理费用（元/小时） da_date = req_data.get("da_date") # 收件日期 postil_up = req_data.get("postil_up") # 文件批注单价 rem = req_data.get("rem") # 文件批注单价 file_serial = req_data.get("file_serial") # IE工艺文件编号 total_manpower = req_data.get("total_manpower") # 标准总人力(人) workshop_name = req_data.get("workshop_name") # 工段名称 point = req_data.get("point") # 瓶颈时间（秒） dep = req_data.get("dep").split('->')[0] # 作业线体(部门) dep_name = req_data.get("dep").split('->')[1] # 作业线体(部门名称) job_time = req_data.get("job_time") # 文作业时间（秒） username = req_data.get("username") # 输单电脑 sys_date = req_data.get("sys_date") # 输单日期 list_bom = req_data.get("list_bom") # 选择bom list_qty = req_data.get("list_qty") # 阶梯单价 # print(req_data) # 校验参数合法性表头 data_list = ['iefile_no','hr_up','da_date','postil_up','rem','file_serial','total_manpower','workshop_name','point','dep', 'dep_name','job_time','username','sys_date'] data_dict={} for i, (k, v) in enumerate(req_data.items()): if k in data_list: data_dict[k] = str(v) sqlinjection = sqlinjections().ifsql(data_dict) if sqlinjection: return http.HttpResponseForbidden("mf参数非法") # 校验参数合法性bom # for i in list_bom: # sqlinjection = sqlinjections().ifsql(i) # if sqlinjection: # return http.HttpResponseForbidden("bom参数非法") for i in list_qty: # 校验参数合法性qty sqlinjection = sqlinjections().ifsql(i) if sqlinjection: return http.HttpResponseForbidden("qty参数非法") with connection.cursor() as cursor: sid = transaction.savepoint() # 开启事物 try: # 修改ie文件号表头 sql = """UPDATE MF_IEFILE SET HR_UP=%s,AE_DATE='%s',postil_up=%s,rem='%s',file_serial='%s',total_manpower=%s, user_no='%s',workshop_name='%s',point=%s,dep='%s',dep_name='%s',job_time=%s,sys_date='%s' WHERE IEFILE_NO='%s'"""\ %(hr_up,da_date,postil_up,rem,file_serial,total_manpower,username,workshop_name,point,dep,dep_name,job_time,sys_date,iefile_no) # print(sql) cursor.execute(sql, []) # 修改表身qty for i in list_qty: # print(i) if "iefile_no" in i.keys(): sql2 = """UPDATE TF_IEFILE_UP SET QTY=%s,UP=%s WHERE IEFILE_NO='%s' AND ITM='%s'""" % ( i["qty"], i["up"],iefile_no, i["itm"]) cursor.execute(sql2, []) else: sql3 = """SELECT MAX(ITM) AS ITM FROM TF_IEFILE_UP WHERE IEFILE_NO='%s'"""%(iefile_no) mfiefile = executeQuery(sql3)[0]["ITM"] if mfiefile is None: mfiefile = 0 sql4 = """INSERT INTO TF_IEFILE_UP(IEFILE_NO,QTY,UP,ITM)VALUES('%s','%s','%s','%s')""" % ( iefile_no, i["qty"], i["up"], mfiefile+1) i["iefile_no"]=iefile_no i["itm"] = mfiefile + 1 cursor.execute(sql4, []) #修改表身BOM for i in list_bom: # print(i) if "iefile_no" not in i.keys(): sql5 = """SELECT MAX(ITM) AS ITM FROM TF_IEFILE_BOM WHERE IEFILE_NO='%s'"""%(iefile_no) mfiefile_bom = executeQuery(sql5)[0]["ITM"] if mfiefile_bom is None: mfiefile_bom = 0 sql6 = """INSERT INTO TF_IEFILE_BOM(IEFILE_NO,BOM_NO,ITM)VALUES('%s','%s','%s')""" % ( iefile_no, i["bom_no"], mfiefile_bom+1) i["iefile_no"]=iefile_no i["itm"] = mfiefile_bom + 1 cursor.execute(sql6, []) print(sql6) except Exception: # print(111) transaction.savepoint_rollback(sid) return http.HttpResponseForbidden("数据修改失败") transaction.savepoint_commit(sid) # 提交事物 content = { "list_qty":list_qty, "list_bom":list_bom, "mssage": "修改成功", "code": 200 } return http.JsonResponse(content) def delete(self,request): req_data = json.loads(request.body.decode()) iefile_no = req_data["iefile_no"] # print(iefile_no) # 校验参数合法性 sqlinjection = sqlinjections().ifsql(req_data) if sqlinjection: return http.HttpResponseForbidden("参数非法") with connection.cursor() as cursor: sid = transaction.savepoint() # 开启事物 try: # 删除表头 sql1 = """DELETE FROM MF_IEFILE WHERE iefile_no = '{0}'""".format(iefile_no) cursor.execute(sql1, []) # 删除BOM sql2 = """DELETE FROM TF_IEFILE_BOM WHERE iefile_no = '{0}'""".format(iefile_no) cursor.execute(sql2, []) # 删除QTY sql3 = """DELETE FROM TF_IEFILE_UP WHERE iefile_no = '{0}'""".format(iefile_no) cursor.execute(sql3, []) except Exception: # print(111) transaction.savepoint_rollback(sid) return http.HttpResponseForbidden("删除数据失败") transaction.savepoint_commit(sid) # 提交事物 content = { "mssage": "删除成功", "code": 200 } return http.JsonResponse(content) # 查询ie文件号管理表头及删除表身 class MfIeFile(View): def get(self, request): date_qi = request.GET["date_qi"] date_zhi = request.GET["date_zhi"] select_file_serial = request.GET["select_file_serial"] select_dep = request.GET["select_dep"] select_iefile_no = request.GET["select_iefile_no"] # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") if date_qi is None or date_zhi is None: return http.HttpResponseForbidden("参数不全") parameter = " WHERE left(Convert(varchar(100), ae_date, 23), 11)>="+"'"+date_qi+"'"+" AND "+"left(Convert(varchar(100), ae_date, 23), 11)<="+"'"+date_zhi+"'" # 处理IE文件编号 if len(select_file_serial)>0: select_file_serial = " AND file_serial LIKE"+"'%%"+select_file_serial+"%%'" else: select_file_serial='' #处理部门 if len(select_dep) > 0: select_dep = " AND dep LIKE" + "'%%" + select_dep + "%%'" else: select_dep = '' # 处理IE文件单号 if len(select_iefile_no) > 0: select_iefile_no = " AND iefile_no LIKE" + "'%%" + select_iefile_no + "%%'" else: select_iefile_no = '' parameter = parameter + select_file_serial + select_dep + select_iefile_no # print(parameter) sql = """select iefile_no,cast(hr_up as float) as hr_up,left(Convert(varchar(100), ae_date, 23), 11)as ae_date,cast(postil_up as float)as postil_up,rem,file_serial, cast(total_manpower as float)as total_manpower,user_no,workshop_name,cast(point as float)as point,dep,dep_name, cast(job_time as float)as job_time,left(Convert(varchar(100), sys_date, 23), 11)as sys_date from MF_IEFILE {0}""".format(parameter) # print(sql) result = executeQuery(sql) content = { "data": result, "mssage": "ie文件查询成功", "code": 200 } return http.JsonResponse(content) def delete(self,request): req_data = json.loads(request.body.decode()) iefile_no = req_data["iefile_no"] try: bom = req_data["bom"] except Exception: return http.HttpResponseForbidden("bom异常") itm = req_data["itm"] if not all([iefile_no,itm]): return http.HttpResponseForbidden("参数不全") # 校验参数合法性 sqlinjection = sqlinjections().ifsql(req_data) if sqlinjection: return http.HttpResponseForbidden("参数非法") if len(bom)<=0: sql = """DELETE FROM TF_IEFILE_UP WHERE iefile_no='{0}' AND itm='{1}'""".format(iefile_no,itm) else: sql="""DELETE FROM TF_IEFILE_BOM WHERE iefile_no='{0}' AND itm='{1}'""".format(iefile_no,itm) with connection.cursor() as cursor: try: cursor.execute(sql, []) except Exception: return http.HttpResponseForbidden("异常操作，删除失败") content = { "mssage": "删除成功", "code": 200 } return http.JsonResponse(content) # ie工艺号文件维护渲染 class IeFileMaintaintm(View): def get(self,request): return render(request,'iefilemaintain.html') # ie工艺号文件维护接口 class IeFileMaintain(View): def get(self,request): # 校验参数合法性 sqlinjection = sqlinjections().ifsql(request.GET) if sqlinjection: return http.HttpResponseForbidden("参数非法") data_timeqi = request.GET['data_timeqi'] #开始时间 data_timezhi = request.GET['data_timezhi'] #结束时间 erp_dep = request.GET['erp_dep'] #部门 mf_mmo = request.GET['mf_mmo'] #缴库单号 iefileno = request.GET['iefileno'] #ie单号 if not all([data_timeqi,data_timezhi]): return http.HttpResponseForbidden('请输入日期') # 校验日期格式 try: datetime.datetime.strptime(data_timeqi, '%Y-%m-%d') datetime.datetime.strptime(data_timezhi, '%Y-%m-%d') except Exception: return http.HttpResponseForbidden('日期格式输入不正确') if len(erp_dep)<=0: erp_dep='' else: erp_dep = ' AND A.DEP='+"'"+erp_dep+"'" if len(iefileno) <= 0: iefileno = '' else: iefileno = ' AND A.iefile_no=' + "'" + iefileno + "'" parameter = " AND left(Convert(varchar(100), a.ae_date, 23), 11)>=" + "'" + data_timeqi + "'"+" AND left(Convert(varchar(100), a.ae_date, 23), 11)<=" + "'" + data_timezhi + "'"\ + erp_dep + iefileno if len(mf_mmo)<=0: sql = """SELECT B.BOM_NO as bom_no,A.AE_DATE as ae_date,A.HR_UP as hr_up,A.file_serial,A.workshop_name,A.dep,A.dep_name,A.total_manpower,A.point, A.job_time,A.postil_up,A.rem,A.IEFILE_NO AS iefile_no,A.sys_date,A.user_no,A.point*A.total_manpower AS bzsj,A.job_time/(A.point*A.total_manpower) AS scxphl, 1-A.job_time/(A.point*A.total_manpower) AS scxbphl,A.point*A.total_manpower-A.job_time AS ssgs,A.point AS scjp,3600/A.point AS xscn, (3600/A.point)/A.total_manpower AS rjcn,8*(3600/A.point) AS bxscn,A.postil_up/((3600/A.point)/A.total_manpower) AS djcb,A.total_manpower*8 AS bzzys,ISNULL(cls_id,'F') AS qiting, ISNULL(cls_id_rem,'') AS qitingexplain,''AS choice FROM MF_IEFILE A,TF_IEFILE_BOM B WHERE A.IEFILE_NO = B.IEFILE_NO {0} ORDER BY A.IEFILE_NO DESC""".format(parameter) else: mf_mmo = ' AND B.BOM_NO=C.ID_NO ' +'AND C.MM_NO='+"'"+mf_mmo+"'" parameter = parameter + mf_mmo sql = """SELECT B.BOM_NO as bom_no,A.AE_DATE as ae_date,A.HR_UP as hr_up,A.file_serial,A.workshop_name,A.dep,A.dep_name,A.total_manpower,A.point, A.job_time,A.postil_up,A.rem,A.IEFILE_NO AS iefile_no,A.sys_date,A.user_no,A.point*A.total_manpower AS bzsj,A.job_time/(A.point*A.total_manpower) AS scxphl, 1-A.job_time/(A.point*A.total_manpower) AS scxbphl,A.point*A.total_manpower-A.job_time AS ssgs,A.point AS scjp,3600/A.point AS xscn, (3600/A.point)/A.total_manpower AS rjcn,8*(3600/A.point) AS bxscn,A.postil_up/((3600/A.point)/A.total_manpower) AS djcb, A.total_manpower*8 AS bzzys,'T' AS qiting,'' AS qitingexplain,''AS choice FROM MF_IEFILE A,TF_IEFILE_BOM B,TF_MM0 C WHERE A.IEFILE_NO = B.IEFILE_NO AND B.BOM_NO=C.ID_NO {0}""".format(parameter) # print(sql) try: result = executeQuery(sql) except Exception: return http.HttpResponseForbidden('没有查询到数据') data = [{"bom_no":"BOM配方","ae_date":'收件日期',"hr_up":"1小时管理费用（元/小时）","file_serial":"IE工艺文件编号","workshop_name":"工段名称", "dep_name":"作业线体","total_manpower":"标准总人力(人)","point":"瓶颈时间（秒）","job_time":"作业时间（秒）","postil_up":"文件批注单价(元)", "rem":"文件备注","iefile_no":"系统输入单号","sys_date":"输单日期","user_no":"输单电脑","bzsj":"标准时间(秒)","scxphl":"生产线平衡率","scxbphl":"不平衡损失率", "ssgs":"损失工时(秒)","scjp":"生产节拍(秒)","xscn":"小时产能(套)","rjcn":"人均产能(套)","bxscn":"8小时产能(套)","djcb":"单件成本(套)","bzzys":"标准总用时(小时)", "qiting":"启/停","qitingexplain":"启/停说明","choice":"选择"}] if len(result) != 0: for i in result: data.append({"bom_no": i["bom_no"], "ae_date": i["ae_date"].strftime('%Y-%m-%d') ,"hr_up": float(i["hr_up"]),"file_serial": i["file_serial"],"workshop_name": i["workshop_name"], "dep_name":i["dep_name"],"total_manpower":float(i["total_manpower"]),"point":float(i["point"]),"job_time":float(i["job_time"]),"postil_up":float(i["postil_up"]), "rem":i["rem"],"iefile_no":i["iefile_no"],"sys_date":i["sys_date"].strftime('%Y-%m-%d'),"user_no":i["user_no"],"bzsj":float(i["bzsj"]),"scxphl":float('%.2f' %i["scxphl"]),"scxbphl":float('%.2f' %i["scxbphl"]), "ssgs": float(i["ssgs"]),"scjp": float(i["scjp"]),"xscn": float(i["xscn"]),"rjcn": float(i["rjcn"]),"bxscn": float(i["bxscn"]),"djcb": float(i["djcb"]),"bzzys": float(i["bzzys"]), "qiting":i["qiting"],"qitingexplain":i["qitingexplain"],"choice":i["choice"]}) # print(data) content = { "data":data, "mssage": "ie文件查询成功", "code": 200 } return http.JsonResponse(content) # 设置启用状态 def post(self,request): req_data = json.loads(request.body.decode())["data"] # print(req_data) sid = transaction.savepoint() # 开启事物 with connection.cursor() as cursor: for i in req_data["selected_data"]: print(i) sql = """UPDATE MF_IEFILE SET CLS_ID='{0}',CLS_ID_REM='{1}' WHERE IEFILE_NO='{2}'""".format(i["qiting"],i["qitingexplain"],i["iefile_no"]) print(sql) try: cursor.execute(sql, []) except Exception: return http.HttpResponseForbidden("异常操作，修改失败") transaction.savepoint_commit(sid) # 提交事物 content = { "mssage": "设置启用状态成功", "code": 200 } return http.JsonResponse(content) def put(self,request): req_data = json.loads(request.body.decode())["data"] selected_data = req_data.get("selected_data") print(selected_data) hr_up = req_data.get("hr_up") #1小时管理费用（元/小时） file_serial = req_data.get("file_serial") #IE工艺文件编号 workshop_name = req_data.get("workshop_name") #工段名称 dep_name = req_data.get("dep_name") #作业线体 total_manpower = req_data.get("total_manpower") #标准总人力(人) point = req_data.get("point") #瓶颈时间（秒 job_time = req_data.get("job_time") #作业时间（秒） bzsj = req_data.get("bzsj") #标准时间秒 postil_up = req_data.get("postil_up") #文件批注单价(元) scxphl = req_data.get("scxphl") #生产线平衡率 scxbphl = req_data.get("scxbphl") #不平衡损失率 ssgs = req_data.get("ssgs") #损失工时(秒) scjp = req_data.get("scjp") #生产节拍(秒) xscn = req_data.get("xscn") #小时产能(套) rjcn = req_data.get("rjcn") #人均产能(套) bxscn = req_data.get("bxscn") #8小时产能(套) djcb = req_data.get("djcb") #单件成本(套) bzzys = req_data.get("bzzys") #标准总用时(小时) mm_no=req_data.get("mm_no") data_timeqi_mo=req_data.get("data_timeqi_mo") data_timezhi_mo=req_data.get("data_timezhi_mo") if not all([data_timeqi_mo,data_timezhi_mo]): return http.HttpResponseForbidden("请选择时间") # 校验日期格式 try: datetime.datetime.strptime(data_timeqi_mo, '%Y-%m-%d') datetime.datetime.strptime(data_timezhi_mo, '%Y-%m-%d') except Exception: return http.HttpResponseForbidden('日期格式输入不正确') data_time = " AND left(Convert(varchar(100), MM_DD, 23), 11)>=" + "'" + data_timeqi_mo + "'" + " AND left(Convert(varchar(100), MM_DD, 23), 11)<=" + "'" + data_timezhi_mo + "'" # 判断是否有数据 if len(selected_data)<=0: return http.HttpResponseForbidden("没有选择数据") sid = transaction.savepoint() # 开启事物 with connection.cursor() as cursor: for i in selected_data: # 1、利用单号查询阶梯单价 sql1 = """SELECT IEFILE_NO,QTY,UP FROM TF_IEFILE_UP WHERE IEFILE_NO ='{0}' ORDER BY QTY ASC""".format(i["iefile_no"]) try: result_up = executeQuery(sql1) except Exception: return http.HttpResponseForbidden("阶梯单价查询异常") #1、1查询缴库单信息 if len(mm_no)<=0: sql2 = """SELECT MM_NO,ID_NO,PRD_NO,CAST(QTY AS FLOAT ) AS QTY,ITM FROM TF_MM0 WHERE MM_ID='MM' AND ID_NO='{0}' {1}""".format(i["bom_no"],data_time) print(sql2) else: sql2 = """SELECT MM_NO,ID_NO,PRD_NO,CAST(QTY AS FLOAT ) AS QTY,ITM FROM TF_MM0 WHERE MM_ID='MM' AND ID_NO='{0}' AND MM_NO='{1}' {2}""".format(i["bom_no"],mm_no,data_time) print(sql2) try: result_mm_no = executeQuery(sql2) if len(result_mm_no)<=0: return http.HttpResponseForbidden("指定更新缴库单号时间范围内无缴库单") except Exception: return http.HttpResponseForbidden("指定更新缴库单号时间范围内无缴库单") # 如果查询得到缴库单的时候遍历 if len(result_mm_no)>0: # 这里可以拿到每个缴库单的数量 for result_mm_no_qty in result_mm_no: # 更新ie文件信息数据除阶梯单价外 sql = """UPDATE TF_MM0_z SET H_GLFY_MB='{0}',IE_NO_MB='{1}',GDMC_MB='{2}',ZYXT_MB='{3}',BZZRL_R_MB='{4}',PJSJ_S_MB='{5}',ZYSJ_S_MB='{6}', WJBZDJ_MB='{7}',BZSJ_S_MB='{8}',SCXPHL_MB='{9}',BPHSSL_MB='{10}',SSGS_S_MB='{11}',SCJP_S_MB='{12}',XXCN_T_MB='{13}',RJCN_T_MB='{14}',HCN_T_MB8='{15}', DJCB_T_MB='{16}',BZZYS_H_MB='{17}' WHERE MM_NO='{18}' AND ITM='{19}'"""\ .format(i["hr_up"],i["file_serial"],i["workshop_name"],i["dep_name"],i["total_manpower"],i["point"],i["job_time"],i["postil_up"],i["bzsj"], i["scxphl"],i["scxbphl"],i["ssgs"],i["scjp"],i["xscn"],i["rjcn"],i["bxscn"],i["djcb"],i["bzzys"],result_mm_no_qty["MM_NO"],result_mm_no_qty["ITM"]) # print(sql) cursor.execute(sql, []) # 有设置阶梯单价的时候 if len(result_up)>0: # 继续遍历阶梯单价 for result_row in result_up: if result_mm_no_qty["QTY"]<=result_row["QTY"]: try: sql3 = """UPDATE TF_MM0_z SET JJDJ_MB={0} WHERE MM_NO='{1}' AND ITM='{2}'""".format(result_row["UP"],result_mm_no_qty["MM_NO"],result_mm_no_qty["ITM"]) # print(sql3) cursor.execute(sql3, []) break except Exception: return http.HttpResponseForbidden("更新数据异常1") #没有设置阶梯单价的时候设置计件单价为文件标注单价 else: # 查询批注单价 try: sql4 = """SELECT CAST(postil_up AS FLOAT) AS postil_up FROM MF_IEFILE WHERE IEFILE_NO='{0}'""".format(i["iefile_no"]) postil_up = executeQuery(sql4)[0]["postil_up"] except Exception: return http.HttpResponseForbidden("查询批注单价异常") try: sql3 = """UPDATE TF_MM0_z SET JJDJ_MB={0} WHERE MM_NO='{1}' AND ITM='{2}'""".format(postil_up, result_mm_no_qty["MM_NO"], result_mm_no_qty["ITM"]) cursor.execute(sql3, []) except Exception: return http.HttpResponseForbidden("更新数据异常2") # if len(result_up)>0: # for result_row in result_up: # for result_mm_no_qty in result_mm_no # print(2222222222222) # else: # print("直接更新批注单价") transaction.savepoint_commit(sid) # 提交事物 content = { "mssage": "修改成功", "code": 200 } return http.JsonResponse(content) def delete(self,request): req_data = json.loads(request.body.decode()) # print(req_data) sid = transaction.savepoint() # 开启事物 with connection.cursor() as cursor: for i in req_data["selected_data"]: print(i) sql = """DELETE FROM MF_IEFILE WHERE IEFILE_NO='{0}'""".format(i["iefile_no"]) print(sql) try: cursor.execute(sql, []) except Exception: return http.HttpResponseForbidden("异常操作，修改失败") transaction.savepoint_commit(sid) # 提交事物 content = { "mssage": "删除成功", "code": 200 } return http.JsonResponse(content) #ie文件号明细表渲染 class IeFileMaintainDetailtm(View): def get(self,request): return render(request, 'iefilemaintaindetail.html') #数据接口 class IeFileMaintainDetail(View): def get(self, request): erp_dep = request.GET['erp_dep'] iefileno = request.GET['iefileno'] data_timeqi = request.GET['data_timeqi'] data_timezhi = request.GET['data_timezhi'] cls_id = request.GET['cls_id'] if not all([data_timeqi,data_timezhi,cls_id]): return http.HttpResponseForbidden("请选择时间,以及其他相关参数") # 校验日期格式 try: datetime.datetime.strptime(data_timeqi, '%Y-%m-%d') datetime.datetime.strptime(data_timezhi, '%Y-%m-%d') except Exception: return http.HttpResponseForbidden('日期格式输入不正确') if erp_dep : erp_dep = " AND C.DEP="+"'"+erp_dep+"'" else: erp_dep='' if iefileno : iefileno = " AND A.IEFILE_NO="+"'"+iefileno+"'" else: iefileno='' if cls_id=='1': cls_id='' elif cls_id=='2': cls_id=" AND ISNULL(A3.CLOSE_ID,'')='T'" else: cls_id = " AND ISNULL(A3.CLOSE_ID,'')<>'T'" data_time=" AND left(Convert(varchar(100), c.MM_DD, 23), 11)>=" + "'" + data_timeqi + "'" + " AND left(Convert(varchar(100), c.MM_DD, 23), 11)<=" + "'" + data_timezhi + "'" parameter = data_time+ erp_dep + iefileno sql = """select isnull(e.gdmc_mb,'') as gdmc_mb,d.prd_no,isnull(d.PRD_NAME,'') as prd_name,isnull(d.SO_NO,'') as so_no,d.mm_no ,isnull(e.ZYXT_MB,'') as zyxt_mb,ISNULL(f.name,'') AS dep_name,g.name as t_wh,isnull(cast(d.qty as float),0) as t_qty ,cast(isnull(e.JJDJ_MB,0) as float) as jjdj_mb,CAST(isnull(e.JJDJ_MB,0)*isnull(d.qty,0) AS FLOAT) as jj_up ,CAST(isnull(a1.mo_bc_qty,0) AS FLOAT) AS mo_bc_qty ,CAST(isnull(a2.mo_ym_qty,0) AS FLOAT) AS mo_ym_qty ,CAST((isnull(a3.QTY_FIN,0)-a3.qty)/a3.qty AS FLOAT) AS cj_qty ,cast(isnull(E.PJSJ_S_MB,0) as float) AS pjsj_s_mb,CAST(isnull(A.job_time,0) AS FLOAT) AS job_time,A.file_serial ,'' as sjgs,'' as kqjs,'' as wdyy,'' as gscs,'' as bzjkjs,'' as bzjkgs ,cast(isnull(E.xxcn_t_mb,0) as float) as xxcn_t_mb,cast(isnull(E.rjcn_t_mb,0) as float) as rjcn_t_mb ,case when isnull(a3.CLOSE_ID,'')='T' then 'T'else 'F' end as mo_cli_id,a3.mo_no,cast(a3.qty as float) as mo_qty ,cast(isnull(a3.QTY_FIN_UNSH,0) as float) as ws__mo_qty,cast(isnull(a3.QTY_FIN,0) as float) as sh_mo_qty ,cast(a.total_manpower as float) as total_manpower,a.iefile_no,d.itm,d.id_no from MF_IEFILE A , TF_IEFILE_BOM b , MF_MM0 c , TF_MM0 d --包材齐套率 LEFT JOIN (select ROUND(sum(a.QTY/(a.QTY_RSV+isnull(a.QTY_LOST,0))),2) as mo_bc_qty,mo_no from TF_MO a,prdt b,INDX C where a.prd_no=b.prd_no AND C.IDX_NO=B.IDX1 AND C.IDX_UP='C' GROUP BY A.MO_NO) A1 ON a1.mo_no=d.mo_no --研磨品齐套率 LEFT JOIN (select ROUND(sum(a.QTY/(a.QTY_RSV+isnull(a.QTY_LOST,0))),2) as mo_ym_qty,mo_no from TF_MO a,prdt b,INDX C where a.prd_no=b.prd_no AND C.IDX_NO=B.IDX1 AND C.IDX_UP='A' GROUP BY A.MO_NO) A2 ON a2.mo_no=d.mo_no --超交率 LEFT JOIN MF_MO A3 ON A3.MO_NO=D.MO_NO , TF_MM0_z e , DEPT F , MY_wh G where A.CLS_ID='T' AND a.iefile_no=b.iefile_no and b.bom_no=d.id_no and c.MM_NO=d.MM_NO and d.MM_NO=e.MM_NO and d.itm=e.itm and c.dep=f.dep and d.wh=g.wh {0} {1}""".format(parameter,cls_id) print(sql) try: result = executeQuery(sql) if len(result)<=0: return http.HttpResponseForbidden('没有查询到数据') except Exception: return http.HttpResponseForbidden('没有查询到数据') data=[{"gdmc_mb":"工序类型","prd_no":"产品","prd_name":"产品名称","so_no":"计划受订","mm_no":"缴库单号","zyxt_mb":"生产线","dep_name":"缴库班组", "t_wh":"仓库","t_qty":"缴库数量","jjdj_mb":"计件单价","jj_up":"计件金额","mo_bc_qty":"包材齐套率","mo_ym_qty":"研磨品齐套率","cj_qty":"超缴率", "pjsj_s_mb":"瓶颈节拍(S)","job_time":"参考工时","file_serial":"工艺文件","sjgs":"实际工时","kqjs":"考勤机时","wdyy":"未达成原因分析/责任部门", "gscs":"改善措施/其他说明项","bzjkjs":"标准缴库机时","bzjkgs":"标准缴库工时","xxcn_t_mb":"IE小时产能","rjcn_t_mb":"IE人均小时产能","mo_cli_id":"结案状态", "mo_no":"制令单号","mo_qty":"需生产量","ws__mo_qty":"缴库未审","sh_mo_qty":"缴库已审","dep_qty_sum":"车间达成数量合计","total_manpower":"车间效率标准","s_ie":"IE单价"}] if len(result)>10000: return http.HttpResponseForbidden("查询记录数过大请重新缩小查询范围") if len(result) != 0: for i in result: # 查询车间达成数量合计 i["dep_qty_sum"]='' if len(i["zyxt_mb"])>0: sql2 ="""select isnull(cast(sum(c.qty) as float),0) dep_qty_sum from TF_MM0 c,TF_MM0_z b where c.mm_no=b.mm_no and c.itm=b.itm and b.zyxt_mb in(select dep_name from DSCSETTING where up=(SELECT up FROM DSCSETTING where dep_name='{0}' and selected='T') ) {1}""".format(i["zyxt_mb"],data_time) # print(sql2) try: dep_qty_sum = executeQuery(sql2)[0]["dep_qty_sum"] # print(dep_qty_sum) except Exception: dep_qty_sum='' i["dep_qty_sum"]=dep_qty_sum # 处理IE单价 # 1、利用单号查询阶梯单价 sql3 = """SELECT IEFILE_NO,CAST(QTY AS FLOAT) AS QTY,CAST(UP AS FLOAT) AS UP FROM TF_IEFILE_UP WHERE IEFILE_NO ='{0}' ORDER BY QTY ASC""".format(i["iefile_no"]) try: result_up = executeQuery(sql3) except Exception: return http.HttpResponseForbidden("阶梯单价查询异常") # 1、1查询缴库单信息 sql4 = """SELECT MM_NO,ID_NO,PRD_NO,CAST(QTY AS FLOAT ) AS QTY,ITM FROM TF_MM0 WHERE MM_ID='MM' AND ID_NO='{0}' AND MM_NO='{1}' AND ITM='{2}'""".format( i["id_no"], i["mm_no"],i["itm"]) try: result_mm_no = executeQuery(sql4)[0] except Exception: return http.HttpResponseForbidden("查询缴库单异常") # 遍历阶梯单价并判断是否得到合法的ie单价 s_ie='' for result_qty in result_up: if result_mm_no["QTY"]<=result_qty["QTY"]: s_ie = result_qty["UP"] break data.append({"gdmc_mb":i["gdmc_mb"],"prd_no":i["prd_no"],"prd_name":i["prd_name"],"so_no":i["so_no"],"mm_no":i["mm_no"],"zyxt_mb":i["zyxt_mb"],"dep_name":i["dep_name"], "t_wh":i["t_wh"],"t_qty":i["t_qty"],"jjdj_mb":i["jjdj_mb"],"jj_up":i["jj_up"],"mo_bc_qty":i["mo_bc_qty"],"mo_ym_qty":i["mo_ym_qty"],"cj_qty":i["cj_qty"], "pjsj_s_mb": i["pjsj_s_mb"],"job_time":i["job_time"],"file_serial":i["file_serial"],"sjgs":i["sjgs"],"kqjs":i["kqjs"],"wdyy":i["wdyy"],"gscs":i["gscs"], "bzjkjs": i["bzjkjs"],"bzjkgs":i["bzjkgs"],"xxcn_t_mb":i["xxcn_t_mb"],"rjcn_t_mb":i["rjcn_t_mb"],"mo_cli_id":i["mo_cli_id"],"mo_no":i["mo_no"],"mo_qty":i["mo_qty"], "ws__mo_qty": i["ws__mo_qty"],"sh_mo_qty":i["sh_mo_qty"],"dep_qty_sum":i["dep_qty_sum"],"total_manpower":i["total_manpower"],"s_ie":s_ie}) content = { "data": data, "mssage": "查询明细成功", "code": 200 } return http.JsonResponse(content) #ie文件号统计表渲染 class IeFileMaintaInfotm(View): def get(self, request): return render(request, 'iefilemaintaininfo.html') # ie文件号统计表数据接口 class IeFileMaintaInfo(View): def get(self, request): erp_dep = request.GET['erp_dep'] iefileno = request.GET['iefileno'] data_timeqi = request.GET['data_timeqi'] data_timezhi = request.GET['data_timezhi'] cls_id = request.GET['cls_id'] if not all([data_timeqi, data_timezhi, cls_id]): return http.HttpResponseForbidden("请选择时间,以及其他相关参数") # 校验日期格式 try: datetime.datetime.strptime(data_timeqi, '%Y-%m-%d') datetime.datetime.strptime(data_timezhi, '%Y-%m-%d') except Exception: return http.HttpResponseForbidden('日期格式输入不正确') if erp_dep: erp_dep = " AND A.DEP=" + "'" + erp_dep + "'" else: erp_dep = '' if iefileno: iefileno = " AND C.IEFILE_NO=" + "'" + iefileno + "'" else: iefileno = '' if cls_id == '1': cls_id = '' elif cls_id == '2': cls_id = " AND ISNULL(B.CLOSE_ID,'')='T'" else: cls_id = " AND ISNULL(B.CLOSE_ID,'')<>'T'" data_time = " AND left(Convert(varchar(100), A.MM_DD, 23), 11)>=" + "'" + data_timeqi + "'" + " AND left(Convert(varchar(100), A.MM_DD, 23), 11)<=" + "'" + data_timezhi + "'" parameter = data_time + erp_dep + iefileno +cls_id sql = """SELECT B.dep,C.dep_name,CAST(SUM(B.qty_fin) AS FLOAT) AS qty_fin,CAST(sum(C.total_manpower) AS FLOAT) AS tjbz,CAST(SUM(B.qty) AS FLOAT) AS qty,CAST(ROUND(SUM(B.QTY_FIN)/SUM(C.total_manpower),2) AS FLOAT) as xl FROM TF_MM0 A , MF_MO B,MF_IEFILE C WHERE A.MO_NO=B.MO_NO AND A.DEP=B.DEP AND A.DEP=C.DEP AND C.CLS_ID='T' {0} GROUP BY B.DEP,C.dep_name """.format(parameter) print(sql) try: result = executeQuery(sql) if len(result)<=0: return http.HttpResponseForbidden('没有查询到数据') except Exception: return http.HttpResponseForbidden('没有查询到数据') data=[{"dep":"车间编码","dep_name":"车间名称","tjbz":"统计标准","qty_fin":"统计数量","xl":"效率"}] for i in result: data.append({"dep":i["dep"],"dep_name":i["dep_name"],"tjbz":i["tjbz"],"qty_fin":i["qty_fin"],"xl":i["xl"]}) print(data) content = { "data": data, "mssage": "查询明细成功", "code": 200 } return http.JsonResponse(content) # pc月度生产计划模板 class PcydscJhtm(View): def get(self, request): return render(request, 'pcydscjhtm.html') # pc月度生产计划数据接口 class PcydscJh(ModelViewSet): pagination_class = MyPageNumberPagination # 设置过滤 filter_backends = (DjangoFilterBackend, filters.OrderingFilter) # filterset_fields = ('need_dd', 'cus_no', 'so_no', 'mrp_no') filter_class = PcCourseFilterSet serializer_class = PcydpcjhZserializer queryset = View_pcydscjh.objects.all() def list(self, request, *args, **kwargs): # print(self.get_queryset()) queryset = self.filter_queryset(self.get_queryset()) # 定义数据存储空间 data_list = [] print(queryset) for i in queryset: print(i) # 校验是否回复时间 if i.pmc_dd: # 转换时间格式 print(str(i.pmc_dd)) try: pmc_dd_new = datetime.datetime.strptime(str(i.pmc_dd), '%Y-%m-%d') except Exception: return Response("时间类型错误", status=status.HTTP_400_BAD_REQUEST) # 递归查询得到所有bom已经累计bom的前置天数 sql = """select so_no,s_id_no,bom_no,prd_no,id_no,mo_no,sort,oldd,round(dd,0) as dd,qty from cst_tf_mp3 where so_no='{0}' and s_id_no='{1}' order by sort asc""".format( i.so_no, i.mrp_no + '->') result1 = executeQuery(sql) for ii in result1: # 计算计划完工日期 # print(round((ii["dd"]) / 9 + 2)) # print(ii["dd"]) print(pmc_dd_new - datetime.timedelta(days=ii["dd"])) # jh_dd = pmc_dd_new - (datetime.timedelta(round((ii["dd"]) / 9 + 2))) jh_dd = pmc_dd_new - datetime.timedelta(days=ii["dd"]) # print(str(jh_dd)+'---'+str(datetime.timedelta(round((ii["dd"]) / 9 + 2)))+'--'+ii["prd_no"]) # 查询制令单与bom相符的数据 sql2 = """select id,pmc_dd,cus_snm,need_dd,so_no,mrp_no,mrp_name,mo_no,mo_no_no,mo_no_name,qty,qty_fin,w_qty,dcl,'{2}' as jhwcrq,mo_dep_name,zx,cn,ddjd,ymp,rq,bq,bc from pcydpcjh where so_no='{0}' and mo_no='{1}'""".format(i.so_no,ii["mo_no"], jh_dd) result2 = executeQuery(sql2) data_list.append(result2[0]) # 得到分页数据 # print(data_list) print(2222222222) page = self.paginate_queryset(data_list) # print(page) if page is not None: # 序列化数据 serializer = self.get_serializer(instance=page, many=True) return self.get_paginated_response(serializer.data) # serializer = self.get_serializer(queryset, many=True) # return Response(serializer.data) # 表头模板需求 class BtMbxqtm(View): def get(self, request): return render(request, 'btmbxqtm.html') # 研磨品需求计划模板 class YmpxqJhtm(View): def get(self, request): return render(request, 'ympxqjhtm.html') # 研磨品需求计划数据接口 class YmpxqJh(ModelViewSet): pagination_class = MyPageNumberPagination # 设置过滤 filter_backends = (DjangoFilterBackend, filters.OrderingFilter) # filterset_fields = ('need_dd', 'cus_no', 'so_no', 'mrp_no') filter_class = YmpCourseFilterSet serializer_class = YmpdpcjhZserializer queryset = view_tf_pos.objects.all() def list(self, request, *args, **kwargs): # print(self.get_queryset()) queryset = self.filter_queryset(self.get_queryset()) # print(queryset) # 定义数据存储空间 data_list = [] # print(queryset) for i in queryset: # 校验是否回复时间 # print(i.mrp_no) if i.pmc_dd: # 转换时间格式 # print(str(i.pmc_dd)) try: pmc_dd_new = datetime.datetime.strptime(str(i.pmc_dd), '%Y-%m-%d') except Exception: return Response("时间类型错误", status=status.HTTP_400_BAD_REQUEST) # 递归查询得到所有bom已经累计bom的前置天数 sql = """select ggsxrq,gzdep,so_no,s_id_no,bom_no,prd_no,id_no,mo_no,sort,oldd,round(dd,0) as dd,qty from cst_tf_mp3_ymp where so_no='{0}' and s_id_no='{1}' order by sort asc""".format( i.so_no, i.mrp_no + '->') result1 = executeQuery(sql) print(sql) # print(result1) # print(result1)------------------------------------------------------------------------------------------------------时间待处理 pmc_dd=i.pmc_dd # 灌装上线日期 ggsxrq = datetime.datetime.strptime('5000-05-25 00:00:00','%Y-%m-%d %H:%M:%S') # --灌装生产线 gzdep = '' gzdep_today = 0 # print(len(result1)) if len(result1)>0: for result1_rows in range(len(result1)): print(result1[result1_rows]) if result1[result1_rows]["ggsxrq"] is None: return http.HttpResponseForbidden("订单分析了，但存在分析单没转制令单") if result1[result1_rows]["ggsxrq"]') result1 = executeQuery(sql) print(sql) print(result1) for ii in result1: # 计算计划完工日期 # print(round((ii["dd"]) / 9 + 2)) # print(ii["dd"]) # print(datetime.timedelta(round((ii["dd"]) / 9 + 2))) jh_dd = pmc_dd_new - datetime.timedelta(days=ii["dd"]) # 查询制令单与bom相符的数据 sql2 = """select id,need_dd,cus_no,so_no,mo_no,knd,scx,mrp_no,name,qty,gzwggs, pmc_dd,ymppt_dd,rqdh_dd,bcdh_dd,prdt1_qty,iebz,xqry,id_no,'{2}' as jhscrq from jtjh where so_no='{0}' and mo_no='{1}'""".format(i.so_no,ii["mo_no"],jh_dd) print(sql2) result2 = executeQuery(sql2) data_list.append(result2[0]) # 得到分页数据 # print(data_list) page = self.paginate_queryset(data_list) # print(page) if page is not None: # 序列化数据 serializer = self.get_serializer(instance=page, many=True) return self.get_paginated_response(serializer.data) serializer = self.get_serializer(queryset, many=True) return Response(serializer.data) # 测试 class text(View): def get(self, request): return render(request, 'text.html') # class text(APIView): # def get(self, request): # # sql = """select os_no,prd_no from tf_pos where os_no='SO2005000001' and prd_no='D4403C001RTP520-881'""" # # result1 = executeQuery(sql) # # for i in result1: # # sql2 = """select mrp_no,mo_no,so_no from mf_mo where os_no='{0}' and mrp_no='{1}'""".format(i["os_no"], i["prd_no"]) # # result2 = executeQuery(sql2) # # if len(result2)>0: # # while True: # # sql3 = """select prd_no,mo_no from tf_mo where mo_no='{0}'""".format(result2[0]["mo_no"]) # # result3 = executeQuery(sql3) # # for ii in result3 # return Response('OK')