/* *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); }