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<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
|
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<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 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<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
|
}
|
}
|
