1

zhangrg

2023-12-22 8abe6c1a1609513b72c10c7af08c7ca2400200f0

 WebAPI/Controllers/CJGL/Cj_SingleStationController.cs

@@ -204,30 +204,34 @@

                ds = oCN.RunProcReturn("exec h_p_Gy_BarCodeBillBomList '" + mainList[0].HProcExchBillNo + "'," + mainList[0].HProcID, "h_p_Gy_BarCodeBillBomList");

                string jsonstr = JsonConvert.SerializeObject(ds.Tables[0]);
                if (ds.Tables[0].Rows.Count > 0) {

                //保存生产组装单主表
                string sql = $@"Insert Into Sc_AssemblyBillMain(HYear,HPeriod,HBillType,HBillSubType,HInterID,HDate
                    string jsonstr = JsonConvert.SerializeObject(ds.Tables[0]);

                    //保存生产组装单主表
                    string sql = $@"Insert Into Sc_AssemblyBillMain(HYear,HPeriod,HBillType,HBillSubType,HInterID,HDate
,HBillNo,HBillStatus,HMaker,HMakeDate,HMainSourceInterID,HMainSourceEntryID,HMainSourceBillNo
,HICMOInterID,HICMOBillNo,HBarCode_P,HMaterID,HUnitID,HAssemblyStatus,HProdOrgID)
values('{DateTime.Now.Year}','{DateTime.Now.Month}','3727','3727',{HInterID1},getdate()
,'{HBillNo1}','1','{user}',getdate(),{mainList[0].HProcExchInterID.ToString()},{mainList[0].HProcExchEntryID.ToString()},'{mainList[0].HProcExchBillNo.ToString()}'
,{mainList[0].HICMOInterID.ToString()},'{mainList[0].HICMOBillNo.ToString()}','{mainList[0].HBarCode.ToString()}',{mainList[0].HMaterID},0,'汇报',{mainList[0].HProdOrgID})";
                oCN.RunProc(sql);
                    oCN.RunProc(sql);

                List<Model.ClsSc_AssemblyBillSub> sub = new List<Model.ClsSc_AssemblyBillSub>();
                sub = JsonConvert.DeserializeObject<List<Model.ClsSc_AssemblyBillSub>>(jsonstr);
                    List<Model.ClsSc_AssemblyBillSub> sub = new List<Model.ClsSc_AssemblyBillSub>();
                    sub = JsonConvert.DeserializeObject<List<Model.ClsSc_AssemblyBillSub>>(jsonstr);

                for (int i = 0; i < sub.Count; i++)
                {
                    //子表存储
                    string sq2 = $@"Insert Into Sc_AssemblyBillSub(HInterID,HBillNo_bak,HEntryID,HSourceInterID,HSourceEntryID,HSourceBillNo
                    for (int i = 0; i < sub.Count; i++)
                    {
                        //子表存储
                        string sq2 = $@"Insert Into Sc_AssemblyBillSub(HInterID,HBillNo_bak,HEntryID,HSourceInterID,HSourceEntryID,HSourceBillNo
                ,HSourceBillType,HMaterID,HSourceID,HEquipID,HUnitID,HQty
                ,HGroupID,HWorkerID,HScanDate,HBarCode,HBarCode_P,HSNNumber)
                values({HInterID1},'{HBillNo1}',{(i + 1)},0,0,''
                ,'',{sub[i].HMaterID},0,0,0,{sub[i].HQty}
                ,0,0,getdate(),'{sub[i].HBarCode}','{mainList[0].HBarCode}','') ";
                    oCN.RunProc(sq2);
                        oCN.RunProc(sq2);
                    }

                }


