
CREATE VIEW `TabsView`  AS  select `Tabs`.`TabID` AS `TabID`,`Tabs`.`TabOrder` AS `TabOrder`,`Tabs`.`ProjectID` AS `ProjectID`,`Tabs`.`TabName` AS `TabName`,`Tabs`.`AuthorizedRoles` AS `AuthorizedRoles`,`Tabs`.`IsVisible` AS `IsVisible`,`Tabs`.`ParentId` AS `ParentId`,`Tabs`.`ImageList` AS `ImageList`,`Tabs`.`Title` AS `Title`,`Tabs`.`Description` AS `Description`,`Tabs`.`IsDeleted` AS `IsDeleted`,`Tabs`.`TabTarget` AS `TabTarget`,`Tabs`.`TabType` AS `TabType`,`Tabs`.`TabOwner` AS `TabOwner`,`Tabs`.`LastUpdate` AS `LastUpdate`,`Tabs`.`DateCreated` AS `DateCreated`,`Tabs`.`Keywords` AS `Keywords`,`Projects`.`ProjectName` AS `ProjectName`,`Users`.`Username` AS `UserName` from ((`Tabs` join `Projects`) join `Users`) where `Projects`.`ProjectId` = `Tabs`.`ProjectID` and `Users`.`UserID` = `Tabs`.`TabOwner` ;
COMMIT;

CREATE VIEW `CalendarView`  AS  select distinct `Calendar`.`CalId` AS `CalId`,`Calendar`.`UserId` AS `UserId`,`Calendar`.`ProjectId` AS `ProjectId`,`Projects`.`ProjectName` AS `ProjectName`,`Calendar`.`DateCreated` AS `DateCreated`,`Calendar`.`CalType` AS `CalType`,`Calendar`.`Description` AS `Description`,`Calendar`.`Url` AS `Url`,`Calendar`.`StartDate` AS `StartDate`,`Calendar`.`EndDate` AS `EndDate`,`Calendar`.`Recurs` AS `Recurs`,`Calendar`.`RecursTill` AS `RecursTill`,`Calendar`.`IsDeleted` AS `IsDeleted`,`Users`.`FirstName` AS `FirstName`,`Users`.`LastName` AS `LastName`,`Users`.`Email` AS `Email`,`Users`.`Unit` AS `Unit`,`Users`.`Username` AS `UserName`,`Users`.`RoleId` AS `RoleId` from ((`Calendar` join `Users` on(`Calendar`.`UserId` = `Users`.`UserID`)) join `Projects` on(`Calendar`.`ProjectId` = `Projects`.`ProjectId`)) where `Calendar`.`IsDeleted` = 0 ;
COMMIT;

CREATE VIEW `IssuesView`  AS  select distinct `Issues`.`IssueId` AS `IssueId`,`Issues`.`ProjectId` AS `ProjectId`,`Issues`.`IssueTitle` AS `IssueTitle`,`Issues`.`IssueCategoryId` AS `IssueCategoryId`,`Issues`.`IssueMilestoneId` AS `IssueMilestoneId`,`Issues`.`IssuePriorityId` AS `IssuePriorityId`,`Issues`.`IssueCreatorId` AS `IssueCreatorId`,`Issues`.`IssueOwnerId` AS `IssueOwnerId`,`Issues`.`IssueAssignedId` AS `IssueAssignedId`,`Issues`.`DateCreated` AS `DateCreated`,`Issues`.`Disabled` AS `Disabled`,`Issues`.`DateNeeded` AS `DateNeeded`,`Issues`.`LevelOfEffort` AS `LevelOfEffort`,`Issues`.`StartDate` AS `StartDate`,`Issues`.`EndDate` AS `EndDate`,`Projects`.`ProjectName` AS `ProjectName`,`ProjectPriorities`.`PriorityName` AS `PriorityName`,`ProjectMilestones`.`MilestoneName` AS `MilestoneName`,concat(`AssignedUsers`.`FirstName`,_utf8' ',`AssignedUsers`.`LastName`) AS `AssignedDisplayName`,concat(`OwnerUsers`.`FirstName`,_utf8' ',`OwnerUsers`.`LastName`) AS `OwnerDisplayName`,concat(`CreatorUsers`.`FirstName`,_utf8' ',`CreatorUsers`.`LastName`) AS `CreatorDisplayName`,`CreatorUsers`.`Username` AS `CreatorUser`,`OwnerUsers`.`Username` AS `OwnerUser`,`AssignedUsers`.`Username` AS `AssignedUser`,`ProjectCategories`.`CategoryName` AS `CategoryName`,`CreatorUsers`.`Unit` AS `CreatorUnit` from (((((((`Issues` left join `Projects` on(`Issues`.`ProjectId` = `Projects`.`ProjectId`)) left join `ProjectPriorities` on(`Issues`.`IssuePriorityId` = `ProjectPriorities`.`PriorityId`)) left join `ProjectMilestones` on(`Issues`.`IssueMilestoneId` = `ProjectMilestones`.`MilestoneId`)) left join `Users` `AssignedUsers` on(`AssignedUsers`.`UserID` = `Issues`.`IssueAssignedId`)) left join `Users` `OwnerUsers` on(`OwnerUsers`.`UserID` = `Issues`.`IssueOwnerId`)) left join `Users` `CreatorUsers` on(`CreatorUsers`.`UserID` = `Issues`.`IssueCreatorId`)) left join `ProjectCategories` on(`ProjectCategories`.`CategoryId` = `Issues`.`IssueCategoryId`)) where `Issues`.`Disabled` <> 1 ;
COMMIT;

