public string DisableVerificationsForID(IConnectToDB _Connect, long?identities_id, string Validation_Type)
{
ER_DML er_dml = new ER_DML();
_DynamicOutputProcedures DynamicOutput = new _DynamicOutputProcedures();
List <DynamicModels.RootReportFilter> Filters = new List <DynamicModels.RootReportFilter>();
try
{
Filters.Add(new DynamicModels.RootReportFilter {
FilterName = "IDENTITIES_ID_", DBType = SqlDbType.BigInt, ParamValue = identities_id
});
Filters.Add(new DynamicModels.RootReportFilter {
FilterName = "VALIDATION_TYPE_", DBType = SqlDbType.VarChar, SearchParamSize = -1, ParamValue = Validation_Type
});
DataTable _ResultSet = DynamicOutput.DynoProcSearch(_Connect, "Custom Query", "SP_S_VW__VERIFY_SEARCH",
new DataTableDotNetModelMetaData {
length = -1, order = "1 asc", start = 0, verify = "T"
},
Filters);
foreach (DataRow _Row in _ResultSet.Rows)
{
er_dml.TOGGLE_OBJECT(_Connect, "VERIFY", _Row.Field <long?>("VERIFY_ID"), "N");
}
return("All Validations Disabled");
}
catch
{
return("Error Disabling Verifications");
}
}
public long?GetProfileRendition(IConnectToDB _Connect, long?identity_id)
{
long?value = 0;
ER_DML er_dml = new ER_DML();
_DynamicOutputProcedures DynamicOutput = new _DynamicOutputProcedures();
List <DynamicModels.RootReportFilter> Filters = new List <DynamicModels.RootReportFilter>();
Filters.Add(new DynamicModels.RootReportFilter {
FilterName = "IDENTITIES_ID_", DBType = SqlDbType.BigInt, ParamValue = identity_id
});
DataTable TempDataTable = DynamicOutput.DynoProcSearch(_Connect, "Custom Query", "SP_S_VW_PROFILES_DAT_CHAR_RENDITION_SEARCH",
new DataTableDotNetModelMetaData {
length = -1, order = "1 asc", start = 0, verify = "T"
},
Filters);
foreach (DataRow row in TempDataTable.Rows)
{
if (row["rendition"].ToString() != "")
{
value = ER_Tools.ConvertToInt64(row["rendition"].ToString());
}
}
return(value);
}
public CommandResult DROP_SCHEMA(IConnectToDB _Connect, string SchemaName)
{
CommandResult _result = new CommandResult();
ER_Tools er_tools = new ER_Tools();
ER_Query er_query = new ER_Query();
ER_DML er_dml = new ER_DML();
StringBuilder SQLBuffer = new StringBuilder();
string _Schema = ER_DB.GetSchema(_Connect);
SQLBuffer.Append("DROP SCHEMA " + SchemaName);
string SuccessMessage = "Success " + SchemaName + " has been dropped.";
_result._Response = er_query.RUN_NON_QUERY(_Connect, SQLBuffer.ToString(), SuccessMessage).ToString();
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
if (_result._Successful)
{
er_dml.DROP_Dictionary_View(_Connect, SchemaName);
}
return(_result);
}
public void ResetPassword(IConnectToDB _Connect, string VerifyUUID, string password)
{
ER_Sec er_sec = new ER_Sec();
ER_DML er_dml = new ER_DML();
_DynamicOutputProcedures DynamicOutput = new _DynamicOutputProcedures();
List <DynamicModels.RootReportFilter> verifyIdentityFilters = new List <DynamicModels.RootReportFilter>();
List <DynamicModels.RootReportFilter> idPasswordFilters = new List <DynamicModels.RootReportFilter>();
verifyIdentityFilters.Add(new DynamicModels.RootReportFilter {
FilterName = "UUID_", DBType = SqlDbType.VarChar, ParamValue = VerifyUUID
});
DataTable verifyIdentityTable = DynamicOutput.DynoProcSearch(_Connect, "Custom Query", "SP_S_VW__VERIFY_SEARCH",
new DataTableDotNetModelMetaData {
columns = "IDENTITIES_ID", length = -1, order = "1 asc", start = 0, verify = "T"
},
verifyIdentityFilters);
idPasswordFilters.Add(new DynamicModels.RootReportFilter {
FilterName = "Identities_ID_", DBType = SqlDbType.BigInt, ParamValue = verifyIdentityTable.Rows[0].Field <long?>("IDENTITIES_ID")
});
DataTable idPasswordTable = DynamicOutput.DynoProcSearch(_Connect, "Custom Query", "SP_S_VW__ID_PASSWORD_SEARCH",
new DataTableDotNetModelMetaData {
columns = "ID_PASSWORD_ID", length = -1, order = "1 asc", start = 0, verify = "T"
},
idPasswordFilters);
//Update Password
string hash = ER_Sec.ComputeHash(password, "SHA512", null);
er_dml.OBJECT_DML(_Connect, "Update", "ID_PASSWORD", "PASSWORD", idPasswordTable.Rows[0].Field <long?>("ID_PASSWORD_ID"),
new Object_Value
{
_File = new File_Object
{
_FileBytes = er_sec.EncryptStringToBytes_Aes(hash,
er_sec.GetCryptPairforID(_Connect, verifyIdentityTable.Rows[0].Field <long?>("IDENTITIES_ID"), new ER_CRYPT_PAIR()))
}
});
//disable verification
VerificationHelper VH = new VerificationHelper();
VH.DisableVerificationsForID(_Connect, verifyIdentityTable.Rows[0].Field <long?>("IDENTITIES_ID"), "ResetPassword");
}
public List <CommandResult> ADD_VIEW(IConnectToDB _Connect, string ViewName, List <sqlSelectStructure> QueryStructure, string ViewType, bool WithBinding)
{
List <CommandResult> results = new List <CommandResult>();
CommandResult _result = new CommandResult();
Tools.Box er_tools = new Tools.Box();
ER_Query er_query = new ER_Query();
ER_DML er_dml = new ER_DML();
ER_Generate er_generate = new ER_Generate();
string _Schema = Revamp.IO.DB.Bridge.DBTools.GetSchema(_Connect);
string _ViewName = "VW__" + er_tools.MaxNameLength(ViewName, (128 - 4));
StringBuilder SQLBuffer = new StringBuilder();
if (WithBinding)
{
SQLBuffer.AppendLine("CREATE VIEW " + _Schema + "." + _ViewName + " WITH SCHEMABINDING AS ");
}
else
{
SQLBuffer.AppendLine("CREATE VIEW " + _Schema + "." + _ViewName + " AS ");
}
SQLBuffer.AppendLine(er_generate.GENERATE_QUERY(_Connect, QueryStructure));
string SuccessMessage = "View " + _ViewName + " created.";
_result.attemptedCommand = SQLBuffer.ToString();
_result._Response = er_query.RUN_NON_QUERY(_Connect, _result.attemptedCommand, SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
if (_result._Successful)
{
IConnectToDB csaConnect = _Connect.Copy();
csaConnect.Schema = "CSA";
er_dml.ADD_Dictionary_View(csaConnect, new Structs.Models.RevampSystem.Dictionary.AddView {
I_VIEW_NAME = _ViewName, I_VIEW_TYPE = ViewType, I_VIEWDATA = ""
});
}
results.Add(_result);
return(results);
}
public DataTable GetIdentities(IConnectToDB _Connect, IdentitySearch Model)
{
ER_DML er_dml = new ER_DML();
VirtualProcedureCall ProcedureModel = new VirtualProcedureCall();
ProcedureModel.ProcedureName = "VW__IDENTITIES_SEARCH";
ProcedureModel.ProcedureReturnType = "query";
ProcedureModel.ProcedureParams = new List <ProcedureParameterStruct>();
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_SEARCH",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "MAX",
ParamValue = Model.Search
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_WHERE",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "MAX",
ParamValue = Model.Where
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_STARTING_ROW",
MSSqlParamDataType = "bigint",
ParamDirection = "input",
ParamValue = Model.StartingRow
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_LENGTH_OF_SET",
MSSqlParamDataType = "bigint",
ParamDirection = "input",
ParamValue = Model.LengthOfSet
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_ORDER_BY",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "MAX",
ParamValue = Model.OrderBy
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_USER_NAME",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "50",
ParamValue = Model.Username
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_EDIPI",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "10",
ParamValue = Model.Edipi
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_EMAIL",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "254",
ParamValue = Model.Email
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_ACTIVE",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "1",
ParamValue = Model.Active
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_VERIFIED",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "1",
ParamValue = Model.Verified
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_OBJECT_LAYER",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "30",
ParamValue = Model.ObjectLayer
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_VERIFY",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "1",
ParamValue = Model.Verify
});
DataTable _Result = ER_Procedure.VIRTUAL_PROCEDURE_CALL(_Connect, ProcedureModel);
return(_Result);
}
public DataTable GetIdentitiesCount(IConnectToDB _Connect, IdentitySearchCount Model)
{
ER_DML er_dml = new ER_DML();
VirtualProcedureCall ProcedureModel = new VirtualProcedureCall();
ProcedureModel.ProcedureName = "IDENTITIES_SEARCH_COUNT";
ProcedureModel.ProcedureReturnType = "value";
ProcedureModel.ProcedureParams = new List <ProcedureParameterStruct>();
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_SEARCH",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "MAX",
ParamValue = Model.Search
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_WHERE",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "MAX",
ParamValue = Model.Where
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_USER_NAME",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "50",
ParamValue = Model.Username
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_EDIPI",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "10",
ParamValue = Model.Edipi
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_EMAIL",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "254",
ParamValue = Model.Email
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_ACTIVE",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "1",
ParamValue = Model.Active
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_VERIFIED",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "1",
ParamValue = Model.Verified
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_OBJECT_LAYER",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "30",
ParamValue = Model.ObjectLayer
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "P_VERIFY",
MSSqlParamDataType = "varchar",
ParamDirection = "input",
ParamSize = "1",
ParamValue = Model.Verify
});
ProcedureModel.ProcedureParams.Add(new ProcedureParameterStruct
{
ParamName = "O_ATTEMPTED_RESULTS",
MSSqlParamDataType = "varchar",
ParamDirection = "output",
ParamSize = "MAX"
});
DataTable _Result = ER_Procedure.VIRTUAL_PROCEDURE_CALL(_Connect, ProcedureModel);
return(_Result);
}
public SessionObjects AddProfilePic(IConnectToDB _Connect, SessionObjects SO, FormCollection _formCollection)
{
try
{
if (_formCollection.Keys.Count > 1)
{
SecurityHelper securityHelper = new SecurityHelper();
IdentityHelper identityHelper = new IdentityHelper();
ER_DML er_dml = new ER_DML();
add addHelp = new add();
long? identitiesId = ER_Tools.ConvertToInt64(identityHelper.GetIdentityID(_Connect, _formCollection["identitiesUUID"].ToString()));
string fileName = _formCollection["fileName"].ToString();
string fileType = _formCollection["fileType"].ToString();
long? fileSize = ER_Tools.ConvertToInt64(_formCollection["fileSize"].ToString());
byte[] fileContent = Encoding.UTF8.GetBytes(_formCollection["profileImage"].ToString());
Guid? ProfilesUUID = ER_Tools.ConvertToGuid(_formCollection["profilesUUID"].ToString());
long? Profiles_ID = GetProfileId(_Connect, ProfilesUUID.ToString());
long? profileImagesId = 0;
var profileImagesDT = GetImageIdByProfile(_Connect, Profiles_ID);
DataColumnCollection _dccColumnID = profileImagesDT.Columns;
if (_dccColumnID.Contains("PROFILE_IMAGES_ID") && profileImagesDT.Rows.Count > 0)
{
Values.UpdateProfileImages ProfileImagesModel = null;
ProfileImagesModel = addHelp.UPDATE_ENTRY_Profile_Images(_Connect, new Values.UpdateProfileImages
{
I_PROFILE_IMAGES_ID = profileImagesDT.Rows[0].Field <long?>("PROFILE_IMAGES_ID"),
I_PROFILES_ID = Profiles_ID,
I_FILE_NAME = fileName,
I_FILE_SIZE = fileSize,
I_CONTENT_TYPE = fileType,
I_VALUE = fileContent
});
profileImagesId = ProfileImagesModel.O_PROFILE_IMAGES_ID;
//Enable Profile Image
er_dml.TOGGLE_OBJECT(_Connect, "PROFILE_IMAGES", profileImagesId, "Y");
}
else
{
//Values.AddProfilesSecPriv ProfilesSecPrivModel = null;
//ProfilesSecPrivModel = addHelp.ADD_ENTRY_Profiles_Sec_Priv(_Connect, new Values.AddProfilesSecPriv
//{
// I_OBJECT_TYPE = "Permission",
// I_PROFILES_ID = Profiles_ID,
// I_PRIVILEGES_ID = ER_Tools.ConvertToInt64(securityHelper.GetPrivID(_Connect, "CREATE OBJECT")),
// I_ENABLED = 'Y',
// I_IDENTITIES_ID = identitiesId
//});
Values.AddProfileImages ProfileImagesModel = null;
ProfileImagesModel = addHelp.ADD_ENTRY_Profile_Images(_Connect, new Values.AddProfileImages
{
I_IDENTITIES_ID = identitiesId,
I_PROFILES_UUID = ProfilesUUID,
I_ENABLED = 'Y',
I_FILE_NAME = fileName,
I_FILE_SIZE = fileSize,
I_CONTENT_TYPE = fileType,
I_VALUE = fileContent
});
}
//Set Profile Image
_session.SetString("ProfileImage", GetProfileImage(_Connect, identitiesId));
}
}
catch
{
}
return(SO);
}
public DataTable GetPendingUser(IConnectToDB _Connect, DataTable _DT, string Username, string SubmittedPassword, string VerifyUUID)
{
ER_Query er_query = new ER_Query();
ER_Query.Parameter_Run SQlinUserName = new ER_Query.Parameter_Run();
ER_Query.Parameter_Run SQlinPassword = new ER_Query.Parameter_Run();
SQlinUserName._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "UUID", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = VerifyUUID
},
new DBParameters {
ParamName = "USER_NAME", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = Username.ToLower()
},
};
SQlinUserName.sqlIn = "Select a.* from CSA.IDENTITIES a inner join CSA.VW__VERIFY b on (a.IDENTITIES_ID = b.IDENTITIES_ID and b.UUID = @UUID) where LOWER(a.User_name) = @USER_NAME or LOWER(a.Email) = @USER_NAME";
DataTable usernamedt = er_query.RUN_PARAMETER_QUERY(_Connect, SQlinUserName);
if (usernamedt.Rows.Count == 1)
{
foreach (DataRow _DR in usernamedt.Rows)
{
if (_DR.Field <string>("User_name").ToLower() == Username.ToLower() || _DR.Field <string>("Email").ToLower() == Username.ToLower())
{
SQlinPassword._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "IDENTITIES_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = _DR.Field <long?>("identities_id")
}
};
SQlinPassword.sqlIn = "Select a.* from CSA.ID_PASSWORD a inner join CSA.ID_PASSWORD b on a.IDENTITIES_ID = b.IDENTITIES_ID and a.RENDITION in (select max(c.RENDITION) from CSA.ID_PASSWORD c where c.Identities_id = b.Identities_ID) where a.identities_id = @IDENTITIES_ID";
DataTable passdt = er_query.RUN_PARAMETER_QUERY(_Connect, SQlinPassword);
byte[] EncryptedPassword = (byte[])passdt.Rows[0]["Password"];
long? IDENTITIES_ID = (long?)passdt.Rows[0]["IDENTITIES_ID"];
ER_Sec er_sec = new ER_Sec();
string DecryptedPasswordHash = er_sec.DecryptStringFromBytes_Aes(EncryptedPassword, er_sec.GetCryptPairforID(_Connect, IDENTITIES_ID, new ER_CRYPT_PAIR()));
if (passdt.Rows.Count != 0 && ER_Sec.VerifyHash(SubmittedPassword, "SHA512", DecryptedPasswordHash)
)
{
_DT = usernamedt;
ER_DML er_dml = new ER_DML();
//er_dml.OBJECT_DML(_Connect, "Update", "Identities", "ENABLED", IDENTITIES_ID, new Object_Value { _String = "Y" }); Commented out because this is already being set to yes when the identity is created.
er_dml.OBJECT_DML(_Connect, "Update", "Identities", "ACTIVE", IDENTITIES_ID, new Object_Value {
_String = "Y"
});
er_dml.OBJECT_DML(_Connect, "Update", "Identities", "VERIFIED", IDENTITIES_ID, new Object_Value {
_String = "Y"
});
VerificationHelper VH = new VerificationHelper();
VH.DisableVerificationsForID(_Connect, IDENTITIES_ID, "CreateUser");
}
break;
}
}
}
return(_DT);
}
