MES报废补单选源单接口

yl

2023-03-07 c715bbf91baff280e7b043adf1fa95f74e5d3481

 VueWebApi/DLL/DAL/ProductionManagementDAL.cs

@@ -385,6 +385,83 @@
        }
        #endregion

        #region[MES报废补单工单查询]
        public static ToMessage MesBadOrderSearch(string mesordercode, string sourceorder, string partcode, string partname, string partspec, int startNum, string creatuser, string createdate, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (mesordercode != "" && mesordercode != null)
                {
                    search += "and A.wo_code like '%'+@mesordercode+'%' ";
                    dynamicParams.Add("@mesordercode", mesordercode);
                }
                if (sourceorder != "" && sourceorder != null)
                {
                    search += "and A.m_po like '%'+@sourceorder+'%' ";
                    dynamicParams.Add("@sourceorder", sourceorder);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and A.materiel_code like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and B.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and B.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (createdate != "" && createdate != null)
                {
                    search += "and CONVERT(varchar(100),A.lm_date,23)=@createdate ";
                    dynamicParams.Add("@createdate", createdate);
                }
                if (creatuser != "" && creatuser != null)
                {
                    search += "and U.username like '%'+@creatuser+'%' ";
                    dynamicParams.Add("@creatuser", creatuser);
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id, A.status,A.wotype,A.wo_code,A.materiel_code as partcode,B.partname,B.partspec,A.plan_qty,A.wkshp_code,C.org_name as wkshp_name,
                            A.route_code,E.name as route_name,A.stck_code,F.name as stck_name,A.plan_startdate,A.plan_enddate,A.piroque,A.sourceid,A.m_po,U.username as lm_user,A.lm_date,S.bad_qty
                            from TK_Wrk_Man A
                            left join (select wo_code,sum(bad_qty) as bad_qty from  TK_Wrk_Step where bad_qty>0 group by wo_code) S on A.wo_code=S.wo_code
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TOrganization C on A.wkshp_code=C.org_code
                            left join T_Sec_Stck D on A.stck_code=D.code 
                            left join TFlw_Rout E on A.route_code=E.code
                            left join T_Sec_Stck F on A.stck_code=F.code 
                            left join TUser U on A.lm_user=U.usercode 
                            where A.is_delete<>'1'  and A.status='START' and A.wotype='PO' and S.bad_qty>0 " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[产品编码查找工艺路线下拉接口]
        public static ToMessage PartSelectRoute(string partcode)
        {
@@ -534,6 +611,13 @@
                            CreateDate = DateTime.Now.ToString()
                        }
                    });
                    //if (is_aps == "Y")  //是否排程
                    //{
                    //    string sql1 = "select id from TBom_Main  where materiel_code='" + PartNumber + "' and status='Y' and version='" + VsionId + "'";
                    //    DataTable dt1 = DBHelper.GetTable(sql1);
                    //}

                    //增加工单用料表（子件）  

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
@@ -631,7 +715,7 @@
            try
            {
                //判断工单是否为未开始状态或者已派发状态(满足其中一种都可删除，否则不允许删除)
                sql = @"select *  from TK_Wrk_Man where wo_code=@wocode and status='NEW' or status='ALLO'";
                sql = @"select *  from TK_Wrk_Man where wo_code=@wocode and status in('NEW','ALLO')";
                dynamicParams.Add("@wocode", wocode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
@@ -752,7 +836,7 @@
                         left join TStep B on A.step_code=B.stepcode
                         left join TK_Wrk_Man M on A.wo_code=M.wo_code
                         left join TMateriel_Info P on M.materiel_code=P.partcode
                        where A.wo_code=@wo_code";
                        where A.wo_code=@wo_code order by A.seq";
                dynamicParams.Add("@wo_code", wo_code);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
@@ -1561,6 +1645,7 @@
            string[] arra1 = new string[] { };
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            var dynamicParamsTran = new DynamicParameters();
            try
            {
                string date = DateTime.Now.ToString(); //获取系统时间
@@ -1593,114 +1678,75 @@
                    mes.data = null;
                    return mes;
                }
                
                if (data.Rows.Count > 0)
                {
                    //获取主表最大ID
                    sql = @"select ISNULL(IDENT_CURRENT('TK_Wrk_Record')+1,1) as id";
                    var dt = DapperHelper.selecttable(sql);
                    //写入开报工记录表
                    sql = @"insert into  TK_Wrk_Record(wo_code,step_seq,step_code,eqp_code,materiel_code,task_qty,start_qty,good_qty,ng_qty,style,lm_user,lm_date) 
                                values(@mesordercode,@stepseq,@stepcode,@eqpcode,@partcode,@taskqty,@startqty,@reportqty,@ngqty,@style,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, eqpcode = eqpcode, partcode = partcode, taskqty = taskqty, startqty = startqty, reportqty = reportqty, ngqty = ngqty, style = "B", lm_user = username, lm_date = date } });

                    //写入子表
                    for (int i = 0; i < arra.Length; i++)
                    //存储过程名
                    sql = @"h_p_MES_ProductionReport_ZZ";
                    dynamicParamsTran.Add("@mesordercode", mesordercode);
                    dynamicParamsTran.Add("@partcode", partcode);
                    dynamicParamsTran.Add("@stepseq", stepseq);
                    dynamicParamsTran.Add("@stepcode", stepcode);
                    dynamicParamsTran.Add("@eqpcode", eqpcode);
                    dynamicParamsTran.Add("@usergroupcode", usergroupcode);
                    dynamicParamsTran.Add("@reportuser", reportuser);
                    dynamicParamsTran.Add("@taskqty", taskqty);
                    dynamicParamsTran.Add("@startqty", startqty);
                    dynamicParamsTran.Add("@reportqty", reportqty);
                    dynamicParamsTran.Add("@ngqty", ngqty);
                    dynamicParamsTran.Add("@badcode", badcode);
                    dynamicParamsTran.Add("@remarks", remarks);
                    dynamicParamsTran.Add("@username", username);
                    bool a = DapperHelper.IsProcedure(sql, dynamicParamsTran);
                    if (a)
                    {
                        sql = @"insert into  TK_Wrk_RecordSub(m_id,eqp_code,report_person,report_date,report_qty,usergroup_code,ng_qty,style,lm_user,lm_date) 
                                values(@m_id,@eqp_code,@report_person,@report_date,@report_qty,@usergroup_code,@ng_qty,@style,@lm_user,@lm_date)";
                        list.Add(new { str = sql, parm = new { m_id = int.Parse(dt.Rows[0]["ID"].ToString()), eqp_code = eqpcode, report_person = arra[i], report_date = date, report_qty = reportqty, usergroup_code = usergroupcode, ng_qty = ngqty, style = "B", lm_user = username, lm_date = date } });

                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    if (badcode != "" && ngqty != "0")
                    else
                    {
                        //写入缺陷记录表
                        for (int i = 0; i < arra1.Length; i++)
                        {
                            sql = @"insert into  CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_code,remarks,style,lm_user,lm_date) 
                                values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_code,@remarks,@style,@lm_user,@lm_date)";
                            list.Add(new { str = sql, parm = new { record_id = int.Parse(dt.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = ngqty, defect_code = arra1[i], remarks = remarks, style = "B", lm_user = username, lm_date = date } });

                        }
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                    ////修改报工记录
                    //sql = @"update TK_Wrk_Record set good_qty=good_qty+@reportqty,ng_qty=ng_qty+@ngqty,
                    //            lm_user=@username,lm_date=@CreateDate where wo_code=@mesordercode and step_code=@stepcode and style='B'";
                    //list.Add(new { str = sql, parm = new { reportqty = decimal.Parse(reportqty), ngqty = decimal.Parse(ngqty), mesordercode = mesordercode, stepcode = stepcode, username = username, CreateDate = date } });
                    ////写入子表
                    //for (int i = 0; i < arra.Length; i++)
                    //{
                    //    sql = @"insert into  TK_Wrk_RecordSub(m_id,eqp_code,report_person,report_date,report_qty,usergroup_code,ng_qty,style,lm_user,lm_date) 
                    //            values(@m_id,@eqp_code,@report_person,@report_date,@report_qty,@usergroup_code,@ng_qty,@style,@lm_user,@lm_date)";
                    //    list.Add(new { str = sql, parm = new { m_id = int.Parse(data.Rows[0]["ID"].ToString()), eqp_code = eqpcode, report_person = arra[i], report_date = date, report_qty = reportqty, usergroup_code = usergroupcode, ng_qty = ngqty, style = "B", lm_user = username, lm_date = date } });

                    //}
                    //if (badcode != "" && ngqty != "0")
                    //{
                    //    //写入缺陷记录表
                    //    for (int i = 0; i < arra1.Length; i++)
                    //    {
                    //        sql = @"insert into  CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_code,style,lm_user,lm_date) 
                    //            values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_code,@style,@lm_user,@lm_date)";
                    //        list.Add(new { str = sql, parm = new { record_id = int.Parse(data.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = ngqty, defect_code = arra1[i], style = "B", lm_user = username, lm_date = date } });

                    //    }
                    //}
                }
                else
                {
                    //获取主表最大ID
                    sql = @"select ISNULL(IDENT_CURRENT('TK_Wrk_Record')+1,1) as id";
                    var dt = DapperHelper.selecttable(sql);
                    //写入开报工记录表
                    sql = @"insert into  TK_Wrk_Record(wo_code,step_seq,step_code,eqp_code,materiel_code,task_qty,start_qty,good_qty,ng_qty,style,lm_user,lm_date) 
                                values(@mesordercode,@stepseq,@stepcode,@eqpcode,@partcode,@taskqty,@startqty,@reportqty,@ngqty,@style,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, eqpcode = eqpcode, partcode = partcode, taskqty = taskqty, startqty = startqty, reportqty = reportqty, ngqty = ngqty, style = "B", lm_user = username, lm_date = date } });

                    //写入子表
                    for (int i = 0; i < arra.Length; i++)
                    //存储过程名
                    sql = @"h_p_MES_ProductionReport_ZZ";
                    dynamicParamsTran.Add("@mesordercode", mesordercode);
                    dynamicParamsTran.Add("@partcode", partcode);
                    dynamicParamsTran.Add("@stepseq", stepseq);
                    dynamicParamsTran.Add("@stepcode", stepcode);
                    dynamicParamsTran.Add("@eqpcode", eqpcode);
                    dynamicParamsTran.Add("@usergroupcode", usergroupcode);
                    dynamicParamsTran.Add("@reportuser", reportuser);
                    dynamicParamsTran.Add("@taskqty", taskqty);
                    dynamicParamsTran.Add("@startqty", startqty);
                    dynamicParamsTran.Add("@reportqty", reportqty);
                    dynamicParamsTran.Add("@ngqty", ngqty);
                    dynamicParamsTran.Add("@badcode", badcode);
                    dynamicParamsTran.Add("@remarks", remarks);
                    dynamicParamsTran.Add("@username", username);
                    bool a = DapperHelper.IsProcedure(sql, dynamicParamsTran);
                    if (a)
                    {
                        sql = @"insert into  TK_Wrk_RecordSub(m_id,eqp_code,report_person,report_date,report_qty,usergroup_code,ng_qty,style,lm_user,lm_date) 
                                values(@m_id,@eqp_code,@report_person,@report_date,@report_qty,@usergroup_code,@ng_qty,@style,@lm_user,@lm_date)";
                        list.Add(new { str = sql, parm = new { m_id = int.Parse(dt.Rows[0]["ID"].ToString()), eqp_code = eqpcode, report_person = arra[i], report_date = date, report_qty = reportqty, usergroup_code = usergroupcode, ng_qty = ngqty, style = "B", lm_user = username, lm_date = date } });

                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    if (badcode != "" && ngqty != "0")
                    else
                    {
                        //写入缺陷记录表
                        for (int i = 0; i < arra1.Length; i++)
                        {
                            sql = @"insert into  CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_code,remarks,style,lm_user,lm_date) 
                                values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_code,@remarks,@style,@lm_user,@lm_date)";
                            list.Add(new { str = sql, parm = new { record_id = int.Parse(dt.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = ngqty, defect_code = arra1[i], remarks = remarks, style = "B", lm_user = username, lm_date = date } });

                        }
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                }


                //回写工单工序表合格数量、不良数量
                sql = @"update TK_Wrk_Step set good_qty=good_qty+@reportqty,ng_qty=ng_qty+@ngqty  where wo_code=@mesordercode and step_code=@stepcode";
                list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepcode = stepcode, reportqty = reportqty, ngqty = ngqty } });

                //回写工单表合格数量、不良数量
                //sql = @"update TK_Wrk_Man set good_qty=good_qty+@reportqty,ng_qty=ng_qty+@ngqty  where wo_code=@mesordercode";
                //list.Add(new { str = sql, parm = new { mesordercode = mesordercode, reportqty = reportqty, ngqty = ngqty } });

                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "操作失败!";
                    mes.data = null;
                }
                }           
            }
            catch (Exception e)
            {
@@ -1797,6 +1843,7 @@
            string[] arra1 = new string[] { };
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            var dynamicParamsTran = new DynamicParameters();
            try
            {
                string date = DateTime.Now.ToString(); //获取系统时间
@@ -1867,102 +1914,68 @@
                        return mes;
                    }


                    //获取主表最大ID
                    sql = @"select ISNULL(IDENT_CURRENT('TK_Wrk_OutRecord')+1,1) as id";
                    var dt = DapperHelper.selecttable(sql);
                    //写入外协记录主表
                    sql = @"insert into  TK_Wrk_OutRecord(wo_code,step_seq,step_code,wx_code,materiel_code,style,sqty,ng_qty,lm_user,lm_date) 
                                values(@mesordercode,@stepseq,@stepcode,@wx_code,@partcode,@style,@sqty,@ngqty,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, wx_code = wxcode, partcode = partcode, style = 'S', sqty = sqty, ngqty = ngqty, lm_user = username, lm_date = date } });

                    //写入外协记录子表
                    sql = @"insert into  TK_Wrk_OutRecordSub(m_id,wx_code,in_person,in_time,sqty,ng_qty,style,lm_user,lm_date) 
                                values(@m_id,@wxcode,@in_person,@in_time,@sqty,@ng_qty,@style,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { m_id = int.Parse(dt.Rows[0]["ID"].ToString()), wxcode = wxcode, in_person = inuser, in_time = date, sqty = sqty, ng_qty = ngqty, style = "S", lm_user = username, lm_date = date } });

                    if (badcode != "" && ngqty != "0")
                    //存储过程名
                    sql = @"h_p_MES_ProductionReport_WX";
                    dynamicParamsTran.Add("@mesordercode", mesordercode);
                    dynamicParamsTran.Add("@partcode", partcode);
                    dynamicParamsTran.Add("@stepseq", stepseq);
                    dynamicParamsTran.Add("@stepcode", stepcode);
                    dynamicParamsTran.Add("@wxcode", wxcode);
                    dynamicParamsTran.Add("@inuser", inuser);
                    dynamicParamsTran.Add("@taskqty", taskqty);
                    dynamicParamsTran.Add("@sqty", sqty);
                    dynamicParamsTran.Add("@ngqty", ngqty);
                    dynamicParamsTran.Add("@badcode", badcode);
                    dynamicParamsTran.Add("@remarks", remarks);
                    dynamicParamsTran.Add("@username", username);
                    bool a = DapperHelper.IsProcedure(sql, dynamicParamsTran);
                    if (a)
                    {
                        //写入缺陷记录表
                        for (int i = 0; i < arra1.Length; i++)
                        {
                            sql = @"insert into  CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_code,remarks,style,lm_user,lm_date) 
                                values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_code,@remarks,@style,@lm_user,@lm_date)";
                            list.Add(new { str = sql, parm = new { record_id = int.Parse(dt.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = ngqty, defect_code = arra1[i], remarks = remarks, style = "S", lm_user = username, lm_date = date } });

                        }
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }


                    ////修改外协记录主表
                    //sql = @"update TK_Wrk_OutRecord set sqty=sqty+@sqty,ng_qty=ng_qty+@ngqty,lm_user=@username,lm_date=@CreateDate
                    //         where wo_code=@mesordercode and step_code=@stepcode and wx_code=@wx_code and style='S'";
                    //list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepcode = stepcode, wx_code = wxcode, sqty = decimal.Parse(sqty), ngqty = decimal.Parse(ngqty), username = username, CreateDate = date } });
                    ////写入外协记录子表
                    //sql = @"insert into  TK_Wrk_OutRecordSub(m_id,wx_code,in_person,in_time,sqty,ng_qty,style,lm_user,lm_date) 
                    //            values(@m_id,@wx_code,@in_person,@in_time,@sqty,@ngqty,@style,@lm_user,@lm_date)";
                    //list.Add(new { str = sql, parm = new { m_id = int.Parse(data.Rows[0]["ID"].ToString()), wx_code = wxcode, in_person = inuser, in_time = date, sqty = sqty, ngqty = ngqty, style = 'S', lm_user = username, lm_date = date } });

                    //if (badcode != "" && ngqty != "0")
                    //{
                    //    //写入缺陷记录表
                    //    for (int i = 0; i < arra1.Length; i++)
                    //    {
                    //        sql = @"insert into  CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_code,style,lm_user,lm_date) 
                    //            values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_code,@style,@lm_user,@lm_date)";
                    //        list.Add(new { str = sql, parm = new { record_id = int.Parse(data.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = ngqty, defect_code = arra1[i], style = "S", lm_user = username, lm_date = date } });

                    //    }
                    //}
                }
                else
                {
                    //获取主表最大ID
                    sql = @"select ISNULL(IDENT_CURRENT('TK_Wrk_OutRecord')+1,1) as id";
                    var dt = DapperHelper.selecttable(sql);
                    //写入外协记录主表
                    sql = @"insert into  TK_Wrk_OutRecord(wo_code,step_seq,step_code,wx_code,materiel_code,style,sqty,ng_qty,lm_user,lm_date) 
                                values(@mesordercode,@stepseq,@stepcode,@wx_code,@partcode,@style,@sqty,@ngqty,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, wx_code = wxcode, partcode = partcode, style = 'S', sqty = sqty, ngqty = ngqty, lm_user = username, lm_date = date } });

                    //写入外协记录子表
                    sql = @"insert into  TK_Wrk_OutRecordSub(m_id,wx_code,in_person,in_time,sqty,ng_qty,style,lm_user,lm_date) 
                                values(@m_id,@wxcode,@in_person,@in_time,@sqty,@ng_qty,@style,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { m_id = int.Parse(dt.Rows[0]["ID"].ToString()), wxcode = wxcode, in_person = inuser, in_time = date, sqty = sqty, ng_qty = ngqty, style = "S", lm_user = username, lm_date = date } });

                    if (badcode != "" && ngqty != "0")
                    //存储过程名
                    sql = @"h_p_MES_ProductionReport_WX";
                    dynamicParamsTran.Add("@mesordercode", mesordercode);
                    dynamicParamsTran.Add("@partcode", partcode);
                    dynamicParamsTran.Add("@stepseq", stepseq);
                    dynamicParamsTran.Add("@stepcode", stepcode);
                    dynamicParamsTran.Add("@wxcode", wxcode);
                    dynamicParamsTran.Add("@inuser", inuser);
                    dynamicParamsTran.Add("@taskqty", taskqty);
                    dynamicParamsTran.Add("@sqty", sqty);
                    dynamicParamsTran.Add("@ngqty", ngqty);
                    dynamicParamsTran.Add("@badcode", badcode);
                    dynamicParamsTran.Add("@remarks", remarks);
                    dynamicParamsTran.Add("@username", username);
                    bool a = DapperHelper.IsProcedure(sql, dynamicParamsTran);
                    if (a)
                    {
                        //写入缺陷记录表
                        for (int i = 0; i < arra1.Length; i++)
                        {
                            sql = @"insert into  CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_code,remarks,style,lm_user,lm_date) 
                                values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_code,@remarks,@style,@lm_user,@lm_date)";
                            list.Add(new { str = sql, parm = new { record_id = int.Parse(dt.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = ngqty, defect_code = arra1[i], remarks = remarks, style = "S", lm_user = username, lm_date = date } });

                        }
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                }
                //回写工单工序表合格数量、不良数量
                sql = @"update TK_Wrk_Step set good_qty=good_qty+@sqty,ng_qty=ng_qty+@ngqty  where wo_code=@mesordercode and step_code=@stepcode";
                list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepcode = stepcode, sqty = sqty, ngqty = ngqty } });

                //回写工单表合格数量、不良数量
                //sql = @"update TK_Wrk_Man set good_qty=good_qty+@sqty,ng_qty=ng_qty+@ngqty  where wo_code=@mesordercode";
                //list.Add(new { str = sql, parm = new { mesordercode = mesordercode, sqty = sqty, ngqty = ngqty } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "收成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "收料失败!";
                    mes.data = null;
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
@@ -2590,7 +2603,7 @@
                                      FROM TWkm_capac_plan_sub t0 where CONVERT(varchar(100), wkdate, 23)>=CONVERT(varchar(100),getdate(), 23) 
                                      GROUP BY t0.m_id  
                             ) E on B.id=E.m_id
                            where C.description='W' ";
                            where C.description='W' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -2616,14 +2629,14 @@
            {
                if (type == "Add")
                {
                    var sql = @"insert into TWkm_capac_plan(wkshop, eqp_typecode, wkshift_code, enable, lm_user, lm_time,classtype) 
                            values(@wkshop,@eqp_typecode,@wkshift_code,@enable,@lm_user,@lm_time,@classtype)";
                    var sql = @"insert into TWkm_capac_plan(wkshop, eqp_typecode, wkshift_code, enable, lm_user, lm_date,classtype) 
                            values(@wkshop,@eqp_typecode,@wkshift_code,@enable,@lm_user,@lm_date,@classtype)";
                    dynamicParams.Add("@wkshop", wkshopcode);
                    dynamicParams.Add("@eqp_typecode", capunitcode);
                    dynamicParams.Add("@wkshift_code", capsetupcode);
                    dynamicParams.Add("@enable", "Y");
                    dynamicParams.Add("@lm_user", username);
                    dynamicParams.Add("@lm_time", DateTime.Now.ToString());
                    dynamicParams.Add("@lm_date", DateTime.Now.ToString());
                    dynamicParams.Add("@classtype", captplantype);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
@@ -2687,10 +2700,10 @@
                sql = @"select CONVERT(varchar(100), wkdate, 23) DataTime,wkshift_code   from TWkm_capac_plan_sub where m_id=@captplanid";
                dynamicParams.Add("@captplanid", captplanid);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < dt.Rows.Count; i++)
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    string DataTime = dt.Rows[i]["DataTime"].ToString();//日期
                    string key = dt.Rows[i]["wkshift_code"].ToString(); //方案编码
                    string DataTime = data.Rows[i]["DataTime"].ToString();//日期
                    string key = data.Rows[i]["wkshift_code"].ToString(); //方案编码
                    CapaPlan cmp = new CapaPlan();
                    cmp.name = DataTime;
                    cmp.key = key;
@@ -2728,11 +2741,11 @@
                dynamicParams.Add("@captplanid", captplanid);
                dynamicParams.Add("@datetime", datetime);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < dt.Rows.Count; i++)
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    string code = dt.Rows[i]["code"].ToString();//方案编码
                    string name = dt.Rows[i]["name"].ToString();//方案名称
                    string flag = dt.Rows[i]["flag"].ToString();   //选中方案标识
                    string code = data.Rows[i]["code"].ToString();//方案编码
                    string name = data.Rows[i]["name"].ToString();//方案名称
                    string flag = data.Rows[i]["flag"].ToString();   //选中方案标识
                    CapacityPlanSect cmp = new CapacityPlanSect();
                    cmp.CapCode = code;
                    cmp.CapName = name;
