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