CREATE VIEW `IssuesView`  AS  select distinct `Issues`.`IssueId` AS `IssueId`,`Issues`.`ProjectId` AS `ProjectId`,`Issues`.`IssueTitle` AS `IssueTitle`,`Issues`.`IssueCategoryId` AS `IssueCategoryId`,`Issues`.`IssueMilestoneId` AS `IssueMilestoneId`,`Issues`.`IssuePriorityId` AS `IssuePriorityId`,`Issues`.`IssueCreatorId` AS `IssueCreatorId`,`Issues`.`IssueOwnerId` AS `IssueOwnerId`,`Issues`.`IssueAssignedId` AS `IssueAssignedId`,`Issues`.`DateCreated` AS `DateCreated`,`Issues`.`Disabled` AS `Disabled`,`Issues`.`DateNeeded` AS `DateNeeded`,`Issues`.`LevelOfEffort` AS `LevelOfEffort`,`Issues`.`StartDate` AS `StartDate`,`Issues`.`EndDate` AS `EndDate`,`Projects`.`ProjectName` AS `ProjectName`,`ProjectPriorities`.`PriorityName` AS `PriorityName`,`ProjectMilestones`.`MilestoneName` AS `MilestoneName`,concat(`AssignedUsers`.`FirstName`,_utf8' ',`AssignedUsers`.`LastName`) AS `AssignedDisplayName`,concat(`OwnerUsers`.`FirstName`,_utf8' ',`OwnerUsers`.`LastName`) AS `OwnerDisplayName`,concat(`CreatorUsers`.`FirstName`,_utf8' ',`CreatorUsers`.`LastName`) AS `CreatorDisplayName`,`CreatorUsers`.`Username` AS `CreatorUser`,`OwnerUsers`.`Username` AS `OwnerUser`,`AssignedUsers`.`Username` AS `AssignedUser`,`ProjectCategories`.`CategoryName` AS `CategoryName`,`CreatorUsers`.`Unit` AS `CreatorUnit` from (((((((`Issues` left join `Projects` on(`Issues`.`ProjectId` = `Projects`.`ProjectId`)) left join `ProjectPriorities` on(`Issues`.`IssuePriorityId` = `ProjectPriorities`.`PriorityId`)) left join `ProjectMilestones` on(`Issues`.`IssueMilestoneId` = `ProjectMilestones`.`MilestoneId`)) left join `Users` `AssignedUsers` on(`AssignedUsers`.`UserID` = `Issues`.`IssueAssignedId`)) left join `Users` `OwnerUsers` on(`OwnerUsers`.`UserID` = `Issues`.`IssueOwnerId`)) left join `Users` `CreatorUsers` on(`CreatorUsers`.`UserID` = `Issues`.`IssueCreatorId`)) left join `ProjectCategories` on(`ProjectCategories`.`CategoryId` = `Issues`.`IssueCategoryId`)) where `Issues`.`Disabled` <> 1 ;
COMMIT;

