/// <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>
/// Index
/// </summary>
/// <returns>List View</returns>
public ActionResult Index()
{
if ((!IsInFunctionList(Constant.FunctionID.BudgetActual_List)))
{
return(this.RedirectToAction("Index", "ErrorAuthent"));
}
var currentUser = GetLoginUser();
int closingMonth = mainService.GetAccountClosingMonth(currentUser.CompanyCode);
int currentYear = DateTime.Now.Year;
int currentMonth = DateTime.Now.Month;
var timeStart = "";
var timeEnd = "";
if (closingMonth == 12)
{
timeStart = currentYear + "/01";
timeEnd = currentYear + "/12";
}
else
{
if (currentMonth <= closingMonth)
{
timeStart = (currentYear - 1) + "/" + ((closingMonth + 1) >= 10 ? (closingMonth + 1).ToString() : "0" + (closingMonth + 1).ToString());
timeEnd = currentYear + "/" + (closingMonth >= 10 ? closingMonth.ToString() : "0" + closingMonth.ToString());
}
else
{
timeStart = currentYear + "/" + ((closingMonth + 1) >= 10 ? (closingMonth + 1).ToString() : "0" + (closingMonth + 1).ToString());
timeEnd = (currentYear + 1) + "/" + (closingMonth >= 10 ? closingMonth.ToString() : "0" + closingMonth.ToString());
}
}
var model = new PMS11003ListViewModel();
model.Condition.Year = DateTime.Now.Year.ToString();
model.TimeStart = timeStart;
model.TimeEnd = timeEnd;
var salesData = mainService.GetListSaleData(timeStart, timeEnd, currentUser.CompanyCode, "", "");
List <IDictionary <string, object> > salesDataTemp = new List <IDictionary <string, object> >();
if (salesData != null)
{
foreach (var item in salesData)
{
salesDataTemp.Add(item);
}
}
model.GroupListToSearch = this.commonService.GetUserGroupSelectList(currentUser.CompanyCode, true);
model.ContractTypeListToSearch = this.commonService.GetContractTypeSelectList(currentUser.CompanyCode, true);
if (Session[Constant.SESSION_IS_BACK] != null)
{
Session[Constant.SESSION_IS_BACK] = null;
}
return(this.View("SalesBudget", model));
}
/// <summary>
/// Create SalesBudget 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 PMS11003ListViewModel CreateSalesBudgetModel(string timeStart, string timeEnd, string GroupId, string ContractTypeId, string checkSalesType)
{
var model = new PMS11003ListViewModel();
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, currentUser.CompanyCode).ToList();
model.Condition.List_Group = mainService.GetListGroupBySearch(GroupId, currentUser.CompanyCode).ToList();
//total group counting
var totalGroup = mainService.GetListTotalGroup(timeStart, timeEnd, currentUser.CompanyCode, ContractTypeId, GroupId, checkSalesType);
List <IDictionary <string, object> > totalGrTemp = new List <IDictionary <string, object> >();
if (totalGroup != null)
{
foreach (var item in totalGroup)
{
totalGrTemp.Add(item);
}
}
var tGrList = new List <TotalGroup>();
for (int i = 0; i < totalGrTemp.Count; i++)
{
var tGr = new TotalGroup();
tGr.target_year = Convert.ToInt32(totalGrTemp[i].Values.ElementAt(0).ToString());
tGr.target_month = Convert.ToInt32(totalGrTemp[i].Values.ElementAt(1).ToString());
tGr.group_id = totalGrTemp[i].Values.ElementAt(2).ToString();
tGr.tgrBudget = totalGrTemp[i].Values.ElementAt(3).ToString();
tGr.tgrSales = totalGrTemp[i].Values.ElementAt(4).ToString();
tGr.tgrProfit = totalGrTemp[i].Values.ElementAt(5).ToString();
tGrList.Add(tGr);
}
//total contract type counting
var totalCt = mainService.GetListTotalCT(timeStart, timeEnd, currentUser.CompanyCode, ContractTypeId, GroupId, checkSalesType);
List <IDictionary <string, object> > totalCTTemp = new List <IDictionary <string, object> >();
if (totalCt != null)
{
foreach (var item in totalCt)
{
totalCTTemp.Add(item);
}
}
var tCTList = new List <TotalContractType>();
for (int i = 0; i < totalCTTemp.Count; i++)
{
var tCT = new TotalContractType();
tCT.target_year = Convert.ToInt32(totalCTTemp[i].Values.ElementAt(0).ToString());
tCT.target_month = Convert.ToInt32(totalCTTemp[i].Values.ElementAt(1).ToString());
tCT.contract_type_id = totalCTTemp[i].Values.ElementAt(2).ToString();
tCT.tgrBudget = totalCTTemp[i].Values.ElementAt(3).ToString();
tCT.tgrSales = totalCTTemp[i].Values.ElementAt(4).ToString();
tCT.tgrProfit = totalCTTemp[i].Values.ElementAt(5).ToString();
tCTList.Add(tCT);
}
//total all year of group counting
var totalGrAll = mainService.GetListTotalAllYearGroup(timeStart, timeEnd, currentUser.CompanyCode, ContractTypeId, GroupId, checkSalesType);
List <IDictionary <string, object> > totalGrAllTemp = new List <IDictionary <string, object> >();
if (totalGrAll != null)
{
foreach (var item in totalGrAll)
{
totalGrAllTemp.Add(item);
}
}
var totalGrAllList = new List <TotalGrAll>();
for (int i = 0; i < totalGrAllTemp.Count; i++)
{
var tGrAll = new TotalGrAll();
tGrAll.group_id = totalGrAllTemp[i].Values.ElementAt(0).ToString();
tGrAll.tgrBudget = totalGrAllTemp[i].Values.ElementAt(1).ToString();
tGrAll.tgrSales = totalGrAllTemp[i].Values.ElementAt(2).ToString();
tGrAll.tgrProfit = totalGrAllTemp[i].Values.ElementAt(3).ToString();
totalGrAllList.Add(tGrAll);
}
//sales data counting
var salesData = mainService.GetListSaleData(timeStart, timeEnd, currentUser.CompanyCode, ContractTypeId, GroupId, checkSalesType);
List <IDictionary <string, object> > salesDataTemp = new List <IDictionary <string, object> >();
if (salesData != null)
{
foreach (var item in salesData)
{
salesDataTemp.Add(item);
}
}
string grIDExCondition = ""; // save all grId after filter second time
var GroupList = new List <Group>();
string ctIDExCondition = ""; // save all ctId after filter second time
var ContractTypeList = new List <ContractType>();
var SaleDataList = new List <SalesData>();
var timeList = new List <TimeList>();
for (int i = 0; i < salesDataTemp.Count; i++)
{
var targetYear = salesDataTemp[i].Values.ElementAt(0).ToString();
var targetMonth = salesDataTemp[i].Values.ElementAt(1).ToString();
var grName = salesDataTemp[i].Values.ElementAt(2).ToString();
var ctName = salesDataTemp[i].Values.ElementAt(3).ToString();
var budgetAmount = salesDataTemp[i].Values.ElementAt(4).ToString();
var ctId = salesDataTemp[i].Values.ElementAt(5).ToString();
var grId = salesDataTemp[i].Values.ElementAt(6).ToString();
var salesActual = salesDataTemp[i].Values.ElementAt(7).ToString();
var profit = salesDataTemp[i].Values.ElementAt(8).ToString();
var gr_order = salesDataTemp[i].Values.ElementAt(9).ToString();
var ct_order = salesDataTemp[i].Values.ElementAt(10).ToString();
var grItem = new Group();
grItem.display_name = grName;
grItem.group_id = Convert.ToInt32(grId);
grItem.display_order = Convert.ToInt32(gr_order);
var chk = true;
for (int j = 0; j < GroupList.Count; j++)
{
if (GroupList[j].display_name == grItem.display_name && GroupList[j].group_id == grItem.group_id)
{
chk = false;
break;
}
}
if (chk)
{
grIDExCondition += grItem.group_id + ",";
GroupList.Add(grItem);
}
var ctItem = new ContractType();
ctItem.contract_type = ctName;
ctItem.contract_type_id = Convert.ToInt32(ctId);
ctItem.display_order = Convert.ToInt32(ct_order);
chk = true;
for (int j = 0; j < ContractTypeList.Count; j++)
{
if (ContractTypeList[j].contract_type == ctItem.contract_type && ContractTypeList[j].contract_type_id == ctItem.contract_type_id)
{
chk = false;
break;
}
}
if (chk)
{
ctIDExCondition += ctItem.contract_type_id + ",";
ContractTypeList.Add(ctItem);
}
var tlItem = new TimeList();
tlItem.year = targetYear;
tlItem.month = targetMonth;
chk = true;
for (int j = 0; j < timeList.Count; j++)
{
if (timeList[j].month == tlItem.month && timeList[j].year == tlItem.year)
{
chk = false;
break;
}
}
if (chk)
{
timeList.Add(tlItem);
}
var saleItem = new SalesData();
saleItem.target_year = Convert.ToInt32(targetYear);
saleItem.target_month = Convert.ToInt32(targetMonth);
saleItem.group_name = grName;
saleItem.contract_type_name = ctName;
saleItem.sales_budget = Convert.ToDecimal(budgetAmount);
saleItem.contract_type_id = ctId;
saleItem.group_id = grId;
saleItem.sales_actuals = salesActual;
saleItem.profit = profit;
SaleDataList.Add(saleItem);
}
if (!string.IsNullOrEmpty(ctIDExCondition) && !string.IsNullOrEmpty(ctIDExCondition))
{
ctIDExCondition = ctIDExCondition.Substring(0, ctIDExCondition.Length - 1);
grIDExCondition = grIDExCondition.Substring(0, grIDExCondition.Length - 1);
//totalGrAllList; tCTList; tGrList
CountChargePersonNull(ref tGrList, ref tCTList, ref totalGrAllList, timeStart, timeEnd, currentUser.CompanyCode, ctIDExCondition, grIDExCondition, ref SaleDataList, checkSalesType);
}
model.TotalGroup = tGrList;
model.TotalCT = tCTList;
model.TotalGrYearList = totalGrAllList;
model.GroupList = GroupList.OrderBy(m => m.display_order).ThenBy(m => m.group_id).ToList();
model.ContractTypeList = ContractTypeList.OrderBy(m => m.display_order).ThenBy(m => m.contract_type_id).ToList();
model.DataSalesBudget = SaleDataList;
model.TimeList = timeList;
model.CheckSalesType = checkSalesType;
return(model);
}