@@ -320,15 +324,29 @@
                }

                ds = oCN.RunProcReturn("select * from gy_czygl where czymc='" + user + "'", "gy_czygl");
                string HProcID = ds.Tables[0].Rows[0]["HProcID"].ToString();

                ds = oCN.RunProcReturn(@"exec h_p_Gy_BarCodeBillBomList '" + HBarCode + "'," + ds.Tables[0].Rows[0]["HProcID"].ToString(), "h_p_Gy_BarCodeBillBomList");
                ds = oCN.RunProcReturn(@"exec h_p_Gy_BarCodeBillBomList '" + HBarCode + "'," + HProcID, "h_p_Gy_BarCodeBillBomList");
                if (ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "配件清单查无数据！";
                    objJsonResult.data = null;
                    return objJsonResult;
                    DataSet dataSet = oCN.RunProcReturn(@"exec h_p_Sc_ProcessExchangeBillList '" + HBarCode + "'," + HProcID, "h_p_Sc_ProcessExchangeBillList");

                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        objJsonResult.code = "2";
                        objJsonResult.count = 2;
                        objJsonResult.Message = "";
                        objJsonResult.data = dataSet.Tables[0];
                        return objJsonResult;
                    }
                    else
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "配件清单查无数据！,可能是当前用户工序不正确或者流转卡输入错误!";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }
                else
                {
@@ -387,7 +405,7 @@
                if (tempList.Count == 0) {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无数据!";
                    objJsonResult.Message = "无配件数据!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
@@ -452,6 +470,7 @@
                }

                oCN.BeginTran();
                string sExeReturnInfo = "";

                ClsSc_AssemblyBillMain omodel = new ClsSc_AssemblyBillMain();
                omodel = JsonConvert.DeserializeObject<ClsSc_AssemblyBillMain>(sMainStr);
@@ -460,7 +479,8 @@
                sub = JsonConvert.DeserializeObject<List<Model.ClsSc_AssemblyBillSub>>(sSubStr);


                ds = oCN.RunProcReturn("select  * from h_v_Gy_QualifiedRecordsList where 产品条码='" + omodel.HBarCode_P + "'", "h_v_Gy_QualifiedRecordsList");
                ds = oCN.RunProcReturn("select  * from h_v_Gy_QualifiedRecordsList where 产品条码='" + omodel.HBarCode_P + "' and HProcExchInterID="+ omodel .HProcExchInterID+ " and HProcExchEntryID="+ omodel.HProcExchEntryID + " and HProcID="+ omodel.HProcID, "h_v_Gy_QualifiedRecordsList");

                if (ds.Tables[0].Rows.Count > 0)
                {
                    objJsonResult.code = "0";
@@ -488,7 +508,7 @@
,HSourceBillType,HMaterID,HSourceID,HEquipID,HUnitID,HQty
,HGroupID,HWorkerID,HScanDate,HBarCode,HBarCode_P,HSNNumber)
values({HInterID1},'{HBillNo1}',{(i + 1)},0,0,''
,'',{sub[i].HMaterID},0,0,0,{sub[i].HQty}
,'',{sub[i].HMaterID},0,0,0,1
,0,0,getdate(),'{sub[i].HBarCode}','{omodel.HBarCode_P}','') ";
                    oCN.RunProc(sq2);
                }
