from django.shortcuts import render

# Create your views here.
import json

import re
from django import http
from django.shortcuts import render

# Create your views here.
from django.views import View
from django.conf import settings
from django.db import connection, transaction
import datetime

from utils.cust_data import customer_data
from utils.et_CACC_NO import et_CACC_NO
from utils.examine_ood import examine_ood
from utils.if_account import if_account

from utils.usr_data import usr_data
from utils.monthly_odd import monthly_odd
from utils.executeQuery import executeQuery

# 进货单

class PcOrder(View):
    @transaction.atomic
    def get(self, request):
        context = {
            'a': '进货单'
        }
        #HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码)
        return http.JsonResponse(context)

    @transaction.atomic
    def post(self, request):
        # 1获取参数
        req_data = json.loads(request.body.decode())
        PS_DD = req_data.get("PS_DD")  # 单据日期
        CUS_NO = req_data.get("CUS_NO")  # 客户编码
        CUS_NO_NAME = req_data.get("CUS_NO_NAME",'')  # 客户名称
        PS_NO = req_data.get("PS_NO")  # 单据号码
        TAX_ID = req_data.get("TAX_ID")  # 扣税类别
        ZHANG_ID = req_data.get("ZHANG_ID")  # 立账方式
        VOH_ID = req_data.get("VOH_ID",'')  # 凭证模板
        USR = req_data.get("USR")  # 制单人编码
        USR_NAME = req_data.get("USR_NAME",'')  # 制单人名称
        # TAX_RTO = req_data.get("TAX_RTO")  # 税率   直接取货品资料里面的
        AMT = req_data.get("AMT",0)  # 金额
        DEP = req_data.get("DEP",'')  # 部门代号
        SAL_NO = req_data.get("SAL_NO",'')  # 业务员代号
        KPF = req_data.get("KPF", '')  # 开票否

        #
        # print(PS_DD)
        # print(CUS_NO)
        # print(CUS_NO_NAME)
        # print(PS_NO)
        # print(TAX_ID)
        # print(ZHANG_ID)
        # print(VOH_ID)
        # print(USR)
        # print(USR_NAME)
        # # print(TAX_RTO)
        # print(AMT)
        # print(DEP)
        # print(SAL_NO)
        # 2校验参数
        # 校验日期格式
        # 判断是否有输入单号
        if PS_DD is None:
            return http.HttpResponseForbidden('请输入进货日期')
        # 判断是否有输入单号
        if PS_NO is None:
            return http.HttpResponseForbidden('请输入进货单号')
        if PS_NO[:3] != 'PCA' or len(PS_NO) != 11:
        #判断单号是否合法
            return http.HttpResponseForbidden('进货单号输入不合法，单号必须是PCA开头且长度必须是11位')
        #判断金额是否输入正确
        if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMT)) is None:
            return http.HttpResponseForbidden('金额输入不正确')
        # 判断是否有输入供应商
        if CUS_NO is None:
            return http.HttpResponseForbidden('请输入供应商')
        if TAX_ID is None:
            return http.HttpResponseForbidden('请输入扣税类别')
        # 判断扣税类别是否输入正确
        if int(TAX_ID) != 1 and int(TAX_ID) != 2 and int(TAX_ID) != 3:
            return http.HttpResponseForbidden('扣税类别只能是1（不计税）,2（应该税内行），3（应税外加）')
        if ZHANG_ID is None:
            return http.HttpResponseForbidden('请输入立账方式')
        # 判断立账方式是否输入正确
        if int(ZHANG_ID) != 1 and int(ZHANG_ID) != 2 and int(ZHANG_ID) != 3:
            return http.HttpResponseForbidden('立账方式只能是1（单张立账）,2（不立账），3（收到发票才立账）')
        # #判断凭证模板是否输入
        # if VOH_ID is None:
        #     return http.HttpResponseForbidden('请输入凭证模板')
        # 判断制单人是否输入
        if USR is None:
            return http.HttpResponseForbidden('请输入制单人')
        # 判断税率是否输入
        # if TAX_RTO is None:
        #     return http.HttpResponseForbidden('请输入税率')
        # 判断进货单是否存在
        row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'PC')
        if row_PS_NO > 0:
            return http.HttpResponseForbidden('进货单号已存在')
        # 判断供应商是否哦存在，不存在的话创建
        try:
            row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '2', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),0)
        except Exception:
            return http.HttpResponseForbidden('创建厂商失败')
        # 判断用户是否存在
        # row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'))
        try:
            # 判断用户是否存在，不存在则创建
            row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'))
        except Exception:
            return http.HttpResponseForbidden('创建用户失败')
        #判断单号是否存在
        #判断供应商是否存在
        #判断凭证模板是否存在--------------------------------------------待完成
        #判断字段制单人是否存在

        # 3数入库
        with connection.cursor() as cursor:

            # 单张立账或者不立账需要判断凭证模板是否存在，直接判断立账方式不等于3即可
            row_usr=0
            try:
                if VOH_ID !=0:
                    row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='PC'", [VOH_ID]).fetchall()[0][0]
            except Exception:
                return http.HttpResponseForbidden('查询凭证模板异常')
            if row_usr <= 0:
                return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板')
            # 获取税率
            try:
                PRDT = cursor.execute("SELECT ISNULL(SPC_TAX,0),[NAME] FROM PRDT WHERE PRD_NO='AQ001'").fetchall()[0]
                SPC_TAX=PRDT[0]
                PRD_NAME=PRDT[1]
            except Exception:
                return http.HttpResponseForbidden('获取安装费税率异常')
            # 计算本位币  AMTN_NET：本位币， TAX：税金 ，SPC_TAX：税率
            TAX=0
            AMTN_NET=0
            if int(TAX_ID)==1:
                TAX=0
                AMTN_NET = AMT
            if int(TAX_ID)==2:
                print(SPC_TAX)
                TAX = float(AMT)/(1+float(SPC_TAX)/100)*float(SPC_TAX)/100
                AMTN_NET = float(AMT) - TAX
            if int(TAX_ID)==3:
                TAX = float(AMT) / 100 * float(SPC_TAX)
                AMTN_NET = float(AMT)
            sid = transaction.savepoint()  # 开启事物
            try:
                # 插入进货单表头-----------------------改CUR_ID  RMB
                if int(ZHANG_ID) == 1:
                    cursor.execute("""INSERT INTO MF_PSS(PS_ID,PS_NO,PS_DD,CUS_NO,ZHANG_ID,USR,CHK_MAN,CLS_DATE,SYS_DATE,EXC_RTO,LZ_CLS_ID,CLSLZ,TAX_ID,ARP_NO,VOH_ID,DEP,SAL_NO)
                                VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                   ['PC',
                                    'PC' + PS_NO[2::],
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    CUS_NO,
                                    ZHANG_ID,
                                    USR,
                                    USR,
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    1,
                                    'F',
                                    '',
                                    TAX_ID,
                                    'AP' + PS_NO[2::],
                                    VOH_ID,
                                    DEP,
                                    SAL_NO])
                    # 插入自定义栏位表
                    if KPF=='T':
                        cursor.execute("""INSERT INTO MF_PSS_Z(PS_ID,PS_NO,KPF)VALUES(%s,%s,%s)""",['PC','PC' + PS_NO[2::],'T'])
                    # 插入立账单MF_MRP------------------改CUR_ID   RMB
                    cursor.execute("""INSERT INTO MF_ARP(ARP_ID,OPN_ID,ARP_NO,BIL_NO,CUS_NO,PAY_DD,AMT,AMTN,AMTN_NET,EXC_RTO,CLOSE_ID,BIL_ID,SYS_DATE,BIL_DD,DEP,ZHANG_ID)VALUES
                                            (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                   [2,
                                    2,
                                    'AP' + PS_NO[2::],
                                    'PC' + PS_NO[2::],
                                    CUS_NO,
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    0,
                                    AMT,
                                    AMTN_NET,
                                    1,
                                    'F',
                                    'PC',
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    DEP,
                                    ZHANG_ID])
                if int(ZHANG_ID) !=1:
                    print(3)
                    cursor.execute("""INSERT INTO MF_PSS(PS_ID,PS_NO,PS_DD,CUS_NO,ZHANG_ID,USR,CHK_MAN,CLS_DATE,SYS_DATE,EXC_RTO,LZ_CLS_ID,CLSLZ,TAX_ID,ARP_NO,VOH_ID,DEP,SAL_NO)
                                  VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                   ['PC',
                                    'PC' + PS_NO[2::],
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    CUS_NO,
                                    ZHANG_ID,
                                    USR,
                                    USR,
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    1,
                                    'F',
                                    '',
                                    TAX_ID,
                                    '',
                                    VOH_ID,
                                    DEP,
                                    SAL_NO])
                # 插入进货单表身
                print(4)
                cursor.execute("""INSERT INTO TF_PSS(PS_ID,PS_NO,PS_DD,WH,PRD_NO,QTY,UP,AMT,AMTN_NET,TAX_RTO,ITM,UNIT,CSTN_SAL,PRE_ITM,TAX,EST_ITM,PRD_NAME)
                        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                               ['PC',
                                'PC' + PS_NO[2::] ,
                                datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                '0000',
                                'AQ001',
                                1,
                                AMT,
                                AMT,
                                AMTN_NET,
                                SPC_TAX,
                                1,
                                1,
                                AMTN_NET,
                                1,
                                TAX,
                                1,
                                PRD_NAME
                                ])
            except Exception:
                transaction.savepoint_rollback(sid)  # 语句执行错误，进行语句回滚
                return http.HttpResponseForbidden("新增进货单sql语句执行错误")
            transaction.savepoint_commit(sid)  # 提交事物
        # {
        #     "PS_DD": "2019-12-09",
        #     "CUS_NO": "AQ1111",
        #     "CUS_NO_NAME": "天心客户",
        #     "PS_NO": "PCA19C09001",
        #     "TAX_ID": "1",
        #     "ZHANG_ID": "1",
        #     "VOH_ID": "01",
        #     "USR": "a00001",
        #     "USR_NAME": "楠楠",
        #     "AMT": "200",
        #     "DEP": "0000",
        #     "SAL_NO": "A00002",
        #     "user": "123",
        #     "password": "123"
        # }

        context = {
            "errmsg": '新增进货单成功',
            "code":200
        }
        # HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码)
        return http.JsonResponse(context)
        # return http.JsonResponse({"code": RET.OK, "errmsg": "ok"})

    @transaction.atomic
    def put(self, request):
        # 1获取参数
        req_data = json.loads(request.body.decode())
        PS_DD = req_data.get("PS_DD")  # 单据日期
        CUS_NO = req_data.get("CUS_NO")  # 客户编码
        CUS_NO_NAME = req_data.get("CUS_NO_NAME",'')  # 客户名称
        PS_NO = req_data.get("PS_NO")  # 单据号码
        TAX_ID = req_data.get("TAX_ID")  # 扣税类别
        ZHANG_ID = req_data.get("ZHANG_ID")  # 立账方式
        VOH_ID = req_data.get("VOH_ID")  # 凭证模板
        # TAX_RTO = req_data.get("TAX_RTO")  # 税率   直接取货品资料里面的
        AMT = req_data.get("AMT",0)  # 金额
        DEP = req_data.get("DEP",'')  # 部门代号
        SAL_NO = req_data.get("SAL_NO",'')  # 业务员代号
        KPF = req_data.get("KPF", '')  # 开票否
        # {
        #     "PS_DD": "2019-12-09",
        #     "CUS_NO": "AQ1111",
        #     "CUS_NO_NAME": "天心客户",
        #     "PS_NO": "PCA19C09001",
        #     "TAX_ID": "1",
        #     "ZHANG_ID": "1",
        #     "VOH_ID": "01",
        #     "USR": "a00001",
        #     "USR_NAME": "楠楠",
        #     "AMT": "200",
        #     "DEP": "0000",
        #     "SAL_NO": "A00002",
        #     "user": "123",
        #     "password": "123"
        # }
        # 2校验参数
        # 判断是否有输入单号
        if PS_DD is None:
            return http.HttpResponseForbidden('请输入进货日期')
        # 校验日期格式
        try:
            datetime.datetime.strptime(PS_DD, '%Y-%m-%d')
        except Exception:
            return http.HttpResponseForbidden('日期格式输入不正确')
        # 判断是否有输入单号
        if PS_NO is None:
            return http.HttpResponseForbidden('请输入进货单号')
        if PS_NO[:3] != 'PCA' or len(PS_NO) != 11:
            # 判断单号是否合法
            return http.HttpResponseForbidden('进货单号输入不合法，单号必须是PCA开头且长度必须为11位')
        # 判断金额是否输入正确
        if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMT)) is None:
            return http.HttpResponseForbidden('金额输入不正确')
        # 判断是否有输入供应商
        if CUS_NO is None:
            return http.HttpResponseForbidden('请输入供应商')
        if TAX_ID is None:
            return http.HttpResponseForbidden('请输入扣税类别')
        # 判断扣税类别是否输入正确
        if int(TAX_ID) != 1 and int(TAX_ID) != 2 and int(TAX_ID) != 3:
            return http.HttpResponseForbidden('扣税类别只能是1（不计税）,2（应该税内行），3（应税外加）')
        if ZHANG_ID is None:
            return http.HttpResponseForbidden('请输入立账方式')
        # 判断立账方式是否输入正确
        if int(ZHANG_ID) != 1 and int(ZHANG_ID) != 2 and int(ZHANG_ID) != 3:
            return http.HttpResponseForbidden('立账方式只能是1（单张立账）,2（不立账），3（收到发票才立账）')
        # # 判断凭证模板是否输入
        # if VOH_ID is None:
        #     return http.HttpResponseForbidden('请输入凭证模板')
            # 判断进货单是否存在
        row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'PC')
        if row_PS_NO <= 0:
            return http.HttpResponseForbidden('进货单号不存在')
        # 判断供应商是否哦存在，不存在的话创建
        try:
            row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '2', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0)
        except Exception:
            return http.HttpResponseForbidden('创建厂商失败')

        #判断进货单是否产生后续单据===========待完成
        # 3数入库
        with connection.cursor() as cursor:
            # 判断单号是否产生后续单据，立账里面的一冲金额
            try:
                # 获取原来单据的立账方式
                ZHANG_ID_OLD = cursor.execute("SELECT ZHANG_ID FROM MF_PSS WHERE PS_ID='PC' AND PS_NO=%s",['PC' + PS_NO[2::]]).fetchall()[0][0]
                if int(ZHANG_ID_OLD)==1:
                    print('AP' + PS_NO[2::])
                    row_no = cursor.execute("SELECT ISNULL(AMTN_RCV,0) FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='PC'", ['AP' + PS_NO[2::]]).fetchall()[0][0]
                    if float(row_no)>0 and row_no is not None:
                        return http.HttpResponseForbidden('进货单已产生后续单据不允许修改')
                if int(ZHANG_ID_OLD) != 1:
                    row_no = cursor.execute("SELECT ISNULL(ACC_FP_NO,'') FROM MF_PSS WHERE PS_NO=%s AND PS_ID='PC'",['PC' + PS_NO[2::]]).fetchall()[0][0]
                    if row_no!='':
                        return http.HttpResponseForbidden('进货单已产生后续单据不允许修改')
            except Exception:
                return http.HttpResponseForbidden('判断进货单是否产生后续单据异常')
            # 判断传过来的凭证模板在数据库里面是否存在
            row_usr=0
            try:
                if VOH_ID !=0:
                    row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='PC'", [VOH_ID]).fetchall()[0][0]
            except Exception:
                return http.HttpResponseForbidden('查询凭证模板异常')
            if row_usr <= 0:
                return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板')
            # 获取税率
            try:
                PRDT = cursor.execute("SELECT ISNULL(SPC_TAX,0),[NAME] FROM PRDT WHERE PRD_NO='AQ001'").fetchall()[0]
                SPC_TAX = PRDT[0]
                PRD_NAME = PRDT[1]
            except Exception:
                return http.HttpResponseForbidden('获取安装费税率异常')
            # 计算本位币  AMTN_NET：本位币， TAX：税金 ，SPC_TAX：税率
            TAX = 0
            AMTN_NET = 0
            if int(TAX_ID) == 1:
                TAX = 0
                AMTN_NET = AMT
            if int(TAX_ID) == 2:
                print(SPC_TAX)
                TAX = float(AMT) / (1 + float(SPC_TAX) / 100) * float(SPC_TAX) / 100
                AMTN_NET = float(AMT) - TAX
            if int(TAX_ID) == 3:
                TAX = float(AMT) / 100 * float(SPC_TAX)
                AMTN_NET = float(AMT)
            sid = transaction.savepoint()  # 开启事物
            try:
                # 插入进货单表头-----------------------改CUR_ID  RMB
                # 修改自定义栏位表
                if KPF == 'T':
                    cursor.execute("""UPDATE MF_PSS_Z SET KPF=%s WHERE  PS_ID='PC' AND PS_NO=%s""",['T','PC' + PS_NO[2::]])
                if KPF == 'F':
                    cursor.execute("""UPDATE MF_PSS_Z SET KPF=%s WHERE  PS_ID='PC' AND PS_NO=%s""",['F','PC' + PS_NO[2::]])
                if int(ZHANG_ID) == 1:
                    print(1)
                    cursor.execute("""UPDATE MF_PSS SET PS_DD=%s,CUS_NO=%s,ZHANG_ID=%s,TAX_ID=%s,VOH_ID=%s,DEP=%s,SAL_NO=%s,MODIFY_DD=%s,ARP_NO=%s WHERE PS_ID='PC' AND PS_NO=%s """,
                               [
                                   datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                   CUS_NO,
                                   ZHANG_ID,
                                   TAX_ID,
                                   VOH_ID,
                                   DEP,
                                   SAL_NO,
                                   datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                   'AP' + PS_NO[2::],
                                   'PC' + PS_NO[2::]
                               ])
                    print(2)
                    # 如果旧的立账方式是1的话那么会有立账单，那么可以直接修改
                    print(DEP)
                    print(ZHANG_ID)
                    if int(ZHANG_ID_OLD)==1:
                        cursor.execute("""UPDATE MF_ARP SET CUS_NO=%s,PAY_DD=%s,AMTN=%s,AMTN_NET=%s,BIL_DD=%s,DEP=%s,ZHANG_ID=%s WHERE BIL_ID='PC' AND ARP_NO=%s""",
                                   [CUS_NO,
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    AMT,
                                    AMTN_NET,
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    DEP,
                                    ZHANG_ID,
                                    'AP' + PS_NO[2::]
                                    ])

                    #如果旧是立账方式四不为1，然后改为1那么系统会在生成一张立账单
                    if int(ZHANG_ID_OLD) != 1:
                        cursor.execute("""INSERT INTO MF_ARP(ARP_ID,OPN_ID,ARP_NO,BIL_NO,CUS_NO,PAY_DD,AMT,AMTN,AMTN_NET,EXC_RTO,CLOSE_ID,BIL_ID,SYS_DATE,BIL_DD,DEP,ZHANG_ID)VALUES
                                       (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                       [2,
                                        2,
                                        'AP' + PS_NO[2::],
                                        'PC' + PS_NO[2::],
                                        CUS_NO,
                                        datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                        0,
                                        AMT,
                                        AMTN_NET,
                                        1,
                                        'F',
                                        'PC',
                                        datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                        datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                        DEP,
                                        ZHANG_ID])
                if int(ZHANG_ID) != 1:
                    print(3)
                    cursor.execute(
                        """UPDATE MF_PSS SET PS_DD=%s,CUS_NO=%s,ZHANG_ID=%s,TAX_ID=%s,VOH_ID=%s,DEP=%s,SAL_NO=%s,MODIFY_DD=%s,ARP_NO=%s WHERE PS_ID='PC' AND PS_NO=%s """,
                        [
                            datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                            CUS_NO,
                            ZHANG_ID,
                            TAX_ID,
                            VOH_ID,
                            DEP,
                            SAL_NO,
                            datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                            '',
                            'PC' + PS_NO[2::]
                        ])
                    # 如果立账方式不为1的时候需要删除，立账单
                    cursor.execute(
                        """DELETE FROM MF_ARP WHERE BIL_ID='PC' AND ARP_NO=%s""",
                        [
                            'AP' + PS_NO[2::]
                        ])
                # 插入进货单表身
                print(4)
                cursor.execute("""UPDATE TF_PSS SET PS_DD=%s,UP=%s,AMT=%s,AMTN_NET=%s,CSTN_SAL=%s,TAX=%s,TAX_RTO=%s WHERE PS_ID='PC' AND PS_NO=%s""",
                        [datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                AMT,
                                AMT,
                                AMTN_NET,
                                AMTN_NET,
                                TAX,
                                SPC_TAX,
                                'PC' + PS_NO[2::]
                                ])

            except Exception:
                transaction.savepoint_rollback(sid)  # 语句执行错误，进行语句回滚
                return http.HttpResponseForbidden("修改进货单sql语句执行错误")
            transaction.savepoint_commit(sid)  # 提交事物
        context = {
            "errmsg": '修改进货单成功',
            "code": 200
        }
        return http.JsonResponse(context)

    @transaction.atomic
    def delete(self, request):
        # 1获取参数
        req_data = json.loads(request.body.decode())
        PS_NO = req_data.get("PS_NO")  # 单据日期
        # {
        #     "PS_NO": "PCA19C09001",
        #     "user": "123",
        #     "password": "123"
        # }
        # 判断是否有输入单号
        if PS_NO is None:
            return http.HttpResponseForbidden('请输入进货单号')
        if PS_NO[:3] != 'PCA' or len(PS_NO) != 11:
            # 判断单号是否合法
            return http.HttpResponseForbidden('进货单号输入不合法，单号必须是PCA开头且长度必须为11位')
        # 判断进货单是否存在
        row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'PC')
        if row_PS_NO <= 0:
            return http.HttpResponseForbidden('进货单号不存在')
        # 3数入库
        with connection.cursor() as cursor:
            # 判断单号是否产生后续单据，立账里面的一冲金额
            try:
                # 获取原来单据的立账方式
                ZHANG_ID_OLD = cursor.execute("SELECT ZHANG_ID FROM MF_PSS WHERE PS_ID='PC' AND PS_NO=%s",
                                              ['PC' + PS_NO[2::]]).fetchall()[0][0]
                if int(ZHANG_ID_OLD) == 1:
                    print('AP' + PS_NO[2::])
                    row_no = cursor.execute("SELECT ISNULL(AMTN_RCV,0) FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='PC'",
                                            ['AP' + PS_NO[2::]]).fetchall()[0][0]
                    if float(row_no) > 0 and row_no is not None:
                        return http.HttpResponseForbidden('进货单已产生后续单据不允许删除')
                if int(ZHANG_ID_OLD) != 1:
                    row_no = cursor.execute("SELECT ISNULL(ACC_FP_NO,'') FROM MF_PSS WHERE PS_NO=%s AND PS_ID='PC'",
                                            ['PC' + PS_NO[2::]]).fetchall()[0][0]
                    if row_no != '':
                        return http.HttpResponseForbidden('进货单已产生后续单据不允许删除')
            except Exception:
                return http.HttpResponseForbidden('判断进货单是否产生后续单据异常')

            sid = transaction.savepoint()  # 开启事物
            try:
                cursor.execute("""DELETE FROM MF_PSS WHERE  PS_NO=%s AND PS_ID='PC'""", ['PC' + PS_NO[2::]])
                cursor.execute("""DELETE FROM TF_PSS WHERE  PS_NO=%s AND PS_ID='PC'""", ['PC' + PS_NO[2::]])
                cursor.execute("""DELETE FROM MF_ARP WHERE  ARP_NO=%s AND BIL_ID='PC'""", ['AP' + PS_NO[2::]])
            except Exception:
                transaction.savepoint_rollback(sid)  # 语句执行错误，进行语句回滚
                return http.HttpResponseForbidden("删除进货单失败")
            transaction.savepoint_commit(sid)  # 提交事物

        context = {
            "errmsg": '删除进货单成功',
            "code": 200
        }
        return http.JsonResponse(context)
# 销货单
class SaOrder(View):

    @transaction.atomic
    def get(self, request):
        context = {
            'a': '销货单'
        }
        # HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码)
        return http.JsonResponse(context)

    @transaction.atomic
    def post(self, request):
        # 1获取参数
        req_data = json.loads(request.body.decode())
        PS_DD = req_data.get("PS_DD")  # 单据日期
        CUS_NO = req_data.get("CUS_NO")  # 客户编码
        CUS_NO_NAME = req_data.get("CUS_NO_NAME", '')  # 客户名称
        PS_NO = req_data.get("PS_NO")  # 单据号码
        TAX_ID = req_data.get("TAX_ID")  # 扣税类别
        ZHANG_ID = req_data.get("ZHANG_ID")  # 立账方式
        VOH_ID = req_data.get("VOH_ID", '')  # 凭证模板
        USR = req_data.get("USR")  # 制单人编码
        USR_NAME = req_data.get("USR_NAME", '')  # 制单人名称
        # TAX_RTO = req_data.get("TAX_RTO")  # 税率   直接取货品资料里面的
        AMT = req_data.get("AMT", 0)  # 金额
        DEP = req_data.get("DEP", '')  # 部门代号
        SAL_NO = req_data.get("SAL_NO", '')  # 业务员代号
        #
        # print(PS_DD)
        # print(CUS_NO)
        # print(CUS_NO_NAME)
        # print(PS_NO)
        # print(TAX_ID)
        # print(ZHANG_ID)
        # print(VOH_ID)
        # print(USR)
        # print(USR_NAME)
        # # print(TAX_RTO)
        # print(AMT)
        # print(DEP)
        # print(SAL_NO)
        # 2校验参数
        # 校验日期格式
        # {
        #     "PS_DD": "2019-12-09",
        #     "CUS_NO": "AQ2222",
        #     "CUS_NO_NAME": "收款",
        #     "PS_NO": "SAB19C09001",
        #     "TAX_ID": "1",
        #     "ZHANG_ID": "1",
        #     "VOH_ID": "01",
        #     "USR": "a00002",
        #     "USR_NAME": "楠楠",
        #     "AMT": "200",
        #     "DEP": "0000",
        #     "SAL_NO": "A00002",
        #     "user": "123",
        #     "password": "123"
        # }
        if PS_DD is None:
            return http.HttpResponseForbidden('请输入日期')
        try:
            datetime.datetime.strptime(PS_DD, '%Y-%m-%d')
        except Exception:
            return http.HttpResponseForbidden('日期格式输入不正确')
        # 判断是否有输入单号
        if PS_NO is None:
            return http.HttpResponseForbidden('请输入销货单号')
        if PS_NO[:3] != 'SAB' or len(PS_NO) != 11:
            # 判断单号是否合法
            return http.HttpResponseForbidden('销货单号输入不合法，单号必须是SAB开头且长度必须为11位')
        # 判断金额是否输入正确
        if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMT)) is None:
            return http.HttpResponseForbidden('金额输入不正确')
        # 判断是否有输入供应商
        if CUS_NO is None:
            return http.HttpResponseForbidden('请输入客户')
        if TAX_ID is None:
            return http.HttpResponseForbidden('请输入扣税类别')
        # 判断扣税类别是否输入正确
        if int(TAX_ID) != 1 and int(TAX_ID) != 2 and int(TAX_ID) != 3:
            return http.HttpResponseForbidden('扣税类别只能是1（不计税）,2（应该税内行），3（应税外加）')
        if ZHANG_ID is None:
            return http.HttpResponseForbidden('请输入立账方式')
        # 判断立账方式是否输入正确
        if int(ZHANG_ID) != 1 and int(ZHANG_ID) != 2 and int(ZHANG_ID) != 3:
            return http.HttpResponseForbidden('立账方式只能是1（单张立账）,2（不立账），3（收到发票才立账）')
        # #判断凭证模板是否输入
        # if VOH_ID is None:
        #     return http.HttpResponseForbidden('请输入凭证模板')
        # 判断制单人是否输入
        if USR is None:
            return http.HttpResponseForbidden('请输入制单人')
        # 判断税率是否输入
        # if TAX_RTO is None:
        #     return http.HttpResponseForbidden('请输入税率')
        # 判断进货单是否存在
        row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'SA')
        if row_PS_NO > 0:
            return http.HttpResponseForbidden('销货单号已存在')
        # 判断供应商是否哦存在，不存在的话创建
        try:
            row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '1', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0)
        except Exception:
            return http.HttpResponseForbidden('创建客户失败')
        # 判断用户是否存在
        # row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'))
        try:
            # 判断用户是否存在，不存在则创建
            row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'))
        except Exception:
            return http.HttpResponseForbidden('创建用户失败')
        # 判断单号是否存在
        # 判断供应商是否存在
        # 判断凭证模板是否存在--------------------------------------------待完成
        # 判断字段制单人是否存在

        # 3数入库
        with connection.cursor() as cursor:

            # 单张立账或者不立账需要判断凭证模板是否存在，直接判断立账方式不等于3即可
            row_usr = 0
            try:
                if VOH_ID != 0:
                    row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='SA'", [VOH_ID]).fetchall()[0][0]
            except Exception:
                return http.HttpResponseForbidden('查询凭证模板异常')
            if row_usr <= 0:
                return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板')
            # 获取税率
            try:
                PRDT = cursor.execute("SELECT ISNULL(SPC_TAX,0),[NAME] FROM PRDT WHERE PRD_NO='AQ001'").fetchall()[0]
                SPC_TAX = PRDT[0]
                PRD_NAME = PRDT[1]
            except Exception:
                return http.HttpResponseForbidden('获取安装费税率异常')
            # 计算本位币  AMTN_NET：本位币， TAX：税金 ，SPC_TAX：税率
            TAX = 0
            AMTN_NET = 0
            if int(TAX_ID) == 1:
                TAX = 0
                AMTN_NET = AMT
            if int(TAX_ID) == 2:
                print(SPC_TAX)
                TAX = float(AMT) / (1 + float(SPC_TAX) / 100) * float(SPC_TAX) / 100
                AMTN_NET = float(AMT) - TAX
            if int(TAX_ID) == 3:
                TAX = float(AMT) / 100 * float(SPC_TAX)
                AMTN_NET = float(AMT)
            sid = transaction.savepoint()  # 开启事物
            try:
                # 插入销货单表头-----------------------改CUR_ID  RMB
                if int(ZHANG_ID) == 1:
                    cursor.execute("""INSERT INTO MF_PSS(PS_ID,PS_NO,PS_DD,CUS_NO,ZHANG_ID,USR,CHK_MAN,CLS_DATE,SYS_DATE,EXC_RTO,LZ_CLS_ID,CLSLZ,TAX_ID,ARP_NO,VOH_ID,DEP,SAL_NO,PRT_SW,YD_ID)
                                    VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                   ['SA',
                                    'SA' + PS_NO[2::],
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    CUS_NO,
                                    ZHANG_ID,
                                    USR,
                                    USR,
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    1,
                                    'F',
                                    '',
                                    TAX_ID,
                                    'AP' + PS_NO[2::],
                                    VOH_ID,
                                    DEP,
                                    SAL_NO,
                                    'N',
                                    'T'
                                    ])
                    # 插入立账单MF_MRP------------------改CUR_ID   RMB
                    print(222222222)
                    cursor.execute("""INSERT INTO MF_ARP(ARP_ID,OPN_ID,ARP_NO,BIL_NO,CUS_NO,PAY_DD,AMT,AMTN,AMTN_NET,EXC_RTO,CLOSE_ID,BIL_ID,SYS_DATE,BIL_DD,DEP,ZHANG_ID)VALUES
                                                (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                   [1,
                                    2,
                                    'AP' + PS_NO[2::],
                                    'SA' + PS_NO[2::],
                                    CUS_NO,
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    0,
                                    AMT,
                                    AMTN_NET,
                                    1,
                                    'F',
                                    'SA',
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    DEP,
                                    ZHANG_ID])
                if int(ZHANG_ID) != 1:
                    print(3)
                    cursor.execute("""INSERT INTO MF_PSS(PS_ID,PS_NO,PS_DD,CUS_NO,ZHANG_ID,USR,CHK_MAN,CLS_DATE,SYS_DATE,EXC_RTO,LZ_CLS_ID,CLSLZ,TAX_ID,ARP_NO,VOH_ID,DEP,SAL_NO,PRT_SW,YD_ID)
                                      VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                   ['SA',
                                    'SA' + PS_NO[2::],
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    CUS_NO,
                                    ZHANG_ID,
                                    USR,
                                    USR,
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                    1,
                                    'F',
                                    '',
                                    TAX_ID,
                                    '',
                                    VOH_ID,
                                    DEP,
                                    SAL_NO,
                                    'N',
                                    'T'
                                    ])
                # 插入进货单表身
                print(4)
                cursor.execute("""INSERT INTO TF_PSS(PS_ID,PS_NO,PS_DD,WH,PRD_NO,QTY,UP,AMT,AMTN_NET,TAX_RTO,ITM,UNIT,CSTN_SAL,PRE_ITM,TAX,EST_ITM,PRD_NAME)
                            VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                               ['SA',
                                'SA' + PS_NO[2::],
                                datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                '0000',
                                'AQ002',
                                1,
                                AMT,
                                AMT,
                                AMTN_NET,
                                SPC_TAX,
                                1,
                                1,
                                AMTN_NET,
                                1,
                                TAX,
                                1,
                                PRD_NAME])
            except Exception:
                transaction.savepoint_rollback(sid)  # 语句执行错误，进行语句回滚
                return http.HttpResponseForbidden("新增销货单sql语句执行错误")
            transaction.savepoint_commit(sid)  # 提交事物

        context = {
            "errmsg": '新增销货单成功',
            "code": 200
        }
        # HttpResponse(content=响应体,content_type=响应体数据MIME类型,status=状态码)
        return http.JsonResponse(context)
        # return http.JsonResponse({"code": RET.OK, "errmsg": "ok"})

    @transaction.atomic
    def put(self, request):
        # 1获取参数
        req_data = json.loads(request.body.decode())
        PS_DD = req_data.get("PS_DD")  # 单据日期
        CUS_NO = req_data.get("CUS_NO")  # 客户编码
        CUS_NO_NAME = req_data.get("CUS_NO_NAME", '')  # 客户名称
        PS_NO = req_data.get("PS_NO")  # 单据号码
        TAX_ID = req_data.get("TAX_ID")  # 扣税类别
        ZHANG_ID = req_data.get("ZHANG_ID")  # 立账方式
        VOH_ID = req_data.get("VOH_ID")  # 凭证模板
        # TAX_RTO = req_data.get("TAX_RTO")  # 税率   直接取货品资料里面的
        AMT = req_data.get("AMT", 0)  # 金额
        DEP = req_data.get("DEP", '')  # 部门代号
        SAL_NO = req_data.get("SAL_NO", '')  # 业务员代号

        # {
        #     "PS_DD": "2019-12-09",
        #     "CUS_NO": "AQ2222",
        #     "CUS_NO_NAME": "收款",
        #     "PS_NO": "SAB19C09001",
        #     "TAX_ID": "3",
        #     "ZHANG_ID": "3",
        #     "VOH_ID": "01",
        #     "USR": "a00002",
        #     "USR_NAME": "楠楠",
        #     "AMT": "200",
        #     "DEP": "0000",
        #     "SAL_NO": "A00002",
        #     "user": "123",
        #     "password": "123"
        # }
        # 2校验参数
        # 校验日期格式
        if PS_DD is None:
            return http.HttpResponseForbidden('请输入日期')
        try:
            datetime.datetime.strptime(PS_DD, '%Y-%m-%d')
        except Exception:
            return http.HttpResponseForbidden('日期格式输入不正确')
        # 判断是否有输入单号
        if PS_NO is None:
            return http.HttpResponseForbidden('请输入销货单号')
        if PS_NO[:3] != 'SAB' or len(PS_NO) != 11:
            # 判断单号是否合法
            return http.HttpResponseForbidden('销货单号输入不合法，单号必须是SAB开头且长度必须为11位')
        # 判断金额是否输入正确
        if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMT)) is None:
            return http.HttpResponseForbidden('金额输入不正确')
        # 判断是否有输入供应商
        if CUS_NO is None:
            return http.HttpResponseForbidden('请输入客户')
        if TAX_ID is None:
            return http.HttpResponseForbidden('请输入扣税类别')
        # 判断扣税类别是否输入正确
        if int(TAX_ID) != 1 and int(TAX_ID) != 2 and int(TAX_ID) != 3:
            return http.HttpResponseForbidden('扣税类别只能是1（不计税）,2（应该税内行），3（应税外加）')
        if ZHANG_ID is None:
            return http.HttpResponseForbidden('请输入立账方式')
        # 判断立账方式是否输入正确
        if int(ZHANG_ID) != 1 and int(ZHANG_ID) != 2 and int(ZHANG_ID) != 3:
            return http.HttpResponseForbidden('立账方式只能是1（单张立账）,2（不立账），3（收到发票才立账）')
        # # 判断凭证模板是否输入
        # if VOH_ID is None:
        #     return http.HttpResponseForbidden('请输入凭证模板')
        # 判断进货单是否存在
        row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'SA')
        if row_PS_NO <= 0:
            return http.HttpResponseForbidden('销货单号不存在')
        # 判断供应商是否哦存在，不存在的话创建
        try:
            row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '2', datetime.datetime.strptime(PS_DD, '%Y-%m-%d'), 0)
        except Exception:
            return http.HttpResponseForbidden('创建客户失败')

        # 判断进货单是否产生后续单据===========待完成
        # 3数入库
        with connection.cursor() as cursor:

            # 判断单号是否产生后续单据，立账里面的一冲金额
            try:
                # 获取原来单据的立账方式
                ZHANG_ID_OLD = cursor.execute("SELECT ZHANG_ID FROM MF_PSS WHERE PS_ID='SA' AND PS_NO=%s",
                                              ['SA' + PS_NO[2::]]).fetchall()[0][0]
                print('AP' + PS_NO[2::])
                if int(ZHANG_ID_OLD) == 1:
                    print('AP' + PS_NO[2::])
                    row_no = cursor.execute("SELECT ISNULL(AMTN_RCV,0) FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='SA'",
                                            ['AP' + PS_NO[2::]]).fetchall()[0][0]
                    if float(row_no) > 0 and row_no is not None:
                        return http.HttpResponseForbidden('销货单已产生后续单据不允许修改')
                if int(ZHANG_ID_OLD) != 1:
                    row_no = cursor.execute("SELECT ISNULL(ACC_FP_NO,'') FROM MF_PSS WHERE PS_NO=%s AND PS_ID='SA'",
                                            ['SA' + PS_NO[2::]]).fetchall()[0][0]
                    if row_no != '':
                        return http.HttpResponseForbidden('销货单已产生后续单据不允许修改')
            except Exception:
                return http.HttpResponseForbidden('判断销货单是否产生后续单据异常')
            # 判断传过来的凭证模板在数据库里面是否存在
            row_usr = 0
            try:
                if VOH_ID != 0:
                    row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='PC'", [VOH_ID]).fetchall()[0][0]
            except Exception:
                return http.HttpResponseForbidden('查询凭证模板异常')
            if row_usr <= 0:
                return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板')
            # 获取税率
            try:
                PRDT = cursor.execute("SELECT ISNULL(SPC_TAX,0),[NAME] FROM PRDT WHERE PRD_NO='AQ001'").fetchall()[0]
                SPC_TAX = PRDT[0]
                PRD_NAME = PRDT[1]
            except Exception:
                return http.HttpResponseForbidden('获取安装费税率异常')
            # 计算本位币  AMTN_NET：本位币， TAX：税金 ，SPC_TAX：税率
            TAX = 0
            AMTN_NET = 0
            if int(TAX_ID) == 1:
                TAX = 0
                AMTN_NET = AMT
            if int(TAX_ID) == 2:
                print(SPC_TAX)
                TAX = float(AMT) / (1 + float(SPC_TAX) / 100) * float(SPC_TAX) / 100
                AMTN_NET = float(AMT) - TAX
            if int(TAX_ID) == 3:
                TAX = float(AMT) / 100 * float(SPC_TAX)
                AMTN_NET = float(AMT)
            sid = transaction.savepoint()  # 开启事物
            try:
                # 插入进货单表头-----------------------改CUR_ID  RMB
                if int(ZHANG_ID) == 1:
                    print(1)
                    cursor.execute(
                        """UPDATE MF_PSS SET PS_DD=%s,CUS_NO=%s,ZHANG_ID=%s,TAX_ID=%s,VOH_ID=%s,DEP=%s,SAL_NO=%s,MODIFY_DD=%s,ARP_NO=%s WHERE PS_ID='SA' AND PS_NO=%s """,
                        [
                            datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                            CUS_NO,
                            ZHANG_ID,
                            TAX_ID,
                            VOH_ID,
                            DEP,
                            SAL_NO,
                            datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                            'AP' + PS_NO[2::],
                            'SA' + PS_NO[2::]
                        ])
                    print(2)
                    # 如果旧的立账方式是1的话那么会有立账单，那么可以直接修改
                    if int(ZHANG_ID_OLD) == 1:
                        cursor.execute(
                            """UPDATE MF_ARP SET CUS_NO=%s,PAY_DD=%s,AMTN=%s,AMTN_NET=%s,BIL_DD=%s,DEP=%s,ZHANG_ID=%s WHERE BIL_ID='SA' AND ARP_NO=%s""",
                            [CUS_NO,
                             datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                             AMT,
                             AMTN_NET,
                             datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                             DEP,
                             ZHANG_ID,
                             'AP' + PS_NO[2::]
                             ])
                    # 如果旧是立账方式四不为1，然后改为1那么系统会在生成一张立账单
                    if int(ZHANG_ID_OLD) != 1:
                        cursor.execute("""INSERT INTO MF_ARP(ARP_ID,OPN_ID,ARP_NO,BIL_NO,CUS_NO,PAY_DD,AMT,AMTN,AMTN_NET,EXC_RTO,CLOSE_ID,BIL_ID,SYS_DATE,BIL_DD,DEP,ZHANG_ID)VALUES
                                           (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                       [1,
                                        2,
                                        'AP' + PS_NO[2::],
                                        'SA' + PS_NO[2::],
                                        CUS_NO,
                                        datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                        0,
                                        AMT,
                                        AMTN_NET,
                                        1,
                                        'F',
                                        'SA',
                                        datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                        datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                                        DEP,
                                        ZHANG_ID])
                if int(ZHANG_ID) != 1:
                    print(3)
                    cursor.execute(
                        """UPDATE MF_PSS SET PS_DD=%s,CUS_NO=%s,ZHANG_ID=%s,TAX_ID=%s,VOH_ID=%s,DEP=%s,SAL_NO=%s,MODIFY_DD=%s,ARP_NO=%s WHERE PS_ID='SA' AND PS_NO=%s """,
                        [
                            datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                            CUS_NO,
                            ZHANG_ID,
                            TAX_ID,
                            VOH_ID,
                            DEP,
                            SAL_NO,
                            datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                            '',
                            'SA' + PS_NO[2::]
                        ])
                    # 如果立账方式不为1的时候需要删除，立账单
                    cursor.execute(
                        """DELETE FROM MF_ARP WHERE BIL_ID='SA' AND ARP_NO=%s""",
                        [
                            'AP' + PS_NO[2::]
                        ])
                # 插入进货单表身
                print(4)
                cursor.execute(
                    """UPDATE TF_PSS SET PS_DD=%s,UP=%s,AMT=%s,AMTN_NET=%s,CSTN_SAL=%s,TAX=%s,TAX_RTO=%s WHERE PS_ID='SA' AND PS_NO=%s""",
                    [datetime.datetime.strptime(PS_DD, '%Y-%m-%d'),
                     AMT,
                     AMT,
                     AMTN_NET,
                     AMTN_NET,
                     TAX,
                     SPC_TAX,
                     'SA' + PS_NO[2::]
                     ])

            except Exception:
                transaction.savepoint_rollback(sid)  # 语句执行错误，进行语句回滚
                return http.HttpResponseForbidden("新增进货单sql语句执行错误")
            transaction.savepoint_commit(sid)  # 提交事物
        context = {
            "errmsg": '修改进货单成功',
            "code": 200
        }
        return http.JsonResponse(context)

    @transaction.atomic
    def delete(self, request):
        # 1获取参数
        req_data = json.loads(request.body.decode())
        PS_NO = req_data.get("PS_NO")  # 单据日期
        # 判断是否有输入单号
        if PS_NO is None:
            return http.HttpResponseForbidden('请输入销货单号')
        if PS_NO[:3] != 'SAB' or len(PS_NO) != 11:
            # 判断单号是否合法
            return http.HttpResponseForbidden('销货单号输入不合法，单号必须是SAB开头且长度必须为11位')
            # 判断进货单是否存在
        row_PS_NO = examine_ood('MF_PSS', 'PS_NO', PS_NO, 'PS_ID', 'SA')
        if row_PS_NO <= 0:
            return http.HttpResponseForbidden('销货单号不存在')
        # 3数入库
        with connection.cursor() as cursor:
            # 判断单号是否产生后续单据，立账里面的一冲金额
            try:
                # 获取原来单据的立账方式
                ZHANG_ID_OLD = cursor.execute("SELECT ZHANG_ID FROM MF_PSS WHERE PS_ID='SA' AND PS_NO=%s",
                                              ['SA' + PS_NO[2::]]).fetchall()[0][0]
                if int(ZHANG_ID_OLD) == 1:
                    print('AP' + PS_NO[2::])
                    row_no = cursor.execute("SELECT ISNULL(AMTN_RCV,0) FROM MF_ARP WHERE ARP_NO=%s AND BIL_ID='SA'",
                                            ['AP' + PS_NO[2::]]).fetchall()[0][0]
                    if float(row_no) > 0 and row_no is not None:
                        return http.HttpResponseForbidden('销货单已产生后续单据不允许删除')
                if int(ZHANG_ID_OLD) != 1:
                    row_no = cursor.execute("SELECT ISNULL(ACC_FP_NO,'') FROM MF_PSS WHERE PS_NO=%s AND PS_ID='SA'",
                                            ['SA' + PS_NO[2::]]).fetchall()[0][0]
                    if row_no != '':
                        return http.HttpResponseForbidden('销货单已产生后续单据不允许删除')
            except Exception:
                return http.HttpResponseForbidden('判断销货单是否产生后续单据异常')

            sid = transaction.savepoint()  # 开启事物
            try:
                cursor.execute("""DELETE FROM MF_PSS WHERE  PS_NO=%s AND PS_ID='SA'""", ['SA' + PS_NO[2::]])
                cursor.execute("""DELETE FROM TF_PSS WHERE  PS_NO=%s AND PS_ID='SA'""", ['SA' + PS_NO[2::]])
                cursor.execute("""DELETE FROM MF_ARP WHERE  ARP_NO=%s AND BIL_ID='SA'""", ['AP' + PS_NO[2::]])
            except Exception:
                transaction.savepoint_rollback(sid)  # 语句执行错误，进行语句回滚
                return http.HttpResponseForbidden("删除销货单失败")
            transaction.savepoint_commit(sid)  # 提交事物

        context = {
            "errmsg": '删除销货单成功',
            "code": 200
        }
        return http.JsonResponse(context)


#预付款单
class PtPayment(View):
    @transaction.atomic
    def get(self, request):
        context = {
            'a': '客户预收款'
        }
        return http.JsonResponse(context)

    @transaction.atomic
    def post(self, request):
        # 获取参数
        req_data = json.loads(request.body.decode())
        RP_NO = req_data.get("RP_NO")  # 预付款单号         字符类型   ----------------1
        RP_DD = req_data.get("RP_DD")  # 预付款日期            字符类型  --------------------1
        CUS_NO = req_data.get("CUS_NO")  # 预付款客户编码        字符类型  -------------------------1
        CUS_NO_NAME = req_data.get("CUS_NO_NAME",'')  # 预付款客户名称       字符类型  ---------------------1
        AMTN_BC = req_data.get("AMTN_BC")  # 预付款金额            数字类型  ---------------------------1
        CACC_NO = req_data.get("CACC_NO")  # 预付款银行编码          字符类型    ------------1
        USR = req_data.get("USR")  # 制单人编码                   字符类型   ------------------1
        USR_NAME = req_data.get("USR_NAME",'')  # 制单人姓名         字符类型   -----------------------1
        DEP = req_data.get("DEP",'')  # 部门         字符类型   -----------------------1
        SAL_NO = req_data.get("SAL_NO",0)  # 业务员         字符类型   -----------------------1
        VOH_ID = req_data.get("VOH_ID",0)  # 凭证模板         字符类型   -----------------------1
        # {
        #     "RP_DD": "2019-12-06",
        #     "CUS_NO": "AQ2222",
        #     "CUS_NO_NAME": "收款",
        #     "RP_NO": "RTF9C0202",
        #     "AMTN_BC": "150",
        #     "CACC_NO": "01",
        #     "VOH_ID": "01",
        #     "USR": "a00002",
        #     "USR_NAME": "楠楠",
        #     "DEP": "0000",
        #     "SAL_NO": "A00002",
        #     "user": "123",
        #     "password": "123"
        # }

        # 判断单号的合法性
        if RP_NO[:3] != 'RTF' or len(RP_NO) != 11:
            return http.HttpResponseForbidden('预收款单号输入不合法，单号必须是RTF开头且长度长度必须为11位')
        if RP_DD is None:
            return http.HttpResponseForbidden('请输入日期')
        # 校验日期格式
        try:
            datetime.datetime.strptime(RP_DD, '%Y-%m-%d')
        except Exception:
            return http.HttpResponseForbidden('日期格式输入不正确')
        if RP_NO is None:
            return http.HttpResponseForbidden('请输入预收款单号')
        if CACC_NO is None:
            return http.HttpResponseForbidden('请输入银行代号')
        # //判断有没有输入银行账号
        try:
            sum_CACC_NO = et_CACC_NO(CACC_NO)
        except Exception:
            return http.HttpResponseForbidden('请检查银行账号是否存在或者是否输入正确')
        if sum_CACC_NO == 'NO':
            return http.HttpResponseForbidden('请检查银行账号是否存在或者是否输入正确')
            # 判断金额输入是否正确
        if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMTN_BC)) is None:
            return http.HttpResponseForbidden('金额输入不正确')
        # 判断单号是否存在
        row_ZL_NO = examine_ood('TF_MON', 'RP_NO', RP_NO, 'RP_ID', '1')
        if row_ZL_NO > 0:
            return http.HttpResponseForbidden('预付款单号已存在')
        # 判断供应商是否哦存在，不存在的话创建
        try:
            row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '1', datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), 0)
        except Exception:
            return http.HttpResponseForbidden('创建客户失败')
        # 判断用户是否存在
        # row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(PS_DD, '%Y-%m-%d'))
        try:
            # 判断用户是否存在，不存在则创建
            row_usr = usr_data(USR, USR_NAME, datetime.datetime.strptime(RP_DD, '%Y-%m-%d'))
        except Exception:
            return http.HttpResponseForbidden('创建用户失败')
        # 判断账号类别
        try:
            tp_CACC_NO = if_account(CACC_NO)
        except Exception:
            return http.HttpResponseForbidden("账户类型异常(现金)、（银行）")
        if tp_CACC_NO == 'NO':
            return http.HttpResponseForbidden('账户类型不存在(现金)、（银行）')
        with connection.cursor() as cursor:
            row_usr = 0
            try:
                if VOH_ID != 0:
                    row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='RT'", [VOH_ID]).fetchall()[0][0]
            except Exception:
                return http.HttpResponseForbidden('查询凭证模板异常')
            if row_usr <= 0:
                return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板')
            # 获取银行账号的会计科目
            try:
                Acc_No_km = cursor.execute("""SELECT ISNULL(Acc_No,'') FROM BACC WHERE BACC_NO=%s""", [CACC_NO]).fetchall()[0][0]
            except Exception:
                return http.HttpResponseForbidden("银行会计科目异常")
            if Acc_No_km == '':
                return http.HttpResponseForbidden('银行会计科目不存在')
            sid = transaction.savepoint()  # 开启事物
            try:
                # 银行账户
                if int(tp_CACC_NO) == 1:
                    print(11112222)
                    # 插入预收款表头
                    cursor.execute("""INSERT INTO TF_MON(IRP_ID,RP_NO,ITM,RP_ID,CLS_ID,RP_DD,CUS_NO,INCLUDESON,AMTN_BB,AMTN_CLS,BC_NO,BACC_NO,EXC_RTO,DEP,USR,CHK_MAN,CLS_DATE,SYS_DATE,IEA_ID,IOR_ID,SK_TYPE,VOH_ID,USR_NO)VALUES
                                   (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                   [
                                       'T',
                                       RP_NO,
                                       1,
                                       1,
                                       'F',
                                       datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),
                                       CUS_NO,
                                       'F',
                                       AMTN_BC,
                                       0,
                                       'BT' + RP_NO[2::],
                                       CACC_NO,
                                       1,
                                       DEP,
                                       USR,
                                       USR,
                                       datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),
                                       datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),
                                       'F',
                                       'F',
                                       1,
                                       VOH_ID,
                                       SAL_NO
                                   ])
                    print(2323)
                # 现金账户
                if int(tp_CACC_NO) == 2:
                    print(1122)
                    print(VOH_ID)
                    print(SAL_NO)
                    # 插入预收款表头
                    cursor.execute("""INSERT INTO TF_MON(IRP_ID,RP_NO,ITM,RP_ID,CLS_ID,RP_DD,CUS_NO,INCLUDESON,AMTN_BC,AMTN_CLS,BC_NO,CACC_NO,EXC_RTO,DEP,USR,CHK_MAN,CLS_DATE,SYS_DATE,IEA_ID,IOR_ID,SK_TYPE,VOH_ID,USR_NO)VALUES
                     (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                                   ['T',RP_NO,1,1,'F',datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CUS_NO,'F',AMTN_BC,0,'BT' + RP_NO[2::],CACC_NO,1,'0000',USR,USR,datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),
                                    datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),'F','F',1,VOH_ID,SAL_NO])
                print(1)
                # 插入语收款立账金额
                cursor.execute("""INSERT INTO MF_MON(RP_ID,RP_NO,RP_DD,DEP,AMTN,AMTN_ARP,AMTN_REST,FJ_NUM)VALUES
                               (%s,%s,%s,%s,%s,%s,%s,%s)""",
                               [1,
                                RP_NO,
                                datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),
                                DEP,
                                AMTN_BC,
                                0,
                                0,
                                0])
                print(3)
                # 插入账户收支单表头
                cursor.execute("""INSERT INTO MF_BAC(BB_ID,BB_NO,BB_DD,BACC_NO,ACC_NO,DEP,BIL_NO,EXC_RTO,AMTN,USR,CHK_MAN,OPN_ID,CLS_DATE,SYS_DATE,BIL_ID_N,BIL_NO_N)VALUES
                                    (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                               ['BT',
                                'BT' + RP_NO[2::],
                                datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),
                                CACC_NO,
                                Acc_No_km,
                                DEP,
                                'BT' + RP_NO,
                                1,
                                float(AMTN_BC),
                                USR,
                                USR,
                                'F',
                                datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),
                                datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),
                                'RT',
                                RP_NO])
                print(4)
                # 插入收支单表身
                cursor.execute("""INSERT INTO TF_BAC(BB_ID,BB_NO,ITM,BB_DD,EXC_RTO,AMTN,DEP,CUS_NO,ADD_ID,PRE_ITM)values
                                    (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                               ['BT',
                                'BT' + RP_NO[2::],
                                1,
                                datetime.datetime.strptime(RP_DD, '%Y-%m-%d'),
                                1,
                                AMTN_BC,
                                DEP,
                                CUS_NO,
                                '+',
                                1 ])
            except Exception:
                transaction.savepoint_rollback(sid)
                return http.HttpResponseForbidden("预收款单新增sql语句执行异常")
            transaction.savepoint_commit(sid)
        context = {
            "errmsg": '新增客户预付款单成功',
            "code": 200
        }
        return http.JsonResponse(context)

    @transaction.atomic
    def put(self, request):

        # 获取参数
        # 获取参数
        req_data = json.loads(request.body.decode())
        RP_NO = req_data.get("RP_NO")  # 预付款单号         字符类型   ----------------1
        RP_DD = req_data.get("RP_DD")  # 预付款日期            字符类型  --------------------1
        CUS_NO = req_data.get("CUS_NO")  # 预付款客户编码        字符类型  -------------------------1
        CUS_NO_NAME = req_data.get("CUS_NO_NAME", '')  # 预付款客户名称       字符类型  ---------------------1
        AMTN_BC = req_data.get("AMTN_BC")  # 预付款金额            数字类型  ---------------------------1
        CACC_NO = req_data.get("CACC_NO")  # 预付款银行编码          字符类型    ------------1
        USR = req_data.get("USR")  # 制单人编码                   字符类型   ------------------1
        USR_NAME = req_data.get("USR_NAME", '')  # 制单人姓名         字符类型   -----------------------1
        DEP = req_data.get("DEP", '')  # 部门         字符类型   -----------------------1
        SAL_NO = req_data.get("SAL_NO", 0)  # 业务员         字符类型   -----------------------1
        VOH_ID = req_data.get("VOH_ID", 0)  # 凭证模板         字符类型   -----------------------1
        UP_DD = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        # {
        #     "RP_DD": "2019-12-06",
        #     "CUS_NO": "AQ2222",
        #     "CUS_NO_NAME": "收款",
        #     "RP_NO": "RTF9C0201",
        #     "AMTN_BC": "100",
        #     "CACC_NO": "01",
        #     "VOH_ID": "01",
        #     "USR": "a00002",
        #     "USR_NAME": "楠楠",
        #     "DEP": "0000",
        #     "SAL_NO": "A00002",
        #     "user": "123",
        #     "password": "123"
        # }
        if RP_DD is None:
            return http.HttpResponseForbidden('请输入时间')
        if RP_NO[:3] != 'RTF' or len(RP_NO) !=11:
            return http.HttpResponseForbidden('预收款单号输入不合法，单号必须是RTF开头且长度必须是11位')
        # 校验日期格式
        try:
            datetime.datetime.strptime(RP_DD, '%Y-%m-%d')
        except Exception:
            return http.HttpResponseForbidden('日期格式输入不正确')
        if RP_NO is None:
            return http.HttpResponseForbidden('请输入预收款单号')
        if CACC_NO is None:
            return http.HttpResponseForbidden('请输入银行代号')
        # //判断有没有输入银行账号
        try:
            sum_CACC_NO = et_CACC_NO(CACC_NO)
        except Exception:
            return http.HttpResponseForbidden('请检查银行账号是否存在或者是否输入正确')
        if sum_CACC_NO == 'NO':
            return http.HttpResponseForbidden('请检查银行账号是否存在或者是否输入正确')
            # 判断金额输入是否正确
        if re.compile('(^-?[0-9](\d+)?(\.\d{1,6})?$)|(^0$)|(^\d\.\d{1,2}$)').match(str(AMTN_BC)) is None:
            return http.HttpResponseForbidden('金额输入不正确')
        # 判断单号是否存在
        try:
            row_ZL_NO = examine_ood('TF_MON', 'RP_NO', RP_NO, 'RP_ID', '1')
        except Exception:
            return http.HttpResponseForbidden('判断单号是否存在，单号查询异常')
        if row_ZL_NO <= 0:
            return http.HttpResponseForbidden('预付款单号不存在')

        # 判断单号是不是已经产生付款单
        try:
            row_ZL_NO = examine_ood('TF_MON1', 'IRP_NO', RP_NO, 'RP_ID', '1')
        except Exception:
            return http.HttpResponseForbidden('判断单号是否产生后续单据异常')
        if row_ZL_NO > 0:
            return http.HttpResponseForbidden('预付款单已经产生付款单')
        # 判断账号类别
        try:
            tp_CACC_NO = if_account(CACC_NO)
        except Exception:
            return http.HttpResponseForbidden("账户类型异常(现金)、（银行）")
        if tp_CACC_NO == 'NO':
            return http.HttpResponseForbidden('账户类型不存在(现金)、（银行）')
        # 判断供应商是否哦存在，不存在的话创建
        try:
            row_cus_no = customer_data(CUS_NO, CUS_NO_NAME, '1', datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), 0)
        except Exception:
            return http.HttpResponseForbidden('创建客户失败')

        with connection.cursor() as cursor:
            row_usr = 0
            try:
                if VOH_ID != 0:
                    row_usr = cursor.execute("SELECT COUNT(*) FROM MF_VHID WHERE VOH_ID=%s AND BIL_ID='RT'", [VOH_ID]).fetchall()[
                        0][0]
            except Exception:
                return http.HttpResponseForbidden('查询凭证模板异常')
            if row_usr <= 0:
                return http.HttpResponseForbidden('凭证模板在ERP里面不存在请重新输入凭证模板')
            # 获取银行账号的会计科目
            try:
                Acc_No_km = cursor.execute("""SELECT ISNULL(Acc_No,'') FROM BACC WHERE BACC_NO=%s""", [CACC_NO]).fetchall()[0][0]
            except Exception:
                return http.HttpResponseForbidden("银行会计科目异常")
            if Acc_No_km == '':
                return http.HttpResponseForbidden('银行会计科目不存在')
            sid = transaction.savepoint()  # 开启事物
            try:
                # 银行账户
                if int(tp_CACC_NO) == 1:
                    # 修改预付款单
                    print(11111)
                    cursor.execute(
                        """UPDATE TF_MON SET RP_DD=%s,CUS_NO=%s,AMTN_BB=%s,BC_NO=%s,BACC_NO=%s,MODIFY_DD=%s,MODIFY_MAN=%s,VOH_ID=%s,USR_NO=%s,AMTN_BC=NULL,CACC_NO='' WHERE RP_NO=%s""",
                        [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CUS_NO, AMTN_BC, 'BT' + RP_NO[2::], CACC_NO,
                         UP_DD,USR,VOH_ID,SAL_NO, RP_NO])
                # 现金账户
                if int(tp_CACC_NO) == 2:
                    print(11222)
                    # 修改预付款单
                    cursor.execute(
                        """UPDATE TF_MON SET RP_DD=%s,CUS_NO=%s,AMTN_BC=%s,BC_NO=%s,CACC_NO=%s,MODIFY_DD=%s,MODIFY_MAN=%s ,VOH_ID=%s,USR_NO=%s,AMTN_BB=NULL,BACC_NO='' WHERE RP_NO=%s""",
                        [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CUS_NO, AMTN_BC, 'BT' + RP_NO[2::], CACC_NO,
                         UP_DD,USR,VOH_ID,SAL_NO, RP_NO])
                # 修改立账单金额
                print(2)
                cursor.execute("""UPDATE MF_MON SET RP_DD=%s,AMTN=%s WHERE RP_NO=%s""",
                               [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), AMTN_BC, RP_NO])
                print(3)
                # 修改账户收支单表头
                cursor.execute("""UPDATE MF_BAC SET BB_DD=%s,BACC_NO=%s,ACC_NO=%s,BIL_NO=%s,AMTN=%s,DEP=%s WHERE BB_NO=%s""",
                               [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), CACC_NO, Acc_No_km, 'BT' + RP_NO,
                                float(AMTN_BC),DEP, 'BT' + RP_NO[2::]])
                print(4)
                # 修改账户收支单表身
                cursor.execute("""UPDATE TF_BAC SET BB_DD=%s,AMTN=%s,CUS_NO=%s WHERE BB_NO=%s""",
                               [datetime.datetime.strptime(RP_DD, '%Y-%m-%d'), AMTN_BC, CUS_NO, 'BT' + RP_NO[2::]])
            except Exception:
                transaction.savepoint_rollback(sid)
                return http.HttpResponseForbidden("预收款单修改sql语句执行异常")
            transaction.savepoint_commit(sid)
        context = {
            "errmsg": '修改客户预付款单成功',
            "code": 200
        }
        return http.JsonResponse(context)

    @transaction.atomic
    def delete(self, request):
        # 获取参数
        req_data = json.loads(request.body.decode())
        RP_NO = req_data.get("RP_NO")  # 预付款单号
        # {
        #     "RP_NO": "RTX9A300003"
        # }
        # 判断单号的合法性
        if RP_NO[:3] != 'RTF' or len(RP_NO) != 11:
            return http.HttpResponseForbidden('预收款单号输入不合法，单号必须是RTF开头且长度必须为11位')
        if RP_NO is None:
            return http.HttpResponseForbidden('请输入预收款单号')
        # 判断单号是不是已经产生付款单
        try:
            row_ZL_NO = examine_ood('TF_MON1', 'IRP_NO', RP_NO, 'RP_ID', '1')
        except Exception:
            return http.HttpResponseForbidden('判断单号是否产生后续单据异常')
        if row_ZL_NO > 0:
            return http.HttpResponseForbidden('预付款单已经产生付款单')
        # 判断单号是否存在
        row_ZL_NO = examine_ood('TF_MON', 'RP_NO', RP_NO, 'RP_ID', '1')
        if row_ZL_NO == 0:
            return http.HttpResponseForbidden('预付款单号不存在')
        with connection.cursor() as cursor:
            sid = transaction.savepoint()  # 开启事物
            try:
                # 删除付款单
                cursor.execute("""DELETE FROM TF_MON WHERE RP_NO=%s AND RP_ID='1'""", [RP_NO])
                # 删除金额立账单
                cursor.execute("""DELETE FROM MF_MON WHERE RP_NO=%s  AND RP_ID='1'""", [RP_NO])
                # 删除账户收支单表头
                cursor.execute("""DELETE FROM MF_BAC WHERE BB_NO=%s""", ['BT' + RP_NO[2::]])
                # 删除账户收支单表身
                cursor.execute("""DELETE FROM TF_BAC WHERE BB_NO=%s""", ['BT' + RP_NO[2::]])
            except Exception:
                transaction.savepoint_rollback(sid)
                return http.HttpResponseForbidden("预收款单删除sql语句执行异常")
            transaction.savepoint_commit(sid)
        context = {
            "errmsg": '删除客户预付款单成功',
            "code": 200
        }
        return http.JsonResponse(context)

