// Get All Previous Year Goal
public GoalYTDDTO GetPreviousGoalYTDList(int storeId, DateTime yearWeekStartDate)
{
GoalYTDDTO dto = null;
GoalsDTO goalsDTO = GetCurrentYearGoalsList(storeId, DateTime.Now.AddYears(-2).Year);
int weekCount = ValidationUtility.YearWeekCount(yearWeekStartDate.AddYears(-1));
if (weekCount == 0)
{
weekCount = 1;
}
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
try
{
DateTime lastYearWeekStartDate = ValidationUtility.YearWeekStartDate(yearWeekStartDate.AddYears(-1));
DateTime lastYearWeekEndDate = ValidationUtility.GetActualWeekStartDate(new DateTime(yearWeekStartDate.AddYears(-1).Year, 12, 31));
//string query = "select SUM(FoodCost)as FoodCost, SUM(LaborCost) as LaborCost,SUM(FoodLaborCost) as FoodLaborCost,SUM(SalesIncrease) as SalesIncrease,SUM(AUV) as AUV,SUM(UnitFootlongSales) as UnitFootlongSales,SUM(FsPercent) as FsPercent, "
// + " SUM(DrinkCount) as DrinkCount, SUM(CheckItem) as CheckItem,SUM(CheckIndex) as CheckIndex,SUM(CustomerCount) as CustomerCount,SUM(CustomerCountIndex) as CustomerCountIndex,SUM(Unit) as Unit,SUM(UI) as UI,sum(Productivity) as Productivity, SUM(Factor)as Factor from dbo.GoalsYTD where StoreId=" + SQLUtility.getInteger(storeId) + " "
// + " and WeekStartDate Between '" + SQLUtility.FormateDateYYYYMMDD(lastYearWeekStartDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(lastYearWeekEndDate.AddDays(6)) + "'";
string query = "select SUM(FoodCost)as FoodCost, SUM(LaborCost) as LaborCost,SUM(FoodLaborCost) as FoodLaborCost,SUM(SalesIncrease) as SalesIncrease,SUM(AUV) as AUV,SUM(UnitFootlongSales) as UnitFootlongSales,SUM(FsPercent) as FsPercent, "
+ " SUM(DrinkCount) as DrinkCount, SUM(CheckItem) as CheckItem,SUM(CheckIndex) as CheckIndex,SUM(CustomerCount) as CustomerCount,SUM(CustomerCountIndex) as CustomerCountIndex,SUM(Unit) as Unit,SUM(UI) as UI,sum(Productivity) as Productivity, SUM(Factor)as Factor from dbo.GoalsYTD where StoreId=" + SQLUtility.getInteger(storeId) + " "
+ " and WeekStartDate >= '" + SQLUtility.FormateDateYYYYMMDD(lastYearWeekStartDate) + "' and WeekEndDate <= '" + SQLUtility.FormateDateYYYYMMDD(lastYearWeekEndDate.AddDays(6)) + "'";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
double foodCost = ValidationUtility.ToDouble(reader["FoodCost"].ToString());
double laborCost = ValidationUtility.ToDouble(reader["LaborCost"].ToString());
double foodLaborCost = ValidationUtility.ToDouble(reader["FoodLaborCost"].ToString());
double salesIncrease = ValidationUtility.ToDouble(reader["SalesIncrease"].ToString());
double auv = ValidationUtility.ToDouble(reader["AUV"].ToString());
double unitFootlongSales = ValidationUtility.ToDouble(reader["UnitFootlongSales"].ToString());
double fsPercent = ValidationUtility.ToDouble(reader["FsPercent"].ToString());
double drinkCount = ValidationUtility.ToDouble(reader["DrinkCount"].ToString());
double checkItem = ValidationUtility.ToDouble(reader["CheckItem"].ToString());
// double checkIndex = ValidationUtility.ToDouble(reader["CheckIndex"].ToString());
double customerCount = ValidationUtility.ToDouble(reader["CustomerCount"].ToString());
//double customerCountIndex = ValidationUtility.ToDouble(reader["CustomerCountIndex"].ToString());
double unit = ValidationUtility.ToDouble(reader["Unit"].ToString());
// double ui = ValidationUtility.ToDouble(reader["UI"].ToString());
double productivity = ValidationUtility.ToDouble(reader["Productivity"].ToString());
double factor = ValidationUtility.ToDouble(reader["Factor"].ToString());
double checkIndex = 0;
double customerCountIndex = 0;
double ui = 0;
if (goalsDTO != null)
{
checkIndex = goalsDTO.CheckIndex;
customerCountIndex = goalsDTO.CustomerCountIndex;
ui = goalsDTO.Ui;
}
dto = new GoalYTDDTO
{
StoreId = storeId,
Year = yearWeekStartDate.Year,
FoodCost = foodCost / weekCount,
LaborCost = laborCost / weekCount,
FoodLaborCost = foodLaborCost / weekCount,
SalesIncrease = salesIncrease / weekCount,
Auv = auv / weekCount,
UnitFootlongSales = unitFootlongSales / weekCount,
FsPercent = fsPercent / weekCount,
DrinkCount = drinkCount / weekCount,
CheckItem = checkItem / weekCount,
CheckIndex = checkIndex / weekCount,
CustomerCount = customerCount / weekCount,
CustomerCountIndex = customerCountIndex / weekCount,
Unit = unit / weekCount,
Ui = ui / weekCount,
Productivity = productivity / weekCount,
Factor = factor / weekCount
};
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetCurrrentYTDList Method", ex);
}
finally
{
db.CloseConnection(conn);
}
return dto;
}
public ArrayList GetCurrentWeekGoalsYTD(DateTime currentWeekStartDate, ArrayList storeList)
{
ArrayList currentYTDList = new ArrayList();
try
{
GoalYTDDTO ytdDTO = new GoalYTDDTO();
ReportModel reportModel = new ReportModel();
StoreDTO storeDTO = (StoreDTO)storeList[0];
WeeklyPaperworkModel model = new WeeklyPaperworkModel();
ytdDTO.StoreId = storeDTO.Id;
ytdDTO.Year = currentWeekStartDate.Year;
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(currentWeekStartDate);
ytdDTO.WeekStartDate = weekStartDate;
ytdDTO.WeekEndDate = weekStartDate.AddDays(6);
bool flag = model.IsWeeklyPaperExistIntoLocalDb(storeDTO.Id, weekStartDate);
if (!flag)
{
model.AddWeeklyPaperWork(weekStartDate, storeList, true);
WeeklyPaperworkDTO dto = model.GetWeeklyPaperWorkOfWeek(storeDTO.Id, weekStartDate);
ytdDTO.FoodCost = dto.CostPercent;
ytdDTO.LaborCost = dto.LaborCostPercent;
ytdDTO.FoodLaborCost = dto.CostPercent + dto.LaborCostPercent;
}
else
{
WeeklyPaperworkDTO dto = model.GetWeeklyPaperWorkOfWeek(storeDTO.Id, weekStartDate);
ytdDTO.FoodCost = dto.CostPercent;
ytdDTO.LaborCost = dto.LaborCostPercent;
ytdDTO.FoodLaborCost = dto.CostPercent + dto.LaborCostPercent;
}
double totalsubWaySale = reportModel.TotalSabWaySaleOfYear(weekStartDate, weekStartDate.AddDays(6), storeDTO.ConnectionString);
ytdDTO.Auv = totalsubWaySale;
double totalDrinkSale = reportModel.TotalDrinkSale(weekStartDate, weekStartDate.AddDays(6), storeDTO.ConnectionString);
ytdDTO.DrinkCount = ValidationUtility.IsNan(totalDrinkSale / totalsubWaySale);
ArrayList list = reportModel.GetSalesItemizationFoodLong(weekStartDate, storeDTO.ConnectionString);
ytdDTO.UnitFootlongSales = (double)list[0];
ytdDTO.FsPercent = ValidationUtility.IsNan(ytdDTO.UnitFootlongSales/ytdDTO.Auv);
ytdDTO.Unit = (double)list[1];
ytdDTO.Factor = (double)list[2];
ytdDTO.CustomerCount = (double)list[3];
ytdDTO.CheckItem = (double)list[4];
ytdDTO.Productivity = (double)list[5];
ytdDTO.HoursWorked = (double)list[6];
currentYTDList.Add(ytdDTO);
}
catch (Exception ex)
{
log.Error("Exception in GetCurrentWeekGoalsYTD Method", ex);
}
return currentYTDList;
}
//public GoalsDTO GetLast(DateTime date)
//{
//}
public void AddCurrentYearYTD(DateTime date, ArrayList storeList)
{
try
{
ArrayList ytdList = new ArrayList();
ArrayList list = new ArrayList();
ReportModel reportModel = new ReportModel();
GoalYTDDTO ytdDTO = new GoalYTDDTO();
bool isWeekExsist = false;
StoreDTO storeDTO = (StoreDTO)storeList[0];
WeeklyPaperworkModel model = new WeeklyPaperworkModel();
DateTime yearFirstWeekStartDate = ValidationUtility.YearWeekStartDate(date);
bool flag = model.IsWeeklyPaperExistIntoLocalDb(storeDTO.Id, yearFirstWeekStartDate);
ytdDTO.StoreId = storeDTO.Id;
ytdDTO.Year = date.Year;
ytdDTO.WeekStartDate = yearFirstWeekStartDate;
ytdDTO.WeekEndDate = yearFirstWeekStartDate.AddDays(6);
if (!flag)
{
model.AddWeeklyPaperWork(yearFirstWeekStartDate, storeList, true);
WeeklyPaperworkDTO dto = model.GetWeeklyPaperWorkOfWeek(storeDTO.Id, yearFirstWeekStartDate);
ytdDTO.FoodCost = dto.CostPercent;
ytdDTO.LaborCost = dto.LaborCostPercent;
ytdDTO.FoodLaborCost = dto.CostPercent + dto.LaborCostPercent;
}
else
{
WeeklyPaperworkDTO dto = model.GetWeeklyPaperWorkOfWeek(storeDTO.Id, yearFirstWeekStartDate);
ytdDTO.FoodCost = dto.CostPercent;
ytdDTO.LaborCost = dto.LaborCostPercent;
ytdDTO.FoodLaborCost = dto.CostPercent + dto.LaborCostPercent;
}
double totalsubWaySale = reportModel.TotalSabWaySaleOfYear(yearFirstWeekStartDate, yearFirstWeekStartDate.AddDays(6), storeDTO.ConnectionString);
ytdDTO.Auv = totalsubWaySale;
double totalDrinkSale = reportModel.TotalDrinkSale(yearFirstWeekStartDate, yearFirstWeekStartDate.AddDays(6), storeDTO.ConnectionString);
ytdDTO.DrinkCount = ValidationUtility.IsNan(totalDrinkSale / totalsubWaySale);
list = reportModel.GetSalesItemizationFoodLong(yearFirstWeekStartDate, storeDTO.ConnectionString);
ytdDTO.UnitFootlongSales = (double)list[0];
ytdDTO.FsPercent = ValidationUtility.IsNan(ytdDTO.UnitFootlongSales/ytdDTO.Auv);
ytdDTO.Unit = (double)list[1];
ytdDTO.Factor = (double)list[2];
ytdDTO.CustomerCount = (double)list[3];
ytdDTO.CheckItem = (double)list[4];
ytdDTO.Productivity = (double)list[5];
ytdDTO.HoursWorked = (double)list[6];
DateTime everyWeekStarDate = yearFirstWeekStartDate;
DateTime currenWeekStartDate = ValidationUtility.GetActualWeekStartDate(DateTime.Now);
ytdList.Add(ytdDTO);
while (!isWeekExsist)
{
ytdDTO = new GoalYTDDTO();
ytdDTO.StoreId = storeDTO.Id;
ytdDTO.Year = date.Year;
everyWeekStarDate = everyWeekStarDate.AddDays(7);
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(everyWeekStarDate);
ytdDTO.WeekStartDate = weekStartDate;
ytdDTO.WeekEndDate = weekStartDate.AddDays(6);
flag = model.IsWeeklyPaperExistIntoLocalDb(storeDTO.Id, weekStartDate);
if (currenWeekStartDate.Date.Equals(weekStartDate.Date))
{
isWeekExsist = true;
if (!flag)
{
model.AddWeeklyPaperWork(weekStartDate, storeList, true);
WeeklyPaperworkDTO dto = model.GetWeeklyPaperWorkOfWeek(storeDTO.Id, weekStartDate);
ytdDTO.FoodCost = dto.CostPercent;
ytdDTO.LaborCost = dto.LaborCostPercent;
ytdDTO.FoodLaborCost = dto.CostPercent + dto.LaborCostPercent;
}
totalsubWaySale = reportModel.TotalSabWaySaleOfYear(weekStartDate, weekStartDate.AddDays(6), storeDTO.ConnectionString);
ytdDTO.Auv = totalsubWaySale;
totalDrinkSale = reportModel.TotalDrinkSale(weekStartDate, weekStartDate.AddDays(6), storeDTO.ConnectionString);
ytdDTO.DrinkCount = ValidationUtility.IsNan(totalDrinkSale / totalsubWaySale);
list = reportModel.GetSalesItemizationFoodLong(weekStartDate, storeDTO.ConnectionString);
ytdDTO.UnitFootlongSales = (double)list[0];
ytdDTO.FsPercent = ValidationUtility.IsNan(ytdDTO.UnitFootlongSales / ytdDTO.Auv);
ytdDTO.Unit = (double)list[1];
ytdDTO.Factor = (double)list[2];
ytdDTO.CustomerCount = (double)list[3];
ytdDTO.CheckItem = (double)list[4];
ytdDTO.Productivity = (double)list[5];
ytdDTO.HoursWorked = (double)list[6];
}
else
{
if (!flag)
{
model.AddWeeklyPaperWork(weekStartDate, storeList, true);
WeeklyPaperworkDTO dto = model.GetWeeklyPaperWorkOfWeek(storeDTO.Id, weekStartDate);
ytdDTO.FoodCost = dto.CostPercent;
ytdDTO.LaborCost = dto.LaborCostPercent;
ytdDTO.FoodLaborCost = dto.CostPercent + dto.LaborCostPercent;
}
totalsubWaySale= reportModel.TotalSabWaySaleOfYear(weekStartDate, weekStartDate.AddDays(6), storeDTO.ConnectionString);
ytdDTO.Auv = totalsubWaySale;
totalDrinkSale = reportModel.TotalDrinkSale(weekStartDate, weekStartDate.AddDays(6), storeDTO.ConnectionString);
ytdDTO.DrinkCount = ValidationUtility.IsNan(totalDrinkSale / totalsubWaySale);
list = reportModel.GetSalesItemizationFoodLong(weekStartDate, storeDTO.ConnectionString);
ytdDTO.UnitFootlongSales = (double)list[0];
ytdDTO.FsPercent = ValidationUtility.IsNan(ytdDTO.UnitFootlongSales / ytdDTO.Auv);
ytdDTO.Unit = (double)list[1];
ytdDTO.Factor = (double)list[2];
ytdDTO.CustomerCount = (double)list[3];
ytdDTO.CheckItem = (double)list[4];
ytdDTO.Productivity = (double)list[5];
ytdDTO.HoursWorked = (double)list[6];
}
ytdList.Add(ytdDTO);
}
AddGoalYTD(ytdList);
}
catch (Exception ex)
{
log.Error("Exception in AddCurrentYearYTD Method", ex);
}
}
public GoalYTDDTO GetAllStoreAverageGoals(ArrayList storeGoalList)
{
GoalYTDDTO newgoalYTDDTO = new GoalYTDDTO();
int storeCount = storeGoalList.Count;
foreach (GoalYTDDTO previousYearGoalDTO in storeGoalList)
{
newgoalYTDDTO.FoodCost = newgoalYTDDTO.FoodCost + previousYearGoalDTO.FoodCost;
newgoalYTDDTO.LaborCost = newgoalYTDDTO.LaborCost + previousYearGoalDTO.FoodCost;
newgoalYTDDTO.FoodLaborCost = newgoalYTDDTO.FoodLaborCost + previousYearGoalDTO.FoodLaborCost;
newgoalYTDDTO.SalesIncrease = newgoalYTDDTO.SalesIncrease + previousYearGoalDTO.SalesIncrease;
newgoalYTDDTO.Auv = newgoalYTDDTO.Auv + previousYearGoalDTO.Auv;
newgoalYTDDTO.UnitFootlongSales = newgoalYTDDTO.UnitFootlongSales + previousYearGoalDTO.UnitFootlongSales;
newgoalYTDDTO.FsPercent = newgoalYTDDTO.FsPercent + previousYearGoalDTO.FsPercent;
newgoalYTDDTO.DrinkCount = newgoalYTDDTO.DrinkCount + previousYearGoalDTO.DrinkCount;
newgoalYTDDTO.CheckItem = newgoalYTDDTO.CheckItem + previousYearGoalDTO.CheckItem;
newgoalYTDDTO.CheckIndex = newgoalYTDDTO.CheckIndex + previousYearGoalDTO.CheckIndex;
newgoalYTDDTO.CustomerCount = newgoalYTDDTO.CustomerCount + previousYearGoalDTO.CustomerCount;
newgoalYTDDTO.CustomerCountIndex = newgoalYTDDTO.CustomerCountIndex + previousYearGoalDTO.CustomerCountIndex;
newgoalYTDDTO.Unit = newgoalYTDDTO.Unit + previousYearGoalDTO.Unit;
newgoalYTDDTO.Ui = newgoalYTDDTO.Ui + previousYearGoalDTO.Ui;
newgoalYTDDTO.Productivity = newgoalYTDDTO.Productivity + previousYearGoalDTO.Productivity;
newgoalYTDDTO.Factor = newgoalYTDDTO.Factor + previousYearGoalDTO.Factor;
}
GoalYTDDTO jsonDTO = new GoalYTDDTO
{
FoodCost = newgoalYTDDTO.FoodCost / storeCount,
LaborCost = newgoalYTDDTO.LaborCost / storeCount,
FoodLaborCost = newgoalYTDDTO.FoodLaborCost / storeCount,
SalesIncrease = newgoalYTDDTO.SalesIncrease / storeCount,
Auv = newgoalYTDDTO.Auv / storeCount,
UnitFootlongSales = newgoalYTDDTO.UnitFootlongSales / storeCount,
FsPercent = newgoalYTDDTO.FsPercent / storeCount,
DrinkCount = newgoalYTDDTO.DrinkCount / storeCount,
CheckItem = newgoalYTDDTO.CheckItem / storeCount,
CheckIndex = newgoalYTDDTO.CheckIndex / storeCount,
CustomerCount = newgoalYTDDTO.CustomerCount / storeCount,
CustomerCountIndex = newgoalYTDDTO.CustomerCountIndex / storeCount,
Unit = newgoalYTDDTO.Unit / storeCount,
Ui = newgoalYTDDTO.Ui / storeCount,
Productivity = newgoalYTDDTO.Productivity / storeCount,
Factor = newgoalYTDDTO.Factor / storeCount
};
return jsonDTO;
}