namespace FrameworkDAL.Query.ReportHeader
{
    public static class ReportHeaderQB
    {
        // Single header joined to its detail rows by client and id
        public const string GET_REPORTHEADER = @"
SELECT
    h.REPORTHEADERID   AS ReportHeaderId,
    h.REPORTHEADERCODE AS ReportHeaderCode,
    h.REPORTHEADERNAME AS ReportHeaderName,
    h.PARTICULARS      AS ReportHeaderParticulars,
    h.REMARKS          AS ReportHeaderRemarks,
    h.CLIENTID         AS ReportHeaderBaseClinetId,
    h.SORTORDER        AS ReportHeaderSortOrder,
    h.STATUS           AS ReportHeaderStatus,
    h.VERSION          AS ReportHeaderVersion,
    h.SOURCETYPE       AS ReportHeaderSourceType,
    h.CREATEDBYID      AS ReportHeaderCreatedById,
    h.CREATEDON        AS ReportHeaderCreatedOn,
    h.MODIFIEDBYID     AS ReportHeaderModifiedById,
    h.MODIFIEDON       AS ReportHeaderModifiedOn
FROM  MREPORTHEADER h
WHERE h.REPORTHEADERID = @ReportHeaderId
AND   h.CLIENTID       = @ClientId
AND   h.STATUS         <> 2";

        public const string GET_REPORTHEADER_DETAIL = @"
SELECT
    d.REPORTHEADERDETAILID    AS ReportHeaderDetailId,
    d.REPORTHEADERID          AS ReportHeaderId,
    d.SLNO                    AS ReportHeaderDetailSlNo,
    d.FROMROWNUMBER           AS ReportHeaderDetailFromRowNumber,
    d.TOROWNUMBER             AS ReportHeaderDetailToRowNumber,
    d.ROWHEIGHT               AS ReportHeaderDetailRowHeight,
    d.FROMCOLUMNNUMBER        AS ReportHeaderDetailFromColumnNumber,
    d.TOCOLUMNNUMBER          AS ReportHeaderDetailToColumnNumber,
    d.CELLWIDTH               AS ReportHeaderDetailCellWidth,
    d.EXPRESSION              AS ReportHeaderDetailExpression,
    d.ALIGNMENT               AS ReportHeaderDetailAlignment,
    d.FONT                    AS ReportHeaderDetailFont,
    d.FONTSIZE                AS ReportHeaderDetailFontSize,
    d.COLOR                   AS ReportHeaderDetailColor,
    d.STYLE                   AS ReportHeaderDetailStyle,
    d.ISEMPTYSUPPRESS         AS ReportHeaderDetailIsEmptySuppress,
    d.CREATEDBYID             AS ReportHeaderDetailCreatedById,
    d.CREATEDON               AS ReportHeaderDetailCreatedOn,
    d.MODIFIEDBYID            AS ReportHeaderDetailModifiedById,
    d.MODIFIEDON              AS ReportHeaderDetailModifiedOn
FROM  MREPORTHEADERDETAIL d
WHERE d.REPORTHEADERID = @ReportHeaderId
ORDER BY d.SLNO";

        // All non-deleted headers for a client, ordered by sort position
        public const string GET_REPORTHEADER_LIST = @"
SELECT
    h.REPORTHEADERID   AS ReportHeaderId,
    h.REPORTHEADERCODE AS ReportHeaderCode,
    h.REPORTHEADERNAME AS ReportHeaderName,
    h.PARTICULARS      AS ReportHeaderParticulars,
    h.REMARKS          AS ReportHeaderRemarks,
    h.CLIENTID         AS ReportHeaderBaseClinetId,
    h.SORTORDER        AS ReportHeaderSortOrder,
    h.STATUS           AS ReportHeaderStatus,
    h.VERSION          AS ReportHeaderVersion,
    h.SOURCETYPE       AS ReportHeaderSourceType,
    h.CREATEDBYID      AS ReportHeaderCreatedById,
    h.CREATEDON        AS ReportHeaderCreatedOn,
    h.MODIFIEDBYID     AS ReportHeaderModifiedById,
    h.MODIFIEDON       AS ReportHeaderModifiedOn
FROM  MREPORTHEADER h
WHERE h.CLIENTID = @ClientId
AND   h.STATUS   <> 2
ORDER BY h.SORTORDER, h.REPORTHEADERNAME";

