/// <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); }