Пример #1
0
        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);
        }
Пример #2
0
        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);
        }