#回传进货票
class PcInvoice(View):
    def post(self, request):
        with connection.cursor() as cursor:
            # 判断表是否存在
            is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='MF_LZ1_Z01'""", []).fetchall()
            if len(is_table)==0:
                return http.HttpResponseForbidden("MF_LZ1_Z01,此表不存在请先创建此表")
            #判断表中是否存在数据
            pc_count=cursor.execute("""SELECT COUNT(*) FROM MF_LZ1_Z01""", []).fetchall()[0][0]
            if pc_count==0:
                return http.HttpResponseForbidden("表数据为空请先增加数据")
            #查询未被拉取的数据
            pc_date = cursor.execute("""SELECT ISNULL(A_LZ_DD,'') AS A_LZ_DD,ISNULL(A_CUS_NO ,'') AS A_CUS_NO,ISNULL(C_NAME,'') AS C_NAME,ISNULL(A_LZ_NO,'') AS A_LZ_NO,ISNULL(A_INV_NO,'') AS A_INV_NO,
                                    ISNULL(A_AMT,0) AS A_AMT,ISNULL(A_AMTN_NET,0) AS A_AMTN_NET,ISNULL(A_TAX,0) AS A_TAX,ISNULL(A_TAX_ID,'') AS A_TAX_ID,ISNULL(A_ZHANG_ID,'') AS A_ZHANG_ID,
                                    ISNULL(B_PS_NO,'') AS B_PS_NO,ISNULL(B_QTY,0) AS B_QTY,ISNULL(B_UP,0) AS B_UP,ISNULL(B_AMT,0) AS B_AMT,ISNULL(B_TAX_RTO,0) AS B_TAX_RTO,OK FROM MF_LZ1_Z01 WHERE OK ='N'""", []).fetchall()
            # print(pc_date)
            # print(len(pc_date))
            if len(pc_date)==0:
                return http.HttpResponseForbidden("没有需要拉取的数据")
            data=[]
            LZ_NO=[]
            for i in pc_date:
                # print(1111)
                LZ_NO.append(i[3])
                # data.append({"A_LZ_DD": i[0], "A_CUS_NO": i[1]})
                data.append({"A_LZ_DD":str(i[0]),"A_CUS_NO":i[1],"C_NAME":i[2],"A_LZ_NO":i[3],
                "A_INV_NO":i[4],"A_AMT":float(i[5]),"A_AMTN_NET":float(i[6]),"A_TAX":float(i[7]),"A_TAX_ID":i[8],
                "A_ZHANG_ID":i[9],"B_PS_NO":i[10],"B_QTY":float(i[11]),"B_UP":float(i[12]),"B_AMT":float(i[13]),"B_TAX_RTO":float(i[14]),"OK":i[15].strip()})
            for i in LZ_NO:
                cursor.execute("""UPDATE MF_LZ1_Z01 SET OK='Y' WHERE A_LZ_NO=%s""", [i])
        context = {
            "data":data,
            "errmsg": "进货开票数据回传app成功",
            "code":200
        }
        return http.JsonResponse(context)

#回传销货票
class SaInvoice(View):
    def post(self, request):
        with connection.cursor() as cursor:
            # 判断表是否存在
            is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='MF_LZ_Z01'""", []).fetchall()
            if len(is_table)==0:
                return http.HttpResponseForbidden("MF_LZ1_Z01,此表不存在请先创建此表")
            #判断表中是否存在数据
            sa_count=cursor.execute("""SELECT COUNT(*) FROM MF_LZ_Z01""", []).fetchall()[0][0]
            if sa_count==0:
                return http.HttpResponseForbidden("表数据为空请先增加数据")
                # 查询未被拉取的数据
            sa_date = cursor.execute("""SELECT ISNULL(A_LZ_DD,'') AS A_LZ_DD,ISNULL(A_CUS_NO,'') AS A_CUS_NO,ISNULL(C_NAME,'') AS C_NAME,ISNULL(A_LZ_NO,'') AS A_LZ_NO,ISNULL(A_INV_NO,'') AS A_INV_NO,
                                    ISNULL(A_AMT,0) AS A_AMT,ISNULL(A_AMTN_NET,0) AS A_AMTN_NET,ISNULL(A_TAX,0) AS A_TAX,ISNULL(A_TAX_ID,'') AS A_TAX_ID,ISNULL(A_ZHANG_ID,'') AS A_ZHANG_ID,
                                    ISNULL(B_CK_NO,'') AS B_CK_NO,ISNULL(B_QTY,0) AS B_QTY,ISNULL(B_UP,0) AS B_UP,ISNULL(B_AMT,0) AS B_AMT,ISNULL(B_TAX_RTO,0) AS B_TAX_RTO,OK FROM MF_LZ_Z01 WHERE OK ='N'""",[]).fetchall()
            if len(sa_date)==0:
                return http.HttpResponseForbidden("没有需要拉取的数据")
            data = []
            LZ_NO = []
            for i in sa_date:
                # print(1111)
                LZ_NO.append(i[3])
                # data.append({"A_LZ_DD": i[0], "A_CUS_NO": i[1]})
                data.append({"A_LZ_DD": str(i[0]), "A_CUS_NO": i[1], "C_NAME": i[2], "A_LZ_NO": i[3],
                             "A_INV_NO": i[4], "A_AMT": float(i[5]), "A_AMTN_NET": float(i[6]), "A_TAX": float(i[7]),
                             "A_TAX_ID": i[8],"A_ZHANG_ID": i[9], "B_CK_NO": i[10], "B_QTY": float(i[11]), "B_UP": float(i[12]),
                             "B_AMT": float(i[13]), "B_TAX_RTO": float(i[14]), "OK": i[15].strip()})
            for i in LZ_NO:
                cursor.execute("""UPDATE MF_LZ_Z01 SET OK='Y' WHERE A_LZ_NO=%s""", [i])
            context = {
                "data": data,
                "errmsg": "销货开票数据回传app成功",
                "code": 200
            }
            return http.JsonResponse(context)

