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");
}
