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