public IHttpActionResult GetFinancialPreAnalysis(string projectId, string usCode, string pageType = "") { var financial = new FinancialPreanalysis(); var reimageSum = ReimageSummary.FirstOrDefault(e => e.ProjectId == projectId && e.IsHistory == false); if (reimageSum != null) { financial = FinancialPreanalysis.FirstOrDefault(e => e.RefId == reimageSum.Id); } if (financial != null) { if (financial.Id != 0) { pageType = "save"; } } if (pageType == "") { //FinancialPreanalysis financial = reimageSummary.LoadFinancialPreanalysisInfo(); var enti = ReimageConsInfo.FirstOrDefault(e => e.ProjectId.Equals(projectId) && e.IsHistory == true); if (enti == null) { enti = ReimageConsInfo.FirstOrDefault(e => e.ProjectId.Equals(projectId) && e.IsHistory == false); } WriteOffAmount writeOff = WriteOffAmount.FirstOrDefault(e => e.ConsInfoID == enti.Id); if (financial == null) { financial = new FinancialPreanalysis(); } if (writeOff != null) { financial.EstimatedWriteOffCost = writeOff.TotalII; } var ContributionMargin = StoreCM.Get(usCode).ContributionMargin.ToString(); var reimageConsInfo = ReimageConsInfo.GetConsInfo(projectId, ""); var reinvestmentBasicInfo = ReinvestmentBasicInfo.FirstOrDefault(e => e.ConsInfoID == reimageConsInfo.Id); //ReinvestmentCost reinCost = ReinvestmentCost.FirstOrDefault(e => e.ConsInfoID == enti.Id); financial.IsMcCafe = reinvestmentBasicInfo.NewMcCafe; financial.IsKiosk = reinvestmentBasicInfo.NewKiosk; financial.IsMDS = reinvestmentBasicInfo.NewMDS; financial.IsTwientyFourHour = reinvestmentBasicInfo.NewTwientyFourHour; financial.StoreCM = ContributionMargin; } return(Ok(financial)); }
public IHttpActionResult DownLoadTemplate(string templateType, string projectId, string consInfoID = "", string NormType = "", string flowCode = "") { var current = HttpContext.Current; ExcelDataInputType outputType; if (!Enum.TryParse(templateType, out outputType)) { throw new Exception("Cannot find the specific template type!"); } if (string.IsNullOrEmpty(projectId)) { throw new Exception("Porject Id is Empty !"); } var siteFilePath = default(string); switch (outputType) { case ExcelDataInputType.WriteOffAmount: siteFilePath = SiteFilePath.FAWrite_offTool_Template_X; break; case ExcelDataInputType.ReinvestmentCost: siteFilePath = SiteFilePath.FATool_Update_Template; break; case ExcelDataInputType.ClosureWOCheckList: siteFilePath = SiteFilePath.Closure_FAWrite_offTool_Template; break; } var path = string.Format(@"{0}\{1}", SiteFilePath.Template_DIRECTORY, siteFilePath); var tempFilePath = current.Server.MapPath("~/") + "Temp\\" + Guid.NewGuid() + ".xlsx"; bool isNotUploadExcel = false; if (!string.IsNullOrEmpty(consInfoID)) { string refTableName = "MajorLeaseConsInfo"; if (projectId.ToLower().IndexOf("rebuild") != -1) { refTableName = "RebuildConsInfo"; } if (projectId.ToLower().IndexOf("reimage") != -1) { refTableName = "ReimageConsInfo"; } var strFileName = GetAttatchFile(refTableName, templateType, consInfoID);//download consifno upload file if (string.IsNullOrEmpty(strFileName)) { isNotUploadExcel = true; tempFilePath = current.Server.MapPath("~/") + "UploadFiles\\" + Guid.NewGuid() + ".xlsx"; } else { tempFilePath = current.Server.MapPath("~/") + "UploadFiles\\" + strFileName; } } if (isNotUploadExcel) { consInfoID = null;// if not upload file in consinfo flow,then we get empty template } var projectInfo = ProjectInfo.FirstOrDefault(e => e.ProjectId == projectId); if (projectInfo == null) { throw new Exception("Cannot find the project info!"); } if (string.IsNullOrEmpty(consInfoID)) { File.Copy(path, tempFilePath); var excelOutputDirector = new ExcelDataInputDirector(new FileInfo(tempFilePath), outputType); var store = StoreBasicInfo.Search(e => e.StoreCode == projectInfo.USCode).FirstOrDefault(); if (store == null) { throw new Exception("Cannot find Store info!"); } var inputInfo = new ExcelInputDTO { Region = store.RegionENUS, Market = store.MarketENUS, City = store.CityENUS, StoreNameCN = store.NameZHCN, StoreNameEN = store.NameENUS, StoreName = store.NameZHCN, USCode = store.StoreCode, StoreType = store.StoreType, StoreTypeName = store.StoreTypeName, OpenDate = store.OpenDate, ClosureDate = store.CloseDate.HasValue ? store.CloseDate.Value : default(DateTime) }; switch (outputType) { case ExcelDataInputType.ReinvestmentCost: Guid consInfoId = Guid.Empty; if (projectId.ToLower().IndexOf("rebuild") != -1) { var rbdInfo = new RebuildConsInfo(); consInfoId = rbdInfo.GetConsInfo(projectId).Id; } else if (projectId.ToLower().IndexOf("majorlease") != -1) { var mjInfo = new MajorLeaseConsInfo(); consInfoId = mjInfo.GetConsInfo(projectId).Id; } else if (projectId.ToLower().IndexOf("reimage") != -1) { consInfoId = ReimageConsInfo.GetConsInfo(projectId).Id; } var reinvestmentBasicInfo = ReinvestmentBasicInfo.FirstOrDefault(e => e.ConsInfoID == consInfoId); if (reinvestmentBasicInfo != null) { inputInfo.NewDesignType = reinvestmentBasicInfo.NewDesignType; inputInfo.NormType = MappingNormType(NormType); inputInfo.GBDate = reinvestmentBasicInfo.GBDate; inputInfo.ConsCompletionDate = reinvestmentBasicInfo.ConsCompletionDate; inputInfo.EstimatedSeatNO = reinvestmentBasicInfo.EstimatedSeatNo; inputInfo.NewDTSiteArea = reinvestmentBasicInfo.NewDTSiteArea; inputInfo.NewOperationArea = reinvestmentBasicInfo.NewOperationSize; inputInfo.NewDiningArea = reinvestmentBasicInfo.NewDiningArea; inputInfo.WallPanelArea = reinvestmentBasicInfo.WallPanelArea; inputInfo.WallGraphicArea = reinvestmentBasicInfo.WallGraphicArea; inputInfo.FacadeACMArea = reinvestmentBasicInfo.FacadeACMArea; inputInfo.NewRemoteKiosk = reinvestmentBasicInfo.NewRemoteKiosk; inputInfo.NewAttachedKiosk = reinvestmentBasicInfo.NewAttachedKiosk; inputInfo.NewMcCafe = reinvestmentBasicInfo.NewMcCafe; inputInfo.NewMDS = reinvestmentBasicInfo.NewMDS; } break; case ExcelDataInputType.WriteOffAmount: string pmName = string.Empty; if (projectId.ToLower().IndexOf("rebuild") != -1) { var rbdInfo = new RebuildInfo(); pmName = rbdInfo.GetRebuildInfo(projectId).PMNameENUS; } else if (projectId.ToLower().IndexOf("majorlease") != -1) { var mjInfo = new MajorLeaseInfo(); pmName = mjInfo.GetMajorLeaseInfo(projectId).PMNameENUS; } else if (projectId.ToLower().IndexOf("reimage") != -1) { pmName = ReimageInfo.GetReimageInfo(projectId).PMNameENUS; } inputInfo.PMNameENUS = pmName; break; case ExcelDataInputType.ClosureWOCheckList: var closure = ClosureInfo.GetByProjectId(projectId); if (closure != null) { inputInfo.ActualCloseDate = closure.ActualCloseDate.HasValue ? closure.ActualCloseDate.Value.ToString("yyyy-MM-dd") : ""; inputInfo.PMNameENUS = closure.PMNameENUS; } break; } excelOutputDirector.Input(inputInfo); } current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + SiteFilePath.GetTemplateFileName(projectInfo.USCode, flowCode, siteFilePath)); current.Response.ContentType = "application/octet-stream"; current.Response.WriteFile("" + tempFilePath + ""); current.Response.End(); return(Ok()); }
public override void Input(ExcelWorksheet worksheet, ExcelInputDTO inputInfo) { var projectInfo = ProjectInfo.FirstOrDefault(e => e.ProjectId == inputInfo.ProjectId); if (projectInfo == null) { throw new Exception("Cannot find the project info!"); } var store = StoreBasicInfo.Search(e => e.StoreCode == projectInfo.USCode).FirstOrDefault(); if (store == null) { throw new Exception("Cannot find Store info!"); } var reimageSummary = BaseWFEntity.GetWorkflowEntity(inputInfo.ProjectId, FlowCode.Reimage_Summary) as ReimageSummary; if (reimageSummary == null) { throw new Exception("Cannot find Reimage Summary Info!"); } var storeSTLocation = StoreSTLocation.GetStoreSTLocation(projectInfo.USCode); var storeLeaseInfo = new StoreProfitabilityAndLeaseInfo(); var reimageConsInfo = ReimageConsInfo.GetConsInfo(inputInfo.ProjectId); var reinvestment = ReinvestmentBasicInfo.FirstOrDefault(e => e.ConsInfoID == reimageConsInfo.Id); if (reimageSummary != null) { storeLeaseInfo = StoreProfitabilityAndLeaseInfo.FirstOrDefault(e => e.RefId == reimageSummary.Id); if (storeLeaseInfo != null) { if (storeLeaseInfo.LastRemodelDate.HasValue && storeLeaseInfo.LastRemodelDate.Value.ToString("yyyy-MM-dd") != "1900-01-01") { worksheet.Cells["B43"].Value = storeLeaseInfo.LastRemodelDate; } worksheet.Cells["B44"].Value = storeLeaseInfo.RemainingLeaseYears; worksheet.Cells["B46"].Value = storeLeaseInfo.TTMSOIPercent / 100; worksheet.Cells["B47"].Value = storeLeaseInfo.AsOf; worksheet.Cells["B48"].Value = reimageSummary.OperationRequirements; var financial = FinancialPreanalysis.FirstOrDefault(e => e.RefId == reimageSummary.Id); worksheet.Cells["B49"].Value = TryParseDecimal(financial.TotalSalesInc); worksheet.Cells["B45"].Value = financial.TTMSales; worksheet.Cells["B50"].Value = TryParseDecimal(financial.StoreCM) * 100 + "%"; worksheet.Cells["B51"].Value = financial.CurrentPriceTier; worksheet.Cells["B52"].Value = string.IsNullOrEmpty(financial.SPTAR) ? 0 : financial.SPTAR.As <decimal>(); worksheet.Cells["B53"].Value = financial.PriceTierafterReimage; } //worksheet.Cells["E4"].Value = financial.MarginInc; } else { var reimageInfo = ReimageInfo.FirstOrDefault(e => e.ProjectId == inputInfo.ProjectId); var storeBasicInfo = StoreBasicInfo.FirstOrDefault(e => e.StoreCode == reimageInfo.USCode); var storeContractInfo = StoreContractInfo.FirstOrDefault(e => e.StoreCode == reimageInfo.USCode); int Year = 0; if (storeContractInfo != null) { if (storeContractInfo.EndDate != null) { DateTime dtNow = DateTime.Now; Year = int.Parse(storeContractInfo.EndDate.ToString().Split('/')[2].Substring(0, 4)) - dtNow.Year; } } DateTime?dt = storeBasicInfo.ReImageDate; if (dt != null) { if (dt.ToString().Substring(0, 8) == "1/1/1900") { dt = null; } } worksheet.Cells["B43"].Value = dt; worksheet.Cells["B44"].Value = Year; //storeLeaseInfo.LastRemodelDate = dt; //storeLeaseInfo.RemainingLeaseYears = Year; } ReinvestmentCost cost = ReinvestmentCost.FirstOrDefault(e => e.ConsInfoID == reimageConsInfo.Id); worksheet.Cells["B2"].Value = store.Region; worksheet.Cells["B3"].Value = store.StoreCode; if (reinvestment != null) { worksheet.Cells["B8"].Value = TryParseDecimal(reinvestment.RightSizingSeatNo); worksheet.Cells["B11"].Value = TryParseDecimal(reinvestment.EstimatedSeatNo); worksheet.Cells["B41"].Value = reinvestment.GBDate; worksheet.Cells["B42"].Value = reinvestment.ConsCompletionDate; } if (cost != null) { worksheet.Cells["B13"].Value = TryParseDecimal(cost.DesignFee); worksheet.Cells["B14"].Value = TryParseDecimal(cost.PublicBudget); worksheet.Cells["B15"].Value = TryParseDecimal(cost.BuildingFacade); worksheet.Cells["B16"].Value = TryParseDecimal(cost.SiteBudget); worksheet.Cells["B17"].Value = TryParseDecimal(cost.BuildingWork); worksheet.Cells["B18"].Value = TryParseDecimal(cost.PlumbingSystem); worksheet.Cells["B19"].Value = TryParseDecimal(cost.ElectricalSystem); worksheet.Cells["B20"].Value = TryParseDecimal(cost.HVACDuctSystem); worksheet.Cells["B21"].Value = TryParseDecimal(cost.Signage); worksheet.Cells["B22"].Value = TryParseDecimal(cost.Seating); worksheet.Cells["B23"].Value = TryParseDecimal(cost.Decor); worksheet.Cells["B24"].Value = TryParseDecimal(cost.Kiosk); worksheet.Cells["B25"].Value = TryParseDecimal(cost.McCafe); worksheet.Cells["B26"].Value = TryParseDecimal(cost.MDS); worksheet.Cells["B27"].Value = TryParseDecimal(cost.Playland); worksheet.Cells["B28"].Value = TryParseDecimal(cost.KitchenCapacityUpgrade); worksheet.Cells["B29"].Value = TryParseDecimal(cost.BuildingWorks); worksheet.Cells["B30"].Value = TryParseDecimal(cost.KitchenEquipment); worksheet.Cells["B31"].Value = TryParseDecimal(cost.HVAC); worksheet.Cells["B32"].Value = TryParseDecimal(cost.Plumbing); worksheet.Cells["B33"].Value = TryParseDecimal(cost.ElectricDistribution); worksheet.Cells["B34"].Value = TryParseDecimal(cost.Structure); worksheet.Cells["B35"].Value = TryParseDecimal(cost.Others); worksheet.Cells["B36"].Value = TryParseDecimal(cost.LHIPMAct); worksheet.Cells["B37"].Value = TryParseDecimal(cost.SignagePMAct); worksheet.Cells["B38"].Value = TryParseDecimal(cost.EquipmentPMAct); worksheet.Cells["B39"].Value = TryParseDecimal(cost.SeatingPackagePMAct); worksheet.Cells["B40"].Value = TryParseDecimal(cost.DecorPMAct); } worksheet.Cells["B5"].Value = storeSTLocation.TotalArea.As <decimal>(); worksheet.Cells["B6"].Value = store.ProvinceZHCN; worksheet.Cells["B7"].Value = store.NameZHCN; worksheet.Cells["B4"].Value = TryParseDecimal(storeSTLocation.TotalSeatsNo); worksheet.Cells["B9"].Value = TryParseDecimal(storeSTLocation.KitchenArea); worksheet.Cells["B10"].Value = store.CityZHCN; if (!string.IsNullOrEmpty(storeSTLocation.DesignStyle)) { var dict = Dictionary.FirstOrDefault(i => i.Code == storeSTLocation.DesignStyle); if (dict != null) { worksheet.Cells["B12"].Value = dict.NameZHCN; } else { worksheet.Cells["B12"].Value = storeSTLocation.DesignStyle; } } //worksheet.Cells["B24"].Value = reimageSummary.ReinvestmentBasicInfo.NewKiosk; //worksheet.Cells["B25"].Value = reimageSummary.ReinvestmentBasicInfo.NewMcCafe; //worksheet.Cells["B26"].Value = reimageSummary.ReinvestmentBasicInfo.NewMDS; //worksheet.Cells["B41"].Value = reimageSummary.ReinvestmentBasicInfo.GBDate; //worksheet.Cells["B42"].Value = reimageSummary.ReinvestmentBasicInfo.ConsCompletionDate; worksheet.Cells["B10"].Value = store.CityZHCN; //worksheet.Cells["B11"].Value = inputInfo.StoreType; //worksheet.Cells["B12"].Value = inputInfo.StoreType; //worksheet.Cells["B13"].Value = inputInfo.StoreType; //worksheet.Cells["B14"].Value = inputInfo.StoreType; //worksheet.Cells["B15"].Value = inputInfo.StoreType; //worksheet.Cells["B16"].Value = inputInfo.StoreType; //worksheet.Cells["B17"].Value = inputInfo.StoreType; //worksheet.Cells["B18"].Value = inputInfo.StoreType; //worksheet.Cells["B19"].Value = inputInfo.StoreType; //worksheet.Cells["B20"].Value = inputInfo.StoreType; //worksheet.Cells["B21"].Value = inputInfo.StoreType; //worksheet.Cells["B22"].Value = inputInfo.StoreType; //worksheet.Cells["B23"].Value = inputInfo.StoreType; //worksheet.Cells["B27"].Value = inputInfo.StoreType; //worksheet.Cells["B28"].Value = inputInfo.StoreType; //worksheet.Cells["B29"].Value = inputInfo.StoreType; //worksheet.Cells["B30"].Value = inputInfo.StoreType; //worksheet.Cells["B31"].Value = inputInfo.StoreType; //worksheet.Cells["B32"].Value = inputInfo.StoreType; //worksheet.Cells["B33"].Value = inputInfo.StoreType; //worksheet.Cells["B34"].Value = inputInfo.StoreType; //worksheet.Cells["B35"].Value = inputInfo.StoreType; //worksheet.Cells["B36"].Value = inputInfo.StoreType; //worksheet.Cells["B37"].Value = inputInfo.StoreType; //worksheet.Cells["B38"].Value = inputInfo.StoreType; //worksheet.Cells["B39"].Value = inputInfo.StoreType; //worksheet.Cells["B40"].Value = inputInfo.StoreType; //if (reimageSummary.StoreProfitabilityAndLeaseInfo != null) //{ // worksheet.Cells["B43"].Value = reimageSummary.StoreProfitabilityAndLeaseInfo.LastRemodelDate; // worksheet.Cells["B44"].Value = reimageSummary.StoreProfitabilityAndLeaseInfo.RemainingLeaseYears; // worksheet.Cells["B45"].Value = reimageSummary.StoreProfitabilityAndLeaseInfo.TTMSales; // worksheet.Cells["B46"].Value = reimageSummary.StoreProfitabilityAndLeaseInfo.TTMSOIPercent; // worksheet.Cells["B47"].Value = reimageSummary.StoreProfitabilityAndLeaseInfo.AsOf; //} ////worksheet.Cells["B48"].Value = reimageSummary.OperationRequirements; //if (reimageSummary.FinancialPreanalysis != null) //{ // worksheet.Cells["B49"].Value = reimageSummary.FinancialPreanalysis.TotalSalesInc; // worksheet.Cells["B50"].Value = reimageSummary.FinancialPreanalysis.StoreCM; // worksheet.Cells["B51"].Value = reimageSummary.FinancialPreanalysis.CurrentPriceTier; // worksheet.Cells["B52"].Value = reimageSummary.FinancialPreanalysis.ISDWIP; // worksheet.Cells["B53"].Value = reimageSummary.FinancialPreanalysis.SPTAR; //} }