public List <QI_REPORT_REC> GetQiReportsCollection()
{
List <QI_REPORT_REC> reportsCollection = new List <QI_REPORT_REC>();
using (SqlConnection dbConn = m_SqlHelpers.GetDbConnection(m_AwareDbConnString))
{
string sqlText = "usp_GetReportsInfo";
SqlCommand sqlCmd = new SqlCommand(sqlText, dbConn);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@ObjectTypeId", (int)AwareObjectTypes.REPORT_DEFINITIONS);
SqlDataReader dr = sqlCmd.ExecuteReader();
while (dr.Read())
{
try
{
QI_REPORT_REC rec = new QI_REPORT_REC(dr.GetGuid(0), dr.GetString(1), dr.GetString(2));
reportsCollection.Add(rec);
}
catch (InvalidCastException ex)
{
}
}
dr.Close();
dbConn.Close();
}
return(reportsCollection);
}
public string GetLocalUserName(Guid userId)
{
string user = string.Empty;
using (SqlConnection dbConn = m_SqlHelpers.GetDbConnection(m_AwareDbConnString))
{
string sqlText = "usp_GetUserNameFromUserId";
SqlCommand sqlCmd = new SqlCommand(sqlText, dbConn);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@UserId", userId);
user = (string)sqlCmd.ExecuteScalar();
dbConn.Close();
}
return(user);
}
public List <QI_FACILITY_REC> GetFacilitiesCollection()
{
List <QI_FACILITY_REC> facilitiesCollection = new List <QI_FACILITY_REC>();
using (SqlConnection dbConn = m_SqlHelpers.GetDbConnection(m_AwareDbConnString))
{
string sqlText = "usp_GetFacilities";
SqlCommand sqlCmd = new SqlCommand(sqlText, dbConn);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader dr = sqlCmd.ExecuteReader();
while (dr.Read())
{
try
{
QI_FACILITY_REC rec = new QI_FACILITY_REC();
try { rec.ID = dr.GetGuid(0); }
catch (SqlNullValueException Exception) { rec.ID = new Guid(); }
try { rec.Name = dr.GetString(1); }
catch (SqlNullValueException ex) { rec.Name = string.Empty; }
facilitiesCollection.Add(rec);
}
catch (InvalidCastException ex)
{
}
}
dr.Close();
dbConn.Close();
}
return(facilitiesCollection);
}
public bool DoesSecurableExists(string secItem, int objType)
{
bool exists = false;
using (SqlConnection awareDbConn = m_SqlHelpers.GetDbConnection(m_AwareDbConnString))
{
string sqlText = "usp_DoesSecurityItemExist";
SqlCommand sqlCmd = new SqlCommand(sqlText, awareDbConn);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@ObjectTypeId", objType);
sqlCmd.Parameters.AddWithValue("@ObjectName", secItem);
int cnt = (int)sqlCmd.ExecuteScalar();
awareDbConn.Close();
if (cnt > 0)
{
exists = true;
}
}
return(exists);
}
private string _GetLastUpdateTimeDate()
{
string luDtTime = string.Empty;
SqlConnection dbConn = m_SqlHelpers.GetDbConnection(m_AppSettings.GetAwareDbConnectionString);
SqlCommand sql = new SqlCommand();
sql.CommandText = "SELECT MAX(LAST_UPDATE) FROM SQLTRX_LAST_UPDATE";
sql.Connection = dbConn;
try
{
DateTime luDT = (DateTime)sql.ExecuteScalar();
luDtTime = luDT.ToString();
}
catch (SqlException ex)
{
}
catch (InvalidCastException ex)
{
}
return(luDtTime);
}
public List <QI_GROUP_REC> GetQiGroupCollection()
{
List <QI_GROUP_REC> qiGroupCollection = new List <QI_GROUP_REC>();
using (SqlConnection dbConn = m_SqlHelpers.GetDbConnection(m_AwareDbConnString))
{
string sqlText = "usp_GetQIGroups";
SqlCommand sqlCmd = new SqlCommand(sqlText, dbConn);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader dr = sqlCmd.ExecuteReader();
while (dr.Read())
{
try
{
QI_GROUP_REC rec = new QI_GROUP_REC(dr.GetGuid(0), dr.GetString(1), dr.GetBoolean(2));
qiGroupCollection.Add(rec);
}
catch (InvalidCastException ex)
{
}
}
dr.Close();
dbConn.Close();
}
return(qiGroupCollection);
}
public QI_VISTA_GRP_REC GetVistaGroupById(Guid VistaGroupId)
{
QI_VISTA_GRP_REC vistaGroup = new QI_VISTA_GRP_REC();
using (SqlConnection dbConn = m_SqlHelpers.GetDbConnection(m_AwareDbConnString))
{
string sqlText = "usp_GetVistaGroupById";
SqlCommand sqlCmd = new SqlCommand(sqlText, dbConn);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@VistaGroupId", VistaGroupId);
SqlDataReader dr = sqlCmd.ExecuteReader();
while (dr.Read())
{
try
{
try { vistaGroup.VistaGrpID = dr.GetGuid(0); }
catch (SqlNullValueException Exception) { vistaGroup.VistaGrpID = new Guid(); }
try { vistaGroup.Name = dr.GetString(1); }
catch (SqlNullValueException ex) { vistaGroup.Name = string.Empty; }
try { vistaGroup.AwareGrpId = dr.GetGuid(2); }
catch (SqlNullValueException ex) { vistaGroup.AwareGrpId = new Guid(); }
try { vistaGroup.FacilityId = dr.GetGuid(3); }
catch (SqlNullValueException ex) { vistaGroup.FacilityId = new Guid(); }
}
catch (InvalidCastException ex)
{
}
}
dr.Close();
dbConn.Close();
}
return(vistaGroup);
}
public List <QI_USER_REC> GetQiUsersCollection(Guid facilityId)
{
List <QI_USER_REC> userCollection = new List <QI_USER_REC>();
userCollection.Clear();
using (SqlConnection dbConn = m_SqlHelpers.GetDbConnection(m_AwareDbConnString))
{
string sqlText = "usp_SelectUserRecsByFacID";
SqlCommand sqlCmd = new SqlCommand(sqlText, dbConn);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@FacilityId", facilityId);
SqlDataReader dr = sqlCmd.ExecuteReader();
while (dr.Read())
{
QI_USER_REC rec = new QI_USER_REC();
try
{
rec.UserID = dr.GetGuid(0);
try{ rec.UserName = dr.GetString(1); }
catch (SqlNullValueException ex) { rec.UserName = string.Empty; }
try { rec.FacilityId = dr.GetGuid(2); }
catch (SqlNullValueException ex) { rec.FacilityId = new Guid(); }
userCollection.Add(rec);
}
catch (InvalidCastException ex)
{
}
}
dr.Close();
dbConn.Close();
}
return(userCollection);
}
public bool VerifyUserCredentials(string userName, string verifyCode)
{
bool verified = false;
using (SqlConnection awareDbConn = m_SqlHelpers.GetDbConnection(m_AwareDbConnString))
{
string sqlText = "usp_VerifyQiUserCredentials";
SqlCommand sqlCmd = new SqlCommand(sqlText, awareDbConn);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@UserName", userName);
sqlCmd.Parameters.AddWithValue("@VerifyCode", verifyCode);
int cnt = (int)sqlCmd.ExecuteScalar();
awareDbConn.Close();
if (cnt > 0)
{
verified = true;
}
}
return(verified);
}
