using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.DLL.DAL
|
{
|
public class 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 'end_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(S.report_qty),0)+isnull(sum(S.ng_qty),0)+isnull(sum(S.bad_qty),0)) as endqty,'完工数量' as 完工数量 from TK_Wrk_Man A
|
inner join TK_Wrk_Record R on A.wo_code=R.wo_code
|
inner join TK_Wrk_RecordSub S on R.id=S.m_id
|
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code and P.isend='Y'
|
where A.lm_date between @startopendate and @endclosedate
|
union all
|
select isnull(sum(S.report_qty),0) as report_qty,'合格产出' as 合格产出 from TK_Wrk_Man A
|
inner join TK_Wrk_Record R on A.wo_code=R.wo_code
|
inner join TK_Wrk_RecordSub S on R.id=S.m_id
|
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code and P.isend='Y'
|
where A.lm_date between @startopendate and @endclosedate
|
union all
|
select (isnull(sum(S.ng_qty),0)+isnull(sum(S.bad_qty),0)) as ngqty,'缺陷产出' as 缺陷产出 from TK_Wrk_Man A
|
inner join TK_Wrk_Record R on A.wo_code=R.wo_code
|
inner join TK_Wrk_RecordSub S on R.id=S.m_id
|
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_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 isnull(sum(A.plan_qty),0) as plan_qty,M.partname,isnull(sum(B.defect_qty),0) as defect_qty from TK_Wrk_Man A
|
inner join CSR_WorkRecord_Defect B on A.wo_code=B.wo_code and A.materiel_code=B.partnumber
|
inner join TMateriel_Info M on A.materiel_code=M.partcode
|
where A.lm_date between @startopendate and @endclosedate and B.defect_qty>0
|
group by M.partname
|
order by defect_qty desc";
|
break;
|
case "stp"://按工序统计
|
sql2 = @"select top 5 isnull(sum(A.plan_qty),0) as plan_qty,S.stepname,isnull(sum(B.defect_qty),0) as defect_qty from TK_Wrk_Step A
|
inner join CSR_WorkRecord_Defect B on A.wo_code=B.wo_code and A.step_code=B.step_code
|
inner join TStep S on A.step_code=S.stepcode
|
where A.lm_date between @startopendate and @endclosedate and B.defect_qty>0
|
group by S.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
|
}
|
}
|