@@ -564,10 +584,13 @@
                double HProcPriceRate = 0;//工价系数
                int HTemporaryAreaID = 0;//暂放区

                ds = oCN.RunProcReturn("select  * from Sc_StationOutBillMain a left join  Sc_StationOutBillSub_SN sn on a.HInterID=sn.HInterID where a.HInterID=" + HInterID + " and HBillNo='" + HBillNo + "'", "Sc_StationOutBillMain");
                ds = oCN.RunProcReturn("select  * from Sc_StationOutBillMain a left join  Sc_StationOutBillSub_SN sn on a.HInterID=sn.HInterID where a.HInterID=" + HInterID + " and HBillNo='" + HBillNo + "' and CONVERT(varchar(10),HDate,20) ='" + DateTime.Now.ToString("yyyy-MM-dd") + "' ", "Sc_StationOutBillMain");

                if (ds.Tables[0].Rows.Count == 0)
                {
                    HInterID = DBUtility.ClsPub.CreateBillID("3791", ref sExeReturnInfo);//递入type得到的单据ID
                    HBillNo = DBUtility.ClsPub.CreateBillCode_Prod("3791", ref sExeReturnInfo, true);//递入type得到的单据号

                    oCN.RunProc("Insert Into Sc_StationOutBillMain " +
                    "(HBillType,HBillSubType,HInterID,HBillNo,HBillStatus,HDate,HMaker,HMakeDate,HMouldNum" +
                    ",HYear,HPeriod,HRemark,HSourceName,HPieceQty,HWasterQty,HPlanPieceQty,HBadPNL" +
@@ -595,7 +618,7 @@
                ds = oCN.RunProcReturn("select  * from Sc_StationOutBillSub_SN where HInterID='" + omodel.HInterID + "' order by HEntryID desc", "Sc_StationOutBillSub_SN");

                oCN.RunProc($@"insert into Sc_StationOutBillSub_SN(HInterID,HBillNo_bak,HEntryID,HBarCode,HBarCodeQty,HMakeTime,HRemark,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney)
values({omodel.HInterID}, '{omodel.HBillNo}', {(ds.Tables[0].Rows.Count == 0 ? 1 : int.Parse(ds.Tables[0].Rows[0]["HEntryID"].ToString()) + 1)}, '{omodel.HBarCode_P}', 1, GETDATE(), '', " + HProcExchInterID + ","+ HProcExchEntryID + ", '"+ HProcExchBillNo + "', '', 0, 0)");
values({HInterID}, '{HBillNo}', {(ds.Tables[0].Rows.Count == 0 ? 1 : int.Parse(ds.Tables[0].Rows[0]["HEntryID"].ToString()) + 1)}, '{omodel.HBarCode_P}', 1, GETDATE(), '', " + HProcExchInterID + ","+ HProcExchEntryID + ", '"+ HProcExchBillNo + "', '', 0, 0)");

                //反写工序出站单的合格数量
                oCN.RunProc("update Sc_StationOutBillMain set HQty+=1  where HProcExchInterID='" + HProcExchInterID + "' and HProcExchEntryID=" + HProcExchEntryID);
@@ -925,7 +948,7 @@
        #region  工序单品过站 查询不良记录
        [Route("Cj_SingleStation/BadRecordsList")]
        [HttpGet]
        public object BadRecordsList(string HBarCode, string user)
        public object BadRecordsList(string HProcExchInterID, string HProcExchEntryID, string user)
        {
            try
            {
@@ -939,12 +962,12 @@
                    return objJsonResult;
                }

                ds = oCN.RunProcReturn(@"select * from h_v_Gy_BadRecordsList where HProcExchBillNo='" + HBarCode + "'", "h_v_Gy_BadRecordsList");
                ds = oCN.RunProcReturn(@"exec h_p_Gy_BadRecordsList " + HProcExchInterID + "," + HProcExchEntryID, "h_p_Gy_BadRecordsList");

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.data = ds;
                return objJsonResult;
            }
            catch (Exception e)
@@ -1025,7 +1048,7 @@
                string HProcExchInterID = ds.Tables[0].Rows[0]["HProcExchInterID"].ToString();
                string HProcExchEntryID = ds.Tables[0].Rows[0]["HProcExchEntryID"].ToString();
                string HBarCode = ds.Tables[0].Rows[0]["HBarCode"].ToString();

                string HDate = ds.Tables[0].Rows[0]["日期"].ToString();

                oCN.BeginTran();

@@ -1041,7 +1064,11 @@
                oCN.RunProc(sql);

                //反写工序出站单的不良数量
                oCN.RunProc("update Sc_StationOutBillMain set HBadCount-=1  where HProcExchInterID='" + HProcExchInterID + "' and HProcExchEntryID=" + HProcExchEntryID);
                oCN.RunProc(@"update Sc_StationOutBillMain set HBadCount-=1 where HProcExchInterID='" + HProcExchInterID + "' and HProcExchEntryID='" + HProcExchEntryID + "' " +
                    "and HBillNo = (select HBillNo from Sc_StationOutBillMain " +
                    "where HProcExchInterID = '" + HProcExchInterID + "' and HProcExchEntryID = '" + HProcExchEntryID + "'  and convert(varchar(10), HDate, 20) = '" + DateTime.Parse(HDate).ToString("yyyy-MM-dd") + "' and HBadCount > 0)");
                //反写流转卡状态
                oCN.RunProc("update Gy_BarCodeBill set HStatus=''  where HBarCode='" + HBarCode + "'");


                oCN.Commit();
@@ -1165,7 +1192,15 @@
                    return objJsonResult;
                }

                if (ds.Tables[0].Rows[0]["HProcID"].ToString() != HProcID)
                int num = 0;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i]["HProcID"].ToString() == HProcID)
                    {
                        num = 1;
                    }
                }
                if (num == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
@@ -1341,25 +1376,6 @@
({model.HInterID},{(ds.Tables[0].Rows.Count == 0 ? 1 : int.Parse(ds.Tables[0].Rows[0]["HEntryID"].ToString()) + 1)},'{model.HBillNo}','',{temp.HProcExchInterID}
,{temp.HProcExchEntryID},'{temp.HProcExchBillNo}','',{temp.HMaterID},0,'{temp.HBarCode}','{temp.HBatchNo}')");


                    ds = oCN.RunProcReturn("select  * from Sc_SourceLineRepairBillSub where HInterID=" + model.HInterID, "Sc_SourceLineRepairBillSub");

                    if (ds.Tables[0].Rows[0]["HRepairResult"].ToString() == "OK") {

                        //生产工序出站SN数据
                        ds = oCN.RunProcReturn("select top 1  HInterID,HBillNo from Sc_StationOutBillMain where HProcExchInterID=" + temp.HProcExchInterID + " and HProcExchEntryID=" + temp.HProcExchEntryID + " and HBadCount<>0  order by  HInterID desc", "Sc_StationOutBillMain");

                        string HInterIDSN = ds.Tables[0].Rows[0]["HInterID"].ToString();
                        string HBillNoSN = ds.Tables[0].Rows[0]["HBillNo"].ToString();

                        ds = oCN.RunProcReturn("select *  from Sc_StationOutBillSub_SN where HInterID=" + HInterIDSN , "Sc_StationOutBillSub_SN");

                        oCN.RunProc($@"insert into Sc_StationOutBillSub_SN(HInterID,HBillNo_bak,HEntryID,HBarCode,HBarCodeQty,HMakeTime,HRemark,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney)
values({HInterIDSN}, '{HBillNoSN}', {ds.Tables[0].Rows.Count + 1}, '{model.HBarCode}', 1, GETDATE(), '', "+ temp.HProcExchInterID + ","+ temp.HProcExchEntryID+ ", '"+ temp.HProcExchBillNo+ "', '', 0, 0)");
                        //反写工序出站单 主表 不良数量 和 合格数量
                        oCN.RunProc("update Sc_StationOutBillMain set HQty+=1,HBadCount-=1  where HInterID='" + HInterIDSN + "'");
                    }

                    //修改产线追溯单绑定的条码批号
                    oCN.RunProc("update Sc_AssemblyBillSub set HBarCode='" + temp.HBarCode + "' where HInterID=" + temp.HInterID + " and HEntryID=" + temp.HEntryID);
                    //oCN.RunProc("exec h_p_AssemblyBill_Temp '" + temp.HProcExchBillNo + "'," + model.HProcess + "," + temp.HMaterID + ",'" + temp.HBatchNo + "'");
@@ -1387,6 +1403,25 @@

                    if (HResult == "OK")
                    {
//                        ds = oCN.RunProcReturn("select  * from Sc_SourceLineRepairBillSub where HInterID=" + model.HInterID, "Sc_SourceLineRepairBillSub");

//                        if (ds.Tables[0].Rows[0]["HRepairResult"].ToString() == "OK")
//                        {

//                            //生产工序出站SN数据
//                            ds = oCN.RunProcReturn("select top 1  HInterID,HBillNo from Sc_StationOutBillMain where HProcExchInterID=" + model.HProcExchInterID + " and HProcExchEntryID=" + model.HProcExchEntryID + " and HBadCount<>0  order by  HInterID desc", "Sc_StationOutBillMain");

//                            string HInterIDSN = ds.Tables[0].Rows[0]["HInterID"].ToString();
//                            string HBillNoSN = ds.Tables[0].Rows[0]["HBillNo"].ToString();

//                            ds = oCN.RunProcReturn("select *  from Sc_StationOutBillSub_SN where HInterID=" + HInterIDSN, "Sc_StationOutBillSub_SN");

//                            oCN.RunProc($@"insert into Sc_StationOutBillSub_SN(HInterID,HBillNo_bak,HEntryID,HBarCode,HBarCodeQty,HMakeTime,HRemark,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney)
//values({HInterIDSN}, '{HBillNoSN}', {ds.Tables[0].Rows.Count + 1}, '{model.HBarCode}', 1, GETDATE(), '', " + model.HProcExchInterID + "," + model.HProcExchEntryID + ", '" + model.HProcExchBillNo + "', '', 0, 0)");
//                            //反写工序出站单 主表 不良数量 和 合格数量
//                            oCN.RunProc("update Sc_StationOutBillMain set HQty+=1,HBadCount-=1  where HInterID='" + HInterIDSN + "'");
//                        }

                        oCN.RunProc("update Gy_BarCodeBill set HStatus='' where HBarCode='" + model.HBarCode + "'");
                    }
                }
@@ -1577,5 +1612,85 @@
        }
        #endregion

        #region 工序单品过站 合格追溯查询报表
        [Route("Cj_SingleStation/RetrospectiveQueryReport")]
        [HttpGet]
        public object RetrospectiveQueryReport(string HBardCode, string HProcExchBillNo, string HICMOBillNo, string sWhere, string user)
        {
            try
            {

                List<object> columnNameList = new List<object>();

                string sql = @"exec  h_p_RetrospectiveQueryReport '" + sWhere + "', '" + HBardCode + "', '" + HProcExchBillNo + "','" + HICMOBillNo + "' ";
                ds = oCN.RunProcReturn(sql, "h_p_RetrospectiveQueryReport");

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception e)
            {
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 工序单品过站 不良追溯查询报表
        [Route("Cj_SingleStation/BadQueryReport")]
        [HttpGet]
        public object BadQueryReport(string HBardCode, string HProcExchBillNo, string HICMOBillNo, string sWhere, string user)
        {
            try
            {

                List<object> columnNameList = new List<object>();

                string sql = @"exec  h_p_BadQueryReport '" + sWhere + "', '" + HBardCode + "', '" + HProcExchBillNo + "','" + HICMOBillNo + "' ";
                ds = oCN.RunProcReturn(sql, "h_p_BadQueryReport");

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception e)
            {
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

    }
}