CREATE VIEW `ProjectMembersView`  AS  select `Users`.`UserID` AS `UserId`,concat(`Users`.`FirstName`,' ',`Users`.`LastName`) AS `FullName`,`Users`.`Username` AS `Username`,`Users`.`Email` AS `Email`,`Users`.`Telephone` AS `TelePhone`,`Users`.`Street` AS `Street`,`Users`.`City` AS `City`,`Users`.`Region` AS `Region`,`Users`.`PostalCode` AS `PostalCode`,`Users`.`Country` AS `Country`,`Roles`.`RoleID` AS `RoleId`,`Roles`.`RoleName` AS `RoleName`,`Projects`.`ProjectId` AS `ProjectId`,`Projects`.`ProjectName` AS `ProjectName` from (((`ProjectMembers` join `Users`) join `Roles`) join `Projects`) where `ProjectMembers`.`UserId` = `Users`.`UserID` and `Projects`.`ProjectId` = `ProjectMembers`.`ProjectId` and `Users`.`RoleId` = `Roles`.`RoleID` ;
COMMIT;

CREATE VIEW `ResponsesDecoded`  AS  select `Responses`.`rUnique` AS `rUnique`,`Responses`.`CaseID` AS `CaseID`,`Responses`.`qID` AS `qID`,`Responses`.`rValue` AS `rValue`,`Responses`.`uID` AS `uID`,`Responses`.`DateCreated` AS `DateCreated`,`Responses`.`rOK` AS `rOK`,`Responses`.`isDeleted` AS `isDeleted`,`Responses`.`rInstance` AS `rInstance`,`Responses`.`ProjectId` AS `ProjectId`,`Responses`.`rValue` AS `tText` from `Responses` where `Responses`.`rValue` = '' and `Responses`.`isDeleted` = 0 union select `Responses`.`rUnique` AS `rUnique`,`Responses`.`CaseID` AS `CaseID`,`Responses`.`qID` AS `qID`,`Responses`.`rValue` AS `rValue`,`Responses`.`uID` AS `uID`,`Responses`.`DateCreated` AS `DateCreated`,`Responses`.`rOK` AS `rOK`,`Responses`.`isDeleted` AS `isDeleted`,`Responses`.`rInstance` AS `rInstance`,`Responses`.`ProjectId` AS `ProjectId`,`Responses`.`rValue` AS `tText` from (`Responses` join `Questions`) where `Responses`.`rValue` <> '' and `Questions`.`qID` = `Responses`.`qID` and `Questions`.`qFormat` not in ('RADIO','CHECKBOX','DROPDOWN','SIGNATURE') and `Responses`.`isDeleted` = 0 union select `Responses`.`rUnique` AS `rUnique`,`Responses`.`CaseID` AS `CaseID`,`Responses`.`qID` AS `qID`,`Responses`.`rValue` AS `rValue`,`Responses`.`uID` AS `uID`,`Responses`.`DateCreated` AS `DateCreated`,`Responses`.`rOK` AS `rOK`,`Responses`.`isDeleted` AS `isDeleted`,`Responses`.`rInstance` AS `rInstance`,`Responses`.`ProjectId` AS `ProjectId`,`ResponseType`.`tText` AS `tText` from ((`Responses` join `Questions`) join `ResponseType`) where `Responses`.`rValue` <> '' and `Questions`.`qID` = `Responses`.`qID` and `Questions`.`qFormat` in ('RADIO','CHECKBOX','DROPDOWN') and `ResponseType`.`tID` = `Questions`.`qType` and `ResponseType`.`tValue` = `Responses`.`rValue` and `Responses`.`isDeleted` = 0 union select `Responses`.`rUnique` AS `rUnique`,`Responses`.`CaseID` AS `CaseID`,`Responses`.`qID` AS `qID`,`Responses`.`rValue` AS `rValue`,`Responses`.`uID` AS `uID`,`Responses`.`DateCreated` AS `DateCreated`,`Responses`.`rOK` AS `rOK`,`Responses`.`isDeleted` AS `isDeleted`,`Responses`.`rInstance` AS `rInstance`,`Responses`.`ProjectId` AS `ProjectId`,concat('<img src=',`Responses`.`rValue`,'>') AS `tText` from ((`Responses` join `Questions`) join `ResponseType`) where `Responses`.`rValue` <> '' and `Questions`.`qID` = `Responses`.`qID` and `Questions`.`qFormat` = 'SIGNATURE' and `ResponseType`.`tID` = `Questions`.`qType` and `ResponseType`.`tValue` = `Responses`.`rValue` and `Responses`.`isDeleted` = 0 ;
COMMIT;

