public void GenerateCover()
{
if (!Attachment.Any(att => att.RefTableID == this.Id.ToString() && att.RequirementId == new Guid("9D1E247C-AAE0-44F6-869E-F50B3E633C1C")))
{
var info = RenewalInfo.Get(this.ProjectId);
var store = StoreBasicInfo.GetStorInfo(info.USCode);
var contract = StoreContractInfo.Get(info.USCode);
var context = HttpContext.Current;
var templateFileName = context.Server.MapPath("~/Template/RenewalCover_Template_v20130922.xlsx");
var fileName = context.Server.MapPath(string.Format("~/UploadFiles/{0}.xlsx", Guid.NewGuid()));
File.Copy(templateFileName, fileName);
var fileInfo = new FileInfo(fileName);
ExcelDataInputDirector excelDirector = new ExcelDataInputDirector(fileInfo, ExcelDataInputType.RenewalCover);
ExcelInputDTO excelInput = new ExcelInputDTO();
excelInput.Region = store.RegionENUS;
excelInput.Province = store.ProvinceZHCN;
excelInput.City = store.CityENUS;
excelInput.Market = store.MarketENUS;
excelInput.StoreName = store.NameZHCN;
excelInput.USCode = store.StoreCode;
excelInput.OpenDate = store.OpenDate;
excelInput.LeaseExpirationDate = contract.EndDate.Value;
excelInput.Priority = info.Priority;
excelDirector.Input(excelInput);
var att = new Attachment();
att.ID = Guid.NewGuid();
att.RefTableID = this.Id.ToString();
att.RefTableName = this.TableName;
att.RelativePath = "/";
att.TypeCode = "Cover";
att.RequirementId = Guid.Parse("9D1E247C-AAE0-44F6-869E-F50B3E633C1C");
att.Name = "Cover";
att.Extension = fileInfo.Extension;
att.InternalName = fileInfo.Name;
att.ContentType = "application/vnd.ms-excel";
att.Length = (int)fileInfo.Length;
att.CreatorID = ClientCookie.UserCode;
att.CreatorNameENUS = ClientCookie.UserNameENUS;
att.CreatorNameZHCN = ClientCookie.UserNameZHCN;
att.CreateTime = DateTime.Now;
att.Add();
}
}
public string DownloadToolTemplate()
{
var templateFileName = string.Concat(SiteFilePath.Template_DIRECTORY, "/", SiteFilePath.RenewalTool_Template);
string fileName = string.Format("{0}/{1}.xlsx", SiteFilePath.TEMP_DIRECTORY, Guid.NewGuid());
File.Copy(templateFileName, fileName);
FileInfo fileInfo = new FileInfo(fileName);
ExcelDataInputDirector excelDirector = new ExcelDataInputDirector(fileInfo, ExcelDataInputType.RenewalTool);
ExcelInputDTO excelDto = new ExcelInputDTO();
var info = RenewalInfo.Get(this.ProjectId);
var storeBasic = StoreBasicInfo.GetStorInfo(info.USCode);
var storeContract = StoreContractInfo.Get(info.USCode);
var finInput = RenewalToolFinMeasureInput.Get(this.ProjectId, this.Id);
var wfAndReinCost = RenewalToolWriteOffAndReinCost.Get(this.ProjectId, this.Id);
excelDto.USCode = info.USCode;
excelDto.Market = storeBasic.Market;
excelDto.StoreName = storeBasic.NameZHCN + "/" + storeBasic.NameENUS;
excelDto.OpenDate = storeBasic.OpenDate;
if (storeContract.EndDate.HasValue)
{
excelDto.LeaseExpirationDate = storeContract.EndDate.Value;
}
excelDto.RenewalYears = info.RenewalYears;
excelDto.ProductSales = finInput.ProductSalesAdjustment;
excelDto.Pac = finInput.PacAdjustment;
excelDto.Rent = finInput.RentAdjustment;
excelDto.DepreciationLhi = finInput.DepreciationLhiAdjustment;
excelDto.InterestLhi = finInput.InterestLhiAdjustment;
excelDto.ServiceFee = finInput.ServiceFeeAdjustment;
excelDto.Accounting = finInput.AccountingAdjustment;
excelDto.Insurance = finInput.InsuranceAdjustment;
excelDto.TaxesAndLicenses = finInput.TaxesAndLicensesAdjustment;
excelDto.DepreciationEssd = finInput.DepreciationEssdAdjustment;
excelDto.InterestEssd = finInput.InterestEssdAdjustment;
excelDto.OtherIncExp = finInput.OtherIncExpAdjustment;
excelDto.NonProductSales = finInput.NonProductSalesAdjustment;
excelDto.NonProductCosts = finInput.NonProductCostsAdjustment;
excelDto.REII = wfAndReinCost.REII;
excelDto.LHIII = wfAndReinCost.LHIII;
excelDto.ESSDII = wfAndReinCost.ESSDII;
excelDto.RENBV = wfAndReinCost.RENBV;
if (wfAndReinCost.LHINBV.HasValue)
{
excelDto.LHINBV = wfAndReinCost.LHINBV.Value;
}
if (wfAndReinCost.ESSDNBV.HasValue)
{
excelDto.ESSDNBV = wfAndReinCost.ESSDNBV.Value;
}
excelDto.RECost = wfAndReinCost.RECost;
excelDto.LHICost = wfAndReinCost.LHICost;
excelDto.ESSDCost = wfAndReinCost.ESSDCost;
excelDto.TotalWriteOff = wfAndReinCost.REWriteOff + wfAndReinCost.LHIWriteOff + wfAndReinCost.ESSDWriteOff;
excelDto.RentalStructure = !string.IsNullOrEmpty(finInput.RentalStructure) ? (Nullable <decimal>)decimal.Parse(finInput.RentalStructure) : null;
excelDto.ContributionMargin = finInput.ContributionMargin;
excelDto.SalesCompYr1 = finInput.SalesCompYr1;
excelDto.SalesCompYr2 = finInput.SalesCompYr2;
excelDto.SalesCompYr3 = finInput.SalesCompYr3;
excelDto.SalesCompYr4 = finInput.SalesCompYr4;
excelDto.SalesCompYr5 = finInput.SalesCompYr5;
excelDto.SalesCompYr6 = finInput.SalesCompYr6;
excelDto.SalesCompYr7 = finInput.SalesCompYr7;
excelDto.SalesCompYr8 = finInput.SalesCompYr8;
excelDto.SalesCompYr9 = finInput.SalesCompYr9;
excelDto.SalesCompYr10 = finInput.SalesCompYr10;
excelDto.SalesCompYr11 = finInput.SalesCompYr11;
excelDto.SalesCompYr12 = finInput.SalesCompYr12;
excelDto.SalesCompYr13 = finInput.SalesCompYr13;
excelDto.SalesCompYr14 = finInput.SalesCompYr14;
excelDto.SalesCompYr15 = finInput.SalesCompYr15;
excelDto.SalesCompYr16 = finInput.SalesCompYr16;
excelDto.SalesCompYr17 = finInput.SalesCompYr17;
excelDto.SalesCompYr18 = finInput.SalesCompYr18;
excelDto.SalesCompYr19 = finInput.SalesCompYr19;
excelDto.SalesCompYr20 = finInput.SalesCompYr20;
excelDto.ComSalesDesc = this.ComSalesDesc;
excelDto.CompSales = finInput.CompSalesAdjustment;
excelDto.FinanceYear = finInput.FinanceYear;
excelDto.FinanceMonth = finInput.FinanceMonth;
excelDirector.Input(excelDto);
return(fileName);
}
private static RenewalAnalysisStoreInfo PrepareStoreInfo(string projectId, string usCode)
{
var store = StoreBasicInfo.GetStorInfo(usCode);
var storeMMInfo = StoreMMInfo.Get(usCode);
var storeContract = StoreContractInfo.Get(usCode);
var ta = StoreSTLocation.GetStoreSTLocation(usCode);
var tool = RenewalTool.Get(projectId);
var finOutput = RenewalToolFinMeasureOutput.GetByToolId(tool.Id) ?? new RenewalToolFinMeasureOutput();
var consInfo = RenewalConsInfo.Get(projectId);
var toolWriteOff = RenewalToolWriteOffAndReinCost.Get(projectId, tool.Id);
var floors = string.Join("/", new[] {
string.Join(",", new[] {
ta.Floor1,
ta.Floor2,
ta.Floor3,
ta.Floor4,
ta.Floor5
}.Where(e => !string.IsNullOrEmpty(e)).ToArray()),
string.Join(",", new[] {
ta.FrontCounterFloor1,
ta.FrontCounterFloor2
}.Where(e => !string.IsNullOrEmpty(e)).ToArray())
}.Where(e => !string.IsNullOrEmpty(e)).ToArray());
var seats = string.Join("/", new[] {
new[] { ta.Seats1, ta.Seats2, ta.Seats3, ta.Seats4, ta.Seats5, ta.FrontCounterSeats }.Select(e => {
var val = 0;
int.TryParse(e, out val);
return(val);
}).Sum(),
string.IsNullOrEmpty(ta.OutsideSeats)?0:int.Parse(ta.OutsideSeats)
});
var beTypes = string.Join(",", StoreBEInfo.Search(e => e.StoreCode == usCode && e.BETypeName != "FC" && e.BETypeName != "DT").Select(e => e.BETypeName).ToArray());
var leaseTenureAndTerm = string.Concat(storeContract.LeasePurchaseTerm, " years,from ",
storeContract.StartDate.HasValue ? storeContract.StartDate.Value.ToString("yyyy-MM-dd") : " / ",
"to",
storeContract.EndDate.HasValue ? storeContract.EndDate.Value.ToString("yyyy-MM-dd") : " / "
);
return(new RenewalAnalysisStoreInfo
{
UsCode = store.StoreCode,
NameZHCN = store.NameZHCN,
NameENUS = store.NameENUS,
MinimarketPriority = storeMMInfo.Priority,
TADesrability = storeMMInfo.Desirability,
SitePortfolioType = storeMMInfo.PortfolioTypeName,
SiteRerating = storeMMInfo.LocationRatingPP,
LeasedArea = storeContract.TotalLeasedArea,
OperationSize = ta.TotalArea,
Floors = floors,
Seats = seats,
BEType = beTypes,
LeaseTenureAndTerm = leaseTenureAndTerm,
FreeRentalPeriod = storeContract.FreeRentalPeriod,
RentStructure = storeContract.RentStructure,
DRMFLastTY = finOutput.AnnualRentExpenseLY,
DRMFLastTYSales = finOutput.RentAsProdSalesLY,
SRMFLastTY = finOutput.AnnualRentExpenseYr1,
SRMFLastTYSales = finOutput.RentAsProdSalesYr1,
SRMF1stTY = finOutput.AnnualRentExpenseAvg,
SRMF1stTYSales = finOutput.RentAsProdSalesAvg,
AnnualSOILastTY = finOutput.SOIProdSalesLY,
AnnualSOIAvg = finOutput.SOIProdSalesAvg,
CashROIAvg = finOutput.CashROIAvg,
CashROILastTY = finOutput.CashROILY,
OriginalInvestment = (toolWriteOff.REII + toolWriteOff.LHIII + toolWriteOff.ESSDII).ToString(),
NBV = (toolWriteOff.RENBV + toolWriteOff.LHINBV + toolWriteOff.ESSDNBV).ToString(),
AdditionalInvestmentCost = (toolWriteOff.RECost + toolWriteOff.LHICost + toolWriteOff.ESSDCost).ToString(),
ExclusivityClause = storeContract.ExclusivityClause,
McDsEarlyTerminationRight = storeContract.WithEarlyTerminationClause == null || storeContract.WithEarlyTerminationClause == 0 ? "N" : "Y",
LandlordEntity = storeContract.PartyAFullName,
LastRemodeling = store.ReImageDate.HasValue ? store.ReImageDate.Value.ToString("yyyy-MM-dd") : ""
});
}
public Dictionary <string, string> GetPrintTemplateFields()
{
var project = ProjectInfo.Get(this.ProjectId, FlowCode.TempClosure_ClosurePackage);
var storeBasic = StoreBasicInfo.FirstOrDefault(s => s.StoreCode == project.USCode);
var storeContract = StoreContractInfo.Get(storeBasic.StoreCode);
var assetMgr = ProjectUsers.FirstOrDefault(pu => pu.ProjectId == ProjectId && pu.RoleCode == ProjectUserRoleCode.AssetManager);
var assetActor = ProjectUsers.FirstOrDefault(pu => pu.ProjectId == ProjectId && pu.RoleCode == ProjectUserRoleCode.AssetActor);
var assetRep = ProjectUsers.FirstOrDefault(pu => pu.ProjectId == ProjectId && pu.RoleCode == ProjectUserRoleCode.AssetRep);
var tempClosurePackage = TempClosurePackage.Get(ProjectId);
var tempClosure = TempClosureInfo.Get(ProjectId);
Dictionary <string, string> templateFileds = new Dictionary <string, string>();
templateFileds.Add("WorkflowName", SystemCode.Instance.GetCodeName(FlowCode.TempClosure, ClientCookie.Language));
templateFileds.Add("ProjectID", ProjectId);
templateFileds.Add("USCode", storeBasic.StoreCode);
templateFileds.Add("Region", storeBasic.Region);
templateFileds.Add("StoreNameEN", storeBasic.NameENUS);
templateFileds.Add("Market", storeBasic.Market);
templateFileds.Add("City", storeBasic.CityZHCN);
templateFileds.Add("StoreNameCN", storeBasic.NameZHCN);
templateFileds.Add("StoreAge", Math.Floor((DateTime.Now - storeBasic.OpenDate).TotalDays / 365D).ToString());
templateFileds.Add("OpenDate", storeBasic.OpenDate.ToString("yyyy-MM-dd"));
var storeInfo = StoreBasicInfo.GetStore(project.USCode);
if (storeInfo.StoreContractInfo != null)
{
templateFileds.Add("CurrentLeaseENDYear", storeContract.EndYear);
}
else
{
templateFileds.Add("CurrentLeaseENDYear", "");
}
if (assetMgr != null)
{
templateFileds.Add("AssetsManager", assetMgr.UserNameENUS);
}
else
{
templateFileds.Add("AssetsManager", "");
}
templateFileds.Add("AssetsActor", assetActor.UserNameENUS);
templateFileds.Add("AssetsRep", assetRep.UserNameENUS);
templateFileds.Add("Address", storeBasic.AddressZHCN);
templateFileds.Add("CloseDate", storeBasic.CloseDate.HasValue ? (storeBasic.CloseDate.Value.Year != 1900 ? storeBasic.CloseDate.Value.ToString("yyyy-MM-dd") : "") : "");
templateFileds.Add("ClosureDate", tempClosure.ActualTempClosureDate.ToString("yyyy-MM-dd"));
templateFileds.Add("LeaseExpireDate", tempClosure.LeaseExpireDate.HasValue ? tempClosure.LeaseExpireDate.Value.ToString("yyyy-MM-dd") : "");
templateFileds.Add("ReOpenDate", tempClosure.ActualReopenDate.ToString("yyyy-MM-dd"));
templateFileds.Add("RentFreeTerm", string.IsNullOrEmpty(tempClosurePackage.RentReliefClause) ? "否" : "是");
templateFileds.Add("RentFreeStartDate", tempClosurePackage.RentReliefStartDate.HasValue ? tempClosurePackage.RentReliefStartDate.Value.ToString("yyyy-MM-dd") : "");
templateFileds.Add("RentFreeEndDate", tempClosurePackage.RentReliefEndDate.HasValue ? tempClosurePackage.RentReliefEndDate.Value.ToString("yyyy-MM-dd") : "");
templateFileds.Add("FreeRentTerm", tempClosurePackage.RentReliefClause);
templateFileds.Add("LandlordName", tempClosure.LandlordName);
if (tempClosurePackage.RentRelief.HasValue)
{
templateFileds.Add("ReliefRent", tempClosurePackage.RentRelief.Value ? "是" : "否");
}
else
{
templateFileds.Add("ReliefRent", "");
}
return(templateFileds);
}
