public static List <T_OE_USERS> GetT_OE_USERSNotInRole(int roleID)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
//first get all users
var allUsers = (from itemA in ctx.T_OE_USERS select itemA);
//next get all users in role
var UsersInRole = (from itemA in ctx.T_OE_USERS
join itemB in ctx.T_OE_USER_ROLES on itemA.USER_IDX equals itemB.USER_IDX
where itemB.ROLE_IDX == roleID
select itemA);
//then get exclusions
var usersNotInRole = allUsers.Except(UsersInRole);
return(usersNotInRole.OrderBy(a => a.USER_ID).ToList());
}
catch (Exception ex)
{
throw ex;
}
}
}
//***************************** ATTAINS_ASSESS **************************************
public static List <AssessDisplay> GetT_ATTAINS_ASSESS_byReportID(int ReportID)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return((from a in ctx.T_ATTAINS_ASSESS
join b in ctx.T_ATTAINS_ASSESS_UNITS on a.ATTAINS_ASSESS_UNIT_IDX equals b.ATTAINS_ASSESS_UNIT_IDX
where b.ATTAINS_REPORT_IDX == ReportID
select new AssessDisplay {
ATTAINS_ASSESS_IDX = a.ATTAINS_ASSESS_IDX,
REPORTING_CYCLE = a.REPORTING_CYCLE,
REPORT_STATUS = a.REPORT_STATUS,
ATTAINS_ASSESS_UNIT_IDX = a.ATTAINS_ASSESS_UNIT_IDX,
ASSESS_UNIT_NAME = b.ASSESS_UNIT_NAME,
AGENCY_CODE = a.AGENCY_CODE,
CYCLE_LAST_ASSESSED = a.CYCLE_LAST_ASSESSED,
CYCLE_LAST_MONITORED = a.CYCLE_LAST_MONITORED
}).ToList());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static int DeleteT_ATTAINS_REPORT(int aTTAINS_REPORT_IDX)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_ATTAINS_REPORT r = (from c in ctx.T_ATTAINS_REPORT
where c.ATTAINS_REPORT_IDX == aTTAINS_REPORT_IDX
select c).FirstOrDefault();
if (r.ATTAINS_SUBMIT_STATUS == "Y" || r.ATTAINS_SUBMIT_STATUS == "U")
{
return(-1);
}
ctx.DeleteObject(r);
ctx.SaveChanges();
return(1);
}
catch
{
return(0);
}
}
}
public static int UpdateT_VCCB_ROLE(int idx, string newROLE_NAME, string newROLE_DESC, string newMODIFY_USR)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_OE_ROLES row = new T_OE_ROLES();
row = (from c in ctx.T_OE_ROLES where c.ROLE_IDX == idx select c).First();
if (newROLE_NAME != null)
{
row.ROLE_NAME = newROLE_NAME;
}
if (newROLE_DESC != null)
{
row.ROLE_DESC = newROLE_DESC;
}
if (newMODIFY_USR != null)
{
row.MODIFY_USERID = newMODIFY_USR;
}
row.MODIFY_DT = System.DateTime.Now;
ctx.SaveChanges();
return(1);
}
catch (Exception ex)
{
return(0);
}
}
}
//*****************USERS **********************************
public static int CreateT_OE_USERS(global::System.String uSER_ID, global::System.String pWD_HASH, global::System.String pWD_SALT, global::System.String fNAME, global::System.String lNAME, global::System.String eMAIL, global::System.Boolean aCT_IND, global::System.Boolean iNITAL_PWD_FLAG, global::System.DateTime?lASTLOGIN_DT, global::System.String pHONE, global::System.String pHONE_EXT, global::System.String cREATE_USER)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_OE_USERS u = new T_OE_USERS();
u.USER_ID = uSER_ID;
u.PWD_HASH = pWD_HASH;
u.PWD_SALT = pWD_SALT;
u.FNAME = fNAME;
u.LNAME = lNAME;
u.EMAIL = eMAIL;
u.ACT_IND = aCT_IND;
u.INITAL_PWD_FLAG = iNITAL_PWD_FLAG;
u.EFFECTIVE_DT = System.DateTime.Now;
u.LASTLOGIN_DT = lASTLOGIN_DT;
u.PHONE = pHONE;
u.PHONE_EXT = pHONE_EXT;
u.CREATE_DT = System.DateTime.Now;
u.CREATE_USERID = cREATE_USER;
ctx.AddToT_OE_USERS(u);
ctx.SaveChanges();
return(u.USER_IDX);
}
catch (Exception ex)
{
return(0);
}
}
}
public override bool ChangePassword(string username, string oldPassword, string newPassword)
{
//validate new password length
if (!Utils.ValidateParameter(ref newPassword, true, true, false, 0, _MinRequiredPasswordLength))
{
return(false);
}
//Validate Non-AlphaNumeric characters
char[] charpwd = newPassword.ToCharArray();
int pwdNonNumericCount = 0;
for (int i = 1; i < newPassword.Length; i++)
{
if (!char.IsLetterOrDigit(charpwd[i]))
{
pwdNonNumericCount += 1;
}
}
if (pwdNonNumericCount < _MinRequiredNonalphanumericCharacters)
{
return(false);
}
using (OpenEnvironmentEntities context = new OpenEnvironmentEntities())
{
T_OE_USERS u = db_Accounts.GetT_OE_USERSByID(username);
if (u != null)
{
//first check accuracy of old password
if (!CheckPassword(oldPassword, u.PWD_HASH, u.PWD_SALT))
{
return(false);
}
//generate new password
string salt = GenerateSalt();
string hashpass = HashPassword(newPassword, MembershipPasswordFormat.Hashed, salt);
//save updated information
if (db_Accounts.UpdateT_OE_USERS(u.USER_IDX, hashpass, salt, null, null, null, null, false, null, null, null, null, "system") > 0)
{
return(true);
}
else
{
return(false);
}
}
}
return(true);
}
public static T_OE_USERS GetT_VCCB_USERByEmail(string email)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return(ctx.T_OE_USERS.FirstOrDefault(usr => usr.EMAIL.ToUpper() == email.ToUpper()));
}
catch (Exception ex)
{
throw ex;
}
}
}
public static List <T_OE_USERS> GetT_OE_USERS()
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return(ctx.T_OE_USERS.ToList());
}
catch (Exception ex)
{
throw ex;
}
}
}
//***************************** SP ***********************************************
public static string SP_GenATTAINSXML(int reportIDX)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return(ctx.GenATTAINSXML(reportIDX).First());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static T_OE_USERS GetT_OE_USERSByIDX(int idx)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return(ctx.T_OE_USERS.FirstOrDefault(usr => usr.USER_IDX == idx));
}
catch (Exception ex)
{
throw ex;
}
}
}
public static T_OE_ROLES GetT_VCCB_ROLEByIDX(int idx)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return(ctx.T_OE_ROLES.FirstOrDefault(role => role.ROLE_IDX == idx));
}
catch (Exception ex)
{
throw ex;
}
}
}
public static T_OE_ROLES GetT_VCCB_ROLEByName(string rolename)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return(ctx.T_OE_ROLES.FirstOrDefault(role => role.ROLE_NAME.ToUpper() == rolename.ToUpper()));
}
catch (Exception ex)
{
throw ex;
}
}
}
//***************************** ATTAINS_REF_WATER_TYPE **************************************
public static List <T_ATTAINS_REF_WATER_TYPE> GetT_ATTAINS_REF_WATER_TYPE()
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return((from a in ctx.T_ATTAINS_REF_WATER_TYPE
orderby a.WATER_TYPE_CODE
select a).ToList());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static T_ATTAINS_REPORT GetT_ATTAINS_REPORT_byID(int ReportID)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return((from a in ctx.T_ATTAINS_REPORT
where a.ATTAINS_REPORT_IDX == ReportID
select a).FirstOrDefault());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static T_ATTAINS_ASSESS_UNITS GetT_ATTAINS_ASSESS_UNITS_byID(int?AssessUnitID)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return((from a in ctx.T_ATTAINS_ASSESS_UNITS
where a.ATTAINS_ASSESS_UNIT_IDX == AssessUnitID
select a).FirstOrDefault());
}
catch (Exception ex)
{
throw ex;
}
}
}
//***************************** ATTAINS_REPORT ************************************************
public static List <T_ATTAINS_REPORT> GetT_ATTAINS_REPORT_byORG_ID(string OrgID)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return((from a in ctx.T_ATTAINS_REPORT
where a.ORG_ID == OrgID
select a).ToList());
}
catch (Exception ex)
{
throw ex;
}
}
}
//***************************** ATTAINS_ASSESSMENT_UNIT **************************************
public static List <T_ATTAINS_ASSESS_UNITS> GetT_ATTAINS_ASSESS_UNITS_byReportID(int ReportID)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return((from a in ctx.T_ATTAINS_ASSESS_UNITS
where a.ATTAINS_REPORT_IDX == ReportID
select a).ToList());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static List <T_WQX_MONLOC> GetT_ATTAINS_ASSESS_UNITS_MLOC_byAssessUnit(int?AssessUnitID)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
return((from a in ctx.T_ATTAINS_ASSESS_UNITS_MLOC
join b in ctx.T_WQX_MONLOC on a.MONLOC_IDX equals b.MONLOC_IDX
where a.ATTAINS_ASSESS_UNIT_IDX == AssessUnitID
select b).ToList());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static int DeleteT_VCCB_ROLE(int idx)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_OE_ROLES row = new T_OE_ROLES();
row = (from c in ctx.T_OE_ROLES where c.ROLE_IDX == idx select c).First();
ctx.DeleteObject(row);
ctx.SaveChanges();
return(1);
}
catch (Exception ex)
{
return(0);
}
}
}
public static List <T_OE_ROLES> GetT_OE_ROLESInUser(int userIDX)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
var roles = from itemA in ctx.T_OE_ROLES
join itemB in ctx.T_OE_USER_ROLES on itemA.ROLE_IDX equals itemB.ROLE_IDX
where itemB.USER_IDX == userIDX
select itemA;
return(roles.ToList());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static int DeleteT_ATTAINS_ASSESS_UNITS(int aSSESS_UNIT_IDX)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_ATTAINS_ASSESS_UNITS r = (from c in ctx.T_ATTAINS_ASSESS_UNITS
where c.ATTAINS_ASSESS_UNIT_IDX == aSSESS_UNIT_IDX
select c).FirstOrDefault();
ctx.DeleteObject(r);
ctx.SaveChanges();
return(1);
}
catch
{
return(0);
}
}
}
public override string ResetPassword(string username, string answer)
{
using (OpenEnvironmentEntities context = new OpenEnvironmentEntities())
{
T_OE_USERS u = db_Accounts.GetT_OE_USERSByID(username);
if (u != null)
{
//generate new password
string newPass = RandomString(8);
string salt = GenerateSalt();
string hashpass = HashPassword(newPass, _PasswordFormat, salt);
//save updated information
if (db_Accounts.UpdateT_OE_USERS(u.USER_IDX, hashpass, salt, null, null, null, null, true, null, null, null, null, "system") > 0)
{
//send email
string msg = "Your password has been reset. Your new temporary password is: " + "\r\n\r\n";
msg += newPass + "\r\n\r\n";
msg += "When you login for the first time you will be asked to set a permanent password.";
if (u.EMAIL == null)
{
return("User does not have email address.");
}
if (Utils.SendEmail(null, u.EMAIL.Split(';').ToList(), null, null, "Open Waters Password Reset", msg, null))
{
return("Email has been sent.");
}
else
{
return("Error in sending email");
}
}
else
{
return("Error updating password");
}
}
else
{
return("Email does not exist in the system.");
}
}
}
public static int DeleteT_VCCB_USER_ROLE(int UserIDX, int RoleIDX)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_OE_USER_ROLES row = new T_OE_USER_ROLES();
row = (from c in ctx.T_OE_USER_ROLES
where c.ROLE_IDX == RoleIDX && c.USER_IDX == UserIDX
select c).FirstOrDefault();
ctx.DeleteObject(row);
ctx.SaveChanges();
return(1);
}
catch (Exception ex)
{
return(0);
}
}
}
//*****************ROLE / USER RELATIONSHIP **********************************
public static List <T_OE_USERS> GetT_OE_USERSInRole(int roleID)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
var users = from itemA in ctx.T_OE_USERS
join itemB in ctx.T_OE_USER_ROLES on itemA.USER_IDX equals itemB.USER_IDX
where itemB.ROLE_IDX == roleID
orderby itemA.USER_ID
select itemA;
return(users.ToList());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static int CreateT_VCCB_USER_ROLE(global::System.Int32 rOLE_IDX, global::System.Int32 uSER_IDX, global::System.String cREATE_USER = "******")
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_OE_USER_ROLES ur = new T_OE_USER_ROLES();
ur.ROLE_IDX = rOLE_IDX;
ur.USER_IDX = uSER_IDX;
ur.CREATE_DT = System.DateTime.Now;
ur.CREATE_USERID = cREATE_USER;
ctx.AddToT_OE_USER_ROLES(ur);
ctx.SaveChanges();
return(1);
}
catch (Exception ex)
{
return(0);
}
}
}
public static int UpdateT_OE_USERSDefaultOrg(int idx, string dEFAULT_ORG_ID)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_OE_USERS row = new T_OE_USERS();
row = (from c in ctx.T_OE_USERS where c.USER_IDX == idx select c).First();
if (dEFAULT_ORG_ID != null)
{
row.DEFAULT_ORG_ID = dEFAULT_ORG_ID;
}
ctx.SaveChanges();
return(row.USER_IDX);
}
catch (Exception ex)
{
return(0);
}
}
}
//*****************ROLES **********************************
public static int CreateT_OE_ROLES(global::System.String rOLE_NAME, global::System.String rOLE_DESC, global::System.String cREATE_USER = "******")
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_OE_ROLES r = new T_OE_ROLES();
r.ROLE_NAME = rOLE_NAME;
r.ROLE_DESC = rOLE_DESC;
r.CREATE_DT = System.DateTime.Now;
r.CREATE_USERID = cREATE_USER;
ctx.AddToT_OE_ROLES(r);
ctx.SaveChanges();
return(1);
}
catch (Exception ex)
{
return(0);
}
}
}
//***************************** ATTAINS_ASSESS_UNITS_MLOC **************************************
public static int InsertOrUpdateATTAINS_ASSESS_UNITS_MLOC(int aSSESS_UNIT_IDX, int mONLOC_IDX, String cREATE_USER = "******")
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
Boolean insInd = false;
try
{
T_ATTAINS_ASSESS_UNITS_MLOC a = (from c in ctx.T_ATTAINS_ASSESS_UNITS_MLOC
where c.ATTAINS_ASSESS_UNIT_IDX == aSSESS_UNIT_IDX &&
c.MONLOC_IDX == mONLOC_IDX
select c).FirstOrDefault();
if (a == null)
{
a = new T_ATTAINS_ASSESS_UNITS_MLOC();
insInd = true;
}
a.ATTAINS_ASSESS_UNIT_IDX = aSSESS_UNIT_IDX;
a.MONLOC_IDX = mONLOC_IDX;
if (insInd) //insert case
{
a.CREATE_USERID = cREATE_USER.ToUpper();
a.CREATE_DT = System.DateTime.Now;
ctx.AddToT_ATTAINS_ASSESS_UNITS_MLOC(a);
}
ctx.SaveChanges();
return(1);
}
catch (Exception ex)
{
return(0);
}
}
}
public static int UpdateT_OE_USERS(int idx, string newPWD_HASH, string newPWD_SALT, string newFNAME, string newLNAME, string newEMAIL, bool?newACT_IND, bool?newINIT_PWD_FLG, DateTime?newEFF_DATE, DateTime?newLAST_LOGIN_DT, string newPHONE, string newPHONE_EXT, string newMODIFY_USR)
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
try
{
T_OE_USERS row = new T_OE_USERS();
row = (from c in ctx.T_OE_USERS where c.USER_IDX == idx select c).First();
if (newPWD_HASH != null)
{
row.PWD_HASH = newPWD_HASH;
}
if (newPWD_SALT != null)
{
row.PWD_SALT = newPWD_SALT;
}
if (newFNAME != null)
{
row.FNAME = newFNAME;
}
if (newLNAME != null)
{
row.LNAME = newLNAME;
}
if (newEMAIL != null)
{
row.EMAIL = newEMAIL;
}
if (newACT_IND != null)
{
row.ACT_IND = (bool)newACT_IND;
}
if (newINIT_PWD_FLG != null)
{
row.INITAL_PWD_FLAG = (bool)newINIT_PWD_FLG;
}
if (newEFF_DATE != null)
{
row.EFFECTIVE_DT = (DateTime)newEFF_DATE;
}
if (newLAST_LOGIN_DT != null)
{
row.LASTLOGIN_DT = (DateTime)newLAST_LOGIN_DT;
}
if (newPHONE != null)
{
row.PHONE = newPHONE;
}
if (newPHONE_EXT != null)
{
row.PHONE_EXT = newPHONE_EXT;
}
if (newMODIFY_USR != null)
{
row.MODIFY_USERID = newMODIFY_USR;
}
row.MODIFY_DT = System.DateTime.Now;
ctx.SaveChanges();
return(row.USER_IDX);
}
catch (Exception ex)
{
return(0);
}
}
}
public static int InsertOrUpdateATTAINS_REPORT(int?aTTAINS_REPORT_IDX, string oRG_ID, string rEPORT_NAME, DateTime?dATA_FROM, DateTime?dATA_TO,
bool?aTTAINS_IND, string aTTAINS_SUBMIT_STATUS, DateTime?aTTAINS_UPDATE_DT, String cREATE_USER = "******")
{
using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities())
{
Boolean insInd = false;
try
{
T_ATTAINS_REPORT a = new T_ATTAINS_REPORT();
if (aTTAINS_REPORT_IDX != null)
{
a = (from c in ctx.T_ATTAINS_REPORT
where c.ATTAINS_REPORT_IDX == aTTAINS_REPORT_IDX
select c).FirstOrDefault();
}
if (aTTAINS_REPORT_IDX == null) //insert case
{
insInd = true;
}
if (oRG_ID != null)
{
a.ORG_ID = oRG_ID;
}
if (rEPORT_NAME != null)
{
a.REPORT_NAME = rEPORT_NAME;
}
if (dATA_FROM != null)
{
a.DATA_FROM = dATA_FROM;
}
if (dATA_TO != null)
{
a.DATA_TO = dATA_TO;
}
if (aTTAINS_IND != null)
{
a.ATTAINS_IND = aTTAINS_IND;
}
if (aTTAINS_SUBMIT_STATUS != null)
{
a.ATTAINS_SUBMIT_STATUS = aTTAINS_SUBMIT_STATUS;
}
if (aTTAINS_UPDATE_DT != null)
{
a.ATTAINS_UPDATE_DT = aTTAINS_UPDATE_DT;
}
if (insInd) //insert case
{
a.CREATE_USERID = cREATE_USER.ToUpper();
a.CREATE_DT = System.DateTime.Now;
ctx.AddToT_ATTAINS_REPORT(a);
}
ctx.SaveChanges();
return(a.ATTAINS_REPORT_IDX);
}
catch (Exception ex)
{
return(0);
}
}
}
