/*************************************************************
* 商品の原価計算データを設定
*************************************************************/
private void setProductData(ExcelWorksheet ws, string supplierCode, string supplierName, string productCode, string productName)
{
Const.CATEGORY_TYPE category = Program.judgeCategory(radioBudget, radioActual);
ws.Cells["AH2"].Value = productName;
ws.Cells["S2"].Value = supplierName;
using (var context = new CostAccountingEntities())
{
var product = from t_product in context.Product
join t_supplier in context.ProductSupplier on
new { t_product.year, t_product.code, t_product.category, t_product.type }
equals
new { t_supplier.year, code = t_supplier.product_code, t_supplier.category, t_supplier.type }
where t_product.year.Equals(Const.TARGET_YEAR)
&& t_product.code.Equals(productCode)
&& t_supplier.supplier_code.Equals(supplierCode)
&& t_product.category.Equals((int)category)
&& t_product.type.Equals((int)Const.PRODUCT_TYPE.Normal)
select new { t_product, t_supplier };
ws.Cells["S3"].Value = product.First().t_product.packing;
ws.Cells["AR2"].Value = product.First().t_product.volume;
ws.Cells["AZ2"].Value = product.First().t_product.tray_num;
ws.Cells["L3"].Value = product.First().t_supplier.unit_price;
ws.Cells["P31"].Value = product.First().t_product.note;
ws.Cells["G35"].Value = product.First().t_supplier.update_user;
ws.Cells["G36"].Value = product.First().t_supplier.update_date;
ws.Cells["R7"].Value = Parameters.getInstance(category).rateLoss;
ws.Cells["T20"].Value = Parameters.getInstance(category).allocationSale;
ws.Cells["W21"].Value = Parameters.getInstance(category).allocationMng;
ws.Cells["U22"].Value = Parameters.getInstance(category).allocationExt;
// ①原料費の設定------------------------------------------------------------
var material = from t in context.ProductMaterial
join m in context.RowMaterial on
new { t.year, t.code } equals new { m.year, m.code }
where t.year.Equals(Const.TARGET_YEAR)
&& t.product_code.Equals(productCode)
&& t.category.Equals((int)category)
orderby t.no
select new { t.code, t.quantity, m.name };
int startRow = 4;
foreach (var data in material.ToList())
{
ws.Cells["AB" + startRow].Value = data.name;
ws.Cells["AI" + startRow].Value = data.quantity;
ws.Cells["AM" + startRow].Value = DataTableSupport.getPrice(DataTableSupport.getInstance(category).rowMaterial, data.code);
++startRow;
}
// ②外注費の設定------------------------------------------------------------
var contractor = from t in context.ProductContractor
where t.year.Equals(Const.TARGET_YEAR)
&& t.product_code.Equals(productCode)
&& t.category.Equals((int)category)
orderby t.no
select t;
startRow = 20;
foreach (var data in contractor.ToList())
{
ws.Cells["AB" + startRow].Value = data.name;
ws.Cells["AI" + startRow].Value = data.cost;
ws.Cells["AL" + startRow].Value = data.quantity;
++startRow;
}
// ③労務費の設定------------------------------------------------------------
ws.Cells["AG28"].Value = product.First().t_product.preprocess_time_m;
ws.Cells["AI29"].Value = product.First().t_product.night_time_m;
ws.Cells["AK28"].Value = product.First().t_product.dry_time_m;
ws.Cells["AM28"].Value = product.First().t_product.selection_time_m;
ws.Cells["AG30"].Value = product.First().t_product.preprocess_time_f;
ws.Cells["AI30"].Value = product.First().t_product.night_time_f;
ws.Cells["AK30"].Value = product.First().t_product.dry_time_f;
ws.Cells["AM30"].Value = product.First().t_product.selection_time_f;
ws.Cells["AR28"].Value = Parameters.getInstance(category).wageM;
ws.Cells["AR29"].Value = Parameters.getInstance(category).wageIndirect;
ws.Cells["AR30"].Value = Parameters.getInstance(category).wageF;
ws.Cells["AO32"].Value = Parameters.getInstance(category).trayNum;
// ④製造経費の設定------------------------------------------------------------
// 《原料運賃》
var materialFare = from t in context.ProductMaterialsFare
where t.year.Equals(Const.TARGET_YEAR)
&& t.product_code.Equals(productCode)
&& t.category.Equals((int)category)
orderby t.no
select t;
startRow = 36;
foreach (var data in materialFare.ToList())
{
ws.Cells["AB" + startRow].Value = data.name;
ws.Cells["AK" + startRow].Value = data.quantity;
ws.Cells["AO" + startRow].Value = data.cost;
++startRow;
}
// 《包装資材費》
var packing = from t in context.ProductPacking
join m in context.Material on
new { t.year, t.code } equals new { m.year, m.code }
where t.year.Equals(Const.TARGET_YEAR)
&& t.product_code.Equals(productCode)
&& t.category.Equals((int)category)
orderby t.no
select new { t.code, t.quantity, m.name };
startRow = 44;
foreach (var data in packing.ToList())
{
ws.Cells["AB" + startRow].Value = data.name;
ws.Cells["AK" + startRow].Value = data.quantity;
ws.Cells["AO" + startRow].Value = DataTableSupport.getPrice(DataTableSupport.getInstance(category).material, data.code);
++startRow;
}
// 《設備費》
var machine = from t in context.ProductMachine
join m in context.Machine on
new { t.year, t.code } equals new { m.year, m.code }
where t.year.Equals(Const.TARGET_YEAR)
&& t.product_code.Equals(productCode)
&& t.category.Equals((int)category)
orderby t.no
select new { t.code, t.time, m.name };
startRow = 56;
foreach (var data in machine.ToList())
{
ws.Cells["AB" + startRow].Value = string.Concat(data.code, " ", data.name);
ws.Cells["AK" + startRow].Value = data.time;
ws.Cells["AO" + startRow].Value = DataTableSupport.getPrice(DataTableSupport.getInstance(category).machine, data.code);
++startRow;
}
ws.Cells["AI56"].Value = product.First().t_product.tray_num;
// 《水道光熱費》
ws.Cells["AL70"].Value = Parameters.getInstance(category).utilitiesAD;
ws.Cells["AL71"].Value = Parameters.getInstance(category).utilitiesFD;
// 《その他経費》
ws.Cells["AL76"].Value = Parameters.getInstance(category).allocationFD;
ws.Cells["AL77"].Value = Parameters.getInstance(category).allocationAD;
ws.Cells["AL78"].Value = Parameters.getInstance(category).allocationLabor;
// 《荷造運賃》
var packingFare = from t in context.ProductPackingFare
join m in context.Fare on
new { t.year, t.code } equals new { m.year, m.code }
where t.year.Equals(Const.TARGET_YEAR)
&& t.product_code.Equals(productCode)
&& t.supplier_code.Equals(supplierCode)
&& t.category.Equals((int)category)
orderby t.no
select new { t.code, t.quantity, m.name };
startRow = 84;
foreach (var data in packingFare.ToList())
{
ws.Cells["AB" + startRow].Value = data.name;
ws.Cells["AK" + startRow].Value = data.quantity;
ws.Cells["AO" + startRow].Value = DataTableSupport.getPrice(DataTableSupport.getInstance(category).fare, data.code);
++startRow;
}
}
ws.Calculate();
}
/*************************************************************
* ブレンド品の原価計算データを設定
*************************************************************/
private void setBlendData(ExcelWorksheet ws, string supplierCode, string supplierName, string productCode, string productName)
{
Const.CATEGORY_TYPE category = Program.judgeCategory(radioBudget, radioActual);
ws.Cells["E2"].Value = productName;
ws.Cells["S2"].Value = supplierName;
using (var context = new CostAccountingEntities())
{
var product = from t_product in context.Product
join t_supplier in context.ProductSupplier on
new { t_product.year, t_product.code, t_product.category, t_product.type }
equals
new { t_supplier.year, code = t_supplier.product_code, t_supplier.category, t_supplier.type }
where t_product.year.Equals(Const.TARGET_YEAR)
&& t_product.code.Equals(productCode)
&& t_supplier.supplier_code.Equals(supplierCode)
&& t_product.category.Equals((int)category)
&& t_product.type.Equals((int)Const.PRODUCT_TYPE.Blend)
select new { t_product, t_supplier };
ws.Cells["S3"].Value = product.First().t_product.packing;
ws.Cells["L3"].Value = product.First().t_supplier.unit_price;
ws.Cells["P31"].Value = product.First().t_product.note;
ws.Cells["G35"].Value = product.First().t_supplier.update_user;
ws.Cells["G36"].Value = product.First().t_supplier.update_date;
// ブレンド品の設定------------------------------------------------------------
var blend = from t in
(from t_product in context.Product
join t_blend in context.ProductBlend
on new { t_product.year, t_product.code, t_product.category }
equals
new { t_blend.year, code = t_blend.product_code, t_blend.category }
where t_product.year.Equals(Const.TARGET_YEAR)
&& t_product.code.Equals(productCode)
&& t_product.category.Equals((int)category)
&& t_product.type.Equals((int)Const.PRODUCT_TYPE.Blend)
select new { product = t_product, blend = t_blend }
)
join m in context.ProductCode on
new { t.blend.year, t.blend.code } equals new { m.year, m.code }
orderby t.blend.no
select new { t.product, t.blend, m.name };
var blendList = blend.ToList();
int startColumm = 14;
int add = 5;
for (int i = 0; i < blendList.Count; i++)
{
var data = blendList[i];
ws.Cells[5, startColumm + (add * i)].Value = data.blend.blend_rate;
ws.Cells[6, startColumm + (add * i)].Value = data.name;
ws.Cells[7, startColumm + (add * i)].Value = data.product.material_cost;
ws.Cells[8, startColumm + (add * i)].Value = data.product.contractors_cost;
ws.Cells[9, startColumm + (add * i)].Value = data.product.labor_cost;
ws.Cells[10, startColumm + (add * i)].Value = data.product.labor_cost_direct;
ws.Cells[11, startColumm + (add * i)].Value = data.product.labor_cost_indirect;
ws.Cells[12, startColumm + (add * i)].Value = data.product.manufacturing_cost;
ws.Cells[13, startColumm + (add * i)].Value = data.product.materials_fare;
ws.Cells[14, startColumm + (add * i)].Value = data.product.packing_cost;
ws.Cells[15, startColumm + (add * i)].Value = data.product.machine_cost;
ws.Cells[16, startColumm + (add * i)].Value = data.product.utilities_cost;
ws.Cells[17, startColumm + (add * i)].Value = data.product.other_cost;
ws.Cells[18, startColumm + (add * i)].Value = data.product.product_cost;
ws.Cells[19, startColumm + (add * i)].Value = data.product.packing_fare;
ws.Cells[20, startColumm + (add * i)].Value = data.product.selling_cost;
ws.Cells[21, startColumm + (add * i)].Value = data.product.management_cost;
ws.Cells[23, startColumm + (add * i)].Value = data.product.overall_cost;
}
}
ws.Calculate();
}