// Method is use for Get all Last perday record sales of each store public ArrayList GetLastDaySalesData(DateTime lastDayDate, string weekOfDay,int storeId) { DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; ArrayList list = new ArrayList(); try { string query = " select ps.Id,ps.StoreId,ps.BusinessDate from dbo.PerdaySales ps where ps.StoreId = " + SQLUtility.getInteger(storeId) + " " + " and ps.BusinessDate='" + lastDayDate.ToString("yyyy/MM/dd") + "' and ps.WeekOfDay=" + SQLUtility.getString(weekOfDay) + " "; con = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); if (reader.Read()) { PerdaySalesDTO dto = new PerdaySalesDTO(); dto.Id = ValidationUtility.ToInteger(reader["Id"].ToString()); dto.StoreId = ValidationUtility.ToInteger(reader["StoreId"].ToString()); dto.BusinessDate = ValidationUtility.ToDate(reader["BusinessDate"].ToString()); list.Add(dto); } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error("Exception in GetLastDaySalesData 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; }
// Store Functionality public void AddStore(StoreDTO storeDTO) { int sNumber = storeDTO.StoreNumber; string sName = storeDTO.StoreName; string connectionString = storeDTO.ConnectionString; DataBaseUtility db = new DataBaseUtility(); try { ArrayList recordSale = new ArrayList(); ArrayList list = new ArrayList(); list.Add("Wednesday"); list.Add("Thursday"); list.Add("Friday"); list.Add("Saturday"); list.Add("Sunday"); list.Add("Monday"); list.Add("Tuesday"); foreach (string s in list) { RecordSalesDTO dto = new RecordSalesDTO(); DataTable dt = GetWeekDayRecord(connectionString, s, DateTime.Now); dto.DayOfWeek = s; for (int i = 0; i < dt.Rows.Count; i++) { dto.BusinessDate = (DateTime)dt.Rows[i]["BusinessDate"]; //dto.DayOfWeek = dt.Rows[i]["WeekOfDay"].ToString(); dto.SalesAmount = ValidationUtility.ToDouble(dt.Rows[i]["PerDaySale"].ToString()); } recordSale.Add(dto); } if (recordSale != null && recordSale.Count == 7) { //string query = " INSERT INTO [dbo].[Store]([StoreNumber],[StoreName],[ConnectionString]) VALUES(" + SQLUtility.getInteger(sNumber) + " , " // + " " + SQLUtility.getString(sName) + "," + SQLUtility.getString(connectionString) + " ) "; //db.ExecuteUpdate(query); UpdateStore(storeDTO); //int id = GetStoredId(sNumber); int id = storeDTO.Id; if (id != 0) { foreach (RecordSalesDTO dto in recordSale) { string query = " INSERT INTO [dbo].[RecordSales]([StoreId],[DayOfWeek],[BusinessDate],[SalesAmount],[CreatedDateTime],[LastUpdateDateTime]) " + " VALUES(" + SQLUtility.getInteger(id) + ", " + SQLUtility.getString(dto.DayOfWeek) + " , '" + dto.BusinessDate.ToString("yyyy/MM/dd") + "'," + SQLUtility.getDouble(dto.SalesAmount) + " " + " , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ,'" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "') "; db.ExecuteUpdate(query); } } } // Add Previous Sales ArrayList openingList = GetAllOpeningInformationId(connectionString); foreach (int id in openingList) { DataTable dt = GetPreviousSales(connectionString, id); PerdaySalesDTO perdaySalesDTO = new PerdaySalesDTO(); for (int i = 0; i < dt.Rows.Count; i++) { perdaySalesDTO.BusinessDate = (DateTime)dt.Rows[i]["BusinessDate"]; perdaySalesDTO.OpeningInformationId = ValidationUtility.ToInteger(dt.Rows[i]["OpeningInformationId"].ToString()); perdaySalesDTO.SalesAmount = ValidationUtility.ToDouble(dt.Rows[i]["PerDaySale"].ToString()); perdaySalesDTO.WeekOfDay = dt.Rows[i]["WeekOfDay"].ToString(); } int storeId = storeDTO.Id; string query = "INSERT INTO [dbo].[PerdaySales]([StoreId],[OpeningInformationId],[BusinessDate],[SalesAmount],[WeekOfDay],[CreatedDateTime],[LastUpdateDateTime]) VALUES ( " + " " + SQLUtility.getInteger(storeId) + " , " + SQLUtility.getInteger(perdaySalesDTO.OpeningInformationId) + " , '" + perdaySalesDTO.BusinessDate.ToString("yyyy/MM/dd") + "'," + "" + SQLUtility.getDouble(perdaySalesDTO.SalesAmount) + ", " + SQLUtility.getString(perdaySalesDTO.WeekOfDay) + ", '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "', '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ) "; db.ExecuteUpdate(query); } //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); throw ex; } }