/// <summary>
///
/// </summary>
/// <param name="userName"></param>
/// <param name="key"></param>
/// <returns></returns>
public List <ModuleAccess> GetModuleDataForUser(string userName, string key)
{
List <ModuleAccess> moduleList = new List <ModuleAccess>();
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
if (key == null)
{
cmd.CommandText = "SELECT M.* FROM Module AS M"
+ " WHERE ((M.expiryDate > GETDATE())"
+ " OR (M.moduleId IN ("
+ " SELECT EX.moduleId FROM Exception AS EX JOIN Account AS A ON EX.accountId = A.accountId"
+ " WHERE A.userName= @userName"
+ " AND EX.expiryDate > GETDATE())))"
+ " AND (M.moduleId NOT IN ("
+ " SELECT AM.moduleId FROM Account_Module AS AM JOIN Account AS A ON A.accountId = AM.accountId"
+ " WHERE A.userName = @userName1));";
cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("@userName1", SqlDbType.VarChar).Value = userName;
}
else
{
cmd.CommandText = "SELECT M.* FROM Module AS M "
+ " WHERE ((M.moduleName LIKE @moduleName"
+ " AND M.expiryDate > GETDATE())"
+ " OR (M.moduleId IN ("
+ " SELECT EX.moduleId FROM Exception AS EX JOIN Account AS A ON EX.accountId = A.accountId"
+ " JOIN Module AS M ON EX.moduleId = M.moduleId"
+ " WHERE A.userName= @userName"
+ " AND M.moduleName LIKE @moduleName1"
+ " AND EX.expiryDate > GETDATE())))"
+ " AND (M.moduleId NOT IN ("
+ " SELECT AM.moduleId FROM Account_Module AS AM JOIN Account AS A ON A.accountId = AM.accountId"
+ " WHERE A.userName = @userName1));";
cmd.Parameters.Add("@moduleName", SqlDbType.VarChar).Value = "%" + key + "%";
cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("@moduleName1", SqlDbType.VarChar).Value = "%" + key + "%";
cmd.Parameters.Add("@userName1", SqlDbType.VarChar).Value = userName;
}
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
ModuleAccess module = new ModuleAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
module.ModuleId = 0;
}
else
{
module.ModuleId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
module.ModuleName = null;
}
else
{
module.ModuleName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
module.Description = null;
}
else
{
module.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
module.ExpiryDate = default(DateTime);
}
else
{
module.ExpiryDate = Convert.ToDateTime(dr[3]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
module.CreationDate = default(DateTime);
}
else
{
module.CreationDate = Convert.ToDateTime(dr[3]);
}
moduleList.Add(module);
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
return(moduleList);
}
public bool UpdateModule(ModuleAccess module, AgreementInfoAccess agreement)
{
ConnectDB db = new ConnectDB();
SqlCommand cmd;
cmd = new SqlCommand("UpdateModule");
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = cmd.Parameters.Add("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
//@moduleName VARCHAR(255) ,
// @description VARCHAR(255) = NULL,
// @expiryDate DATETIME = NULL,
// @creationDate DATETIME = null,
// @userType VARCHAR(255),
// @content VARCHAR(2048) = NULL
SqlParameter moduleId = cmd.Parameters.Add("@moduleId", SqlDbType.Int);
moduleId.Direction = ParameterDirection.Input;
SqlParameter moduleName = cmd.Parameters.Add("@moduleName", SqlDbType.VarChar, 255);
moduleName.Direction = ParameterDirection.Input;
SqlParameter description = cmd.Parameters.Add("@description", SqlDbType.VarChar, 255);
description.Direction = ParameterDirection.Input;
SqlParameter expiryDate = cmd.Parameters.Add("@expiryDate", SqlDbType.DateTime);
expiryDate.Direction = ParameterDirection.Input;
SqlParameter creationDate = cmd.Parameters.Add("@creationDate", SqlDbType.DateTime);
creationDate.Direction = ParameterDirection.Input;
SqlParameter userType = cmd.Parameters.Add("@userType", SqlDbType.VarChar, 255);
userType.Direction = ParameterDirection.Input;
SqlParameter content = cmd.Parameters.Add("@content", SqlDbType.VarChar, 2048);
content.Direction = ParameterDirection.Input;
if (module.ModuleId != 0)
{
return(false);
}
moduleId.Value = module.ModuleId;
if (module.ModuleName != null)
{
moduleName.Value = module.ModuleName;
}
if (module.Description != null)
{
description.Value = module.Description;
}
if (module.ExpiryDate != null)
{
expiryDate.Value = module.ExpiryDate;
}
creationDate.Value = DateTime.Now;
if (agreement.UserType != null)
{
userType.Value = agreement.UserType;
}
if (agreement.Content != null)
{
content.Value = agreement.Content;
}
db.changeData(cmd);
db.Close();
if (Int32.Parse(RetVal.Value.ToString()) == 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public List <ModuleAccess> GetModuleData(string key = null)
{
List <ModuleAccess> moduleList = new List <ModuleAccess>();
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
if (key == null)
{
cmd.CommandText = "SELECT moduleId, moduleName, description, expiryDate, creationDate FROM Module; ";
}
else
{
cmd.CommandText = "SELECT moduleId, moduleName, description, expiryDate, creationDate FROM Module WHERE moduleName LIKE @moduleName;";
cmd.Parameters.Add("@moduleName", SqlDbType.VarChar).Value = "%" + key + "%";
}
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
ModuleAccess module = new ModuleAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
module.ModuleId = 0;
}
else
{
module.ModuleId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
module.ModuleName = null;
}
else
{
module.ModuleName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
module.Description = null;
}
else
{
module.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
module.ExpiryDate = default(DateTime);
}
else
{
module.ExpiryDate = Convert.ToDateTime(dr[3]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
module.CreationDate = default(DateTime);
}
else
{
module.CreationDate = Convert.ToDateTime(dr[3]);
}
moduleList.Add(module);
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
return(moduleList);
}
