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