设备维修记录、工单排程

yl

2022-10-15 25f06fca74270032e39783189d0b5ea3cee9503a

 VueWebApi/DLL/DAL/DeviceManagerDAL.cs

@@ -2311,5 +2311,262 @@
            return mes;
        }
        #endregion



        #region[设备维修记录查询]
        public static ToMessage DeviceUpdateSearch(string wkshopcode, string eqpcode, string eqpname, string reportuser, string repairuser, string vrifcatuser, string reportopendate, string reportclosedate, string repairopendate, string repairclosedate, string vrifcatopendate, string vrifcatclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wksp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code like '%'+@eqpcode+'%' ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (eqpname != "" && eqpname != null)
                {
                    search += "and E.name like '%'+@eqpname+'%' ";
                    dynamicParams.Add("@eqpname", eqpname);
                }
                if (reportuser != "" && reportuser != null)
                {
                    search += "and B.request_person like '%'+@reportuser+'%' ";
                    dynamicParams.Add("@reportuser", reportuser);
                }
                if (repairuser != "" && repairuser != null)
                {
                    search += "and A.repair_person like '%'+@repairuser+'%' ";
                    dynamicParams.Add("@repairuser", repairuser);
                }
                if (vrifcatuser != "" && vrifcatuser != null)
                {
                    search += "and A.verify_person like '%'+@vrifcatuser+'%' ";
                    dynamicParams.Add("@vrifcatuser", vrifcatuser);
                }
                if (reportopendate != "" && reportopendate != null)
                {
                    search += "and B.request_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }
                if (repairopendate != "" && repairopendate != null)
                {
                    search += "and A.repair_date between @repairopendate and @repairclosedate ";
                    dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
                    dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
                }
                if (vrifcatopendate != "" && vrifcatopendate != null)
                {
                    search += "and A.verify_date between @vrifcatopendate and @vrifcatclosedate ";
                    dynamicParams.Add("@vrifcatopendate", vrifcatopendate + " 00:00:00");
                    dynamicParams.Add("@vrifcatclosedate", vrifcatclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.repair_code,A.wksp_code,T.org_name as wksp_name,A.eqp_code,E.name as eqp_name,B.request_person,B.request_date,
                            A.repair_person,A.repair_date,CAST(datediff(minute, B.request_date,A.repair_date)/60.0 AS decimal(9,1)) as cycleDate,
                            A.verify_person,A.verify_date,(case when A.verify_result='OK' then '通过' when  A.verify_result='NG' then '不通过' end)as verify_result 
                            from TEqp_Repair A
                            left join TEqp_RepairRequest B on A.source_wo=B.docu_code 
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TOrganization T on A.wksp_code=T.org_code
                            where T.description='W' " + 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 DeviceUpdateSubSearch(string repair_code)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            Dictionary<object, object> Dict = new Dictionary<object, object>();
            try
            {
                //获取设备报修申请数据
                sql = @"select B.docu_code,
                        (
                        case when B.source='A' then '设备点检'
                        when B.source='B' then '设备保养'
                        when B.source='C' then '安灯呼叫'
                        when B.source='C' then '手工创建' end
                        ) as source,B.failure_descript from TEqp_Repair A
                        left join TEqp_RepairRequest B on A.source_wo=B.docu_code
                        where A.repair_code=@repair_code";
                dynamicParams.Add("@repair_code", repair_code);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                DeviceReport report = new DeviceReport();
                report.faultsource = data.Rows[0]["source"].ToString();
                report.faultdescr = data.Rows[0]["failure_descript"].ToString();
                //获取设备报修申请图片数据
                sql = @"select M.img1url from TEqp_Repair A
                        left join TEqp_RepairRequest B on A.source_wo=B.docu_code
                        left join TEqp_RepairImage M on B.docu_code=M.source_wo
                        where A.repair_code=@repair_code and M.wo_type='REPA'";
                dynamicParams.Add("@repair_code", repair_code);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                report.faultimage = data0;
                Dict.Add("data1", report);

                //获取设备维修数据
                sql = @"select A.failure_descript,A.is_shutdown,A.repair_content,A.repair_part from TEqp_Repair A
                        left join TEqp_RepairRequest B on A.source_wo=B.docu_code
                        where A.repair_code='SBWX221010016'";
                dynamicParams.Add("@repair_code", repair_code);
                var data1 = DapperHelper.selectdata(sql, dynamicParams);
                DeviceRepair repair = new DeviceRepair();
                repair.faultdescr = data1.Rows[0]["failure_descript"].ToString();
                repair.isstoprepair = data1.Rows[0]["is_shutdown"].ToString();
                repair.repaircontent = data1.Rows[0]["repair_content"].ToString();
                repair.repairpart = data1.Rows[0]["repair_part"].ToString();
                //获取设备维修图片数据
                sql = @"select M.img1url,M.img2url from TEqp_Repair A
                        left join TEqp_RepairImage M on A.repair_code=M.source_wo
                        where A.repair_code='SBWX221010016' and M.wo_type='COMP'";
                dynamicParams.Add("@repair_code", repair_code);
                var data3 = DapperHelper.selectdata(sql, dynamicParams);
                repair.repairimage = data3;
                Dict.Add("data2", repair);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = Dict;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[设备维修记录导出]
        public static ToMessage DeviceUpdateOutExcel(string wkshopcode, string eqpcode, string eqpname, string reportuser, string repairuser, string vrifcatuser, string reportopendate, string reportclosedate, string repairopendate, string repairclosedate, string vrifcatopendate, string vrifcatclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wksp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code like '%'+@eqpcode+'%' ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (eqpname != "" && eqpname != null)
                {
                    search += "and E.name like '%'+@eqpname+'%' ";
                    dynamicParams.Add("@eqpname", eqpname);
                }
                if (reportuser != "" && reportuser != null)
                {
                    search += "and B.request_person like '%'+@reportuser+'%' ";
                    dynamicParams.Add("@reportuser", reportuser);
                }
                if (repairuser != "" && repairuser != null)
                {
                    search += "and A.repair_person like '%'+@repairuser+'%' ";
                    dynamicParams.Add("@repairuser", repairuser);
                }
                if (vrifcatuser != "" && vrifcatuser != null)
                {
                    search += "and A.verify_person like '%'+@vrifcatuser+'%' ";
                    dynamicParams.Add("@vrifcatuser", vrifcatuser);
                }
                if (reportopendate != "" && reportopendate != null)
                {
                    search += "and B.request_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }
                if (repairopendate != "" && repairopendate != null)
                {
                    search += "and A.repair_date between @repairopendate and @repairclosedate ";
                    dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
                    dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
                }
                if (vrifcatopendate != "" && vrifcatopendate != null)
                {
                    search += "and A.verify_date between @vrifcatopendate and @vrifcatclosedate ";
                    dynamicParams.Add("@vrifcatopendate", vrifcatopendate + " 00:00:00");
                    dynamicParams.Add("@vrifcatclosedate", vrifcatclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select 
                            A.repair_code as '维修单号',
                            T.org_name as '所属车间',
                            A.eqp_code as '设备编码',
                            E.name as '设备名称',
                            B.request_person as '报修人员',
                            B.request_date as '报修时间',
                            A.repair_person as '维修人员',
                            A.repair_date as '维修时间', 
                            CAST(datediff(minute, B.request_date,A.repair_date)/60.0 AS decimal(9,1))  as '维修时长(小时)',
                            A.verify_person as '验证人员',
                            A.verify_date as '验证时间',
                            (case when A.verify_result='OK' then '通过' when  A.verify_result='NG' then '不通过' end)as '验证结果' 
                            from TEqp_Repair A
                            left join TEqp_RepairRequest B on A.source_wo=B.docu_code 
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TOrganization T on A.wksp_code=T.org_code
                            where T.description='W'" + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "设备维修记录");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}