/// <summary>
/// 根据model信息生成 [condition1]=@condition,[condition2]=@condition2.....
/// </summary>
/// <param name="model">载有委托处理的Model</param>
/// <param name="conditions">需要匹配的成员集合</param>
/// <returns>条件字符串结果</returns>
public string Condition(MakerModel model, params MemberInfo[] conditions)
{
StringBuilder sql = new StringBuilder();
for (int i = 0; i < conditions.Length; i += 1)
{
sql.Append(model.Left);
if (model.ColFunction != null)
{
sql.Append(model.ColFunction(conditions[i]));
}
else
{
sql.Append(conditions[i].Name);
}
sql.Append(model.Right);
sql.Append("=@");
if (model.FilterFunction != null)
{
sql.Append(model.FilterFunction(conditions[i]));
}
else
{
sql.Append(conditions[i].Name);
}
sql.Append(" AND ");
}
sql.Length -= 5;
return(sql.ToString());
}
public string Condition(MakerModel model, IEnumerable <MemberInfo> conditions)
{
StringBuilder sql = new StringBuilder();
foreach (var item in conditions)
{
sql.Append(model.Left);
if (model.ColFunction != null)
{
sql.Append(model.ColFunction(item));
}
else
{
sql.Append(item.Name);
}
sql.Append(model.Right);
sql.Append("=@");
if (model.FilterFunction != null)
{
sql.Append(model.FilterFunction(item));
}
else
{
sql.Append(item.Name);
}
sql.Append(" AND ");
}
if (sql.Length > 0)
{
sql.Length -= 5;
}
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 SELECT [member1],[member2]... FROM [TableName]
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string Select(MakerModel model)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
foreach (var item in model.Members)
{
sql.Append(model.Left);
if (model.ColFunction != null)
{
sql.Append(model.ColFunction(item));
}
else
{
sql.Append(item.Name);
}
sql.Append(model.Right);
sql.Append(",");
}
sql.Length -= 1;
sql.Append(" FROM ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 SELECT Count(*) FROM [TableName] WHERE
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string SelectCountByCondition(MakerModel model)
{
StringBuilder sql = new StringBuilder();
sql.Append(SelectCount(model));
sql.Append(" WHERE ");
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 UPDATE [TableName] SET([member1]=@member1,[member2]...=@member2...) WHERE [condition1]=@condition,[condition2]=@condition2.....
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <param name="condition_models">需要匹配的成员集合</param>
/// <returns>更新字符串结果</returns>
public string UpdateWithCondition(MakerModel model, params MemberInfo[] conditions)
{
var select = UpdateByCondition(model);
StringBuilder sql = new StringBuilder(select);
ConditionTemplate template = new ConditionTemplate();
sql.Append(template.Condition(model, conditions));
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 SELECT Count(*) FROM [TableName]
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string SelectCount(MakerModel model)
{
StringBuilder sql = new StringBuilder(16 + model.TableName.Length);
sql.Append("SELECT Count(*) FROM ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 DELETE FROM [TableName] WHERE
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>删除字符串结果</returns>
public string DeleteByCondition(MakerModel model)
{
StringBuilder sql = new StringBuilder(21 + model.TableName.Length);
sql.Append("DELETE FROM ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
sql.Append(" WHERE ");
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 SELECT [member1],[member2]... FROM [TableName] WHERE [PrimaryKey] IN @keys
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string SelectIn(MakerModel model)
{
StringBuilder sql = new StringBuilder();
sql.Append(SelectByCondition(model));
sql.Append(model.Left);
sql.Append(model.PrimaryKey);
sql.Append(model.Right);
sql.Append(" IN @keys");
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 INSERT INTO [TableName] ([member1],[member2]...) VALUES (@member1,@member2...)
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>插入字符串的结果</returns>
public string Insert(MakerModel model)
{
StringBuilder pre_str = new StringBuilder(20);
StringBuilder aft_str = new StringBuilder(20);
pre_str.Append(" (");
aft_str.Append('(');
foreach (var item in model.Members)
{
pre_str.Append(model.Left);
if (model.ColFunction != null)
{
pre_str.Append(model.ColFunction(item));
}
else
{
pre_str.Append(item.Name);
}
pre_str.Append(model.Right);
pre_str.Append(',');
aft_str.Append('@');
if (model.FilterFunction != null)
{
aft_str.Append(model.FilterFunction(item));
}
else
{
aft_str.Append(item.Name);
}
aft_str.Append(',');
}
pre_str.Length -= 1;
aft_str.Length -= 1;
pre_str.Append(')');
aft_str.Append(')');
StringBuilder sql = new StringBuilder(40);
sql.Append("INSERT INTO ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
sql.Append(pre_str);
sql.Append("VALUES");
sql.Append(aft_str);
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 UPDATE [TableName] SET([member1]=@member1,[member2]...=@member2...) WHERE PrimaryKey=@PrimaryKe
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>更新字符串结果</returns>
public string UpdateByPrimary(MakerModel model)
{
if (model.PrimaryKey != null)
{
StringBuilder sql = new StringBuilder();
sql.Append(UpdateByCondition(model));
sql.Append(model.Left);
sql.Append(model.PrimaryKey);
sql.Append(model.Right);
sql.Append("=@");
sql.Append(model.PrimaryKey);
return(sql.ToString());
}
return(null);
}
/// <summary>
/// 根据model信息生成 DELETE FROM [TableName] WHERE [PrimaryKey] =@PrimaryKey
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>删除字符串结果</returns>
public string DeleteByPrimary(MakerModel model)
{
if (model.PrimaryKey != null)
{
StringBuilder sql = new StringBuilder(23 + model.TableName.Length + model.PrimaryKey.Length * 2);
sql.Append(DeleteByCondition(model));
sql.Append(model.Left);
sql.Append(model.PrimaryKey);
sql.Append(model.Right);
sql.Append("=@");
sql.Append(model.PrimaryKey);
return(sql.ToString());
}
return(null);
}
// <summary>
/// 根据model信息生成 SELECT * FROM [TableName] WHERE [Member1]=@Member1 AND [Member2]=@Member2 ....
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查重字符串结果</returns>
public string RepeateEntities(MakerModel model)
{
StringBuilder sql = new StringBuilder(40);
sql.Append("SELECT * FROM ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
sql.Append(" WHERE ");
ConditionTemplate template = new ConditionTemplate();
sql.Append(template.Condition(model, model.Members));
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 [condition1]=@condition,[condition2]=@condition2.....
/// </summary>
/// <param name="model">载有委托处理的Model</param>
/// <param name="conditions">需要匹配的字符串集合</param>
/// <returns>条件字符串结果</returns>
public string Condition(MakerModel model, params string[] conditions)
{
StringBuilder sql = new StringBuilder();
for (int i = 0; i < conditions.Length; i += 1)
{
sql.Append(model.Left);
sql.Append(conditions[i]);
sql.Append(model.Right);
sql.Append("=@");
sql.Append(conditions[i]);
sql.Append(" AND ");
}
sql.Length -= 5;
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 UPDATE [TableName] SET([member1]=@member1,[member2]...=@member2...) WHERE
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>更新字符串结果</returns>
public string UpdateByCondition(MakerModel model)
{
StringBuilder update = new StringBuilder(40);
foreach (var item in model.Members)
{
update.Append(model.Left);
if (model.ColFunction != null)
{
update.Append(model.ColFunction(item));
}
else
{
update.Append(item.Name);
}
update.Append(model.Right);
update.Append("=@");
if (model.FilterFunction != null)
{
update.Append(model.FilterFunction(item));
}
else
{
update.Append(item.Name);
}
update.Append(',');
}
StringBuilder sql = new StringBuilder(60);
if (update.Length > 0)
{
update.Length -= 1;
sql.Append("UPDATE ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
sql.Append(" SET ");
sql.Append(update);
sql.Append(" WHERE ");
}
return(sql.ToString());
}
public IHttpActionResult Put(int id, [FromBody] MakerApiModel value)
{
try
{
if (id <= 0)
{
return(BadRequest(Messages.ApiIllegalParameter));
}
using (DbContextTransaction tx =
dbContext.Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
value.Id = id;
MakerModel maker = dbContext.MakerModels.Where(m => m.Id == value.Id).SingleOrDefault();
if (maker == null)
{
tx.Rollback();
return(NotFound());
}
maker.Enabled = value.Enabled;
dbContext.Entry(maker).State = EntityState.Modified;
if (dbContext.SaveChanges() == 0)
{
tx.Rollback();
return(Conflict());
}
tx.Commit();
return(Ok(value));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
/// <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);
}
}
public BaseHandler(string spiltes, Type entity_type)
{
_model = new MakerModel();
if (spiltes == null)
{
_model.Left = default(char);
_model.Right = default(char);
}
else
{
_model.Left = spiltes[0];
_model.Right = spiltes[1];
}
if (entity_type == null)
{
return;
}
_model.PrimaryKey = null;
_entity_type = entity_type;
//创建标签Helper
_handler = new AttrOperator(_entity_type);
//表名解析
var table = _handler.ClassInstance <TableAttribute>();
_model.TableName = table.Name;
//主键解析
var primary = _handler.Mapping <PrimaryKeyAttribute>();
if (primary.Instance != null)
{
_model.PrimaryKey = primary.Member.Name;
_primary_manually = primary.Instance.IsManually;
_primary_member = primary.Member;
}
// 忽略掉Ignore标签的成员
var ignores = _handler.Members <IgnoreAttribute>();
_model.LoadMembers(_handler._members);
_model.AddIgnores(ignores);
//列名映射解析
ConcurrentDictionary <MemberInfo, string> _column_mapping = new ConcurrentDictionary <MemberInfo, string>();
ConcurrentDictionary <string, string> _string_mapping = new ConcurrentDictionary <string, string>();
var mappings = _handler.Mappings <ColumnAttribute>();
foreach (var item in _handler._members)
{
_column_mapping[item] = item.Name;
_string_mapping[item.Name] = item.Name;
}
foreach (var item in mappings)
{
_column_mapping[item.Member] = item.Instance.Name;
_string_mapping[item.Member.Name] = item.Instance.Name;
}
_model.ColumnMapping = _column_mapping;
_model.StringMapping = _string_mapping;
//填充属性
//静态sql生成器。例如 MakerModel<Student>
GsOperator gs = new GsOperator(typeof(MakerModel <>), _entity_type);
gs.Set("PrimaryKey", _model.PrimaryKey);
gs.Set("TableName", _model.TableName);
gs.Set("Left", _model.Left);
gs.Set("Right", _model.Right);
gs.Set("Members", _model.Members);
gs.Set("ColumnMapping", _column_mapping);
gs.Set("StringMapping", _string_mapping);
AttrOperator attr = new AttrOperator(_entity_type);
var _sql_type = attr.ClassInstance <TableAttribute>().Type;
gs.Set("OperatorType", _sql_type);
}
