//Returns the count of the new notifications
public string NewNotificationCount(IConnectToDB _Connect, string _identity)
{
ER_Query er_query = new ER_Query();
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run();
string count = "";
SQlin._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "IDENTITIES_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = _identity
},
};
SQlin.sqlIn = " Select COUNT(*) total from CSA.VW__ACTIVITY a where a.APPLICATION_NAME != 'Core Settings' and a.DT_CREATED >= (select max(ip.DT_CREATED) FROM CSA.IDENTITY_PROPERTIES ip where ip.IDENTITIES_ID = @IDENTITIES_ID) and a.OBJECT_TYPE not in ('Login','Access Log')";
DataTable TempDataTable = er_query.RUN_PARAMETER_QUERY(_Connect, SQlin);
if (TempDataTable.Rows.Count > 0)
{
count = TempDataTable.Rows[0][0].ToString();
}
return(count);
}
//TOLEARN: This is important and has to be refactored for new system.
public DataTable FindIDStatuses(IConnectToDB _Connect, long?identities_id)
{
ER_Query er_query = new ER_Query();
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run();
SQlin._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "IDENTITIES_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = identities_id
}
};
SQlin.sqlIn = "Select distinct e.FORMS_ID, a.DT_CREATED 'Date_Submitted', a.DT_CREATED 'Date_Updated', a.IDENTITIES_ID, a.FORMS_DAT_OPT_ID, a.applications_id, d.application_name, a.PROPERTY_VALUE, a.VALUE from " + _Connect.Schema + ".FORMS_DAT_OPT a " +
"inner join CSA.Object_Sets b ON " +
"(a.OBJECT_SETS_ID = b.OBJECT_SETS_ID and a.Identities_id = @IDENTITIES_ID) " +
"inner join CSA.[OBJ_PROP_SETS] c on (b.OBJECT_SETS_ID = c.OBJECT_SETS_ID) " +
"inner join CSA.[APPLICATIONS] d on (d.APPLICATIONS_ID = a.APPLICATIONS_ID) " +
"inner join CSA.FORMS e on (e.FORMS_ID = a.FORMS_ID) " +
"where c.PROPERTY_NAME in ('Status Check', 'Status Type', 'Status Message') " +
"and a.RENDITION in (Select MAX(RENDITION) from CSA.VW__forms_dat_opt fdo2 where fdo2.FORMS_ID = a.FORMS_ID) " +
"ORDER BY a.FORMS_DAT_OPT_ID desc ";
DataTable Result_DT = er_query.RUN_PARAMETER_QUERY(_Connect, SQlin);
return(Result_DT);
}
public string GetNotificationSettingValue(IConnectToDB _Connect, long?identity, string type)
{
ER_Query er_query = new ER_Query();
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run();
string settingValue = "";
SQlin.sqlIn = "Select property_type from CSA.IDENTITY_PROPERTIES a where a.DT_CREATED >= (select max(ip.DT_CREATED) FROM CSA.IDENTITY_PROPERTIES ip where ip.IDENTITIES_ID = @IDENTITIES_ID and ip.PROPERTY_NAME = @PROPERTY_NAME) and a.PROPERTY_NAME = @PROPERTY_NAME";
SQlin._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "IDENTITIES_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = identity
},
new DBParameters {
ParamName = "PROPERTY_NAME", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = type
}
};
DataTable TempDataTable = er_query.RUN_PARAMETER_QUERY(_Connect, SQlin);
if (TempDataTable.Rows.Count > 0)
{
settingValue = TempDataTable.Rows[0][0].ToString();
}
return(settingValue);
}
public static string GetSymbolbyID(IConnectToDB _Connect, string SymbolName)
{
string sqlstatement = "Select * from CSA.SYMBOLS where LOWER(SYMBOL_NAME) = @SYMBOL_NAME";
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run
{
sqlIn = sqlstatement,
_dbParameters = new List <DBParameters> {
new DBParameters {
ParamName = "SYMBOL_NAME", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = SymbolName.ToLower()
}
}
};
DataTable _DT = ER_Query._RUN_PARAMETER_QUERY(_Connect, SQlin);
DataColumnCollection DCC = _DT.Columns;
if (_DT.Rows.Count > 0 && DCC.Contains("SYMBOLS_ID"))
{
return(_DT.Rows[0]["SYMBOLS_ID"].ToString());
}
else
{
return("1000");
}
}
public ViewObjectPropFile getFileBytesFromPropID(IConnectToDB _Connect, string objectpropsetid)
{
byte[] bytes = new byte[0];
ViewObjectPropFile _File = new ViewObjectPropFile();
int n;
bool isNumeric = int.TryParse(objectpropsetid, out n);
if (isNumeric)
{
ER_Query er_query = new ER_Query();
ObjectPropSetModels opsm = new ObjectPropSetModels();
ObjectPropSetsHelper opsHelper = new ObjectPropSetsHelper();
List <ViewObjectPropFile> _FilesList = opsHelper.FindFiles(_Connect, new List <ViewObjectPropFile>(), objectpropsetid, "object_prop_set");
foreach (ViewObjectPropFile e in _FilesList)
{
if (e != null)
{
_File.file_name = e.file_name;
_File.value = e.value;
_File.content_type = e.content_type;
//bytes = (byte[])e.file_data;
}
}
}
return(_File);
}
public DataTable FindNotifications(IConnectToDB _Connect, string start)
{
ER_Query er_query = new ER_Query();
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run();
SQlin._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "ACTIVITY_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = start
}
};
SQlin.sqlIn = " Select * " +
" from (Select ROW_NUMBER() OVER(ORDER BY ACTIVITY_ID ) AS rownumb, * from CSA.VW__ACTIVITY where Lower(object_type) in ('add object','delete object') and " +
"Lower(table_source) in ('identities', 'applications')) ok " +
" where rownumb between CONVERT(int, (select rownumb1 from (Select ROW_NUMBER() OVER(ORDER BY ACTIVITY_ID ) AS rownumb1, * from CSA.VW__ACTIVITY where Lower(object_type) in ('add object','delete object') and " +
" Lower(table_source) in ('identities', 'applications')) ok1 where activity_id = @ACTIVITY_ID)) + 1 and CONVERT(int, (select rownumb1 from (Select ROW_NUMBER() OVER(ORDER BY ACTIVITY_ID ) AS rownumb1, * from CSA.VW__ACTIVITY where Lower(object_type) in ('add object','delete object') and " +
" Lower(table_source) in ('identities', 'applications')) ok1 where activity_id = @ACTIVITY_ID)) + 10 and Lower(object_type) in ('add object','delete object') and " +
" Lower(table_source) in ('identities', 'applications') Order by DT_CREATED desc";
DataTable TempDataTable = er_query.RUN_PARAMETER_QUERY(_Connect, SQlin);
return(TempDataTable);
}
public List <CommandResult> DROP_ALL(IConnectToDB _Connect)
{
List <CommandResult> Logger = new List <CommandResult>();
switch (_Connect.Platform.ToUpper())
{
case "MICROSOFT":
Logger.Add(DROP_MSSQL_DB(_Connect, _Connect.SourceDBOwner));
Logger.Add(DROP_MSSQL_USER(_Connect, _Connect.SourceDBOwner));
string sqlIn = "BEGIN " +
"DECLARE @SYSTEM_NM varchar(max) = '{0}' " +
"DELETE FROM [ReVampNucleus].[CSA].[ER_OWNER_SEEDS] " +
"WHERE SYSTEM_NAME = @SYSTEM_NM " +
"END";
if (!Logger.Exists(x => x._Successful == false))
{
sqlIn = String.Format(sqlIn, _Connect.SourceDBOwner);
ER_Query er_query = new ER_Query();
CommandResult _result = new CommandResult();
_Connect.SourceDBOwner = "RevampNucleus";
string SuccessMessage = "Dropped DB Successfully issued";
_result._Response = er_query.RUN_NON_QUERY(_Connect, sqlIn, SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
Logger.Add(_result);
}
break;
}
return(Logger);
}
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 CommandResult CREATE_CATALOG(IConnectToDB _Connect, string Name)
{
ER_Query er_query = new ER_Query();
CommandResult _result = new CommandResult();
string SuccessMessage = "Catalog " + Name + "_CATALOG Created";
_result._Response = er_query.RUN_NON_QUERY(_Connect, "Create FullTEXT CATALOG " + Name + "_CATALOG", SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
return(_result);
}
public CommandResult ALTER_AUTH_ON_MSSQL_SCHEMA(IConnectToDB _Connect, string Assignee, string SchemaName)
{
ER_Query er_query = new ER_Query();
CommandResult _result = new CommandResult();
string SuccessMessage = Assignee + " has been authorized for Schema " + SchemaName + " successfully";
_result._Response = er_query.RUN_NON_QUERY(_Connect, "ALTER AUTHORIZATION ON SCHEMA::" + _Connect.Schema + "." + SchemaName + " TO " + Assignee, SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
return(_result);
}
public CommandResult UPDATE_MSSQL_LOGIN_DEFAULT_DB(IConnectToDB _Connect, string LoginName, string DefaultDatabase)
{
ER_Query er_query = new ER_Query();
CommandResult _result = new CommandResult();
string SuccessMessage = LoginName + " default db changed to " + DefaultDatabase + " successfully";
_result._Response = er_query.RUN_NON_QUERY(_Connect, "ALTER LOGIN " + LoginName + " with DEFAULT_DATABASE =" + DefaultDatabase + "", SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
return(_result);
}
public CommandResult UPDATE_MSSQL_USER_DEFAULT_SCHEMA(IConnectToDB _Connect, string UserName, string DefaultSchema)
{
ER_Query er_query = new ER_Query();
CommandResult _result = new CommandResult();
string SuccessMessage = UserName + " default schema changed to " + DefaultSchema + " successfully";
_result._Response = er_query.RUN_NON_QUERY(_Connect, "ALTER USER " + UserName + " with DEFAULT_SCHEMA =" + DefaultSchema + "", SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
return(_result);
}
public CommandResult ADD_MEMBER_TO_ROLE(IConnectToDB _Connect, string Role, string MemberName)
{
ER_Query er_query = new ER_Query();
CommandResult _result = new CommandResult();
string SuccessMessage = MemberName + " added to role " + Role + " successfully";
_result._Response = er_query.RUN_NON_QUERY(_Connect, "EXEC sp_addrolemember '" + Role + "','" + MemberName + "'", SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
return(_result);
}
public CommandResult GRANT_MSSQL_PRIVILEGE(IConnectToDB _Connect, string Privilege, string Grantee, string SourceDB)
{
ER_Query er_query = new ER_Query();
CommandResult _result = new CommandResult();
string SuccessMessage = "Grant Successfully issued";
_result._Response = er_query.RUN_NON_QUERY(_Connect, "GRANT " + Privilege + " TO " + Grantee, SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
return(_result);
}
public CommandResult ADD_MSSQL_SCHEMA(IConnectToDB _Connect, string SchemaName, string SchemaOwner)
{
ER_Query er_query = new ER_Query();
CommandResult _result = new CommandResult();
string SuccessMessage = "Schema Successfully created";
//_result._Response = er_query.RUN_NON_QUERY(_Connect, "CREATE SCHEMA " + SchemaName + " AUTHORIZATION " + SchemaOwner, SuccessMessage);
_result._Response = er_query.RUN_NON_QUERY(_Connect, "CREATE SCHEMA " + SchemaName, SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
return(_result);
}
public DataTable GetUser(IConnectToDB _Connect, DataTable _DT, string Username, string SubmittedPassword)
{
ER_Query er_query = new ER_Query();
IdentityHelper ih = new IdentityHelper();
_DT = new DataTable();
DataTable usernamedt = ih.FindIdentity(_Connect, Username);
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())
{
List <DynamicModels.RootReportFilter> passwordFilters = new List <DynamicModels.RootReportFilter>();
passwordFilters.Add(new DynamicModels.RootReportFilter {
FilterName = "IDENTITIES_ID_", ParamValue = _DR.Field <long?>("identities_id")
});
DataTable passdt = _DynamicOutputProcedures._DynoProcSearch(_Connect, "Custom Query", "SP_S_" + "VW__ID_PASSWORD" + "_SEARCH",
new DataTableDotNetModelMetaData {
length = -1, order = "1 asc", start = 0, verify = "T"
},
passwordFilters);
if (passdt.Rows.Count != 0)
{
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;
}
}
else
{
_DT = usernamedt;
}
break;
}
}
}
return(_DT);
}
public static CommandResult RUN_SQL_FILE(IConnectToDB _NewConnect, string ServerPath, string ResultMessage, StringBuilder _sqlIn)
{
_sqlIn = Tools.Box.convertStringArray(System.IO.File.ReadAllLines(ServerPath), _sqlIn);
CommandResult _Result = new CommandResult();
_Result._StartTime = DateTime.Now;
_Result._Response = ER_Query._RUN_NON_QUERY(_NewConnect, _sqlIn.ToString(), ResultMessage);
_Result._Successful = _Result._Response.IndexOf("Success") != -1 ? true : false;
_Result._EndTime = DateTime.Now;
return(_Result);
}
public DataTable GetActivityCounts(IConnectToDB _Connect, DataTable _DataTable, long?applications_id, DateTime?dateFrom, DateTime?dateTo)
{
ER_Query er_query = new ER_Query();
ActivityHelper AH = new ActivityHelper();
if (applications_id != null)
{
string query = "";
List <DBParameters> _dbParameters = new List <DBParameters>();
_dbParameters.Add(new DBParameters {
ParamName = "APPLICATIONS_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = applications_id
});
if (dateFrom == null && dateTo == null)
{
query = "SELECT count(*) as count, dateadd(DAY, 0, datediff(DAY, 0, a.dt_created)) as dt_created from CSA.ACTIVITY a inner join CSA.Applications aa ON (a.applications_id = aa.applications_id) inner join CSA.Applications aaa ON (aa.ROOT_APPLICATION = aaa.ROOT_APPLICATION and aaa.applications_id = @APPLICATIONS_ID) where a.DT_CREATED > DATEADD(DAY, -51, GETDATE()) group by dateadd(DAY, 0, datediff(DAY, 0, a.dt_created))";
}
else if (dateFrom != null && dateTo == null)
{
query = "SELECT count(*) as count, dateadd(DAY, 0, datediff(DAY, 0, a.dt_created)) as dt_created from CSA.ACTIVITY a inner join CSA.Applications aa ON (a.applications_id = aa.applications_id) inner join CSA.Applications aaa ON (aa.ROOT_APPLICATION = aaa.ROOT_APPLICATION and aaa.applications_id = @APPLICATIONS_ID) where a.DT_CREATED > DATEADD(DAY, -30, GETDATE()) group by dateadd(DAY, 0, datediff(DAY, 0, a.dt_created))";
}
else if (dateFrom == null && dateTo != null)
{
query = "SELECT count(*) as count, dateadd(DAY, 0, datediff(DAY, 0, a.dt_created)) as dt_created from CSA.ACTIVITY a inner join CSA.Applications aa ON (a.applications_id = aa.applications_id) inner join CSA.Applications aaa ON (aa.ROOT_APPLICATION = aaa.ROOT_APPLICATION and aaa.applications_id = @APPLICATIONS_ID) where a.DT_CREATED > DATEADD(DAY, -30, GETDATE()) group by dateadd(DAY, 0, datediff(DAY, 0, a.dt_created))";
}
else if (dateFrom != null && dateTo != null)
{
query = "SELECT count(*) as count, dateadd(DAY, 0, datediff(DAY, 0, a.dt_created)) as dt_created from CSA.ACTIVITY a inner join CSA.Applications aa ON (a.applications_id = aa.applications_id) inner join CSA.Applications aaa ON (aa.ROOT_APPLICATION = aaa.ROOT_APPLICATION and aaa.applications_id = @APPLICATIONS_ID) where a.DT_CREATED >= dateadd(DAY, -1, @START_DATE) and a.DT_CREATED <= dateadd(DAY, 1, @END_DATE) group by dateadd(DAY, 0, datediff(DAY, 0, a.dt_created))";
_dbParameters.Add(new DBParameters {
ParamName = "START_DATE", MSSqlParamDataType = SqlDbType.DateTime, ParamValue = dateFrom.Value.ToShortDateString()
});
_dbParameters.Add(new DBParameters {
ParamName = "END_DATE", MSSqlParamDataType = SqlDbType.DateTime, ParamValue = dateTo.Value.ToShortDateString()
});
}
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run
{
sqlIn = query,
_dbParameters = _dbParameters
};
_DataTable = er_query.RUN_PARAMETER_QUERY(_Connect, SQlin);
}
return(_DataTable);
}
public CommandResult ADD_MSSQL_ROLE(IConnectToDB _Connect, string Name)
{
CommandResult _result = new CommandResult();
ER_Tools er_tools = new ER_Tools();
ER_Query er_query = new ER_Query();
string tempstringNAME = er_tools.MaxNameLength(Name, 125) + "_RL";
string SuccessMessage = "Role " + Name + " Created";
_result._Response = er_query.RUN_NON_QUERY(_Connect, "Create Role " + tempstringNAME, SuccessMessage);
_result._Successful = _result._Response.IndexOf(SuccessMessage) > -1 ? true : false;
_result._EndTime = DateTime.Now;
return(_result);
}
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 FindMemberNotifications(IConnectToDB _Connect, string identity)
{
ER_Query er_query = new ER_Query();
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run();
SQlin._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "IDENTITIES_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = identity
}
};
SQlin.sqlIn = "Select top 10 * from CSA.VW__ACTIVITY a where Lower(object_type) in ('add object','delete object') and Lower(table_source) in ('identities', 'applications') and a.CORES_ID in (Select c.CORES_ID from CSA.CORES_IDENTITIES c where c.IDENTITIES_ID = @IDENTITIES_ID) Order by DT_CREATED desc";
DataTable TempDataTable = er_query.RUN_PARAMETER_QUERY(_Connect, SQlin);
return(TempDataTable);
}
public string getFilesFromBytes(IConnectToDB _Connect, string object_id, string object_type)
{
ER_Query er_query = new ER_Query();
ObjectPropSetModels opsm = new ObjectPropSetModels();
ObjectPropSetsHelper opsHelper = new ObjectPropSetsHelper();
List <ViewObjectPropFile> _FilesList = opsHelper.FindFiles(_Connect, new List <ViewObjectPropFile>(), object_id, object_type);
char[] chars = new char[0];
foreach (ViewObjectPropFile e in _FilesList)
{
if (e != null)
{
byte[] bytes = e.value;
chars = new char[bytes.Length / sizeof(char)];
System.Buffer.BlockCopy(bytes, 0, chars, 0, bytes.Length);
}
}
return(new string(chars));
}
public List <CommandResult> GET_FORM_VALUES(IConnectToDB _Connect, List <CommandResult> _results)
{
ER_Query er_query = new ER_Query();
string _Schema = ER_DB.GetSchema(_Connect);
StringBuilder SQLin = new StringBuilder();
SQLin.AppendLine("CREATE PROCEDURE " + _Schema + ".FORMS_GET_ENTRY_VALUES");
SQLin.AppendLine(" @P_FORM_ID bigint ");
SQLin.AppendLine("AS");
SQLin.AppendLine("BEGIN");
SQLin.AppendLine(" -- SET NOCOUNT ON added to prevent extra result sets from");
SQLin.AppendLine(" -- interfering with SELECT statements.");
SQLin.AppendLine(" SET NOCOUNT ON;");
SQLin.AppendLine("");
SQLin.AppendLine(" select * from (");
SQLin.AppendLine(" select 'FORMS_DAT_CHAR' source, fdc.FORMS_ID, fdc.GRIPS_ID, fdc.STAGES_ID, fdc.OBJ_PROP_SETS_ID, fdc.RENDITION, fdc.VALUE char_value, null numb_value, null date_value, null deci_value, null opt_value, null file_value from "+ _Schema + ".VW__FORMS_DAT_CHAR fdc where fdc.FORMS_ID = @P_FORM_ID");
SQLin.AppendLine(" union");
SQLin.AppendLine(" select 'FORMS_DAT_NUMB' source, fdn.FORMS_ID, fdn.GRIPS_ID, fdn.STAGES_ID, fdn.OBJ_PROP_SETS_ID, fdn.RENDITION, null char_value, fdn.VALUE numb_value, null date_value,null deci_value, null opt_value, null file_value from "+ _Schema + ".VW__FORMS_DAT_NUMB fdn where fdn.FORMS_ID = @P_FORM_ID");
SQLin.AppendLine(" union");
SQLin.AppendLine(" select 'FORM_DAT_DATE' source, fdd.FORMS_ID, fdd.GRIPS_ID, fdd.STAGES_ID, fdd.OBJ_PROP_SETS_ID, fdd.RENDITION, null char_value, null numb_value, fdd.VALUE date_value, null deci_value, null opt_value, null file_value from "+ _Schema + ".VW__FORMS_DAT_DATE fdd where fdd.FORMS_ID = @P_FORM_ID");
SQLin.AppendLine(" union");
SQLin.AppendLine(" select 'FORM_DATE_DECI' source, fddc.FORMS_ID, fddc.GRIPS_ID, fddc.STAGES_ID, fddc.OBJ_PROP_SETS_ID, fddc.RENDITION, null char_value, null numb_value, null date_value, fddc.VALUE deci_value, null opt_value, null file_value from "+ _Schema + ".VW__FORMS_DAT_DECI fddc where fddc.FORMS_ID = @P_FORM_ID");
SQLin.AppendLine(" union ");
SQLin.AppendLine(" select 'FORMS_DAT_OPT' source, fdo.FORMS_ID, fdo.GRIPS_ID, fdo.STAGES_ID, fdo.OBJ_PROP_SETS_ID, fdo.RENDITION, null char_value, null numb_value, null date_value, null deci_value, fdo.VALUE opt_value, null file_value from "+ _Schema + ".VW__FORMS_DAT_OPT fdo where fdo.FORMS_ID = @P_FORM_ID");
SQLin.AppendLine(" union ");
SQLin.AppendLine(" select 'FORMS_DAT_FILE' source, fdf.FORMS_ID, fdf.GRIPS_ID, fdf.STAGES_ID, fdf.OBJ_PROP_SETS_ID, fdf.RENDITION, null char_value, null numb_value, null date_value, null deci_value, null opt_value, fdf.VALUE file_value from "+ _Schema + ".VW__FORMS_DAT_FILE fdf where fdf.FORMS_ID = @P_FORM_ID");
SQLin.AppendLine(" ) ");
SQLin.AppendLine(" ALL_VALUES");
SQLin.AppendLine("END");
CommandResult _result = new CommandResult();
_result._StartTime = DateTime.Now;
_result._Response = er_query.RUN_NON_QUERY(_Connect, SQLin.ToString(), "Procedure " + _Schema + "." + "FORMS_GET_ENTRY_VALUES" + " created");
_result._Successful = _result._Response.Contains("created") ? true : false;
_result._EndTime = DateTime.Now;
_results.Add(_result);
return(_results);
}
public string getStringFromBytesViaObjectProSetid(IConnectToDB _Connect, Guid?objectpropsetid)
{
ER_Query er_query = new ER_Query();
ObjectPropSetModels opsm = new ObjectPropSetModels();
ObjectPropSetsHelper opsHelper = new ObjectPropSetsHelper();
opsm = opsHelper.FindExtras(_Connect, opsm, objectpropsetid);
char[] chars = new char[0];
foreach (ViewObjectPropSetFile e in opsm.PropSetView.PropFiles)
{
if (e != null)
{
byte[] bytes = e.value;
chars = new char[bytes.Length / sizeof(char)];
System.Buffer.BlockCopy(bytes, 0, chars, 0, bytes.Length);
}
}
return(new string(chars));
}
public static bool checkIfTableExist(IConnectToDB _Connect, string schema, string tablename)
{
string _sqlIn = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME";
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run
{
sqlIn = _sqlIn,
_dbParameters = new List <DBParameters> {
new DBParameters {
ParamName = "TABLE_SCHEMA", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = schema
},
new DBParameters {
ParamName = "TABLE_NAME", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = tablename
}
}
};
DataTable _DT = ER_Query._RUN_PARAMETER_QUERY(_Connect, SQlin);
DataColumnCollection DCC = _DT.Columns;
return(_DT.Rows.Count > 0 && DCC.Contains("TABLE_SCHEMA") ? true : false);
}
public Boolean isUserValid(IConnectToDB _Connect, string EDIPI)
{
ER_Query er_query = new ER_Query();
DataRow TempDR = GetUser(_Connect, EDIPI);
try
{
if (!string.IsNullOrWhiteSpace(EDIPI) && TempDR.Field <string>("EDIPI") == EDIPI)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
public DataTable FindAllbyIDforDays(IConnectToDB _Connect, string _typeofActivity, long?_forIdentityID, int DaysBack)
{
ER_Query er_query = new ER_Query();
DataTable TempDataTable;
List <DBParameters> _dbParameters = new List <DBParameters>();
string query = "";
long? Identity = _forIdentityID != null ? _forIdentityID : 0;
_dbParameters.Add(new DBParameters {
ParamName = "DAYS_BACK", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = DaysBack
});
_dbParameters.Add(new DBParameters {
ParamName = "IDENTITIES_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = Identity
});
switch (_typeofActivity.ToLower())
{
case "all":
query = "Select * from CSA.VW__ACTIVITY where DT_CREATED > DATEADD(DAY, -@DAYS_BACK, GETDATE()) and identities_id = @IDENTITIES_ID Order by DT_CREATED desc";
break;
default:
_dbParameters.Add(new DBParameters {
ParamName = "OBJECT_TYPE", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = _typeofActivity.ToLower()
});
query = "Select * from CSA.VW__ACTIVITY where DT_CREATED > DATEADD(DAY, -@DAYS_BACK, GETDATE()) and Lower(object_type) = @OBJECT_TYPE and identities_id = @IDENTITIES_ID Order by DT_CREATED desc";
break;
}
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run
{
sqlIn = query,
_dbParameters = _dbParameters
};
TempDataTable = er_query.RUN_PARAMETER_QUERY(_Connect, SQlin);
return(TempDataTable);
}
public string GetUsernameForVerification(IConnectToDB _Connect, string VerifyUUID)
{
ER_Query er_query = new ER_Query();
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run();
SQlin._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "UUID", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = VerifyUUID
}
};
SQlin.sqlIn = "Select a.* from CSA.IDENTITIES a inner join CSA.VW__VERIFY b on (a.IDENTITIES_ID = b.IDENTITIES_ID and b.UUID = @UUID)";
DataTable usernamedt = er_query.RUN_PARAMETER_QUERY(_Connect, SQlin);
if (usernamedt.Rows.Count == 1)
{
return(usernamedt.Rows[0]["User_Name"].ToString());
}
return("");
}
public string isPendingUserValid(IConnectToDB _Connect, string Username, string Password, string VerifyUUID)
{
//Logic comes later make everyone true for now.
ER_Query er_query = new ER_Query();
string uname = "";
DataTable TempDataTable = GetPendingUser(_Connect, new DataTable(), Username, Password, VerifyUUID);
if (TempDataTable.Rows.Count > 0)
{
if (TempDataTable.Rows[0]["Email"].ToString().ToLower() == Username.ToLower())
{
//get username by emailid
uname = GetUsernameByEmail(_Connect, Username);
/*Session["UserName"] = uname;
* Session["User"] = uname;*/
}
else if (TempDataTable.Rows[0]["User_Name"].ToString().ToLower() == Username.ToLower())
{
uname = Username;
}
}
return(uname);
}
public string VerifyUserByRegCode(IConnectToDB _Connect, 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();
string DecryptedPassword = "";
SQlinUserName._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "UUID", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = VerifyUUID
}
};
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 b.UUID = @UUID";
DataTable usernamedt = er_query.RUN_PARAMETER_QUERY(_Connect, SQlinUserName);
if (usernamedt.Rows.Count == 1)
{
foreach (DataRow _DR in usernamedt.Rows)
{
SQlinPassword._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "IDENTITIES_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = _DR.Field <long?>("identities_id").ToString()
}
};
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();
DecryptedPassword = er_sec.DecryptStringFromBytes_Aes(EncryptedPassword, er_sec.GetCryptPairforID(_Connect, IDENTITIES_ID, new ER_CRYPT_PAIR()));
if (passdt.Rows.Count != 0)
{
//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.
add addHelp = new add();
long?IdentitiesId = null;
//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" });
Values.UpdateIdentity IdentitiesModel = null;
IdentitiesModel = addHelp.UPDATE_ENTRY_Identities(_Connect, new Values.UpdateIdentity
{
I_IDENTITIES_ID = _DR.Field <long?>("IDENTITIES_ID"),
I_OBJECT_TYPE = _DR.Field <string>("OBJECT_TYPE"),
I_USER_NAME = _DR.Field <string>("USER_NAME"),
I_EDIPI = _DR.Field <string>("EDIPI"),
I_EMAIL = _DR.Field <string>("EMAIL"),
I_ACTIVE = "Y",
I_VERIFIED = "Y",
});
IdentitiesId = IdentitiesModel.O_IDENTITIES_ID;
VerificationHelper VH = new VerificationHelper();
VH.MarkVerificationsForID(_Connect, IDENTITIES_ID, "CreateUser");
VH.DisableVerificationsForID(_Connect, IDENTITIES_ID, "CreateUser");
}
break;
}
}
return(DecryptedPassword);
}
