JTAPI.git - Gitblit

			@@ -1286,7 +1286,9 @@
			//删除设备点检标准关联点检项目子表
			sql = @"delete TEqpchk_Deta where eqpchk_main_code=@checkstand_code";
			list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });

			//删除设备点检标准关联设备表
			sql = @"delete TEqpchk_Eqp where eqpchk_main_code=@checkstand_code";
			list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
			bool aa = DapperHelper.DoTransaction(list);
			if (aa)
			{
			@@ -1351,9 +1353,8 @@
			sql = @"select * from(
			select AA.code,AA.name,'E' as type,(case when BB.eqpchkmain_code is null then 'N' else 'Y' end) flag,(case when AA.eqpchkmain_code is null then 'N' else 'Y' end) flage1
			from(
			select A.code,A.name,B.eqpchkmain_code
			select A.code,A.name,'' as eqpchkmain_code
			from TEqpInfo A
			left join TEqpchk_Eqp B on A.code=B.eqp_code
			where A.wksp_code=@wkspcode and A.is_delete<>'1'
			) as AA
			left join (
			@@ -1362,7 +1363,7 @@
			inner join TEqpchk_Eqp B on A.code=B.eqp_code
			where A.wksp_code=@wkspcode and B.eqpchkmain_code=@checkstand_code and A.is_delete<>'1'
			) as BB on AA.code=BB.code
			) as CC where case when flag ='N' and flage1 ='Y' then 0 else 1 end=1";
			) as CC "; //where case when flag ='N' and flage1 ='Y' then 0 else 1 end=1
			dynamicParams.Add("@checkstand_code", checkstand_code);
			dynamicParams.Add("@wkspcode", data.Rows[i]["WKSP_CODE"].ToString());
			var data0 = DapperHelper.selectdata(sql, dynamicParams);
			@@ -1723,6 +1724,9 @@
			//删除设备保养标准关联保养项目子表
			sql = @"delete TEqpmai_Deta where eapmai_code=@repairstand_code";
			list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
			//删除设备保养标准关联设备表
			sql = @"delete TEqpmai_Eqp where eapmai_code=@repairstand_code";
			list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });

			bool aa = DapperHelper.DoTransaction(list);
			if (aa)
			@@ -1789,9 +1793,8 @@
			sql = @"select * from(
			select AA.code,AA.name,'E' as type,(case when BB.eapmai_code is null then 'N' else 'Y' end) flag,(case when AA.eapmai_code is null then 'N' else 'Y' end) flage1
			from(
			select A.code,A.name,B.eapmai_code
			select A.code,A.name,'' as eapmai_code
			from TEqpInfo A
			left join TEqpmai_Eqp B on A.code=B.eqp_code
			where A.wksp_code=@wkspcode and A.is_delete<>'1'
			) as AA
			left join (
			@@ -1800,7 +1803,7 @@
			inner join TEqpmai_Eqp B on A.code=B.eqp_code
			where A.wksp_code=@wkspcode and B.eapmai_code=@repairstand_code and A.is_delete<>'1'
			) as BB on AA.code=BB.code
			) as CC where case when flag ='N' and flage1 ='Y' then 0 else 1 end=1";
			) as CC "; //where case when flag ='N' and flage1 ='Y' then 0 else 1 end=1
			dynamicParams.Add("@repairstand_code", repairstand_code);
			dynamicParams.Add("@wkspcode", data.Rows[i]["WKSP_CODE"].ToString());
			var data0 = DapperHelper.selectdata(sql, dynamicParams);
			@@ -2041,7 +2044,7 @@
			{
			search = "and 1=1 ";
			}
			search = search.Substring(3);//截取索引2后面的字符
			//search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select
			@@ -2251,7 +2254,7 @@
			{
			search = "and 1=1 ";
			}
			search = search.Substring(3);//截取索引2后面的字符
			//search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select
			@@ -2311,5 +2314,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=@repair_code";
			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=@repair_code 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
			}
			}