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 static DynamicModels.RootReport PopulateReport(IConnectToDB _Connect1, DynamicModels.RootReport thisModel, string ReportName, DataTable thisDT)
{
ConnectToDB _Connect = _Connect1.Copy();
_Connect.Schema = "DYNAMIC";
thisModel.ReportName = ReportName;
if (thisDT.Rows.Count > 0)
{
thisModel.ReportFilters = new List <DynamicModels.RootReportFilter>();
// thisModel.ProcedureName = thisDT.Rows[0].Field<string>("PROCEDURE_NAME");
thisModel.Template_ID = thisDT.Rows[0].Field <long>("TEMPLATE_ID");
thisModel.Template = thisDT.Rows[0].Field <string>("TEMPLATE");
thisModel.ReportName = thisDT.Rows[0].Field <string>("REPORT_NAME");
thisModel.ReportFilters = new List <DynamicModels.RootReportFilter>();
for (int i = 0; i < thisDT.Rows.Count; i++)
{
DynamicModels.RootReportFilter thisReport = new DynamicModels.RootReportFilter();
thisReport.FilterName = thisDT.Rows[i].Field <string>("FILTER_NAME");
thisReport.PrettyName = thisDT.Rows[i].Field <string>("PRETTY_NAME");
thisReport.ParamSize = Convert.ToInt32(thisDT.Rows[i].Field <long>("PARAM_SIZE"));
thisReport.SearchParamSize = Convert.ToInt32(thisDT.Rows[i].Field <long>("SEARCH_DB_SIZE"));
thisReport.DBType = GetSqlDbType(thisDT.Rows[i].Field <string>("DB_TYPE"));
thisReport.SearchDBType = GetSqlDbType(thisDT.Rows[i].Field <string>("SEARCH_DB_TYPE"));
thisReport.FilterSelect = thisDT.Rows[i].Field <string>("FILTER_SELECT");
thisReport.FilterType = GetFilterType(thisDT.Rows[i].Field <string>("FILTER_TYPE"));
thisReport.inPickList = thisDT.Rows[i].Field <string>("IN_PICK_LIST") == "T" ? true : false;
thisReport.ParamValue = thisDT.Rows[i].Field <string>("ParamValue");
thisReport.Required = thisDT.Rows[i].Field <string>("REQUIRED") == "T" ? true : false;
thisReport.AlternateTemplate = thisDT.Rows[i].Field <string>("ALTERNATE_TEMPLATE");
thisReport.AlternateReportName = thisDT.Rows[i].Field <string>("ALTERNATE_REPORT_NAME");
thisReport.AlternateSchema = thisDT.Rows[i].Field <string>("ALTERNATE_SCHEMA");
thisReport.AlternateSource = thisDT.Rows[i].Field <string>("ALTERNATE_SOURCE");
thisModel.ReportFilters.Add(thisReport);
}
}
else
{
thisModel.ReportFilters = new List <DynamicModels.RootReportFilter>();
}
return(thisModel);
}
public override DynamicModels.ReportDefinitions ReportMapper(IConnectToDB _Connection, DynamicModels.ReportDefinitions thisDefinition)
{
_DynamicOutputProcedures CoreOut = new _DynamicOutputProcedures();
DynamicModels.RootReport CurrentReportSelected = new DynamicModels.RootReport();
CurrentReportSelected.ReportName = thisDefinition.SearchReport;
DataTable Result_Set = ER_Query._RUN_PARAMETER_QUERY(_Connection, new ER_Query.Parameter_Run
{
_dbParameters = new List <DBParameters>
{
new DBParameters {
ParamName = "P_TABLE_NAME", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = thisDefinition.SearchReport
},
new DBParameters {
ParamName = "P_SCHEMA", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = _Connection.Schema
},
new DBParameters {
ParamName = "P_PROC", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = "SP_S" + thisDefinition.SearchReport + "_SEARCH"
},
new DBParameters {
ParamName = "P_REPORT_NAME", MSSqlParamDataType = SqlDbType.VarChar, ParamValue = thisDefinition.SearchReport
}
},
sqlIn = "select 0 rownumb," +
"0 ROOT_REPORT_ID," +
"0 BASE_ROOT_REPORT_ID," +
"0 PREV_ROOT_REPORT_ID," +
"'Y' ENABLED," +
"getDate() DT_CREATED," +
"getDate() DT_UPDATE," +
" CAST(0 AS bigint) TEMPLATE_ID," +
"'Tempt' TEMPLATE," +
"@P_REPORT_NAME REPORT_NAME," +
"@P_PROC PROCEDURE_NAME," +
" CAST(0 AS bigint) ROOT_REPORT_FILTER_ID," +
" CAST(0 AS bigint) BASE_ROOT_REPORT_FILTER_ID," +
" CAST(0 AS bigint) PREV_ROOT_REPORT_FILTER_ID," +
"'N' REQUIRED," +
"COLUMN_NAME FILTER_NAME," +
"COLUMN_NAME PRETTY_NAME," +
"'MultiSelect' FILTER_TYPE," +
"'T' IN_PICK_LIST," +
"CASE " +
" WHEN a.DATA_TYPE = 'int' then 'Int' " +
" WHEN a.DATA_TYPE = 'bigint' then 'BigInt' " +
" WHEN a.DATA_TYPE = 'decimal' then 'Decimal'" +
" WHEN a.DATA_TYPE = 'varchar' then 'VarChar' " +
" WHEN a.DATA_TYPE = 'char' then 'Char' " +
" WHEN a.DATA_TYPE = 'varbinary' then 'VarBinary' " +
" WHEN a.DATA_TYPE in ('datetime') then 'DateTime'" +
" WHEN a.DATA_TYPE in ('datetime2') then 'DateTime2'" +
" WHEN a.DATA_TYPE in ('smalldatetime') then 'SmallDateTime'" +
" WHEN a.DATA_TYPE in ('text') then 'SmallDateTime'" +
" WHEN a.DATA_TYPE in ('bit') then 'Bit'" +
" WHEN a.DATA_TYPE = 'NVarChar' then 'VarChar'" +
" WHEN a.DATA_TYPE in ('date') then 'Date'" +
" WHEN a.DATA_TYPE in ('money', 'numeric') then 'Decimal'" +
" WHEN a.DATA_TYPE in ('uniqueidentifier') then 'UniqueIdentifier'" +
" else a.DATA_TYPE END DB_TYPE," +
"CAST(isNull(a.CHARACTER_MAXIMUM_LENGTH,0) AS bigint) PARAM_SIZE," +
"'VarChar' SEARCH_DB_TYPE," +
"CAST(-1 AS bigint) SEARCH_DB_SIZE," +
"'' ParamValue," +
"'DISTINCT ' + a.COLUMN_NAME FILTER_SELECT," +
"'' ALTERNATE_REPORT_NAME," +
"'' ALTERNATE_SCHEMA," +
"'' ALTERNATE_SOURCE," +
"'' ALTERNATE_TEMPLATE from INFORMATION_SCHEMA.COLUMNS a " +
"where TABLE_SCHEMA = @P_SCHEMA and upper(TABLE_NAME) = upper(@P_TABLE_NAME)"
});
// DynamicModels.CoreReport thisReport = thisDef.theReport;
DynamicModels.RootReport thisReport = UniversalHelper.PopulateReport(_Connection, new DynamicModels.RootReport(), CurrentReportSelected.ReportName, Result_Set);
thisDefinition.theReport = thisReport;
thisDefinition.theReport.ProcedureName = "SP_S_" + thisDefinition.SearchReport + "_SEARCH";
Revamp.IO.Helpers.DataCompositions.UniversalHelper.GetTableStructure(_Connection, thisDefinition, CoreOut, thisDefinition.theReport);
return(thisDefinition);
}
