public IHttpActionResult Post([FromBody] SalesViewApiModel value) { HttpStatusCode code = service.SetSalesView(value); if (code != HttpStatusCode.OK) { return(StatusCode(code)); } SalesViewApiModel resultModel; SalesViewApiParameterModel param = new SalesViewApiParameterModel(); param.Year = value.SalesList.Select(x => x.detail_date).Min().Year; param.Year += 1; code = service.GetSalesView(param, out resultModel, value.Product.Id); if (code == HttpStatusCode.OK) { return(Ok(resultModel)); } else { return(StatusCode(code)); } }
public IHttpActionResult GetSalesTrend(int id, int tid, [FromUri] SalesViewApiParameterModel param) { try { var trends = dbContext.SalesTrendModels .Where(st => st.Id == tid) .Select(st => new { id = st.Id, product_id = st.ProductModelId, detail_date = st.TargetDate, quantity = st.Sales, comments = st.Comments, user_id = st.UserModelId, user_name = st.UserModel.Name }).SingleOrDefault(); if (trends == null) { WriteAppLog("SalesViews/" + id.ToString() + "/Trends/" + tid.ToString(), "データが存在しない"); return(NotFound()); } WriteAppLog("SalesViews/" + id.ToString() + "/Trends/" + tid.ToString(), "処理成功"); return(Ok(trends)); } catch (Exception) { WriteAppLog("SalesViews/" + id.ToString() + "/Trends/" + tid.ToString(), "不正な処理が発生"); return(BadRequest()); } }
/// <summary> /// 条件に合致する商品一覧の抽出 /// </summary> /// <param name="param">条件パラメーター</param> /// <returns>商品リスト</returns> private List <ProductApiModel> GetProductList(SalesViewApiParameterModel param) { try { IQueryable <ProductModel> query; if (param.GroupId.HasValue) { this.logMessage += " GroupId=" + param.GroupId.ToString(); query = dbContext.GroupProductModels.Where(gp => gp.Deleted == false).Where(gp => gp.GroupModelId == (int)param.GroupId).Select(gp => gp.ProductModel).OrderBy(p => p.Id); } else if (param.MakerId.HasValue) { this.logMessage += " MakerId=" + param.MakerId.ToString(); query = dbContext.ProductModels.Where(p => p.MakerModelId == (int)param.MakerId).OrderBy(p => p.Id); } else { query = dbContext.ProductModels.OrderBy(p => p.Id); } if (!param.Deleted) { this.logMessage += " Deleted=false"; query = query.Where(p => p.Deleted == false); } if (param.Enabled) { this.logMessage += " Enabled=true"; query = query.Where(p => p.Enabled == true); } if (param.Limit.HasValue) { if (param.Page.HasValue) { this.logMessage += " Limit=" + param.Limit.ToString() + " Page=" + param.Page.ToString(); query = query.Skip((int)param.Limit * (int)param.Page).Take((int)param.Limit); } else { this.logMessage += " Limit=" + param.Limit.ToString(); query = query.Take((int)param.Limit); } } return(query.ProjectTo <ProductApiModel>().ToList()); } catch (Exception ex) { WriteAppLog(this.ToString() + ".GetProductList", "内部エラー発生:" + ex.Message); return(null); } }
public IHttpActionResult Get(int id, [FromUri] SalesViewApiParameterModel param) { SalesViewApiModel result; HttpStatusCode code = service.GetSalesView(param, out result, id); if (code == HttpStatusCode.OK) { return(Ok(result)); } else { return(StatusCode(code)); } }
public IHttpActionResult GetCurrent(int id, [FromUri] SalesViewApiParameterModel param) { ProductModel product = dbContext.ProductModels.Where(p => p.Id == id).SingleOrDefault(); if (product == null) { WriteAppLog("SalesViews/" + id.ToString() + "/Current", "パラメーターエラー:商品が存在しない"); return(BadRequest()); } var stocks = dbContext.CurrentStockModels .Where(cs => cs.Deleted == false) .Where(cs => cs.ProductModelId == product.Id) .Select(cs => new { cs.WarehouseCode, cs.WarehouseName, cs.StateName, cs.ExpirationDate, cs.LogicalQuantity, cs.ActualQuantity }) .OrderBy(no => no.WarehouseCode).ThenBy(no => no.ExpirationDate).ThenBy(no => no.StateName) .ToList(); var stockMaxDate = dbContext.CurrentStockModels .Where(cs => cs.Deleted == false) .Where(cs => cs.ProductModelId == product.Id) .Select(cs => cs.ModifiedDateTime).Max(); var orders = dbContext.OrderModels .Where(od => od.Deleted == false) .Where(od => od.ProductModelId == product.Id) .Select(od => new { od.OrderNo, od.OrderDate, od.Order }).ToList(); var orderMaxDate = dbContext.OrderModels .Where(od => od.Deleted == false) .Where(od => od.ProductModelId == product.Id) .Select(od => od.ModifiedDateTime).Max(); var invoices = dbContext.InvoiceModels .Where(iv => iv.Deleted == false) .Where(iv => iv.ProductModelId == product.Id) .Select(iv => new { iv.InvoiceNo, iv.WarehouseCode, iv.ETA, iv.CustomsClearanceDate, iv.PurchaseDate, iv.Quantity }).ToList(); var invoiceMaxDate = dbContext.InvoiceModels .Where(iv => iv.Deleted == false) .Where(iv => iv.ProductModelId == product.Id) .Select(iv => iv.ModifiedDateTime).Max(); WriteAppLog("SalesViews/" + id.ToString() + "/Current", "処理成功"); return(Ok(new { stocks, orders, invoices, stockMaxDate, orderMaxDate, invoiceMaxDate })); }
public IHttpActionResult DeleteSalesTrend(int id, int tid, [FromUri] SalesViewApiParameterModel param) { if (tid == 0) { WriteAppLog("DELETE SalesViews/" + id.ToString() + "/Trends/" + tid.ToString(), "パラメーターエラー"); return(BadRequest()); } SalesTrendModel work = dbContext.SalesTrendModels.Where(st => st.Id == tid).SingleOrDefault(); if (work == null) { WriteAppLog("DELETE SalesViews/" + id.ToString() + "/Trends/" + tid.ToString(), "データが存在しない"); return(NotFound()); } work.Deleted = true; dbContext.SaveChanges(); WriteAppLog("DELETE SalesViews/" + id.ToString() + "/Trends/" + tid.ToString(), "処理成功"); return(Ok()); }
public IHttpActionResult GetSalesTrend(int id, [FromUri] SalesViewApiParameterModel param) { ProductModel product = dbContext.ProductModels.Where(p => p.Id == id).SingleOrDefault(); if (product == null) { WriteAppLog("SalesViews/" + id.ToString() + "/Trends", "パラメーターエラー:商品が存在しない"); return(BadRequest()); } // パラメータは必須 if (param.Year.HasValue == false) { WriteAppLog("SalesViews/" + id.ToString() + "/Trends", "パラメーターエラー:年度未指定"); return(StatusCode(HttpStatusCode.BadRequest)); } DateTime startDate = DateTime.Parse((param.Year - 1).ToString() + "/10/1"); DateTime endDate = startDate.AddYears(1); var trends = dbContext.SalesTrendModels .Where(st => st.ProductModelId == product.Id).Where(st => st.Deleted == false) .Where(st => st.TargetDate >= startDate).Where(st => st.TargetDate < endDate) .Select(st => new { id = st.Id, product_id = st.ProductModelId, detail_date = st.TargetDate, quantity = st.Sales, comments = st.Comments, user_id = st.UserModelId, user_name = st.UserModel.Name }).ToList(); WriteAppLog("SalesViews/" + id.ToString() + "/Trends", "処理成功(条件 Year=" + param.Year.ToString() + ")"); return(Ok(trends)); }
/// <summary> /// 販売データを取得して返却用オブジェクトを生成する /// </summary> /// <param name="param">条件パラメーター</param> /// <param name="result">返却用オブジェクト</param> /// <param name="months">取得期間(単位は月、省略時は12ヶ月)</param> /// <returns>処理結果のステータスコード</returns> public HttpStatusCode GetSalesViews(SalesViewApiParameterModel param, out List <SalesViewApiModel> result, int months = 12) { this.logMessage = string.Empty; result = null; try { // 必須パラメーターのチェック if (param.GroupId.HasValue == false && param.MakerId.HasValue == false) { WriteAppLog(this.ToString() + ".GetSalesViews", ErrorMessage.CKeyError); return(HttpStatusCode.BadRequest); } if (param.Year.HasValue == false) { WriteAppLog(this.ToString() + ".GetSalesViews", ErrorMessage.CYearError); return(HttpStatusCode.BadRequest); } this.logMessage += " Year=" + param.Year.ToString(); // 商品一覧の取得 List <ProductApiModel> products = this.GetProductList(param); List <int> productIds = products.Select(p => p.Id).ToList <int>(); // 事業所リストの取得 ICollection <OfficeModel> office = dbContext.OfficeModels.Where(o => o.Deleted == false).OrderBy(o => o.Code).ToList(); // データ取得準備 string productList = string.Join(",", productIds); string sql = ContextResources.SelectSalesViews.Replace("@p2", productList); // 全体の販売実績データ List <SalesViewsTempModel> salesList; DateTime startDate = DateTime.Parse((param.Year - 1).ToString() + "/10/1"); DateTime endDate = startDate.AddMonths(months); salesList = dbContext.Database.SqlQuery <SalesViewsTempModel>(sql, startDate, endDate).ToList <SalesViewsTempModel>(); // 事業所別 List <SalesOfficeTempModel> salesOfficeList; sql = ContextResources.SelectOfficesSalesViews.Replace("@p2", productList); salesOfficeList = dbContext.Database.SqlQuery <SalesOfficeTempModel>( sql, startDate.AddYears(-1), endDate ).ToList <SalesOfficeTempModel>(); // 返却用の準備 result = new List <SalesViewApiModel>(); foreach (var product in products) { // 在庫予測計算用に1ヶ月多めに取得(貿易のみ) DateTime check = startDate.AddMonths(-1);; SalesViewApiModel addModel = new SalesViewApiModel(); addModel.Product = product; addModel.SalesList = new List <SalesViewsTempModel>(); addModel.OfficeSales = new List <ICollection <SalesOfficeTempModel> >(); for (; check <= endDate; check = check.AddMonths(1)) { SalesViewsTempModel work = salesList.Where(x => x.product_id == addModel.Product.Id) .Where(x => x.detail_date == check).SingleOrDefault(); if (work == null) { SalesViewsTempModel tempModel = new SalesViewsTempModel(); tempModel.product_id = addModel.Product.Id; tempModel.detail_date = check; addModel.SalesList.Add(tempModel); } else { addModel.SalesList.Add(work); } ICollection <SalesOfficeTempModel> workOffice = new List <SalesOfficeTempModel>(); foreach (var ofs in office) { SalesOfficeTempModel ofsData = salesOfficeList.Where(x => x.product_id == addModel.Product.Id) .Where(x => x.detail_date == check).Where(x => x.office_id == ofs.Id).SingleOrDefault(); if (ofsData == null) { SalesOfficeTempModel tempModel = new SalesOfficeTempModel(); tempModel.product_id = product.Id; tempModel.detail_date = check; tempModel.office_id = ofs.Id; tempModel.office_name = ofs.Name; workOffice.Add(tempModel); } else { workOffice.Add(ofsData); } } addModel.OfficeSales.Add(workOffice); } result.Add(addModel); } WriteAppLog(this.ToString() + ".GetSalesViews", ErrorMessage.CCompleteMulti.Replace("@message", this.logMessage)); return(HttpStatusCode.OK); } catch (Exception ex) { WriteAppLog(this.ToString() + ".GetSalesViews", ErrorMessage.CException + ex.Message); return(HttpStatusCode.InternalServerError); } }
/// <summary> /// 販売データを取得して返却用オブジェクトを生成する /// </summary> /// <param name="param">条件パラメーター</param> /// <param name="result">返却用オブジェクト</param> /// <param name="productId">対象商品ID</param> /// <param name="months">取得期間(単位は月、省略時は12ヶ月)</param> /// <returns>処理結果のステータスコード</returns> public HttpStatusCode GetSalesView(SalesViewApiParameterModel param, out SalesViewApiModel result, int productId = 0, int months = 12) { this.logMessage = string.Empty; result = null; try { // パラメータは必須 if (productId <= 0) { WriteAppLog(this.ToString() + ".GetSalesView/" + productId.ToString(), ErrorMessage.CParamError); return(HttpStatusCode.BadRequest); } if (param.Year.HasValue == false) { WriteAppLog(this.ToString() + ".GetSalesView/" + productId.ToString(), ErrorMessage.CYearError); return(HttpStatusCode.BadRequest); } ProductApiModel product = dbContext.ProductModels.Where(p => p.Id == productId).ProjectTo <ProductApiModel>().SingleOrDefault(); if (product == null) { WriteAppLog(this.ToString() + ".GetSalesView/" + productId.ToString(), ErrorMessage.CProductError); return(HttpStatusCode.BadRequest); } List <SalesViewsTempModel> salesList; DateTime startDate = DateTime.Parse((param.Year - 1).ToString() + "/10/1"); DateTime endDate = startDate.AddMonths(months); salesList = dbContext.Database.SqlQuery <SalesViewsTempModel>( ContextResources.SelectSalesViews, startDate, endDate, product.Id ).ToList <SalesViewsTempModel>(); ICollection <OfficeModel> office = dbContext.OfficeModels.Where(o => o.Deleted == false).OrderBy(o => o.Code).ToList(); List <SalesOfficeTempModel> salesOfficeList; salesOfficeList = dbContext.Database.SqlQuery <SalesOfficeTempModel>( ContextResources.SelectOfficesSalesViews, startDate, endDate, product.Id ).ToList <SalesOfficeTempModel>(); // 在庫予測計算用に1ヶ月多めに取得(貿易のみ) DateTime check = startDate.AddMonths(-1); result = new SalesViewApiModel(); result.Product = product; result.SalesList = new List <SalesViewsTempModel>(); result.OfficeSales = new List <ICollection <SalesOfficeTempModel> >(); for (; check <= endDate; check = check.AddMonths(1)) { SalesViewsTempModel work = salesList.Where(x => x.product_id == product.Id) .Where(x => x.detail_date == check).SingleOrDefault(); if (work == null) { SalesViewsTempModel tempModel = new SalesViewsTempModel(); tempModel.product_id = product.Id; tempModel.detail_date = check; result.SalesList.Add(tempModel); } else { result.SalesList.Add(work); } ICollection <SalesOfficeTempModel> workOffice = new List <SalesOfficeTempModel>(); foreach (var value in office) { SalesOfficeTempModel wk = salesOfficeList.Where(so => so.detail_date == check).Where(so => so.office_id == value.Id).SingleOrDefault(); if (wk == null) { SalesOfficeTempModel tempModel = new SalesOfficeTempModel(); tempModel.product_id = product.Id; tempModel.detail_date = check; tempModel.office_id = value.Id; tempModel.office_name = value.Name; tempModel.sales_plan = 0; tempModel.sales_actual = 0; workOffice.Add(tempModel); } else { workOffice.Add(wk); } } result.OfficeSales.Add(workOffice); } WriteAppLog(this.ToString() + ".GetSalesView/" + productId.ToString(), ErrorMessage.CCompleteSingle.Replace("@year", param.Year.ToString())); return(HttpStatusCode.OK); } catch (Exception ex) { WriteAppLog(this.ToString() + ".GetSalesView/" + productId.ToString(), ErrorMessage.CException + ex.Message); return(HttpStatusCode.InternalServerError); } }
public IHttpActionResult PutSalesTrend(int id, int tid, [FromBody] TrendApiModel model, [FromUri] SalesViewApiParameterModel param) { if (tid == 0 || model == null || model.Id == 0) { WriteAppLog("PUT SalesViews/" + id.ToString() + "/Trends/" + tid.ToString(), "パラメーターエラー"); return(BadRequest()); } SalesTrendModel work = dbContext.SalesTrendModels.Where(st => st.Id == tid).SingleOrDefault(); if (work == null) { WriteAppLog("PUT SalesViews/" + id.ToString() + "/Trends/" + tid.ToString(), "データが存在しない"); return(NotFound()); } work.TargetDate = model.Detail_date; work.Sales = model.Quantity; work.Comments = model.Comments; work.UserModelId = model.User_id; dbContext.SaveChanges(); model.User_name = dbContext.UserModels.Where(um => um.Id == model.User_id).Select(um => um.Name).SingleOrDefault(); WriteAppLog("PUT SalesViews/" + id.ToString() + "/Trends/" + tid.ToString(), "処理成功"); return(Ok(model)); }
public IHttpActionResult PostSalesTrend(int id, [FromBody] TrendApiModel model, [FromUri] SalesViewApiParameterModel param) { if (model == null || model.Id != 0) { WriteAppLog("POST SalesViews/" + id.ToString() + "/Trends", "パラメーターエラー"); return(BadRequest()); } SalesTrendModel addmodel = new SalesTrendModel(); addmodel.ProductModelId = model.Product_id; addmodel.TargetDate = model.Detail_date.Date; addmodel.Sales = model.Quantity; addmodel.Comments = model.Comments; addmodel.UserModelId = model.User_id; addmodel.Deleted = false; dbContext.Database.ExecuteSqlCommand(ContextResources.IncrementResetSalesTrend); dbContext.SalesTrendModels.Add(addmodel); dbContext.SaveChanges(); dbContext.Database.ExecuteSqlCommand(ContextResources.IncrementResetSalesTrend); model.Id = addmodel.Id; model.User_name = dbContext.UserModels.Where(um => um.Id == addmodel.UserModelId).Select(um => um.Name).SingleOrDefault(); if (model.Id == 0) { WriteAppLog("POST SalesViews/" + id.ToString() + "/Trends", "登録処理に失敗"); return(BadRequest()); } WriteAppLog("POST SalesViews/" + id.ToString() + "/Trends", "処理成功"); return(Ok(model)); }
/// <summary> /// 販売在庫データ情報を抽出しEXCELデータ配列を生成 /// </summary> /// <param name="year">年度</param> /// <param name="groupid">グループID</param> /// <param name="makerid">メーカーID</param> /// <returns>EXCELデータ配列</returns> public byte[] CreateXlsxOneSheetBySalesView(int year, int?groupid, int?makerid) { SalesViewService service = new SalesViewService(); try { // 事務所データ ICollection <OfficeModel> offices = dbContext.OfficeModels.Where(ofs => ofs.Deleted == false).Where(ofs => ofs.Code != ContextResources.CompanyCode).ToList(); int officeCount = offices.Count; // メーカー情報とグループ情報 GroupModel group = null; MakerModel maker = null; if (groupid.HasValue) { group = dbContext.GroupModels.Where(gm => gm.Id == groupid).SingleOrDefault(); } if (group == null && makerid.HasValue) { maker = dbContext.MakerModels.Where(mm => mm.Id == makerid).SingleOrDefault(); } else { maker = dbContext.MakerModels.Where(mm => mm.Id == group.MakerModelId).SingleOrDefault(); } if (group == null && maker == null) { return(null); } // データ抽出 List <SalesViewApiModel> outputData; SalesViewApiParameterModel param = new SalesViewApiParameterModel(); param.Year = year; param.MakerId = makerid; param.GroupId = groupid; if (service.GetSalesViews(param, out outputData, 18) != System.Net.HttpStatusCode.OK) { return(null); } // ブックとシートを用意 CreateBook(); GetSheetByName("シート"); // シートの全体定義を出力 // 作成日 WriteCell(0, ONE_ROW.ROW_HEAD, "作成日", top_title); WriteCell(1, ONE_ROW.ROW_HEAD, DateTime.Now, date_local); MergedCell(1, ONE_ROW.ROW_HEAD, 2, ONE_ROW.ROW_HEAD); // メーカーコード WriteCell(3, ONE_ROW.ROW_HEAD, "メーカーコード", top_title); WriteCell(4, ONE_ROW.ROW_HEAD, maker.Code, cenetr_string); // メーカー名 WriteCell(5, ONE_ROW.ROW_HEAD, "メーカー名", top_title); WriteCell(6, ONE_ROW.ROW_HEAD, maker.Name, box_string); MergedCell(6, ONE_ROW.ROW_HEAD, 9, ONE_ROW.ROW_HEAD); // グループ名 WriteCell(10, ONE_ROW.ROW_HEAD, "グループ名", top_title); if (group == null) { WriteCell(11, ONE_ROW.ROW_HEAD, string.Empty, box_string); } else { WriteCell(11, ONE_ROW.ROW_HEAD, group.Name, box_string); } MergedCell(11, ONE_ROW.ROW_HEAD, 15, ONE_ROW.ROW_HEAD); // サインイン int userId = GetUserId(); string userName = dbContext.UserModels.Where(um => um.Id == userId).Select(um => um.Name).SingleOrDefault(); WriteCell(16, ONE_ROW.ROW_HEAD, "ユーザー名", top_title); WriteCell(17, ONE_ROW.ROW_HEAD, userName, box_string); MergedCell(17, ONE_ROW.ROW_HEAD, 20, ONE_ROW.ROW_HEAD); if (group == null) { WriteAppLog(this.ToString(), "データ作成条件:年度[" + year.ToString() + "] メーカー[" + maker.Code + "]"); } else { WriteAppLog(this.ToString(), "データ作成条件:年度[" + year.ToString() + "] メーカー[" + maker.Code + "] グループ[" + group.Code + "]"); } DateTime startDate; DateTime check; int productIndex = 0; int baseRowIndex = 0; foreach (var productData in outputData) { // 最小年月+1が表示開始年月となる startDate = productData.SalesList.Select(sl => sl.detail_date).Min().AddMonths(1); // 商品データの基準行 baseRowIndex = ONE_ROW.ROW_START + productIndex * ONE_ROW.ROWS_ONE_PRODUCT; // 頭に空行を挟む(上線を引く必要がありそう) for (int i = 0; i <= ONE_ROW.COL_SUMMARY; i++) { WriteStyle(i, baseRowIndex, top_border); } baseRowIndex += 1; // 商品コード WriteCell(0, baseRowIndex, "商品コード", top_title); WriteCell(1, baseRowIndex, productData.Product.Code, cenetr_string); MergedCell(1, baseRowIndex, 2, baseRowIndex); // 商品名 WriteCell(3, baseRowIndex, "商品名", top_title); WriteCell(4, baseRowIndex, productData.Product.Name, box_string); MergedCell(4, baseRowIndex, 12, baseRowIndex); // 既定入数 WriteCell(13, baseRowIndex, "既定入数", top_title); WriteCell(14, baseRowIndex, productData.Product.Quantity, center_only); // カートン入数 WriteCell(15, baseRowIndex, "カートン入数", top_title); MergedCell(15, baseRowIndex, 16, baseRowIndex); if (productData.Product.CartonQuantity.HasValue) { WriteCell(17, baseRowIndex, (decimal)productData.Product.CartonQuantity, center_only); } else { WriteCell(17, baseRowIndex, "未登録", center_only); } // パレット入数 WriteCell(18, baseRowIndex, "パレット入数", top_title); MergedCell(18, baseRowIndex, 19, baseRowIndex); if (productData.Product.PaletteQuantity.HasValue) { WriteCell(20, baseRowIndex, (decimal)productData.Product.PaletteQuantity, center_only); } else { WriteCell(20, baseRowIndex, "未登録", center_only); } // 年月の行 baseRowIndex += 1; // 年度+月の表示 check = startDate; WriteCell(0, baseRowIndex, check, year_title); MergedCell(0, baseRowIndex, 1, baseRowIndex); for (int i = 0; i < ONE_ROW.COLS_REPEAT_MONTH; i++) { WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex, check, month_title); check = check.AddMonths(1); } WriteCell(ONE_ROW.COL_SUMMARY, baseRowIndex, "累計", month_title); // 在庫販売データの左側見出し // 在庫関連 WriteCell(0, baseRowIndex + ONE_ROW.ROW_STOCK_PLAN, "月初在庫", left_title); MergedCell(0, baseRowIndex + ONE_ROW.ROW_STOCK_PLAN, 0, baseRowIndex + ONE_ROW.ROW_STOCK_ACTUAL); WriteCell(1, baseRowIndex + ONE_ROW.ROW_STOCK_PLAN, "予測", left_title_top); WriteCell(1, baseRowIndex + ONE_ROW.ROW_STOCK_ACTUAL, "実績", left_title_bottom); // 発注関連 WriteCell(0, baseRowIndex + ONE_ROW.ROW_ORDER_PLAN, "発注", left_title); MergedCell(0, baseRowIndex + ONE_ROW.ROW_ORDER_PLAN, 0, baseRowIndex + ONE_ROW.ROW_ORDER_ACTUAL); WriteCell(1, baseRowIndex + ONE_ROW.ROW_ORDER_PLAN, "予定", left_title_top); WriteCell(1, baseRowIndex + ONE_ROW.ROW_ORDER_ACTUAL, "実績", left_title_bottom); // 入荷関連 WriteCell(0, baseRowIndex + ONE_ROW.ROW_INVOICE_PLAN, "入荷", left_title); MergedCell(0, baseRowIndex + ONE_ROW.ROW_INVOICE_PLAN, 0, baseRowIndex + ONE_ROW.ROW_INVOICE_ADJUSTMENT); WriteCell(1, baseRowIndex + ONE_ROW.ROW_INVOICE_PLAN, "予定", left_title_top); WriteCell(1, baseRowIndex + ONE_ROW.ROW_INVOICE_ACTUAL, "実績", left_title_mid); WriteCell(1, baseRowIndex + ONE_ROW.ROW_INVOICE_REAMING, "残数", left_title_mid); WriteCell(1, baseRowIndex + ONE_ROW.ROW_INVOICE_ADJUSTMENT, "調整", left_title_bottom); // 販売関連 WriteCell(0, baseRowIndex + ONE_ROW.ROW_SALES_PRE, "販売", left_title); MergedCell(0, baseRowIndex + ONE_ROW.ROW_SALES_PRE, 0, baseRowIndex + ONE_ROW.ROW_SALES_PLAN_PERCENT); WriteCell(1, baseRowIndex + ONE_ROW.ROW_SALES_PRE, "前年", left_title_top); WriteCell(1, baseRowIndex + ONE_ROW.ROW_SALES_PLAN, "予算", left_title_mid); WriteCell(1, baseRowIndex + ONE_ROW.ROW_SALES_TREND, "動向", left_title_mid); WriteCell(1, baseRowIndex + ONE_ROW.ROW_SALES_ACTUAL, "実績", left_title_mid); WriteCell(1, baseRowIndex + ONE_ROW.ROW_SALES_PRE_PERCENT, "前年比", left_title_mid); WriteCell(1, baseRowIndex + ONE_ROW.ROW_SALES_PLAN_PERCENT, "予実比", left_title_bottom); // データ貼り付け decimal stockPlan = 0; check = startDate; for (int i = 0; i < ONE_ROW.COLS_REPEAT_MONTH; i++) { SalesViewsTempModel work = productData.SalesList.Where(x => x.detail_date == check).SingleOrDefault(); if (work == null) { for (int j = ONE_ROW.ROW_STOCK_PLAN; j <= ONE_ROW.ROW_SALES_PLAN_PERCENT; j++) { switch (j) { case ONE_ROW.ROW_STOCK_PLAN: WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + j, stockPlan, productData.Product.IsSoldWeight ? double_top : int_top); break; case ONE_ROW.ROW_SALES_PRE_PERCENT: WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + j, 0, percent_mid); break; case ONE_ROW.ROW_SALES_PLAN_PERCENT: WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + j, 0, percent_bottom); break; case ONE_ROW.ROW_ORDER_PLAN: case ONE_ROW.ROW_INVOICE_PLAN: case ONE_ROW.ROW_SALES_PRE: WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + j, 0, productData.Product.IsSoldWeight ? double_top : int_top); break; case ONE_ROW.ROW_STOCK_ACTUAL: case ONE_ROW.ROW_ORDER_ACTUAL: case ONE_ROW.ROW_INVOICE_ADJUSTMENT: WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + j, 0, productData.Product.IsSoldWeight ? double_bottom : int_bottom); break; default: WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + j, 0, productData.Product.IsSoldWeight ? double_mid : int_mid); break; } } } else { // 在庫関連 WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_STOCK_PLAN, stockPlan, productData.Product.IsSoldWeight ? double_top : int_top); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_STOCK_ACTUAL, work.zaiko_actual, productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_ORDER_PLAN, work.order_plan, productData.Product.IsSoldWeight ? double_top : int_top); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_ORDER_ACTUAL, work.order_actual, productData.Product.IsSoldWeight ? double_bottom : int_bottom); // 入荷関連 WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_INVOICE_PLAN, work.invoice_plan, productData.Product.IsSoldWeight ? double_top : int_top); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_INVOICE_ACTUAL, work.invoice_actual, productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_INVOICE_REAMING, work.invoice_zan, productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_INVOICE_ADJUSTMENT, work.invoice_adjust, productData.Product.IsSoldWeight ? double_bottom : int_bottom); // 販売関連 WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_SALES_PRE, work.pre_sales_actual, productData.Product.IsSoldWeight ? double_top : int_top); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_SALES_PLAN, work.sales_plan, productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_SALES_TREND, work.sales_trend, productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_SALES_ACTUAL, work.sales_actual, productData.Product.IsSoldWeight ? double_mid : int_mid); // 比率計算 WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_SALES_PRE_PERCENT, work.pre_sales_actual == 0 ? 0 : work.sales_actual / work.pre_sales_actual, percent_mid); WriteCell(ONE_ROW.COL_START_MONTH + i, baseRowIndex + ONE_ROW.ROW_SALES_PLAN_PERCENT, work.sales_plan == 0 ? 0 : work.sales_actual / work.sales_plan, percent_bottom); // 次月の在庫予測の算出 if (check <= DateTime.Now.Date) { stockPlan = work.zaiko_actual; } stockPlan -= (work.sales_plan + work.sales_trend); stockPlan += (work.invoice_plan); stockPlan += productData.SalesList.Where(x => x.detail_date == check.AddMonths(-1)).Select(x => x.invoice_zan).SingleOrDefault(); stockPlan -= productData.SalesList.Where(x => x.detail_date == check.AddMonths(-1)).Select(x => x.invoice_adjust).SingleOrDefault(); } check = check.AddMonths(1); } // 在庫関連累計 WriteCell(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_STOCK_PLAN, "-", center_top); WriteFormula(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_STOCK_ACTUAL, MakeSumFormula(ONE_ROW.COL_START_MONTH, ONE_ROW.COL_SUMMARY - 1, baseRowIndex + ONE_ROW.ROW_STOCK_ACTUAL), productData.Product.IsSoldWeight ? double_bottom : int_bottom); // 発注関連 WriteFormula(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_ORDER_PLAN, MakeSumFormula(ONE_ROW.COL_START_MONTH, ONE_ROW.COL_SUMMARY - 1, baseRowIndex + ONE_ROW.ROW_ORDER_PLAN), productData.Product.IsSoldWeight ? double_top : int_top); WriteFormula(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_ORDER_ACTUAL, MakeSumFormula(ONE_ROW.COL_START_MONTH, ONE_ROW.COL_SUMMARY - 1, baseRowIndex + ONE_ROW.ROW_ORDER_ACTUAL), productData.Product.IsSoldWeight ? double_bottom : int_bottom); // 入荷関連 WriteFormula(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_INVOICE_PLAN, MakeSumFormula(ONE_ROW.COL_START_MONTH, ONE_ROW.COL_SUMMARY - 1, baseRowIndex + ONE_ROW.ROW_INVOICE_PLAN), productData.Product.IsSoldWeight ? double_top : int_top); WriteFormula(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_INVOICE_ACTUAL, MakeSumFormula(ONE_ROW.COL_START_MONTH, ONE_ROW.COL_SUMMARY - 1, baseRowIndex + ONE_ROW.ROW_INVOICE_ACTUAL), productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_INVOICE_REAMING, "-", center_mid); WriteCell(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_INVOICE_ADJUSTMENT, "-", center_bottom); // 販売関連 WriteFormula(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_SALES_PRE, MakeSumFormula(ONE_ROW.COL_START_MONTH, ONE_ROW.COL_SUMMARY - 1, baseRowIndex + ONE_ROW.ROW_SALES_PRE), productData.Product.IsSoldWeight ? double_top : int_top); WriteFormula(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_SALES_PLAN, MakeSumFormula(ONE_ROW.COL_START_MONTH, ONE_ROW.COL_SUMMARY - 1, baseRowIndex + ONE_ROW.ROW_SALES_PLAN), productData.Product.IsSoldWeight ? double_mid : int_mid); WriteFormula(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_SALES_TREND, MakeSumFormula(ONE_ROW.COL_START_MONTH, ONE_ROW.COL_SUMMARY - 1, baseRowIndex + ONE_ROW.ROW_SALES_TREND), productData.Product.IsSoldWeight ? double_mid : int_mid); WriteFormula(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_SALES_ACTUAL, MakeSumFormula(ONE_ROW.COL_START_MONTH, ONE_ROW.COL_SUMMARY - 1, baseRowIndex + ONE_ROW.ROW_SALES_ACTUAL), productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_SALES_PRE_PERCENT, "-", center_mid); WriteCell(ONE_ROW.COL_SUMMARY, baseRowIndex + ONE_ROW.ROW_SALES_PLAN_PERCENT, "-", center_bottom); // カウンタアップ productIndex++; } // 仕上げ baseRowIndex = ONE_ROW.ROW_START + productIndex * ONE_ROW.ROWS_ONE_PRODUCT; for (int i = 0; i <= ONE_ROW.COL_SUMMARY; i++) { WriteStyle(i, baseRowIndex, top_border); } // ヘッダフッタほか TargetSheet.Header.Right = HeaderFooter.Date; TargetSheet.Footer.Center = HeaderFooter.Page; TargetSheet.Autobreaks = true; TargetSheet.FitToPage = true; // IPrintSetup print = activeSheet.PrintSetup; // activeSheet.PrintSetup.Scale = 90; // activeSheet.PrintSetup.FitHeight = 1; TargetSheet.PrintSetup.FitWidth = 1; /* 8:A3 / 9:A4 / 11:A5 / 12:B4 / 13;B5 */ TargetSheet.PrintSetup.PaperSize = 9; // A4用紙 TargetSheet.PrintSetup.Landscape = false; // 横向き WriteAppLog(this.ToString(), "データ作成成功"); using (MemoryStream ms = new MemoryStream()) { WorkBook.Write(ms); CloseBook(); return(ms.ToArray()); } } catch (Exception ex) { WriteAppLog(this.ToString(), ex.Message); return(null); } }
/// <summary> /// 販売在庫データ情報を抽出しタブ型のEXCELデータ配列を生成 /// </summary> /// <param name="year">年度</param> /// <param name="groupid">グループID</param> /// <param name="makerid">メーカーID</param> /// <returns>EXCELデータ配列</returns> public byte[] CreateXlsxBySalesViewTab(int year, int?groupid, int?makerid) { SalesViewService service = new SalesViewService(); try { // 事務所データ ICollection <OfficeModel> offices = dbContext.OfficeModels.Where(ofs => ofs.Deleted == false).Where(ofs => ofs.Code != ContextResources.CompanyCode).ToList(); int officeCount = offices.Count; // データ抽出 List <SalesViewApiModel> outputData; SalesViewApiParameterModel param = new SalesViewApiParameterModel(); param.Year = year; param.MakerId = makerid; param.GroupId = groupid; if (service.GetSalesViews(param, out outputData, 18) != System.Net.HttpStatusCode.OK) { return(null); } // とりあえずブック CreateBook(); DateTime startDate; DateTime check; foreach (var productData in outputData) { // 最小年月+1が表示開始年月となる startDate = productData.SalesList.Select(sl => sl.detail_date).Min().AddMonths(1); check = startDate; // 商品コードをシート名にする GetSheetByName(productData.Product.Code); // 全体書式を設定する // 一番上 WriteCell(0, MULTI_CONFIG.ROW_HEAD, "作成日", top_title); WriteCell(1, MULTI_CONFIG.ROW_HEAD, DateTime.Now.Date, date_local); MergedCell(1, MULTI_CONFIG.ROW_HEAD, 2, MULTI_CONFIG.ROW_HEAD); WriteCell(3, MULTI_CONFIG.ROW_HEAD, "商品コード", top_title); WriteCell(4, MULTI_CONFIG.ROW_HEAD, productData.Product.Code, cenetr_string); MergedCell(4, MULTI_CONFIG.ROW_HEAD, 5, MULTI_CONFIG.ROW_HEAD); WriteCell(6, MULTI_CONFIG.ROW_HEAD, "商品名", top_title); WriteCell(7, MULTI_CONFIG.ROW_HEAD, productData.Product.Name, cenetr_string); MergedCell(7, MULTI_CONFIG.ROW_HEAD, 12, MULTI_CONFIG.ROW_HEAD); WriteCell(13, 0, "入数", top_title); WriteCell(14, 0, productData.Product.Quantity, center_only); // 上段年月表示行 WriteCell(0, MULTI_CONFIG.ROW_UP_MONTH, check, year_title); MergedCell(0, MULTI_CONFIG.ROW_UP_MONTH, 1, MULTI_CONFIG.ROW_UP_MONTH); WriteCell(0, MULTI_CONFIG.ROW_DOWN_MONTH, check, year_title); MergedCell(0, MULTI_CONFIG.ROW_DOWN_MONTH, 1, MULTI_CONFIG.ROW_DOWN_MONTH); for (int i = 0; i < MULTI_CONFIG.COLS_REPEAT_MONTH; i++) { WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_UP_MONTH, check, month_title); WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_DOWN_MONTH, check, month_title); check = check.AddMonths(1); } WriteCell(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_UP_MONTH, "累計", month_title); WriteCell(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_DOWN_MONTH, "累計", month_title); // 月初在庫タイトル WriteCell(0, MULTI_CONFIG.ROW_STOCK_PLAN, "月初在庫", left_title); MergedCell(0, MULTI_CONFIG.ROW_STOCK_PLAN, 0, MULTI_CONFIG.ROW_STOCK_ACTUAL); WriteCell(1, MULTI_CONFIG.ROW_STOCK_PLAN, "予測", left_title_top); WriteCell(1, MULTI_CONFIG.ROW_STOCK_ACTUAL, "実績", left_title_bottom); // 発注タイトル WriteCell(0, MULTI_CONFIG.ROW_ORDER_PLAN, "発注", left_title); MergedCell(0, MULTI_CONFIG.ROW_ORDER_PLAN, 0, MULTI_CONFIG.ROW_ORDER_ACTUAL); WriteCell(1, MULTI_CONFIG.ROW_ORDER_PLAN, "予定", left_title_top); WriteCell(1, MULTI_CONFIG.ROW_ORDER_ACTUAL, "実績", left_title_bottom); // 入荷タイトル WriteCell(0, MULTI_CONFIG.ROW_INVOICE_PLAN, "入荷", left_title); MergedCell(0, MULTI_CONFIG.ROW_INVOICE_PLAN, 0, MULTI_CONFIG.ROW_INVOICE_ADJUSTMENT); WriteCell(1, MULTI_CONFIG.ROW_INVOICE_PLAN, "予定", left_title_top); WriteCell(1, MULTI_CONFIG.ROW_INVOICE_ACTUAL, "実績", left_title_mid); WriteCell(1, MULTI_CONFIG.ROW_INVOICE_REAMING, "残数", left_title_mid); WriteCell(1, MULTI_CONFIG.ROW_INVOICE_ADJUSTMENT, "調整", left_title_bottom); // 販売タイトル WriteCell(0, MULTI_CONFIG.ROW_SALES_PRE, "販売", left_title); MergedCell(0, MULTI_CONFIG.ROW_SALES_PRE, 0, MULTI_CONFIG.ROW_SALES_PLAN_PERCENT); WriteCell(1, MULTI_CONFIG.ROW_SALES_PRE, "前年", left_title_top); WriteCell(1, MULTI_CONFIG.ROW_SALES_PLAN, "予定", left_title_mid); WriteCell(1, MULTI_CONFIG.ROW_SALES_TREND, "動向", left_title_mid); WriteCell(1, MULTI_CONFIG.ROW_SALES_ACTUAL, "実績", left_title_mid); WriteCell(1, MULTI_CONFIG.ROW_SALES_PRE_PERCENT, "前年比", left_title_mid); WriteCell(1, MULTI_CONFIG.ROW_SALES_PLAN_PERCENT, "予実比", left_title_bottom); // 支店が存在しない場合は出力しない if (officeCount > 0) { int i = 0; foreach (var office in offices) { int baseRow = MULTI_CONFIG.ROW_OFFICE_START + MULTI_CONFIG.ROWS_ONE_OFFICE * i; WriteCell(0, baseRow, office.Name, left_title); MergedCell(0, baseRow + MULTI_CONFIG.ROW_OFFICE_PRE, 0, baseRow + MULTI_CONFIG.ROW_OFFICE_ACTUAL); WriteCell(1, baseRow + MULTI_CONFIG.ROW_OFFICE_PRE, "前年", left_title_top); WriteCell(1, baseRow + MULTI_CONFIG.ROW_OFFICE_ACTUAL, "実績", left_title_bottom); i++; } } decimal stockPlan = 0; check = startDate; for (int i = 0; i < MULTI_CONFIG.COLS_REPEAT_MONTH; i++) { SalesViewsTempModel work = productData.SalesList.Where(x => x.detail_date == check).SingleOrDefault(); if (work == null) { for (int j = MULTI_CONFIG.ROW_STOCK_PLAN; j <= MULTI_CONFIG.ROW_SALES_PLAN_PERCENT; j++) { switch (j) { case MULTI_CONFIG.ROW_STOCK_PLAN: WriteCell(MULTI_CONFIG.COL_START_MONTH + i, j, stockPlan, productData.Product.IsSoldWeight ? double_top : int_top); break; case MULTI_CONFIG.ROW_SALES_PLAN_PERCENT: WriteCell(MULTI_CONFIG.COL_START_MONTH + i, j, 0, percent_bottom); break; case MULTI_CONFIG.ROW_SALES_PRE_PERCENT: WriteCell(MULTI_CONFIG.COL_START_MONTH + i, j, 0, percent_mid); break; case MULTI_CONFIG.ROW_ORDER_PLAN: case MULTI_CONFIG.ROW_INVOICE_PLAN: case MULTI_CONFIG.ROW_SALES_PRE: WriteCell(MULTI_CONFIG.COL_START_MONTH + i, j, 0, productData.Product.IsSoldWeight ? double_top : int_top); break; case MULTI_CONFIG.ROW_STOCK_ACTUAL: case MULTI_CONFIG.ROW_ORDER_ACTUAL: case MULTI_CONFIG.ROW_INVOICE_ADJUSTMENT: WriteCell(MULTI_CONFIG.COL_START_MONTH + i, j, 0, productData.Product.IsSoldWeight ? double_bottom : int_bottom); break; default: WriteCell(MULTI_CONFIG.COL_START_MONTH + i, j, 0, productData.Product.IsSoldWeight ? double_mid : int_mid); break; } } } else { // 在庫情報 WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_STOCK_PLAN, stockPlan, productData.Product.IsSoldWeight ? double_top : int_top); WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_STOCK_ACTUAL, work.zaiko_actual, productData.Product.IsSoldWeight ? double_bottom : int_bottom); // 発注情報 WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_ORDER_PLAN, work.order_plan, productData.Product.IsSoldWeight ? double_top : int_top); WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_ORDER_ACTUAL, work.order_actual, productData.Product.IsSoldWeight ? double_bottom : int_bottom); // 入荷情報 WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_INVOICE_PLAN, work.invoice_plan, productData.Product.IsSoldWeight ? double_top : int_top); WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_INVOICE_ACTUAL, work.invoice_actual, productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_INVOICE_REAMING, work.invoice_zan, productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_INVOICE_ADJUSTMENT, work.invoice_adjust, productData.Product.IsSoldWeight ? double_bottom : int_bottom); // 販売情報 WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_SALES_PRE, work.pre_sales_actual, productData.Product.IsSoldWeight ? double_top : int_top); WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_SALES_PLAN, work.sales_plan, productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_SALES_TREND, work.sales_trend, productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_SALES_ACTUAL, work.sales_actual, productData.Product.IsSoldWeight ? double_mid : int_mid); // 前年比 WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_SALES_PRE_PERCENT, work.pre_sales_actual == 0 ? 0 : work.sales_actual / work.pre_sales_actual, percent_mid); // 予実比 WriteCell(MULTI_CONFIG.COL_START_MONTH + i, MULTI_CONFIG.ROW_SALES_PLAN_PERCENT, work.sales_plan == 0 ? 0 : work.sales_actual / work.sales_plan, percent_bottom); // 次月の在庫予測の算出 if (check <= DateTime.Now.Date) { stockPlan = work.zaiko_actual; } stockPlan -= (work.sales_plan + work.sales_trend); stockPlan += (work.invoice_plan); stockPlan += productData.SalesList.Where(x => x.detail_date == check.AddMonths(-1)).Select(x => x.invoice_zan).SingleOrDefault(); stockPlan -= productData.SalesList.Where(x => x.detail_date == check.AddMonths(-1)).Select(x => x.invoice_adjust).SingleOrDefault(); } // 支店別は件数分 if (officeCount > 0) { int j = 0; SalesOfficeTempModel officeWork; foreach (var office in offices) { int baseRow = MULTI_CONFIG.ROW_OFFICE_START + MULTI_CONFIG.ROWS_ONE_OFFICE * j; officeWork = productData.OfficeSales.SelectMany(os => os.Where(ot => ot.office_id == office.Id)) .Where(ot => ot.detail_date == check).SingleOrDefault(); // 前年 WriteCell(MULTI_CONFIG.COL_START_MONTH + i, baseRow + MULTI_CONFIG.ROW_OFFICE_PRE, officeWork == null ? 0 : officeWork.pre_sales_actual, productData.Product.IsSoldWeight ? double_top : int_top); // 当年 WriteCell(MULTI_CONFIG.COL_START_MONTH + i, baseRow + MULTI_CONFIG.ROW_OFFICE_ACTUAL, officeWork == null ? 0 : officeWork.sales_actual, productData.Product.IsSoldWeight ? double_bottom : int_bottom); j++; } } check = check.AddMonths(1); } // 累計列 // 在庫関連 WriteCell(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_STOCK_PLAN, "-", center_top); WriteFormula(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_STOCK_ACTUAL, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, MULTI_CONFIG.ROW_STOCK_ACTUAL), productData.Product.IsSoldWeight ? double_bottom : int_bottom); // 発注関連 WriteFormula(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_ORDER_PLAN, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, MULTI_CONFIG.ROW_ORDER_PLAN), productData.Product.IsSoldWeight ? double_top : int_top); WriteFormula(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_ORDER_ACTUAL, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, MULTI_CONFIG.ROW_ORDER_ACTUAL), productData.Product.IsSoldWeight ? double_bottom : int_bottom); // 入荷関連 WriteCell(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_INVOICE_PLAN, "-", center_top); WriteFormula(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_INVOICE_PLAN, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, MULTI_CONFIG.ROW_INVOICE_PLAN), productData.Product.IsSoldWeight ? double_mid : int_mid); WriteFormula(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_INVOICE_ACTUAL, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, MULTI_CONFIG.ROW_INVOICE_ACTUAL), productData.Product.IsSoldWeight ? double_mid : int_mid); WriteCell(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_INVOICE_REAMING, "-", center_mid); WriteCell(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_INVOICE_ADJUSTMENT, "-", center_bottom); // 販売関連 WriteFormula(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_SALES_PRE, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, MULTI_CONFIG.ROW_SALES_PRE), productData.Product.IsSoldWeight ? double_top : int_top); WriteFormula(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_SALES_PLAN, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, MULTI_CONFIG.ROW_SALES_PLAN), productData.Product.IsSoldWeight ? double_mid : int_mid); WriteFormula(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_SALES_TREND, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, MULTI_CONFIG.ROW_SALES_TREND), productData.Product.IsSoldWeight ? double_mid : int_mid); WriteFormula(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_SALES_ACTUAL, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, MULTI_CONFIG.ROW_SALES_ACTUAL), productData.Product.IsSoldWeight ? double_mid : int_mid); // 比率関連 WriteCell(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_SALES_PRE_PERCENT, "-", center_mid); WriteCell(MULTI_CONFIG.COL_SUMMARY, MULTI_CONFIG.ROW_SALES_PLAN_PERCENT, "-", center_bottom); // 支店別は件数分 if (officeCount > 0) { for (int j = 0; j < officeCount; j++) { int baseRow = MULTI_CONFIG.ROW_OFFICE_START + MULTI_CONFIG.ROWS_ONE_OFFICE * j; WriteFormula(MULTI_CONFIG.COL_SUMMARY, baseRow + MULTI_CONFIG.ROW_OFFICE_PRE, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, baseRow + MULTI_CONFIG.ROW_OFFICE_PRE), productData.Product.IsSoldWeight ? double_top : int_top); WriteFormula(MULTI_CONFIG.COL_SUMMARY, baseRow + MULTI_CONFIG.ROW_OFFICE_ACTUAL, MakeSumFormula(MULTI_CONFIG.COL_START_MONTH, MULTI_CONFIG.COL_SUMMARY - 1, baseRow + MULTI_CONFIG.ROW_OFFICE_ACTUAL), productData.Product.IsSoldWeight ? double_bottom : int_bottom); } } // 仕上げ for (int i = 0; i <= MULTI_CONFIG.COL_SUMMARY; i++) { WriteStyle(i, MULTI_CONFIG.ROW_OFFICE_START + MULTI_CONFIG.ROWS_ONE_OFFICE * officeCount, top_border); } // ヘッダフッタほか TargetSheet.Header.Right = HeaderFooter.Date; TargetSheet.Footer.Center = HeaderFooter.Page; TargetSheet.Autobreaks = true; TargetSheet.FitToPage = true; // IPrintSetup print = activeSheet.PrintSetup; // activeSheet.PrintSetup.Scale = 90; TargetSheet.PrintSetup.FitHeight = 1; TargetSheet.PrintSetup.FitWidth = 1; /* 8:A3 / 9:A4 / 11:A5 / 12:B4 / 13;B5 */ TargetSheet.PrintSetup.PaperSize = 9; // A4用紙 TargetSheet.PrintSetup.Landscape = true; // 横向き } using (MemoryStream ms = new MemoryStream()) { WorkBook.Write(ms); CloseBook(); return(ms.ToArray()); } } catch (Exception ex) { WriteAppLog(this.ToString(), ex.Message); return(null); } }