from django.db import transaction 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'） if mo_no: sql = ClassSqls.TzInfoZy else: sql = ClassSqls.TzInfoZy1 # 判断单号是否多个 print(sql) 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)) 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'): 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 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']) 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 int(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 int(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) 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: 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 + "'" # 获取账号等级 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) 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) 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.ZyUpSql_dai.format(i['mo_no'], bg_id,i['zd_zc']) 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)