using Dapper; using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; using VueWebApi.Tools; namespace VueWebApi.DLL.DAL { public class ReportManagerDAL { public static DataTable dt; //定义全局变量dt public static bool res; //定义全局变量dt public static ToMessage mes = new ToMessage(); //定义全局返回信息对象 public static string strProcName = ""; //定义全局sql变量 public static List listStr = new List(); //定义全局参数集合 public static SqlParameter[] parameters; //定义全局SqlParameter参数数组 #region[委外报表记录查询] public static ToMessage OutSourceReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string suppername, string type, string receivopendate, string receivclosedate, int startNum, int endNum, string prop, string order) { var dynamicParams = new DynamicParameters(); string search = ""; try { if (wocode != "" && wocode != null) { search += "and A.wo_code like '%'+@wocode+'%' "; dynamicParams.Add("@wocode", wocode); } 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 S.stepname like '%'+@stepname+'%' "; dynamicParams.Add("@stepname", stepname); } if (suppername != "" && suppername != null) { search += "and C.name like '%'+@suppername+'%' "; dynamicParams.Add("@suppername", suppername); } if (type != "" && type != null) { search += "and A.style=@type "; dynamicParams.Add("@type", type); switch (type) { case "F": //发料 if (receivopendate != "" && receivopendate != null) { search += "and B.out_time between @receivopendate and @receivclosedate "; dynamicParams.Add("@receivopendate", receivopendate + " 00:00:00"); dynamicParams.Add("@receivclosedate", receivclosedate + " 23:59:59"); } break; case "S": //收料 if (receivopendate != "" && receivopendate != null) { search += "and B.in_time between @receivopendate and @receivclosedate "; dynamicParams.Add("@receivopendate", receivopendate + " 00:00:00"); dynamicParams.Add("@receivclosedate", receivclosedate + " 23:59:59"); } break; default: break; } } if (search == "") { search = "and 1=1 "; } search = search.Substring(3);//截取索引2后面的字符 // --------------查询指定数据-------------- var total = 0; //总条数 var sql = @"select A.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,C.code,C.name,(case when A.style='F' then '发料' when A.style='S' then '收料' end) as style, A.fqty,A.sqty,A.ng_qty,A.bad_qty,U.username as out_person,B.out_time,T.username as in_person,B.in_time from TK_Wrk_OutRecord A inner join TK_Wrk_OutRecordSub B on A.id=B.m_id left join TMateriel_Info M on A.materiel_code=M.partcode left join TStep S on A.step_code=S.stepcode left join TCustomer C on A.wx_code=C.code left join TUser U on B.out_person=U.usercode left join TUser T on B.in_person=T.usercode where " + search; var data = DapperHelper.GetPageList(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 OutSourceReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string suppername, string type, string receivopendate, string receivclosedate) { var dynamicParams = new DynamicParameters(); string search = ""; try { if (wocode != "" && wocode != null) { search += "and A.wo_code like '%'+@wocode+'%' "; dynamicParams.Add("@wocode", wocode); } 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 S.stepname like '%'+@stepname+'%' "; dynamicParams.Add("@stepname", stepname); } if (suppername != "" && suppername != null) { search += "and C.name like '%'+@suppername+'%' "; dynamicParams.Add("@suppername", suppername); } if (type != "" && type != null) { search += "and A.style=@type "; dynamicParams.Add("@type", type); switch (type) { case "F": //发料 if (receivopendate != "" && receivopendate != null) { search += "and B.out_time between @receivopendate and @receivclosedate "; dynamicParams.Add("@receivopendate", receivopendate + " 00:00:00"); dynamicParams.Add("@receivclosedate", receivclosedate + " 23:59:59"); } break; case "S": //收料 if (receivopendate != "" && receivopendate != null) { search += "and B.in_time between @receivopendate and @receivclosedate "; dynamicParams.Add("@receivopendate", receivopendate + " 00:00:00"); dynamicParams.Add("@receivclosedate", receivclosedate + " 23:59:59"); } break; default: break; } } if (search == "") { search = "and 1=1 "; } search = search.Substring(3);//截取索引2后面的字符 // --------------查询指定数据-------------- var total = 0; //总条数 var sql = @"select A.wo_code as '工单编号',M.partcode as '产品编码',M.partname as '产品名称',M.partspec as '产品规格',S.stepcode as '工序编码',S.stepname as '工序名称', C.code as '外协供方编码',C.name as '外协供方名称',(case when A.style='F' then '发料' when A.style='S' then '收料' end) as '操作类型', A.fqty as '发料数量',A.sqty as '收料数量',A.ng_qty as '不良数量',A.bad_qty as '报废数量',U.username as '发料人员',B.out_time as '发料时间', T.username as '收料人员',B.in_time as '收料时间' from TK_Wrk_OutRecord A inner join TK_Wrk_OutRecordSub B on A.id=B.m_id left join TMateriel_Info M on A.materiel_code=M.partcode left join TStep S on A.step_code=S.stepcode left join TCustomer C on A.wx_code=C.code left join TUser U on B.out_person=U.usercode left join TUser T on B.in_person=T.usercode where " + 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 #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) { var dynamicParams = new DynamicParameters(); string search = ""; try { if (wocode != "" && wocode != null) { search += "and A.wo_code like '%'+@wocode+'%' "; dynamicParams.Add("@wocode", wocode); } 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 (username != "" && username != null) { search += "and U.username like '%'+@username+'%' "; dynamicParams.Add("@username", username); } if (operopendate != "" && operopendate != null) { search += "and A.lm_date between @operopendate and @operclosedate "; dynamicParams.Add("@operopendate", operopendate + " 00:00:00"); dynamicParams.Add("@operclosedate", operclosedate + " 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,A.good_qty,isnull(S.unprice,0) as unprice,U.username as lm_user,A.lm_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' 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; var data = DapperHelper.GetPageList(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 } }