using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.Query.AttributeType
{
    public class AttributeTypeQB
    {
        public const string GET_ATTRIBUTETYPE = @"SELECT
                                                    AT.ATTRIBUTETYPEID        AS AttributeTypeId,
                                                    AT.ATTRIBUTETYPECODE      AS AttributeTypeCode,
                                                    AT.ATTRIBUTETYPENAME      AS AttributeTypeName,
                                                    AT.REMARKS                AS AttributeTypeRemarks,
                                                    AT.SORTORDER              AS AttributeTypeSortOrder,
                                                    AT.STATUS                 AS AttributeTypeStatus,
                                                    AT.VERSION                AS AttributeTypeVersion,
                                                    AT.SOURCETYPE             AS AttributeTypeSourceType,
                                                    AT.CREATEDBYID            AS AttributeTypeCreatedById,
                                                    AT.CREATEDON              AS AttributeTypeCreatedOn,
                                                    CB.USERNAME               AS CreatedByName,
                                                    AT.MODIFIEDBYID           AS AttributeTypeModifiedById,
                                                    AT.MODIFIEDON             AS AttributeTypeModifiedOn,
                                                    MB.USERNAME               AS ModifiedByName,
                                                    AT.TENANTID               AS TenantId

                                                FROM MATTRIBUTETYPE AT
                                                LEFT JOIN MUSER CB ON CB.USERID = AT.CREATEDBYID
                                                LEFT JOIN MUSER MB ON MB.USERID = AT.MODIFIEDBYID
                                                WHERE AT.ATTRIBUTETYPEID = @attributetypeid;";

        public const string SAVE_ATTRIBUTETYPE = @"INSERT INTO MATTRIBUTETYPE
                                                    (
                                                        ATTRIBUTETYPEID,
                                                        ATTRIBUTETYPECODE,
                                                        ATTRIBUTETYPENAME,
                                                        REMARKS,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        SOURCETYPE
                                                    )
                                                    VALUES
                                                    (
                                                        @AttributeTypeId,
                                                        @AttributeTypeCode,
                                                        @AttributeTypeName,
                                                        @AttributeTypeRemarks,
                                                        @AttributeTypeSortOrder,
                                                        @AttributeTypeStatus,
                                                        @AttributeTypeVersion,
                                                        @AttributeTypeCreatedById,
                                                        @AttributeTypeCreatedOn,
                                                        @AttributeTypeModifiedById,
                                                        @AttributeTypeModifiedOn,
                                                        @AttributeTypeSourceType
                                                    );";

        public const string UPDATE_ATTRIBUTETYPE = @"UPDATE MATTRIBUTETYPE
                                                    SET
                                                        ATTRIBUTETYPECODE   = @AttributeTypeCode,
                                                        ATTRIBUTETYPENAME   = @AttributeTypeName,
                                                        REMARKS             = @AttributeTypeRemarks,
                                                        SORTORDER           = @AttributeTypeSortOrder,
                                                        STATUS              = @AttributeTypeStatus,
                                                        VERSION             = @AttributeTypeVersion,
                                                        MODIFIEDBYID        = @AttributeTypeModifiedById,
                                                        MODIFIEDON          = @AttributeTypeModifiedOn,
                                                        SOURCETYPE          = @AttributeTypeSourceType
                                                    WHERE ATTRIBUTETYPEID  = @AttributeTypeId;";

        public const string DELETE_ATTRIBUTETYPE = @"DELETE FROM MATTRIBUTETYPE
                                                        WHERE ATTRIBUTETYPEID = @attributetypeid;";

        public const string GET_SELECTLIST_ATTRIBUTETYPE = @"WITH PagedAttributeType AS (
                                                        SELECT
                                                            AT.ATTRIBUTETYPEID           AS Id,
                                                            AT.ATTRIBUTETYPECODE         AS Code,
                                                            AT.ATTRIBUTETYPENAME         AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY AT.ATTRIBUTETYPENAME) AS RowNum
                                                        FROM 
                                                            MATTRIBUTETYPE AT
                                                        WHERE 
                                                            AT.STATUS = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name
                                                    FROM PagedAttributeType
                                                   WHERE(@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult);";

    }
}