//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); }