MARIProject Database Structure

/*
 * MARIProject Version 4.9
 * ProjectManagement.SQLScript4.9.HotlineIssuesHeader.sql
 * Shows all Information to the issues with the Status, IssueType etc.
 */
 
SELECT 
	MARISupportIssue.IssueID,
	MARISupportIssue.BriefDescription AS ShortText,
	MARISupportIssue.RequestText AS LongText,
	MARISupportIssue.RequestDate AS IssueDate,
	MARISupportIssue.ChangeAtDate AS LastIssueChangeDate,
	MARISupportIssue.ContactPerson,
	CreatedBy = 
	CASE MARISupportIssue.OriginatorType
		WHEN 0 THEN ''
		WHEN 1 THEN MARISupportIssue.Originator
		WHEN 2 THEN (SELECT [Name] FROM MARIUser WHERE UserType = 2 AND UserCode = MARISupportIssue.Originator)
		WHEN 3 THEN (SELECT Matchcode FROM MARIEmployeeMaster WHERE EmployeeNumber = MARISupportIssue.Originator)
		WHEN 4 THEN (SELECT [Description] FROM MARISupportGroup WHERE GroupId = MARISupportIssue.Originator)
	END,
	Responsible = 
	CASE MARISupportIssue.EditorType
		WHEN 0 THEN ''
		WHEN 1 THEN MARISupportIssue.HandledBy
		WHEN 2 THEN (SELECT [Name] FROM MARIUser WHERE UserType = 2 AND UserCode = MARISupportIssue.HandledBy)
		WHEN 3 THEN (SELECT Matchcode FROM MARIEmployeeMaster WHERE EmployeeNumber = MARISupportIssue.HandledBy)
		WHEN 4 THEN (SELECT [Description] FROM MARISupportGroup WHERE GroupId = MARISupportIssue.HandledBy)
	END,
	MARISupportSettings.[Description] AS Status,
	MARISupportSettings.DescriptionID AS StatusID,
    MARISupportSettings_1.[Description] AS HotlineType,
	MARISupportSettings_1.DescriptionID AS HotlineTypID,
	MARISupportSettings_2.[Description] AS Priority,
	MARISupportSettings_2.DescriptionID AS ProirityID,
  	MARIOCRD.Matchcode AS CustomerMatchcode,
	MARIOCRD.CardCode,
	MARISupportProduct.BriefDescription AS ProductNameShortName,
	MARISupportProduct.ProductName,
   (SELECT Max(PV.ProductVersion) FROM MARISupportIssueVersions AS AV INNER JOIN MARISupportProductVersions AS PV ON AV.ProductVersionID=PV.ProductVersionID
    WHERE AV.IssueID=MARISupportIssue.ChangeUser ) AS MaxIssueVersion,
   (SELECT Max(PM.ProductModule) FROM MARISupportIssueModules AS AM INNER JOIN MARISupportProductModules AS PM ON PM.ProductModuleID=AM.ProductModuleID
    WHERE AM.IssueID=MARISupportIssue.ChangeUser) AS MaxIssueModule,

   (SELECT MAX(PV.ProductVersion) FROM MARISupportProductCustomer AS PK INNER JOIN MARISupportProductVersions AS PV ON PK.ProductVersionID=PV.ProductVersionID
    WHERE PK.ProductID=MARISupportIssue.ProductID AND PK.CardCode=MARISupportIssue.CardCode AND PK.Company=MARISupportIssue.Company) AS CustomerProductLinkVersion
	
FROM MARISupportIssue
INNER JOIN MARISupportSettings
	ON MARISupportIssue.Status = MARISupportSettings.[ID] AND MARISupportSettings.Setting = 1
INNER JOIN MARISupportSettings AS MARISupportSettings_1
	ON MARISupportIssue.IssueType = MARISupportSettings_1.[ID] AND MARISupportSettings_1.Setting = 2 
INNER JOIN MARISupportSettings AS MARISupportSettings_2
	ON MARISupportIssue.Priority = MARISupportSettings_2.[ID] AND MARISupportSettings_2.Setting = 3
LEFT JOIN MARIOCRD -- AS KHKKontokorrent
	ON MARISupportIssue.Company = MARIOCRD.CompanyID
		AND MARISupportIssue.CardCode = MARIOCRD.CardCode
INNER JOIN MARISupportProduct
	ON MARISupportIssue.ProductID = MARISupportProduct.ProductID
ORDER BY MARISupportIssue.ChangeUser DESC

			

/*
 * ProjectManagement Version 4.9
 * ProjectManagement.SQLScript4.9.SupportIssuesLines.sql
 * Shows details to the support ticket issues
 */
 
SELECT
	IssueID,
	CreatedBy = 
	CASE MARISupportIssueLine.OriginatorType
		WHEN 0 THEN ''
		WHEN 1 THEN MARISupportIssueLine.Originator
		WHEN 2 THEN (SELECT [Name] FROM MARIUser WHERE UserType = 2 AND UserCode = MARISupportIssueLine.Originator)
		WHEN 3 THEN (SELECT Matchcode FROM MARIEmployeeMaster WHERE EmployeeNumber = MARISupportIssueLine.Originator)
		WHEN 4 THEN (SELECT Description FROM MARISupportGroup WHERE GroupId = MARISupportIssueLine.Originator)
	END,
	ChangedBy = 
	CASE MARISupportIssueLine.EditorType
		WHEN 0 THEN ''
		WHEN 1 THEN MARISupportIssueLine.HandledBy
		WHEN 2 THEN (SELECT [Name] FROM MARIUser WHERE UserType = 2 AND UserCode = MARISupportIssueLine.HandledBy)
		WHEN 3 THEN (SELECT Matchcode FROM MARIEmployeeMaster WHERE EmployeeNumber = MARISupportIssueLine.HandledBy)
		WHEN 4 THEN (SELECT Description FROM MARISupportGroup WHERE GroupId = MARISupportIssueLine.HandledBy)
	END,
	PosType = 
	case MARISupportIssueLine.RequestPosType
		WHEN 1 THEN 'Comment'
		WHEN 2 THEN 'Change Responsible'
		WHEN 3 THEN 'Picture'
		WHEN 4 THEN 'Document'
		WHEN 5 THEN 'eMail'
		WHEN 6 THEN 'Link'
		WHEN 7 THEN 'Auto eMail'
		WHEN 8 THEN 'Incomming eMail'
		WHEN 9 THEN 'Synchronisation Comment'
	END,
	VisibleInternOnly,
	RequestText,
	CreateDate
FROM MARISupportIssueLine