public void GetDBBackup(string backupDBName, string backupPath) { try { DataBaseUtility db = new DataBaseUtility(); SqlCommand cm = new SqlCommand(); string s = DateTime.Now.ToString("MMM/dd/yyyy"); SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@DBNAME", backupDBName), new SqlParameter("@PATH", backupPath), new SqlParameter("@BACKUPTYPE", 1), new SqlParameter("@MSG", "Genrate Backup"), new SqlParameter("@BACKUPFILENAME", s.Replace("/", "_") )}; db.ExecuteSP("DATABASE_BACKUP", sqlParams); log.Info("Backup created success fully with name "+ s.Replace("/", "_")); } catch (Exception ex) { log.Info("Exception in GetDBBackup ",ex); } }
// Add Food Order this order add by only manager public void AddFoodOrder(FoodOrderDTO dto) { DataBaseUtility db = new DataBaseUtility(); string query = "INSERT INTO [dbo].[FoodOrder]([StoreId],[OrderDateTime],[CreatedDateTime],[LastUpdateDateTime])VALUES ( " + " " + SQLUtility.getInteger(dto.StoreId) + " , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.FoodOrderDate) + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.CreatedDateTime) + "' ,'" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.LastUpdateDateTime) + "' ) "; db.ExecuteUpdate(query); }
public void Add(string name, string role, string emailId) { try { string password = ""; if (name.Length <= 4) { password = ValidationUtility.CreatePassword(name.Length + 2); } else { password = ValidationUtility.CreatePassword(name.Length); } int roleId = ValidationUtility.ToInteger(role); DataBaseUtility db = new DataBaseUtility(); string query = " insert into Users ([UserId],[Password],[RoleId],[EmailId],[CreatedDateTime],[LastUpdateDateTime]) " + " values(" + SQLUtility.getString(name) + "," + SQLUtility.getString(password) + ", " + SQLUtility.getInteger(roleId) + ", " + SQLUtility.getString(emailId) + " , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ,'" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' )"; db.ExecuteUpdate(query); } catch (Exception ex) { log.Error(" Exception in Add Method ", ex); } }
public void AddActual(ManagerBonusDTO managerBonusDTO) { DataBaseUtility db = new DataBaseUtility(); string query = "INSERT INTO [dbo].[ManagerBonus] ([StoreId] ,[LastDateOFMonth] ,[FoodCost] ,[LaborCost] ,[FoodLaborCost] ,[SalesIncrease] ,[CustomerIndex] ,[CustomerComplaint] ,[Catering] ,[SubInspection] ,[CreatedDateTime] ,[LastUpdateDateTime]) " + " VALUES (" + SQLUtility.getInteger(managerBonusDTO.StoreId) + ",'" + SQLUtility.FormateDateYYYYMMDD(managerBonusDTO.LastDateOFMonth) + "',0,0,0,0,0,0,0,0,'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')"; db.ExecuteUpdate(query); }
//Add Employee Clocking Information public void AddEmpClockingInfo(int empTrackerId, EmployeeClockingDTO employeeClockingDTO) { DataBaseUtility db = new DataBaseUtility(); string query = "INSERT INTO [dbo].[EmployeeClocking]([EmployeeTrackerId],[ClockFunctionTypeId],[ClockingTime],[MinutesWorked],[CreatedDateTime],[LastUpdateDateTime]) " + " VALUES(" + SQLUtility.getInteger(empTrackerId) + "," + SQLUtility.getInteger(employeeClockingDTO.ClockFunctionTypeId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeClockingDTO.ClockingTime) + "', " + " " + SQLUtility.getInteger(employeeClockingDTO.MinutesWorked) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )"; db.ExecuteUpdate(query); }
public void AddEmpInfo(int storeId, EmployeeInfoDTO employeeInfoDTO) { DataBaseUtility db = new DataBaseUtility(); string query = "INSERT INTO [dbo].[EmployeeInfo] ([EmpId],[StoreId],[EmpFirstName],[EmpMiddleName],[EmpLastName],[CreatedDateTime],[LastUpdateDateTime]) " + " VALUES(" + SQLUtility.getInteger(employeeInfoDTO.EmpId) + "," + SQLUtility.getInteger(storeId) + "," + SQLUtility.getString(employeeInfoDTO.EmpFirstName) + "," + SQLUtility.getString(employeeInfoDTO.EmpMiddleName) + ", " + " " + SQLUtility.getString(employeeInfoDTO.EmpLastName) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')"; db.ExecuteUpdate(query); }
public void DeleteComplaintsData(int cId) { DataBaseUtility db = new DataBaseUtility(); string query = "delete from dbo.Complaints where Id=" + SQLUtility.getInteger(cId) + ""; db.ExecuteUpdate(query); }
//Add Food order public void AddFoodOrderItem(string item) { DataBaseUtility db = new DataBaseUtility(); string query = "INSERT INTO [dbo].[FoodItem]([FoodName],[CreatedDateTime],[LastUpdateDateTime]) " + " VALUES(" + SQLUtility.getString(item) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')"; db.ExecuteUpdate(query); }
//Add Inactive Store public void AddInactiveStore(StoreDTO storeDTO) { DataBaseUtility db = new DataBaseUtility(); string query = "INSERT INTO [dbo].[Store] ([StoreNumber] ,[StoreName] ,[ConnectionString] ,[IsStoreActive] ,[CreatedDateTime] ,[LastUpdateDateTime]) VALUES(" + SQLUtility.getInteger(storeDTO.StoreNumber) + " , " + "" + SQLUtility.getString(storeDTO.StoreName) + "," + SQLUtility.getString(storeDTO.ConnectionString) + ",'" + storeDTO.IsActive + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')"; db.ExecuteUpdate(query); }
public void DeleteFoodItem(int id) { DataBaseUtility db = new DataBaseUtility(); string query = "delete from dbo.Orders where ItemId=" + SQLUtility.getInteger(id) + ""; db.ExecuteUpdate(query); query = " delete from dbo.FoodItem where Id=" + SQLUtility.getInteger(id) + ""; db.ExecuteUpdate(query); }
// Add Order by manager // one food order have multiple order public void AddOrder(OrderDTO dto) { DataBaseUtility db = new DataBaseUtility(); string query = " INSERT INTO [dbo].[Orders]([FoodOrderId],[ItemId],[NumberRemaining],[NumberOver],[CreatedDateTime],[LastUpdateDateTime])VALUES ( " + " " + SQLUtility.getInteger(dto.FoodOrderId) + " , " + SQLUtility.getInteger(dto.ItemId) + " , " + SQLUtility.getInteger(dto.NumberRemaining) + " , " + SQLUtility.getInteger(dto.NumberOver) + " , " + " '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.CreatedDateTime) + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.LastUpdateDateTime) + "' ) "; db.ExecuteUpdate(query); }
public void AddBounus(ManagerBonusDTO managerBonusDTO, bool isZeroBasis) { DataBaseUtility db = new DataBaseUtility(); String query = "INSERT INTO [dbo].[ManagerBonus] ([StoreId] ,[FirstDateOfMonth],[LastDateOfMonth],[FoodCost] ,[LaborCost] ,[FoodLaborCost] ,[SalesIncrease] ,[CustomerIndex] ,[CustomerComplaint] " + " ,[Catering] ,[SubInspection],[IsZeroBasis] ,[CreatedDateTime] ,[LastUpdateDateTime]) " + " VALUES (" + SQLUtility.getInteger(managerBonusDTO.StoreId) + ", '" + SQLUtility.FormateDateYYYYMMDD(managerBonusDTO.FirstDateOFMonth) + "','" + SQLUtility.FormateDateYYYYMMDD(managerBonusDTO.LastDateOFMonth) + "'," + SQLUtility.getDouble(managerBonusDTO.FoodCost) + "," + SQLUtility.getDouble(managerBonusDTO.LaborCost) + "," + SQLUtility.getDouble(managerBonusDTO.FoodLaborCost) + "," + SQLUtility.getDouble(managerBonusDTO.SalesIncrease) + ", " + " " + SQLUtility.getDouble(managerBonusDTO.CustomerIndex) + "," + SQLUtility.getDouble(managerBonusDTO.CustomerComplaint) + "," + SQLUtility.getDouble(managerBonusDTO.Catering) + ", " + " " + SQLUtility.getDouble(managerBonusDTO.SubInspection) + ",'" + isZeroBasis + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "') "; db.ExecuteUpdate(query); }
// Get General Manager AssignStore Information public ArrayList GetGenMngAssignStore(int userId) { ArrayList amList = new ArrayList(); DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; try { // string query = " select su.*,u.UserId as AeaManagerName from dbo.StoreUser su,dbo.Users u where su.UserId=u.Id and su.UserId in(select AreaManagerId from dbo.AssignAreaManagerInfo where GeneralManagerId=" + SQLUtility.getInteger(userId) + " ) "; // string query = "select AreaManagerId from dbo.AssignAreaManagerInfo where GeneralManagerId=" + SQLUtility.getInteger(userId) + " "; string query = " select aami.AreaManagerId,u.UserId from dbo.AssignAreaManagerInfo aami,dbo.Users u where aami.AreaManagerId=u.Id and aami.GeneralManagerId=" + SQLUtility.getInteger(userId) + " "; con = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { int uId = ValidationUtility.ToInteger(reader["AreaManagerId"].ToString()); string amName = reader["UserId"].ToString(); GeneralManagerBonusDTO dto = new GeneralManagerBonusDTO {UserId = uId, AmName = amName }; amList.Add(dto); } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error(" Exception in GetGenMngAssignStore Method ", ex); } finally { db.CloseConnection(con); } return amList; }
public void AddComplaints(ComplaintsDTO complaintsDTO) { DataBaseUtility db = new DataBaseUtility(); //string query = "INSERT INTO [dbo].[Complaints] ([StoreId],[ComplaintDate],[CustomerContacted],[FeedbackReceived],[ProblemFixed],[Comment],[Response] " // + " ,[CreatedDateTime],[LastUpdateDateTime]) VALUES(" + SQLUtility.getInteger(complaintsDTO.StoreId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.ComplaintDate) + "' ," // + " '" + complaintsDTO.CustomerContacted + "','" + complaintsDTO.FeedbackRecieved + "','" + complaintsDTO.ProblemFixed + "'," // + " " + SQLUtility.getString(complaintsDTO.Comment) + ", " + SQLUtility.getString(complaintsDTO.Response) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.CreatedDateTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.LastUpdateDateTime) + "' )"; string query = "INSERT INTO [dbo].[Complaints] ([StoreId] ,[ComplaintDate],[CustomerContacted] ,[FeedbackReceived],[ProblemFixed] ,[EmailedSubway] " +" ,[Comment],[Response],[Name] ,[Telephone] ,[Email],[CreatedDateTime] ,[LastUpdateDateTime]) " + " VALUES(" + SQLUtility.getInteger(complaintsDTO.StoreId) + ",'" + SQLUtility.FormateDateYYYYMMDD(complaintsDTO.ComplaintDate) + "', '" + complaintsDTO.CustomerContacted + "','"+complaintsDTO.FeedbackRecieved+"', " + " '" + complaintsDTO.ProblemFixed + "','" + complaintsDTO.EmailedSubway + "'," + SQLUtility.getString(ValidationUtility.TruncateString(complaintsDTO.Comment, 500)) + "," + SQLUtility.getString(ValidationUtility.TruncateString(complaintsDTO.Response, 500)) + "," + SQLUtility.getString(complaintsDTO.Name) + "," + SQLUtility.getString(complaintsDTO.TelePhone) + "," + SQLUtility.getString(complaintsDTO.Email) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.CreatedDateTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.LastUpdateDateTime) + "')"; db.ExecuteUpdate(query); }
public void DeleteMaintenanceRequest(int id) { DataBaseUtility db = new DataBaseUtility(); try { string query = " delete from dbo.StoreMaintainance where Id = " + SQLUtility.getInteger(id) + " "; db.ExecuteUpdate(query); } catch (Exception ex) { log.Error(" Exception in DeleteMaintenanceRequest Method ", ex); } }
//Method use for General Manager public void AddAssignAreaManager(int gmId, int amId) { DataBaseUtility db = new DataBaseUtility(); if (IsAreaManagerExist(amId)) { string deleteQuery = "delete from dbo.AssignAreaManagerInfo where AreaManagerId=" + SQLUtility.getInteger(amId) + ""; db.ExecuteUpdate(deleteQuery); } string query = "INSERT INTO [dbo].[AssignAreaManagerInfo] ([GeneralManagerId] ,[AreaManagerId] ,[CreatedDateTime] ,[LastUpdateDateTime]) " + " VALUES (" + SQLUtility.getInteger(gmId) + "," + SQLUtility.getInteger(amId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')"; db.ExecuteUpdate(query); }
// Store Functionality public void AddStore(int sNumber, string sName, string connectionString) { try { DataBaseUtility db = new DataBaseUtility(); string query = " INSERT [dbo].[Store]([StoreNumber],[StoreName],[ConnectionString]) VALUES(" + SQLUtility.getInteger(sNumber) + " , " + " " + SQLUtility.getString(sName) + "," + SQLUtility.getString(connectionString) + " ) "; db.ExecuteUpdate(query); } catch (Exception ex) { log.Error(" Exception in AddStore Method ", ex); } }
public void AddPaperWork(ArrayList list) { foreach (WeeklyPaperworkDTO dto in list) { DataBaseUtility db = new DataBaseUtility(); string query = "INSERT INTO [dbo].[WeeklyPaperWork]([StoreId],[NetSales],[GiftCardSales],[AR],[PaidOuts],[PettyExpense],[Total],[StoreTransfer],[PFG1],[PFG2],[TotalPFG],[Coke],[TotalCost],[CostPercent],[LaborCost],[LaborCostPercent],[Royalty],[FAF],[CostAndLaborCostPercent],[AdjTax],[GCRedeem],[GCDifference],[TaxPercent], " + " [ActualSalesTaxper],[DifferenceOfSalesTax],[NonTaxableSale],[WeekStartDate],[CreatedDateTime],[LastUpdateDateTime]) " + " VALUES (" + SQLUtility.getInteger(dto.StoreId) + " , " + SQLUtility.getDouble(dto.NetSales) + " ," + SQLUtility.getDouble(dto.GiftCardSales) + "," + SQLUtility.getDouble(dto.Ar) + "," + SQLUtility.getDouble(dto.PaidOuts) + "," + SQLUtility.getDouble(dto.PettyExpense) + ", " + " " + SQLUtility.getDouble(dto.Total) + "," + SQLUtility.getDouble(dto.StoreTransfer) + "," + SQLUtility.getDouble(dto.Pfg1) + "," + SQLUtility.getDouble(dto.Pfg2) + "," + SQLUtility.getDouble(dto.TotalPFG) + "," + SQLUtility.getDouble(dto.Coke) + "," + SQLUtility.getDouble(dto.TotalCost) + ", " + " " + SQLUtility.getDouble(dto.CostPercent) + "," + SQLUtility.getDouble(dto.LaborCost) + "," + SQLUtility.getDouble(dto.LaborCostPercent) + "," + SQLUtility.getDouble(dto.Royalty) + "," + SQLUtility.getDouble(dto.Faf) + "," + SQLUtility.getDouble(dto.CostAndLaborCostPercent) + "," + SQLUtility.getDouble(dto.AdjTax) + "," + SQLUtility.getDouble(dto.GcRedeem) + "," + SQLUtility.getDouble(dto.GcDifference) + ", " + " " + SQLUtility.getDouble(dto.TaxPercent) + ", " + SQLUtility.getDouble(dto.ActualSalesTaxper) + "," + SQLUtility.getDouble(dto.DifferenceOfSalesTax) + "," + SQLUtility.getDouble(dto.NonTaxableSale) + ",'" + SQLUtility.FormateDateYYYYMMDD(dto.WeekStartDate) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')"; db.ExecuteUpdate(query); } }
public void AddUserStore(int storeId, int userId) { try { DataBaseUtility db = new DataBaseUtility(); string query = " INSERT [dbo].[StoreUser]([StoreId],[UserId],[CreatedDateTime],[LastUpdateDateTime]) VALUES(" + SQLUtility.getInteger(storeId) + " , " + " " + SQLUtility.getInteger(userId) + " , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ,'" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ) "; db.ExecuteUpdate(query); } catch (Exception ex) { log.Error(" Exception in AddUserStore Method ", ex); } }
public ArrayList GetFoodItemList() { DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; ArrayList list = new ArrayList(); try { string query = "select Id,FoodName from dbo.FoodItem order by Id desc"; //string query = "select Id,StoreId,BusinessDate,Item,ItemQuantity,LeftQuantity,OverQuantity from FoodOrder WHERE BusinessDate BETWEEN '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' AND '" + SQLUtility.FormateDateYYYYMMDD(weekEndDate) + "'"; con = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { int id = ValidationUtility.ToInteger(reader[0].ToString()); string foodName = reader[1].ToString(); FoodItemDTO dto = new FoodItemDTO { Id = id, FoodName = foodName }; list.Add(dto); } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error(" Exception in GetFoodItemList Method ", ex); } finally { db.CloseConnection(con); } return list; }
public void AddMaintenanceRequest(MaintenanceDTO dto) { // string query = "insert into StoreMaintainance " try { DataBaseUtility db = new DataBaseUtility(); string query = "INSERT INTO [dbo].[StoreMaintainance]([StoreId],[MaintenanceCategoryId],[Description],[status],[DateCreated]) VALUES " + "(" + SQLUtility.getInteger(dto.StoreId) + ", " + SQLUtility.getInteger(dto.CategoryId) + " ," + SQLUtility.getString(dto.Description) + " , " + " "+SQLUtility.getString(dto.Status)+" , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' )"; db.ExecuteUpdate(query); } catch (Exception ex) { log.Error(" Exception in AddMaintenanceRequest Method ", ex); } }
public void AddNewEmployeeInTracking(int empInfoId, DateTime date) { DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(date); DataBaseUtility db = new DataBaseUtility(); for (int i = 0; i < 7; i++) { DateTime businessDate = weekStartDate.AddDays(i); EmployeeTrackerDTO employeeTrackerDTO = new EmployeeTrackerDTO(); string query = "INSERT INTO [dbo].[EmployeeTracker]([BusinessDate],[EmployeeInfoId],[ScheduleIn],[ScheduleOut],[TotalTimeWorked],[CreatedDateTime],[LastUpdateDateTime]) " + " VALUES('" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'," + SQLUtility.getInteger(empInfoId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeTrackerDTO.ScheduleIn) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeTrackerDTO.ScheduleOut) + "', " + " '00:00:00','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )"; db.ExecuteUpdate(query); } }
// Get AreaManager AssignStore Information public ArrayList GetAreaMngAssignStore(int userId) { ArrayList assigneStore = new ArrayList(); DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; try { string query = "select su.Id,s.StoreName,s.Id from dbo.StoreUser su, dbo.Store s where s.Id = su.StoreId and su.UserId = " + SQLUtility.getInteger(userId) + " "; con = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { int uId = ValidationUtility.ToInteger(reader[0].ToString()); string storeName = reader[1].ToString(); int sId = ValidationUtility.ToInteger(reader[2].ToString()); StoreDTO dto = new StoreDTO { Id = uId, StoreName = storeName, StoreId = sId }; assigneStore.Add(dto); } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error(" Exception in GetAreaMngAssignStore Method ", ex); } finally { db.CloseConnection(con); } return assigneStore; }
public static int GetActiveStoredId(int userId) { DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; int storId = 0; try { string query = " select Id from dbo.Store where Id in(select StoreId from StoreUser where UserId = " + SQLUtility.getInteger(userId) + ") and IsStoreActive='true'"; con = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); if (reader.Read()) { storId = ValidationUtility.ToInteger(reader[0].ToString()); } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error(" Exception in GetStoredId Method ", ex); } finally { db.CloseConnection(con); } return storId; }
public DataTable GetEmpInfo() { // string query = " select AmtTotalSales as TOTAL_SUBWAY_SALES, QtyFootlongs as FOOT_LONG, Qty6Inches as INCH_6, Qty3Inches INCH_3, QtyMuffinMelt as MUFF_IN_MELT , QtySalads as SALAD ,QtyPizzas as PIZZA , AmtCateringSales as CATERING , AmtTotalRefund as REFUNDS , AmtSubwayVoid as VOIDS, AmtUnitSales as TOTAL_UNIT_SALES , AmtDrinkSales as TOTAL_DRINK_SALES from Report.DrawersSummary where OpeningInformationId in (select OpeningInformationId from dbo.OpeningInformation where BusinessDate between '" + weekDate.ToString("yyyy/MM/dd") + "' and '" + nextDate.ToString("yyyy/MM/dd") + "' )"; DataTable drawersSummaryTable = null; try { string query = "select emp.FirstName , emp.MiddleName, emp.LastName" + ", uc.ClockingTime,uc.MinutesWorked, uc.InsertedDate from dbo.UserClocking uc, dbo.Employees emp where emp.EmployeeId = uc.EmployeeId"; DataBaseUtility db = new DataBaseUtility(); drawersSummaryTable = db.FetchData(query); } catch (Exception ex) { log.Error(" Exception in GetEmpInfo Method ", ex); } return drawersSummaryTable; }
public ArrayList GetBonusEmpName(int userId, string connectionString, DateTime businessDate) { DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; ArrayList list = new ArrayList(); DateTime weekStartDate = GetActualWeekStartDate(businessDate); DateTime secondDate = weekStartDate.AddDays(1); DateTime thirdDate = weekStartDate.AddDays(2); DateTime fourDate = weekStartDate.AddDays(3); DateTime fiveDate = weekStartDate.AddDays(4); DateTime sixDate = weekStartDate.AddDays(5); DateTime sevenDate = weekStartDate.AddDays(6); ArrayList DateList = new ArrayList(); DateList.Add(weekStartDate); DateList.Add(secondDate); DateList.Add(thirdDate); DateList.Add(fourDate); DateList.Add(fiveDate); DateList.Add(sixDate); DateList.Add(sevenDate); try { string query = null; con = db.OpenConnection(); foreach (DateTime date in DateList) { query = " select EmployeeId, FirstName,LastName,BusinessDate from dbo.EmployeeBonus where StoreId in (select StoreId from dbo.StoreUser where UserId =" + SQLUtility.getInteger(userId) + ") and BusinessDate='" + date.ToString("yyyy/MM/dd") + "'"; SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { int emoId = ValidationUtility.ToInteger(reader[0].ToString()); string firstName = reader[1].ToString(); string lastName = reader[2].ToString(); EmpBonusDTO dto = new EmpBonusDTO { EmployeeId = emoId, FirstName = firstName, LastName = lastName, BusinessDateStringType = date.ToString("MM/dd/yyyy") }; list.Add(dto); } reader.Close(); comm.Dispose(); } } catch (Exception ex) { log.Error("Exception in GetPerdaySales method ", ex); } finally { db.CloseConnection(con); } return list; }
public void UpdatePerdaySales(double perDaySalesAmount, int storeId, DateTime bussnessDate, int opId) { bool isCurrentDateSale = IsCurrentDateSaleIsExsist(storeId, bussnessDate); DataBaseUtility db = new DataBaseUtility(); if (isCurrentDateSale) { string query = " update dbo.PerdaySales set SalesAmount = " + SQLUtility.getDouble(perDaySalesAmount) + " , OpeningInformationId = " + SQLUtility.getInteger(opId) + " , " + " LastUpdateDateTime = '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' where StoreId = " + SQLUtility.getInteger(storeId) + " and BusinessDate = '" + bussnessDate.ToString("yyyy/MM/dd") + "' "; db.ExecuteUpdate(query); } else { string query = " INSERT INTO [dbo].[PerdaySales]([StoreId],[OpeningInformationId],[BusinessDate],[SalesAmount],[WeekOfDay],[CreatedDateTime],[LastUpdateDateTime]) VALUES " + " (" + SQLUtility.getInteger(storeId) + ", " + SQLUtility.getInteger(opId) + " , '" + SQLUtility.FormateDateYYYYMMDD(bussnessDate) + "' ," + SQLUtility.getDouble(perDaySalesAmount) + " , " + SQLUtility.getString(bussnessDate.DayOfWeek.ToString()) + " , " + " '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "') "; db.ExecuteUpdate(query); } }
// Method is use for Get perday total sales amount day public bool IsCurrentDateSaleIsExsist(int storeId, DateTime currentDate) { DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; bool isCurrentDateSale = false; try { string query = " select * from dbo.PerdaySales where BusinessDate = '" + currentDate.ToString("yyyy/MM/dd") + "' and StoreId = " + SQLUtility.getInteger(storeId) + " "; con = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); if (reader.Read()) { isCurrentDateSale = true; } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error("Exception in IsCurrentDateSaleIsExsist method ", ex); } finally { db.CloseConnection(con); } return isCurrentDateSale; }
public ArrayList GetRecodSaleList(int userId, DateTime selectedDate) { DateTime dt = Convert.ToDateTime(selectedDate); // DateTime weekStartDate = GetActualWeekStartDate(DateTime.Now); DateTime weekStartDate = GetActualWeekStartDate(dt); weekStartDate = weekStartDate.AddDays(-1); DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; ArrayList list = new ArrayList(); try { string query = " select * from dbo.RecordSales where StoreId in (select StoreId from dbo.StoreUser where UserId = " + SQLUtility.getInteger(userId) + ")"; con = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { weekStartDate = weekStartDate.AddDays(1); string weekDate = weekStartDate.ToString("MM/dd/yyyy"); RecordSalesDTO dto = null; int id = ValidationUtility.ToInteger(reader[0].ToString()); int sid = ValidationUtility.ToInteger(reader[1].ToString()); string day = reader[2].ToString(); DateTime businessDate = ValidationUtility.ToDate(reader[3].ToString()); double salesAmount = ValidationUtility.ToDouble(reader[4].ToString()); //if (!ValidationUtility.IsEqual("01-01-0001 00:00:00", BusinessDate.ToString()) || !ValidationUtility.IsEqual("1/1/0001 12:00:00 AM", BusinessDate.ToString())) if (ValidationUtility.FormateDateYYYYMMDD(ValidationUtility.GetDefaultDate()).Equals(ValidationUtility.FormateDateYYYYMMDD(businessDate))) { dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = "N/A", SalesAmountStringType = "N/A" }; //dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = BusinessDate.ToString("MM/dd/yyyy"), SalesAmountStringType = salesAmount.ToString() }; } //else if (ValidationUtility.IsEqual("1/1/0001 12:00:00 AM", BusinessDate.ToString())) //{ // dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = "N/A", SalesAmountStringType = "N/A" }; // // dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = BusinessDate.ToString("MM/dd/yyyy"), SalesAmountStringType = salesAmount.ToString() }; //} else { dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = businessDate.ToString("MM/dd/yyyy"), SalesAmountStringType = salesAmount.ToString() }; // dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = "N/A", SalesAmountStringType = "N/A" }; } list.Add(dto); } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error("Exception in GetRecodSaleList method ", ex); } finally { db.CloseConnection(con); } return list; }
public ArrayList GetPerdaySales(int userId, string connectionString, DateTime businessDate) { DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; ArrayList list = new ArrayList(); DateTime weekStartDate = GetActualWeekStartDate(businessDate); DateTime secondDate = weekStartDate.AddDays(1); DateTime thirdDate = weekStartDate.AddDays(2); DateTime fourDate = weekStartDate.AddDays(3); DateTime fiveDate = weekStartDate.AddDays(4); DateTime sixDate = weekStartDate.AddDays(5); DateTime sevenDate = weekStartDate.AddDays(6); ArrayList DateList = new ArrayList(); DateList.Add(weekStartDate); DateList.Add(secondDate); DateList.Add(thirdDate); DateList.Add(fourDate); DateList.Add(fiveDate); DateList.Add(sixDate); DateList.Add(sevenDate); try { string query = null; con = db.OpenConnection(); foreach (DateTime date in DateList) { query = " select * from dbo.PerdaySales where StoreId in (select StoreId from dbo.StoreUser where UserId =" + SQLUtility.getInteger(userId) + ") and BusinessDate='" + date.ToString("yyyy/MM/dd") + "'"; SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); if (reader.Read()) { int id = ValidationUtility.ToInteger(reader[0].ToString()); int sid = ValidationUtility.ToInteger(reader[1].ToString()); int openingInformationId = ValidationUtility.ToInteger(reader[2].ToString()); double salesAmount = ValidationUtility.ToDouble(reader[4].ToString()); string day = reader[5].ToString(); if (openingInformationId == 0 && !ValidationUtility.IsEqual(day, DateTime.Now.DayOfWeek.ToString())) { PerdaySalesDTO dto = new PerdaySalesDTO { Id = id, StoreId = sid, OpeningInformationId = openingInformationId, BusinessDate = date, SalesAmountString = "N/A", WeekOfDay = day }; list.Add(dto); } else { PerdaySalesDTO dto = new PerdaySalesDTO { Id = id, StoreId = sid, OpeningInformationId = openingInformationId, BusinessDate = date, SalesAmountString = salesAmount.ToString(), WeekOfDay = day }; list.Add(dto); } } else { reader.Close(); comm.Dispose(); PerdaySalesDTO dto = null; if (date<=DateTime.Now) { int id = GetCurrentDateOpeningInfoId(date, connectionString); if (id == 0) { dto = new PerdaySalesDTO { SalesAmountString = "N/A" }; list.Add(dto); } else { dto = new PerdaySalesDTO { SalesAmountString = "0" }; list.Add(dto); } } else { dto = new PerdaySalesDTO { SalesAmountString = "0" }; list.Add(dto); } } reader.Close(); comm.Dispose(); } } catch (Exception ex) { log.Error("Exception in GetPerdaySales method ", ex); } finally { db.CloseConnection(con); } return list; }