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(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 dir = new Dictionary(); try { //存储过程名(当前时间往前一周) string[] wkshoplist = Array.ConvertAll(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(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 dir = new Dictionary(); Dictionary dir0 = new Dictionary(); Dictionary dir1 = new Dictionary(); try { string[] wkshoplist = Array.ConvertAll(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("wkshp_code") == wkshoplist[i].ToString()) // 添加查询条件 .Select(group => new { wkshp_code = group.Field("wkshp_code"), plan_qty = group.Field("plan_qty"), good_qty = group.Field("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("wkshp_code") == wkshoplist[i].ToString()) // 添加查询条件 .Select(group => new { wkshp_code = group.Field("wkshp_code"), plan_qty = group.Field("plan_qty"), quantity = group.Field("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 dir = new Dictionary(); 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 dir = new Dictionary(); 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 dir = new Dictionary(); 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 } }