//
// GET: /Api/
public ActionResult Index()
{
ArrayList al = new ArrayList();
Dictionary<int, string> connectionString = (Dictionary<int, string>)Session["ConnectionString"];
string storeNameConnectionString = "";
if (connectionString.ContainsKey(1))
{
storeNameConnectionString = connectionString[1];
}
SqlConnection con = new SqlConnection(storeNameConnectionString);
StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(storeNameConnectionString);
try
{
string query = " SELECT sc.name +'.'+ ta.name TableName ,SUM(pa.rows) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN " + " sys.schemas sc ON ta.schema_id = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) GROUP BY sc.name,ta.name ORDER BY sc.name,ta.name ";
DataTable tableName = db.FetchData(query);
// String query = "select count(*) from ";
foreach (DataRow rd in tableName.Rows)
{
string tName = (string)rd[0];
int tCt = Convert.ToInt32(rd[1].ToString());
if (tCt > 0)
{
al.Add(tName);
}
}
}
catch (Exception ex)
{
}
finally
{
db.CloseConnection(con);
}
if (al != null && al.Count > 0)
{
return View(al);
}
else
{
ViewBag.Message = " Exception occured in fetching data ";
return View();
}
}
public ActionResult Data(string id)
{
string tName = id.Replace("-", ".");
//String id = "dbo.OrderPayments";
DataTable tableName=null;
Dictionary<int, string> connectionString = (Dictionary<int, string>)Session["ConnectionString"];
string storeNameConnectionString = "";
if (connectionString.ContainsKey(1))
{
storeNameConnectionString = connectionString[1];
}
SqlConnection con = new SqlConnection(storeNameConnectionString);
StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(storeNameConnectionString);
try
{
string query = "SELECT * from " + tName;
tableName = db.FetchData(query);
}
catch (Exception ex)
{
}
finally
{
db.CloseConnection(con);
}
return View(tableName);
}
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 ;
}
// This method is use for get Employee Information from client store
public ArrayList GetEmpInfo(string connectionString, DateTime bussinessDate)
{
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
SqlConnection con = null;
ArrayList list = new ArrayList();
try
{
//string query = " select e.EmployeeId, er.EmployeeRoleId , e.FirstName,e.LastName from dbo.Employees e ,dbo.EmployeeRoles er "
// + " where er.EmployeeRoleId = e.EmployeeId and (er.EmployeeRoleId=5 or er.EmployeeRoleId=6) and e.EmployeeId "
// + " in (select EmployeeId from dbo.UserClocking where OpeningInformationId in(select OpeningInformationId from dbo.OpeningInformation where BusinessDate='" + bussinessDate.ToString("yyyy/MM/dd") + "'))";
string query = "select e.EmployeeId,ere.EmployeeRole_Id,e.FirstName,e.LastName from dbo.Employees e ,dbo.EmployeeRoleEmployees ere "
+ " where ere.Employee_Id = e.EmployeeId and ere.EmployeeRole_Id in (5,6) and ere.Employee_Id "
+ " in (select EmployeeId from dbo.UserClocking where OpeningInformationId in(select OpeningInformationId from dbo.OpeningInformation where BusinessDate='" + bussinessDate.ToString("yyyy/MM/dd") + "'))";
con = remoteDataBase.OpenConnection();
SqlCommand comm = remoteDataBase.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
EmpBonusDTO dto = new EmpBonusDTO();
dto.EmployeeId = ValidationUtility.ToInteger(reader[0].ToString());
dto.EmployeeRoleId = ValidationUtility.ToInteger(reader[1].ToString());
dto.FirstName = reader[2].ToString();
dto.LastName = reader[3].ToString();
dto.BusinessDate = bussinessDate;
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetPerDaySalesAmount method ", ex);
}
finally
{
remoteDataBase.CloseConnection(con);
}
return list;
}
public double GetPerDaySalesAmount(string connectionString, DateTime bussinessDate)
{
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
SqlConnection con = null;
double perDaySaleAmount = 0;
try
{
string query = " select SUM(PaymentAmount) as amount from dbo.OrderPayments where "
+ " OrderHeaderId in ( select OrderHeaderId from dbo.OrderHeaders where OpeningInformationId in "
+ " (select OpeningInformationId from OpeningInformation where BusinessDate = '" + bussinessDate.ToString("yyyy/MM/dd") + "')) ";
con = remoteDataBase.OpenConnection();
SqlCommand comm = remoteDataBase.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
perDaySaleAmount = ValidationUtility.ToDouble(reader[0].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetPerDaySalesAmount method ", ex);
}
finally
{
remoteDataBase.CloseConnection(con);
}
return perDaySaleAmount;
}
public ArrayList GetData(string storeNameConnectionString)
{
ArrayList list = new ArrayList();
StoreRemoteDataBaseUitility srdbu = new StoreRemoteDataBaseUitility(storeNameConnectionString);
SqlConnection con = null;
try
{
StringBuilder itemName = new StringBuilder();
//string query = "select op.PaymentAmount as LastSaleAmount , oh.InsertedDate as LastSaleTime ,oh.TaxAmount as Tax from dbo.OrderHeaders oh ,dbo.OrderPayments op "
// + " where op.OrderHeaderId = oh.OrderHeaderId and oh.InsertedDate in (select MAX(InsertedDate) from OrderHeaders) ";
String query = " select mi.MenuItemName as ItemName,op.PaymentAmount as LastSaleAmount,oh.InsertedDate as LastSaleTime,oh.TaxAmount as Tax from Staging.MenuItems mi,dbo.OrderPayments op ,dbo.OrderHeaders oh where MenuItemId in ( select oip.MenuItemId from dbo.OrderHeaders oh,dbo.OrderItemsPaid oip where oh.OrderHeaderId =oip.OrderHeaderId and oh.InsertedDate in (select MAX(InsertedDate) from OrderHeaders) ) "
+ " and op.OrderHeaderId = oh.OrderHeaderId and oh.InsertedDate in (select MAX(InsertedDate) from OrderHeaders) ";
con = srdbu.OpenConnection();
SqlCommand comm = srdbu.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
double LastSaleAmountWithTax = 0.0;
double tax = 0.0;
DateTime lastSaledate=new DateTime();
while (reader.Read())
{
itemName.Append(reader["ItemName"].ToString() +" "+"<br />"+" ");
// itemName.Append(",");
//list.Add(reader["ItemName"].ToString());
// double withTax = ValidationUtility.ToDouble(reader["LastSaleAmount"]);
LastSaleAmountWithTax = ValidationUtility.ToDouble(reader["LastSaleAmount"].ToString());
tax = ValidationUtility.ToDouble(reader["Tax"].ToString());
lastSaledate = ValidationUtility.ToDate(reader["LastSaleTime"].ToString());
}
list.Add(itemName.ToString().Remove(itemName.ToString().Trim().Length-1));
list.Add(LastSaleAmountWithTax);
list.Add(tax);
list.Add(lastSaledate);
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetData Method ", ex);
}
finally
{
srdbu.CloseConnection(con);
}
return list;
}
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;
}
// This method is use for get Imployee info from server
public ArrayList GetEmployeeInfo(string connectionString)
{
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
SqlConnection conn = null;
ArrayList list = new ArrayList();
try
{
//string query = "select e.EmployeeId ,e.FirstName,e.MiddleName,e.LastName from dbo.Employees e, dbo.EmployeeRoleEmployees ere where ere.Employee_Id = e.EmployeeId and ere.EmployeeRole_Id in(5,6)";
//string query = "select e.EmployeeId ,e.FirstName,e.MiddleName,e.LastName from dbo.Employees e, dbo.EmployeeRoleEmployees ere where ere.Employee_Id = e.EmployeeId and ere.EmployeeRole_Id in(1,2)";
string query = "select e.EmployeeId ,e.FirstName,e.MiddleName,e.LastName from dbo.Employees e, dbo.EmployeeRoleEmployees ere where ere.Employee_Id = e.EmployeeId and ere.EmployeeRole_Id in(select EmployeeRoleId from dbo.EmployeeRoles)";
conn = remoteDataBase.OpenConnection();
SqlCommand comm = remoteDataBase.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
EmployeeInfoDTO employeeInfoDTO = new EmployeeInfoDTO();
employeeInfoDTO.EmpId = ValidationUtility.ToInteger(reader[0].ToString());
employeeInfoDTO.EmpFirstName = reader[1].ToString();
employeeInfoDTO.EmpMiddleName = reader[2].ToString();
employeeInfoDTO.EmpLastName = reader[3].ToString();
list.Add(employeeInfoDTO);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeInfo Method ", ex);
}
finally
{
remoteDataBase.CloseConnection(conn);
}
return list;
}
public double GetSubWaySale(string query, string connetionStringName)
{
StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);
SqlConnection con = null;
double ammount = 0;
try
{
con = db.OpenConnection();
SqlCommand comd = db.getSQLCommand(query, con);
SqlDataReader reader = comd.ExecuteReader();
if (reader.Read())
{
if (!ValidationUtility.IsNull(reader["ammount"].ToString()))
{
ammount = Convert.ToDouble(reader["ammount"]);
}
}
reader.Close();
comd.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetSubWaySale Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return ammount;
}
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;
}
public double GetYearSaleOfStore(int storeId, int year, string storeNameConnectionString)
{
DateTime startDateOfYear = new DateTime(year, 1, 1);
DateTime endDateOfYear = new DateTime(year, 12, 31);
double totalAmount = 0;
StoreRemoteDataBaseUitility srdbu = new StoreRemoteDataBaseUitility(storeNameConnectionString);
SqlConnection con = null;
try
{
string query = " select sum(ClosingGrandTotal-OpeningGrandTotal) as ammount from dbo.OpeningInformation "
+ " where BusinessDate >= '" + ValidationUtility.FormateDateYYYYMMDD(startDateOfYear) + "' and BusinessDate <= '" + ValidationUtility.FormateDateYYYYMMDD(endDateOfYear) + "' ";
con = srdbu.OpenConnection();
SqlCommand comm = srdbu.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
totalAmount = ValidationUtility.ToDouble(reader["ammount"].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetYearSaleOfStore Method ", ex);
}
finally
{
srdbu.CloseConnection(con);
}
return totalAmount;
}
//Get Employee Clocking Time
public ArrayList GetEmpClockingTime(EmployeeInfoDTO employeeInfoDTO, DateTime selectDate)
{
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(employeeInfoDTO.ConnectionString);
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);
SqlConnection conn = null;
ArrayList list = new ArrayList();
try
{
conn = remoteDataBase.OpenConnection();
for (int i = 0; i < 7; i++)
{
string query = "select EmployeeId,ClockFunctionTypeId,ClockingTime,MinutesWorked from dbo.UserClocking where ClockingTime "
+ "between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(i)) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(i + 1)) + "' and EmployeeId = " + SQLUtility.getInteger(employeeInfoDTO.EmpId) + " ";
SqlCommand comm = remoteDataBase.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
EmployeeClockingDTO employeeClockingDTO = null;
while (reader.Read())
{
employeeClockingDTO = new EmployeeClockingDTO();
employeeClockingDTO.EmployeeId = ValidationUtility.ToInteger(reader[0].ToString());
employeeClockingDTO.ClockFunctionTypeId = ValidationUtility.ToInteger(reader[1].ToString());
employeeClockingDTO.ClockingTime = ValidationUtility.ToDate(reader[2].ToString());
employeeClockingDTO.MinutesWorked = ValidationUtility.ToInteger(reader[3].ToString());
employeeClockingDTO.BusinessDate = weekStartDate.AddDays(i);
list.Add(employeeClockingDTO);
}
//if (EmployeeClockingDTO==null)
//{
// EmployeeClockingDTO = new EmployeeClockingDTO();
// EmployeeClockingDTO.BusinessDate = weekStartDate.AddDays(i);
// list.Add(EmployeeClockingDTO);
//}
reader.Close();
comm.Dispose();
}
}
catch (Exception ex)
{
log.Error("Exception in GetEmpClockingTime Method ", ex);
}
finally
{
remoteDataBase.CloseConnection(conn);
}
return list;
}
public ArrayList GetAllOpeningInformationId(string connectionString)
{
ArrayList idList = new ArrayList();
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
SqlConnection con = null;
try
{
string query = "select OpeningInformationId from dbo.OpeningInformation order by BusinessDate ";
con = remoteDataBase.OpenConnection();
SqlCommand comm = remoteDataBase.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader[0].ToString());
idList.Add(id);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetAllOpeningInformationId method ", ex);
}
finally
{
remoteDataBase.CloseConnection(con);
}
return idList;
}
// Employee Clocking Information of each business day for every night
public ArrayList GetEmployeClocking(int empId, DateTime businessDate, string connetionString)
{
ArrayList list = new ArrayList();
StoreRemoteDataBaseUitility remoteDB = new StoreRemoteDataBaseUitility(connetionString);
SqlConnection con = null;
try
{
string query = "select EmployeeId,ClockFunctionTypeId,ClockingTime,MinutesWorked from dbo.UserClocking where ClockingTime "
+ "between '" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(businessDate.AddDays(1)) + "' and EmployeeId = " + SQLUtility.getInteger(empId) + " ";
con = remoteDB.OpenConnection();
SqlCommand comm = remoteDB.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
EmployeeClockingDTO employeeClockingDTO = null;
while (reader.Read())
{
employeeClockingDTO = new EmployeeClockingDTO();
employeeClockingDTO.EmployeeId = ValidationUtility.ToInteger(reader[0].ToString());
employeeClockingDTO.ClockFunctionTypeId = ValidationUtility.ToInteger(reader[1].ToString());
employeeClockingDTO.ClockingTime = ValidationUtility.ToDateTime(reader.GetSqlDateTime(2));
// employeeClockingDTO.ClockingTime = ValidationUtility.ToDate(reader[2].ToString());
employeeClockingDTO.MinutesWorked = ValidationUtility.ToInteger(reader[3].ToString());
employeeClockingDTO.BusinessDate = businessDate;
list.Add(employeeClockingDTO);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeClocking Method ", ex);
}
finally
{
remoteDB.CloseConnection(con);
}
return list;
}
// Get All Previous Sales of Day
public DataTable GetPreviousSales(string connectionString, int opId)
{
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
string query = " select (ClosingGrandTotal - OpeningGrandTotal) as PerDaySale , BusinessDate ,OpeningInformationId ,datename(dw,BusinessDate) as WeekOfDay from OpeningInformation where OpeningInformationId = " + SQLUtility.getInteger(opId) + " ";
DataTable dt = remoteDataBase.FetchData(query);
return dt;
}
public int GetCurrentDateOpeningInfoId(DateTime businessDate, string connectionString)
{
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
SqlConnection con = null;
int opId = 0;
try
{
string query = "select OpeningInformationId from OpeningInformation where BusinessDate = '" + businessDate.ToString("yyyy/MM/dd") + "' ";
con = remoteDataBase.OpenConnection();
SqlCommand comm = remoteDataBase.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
opId = ValidationUtility.ToInteger(reader[0].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetCurrentDateOpeningInfoId method ", ex);
}
finally
{
remoteDataBase.CloseConnection(con);
}
return opId;
}
// 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;
}
// Get Record sales of year
public DataTable GetWeekDayRecord(string connectionString, string weekDay, DateTime selectDate)
{
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
string query = "select top 1 BusinessDate, datename(dw,BusinessDate) as WeekOfDay, (ClosingGrandTotal - OpeningGrandTotal) as PerDaySale "
+ " from dbo.OpeningInformation where datename(dw,BusinessDate) = " + SQLUtility.getString(weekDay) + " "
+ " and BusinessDate > DateADD(YEAR, -1, '" + selectDate.ToString("yyyy/MM/dd") + "') and BusinessDate <= '" + selectDate.ToString("yyyy/MM/dd") + "' order by (ClosingGrandTotal - OpeningGrandTotal) desc";
DataTable dt = remoteDataBase.FetchDataWithException(query);
return dt;
}
//public double GetTotalAmountOfDayWithOutTax(string storeNameConnectionString)
//{
// double amount = 0;
// StoreRemoteDataBaseUitility srdbu = new StoreRemoteDataBaseUitility(storeNameConnectionString);
// SqlConnection con = null;
// try
// {
// string query = " select sum(AmtTotalSales) as amount from Report.OrdersSummary where OpeningInformationId in (select OpeningInformationId from dbo.OpeningInformation where BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(DateTime.Now) + "') ";
// con = srdbu.OpenConnection();
// SqlCommand comm = srdbu.getSQLCommand(query, con);
// SqlDataReader reader = comm.ExecuteReader();
// if (reader.Read())
// {
// amount = ValidationUtility.ToDouble(reader["amount"].ToString());
// }
// reader.Close();
// comm.Dispose();
// }
// catch (Exception ex)
// {
// log.Error(" Exception in GetTotalAmountOfDayWithOutTax Method ", ex);
// }
// finally
// {
// srdbu.CloseConnection(con);
// }
// return amount;
//}
public double GetTotalAmountWithTax(string storeNameConnectionString, DateTime businessDate )
{
double amount = 0;
StoreRemoteDataBaseUitility srdbu = new StoreRemoteDataBaseUitility(storeNameConnectionString);
SqlConnection con = null;
try
{
//get adjusted proceeds
//string query = " select SUM(PaymentAmount) as amount from dbo.OrderPayments where "
// + " OrderHeaderId in ( select OrderHeaderId from dbo.OrderHeaders where OpeningInformationId in (select OpeningInformationId from dbo.OpeningInformation where BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "')) ";
//Get Total Subway Sales
string query = " select AmtTotalSales as amount from Report.OpeningInformationsSummary where OpeningInformationId = (select OpeningInformationId from dbo.OpeningInformation where BusinessDate = '" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' ) ";
con = srdbu.OpenConnection();
SqlCommand comm = srdbu.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
amount = ValidationUtility.ToDouble(reader["amount"].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetTotalAmountWithTax Method ", ex);
}
finally
{
srdbu.CloseConnection(con);
}
return amount;
}