@@ -2741,7 +2754,7 @@

                    string sql1 = @"select wktme1_start,wktme2_start,wktme3_start,wktme4_start,wktme5_start  from TBas_wkshift_info where code=@code";
                    dynamicParams.Add("@code", code);
                    var db = DapperHelper.selectdata(sql, dynamicParams);
                    var db = DapperHelper.selectdata(sql1, dynamicParams);

                    for (int j = 0; j < db.Columns.Count; j++) //时间段
                    {
@@ -2855,11 +2868,10 @@
                    List<CapaPlan> list4 = ids1.Except(ids).ToList(); //表示ids1中哪些值是ids中所不存在的;
                    list4 = list3.Union(list4).ToList();
                    //查找关键工序设备、工位群组的所有设备
                    string sql3 = @"select *  from(
                                  select distinct A.code,A.name  from TEqpInfo A 
                    string sql3 = @"select distinct A.code,A.name  from TEqpInfo A 
                                  left join TFlw_Rteqp B on A.code=B.eqp_code
                                  left join TFlw_Rtdt D on B.step_code=D.step_code                 
                                  where A.wksp_code=@wkshopcode  and D.first_choke='Y'  order by A.code)";
                                  where A.wksp_code=@wkshopcode  and D.first_choke='Y'  order by A.code";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                    var dt3 = DapperHelper.selectdata(sql3, dynamicParams);
                    if (list4.Count > 0)  //如果日期有变动
