wnserver/obj/apps/bg/views.py

from django.db import transaction, DatabaseError

import datetime
# Create your views here.
# 查询制程信息
from django.http import Http404
from rest_framework import status
from rest_framework.authentication import SessionAuthentication
from rest_framework.permissions import IsAuthenticated
from rest_framework.response import Response
from rest_framework.views import APIView
from rest_framework_jwt.authentication import JSONWebTokenAuthentication

from utils.monthly_odd import monthly_odd
from utils.executeQuery import executeQuery,IseUpDelQuery,rpoold
from utils.filters import filter
from utils.ClssSql import ClassSqls
from .models import Django_mf_bg,Django_tf_bg
from .bgutils import uptz
from .utils import MyException
# 过滤通知单信息
class TzInfo(APIView):
    # 1,设置局部认证
    authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication,)
    # authentication_classes = [SessionAuthentication, BasicAuthentication]
    # 2,设置局部权限
    permission_classes = (IsAuthenticated,)

    # permission_classes = (AllowAny,) #任何用户都能访问
    def get(self, request):
        params = request.query_params
        mo_no = params.get('mo_no')
        mo_no = mo_no.replace('\n', '')  #去除换行
        mo_no = mo_no.replace(' ', '')  #去除空格
        zc_no = params.get('zc_no')
        bg_id = params.get('bg_id')
        zc_no_dn = params.get('zc_no_dn')
        print(mo_no)
        print(zc_no)
        print(bg_id)
        if bg_id is None:
            return Response('单据类别异常', status=status.HTTP_416_REQUESTED_RANGE_NOT_SATISFIABLE)
        if zc_no is None or mo_no is None:
            return Response('参数异常', status=status.HTTP_416_REQUESTED_RANGE_NOT_SATISFIABLE)
        if bg_id=='JS':
            sql = ClassSqls.TzInfoJs
        elif bg_id=='PG':
            sql = ClassSqls.TzInfoPg
        elif bg_id=='SH':
            sql = ClassSqls.TzInfoSh
        else:
            # 都不成立那么就是转移单（'ZY'）
            if  mo_no:
                sql = ClassSqls.TzInfoZy
            else:
                sql = ClassSqls.TzInfoZy1
        # 判断单号是否多个
        ismulti=';' in mo_no
        # 用于存储结案掉的单据
        error_mo = []
        # 用户存储结果集
        result = []
        # print(sql)
        if ismulti:
            mo_no=list(set(mo_no.split(';')))
            for i in mo_no:
                if bg_id=='JS':
                    row_result = executeQuery(sql.format(zc_no))
                if bg_id=='ZY':
                    row_result = executeQuery(sql.format(zc_no, i,zc_no_dn))
                else:
                    row_result = executeQuery(sql.format(zc_no, i))
                if len(row_result)==0:
                    error_mo.append(i)
                else:
                    for row in row_result:
                        result.append(row)
        else:
            if bg_id == 'JS':
                result = executeQuery(sql.format(zc_no))
            elif bg_id == 'ZY':
                if mo_no:
                    result = executeQuery(sql.format(zc_no, mo_no, zc_no_dn))
                else:
                    result = executeQuery(sql.format(zc_no, zc_no_dn))
            else:
                result = executeQuery(sql.format(zc_no,mo_no))
            if len(result) == 0 or result is None:
                error_mo.append(mo_no)
        # 如果没有查询到单据直接返回
        if len(result) == 0:
            data = {
                "msg":"没有到单据",
                "error_mo": error_mo
            }
            return Response(data, status=status.HTTP_200_OK)
        # 拼接数据的返回项次
        for itm in range(len(result)):
            result[itm]['itm']=itm+1
        # print(result)
        data = {
            "msg":"过滤成功",
            "result":result,
            "error_mo":error_mo
        }
        return Response(data, status=status.HTTP_200_OK)
