wnserver/obj/apps/erp/views.py

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"]<ggsxrq:
                            ggsxrq = result1[result1_rows]["ggsxrq"]
                            gzdep = result1[result1_rows]["gzdep"]
                            gzdep_today = result1[result1_rows]["dd"]
                ggsxrq = pmc_dd_new - datetime.timedelta(days=round(gzdep_today,0))
                # print(ggsxrq)
                print(gzdep_today)
                # 查询制令单与bom相符的数据
                # print(i)
                sql2 = """select id,mo_dd,cus_no,so_no,dl,gzwwgs,gzdep,pmc_dd,ggsxrq,mrp_no,qty,jkwshl,llwsl,mrp_name,wfl,zzl,os_wfl,jcq,dep,'{2}' as pmc_dd,bom_no,est_itm,
              '{3}' as ggsxrq,'{4}' as gzdep,
                '' as mo_no1,'' as mo_no2,'' as mo_no3,'' as mo_no4,'' as mo_no5,'' as mo_no6,'' as mo_no7,'' as qty1,'' as qty2,'' as qty3,'' as qty4,'' as qty5,
                '' as qty6,'' as qty7 from ympxqjh 
                where so_no='{0}'  and id='{1}'""".format(i.so_no, i.id, pmc_dd,ggsxrq,gzdep)
                print(sql2)
                result2 = executeQuery(sql2)
                # print(result2)
                itm = 0
                for result2_itm in result2:
                    sql3 = """select a.mo_no,a.qty,b.dep_up from mf_mo a,DEPT_CHK b where a.dep=b.dep_dw and b.dep_up in(select dep from DSCSETTING where selected='T') and
                    a.mrp_no='{0}' and a.so_no='{1}' and a.est_itm='{2}' and b.dep_up='{3}'"""\
                    .format(result2_itm["mrp_no"],result2_itm["so_no"],result2_itm["est_itm"],result2_itm["dep"])
                    result3 = executeQuery(sql3)
                    # print(result3)
                    result3_itm=0
                    for result3_i in result3:
                        # print('mo_no'+str(itm))
                        # print('mo_no'+str(itm))
                        result3_itm = result3_itm + 1
                        result2[itm]['mo_no'+str(result3_itm)] = result3_i['mo_no']
                        result2[itm]['qty'+str(result3_itm)] = result3_i['qty']
                    data_list.append(result2[itm])
                    itm = itm + 1
                    # print(ii)
        # 得到分页数据
        # 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 Ydzpcjhtm(View):
    def get(self, request):
        return render(request, 'ydzpcjhtm.html')

# 月度主排产计划接口数据
class Ydzpcjh(ModelViewSet):
    # 1,设置局部认证
    # authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication,)
    # permission_classes = (IsAuthenticated,)  # 登录用户才能访问
    # permission_classes = (AllowAny,) #任何用户都能访问
    # 分页
    pagination_class = MyPageNumberPagination
    # 设置过滤
    filter_backends = (DjangoFilterBackend, filters.OrderingFilter)
    # filterset_fields = ('need_dd', 'cus_no', 'so_no', 'mrp_no')

    filter_class = YdCourseFilterSet
    serializer_class = YdzpcjhZserializer
    queryset = View_ydzpcjh.objects.all()

    def list(self, request, *args, **kwargs):
        # print(self.get_queryset())
        queryset = self.filter_queryset(self.get_queryset())
        print(queryset)
        print(222222222222)
        # 定义数据存储空间
        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 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)
                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')