//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 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 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 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 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 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 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 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);
}
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);
}
public static DynamicModels.Input.Custom_Reports LoadCustomReport(IConnectToDB _Connect, SessionObjects _SessionModel, DynamicModels.Input.Custom_Reports Report, AuditDB AuditCommand = null)
{
_DynamicInputProcedures CoreIH = new _DynamicInputProcedures();
DynamicModels.Input.Custom_Reports thisModel = Report;
if (thisModel != null)
{
thisModel.I_BASE_CUSTOM_REPORT_ID = thisModel.I_BASE_CUSTOM_REPORT_ID == null ? 0 : thisModel.I_BASE_CUSTOM_REPORT_ID;
thisModel.I_PREV_CUSTOM_REPORT_ID = thisModel.I_PREV_CUSTOM_REPORT_ID == null ? 0 : thisModel.I_PREV_CUSTOM_REPORT_ID;
thisModel.I_ENABLED = thisModel.I_ENABLED == null ? 'Y' : thisModel.I_ENABLED;
thisModel.I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id;
thisModel = CoreIH.InsertCustomReport(_Connect, thisModel, AuditCommand);
if (thisModel.Filters != null && thisModel.O_CUSTOM_REPORT_ID > 0)
{
for (int i = 0; i < thisModel.Filters.Count; i++)
{
thisModel.Filters[i].I_CUSTOM_REPORT_ID = thisModel.O_CUSTOM_REPORT_ID;
thisModel.Filters[i].I_BASE_FILTER_ID = 0;
thisModel.Filters[i].I_PREV_FILTER_ID = 0;
thisModel.Filters[i].I_ENABLED = 'Y';
thisModel.Filters[i].I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id;
thisModel.Filters[i].I_FILTER_ORDER = i;
thisModel.Filters[i].I_FILTER_ALIAS = String.IsNullOrWhiteSpace(thisModel.Filters[i].I_FILTER_ALIAS) ? thisModel.Filters[i].I_FILTER : thisModel.Filters[i].I_FILTER_ALIAS;
thisModel.Filters[i] = CoreIH.InsertCustomReportFilter(_Connect, thisModel.Filters[i]);
}
}
if (thisModel.Columns != null && thisModel.O_CUSTOM_REPORT_ID > 0)
{
for (int i = 0; i < thisModel.Columns.Count; i++)
{
thisModel.Columns[i].I_CUSTOM_REPORT_ID = thisModel.O_CUSTOM_REPORT_ID;
thisModel.Columns[i].I_BASE_CUSTOM_REPORT_COLUMN_ID = 0;
thisModel.Columns[i].I_PREV_CUSTOM_REPORT_COLUMN_ID = 0;
thisModel.Columns[i].I_ENABLED = 'Y';
thisModel.Columns[i].I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id;
thisModel.Columns[i] = CoreIH.InsertCustomReportColumns(_Connect, thisModel.Columns[i]);
}
}
else
{
StringBuilder _sqlin = new StringBuilder();
//TODO: Get Columns from DB
if (thisModel.O_CUSTOM_REPORT_ID > 0)
{
_sqlin.AppendLine("BEGIN");
//string _Schema = TemplateName == "SYSTEM" ? "CSA" : TemplateName;
_sqlin.AppendLine("DECLARE @P_ROOT_REPORT_ID bigint = '" + thisModel.I_ROOT_REPORT_ID + "'");
_sqlin.AppendLine("SELECT ORIGINAL_COLUMN, ROOT_REPORT_COLUMN_ID");
_sqlin.AppendLine("FROM [DYNAMIC].[ROOT_REPORTS] a");
_sqlin.AppendLine("INNER JOIN [DYNAMIC].ROOT_REPORT_COLUMNS b on a.ROOT_REPORT_ID = b.ROOT_REPORT_ID");
_sqlin.AppendLine("WHERE a.ROOT_REPORT_ID = @P_ROOT_REPORT_ID");
_sqlin.AppendLine("ORDER BY ROOT_REPORT_COLUMN_ID ASC");
_sqlin.AppendLine("END ");
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run
{
sqlIn = _sqlin.ToString(),
_dbParameters = new List <DBParameters>()
};
DataTable ReportColumnns = ER_Query._RUN_PARAMETER_QUERY(_Connect, SQlin);
DataColumnCollection DCC = ReportColumnns.Columns;
for (int i = 0; i < ReportColumnns.Rows.Count; i++)
{
DynamicModels.Input.Custom_Report_Column thisColumn = new DynamicModels.Input.Custom_Report_Column();
thisColumn.I_CUSTOM_REPORT_ID = thisModel.O_CUSTOM_REPORT_ID;
thisColumn.I_BASE_CUSTOM_REPORT_COLUMN_ID = 0;
thisColumn.I_PREV_CUSTOM_REPORT_COLUMN_ID = 0;
thisColumn.I_ENABLED = 'Y';
thisColumn.I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id;
thisColumn.I_ORIGINAL_COLUMN = ReportColumnns.Rows[i].Field <string>("ORIGINAL_COLUMN");
thisColumn.I_ALIAS_AREA = ReportColumnns.Rows[i].Field <string>("ORIGINAL_COLUMN");
thisColumn.I_COLUMN_AREA = ReportColumnns.Rows[i].Field <string>("ORIGINAL_COLUMN");
thisColumn = CoreIH.InsertCustomReportColumns(_Connect, thisColumn);
}
}
}
if (Report.V_ROOT_REPORT_NAME != null)
{
//Report.RootReport = CoreHelper.ReportMapper(_Connect, new DynamicModels.ReportDefinitions
//{
// GetStructure = false,
// SearchReport = Report.V_ROOT_REPORT_NAME
//}).theReport;
DynamicModels.Input.Custom_Report_Order_Sets thisOrderSet = new DynamicModels.Input.Custom_Report_Order_Sets();
if (Report.RootReport.ReportColumnOrderSet != null && Report.RootReport.ReportColumnOrderSet.Count > 0)
{
thisOrderSet = new DynamicModels.Input.Custom_Report_Order_Sets
{
I_BASE_CUSTOM_REPORTS_ORDER_SET_ID = 0,
I_PREV_CUSTOM_REPORTS_ORDER_SET_ID = 0,
I_CUSTOM_REPORT_ID = thisModel.O_CUSTOM_REPORT_ID,
I_ENABLED = 'Y',
I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id
};
thisOrderSet = _DynamicInputProcedures._InsertCustomReportOrderSet(_Connect, thisOrderSet);
if (Report.RootReport.ReportColumnOrderSet != null && thisOrderSet.O_CUSTOM_REPORTS_ORDER_SET_ID > 0)
{
for (int j = 0; j < Report.RootReport.ReportColumnOrderSet.Count; j++)
{
DynamicModels.ReportOrder thisOrder = Report.RootReport.ReportColumnOrderSet[j];
_DynamicInputProcedures._InsertCustomReportOrder(_Connect, new DynamicModels.Input.Custom_Report_Order
{
I_BASE_CUSTOM_REPORTS_ORDER_ID = 0,
I_PREV_CUSTOM_REPORTS_ORDER_ID = 0,
I_ENABLED = 'Y',
I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id,
I_CUSTOM_REPORTS_ORDER_SET_ID = thisOrderSet.O_CUSTOM_REPORTS_ORDER_SET_ID,
I_SORT_COLUMN = thisOrder.sort_column,
I_SORT_DIRECTION = thisOrder.sort_direction,
I_SORT_ORDER = thisOrder.sort_order
});
}
}
}
}
}
return(Report);
}
public static void GetTableStructure(IConnectToDB _Connect, DynamicModels.ReportDefinitions thisDefinition, _DynamicOutputProcedures CoreOut, DynamicModels.RootReport CurrentReportSelected)
{
thisDefinition.theReport = CurrentReportSelected;
if (thisDefinition.GetStructure)
{
if (string.IsNullOrEmpty(thisDefinition.theReport.Source))
{
thisDefinition.theStructure = CoreOut.GetTableStruct(_Connect, CurrentReportSelected.ProcedureName, thisDefinition.DynamicColumns);
}
else
{
if (!string.IsNullOrEmpty(thisDefinition.DynamicColumns))
{
//TODO: Convert to Procedure
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run
{
sqlIn = "select top 0 '-' as rownumb, " + thisDefinition.DynamicColumns + " from " + _Connect.Schema + "." + thisDefinition.theReport.Source + " where 1 = 2",
_dbParameters = new List <DBParameters>()
};
DataTable thisTableColumns = ER_Query._RUN_PARAMETER_QUERY(_Connect, SQlin);
DataColumnCollection DCC = thisTableColumns.Columns;
thisDefinition.theStructure = thisTableColumns;
}
else
{
//TODO: Convert to Procedure
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run
{
sqlIn = "select ORDINAL_POSITION, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @TABLE_SCHEMA and upper(TABLE_NAME) = upper(@TABLE_NAME) order by ORDINAL_POSITION ASC",
_dbParameters = new List <DBParameters> {
new DBParameters {
ParamName = "TABLE_SCHEMA", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = _Connect.Schema
},
new DBParameters {
ParamName = "TABLE_NAME", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = thisDefinition.theReport.Source
}
}
};
DataTable thisTableColumns = ER_Query._RUN_PARAMETER_QUERY(_Connect, SQlin);
DataColumnCollection DCC = thisTableColumns.Columns;
System.Data.DataTable table = new System.Data.DataTable();
DataColumn column;
column = new DataColumn();
column.ColumnName = "rownumb";
column.Caption = "rownumb";
table.Columns.Add(column);
for (int i = 0; i < thisTableColumns.Rows.Count; i++)
{
column = new DataColumn();
column.ColumnName = thisTableColumns.Rows[i]["COLUMN_NAME"].ToString();
column.Caption = thisTableColumns.Rows[i]["COLUMN_NAME"].ToString();
table.Columns.Add(column);
}
thisDefinition.theStructure = table;
}
}
}
}
public DataTable GetContainerApplications(IConnectToDB _Connect, string coreid, string containers_id)
{
AppHelper appHelper = new AppHelper();
List <string> appids = new List <string>();
ER_Query er_query = new ER_Query();
ER_Query.Parameter_Run SQlinAppId = new ER_Query.Parameter_Run();
ER_Query.Parameter_Run SQlinApps = new ER_Query.Parameter_Run();
SQlinAppId._dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "CORES_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = coreid
},
new DBParameters {
ParamName = "CONTAINERS_ID", MSSqlParamDataType = SqlDbType.BigInt, ParamValue = containers_id
}
};
SQlinAppId.sqlIn = "Select DISTINCT(APPLICATIONS_ID) from CSA.VW__STAGES s where CONTAINERS_ID = @CONTAINERS_ID and cores_id = @CORES_ID and RENDITION in (Select MAX(Convert(int,RENDITION)) from CSA.VW__STAGES s1 where s.ROOT_APPLICATION = s1.ROOT_APPLICATION) order by APPLICATIONS_ID desc";
DataTable TempDataTable = er_query.RUN_PARAMETER_QUERY(_Connect, SQlinAppId);
for (int i = 0; i < TempDataTable.Rows.Count; i++)
{
appids.Add(TempDataTable.Rows[i]["applications_id"].ToString());
}
string query = "Select a0.*,(select top(1) [USER_NAME] from CSA.VW__ACTIVITY a INNEr join CSA.VW__IDENTITIES i ON a.IDENTITIES_ID=i.IDENTITIES_ID " +
" where a.APPLICATIONS_ID=a0.APPLICATIONS_ID and a.OBJECT_TYPE='add object' and Table_Source='APPLICATIONS')USER_NAME from CSA.VW__APPLICATIONS a0 " +
" INNER JOIN CSA.VW__APPLICATIONS a1 ON a0.APPLICATIONS_ID = a1.APPLICATIONS_ID and a0.APPLICATION_NAME not in('Core Settings','Revamp System') and a1.RENDITION in (Select MAX(Convert(int,RENDITION)) from CSA.VW__APPLICATIONS a1a where a1.ROOT_APPLICATION = a1a.ROOT_APPLICATION)";
if (TempDataTable.Rows.Count > 0)
{
query += " where a0.APPLICATIONS_ID in (";
for (int i = 0; i < appids.Count; i++)
{
// apps = appHelper.FindbyColumnID(_Connect, "applications_id", appids[i]);
if (i != (appids.Count - 1))
{
query += appids[i] + ",";
}
else
{
query += appids[i];
}
}
query += ") and a0.Enabled='Y' Order By DT_CREATED DESC";
}
else
{
query += " where a0.APPLICATIONS_ID in (1)"; //used to return nothing if no apps belong to the container passed in
}
SQlinApps.sqlIn = query;
DataTable appsDT = er_query.RUN_PARAMETER_QUERY(_Connect, SQlinApps);
return(appsDT);
}
