// Method is use for get Selected DateRecored sales (Last one year from selected date) public ArrayList GetSelectedDateRecordSales(DateTime selectedDate, string connectionString) { 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"); ArrayList recordSale = new ArrayList(); DateTime weekStartDate = GetActualWeekStartDate(selectedDate); weekStartDate = weekStartDate.AddDays(-1); foreach (string s in list) { try { DataTable dt = GetWeekDayRecord(connectionString, s, selectedDate); string dayOfweek = s; //dto.DayOfWeek = s; DateTime businessDate = new DateTime(); double ammount = 0; weekStartDate = weekStartDate.AddDays(1); string weekDate = weekStartDate.ToString("MM/dd/yyyy"); for (int i = 0; i < dt.Rows.Count; i++) { businessDate = (DateTime)dt.Rows[i]["BusinessDate"]; ammount = ValidationUtility.ToDouble(dt.Rows[i]["PerDaySale"].ToString()); } RecordSalesDTO dto = null; DateTime defultDate = new DateTime(0001,01,01); if (defultDate.Date.Equals(businessDate.Date)) { dto = new RecordSalesDTO { BusinessDateStringType = "N/A", SalesAmountStringType = "N/A", DayOfWeek = weekStartDate.DayOfWeek.ToString().Substring(0, 3).ToUpper(), WeekDayDate = weekDate }; } else if (ValidationUtility.IsEqual("1/1/0001 12:00:00 AM", businessDate.ToString())) { dto = new RecordSalesDTO { BusinessDateStringType = "N/A", SalesAmountStringType = "N/A", DayOfWeek = weekStartDate.DayOfWeek.ToString().Substring(0, 3).ToUpper(), WeekDayDate = weekDate }; } else { dto = new RecordSalesDTO { SalesAmountStringType = ammount.ToString(), BusinessDateStringType = businessDate.ToString("MM/dd/yyyy"), DayOfWeek = weekStartDate.DayOfWeek.ToString().Substring(0, 3).ToUpper(), WeekDayDate = weekDate }; } recordSale.Add(dto); } catch (Exception ex) { log.Error("Exception in GetSelectedDateRecordSales Method ",ex); } } return recordSale; }
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; }
// Update Local Store Data where Record sales is less then current record sale public void UpdateStore(RecordSalesDTO dto, string connectionString) { DataBaseUtility db = new DataBaseUtility(); if (!ValidationUtility.IsNull(dto.DayOfWeek)) { RecordSalesDTO updateDTO = GetRecordSaleByStoreID(dto.StoreId, dto.DayOfWeek); log.Info("DayOfWeek " + dto.DayOfWeek); log.Info(" server record sales amount " + dto.SalesAmount); //for testing only if (!ValidationUtility.IsNull(updateDTO)) { log.Info(" local record sales amount " + updateDTO.SalesAmount); } if (!ValidationUtility.IsNull(updateDTO) && dto.SalesAmount > updateDTO.SalesAmount) { string query = " update dbo.RecordSales set BusinessDate = '" + dto.BusinessDate.ToString("yyyy/MM/dd") + "' , SalesAmount = " + SQLUtility.getDouble(dto.SalesAmount) + " , " + " PreviousSaleAmount = " + SQLUtility.getDouble(updateDTO.SalesAmount) + " , PreviousRecordSaleDate = '" + updateDTO.BusinessDate.ToString("yyyy/MM/dd") + "' , DayOfWeek = " + SQLUtility.getString(dto.DayOfWeek) + " , " + " LastUpdateDateTime = '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' where StoreId = " + SQLUtility.getInteger(dto.StoreId) + " and DayOfWeek = " + SQLUtility.getString(dto.DayOfWeek) + " "; db.ExecuteUpdate(query); ArrayList empBonusList = GetEmpInfo(connectionString, dto.BusinessDate); foreach (EmpBonusDTO empDTO in empBonusList) { log.Info(" Name of employee for bonus " + empDTO.EmployeeId + " " + empDTO.FirstName); // Insert string empQuery = "INSERT INTO [dbo].[EmployeeBonus]([StoreId],[EmployeeId],[EmployeeRoleId],[FirstName],[LastName],[BonusAmount],[BusinessDate],[CreatedDateTime],[LastUpdateDateTime]) " + " VALUES(" + SQLUtility.getInteger(dto.StoreId) + "," + SQLUtility.getInteger(empDTO.EmployeeId) + "," + SQLUtility.getInteger(empDTO.EmployeeRoleId) + "," + SQLUtility.getString(empDTO.FirstName) + "," + SQLUtility.getString(empDTO.LastName) + ", " + " 10,'" + empDTO.BusinessDate.ToString("yyyy/MM/dd") + "','" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "','" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "')"; db.ExecuteUpdate(empQuery); } } // When Record sale is not found of given day then insert it on data base else if (ValidationUtility.IsNull(updateDTO)) { string query = " INSERT INTO [dbo].[RecordSales]([StoreId],[DayOfWeek],[BusinessDate],[SalesAmount],[CreatedDateTime],[LastUpdateDateTime]) " + " VALUES(" + SQLUtility.getInteger(dto.StoreId) + ", " + 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); } } }
public void UpdateRecordSale() { try { log.Info(" UpdateRecordSale Start Time is " + DateTime.Now); ArrayList weekDayList = new ArrayList(); string weekOfDay = DateTime.Now.AddDays(-1).DayOfWeek.ToString(); weekDayList.Add(weekOfDay); log.Info(" Day Of week " + DateTime.Now.AddDays(-1).DayOfWeek.ToString()); //weekDayList.Add("Thursday"); //weekDayList.Add("Friday"); //weekDayList.Add("Saturday"); //weekDayList.Add("Sunday"); //weekDayList.Add("Monday"); //weekDayList.Add("Tuesday"); StoreModel sm = new StoreModel(); // Get All Store List from local database // ArrayList list = sm.GetStoreList(); ArrayList list = ValidationUtility.GetActiveStoreList(true); foreach (StoreDTO dto in list) { ArrayList recordSale = new ArrayList(); foreach (string s in weekDayList) { //Get Business day record (which is high in year) DataTable dt = GetWeekDayRecord(dto.ConnectionString, s); RecordSalesDTO recordSaleDto = new RecordSalesDTO(); for (int i = 0; i < dt.Rows.Count; i++) { recordSaleDto.BusinessDate = (DateTime)dt.Rows[i]["BusinessDate"]; recordSaleDto.DayOfWeek = dt.Rows[i]["WeekOfDay"].ToString(); recordSaleDto.SalesAmount = ValidationUtility.ToDouble(dt.Rows[i]["PerDaySale"].ToString()); recordSaleDto.StoreId = dto.Id; } recordSale.Add(recordSaleDto); } // Now Update Records foreach (RecordSalesDTO rsDto in recordSale) { UpdateStore(rsDto, dto.ConnectionString); } } // UpdateLast sale foreach (StoreDTO dto in list) { RecordSaleModel rsm = new RecordSaleModel(); log.Info("Record update for StorId " + dto.Id); log.Info("Last Perday sale date " + DateTime.Now.AddDays(-1)); double perDaySalesAmount = GetPerDayUpdatedSalesAmount(dto.ConnectionString, DateTime.Now.AddDays(-1)); log.Info("Last date perday sales amount " + perDaySalesAmount); int opId = rsm.GetCurrentDateOpeningInfoId(DateTime.Now.AddDays(-1), dto.ConnectionString); log.Info("Opening Information Id of last per day sales " + opId); rsm.UpdatePerdaySales(perDaySalesAmount, dto.Id, DateTime.Now.AddDays(-1), opId); //ArrayList lastPerDaySales = GetLastDaySalesData(DateTime.Now.AddDays(-1), DateTime.Now.AddDays(-1).DayOfWeek.ToString(), dto.Id); //foreach (PerdaySalesDTO perdaySalesDTO in lastPerDaySales) //{ // double perDaySalesAmount = GetPerDayUpdatedSalesAmount(dto.ConnectionString, perdaySalesDTO.BusinessDate); // int opId = rsm.GetCurrentDateOpeningInfoId(perdaySalesDTO.BusinessDate, dto.ConnectionString); // rsm.UpdatePerdaySales(perDaySalesAmount, perdaySalesDTO.StoreId, perdaySalesDTO.BusinessDate, opId); //} } log.Info(" End Time " + DateTime.Now); } catch (Exception ex) { log.Error("Exception in UpdateRecordSale ", ex); } }
// Get Record sales ammount from local database public RecordSalesDTO GetRecordSaleByStoreID(int storeId, string weekOfDay) { DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; ArrayList list = new ArrayList(); RecordSalesDTO dto = null; try { string query = " select SalesAmount,BusinessDate from dbo.RecordSales where StoreId = " + SQLUtility.getInteger(storeId) + " and DayOfWeek = " + SQLUtility.getString(weekOfDay) + " "; con = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); if (reader.Read()) { dto = new RecordSalesDTO(); dto.SalesAmount = ValidationUtility.ToDouble(reader["SalesAmount"].ToString()); dto.BusinessDate = ValidationUtility.ToDate(reader["BusinessDate"].ToString()); } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error("Exception in GetRecordSaleByStoreID method ", ex); } finally { db.CloseConnection(con); } return dto; }
// 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; } }