/// <summary>
/// Download Xlsx file
/// </summary>
/// <param name="timeStart"></param>
/// <param name="timeEnd"></param>
/// <param name="GroupId"></param>
/// <param name="ContractTypeId"></param>
/// <param name="checkSalesType"></param>
/// <param name="tabId"></param>
/// <returns></returns>
public ActionResult DownloadXlsxFileSales(string timeStart, string timeEnd, string GroupId, string ContractTypeId, string checkSalesType, string tabId)
{
PMS11003ListViewModel dataSales = new PMS11003ListViewModel();
PMS11003ListViewModelPlus dataProfit = new PMS11003ListViewModelPlus();
if (Session["SalesBudgetData" + tabId] != null)
{
dataSales = Session["SalesBudgetData" + tabId] as PMS11003ListViewModel;
}
else
{
dataSales = CreateSalesBudgetModel(timeStart, timeEnd, GroupId, ContractTypeId, checkSalesType);
}
if (Session["SalesBudgetData" + tabId] != null)
{
dataProfit = Session["ProfitBudgetData" + tabId] as PMS11003ListViewModelPlus;
}
else
{
dataProfit = CreateProfitBudgetModel(timeStart, timeEnd, GroupId, ContractTypeId, checkSalesType);
}
DataSalesProfitExport data = new DataSalesProfitExport();
data.data_sale = dataSales;
data.data_profit = dataProfit;
Utility.DownloadXlsxFile(this, data, "所属別予実一覧.xlsx", "所属別予実一覧.xlsx");
return(new EmptyResult());
}
/// <summary>
/// Create Profit Budget Model
/// </summary>
/// <param name="timeStart"></param>
/// <param name="timeEnd"></param>
/// <param name="GroupId"></param>
/// <param name="ContractTypeId"></param>
/// <param name="checkSalesType"></param>
/// <returns></returns>
public PMS11003ListViewModelPlus CreateProfitBudgetModel(string timeStart, string timeEnd, string GroupId, string ContractTypeId, string checkSalesType)
{
var model = new PMS11003ListViewModelPlus();
var currentUser = GetLoginUser();
model.Condition.Year = DateTime.Now.Year.ToString();
model.TimeStart = timeStart;
model.TimeEnd = timeEnd;
if (!string.IsNullOrEmpty(GroupId))
{
model.Condition.GroupId = GroupId;
}
if (!string.IsNullOrEmpty(ContractTypeId))
{
model.Condition.ContractTypeId = ContractTypeId;
}
model.Condition.List_Contract = mainService.GetListContractTypeBySearch(ContractTypeId, GetLoginUser().CompanyCode).OrderBy(m => m.display_order).ThenBy(m => m.contract_type_id).ToList();
model.Condition.List_Group = mainService.GetListGroupBySearch(GroupId, GetLoginUser().CompanyCode).OrderBy(m => m.display_order).ThenBy(m => m.group_id).ToList();
#region Get profit data
var listProfitBudget = mainService.GetListProfitBudget(timeStart, timeEnd, currentUser.CompanyCode, ContractTypeId, GroupId);
var listCost = mainService.GetListCost(timeStart, timeEnd, currentUser.CompanyCode, ContractTypeId, GroupId, checkSalesType);
var listSalesActual = mainService.GetListSaleActual(timeStart, timeEnd, currentUser.CompanyCode, ContractTypeId, GroupId, checkSalesType);
var profitDataList = new List <ProfitData>();
CountChargePersonNullProfit(timeStart, timeEnd, currentUser.CompanyCode, ContractTypeId, GroupId, ref listSalesActual, checkSalesType);
var profitData = (from pb in listProfitBudget
join c in listCost
on new { target_year = pb.target_year, target_month = pb.target_month, group_id = pb.group_id, contract_type_id = pb.contract_type_id } equals
new { target_year = c.target_year, target_month = c.target_month, group_id = c.group_id, contract_type_id = c.contract_type_id }
into joinC
from c in joinC.DefaultIfEmpty()
join sa in listSalesActual
on new { target_year = pb.target_year, target_month = pb.target_month, group_id = pb.group_id, contract_type_id = pb.contract_type_id } equals
new { target_year = sa.target_year, target_month = sa.target_month, group_id = sa.group_id, contract_type_id = sa.contract_type_id }
into joinSa
from sa in joinSa.DefaultIfEmpty()
select new ProfitData()
{
target_year = pb.target_year,
target_month = pb.target_month,
group_id = pb.group_id,
contract_type_id = pb.contract_type_id,
group_name = pb.group_name,
contract_type = pb.contract_type,
profit_budget = (pb == null) ? 0 : pb.profit_budget,
cost_price = (c == null) ? 0 : c.cost_price,
sales_price = (sa == null) ? 0 : sa.total_sales,
contract_type_display_order = (sa == null) ? 0: sa.contract_type_display_order,
group_display_order = (sa == null) ? 0 : sa.group_display_order
}
).ToList();
foreach (var profit in profitData)
{
profit.profit_actual = profit.sales_price - profit.cost_price;
profit.tgrProfitRate = (profit.sales_price != 0) ? ((profit.profit_actual / profit.sales_price) * 100).ToString() : "-";
profit.tgrSuccessRate = (profit.profit_budget != 0) ? ((profit.profit_actual / profit.profit_budget) * 100).ToString() : "-";
}
var profitTotalGroup = profitData.GroupBy(l => new { l.target_year, l.target_month, l.group_id })
.Select(cl => new TotalGroupProfit
{
target_year = cl.First().target_year,
target_month = cl.First().target_month,
group_id = cl.First().group_id.ToString(),
tgrProfitBudget = cl.Sum(c => c.profit_budget),
tgrCost = cl.Sum(c => c.cost_price),
tgrSaleActual = cl.Sum(c => c.sales_price),
tgrProfitActual = cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price),
tgrProfitRate = (cl.Sum(c => c.sales_price) != 0) ? ((cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price)) / cl.Sum(c => c.sales_price) * 100).ToString() : "-",
tgrSuccessRate = (cl.Sum(c => c.profit_budget) != 0) ? ((cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price)) / cl.Sum(c => c.profit_budget) * 100).ToString() : "-",
}).ToList();
var profitCT = profitData.GroupBy(l => new { l.target_year, l.target_month, l.contract_type_id })
.Select(cl => new TotalContractTypeProfit
{
target_year = cl.First().target_year,
target_month = cl.First().target_month,
contract_type_id = cl.First().contract_type_id.ToString(),
tgrProfitBudget = cl.Sum(c => c.profit_budget),
tgrCost = cl.Sum(c => c.cost_price),
tgrSaleActual = cl.Sum(c => c.sales_price),
tgrProfitActual = cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price),
tgrProfitRate = (cl.Sum(c => c.sales_price) != 0) ? ((cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price)) / cl.Sum(c => c.sales_price) * 100).ToString() : "-",
tgrSuccessRate = (cl.Sum(c => c.profit_budget) != 0) ? ((cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price)) / cl.Sum(c => c.profit_budget) * 100).ToString() : "-",
}).ToList();
var profitTotalMonth = profitData.GroupBy(l => new { l.target_year, l.target_month })
.Select(cl => new TotalGrAllProfit
{
target_year = cl.First().target_year,
target_month = cl.First().target_month,
tgrProfitBudget = cl.Sum(c => c.profit_budget),
tgrCost = cl.Sum(c => c.cost_price),
tgrSaleActual = cl.Sum(c => c.sales_price),
tgrProfitActual = cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price),
tgrProfitRate = (cl.Sum(c => c.sales_price) != 0) ? ((cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price)) / cl.Sum(c => c.sales_price) * 100).ToString() : "-",
tgrSuccessRate = (cl.Sum(c => c.profit_budget) != 0) ? ((cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price)) / cl.Sum(c => c.profit_budget) * 100).ToString() : "-",
}).ToList();
var profitTotalGroupAll = profitData.GroupBy(l => new { l.group_id })
.Select(cl => new TotalGrAllProfit
{
group_id = cl.First().group_id.ToString(),
tgrProfitBudget = cl.Sum(c => c.profit_budget),
tgrCost = cl.Sum(c => c.cost_price),
tgrSaleActual = cl.Sum(c => c.sales_price),
tgrProfitActual = cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price),
tgrProfitRate = (cl.Sum(c => c.sales_price) != 0) ? ((cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price)) / cl.Sum(c => c.sales_price) * 100).ToString() : "-",
tgrSuccessRate = (cl.Sum(c => c.profit_budget) != 0) ? ((cl.Sum(c => c.sales_price) - cl.Sum(c => c.cost_price)) / cl.Sum(c => c.profit_budget) * 100).ToString() : "-",
}).ToList();
var profitDataTemp = new List <ProfitData>();
if (profitData != null && profitData.Count > 0)
{
foreach (var item in profitData)
{
profitDataTemp.Add(item);
}
}
var groupListProfit = new List <Group>();
var contractTypeListProfit = new List <ContractType>();
var timeListProfit = new List <TimeList>();
for (int i = 0; i < profitDataTemp.Count; i++)
{
var grItem = new Group();
grItem.display_name = profitDataTemp[i].group_name ?? mainService.GetGroupName(currentUser.CompanyCode, profitDataTemp[i].group_id);
grItem.group_id = Convert.ToInt32(profitDataTemp[i].group_id);
grItem.display_order = Convert.ToInt32(profitDataTemp[i].group_display_order);
var chk = true;
for (int j = 0; j < groupListProfit.Count; j++)
{
if (groupListProfit[j].display_name == grItem.display_name && groupListProfit[j].group_id == grItem.group_id)
{
chk = false;
break;
}
}
if (chk)
{
groupListProfit.Add(grItem);
}
var ctItem = new ContractType();
ctItem.contract_type = profitDataTemp[i].contract_type ?? mainService.GetContractTypeName(currentUser.CompanyCode, profitDataTemp[i].contract_type_id);
ctItem.contract_type_id = profitDataTemp[i].contract_type_id;
ctItem.display_order = Convert.ToInt32(profitDataTemp[i].contract_type_display_order);
chk = true;
for (int j = 0; j < contractTypeListProfit.Count; j++)
{
if (contractTypeListProfit[j].contract_type == ctItem.contract_type && contractTypeListProfit[j].contract_type_id == ctItem.contract_type_id)
{
chk = false;
break;
}
}
if (chk)
{
contractTypeListProfit.Add(ctItem);
}
var tlItem = new TimeList();
tlItem.year = profitDataTemp[i].target_year.ToString();
tlItem.month = profitDataTemp[i].target_month.ToString();
chk = true;
for (int j = 0; j < timeListProfit.Count; j++)
{
if (timeListProfit[j].month == tlItem.month && timeListProfit[j].year == tlItem.year)
{
chk = false;
break;
}
}
if (chk)
{
timeListProfit.Add(tlItem);
}
}
model.GroupListProfit = groupListProfit.OrderBy(m => m.display_order).ThenBy(m => m.group_id).ToList();
model.ContractTypeListProfit = contractTypeListProfit.OrderBy(m => m.display_order).ThenBy(m => m.contract_type_id).ToList();
model.DataProfitBudget = profitData;
model.TimeListProfit = timeListProfit;
model.TotalGroupProfit = profitTotalGroup;
model.TotalCTProfit = profitCT;
model.TotalMonthProfit = profitTotalMonth;
model.TotalGrYearListProfit = profitTotalGroupAll;
model.CheckSalesType = checkSalesType;
return(model);
}
