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 static TempClosureReopenMemo Get(string projectId)
{
var db = PrepareDb();
var usCode = db.ProjectInfo.FirstOrDefault(pi => pi.ProjectId == projectId).USCode;
var reopenMemo = FirstOrDefault(cm => cm.ProjectId == projectId);
if (reopenMemo == null)
{
reopenMemo = SqlQuery <TempClosureReopenMemo>(@"
SELECT CAST('00000000-0000-0000-0000-000000000000' AS uniqueidentifier) [Id]
,@ProjectId [ProjectId]
,@USCode [USCode]
,s.RegionCode [RegionCode]
,s.RegionENUS [RegionENUS]
,s.RegionZHCN [RegionZHCN]
,s.MarketCode [MarketCode]
,s.MarketENUS [MarketENUS]
,s.MarketZHCN [MarketZHCN]
,s.ProvinceENUS [ProvinceENUS]
,s.ProvinceZHCN [ProvinceZHCN]
,s.CityCode [CityCode]
,s.CityENUS [CityENUS]
,s.CityZHCN [CityZHCN]
,s.NameENUS [StoreENUS]
,s.NameZHCN [StoreZHCN]
,'' [PipelineId]
,NULL MarketDesirability
,NULL RERating
,NULL [ActualConsFinishDate]
,ci.ActualReopenDate [OpeningDate]
,s.PortfolioType [PortfolioType]
,s.PortfolioTypeName [ProtfolioTypeName]
,s.TACode [TAClassification]
,s.TAName [TAClassificationName]
,cast(cast(isnull(sloc.Seats1,0) as decimal)+cast(isnull(sloc.Seats2,0) as decimal)
+cast(isnull(sloc.Seats3,0) as decimal)+cast(isnull(sloc.Seats4,0) as decimal)
+cast(isnull(sloc.Seats5,0) as decimal) as nvarchar(10)) SeatingNum
,sloc.TotalArea [BusinessArea]
,sloc.KitchenFloor [KitchenFloor]
,sloc.Floor [SeatingFloor]
,'' [EarlyTerminationClause]
,NULL CarParkTotal
,sloc.KitchenFloor [ParkingNum]
,'' [ContractType]
,'' [Kiosk]
,sd.AssetRepEid [RERep]
,sd.PlannerEid [Planner]
,sd.RERepName RERepName
,sd.PlannerName PlannerName
,@Creator Creator
,getdate() CreateTime
FROM StoreBasicInfo s INNER JOIN StoreSTLocation sloc
ON sloc.StoreCode = s.StoreCode INNER JOIN StoreDevelop sd
ON sd.StoreCode = s.StoreCode INNER JOIN TempClosureInfo ci
ON ci.USCode = s.StoreCode
WHERE s.StoreCode = @USCode AND ci.ProjectId = @ProjectId
", new
{
ProjectId = projectId,
USCode = usCode,
Creator = ClientCookie.UserCode
}).FirstOrDefault();
var contractType = StoreContractInfo.SearchByProject(projectId).OrderByDescending(c => c.CreatedTime).Select(c => c.LeasePurchase).FirstOrDefault();
var taInfo = StoreMMInfo.Search(ta => ta.StoreCode == usCode).Select(ta => new
{
Desirability = ta.Desirability,
LocationRatingPP = ta.LocationRatingPP
}).FirstOrDefault();
var dic_contractType = Dictionary.GetDictionary(contractType);
reopenMemo.ContractType = dic_contractType == null ? "" : dic_contractType.NameZHCN;
var dic_desirability = Dictionary.GetDictionary(taInfo.Desirability);
reopenMemo.MarketDesirability = dic_desirability == null ? "" : dic_desirability.NameZHCN;
var dic_locationRatingPP = Dictionary.GetDictionary(taInfo.LocationRatingPP);
reopenMemo.RERating = dic_locationRatingPP == null ? "" : dic_locationRatingPP.NameZHCN;
var tempMemo = TempClosureMemo.GetTempClosureMemo(projectId);
if (tempMemo != null)
{
reopenMemo.TempCloseDate = tempMemo.ClosureDate;
}
}
return(reopenMemo);
}
