示例#1
0
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public FuncResult Select(FactWasteModel model)
        {
            var data = GetData(model);

            return(new FuncResult()
            {
                IsSuccess = true, Content = new { data }
            });
        }
        public IActionResult Export(DateTime?StartTime, DateTime?EndTime, string stRegion, string stCity, string stDM, string stStore)
        {
            var model = new FactWasteModel()
            {
                stCity    = stCity,
                StartTime = StartTime,
                EndTime   = EndTime,
                stDM      = stDM,
                stRegion  = stRegion,
                storeCode = stStore
            };
            var result = factWasteBLL.GetWasteListBytes(model);

            return(File(result, "application/ms-excel", $"Waste.xls"));
        }
示例#3
0
        private IEnumerable <FactWasteResultModel> GetData(FactWasteModel model)
        {
            //total = 0;
            //根据页码取出  当前页要展示的交易记录
            DateTime _start, _end;

            _start = model.StartTime != null ? (DateTime)
                     model.StartTime : Convert.ToDateTime((DateTime.Now.AddDays(-10)).ToShortDateString());

            _end = model.EndTime != null ? (DateTime)
                   model.EndTime : Convert.ToDateTime((DateTime.Now.AddDays(-1)).ToShortDateString());



            var stcodes = _context.OdsStoreMasters.Where(b => (string.IsNullOrWhiteSpace(model.stCity) || b.Stcity.Contains(b.Stcity)) &&
                                                         (string.IsNullOrWhiteSpace(model.stRegion) || b.Stregion.Contains(model.stRegion)) &&
                                                         (string.IsNullOrWhiteSpace(model.stDM) || b.Stdm.Contains(model.stDM)) &&
                                                         (string.IsNullOrWhiteSpace(model.storeCode) || b.Stcode.Contains(model.storeCode))).Select(e => e.Stcode).ToList();


            var query = (from a in _context.FactWastes
                         where a.waste_Date >= _start && a.waste_Date <= _end && (stcodes.Count <= 0 || stcodes.Contains(a.store_Code))
                         group a by new { a.product_Code, a.product_Name, a.product_Dishcolor, a.product_Price, a.product_Type, a.product_Cost_Price }
                         into g
                         select new
            {
                productCode = g.Key.product_Code,
                productName = g.Key.product_Name,
                productDishcolor = g.Key.product_Dishcolor,
                productPrice = g.Key.product_Price,
                productType = g.Key.product_Type,
                productCostPrice = g.Key.product_Cost_Price == null?0:g.Key.product_Cost_Price,
                ProductTotalWasteAmountBelt1300 = g.Sum(su => su.waste_Amount_Belt_1300),
                ProductTotalWasteAmountBelt1400 = g.Sum(su => su.waste_Amount_Belt_1400),
                ProductTotalWasteAmountBelt1500 = g.Sum(su => su.waste_Amount_Belt_1500),
                ProductTotalWasteAmountBelt1600 = g.Sum(su => su.waste_Amount_Belt_1600),
                ProductTotalWasteAmountBelt1700 = g.Sum(su => su.waste_Amount_Belt_1700),
                ProductTotalWasteAmountBelt1800 = g.Sum(su => su.waste_Amount_Belt_1800),
                ProductTotalWasteAmountBelt1900 = g.Sum(su => su.waste_Amount_Belt_1900),
                ProductTotalWasteAmountBelt2000 = g.Sum(su => su.waste_Amount_Belt_2000),
                ProductTotalWasteAmountBelt2100 = g.Sum(su => su.waste_Amount_Belt_2100),
                ProductTotalWasteAmountBelt2200 = g.Sum(su => su.waste_Amount_Belt_2200),
                ProductTotalWasteAmountBeltALL = g.Sum(su => su.waste_Amount_Belt_all),
                ProductTotalProduction_Amount = g.Sum(su => su.production_Amount),
                //waste%

                ProductTotalWasteAmountNonBeltAll = g.Sum(su => su.waste_Amount_Non_Belt_All),
                ProductTotalWasteAmountAll = g.Sum(su => su.waste_Amount_All),
                ProductTotalWasteMoneyAll = g.Sum(su => su.waste_Money_All),
                ProductTotalWasteCostAll = g.Sum(su => su.waste_Cost_All),
            }).ToList();
            ////关联表
            //var product_query = (from a in _context.DimProducts
            //                     join b in query2 on a.code equals b.productCode
            //                     select new
            //                     {
            //                         a.price,
            //                         a.cost,
            //                         b.productCode,
            //                         a.name,
            //                         a.dish_Color,
            //                         a.category,
            //                         b.ProductTotalWasteAmountBelt1300,
            //                         b.ProductTotalWasteAmountBelt1400,
            //                         b.ProductTotalWasteAmountBelt1500,
            //                         b.ProductTotalWasteAmountBelt1600,
            //                         b.ProductTotalWasteAmountBelt1700,
            //                         b.ProductTotalWasteAmountBelt1800,
            //                         b.ProductTotalWasteAmountBelt1900,
            //                         b.ProductTotalWasteAmountBelt2000,
            //                         b.ProductTotalWasteAmountBelt2100,
            //                         b.ProductTotalWasteAmountBelt2200,
            //                         b.ProductTotalWasteAmountBeltALL,
            //                         b.ProductTotalProduction_Amount,
            //                         b.ProductTotalWasteAmountNonBeltAll,
            //                         b.ProductTotalWasteAmountAll,
            //                         b.ProductTotalWasteMoneyAll,
            //                         b.ProductTotalWasteCostAll
            //                     }).ToList();

            var result = query.Select(a => new FactWasteResultModel
            {
                price               = a.productPrice,
                cost                = a.productCostPrice == null?0 : a.productCostPrice,
                productName         = a.productName,
                productCode         = a.productCode,
                dish_Color          = a.productDishcolor,
                category            = a.productType,
                wasteAmountBelt1300 = (int)a.ProductTotalWasteAmountBelt1300,
                wasteAmountBelt1400 = (int)a.ProductTotalWasteAmountBelt1400,
                wasteAmountBelt1500 = (int)a.ProductTotalWasteAmountBelt1500,
                wasteAmountBelt1600 = (int)a.ProductTotalWasteAmountBelt1600,
                wasteAmountBelt1700 = (int)a.ProductTotalWasteAmountBelt1700,
                wasteAmountBelt1800 = (int)a.ProductTotalWasteAmountBelt1800,
                wasteAmountBelt1900 = (int)a.ProductTotalWasteAmountBelt1900,
                wasteAmountBelt2000 = (int)a.ProductTotalWasteAmountBelt2000,
                wasteAmountBelt2100 = (int)a.ProductTotalWasteAmountBelt2100,
                wasteAmountBelt2200 = (int)a.ProductTotalWasteAmountBelt2200,
                //Waste合计
                wasteAmountBeltall = a.ProductTotalWasteAmountBeltALL,
                //制作数
                productionAmount = a.ProductTotalProduction_Amount,
                //Waste%
                wasteBeltPercent = (a.ProductTotalProduction_Amount == 0 ? 0 :a.ProductTotalProduction_Amount / a.ProductTotalWasteAmountBeltALL).ToString("#0.00") + "%",
                //nonbelt
                wasteAmountNonBeltAll = (int)a.ProductTotalWasteAmountNonBeltAll,
                //数量
                wasteAmountAll = (int)a.ProductTotalWasteAmountAll,
                //金额
                wasteMoneyAll = (int)a.ProductTotalWasteMoneyAll,
                //成本
                wasteCostAll = a.ProductTotalWasteCostAll == null?0:(int)a.ProductTotalWasteCostAll,
            }).ToList();


            //合计
            var addCount = new FactWasteResultModel
            {
                productCode = "汇总:",
            };

            result.Add(addCount);
            return(result);
        }