#客户预收冲应收
# SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD,
# ISNULL(C_RP_NO,'') AS C_RP_NO,ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS,
# ISNULL(OK,'') AS OK,ISNULL(IRP_NO,'') AS IRP_NO,ISNULL(AMTN_IRP,0) AS AMTN_IRP,ISNULL(B_BIL_ID,'') AS B_BIL_ID
#  FROM TC_MON_PC WHERE C_RP_ID=1 AND ISNULL(IRP_NO,'')<>''
class yrt(View):
    def post(self, request):
        with connection.cursor() as cursor:
            # 判断表是否存在
            is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='TC_MON_PC'""", []).fetchall()
            if len(is_table)==0:
                return http.HttpResponseForbidden("TC_MON_PC,此表不存在请先创建此表")
            #判断表中是否存在数据
            sa_count=cursor.execute("""SELECT COUNT(*) FROM TC_MON_PC""", []).fetchall()[0][0]
            if sa_count==0:
                return http.HttpResponseForbidden("表数据为空请先增加数据")
                # 查询未被拉取的数据
            sa_date = cursor.execute("""SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD,
                                    ISNULL(C_RP_NO,'') AS C_RP_NO,ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS,
                                    ISNULL(OK,'') AS OK,ISNULL(IRP_NO,'') AS IRP_NO,ISNULL(AMTN_IRP,0) AS AMTN_IRP,ISNULL(B_BIL_ID,'') AS B_BIL_ID
                                    FROM TC_MON_PC WHERE C_RP_ID=1 AND ISNULL(IRP_NO,'')<>'' AND OK='N'""",[]).fetchall()
            if len(sa_date)==0:
                return http.HttpResponseForbidden("没有需要拉取的数据")
            data = []
            RT_NO = []
            for i in sa_date:
                # print(1111)
                RT_NO.append(i[3])
                data.append({"C_CUS_NO": i[0], "D_NAME": i[1], "C_RP_DD": i[2], "C_RP_NO": str(i[3]),
                             "B_BIL_NO": i[4], "A_AMTN_CLS": i[5], "OK": i[6], "IRP_NO": i[7].strip(),
                             "AMTN_IRP": i[8], "B_BIL_ID": i[9]})
            for i in RT_NO:
                cursor.execute("""UPDATE TC_MON_PC SET OK='Y' WHERE C_RP_NO=%s""", [i])
            context = {
                "data": data,
                "errmsg": "客户预收冲应收数据回传app成功",
                "code": 200
            }
            return http.JsonResponse(context)
#客户收款单
# SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD,
# ISNULL(C_CACC_NO,'') AS C_CACC_NO,ISNULL(C_AMTN_BC,0) AS C_AMTN_BC,ISNULL(C_RP_NO,'') AS C_RP_NO,
# ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(B_BIL_ID,'') AS B_BIL_ID,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS,
# ISNULL(OK,'') AS OK
#  FROM TC_MON_PC WHERE C_RP_ID=1 AND ISNULL(B_BIL_ID,'')<>'' AND ISNULL(IRP_NO,'')=''
class rt(View):
    def post(self, request):
        with connection.cursor() as cursor:
            # 判断表是否存在
            is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='TC_MON_PC'""", []).fetchall()
            if len(is_table)==0:
                return http.HttpResponseForbidden("TC_MON_PC,此表不存在请先创建此表")
            #判断表中是否存在数据
            sa_count=cursor.execute("""SELECT COUNT(*) FROM TC_MON_PC""", []).fetchall()[0][0]
            if sa_count==0:
                return http.HttpResponseForbidden("表数据为空请先增加数据")
                # 查询未被拉取的数据
            sa_date = cursor.execute("""SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD,
            ISNULL(C_CACC_NO,'') AS C_CACC_NO,ISNULL(C_AMTN_BC,0) AS C_AMTN_BC,ISNULL(C_RP_NO,'') AS C_RP_NO,
            ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(B_BIL_ID,'') AS B_BIL_ID,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS,
            ISNULL(OK,'') AS OK FROM TC_MON_PC WHERE C_RP_ID=1 AND ISNULL(B_BIL_ID,'')<>'' AND ISNULL(IRP_NO,'')='' AND OK='N'""",[]).fetchall()
            if len(sa_date) == 0:
                return http.HttpResponseForbidden("没有需要拉取的数据")
            data = []
            RT_NO = []
            for i in sa_date:
                # print(1111)
                RT_NO.append(i[5])
                data.append({"C_CUS_NO": i[0], "D_NAME": i[1], "C_RP_DD": i[2], "C_CACC_NO": str(i[3]),
                             "C_AMTN_BC": float(i[4]), "C_RP_NO": i[5], "B_BIL_NO": i[6], "B_BIL_ID": i[7].strip(),
                             "A_AMTN_CLS": float(i[8]), "OK": i[9]})
            for i in RT_NO:
                cursor.execute("""UPDATE TC_MON_PC SET OK='Y' WHERE C_RP_NO=%s""", [i])
            context = {
                "data": data,
                "errmsg": "客户收款单数据回传app成功",
                "code": 200
            }
            return http.JsonResponse(context)