@@ -2871,67 +2883,573 @@
                                for (int n = 0; n < dt3.Rows.Count; n++)
                                {
                                    string sql4 = @"select  A.wo_code,A.eqp_code,A.Time_Start,A.time_end, 
                                                 (case  B.Status when 'SCHED' then '已排程' when 'ALLOC' then '已派发' when 'START' then '已开工' end ) status   
                                                  from TK_Wrk_EqpAps  A
                                                  left join mes_tk_wrk_man B on A.wo_code=B.Wo_Code
                                                  left join gn_eqp_eqpinfo C on A.eqp_code=C.code
                                                  where TO_char(time_start,'YYYY-MM-DD')='" + list4[m].name + "' and A.eqp_code='" + dt3.Rows[n]["CODE"] + "' and B.isaps='Y' and B.status IN('SCHED','ALLOC','START')";
                                    DataTable dt4 = DBHelper.GetTable(sql4);
                                                     (case  B.Status when 'SCHED' then '已排程' when 'ALLOC' then '已派发' when 'START' then '已开工' end ) status   
                                                      from TK_Wrk_EqpAps  A
                                                      left join TK_Wrk_Man B on A.wo_code=B.wo_code
                                                      left join TEqpInfo C on A.eqp_code=C.code
                                                      where CONVERT(varchar(100), time_start, 23)=@time_start and A.eqp_code=@eqp_code and B.isaps='Y' and B.status IN('SCHED','ALLOC','START')";
                                    dynamicParams.Add("@time_start", list4[m].name);
                                    dynamicParams.Add("@eqp_code", dt3.Rows[n]["CODE"]);
                                    var dt4 = DapperHelper.selectdata(sql4, dynamicParams);
                                    if (dt4.Rows.Count > 0)
                                    {
                                        for (int g = 0; g < dt4.Rows.Count; g++)
                                        {
                                            json = new DWZJson()
                                            {
                                                statusCode = "300",
                                                message = "修改失败！,日期：" + dt4.Rows[g]["TIME_START"] + "有【" + dt4.Rows[g]["STATUS"] + "】工单：" + dt4.Rows[g]["WO_CODE"] + "",
                                            };
                                            return json;
                                            mes.code = "300";
                                            mes.count = 0;
                                            mes.Message = "修改失败！,日期：" + dt4.Rows[g]["TIME_START"] + "有【" + dt4.Rows[g]["STATUS"] + "】工单：" + dt4.Rows[g]["WO_CODE"] + "";
                                            mes.data = null;
                                            return mes;
                                        }
                                    }
                                }
                            }
                        }
                    }


                    list.Add(@"update gn_wkm_capac_plan set wkshift_code='" + CapSetupCode + "',classtype='" + CaptPlanType + "', enable='Y',lm_user='" + lm_user + "',lm_time=sysdate  where id='" + CaptPlanId + "'");
                    list.Add(@"delete gn_wkm_capac_plan_sub where m_id='" + CaptPlanId + "'");
                    //更新排产生产资源主表
                    sql = @"update TWkm_capac_plan set wkshift_code=@wkshift_code,classtype=@classtype,lm_user=@lm_user,lm_date=@lm_time  where id=@id";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            id = captplanid,
                            wkshift_code = capsetupcode,
                            classtype = captplantype,
                            lm_user = username,
                            lm_time = DateTime.Now.ToString()
                        }
                    });
                    //删除排产生产资源子表
                    sql = @"delete TWkm_capac_plan_sub  where m_id=@id";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            id = captplanid
                        }
                    });
                    //循环写入排产生产资源子表
                    for (int i = 0; i < objs.Count; i++)
                    {
                        list.Add(@"insert into gn_wkm_capac_plan_sub(m_id, id, wkdate,wkshift_code) 
                                     values('" + CaptPlanId + "',gn_wkm_capac_plan_sub_S.nextVal,to_date('" + objs[i].name + "','yyyy-MM-dd'),'" + objs[i].key + "')");
                    }
                    int count = DBHelper.Executesqltran(list, pt.ToArray());
                    if (count > 0)
                    {
                        json = new DWZJson()
                        sql = @"insert into TWkm_capac_plan_sub(m_id,wkdate,wkshift_code) values(@m_id,@wkdate,@wkshift_code)";
                        list.Add(new
                        {
                            statusCode = "200",
                            message = "修改成功！",
                        };
                            str = sql,
                            parm = new
                            {
                                m_id = captplanid,
                                wkdate = objs[i].name,
                                wkshift_code = objs[i].key
                            }
                        });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        json = new DWZJson()
                        {
                            statusCode = "300",
                            message = "修改失败！",
                        };
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {

                json = new DWZJson()
                {
                    statusCode = "300",
                    message = e.Message,
                };
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion



        #region[自动排程工单查询]
        public static ToMessage AdvancedSchedulingSearch(string workshop, string wocode, string partcode, string partname, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (workshop != "" && workshop != null)
                {
                    search += "and A.wkshp_code=@workshop ";
                    dynamicParams.Add("@workshop", workshop);
                }
                if (wocode != "" && wocode != null)
                {
                    search += "and A.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and A.materiel_code like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and C.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select
                             A.id              AdvaScheId, 
                             A.status          AdvaScheStus,
                             A.wo_code         AdvaScheWorkCode,
                             A.wkshp_code      AdvaScheWorkShopid,
                             M.org_name        AdvaScheWorkShop,
                             A.materiel_code   AdvaSchePartNumber,
                             C.partname        AdvaSchePartName,
                             C.partspec        AdvaSchePartSpec,
                             D.name            AdvaSchePartModel,
                             A.plan_qty        AdvaScheQty,
                             U.name            AdvaScheUom,
                             isnull(E.sched_qty,0)       AdvaScheYPQty,
                            CONVERT(varchar(100), B.planenddate, 23) AdvaScheEndDate,
                            convert(varchar(20),isnull(E.sched_qty,0))+'/'+convert(varchar(20),isnull(A.plan_qty,0)) AdvaScheSpeed,
                             A.route_code    AdvaScheRoutid,
                             F.name        AdvaScheRoutName, 
                             E.step_code   AdvaScheBotProcid,
                             G.stepname        AdvaScheBotProcName,
                            CONVERT(varchar(100), H.maxtime, 23) AdvaSchePCEndDate,
                             CONVERT(varchar(100), H.mintime, 23) AdvaSchePCStartDate,
                            (case  when CONVERT(varchar(100), H.maxtime, 23)<=CONVERT(varchar(100), E.plan_enddate, 23) then 'Y'  when H.MAXTime is null  then 'Y'  else 'N' end) Flag,
                            (case when A.PiroQue='1' then '特急' when A.PiroQue='2' then '紧急' when A.PiroQue='3' then '正常' end) AdvaSchePiroQue 
                            from TK_Wrk_Man A
                            left join TKimp_Ewo B on A.m_po=B.wo
                            left join TMateriel_Info C on C.partcode= A.materiel_code
                             left join T_Dict D on C.stocktype_code= d.code
                             left join TK_Wrk_Step E on E.wo_code=A.wo_code
                             left join TFlw_Rout F  on A.route_code=F.code
                             left join TStep G  on E.step_code=G.stepcode
                             left join (select wo_code, max(TIME_END) MAXTime,min(time_start) MINTime  from TK_Wrk_EqpAps group by wo_code) H on A.wo_code=H.wo_code
                            left join TUom U on C.uom_code=U.code
                             left join TOrganization M on A.wkshp_code=M.org_code
                            where    E.isbott = 'Y' and A.status='NEW' and A.isaps='Y'";
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[排产设备信息]
        public static List<AdvancedSchedulingDevice> OnclickAdvancedSchedulingDevice(string wocode, string wkshpcode, string partcode, string startdate, string enddate, ref ToMessage mes)
        {
            string sql = "", sql0 = "";
            var dynamicParams = new DynamicParameters();
            List<AdvancedSchedulingDevice> list = new List<AdvancedSchedulingDevice>();
            DataTable dt, dt1;

            DateTime beginDate = Convert.ToDateTime(startdate);
            DateTime endDate = DateTime.Parse(enddate);
            try
            {
                if (beginDate > endDate)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "要求交付时间不能小于当前系统时间！";
                    mes.data = null;
                    return list;
                }
                bool IsCap = false;    //是否设置产能
                //通过工单查找工艺路线对应关键工序所关联的设备是否可用
                sql = @"select distinct C.eqp_code ,D.Enable  from TK_Wrk_Man A
                       left join TFlw_Rout K on A.route_code=K.code
                       left join TFlw_Rtdt B on K.code=B.rout_code and B.first_choke='Y'
                       left join TFlw_Rteqp C on B.step_code= C.step_code
                       left join TEqpInfo D on C.eqp_code=D.code
                       where A.wo_code=@wocode and A.materiel_code=@partcode";
                dynamicParams.Add("@wocode", wocode);
                dynamicParams.Add("@partcode", partcode);
                var dt_0 = DapperHelper.selectdata(sql, dynamicParams);
                if (dt_0.Rows.Count > 0)
                {
                    int cout = dt_0.Rows.Count;
                    int num = 0;
                    for (int m = 0; m < dt_0.Rows.Count; m++)
                    {
                        if (dt_0.Rows[m]["Enable"].ToString() == "N")
                        {
                            num = num + 1;
                        }
                    }
                    if (num == cout)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "设备不可用！";
                        mes.data = null;
                        return list;
                    }
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "工艺路线未关联设备！";
                    mes.data = null;
                    return list;
                }

                //通过工单查找工艺路线对应关键工序所关联的设备所属车间
                sql0 = @"select distinct D.wksp_code  from TK_Wrk_Man A
                         left join TFlw_Rout K on A.route_code=K.code
                         left join TFlw_Rtdt B on K.code=B.rout_code and B.first_choke='Y'
                         left join TFlw_Rteqp C on B.step_code= C.step_code
                         left join TEqpInfo D on C.eqp_code=D.code
                         where A.wo_code=@wocode and A.materiel_code=@partcode ";
                dynamicParams.Add("@wocode", wocode);
                dynamicParams.Add("@partcode", partcode);
                var dt0 = DapperHelper.selectdata(sql0, dynamicParams);
                if (dt0.Rows.Count > 0)
                {
                    string sy = "0";
                    for (int i = 0; i < dt0.Rows.Count; i++)
                    {
                        if (dt0.Rows[i]["WKSHOP"].ToString() == wkshpcode)  //工单创建车间是否等于排产设备 车间
                        {
                            sy = "1";
                            break;
                        }
                        else
                        {
                            sy = "0";
                        }
                    }
                    if (sy == "0")
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "排产设备车间与工单创建的车间不同！";
                        mes.data = null;
                    }
                    else
                    {
                        List<APSList> listData = SchedulingMethod.SchedulingMethodTF(wocode, wkshpcode, partcode);
                        for (DateTime date = beginDate; date <= endDate; date = date.AddDays(1))
                        {
                            AdvancedSchedulingDevice tbj = new AdvancedSchedulingDevice();
                            tbj.YearDate = date.ToString("yyyy-MM-dd");
                            tbj.children = new List<AdvancedSchedulingDeviceCont>();
                            for (int j = 0; j < listData.Count; j++)
                            {
                                if (listData[j].AdvaDevicCropMob.ToString() == "0" || listData[j].AdvaDevicRhythm.ToString() == "")
                                {
                                    mes.code = "300";
                                    mes.count = 0;
                                    mes.Message = "排程设备" + listData[j].eqp_id.ToString() + "稼动率不能为0或为空！";
                                    mes.data = null;
                                    return list;
                                }
                                if (listData[j].AdvaDevicRhythm.ToString() == "0" || listData[j].AdvaDevicRhythm.ToString() == "")
                                {
                                    mes.code = "300";
                                    mes.count = 0;
                                    mes.Message = "排程设备" + listData[j].eqp_id.ToString() + "未设置节拍！";
                                    mes.data = null;
                                    return list;
                                }
                                string sql1 = @"select wktme1_start,wktme2_start,wktme3_start,wktme4_start,wktme5_start,G.name  
                                              from  TWkm_capac_plan  E 
                                                left join TWkm_capac_plan_sub F on E.id=F.m_id
                                                left join TBas_wkshift_info G on F.wkshift_code=G.code
                                                where E.wkshop='CJ001'and E.eqp_typecode='SBLX001'  and E.ClassType='D'
                                              and CONVERT(varchar(100), F.wkdate, 23)='2022-10-11' and E.enable='Y'";
                                dynamicParams.Add("@wkshop", wkshpcode);
                                dynamicParams.Add("@eqp_typecode", listData[j].Style.ToString());
                                dynamicParams.Add("@classtype", listData[j].ClassType.ToString());
                                dynamicParams.Add("@wkdate", date.ToString("yyyy-MM-dd"));
                                dt1 = DapperHelper.selectdata(sql1, dynamicParams);

                                AdvancedSchedulingDeviceCont tbjson = new AdvancedSchedulingDeviceCont();
                                tbjson.AdvaDevicNumber = listData[j].eqp_id.ToString();
                                tbjson.AdvaDevicName = listData[j].name.ToString();
                                tbjson.AdvaDevicCropMob = listData[j].AdvaDevicCropMob.ToString();  //稼动率
                                tbjson.AdvaDevicRhythm = listData[j].AdvaDevicRhythm.ToString();    //生产节拍
                                if (dt1.Rows.Count > 0)
                                {
                                    tbjson.OneStartDate = dt1.Rows[0]["wktme1_start"].ToString();
                                    tbjson.TwoStartDate = dt1.Rows[0]["wktme2_start"].ToString();
                                    tbjson.ThreeStartDate = dt1.Rows[0]["wktme3_start"].ToString();
                                    tbjson.FourStartDate = dt1.Rows[0]["wktme4_start"].ToString();
                                    tbjson.FiveStartDate = dt1.Rows[0]["wktme5_start"].ToString();
                                    tbj.children.Add(tbjson);
                                    IsCap = true;
                                }
                                else
                                {

                                    tbjson.OneStartDate = "";
                                    tbjson.TwoStartDate = "";
                                    tbjson.ThreeStartDate = "";
                                    tbjson.FourStartDate = "";
                                    tbjson.FiveStartDate = "";
                                    tbj.children.Add(tbjson);
                                }
                            }

                            list.Add(tbj);
                        }
                        if (list.Select(p => p.children).ToList().Count > 0)
                        {
                            int one = list.Where(t => t.children.Select(s => s.OneStartDate).Any(x => x != "")).ToList().Count;
                            int two = list.Where(t => t.children.Select(s => s.TwoStartDate).Any(x => x != "")).ToList().Count;
                            int three = list.Where(t => t.children.Select(s => s.ThreeStartDate).Any(x => x != "")).ToList().Count;
                            int four = list.Where(t => t.children.Select(s => s.FourStartDate).Any(x => x != "")).ToList().Count;
                            int five = list.Where(t => t.children.Select(s => s.FiveStartDate).Any(x => x != "")).ToList().Count;
                            if (one <= 0 && two <= 0 && three <= 0 && four <= 0 && five <= 0)
                            {
                                mes.code = "300";
                                mes.count = 0;
                                mes.Message = "排程设备未设置产能！";
                                mes.data = null;
                            }
                        }
                    }
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "设备没有关联车间！";
                    mes.data = null;
                }
                return list;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return list;
        }
        #endregion

        #region[设备已排程信息]
        public static DataTable AlreadyScheduling(string wocode, string wkshpcode, string partcode, string botproccode, string startdate, string enddate)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                List<APSList> listData = SchedulingMethod.SchedulingMethodTF(wocode, wkshpcode, partcode);
                string[] empIds = listData.Select(a => a.eqp_id).ToArray();
                string str = string.Join(",", empIds);
                string s1 = string.Format("'{0}'", str.Replace(",", "','"));

                string sql = @"select B.wo_code,  B.eqp_code,B.time_start,B.time_end, 'S' status , B.alloc_qty,D.partname as part_name
                              from TK_Wrk_EqpAps B 
                               left join TK_Wrk_Man C on B.wo_code=C.wo_code
                               left join  TMateriel_Info D on C.materiel_code=D.partcode
                               where  B.eqp_code in(@s1) 
                              and convert(varchar(100),B.Time_Start,21)>=@startdate and convert(varchar(100),B.Time_End,21)<=@enddate order by time_end";
                dynamicParams.Add("@s1", s1);
                dynamicParams.Add("@startdate", startdate + " 00:00:00");
                dynamicParams.Add("@enddate", enddate + " 23:59:59");
                var dt_0 = DapperHelper.selectdata(sql, dynamicParams);
                if (dt == null || dt.Rows.Count == 0)
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return dt;
        }
        #endregion

        #region[排程数据提交]
        public static ToMessage SubmitAlreadyScheduling(string username, string wocode, string botprocecode, List<AlreadyScheduling> objs)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                string maxTime = objs.Max(t => Convert.ToDateTime(t.AlreEndDate).ToString("yyyy-MM-dd HH:mm"));  //最大值
                string minTime = objs.Min(t => Convert.ToDateTime(t.AlreEndDate).ToString("yyyy-MM-dd HH:mm"));  //最小值
                sql = @"select 
                        A.id, A.status,
                        '0' BottFrointv,  convert(varchar(100),B.plan_enddate-0,21) Plan_end ,
                        convert(varchar(100),B.plan_startdate+0,21) plan_start
                        from TK_Wrk_Step A
                        left join TK_Wrk_Man B on A.wo_code=B.wo_code  
                        where A.wo_code=@wocode and A.step_code=@botprocecode and A.isbott='Y'";
                dynamicParams.Add("@wocode", wocode);
                dynamicParams.Add("@botprocecode", botprocecode);
                var dt = DapperHelper.selectdata(sql, dynamicParams);
                string ID = dt.Rows[0]["ID"].ToString();     // mes_tk_wrk_step 表 瓶径工序行ID
                string status = dt.Rows[0]["STATUS"].ToString();
                Decimal nm = 0;                                                     //瓶径工序的前置天数
                Decimal nn = Decimal.Parse(dt.Rows[0]["BottFrointv"].ToString());   //瓶径工序的后置天数
                if (status != "NEW" && status != "SCHED")   //工序任务的状态已经派发（审核）
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前排程任务已经派发,提交取消！";
                    mes.data = null;
                    return mes;
                }
                if (status == "SCHED")   //工序任务的状态已经排程
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前排程任务已经排程,提交取消！";
                    mes.data = null;
                    return mes;
                }

                list.Clear();
                //按工单删除设备任务表(日任务表）
                sql = @"delete TK_Wrk_EqpAps  where wo_code=@wocode";
                list.Add(new { str = sql, parm = new { wocode = wocode } });
                //按工单删除设备任务表(汇总表）
                sql = @"delete TK_Wrk_EqpApsSum  where wo_code=@wocode";
                list.Add(new { str = sql, parm = new { wocode = wocode } });
                //按工单删除设备任务表(汇总表）物料表
                sql = @"delete TK_Wrk_EqpSum_Allo  where wo_code=@wocode";
                list.Add(new { str = sql, parm = new { wocode = wocode } });

                float n = 0;  //累计排产总数

                for (int i = 0; i < objs.Count; i++)        //循环添加每个设备的机台任务
                {
                    sql = @"insert into TK_Wrk_EqpAps (wo_code,step_taid,eqp_code,time_start,time_end,alloc_qty,status)
                            values(@wo_code,@step_taid,@eqp_code,@time_start,@time_end,@alloc_qty,@status)";
                    list.Add(new { str = sql, parm = new { wo_code = wocode, step_taid = ID, eqp_code = objs[i].AlreDevicNumber, time_start = objs[i].AlreStartDate, time_end = objs[i].AlreEndDate, alloc_qty = objs[i].AlreQty, status = "NEW" } });
                    n = n + float.Parse(objs[i].AlreQty.ToString());
                }

                bool aa = DapperHelper.DoTransaction(list); //提交设备任务
                if (!aa)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "先预排,再点击提交！";
                    mes.data = null;
                    return mes;
                }
                list.Clear();
                //生成设备任务汇总表  （根据设备日任务表）
                sql = "select distinct eqp_code from TK_Wrk_EqpAps where wo_code=@wocode";
                dynamicParams.Add("@wocode", wocode);
                var dt1 = DapperHelper.selectdata(sql, dynamicParams);

                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    sql = @"insert into  TK_Wrk_EqpApsSum (wo_code,eqp_code,step_taid,p_date, t_date, qty,status)
                           select  min(wo_code),min(eqp_code),min(step_taid),min(time_start),max(time_end),sum(Alloc_Qty),'NEW' from TK_Wrk_EqpAps  
                           where wo_code=@wocode and eqp_code=''";
                    list.Add(new { str = sql, parm = new { wocode = wocode, eqp_code = dt1.Rows[i]["EQP_CODE"].ToString() } });
                }

                bool aa1 = DapperHelper.DoTransaction(list); //提交设备任务
                if (!aa1)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "生成设备任务汇总表出错，排产失败！";
                    mes.data = null;
                    return mes;
                }

                list.Clear();
                //写入设备任务(汇总表)用料  计划数量*子件基本用量*（1+损耗率）/母件基本用量
                sql = @"insert into TK_Wrk_EqpSum_Allo(m_id, seq, invcode, qty,wo_code,pn_type)
                        select A.id M_id, B.seq,B.materiel_code,(round(A.qty,2)*BE.Base_Quantity*(1+BE.LOSS_QUANTITY/100))/BM.quantity  qty,A.wo_code,B.materieltype  
                        from TK_Wrk_EqpApsSum A 
                        left join TK_Wrk_Allo B on A.Wo_Code= B.Wo_Code 
                        left join TBom_Deta BE  ON B.bom_id=BE.m_id and B.materiel_code=BE.smateriel_code
                        left join  TBom_Main BM on BE.m_Id=BM.id  where A.wo_code=@wocode";
                list.Add(new { str = sql, parm = new { wocode = wocode } });

                //更新 工序任务单的【瓶径工序】 排产预开工日期、排产预完工日期、状态：NEW===>SCHED 、已排产数量
                sql = @"update TK_Wrk_Step   set plan_startdate =convert(varchar(100),@plan_startdate,21),  plan_enddate =convert(varchar(100),@plan_enddate,21),  status = 'SCHED', sched_qty =@sched_qty where id =@id";
                list.Add(new { str = sql, parm = new { plan_startdate = minTime, plan_enddate = maxTime, sched_qty = n, id = ID } });

                //工单工序的“计划开机日期 = 瓶径工序的预开工日期 - 瓶径工序的前置日期）    主工单：计划完工日期 = 瓶径工序的预完工日期 + 瓶径工序的后置日期
                sql = @"update TK_Wrk_Step   set plan_startdate =convert(varchar(100),@plan_startdate,21),  plan_enddate =convert(varchar(100),@plan_enddate,21),  status = 'SCHED', sched_qty =@sched_qty where wo_code =@wocode and isbott='N'";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        plan_startdate = Convert.ToDateTime(minTime).AddDays(Convert.ToDouble(-nm)).ToString("yyyy-MM-dd"),
                        plan_enddate = Convert.ToDateTime(maxTime).AddDays(Convert.ToDouble(nn)).ToString("yyyy-MM-dd"),
                        sched_qty = n,
                        wocode = wocode
                    }
                });
                //主工单的“计划开机日期 = 瓶径工序的预开工日期 - 瓶径工序的前置日期）    主工单：计划完工日期 = 瓶径工序的预完工日期 + 瓶径工序的后置日期
                sql = @"update mes_tk_wrk_man set status='SCHED',plan_startdate =convert(varchar(100),@plan_startdate,21),  plan_enddate =convert(varchar(100),@plan_enddate,21), exchag='Y',allocfag='N' where wo_code =@wocode";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        plan_startdate = Convert.ToDateTime(minTime).AddDays(Convert.ToDouble(-nm)).ToString("yyyy-MM-dd"),
                        plan_enddate = Convert.ToDateTime(maxTime).AddDays(Convert.ToDouble(nn)).ToString("yyyy-MM-dd"),
                        sched_qty = n,
                        wocode = wocode
                    }
                });
                bool aa2 = DapperHelper.DoTransaction(list); //提交设备任务
                if (aa2)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "提交排程成功！";
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "提交排程失败！";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

    }
}