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)
{
// 在庫予測計算用に１ヶ月多めに取得（貿易のみ）
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">対象商品ＩＤ</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>();
// 在庫予測計算用に１ヶ月多めに取得（貿易のみ）
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">グループＩＤ</param>
/// <param name="makerid">メーカーＩＤ</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)
{
// 最小年月＋１が表示開始年月となる
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">グループＩＤ</param>
/// <param name="makerid">メーカーＩＤ</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)
{
// 最小年月＋１が表示開始年月となる
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);
}
}
