from django.db import transaction from django.shortcuts import render import datetime # Create your views here. # 查询制程信息 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 from utils.filters import filter from utils.ClssSql import ClassSqls from .models import Django_mf_bg,Django_tf_bg from .bgutils import uptz # 过滤通知单信息 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') zc_no = params.get('zc_no') bg_id = params.get('bg_id') zc_no_dn = params.get('zc_no_dn') 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'） sql = ClassSqls.TzInfoZy # 判断单号是否多个 ismulti=';' in mo_no # 用于存储结案掉的单据 error_mo = [] # 用户存储结果集 result = [] 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)) elif 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: 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'): 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+"'" if select_zc_no: condition = condition + ' and zc_no='+ "'"+select_zc_no+"'" sql1 = ClassSqls.CommonFilter.format(condition) print(sql1) 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 print(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) print(bg_no) 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']) 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.BgUpSql.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(bg_id) # 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() print(data.get('dataList')) # 3、修改表身 for i in request.data.get('dataList'): print(i['maxqty']) # 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.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: 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.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() 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) # 计算应生产量 print(1111111111) for i in range(len(tf_result)): print(2222) 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 + "'" 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) 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']) 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) # 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.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() # for i in del_zt_no: # update_sql = ClassSqls.PgUpSql.format(i['zt_no'], i['bg_id']) # IseUpDelQuery(update_sql) # transaction.savepoint_commit(sid) # 提交事物 # else: # Django_tf_bg.objects.filter(bg_no_id=scope.get('bg_no_id'), itm=scope.get('itm')).delete() # update_sql = ClassSqls.PgUpSql.format(scope.get('zt_no'), scope.get('bg_id')) # IseUpDelQuery(update_sql) # 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.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 + "'" 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) 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: # # 删除表身 # 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() # for i in del_zt_no: # update_sql = ClassSqls.ShUpSql.format(i['zt_no'], i['bg_id']) # IseUpDelQuery(update_sql) # transaction.savepoint_commit(sid) # 提交事物 # else: # Django_tf_bg.objects.filter(bg_no_id=scope.get('bg_no_id'), itm=scope.get('itm')).delete() # update_sql = ClassSqls.ShUpSql.format(scope.get('zt_no'), scope.get('bg_id')) # IseUpDelQuery(update_sql) # 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 + "'" 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) 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']) 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.ZyUpSql.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') # 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) # 更新通知单生产数量以及结案标识 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') mf_bg.save() update_sql = ClassSqls.ZyUpSql.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() # for i in del_zt_no: # update_sql = ClassSqls.ZyUpSql.format(i['zt_no'], i['bg_id']) # IseUpDelQuery(update_sql) # transaction.savepoint_commit(sid) # 提交事物 # else: # Django_tf_bg.objects.filter(bg_no_id=scope.get('bg_no_id'), itm=scope.get('itm')).delete() # update_sql = ClassSqls.ZyUpSql.format(scope.get('zt_no'), scope.get('bg_id')) # IseUpDelQuery(update_sql) # 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.ZyUpdel.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() update_sql = ClassSqls.ZyUpdel.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)