LKApi.git - Gitblit

			@@ -221,12 +221,17 @@


			#region[班组工资报表记录查询]
			public static ToMessage GroupSalaryReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate, int startNum, int endNum, string prop, string order)
			public static ToMessage GroupSalaryReportSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate,string rejectstepcode, int startNum, int endNum, string prop, string order)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (compute == "last") //末道工序
			{
			search += "and P.isend=@isend ";
			dynamicParams.Add("@isend", "Y");
			}
			if (wocode != "" && wocode != null)
			{
			search += "and A.wo_code like '%'+@wocode+'%' ";
			@@ -268,26 +273,31 @@
			dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
			dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
			}

			if (rejectstepcode != "" && rejectstepcode != null)
			{
			string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
			search += "and A.step_code not in @s1";
			dynamicParams.Add("@s1", s1);
			}
			if (search == "")
			{
			search = "and 1=1 ";
			}
			search = search.Substring(3);//截取索引2后面的字符
			//search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select distinct A.id,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,A.good_qty,
			isnull(S.unprice,0) as unprice,A.good_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,A.lm_date,B.report_date
			from TK_Wrk_Record A
			inner join TK_Wrk_RecordSub B on A.id=B.m_id
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
			left join TK_Wrk_Man K on A.wo_code=K.wo_code
			left join TGroup G on B.usergroup_code=G.group_code
			left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
			left join TMateriel_Info M on A.materiel_code=M.partcode
			left join TStep T on A.step_code=T.stepcode
			left join TUser U on A.lm_user=U.usercode
			where " + search;
			where G.group_code<>'' " + search;
			var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
			mes.code = "200";
			mes.Message = "查询成功!";
			@@ -334,12 +344,17 @@
			#endregion

			#region[班组工资报表记录导出]
			public static ToMessage GroupSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate)
			public static ToMessage GroupSalaryReportExcelSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate,string rejectstepcode)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (compute == "last") //末道工序
			{
			search += "and P.isend=@isend ";
			dynamicParams.Add("@isend", "Y");
			}
			if (wocode != "" && wocode != null)
			{
			search += "and A.wo_code like '%'+@wocode+'%' ";
			@@ -381,12 +396,17 @@
			dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
			dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
			}

			if (rejectstepcode != "" && rejectstepcode != null)
			{
			string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
			search += "and A.step_code not in @s1";
			dynamicParams.Add("@s1", s1);
			}
			if (search == "")
			{
			search = "and 1=1 ";
			}
			search = search.Substring(3);//截取索引2后面的字符
			//search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select distinct A.wo_code as 工单编号,M.partcode as 产品编码,M.partname as 产品名称,M.partspec as 产品规格,
			@@ -394,14 +414,14 @@
			A.good_qty as 报工数量,isnull(S.unprice,0) as 工序单价,A.good_qty*isnull(S.unprice,0) as 计件工资,U.username as 操作人员,A.lm_date as 操作时间,B.report_date as 报工时间
			from TK_Wrk_Record A
			inner join TK_Wrk_RecordSub B on A.id=B.m_id
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
			left join TK_Wrk_Man K on A.wo_code=K.wo_code
			left join TGroup G on B.usergroup_code=G.group_code
			left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
			left join TMateriel_Info M on A.materiel_code=M.partcode
			left join TStep T on A.step_code=T.stepcode
			left join TUser U on A.lm_user=U.usercode
			where " + search;
			where G.group_code<>'' " + search;
			DataTable data = DapperHelper.selectdata(sql, dynamicParams);
			data.TableName = "Table"; //设置DataTable的名称
			string msg = DownLoad.DataTableToExcel(data, "班组计件工资报表");
			@@ -423,98 +443,17 @@


			#region[人员工资明细报表]
			public static ToMessage PeopleSalaryReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
			public static ToMessage PeopleSalaryReportSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate,string rejectstepcode, int startNum, int endNum, string prop, string order)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (wocode != "" && wocode != null)
			if (compute == "last") //末道工序
			{
			search += "and A.wo_code like '%'+@wocode+'%' ";
			dynamicParams.Add("@wocode", wocode);
			search += "and AA.isend=@isend ";
			dynamicParams.Add("@isend", "Y");
			}
			if (partcode != "" && partcode != null)
			{
			search += "and M.partcode like '%'+@partcode+'%' ";
			dynamicParams.Add("@partcode", partcode);
			}
			if (partname != "" && partname != null)
			{
			search += "and M.partname like '%'+@partname+'%' ";
			dynamicParams.Add("@partname", partname);
			}
			if (partspec != "" && partspec != null)
			{
			search += "and M.partspec like '%'+@partspec+'%' ";
			dynamicParams.Add("@partspec", partspec);
			}
			if (stepname != "" && stepname != null)
			{
			search += "and T.stepname like '%'+@stepname+'%' ";
			dynamicParams.Add("@stepname", stepname);
			}
			if (groupcode != "" && groupcode != null)
			{
			search += "and G.group_code=@groupcode ";
			dynamicParams.Add("@groupcode", groupcode);
			}
			if (reportname != "" && reportname != null)
			{
			search += "and U.username like '%'+@reportname+'%' ";
			dynamicParams.Add("@reportname", reportname);
			}
			if (reportopendate != "" && reportopendate != null)
			{
			search += "and B.report_date between @reportopendate and @reportclosedate ";
			dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
			dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
			}

			if (search == "")
			{
			search = "and 1=1 ";
			}
			search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,B.report_qty
			,isnull(S.unprice,0) as unprice,B.report_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,B.report_date,
			(select distinct count(*) from TK_Wrk_RecordSub S where S.m_id=B.m_id) as colum
			from TK_Wrk_Record A
			inner join TK_Wrk_RecordSub B on A.id=B.m_id
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
			left join TK_Wrk_Man K on A.wo_code=K.wo_code
			left join TGroup G on B.usergroup_code=G.group_code
			left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
			left join TMateriel_Info M on A.materiel_code=M.partcode
			left join TStep T on A.step_code=T.stepcode
			left join TUser U on B.report_person=U.usercode
			where " + 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 PeopleSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (wocode != "" && wocode != null)
			{
			search += "and AA.wo_code like '%'+@wocode+'%' ";
			@@ -556,7 +495,12 @@
			dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
			dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
			}

			if (rejectstepcode != "" && rejectstepcode != null)
			{
			string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
			search += "and AA.stepcode not in @s1";
			dynamicParams.Add("@s1", s1);
			}
			if (search == "")
			{
			search = "and 1=1 ";
			@@ -564,17 +508,117 @@
			search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select AA.wo_code as 工单编号,AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.stepcode as 工序编码,AA.stepname as 工序名称,
			AA.task_qty as 任务数量,AA.group_code as 生产班组编码,AA.group_name as 生产班组名称,AA.report_qty as 报工数量,AA.unprice as 工序单价,
			AA.moneys/colum as 计件工资,AA.username as 报工人员,AA.report_date as 报工时间,colum as 班组报工人数
			var sql = @"select AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,
			AA.task_qty,AA.group_code,AA.group_name,AA.report_qty,AA.unprice,AA.ratio,AA.isend,
			AA.moneys as usermoney,AA.username,AA.report_date
			from (
			select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
			A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,
			B.report_qty*isnull(S.unprice,0) as moneys,U.username,B.report_date,
			(select distinct count(*) from TK_Wrk_RecordSub S where S.m_id=B.m_id) as colum
			A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,B.ratio,P.isend,
			(B.report_qtyisnull(S.unprice,0))(B.ratio/100) as moneys,U.username,B.report_date
			from TK_Wrk_Record A
			inner join TK_Wrk_RecordSub B on A.id=B.m_id
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
			left join TK_Wrk_Man K on A.wo_code=K.wo_code
			left join TGroup G on B.usergroup_code=G.group_code
			left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
			left join TMateriel_Info M on A.materiel_code=M.partcode
			left join TStep T on A.step_code=T.stepcode
			left join TUser U on B.report_person=U.usercode
			) as AA
			where " + 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 PeopleSalaryReportExcelSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate,string rejectstepcode)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (compute == "last") //末道工序
			{
			search += "and AA.isend=@isend ";
			dynamicParams.Add("@isend", "Y");
			}
			if (wocode != "" && wocode != null)
			{
			search += "and AA.wo_code like '%'+@wocode+'%' ";
			dynamicParams.Add("@wocode", wocode);
			}
			if (partcode != "" && partcode != null)
			{
			search += "and AA.partcode like '%'+@partcode+'%' ";
			dynamicParams.Add("@partcode", partcode);
			}
			if (partname != "" && partname != null)
			{
			search += "and AA.partname like '%'+@partname+'%' ";
			dynamicParams.Add("@partname", partname);
			}
			if (partspec != "" && partspec != null)
			{
			search += "and AA.partspec like '%'+@partspec+'%' ";
			dynamicParams.Add("@partspec", partspec);
			}
			if (stepname != "" && stepname != null)
			{
			search += "and AA.stepname like '%'+@stepname+'%' ";
			dynamicParams.Add("@stepname", stepname);
			}
			if (groupcode != "" && groupcode != null)
			{
			search += "and AA.group_code=@groupcode ";
			dynamicParams.Add("@groupcode", groupcode);
			}
			if (reportname != "" && reportname != null)
			{
			search += "and AA.username like '%'+@reportname+'%' ";
			dynamicParams.Add("@reportname", reportname);
			}
			if (reportopendate != "" && reportopendate != null)
			{
			search += "and AA.report_date between @reportopendate and @reportclosedate ";
			dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
			dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
			}
			if (rejectstepcode != "" && rejectstepcode != null)
			{
			string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
			search += "and AA.stepcode not in @s1";
			dynamicParams.Add("@s1", s1);
			}
			if (search == "")
			{
			search = "and 1=1 ";
			}
			search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select AA.wo_code as 工单编号,AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.stepcode as 工序编码,AA.stepname as 工序名称,AA.isend as 是否末道工序,
			AA.task_qty as 任务数量,AA.group_code as 生产班组编码,AA.group_name as 生产班组名称,AA.report_qty as 报工数量,AA.unprice as 工序单价,AA.ratio as 分配比例,
			AA.moneys as 计件工资,AA.username as 报工人员,AA.report_date as 报工时间
			from (
			select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
			A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,B.ratio,P.isend,
			(B.report_qtyisnull(S.unprice,0))(B.ratio/100) as moneys,U.username,B.report_date
			from TK_Wrk_Record A
			inner join TK_Wrk_RecordSub B on A.id=B.m_id
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
			left join TK_Wrk_Man K on A.wo_code=K.wo_code
			left join TGroup G on B.usergroup_code=G.group_code
			left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode