using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
using VueWebApi.Models;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.DLL.DAL
|
{
|
public class AppAnalyticsDAL
|
{
|
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 ProductionKanban(string usercode, string startopendate, string endclosedate,string defecttype)
|
{
|
Dictionary<object, object> dList = new Dictionary<object, object>();
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
// --------------生产总览--------------
|
var sql = @"select
|
sum(CASE AA.clmname WHEN '计划数量' THEN AA.clmqty ELSE 0 END) as 'plan_qty',
|
sum(CASE AA.clmname WHEN '合格产出' THEN AA.clmqty ELSE 0 END) as 'good_qty',
|
sum(CASE AA.clmname WHEN '缺陷产出' THEN AA.clmqty ELSE 0 END) as 'defect_qty'
|
from(
|
select isnull(sum(A.plan_qty),0) as clmqty,'计划数量'as clmname from TK_Wrk_Man A
|
where A.lm_date between @startopendate and @endclosedate
|
union all
|
select isnull(sum(P.good_qty),0) as report_qty,'合格产出' as 合格产出 from TK_Wrk_Man A
|
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and P.isend='Y'
|
where A.lm_date between @startopendate and @endclosedate
|
union all
|
select (isnull(sum(P.ng_qty),0)+isnull(sum(P.bad_qty),0)) as ngqty,'缺陷产出' as 缺陷产出 from TK_Wrk_Man A
|
inner join TK_Wrk_Step P on A.wo_code=P.wo_code
|
where A.lm_date between @startopendate and @endclosedate
|
) as AA";
|
dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
|
dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
dList.Add("dt0", data0);
|
// --------------生产工单--------------
|
var sql1 = @"select sum(AA.cluntqty) as wo_coum,AA.wo_status from (
|
select COUNT(A.id) as cluntqty,
|
case when A.status='START' then '执行中' when A.status='CLOSED' then '已结束' else '未开始' end as wo_status
|
from TK_Wrk_Man A
|
where A.lm_date between @startopendate and @endclosedate
|
group by A.status
|
) as AA group by AA.wo_status";
|
var data1 = DapperHelper.selectdata(sql1, dynamicParams);
|
dList.Add("dt1", data1);
|
// --------------缺陷统计--------------
|
var sql2 = "";
|
switch (defecttype)
|
{
|
case "prt": //按产品统计
|
sql2 = @"select top 5 sum(AA.plan_qty) as plan_qty,AA.partname,sum(AA.ng_qty)+sum(AA.bad_qty) as defect_qty from (
|
select A.plan_qty,M.partname,S.ng_qty,S.bad_qty from TK_Wrk_Man A
|
inner join (select wo_code, isnull(sum(ng_qty),0) as ng_qty,isnull(sum(bad_qty),0) as bad_qty from TK_Wrk_Step where ng_qty>0 or bad_qty>0 group by wo_code) S on A.wo_code=S.wo_code
|
inner join TMateriel_Info M on A.materiel_code=M.partcode
|
where A.lm_date between @startopendate and @endclosedate
|
) as AA
|
group by AA.partname
|
order by defect_qty desc";
|
break;
|
case "stp"://按工序统计
|
sql2 = @"select top 5 sum(AA.plan_qty) as plan_qty,AA.stepname,sum(AA.ng_qty)+sum(AA.bad_qty) as defect_qty from (
|
select A.plan_qty,T.stepname,S.ng_qty,S.bad_qty from TK_Wrk_Man A
|
inner join (select wo_code,step_code, isnull(sum(ng_qty),0) as ng_qty,isnull(sum(bad_qty),0) as bad_qty from TK_Wrk_Step where ng_qty>0 or bad_qty>0 group by wo_code,step_code) S on A.wo_code=S.wo_code
|
inner join TStep T on S.step_code=T.stepcode
|
where A.lm_date between @startopendate and @endclosedate
|
) as AA
|
group by AA.stepname
|
order by defect_qty desc";
|
break;
|
default:
|
break;
|
}
|
var data2 = DapperHelper.selectdata(sql2, dynamicParams);
|
dList.Add("dt2", data2);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = dList;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[App生产进度看板单据信息]
|
public static ToMessage ProductionScheduleKanban(string ordertype, string partcode, string startopendate, string endclosedate)
|
{
|
Dictionary<object, object> dList = new Dictionary<object, object>();
|
List<AppScheduleKanban> list = new List<AppScheduleKanban>();
|
var dynamicParams = new DynamicParameters();
|
string search = "",sql="";
|
DataTable dt;
|
try
|
{
|
//根据查询条件,查找符合要求的单据
|
switch (ordertype)
|
{
|
case "SO": //按销售订单统计
|
if (partcode != "" && partcode != null)
|
{
|
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 (startopendate != "" && startopendate != null)
|
{
|
search += " and convert(varchar(100),A.saleOrderDate,21)>=@startopendate and convert(varchar(100),A.saleOrderDate,21)<=@endclosedate ";
|
dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
|
dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
|
}
|
//获取单据信息
|
sql = @"select distinct saleOrderCode,A.saleOrderDate,sum(A.saleOrderqty) as saleOrderqty
|
from TKimp_Ewo A
|
where A.saleOrderCode<>'' " + search+ " group by A.saleOrderCode,A.saleOrderDate order by A.saleOrderDate desc";
|
dt = DapperHelper.selectlist(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
AppScheduleKanban cmp = new AppScheduleKanban();
|
cmp.ordercode = dt.Rows[i]["saleOrderCode"].ToString();
|
cmp.orderqty = dt.Rows[i]["saleOrderqty"].ToString();
|
cmp.children = new List<AppScheduleKanbanSub>();
|
//获取单据下产品完成信息
|
sql = @"select AA.wo,AA.partcode,AA.partname,AA.partspec,AA.saleOrderqty,isnull(BB.good_qty,0) as good_qty from(
|
select distinct E.wo,T.partcode,T.partname,T.partspec,sum(E.saleOrderqty) as saleOrderqty
|
from TKimp_Ewo E
|
left join TMateriel_Info T on E.materiel_code=T.partcode
|
where E.saleOrderCode=@saleOrderCode
|
group by E.wo,T.partcode,T.partname,T.partspec
|
) as AA
|
left join
|
(
|
select M.m_po,sum(S.good_qty) as good_qty from TK_Wrk_Man M
|
inner join TK_Wrk_Step S on M.wo_code=S.wo_code
|
where S.isend='Y' and S.good_qty>0
|
group by M.m_po
|
) as BB on AA.wo=BB.m_po";
|
dynamicParams.Add("@saleOrderCode", dt.Rows[i]["saleOrderCode"].ToString());
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
decimal good_qty = data.AsEnumerable().Select(d => d.Field<decimal>("good_qty")).Sum(); //报工总数量
|
cmp.ordergoodqty = good_qty.ToString();
|
for (int j = 0; j < data.Rows.Count; j++)
|
{
|
AppScheduleKanbanSub cbp = new AppScheduleKanbanSub();
|
cbp.code= data.Rows[j]["partcode"].ToString();
|
cbp.name = data.Rows[j]["partname"].ToString();
|
cbp.spec = data.Rows[j]["partspec"].ToString();
|
cbp.planqty= data.Rows[j]["saleOrderqty"].ToString();
|
cbp.goodqty = data.Rows[j]["good_qty"].ToString();
|
cmp.children.Add(cbp);
|
}
|
list.Add(cmp);
|
}
|
mes.code = "200";
|
mes.Message = "success!";
|
mes.data = list;
|
return mes;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "无符合要求单据！";
|
mes.data = null;
|
}
|
break;
|
case "MO": //按生产订单统计
|
if (partcode != "" && partcode != null)
|
{
|
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 (startopendate != "" && startopendate != null)
|
{
|
search += " and convert(varchar(100),A.createdate,21)>=@startopendate and convert(varchar(100),A.createdate,21)<=@endclosedate ";
|
dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
|
dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
|
}
|
//获取单据信息
|
sql = @"select distinct A.wo,A.createdate,sum(A.qty) as orderqty
|
from TKimp_Ewo A
|
where 1=1 "+search+" group by A.wo,A.createdate order by A.createdate desc";
|
dt = DapperHelper.selectlist(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
AppScheduleKanban cmp = new AppScheduleKanban();
|
cmp.ordercode = dt.Rows[i]["wo"].ToString();
|
cmp.orderqty = dt.Rows[i]["orderqty"].ToString();
|
cmp.children = new List<AppScheduleKanbanSub>();
|
//获取单据下产品完成信息
|
sql = @"select distinct E.wo,T.partcode,T.partname,T.partspec,sum(E.qty) as orderqty,isnull(BB.good_qty,0) as good_qty
|
from TKimp_Ewo E
|
left join TMateriel_Info T on E.materiel_code=T.partcode
|
left join (
|
select M.m_po,sum(S.good_qty) as good_qty from TK_Wrk_Man M
|
inner join TK_Wrk_Step S on M.wo_code=S.wo_code
|
where S.isend='Y' and S.good_qty>0
|
group by M.m_po
|
) as BB on E.wo=BB.m_po
|
where E.wo=@wo
|
group by E.wo,T.partcode,T.partname,T.partspec,BB.good_qty";
|
dynamicParams.Add("@wo", dt.Rows[i]["wo"].ToString());
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
decimal good_qty = data.AsEnumerable().Select(d => d.Field<decimal>("good_qty")).Sum(); //报工总数量
|
cmp.ordergoodqty = good_qty.ToString();
|
for (int j = 0; j < data.Rows.Count; j++)
|
{
|
AppScheduleKanbanSub cbp = new AppScheduleKanbanSub();
|
cbp.code = data.Rows[j]["partcode"].ToString();
|
cbp.name = data.Rows[j]["partname"].ToString();
|
cbp.spec = data.Rows[j]["partspec"].ToString();
|
cbp.planqty = data.Rows[j]["orderqty"].ToString();
|
cbp.goodqty = data.Rows[j]["good_qty"].ToString();
|
cmp.children.Add(cbp);
|
}
|
list.Add(cmp);
|
}
|
mes.code = "200";
|
mes.Message = "success!";
|
mes.data = list;
|
return mes;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "无符合要求单据！";
|
mes.data = null;
|
}
|
break;
|
case "PO": //按生产工单统计
|
if (partcode != "" && partcode != null)
|
{
|
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 (startopendate != "" && startopendate != null)
|
{
|
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,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 1=1 "+search+" group by A.wo_code,T.partcode,T.partname,T.partspec,A.lm_date order by A.lm_date desc";
|
dt = DapperHelper.selectlist(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
AppScheduleKanban cmp = new AppScheduleKanban();
|
cmp.ordercode = dt.Rows[i]["wo_code"].ToString();
|
cmp.partcode= dt.Rows[i]["partcode"].ToString();
|
cmp.partname = dt.Rows[i]["partname"].ToString();
|
cmp.partspec = dt.Rows[i]["partspec"].ToString();
|
cmp.orderqty = dt.Rows[i]["orderqty"].ToString();
|
cmp.children = new List<AppScheduleKanbanSub>();
|
//获取单据下产品完成信息
|
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=@wo_code
|
order by S.seq ";
|
dynamicParams.Add("@wo_code", dt.Rows[i]["wo_code"].ToString());
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
cmp.ordergoodqty = data.AsEnumerable().Where(t => t.Field<string>("isend") == "Y").ToList().Select(c => c.Field<decimal>("good_qty")).First().ToString();
|
for (int j = 0; j < data.Rows.Count; j++)
|
{
|
AppScheduleKanbanSub cbp = new AppScheduleKanbanSub();
|
cbp.code = data.Rows[j]["stepcode"].ToString();
|
cbp.name = data.Rows[j]["stepname"].ToString();
|
cbp.spec = data.Rows[j]["isend"].ToString();
|
cbp.planqty = data.Rows[j]["plan_qty"].ToString();
|
cbp.goodqty = data.Rows[j]["good_qty"].ToString();
|
cmp.children.Add(cbp);
|
}
|
list.Add(cmp);
|
}
|
else
|
{
|
cmp.ordergoodqty = "0";
|
list.Add(cmp);
|
}
|
}
|
mes.code = "200";
|
mes.Message = "success!";
|
mes.data = list;
|
return mes;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "无符合要求单据！";
|
mes.data = null;
|
}
|
break;
|
default:
|
break;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
