public void AddRequest(string id, string desc, string storeId)
{
MaintenanceModel md = new MaintenanceModel();
MaintenanceDTO dto = new MaintenanceDTO();
UserContext cont = (UserContext)Session["UserContext"];
if (!ValidationUtility.IsNull(cont))
{
if (ValidationUtility.IsEqual("AreaManager", cont.Role))
{
int storId = ValidationUtility.ToInteger(storeId);
dto.Status = "pending";
dto.StoreId = storId;
dto.CategoryId = ValidationUtility.ToInteger(id);
dto.Description = desc;
md.AddMaintenanceRequest(dto);
}
if (ValidationUtility.IsEqual("GeneralManager", cont.Role))
{
int storId = ValidationUtility.ToInteger(storeId);
dto.Status = "pending";
dto.StoreId = storId;
dto.CategoryId = ValidationUtility.ToInteger(id);
dto.Description = desc;
md.AddMaintenanceRequest(dto);
}
}
}
public void AddMaintenanceRequest(MaintenanceDTO dto)
{
// string query = "insert into StoreMaintainance "
try
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[StoreMaintainance]([StoreId],[MaintenanceCategoryId],[Description],[status],[DateCreated]) VALUES "
+ "(" + SQLUtility.getInteger(dto.StoreId) + ", " + SQLUtility.getInteger(dto.CategoryId) + " ," + SQLUtility.getString(dto.Description) + " , "
+ " "+SQLUtility.getString(dto.Status)+" , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' )";
db.ExecuteUpdate(query);
}
catch (Exception ex)
{
log.Error(" Exception in AddMaintenanceRequest Method ", ex);
}
}
public void AddRequest(string sId, string id, string desc)
{
MaintenanceModel md = new MaintenanceModel();
MaintenanceDTO dto = new MaintenanceDTO();
UserContext cont = (UserContext)Session["UserContext"];
if (!ValidationUtility.IsNull(cont))
{
if (ValidationUtility.IsEqual("Manager", cont.Role))
{
int storId = md.GetStoredId(cont.Id);
dto.Status = "pending";
dto.StoreId = storId;
dto.CategoryId = ValidationUtility.ToInteger(id);
dto.Description = desc;
md.AddMaintenanceRequest(dto);
}
else if (ValidationUtility.IsEqual("Admin", cont.Role) || ValidationUtility.IsEqual("Corporate", cont.Role) || ValidationUtility.IsEqual("Maintenance", cont.Role))
{
//dto.Status = "pending";
//dto.CategoryId = ValidationUtility.ToInteger(id);
//dto.Description = desc;
//md.AddUserStore(ValidationUtility.ToInteger(sId), cont.Id);
//int storId = md.GetStoredId(cont.Id);
//dto.StoreId = storId;
//md.AddMaintenanceRequest(dto);
dto.Status = "pending";
dto.CategoryId = ValidationUtility.ToInteger(id);
dto.Description = desc;
// md.AddUserStore(ValidationUtility.ToInteger(sId), cont.Id);
// int storId = md.GetStoredId(cont.Id);
dto.StoreId = ValidationUtility.ToInteger(sId);
md.AddMaintenanceRequest(dto);
}
}
}
public void UpdateMaintenanceRequest(MaintenanceDTO dto, bool isAdmin)
{
DataBaseUtility db = new DataBaseUtility();
try
{
if (isAdmin)
{
string query = " update dbo.StoreMaintainance set MaintenanceCategoryId =" + SQLUtility.getInteger(dto.CategoryId) + ",Description = " + SQLUtility.getString(dto.Description) + " , status=" + SQLUtility.getString(dto.Status) + ",DateResolved='" + dto.DateResolved.ToString("yyyy/MM/dd hh:mm tt") + "' "
+ " where Id = " + SQLUtility.getInteger(dto.Id) + " ";
db.ExecuteUpdate(query);
}
else
{
string query = " update dbo.StoreMaintainance set MaintenanceCategoryId =" + SQLUtility.getInteger(dto.CategoryId) + ",Description = " + SQLUtility.getString(dto.Description) + " "
+ " where Id = " + SQLUtility.getInteger(dto.Id) + " ";
db.ExecuteUpdate(query);
}
}
catch(Exception ex)
{
log.Error(" Exception in UpdateMaintenanceRequest Method ", ex);
}
}
public ArrayList GetMaintenanceList(int userId)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
try
{
string query = "";
if (userId == 0)
{
//query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc "
// + " where sm.MaintenanceCategoryId = mc.Id order by sm.Id desc ";
//query = "select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , s.Id,s.StoreName "
// + " from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc ,dbo.StoreUser su, dbo.Store s "
// + " where sm.MaintenanceCategoryId = mc.Id and su.Id = sm.StoreId and s.Id = su.StoreId order by sm.Id desc ";
query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , sm.StoreId from "
+ " dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc where "
+ " sm.MaintenanceCategoryId = mc.Id order by sm.Id desc ";
}
else
{
//query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc "
// + " where sm.MaintenanceCategoryId = mc.Id and sm.StoreId in (select Id from dbo.StoreUser where UserId=" + SQLUtility.getInteger(userId) + " ) order by sm.Id desc ";
//query = "select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , s.Id,s.StoreName "
// + " from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc ,dbo.StoreUser su, dbo.Store s "
// + " where sm.MaintenanceCategoryId = mc.Id and su.Id = sm.StoreId and s.Id = su.StoreId and "
// + " sm.StoreId in (select su.Id from dbo.StoreUser where su.UserId = " + SQLUtility.getInteger(userId) + ") order by sm.Id desc ";
query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , sm.StoreId "
+ " from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc "
+ " where sm.MaintenanceCategoryId = mc.Id and "
+ " sm.StoreId in (select su.StoreId from dbo.StoreUser su where su.UserId = " + SQLUtility.getInteger(userId) + ") order by sm.Id desc ";
}
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
DateTime resolvDate = new DateTime();
int id = ValidationUtility.ToInteger(reader[0].ToString());
string des = reader[1].ToString();
string status = reader[2].ToString();
DateTime creatDate = ValidationUtility.ToDate(reader[3].ToString());
int cId = ValidationUtility.ToInteger(reader[5].ToString());
string item = reader[6].ToString();
int rank = ValidationUtility.ToInteger(reader[7].ToString());
int storeId = ValidationUtility.ToInteger(reader[8].ToString());
//string storeName = reader[9].ToString();
MaintenanceDTO dto = null;
if (ValidationUtility.IsEqual("resolved", status))
{
resolvDate = ValidationUtility.ToDate(reader[4].ToString());
dto = new MaintenanceDTO
{
Id = id,
Description = des,
Status = status,
CreateDateTimeInString = creatDate.ToString("MMM/dd/yyyy"),
ResolveDateTimeInString = resolvDate.ToString("MMM/dd/yyyy"),
CategoryId = cId,
ItemName = item,
Rank = rank,
StoreId = storeId,
//StoreName = storeName
};
}
else
{
dto = new MaintenanceDTO
{
Id = id,
Description = des,
Status = status,
CreateDateTimeInString = creatDate.ToString("MMM/dd/yyyy"),
ResolveDateTimeInString = "",
CategoryId = cId,
ItemName = item,
Rank = rank,
StoreId = storeId,
//StoreName = storeName
};
}
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetMaintenanceList Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
public ArrayList GetList()
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
try
{
string query = " select Id , ItemName,Rank from dbo.MaintenanceCategory ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader[0].ToString());
string name = reader[1].ToString();
int rank = ValidationUtility.ToInteger(reader[2].ToString());
MaintenanceDTO dto = new MaintenanceDTO { Id = id, ItemName = name, Rank = rank };
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetList Method ",ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
public void UpdateRequest(string id, string desc,string itemName)
{
MaintenanceModel md = new MaintenanceModel();
MaintenanceDTO dto = new MaintenanceDTO();
dto.Id = ValidationUtility.ToInteger(id);
dto.CategoryId = md.GetCategoryId(itemName);
dto.Description = desc;
md.UpdateMaintenanceRequest(dto, false);
}
public void UpdateRequest(string id, string desc, string status, string itemName, string resDate)
{
MaintenanceModel md = new MaintenanceModel();
MaintenanceDTO dto = new MaintenanceDTO();
dto.Id = ValidationUtility.ToInteger(id);
dto.CategoryId = md.GetCategoryId(itemName);
dto.Description = desc;
dto.Status = status;
dto.DateResolved = ValidationUtility.ToDate(resDate);
md.UpdateMaintenanceRequest(dto, true);
}
