namespace MMDAL.Query.NDTCertificate
{
    public class NDTCertificateQB
    {
        public const string GET_NDTCERTIFICATE = @"
            SELECT
                NC.NDTCERTIFICATEID             AS NDTCertificateId,
                NC.NDTREPORTID                  AS NDTCertificateNdtReportId,
                NC.NDTAGENCYID                  AS NDTCertificateNdtAgencyId,
                AG.PARTYNAME                    AS NDTCertificateAgencyName,
                NC.CERTIFICATENO                AS NDTCertificateCertificateNo,
                NC.CERTIFICATEDATE              AS NDTCertificateCertificateDate,
                NC.INSPECTORNAME                AS NDTCertificateInspectorName,
                NC.INSPECTORLEVEL               AS NDTCertificateInspectorLevel,
                NC.AGENCYCERTIFICATENO          AS NDTCertificateAgencyCertificateNo,
                NC.VALIDUPTO                    AS NDTCertificateValidUpto,
                NC.ATTACHMENTID                 AS NDTCertificateAttachmentId,
                NC.VERIFIEDBYID                 AS NDTCertificateVerifiedById,
                VB.USERNAME                     AS NDTCertificateVerifiedByName,
                NC.VERIFIEDON                   AS NDTCertificateVerifiedOn,
                NC.REMARKS                      AS NDTCertificateRemarks,
                NC.SORTORDER                    AS NDTCertificateSortOrder,
                NC.STATUS                       AS NDTCertificateStatus,
                NC.VERSION                      AS NDTCertificateVersion,
                NC.SOURCETYPE                   AS NDTCertificateSourceType,
                NC.CREATEDBYID                  AS NDTCertificateCreatedById,
                NC.CREATEDON                    AS NDTCertificateCreatedOn,
                CU.USERNAME                     AS NDTCertificateCreatedByName,
                NC.MODIFIEDBYID                 AS NDTCertificateModifiedById,
                NC.MODIFIEDON                   AS NDTCertificateModifiedOn,
                MU.USERNAME                     AS NDTCertificateModifiedByName
            FROM TNDTCERTIFICATE NC
            LEFT JOIN MPARTY AG ON AG.PARTYID = NC.NDTAGENCYID
            LEFT JOIN MUSER VB ON VB.USERID   = NC.VERIFIEDBYID
            LEFT JOIN MUSER CU ON CU.USERID   = NC.CREATEDBYID
            LEFT JOIN MUSER MU ON MU.USERID   = NC.MODIFIEDBYID
            WHERE NC.NDTCERTIFICATEID = @ndtcertificateid
              AND NC.TENANTID         = @tenantid;";

        public const string SAVE_NDTCERTIFICATE = @"
            INSERT INTO TNDTCERTIFICATE
            (
                NDTCERTIFICATEID, NDTREPORTID, NDTAGENCYID, CERTIFICATENO, CERTIFICATEDATE,
                INSPECTORNAME, INSPECTORLEVEL, AGENCYCERTIFICATENO, VALIDUPTO, ATTACHMENTID,
                VERIFIEDBYID, VERIFIEDON, REMARKS,
                SORTORDER, STATUS, VERSION, SOURCETYPE,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
            )
            VALUES
            (
                @NDTCertificateId, @NDTCertificateNdtReportId, @NDTCertificateNdtAgencyId,
                @NDTCertificateCertificateNo, @NDTCertificateCertificateDate,
                @NDTCertificateInspectorName, @NDTCertificateInspectorLevel,
                @NDTCertificateAgencyCertificateNo, @NDTCertificateValidUpto, @NDTCertificateAttachmentId,
                @NDTCertificateVerifiedById, @NDTCertificateVerifiedOn, @NDTCertificateRemarks,
                @NDTCertificateSortOrder, @NDTCertificateStatus, @NDTCertificateVersion, @NDTCertificateSourceType,
                @NDTCertificateCreatedById, @NDTCertificateCreatedOn,
                @NDTCertificateModifiedById, @NDTCertificateModifiedOn, @tenantid
            );";

        public const string UPDATE_NDTCERTIFICATE = @"
            UPDATE TNDTCERTIFICATE
            SET
                NDTREPORTID             = @NDTCertificateNdtReportId,
                NDTAGENCYID             = @NDTCertificateNdtAgencyId,
                CERTIFICATENO           = @NDTCertificateCertificateNo,
                CERTIFICATEDATE         = @NDTCertificateCertificateDate,
                INSPECTORNAME           = @NDTCertificateInspectorName,
                INSPECTORLEVEL          = @NDTCertificateInspectorLevel,
                AGENCYCERTIFICATENO     = @NDTCertificateAgencyCertificateNo,
                VALIDUPTO               = @NDTCertificateValidUpto,
                ATTACHMENTID            = @NDTCertificateAttachmentId,
                VERIFIEDBYID            = @NDTCertificateVerifiedById,
                VERIFIEDON              = @NDTCertificateVerifiedOn,
                REMARKS                 = @NDTCertificateRemarks,
                SORTORDER               = @NDTCertificateSortOrder,
                STATUS                  = @NDTCertificateStatus,
                VERSION                 = @NDTCertificateVersion,
                SOURCETYPE              = @NDTCertificateSourceType,
                MODIFIEDBYID            = @NDTCertificateModifiedById,
                MODIFIEDON              = @NDTCertificateModifiedOn
            WHERE NDTCERTIFICATEID = @NDTCertificateId
              AND TENANTID         = @tenantid;";

        public const string DELETE_NDTCERTIFICATE = @"
            DELETE FROM TNDTCERTIFICATE
            WHERE NDTCERTIFICATEID = @ndtcertificateid
              AND TENANTID         = @tenantid;";

        public const string GET_SELECTLIST_NDTCERTIFICATE = @"
            WITH PagedNDTCertificate AS
            (
                SELECT
                    NC.NDTCERTIFICATEID     AS Id,
                    NC.CERTIFICATENO        AS Code,
                    NC.INSPECTORNAME        AS Name,
                    ROW_NUMBER() OVER (ORDER BY NC.CERTIFICATEDATE DESC, NC.CERTIFICATENO) AS RowNum
                FROM TNDTCERTIFICATE NC
                WHERE NC.TENANTID = @tenantid
            )
            SELECT Id, Code, Name
            FROM   PagedNDTCertificate
            WHERE (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}