示例#4
0
        /// <summary>
        /// 导出按钮事件
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public byte[] GetWasteListBytes(FactWasteModel model)
        {
            var comlumHeadrs = new[] { "产品编号", "产品名称", "产品类别", "单价", "成本价", "色碟", "13:00", "14:00", "15:00", "16:00", "17:00", "18:00", "19:00", "20:00", "21:00", "22:00", "Waste合计", "制作数", "Waste%", "Watste数", "数量", "金额", "成本" };

            byte[] result;
            //使用查询得出结果
            var data = GetData(model);

            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("Sheet1"); //Worksheet name

                worksheet.Cells["A1:F1"].Merge = true;
                worksheet.Cells[1, 1].Value    = "条件";
                worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;


                worksheet.Cells["G1:P1"].Merge = true;
                worksheet.Cells[1, 7].Value    = "Belt Waste";
                worksheet.Cells[1, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                var fill1 = worksheet.Cells[1, 7].Style.Fill;
                fill1.PatternType = ExcelFillStyle.Solid;
                fill1.BackgroundColor.SetColor(Color.Yellow);
                worksheet.Cells["Q1:S1"].Merge = true;
                worksheet.Cells[1, 17].Value   = "Belt";
                worksheet.Cells[1, 17].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;


                worksheet.Cells["T1:T1"].Merge = true;
                worksheet.Cells[1, 20].Value   = "NonBelt";
                worksheet.Cells[1, 20].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                worksheet.Cells["U1:W1"].Merge = true;
                worksheet.Cells[1, 21].Value   = "总计Waste";
                worksheet.Cells[1, 21].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                //First add the headers
                for (var i = 0; i < comlumHeadrs.Count(); i++)
                {
                    worksheet.Cells[2, i + 1].Value = comlumHeadrs[i];
                    worksheet.Cells[2, i + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    //worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
                }
                //Add values
                var j = 3;
                // var chars = new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
                foreach (var obj in data)
                {
                    var rt = obj.GetType();
                    var rp = rt.GetProperties();

                    worksheet.Cells["A" + j].Value = obj.productCode;
                    //worksheet.Cells[1, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    worksheet.Cells["A" + j].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    worksheet.Cells["B" + j].Value = obj.productName;
                    worksheet.Cells["C" + j].Value = obj.category;
                    worksheet.Cells["D" + j].Value = obj.price;
                    worksheet.Cells["E" + j].Value = obj.cost;
                    worksheet.Cells["F" + j].Value = obj.dish_Color;//色碟

                    worksheet.Cells["G" + j].Value = obj.wasteAmountBelt1300;
                    worksheet.Cells["H" + j].Value = obj.wasteAmountBelt1400;
                    worksheet.Cells["I" + j].Value = obj.wasteAmountBelt1500;
                    worksheet.Cells["J" + j].Value = obj.wasteAmountBelt1600;
                    worksheet.Cells["K" + j].Value = obj.wasteAmountBelt1700;
                    worksheet.Cells["L" + j].Value = obj.wasteAmountBelt1800;

                    worksheet.Cells["M" + j].Value = obj.wasteAmountBelt1900;
                    worksheet.Cells["N" + j].Value = obj.wasteAmountBelt2000;
                    worksheet.Cells["O" + j].Value = obj.wasteAmountBelt2100;
                    worksheet.Cells["P" + j].Value = obj.wasteAmountBelt2200;
                    worksheet.Cells["Q" + j].Value = obj.wasteAmountBeltall;
                    worksheet.Cells["R" + j].Value = obj.productionAmount;
                    worksheet.Cells["S" + j].Value = obj.wasteBeltPercent;
                    worksheet.Cells["T" + j].Value = obj.wasteAmountNonBeltAll;
                    worksheet.Cells["U" + j].Value = obj.wasteAmountAll;
                    worksheet.Cells["V" + j].Value = obj.wasteMoneyAll;
                    worksheet.Cells["W" + j].Value = obj.wasteCostAll;
                    j++;
                }
                result = package.GetAsByteArray();
            }
            return(result);
        }
        public FuncResult Select([FromBody] FactWasteModel model)
        {
            //接受传递过来的参数处理.

            return(factWasteBLL.Select(model));
        }