using System;

namespace AdminDAL.Query.CanonicalModel
{
    public static class CanonicalModelQB
    {
        public const string GET_CANONICALMODEL = @"
                    SELECT
                        C.CANONICALMODELID AS CanonicalModelId,
                        C.NAME AS CanonicalModelName,

                        C.MODULEID AS CanonicalModelModuleId,

                        C.SECTIONID AS CanonicalModelSectionId,

                        C.DESCRIPTION AS CanonicalModelDescription,
                        C.FIELDDATA AS CanonicalModelFieldData,
                        C.SAMPLEDATA AS CanonicalModelSampleData,

                        C.DISPLAYTEMPLATEID AS CanonicalModelDisplayTemplateId,

                        C.SORTORDER AS CanonicalModelSortOrder,
                        C.STATUS AS CanonicalModelStatus,
                        C.VERSION AS CanonicalModelVersion,

                        C.TENANTID AS CanonicalModelTenantId,

                        C.CREATEDBYID AS CanonicalModelCreatedById,
                        CU.USERNAME AS CanonicalModelCreatedByName,
                        C.CREATEDON AS CanonicalModelCreatedOn,

                        C.MODIFIEDBYID AS CanonicalModelModifiedById,
                        MU.USERNAME AS CanonicalModelModifiedByName,
                        C.MODIFIEDON AS CanonicalModelModifiedOn

                    FROM MCANONICALMODEL C
                    LEFT JOIN MCLIENT T  ON C.TENANTID = T.CLIENTID
                    LEFT JOIN MUSER CU   ON C.CREATEDBYID = CU.USERID
                    LEFT JOIN MUSER MU  ON C.MODIFIEDBYID = MU.USERID
                    WHERE C.CANONICALMODELID = @canonicalmodelid; ";

        public const string GET_SELECTLIST_CANONICALMODEL = @"
                            WITH PagedCanonicalModel AS (
                                SELECT
                                    C.CANONICALMODELID AS CanonicalModelId,
                                    C.NAME AS CanonicalModelName,
                                    C.MODULEID AS CanonicalModelModuleId,
                                    C.SECTIONID AS CanonicalModelSectionId,
                                    C.DESCRIPTION AS CanonicalModelDescription,
                                    C.SORTORDER AS CanonicalModelSortOrder,
                                    C.STATUS AS CanonicalModelStatus,
                                    C.VERSION AS CanonicalModelVersion,
                                    C.CREATEDBYID AS CanonicalModelCreatedById,
                                    C.CREATEDON AS CanonicalModelCreatedOn,
                                    C.MODIFIEDBYID AS CanonicalModelModifiedById,
                                    C.MODIFIEDON AS CanonicalModelModifiedOn,
                                    ROW_NUMBER() OVER (ORDER BY C.CANONICALMODELID) AS RowNum
                                FROM MCANONICALMODEL C
                            )
                            SELECT
                                CanonicalModelId,
                                CanonicalModelName,
                                CanonicalModelModuleId,
                                CanonicalModelSectionId,
                                CanonicalModelDescription,
                                CanonicalModelSortOrder,
                                CanonicalModelStatus,
                                CanonicalModelVersion,
                                CanonicalModelCreatedById,
                                CanonicalModelCreatedOn,
                                CanonicalModelModifiedById,
                                CanonicalModelModifiedOn
                            FROM PagedCanonicalModel
                            WHERE  
                                (@firstnumber = -1 AND @maxresult = -1)
                                OR
                                (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";

        public const string SAVE_CANONICALMODEL = @"
                            INSERT INTO MCANONICALMODEL (
                                NAME,
                                MODULEID,
                                SECTIONID,
                                DESCRIPTION,
                                FIELDDATA,
                                SAMPLEDATA,
                                DISPLAYTEMPLATEID,
                                SORTORDER,
                                STATUS,
                                VERSION,
                                TENANTID,
                                CREATEDBYID,
                                CREATEDON,
                                MODIFIEDBYID,
                                MODIFIEDON
                            ) VALUES (
                                @CanonicalModelName,
                                @CanonicalModelModuleId,
                                @CanonicalModelSectionId,
                                @CanonicalModelDescription,
                                @CanonicalModelFieldData,
                                @CanonicalModelSampleData,
                                @CanonicalModelDisplayTemplateId,
                                @CanonicalModelSortOrder,
                                @CanonicalModelStatus,
                                @CanonicalModelVersion,
                                @CanonicalModelTenantId,
                                @CanonicalModelCreatedById,
                                @CanonicalModelCreatedOn,
                                @CanonicalModelModifiedById,
                                @CanonicalModelModifiedOn
                            );
                            ";

        public const string UPDATE_CANONICALMODEL = @"
                            UPDATE MCANONICALMODEL SET
                                NAME = @CanonicalModelName,
                                MODULEID = @CanonicalModelModuleId,
                                SECTIONID = @CanonicalModelSectionId,
                                DESCRIPTION = @CanonicalModelDescription,
                                FIELDDATA = @CanonicalModelFieldData,
                                SAMPLEDATA = @CanonicalModelSampleData,
                                DISPLAYTEMPLATEID = @CanonicalModelDisplayTemplateId,
                                SORTORDER = @CanonicalModelSortOrder,
                                STATUS = @CanonicalModelStatus,
                                VERSION = @CanonicalModelVersion,
                                TENANTID = @CanonicalModelTenantId,
                                MODIFIEDBYID = @CanonicalModelModifiedById,
                                MODIFIEDON = @CanonicalModelModifiedOn
                            WHERE CANONICALMODELID = @CanonicalModelId;
                            ";

        public const string DELETE_CANONICALMODEL = @"
                            DELETE FROM MCANONICALMODEL
                            WHERE CANONICALMODELID = @canonicalmodelid;
                            ";
    }
}