/************************************************************* * 商品の原価計算データを設定 *************************************************************/ 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(); }