# 厂商付款单
# SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD,
# ISNULL(C_CACC_NO,'') AS C_CACC_NO,ISNULL(C_AMTN_BC,0) AS C_AMTN_BC,ISNULL(C_RP_NO,'') AS C_RP_NO,
# ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(B_BIL_ID,'') AS B_BIL_ID,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS,
# ISNULL(OK,'') AS OK
#  FROM TC_MON_PC WHERE C_RP_ID=2 AND ISNULL(B_BIL_ID,'')<>'' AND ISNULL(IRP_NO,'')=''
class pt(View):
    def post(self, request):
        with connection.cursor() as cursor:
            # 判断表是否存在
            is_table=cursor.execute("""SELECT table_name FROM information_schema.TABLES WHERE table_name ='TC_MON_PC'""", []).fetchall()
            if len(is_table)==0:
                return http.HttpResponseForbidden("TC_MON_PC,此表不存在请先创建此表")
            #判断表中是否存在数据
            sa_count=cursor.execute("""SELECT COUNT(*) FROM TC_MON_PC""", []).fetchall()[0][0]
            if sa_count==0:
                return http.HttpResponseForbidden("表数据为空请先增加数据")
                # 查询未被拉取的数据
            sa_date = cursor.execute("""SELECT ISNULL(C_CUS_NO,'') AS C_CUS_NO,ISNULL(D_NAME,'') AS D_NAME,ISNULL(C_RP_DD,'') AS C_RP_DD,
                                    ISNULL(C_CACC_NO,'') AS C_CACC_NO,ISNULL(C_AMTN_BC,0) AS C_AMTN_BC,ISNULL(C_RP_NO,'') AS C_RP_NO,
                                    ISNULL(B_BIL_NO,'') AS B_BIL_NO,ISNULL(B_BIL_ID,'') AS B_BIL_ID,ISNULL(A_AMTN_CLS,0)AS A_AMTN_CLS,
                                    ISNULL(OK,'') AS OK FROM TC_MON_PC WHERE C_RP_ID=2 AND ISNULL(B_BIL_ID,'')<>'' AND ISNULL(IRP_NO,'')='' AND OK='N'""",[]).fetchall()
            if len(sa_date) == 0:
                return http.HttpResponseForbidden("没有需要拉取的数据")
            data = []
            RT_NO = []
            for i in sa_date:
                # print(1111)
                RT_NO.append(i[5])
                data.append({"C_CUS_NO": i[0], "D_NAME": i[1], "C_RP_DD": i[2], "C_CACC_NO": str(i[3]),
                             "C_AMTN_BC": float(i[4]), "C_RP_NO": i[5], "B_BIL_NO": i[6], "B_BIL_ID": i[7].strip(),
                             "A_AMTN_CLS": float(i[8]), "OK": i[9]})
            for i in RT_NO:
                cursor.execute("""UPDATE TC_MON_PC SET OK='Y' WHERE C_RP_NO=%s""", [i])
            context = {
                "data": data,
                "errmsg": "客户收款单数据回传app成功",
                "code": 200
            }
            return http.JsonResponse(context)
