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