public DataTable GetSalesItemizationData(DateTime dateTime, string connetionStringName)
{
StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);
// This procedure set 7 day data
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
DateTime weekEndDate = weekStartDate.AddDays(6);
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@StartDate",weekStartDate ),
new SqlParameter("@EndDate",weekEndDate ),};
db.ExecuteSPDataTable("dbo.CalculateCashControlData", sqlParams);
// This procedure is use for get SalesItemization data
SqlParameter[] salesItemizationSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable salesItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_SalesItemization_SelectStoredProcedure", salesItemizationSqlParams);
return salesItemizationDataTable;
// Trhow ;
}
// Get Deposit Data from server
public DataTable GetEachStoreDepositAmount(string connectionString, DateTime selectDate)
{
StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connectionString);
// This procedure set 7 day data
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);
DateTime weekEndDate = weekStartDate.AddDays(6);
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@StartDate",weekStartDate ),
new SqlParameter("@EndDate",weekEndDate ),};
db.ExecuteSPDataTable("dbo.CalculateCashControlData", sqlParams);
// This procedure is use for get CashControl data
SqlParameter[] controlSheetSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable cashControlDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_CashControl_SelectStoredProcedure", controlSheetSqlParams);
return cashControlDataTable;
}
public ArrayList GetBreadControlData(DateTime dateTime, string connetionStringName)
{
ArrayList list = new ArrayList();
StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);
// This procedure set 7 day data
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
DateTime weekEndDate = weekStartDate.AddDays(6);
// This procedure is use for get BreadControl data
SqlParameter[] breadControlSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable breadControlDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_BreadControl_SelectStoredProcedure", breadControlSqlParams);
DataTable newBreadControlDataTable = new DataTable();
newBreadControlDataTable = db.GenerateTransposedTable(breadControlDataTable);
for (int i = 0; i < newBreadControlDataTable.Rows.Count; i++)
{
string businessDate = newBreadControlDataTable.Rows[i]["BusinessDate"].ToString();
double day1 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(0).Date.ToString()].ToString());
double day2 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(1).Date.ToString()].ToString());
double day3 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(2).Date.ToString()].ToString());
double day4 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(3).Date.ToString()].ToString());
double day5 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(4).Date.ToString()].ToString());
double day6 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(5).Date.ToString()].ToString());
double day7 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(6).Date.ToString()].ToString());
double weekTotal = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekEndDate.AddYears(1).Date.ToString()].ToString());
SalesItemizationReportDTO salesItemizationReportDTO = new SalesItemizationReportDTO { ProductCategoryDescription = businessDate, Day1 = day1, Day2 = day2, Day3 = day3, Day4 = day4, Day5 = day5, Day6 = day6, Day7 = day7, WeekTotal = weekTotal };
list.Add(salesItemizationReportDTO);
}
return list;
}
public ArrayList GetUnitsItemizationData(DateTime dateTime, string connetionStringName)
{
ArrayList list = new ArrayList();
StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);
try
{
// This procedure set 7 day data
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
DateTime weekEndDate = weekStartDate.AddDays(6);
// This procedure is use for get UnitsItemization data
SqlParameter[] unitsItemizationSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable UnitsItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_UnitsItemization_SelectStoredProcedure", unitsItemizationSqlParams);
for (int i = 0; i < UnitsItemizationDataTable.Rows.Count; i++)
{
string productCategoryDescription = UnitsItemizationDataTable.Rows[i]["ProductCategoryDescription"].ToString();
double day1 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day1"].ToString());
double day2 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day2"].ToString());
double day3 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day3"].ToString());
double day4 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day4"].ToString());
double day5 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day5"].ToString());
double day6 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day6"].ToString());
double day7 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day7"].ToString());
double weekTotal = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["WeekTotal"].ToString());
SalesItemizationReportDTO salesItemizationReportDTO = new SalesItemizationReportDTO { ProductCategoryDescription = productCategoryDescription, Day1 = day1, Day2 = day2, Day3 = day3, Day4 = day4, Day5 = day5, Day6 = day6, Day7 = day7, WeekTotal = weekTotal };
list.Add(salesItemizationReportDTO);
}
}
catch (Exception ex)
{
log.Error("Exception in GetUnitsItemizationData Method", ex);
}
return list;
}
//Get Total FooT Long For Goal tab
public ArrayList GetSalesItemizationFoodLong(DateTime dateTime, string connetionStringName)
{
ArrayList list = new ArrayList();
StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);
try
{
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
DateTime weekEndDate = weekStartDate.AddDays(6);
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@StartDate",weekStartDate ),
new SqlParameter("@EndDate",weekEndDate ),};
db.ExecuteSPDataTable("dbo.CalculateCashControlData", sqlParams);
// This procedure is use for get CashControl data
SqlParameter[] salesItemizationSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable salesItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_SalesItemization_SelectStoredProcedure", salesItemizationSqlParams);
list.Add(ValidationUtility.ToDouble(salesItemizationDataTable.Rows[0]["WeekTotal"].ToString()));
// This procedure is use for get UnitsItemization data
SqlParameter[] unitsItemizationSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable UnitsItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_UnitsItemization_SelectStoredProcedure", unitsItemizationSqlParams);
list.Add(ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[10]["WeekTotal"].ToString()));
list.Add(ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[13]["WeekTotal"].ToString()));
list.Add(ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[14]["WeekTotal"].ToString()));
list.Add(ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[15]["WeekTotal"].ToString()));
// This procedure is use for get Labor data
SqlParameter[] laborSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable laborDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_Labor_SelectStoredProcedure", laborSqlParams);
list.Add(ValidationUtility.ToDouble(laborDataTable.Rows[7]["Productivity"].ToString()));
list.Add(ValidationUtility.ToDouble(laborDataTable.Rows[7]["HoursWorked"].ToString()));
}
catch (Exception ex)
{
log.Error(" Exception in GetSalesItemizationFoodLong Method ", ex);
}
return list;
}
public DataSet GetControlSheet(DateTime dateTime, string connetionStringName)
{
StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);
// This procedure set 7 day data
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
DateTime weekEndDate = weekStartDate.AddDays(6);
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@StartDate",weekStartDate ),
new SqlParameter("@EndDate",weekEndDate ),};
db.ExecuteSPDataTable("dbo.CalculateCashControlData", sqlParams);
// This procedure is use for get CashControl data
SqlParameter[] controlSheetSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable cashControlDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_CashControl_SelectStoredProcedure", controlSheetSqlParams);
// This procedure is use for get SalesItemization data
SqlParameter[] salesItemizationSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable salesItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_SalesItemization_SelectStoredProcedure", salesItemizationSqlParams);
// This procedure is use for get BreadControl data
SqlParameter[] breadControlSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable breadControlDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_BreadControl_SelectStoredProcedure", breadControlSqlParams);
// This procedure is use for get UnitsItemization data
SqlParameter[] unitsItemizationSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable UnitsItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_UnitsItemization_SelectStoredProcedure", unitsItemizationSqlParams);
// This procedure is use for get Labor data
SqlParameter[] laborSqlParams = new SqlParameter[] {
new SqlParameter("@WeekEndingDate",weekEndDate ),};
DataTable laborDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_Labor_SelectStoredProcedure", laborSqlParams);
ArrayList list = new ArrayList();
for (int i = 0; i <= 6; i++)
{
DateTime getDateRecord = weekStartDate.AddDays(i);
//string innerQuery = " select sum(AmtSubwaySales) as ammount from Report.OrdersSummary os, dbo.OpeningInformation oi where os.OpeningInformationId = oi.OpeningInformationId and oi.BusinessDate = '" + getDateRecord.ToString("yyyy/MM/dd") + "' ";
string innerQuery = " select AmtTotalSales from Report.OpeningInformationsSummary where OpeningInformationId = (select OpeningInformationId from dbo.OpeningInformation where BusinessDate = '" + SQLUtility.FormateDateYYYYMMDD(getDateRecord) + "' ) ";
list.Add(db.GetSubWaySale(innerQuery));
}
ArrayList list1 = new ArrayList();
for (int i = 0; i <= 6; i++)
{
DateTime getDateRecord = weekStartDate.AddDays(i);
string innerQuery = " select sum(AmtAdjustedUnitSales) as ammount from Report.OrdersSummary os, dbo.OpeningInformation oi where os.OpeningInformationId = oi.OpeningInformationId and oi.BusinessDate = '" + getDateRecord.ToString("yyyy/MM/dd") + "' ";
list1.Add(db.GetSubWaySale(innerQuery));
}
ArrayList list2 = new ArrayList();
for (int i = 0; i <= 6; i++)
{
DateTime getDateRecord = weekStartDate.AddDays(i);
string innerQuery = " select sum(AmtDrinkSales) as ammount from Report.OrdersSummary os, dbo.OpeningInformation oi where os.OpeningInformationId = oi.OpeningInformationId and oi.BusinessDate = '" + getDateRecord.ToString("yyyy/MM/dd") + "' ";
list2.Add(db.GetSubWaySale(innerQuery));
}
DataTable totalSubSaleTable = ConvertListToDataTable(list);
DataTable totalUnitSaleTable = ConvertListToUnitTable(list1);
DataTable totalDrinkSaleTable = ConvertListToDrinkTable(list2);
DataSet dataSet = new DataSet();
dataSet.Tables.Add(cashControlDataTable);
dataSet.Tables.Add(salesItemizationDataTable);
dataSet.Tables.Add(breadControlDataTable);
dataSet.Tables.Add(UnitsItemizationDataTable);
dataSet.Tables.Add(laborDataTable);
dataSet.Tables.Add(totalSubSaleTable);
dataSet.Tables.Add(totalUnitSaleTable);
dataSet.Tables.Add(totalDrinkSaleTable);
dataSet.Tables.Add(HeaderInformation(dateTime));
return dataSet;
}
