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 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);
}
private static List <CommandResult> CreateTablesCode(IConnectToDB _Connect, CreateTableDefinition thisDef, bool isChildCall)
{
List <CommandResult> results = new List <CommandResult>();
ScaffoldStructure thisAppStructure = new ScaffoldStructure();
foreach (var thisCurrentObject in thisDef.listOfObjects)
{
ER_DDL er_ddl = new ER_DDL();
List <ColumnStructure> ExistingColumnsList = new List <ColumnStructure>();
List <ColumnStructure> MetaColumnsList = new List <ColumnStructure>();
List <ColumnStructure> ScaffoldColumns = new List <ColumnStructure>();
List <ColumnStructure> ScaffoldColumnsForReOrder = new List <ColumnStructure>();
DataTable thisStageObjects = thisDef.theseObjects.AsEnumerable().Where(r => r.Field <string>(thisDef.BindRootFamilyOnObject).Replace(" ", "_").Replace("-", "_") == thisCurrentObject).CopyToDataTable();
DataColumnCollection ccthisStageObjects = thisStageObjects.Columns;
Scaffolds scaffolds = new Scaffolds();
ConnectToDB forCore = _Connect.Copy();
forCore.Schema = thisDef.desiredSchema;
string ObjectName = "";
string BaseObjectUUID = "";
string parentBaseColumnUUID = ""; //Unrelated to Outside
List <MultiValueChildScaffold> ObjectChildren = new List <MultiValueChildScaffold>();
if (ccthisStageObjects.Contains(thisDef.BindRootFamilyOnObject))
{
ObjectName = string.IsNullOrWhiteSpace(ObjectName) ? thisStageObjects.Rows[0].Field <string>(thisDef.BindRootFamilyOnObject).Replace(" ", "_") : ObjectName;
BaseObjectUUID = string.IsNullOrWhiteSpace(BaseObjectUUID) ? thisStageObjects.Rows[0].Field <Guid>(thisDef.BindRootColumnUUID).ToString().Replace("-", "_") : BaseObjectUUID;
string StageTableName = thisDef.rootTablePrefix + "_" + BaseObjectUUID;
string RootColumnName = StageTableName;
parentBaseColumnUUID = string.IsNullOrWhiteSpace(parentBaseColumnUUID) ? thisStageObjects.Rows[0].Field <Guid>(thisDef.ThisParent.BindParentRootColumnUUID).ToString().Replace("-", "_") : parentBaseColumnUUID;
string ParentTable = thisDef.ThisParent.ParentTablePrefix + "_" + parentBaseColumnUUID;
#region Create App Table
//if (!thisDef.sourceDatabase.Tables.Contains(ParentTable, forCore.Schema))
//{
thisDef.ThisParent.BindParentRootColumnFamilyName = ParentTable;
thisDef.ThisParent.FkColumnAvailableInBothTable = ParentTable + "_UUID";
//thisDef.ThisParent.BindParentRootColumnUUID = "BASE_" + ParentTable + "_UUID";
thisAppStructure = new ScaffoldStructure
{
_Connect = forCore.Copy(),
Name = ParentTable,
ScaffoldType = Tools.Box.Clone <string>(thisDef.ThisParent.ParentObjectLayer),
ColumnsList = Tools.Box.Clone <List <ColumnStructure> >(ScaffoldColumns),
useIdentityUUID = true,
RootColumn = Tools.Box.Clone <string>(thisDef.ThisParent.BindParentRootColumnFamilyName)
};
ScaffoldColumns.Add(new ColumnStructure {
_Name = "CORES_UUID", _DataType = "guid", _IsNull = false
});
ScaffoldColumns.Add(new ColumnStructure {
_Name = "APPLICATIONS_UUID", _DataType = "guid", _IsNull = false
});
results.AddRange(scaffolds.SYNC_SCAFFOLD(forCore, ParentTable, thisDef.ThisParent.ParentObjectLayer, ScaffoldColumns, true, thisDef.ThisParent.BindParentRootColumnFamilyName, true));
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
ExistingColumnsList.Add(new ColumnStructure {
_Name = "IDENTITIES_UUID", _DataType = "Guid", _DefaultValue = "", _IsNull = false
});
IConnectToDB Link2CSA = forCore.Copy();
Link2CSA.Schema2 = "CSA";
results.AddRange(er_ddl.ADD_KEY_FOREIGN(Link2CSA, "FK_" + ParentTable + "_" + "IDENTITIES", ParentTable, "IDENTITIES", ExistingColumnsList, ExistingColumnsList, true));
// results.AddRange(ER_DDL._DROP_VIEW(Link2CSA, ParentTable));
results.AddRange(ER_Generate._GENERATE_VIEW(Link2CSA, ParentTable, "Generated"));
//}
//else
//{
// //thisDef.ThisParent.FkColumnAvailableInBothTable = ParentTable + "_UUID";
// thisDef.ThisParent.BuildParentScaffold = false;
//}
#endregion
#region Create Stage Table & Any Children
//if (!thisDef.sourceDatabase.Tables.Contains(TableName, forCore.Schema))
if (!thisDef.sourceDatabase.Tables.Contains(StageTableName, forCore.Schema) || true)
{
bool createdAppScaffold = false;
long? T_OBJECT_SETS_ID = 0;
long? @T_PREV_OBJECT_SETS_ID = 0;
if (!createdAppScaffold && thisDef.ThisParent.BuildParentScaffold)
{
createdAppScaffold = true;
forCore.Schema = thisDef.desiredSchema;
}
ScaffoldColumns.Add(new ColumnStructure {
_Name = "CORES_UUID", _DataType = "guid", _IsNull = false
});
ScaffoldColumns.Add(new ColumnStructure {
_Name = "APPLICATIONS_UUID", _DataType = "guid", _IsNull = false
});
ScaffoldColumns.Add(new ColumnStructure {
_Name = "STAGES_UUID", _DataType = "guid", _IsNull = false
});
#region Loop Through Every Column
for (int i = 0; i < thisStageObjects.AsEnumerable().Count(); i++)
{
thisDef.desiredSchema = string.IsNullOrWhiteSpace(thisDef.desiredSchema) ? thisStageObjects.Rows[i].Field <string>("CORE_NAME").Replace(" ", "_") : thisDef.desiredSchema;
DataRow item = thisStageObjects.Rows[i];
string PROPERTY_NAME, PROPERTY_VALUE, OBJECT_TYPE;
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
if (T_PREV_OBJECT_SETS_ID != T_OBJECT_SETS_ID)
{
if (PROPERTY_NAME == "ID")
{
ColumnStructure thisColumn = new ColumnStructure();
bool addScaffold = true;
if (Array.IndexOf(StringTypes, OBJECT_TYPE.ToLower()) > -1)
{
HandleVarCharTypes(thisStageObjects, ref T_OBJECT_SETS_ID, out T_PREV_OBJECT_SETS_ID, ref i, ref item, ref PROPERTY_NAME, ref PROPERTY_VALUE, ref OBJECT_TYPE, out thisColumn);
}
else if (Array.IndexOf(NumberTypes, OBJECT_TYPE.ToLower()) > -1)
{
HandleNumberTypes(thisStageObjects, ref T_OBJECT_SETS_ID, out T_PREV_OBJECT_SETS_ID, ref i, ref item, ref PROPERTY_NAME, ref PROPERTY_VALUE, ref OBJECT_TYPE, out thisColumn);
}
else if ((Array.IndexOf(DateAndTimeTypes, OBJECT_TYPE.ToLower()) > -1))
{
HandleDateAndTimeTypes(thisStageObjects, ref T_OBJECT_SETS_ID, out T_PREV_OBJECT_SETS_ID, ref i, ref item, ref PROPERTY_NAME, ref PROPERTY_VALUE, ref OBJECT_TYPE, out thisColumn);
}
else if ((Array.IndexOf(ByteTypes, OBJECT_TYPE.ToLower()) > -1))
{
HandleByteTypes(thisStageObjects, ref T_OBJECT_SETS_ID, out T_PREV_OBJECT_SETS_ID, ref i, ref item, ref PROPERTY_NAME, ref PROPERTY_VALUE, ref OBJECT_TYPE, out thisColumn);
}
else if ((Array.IndexOf(MultiTypes, OBJECT_TYPE.ToLower()) > -1))
{
#region Define Multi Object for post creation.
List <ColumnStructure> TheseChildColumns = new List <ColumnStructure>();
var objectName = thisStageObjects.Rows[i].Field <string>("PROPERTY_VALUE");
TheseChildColumns.Add(new ColumnStructure {
_Name = "CORES_UUID", _DataType = "guid", _IsNull = false
});
TheseChildColumns.Add(new ColumnStructure {
_Name = "APPLICATIONS_UUID", _DataType = "guid", _IsNull = false
});
TheseChildColumns.Add(new ColumnStructure {
_Name = "STAGES_UUID", _DataType = "guid", _IsNull = false
});
TheseChildColumns.Add(new ColumnStructure {
_Name = objectName, _DataType = "Characters(MAX)", _IsNull = false
});
ObjectChildren.Add(new MultiValueChildScaffold
{
SchemaName = thisDef.desiredSchema,
TableName = "O_" + thisStageObjects.Rows[i].Field <Guid>("BASE_OBJECT_SETS_UUID").ToString().Replace(" ", "_").Replace("-", "_"),
ObjectLayer = "Design",
RootColumnName = PROPERTY_VALUE,
ParentRootColumnName = RootColumnName,
ScaffoldColumns = TheseChildColumns
});
addScaffold = false;
#endregion
}
if (addScaffold)
{
ScaffoldColumns.Add(thisColumn);
}
}
}
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
}
#endregion
forCore.Schema = thisDef.desiredSchema;
}
if (thisStageObjects.AsEnumerable().Count() > 0 && !string.IsNullOrWhiteSpace(BaseObjectUUID) && !string.IsNullOrWhiteSpace(parentBaseColumnUUID))
{
ScaffoldColumnsForReOrder = new List <ColumnStructure>();
ScaffoldColumnsForReOrder.Add(new ColumnStructure {
_Name = thisDef.ThisParent.FkColumnAvailableInBothTable, _DataType = thisDef.ThisParent.FkRelatedColumnType, _IsNull = false
});
ScaffoldColumnsForReOrder.AddRange(ScaffoldColumns);
ScaffoldColumns = ScaffoldColumnsForReOrder;
forCore.Schema = thisDef.desiredSchema;
ScaffoldStructure thisStage = new ScaffoldStructure
{
_Connect = forCore.Copy(),
Name = StageTableName,
ScaffoldType = Tools.Box.Clone <string>(thisDef.objectLayer),
ColumnsList = Tools.Box.Clone <List <ColumnStructure> >(ScaffoldColumns),
useIdentityUUID = true,
RootColumn = Tools.Box.Clone <string>(RootColumnName)
};
results.AddRange(scaffolds.SYNC_SCAFFOLD(forCore, StageTableName, thisDef.objectLayer, ScaffoldColumns, true, RootColumnName, true));
#region Add Foreign Key to Parent Application Table
forCore.Schema2 = thisDef.desiredSchema;
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
ExistingColumnsList.Add(new ColumnStructure {
_Name = thisDef.ThisParent.FkColumnAvailableInBothTable, _DataType = thisDef.ThisParent.FkRelatedColumnType, _DefaultValue = "", _IsNull = false
});
results.AddRange(er_ddl.ADD_KEY_FOREIGN(forCore, "FK_" + StageTableName, StageTableName, ParentTable, ExistingColumnsList, ExistingColumnsList, true));
#endregion
#region Add Foreign Key to Identities
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
ExistingColumnsList.Add(new ColumnStructure {
_Name = "IDENTITIES_UUID", _DataType = "Guid", _DefaultValue = "", _IsNull = false
});
// IConnectToDB Link2CSA = forCore.Copy();
Link2CSA = forCore.Copy();
Link2CSA.Schema2 = "CSA";
results.AddRange(er_ddl.ADD_KEY_FOREIGN(Link2CSA, "FK_" + StageTableName + "_IDENTITIES", StageTableName, "IDENTITIES", ExistingColumnsList, ExistingColumnsList, false));
#endregion
#region Create Each Multi Object Table
foreach (MultiValueChildScaffold item in ObjectChildren)
{
ScaffoldStructure thisMultiObject = new ScaffoldStructure
{
_Connect = forCore.Copy(),
Name = Tools.Box.Clone <string>(item.TableName),
ScaffoldType = Tools.Box.Clone <string>(item.ObjectLayer),
ColumnsList = Tools.Box.Clone <List <ColumnStructure> >(item.ScaffoldColumns),
useIdentityUUID = true,
RootColumn = Tools.Box.Clone <string>(item.TableName)
};
thisStage.children.Add(thisMultiObject);
List <ColumnStructure> itemScaffoldColumnsForReOrder = new List <ColumnStructure>();
itemScaffoldColumnsForReOrder.Add(new ColumnStructure {
_Name = StageTableName + "_UUID", _DataType = thisDef.ThisParent.FkRelatedColumnType, _IsNull = false
});
itemScaffoldColumnsForReOrder.AddRange(item.ScaffoldColumns);
item.ScaffoldColumns = itemScaffoldColumnsForReOrder;
results.AddRange(scaffolds.SYNC_SCAFFOLD(forCore, item.TableName, item.ObjectLayer, item.ScaffoldColumns, true, item.TableName, true));
#region Add Foreign to Parent Stage Table
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
// ExistingColumnsList.Add(new ColumnStructure { _Name = item.ParentRootColumnName, _DataType = "Guid", _DefaultValue = "", _IsNull = false });
ExistingColumnsList.Add(new ColumnStructure {
_Name = StageTableName + "_UUID", _DataType = thisDef.ThisParent.FkRelatedColumnType, _DefaultValue = "", _IsNull = false
});
results.AddRange(er_ddl.ADD_KEY_FOREIGN(forCore, "FK_" + item.TableName + "_" + item.RootColumnName, item.TableName, StageTableName, ExistingColumnsList, ExistingColumnsList, true));
#endregion
#region Add Foreign Key to Identities
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
ExistingColumnsList.Add(new ColumnStructure {
_Name = "IDENTITIES_UUID", _DataType = "Guid", _DefaultValue = "", _IsNull = false
});
IConnectToDB Link2CSA2 = forCore.Copy();
Link2CSA2.Schema2 = "CSA";
results.AddRange(er_ddl.ADD_KEY_FOREIGN(Link2CSA2, "FK_" + item.TableName + "_" + "IDENTITIES", item.TableName, "IDENTITIES", ExistingColumnsList, ExistingColumnsList, false));
#endregion
//results.AddRange(ER_DDL._DROP_VIEW(forCore, item.TableName));
// results.AddRange(ER_Generate._GENERATE_VIEW(forCore, item.TableName, "Generated"));
}
thisAppStructure.children.Add(thisStage);
//results.AddRange(ER_DDL._DROP_VIEW(forCore, StageTableName));
results.AddRange(ER_Generate._GENERATE_VIEW_WITH_CHILDREN(forCore, StageTableName, "Generated"));
#endregion
}
#endregion
}
else
{
//IF CASTGOOP Not Present
//Update Logic
}
}
return(results);
}