//Add Inactive Store
public void AddInactiveStore(StoreDTO storeDTO)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[Store] ([StoreNumber] ,[StoreName] ,[ConnectionString] ,[IsStoreActive] ,[CreatedDateTime] ,[LastUpdateDateTime]) VALUES(" + SQLUtility.getInteger(storeDTO.StoreNumber) + " , "
+ "" + SQLUtility.getString(storeDTO.StoreName) + "," + SQLUtility.getString(storeDTO.ConnectionString) + ",'" + storeDTO.IsActive + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
db.ExecuteUpdate(query);
}
public JsonResult Addstore(string snumber, string sname, string connstring)
{
try
{
StoreModel sm = new StoreModel();
if (!ValidationUtility.IsNull(snumber) && !ValidationUtility.IsNull(sname))
{
int storeNumber = ValidationUtility.ToInteger(snumber);
bool flag = sm.IsStoreExist(storeNumber, sname);
if (!flag)
{
StoreDTO storeDTO = new StoreDTO();
storeDTO.StoreNumber = storeNumber;
storeDTO.StoreName = sname;
storeDTO.ConnectionString = connstring;
sm.AddInactiveStore(storeDTO);
string msg = "Recored Added Successfully";
return Json(msg, JsonRequestBehavior.AllowGet);
//if (!ValidationUtility.IsNull(connstring))
//{
// sm.AddStore(storeNumber, sname, connstring);
// string msg = "Recored Added Successfully";
// return Json(msg, JsonRequestBehavior.AllowGet);
//}
//else
//{
// StoreDTO storeDTO = new StoreDTO();
// storeDTO.StoreNumber = storeNumber;
// storeDTO.StoreName = sname;
// sm.AddInactiveStore(storeDTO);
//}
}
else
{
string msg = "Store Already Exist";
return Json(msg, JsonRequestBehavior.AllowGet);
}
}
}
catch (Exception ex)
{
}
string msg1 = " Recored Not Added Successfully ";
return Json(msg1, JsonRequestBehavior.AllowGet);
}
//Code for Area manager
// Get AreaManager AssignStore Information
public ArrayList GetAreaMngAssignStore(int userId)
{
ArrayList assigneStore = new ArrayList();
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
try
{
string query = "select su.Id,s.StoreName,s.Id from dbo.StoreUser su, dbo.Store s where s.Id = su.StoreId and su.UserId = " + SQLUtility.getInteger(userId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int uId = ValidationUtility.ToInteger(reader[0].ToString());
string storeName = reader[1].ToString();
int sId = ValidationUtility.ToInteger(reader[2].ToString());
StoreDTO dto = new StoreDTO { Id = uId, StoreName = storeName, StoreId = sId };
assigneStore.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetAreaMngAssignStore Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return assigneStore;
}
// Get All StoreList from local database
public ArrayList GetStoreList()
{
ArrayList collection = new ArrayList();
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
try
{
string query = "select Id,StoreNumber,StoreName,ConnectionString from dbo.Store ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
int sNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());
string strorName = reader["StoreName"].ToString();
string connectionString = reader["ConnectionString"].ToString();
StoreDTO dto = new StoreDTO { Id = id, StoreNumber = sNumber, StoreName = strorName, ConnectionString = connectionString };
collection.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetStoreList Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return collection;
}
// 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 Bank Deposit Amount from local data base of week wise
public DepositDTO GetBankDepositListFromLocalDB(DateTime selectDate, StoreDTO storeDTO)
{
// ArrayList allDepositList = new ArrayList();
ArrayList weekday = new ArrayList();
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);
DateTime weekEndDate = weekStartDate.AddDays(6);
DataBaseUtility db = new DataBaseUtility();
DepositModel dmodel = new DepositModel();
// ArrayList StoreList = dmodel.GetStoreList();
ArrayList StoreList = ValidationUtility.GetActiveStoreList(true);
List<DepositAmount> depositAmmountList = GetDepositAmountLocalDB(weekStartDate, weekEndDate, storeDTO.Id);
DepositDTO dto = null;
if (depositAmmountList != null && depositAmmountList.Count > 0)
{
dto = new DepositDTO { DepositAmount = depositAmmountList, StoreId = storeDTO.StoreNumber };
//allDepositList.Add(dto);
}
return dto;
}
public JsonResult UpdateInActiveStore(string id, string storeName, string connectionString)
{
string error = "";
string msg = "";
try
{
StoreModel sm = new StoreModel();
if (!ValidationUtility.IsNull(id))
{
int sId = ValidationUtility.ToInteger(id);
StoreDTO dto = new StoreDTO();
dto.Id = sId;
dto.StoreName = storeName;
dto.ConnectionString = connectionString;
dto.IsActive = true;
sm.AddStore(dto);
error = "Success";
msg = "Store Connected Successfully";
}
}
catch (Exception ex)
{
log.Error("Exception in UpdateInActiveStore method",ex);
error = "Error";
msg = " Store is not connected.";
}
ResponseDTO resDTO = new ResponseDTO { Error = error, Message = msg };
return Json(resDTO, JsonRequestBehavior.AllowGet);
}
// 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;
}
}
public void UpdateStore(StoreDTO dto)
{
DataBaseUtility db = new DataBaseUtility();
string query = "update Store set StoreName = " + SQLUtility.getString(dto.StoreName) + " , ConnectionString = " + SQLUtility.getString(dto.ConnectionString) + " , "
+ " IsStoreActive = '" + dto.IsActive + "' , LastUpdateDateTime= '" + SQLUtility.FormateDateYYYYMMDD(DateTime.Now) + "' where Id = " + SQLUtility.getInteger(dto.Id) + " ";
db.ExecuteUpdate(query);
}
//Get Corporate User Assign Store
public static ArrayList GetManagerAssignStore(int userId, bool IsConnectionStringReq)
{
ArrayList collection = new ArrayList();
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
try
{
string query = "select * from dbo.Store where Id in(select su.StoreId from dbo.StoreUser su where su.UserId =" + SQLUtility.getInteger(userId) + ") ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
int sNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());
string strorName = reader["StoreName"].ToString();
string connectionString = null;
if (IsConnectionStringReq)
{
connectionString = reader["ConnectionString"].ToString();
}
StoreDTO dto = new StoreDTO { Id = id, StoreNumber = sNumber, StoreName = strorName, ConnectionString = connectionString };
collection.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetStoreList Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return collection;
}