# 渲染order.html模板数据订单一览表
class index(View):
    def get(self, request):
        return render(request, 'order.html')
# 订单一览表数据接口
class listtb(View):
    def get(self, request):
        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']  #生产部门
        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 = settings.PAGE_SIZE
        # 处理客户代号
        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 '+ 'A.CLS_ID ='+"'"+cls_id+"'"
            else:
                cls_id = ' AND ' + 'A.CLS_ID !=' + "'" + 'T' + "'"
        # 处理是否回复
        if PMC_REM:
            if PMC_REM =='1':
                PMC_REM = ''
            elif PMC_REM == '2':
                PMC_REM = ' AND '+ "ISNULL(C.PMC_REM,'') != ''"
            else:
                PMC_REM = ' AND ' + "ISNULL(C.PMC_REM,'') = ''"
        # 处理业务交期
        if 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 sort:
            # F.SPC_NO
            if sort == '1':
                sort = ' ORDER BY OS_NO DESC'
                sort_if='1'
            if sort == '2':
                sort = ' ORDER BY SPC_NO DESC'
            if sort == '3':
                sort = ' ORDER BY CUS_NO DESC'
            else:
                sort = ' ORDER BY PMC_DD DESC'
        if select=='2':
            conditional = ' left(Convert(varchar(100), OS_DD, 23), 7) >=' + "'" + th_time + "'" + ' ORDER BY OS_NO DESC'
        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 + sort
            # 定义sql语句
        if ifmerge=='1':
            sql = """SELECT 
    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
     FROM View_aa WHERE {0}""".format(conditional)
        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 + ' GROUP BY OS_NO,PRD_NO,OS_DD,PMC_DD,ORDER0,CUS_SNM,DZRL,RQ,PD_DEP,REM ' + sort
            sql = """SELECT 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
 FROM View_aa A WHERE {0}""".format(conditional)
        print(sql)
        with connection.cursor() as cursor:
            no_data = cursor.execute(sql,[]).fetchall()
        data = [
                    {"ORDER0": "类别", "OS_DD": '下单日期', "CUS_SNM": "客户名称",
                     "OS_NO":"计划单号","PRD_NO":"产品编码","DZRL":"容量（ML）",
                     "QY":"容器","YSSL":"套装颜色数","YMXH":"研磨品类型",
                     "QTY":"订单数量","GZSL":"灌装支数量","EST_DD":"业务交期",
                     "PD_DEP":"生产部门","REM":"备注","PMC_DD":"PMC回复交期","WH_REM":"大货备注",
                     "operation":"操作","ITM":"项次"},
                ]
        # OS_NO = []  啊啊撒打算梵蒂冈git add .
        for i in no_data:
            # print(1111)
            # OS_NO.append(i[5])
            data.append({"ORDER0": i[0], "OS_DD": str(i[1])[:10] , "CUS_SNM": i[2], "OS_NO": str(i[3]),
                         "PRD_NO": i[4], "DZRL": i[5], "QY": i[6], "YSSL": i[7],
                         "YMXH": i[8], "QTY": float(i[9]),"GZSL": float(i[10]),"EST_DD": str(i[11]),
                         "PD_DEP": i[12],"REM":i[13],"PMC_DD": str(i[14]),"WH_REM": i[15],"operation": "保存","ITM":i[16]})
        # print(data)
        context = {
            "data": data,
            "errmsg": "一览表获取成功",
            "code": 200
        }
        return http.JsonResponse(context)

    def put(self, request):
        # req_data = json.loads(request.body.decode())
        try:
            eq_data = json.loads(request.body.decode())["params"]
        except Exception:
            return http.HttpResponseForbidden('参数不全')
        req_data = json.loads(request.body.decode())["params"]
        OS_NO = req_data.get("OS_NO")
        PMC_DD = req_data.get("PMC_DD",'')
        WH_REM = req_data.get("WH_REM",'')
        ITM = req_data.get("ITM")
        with connection.cursor() as cursor:
            cursor.execute(
                """UPDATE TF_POS_Z SET PMC_REM=%s,WH_REM=%s WHERE OS_ID='SO' AND OS_NO=%s AND ITM=%s """,
                [PMC_DD,WH_REM,OS_NO,ITM])
        context = {
            "errmsg": "保存成功",
            "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 post(self,request):
        # 获取数据
        req_data = json.loads(request.body.decode())["params"]
        print(req_data["data_list"])
        t_today = datetime.datetime.now().strftime('%Y-%m-%d')  # 获取年月日
        itm = 1 #定义项次
        with connection.cursor() as cursor:
            # 获取数据里面当天最大的单号，然后处理生产单号
            sql_no = """SELECT MAX(NO) AS NO FROM MCAF WHERE DD='{0}'""".format(t_today)
            monthly_odd_new = monthly_odd(sql_no)  # 生产单号
            for i in req_data["data_list"]:
                # 计算订单可维持天数
                tolf_days = float(i["un_qty"])/float(i["s_capacity"])
                #计算现有配置可生产天数
                p_d_f_e_configurations = float(i["un_qty"])/float(i["e_capacity"])
                #计算现有出勤人员合计
                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
                cursor.execute(sql,[])
        # data = {"monthly_odd_new":monthly_odd_new}
        context = {
            "data": monthly_odd_new,
            "errmsg": "保存成功",
            "code": 200
        }
        return http.JsonResponse(context)
# 获取部门数据
class dept(View):
    def get(self, request):
        with connection.cursor() as cursor:
            dep_data = cursor.execute("""SELECT DEP,NAME FROM DEPT WHERE MAKE_ID IN('1','3')""",[]).fetchall()
            # print(dep_data)
            data = []
            # print(dep_data)
            for i in dep_data:
                # print(1111)
                data.append({"DEP": i[0], "DEP_NAME": i[1]})
            # print(data)
            context = {
                "data": data,
                "errmsg": "获取部门成功",
                "code": 200
            }
            return http.JsonResponse(context)
# 获取货品资料
class Prdt(View):
    def get(self,request):
        search_no = request.GET["search_no"]
        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,
                    "errmsg": "没有获取到数据",
                    "code": 510
                }
                return http.JsonResponse(context)
        if len(dep_data)!=0:
            for i in dep_data:
                data.append({"id": i[0], "text":i[0]+'--'+ i[1]})
        # print(data)
        context = {
            "data": data,
            "errmsg": "获取部门成功",
            "code": 200
        }
        return http.JsonResponse(context)
