using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.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 ProductionScheduleReportSearch(string status, string wocode,string wkshopcode,string wkshopname, string partcode, string partname, string partspec, string opendate, string closedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (status != "" && status != null)
|
{
|
switch (status)
|
{
|
case "START":
|
search += "and AA.status='START' ";
|
break;
|
case "CLOSED":
|
search += "and AA.status='CLOSED' ";
|
break;
|
default:
|
search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
|
break;
|
}
|
}
|
if (wocode != "" && wocode != null)
|
{
|
search += "and AA.wo_code like '%'+@wocode+'%' ";
|
dynamicParams.Add("@wocode", wocode);
|
}
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code like '%'+@wkshopcode+'%' ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (wkshopname != "" && wkshopname != null)
|
{
|
search += "and AA.wkshp_name like '%'+@wkshopname+'%' ";
|
dynamicParams.Add("@wkshopname", wkshopname);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and AA.partcode like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
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 (opendate != "" && opendate != null)
|
{
|
search += "and AA.lm_date between @opendate and @closedate ";
|
dynamicParams.Add("@opendate", opendate + " 00:00:00");
|
dynamicParams.Add("@closedate", closedate + " 23:59:59");
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select AA.saleOrderCode,AA.m_po,AA.wo_code,AA.wkshp_code,AA.wkshp_name,
|
(case when AA.status='START' then '执行中' when AA.status='CLOSED' then '已完成' else '未开始' end) as status,
|
AA.lm_date,AA.partcode,AA.partname,AA.partspec,AA.plan_qty,left(AA.concat_name,len(concat_name)-1) as concat_name from
|
(
|
select E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.torg_name as wkshp_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,m.plan_qty,
|
(select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' from TK_Wrk_Step n
|
inner join TStep S on n.step_code=S.stepcode
|
where n.wo_code=m.wo_code for xml path('')) as concat_name
|
from TK_Wrk_Step m
|
inner join TK_Wrk_Man W on m.wo_code=W.wo_code
|
left join TKimp_Ewo E on W.m_po=E.wo
|
inner join TMateriel_Info P on W.materiel_code=p.partcode
|
left join TOrganization F on W.wkshp_code=F.torg_code
|
group by E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,m.plan_qty
|
) 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 ProductionScheduleReportExcelSearch(string status, string wocode, string wkshopcode, string wkshopname, string partcode, string partname, string partspec, string opendate, string closedate)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (status != "" && status != null)
|
{
|
switch (status)
|
{
|
case "START":
|
search += "and AA.status='START' ";
|
break;
|
case "CLOSED":
|
search += "and AA.status='CLOSED' ";
|
break;
|
default:
|
search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
|
break;
|
}
|
}
|
if (wocode != "" && wocode != null)
|
{
|
search += "and AA.wo_code like '%'+@wocode+'%' ";
|
dynamicParams.Add("@wocode", wocode);
|
}
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code like '%'+@wkshopcode+'%' ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (wkshopname != "" && wkshopname != null)
|
{
|
search += "and AA.wkshp_name like '%'+@wkshopname+'%' ";
|
dynamicParams.Add("@wkshopname", wkshopname);
|
}
|
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 (opendate != "" && opendate != null)
|
{
|
search += "and AA.lm_date between @opendate and @closedate ";
|
dynamicParams.Add("@opendate", opendate + " 00:00:00");
|
dynamicParams.Add("@closedate", closedate + " 23:59:59");
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select AA.saleOrderCode as ERP源单号,AA.m_po as ERP生产订单,AA.wo_code as 生产工单号,AA.wkshp_code as 车间编码,AA.wkshp_name as 车间名称,
|
(case when AA.status='START' then '执行中' when AA.status='CLOSED' then '已完成' else '未开始' end) as 工单状态,
|
AA.lm_date as 单据日期,AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.plan_qty as 任务数量,left(AA.concat_name,len(concat_name)-1) as 生产进度 from
|
(
|
select E.saleOrderCode,W.m_po,m.wo_code,W.status,W.lm_date,W.wkshp_code,F.torg_name as wkshp_name,P.partcode,P.partname,P.partspec,m.plan_qty,
|
(select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' from TK_Wrk_Step n
|
inner join TStep S on n.step_code=S.stepcode
|
where n.wo_code=m.wo_code for xml path('')) as concat_name
|
from TK_Wrk_Step m
|
inner join TK_Wrk_Man W on m.wo_code=W.wo_code
|
left join TKimp_Ewo E on W.m_po=E.wo
|
inner join TMateriel_Info P on W.materiel_code=p.partcode
|
left join TOrganization F on W.wkshp_code=F.torg_code
|
group by E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,m.plan_qty
|
) AA
|
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
|
}
|
}
|
