protected override void LoadScheme() { OwnerTable = "INCIDENTS"; this.Fields.Add(new QField("IncId", LocRM.GetString("IncId"), "IncidentId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, true)); this.Fields.Add(new QField("IncTitle", LocRM.GetString("IncTitle"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("IncType", LocRM.GetString("IncType"), "TypeName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "INCIDENT_TYPES", "TypeId", "TypeId"))); this.Fields.Add(new QField("IncTypeId", LocRM.GetString("IncType"), "TypeId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "INCIDENT_TYPES", "TypeId", "TypeId"))); this.Dictionary.Add(new QDictionary(this.Fields["IncTypeId"], this.Fields["IncType"], "SELECT TypeId as Id, TypeName as Value FROM INCIDENT_TYPES")); //this.Fields.Add(new QField("IncState",LocRM.GetString("IncState"),"StateName", DbType.String,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort, //new QFieldJoinRelation(this.OwnerTable, "INCIDENT_STATE_LANGUAGE", "StateId", "StateId", "LanguageId"))); //this.Fields.Add(new QField("IncStateId","IncStateId","StateId", DbType.Int32,QFieldUsingType.Abstract, // new QFieldJoinRelation(this.OwnerTable, "INCIDENT_STATE_LANGUAGE", "StateId", "StateId", "LanguageId"))); // OZ 2008-11-18 Added Overdue state this.Fields.Add(new QField("IncState", LocRM.GetString("IncState"), "(SELECT StateName FROM INCIDENT_STATE_LANGUAGE WHERE LanguageId = {1} AND StateId = " + "(CASE WHEN " + "({0}.StateId IN (1, 2, 6, 7) " + "AND " + "( " + "{0}.ExpectedResolveDate < GetUtcDate() OR (EXISTS(SELECT INM.NewMessage FROM INCIDENT_NEWMESSAGE INM WHERE INM.IncidentId = {0}.IncidentId AND INM.NewMessage = 1) AND {0}.ExpectedResponseDate < GetUtcDate())) " + ") " + "OR " + "( " + "{0}.StateId IN (1, 2, 6) " + "AND {0}.ResponsibleId <= 0 " + "AND {0}.ExpectedAssignDate < GetUtcDate() " + ")" + "THEN 3 ELSE {0}.StateId " + "END))", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("IncStateId", "IncStateId", "(CASE WHEN " + "({0}.StateId IN (1, 2, 6, 7) " + "AND " + "( " + "{0}.ExpectedResolveDate < GetUtcDate() OR (EXISTS(SELECT INM.NewMessage FROM INCIDENT_NEWMESSAGE INM WHERE INM.IncidentId = {0}.IncidentId AND INM.NewMessage = 1) AND {0}.ExpectedResponseDate < GetUtcDate())) " + ") " + "OR " + "( " + "{0}.StateId IN (1, 2, 6) " + "AND {0}.ResponsibleId <= 0 " + "AND {0}.ExpectedAssignDate < GetUtcDate() " + ")" + "THEN 3 ELSE {0}.StateId " + "END)", DbType.Int32, QFieldUsingType.Abstract, false)); this.Dictionary.Add(new QDictionary(this.Fields["IncStateId"], this.Fields["IncState"], "SELECT StateId as Id, StateName as Value FROM INCIDENT_STATE_LANGUAGE WHERE LanguageId = {0}")); if (Mediachase.Ibn.License.ProjectManagement) { this.Fields.Add(new QField("IncProject", LocRM.GetString("IncProject"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"))); this.Fields.Add(new QField("IncProjectId", LocRM.GetString("IncProjectId"), "ProjectId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"))); this.Dictionary.Add(new QDictionary(this.Fields["IncProjectId"], this.Fields["IncProject"], "SELECT ProjectId as Id, Title as [Value] FROM PROJECTS ORDER BY [Value]")); // 2009-09-30 this.Fields.Add(new QField("PrjCode", LocRM.GetString("PrjCode"), "ProjectCode", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"))); } this.Fields.Add(new QField("IncPriority", LocRM.GetString("IncPriority"), "PriorityName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PRIORITY_LANGUAGE", "PriorityId", "PriorityId", "LanguageId"))); this.Fields.Add(new QField("IncPriorityId", LocRM.GetString("IncPriority"), "PriorityId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "PRIORITY_LANGUAGE", "PriorityId", "PriorityId", "LanguageId"))); this.Dictionary.Add(new QDictionary(this.Fields["IncPriorityId"], this.Fields["IncPriority"], "SELECT PriorityId as Id, PriorityName as Value FROM PRIORITY_LANGUAGE WHERE LanguageId = {0}")); this.Fields.Add(new QField("IncCreator", LocRM.GetString("IncCreator"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Fields.Add(new QField("IncCreatorId", LocRM.GetString("IncCreatorId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["IncCreatorId"], this.Fields["IncCreator"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT CreatorId FROM INCIDENTS) ORDER BY [Value]")); // this.Fields.Add(new QField("IncManager",LocRM.GetString("IncManager"),"{0}.FirstName + ' ' + {0}.LastName",DbType.String,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort, // new QFieldJoinRelation(this.OwnerTable,"USERS","ManagerId","PrincipalId"))); // this.Fields.Add(new QField("IncManagerId",LocRM.GetString("IncManagerId"),"PrincipalId",DbType.Int32,QFieldUsingType.Abstract, // new QFieldJoinRelation(this.OwnerTable,"USERS","ManagerId","PrincipalId"))); // // this.Dictionary.Add(new QDictionary(this.Fields["IncManagerId"],this.Fields["IncManager"],"SELECT DISTINCT PrincipalId as Id, (FirstName +' ' + LastName) as Value FROM USERS WHERE PrincipalId IN (SELECT ManagerId FROM INCIDENTS)")); this.Fields.Add(new QField("IncCreationDate", LocRM.GetString("IncCreationDate"), "CreationDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // this.Fields.Add(new QField("IncActivationDate",LocRM.GetString("IncActivationDate"), "ActivationDate",DbType.DateTime,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort, false)); // this.Fields.Add(new QField("IncCloseDate",LocRM.GetString("IncCloseDate"), "CloseDate",DbType.DateTime,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort, false)); this.Fields.Add(new QField("IncGeneralCategories", LocRM.GetString("IncGeneralCategories"), "CategoryName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "OBJECT_CATEGORY", "IncidentId", "ObjectId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Issue).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("OBJECT_CATEGORY", "CATEGORIES", "CategoryId", "CategoryId") })); this.Fields.Add(new QField("IncGeneralCategoriesId", "IncGeneralCategoriesId", "CategoryId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "OBJECT_CATEGORY", "IncidentId", "ObjectId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Issue).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("OBJECT_CATEGORY", "CATEGORIES", "CategoryId", "CategoryId") })); this.Dictionary.Add(new QDictionary(this.Fields["IncGeneralCategoriesId"], this.Fields["IncGeneralCategories"], "SELECT CategoryId As Id, CategoryName As Value FROM CATEGORIES")); this.Fields.Add(new QField("IncIncidentCategories", LocRM.GetString("IncIncidentCategories"), "CategoryName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "INCIDENT_CATEGORY", "IncidentId", "IncidentId"), new QFieldJoinRelation("INCIDENT_CATEGORY", "INCIDENT_CATEGORIES", "CategoryId", "CategoryId") })); this.Fields.Add(new QField("IncIncidentCategoriesId", LocRM.GetString("IncIncidentCategories"), "CategoryId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "INCIDENT_CATEGORY", "IncidentId", "IncidentId"), new QFieldJoinRelation("INCIDENT_CATEGORY", "INCIDENT_CATEGORIES", "CategoryId", "CategoryId") })); this.Dictionary.Add(new QDictionary(this.Fields["IncIncidentCategoriesId"], this.Fields["IncIncidentCategories"], "SELECT CategoryId As Id, CategoryName As Value FROM INCIDENT_CATEGORIES")); // New fields 2006-07-07 // this.Fields.Add(new QField("Inc ",LocRM.GetString("IncDescription"),"Description",DbType.String,QFieldUsingType.Field|QFieldUsingType.Filter, false)); // this.Fields.Add(new QField("IncResolution",LocRM.GetString("IncResolution"),"Resolution",DbType.String,QFieldUsingType.Field|QFieldUsingType.Filter, false)); // this.Fields.Add(new QField("IncWorkaround",LocRM.GetString("IncWorkaround"),"Workaround",DbType.String,QFieldUsingType.Field|QFieldUsingType.Filter, false)); //Commented 2007-10-17 //// New Addod 2006-10-18 //this.Fields.Add(new QField("IncTaskTime", LocRM.GetString("IncTaskTime"), "TaskTime", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // 2006-12-13 OZ: Client //this.Fields.Add(new QField("IncClient", LocRM.GetString("PrjClient"), // "(ISNULL((SELECT O.OrgName FROM Organizations O WHERE O.OrgId = {0}.OrgId),(SELECT V.FullName FROM VCard V WHERE V.VCardId = {0}.VCardId)))", // DbType.String, // QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); //this.Fields.Add(new QField("IncClientId", "IncClientId", // "(ISNULL({0}.OrgId,-{0}.VCardId))", // DbType.Int32, // QFieldUsingType.Abstract)); //this.Dictionary.Add(new QDictionary(this.Fields["IncClientId"], this.Fields["IncClient"], // "SELECT (O.OrgId) AS Id , O.OrgName as Value FROM Organizations O UNION SELECT (-V.VCardId) AS Id , V.FullName as Value FROM VCard V")); // 2006-12-13 OZ: IncidentBox this.Fields.Add(new QField("IncBox", LocRM.GetString("IncBox"), "Name", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "IncidentBox", "IncidentBoxId", "IncidentBoxId"))); this.Fields.Add(new QField("IncBoxId", "IncBoxId", "IncidentBoxId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "IncidentBox", "IncidentBoxId", "IncidentBoxId"))); this.Dictionary.Add(new QDictionary(this.Fields["IncBoxId"], this.Fields["IncBox"], "SELECT IncidentBoxId as Id, Name as Value FROM IncidentBox")); // 2007-07-20 OZ: Incident Responsible this.Fields.Add(new QField("IncResponsible", LocRM.GetString("IncResponsible"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "ResponsibleId", "PrincipalId"))); this.Fields.Add(new QField("IncResponsibleId", LocRM.GetString("IncResponsibleId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "ResponsibleId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["IncResponsibleId"], this.Fields["IncResponsible"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT ResponsibleId FROM INCIDENTS) ORDER BY [Value]")); // 2010-02-16 OR: Current Responsible this.Fields.Add(new QField("IncCurrentResponsible", LocRM.GetString("IncCurrentResponsible"), "( " + "CASE " + "WHEN {0}.CurrentResponsibleId = -2 THEN '{{IbnFramework.Incident:RespNotSet}}' " + "WHEN {0}.CurrentResponsibleId = -3 THEN '{{IbnFramework.Incident:RespGroup}}' " + "WHEN {0}.CurrentResponsibleId = -4 THEN '{{IbnFramework.Incident:RespAllDenied}}' " + "ELSE (SELECT UR.FirstName + ' ' + UR.LastName FROM USERS AS UR WHERE UR.PrincipalId = {0}.CurrentResponsibleId) " + "END " + ") ", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); //this.Fields.Add(new QField("IncCurrentResponsible", LocRM.GetString("IncCurrentResponsible"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, // new QFieldJoinRelation(this.OwnerTable, "USERS", "CurrentResponsibleId", "PrincipalId"))); this.Fields.Add(new QField("IncCurrentResponsibleId", LocRM.GetString("IncCurrentResponsibleId"), "CurrentResponsibleId", DbType.Int32, QFieldUsingType.Abstract, false)); this.Dictionary.Add(new QDictionary(this.Fields["IncCurrentResponsibleId"], this.Fields["IncCurrentResponsible"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT CurrentResponsibleId FROM INCIDENTS) UNION ALL SELECT -2 as Id, '{{IbnFramework.Incident:RespNotSet}}' as Value UNION ALL SELECT -3 as Id, '{{IbnFramework.Incident:RespGroup}}' as Value UNION ALL SELECT -4 as Id, '{{IbnFramework.Incident:RespAllDenied}}' as Value ORDER BY [Value]")); // 2007-10-15: New Fields this.Fields.Add(new QField("IncModifiedDate", LocRM.GetString("IncModifiedDate"), "ModifiedDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("IncExpectedResponseDate", LocRM.GetString("IncExpectedResponseDate"), "ExpectedResponseDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("IncExpectedResolveDate", LocRM.GetString("IncExpectedResolveDate"), "ExpectedResolveDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("IncActualOpenDate", LocRM.GetString("IncActualOpenDate"), "ActualOpenDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // 2007-10-17 New Field is assigned with TimeTracking this.Fields.Add(new QField("IncTaskTime", LocRM.GetString("TaskTime"), "TaskTime", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("IncTotalMinutes", LocRM.GetString("TotalMinutes"), "TotalMinutes", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("IncTotalApproved", LocRM.GetString("TotalApproved"), "TotalApproved", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // 2008-09-09: New Fields this.Fields.Add(new QField("IncActualFinishDate", LocRM.GetString("IncActualFinishDate"), "ActualFinishDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); /////////////////////////// // OZ 2009-05-18 IncOrg = OrgUid || (ClientUID -> OrganizationId) // Organization OrgUid this.Fields.Add(new QField("IncOrg", LocRM.GetString("PrjOrganization"), "Name", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "cls_Organization", "OrgUid", "OrganizationId"))); this.Fields.Add(new QField("IncOrgUid", "IncOrgUid", "OrganizationId", DbType.Guid, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "cls_Organization", "OrgUid", "OrganizationId"))); this.Dictionary.Add(new QDictionary(this.Fields["IncOrgUid"], this.Fields["IncOrg"], "SELECT DISTINCT OrganizationId as Id, Name as Value FROM cls_Organization ORDER BY Value")); // Contact ContactUid this.Fields.Add(new QField("IncContact", LocRM.GetString("PrjContact"), "FullName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "cls_Contact", "ContactUid", "ContactId"))); this.Fields.Add(new QField("IncContactUid", "IncContactUid", "ContactId", DbType.Guid, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "cls_Contact", "ContactUid", "ContactId"))); this.Dictionary.Add(new QDictionary(this.Fields["IncContactUid"], this.Fields["IncContact"], "SELECT DISTINCT ContactId as Id, FullName as Value FROM cls_Contact ORDER BY Value")); // Client this.Fields.Add(new QField("IncClient", LocRM.GetString("PrjClient"), "Name", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "IncidentClients", "IncidentId", "IncidentId"), new QFieldJoinRelation(this.OwnerTable, "Clients", "ClientId", "ClientId") })); this.Fields.Add(new QField("IncClientUid", "ClientId", "ClientId", DbType.Guid, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "IncidentClients", "IncidentId", "IncidentId"), new QFieldJoinRelation(this.OwnerTable, "Clients", "ClientId", "ClientId") })); this.Dictionary.Add(new QDictionary(this.Fields["IncClientUid"], this.Fields["IncClient"], "SELECT DISTINCT ClientId as Id, Name as Value FROM Clients ORDER BY Value")); /////////////////////////// QMetaLoader.LoadMetaField(this, "IncidentsEx"); }
protected override void LoadScheme() { OwnerTable = "PROJECT_GROUPS"; this.Fields.Add(new QField("PortfolioId", LocRM.GetString("PortfolioId"), "ProjectGroupId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, true)); this.Fields.Add(new QField("PortfolioTitle", LocRM.GetString("PortfolioTitle"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); this.Fields.Add(new QField("PortfolioCreator", LocRM.GetString("PortfolioCreator"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Fields.Add(new QField("PortfolioCreatorId", LocRM.GetString("PortfolioCreatorId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["PortfolioCreatorId"], this.Fields["PortfolioCreator"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT CreatorId FROM PROJECT_GROUPS) ORDER BY [Value]")); /* this.Fields.Add(new QField("PortfolioLastEditor",LocRM.GetString("PortfolioLastEditor"), "{0}.FirstName + ' ' + {0}.LastName", DbType.String,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort, * new QFieldJoinRelation(this.OwnerTable,"USERS","LastEditorId","PrincipalId"))); * this.Fields.Add(new QField("PortfolioLastEditorId",LocRM.GetString("PortfolioLastEditorId"), "LastEditorId", DbType.Int32, QFieldUsingType.Abstract, * new QFieldJoinRelation(this.OwnerTable,"USERS","LastEditorId","PrincipalId"))); * this.Dictionary.Add(new QDictionary(this.Fields["PortfolioCreatorId"],this.Fields["PortfolioCreator"],"SELECT DISTINCT PrincipalId as Id, (FirstName +' ' + LastName) as Value FROM USERS WHERE PrincipalId IN (SELECT LastEditorId FROM PROJECT_GROUPS)")); */ this.Fields.Add(new QField("PortfolioCreationDate", LocRM.GetString("PortfolioCreationDate"), "CreationDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); // this.Fields.Add(new QField("PortfolioLastSavedDate",LocRM.GetString("PortfolioLastSavedDate"), "LastSavedDate",DbType.DateTime,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort)); // Projects this.Fields.Add(new QField("PortfolioProject", LocRM.GetString("PortfolioProject"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECT_GROUP", "ProjectGroupId", "ProjectGroupId"), new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId") } )); this.Fields.Add(new QField("PortfolioProjectId", LocRM.GetString("PortfolioProjectId"), "ProjectId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Sort, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECT_GROUP", "ProjectGroupId", "ProjectGroupId"), new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId") } )); this.Dictionary.Add(new QDictionary(this.Fields["PortfolioProjectId"], this.Fields["PortfolioProject"], "SELECT ProjectId as Id, Title as [Value] FROM PROJECTS ORDER BY [Value]")); // Project Status // this.Fields.Add(new QField("PortfolioProjectStatus",LocRM.GetString("PortfolioProjectStatus"),"StatusName",DbType.String ,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort, // new QFieldJoinRelation[]{ // new QFieldJoinRelation(this.OwnerTable,"PROJECT_GROUP","ProjectGroupId","ProjectGroupId"), // new QFieldJoinRelation(this.OwnerTable,"PROJECTS","ProjectId","ProjectId"), // new QFieldJoinRelation(this.OwnerTable,"PROJECT_STATUS_LANGUAGE","StatusId","StatusId","LanguageId") // } // )); // // this.Fields.Add(new QField("PortfolioProjectStatusId","PortfolioProjectStatusId","StatusId",DbType.Int32 ,QFieldUsingType.Abstract, // new QFieldJoinRelation[]{ // new QFieldJoinRelation(this.OwnerTable,"PROJECT_GROUP","ProjectGroupId","ProjectGroupId"), // new QFieldJoinRelation(this.OwnerTable,"PROJECTS","ProjectId","ProjectId"), // new QFieldJoinRelation(this.OwnerTable,"PROJECT_STATUS_LANGUAGE","StatusId","StatusId","LanguageId") // } // )); // this.Dictionary.Add(new QDictionary(this.Fields["PortfolioProjectStatusId"],this.Fields["PortfolioProjectStatus"],"SELECT StatusId as Id, StatusName as Value FROM PROJECT_STATUS_LANGUAGE WHERE LanguageId = {0}")); // Total Project Count this.Fields.Add(new QField("PortfolioTotalProjectCount", LocRM.GetString("PortfolioTotalProjectCount"), "(SELECT COUNT(*) FROM PROJECT_GROUP WHERE ProjectGroupId = {0}.ProjectGroupId)", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); // Active Project Count this.Fields.Add(new QField("PortfolioActiveProjectCount", LocRM.GetString("PortfolioActiveProjectCount"), "(SELECT COUNT(*) FROM PROJECT_GROUP PG INNER JOIN PROJECTS P ON P.ProjectId = PG.ProjectId INNER JOIN PROJECT_STATUS PS ON P.StatusId = PS.StatusId WHERE PS.IsActive = 1 AND ProjectGroupId = {0}.ProjectGroupId)", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); // PortfolioFinanceTarget this.Fields.Add(new QField("PortfolioFinanceTarget", LocRM.GetString("PortfolioFinanceTarget"), "(SELECT SUM(A.TCur+A.TSub) FROM PROJECT_GROUP PG INNER JOIN ACCOUNTS A ON A.ProjectId = PG.ProjectId WHERE A.OutlineLevel = 1 AND ProjectGroupId = {0}.ProjectGroupId)", DbType.Decimal, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); // PortfolioFinanceEstimate this.Fields.Add(new QField("PortfolioFinanceEstimate", LocRM.GetString("PortfolioFinanceEstimate"), "(SELECT SUM(A.ECur+A.ESub) FROM PROJECT_GROUP PG INNER JOIN ACCOUNTS A ON A.ProjectId = PG.ProjectId WHERE A.OutlineLevel = 1 AND ProjectGroupId = {0}.ProjectGroupId)", DbType.Decimal, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); // PortfolioFinanceActual this.Fields.Add(new QField("PortfolioFinanceActual", LocRM.GetString("PortfolioFinanceActual"), "(SELECT SUM(A.ACur+A.ASub) FROM PROJECT_GROUP PG INNER JOIN ACCOUNTS A ON A.ProjectId = PG.ProjectId WHERE A.OutlineLevel = 1 AND ProjectGroupId = {0}.ProjectGroupId)", DbType.Decimal, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); //Meta Fields QMetaLoader.LoadMetaField(this, "PortfolioEx"); }
protected override void LoadScheme() { OwnerTable = "TASKS"; this.Fields.Add(new QField("ToDoId", LocRM.GetString("ToDoId"), "TaskId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, true)); this.Fields.Add(new QField("ToDoTitle", LocRM.GetString("ToDoTitle"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // this.Fields.Add(new QField("ToDoResources","Resources","{0}.FirstName + ' ' + {0}.LastName",DbType.String,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter, // new QFieldJoinRelation[] // { // new QFieldJoinRelation(this.OwnerTable,"TASK_RESOURCES","TaskId","TaskId"), // new QFieldJoinRelation("TASK_RESOURCES","USERS","PrincipalId","PrincipalId") // })); this.Fields.Add(new QField("ToDoResources", LocRM.GetString("ToDoResources"), "CASE {0}.IsGroup " + "WHEN 0 THEN (SELECT UR.LastName + ' ' + UR.FirstName FROM USERS UR WHERE UR.PrincipalId = {0}.PrincipalId) " + "WHEN 1 THEN (SELECT GR.GroupName FROM GROUPS GR WHERE GR.PrincipalId = {0}.PrincipalId) END", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "TASK_RESOURCES", "TaskId", "TaskId"), new QFieldJoinRelation("TASK_RESOURCES", "PRINCIPALS", "PrincipalId", "PrincipalId") })); this.Fields.Add(new QField("ToDoResourcesId", "ToDoResourcesId", "PrincipalId", DbType.String, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "TASK_RESOURCES", "TaskId", "TaskId"), new QFieldJoinRelation("TASK_RESOURCES", "PRINCIPALS", "PrincipalId", "PrincipalId") })); this.Dictionary.Add(new QDictionary(this.Fields["ToDoResourcesId"], this.Fields["ToDoResources"], "SELECT DISTINCT TR.PrincipalId as Id, (CASE P.IsGroup WHEN 0 THEN (SELECT UR.LastName + ' ' + UR.FirstName FROM USERS UR WHERE UR.PrincipalId = TR.PrincipalId) WHEN 1 THEN (SELECT GR.GroupName FROM GROUPS GR WHERE GR.PrincipalId = TR.PrincipalId) END) as Value FROM TASK_RESOURCES TR INNER JOIN PRINCIPALS P ON P.PrincipalId = TR.PrincipalId")); this.Fields.Add(new QField("ToDoCreator", LocRM.GetString("ToDoCreator"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Fields.Add(new QField("ToDoCreatorId", LocRM.GetString("ToDoCreatorId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["ToDoCreatorId"], this.Fields["ToDoCreator"], "SELECT DISTINCT PrincipalId as Id, (LastName +' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT CreatorId FROM TASKS) ORDER BY [Value]")); this.Fields.Add(new QField("ToDoManager", LocRM.GetString("ToDoManager"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"), new QFieldJoinRelation("PROJECTS", "USERS", "ManagerId", "PrincipalId") })); this.Fields.Add(new QField("ToDoManagerId", LocRM.GetString("ToDoManagerId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"), new QFieldJoinRelation("PROJECTS", "USERS", "ManagerId", "PrincipalId") })); this.Dictionary.Add(new QDictionary(this.Fields["ToDoManagerId"], this.Fields["ToDoManager"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT ManagerId FROM PROJECTS) ORDER BY [Value]")); this.Fields.Add(new QField("ToDoProject", LocRM.GetString("ToDoProject"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"))); this.Fields.Add(new QField("ToDoProjectId", LocRM.GetString("ToDoProjectId"), "ProjectId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"))); this.Dictionary.Add(new QDictionary(this.Fields["ToDoProjectId"], this.Fields["ToDoProject"], "SELECT ProjectId as Id, Title as [Value] FROM PROJECTS ORDER BY [Value]")); // 2009-09-30 this.Fields.Add(new QField("PrjCode", LocRM.GetString("PrjCode"), "ProjectCode", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"))); this.Fields.Add(new QField("ToDoCreationDate", LocRM.GetString("ToDoCreationDate"), "CreationDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("ToDoStartDate", LocRM.GetString("ToDoStartDate"), "StartDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("ToDoDueDate", LocRM.GetString("ToDoDueDate"), "FinishDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("ToDoActualStartDate", LocRM.GetString("ToDoActualStartDate"), "ActualStartDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("ToDoActualDueDate", LocRM.GetString("ToDoActualDueDate"), "ActualFinishDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("ToDoOverallStatus", LocRM.GetString("ToDoOverallStatus"), "PercentCompleted", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("ToDoStatus", LocRM.GetString("ToDoStatus"), "(CASE {0}.IsCompleted " + "WHEN 1 THEN ( (SELECT ReasonName FROM COMPLETION_REASON_LANGUAGE AS CR WHERE CR.ReasonId = {0}.ReasonId AND LanguageId = {1})) " + "WHEN 0 THEN (CASE WHEN {0}.FinishDate < GetUtcDate() THEN N'" + LocRM.GetString("ToDoStatus_Overdue") + "' " + "WHEN {0}.StartDate > GetUtcDate() THEN N'" + LocRM.GetString("ToDoStatus_Upcoming") + "' ELSE N'" + LocRM.GetString("ToDoStatus_Active") + "' END ) " + "END)", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("ToDoStatusId", "ToDoStatusId", "(CASE {0}.IsCompleted " + "WHEN 1 THEN ( (SELECT CR.ReasonId FROM COMPLETION_REASON_LANGUAGE AS CR WHERE CR.ReasonId = {0}.ReasonId AND LanguageId = {1})) " + "WHEN 0 THEN (CASE WHEN {0}.FinishDate < GetUtcDate() THEN -100 " + "WHEN {0}.StartDate > GetUtcDate() THEN -101 ELSE -102 END ) " + "END)", DbType.Int32, QFieldUsingType.Abstract)); this.Dictionary.Add(new QDictionary(this.Fields["ToDoStatusId"], this.Fields["ToDoStatus"], " SELECT ReasonId As Id, ReasonName As Value FROM COMPLETION_REASON_LANGUAGE WHERE LanguageId = {0}" + " UNION " + " SELECT -100 As Id, N'" + LocRM.GetString("ToDoStatus_Overdue") + "' As Value" + " UNION " + " SELECT -101 As Id, N'" + LocRM.GetString("ToDoStatus_Upcoming") + "' As Value" + " UNION " + " SELECT -102 As Id, N'" + LocRM.GetString("ToDoStatus_Active") + "' As Value" )); //this.Fields.Add(new QField("ToDoType",LocRM.GetString("ToDoType"),"Task")); this.Fields.Add(new QField("ToDoGeneralCategories", LocRM.GetString("ToDoGeneralCategories"), "CategoryName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "OBJECT_CATEGORY", "TaskId", "ObjectId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Task).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("OBJECT_CATEGORY", "CATEGORIES", "CategoryId", "CategoryId") })); this.Fields.Add(new QField("ToDoGeneralCategoriesId", "ToDoGeneralCategoriesId", "CategoryId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "OBJECT_CATEGORY", "TaskId", "ObjectId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Task).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("OBJECT_CATEGORY", "CATEGORIES", "CategoryId", "CategoryId") })); this.Dictionary.Add(new QDictionary(this.Fields["ToDoGeneralCategoriesId"], this.Fields["ToDoGeneralCategories"], "SELECT CategoryId As Id, CategoryName As Value FROM CATEGORIES")); // New Addod 2006-10-18 this.Fields.Add(new QField("ToDoDuration", LocRM.GetString("ToDoDuration"), "Duration", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); //this.Fields.Add(new QField("ToDoTaskTime",LocRM.GetString("ToDoTaskTime"),"TaskTime", DbType.Int32,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort,false)); // 2007-10-17 New Field is assigned with TimeTracking this.Fields.Add(new QField("ToDoTaskTime", LocRM.GetString("TaskTime"), "TaskTime", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("ToDoTotalMinutes", LocRM.GetString("TotalMinutes"), "TotalMinutes", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("ToDoTotalApproved", LocRM.GetString("TotalApproved"), "TotalApproved", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // 2008-02-27 Priority this.Fields.Add(new QField("ToDoPriority", LocRM.GetString("ToDoPriority"), "PriorityName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PRIORITY_LANGUAGE", "PriorityId", "PriorityId", "LanguageId"))); this.Fields.Add(new QField("ToDoPriorityId", LocRM.GetString("ToDoPriority"), "PriorityId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "PRIORITY_LANGUAGE", "PriorityId", "PriorityId", "LanguageId"))); this.Dictionary.Add(new QDictionary(this.Fields["ToDoPriorityId"], this.Fields["ToDoPriority"], "SELECT PriorityId as Id, PriorityName as Value FROM PRIORITY_LANGUAGE WHERE LanguageId = {0}")); // 2008-06-01 Outline Number + Outline Level this.Fields.Add(new QField("TaskOutlineNumber", LocRM.GetString("TaskOutlineNumber"), "OutlineNumber", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("TaskOutlineLevel", LocRM.GetString("TaskOutlineLevel"), "OutlineLevel", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // 2008-07-28 Add Resources With Percen this.Fields.Add(new QField("ToDoResourcesWithPercent", LocRM.GetString("ToDoResourcesWithPercent"), "(CASE (SELECT PP.IsGroup FROM PRINCIPALS PP WHERE PP.PrincipalId = {0}.PrincipalId) " + "WHEN 0 THEN (SELECT UR.LastName + ' ' + UR.FirstName FROM USERS UR WHERE UR.PrincipalId = {0}.PrincipalId) " + "WHEN 1 THEN (SELECT GR.GroupName FROM GROUPS GR WHERE GR.PrincipalId = {0}.PrincipalId) END) + " + "+ (CASE AL1.CompletionTypeId WHEN 1 THEN (N' (' + CAST({0}.PercentCompleted AS NVARCHAR(20)) + N'%)') WHEN 2 THEN N'' END)", DbType.String, QFieldUsingType.Field, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "TASK_RESOURCES", "TaskId", "TaskId") //, //new QFieldJoinRelation("TASK_RESOURCES","PRINCIPALS","PrincipalId","PrincipalId") })); // 2008-09-03 TaskNum this.Fields.Add(new QField("TaskNum", LocRM.GetString("TaskNum"), "TaskNum", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // 2009-01-20 OZ New Columns Type (Common, Milestone, Summary) this.Fields.Add(new QField("TaskType", LocRM.GetString("TaskType"), "(CASE ({0}.IsMileStone + 2*{0}.IsSummary) " + "WHEN 1 THEN N'" + LocRM.GetString("TaskTypeMilestone") + "' " + "WHEN 2 THEN N'" + LocRM.GetString("TaskTypeSummary") + "' " + "ELSE N'" + LocRM.GetString("TaskTypeCommon") + "' END)", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("TaskTypeId", "TaskTypeId", "({0}.IsMileStone + 2*{0}.IsSummary)", DbType.Int32, QFieldUsingType.Abstract, false)); this.Dictionary.Add(new QDictionary(this.Fields["TaskTypeId"], this.Fields["TaskType"], "SELECT 0 As Id, N'" + LocRM.GetString("TaskTypeCommon") + "' As Value UNION SELECT 1 As Id, N'" + LocRM.GetString("TaskTypeMilestone") + "' As Value UNION SELECT 2 As Id, N'" + LocRM.GetString("TaskTypeSummary") + "' As Value")); /* this.Fields.Add(new QField("ToDoResourcesWithPercentId", "ToDoResourcesWithPercentId", * "PrincipalId", * DbType.String, QFieldUsingType.Abstract, * new QFieldJoinRelation[] * { * new QFieldJoinRelation(this.OwnerTable,"TASK_RESOURCES","TaskId","TaskId"), * new QFieldJoinRelation("TASK_RESOURCES","PRINCIPALS","PrincipalId","PrincipalId") * })); * * this.Dictionary.Add(new QDictionary(this.Fields["ToDoResourcesWithPercentId"], this.Fields["ToDoResourcesWithPercent"], "SELECT DISTINCT TR.PrincipalId as Id, (CASE P.IsGroup WHEN 0 THEN (SELECT UR.FirstName + ' ' + UR.LastName FROM USERS UR WHERE UR.PrincipalId = TR.PrincipalId) WHEN 1 THEN (SELECT GR.GroupName FROM GROUPS GR WHERE GR.PrincipalId = TR.PrincipalId) END) as Value FROM TASK_RESOURCES TR INNER JOIN PRINCIPALS P ON P.PrincipalId = TR.PrincipalId")); */ // 2006-12-13 OZ: Client // this.Fields.Add(new QField("ToDoClient",LocRM.GetString("PrjClient"), // "(ISNULL((SELECT O.OrgName FROM Organizations O WHERE O.OrgId = {0}.OrgId),(SELECT V.FullName FROM VCard V WHERE V.VCardId = {0}.VCardId)))", // DbType.String, // QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort)); // // this.Fields.Add(new QField("ToDoClientId","ToDoClientId", // "(ISNULL({0}.OrgId,-{0}.VCardId))", // DbType.Int32, // QFieldUsingType.Abstract)); // // this.Dictionary.Add(new QDictionary(this.Fields["ToDoClientId"],this.Fields["ToDoClient"], // "SELECT (O.OrgId) AS Id , O.OrgName as Value FROM Organizations O UNION SELECT (-V.VCardId) AS Id , V.FullName as Value FROM VCard V")); // 2010-06-10 this.Fields.Add(new QField("ToDoDescription", LocRM.GetString("ToDoDescription"), "Description", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter, false)); QMetaLoader.LoadMetaField(this, "TaskEx"); }
protected override void LoadScheme() { OwnerTable = "EVENTS"; //id this.Fields.Add(new QField("EventId", LocRM.GetString("EventId"), "EventId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, true)); // Title 1 this.Fields.Add(new QField("EventTitle", LocRM.GetString("EventTitle"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // Participants 0..N // this.Fields.Add(new QField("EventParticipants","Participants","{0}.FirstName + ' ' + {0}.LastName",DbType.String,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter, // new QFieldJoinRelation[] // { // new QFieldJoinRelation(this.OwnerTable,"EVENT_RESOURCES","EventId","EventId"), // new QFieldJoinRelation("EVENT_RESOURCES","USERS","PrincipalId","PrincipalId") // })); this.Fields.Add(new QField("EventParticipants", LocRM.GetString("EventParticipants"), "CASE {0}.IsGroup " + "WHEN 0 THEN (SELECT UR.LastName + ' ' + UR.FirstName FROM USERS UR WHERE UR.PrincipalId = {0}.PrincipalId) " + "WHEN 1 THEN (SELECT GR.GroupName FROM GROUPS GR WHERE GR.PrincipalId = {0}.PrincipalId) END", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "EVENT_RESOURCES", "EventId", "EventId"), new QFieldJoinRelation("EVENT_RESOURCES", "PRINCIPALS", "PrincipalId", "PrincipalId") })); this.Fields.Add(new QField("EventParticipantsId", "EventParticipantsId", "PrincipalId", DbType.String, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "EVENT_RESOURCES", "EventId", "EventId"), new QFieldJoinRelation("EVENT_RESOURCES", "PRINCIPALS", "PrincipalId", "PrincipalId") })); this.Dictionary.Add(new QDictionary(this.Fields["EventParticipantsId"], this.Fields["EventParticipants"], "SELECT DISTINCT TR.PrincipalId as Id, (CASE P.IsGroup WHEN 0 THEN (SELECT UR.LastName + ' ' + UR.FirstName FROM USERS UR WHERE UR.PrincipalId = TR.PrincipalId) WHEN 1 THEN (SELECT GR.GroupName FROM GROUPS GR WHERE GR.PrincipalId = TR.PrincipalId) END) as Value FROM EVENT_RESOURCES TR INNER JOIN PRINCIPALS P ON P.PrincipalId = TR.PrincipalId")); // Organizer 1 this.Fields.Add(new QField("EventOrganizer", LocRM.GetString("EventOrganizer"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "ManagerId", "PrincipalId"))); this.Fields.Add(new QField("EventOrganizerId", LocRM.GetString("EventOrganizerId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "ManagerId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["EventOrganizerId"], this.Fields["EventOrganizer"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT ManagerId FROM EVENTS) ORDER BY [Value]")); // Creator 1 this.Fields.Add(new QField("EventCreator", LocRM.GetString("EventCreator"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Fields.Add(new QField("EventCreatorId", LocRM.GetString("EventCreatorId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["EventCreatorId"], this.Fields["EventCreator"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT CreatorId FROM EVENTS) ORDER BY [Value]")); // Type (event, meeting, appointment) this.Fields.Add(new QField("EventType", LocRM.GetString("EventType"), "TypeName", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "EVENT_TYPE_LANGUAGE", "TypeId", "TypeId", "LanguageId"))); this.Fields.Add(new QField("EventTypeId", "EventTypeId", "TypeId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "EVENT_TYPE_LANGUAGE", "TypeId", "TypeId", "LanguageId"))); this.Dictionary.Add(new QDictionary(this.Fields["EventTypeId"], this.Fields["EventType"], "SELECT TypeId as Id, TypeName as Value FROM EVENT_TYPE_LANGUAGE WHERE LanguageId = {0}")); // Project 0..1 if (Mediachase.Ibn.License.ProjectManagement) { this.Fields.Add(new QField("EventProject", LocRM.GetString("EventProject"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"))); this.Fields.Add(new QField("EventProjectId", LocRM.GetString("EventProjectId"), "ProjectId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"))); this.Dictionary.Add(new QDictionary(this.Fields["EventProjectId"], this.Fields["EventProject"], "SELECT ProjectId as Id, Title as [Value] FROM PROJECTS ORDER BY [Value]")); // 2009-09-30 this.Fields.Add(new QField("PrjCode", LocRM.GetString("PrjCode"), "ProjectCode", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECTS", "ProjectId", "ProjectId"))); } // Start date this.Fields.Add(new QField("EventStartDate", LocRM.GetString("EventStartDate"), "StartDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // Finish date this.Fields.Add(new QField("EventFinishDate", LocRM.GetString("EventFinishDate"), "FinishDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // General category 0..n this.Fields.Add(new QField("EventGeneralCategories", LocRM.GetString("EventGeneralCategories"), "CategoryName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "OBJECT_CATEGORY", "EventId", "ObjectId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.CalendarEntry).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("OBJECT_CATEGORY", "CATEGORIES", "CategoryId", "CategoryId") })); this.Fields.Add(new QField("EventGeneralCategoriesId", "EventGeneralCategoriesId", "CategoryId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "OBJECT_CATEGORY", "EventId", "ObjectId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.CalendarEntry).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("OBJECT_CATEGORY", "CATEGORIES", "CategoryId", "CategoryId") })); this.Dictionary.Add(new QDictionary(this.Fields["EventGeneralCategoriesId"], this.Fields["EventGeneralCategories"], "SELECT CategoryId As Id, CategoryName As Value FROM CATEGORIES")); // 2006-12-13 OZ: Client //this.Fields.Add(new QField("EventClient",LocRM.GetString("PrjClient"), // "(ISNULL((SELECT O.OrgName FROM Organizations O WHERE O.OrgId = {0}.OrgId),(SELECT V.FullName FROM VCard V WHERE V.VCardId = {0}.VCardId)))", // DbType.String, // QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort)); //this.Fields.Add(new QField("EventClientId","EventClientId", // "(ISNULL({0}.OrgId,-{0}.VCardId))", // DbType.Int32, // QFieldUsingType.Abstract)); //this.Dictionary.Add(new QDictionary(this.Fields["EventClientId"],this.Fields["EventClient"], // "SELECT (O.OrgId) AS Id , O.OrgName as Value FROM Organizations O UNION SELECT (-V.VCardId) AS Id , V.FullName as Value FROM VCard V")); // 2007-10-17 New Field is assigned with TimeTracking this.Fields.Add(new QField("EventTaskTime", LocRM.GetString("TaskTime"), "TaskTime", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("EventTotalMinutes", LocRM.GetString("TotalMinutes"), "TotalMinutes", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("EventTotalApproved", LocRM.GetString("TotalApproved"), "TotalApproved", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); /////////////////////////// // 2008-10-20 New Clients // Organization OrgUid this.Fields.Add(new QField("EventOrg", LocRM.GetString("PrjOrganization"), "Name", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "cls_Organization", "OrgUid", "OrganizationId"))); this.Fields.Add(new QField("EventOrgUid", "EventOrgUid", "OrganizationId", DbType.Guid, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "cls_Organization", "OrgUid", "OrganizationId"))); this.Dictionary.Add(new QDictionary(this.Fields["EventOrgUid"], this.Fields["EventOrg"], "SELECT DISTINCT OrganizationId as Id, Name as Value FROM cls_Organization ORDER BY Value")); // Contact ContactUid this.Fields.Add(new QField("EventContact", LocRM.GetString("PrjContact"), "FullName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "cls_Contact", "ContactUid", "ContactId"))); this.Fields.Add(new QField("EventContactUid", "EventContactUid", "ContactId", DbType.Guid, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "cls_Contact", "ContactUid", "ContactId"))); this.Dictionary.Add(new QDictionary(this.Fields["EventContactUid"], this.Fields["EventContact"], "SELECT DISTINCT ContactId as Id, FullName as Value FROM cls_Contact ORDER BY Value")); // Client this.Fields.Add(new QField("EventClient", LocRM.GetString("PrjClient"), "Name", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "EventClients", "EventId", "EventId"), new QFieldJoinRelation(this.OwnerTable, "Clients", "ClientId", "ClientId") })); this.Fields.Add(new QField("EventClientUid", "ClientId", "ClientId", DbType.Guid, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "EventClients", "EventId", "EventId"), new QFieldJoinRelation(this.OwnerTable, "Clients", "ClientId", "ClientId") })); this.Dictionary.Add(new QDictionary(this.Fields["EventClientUid"], this.Fields["EventClient"], "SELECT DISTINCT ClientId as Id, Name as Value FROM Clients ORDER BY Value")); /////////////////////////// // 2009-04-28 New CreationDate Field is assigned with TimeTracking this.Fields.Add(new QField("EventCreationDate", LocRM.GetString("ToDoCreationDate"), "CreationDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); // 2009-06-22 New Field Location this.Fields.Add(new QField("EventLocation", LocRM.GetString("EventLocation"), "Location", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("EventDescription", LocRM.GetString("EventDescription"), "Description", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); QMetaLoader.LoadMetaField(this, "EventsEx"); }
protected override void LoadScheme() { OwnerTable = "USERS"; //key this.Fields.Add(new QField("UserId", LocRM.GetString("UserId"), "PrincipalId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, true)); //First Name this.Fields.Add(new QField("UserFirstName", LocRM.GetString("UserFirstName"), "FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); //Last Name this.Fields.Add(new QField("UserLastName", LocRM.GetString("UserLastName"), "LastName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); //E-Mail this.Fields.Add(new QField("UserEmail", LocRM.GetString("UserEmail"), "Email", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); //Phone this.Fields.Add(new QField("UserPhone", LocRM.GetString("UserPhone"), "Phone", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USER_DETAILS", "PrincipalId", "UserId"), true)); //Fax this.Fields.Add(new QField("UserFax", LocRM.GetString("UserFax"), "Fax", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USER_DETAILS", "PrincipalId", "UserId"), true)); //Mobile this.Fields.Add(new QField("UserMobile", LocRM.GetString("UserMobile"), "Mobile", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USER_DETAILS", "PrincipalId", "UserId"), true)); //Job Title this.Fields.Add(new QField("UserJobTitle", LocRM.GetString("UserJobTitle"), "Position", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USER_DETAILS", "PrincipalId", "UserId"), true)); //Department this.Fields.Add(new QField("UserDepartment", LocRM.GetString("UserDepartment"), "Department", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USER_DETAILS", "PrincipalId", "UserId"), true)); //Location this.Fields.Add(new QField("UserLocation", LocRM.GetString("UserLocation"), "Location", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USER_DETAILS", "PrincipalId", "UserId"), true)); //UserActivity this.Fields.Add(new QField("UserActivity", LocRM.GetString("UserActivity"), "(CASE {0}.Activity WHEN 1 THEN N'" + LocRM.GetString("UserActivityInactive") + "' WHEN 2 THEN N'" + LocRM.GetString("UserActivityPending") + "' WHEN 3 THEN N'" + LocRM.GetString("UserActivityActive") + "' END)", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); this.Fields.Add(new QField("UserActivityId", "UserActivityId", "Activity", DbType.Int32, QFieldUsingType.Abstract)); this.Dictionary.Add(new QDictionary(this.Fields["UserActivityId"], this.Fields["UserActivity"], "SELECT 1 As Id, N'" + LocRM.GetString("UserActivityInactive") + "' As Value" + " UNION " + "SELECT 2 As Id, N'" + LocRM.GetString("UserActivityPending") + "' As Value" + " UNION " + "SELECT 3 As Id, N'" + LocRM.GetString("UserActivityActive") + "' As Value" )); //All secure groups this.Fields.Add(new QField("UserSecureGroups", LocRM.GetString("UserSecureGroups"), "GroupName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "dbo.[USER_GROUP]", "PrincipalId", "UserId"), new QFieldJoinRelation("dbo.[USER_GROUP]", "dbo.[GROUPS]", "GroupId", "PrincipalId") })); this.Fields.Add(new QField("UserSecureGroupsId", "UserSecureGroupsId", "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "dbo.[USER_GROUP]", "PrincipalId", "UserId"), new QFieldJoinRelation("dbo.[USER_GROUP]", "dbo.[GROUPS]", "GroupId", "PrincipalId") })); this.Dictionary.Add(new QDictionary(this.Fields["UserSecureGroupsId"], this.Fields["UserSecureGroups"], "SELECT PrincipalId As Id, GroupName As Value FROM GROUPS")); //Only custom Groups this.Fields.Add(new QField("UserCustomGroups", LocRM.GetString("UserCustomGroups"), "GroupName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "USER_GROUP", "PrincipalId", "UserId"), new QFieldJoinRelation("USER_GROUP", "GROUPS", "GroupId", "PrincipalId", new SimpleFilterCondition(new QField("PrincipalId"), "9", SimpleFilterType.Great)) })); this.Fields.Add(new QField("UserCustomGroupsId", "UserCustomGroupsId", "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "USER_GROUP", "PrincipalId", "UserId"), new QFieldJoinRelation("USER_GROUP", "GROUPS", "GroupId", "PrincipalId", new SimpleFilterCondition(new QField("PrincipalId"), "9", SimpleFilterType.Great)) })); this.Dictionary.Add(new QDictionary(this.Fields["UserCustomGroupsId"], this.Fields["UserCustomGroups"], "SELECT PrincipalId As Id, GroupName As Value FROM GROUPS WHERE PrincipalId > 9")); //Only Roles this.Fields.Add(new QField("UserRoles", LocRM.GetString("UserRoles"), "GroupName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "[USER_GROUP]", "PrincipalId", "UserId"), new QFieldJoinRelation("[USER_GROUP]", "[GROUPS]", "GroupId", "PrincipalId", new SimpleFilterCondition(new QField("PrincipalId"), "((SELECT PrincipalID FROM GROUPS WHERE PrincipalId < 8 AND PrincipalId != 1 AND PrincipalId != 6))", SimpleFilterType.In)) })); this.Fields.Add(new QField("UserRolesId", "UserRolesId", "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "[USER_GROUP]", "PrincipalId", "UserId"), new QFieldJoinRelation("[USER_GROUP]", "[GROUPS]", "GroupId", "PrincipalId", new SimpleFilterCondition(new QField("PrincipalId"), "((SELECT PrincipalID FROM GROUPS WHERE PrincipalId < 8 AND PrincipalId != 1 AND PrincipalId != 6))", SimpleFilterType.In)) })); this.Dictionary.Add(new QDictionary(this.Fields["UserRolesId"], this.Fields["UserRoles"], "SELECT PrincipalId As Id, GroupName As Value FROM GROUPS WHERE PrincipalId < 8 AND PrincipalId != 1 AND PrincipalId != 6")); QMetaLoader.LoadMetaField(this, "UsersEx"); //Im Groups }
protected void LoadScheme2() { OwnerTable = "PROJECTS"; this.Fields.Add(new QField("PrjId", LocRM.GetString("PrjId"), "ProjectId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, true)); this.Fields.Add(new QField("PrjTitle", LocRM.GetString("PrjTitle"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("PrjCreator", LocRM.GetString("PrjCreator"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Fields.Add(new QField("PrjCreatorId", LocRM.GetString("PrjCreatorId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["PrjCreatorId"], this.Fields["PrjCreator"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT CreatorId FROM PROJECTS) ORDER BY [Value]")); this.Fields.Add(new QField("PrjManager", LocRM.GetString("PrjManager"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "ManagerId", "PrincipalId"))); this.Fields.Add(new QField("PrjManagerId", LocRM.GetString("PrjManagerId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "ManagerId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["PrjManagerId"], this.Fields["PrjManager"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT ManagerId FROM PROJECTS) ORDER BY [Value]")); this.Fields.Add(new QField("PrjExecutiveManager", LocRM.GetString("PrjExecutiveManager"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "ExecutiveManagerId", "PrincipalId"))); this.Fields.Add(new QField("PrjExecutiveManagerId", LocRM.GetString("PrjExecutiveManagerId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "ManagerId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["PrjExecutiveManagerId"], this.Fields["PrjExecutiveManager"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT ExecutiveManagerId FROM PROJECTS) ORDER BY [Value]")); this.Fields.Add(new QField("PrjCreationDate", LocRM.GetString("PrjCreationDate"), "CreationDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("PrjStartDate", LocRM.GetString("PrjStartDate"), "StartDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("PrjFinishDate", LocRM.GetString("PrjFinishDate"), "FinishDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("PrjTargetFinishDate", LocRM.GetString("PrjTargetFinishDate"), "TargetFinishDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("PrjActualFinishDate", LocRM.GetString("PrjActualFinishDate"), "ActualFinishDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("PrjStatus", LocRM.GetString("PrjStatus"), "StatusName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECT_STATUS_LANGUAGE", "StatusId", "StatusId", "LanguageId"))); this.Fields.Add(new QField("PrjStatusId", "PrjStatusId", "StatusId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "PROJECT_STATUS_LANGUAGE", "StatusId", "StatusId", "LanguageId"))); this.Dictionary.Add(new QDictionary(this.Fields["PrjStatusId"], this.Fields["PrjStatus"], "SELECT StatusId as Id, StatusName as Value FROM PROJECT_STATUS_LANGUAGE WHERE LanguageId = {0}")); // this.Fields.Add(new QField("PrjClient",LocRM.GetString("PrjClient"),"ClientName", DbType.String,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter|QFieldUsingType.Sort, // new QFieldJoinRelation(this.OwnerTable,"CLIENTS","ClientId","ClientId"))); // // this.Fields.Add(new QField("PrjClientId","PrjClientId","ClientId", DbType.Int32,QFieldUsingType.Abstract, // new QFieldJoinRelation(this.OwnerTable,"CLIENTS","ClientId","ClientId"))); // // this.Dictionary.Add(new QDictionary(this.Fields["PrjClientId"],this.Fields["PrjClient"],"SELECT ClientId as Id, ClientName as Value FROM CLIENTS")); this.Fields.Add(new QField("PrjGeneralCategories", LocRM.GetString("PrjGeneralCategories"), "CategoryName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "OBJECT_CATEGORY", "ProjectId", "ObjectId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Project).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("OBJECT_CATEGORY", "CATEGORIES", "CategoryId", "CategoryId") })); this.Fields.Add(new QField("PrjGeneralCategoriesId", "PrjGeneralCategoriesId", "CategoryId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "OBJECT_CATEGORY", "ProjectId", "ObjectId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Project).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("OBJECT_CATEGORY", "CATEGORIES", "CategoryId", "CategoryId") })); this.Dictionary.Add(new QDictionary(this.Fields["PrjGeneralCategoriesId"], this.Fields["PrjGeneralCategories"], "SELECT CategoryId As Id, CategoryName As Value FROM CATEGORIES")); this.Fields.Add(new QField("PrjProjectCategories", LocRM.GetString("PrjProjectCategories"), "CategoryName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECT_CATEGORY", "ProjectId", "ProjectId"), new QFieldJoinRelation("PROJECT_CATEGORY", "PROJECT_CATEGORIES", "CategoryId", "CategoryId") })); this.Fields.Add(new QField("PrjProjectCategoriesId", "PrjProjectCategoriesId", "CategoryId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECT_CATEGORY", "ProjectId", "ProjectId"), new QFieldJoinRelation("PROJECT_CATEGORY", "PROJECT_CATEGORIES", "CategoryId", "CategoryId") })); this.Dictionary.Add(new QDictionary(this.Fields["PrjProjectCategoriesId"], this.Fields["PrjProjectCategories"], "SELECT CategoryId As Id, CategoryName As Value FROM PROJECT_CATEGORIES")); this.Fields.Add(new QField("PrjResources", LocRM.GetString("PrjResources"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECT_MEMBERS", "ProjectId", "ProjectId"), new QFieldJoinRelation("PROJECT_MEMBERS", "USERS", "PrincipalId", "PrincipalId") })); this.Fields.Add(new QField("PrjResourcesId", "PrjResourcesId", "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECT_MEMBERS", "ProjectId", "ProjectId"), new QFieldJoinRelation("PROJECT_MEMBERS", "USERS", "PrincipalId", "PrincipalId") })); this.Dictionary.Add(new QDictionary(this.Fields["PrjResourcesId"], this.Fields["PrjResources"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT PrincipalId FROM PROJECT_MEMBERS) ORDER BY [Value]")); // this.Fields.Add(new QField("PrjTargetBudget",LocRM.GetString("PrjTargetBudget"), "TargetBudget",DbType.Decimal,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter, false)); // this.Fields.Add(new QField("PrjEstimatedBudget",LocRM.GetString("PrjEstimatedBudget"), "EstimatedBudget",DbType.Decimal,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter, false)); // this.Fields.Add(new QField("PrjActualBudget",LocRM.GetString("PrjActualBudget"), "ActualBudget",DbType.Decimal,QFieldUsingType.Field|QFieldUsingType.Grouping|QFieldUsingType.Filter, false)); // Project Types [12/17/2004] this.Fields.Add(new QField("PrjType", LocRM.GetString("PrjType"), "FormName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "OBJECT_FORMS", "FormId", "FormId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Project).ToString(), SimpleFilterType.Equal)))); this.Fields.Add(new QField("PrjTypeId", "PrjTypeId", "FormId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "OBJECT_FORMS", "FormId", "FormId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Project).ToString(), SimpleFilterType.Equal)))); this.Dictionary.Add(new QDictionary(this.Fields["PrjTypeId"], this.Fields["PrjType"], "SELECT FormId as Id, FormName as Value FROM OBJECT_FORMS WHERE ObjectTypeId = 3")); // PercentCompleted [1/13/2005] this.Fields.Add(new QField("PrjPercentCompleted", LocRM.GetString("PrjPercentCompleted"), "PercentCompleted", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); // PriorityId [1/13/2005] this.Fields.Add(new QField("PrjPriority", LocRM.GetString("PrjPriority"), "PriorityName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PRIORITY_LANGUAGE", "PriorityId", "PriorityId", "LanguageId"))); this.Fields.Add(new QField("PrjPriorityId", "PrjPriorityId", "PriorityId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "PRIORITY_LANGUAGE", "PriorityId", "PriorityId", "LanguageId"))); this.Dictionary.Add(new QDictionary(this.Fields["PrjPriorityId"], this.Fields["PrjPriority"], "SELECT PriorityId as Id, PriorityName as Value FROM PRIORITY_LANGUAGE WHERE LanguageId = {0}")); // Phase [3/35/2005] this.Fields.Add(new QField("PrjPhase", LocRM.GetString("PrjPhase"), "PhaseName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "PROJECT_PHASES", "PhaseId", "PhaseId"))); this.Fields.Add(new QField("PrjPhaseId", "PrjPhaseId", "PhaseId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "PROJECT_PHASES", "PhaseId", "PhaseId"))); this.Dictionary.Add(new QDictionary(this.Fields["PrjPhaseId"], this.Fields["PrjPhase"], "SELECT PhaseId as Id, PhaseName as Value FROM PROJECT_PHASES")); // Portfolio [3/35/2005] this.Fields.Add(new QField("PrjPortfolio", LocRM.GetString("PrjPortfolio"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECT_GROUP", "ProjectId", "ProjectId"), new QFieldJoinRelation("PROJECT_GROUP", "PROJECT_GROUPS", "ProjectGroupId", "ProjectGroupId") })); this.Fields.Add(new QField("PrjPortfolioId", "PrjPortfolioId", "ProjectGroupId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "PROJECT_GROUP", "ProjectId", "ProjectId"), new QFieldJoinRelation("PROJECT_GROUP", "PROJECT_GROUPS", "ProjectGroupId", "ProjectGroupId") })); this.Dictionary.Add(new QDictionary(this.Fields["PrjPortfolioId"], this.Fields["PrjPortfolio"], "SELECT ProjectGroupId as Id, Title as Value FROM PROJECT_GROUPS")); // OZ: 2006-12-12 New Finance by business Score (Current plan) foreach (BusinessScore bs in BusinessScore.List()) { this.Fields.Add(new QField(string.Format("PrjBs{0}", bs.Key), string.Format("{0} ({1})", bs.Name, LocRM.GetString("PrjCurrentPlan")), "(SELECT CAST(SUM(Value) AS MONEY) FROM BusinessScoreData WHERE [Index] = 0 AND BusinessScoreId = " + bs.BusinessScoreId + " AND ProjectId = {0}.ProjectId)", DbType.Decimal, QFieldUsingType.Field | /*QFieldUsingType.Grouping|*/ QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "BusinessScoreData", "ProjectId", "ProjectId"))); } // TODO: OZ: 2007-07-12 New Finance by business Score (Fact) //foreach (BusinessScore bs in BusinessScore.List()) //{ // this.Fields.Add(new QField(string.Format("PrjBs{0}", bs.Key), // string.Format("{0} ({1})", bs.Name, LocRM.GetString("PrjFact")), // "(SELECT SUM(Value) FROM BusinessScoreData WHERE [Index] = 0 AND BusinessScoreId = " + bs.BusinessScoreId + " AND ProjectId = {0}.ProjectId)", // DbType.Decimal, // QFieldUsingType.Field |/*QFieldUsingType.Grouping|*/QFieldUsingType.Filter | QFieldUsingType.Sort, // new QFieldJoinRelation(this.OwnerTable, "BusinessScoreData", "ProjectId", "ProjectId"))); //} // 2006-12-13 OZ: Client //this.Fields.Add(new QField("PrjClient", LocRM.GetString("PrjClient"), // "(ISNULL((SELECT O.OrgName FROM Organizations O WHERE O.OrgId = {0}.OrgId),(SELECT V.FullName FROM VCard V WHERE V.VCardId = {0}.VCardId)))", // DbType.String, // QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort)); //this.Fields.Add(new QField("PrjClientId", "PrjClientId", // "(ISNULL({0}.OrgId,-{0}.VCardId))", // DbType.Int32, // QFieldUsingType.Abstract)); //this.Dictionary.Add(new QDictionary(this.Fields["PrjClientId"], this.Fields["PrjClient"], // "SELECT (O.OrgId) AS Id , O.OrgName as Value FROM Organizations O UNION SELECT (-V.VCardId) AS Id , V.FullName as Value FROM VCard V")); // 2007-10-17 New Field is assigned with TimeTracking this.Fields.Add(new QField("PrjTaskTime", LocRM.GetString("TaskTime"), "TaskTime", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("PrjTotalMinutes", LocRM.GetString("TotalMinutes"), "TotalMinutes", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); this.Fields.Add(new QField("PrjTotalApproved", LocRM.GetString("TotalApproved"), "TotalApproved", DbType.Time, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); /////////////////////////// // 2008-10-20 New Clients // Organization OrgUid this.Fields.Add(new QField("PrjOrg", LocRM.GetString("PrjOrganization"), "Name", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "cls_Organization", "OrgUid", "OrganizationId"))); this.Fields.Add(new QField("PrjOrgUid", "PrjOrgUid", "OrganizationId", DbType.Guid, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "cls_Organization", "OrgUid", "OrganizationId"))); this.Dictionary.Add(new QDictionary(this.Fields["PrjOrgUid"], this.Fields["PrjOrg"], "SELECT DISTINCT OrganizationId as Id, Name as Value FROM cls_Organization ORDER BY Value")); // Contact ContactUid this.Fields.Add(new QField("PrjContact", LocRM.GetString("PrjContact"), "FullName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "cls_Contact", "ContactUid", "ContactId"))); this.Fields.Add(new QField("PrjContactUid", "PrjContactUid", "ContactId", DbType.Guid, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "cls_Contact", "ContactUid", "ContactId"))); this.Dictionary.Add(new QDictionary(this.Fields["PrjContactUid"], this.Fields["PrjContact"], "SELECT DISTINCT ContactId as Id, FullName as Value FROM cls_Contact ORDER BY Value")); // Client this.Fields.Add(new QField("PrjClient", LocRM.GetString("PrjClient"), "Name", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "ProjectClients", "ProjectId", "ProjectId"), new QFieldJoinRelation(this.OwnerTable, "Clients", "ClientId", "ClientId") })); this.Fields.Add(new QField("PrjClientUid", "ClientId", "ClientId", DbType.Guid, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "ProjectClients", "ProjectId", "ProjectId"), new QFieldJoinRelation(this.OwnerTable, "Clients", "ClientId", "ClientId") })); this.Dictionary.Add(new QDictionary(this.Fields["PrjClientUid"], this.Fields["PrjClient"], "SELECT DISTINCT ClientId as Id, Name as Value FROM Clients ORDER BY Value")); /////////////////////////// // 2008-11-12 this.Fields.Add(new QField("PrjGoals", LocRM.GetString("PrjGoals"), "Goals", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter, false)); this.Fields.Add(new QField("PrjScope", LocRM.GetString("PrjScope"), "Scope", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter, false)); this.Fields.Add(new QField("PrjDeliverables", LocRM.GetString("PrjDeliverables"), "Deliverables", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter, false)); this.Fields.Add(new QField("PrjDescription", LocRM.GetString("PrjDescription"), "Description", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter, false)); // 2009-08-24 this.Fields.Add(new QField("PrjCode", LocRM.GetString("PrjCode"), "ProjectCode", DbType.String, QFieldUsingType.Field | QFieldUsingType.Filter | QFieldUsingType.Sort, false)); //Description // Meta Fields MetaClass mcProjects = MetaClass.Load("Projects"); if (mcProjects.ChildClasses.Count > 0) { QMetaLoader.LoadMetaField(this, mcProjects.ChildClasses[_ExtensionsId]); for (int ExIndex = 0; ExIndex < mcProjects.ChildClasses.Count; ExIndex++) { if (ExIndex != _ExtensionsId && mcProjects.ChildClasses[ExIndex].UserMetaFields.Count > 0) { QMetaLoader.LoadMetaField(this, mcProjects.ChildClasses[ExIndex]); } } if (_ExtensionsId == 0) { for (int ExIndex = 1; ExIndex < mcProjects.ChildClasses.Count; ExIndex++) { if (mcProjects.ChildClasses[ExIndex].UserMetaFields.Count > 0) { QProject newExtension = new QProject(ExIndex); this._Extensions.Add(newExtension); } } } } }
protected override void LoadScheme() { OwnerTable = "ASSETS"; //key this.Fields.Add(new QField("AssetId", LocRM.GetString("AssetId"), "AssetId", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, true)); //VersionId 1 this.Fields.Add(new QField("AssetVersionId", LocRM.GetString("AssetVersionId"), "VersionId", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"))); //CreatedDate 1 this.Fields.Add(new QField("AssetCreationDate", LocRM.GetString("AssetCreationDate"), "CreatedDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, true)); //TODO:ModifiedDate 1 this.Fields.Add(new QField("AssetModifiedDate", LocRM.GetString("AssetModifiedDate"), "CreatedDate", DbType.DateTime, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"))); //CreatorId 1 this.Fields.Add(new QField("AssetCreator", LocRM.GetString("AssetCreator"), "{0}.LastName + ' ' + {0}.FirstName", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Fields.Add(new QField("AssetCreatorId", LocRM.GetString("AssetCreatorId"), "PrincipalId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "USERS", "CreatorId", "PrincipalId"))); this.Dictionary.Add(new QDictionary(this.Fields["AssetCreatorId"], this.Fields["AssetCreator"], "SELECT DISTINCT PrincipalId as Id, (LastName + ' ' + FirstName) as Value FROM USERS WHERE PrincipalId IN (SELECT CreatorId FROM ASSETS) ORDER BY [Value]")); //Title 1 this.Fields.Add(new QField("AssetTitle", LocRM.GetString("AssetTitle"), "Title", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter, new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"))); //Type this.Fields.Add(new QField("AssetType", LocRM.GetString("AssetType"), "(CASE {0}.IsInternal WHEN 1 THEN N'" + LocRM.GetString("AssetType_File") + "' WHEN 0 THEN N'" + LocRM.GetString("AssetType_Link") + "' END)", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"), true)); this.Fields.Add(new QField("AssetTypeId", "AssetTypeId", "(CASE {0}.IsInternal WHEN 1 THEN N'" + LocRM.GetString("AssetType_File") + "' WHEN 0 THEN N'" + LocRM.GetString("AssetType_Link") + "' END)", DbType.String, QFieldUsingType.Abstract, new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"), true)); this.Dictionary.Add(new QDictionary(this.Fields["AssetTypeId"], this.Fields["AssetType"], "SELECT N'" + LocRM.GetString("AssetType_File") + "' As Id, N'" + LocRM.GetString("AssetType_File") + "' As Value" + " UNION " + "SELECT N'" + LocRM.GetString("AssetType_Link") + "' As Id, N'" + LocRM.GetString("AssetType_Link") + "' As Value" )); //FileName this.Fields.Add(new QField("AssetFileName", LocRM.GetString("AssetFileName"), "(CASE {0}.IsInternal WHEN 1 THEN {0}.FileName WHEN 0 THEN NULL END)", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"))); //Size this.Fields.Add(new QField("AssetSize", LocRM.GetString("AssetSize"), "(CASE {0}.IsInternal WHEN 1 THEN {0}.[Size] WHEN 0 THEN NULL END)", DbType.Int32, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"))); //URL this.Fields.Add(new QField("AssetURL", LocRM.GetString("AssetURL"), "(CASE {0}.IsInternal WHEN 1 THEN NULL WHEN 0 THEN {0}.URL END)", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"))); //ParentFolder this.Fields.Add(new QField("AssetParentFolder", LocRM.GetString("AssetParentFolder"), "Name", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "ASSET_CONTAINERS", "AssetId", "AssetId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Folder).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("ASSET_CONTAINERS", "FOLDERS", "ObjectId", "FolderId") })); //ParentFolderId this.Fields.Add(new QField("AssetParentFolderId", LocRM.GetString("AssetParentFolderId"), "FolderId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "ASSET_CONTAINERS", "AssetId", "AssetId", new SimpleFilterCondition(new QField("ObjectTypeId"), ((int)ObjectTypes.Folder).ToString(), SimpleFilterType.Equal)), new QFieldJoinRelation("ASSET_CONTAINERS", "FOLDERS", "ObjectId", "FolderId") })); this.Dictionary.Add(new QDictionary(this.Fields["AssetParentFolderId"], this.Fields["AssetParentFolder"], "SELECT FolderId as Id, Name as Value FROM FOLDERS")); //ContentType this.Fields.Add(new QField("AssetContentType", LocRM.GetString("AssetContentType"), "ContentTypeString", DbType.String, QFieldUsingType.Field | QFieldUsingType.Grouping | QFieldUsingType.Filter | QFieldUsingType.Sort, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"), new QFieldJoinRelation("ASSET_VERSIONS", "CONTENT_TYPES", "ContentTypeId", "ContentTypeId") })); this.Fields.Add(new QField("AssetContentTypeId", "AssetContentTypeId", "ContentTypeId", DbType.Int32, QFieldUsingType.Abstract, new QFieldJoinRelation[] { new QFieldJoinRelation(this.OwnerTable, "ASSET_VERSIONS", "AssetId", "AssetId"), new QFieldJoinRelation("ASSET_VERSIONS", "CONTENT_TYPES", "ContentTypeId", "ContentTypeId") })); this.Dictionary.Add(new QDictionary(this.Fields["AssetContentTypeId"], this.Fields["AssetContentType"], "SELECT ContentTypeId as Id, FriendlyName as Value FROM CONTENT_TYPES")); QMetaLoader.LoadMetaField(this, "AssetsEx"); }