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

namespace MMDAL.Query.ItemSubCategory
{
    public class ItemSubCategoryQB
    {
        public const string GET_ITEMSUBCATEGORY = @"SELECT
                                                        ISC.ITEMSUBCATEGORYID        AS ItemSubCategoryId,
                                                        ISC.ITEMSUBCATEGORYCODE      AS ItemSubCategoryCode,
                                                        ISC.ITEMSUBCATEGORYNAME      AS ItemSubCategoryName,
                                                        ISC.ITEMCATEGORYID           AS ItemSubCategoryItemCategoryId,
                                                        IC.ITEMCATEGORYCODE          AS ItemSubCategoryItemCategoryCode,
                                                        IC.ITEMCATEGORYNAME          AS ItemSubCategoryItemCategoryName,
                                                        ISC.ISSELECTED               AS ItemSubCategoryIsSelected,
                                                        CASE 
                                                            WHEN ISC.ISSELECTED = 1 THEN 'No'
                                                            ELSE 'Yes'
                                                        END                          AS ItemSubCategoryIsSelectedName,
                                                        ISC.REMARKS                  AS ItemSubCategoryRemarks,
                                                        ISC.ISASSET                  AS ItemSubCategoryIsAsset,
                                                        ISC.ISITEM                   AS ItemSubCategoryIsItem,
                                                        ISC.CREATEDBYID              AS ItemSubCategoryCreatedById,
                                                        ISC.CREATEDON                AS ItemSubCategoryCreatedOn,
                                                        CU.USERNAME                  AS ItemSubCategoryCreatedByName,
                                                        ISC.MODIFIEDBYID             AS ItemSubCategoryModifiedById,
                                                        ISC.MODIFIEDON               AS ItemSubCategoryModifiedOn,
                                                        MU.USERNAME                  AS ItemSubCategoryModifiedByName,
                                                        ISC.SORTORDER                AS ItemSubCategorySortOrder,
                                                        ISC.STATUS                   AS ItemSubCategoryStatus,
                                                        ISC.VERSION                  AS ItemSubCategoryVersion,
                                                        ISC.SOURCETYPE               AS SourceType,
                                                        ISC.TENANTID                 AS TenantId
                                                    FROM MITEMSUBCATEGORY ISC
                                                    INNER JOIN MITEMCATEGORY IC  ON IC.ITEMCATEGORYID = ISC.ITEMCATEGORYID
                                                    LEFT JOIN MUSER CU  ON CU.USERID = ISC.CREATEDBYID
                                                    LEFT JOIN MUSER MU ON MU.USERID = ISC.MODIFIEDBYID
                                                    WHERE ISC.ITEMSUBCATEGORYID = @itemsubcategoryid;";

        public const string SAVE_ITEMSUBCATEGORY = @"INSERT INTO MITEMSUBCATEGORY
                                                        (
                                                            ITEMSUBCATEGORYID,
                                                            ITEMSUBCATEGORYCODE,
                                                            ITEMSUBCATEGORYNAME,
                                                            ITEMCATEGORYID,
                                                            ISSELECTED,
                                                            REMARKS,
                                                            SORTORDER,
                                                            STATUS,
                                                            VERSION,
                                                            CREATEDBYID,
                                                            CREATEDON,
                                                            MODIFIEDBYID,
                                                            MODIFIEDON,
                                                            ISASSET,
                                                            ISITEM
                                                        )
                                                        VALUES
                                                        (
                                                            @ItemSubCategoryId,
                                                            @ItemSubCategoryCode,
                                                            @ItemSubCategoryName,
                                                            @ItemSubCategoryItemCategoryId,
                                                            @ItemSubCategoryIsSelected,
                                                            @ItemSubCategoryRemarks,
                                                            @ItemSubCategorySortOrder,
                                                            @ItemSubCategoryStatus,
                                                            @ItemSubCategoryVersion,
                                                            @ItemSubCategoryCreatedById,
                                                            @ItemSubCategoryCreatedOn,
                                                            @ItemSubCategoryModifiedById,
                                                            @ItemSubCategoryModifiedOn,
                                                            @ItemSubCategoryIsAsset,
                                                            @ItemSubCategoryIsItem
                                                        );";

        public const string UPDATE_ITEMSUBCATEGORY = @"UPDATE MITEMSUBCATEGORY
                                                        SET
                                                            ITEMSUBCATEGORYCODE        = @ItemSubCategoryCode,
                                                            ITEMSUBCATEGORYNAME        = @ItemSubCategoryName,
                                                            ITEMCATEGORYID             = @ItemSubCategoryItemCategoryId,
                                                            ISSELECTED                 = @ItemSubCategoryIsSelected,
                                                            REMARKS                    = @ItemSubCategoryRemarks,
                                                            SORTORDER                  = @ItemSubCategorySortOrder,
                                                            STATUS                     = @ItemSubCategoryStatus,
                                                            VERSION                    = @ItemSubCategoryVersion,
                                                            MODIFIEDBYID               = @ItemSubCategoryModifiedById,
                                                            MODIFIEDON                 = @ItemSubCategoryModifiedOn,
                                                            ISASSET                    = @ItemSubCategoryIsAsset,
                                                            ISITEM                     = @ItemSubCategoryIsItem
                                                        WHERE
                                                            ITEMSUBCATEGORYID          = @ItemSubCategoryId;";

        public const string DELETE_ITEMSUBCATEGORY = @"DELETE FROM MITEMSUBCATEGORY
                                                        WHERE ITEMSUBCATEGORYID = @itemsubcategoryid;";

        public const string GET_SELECTLIST_ITEMSUBCATEGORY = @"SELECT
                                                                ISC.ITEMSUBCATEGORYID      AS Id,
                                                                ISC.ITEMSUBCATEGORYCODE    AS Code,
                                                                ISC.ITEMSUBCATEGORYNAME    AS Name,
                                                                ISC.ITEMCATEGORYID         AS ItemSubCategoryItemCategoryId,
                                                                IC.ITEMCATEGORYNAME        AS ItemSubCategoryItemCategoryName
                                                            FROM MITEMSUBCATEGORY ISC
                                                            INNER JOIN MITEMCATEGORY IC
                                                                ON IC.ITEMCATEGORYID = ISC.ITEMCATEGORYID";
    }
}