using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.DLL.DAL
|
{
|
public class KanBanManagerentDAL
|
{
|
public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
|
public static string ErpPath = AppSetting.GetAppSetting("ErpPath"); //获取ERP配置
|
|
|
#region[车间看板,上生产信息]
|
public static ToMessage WorkShopProduceTopData(string wkshopcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
string[] wkshoplist = Array.ConvertAll<string, string>(wkshopcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[] //存储过程名
|
sql = @"select * from h_v_JLKanBan_WorkshopTop where wkshp_code in @wkshoplist";
|
dynamicParams.Add("@wkshoplist", wkshoplist);
|
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 WorkShopProduceBottomLeftData(string wkshopcode)
|
{
|
string sql = "";
|
Dictionary<object, object> dir = new Dictionary<object, object>();
|
try
|
{
|
//存储过程名(当前时间往前一周)
|
string[] wkshoplist = Array.ConvertAll<string, string>(wkshopcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
sql = @"h_p_JLKanBan_WorkshopBottomLeft";
|
for (int i = 0; i < wkshoplist.Length; i++)
|
{
|
var dynamicParams = new DynamicParameters();
|
dynamicParams.Add("@wkshop_code", wkshoplist[i].ToString());
|
var dt = DapperHelper.selectProcedure(sql, dynamicParams);
|
dir.Add(wkshoplist[i].ToString(), dt);
|
}
|
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = dir;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[车间看板,下右近一月不良统计]
|
public static ToMessage WorkShopProduceBottomRightData(string wkshopcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
string[] wkshoplist = Array.ConvertAll<string, string>(wkshopcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
sql = @"select * from h_v_JLKanBan_WorkshopBottomRight where wkshp_code in @wkshoplist";
|
dynamicParams.Add("@wkshoplist", wkshoplist);
|
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 WorkShopCompreLeftTop(string wkshopcode)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
Dictionary<object, object> dir = new Dictionary<object, object>();
|
Dictionary<object, object> dir0 = new Dictionary<object, object>();
|
Dictionary<object, object> dir1 = new Dictionary<object, object>();
|
try
|
{
|
string[] wkshoplist = Array.ConvertAll<string, string>(wkshopcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWorkShopCompreLeftTop";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WorkShopCompreLeftTop";
|
break;
|
default:
|
break;
|
}
|
var data = DapperHelper.selecttable(sql);
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWorkShopCompreLeftCenter";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WorkShopCompreLeftCenter";
|
break;
|
default:
|
break;
|
}
|
var data1 = DapperHelper.selecttable(sql);
|
for (int i = 0; i < wkshoplist.Length; i++)
|
{
|
var top0 = data.AsEnumerable()
|
.Where(row => row.Field<string>("wkshp_code") == wkshoplist[i].ToString()) // 添加查询条件
|
.Select(group => new
|
{
|
wkshp_code = group.Field<string>("wkshp_code"),
|
plan_qty = group.Field<decimal>("plan_qty"),
|
good_qty = group.Field<decimal>("good_qty")
|
}).DefaultIfEmpty(new { wkshp_code = wkshoplist[i].ToString(), plan_qty = 0m, good_qty = 0m })//为空时指定默认值
|
.ToList();
|
dir0.Add(wkshoplist[i].ToString(), top0);
|
|
var top1 = data1.AsEnumerable()
|
.Where(row => row.Field<string>("wkshp_code") == wkshoplist[i].ToString()) // 添加查询条件
|
.Select(group => new
|
{
|
wkshp_code = group.Field<string>("wkshp_code"),
|
plan_qty = group.Field<decimal>("plan_qty"),
|
quantity = group.Field<decimal>("quantity")
|
}).DefaultIfEmpty(new { wkshp_code = wkshoplist[i].ToString(), plan_qty = 0m, quantity = 0m })//为空时指定默认值
|
.ToList();
|
dir1.Add(wkshoplist[i].ToString(), top1);
|
}
|
dir.Add("LeftTop", dir0);
|
dir.Add("LeftBottom", dir1);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = dir;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[车间综合看板,右上销售订单]
|
public static ToMessage WorkShopCompreRightTop()
|
{
|
var sql = "";
|
Dictionary<object, object> dir = new Dictionary<object, object>();
|
try
|
{
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWorkShopCompreRightTop";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WorkShopCompreRightTop";
|
break;
|
default:
|
break;
|
}
|
var data = DapperHelper.selecttable(sql);
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWorkShopCompreRightCenter";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WorkShopCompreRightCenter";
|
break;
|
default:
|
break;
|
}
|
|
var data1 = DapperHelper.selecttable(sql);
|
dir.Add("RightTop", data);
|
dir.Add("RightBottom", data1);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = dir;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[车间综合看板,左下采购订单]
|
public static ToMessage WorkShopCompreLeftBottom()
|
{
|
var sql = "";
|
Dictionary<object, object> dir = new Dictionary<object, object>();
|
try
|
{
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWorkShopCompreLeftBottom_Top";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WorkShopCompreLeftBottom_Top";
|
break;
|
default:
|
break;
|
}
|
var data = DapperHelper.selecttable(sql);
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWorkShopCompreLeftBottom_Bottom";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WorkShopCompreLeftBottom_Bottom";
|
break;
|
default:
|
break;
|
}
|
var data1 = DapperHelper.selecttable(sql);
|
dir.Add("BottomLeftTop", data);
|
dir.Add("BottomLeftBottom", data1);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = dir;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[车间综合看板,右下库存]
|
public static ToMessage WorkShopCompreRightBottom()
|
{
|
var sql = "";
|
Dictionary<object, object> dir = new Dictionary<object, object>();
|
try
|
{
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWorkShopCompreRightBottom";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WorkShopCompreRightBottom";
|
break;
|
default:
|
break;
|
}
|
var data = DapperHelper.selecttable(sql);
|
dir.Add("Y", data.Rows[0]["BaseQuantity"].ToString());
|
dir.Add("B", data.Rows[1]["BaseQuantity"].ToString());
|
dir.Add("C", data.Rows[2]["BaseQuantity"].ToString());
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = dir;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[仓库看板,左上产品待入库列表]
|
public static ToMessage WareHouseTopLeftData()
|
{
|
string sql = "";
|
try
|
{
|
//获取仓库生产加工单待入库列表
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWareHouseTopLeft order by voucherdate";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WareHouseTopLeft order by voucherdate";
|
break;
|
default:
|
break;
|
}
|
var data = DapperHelper.selecttable(sql);
|
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 WareHouseTopBottomData()
|
{
|
string sql = "";
|
try
|
{
|
//获取仓库生产加工单待入库列表
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWareHouseTopBottom order by voucherdate";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WareHouseTopBottom order by voucherdate";
|
break;
|
default:
|
break;
|
}
|
var data = DapperHelper.selecttable(sql);
|
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[仓库看板,右上成品库、半成品库top5库存排行]
|
public static ToMessage WareHouseRightTopData()
|
{
|
string sql = "";
|
try
|
{
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWareHouseRightTop";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WareHouseRightTop";
|
break;
|
default:
|
break;
|
}
|
var data = DapperHelper.selecttable(sql);
|
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[仓库看板,右下原料库库top5库存排行]
|
public static ToMessage WareHouseRightBottomData()
|
{
|
string sql = "";
|
try
|
{
|
switch (ErpPath)
|
{
|
case "T+":
|
sql = @"select * from h_v_JLKanBan_TCloundWareHouseRightBottom";
|
break;
|
case "T8":
|
sql = @"select * from h_v_JLKanBan_T8WareHouseRightBottom";
|
break;
|
default:
|
break;
|
}
|
var data = DapperHelper.selecttable(sql);
|
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 PurchaseLeftTop()
|
{
|
string sql = "";
|
try
|
{
|
//获取本月采购订单数、本月采购进货单数、本月采购入库单数
|
sql = @"select * from h_v_SFKanBan_PurchaseLeftTop ";
|
var data = DapperHelper.selecttable(sql);
|
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 PurchaseLeftBottom()
|
{
|
string sql = "";
|
try
|
{
|
//获取采购订单跟踪列表
|
sql = @"select * from h_v_SFKanBan_PurchaseLeftBottom ";
|
var data = DapperHelper.selecttable(sql);
|
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[采购订单跟踪管理看板,右侧Top]
|
public static ToMessage PurchaseRight()
|
{
|
string sql = "";
|
try
|
{
|
//获取采购订单跟踪列表
|
sql = @"select * from h_v_SFKanBan_PurchaseRight ";
|
var data = DapperHelper.selecttable(sql);
|
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 ProcureOutsourcLeftTop()
|
{
|
var sql = "";
|
try
|
{
|
sql = @"select * from h_v_SFKanBan_ProcureNotReceived";
|
var data = DapperHelper.selecttable(sql);
|
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 ProductConsignmentLeftBottom()
|
{
|
var sql = "";
|
try
|
{
|
sql = @"select * from h_v_SFKanBan_ProductionNotDeliver";
|
var data = DapperHelper.selecttable(sql);
|
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[大岛仓库看板右上,近一周采购待入库物料排行TOP5]
|
public static ToMessage ProcureOutsourcRightTop()
|
{
|
var sql = "";
|
try
|
{
|
sql = @"select * from h_v_SFKanBan_ProcureNotReceivedSumTop";
|
var data = DapperHelper.selecttable(sql);
|
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[大岛仓库看板右下,近一周销售待发货产品排行TOP5]
|
public static ToMessage ProductionStockedInRightBottom()
|
{
|
var sql = "";
|
try
|
{
|
sql = @"select * from h_v_SFKanBan_ProductionNotDeliverSumTop";
|
var data = DapperHelper.selecttable(sql);
|
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
|
}
|
}
|
