public virtual ActionResult CashControlStoredProcedure(string name)
{
ReportModel rep = new ReportModel();
Dictionary<int, string> connectionString = (Dictionary<int, string>)Session["ConnectionString"];
string storeNameConnectionString = "";
if (connectionString.ContainsKey(1))
{
storeNameConnectionString = connectionString[1];
}
if (!ValidationUtility.IsNull(name))
{
DateTime selectDate = ValidationUtility.ToDate(name);
DataSet report = rep.GetControlSheet(selectDate, storeNameConnectionString);
// return View(report);
if (report != null && report.Tables[0].Rows.Count > 0)
{
return View(report);
}
else
{
ViewBag.Message = " Exception occured in fetching data ";
return View();
}
}
else
{
DataSet report = rep.GetControlSheet(DateTime.Now, storeNameConnectionString);
if (report != null && report.Tables.Count > 0)
{
return View(report);
}
else
{
ViewBag.Message = " Exception occured in fetching data ";
return View();
}
}
}
public JsonResult GetcontrolSheetData(string sId, string selectDate)
{
ReportModel repModel = new ReportModel();
ArrayList AllReportList = new ArrayList();
int storeId = 1;
DateTime date = DateTime.Now;
if (!ValidationUtility.IsNull(sId))
{
storeId = ValidationUtility.ToInteger(sId);
}
if (!ValidationUtility.IsNull(selectDate))
{
date = ValidationUtility.ToDate(selectDate);
}
Dictionary<int, string> connectionString = (Dictionary<int, string>)Session["ConnectionString"];
string storeNameConnectionString = "";
if (connectionString.ContainsKey(storeId))
{
storeNameConnectionString = connectionString[storeId];
}
AllReportList.Add(repModel.GetCashControlData(date, storeNameConnectionString));
AllReportList.Add(repModel.GetSalesItemizationData(date, storeNameConnectionString));
AllReportList.Add(repModel.GetBreadControlData(date, storeNameConnectionString));
AllReportList.Add(repModel.GetUnitsItemizationData(date, storeNameConnectionString));
AllReportList.Add(repModel.GetLaborData(date, storeNameConnectionString));
AllReportList.Add(WeekInformation(date));
AllReportList.Add(repModel.GetTotalSubwaySale(date, storeNameConnectionString));
AllReportList.Add(repModel.GetTotalUnitSale(date, storeNameConnectionString));
AllReportList.Add(repModel.GetTotalDrinkSale(date, storeNameConnectionString));
return Json(AllReportList, JsonRequestBehavior.AllowGet);
}
//Get Data From server For Actual Value
public WeeklyPaperworkDTO GetWeeklyPaperWork(DateTime currentWeekStartDate, ArrayList storeList)
{
WeeklyPaperworkDTO dto = null;
try
{
ReportModel reportModel = new ReportModel();
StoreDTO storeDTO = (StoreDTO)storeList[0];
WeeklyPaperworkModel model = new WeeklyPaperworkModel();
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(currentWeekStartDate);
bool flag = model.IsWeeklyPaperExistIntoLocalDb(storeDTO.Id, weekStartDate);
if (!flag)
{
model.AddWeeklyPaperWork(weekStartDate, storeList, true);
dto = model.GetWeeklyPaperWorkOfWeek(storeDTO.Id, weekStartDate);
}
else
{
dto = model.GetWeeklyPaperWorkOfWeek(storeDTO.Id, weekStartDate);
}
}
catch (Exception ex)
{
log.Error("Exception in GetCurrentWeekGoalsYTD Method", ex);
}
return dto;
}
public ArrayList AddWeeklyPaperWork(DateTime selectDate, ArrayList storeList, bool shouldAdd)
{
ArrayList list = new ArrayList();
try
{
DateTime startWeekDate = ValidationUtility.GetActualWeekStartDate(selectDate);
DateTime endWeekDate = startWeekDate.AddDays(6);
ReportModel reportModel = new ReportModel();
foreach (StoreDTO storeDTO in storeList)
{
//delete(storeDTO.Id, startWeekDate);
WeeklyPaperworkDTO weeklyPaperworkDTO = new WeeklyPaperworkDTO();
weeklyPaperworkDTO.WeekStartDate = startWeekDate;
weeklyPaperworkDTO.StoreId = storeDTO.Id;
string netSalesQuery = "select sum(AmtTotalSales) as ammount from Report.OpeningInformationsSummary where OpeningInformationId in (select OpeningInformationId from dbo.OpeningInformation where BusinessDate between '" + SQLUtility.FormateDateYYYYMMDD(startWeekDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(endWeekDate) + "')";
double netSales = reportModel.GetSubWaySale(netSalesQuery, storeDTO.ConnectionString);
weeklyPaperworkDTO.NetSales = netSales;
DataTable cashControllData = GetCashControlData(selectDate, storeDTO.ConnectionString);
//double paidOuts = ValidationUtility.ToDouble(cashControllData.Rows[cashControllData.Rows.Count - 1]["PaidOuts"].ToString());
//weeklyPaperworkDTO.PaidOuts = paidOuts;
double giftCard = ValidationUtility.ToDouble(cashControllData.Rows[cashControllData.Rows.Count - 1]["GiftCards"].ToString());
weeklyPaperworkDTO.GcRedeem = giftCard;
weeklyPaperworkDTO.Total = weeklyPaperworkDTO.Ar + weeklyPaperworkDTO.PaidOuts + weeklyPaperworkDTO.PettyExpense;
//Get SalesItemization controll sheet Data
DataTable salesItemizationData = GetSalesItemizationData(selectDate, storeDTO.ConnectionString);
double cashCardRightSide = ValidationUtility.ToDouble(salesItemizationData.Rows[salesItemizationData.Rows.Count - 12]["WeekTotal"].ToString());
weeklyPaperworkDTO.GiftCardSales = cashCardRightSide;
weeklyPaperworkDTO.GcDifference = giftCard - cashCardRightSide;
double salesTax = ValidationUtility.ToDouble(salesItemizationData.Rows[salesItemizationData.Rows.Count - 2]["WeekTotal"].ToString());
weeklyPaperworkDTO.AdjTax = salesTax;
//this value will change
weeklyPaperworkDTO.TaxPercent = 8.25;
list.Add(weeklyPaperworkDTO);
}
if (shouldAdd)
{
AddPaperWork(list);
}
else
{
// Update
UpdatePaperWork(list);
}
}
catch (Exception ex)
{
log.Error("Exception in AddWeeklyPaperWork Method ", ex);
}
return list;
}
public ArrayList GetControlSheetDataOfYear(DateTime date, ArrayList storeList)
{
ArrayList listItem = new ArrayList();
try
{
double totalFoodLong = 0;
double totalUnitSold = 0;
double totalFactor = 0;
double totalCustomerCount = 0;
double totalAverageTicket = 0;
double totalProductivity = 0;
bool isWeekExsist = false;
ArrayList list = null;
StoreDTO storeDTO = (StoreDTO)storeList[0];
ReportModel reportModel = new ReportModel();
DateTime yearFirstWeekDate = ValidationUtility.YearWeekStartDate(date);
DateTime yearLastWeekDate = ValidationUtility.GetActualWeekStartDate(new DateTime(date.Year, 12, 31));
ArrayList foodLaborCostList = GetYearlyFoodAndLaborCostPercent(yearFirstWeekDate, yearLastWeekDate.AddDays(6),storeDTO.Id);
double foodCost = (double)foodLaborCostList[0];
double laborCost = (double)foodLaborCostList[1];
double totalsubWaySale = reportModel.TotalSabWaySaleOfYear(yearFirstWeekDate, yearLastWeekDate.AddDays(6), storeDTO.ConnectionString);
double totalDrinkSale = reportModel.TotalDrinkSale(yearFirstWeekDate, yearLastWeekDate.AddDays(6), storeDTO.ConnectionString);
list = reportModel.GetSalesItemizationFoodLong(yearFirstWeekDate, storeDTO.ConnectionString);
totalFoodLong = (double)list[0];
totalUnitSold = (double)list[1];
totalFactor = (double)list[2];
totalCustomerCount = (double)list[3];
totalAverageTicket = (double)list[4];
totalProductivity = (double)list[5];
DateTime everyWeekStarDate = yearFirstWeekDate;
while (!isWeekExsist)
{
double total = 0;
double unitSold = 0;
double factor = 0;
double customerCount = 0;
double averageTicket = 0;
double productivity = 0;
everyWeekStarDate = everyWeekStarDate.AddDays(7);
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(everyWeekStarDate);
if (yearLastWeekDate.Date.Equals(weekStartDate.Date))
{
isWeekExsist = true;
list = reportModel.GetSalesItemizationFoodLong(weekStartDate, storeDTO.ConnectionString);
total = (double)list[0];
unitSold = (double)list[1];
factor = (double)list[2];
customerCount = (double)list[3];
averageTicket = (double)list[4];
productivity = (double)list[5];
}
else
{
list = reportModel.GetSalesItemizationFoodLong(weekStartDate, storeDTO.ConnectionString);
total = (double)list[0];
unitSold = (double)list[1];
factor = (double)list[2];
customerCount = (double)list[3];
averageTicket = (double)list[4];
productivity = (double)list[5];
}
totalFoodLong = totalFoodLong + total;
totalUnitSold = totalUnitSold + unitSold;
totalFactor = totalFactor + factor;
totalCustomerCount = totalCustomerCount + customerCount;
totalAverageTicket = totalAverageTicket + averageTicket;
totalProductivity = totalProductivity + productivity;
}
listItem.Add(foodCost);
listItem.Add(laborCost);
listItem.Add(totalsubWaySale);
listItem.Add(totalFoodLong);
listItem.Add(totalDrinkSale);
listItem.Add(totalUnitSold);
listItem.Add(totalFactor);
listItem.Add(totalCustomerCount);
listItem.Add(totalAverageTicket);
listItem.Add(totalProductivity);
}
catch (Exception ex)
{
log.Error("Exception in GetControlSheetDataOfYear Method",ex);
}
return listItem;
}
public JsonResult StoreList()
{
ReportModel repModel = new ReportModel();
// ArrayList list = repModel.GetStoreList();
ArrayList list = ValidationUtility.GetActiveStoreList(false);
return Json(list, JsonRequestBehavior.AllowGet);
}
public virtual ActionResult GetStorName(string storeName)
{
Dictionary<string, string> connectionString = (Dictionary<string, string>)Session["ConnectionString"];
string storeNameConnectionString = "POS1";
if (connectionString.ContainsKey("POS1"))
{
storeNameConnectionString = connectionString["POS1"];
}
ReportModel rep = new ReportModel();
DataSet report = rep.GetControlSheet(DateTime.Now, storeNameConnectionString);
if (report != null && report.Tables.Count > 0)
{
return View("CashControlStoredProcedure", report);
}
else
{
ViewBag.Message = " Exception occured in fetching data ";
return View("CashControlStoredProcedure");
}
}
//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 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;
}