# 接收单
class Bg(APIView):
    # 1,设置局部认证
    authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication,)
    # authentication_classes = [SessionAuthentication, BasicAuthentication]
    # 2,设置局部权限
    permission_classes = (IsAuthenticated,)
    def get(self,request):
        params = request.query_params
        # 如果单号存在那么就是查询单据，如果单号不存在就是弹窗查询
        # 如果单号存在则按单号进行查询数据
        filter_no = params.get('filter_no','')
        if filter_no:
            # 查询表头信息
            mf_sql = ClassSqls.CommonMf_sql.format(filter_no)
            mf_result = executeQuery(mf_sql)
            if len(mf_result)>1 or len(mf_result)==0:
                return Response('查询表头数据异常',status=status.HTTP_206_PARTIAL_CONTENT)
            # 查询表身信息
            tf_sql = ClassSqls.CommonTf_sql.format(filter_no)
            tf_result = executeQuery(tf_sql)
            if len(tf_result) == 0:
                return Response('查询表表身数据异常', status=status.HTTP_206_PARTIAL_CONTENT)
            # 计算应生产量
            for i in range(len(tf_result)):
                yscl_sql = ClassSqls.BgYscl.format(tf_result[i].get('zt_no'))
                yscl_qty = executeQuery(yscl_sql)[0]
                if tf_result[i]['zt_no'] == yscl_qty.get('tz_no'):
                    print(print(yscl_qty.get('qty')))
                    print(tf_result[i]['qty'])
                    tf_result[i]['yscl'] = yscl_qty.get('qty')
                    tf_result[i]['maxqty'] = yscl_qty.get('qty')+tf_result[i]['qty']
                    tf_result[i]['totalqty'] = yscl_qty.get('totalqty')
            # 返回单据查询成功的数据
            # print(tf_result)
            data = {
                "mf_result":mf_result[0],
                "tf_result":tf_result,
                "msg":"查询成功"
            }
            return Response(data,status=status.HTTP_200_OK)
        to_date = params.get('to_date')#开始日期
        end_date = params.get('end_date')  #结束日期
        bg_id = params.get('bg_id')   #单据类别
        select_zc_no = params.get('select_zc_no')   #制程代号
        # print(to_date)
        # print(end_date)
        condition = 'where left(Convert(varchar(100), no_dd, 23), 11)>=' + "'" + to_date + "'" + ' and left(Convert(varchar(100), no_dd, 23), 11)<=' + "'" + end_date + "'" + ' and bg_id=' + "'" + bg_id + "'"
        # 获取账号等级
        is_superuser = request.user.is_superuser
        # 获取登录用户
        users = request.user
        if is_superuser==False:
            condition = condition +" and create_user="+"'"+str(users)+"'"
        if select_zc_no:
            condition = condition + ' and zc_no='+ "'"+select_zc_no+"'"
        sql1 = ClassSqls.CommonFilter.format(condition)
        result = executeQuery(sql1)
        if len(result)<=0:
            data = {
                "result": result,
                "msg": "没有查询到数据"
            }
            return Response(data, status=status.HTTP_200_OK)
        data = {
            "result":result,
            "msg":"查询成功"
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def post(self,request):
        # 1、获取参数
        data = request.data
        # 单据日期
        no_dd = data['no_dd']
        # 制程代号
        zc_no = data['zc_no']
        if zc_no is None:
            return Response("制程代码不能为空", status=status.HTTP_200_OK)
        # 创建日期
        create_user = data['create_user']
        # 创建用户
        username = data['username']
        #单据类别
        bg_id = data['bg_id']
        # 作业人员
        sal_no = data['sal_no']
        # 制令单号
        mo_no=data['mo_no']
        # 表身数据
        dataList = data['dataList']
        # 2、处理生成单号
        # 每次新增之前先把通知单没有自定栏位的单号插入到自定义表
        insert_sql = ClassSqls.CommonSql_z
        IseUpDelQuery(insert_sql)
        sql1 = ClassSqls.CommonSqlNo.format(bg_id)
        bg_no = bg_id+monthly_odd(sql1)
        # 判断单号是否重复
        ifbg_no_sql = ClassSqls.CommonIfoold.format(bg_no,bg_id)
        ifbg_no_qty = rpoold(ifbg_no_sql)
        if int(ifbg_no_qty)>0:
            data = {
                "msg": "单号重复请重新做单",
            }
            return Response(data, status=status.HTTP_200_OK)
        sid = transaction.savepoint()  # 开启事物
        # 3、插入表头数据
        mf_bg = Django_mf_bg(bg_no=bg_no,no_dd=no_dd,bg_id=bg_id,zc_no=zc_no,sal_no=sal_no,create_user=username,mo_no=mo_no,create_time=datetime.datetime.now())
        mf_bg.save()
        # 4、插入表身数据
        # 查询外键
        fordata = Django_mf_bg.objects.get(bg_no=bg_no)
        for i in dataList:
            # 判断是否超
            if i['qty']>i['yscl']:
                data = {
                    "msg": "新增失败，存在超出数量项次",
                }
                return Response(data, status=status.HTTP_200_OK)
            # filter(i['cc']),此方法是用来格式化数据的，如果数据为none则返回空
            prd_name = filter(i['prd_name'])
            cc = filter(i['cc'])
            zy = filter(i['zy'])
            zl = filter(i['zl'])
            ms = filter(i['ms'])
            rem = filter(i['rem'])
            dd = filter(i['dd'])
            prd_rem = filter(i['prd_rem'])
            zc_no_up = filter(i['zc_no_up'])
            zc_no_dn = filter(i['zc_no_dn'])
            spc_no = i['spc_no']
            tf_bg = Django_tf_bg(bg_no=fordata,no_dd=no_dd,itm=i['itm'],bg_id=bg_id,mo_no=i['mo_no'],zt_no=i['zt_no'],prd_no=i['prd_no'],
            prd_name=prd_name,cc=cc,zy=zy,qty=i['qty'],zl=zl,ms=ms,rem=rem,dd=dd,prd_rem=prd_rem,zc_no_end=zc_no_dn,zc_no_up=zc_no_up)
            tf_bg.save()
            # 如果spc_no为1的时候则跳过派工和收货
            if str(spc_no) == '1':
                update_sql = ClassSqls.BgUpSql1.format(i['zt_no'], bg_id)
            else:
                update_sql = ClassSqls.BgUpSql.format(i['zt_no'], bg_id)
            IseUpDelQuery(update_sql)
        transaction.savepoint_commit(sid)  # 提交事物
        # 5、返回响应
        data = {
            "msg":"新增成功",
            "bg_no":bg_no
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def put(self,request):
        # 1、获取参数
        data = request.data
        bg_id = data.get('bg_id')
        bg_no = data.get('bg_no')
        # print(data)
        # 2、修改表头
        sid = transaction.savepoint()  # 开启事物
        mf_bg = Django_mf_bg.objects.get(bg_id=bg_id,bg_no=bg_no)
        mf_bg.no_dd = data.get('no_dd')
        mf_bg.sal_no = data.get('sal_no')
        mf_bg.mo_no = data.get('mo_no')
        mf_bg.save()
        # 3、修改表身
        for i in request.data.get('dataList'):
            # print(i)
            # 判断是否超
            if i['qty'] > i['maxqty']:
                data = {
                    "msg": "修改失败，存在超出数量项次",
                }
                return Response(data, status=status.HTTP_200_OK)
            mf_bg = Django_tf_bg.objects.get(bg_id = bg_id,bg_no_id = bg_no,itm = i.get('itm'))
            mf_bg.qty = i.get('qty')
            mf_bg.zl = i.get('zl')
            mf_bg.save()
            if str(i.get('spc_no'))=='1':
                update_sql = ClassSqls.BgUpSql1.format(i['zt_no'], bg_id)
            else:
                update_sql = ClassSqls.BgUpSql.format(i['zt_no'], bg_id)
            IseUpDelQuery(update_sql)
        transaction.savepoint_commit(sid)  # 提交事物
        data={
            "msg":"修改成功"
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def delete(self, request):
        # 1、获取参数
        data = request.data
        scope = data.get('scope')
        bg_no = data.get('bg_no')
        # 是否删除行标志
        sign = data.get('sign')
        if scope is None and bg_no is None:
            data = {
                "msg": "删除失败"
            }
            return Response(data, status=status.HTTP_304_NOT_MODIFIED)
        if bg_no and sign is None:
            # 删除表身
            sid = transaction.savepoint()  # 开启事物
            # 查询删除表身的通知单号
            sel_sql = ClassSqls.CommonTfSql.format(bg_no)
            del_zt_no = executeQuery(sel_sql)
            Django_tf_bg.objects.filter(bg_no_id=bg_no).delete()
            Django_mf_bg.objects.filter(bg_no=bg_no).delete()
            # print(del_zt_no)
            for i in del_zt_no:
                if int(i.get('spc_no'))==1:
                    update_sql = ClassSqls.BgUpdel1.format(i.get('qty'), i.get('zl'),i.get('zt_no'),i.get('qty'),i.get('qty'))
                else:
                    update_sql = ClassSqls.BgUpdel.format(i.get('qty'), i.get('zl'),i.get('zt_no'))
                IseUpDelQuery(update_sql)
            transaction.savepoint_commit(sid)  # 提交事物
        else:
            sid = transaction.savepoint()  # 开启事物
            try:
                # 删除的时候要先查询出删除数据的的数量，查询单据的数量和重量
                old_data = Django_tf_bg.objects.get(zt_no=scope.get('zt_no'), bg_id=scope.get('bg_id'),bg_no_id=scope.get('bg_no'))
                Django_tf_bg.objects.get(bg_no_id=scope.get('bg_no'), itm=scope.get('itm')).delete()
                if int(scope.get('spc_no'))==1:
                    update_sql = ClassSqls.BgUpdel1.format(old_data.qty, old_data.zl, scope.get('zt_no'),old_data.qty,old_data.qty)
                else:
                    update_sql = ClassSqls.BgUpdel.format(old_data.qty, old_data.zl, scope.get('zt_no'))
                IseUpDelQuery(update_sql)
            except:
                data = {
                    "msg": "删除成功"
                }
                return Response(data, status=status.HTTP_200_OK)
            transaction.savepoint_commit(sid)  # 提交事物
        data = {
            "msg": "删除成功"
        }
        return Response(data, status=status.HTTP_200_OK)

# 派工单
class Pg(APIView):
    # 1,设置局部认证
    authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication,)
    # authentication_classes = [SessionAuthentication, BasicAuthentication]
    # 2,设置局部权限
    permission_classes = (IsAuthenticated,)
    def get(self,request):
        params = request.query_params
        # 如果单号存在那么就是查询单据，如果单号不存在就是弹窗查询
        # 如果单号存在则按单号进行查询数据
        filter_no = params.get('filter_no','')
        if filter_no:
            # 查询表头信息
            mf_sql = ClassSqls.CommonMf_sql.format(filter_no)
            mf_result = executeQuery(mf_sql)
            if len(mf_result)>1 or len(mf_result)==0:
                return Response('查询表头数据异常',status=status.HTTP_206_PARTIAL_CONTENT)
            # 查询表身信息
            tf_sql = ClassSqls.CommonTf_sql.format(filter_no)
            tf_result = executeQuery(tf_sql)
            if len(tf_result) == 0:
                return Response('查询表表身数据异常', status=status.HTTP_206_PARTIAL_CONTENT)
            # 计算应生产量
            for i in range(len(tf_result)):
                yscl_sql = ClassSqls.PgYscl.format(tf_result[i].get('zt_no'))
                print(yscl_sql)
                yscl_qty = executeQuery(yscl_sql)[0]
                if tf_result[i]['zt_no']==yscl_qty.get('tz_no'):
                    tf_result[i]['yscl'] = yscl_qty.get('qty')
                    tf_result[i]['maxqty'] = yscl_qty.get('qty') + tf_result[i]['qty']
                    tf_result[i]['totalqty'] = yscl_qty.get('totalqty')
            # 返回单据查询成功的数据
            data = {
                "mf_result":mf_result[0],
                "tf_result":tf_result,
                "msg":"查询成功"
            }
            return Response(data,status=status.HTTP_200_OK)
        to_date = params.get('to_date')#开始日期
        end_date = params.get('end_date')  #结束日期
        bg_id = params.get('bg_id')   #单据类别
        select_zc_no = params.get('select_zc_no')   #制程代号
        condition = 'where left(Convert(varchar(100), no_dd, 23), 11)>=' + "'" + to_date + "'" + ' and left(Convert(varchar(100), no_dd, 23), 11)<=' + "'" + end_date + "'" + ' and bg_id=' + "'" + bg_id + "'"
        # 获取账号等级
        is_superuser = request.user.is_superuser
        # 获取登录用户
        users = request.user
        if is_superuser == False:
            condition = condition + " and create_user=" + "'" + str(users) + "'"
        if select_zc_no:
            condition = condition + ' and zc_no=' + "'" + select_zc_no + "'"
        sql1 = ClassSqls.CommonFilter.format(condition)
        result = executeQuery(sql1)
        if len(result) <= 0:
            data = {
                "result": result,
                "msg": "没有查询到数据"
            }
            return Response(data, status=status.HTTP_200_OK)
        data = {
            "result":result,
            "msg":"查询成功"
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def post(self,request):
        # 1、获取参数
        data = request.data
        # 单据日期
        no_dd = data['no_dd']
        # 制程代号
        zc_no = data['zc_no']
        if zc_no is None:
            return Response("制程代码不能为空", status=status.HTTP_200_OK)
        # 创建日期
        create_user = data['create_user']
        # 创建用户
        username = data['username']
        #单据类别
        bg_id = data['bg_id']
        # 作业人员
        sal_no = data['sal_no']
        # 制令单号
        mo_no=data['mo_no']
        # 表身数据
        dataList = data['dataList']
        # print(dataList)
        # 2、处理生成单号
        # 每次新增之前先把通知单没有自定栏位的单号插入到自定义表
        insert_sql = ClassSqls.CommonSql_z
        IseUpDelQuery(insert_sql)
        sql1 = ClassSqls.CommonSqlNo.format(bg_id)
        bg_no = bg_id+monthly_odd(sql1)
        # 判断单号是否重复
        ifbg_no_sql = ClassSqls.CommonIfoold.format(bg_no, bg_id)
        ifbg_no_qty = rpoold(ifbg_no_sql)
        if int(ifbg_no_qty) > 0:
            data = {
                "msg": "单号重复请重新做单",
            }
            return Response(data, status=status.HTTP_200_OK)
        # sid = transaction.savepoint()  # 开启事物
        save_id = transaction.savepoint() #
        # 3、插入表头数据
        mf_bg = Django_mf_bg(bg_no=bg_no,no_dd=no_dd,bg_id=bg_id,zc_no=zc_no,sal_no=sal_no,create_user=username,mo_no=mo_no,create_time=datetime.datetime.now())
        mf_bg.save()
        # 4、插入表身数据
        # 查询外键
        fordata = Django_mf_bg.objects.get(bg_no=bg_no)
        for i in dataList:
            # 判断是否超
            if i['qty'] > i['yscl']:
                data = {
                    "msg": "新增失败，存在超出数量项次",
                }
                return Response(data, status=status.HTTP_200_OK)
            selPg = ClassSqls.PgSelSql.format(i['zt_no'])
            selold = executeQuery(selPg)[0]
            if selold['yscl']<selold['qty_pg_lx']+i['qty']:
                transaction.savepoint_rollback(save_id)
                data = {
                        "msg": "超出或者重复扫单超出了应发量",
                    }
                return Response(data, status=status.HTTP_200_OK)
            # filter(i['cc']),此方法是用来格式化数据的，如果数据为none则返回空
            prd_name = filter(i['prd_name'])
            cc = filter(i['cc'])
            zy = filter(i['zy'])
            zl = filter(i['zl'])
            ms = filter(i['ms'])
            rem = filter(i['rem'])
            dd = filter(i['dd'])
            prd_rem = filter(i['prd_rem'])
            zc_no_up = filter(i['zc_no_up'])
            zc_no_dn = filter(i['zc_no_dn'])
            tf_bg = Django_tf_bg(bg_no=fordata, no_dd=no_dd, itm=i['itm'], bg_id=bg_id, mo_no=i['mo_no'],zt_no=i['zt_no'], prd_no=i['prd_no'],
            prd_name=prd_name, cc=cc, zy=zy, qty=i['qty'], zl=zl, ms=ms, rem=rem, dd=dd,prd_rem=prd_rem, zc_no_end=zc_no_dn, zc_no_up=zc_no_up)
            tf_bg.save()
            update_sql = ClassSqls.PgUpSql.format(i['zt_no'], bg_id)
            IseUpDelQuery(update_sql)
        transaction.savepoint_commit(save_id)  # 提交事物
        # 5、返回响应
        data = {
            "msg":"新增成功",
            "bg_no":bg_no
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def put(self,request):
        # 1、获取参数
        data = request.data
        bg_id = data.get('bg_id')
        bg_no = data.get('bg_no')
        # print(data)
        # 2、修改表头
        sid = transaction.savepoint()  # 开启事物
        mf_bg = Django_mf_bg.objects.get(bg_id=bg_id,bg_no=bg_no)
        mf_bg.no_dd = data.get('no_dd')
        mf_bg.sal_no = data.get('sal_no')
        mf_bg.mo_no = data.get('mo_no')
        mf_bg.save()
        # 3、修改表身
        for i in request.data.get('dataList'):
            print(i)
            # 判断是否超
            if i['qty'] > i['maxqty']:
                data = {
                    "msg": "新增失败，存在超出数量项次",
                }
                return Response(data, status=status.HTTP_200_OK)
            mf_bg = Django_tf_bg.objects.get(bg_id = bg_id,bg_no_id = bg_no,itm = i.get('itm'))
            mf_bg.qty = i.get('qty')
            mf_bg.zl = i.get('zl')
            mf_bg.save()
            update_sql = ClassSqls.PgUpSql.format(i['zt_no'], bg_id)
            IseUpDelQuery(update_sql)
        transaction.savepoint_commit(sid)  # 提交事物
        data={
            "msg":"修改成功"
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def delete(self, request):
        # 1、获取参数
        data = request.data
        scope = data.get('scope')
        bg_no = data.get('bg_no')
        # 是否删除行标志
        sign = data.get('sign')
        if scope is None and bg_no is None:
            data = {
                "msg": "删除失败"
            }
            return Response(data, status=status.HTTP_304_NOT_MODIFIED)
        if bg_no and sign is None:
            # 删除表身
            sid = transaction.savepoint()  # 开启事物
            # 查询删除表身的通知单号
            sel_sql = ClassSqls.CommonTfSql.format(bg_no)
            del_zt_no = executeQuery(sel_sql)
            Django_tf_bg.objects.filter(bg_no_id=bg_no).delete()
            Django_mf_bg.objects.filter(bg_no=bg_no).delete()
            # print(del_zt_no)
            for i in del_zt_no:
                update_sql = ClassSqls.PgUpdel.format(i.get('qty'), i.get('zl'), i.get('zt_no'))
                IseUpDelQuery(update_sql)
            transaction.savepoint_commit(sid)  # 提交事物
        else:
            sid = transaction.savepoint()  # 开启事物
            try:
                # 删除的时候要先查询出删除数据的的数量，查询单据的数量和重量
                old_data = Django_tf_bg.objects.get(zt_no=scope.get('zt_no'), bg_id=scope.get('bg_id'),bg_no_id=scope.get('bg_no'))
                Django_tf_bg.objects.filter(bg_no_id=scope.get('bg_no'), itm=scope.get('itm')).delete()
                update_sql = ClassSqls.PgUpdel.format(old_data.qty, old_data.zl, scope.get('zt_no'))
                IseUpDelQuery(update_sql)
            except:
                data = {
                    "msg": "删除成功"
                }
                return Response(data, status=status.HTTP_200_OK)
            transaction.savepoint_commit(sid)  # 提交事物
        data = {
            "msg": "删除成功"
        }
        return Response(data, status=status.HTTP_200_OK)

# 收货单
class Sh(APIView):
    # 1,设置局部认证
    authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication,)
    # authentication_classes = [SessionAuthentication, BasicAuthentication]
    # 2,设置局部权限
    permission_classes = (IsAuthenticated,)
    def get(self,request):
        params = request.query_params
        # 如果单号存在那么就是查询单据，如果单号不存在就是弹窗查询
        # 如果单号存在则按单号进行查询数据
        filter_no = params.get('filter_no','')
        if filter_no:
            # 查询表头信息
            mf_sql = ClassSqls.CommonMf_sql.format(filter_no)
            mf_result = executeQuery(mf_sql)
            if len(mf_result)>1 or len(mf_result)==0:
                return Response('查询表头数据异常',status=status.HTTP_206_PARTIAL_CONTENT)
            # 查询表身信息
            tf_sql = ClassSqls.CommonTf_sql.format(filter_no)
            tf_result = executeQuery(tf_sql)
            if len(tf_result) == 0:
                return Response('查询表表身数据异常', status=status.HTTP_206_PARTIAL_CONTENT)
            # 计算应生产量
            for i in range(len(tf_result)):
                yscl_sql = ClassSqls.ShYscl.format(tf_result[i].get('zt_no'))
                # print(yscl_sql)
                # print(1111)
                yscl_qty = executeQuery(yscl_sql)[0]
                if tf_result[i]['zt_no']==yscl_qty.get('tz_no'):
                    tf_result[i]['yscl'] = yscl_qty.get('qty')
                    tf_result[i]['maxqty'] = yscl_qty.get('qty') + tf_result[i]['qty']
                    tf_result[i]['totalqty'] = yscl_qty.get('totalqty')
            # 返回单据查询成功的数据
            print(tf_result)
            data = {
                "mf_result":mf_result[0],
                "tf_result":tf_result,
                "msg":"查询成功"
            }
            return Response(data,status=status.HTTP_200_OK)
        to_date = params.get('to_date')#开始日期
        end_date = params.get('end_date')  #结束日期
        bg_id = params.get('bg_id')   #单据类别
        select_zc_no = params.get('select_zc_no')   #制程代号
        condition = 'where left(Convert(varchar(100), no_dd, 23), 11)>=' + "'" + to_date + "'" + ' and left(Convert(varchar(100), no_dd, 23), 11)<=' + "'" + end_date + "'" + ' and bg_id=' + "'" + bg_id + "'"
        # 获取账号等级
        is_superuser = request.user.is_superuser
        # 获取登录用户
        users = request.user
        if is_superuser == False:
            condition = condition + " and create_user=" + "'" + str(users) + "'"
        if select_zc_no:
            condition = condition + ' and zc_no=' + "'" + select_zc_no + "'"
        sql1 = ClassSqls.CommonFilter.format(condition)
        result = executeQuery(sql1)
        data = {
            "result":result,
            "msg":"查询成功"
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def post(self,request):
        # 1、获取参数
        data = request.data
        # 单据日期
        no_dd = data['no_dd']
        # 制程代号
        zc_no = data['zc_no']
        if zc_no is None:
            return Response("制程代码不能为空", status=status.HTTP_200_OK)
        # 创建日期
        create_user = data['create_user']
        # 创建用户
        username = data['username']
        #单据类别
        bg_id = data['bg_id']
        # 作业人员
        sal_no = data['sal_no']
        # 制令单号
        mo_no=data['mo_no']
        # 表身数据
        dataList = data['dataList']
        # print(dataList)
        # 2、处理生成单号
        # 每次新增之前先把通知单没有自定栏位的单号插入到自定义表
        insert_sql = ClassSqls.CommonSql_z
        IseUpDelQuery(insert_sql)
        sql1 = ClassSqls.CommonSqlNo.format(bg_id)
        bg_no = bg_id+monthly_odd(sql1)
        # 判断单号是否重复
        ifbg_no_sql = ClassSqls.CommonIfoold.format(bg_no, bg_id)
        ifbg_no_qty = rpoold(ifbg_no_sql)
        if int(ifbg_no_qty) > 0:
            data = {
                "msg": "单号重复请重新做单",
            }
            return Response(data, status=status.HTTP_200_OK)
        sid = transaction.savepoint()  # 开启事物
        # 3、插入表头数据
        mf_bg = Django_mf_bg(bg_no=bg_no,no_dd=no_dd,bg_id=bg_id,zc_no=zc_no,sal_no=sal_no,create_user=username,mo_no=mo_no,create_time=datetime.datetime.now())
        mf_bg.save()
        # 4、插入表身数据
        # 查询外键
        fordata = Django_mf_bg.objects.get(bg_no=bg_no)
        for i in dataList:
            # 判断是否超
            if i['qty'] > i['yscl']:
                data = {
                    "msg": "新增失败，存在超出数量项次",
                }
                return Response(data, status=status.HTTP_200_OK)
            # filter(i['cc']),此方法是用来格式化数据的，如果数据为none则返回空
            prd_name = filter(i['prd_name'])
            cc = filter(i['cc'])
            zy = filter(i['zy'])
            zl = filter(i['zl'])
            ms = filter(i['ms'])
            rem = filter(i['rem'])
            dd = filter(i['dd'])
            prd_rem = filter(i['prd_rem'])
            zc_no_up = filter(i['zc_no_up'])
            zc_no_dn = filter(i['zc_no_dn'])
            sal_no = filter(i['sal_no'])
            print(sal_no)
            tf_bg = Django_tf_bg(bg_no=fordata, no_dd=no_dd, itm=i['itm'], bg_id=bg_id, mo_no=i['mo_no'],zt_no=i['zt_no'], prd_no=i['prd_no'],
            prd_name=prd_name, cc=cc, zy=zy, qty=i['qty'], zl=zl, ms=ms, rem=rem, dd=dd,prd_rem=prd_rem, zc_no_end=zc_no_dn, zc_no_up=zc_no_up,sal_no=sal_no)
            tf_bg.save()
            update_sql = ClassSqls.ShUpSql.format(i['zt_no'], bg_id)
            IseUpDelQuery(update_sql)
            #     print(update_sql)
            #     IseUpDelQuery(update_sql)
            # # 根据单据类别的不同更新通知单数据
            # if bg_id == 'JS':
            #     update_sql = """update a set a.qty_js_lx=b.qty,a.qty1_js_lx=b.zl from MF_TZ_Z a,
            #     (select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl from Django_tf_bg where zt_no='{0}' and bg_id='{1}')
            #     b WHERE a.tz_no=b.zt_no""".format(i['zt_no'],bg_id)
            #     print(update_sql)
            #     IseUpDelQuery(update_sql)
            # if bg_id == 'PG':
            #     update_sql = """update a set a.qty_pg_lx=b.qty,a.qty1_pg_lx=b.zl from MF_TZ_Z a,
            #     (select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl from Django_tf_bg where zt_no='{0}' and bg_id='{1}')
            #     b WHERE a.tz_no=b.zt_no""".format(i['zt_no'],bg_id)
            #     IseUpDelQuery(update_sql)
            # if bg_id == 'SH':
            #     update_sql = """update a set a.qty_sh_lx=b.qty,a.qty1_sh_lx=b.zl from MF_TZ_Z a,
            #     (select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl from Django_tf_bg where zt_no='{0}' and bg_id='{1}')
            #     b WHERE a.tz_no=b.zt_no""".format(i['zt_no'], bg_id)
            #     IseUpDelQuery(update_sql)
            # if bg_id == 'ZY':
            #     update_sql = """update a set a.qty_zy_lx=b.qty,a.qty1_zy_lx=b.zl from MF_TZ_Z a,
            #     (select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl from Django_tf_bg where zt_no='{0}' and bg_id='{1}')
            #     b WHERE a.tz_no=b.zt_no""".format(i['zt_no'], bg_id)
            #     IseUpDelQuery(update_sql)
        transaction.savepoint_commit(sid)  # 提交事物
        # 5、返回响应
        data = {
            "msg":"新增成功",
            "bg_no":bg_no
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def put(self,request):
        # 1、获取参数
        data = request.data
        bg_id = data.get('bg_id')
        bg_no = data.get('bg_no')
        # print(data)
        # 2、修改表头
        sid = transaction.savepoint()  # 开启事物
        mf_bg = Django_mf_bg.objects.get(bg_id=bg_id,bg_no=bg_no)
        mf_bg.no_dd = data.get('no_dd')
        mf_bg.sal_no = data.get('sal_no')
        mf_bg.mo_no = data.get('mo_no')
        mf_bg.save()
        # 3、修改表身
        for i in request.data.get('dataList'):
            # print(i)
            # 判断是否超
            if i['qty'] > i['maxqty']:
                data = {
                    "msg": "新增失败，存在超出数量项次",
                }
                return Response(data, status=status.HTTP_200_OK)
            mf_bg = Django_tf_bg.objects.get(bg_id = bg_id,bg_no_id = bg_no,itm = i.get('itm'))
            mf_bg.qty = i.get('qty')
            mf_bg.zl = i.get('zl')
            mf_bg.save()
            update_sql = ClassSqls.ShUpSql.format(i['zt_no'], bg_id)
            IseUpDelQuery(update_sql)
        transaction.savepoint_commit(sid)  # 提交事物
        data={
            "msg":"修改成功"
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def delete(self, request):
        # 1、获取参数
        data = request.data
        scope = data.get('scope')
        bg_no = data.get('bg_no')
        # 是否删除行标志
        sign = data.get('sign')
        if scope is None and bg_no is None:
            data = {
                "msg": "删除失败"
            }
            return Response(data, status=status.HTTP_304_NOT_MODIFIED)
        if bg_no and sign is None:
            print(1111)
            # 删除表身
            sid = transaction.savepoint()  # 开启事物
            # 查询删除表身的通知单号
            sel_sql = ClassSqls.CommonTfSql.format(bg_no)
            del_zt_no = executeQuery(sel_sql)
            Django_tf_bg.objects.filter(bg_no_id=bg_no).delete()
            Django_mf_bg.objects.filter(bg_no=bg_no).delete()
            # print(del_zt_no)
            for i in del_zt_no:
                update_sql = ClassSqls.ShUpdel.format(i.get('qty'), i.get('zl'), i.get('zt_no'))
                IseUpDelQuery(update_sql)
            transaction.savepoint_commit(sid)  # 提交事物
        else:
            sid = transaction.savepoint()  # 开启事物
            try:
                # 删除的时候要先查询出删除数据的的数量，查询单据的数量和重量
                old_data = Django_tf_bg.objects.get(zt_no=scope.get('zt_no'), bg_id=scope.get('bg_id'),bg_no_id=scope.get('bg_no'))
                Django_tf_bg.objects.filter(bg_no_id=scope.get('bg_no'), itm=scope.get('itm')).delete()
                update_sql = ClassSqls.ShUpdel.format(old_data.qty, old_data.zl, scope.get('zt_no'))
                IseUpDelQuery(update_sql)
            except:
                data = {
                    "msg": "删除成功"
                }
                return Response(data, status=status.HTTP_200_OK)
            transaction.savepoint_commit(sid)  # 提交事物
        data = {
            "msg": "删除成功"
        }
        return Response(data, status=status.HTTP_200_OK)
# 转移单
class Zy(APIView):
    # 1,设置局部认证
    authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication,)
    # authentication_classes = [SessionAuthentication, BasicAuthentication]
    # 2,设置局部权限
    permission_classes = (IsAuthenticated,)
    def get(self,request):
        params = request.query_params
        # 如果单号存在那么就是查询单据，如果单号不存在就是弹窗查询
        # 如果单号存在则按单号进行查询数据
        filter_no = params.get('filter_no','')
        if filter_no:
            # 查询表头信息
            mf_sql = ClassSqls.CommonMf_sql.format(filter_no)
            mf_result = executeQuery(mf_sql)
            if len(mf_result)>1 or len(mf_result)==0:
                return Response('查询表头数据异常',status=status.HTTP_206_PARTIAL_CONTENT)
            # 查询表身信息
            tf_sql = ClassSqls.CommonTf_sql.format(filter_no)
            tf_result = executeQuery(tf_sql)
            if len(tf_result) == 0:
                return Response('查询表表身数据异常', status=status.HTTP_206_PARTIAL_CONTENT)
            # 计算应生产量
            for i in range(len(tf_result)):
                yscl_sql = ClassSqls.ZyYscl.format(tf_result[i].get('zt_no'))
                # print(yscl_sql)
                # print(1111)
                yscl_qty = executeQuery(yscl_sql)[0]
                if tf_result[i]['zt_no']==yscl_qty.get('tz_no'):
                    tf_result[i]['yscl'] = yscl_qty.get('qty')
                    tf_result[i]['maxqty'] = yscl_qty.get('qty') + tf_result[i]['qty']
                    tf_result[i]['totalqty'] = yscl_qty.get('totalqty')
            # 返回单据查询成功的数据
            data = {
                "mf_result":mf_result[0],
                "tf_result":tf_result,
                "msg":"查询成功"
            }
            return Response(data,status=status.HTTP_200_OK)
        to_date = params.get('to_date')#开始日期
        end_date = params.get('end_date')  #结束日期
        bg_id = params.get('bg_id')   #单据类别
        select_zc_no = params.get('select_zc_no')   #制程代号
        condition = 'where left(Convert(varchar(100), no_dd, 23), 11)>=' + "'" + to_date + "'" + ' and left(Convert(varchar(100), no_dd, 23), 11)<=' + "'" + end_date + "'" + ' and bg_id=' + "'" + bg_id + "'"
        # 获取账号等级
        is_superuser = request.user.is_superuser
        # 获取登录用户
        users = request.user
        if is_superuser == False:
            condition = condition + " and create_user=" + "'" + str(users) + "'"
        if select_zc_no:
            condition = condition + ' and zc_no=' + "'" + select_zc_no + "'"
        sql1 = ClassSqls.CommonFilter.format(condition)
        result = executeQuery(sql1)
        data = {
            "result":result,
            "msg":"查询成功"
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def post(self,request):
        # 1、获取参数
        data = request.data
        # 单据日期
        no_dd = data['no_dd']
        # 制程代号
        zc_no = data['zc_no']
        # 指定的制程代号
        zd_zc = data['zd_zc']
        if zc_no is None:
            return Response("制程代码不能为空", status=status.HTTP_200_OK)
        # 创建日期
        create_user = data['create_user']
        # 创建用户
        username = data['username']
        #单据类别
        bg_id = data['bg_id']
        # 作业人员
        sal_no = data['sal_no']
        # 制令单号
        mo_no=data['mo_no']
        # 是否异常单
        isbad = data['isbad']
        # 表身数据
        dataList = data['dataList']
        # print(dataList)
        # 2、处理生成单号
        # 每次新增之前先把通知单没有自定栏位的单号插入到自定义表
        insert_sql = ClassSqls.CommonSql_z
        IseUpDelQuery(insert_sql)
        sql1 = ClassSqls.CommonSqlNo.format(bg_id)
        bg_no = bg_id+monthly_odd(sql1)
        # 判断单号是否重复
        ifbg_no_sql = ClassSqls.CommonIfoold.format(bg_no, bg_id)
        ifbg_no_qty = rpoold(ifbg_no_sql)
        if int(ifbg_no_qty) > 0:
            data = {
                "msg": "单号重复请重新做单",
            }
            return Response(data, status=status.HTTP_200_OK)
        sid = transaction.savepoint()  # 开启事物
        # 3、插入表头数据
        mf_bg = Django_mf_bg(bg_no=bg_no,no_dd=no_dd,bg_id=bg_id,zc_no=zc_no,sal_no=sal_no,isbad=isbad,create_user=username,mo_no=mo_no,zd_zc=zd_zc,create_time=datetime.datetime.now())
        mf_bg.save()
        # 4、插入表身数据
        # 查询外键
        fordata = Django_mf_bg.objects.get(bg_no=bg_no)
        for i in dataList:
            # 判断是否超
            if i['qty'] > i['yscl']:
                data = {
                    "msg": "新增失败，存在超出数量项次",
                }
                return Response(data, status=status.HTTP_200_OK)
            # filter(i['cc']),此方法是用来格式化数据的，如果数据为none则返回空
            prd_name = filter(i['prd_name'])
            cc = filter(i['cc'])
            zy = filter(i['zy'])
            zl = filter(i['zl'])
            ms = filter(i['ms'])
            rem = filter(i['rem'])
            dd = filter(i['dd'])
            prd_rem = filter(i['prd_rem'])
            zc_no_up = filter(i['zc_no_up'])
            zc_no_dn = filter(i['zc_no_dn'])
            tf_bg = Django_tf_bg(bg_no=fordata, no_dd=no_dd, itm=i['itm'], bg_id=bg_id, mo_no=i['mo_no'],zt_no=i['zt_no'], prd_no=i['prd_no'],
             prd_name=prd_name, cc=cc, zy=zy, qty=i['qty'], zl=zl, ms=ms, rem=rem, dd=dd,prd_rem=prd_rem, zc_no_end=zc_no_dn, zc_no_up=zc_no_up,zd_zc=zd_zc)
            tf_bg.save()
            # 更新通知单自定义栏位
            #     0为正常单据 ，1为异常单据
            if isbad==0:
                update_sql = ClassSqls.ZyUpSql.format(i['zt_no'], bg_id)
                # 更新下制程的带接收数量
                update_sql_dai = ClassSqls.ZyAddSql_dai.format(i['mo_no'], bg_id,i['zd_zc'], bg_no)
                IseUpDelQuery(update_sql_dai)
            else:
                update_sql = ClassSqls.ZyUpSql1.format(i['zt_no'], bg_id)
            IseUpDelQuery(update_sql)
            # 更新通知单生产数量以及结案标识
            uptz(i['zt_no'])
        transaction.savepoint_commit(sid)  # 提交事物
        # 5、返回响应
        data = {
            "msg":"新增成功",
            "bg_no":bg_no
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def put(self,request):
        # 1、获取参数
        data = request.data
        bg_id = data.get('bg_id')
        bg_no = data.get('bg_no')
        isbad = data.get('isbad')
        zd_zc = data.get('zd_zc')
        # print(data)
        # 2、修改表头
        sid = transaction.savepoint()  # 开启事物
        mf_bg = Django_mf_bg.objects.get(bg_id=bg_id,bg_no=bg_no)
        mf_bg.no_dd = data.get('no_dd')
        mf_bg.sal_no = data.get('sal_no')
        mf_bg.mo_no = data.get('mo_no')
        mf_bg.zd_zc = data.get('zd_zc')
        mf_bg.save()
        # 3、修改表身
        for i in request.data.get('dataList'):
            # print(i)
            # 判断是否超
            if i['qty'] > i['maxqty']:
                data = {
                    "msg": "新增失败，存在超出数量项次",
                }
                return Response(data, status=status.HTTP_200_OK)
            # 更新通知单生产数量以及结案标识
            uptz(i.get('zt_no'))
            mf_bg = Django_tf_bg.objects.get(bg_id = bg_id,bg_no_id = bg_no,itm = i.get('itm'))
            mf_bg.qty = i.get('qty')
            mf_bg.zl = i.get('zl')
            old_zd_zc=mf_bg.zd_zc
            mf_bg.zd_zc = zd_zc
            mf_bg.save()
            # 更新通知单自定义栏位
            #     0为正常单据 ，1为异常单据
            if isbad == 0:
                print(old_zd_zc)
                update_sql = ClassSqls.ZyUpSql.format(i['zt_no'], bg_id)
                # 更新掉旧的待接收数量
                update_old_dai_sql = ClassSqls.update_old_dai_sql.format(i.get('qty'), i.get('zl'), mf_bg.mo_no,old_zd_zc)
                IseUpDelQuery(update_old_dai_sql)
                # 查询更新新的指定制程带接收量
                update_sql_dai = ClassSqls.ZyUpSql_dai.format(i['mo_no'], bg_id, zd_zc)
                IseUpDelQuery(update_sql_dai)
            else:
                update_sql = ClassSqls.ZyUpSql1.format(i['zt_no'], bg_id)
            IseUpDelQuery(update_sql)
        transaction.savepoint_commit(sid)  # 提交事物
        data={
            "msg":"修改成功"
        }
        return Response(data,status=status.HTTP_200_OK)
    @transaction.atomic
    def delete(self, request):
        # 1、获取参数
        data = request.data
        scope = data.get('scope')
        bg_no = data.get('bg_no')
        isbad = data.get('isbad')
        # 是否删除行标志
        sign = data.get('sign')
        if scope is None and bg_no is None:
            data = {
                "msg": "删除失败"
            }
            return Response(data, status=status.HTTP_304_NOT_MODIFIED)
        # 删除整单
        if bg_no and sign is None:
            # 删除表身
            sid = transaction.savepoint()  # 开启事物
            # 查询删除表身的通知单号
            sel_sql = ClassSqls.CommonTfSql.format(bg_no)
            del_zt_no = executeQuery(sel_sql)
            Django_tf_bg.objects.filter(bg_no_id=bg_no).delete()
            Django_mf_bg.objects.filter(bg_no=bg_no).delete()
            # print(del_zt_no)
            for i in del_zt_no:
                if isbad==0:
                    # 更新掉旧的待接收数量
                    update_old_dai_sql = ClassSqls.update_old_dai_sql.format(i.get('qty'), i.get('zl'), i.get('mo_no'),i.get('zd_zc'))
                    IseUpDelQuery(update_old_dai_sql)
                    update_sql = ClassSqls.ZyUpdel.format(i.get('qty'), i.get('zl'), i.get('zt_no'))
                else:
                    update_sql = ClassSqls.ZyUpdel1.format(i.get('qty'), i.get('zl'), i.get('zt_no'))
                IseUpDelQuery(update_sql)
                # 更新结案标志
                uptz(i.get('zt_no'))
            transaction.savepoint_commit(sid)  # 提交事物
        else:
            # 删除表身项次
            sid = transaction.savepoint()  # 开启事物
            try:
                # 删除的时候要先查询出删除数据的的数量，查询单据的数量和重量
                old_data = Django_tf_bg.objects.get(zt_no=scope.get('zt_no'), bg_id=scope.get('bg_id'),bg_no_id=scope.get('bg_no'))
                Django_tf_bg.objects.filter(bg_no_id=scope.get('bg_no'), itm=scope.get('itm')).delete()
                # 正常单据删除
                if isbad==0:
                    # 更新掉旧的待接收数量
                    update_old_dai_sql = ClassSqls.update_old_dai_sql.format(i.get('qty'), i.get('zl'), i.get('mo_no'),i.get('zd_zc'))
                    IseUpDelQuery(update_old_dai_sql)
                    update_sql = ClassSqls.ZyUpdel.format(old_data.qty, old_data.zl, scope.get('zt_no'))
                else:
                    update_sql = ClassSqls.ZyUpdel1.format(old_data.qty, old_data.zl, scope.get('zt_no'))
                # 异常单据删除
                IseUpDelQuery(update_sql)
                # 更新结案标志
                uptz(scope.get('zt_no'))
            except:
                data = {
                    "msg": "删除成功"
                }
                return Response(data, status=status.HTTP_200_OK)
            transaction.savepoint_commit(sid)  # 提交事物
        data = {
            "msg": "删除成功"
        }
        return Response(data, status=status.HTTP_200_OK)