using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Models;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.DLL.DAL
|
{
|
public class AppProductDAL
|
{
|
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[App已派发工单的物料]
|
public static ToMessage DistrInventory(User us)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select distinct P.materiel_code as code,M.partname as name from TK_Wrk_Man P
|
left join TMateriel_Info M on P.materiel_code=M.partcode
|
where P.status<>'NEW'";
|
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[App查看单据及工艺信息]
|
public static ToMessage ProductOrderSearch(User us, string ordertype, string ordercode, string partcode, string startopendate, string endclosedate)
|
{
|
Dictionary<object, object> dList = new Dictionary<object, object>();
|
var dynamicParams = new DynamicParameters();
|
string search = "",sql = "", torg_codelist = "";
|
DataTable dt;
|
try
|
{
|
//获取当前用户所属组织及所有子节点组织
|
mes = TOrganizationRecursion.TOrgCodeSeachNo(us.storg_code);
|
if (mes.code == "300")
|
{
|
return mes;
|
}
|
else
|
{
|
torg_codelist = mes.data.ToString(); //获取组织集合
|
string[] torglist = Array.ConvertAll<string, string>(torg_codelist.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
search += " and A.wkshp_code in @wkshp_code ";
|
dynamicParams.Add("@wkshp_code", torglist.ToArray());
|
}
|
|
//根据查询条件,查找符合要求的单据
|
switch (ordertype)
|
{
|
case "SO": //按销售订单统计
|
if (!string.IsNullOrEmpty(ordercode))
|
{
|
search += "and A.saleOrderCode like '%'+@ordercode+'%' ";
|
dynamicParams.Add("@ordercode", ordercode);
|
}
|
if (!string.IsNullOrEmpty(partcode))
|
{
|
string[] selects = Array.ConvertAll<string, string>(partcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
search += " and A.materiel_code in @partcode ";
|
dynamicParams.Add("@partcode", selects.ToArray());
|
}
|
if (!string.IsNullOrEmpty(startopendate))
|
{
|
search += " and convert(varchar(100),A.lm_date,21)>=@startopendate and convert(varchar(100),A.lm_date,21)<=@endclosedate ";
|
dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
|
dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
|
}
|
//获取单据信息
|
sql = @"select distinct BB.wo_code as ordercode,BB.materiel_code as partcode,P.partname,P.partspec,E.saleOrderDate as lm_date,sum(BB.plan_qty) as orderqty
|
from TKimp_Ewo E
|
inner join(
|
select A.m_po,A.wo_code,A.plan_qty,A.materiel_code
|
from TK_Wrk_Man A
|
where A.status not in('NEW','CLOSED') " + search + ") as BB on E.wo=BB.m_po and E.materiel_code=BB.materiel_code left join TMateriel_Info P on E.materiel_code=p.partcode group by BB.wo_code,BB.materiel_code,P.partname,P.partspec,E.saleOrderDate order by E.saleOrderDate desc";
|
var sorders = DapperHelper.select<AppScheduleKanban>(sql, dynamicParams).AsList();
|
if (sorders.Count == 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "无符合要求单据！";
|
mes.data = null;
|
return mes;
|
}
|
// 批量查询所有工单的步骤信息
|
var soCodes = sorders.Select(o => o.ordercode).Distinct().ToArray();
|
sql = @"select M.wo_code,T.stepcode,T.stepname,S.plan_qty,S.good_qty,S.isend
|
from TK_Wrk_Man M
|
inner join TK_Wrk_Step S on M.wo_code=S.wo_code
|
left join TStep T on S.step_code=T.stepcode
|
where M.wo_code in @wo_codes
|
order by M.wo_code,S.seq ";
|
dynamicParams.Add("@wo_codes", soCodes);
|
var sosteps = DapperHelper.select<StepResult>(sql, dynamicParams);
|
var stepsBySoCode = sosteps.GroupBy(s => s.wo_code).ToDictionary(g => g.Key, g => g.AsEnumerable());
|
// 组装数据
|
foreach (var order in sorders)
|
{
|
if (stepsBySoCode.TryGetValue(order.ordercode, out var orderSteps))
|
{
|
order.children = orderSteps.Select(s => new AppScheduleKanbanSub
|
{
|
code = s.stepcode,
|
name = s.stepname,
|
spec = s.isend,
|
planqty = s.plan_qty.ToString("F2"),
|
goodqty = s.good_qty.ToString("F2")
|
}).ToList();
|
|
var endStep = orderSteps.FirstOrDefault(s => s.isend == "Y");
|
order.ordergoodqty = endStep?.good_qty.ToString("F2") ?? "0";
|
}
|
else
|
{
|
order.children = new List<AppScheduleKanbanSub>();
|
order.ordergoodqty = "0";
|
}
|
}
|
mes.code = "200";
|
mes.count = sorders.Count;
|
mes.data = sorders;
|
mes.message = "查询成功!";
|
break;
|
case "MO": //按生产订单统计
|
if (!string.IsNullOrEmpty(ordercode))
|
{
|
search += "and A.m_po like '%'+@ordercode+'%' ";
|
dynamicParams.Add("@ordercode", ordercode);
|
}
|
if (!string.IsNullOrEmpty(partcode))
|
{
|
string[] selects = Array.ConvertAll<string, string>(partcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
search += " and A.materiel_code in @partcode ";
|
dynamicParams.Add("@partcode", selects.ToArray());
|
}
|
if (!string.IsNullOrEmpty(startopendate))
|
{
|
search += " and convert(varchar(100),A.lm_date,21)>=@startopendate and convert(varchar(100),A.lm_date,21)<=@endclosedate ";
|
dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
|
dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
|
}
|
//获取单据信息
|
sql = @"select distinct BB.wo_code as ordercode,BB.materiel_code as partcode,P.partname,P.partspec,E.voucherdate as lm_date,sum(BB.plan_qty) as orderqty
|
from TKimp_Ewo E
|
inner join(
|
select A.m_po,A.wo_code,A.plan_qty,A.materiel_code
|
from TK_Wrk_Man A
|
where A.status not in('NEW','CLOSED') " + search + ") as BB on E.wo=BB.m_po and E.materiel_code=BB.materiel_code left join TMateriel_Info P on E.materiel_code=p.partcode group by BB.wo_code,BB.materiel_code,P.partname,P.partspec,E.voucherdate order by E.voucherdate desc";
|
var morders = DapperHelper.select<AppScheduleKanban>(sql, dynamicParams).AsList();
|
if (morders.Count == 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "无符合要求单据！";
|
mes.data = null;
|
return mes;
|
}
|
// 批量查询所有工单的步骤信息
|
var moCodes = morders.Select(o => o.ordercode).Distinct().ToArray();
|
sql = @"select M.wo_code,T.stepcode,T.stepname,S.plan_qty,S.good_qty,S.isend
|
from TK_Wrk_Man M
|
inner join TK_Wrk_Step S on M.wo_code=S.wo_code
|
left join TStep T on S.step_code=T.stepcode
|
where M.wo_code in @wo_codes
|
order by M.wo_code,S.seq ";
|
dynamicParams.Add("@wo_codes", moCodes);
|
var mosteps = DapperHelper.select<StepResult>(sql, dynamicParams);
|
var stepsByMoCode = mosteps.GroupBy(s => s.wo_code).ToDictionary(g => g.Key, g => g.AsEnumerable());
|
// 组装数据
|
foreach (var order in morders)
|
{
|
if (stepsByMoCode.TryGetValue(order.ordercode, out var orderSteps))
|
{
|
order.children = orderSteps.Select(s => new AppScheduleKanbanSub
|
{
|
code = s.stepcode,
|
name = s.stepname,
|
spec = s.isend,
|
planqty = s.plan_qty.ToString("F2"),
|
goodqty = s.good_qty.ToString("F2")
|
}).ToList();
|
|
var endStep = orderSteps.FirstOrDefault(s => s.isend == "Y");
|
order.ordergoodqty = endStep?.good_qty.ToString("F2") ?? "0";
|
}
|
else
|
{
|
order.children = new List<AppScheduleKanbanSub>();
|
order.ordergoodqty = "0";
|
}
|
}
|
mes.code = "200";
|
mes.count = morders.Count;
|
mes.data = morders;
|
mes.message = "查询成功!";
|
break;
|
case "PO": //按生产工单统计
|
if (!string.IsNullOrEmpty(ordercode))
|
{
|
search += "and A.wo_code like '%'+@ordercode+'%' ";
|
dynamicParams.Add("@ordercode", ordercode);
|
}
|
if (!string.IsNullOrEmpty(partcode))
|
{
|
string[] selects = Array.ConvertAll<string, string>(partcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
search += " and A.materiel_code in @partcode ";
|
dynamicParams.Add("@partcode", selects.ToArray());
|
}
|
if (!string.IsNullOrEmpty(startopendate))
|
{
|
search += " and convert(varchar(100),A.lm_date,21)>=@startopendate and convert(varchar(100),A.lm_date,21)<=@endclosedate ";
|
dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
|
dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
|
}
|
//获取单据信息
|
sql = @"select distinct A.wo_code as ordercode,T.partcode,T.partname,T.partspec,A.lm_date,sum(A.plan_qty) as orderqty
|
from TK_Wrk_Man A
|
left join TMateriel_Info T on A.materiel_code=T.partcode
|
where A.status<>'CLOSED' " + search+ " group by A.wo_code,T.partcode,T.partname,T.partspec,A.lm_date order by A.lm_date desc";
|
var orders = DapperHelper.select<AppScheduleKanban>(sql, dynamicParams).AsList();
|
if (orders.Count == 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "无符合要求单据！";
|
mes.data = null;
|
return mes;
|
}
|
// 批量查询所有工单的步骤信息
|
var woCodes = orders.Select(o => o.ordercode).Distinct().ToArray();
|
sql = @"select M.wo_code,T.stepcode,T.stepname,S.plan_qty,S.good_qty,S.isend
|
from TK_Wrk_Man M
|
inner join TK_Wrk_Step S on M.wo_code=S.wo_code
|
left join TStep T on S.step_code=T.stepcode
|
where M.wo_code in @wo_codes
|
order by M.wo_code,S.seq ";
|
dynamicParams.Add("@wo_codes", woCodes);
|
var posteps = DapperHelper.select<StepResult>(sql, dynamicParams);
|
var stepsByWoCode = posteps.GroupBy(s => s.wo_code).ToDictionary(g => g.Key, g => g.AsEnumerable());
|
// 组装数据
|
foreach (var order in orders)
|
{
|
if (stepsByWoCode.TryGetValue(order.ordercode, out var orderSteps))
|
{
|
order.children = orderSteps.Select(s => new AppScheduleKanbanSub
|
{
|
code = s.stepcode,
|
name = s.stepname,
|
spec = s.isend,
|
planqty = s.plan_qty.ToString("F2"),
|
goodqty = s.good_qty.ToString("F2")
|
}).ToList();
|
|
var endStep = orderSteps.FirstOrDefault(s => s.isend == "Y");
|
order.ordergoodqty = endStep?.good_qty.ToString("F2") ?? "0";
|
}
|
else
|
{
|
order.children = new List<AppScheduleKanbanSub>();
|
order.ordergoodqty = "0";
|
}
|
}
|
mes.code = "200";
|
mes.count = orders.Count;
|
mes.data = orders;
|
mes.message = "查询成功!";
|
break;
|
default:
|
break;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[步骤查询结果映射类]
|
// 步骤查询结果映射类
|
private class StepResult
|
{
|
public string wo_code { get; set; }
|
public string stepcode { get; set; }
|
public string stepname { get; set; }
|
public decimal plan_qty { get; set; }
|
public decimal good_qty { get; set; }
|
public string isend { get; set; }
|
}
|
#endregion
|
}
|
}
|