# 获取客户信息
class Cust(View):
    def get(self, request):
        search_no = request.GET["search_no"]
        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,
                    "errmsg": "没有获取到数据",
                    "code": 510
                }
                return http.JsonResponse(context)
        if len(dep_data) != 0:
            for i in dep_data:
                data.append({"id": i[0], "text":i[0] +'--'+ i[1]})
        # print(data)
        context = {
            "data": data,
            "errmsg": "获取部门成功",
            "code": 200
        }
        return http.JsonResponse(context)
# 查询单号
class oddNo(View):
    def get(self, request):
        sql = ("SELECT TOP 5 MO_NO FROM MF_MO ")
        result = executeQuery(sql)
        context = {
            "data": result,
            "errmsg": "获取单号成功",
            "code": 200
        }
        return http.JsonResponse(context)

# 月度产能评估表更具部门获取数据
class DepMacfDepData(View):
    def get(self, request):
        sql = ("""SELECT b.dep,b.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,
    0 as s_capacity ,0 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 dept b on a.dep=b.dep where isnull(a.CLOSE_ID,'')!='T' and a.dep='MMG01L1' group by b.dep,b.name""")
        result = executeQuery(sql)
        context = {
            "data": result[0],
            "errmsg": "获取部门数据成功",
            "code": 200
        }
        return http.JsonResponse(context)
# 生成月度产能评估表单号
class monthly(View):
    def get(self, request):
        monthly_odd_old='20200219009'
        monthly_odd_new = monthly_odd(monthly_odd_old)
        # data = {"monthly_odd_new":monthly_odd_new}
        context = {
            "data": monthly_odd_new,
            "errmsg": "获取部门成功",
            "code": 200
        }
        return http.JsonResponse(context)
# 测试
class text(View):
    def get(self, request):
        return render(request, 'text.html')