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