using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Models;
|
using VueWebCoreApi.Models.InventoryModel;
|
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参数数组
|
public static string ErpPath = AppSetting.GetAppSetting("ErpPath"); //获取ERP配置
|
|
|
#region[生产进度报表]
|
public static ToMessage ProductionScheduleReportSearch(string wkshopcode, string status, string socode, string wocode, 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 (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (status != "" && status != null)
|
{
|
switch (status)
|
{
|
case "START":
|
search += "and AA.status='执行中' ";
|
break;
|
case "CLOSED":
|
search += "and AA.status='已完成' ";
|
break;
|
default:
|
search += "and AA.status='未开始' ";
|
break;
|
}
|
}
|
if (socode != "" && socode != null)
|
{
|
search += "and AA.saleOrderCode like '%'+@socode+'%' ";
|
dynamicParams.Add("@socode", socode);
|
}
|
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 (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");
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select top 100 percent AA.saleOrderCode,AA.m_po,AA.wkshp_code,AA.wkshp_name,AA.wo_code,AA.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 W.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.torg_name as wkshp_name,
|
case when W.status='START' then '执行中' when W.status='CLOSED' then '已完成' else '未开始' end as status,
|
W.lm_date,
|
P.partcode,P.partname,P.partspec,W.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=W.wo_code for xml path('')
|
) as concat_name
|
from TK_Wrk_Man W
|
left join TMateriel_Info P on W.materiel_code=p.partcode
|
left join TOrganization F on W.wkshp_code=F.torg_code
|
group by W.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,W.plan_qty
|
) as AA where AA.concat_name is not null " + 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 wkshopcode, string status, string socode, string wocode, string partcode, string partname, string partspec, string opendate, string closedate)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (status != "" && status != null)
|
{
|
switch (status)
|
{
|
case "START":
|
search += "and AA.status='执行中' ";
|
break;
|
case "CLOSED":
|
search += "and AA.status='已完成' ";
|
break;
|
default:
|
search += "and AA.status='未开始' ";
|
break;
|
}
|
}
|
if (socode != "" && socode != null)
|
{
|
search += "and AA.saleOrderCode like '%'+@socode+'%' ";
|
dynamicParams.Add("@socode", socode);
|
}
|
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 (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");
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
|
var sql = @"select top 100 percent AA.saleOrderCode as 销售订单号,AA.m_po as 生产订单号,AA.wkshp_code as 车间编码,AA.wkshp_name as 车间名称,AA.wo_code as 生产工单号,
|
AA.status 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 W.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.torg_name as wkshp_name,
|
case when W.status='START' then '执行中' when W.status='CLOSED' then '已完成' else '未开始' end as status,
|
W.lm_date,
|
P.partcode,P.partname,P.partspec,W.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=W.wo_code for xml path('')
|
) as concat_name
|
from TK_Wrk_Man W
|
left join TMateriel_Info P on W.materiel_code=p.partcode
|
left join TOrganization F on W.wkshp_code=F.torg_code
|
group by W.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,W.plan_qty
|
) as AA
|
where where AA.concat_name is not null " + 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 compute, string wkshopcode, string socode, 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 (compute == "last") //末道工序
|
{
|
search += "and P.isend=@isend ";
|
dynamicParams.Add("@isend", "Y");
|
}
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and K.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (socode != "" && socode != null)
|
{
|
search += "and K.saleOrderCode like '%'+@socode+'%' ";
|
dynamicParams.Add("@socode", socode);
|
}
|
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.usergroupcode=@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 B.report_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.id,K.wkshp_code,F.torg_name as wkshp_name,K.saleOrderCode,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.usergroupcode,G.usergroupname,A.good_qty,
|
isnull(A.step_price,0) as unprice,A.good_qty*isnull(A.step_price,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
|
left join TK_Wrk_Man K on A.wo_code=K.wo_code
|
left join TGroup G on B.usergroup_code=G.usergroupcode
|
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
|
left join TOrganization F on K.wkshp_code=F.torg_code
|
where B.usergroup_code<>'' and A.style='B' " + 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 GroupSalaryReportSearchUser(string id)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取报工人员
|
sql = @"select S.report_person as usercode,
|
STUFF((SELECT ',' + U.username
|
FROM TUser U
|
WHERE CHARINDEX(',' + U.usercode + ',', ',' + S.report_person + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS username
|
from TK_Wrk_RecordSub S
|
where m_id=@id";
|
dynamicParams.Add("@id", id);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[班组工资报表记录导出]
|
public static ToMessage GroupSalaryReportExcelSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (compute == "last") //末道工序
|
{
|
search += "and P.isend=@isend ";
|
dynamicParams.Add("@isend", "Y");
|
}
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and K.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (socode != "" && socode != null)
|
{
|
search += "and K.saleOrderCode like '%'+@socode+'%' ";
|
dynamicParams.Add("@socode", socode);
|
}
|
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.usergroupcode=@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 B.report_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 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select distinct K.wkshp_code as 车间编码,F.torg_name as 车间名称,K.saleOrderCode as '销售单号',A.wo_code as 工单编号,M.partcode as 产品编码,M.partname as 产品名称,M.partspec as 产品规格,
|
T.stepcode as 工序编码,T.stepname as 工序名称,A.task_qty as 任务数量,G.usergroupcode as 班组编码,G.usergroupname as 班组名称,
|
A.good_qty as 报工数量,isnull(A.step_price,0) as 工序单价,A.good_qty*isnull(A.step_price,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
|
left join TK_Wrk_Man K on A.wo_code=K.wo_code
|
left join TGroup G on B.usergroup_code=G.usergroupcode
|
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
|
left join TOrganization F on K.wkshp_code=F.torg_code
|
where B.usergroup_code<>'' and A.style='B' " + 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 PeopleSalaryReportSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (compute == "last") //末道工序
|
{
|
search += "and AA.isend=@isend ";
|
dynamicParams.Add("@isend", "Y");
|
}
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (socode != "" && socode != null)
|
{
|
search += "and AA.saleOrderCode like '%'+@socode+'%' ";
|
dynamicParams.Add("@socode", socode);
|
}
|
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 (stepcode != "" && stepcode != null)
|
{
|
search += "and AA.step_code like '%'+@stepcode+'%' ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
if (stepname != "" && stepname != null)
|
{
|
search += "and AA.stepname like '%'+@stepname+'%' ";
|
dynamicParams.Add("@stepname", stepname);
|
}
|
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 (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select * from(
|
select M.wkshp_code,F.torg_name as wkshp_name,M.saleOrderCode,A.wo_code,A.materiel_code as partcode,P.partname,P.partspec,A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,K.isend,
|
A.task_qty,B.report_qty,isnull(A.step_price,0) as step_price,(B.report_qty*isnull(A.step_price,0)) as moneys,
|
B.report_person as usercode,
|
STUFF((SELECT ',' + U.username
|
FROM TUser U
|
WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.report_person + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS username,
|
B.report_date
|
from TK_Wrk_Record A
|
inner join TK_Wrk_RecordSub B on A.id=B.m_id
|
left join TK_Wrk_Man M on A.wo_code=M.wo_code
|
left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
|
left join TStep S on A.step_code=S.stepcode
|
left join TMateriel_Info P on A.materiel_code=P.partcode
|
left join TOrganization F on M.wkshp_code=F.torg_code
|
where A.style='B' and B.style='B' and A.verify='Y' and B.usergroup_code=''
|
union all
|
select M.wkshp_code,F.torg_name as wkshp_name,M.saleOrderCode,A.wo_code,A.materiel_code as partcode,P.partname,P.partspec,A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,K.isend,
|
M.plan_qty as task_qty,B.sqty as report_qty,isnull(A.step_price,0) as step_price,(B.sqty*isnull(A.step_price,0)) as moneys,
|
B.in_person as usercode,
|
STUFF((SELECT ',' + U.username
|
FROM TUser U
|
WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.in_person + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS username,
|
B.in_time as report_date
|
from TK_Wrk_OutRecord A
|
inner join TK_Wrk_OutRecordSub B on A.id = B.m_id
|
left join TK_Wrk_Man M on A.wo_code = M.wo_code
|
left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
|
left join TStep S on A.step_code = S.stepcode
|
left join TMateriel_Info P on A.materiel_code = P.partcode
|
left join TOrganization F on M.wkshp_code=F.torg_code
|
where A.style = 'S' and B.style = 'S' and A.verify='Y'
|
) 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 PeopleSalaryReportSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (compute == "last") //末道工序
|
{
|
search += "and AA.isend=@isend ";
|
dynamicParams.Add("@isend", "Y");
|
}
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (socode != "" && socode != null)
|
{
|
search += "and AA.saleOrderCode like '%'+@socode+'%' ";
|
dynamicParams.Add("@socode", socode);
|
}
|
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 (stepcode != "" && stepcode != null)
|
{
|
search += "and AA.step_code like '%'+@stepcode+'%' ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
if (stepname != "" && stepname != null)
|
{
|
search += "and AA.stepname like '%'+@stepname+'%' ";
|
dynamicParams.Add("@stepname", stepname);
|
}
|
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 (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select AA.wkshp_code as 车间编码,AA.wkshp_name as 车间名称,AA.saleOrderCode as '销售单号',AA.wo_code as '工单号',AA.partcode as '产品编码',AA.partname '产品名称',AA.partspec as '规格型号',AA.step_seq as '工序序号',
|
AA.step_code as '工序编码',AA.stepname as '工序名称',(case when AA.steptype='Z' then '自制' else '外协' end) as '工序类型',AA.task_qty as '任务数量',AA.report_qty as '报工数量',
|
AA.step_price as '工序单价',AA.moneys as '计件工资',AA.usercode as '报工人员编号',AA.username as '报工人员名称',AA.report_date as '报工时间'
|
from(
|
select M.wkshp_code,F.torg_name as wkshp_name,M.saleOrderCode,A.wo_code,A.materiel_code as partcode,P.partname,P.partspec,A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,K.isend,
|
A.task_qty,B.report_qty,isnull(A.step_price,0) as step_price,(B.report_qty*isnull(A.step_price,0)) as moneys,
|
B.report_person as usercode,
|
STUFF((SELECT ',' + U.username
|
FROM TUser U
|
WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.report_person + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS username,
|
B.report_date
|
from TK_Wrk_Record A
|
inner join TK_Wrk_RecordSub B on A.id=B.m_id
|
left join TK_Wrk_Man M on A.wo_code=M.wo_code
|
left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
|
left join TStep S on A.step_code=S.stepcode
|
left join TMateriel_Info P on A.materiel_code=P.partcode
|
left join TOrganization F on M.wkshp_code=F.torg_code
|
where A.style='B' and B.style='B' and A.verify='Y' and B.usergroup_code=''
|
union all
|
select M.wkshp_code,F.torg_name as wkshp_name,M.saleOrderCode,A.wo_code,A.materiel_code as partcode,P.partname,P.partspec,A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,K.isend,
|
M.plan_qty as task_qty,B.sqty as report_qty,isnull(A.step_price,0) as step_price,(B.sqty*isnull(A.step_price,0)) as moneys,
|
B.in_person as usercode,
|
STUFF((SELECT ',' + U.username
|
FROM TUser U
|
WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.in_person + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS username,
|
B.in_time as report_date
|
from TK_Wrk_OutRecord A
|
inner join TK_Wrk_OutRecordSub B on A.id = B.m_id
|
left join TK_Wrk_Man M on A.wo_code = M.wo_code
|
left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
|
left join TStep S on A.step_code = S.stepcode
|
left join TMateriel_Info P on A.materiel_code = P.partcode
|
left join TOrganization F on M.wkshp_code=F.torg_code
|
where A.style = 'S' and B.style = 'S' and A.verify='Y'
|
) as 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
|
|
|
#region[委外报表记录查询]
|
public static ToMessage OutSourceReportSearch(string wkshopcode, 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 (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and P.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
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 P.wkshp_code,G.torg_name as wkshp_name,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.laborbad_qty,A.materielbad_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 TK_Wrk_Man P on A.wo_code=P.wo_code
|
left join TOrganization G on P.wkshp_code=G.torg_code
|
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 wkshopcode, 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 (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and P.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
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 P.wkshp_code as 车间编码,G.torg_name as 车间名称,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.laborbad_qty as '工废数量',A.materielbad_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 TK_Wrk_Man P on A.wo_code=P.wo_code
|
left join TOrganization G on P.wkshp_code=G.torg_code
|
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 DefectDetailsReportSearch(string wkshopcode, string wocode, string partcode, string partname, string partspec, string stepname, string defectcode, string defectname, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
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 (defectcode != "" && defectcode != null)
|
{
|
search += "and AA.defect_code like '%'+@defectcode+'%' ";
|
dynamicParams.Add("@defectcode", defectcode);
|
}
|
if (defectname != "" && defectname != null)
|
{
|
search += "and AA.defect_name like '%'+@defectname+'%' ";
|
dynamicParams.Add("@defectname", defectname);
|
}
|
if (reportname != "" && reportname != null)
|
{
|
search += "and AA.lm_user like '%'+@reportname+'%' ";
|
dynamicParams.Add("@reportname", reportname);
|
}
|
if (reportopendate != "" && reportopendate != null)
|
{
|
search += "and AA.lm_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 * from(
|
select W.wkshp_code,G.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
|
A.defect_code,
|
STUFF((SELECT ',' + F.name
|
FROM TDefect F
|
WHERE CHARINDEX(',' + F.code + ',', ',' + A.defect_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS defect_name,
|
A.style,U.username as lm_user,A.lm_date
|
from CSR_WorkRecord_Defect A
|
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 W on A.wo_code=W.wo_code
|
left join TOrganization G on W.wkshp_code=G.torg_code
|
left join TMateriel_Info M on A.partnumber=M.partcode
|
left join TStep T on A.step_code=T.stepcode
|
left join TUser U on A.lm_user=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 DefectDetailsReportExcelSearch(string wkshopcode, string wocode, string partcode, string partname, string partspec, string stepname, string defectcode, string defectname, string reportname, string reportopendate, string reportclosedate)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
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 (defectcode != "" && defectcode != null)
|
{
|
search += "and AA.defect_code like '%'+@defectcode+'%' ";
|
dynamicParams.Add("@defectcode", defectcode);
|
}
|
if (defectname != "" && defectname != null)
|
{
|
search += "and AA.defect_name like '%'+@defectname+'%' ";
|
dynamicParams.Add("@defectname", defectname);
|
}
|
if (reportname != "" && reportname != null)
|
{
|
search += "and AA.lm_user like '%'+@reportname+'%' ";
|
dynamicParams.Add("@reportname", reportname);
|
}
|
if (reportopendate != "" && reportopendate != null)
|
{
|
search += "and AA.lm_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 AA.wkshp_code as 车间编码,AA.wkshp_name as 车间名称,AA.wo_code as 工单编号,AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.stepcode as 工序编码,
|
AA.stepname as 工序名称,AA.plan_qty as 任务数量,AA.defect_qty as 不良数量,AA.defect_code as 缺陷代码,AA.defect_name as 缺陷名称,
|
(case when AA.style='B' then '报工' when AA.style='S' then '收料' end) as '操作类型',AA.lm_user as 操作人员,AA.lm_date as 操作时间
|
from(
|
select W.wkshp_code,G.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
|
A.defect_code,
|
STUFF((SELECT ',' + F.name
|
FROM TDefect F
|
WHERE CHARINDEX(',' + F.code + ',', ',' + A.defect_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS defect_name,
|
A.style,U.username as lm_user,A.lm_date
|
from CSR_WorkRecord_Defect A
|
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 W on A.wo_code=W.wo_code
|
left join TOrganization G on W.wkshp_code=G.torg_code
|
left join TMateriel_Info M on A.partnumber=M.partcode
|
left join TStep T on A.step_code=T.stepcode
|
left join TUser U on A.lm_user=U.usercode
|
) as 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
|
|
|
#region[维修明细报表]
|
public static ToMessage MaintenanceDetailsReportSearch(string wkshopcode, string wocode, string partcode, string partname, string partspec, string stepname, string style, string defectname, string repairname, string repairopendate, string repairclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
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 (style != "" && style != null)
|
{
|
search += "and AA.style=@style ";
|
dynamicParams.Add("@style", style);
|
}
|
if (defectname != "" && defectname != null)
|
{
|
search += "and AA.defect_name like '%'+@defectname+'%' ";
|
dynamicParams.Add("@defectname", defectname);
|
}
|
if (repairname != "" && repairname != null)
|
{
|
search += "and AA.lm_user like '%'+@repairname+'%' ";
|
dynamicParams.Add("@repairname", repairname);
|
}
|
if (repairopendate != "" && repairopendate != null)
|
{
|
search += "and AA.lm_date between @repairopendate and @repairclosedate ";
|
dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
|
dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
|
}
|
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select AA.wkshp_code,AA.wkshp_name,AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,(case when AA.style='B' then '报工' when AA.style='S' then '收料' end) as style,
|
AA.plan_qty,AA.repair_qty,AA.laborbad_qty,AA.materielbad_qty,AA.defect_code,AA.defect_name,AA.lm_user,AA.lm_date
|
from(
|
select W.wkshp_code,G.torg_name as wkshp_name,F.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,F.style,P.plan_qty,F.repair_qty,F.laborbad_qty,F.materielbad_qty,F.defect_code,
|
defect_name = STUFF(( SELECT ',' + T.name
|
FROM TDefect as T
|
where PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')>0
|
ORDER BY PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')
|
FOR XML PATH('')), 1, 1,''),U.username as lm_user,F.lm_date
|
from CSR_WorkRecord_DefectHandle as F
|
inner join TK_Wrk_Step P on F.wo_code=P.wo_code and F.step_code=P.step_code
|
left join TK_Wrk_Man W on F.wo_code=W.wo_code
|
left join TOrganization G on W.wkshp_code=G.torg_code
|
left join TMateriel_Info M on F.partnumber=M.partcode
|
left join TStep S on F.step_code=S.stepcode
|
left join TUser U on F.lm_user=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 MaintenanceDetailsReportExcelSearch(string wkshopcode, string wocode, string partcode, string partname, string partspec, string stepname, string style, string defectname, string repairname, string repairopendate, string repairclosedate)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
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 (style != "" && style != null)
|
{
|
search += "and AA.style=@style ";
|
dynamicParams.Add("@style", style);
|
}
|
if (defectname != "" && defectname != null)
|
{
|
search += "and AA.defect_name like '%'+@defectname+'%' ";
|
dynamicParams.Add("@defectname", defectname);
|
}
|
if (repairname != "" && repairname != null)
|
{
|
search += "and AA.lm_user like '%'+@repairname+'%' ";
|
dynamicParams.Add("@repairname", repairname);
|
}
|
if (repairopendate != "" && repairopendate != null)
|
{
|
search += "and AA.lm_date between @repairopendate and @repairclosedate ";
|
dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
|
dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
|
}
|
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select AA.wkshp_code as 车间编码,AA.wkshp_name as 车间名称,AA.wo_code as 工单编号,AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.stepcode as 工序编码,AA.stepname as 工序名称,
|
(case when AA.style='B' then '报工' when AA.style='S' then '收料' end) as 操作类型,
|
AA.plan_qty as 任务数量,AA.repair_qty as 维修数量,AA.laborbad_qty as 工废数量,AA.materielbad_qty as 料废数量,AA.defect_name as 缺陷名称,AA.lm_user as 维修人员,AA.lm_date as 维修时间
|
from(
|
select W.wkshp_code,G.torg_name as wkshp_name,F.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,F.style,P.plan_qty,F.repair_qty,F.laborbad_qty,F.materielbad_qty,F.defect_code,
|
defect_name = STUFF(( SELECT ',' + T.name
|
FROM TDefect as T
|
where PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')>0
|
ORDER BY PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')
|
FOR XML PATH('')), 1, 1,''),U.username as lm_user,F.lm_date
|
from CSR_WorkRecord_DefectHandle as F
|
inner join TK_Wrk_Step P on F.wo_code=P.wo_code and F.step_code=P.step_code
|
left join TK_Wrk_Man W on F.wo_code=W.wo_code
|
left join TOrganization G on W.wkshp_code=G.torg_code
|
left join TMateriel_Info M on F.partnumber=M.partcode
|
left join TStep S on F.step_code=S.stepcode
|
left join TUser U on F.lm_user=U.usercode
|
) as 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
|
|
|
#region[安灯报表明细]
|
public static ToMessage AnDonReportDefinitSearch(string wkshopcode, string calltypecode, string calluser, string callopendate, string callclosedate, string eqpcode, string eqpname, string responduser, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and A.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (calltypecode != "" && calltypecode != null)
|
{
|
search += "and A.type=@calltypecode ";
|
dynamicParams.Add("@calltypecode", calltypecode);
|
}
|
if (calluser != "" && calluser != null)
|
{
|
search += "and A.start_user like '%'+@calluser+'%' ";
|
dynamicParams.Add("@calluser", calluser);
|
}
|
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 (responduser != "" && responduser != null)
|
{
|
search += "and A.resp_user like '%'+@responduser+'%' ";
|
dynamicParams.Add("@responduser", responduser);
|
}
|
if (callopendate != "" && callopendate != null)
|
{
|
search += "and A.start_date between @callopendate and @callclosedate ";
|
dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
|
dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
|
}
|
if (respondopendate != "" && respondopendate != null)
|
{
|
search += "and A.resp_date between @respondopendate and @respondclosedate ";
|
dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
|
dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.wkshp_code,T.torg_name as wkshp_name,A.eqp_code,E.name as eqp_name,Y.name as typename,
|
A.start_user,A.start_date,A.resp_user,A.resp_date,
|
CAST(CAST(datediff(second,A.start_date,A.resp_date) / (60*60*24) AS INT) AS VARCHAR) + '天'
|
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 86400 / 3600 AS INT) AS VARCHAR) + '小时'
|
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 3600 / 60 AS INT) AS VARCHAR) + '分'
|
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 60 AS INT) AS VARCHAR) + '秒' AS respondcont,
|
(case when A.resp_user is null then '待响应' else '已响应' end) as status
|
from TAnDon_Task_Info A
|
left join TOrganization T on A.wkshp_code=T.torg_code
|
left join TEqpInfo E on A.eqp_code=E.code
|
left join TAnDonType Y on A.type=Y.code
|
where 1=1 " + 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 AnDonReportDefinitExcelSearch(string wkshopcode, string calltypecode, string calluser, string callopendate, string callclosedate, string eqpcode, string eqpname, string responduser, string respondopendate, string respondclosedate)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and A.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (calltypecode != "" && calltypecode != null)
|
{
|
search += "and A.type=@calltypecode ";
|
dynamicParams.Add("@calltypecode", calltypecode);
|
}
|
if (calluser != "" && calluser != null)
|
{
|
search += "and A.start_user like '%'+@calluser+'%' ";
|
dynamicParams.Add("@calluser", calluser);
|
}
|
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 (responduser != "" && responduser != null)
|
{
|
search += "and A.resp_user like '%'+@responduser+'%' ";
|
dynamicParams.Add("@responduser", responduser);
|
}
|
if (callopendate != "" && callopendate != null)
|
{
|
search += "and A.start_date between @callopendate and @callclosedate ";
|
dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
|
dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
|
}
|
if (respondopendate != "" && respondopendate != null)
|
{
|
search += "and A.resp_date between @respondopendate and @respondclosedate ";
|
dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
|
dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.wkshp_code as 车间编码,T.torg_name as 车间名称,A.eqp_code as 设备编码,E.name as 设备名称,Y.name as 呼叫类型,
|
A.start_user as 呼叫人,A.start_date as 呼叫时间,A.resp_user as 响应人,A.resp_date as 响应时间,
|
CAST(CAST(datediff(second,A.start_date,A.resp_date) / (60*60*24) AS INT) AS VARCHAR) + '天'
|
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 86400 / 3600 AS INT) AS VARCHAR) + '小时'
|
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 3600 / 60 AS INT) AS VARCHAR) + '分'
|
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 60 AS INT) AS VARCHAR) + '秒' AS 响应时长,
|
(case when A.resp_user is null then '待响应' else '已响应' end) as 状态
|
from TAnDon_Task_Info A
|
left join TOrganization T on A.wkshp_code=T.torg_code
|
left join TEqpInfo E on A.eqp_code=E.code
|
left join TAnDonType Y on A.type=Y.code
|
where 1=1 " + 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 AnDonReportSumSearch(string wkshopcode, string eqpcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and A.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (eqpcode != "" && eqpcode != null)
|
{
|
search += "and A.eqp_code=@eqpcode ";
|
dynamicParams.Add("@eqpcode", eqpcode);
|
}
|
if (calltypecode != "" && calltypecode != null)
|
{
|
search += "and A.type=@calltypecode ";
|
dynamicParams.Add("@calltypecode", calltypecode);
|
}
|
if (callopendate != "" && callopendate != null)
|
{
|
search += "and A.start_date between @callopendate and @callclosedate ";
|
dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
|
dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
|
}
|
if (respondopendate != "" && respondopendate != null)
|
{
|
search += "and A.resp_date between @respondopendate and @respondclosedate ";
|
dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
|
dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select top 100 percent T.torg_name as wkshp_name,A.eqp_code,E.name as eqp_name,Y.name as calltypename,
|
(select count(*) callcount from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.type=A.type) as callcount,
|
(select count(*) repondcount from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.type=A.type and BB.status='CLOSED') as repondcount,
|
(select ISNULL(SUM(CAST(DATEDIFF(ss, CC.start_date,CC.resp_date)/60 as INT)),0) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.eqp_code=A.eqp_code and CC.type=A.type) as repondtime
|
from TAnDon_Task_Info A
|
left join TOrganization T on A.wkshp_code=T.torg_code
|
left join TAnDonType Y on A.type=Y.code
|
left join TEqpInfo E on A.eqp_code=E.code
|
where 1=1 " + search + " group by A.wkshp_code,T.torg_name,A.type,Y.name,A.eqp_code,E.name order by T.torg_name,A.eqp_code ";
|
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 AnDonReportSumExcelSearch(string wkshopcode, string eqpcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and A.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (eqpcode != "" && eqpcode != null)
|
{
|
search += "and A.eqp_code=@eqpcode ";
|
dynamicParams.Add("@eqpcode", eqpcode);
|
}
|
if (calltypecode != "" && calltypecode != null)
|
{
|
search += "and A.type=@calltypecode ";
|
dynamicParams.Add("@calltypecode", calltypecode);
|
}
|
if (callopendate != "" && callopendate != null)
|
{
|
search += "and A.start_date between @callopendate and @callclosedate ";
|
dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
|
dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
|
}
|
if (respondopendate != "" && respondopendate != null)
|
{
|
search += "and A.resp_date between @respondopendate and @respondclosedate ";
|
dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
|
dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select top 100 percent T.torg_name as 生产车间,E.name as 设备名称,Y.name as 呼叫类型,
|
(select count(*) callcount from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.eqp_code=A.eqp_code and AA.type=A.type) as 总呼叫次数,
|
(select count(*) repondcount from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.eqp_code=A.eqp_code and BB.type=A.type and BB.status='CLOSED') as 总响应次数,
|
(select ISNULL(SUM(CAST(DATEDIFF(ss, CC.start_date,CC.resp_date)/60 as INT)),0) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.eqp_code=A.eqp_code and CC.type=A.type) 总响应时长
|
from TAnDon_Task_Info A
|
left join TOrganization T on A.wkshp_code=T.torg_code
|
left join TAnDonType Y on A.type=Y.code
|
left join TEqpInfo E on A.eqp_code=E.code
|
where 1=1 " + search + " group by A.wkshp_code,T.torg_name,A.type,Y.name,A.eqp_code,E.name order by T.torg_name,A.eqp_code ";
|
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[T8入库记录表头]
|
public static ToMessage StorageRecordMainSearch(string hbillno, string username, string hbdateopendate, string hbdateclosedate, string userdateopendate, string userdateclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (hbillno != "" && hbillno != null)
|
{
|
search += "and hbillno like '%'+@hbillno+'%' ";
|
dynamicParams.Add("@hbillno", hbillno);
|
}
|
if (username != "" && username != null)
|
{
|
search += "and create_user like '%'+@username+'%' ";
|
dynamicParams.Add("@username", username);
|
}
|
if (hbdateopendate != "" && hbdateopendate != null)
|
{
|
search += "and hbdate between @hbdateopendate and @hbdateclosedate ";
|
dynamicParams.Add("@hbdateopendate", hbdateopendate + " 00:00:00");
|
dynamicParams.Add("@hbdateclosedate", hbdateclosedate + " 23:59:59");
|
}
|
if (userdateopendate != "" && userdateopendate != null)
|
{
|
search += "and create_date between @userdateopendate and @userdateclosedate ";
|
dynamicParams.Add("@userdateopendate", userdateopendate + " 00:00:00");
|
dynamicParams.Add("@userdateclosedate", userdateclosedate + " 23:59:59");
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select (case when docu_typecode='WI' then '产成品入库' end) as hbilltype,hbillno,hbdate,create_user,create_date
|
from TK_WMS_Inwh_Main where 1=1 " + 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[T8入库记录表体]
|
public static ToMessage StorageRecordSubSearch(string hbillno)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
dynamicParams.Add("@hbillno", hbillno);
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select * from(
|
select S.hbillno,S.rownumber,S.inbarcode,S.mo_id,S.mo_no,E.sbid,S.wocode,R.step_code,T.stepname,E.materiel_id,S.partcode,M.partname,M.partspec,
|
E.unitid,S.unitcode,E.unitname,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,K.name as stockname,
|
S.qty,S.salecode,S.style
|
from TK_WMS_Inwh_Sub S
|
left join TKimp_Ewo E on S.mo_no=E.wo and S.mo_id=E.woid and S.partcode=E.materiel_code
|
left join TK_Wrk_Record R on S.inbarcode=R.inbarcode
|
left join TStep T on R.step_code=T.stepcode
|
left join TMateriel_Info M on S.partcode=M.partcode
|
left join TSecStck K on COALESCE(M.idwarehouse, E.stck_code)=K.code
|
where S.style='B' and S.hbillno=@hbillno
|
union all
|
select S.hbillno,S.rownumber,S.inbarcode,S.mo_id,S.mo_no,E.sbid,S.wocode,O.step_code,T.stepname,E.materiel_id,S.partcode,M.partname,M.partspec,
|
E.unitid,S.unitcode,E.unitname,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,K.name as stockname,
|
S.qty,S.salecode,S.style
|
from TK_WMS_Inwh_Sub S
|
left join TKimp_Ewo E on S.mo_no=E.wo and S.mo_id=E.woid and S.partcode=E.materiel_code
|
left join TK_Wrk_OutRecord O on S.inbarcode=O.inbarcode
|
left join TStep T on O.step_code=T.stepcode
|
left join TMateriel_Info M on S.partcode=M.partcode
|
left join TSecStck K on COALESCE(M.idwarehouse, E.stck_code)=K.code
|
where S.style='S' and S.hbillno=@hbillno
|
) as AA" + search;
|
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.count = total;
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[T8入库记录删除]
|
public static ToMessage StorageRecordDelete(DataTable dt, User us)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//存储过程名
|
sql = @"h_p_IFCLD_T8DeleteInProductOrder";
|
dynamicParams.Add("@RecordSub", dbType: DbType.Object, value: dt);
|
// 添加输出参数
|
dynamicParams.Add("@StatusCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
|
dynamicParams.Add("@Message", dbType: DbType.String, size: 255, direction: ParameterDirection.Output);
|
bool a = DapperHelper.IsProcedure(sql, dynamicParams);
|
// 获取输出参数的值
|
var statusCode = dynamicParams.Get<int>("@StatusCode");
|
var message = dynamicParams.Get<string>("@Message");
|
if (a)
|
{
|
mes.code = statusCode.ToString();
|
mes.count = 0;
|
mes.message = message;
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = statusCode.ToString();
|
mes.count = 0;
|
mes.message = message;
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[入库记录删除]
|
public static ToMessage DeleteStorageRecord(List<StorageRecord> json, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
// 使用LINQ的lambda表达式根据hbillno分组并统计每个组的条数
|
var groupedRecords = json.GroupBy(r => r.hbillno).Select(g => new { hbillno = g.Key, count = g.Count() }).ToList();
|
for (int i = 0; i < groupedRecords.Count; i++)
|
{
|
//查询入库单子表的数据条数
|
sql = @"select * from TK_WMS_Inwh_Sub where hbillno=@hbillno";
|
dynamicParams.Add("@hbillno", groupedRecords[i].hbillno);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
//子表有对应入库单号数据且数据条数等于提交的入库条数
|
if (data.Rows.Count > 0 && data.Rows.Count == groupedRecords[i].count)
|
{
|
//删除入库单主表
|
sql = @"delete from TK_WMS_Inwh_Main where hbillno=@hbillno";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
hbillno = groupedRecords[i].hbillno.ToString()
|
}
|
});
|
}
|
}
|
|
for (int i = 0; i < json.Count; i++)
|
{
|
//删除入库单子表
|
sql = @"delete from TK_WMS_Inwh_Sub where hbillno=@hbillno and inbarcode=@inbarcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
hbillno = json[i].hbillno.ToString(),
|
inbarcode = json[i].inbarcode.ToString()
|
}
|
});
|
if (json[i].style == "B")
|
{
|
//删除报工记录主表
|
//sql = @"delete from TK_Wrk_Record where id=@repoid and inbarcode=@inbarcode";
|
//list.Add(new
|
//{
|
// str = sql,
|
// parm = new
|
// {
|
// repoid = json[i].repoid.ToString(),
|
// inbarcode = json[i].inbarcode.ToString()
|
// }
|
//});
|
//修改自制报工入库数量
|
sql = @"update TK_Wrk_Record set inhouseqty=inhouseqty-@qty where inbarcode=@inbarcode and id=@repoid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
repoid = json[i].repoid.ToString(),
|
qty = json[i].qty.ToString(),
|
inbarcode = json[i].inbarcode.ToString()
|
}
|
});
|
}
|
if (json[i].style == "S")
|
{
|
//删除外协收料记录主表
|
//sql = @"delete from TK_Wrk_OutRecord where id=@repoid and inbarcode=@inbarcode";
|
//list.Add(new
|
//{
|
// str = sql,
|
// parm = new
|
// {
|
// repoid = json[i].repoid.ToString(),
|
// inbarcode = json[i].inbarcode.ToString()
|
// }
|
//});
|
//修改外协收料记录表入库数量
|
sql = @"update TK_Wrk_OutRecord set inhouseqty=inhouseqty-@qty where inbarcode=@inbarcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
qty = json[i].qty.ToString(),
|
inbarcode = json[i].inbarcode.ToString()
|
}
|
});
|
}
|
//清除工序任务表累计入库数量
|
sql = @"update TK_Wrk_Step set inhouseqty=inhouseqty-@qty where wo_code=@wocode and step_code=@stepcode and isend='Y'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
qty = json[i].qty.ToString(),
|
wocode = json[i].wocode.ToString(),
|
stepcode = json[i].step_code.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
LogHelper.WriteLogData(aa.ToString());
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了入库单:" + string.Join(",", json.Select(r => $"{r.hbillno},{r.inbarcode}")), us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.message = "入库单删除成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "入库单删除失败!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[入库记录导出]
|
public static ToMessage StorageRecordExcelSearch(string hbillno, string sono, string mono, string wocode, string partcode, string partname, string stockcode, string respondopendate, string respondclosedate)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (hbillno != "" && hbillno != null)
|
{
|
search += "and AA.hbillno like '%'+@hbillno+'%' ";
|
dynamicParams.Add("@hbillno", hbillno);
|
}
|
if (sono != "" && sono != null)
|
{
|
search += "and AA.salecode like '%'+@sono+'%' ";
|
dynamicParams.Add("@sono", sono);
|
}
|
if (mono != "" && mono != null)
|
{
|
search += "and AA.mo_no like '%'+@mono+'%' ";
|
dynamicParams.Add("@mono", mono);
|
}
|
if (wocode != "" && wocode != null)
|
{
|
search += "and AA.wocode 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 (stockcode != "" && stockcode != null)
|
{
|
search += "and AA.stockcode=@stockcode ";
|
dynamicParams.Add("@stockcode", stockcode);
|
}
|
if (respondopendate != "" && respondopendate != null)
|
{
|
search += "and A.hbdate between @respondopendate and @respondclosedate ";
|
dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
|
dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select AA.hbillno as 入库单号,AA.hbdate as 单据日期,AA.salecode as 销售单号,AA.mo_no as 订单号,AA.wocode as 工单号,AA.partcode as 产品编码,AA.partname as 产品名称,
|
AA.partspec as 产品规格,AA.wkshp_code as 车间编码,AA.torg_name as 车间名称,AA.stockcode as 仓库编码,AA.stockname as 仓库名称, AA.qty as 入库数量,
|
AA.step_code as 工序编码,AA.stepname as 工序名称,AA.create_user as 创建人员,AA.create_date as 创建时间
|
from(
|
select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name,B.stockcode,S.name as stockname,
|
B.qty,B.inbarcode,R.step_code,E.stepname,R.style,A.create_user,A.create_date
|
from TK_WMS_Inwh_Main A
|
inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
|
inner join TK_Wrk_Record R on B.inbarcode=R.inbarcode
|
left join TK_Wrk_Man M on B.wocode=M.wo_code
|
left join TMateriel_Info P on B.partcode=P.partcode
|
left join TSecStck S on B.stockcode=S.code
|
left join TStep E on R.step_code=E.stepcode
|
left join TOrganization T on M.wkshp_code=T.torg_code
|
union all
|
select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name as wkshp_name,B.stockcode,S.name as stockname,
|
B.qty,B.inbarcode,O.step_code,E.stepname,O.style,A.create_user,A.create_date
|
from TK_WMS_Inwh_Main A
|
inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
|
inner join TK_Wrk_OutRecord O on B.inbarcode=O.inbarcode
|
left join TK_Wrk_Man M on B.wocode=M.wo_code
|
left join TMateriel_Info P on B.partcode=P.partcode
|
left join TSecStck S on B.stockcode=S.code
|
left join TStep E on O.step_code=E.stepcode
|
left join TOrganization T on M.wkshp_code=T.torg_code
|
) as AA where 1=1 " + 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[T+入库记录表头]
|
public static ToMessage InStorageRecordMainSearch(string hbillno, string username, string hbdateopendate, string hbdateclosedate, string userdateopendate, string userdateclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (hbillno != "" && hbillno != null)
|
{
|
search += "and hbillno like '%'+@hbillno+'%' ";
|
dynamicParams.Add("@hbillno", hbillno);
|
}
|
if (username != "" && username != null)
|
{
|
search += "and create_user like '%'+@username+'%' ";
|
dynamicParams.Add("@username", username);
|
}
|
if (hbdateopendate != "" && hbdateopendate != null)
|
{
|
search += "and hbdate between @hbdateopendate and @hbdateclosedate ";
|
dynamicParams.Add("@hbdateopendate", hbdateopendate + " 00:00:00");
|
dynamicParams.Add("@hbdateclosedate", hbdateclosedate + " 23:59:59");
|
}
|
if (userdateopendate != "" && userdateopendate != null)
|
{
|
search += "and create_date between @userdateopendate and @userdateclosedate ";
|
dynamicParams.Add("@userdateopendate", userdateopendate + " 00:00:00");
|
dynamicParams.Add("@userdateclosedate", userdateclosedate + " 23:59:59");
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select (case when docu_typecode='69' then '产成品入库' end) as hbilltype,hbillno,hbdate,create_user,create_date
|
from TK_WMS_Inwh_Main where 1=1 " + 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[T+入库记录表体]
|
public static ToMessage InStorageRecordSubSearch(string hbillno)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
dynamicParams.Add("@hbillno", hbillno);
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select * from(
|
select S.hbillno,S.rownumber,S.inbarcode,S.mo_id,S.mo_no,E.sbid,S.wocode,R.step_code,T.stepname,E.materiel_id,S.partcode,M.partname,M.partspec,
|
E.unitid,S.unitcode,E.unitname,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,K.name as stockname,
|
S.qty,S.salecode,S.style
|
from TK_WMS_Inwh_Sub S
|
left join TKimp_Ewo E on S.mo_no=E.wo and S.mo_id=E.woid and S.partcode=E.materiel_code
|
left join TK_Wrk_Record R on S.inbarcode=R.inbarcode
|
left join TStep T on R.step_code=T.stepcode
|
left join TMateriel_Info M on S.partcode=M.partcode
|
left join TSecStck K on COALESCE(M.idwarehouse, E.stck_code)=K.code
|
where S.style='B' and S.hbillno=@hbillno
|
union all
|
select S.hbillno,S.rownumber,S.inbarcode,S.mo_id,S.mo_no,E.sbid,S.wocode,O.step_code,T.stepname,E.materiel_id,S.partcode,M.partname,M.partspec,
|
E.unitid,S.unitcode,E.unitname,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,K.name as stockname,
|
S.qty,S.salecode,S.style
|
from TK_WMS_Inwh_Sub S
|
left join TKimp_Ewo E on S.mo_no=E.wo and S.mo_id=E.woid and S.partcode=E.materiel_code
|
left join TK_Wrk_OutRecord O on S.inbarcode=O.inbarcode
|
left join TStep T on O.step_code=T.stepcode
|
left join TMateriel_Info M on S.partcode=M.partcode
|
left join TSecStck K on COALESCE(M.idwarehouse, E.stck_code)=K.code
|
where S.style='S' and S.hbillno=@hbillno
|
) as AA" + search;
|
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.count = total;
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[T+入库记录删除]
|
public static ToMessage DeleteInStorageRecord(DataTable dt, User us)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//存储过程名
|
sql = @"h_p_IFCLD_TCloudDeleteInProductOrder";
|
dynamicParams.Add("@RecordSub", dbType: DbType.Object, value: dt);
|
// 添加输出参数
|
dynamicParams.Add("@StatusCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
|
dynamicParams.Add("@Message", dbType: DbType.String, size: 255, direction: ParameterDirection.Output);
|
bool a = DapperHelper.IsProcedure(sql, dynamicParams);
|
// 获取输出参数的值
|
var statusCode = dynamicParams.Get<int>("@StatusCode");
|
var message = dynamicParams.Get<string>("@Message");
|
if (a)
|
{
|
mes.code = statusCode.ToString();
|
mes.count = 0;
|
mes.message = message;
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = statusCode.ToString();
|
mes.count = 0;
|
mes.message = message;
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
