/*
*Get WeeklyPaper Work Info Id based
*/
public WeeklyPaperworkDTO GetWeeklyPaperWorkOfWeek(int id)
{
WeeklyPaperworkDTO dto = null;
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
try
{
conn = db.OpenConnection();
//string query = " select SUM(CostPercent) as CostPercent ,SUM(LaborCostPercent) as LaborCostPercent from dbo.WeeklyPaperWork "
// + " where WeekStartDate between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDay.AddDays(6)) + "'";
String query = " select StoreId,NetSales,StoreTransfer,PFG1,PFG2,Coke,LaborCost,WeekStartDate from dbo.WeeklyPaperWork where Id=" + SQLUtility.getInteger(id) + " ";
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
dto = new WeeklyPaperworkDTO();
dto.StoreId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
double netSales = ValidationUtility.ToDouble(reader["NetSales"].ToString());
double storeTransfer = ValidationUtility.ToDouble(reader["StoreTransfer"].ToString());
double pfg1 = ValidationUtility.ToDouble(reader["PFG1"].ToString());
double pfg2 = ValidationUtility.ToDouble(reader["PFG2"].ToString());
double coke = ValidationUtility.ToDouble(reader["Coke"].ToString());
double laborCost = ValidationUtility.ToDouble(reader["LaborCost"].ToString());
dto.WeekStartDate = ValidationUtility.ToDate(reader["WeekStartDate"].ToString());
double totalPFG = pfg1 + pfg2;
double totalCost = storeTransfer + totalPFG + coke;
double costPercent = ValidationUtility.IsNan(totalCost / netSales);
double laborCostPercent = ValidationUtility.IsNan(laborCost / netSales);
dto.CostPercent = costPercent;
dto.LaborCostPercent = laborCostPercent;
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception into IsWeeklyPaperExistIntoLocalDb method", ex);
}
finally
{
db.CloseConnection(conn);
}
return dto;
}
//public ArrayList GetAvg(ArrayList list)
//{
// foreach (WeeklyPaperworkDTO dto in list)
// {
// }
//}
public void UpdateWeeklyPaperSales(WeeklyPaperworkDTO weeklyPaperworkDTO)
{
DataBaseUtility db = new DataBaseUtility();
string query = "update dbo.WeeklyPaperWork set AR=" + SQLUtility.getDouble(weeklyPaperworkDTO.Ar) + ",PaidOuts=" + SQLUtility.getDouble(weeklyPaperworkDTO.PaidOuts) + ",PettyExpense=" + SQLUtility.getDouble(weeklyPaperworkDTO.PettyExpense) + ",StoreTransfer=" + SQLUtility.getDouble(weeklyPaperworkDTO.StoreTransfer) + ",PFG1=" + SQLUtility.getDouble(weeklyPaperworkDTO.Pfg1) + ",PFG2=" + SQLUtility.getDouble(weeklyPaperworkDTO.Pfg2) + ",Coke=" + SQLUtility.getDouble(weeklyPaperworkDTO.Coke) + ",LaborCost=" + SQLUtility.getDouble(weeklyPaperworkDTO.LaborCost) + ",TaxPercent=" + SQLUtility.getDouble(weeklyPaperworkDTO.TaxPercent) + ",NonTaxableSale=" + SQLUtility.getDouble(weeklyPaperworkDTO.NonTaxableSale) + " where Id=" + SQLUtility.getDouble(weeklyPaperworkDTO.Id) + "";
db.ExecuteUpdate(query);
}
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;
}
//Get weekly paper list from local database list
public ArrayList GetWeeklyPaperListFromLocalDB(ArrayList storeList, DateTime weekStartDay)
{
ArrayList list = new ArrayList();
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
foreach (StoreDTO storeDTO in storeList)
{
if (!IsWeeklyPaperExistIntoLocalDb(storeDTO.Id, weekStartDay))
{
ArrayList oneStoreList = new ArrayList();
oneStoreList.Add(storeDTO);
AddWeeklyPaperWork(weekStartDay, oneStoreList, true);
}
DateTime currentWeekStartDate = ValidationUtility.GetActualWeekStartDate(DateTime.Now);
DateTime lastWeekStartDate = ValidationUtility.GetActualWeekStartDate(currentWeekStartDate.AddDays(-1));
if (weekStartDay.Date >= lastWeekStartDate.Date)
{
ArrayList oneStoreList = new ArrayList();
oneStoreList.Add(storeDTO);
AddWeeklyPaperWork(lastWeekStartDate, oneStoreList, false);
}
if (weekStartDay.Date.Equals(currentWeekStartDate.Date))
{
ArrayList oneStoreList = new ArrayList();
oneStoreList.Add(storeDTO);
AddWeeklyPaperWork(currentWeekStartDate, oneStoreList, false);
}
try
{
// string query = " select * from dbo.WeeklyPaperWork where StoreId=" + SQLUtility.getInteger(storeDTO.Id) + " and WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "'";
string query = "select s.StoreNumber, wpw.* from dbo.WeeklyPaperWork wpw,dbo.Store s where wpw.StoreId=s.Id and wpw.StoreId=" + SQLUtility.getInteger(storeDTO.Id) + " and wpw.WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "'";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int storeNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
double netSales = ValidationUtility.ToDouble(reader["NetSales"].ToString());
double giftCardSales = ValidationUtility.ToDouble(reader["GiftCardSales"].ToString());
double ar = ValidationUtility.ToDouble(reader["AR"].ToString());
double paidOuts = ValidationUtility.ToDouble(reader["PaidOuts"].ToString());
double pettyExpense = ValidationUtility.ToDouble(reader["PettyExpense"].ToString());
// double total = ValidationUtility.ToDouble(reader["Total"].ToString());
double total = ar + paidOuts + pettyExpense;
double storeTransfer = ValidationUtility.ToDouble(reader["StoreTransfer"].ToString());
double pfg1 = ValidationUtility.ToDouble(reader["PFG1"].ToString());
double pfg2 = ValidationUtility.ToDouble(reader["PFG2"].ToString());
// double totalPFG = ValidationUtility.ToDouble(reader["TotalPFG"].ToString());
double totalPFG = pfg1 + pfg2;
double coke = ValidationUtility.ToDouble(reader["Coke"].ToString());
// double totalCost = ValidationUtility.ToDouble(reader["TotalCost"].ToString());
double totalCost = storeTransfer + totalPFG + coke;
// double costPercent = ValidationUtility.ToDouble(reader["CostPercent"].ToString());
double costPercent = totalCost / netSales;
costPercent = ValidationUtility.IsNan(costPercent);
double laborCost = ValidationUtility.ToDouble(reader["LaborCost"].ToString());
// double laborCostPercent = ValidationUtility.ToDouble(reader["LaborCostPercent"].ToString());
double laborCostPercent = laborCost / netSales;
laborCostPercent = ValidationUtility.IsNan(laborCostPercent);
// double royalty = ValidationUtility.ToDouble(reader["Royalty"].ToString());
double royalty = netSales * 0.08;
// double fAF = ValidationUtility.ToDouble(reader["FAF"].ToString());
double fAF = netSales * 0.045;
// double costAndLaborCostPercent = ValidationUtility.ToDouble(reader["CostAndLaborCostPercent"].ToString());
double costAndLaborCostPercent = laborCostPercent + costPercent;
double adjTax = ValidationUtility.ToDouble(reader["AdjTax"].ToString());
double gcRedeem = ValidationUtility.ToDouble(reader["GCRedeem"].ToString());
// double gcDifference = ValidationUtility.ToDouble(reader["GCDifference"].ToString());
double gcDifference = gcRedeem - giftCardSales;
double taxPercent = ValidationUtility.ToDouble(reader["TaxPercent"].ToString());
// double actualSalesTaxper = ValidationUtility.ToDouble(reader["ActualSalesTaxper"].ToString());
double actualSalesTaxper = netSales * taxPercent;
// double differenceOfSalesTax = ValidationUtility.ToDouble(reader["DifferenceOfSalesTax"].ToString());
double differenceOfSalesTax = actualSalesTaxper - adjTax;
double nonTaxableSale = ValidationUtility.ToDouble(reader["NonTaxableSale"].ToString());
double weekStartDate = ValidationUtility.ToDouble(reader["WeekStartDate"].ToString());
WeeklyPaperworkDTO weeklyPaperworkDTO = new WeeklyPaperworkDTO
{
StoreNumber = storeNumber,
Id = id,
StoreId = sId,
NetSales = netSales,
GiftCardSales = giftCardSales,
Ar = ar,
PaidOuts = paidOuts,
PettyExpense = pettyExpense,
Total = total,
StoreTransfer = storeTransfer,
Pfg1 = pfg1,
Pfg2 = pfg2,
TotalPFG = totalPFG,
Coke = coke,
TotalCost = totalCost,
CostPercent = costPercent,
LaborCost = laborCost,
LaborCostPercent = laborCostPercent,
Royalty = royalty,
Faf = fAF,
CostAndLaborCostPercent = costAndLaborCostPercent,
AdjTax = adjTax,
GcRedeem = gcRedeem,
GcDifference = gcDifference,
WeekStartDate = weekStartDay,
TaxPercent = taxPercent,
ActualSalesTaxper = actualSalesTaxper,
DifferenceOfSalesTax = differenceOfSalesTax,
NonTaxableSale = nonTaxableSale
};
list.Add(weeklyPaperworkDTO);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetWeeklyPaperListFromLocalDB Method ", ex);
}
finally
{
db.CloseConnection(conn);
}
}
return list;
}
public ArrayList GetTotal(ArrayList list)
{
ArrayList totalList = new ArrayList();
double netSaleSum = 0.0;
double giftCardSum = 0.0;
double arSum = 0.0;
double paidOutsSum = 0.0;
double pettyExpenseSum = 0.0;
double totalSum = 0.0;
double storeTransferSum = 0.0;
double pfg1Sum = 0.0;
double pfg2Sum = 0.0;
double totalPFGSum = 0.0;
double cokeSum = 0.0;
double totalCostSum = 0.0;
double laborCostSum = 0.0;
double royaltySum = 0.0;
double fafSum = 0.0;
double adjTaxSum = 0.0;
double gcRedeemSum = 0.0;
double gcDifferenceSum = 0.0;
double actualSalesTaxperSum = 0.0;
double differenceOfSalesTaxSum = 0.0;
double nonTaxableSaleSum = 0.0;
foreach (WeeklyPaperworkDTO dto in list)
{
netSaleSum = netSaleSum + dto.NetSales;
giftCardSum = giftCardSum + dto.GiftCardSales;
arSum = arSum + dto.Ar;
paidOutsSum = paidOutsSum + dto.PaidOuts;
pettyExpenseSum = pettyExpenseSum + dto.PettyExpense;
totalSum = totalSum + dto.Total;
storeTransferSum = storeTransferSum + dto.StoreTransfer;
pfg1Sum = pfg1Sum + dto.Pfg1;
totalPFGSum = totalPFGSum + dto.TotalPFG;
pfg2Sum = pfg2Sum + dto.Pfg2;
cokeSum = cokeSum + dto.Coke;
totalCostSum = totalCostSum + dto.TotalCost;
laborCostSum = laborCostSum + dto.LaborCost;
royaltySum = royaltySum + dto.Royalty;
fafSum = fafSum + dto.Faf;
adjTaxSum = adjTaxSum + dto.AdjTax;
gcRedeemSum = gcRedeemSum + dto.GcRedeem;
gcDifferenceSum = gcDifferenceSum + dto.GcDifference;
actualSalesTaxperSum = actualSalesTaxperSum + dto.ActualSalesTaxper;
differenceOfSalesTaxSum = differenceOfSalesTaxSum + dto.DifferenceOfSalesTax;
nonTaxableSaleSum = nonTaxableSaleSum + dto.NonTaxableSale;
}
double costPercentSum = totalCostSum / netSaleSum;
costPercentSum = ValidationUtility.IsNan(costPercentSum);
double laborCostPercentSum = laborCostSum / netSaleSum;
laborCostPercentSum = ValidationUtility.IsNan(laborCostPercentSum);
double costAndLaborCostPercentSum = laborCostPercentSum + costPercentSum;
WeeklyPaperworkDTO totalDto = new WeeklyPaperworkDTO
{
NetSales = netSaleSum,
GiftCardSales = giftCardSum,
Ar = arSum,
PaidOuts = paidOutsSum,
PettyExpense = pettyExpenseSum,
Total = totalSum,
StoreTransfer = storeTransferSum,
Pfg1 = pfg1Sum,
Pfg2 = pfg2Sum,
TotalPFG = totalPFGSum,
Coke = cokeSum,
TotalCost = totalCostSum,
CostPercent = costPercentSum,
LaborCost = laborCostSum,
LaborCostPercent = laborCostPercentSum,
Royalty = royaltySum,
Faf = fafSum,
CostAndLaborCostPercent = costAndLaborCostPercentSum,
AdjTax = adjTaxSum,
GcRedeem = gcRedeemSum,
GcDifference = gcDifferenceSum,
TaxPercent = 8.25,
ActualSalesTaxper = actualSalesTaxperSum,
DifferenceOfSalesTax = differenceOfSalesTaxSum,
NonTaxableSale = nonTaxableSaleSum
};
totalList.Add(totalDto);
// Avarage
int numberOfRow = list.Count;
double netSalesAvg = ValidationUtility.IsNan(netSaleSum / numberOfRow);
double totalCostSumAvg = ValidationUtility.IsNan(totalCostSum / numberOfRow);
double costPercentAvg = ValidationUtility.IsNan(totalCostSumAvg / netSalesAvg);
double laborCostAvg = ValidationUtility.IsNan(laborCostSum / numberOfRow);
double laborCostPercentAvg = ValidationUtility.IsNan(laborCostAvg / netSalesAvg);
double costAndLaborCostPercentAvg = laborCostAvg + costPercentAvg;
totalDto = new WeeklyPaperworkDTO { NetSales = netSalesAvg, TotalCost = totalCostSumAvg, CostPercent = costPercentAvg, LaborCost = laborCostAvg, LaborCostPercent = laborCostPercentAvg, CostAndLaborCostPercent = costAndLaborCostPercentAvg };
totalList.Add(totalDto);
return totalList;
}
public JsonResult UpdatePaperworkList(string id, string ar, string paidOuts, string pettyExpense, string storeTransfer, string pfg1, string pfg2, string coke, string laborCost, string taxPercent, string nonTaxableSale)
{
WeeklyPaperworkModel weeklyPaperworkModel = new WeeklyPaperworkModel();
GoalsModel goalsModel = new GoalsModel();
string error = "";
string msg = "";
try
{
WeeklyPaperworkDTO weeklyPaperworkDTO = new WeeklyPaperworkDTO();
weeklyPaperworkDTO.Id = ValidationUtility.ToInteger(id);
weeklyPaperworkDTO.Ar = ValidationUtility.ToDouble(ar);
weeklyPaperworkDTO.PaidOuts = ValidationUtility.ToDouble(paidOuts);
weeklyPaperworkDTO.PettyExpense = ValidationUtility.ToDouble(pettyExpense);
weeklyPaperworkDTO.StoreTransfer = ValidationUtility.ToDouble(storeTransfer);
weeklyPaperworkDTO.Pfg1 = ValidationUtility.ToDouble(pfg1);
weeklyPaperworkDTO.Pfg2 = ValidationUtility.ToDouble(pfg2);
weeklyPaperworkDTO.Coke = ValidationUtility.ToDouble(coke);
weeklyPaperworkDTO.LaborCost = ValidationUtility.ToDouble(laborCost);
weeklyPaperworkDTO.TaxPercent = ValidationUtility.ToDouble(taxPercent);
weeklyPaperworkDTO.NonTaxableSale = ValidationUtility.ToDouble(nonTaxableSale);
weeklyPaperworkModel.UpdateWeeklyPaperSales(weeklyPaperworkDTO);
weeklyPaperworkDTO = weeklyPaperworkModel.GetWeeklyPaperWorkOfWeek(ValidationUtility.ToInteger(id));
ArrayList goalStoreList = new ArrayList();
// ArrayList storeList = ValidationUtility.GetStoreList(true);
ArrayList storeList = ValidationUtility.GetActiveStoreList(true);
foreach (StoreDTO storDTO in storeList)
{
if (storDTO.Id == weeklyPaperworkDTO.StoreId)
{
goalStoreList.Add(storDTO);
break;
}
}
if (!goalsModel.IsGoalsYTDCurrentYearRecordExist(weeklyPaperworkDTO.WeekStartDate, goalStoreList))
{
ArrayList currentYTDList = goalsModel.GetCurrentWeekGoalsYTD(weeklyPaperworkDTO.WeekStartDate, goalStoreList);
goalsModel.AddGoalYTD(currentYTDList);
}
else
{
goalsModel.UpdateCurrentYTD(weeklyPaperworkDTO);
}
error = "Success";
msg = "Update Successfully";
}
catch (Exception ex)
{
error = "Error";
msg = " Not Update ";
log.Error("Exception in UpdatePaperworkList ", ex);
}
ResponseDTO resDTO = new ResponseDTO { Error = error, Message = msg };
return Json(resDTO, JsonRequestBehavior.AllowGet);
}
public void UpdateCurrentYTD(WeeklyPaperworkDTO dto)
{
DataBaseUtility db = new DataBaseUtility();
string query = " update dbo.GoalsYTD set FoodCost=" + SQLUtility.getDouble(dto.CostPercent) + ",LaborCost= " + SQLUtility.getDouble(dto.LaborCostPercent) + " ,FoodLaborCost =" + SQLUtility.getDouble(dto.CostPercent + dto.LaborCostPercent) + " "
+ " where WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(dto.WeekStartDate) + "' and StoreId=" + SQLUtility.getInteger(dto.StoreId) + " ";
db.ExecuteUpdate(query);
}
