public List<DepositAmount> GetDepositAmountLocalDB(DateTime weekStartDate, DateTime weekEndDate, int storeId) { SqlConnection con = null; DataBaseUtility db = new DataBaseUtility(); List<DepositAmount> list = new List<DepositAmount>(); try { string query = " select bp.Id, bp.StoreId,bp.DepositAmount,bp.BusinessDate,bp.Status,bp.WeekOfDay from dbo.BankDeposit bp " + " where bp.BusinessDate between '" + ValidationUtility.FormateDateYYYYMMDD(weekStartDate) + "' and '" + ValidationUtility.FormateDateYYYYMMDD(weekEndDate) + "' " + " and bp.StoreId = " + SQLUtility.getInteger(storeId) + ""; con = db.OpenConnection(); SqlCommand cmd = db.getSQLCommand(query, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int id = ValidationUtility.ToInteger(reader["Id"].ToString()); double depositAmount = ValidationUtility.ToDouble(reader["DepositAmount"].ToString()); DateTime businessDate = ValidationUtility.ToDate(reader["BusinessDate"].ToString()); string status = reader["Status"].ToString(); string weekOfDay = reader["WeekOfDay"].ToString(); DepositAmount damount = new DepositAmount { Id = id, DepositBankAmount = depositAmount, Status = status, BusinessDateToString = ValidationUtility.FormateDateYYYYMMDD(businessDate), BusinessDate = businessDate, WeekOfDay = weekOfDay.Substring(0, 3).ToUpper() }; list.Add(damount); } reader.Close(); cmd.Dispose(); } catch (Exception ex) { log.Error("Exception in GetDepositAmountLocalDB method ", ex); } finally { db.CloseConnection(con); } return list; }
//Get weekly deposit amount from cash control public ArrayList GetWeeklyDepositAmount(DateTime selectDate, ArrayList storeList) { ArrayList depositAmountList = new ArrayList(); try { // ArrayList storeList = GetStoreList(); foreach (StoreDTO storeDTO in storeList) { DepositDTO depositDTO1 = GetBankDepositListFromLocalDB(selectDate, storeDTO, true); if (depositDTO1 != null) { //foreach (DepositAmount depositAmountDTO in depositDTO1.DepositAmount) //{ DataTable dt = GetEachStoreDepositAmount(storeDTO.ConnectionString, selectDate); ArrayList ammountList = new ArrayList(); if (!ValidationUtility.IsNull(dt)) { List<DepositAmount> depositAmmountList = new List<DepositAmount>(); DateTime businessDate = new DateTime(); for (int i = 0; i < dt.Rows.Count; i++) { double depositAmount = 0; businessDate = ValidationUtility.ToDate(dt.Rows[i]["BusinessDate"].ToString()); if (businessDate.Date <= ValidationUtility.GetActualWeekStartDate(selectDate).AddDays(7).Date) { DepositAmount depositAmountDTO = (DepositAmount)depositDTO1.DepositAmount[i]; depositAmount = ValidationUtility.ToDouble(dt.Rows[i]["DepositInBank"].ToString()); DepositAmount dammount = new DepositAmount { Id = depositAmountDTO.Id, DepositBankAmount = depositAmount, BusinessDate = businessDate }; depositAmmountList.Add(dammount); } } DepositDTO depositDTO = new DepositDTO { StoreId = storeDTO.Id, StoreNumber = storeDTO.StoreNumber, DepositAmount = depositAmmountList }; //InsertDepositAmount(depositDTO); UpdateSelectDateDepositAmount(depositDTO); depositAmountList.Add(GetBankDepositListFromLocalDB(selectDate, storeDTO, false)); } //} // depositAmountList.Add(depositDTO1); } if (depositDTO1 == null) { DataTable dt = GetEachStoreDepositAmount(storeDTO.ConnectionString, selectDate); ArrayList ammountList = new ArrayList(); if (!ValidationUtility.IsNull(dt)) { List<DepositAmount> depositAmmountList = new List<DepositAmount>(); DateTime businessDate = new DateTime(); for (int i = 0; i < dt.Rows.Count; i++) { double depositAmount = 0; businessDate = ValidationUtility.ToDate(dt.Rows[i]["BusinessDate"].ToString()); if (businessDate.Date <= ValidationUtility.GetActualWeekStartDate(selectDate).AddDays(7).Date) { depositAmount = ValidationUtility.ToDouble(dt.Rows[i]["DepositInBank"].ToString()); DepositAmount dammount = new DepositAmount { DepositBankAmount = depositAmount, BusinessDate = businessDate }; depositAmmountList.Add(dammount); } } DepositDTO depositDTO = new DepositDTO { StoreId = storeDTO.Id, StoreNumber = storeDTO.StoreNumber, DepositAmount = depositAmmountList }; InsertDepositAmount(depositDTO); depositAmountList.Add(GetBankDepositListFromLocalDB(selectDate, storeDTO, false)); } } } } catch (Exception ex) { log.Error("Exception in GetWeeklyDepositAmount method", ex); } return depositAmountList; }
// Update Current Business day ammount public void Update(DepositAmount amount, int storeId) { try { DataBaseUtility db = new DataBaseUtility(); string query = " update dbo.BankDeposit set DepositAmount = " + SQLUtility.getDouble(amount.DepositBankAmount) + " ,LastUpdateDateTime= '" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + " ' " + " where StoreId = " + SQLUtility.getInteger(storeId) + " and BusinessDate = '" + SQLUtility.FormateDateYYYYMMDD(amount.BusinessDate) + "' and WeekOfDay = " + SQLUtility.getString(amount.WeekOfDay) + " "; db.ExecuteUpdate(query); } catch (Exception ex) { log.Error("Exception in Update method ", ex); } }
// Get Bank Deposit Amount from local data base of week wise public DepositDTO GetBankDepositListFromLocalDB(DateTime selectDate, StoreDTO storeDTO, bool checkAgainToServer) { DepositDTO dto = null; try { ArrayList weekday = new ArrayList(); DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate); DateTime weekEndDate = weekStartDate.AddDays(6); DataBaseUtility db = new DataBaseUtility(); // ArrayList StoreList = GetStoreList(); // Check of only Current Date Week DateTime weekStartDateForCurrentDate = ValidationUtility.GetActualWeekStartDate(DateTime.Now); DateTime weekEndDateForCurrentDate = weekStartDate.AddDays(6); if (selectDate.Date >= weekStartDateForCurrentDate.Date && selectDate.Date <= weekEndDateForCurrentDate.Date) { if (checkAgainToServer) { List<DepositAmount> depositAmmountList = GetDepositAmountLocalDB(weekStartDate, weekEndDate, storeDTO.Id); if (depositAmmountList != null && depositAmmountList.Count > 0) { // Update Current Date Deposit Ammount DataTable dt = GetEachStoreDepositAmount(storeDTO.ConnectionString, selectDate); if (!ValidationUtility.IsNull(dt)) { DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); DateTime businessDate = new DateTime(); for (int i = 0; i < dt.Rows.Count; i++) { double depositAmount = 0; businessDate = ValidationUtility.ToDate(dt.Rows[i]["BusinessDate"].ToString()); if (businessDate.Date.Equals(currentDate.Date)) { depositAmount = ValidationUtility.ToDouble(dt.Rows[i]["DepositInBank"].ToString()); DepositAmount dammount = new DepositAmount { DepositBankAmount = depositAmount, BusinessDate = businessDate, WeekOfDay = businessDate.DayOfWeek.ToString() }; Update(dammount, storeDTO.Id); } } } } } } List<DepositAmount> depositUpdatedListAmmountList = GetDepositAmountLocalDB(weekStartDate, weekEndDate, storeDTO.Id); if (depositUpdatedListAmmountList != null && depositUpdatedListAmmountList.Count > 0) { dto = new DepositDTO { DepositAmount = depositUpdatedListAmmountList, StoreId = storeDTO.Id, StoreNumber = storeDTO.StoreNumber }; } } catch (Exception ex) { log.Error("Exception in GetBankDepositListFromLocalDB method ", ex); } return dto; }
// Get Name of week days and date public ArrayList GetDepositWeekInfo(DateTime selectDate) { ArrayList list = new ArrayList(); DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate); DateTime startDate = weekStartDate.AddDays(-1); for (int i = 0; i <= 6; i++) { DepositAmount dto = new DepositAmount { BusinessDateToString = weekStartDate.AddDays(i).ToString("MM/dd/yyyy"), WeekOfDay = weekStartDate.AddDays(i).DayOfWeek.ToString().Substring(0, 3).ToUpper() }; list.Add(dto); } return list; }
//This method is use for update depoisit amount of last business date public void UpdateDeposit() { log.Info(" UpdateDeposit Method is Called now for Deposit "+DateTime.Now); DepositModel dmodel = new DepositModel(); // ArrayList storeList = dmodel.GetStoreList(); ArrayList storeList = ValidationUtility.GetActiveStoreList(true); foreach (StoreDTO storeDTO in storeList) { DateTime lastDayDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day).AddDays(-1); DepositDTO depositDTO1 = GetBankDepositListFromLocalDB(lastDayDate, storeDTO); if (!ValidationUtility.IsNull(depositDTO1)) { DataTable dt = GetEachStoreDepositAmount(storeDTO.ConnectionString, lastDayDate); if (!ValidationUtility.IsNull(dt)) { DateTime businessDate = new DateTime(); for (int i = 0; i < dt.Rows.Count; i++) { double depositAmount = 0; businessDate = ValidationUtility.ToDate(dt.Rows[i]["BusinessDate"].ToString()); if (businessDate.Date.Equals(lastDayDate.Date)) { depositAmount = ValidationUtility.ToDouble(dt.Rows[i]["DepositInBank"].ToString()); DepositAmount dammount = new DepositAmount { DepositBankAmount = depositAmount, BusinessDate = businessDate, WeekOfDay = businessDate.DayOfWeek.ToString() }; Update(dammount, storeDTO.Id); } } } } // Add Week deposit ammount else { DataTable dt = GetEachStoreDepositAmount(storeDTO.ConnectionString, lastDayDate); ArrayList ammountList = new ArrayList(); if (!ValidationUtility.IsNull(dt)) { List<DepositAmount> depositAmmountList = new List<DepositAmount>(); DateTime businessDate = new DateTime(); for (int i = 0; i < dt.Rows.Count; i++) { double depositAmount = 0; businessDate = ValidationUtility.ToDate(dt.Rows[i]["BusinessDate"].ToString()); if (businessDate.Date <= ValidationUtility.GetActualWeekStartDate(lastDayDate).AddDays(7).Date) { depositAmount = ValidationUtility.ToDouble(dt.Rows[i]["DepositInBank"].ToString()); DepositAmount dammount = new DepositAmount { DepositBankAmount = depositAmount, BusinessDate = businessDate }; depositAmmountList.Add(dammount); } } DepositDTO depositDTO = new DepositDTO { StoreId = storeDTO.Id, DepositAmount = depositAmmountList }; dmodel.InsertDepositAmount(depositDTO); } } } log.Info(" UpdateDeposit Method Call is finish " + DateTime.Now); }
// GetLast Business day ammount public void Update(DepositAmount amount, int storeId) { DataBaseUtility db = new DataBaseUtility(); if (IsLastDepositRecordExsist(storeId, amount)) { log.Info("Recored is Exsist for storeID " + storeId); string query = " update dbo.BankDeposit set DepositAmount = " + SQLUtility.getDouble(amount.DepositBankAmount) + " ,LastUpdateDateTime= '" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + " ' " + " where StoreId = " + SQLUtility.getInteger(storeId) + " and BusinessDate = '" + SQLUtility.FormateDateYYYYMMDD(amount.BusinessDate) + "' and WeekOfDay = " + SQLUtility.getString(amount.WeekOfDay) + " "; db.ExecuteUpdate(query); } }
public bool IsLastDepositRecordExsist(int storeId, DepositAmount amount) { bool isLastBusinessDayRecord = false; DataBaseUtility db = new DataBaseUtility(); SqlConnection conn = null; try { string query = "select Id from dbo.BankDeposit where StoreId = " + SQLUtility.getInteger(storeId) + " and BusinessDate = '" + SQLUtility.FormateDateYYYYMMDD(amount.BusinessDate) + "' and WeekOfDay = " + SQLUtility.getString(amount.WeekOfDay) + " "; conn = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, conn); SqlDataReader reader = comm.ExecuteReader(); if (reader.Read()) { isLastBusinessDayRecord = true; } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error("Exception in IsLastDepositRecordExsist Method ", ex); } finally { db.CloseConnection(conn); } return isLastBusinessDayRecord; }