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