        // Paged admin list for management screens
        public const string GET_REPORTHEADER_PAGED = @"
SELECT
    h.REPORTHEADERID   AS ReportHeaderId,
    h.REPORTHEADERCODE AS ReportHeaderCode,
    h.REPORTHEADERNAME AS ReportHeaderName,
    h.SORTORDER        AS ReportHeaderSortOrder,
    h.STATUS           AS ReportHeaderStatus,
    h.VERSION          AS ReportHeaderVersion,
    h.SOURCETYPE       AS ReportHeaderSourceType
FROM  MREPORTHEADER h
WHERE h.CLIENTID = @ClientId
AND   h.STATUS   <> 2
ORDER BY h.SORTORDER, h.REPORTHEADERNAME
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // Dropdown picklist: Id / Code / Name
        public const string GET_SELECTLIST = @"
SELECT
    h.REPORTHEADERID   AS Id,
    h.REPORTHEADERCODE AS Code,
    h.REPORTHEADERNAME AS Name
FROM  MREPORTHEADER h
WHERE h.STATUS   <> 2
ORDER BY h.SORTORDER, h.REPORTHEADERNAME";

        public const string INSERT_REPORTHEADER = @"
INSERT INTO MREPORTHEADER (
    REPORTHEADERID, REPORTHEADERCODE, REPORTHEADERNAME, PARTICULARS, REMARKS,
    CLIENTID, SORTORDER, STATUS, VERSION, SOURCETYPE,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES (
    @ReportHeaderId, @ReportHeaderCode, @ReportHeaderName, @ReportHeaderParticulars, @ReportHeaderRemarks,
    @ReportHeaderBaseClinetId, @ReportHeaderSortOrder, @ReportHeaderStatus, 0, @ReportHeaderSourceType,
    @ReportHeaderCreatedById, @ReportHeaderCreatedOn, @ReportHeaderModifiedById, @ReportHeaderModifiedOn)";

        public const string UPDATE_REPORTHEADER = @"
UPDATE MREPORTHEADER SET
    REPORTHEADERCODE = @ReportHeaderCode,
    REPORTHEADERNAME = @ReportHeaderName,
    PARTICULARS      = @ReportHeaderParticulars,
    REMARKS          = @ReportHeaderRemarks,
    SORTORDER        = @ReportHeaderSortOrder,
    STATUS           = @ReportHeaderStatus,
    VERSION          = VERSION + 1,
    SOURCETYPE       = @ReportHeaderSourceType,
    MODIFIEDBYID     = @ReportHeaderModifiedById,
    MODIFIEDON       = @ReportHeaderModifiedOn
WHERE REPORTHEADERID = @ReportHeaderId
AND   CLIENTID       = @ReportHeaderBaseClinetId";

        public const string SOFT_DELETE_REPORTHEADER = @"
UPDATE MREPORTHEADER SET
    STATUS       = 2,
    VERSION      = VERSION + 1,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE REPORTHEADERID = @ReportHeaderId
AND   CLIENTID       = @ClientId";

        // Wipe all detail rows before re-inserting
        public const string DELETE_DETAIL = @"
DELETE FROM MREPORTHEADERDETAIL WHERE REPORTHEADERID = @ReportHeaderId";

        public const string INSERT_DETAIL = @"
INSERT INTO MREPORTHEADERDETAIL (
    REPORTHEADERDETAILID, REPORTHEADERID, SLNO,
    FROMROWNUMBER, TOROWNUMBER, ROWHEIGHT,
    FROMCOLUMNNUMBER, TOCOLUMNNUMBER, CELLWIDTH,
    EXPRESSION, ALIGNMENT, FONT, FONTSIZE, COLOR, STYLE, ISEMPTYSUPPRESS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES (
    @ReportHeaderDetailId, @ReportHeaderId, @ReportHeaderDetailSlNo,
    @ReportHeaderDetailFromRowNumber, @ReportHeaderDetailToRowNumber, @ReportHeaderDetailRowHeight,
    @ReportHeaderDetailFromColumnNumber, @ReportHeaderDetailToColumnNumber, @ReportHeaderDetailCellWidth,
    @ReportHeaderDetailExpression, @ReportHeaderDetailAlignment, @ReportHeaderDetailFont,
    @ReportHeaderDetailFontSize, @ReportHeaderDetailColor, @ReportHeaderDetailStyle, @ReportHeaderDetailIsEmptySuppress,
    @ReportHeaderDetailCreatedById, @ReportHeaderDetailCreatedOn, @ReportHeaderDetailModifiedById, @ReportHeaderDetailModifiedOn)";
    }
}