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

namespace MMDAL.Query.ItemCategory
{
    public class ItemCategoryQB
    {
        public const string GET_ITEMCATEGORY = @"SELECT
                                                IC.ITEMCATEGORYID         AS ItemCategoryId,
                                                IC.ITEMCATEGORYCODE       AS ItemCategoryCode,
                                                IC.ITEMCATEGORYNAME       AS ItemCategoryName,
                                                IC.ISSELECTED             AS ItemCategoryIsSelected,
                                                IC.REMARKS                AS ItemCategoryRemarks,
                                                IC.SORTORDER              AS ItemCategorySortOrder,
                                                IC.STATUS                 AS ItemCategoryStatus,
                                                IC.VERSION                AS ItemCategoryVersion,
                                                IC.CREATEDBYID            AS ItemCategoryCreatedById,
                                                IC.CREATEDON              AS ItemCategoryCreatedOn,
                                                IC.MODIFIEDBYID           AS ItemCategoryModifiedById,
                                                IC.MODIFIEDON             AS ItemCategoryModifiedOn,
                                                IC.ISASSET                AS ItemCategoryIsAsset,
                                                IC.ISITEM                 AS ItemCategoryIsItem,
                                                IC.TENANTID               AS TenantId,
                                                IC.SOURCETYPE             AS SourceType
                                            FROM MITEMCATEGORY IC
                                            INNER JOIN MCLIENT C ON IC.TENANTID = C.CLIENTID
                                            INNER JOIN MUSER U ON IC.CREATEDBYID = U.USERID
                                            INNER JOIN MUSER UM ON IC.MODIFIEDBYID = UM.USERID
                                            WHERE IC.ITEMCATEGORYID = @itemcategoryid;";

        public const string SAVE_ITEMCATEGORY = @"INSERT INTO MITEMCATEGORY
                                                                (
                                                                    ITEMCATEGORYID,
                                                                    ITEMCATEGORYCODE,
                                                                    ITEMCATEGORYNAME,
                                                                    ISSELECTED,
                                                                    REMARKS,
                                                                    SORTORDER,
                                                                    STATUS,
                                                                    VERSION,
                                                                    CREATEDBYID,
                                                                    CREATEDON,
                                                                    MODIFIEDBYID,
                                                                    MODIFIEDON,
                                                                    SOURCETYPE,    
                                                                    ISASSET,
                                                                    ISITEM,
                                                                    TENANTID
                                                                )
                                                                VALUES
                                                                (
                                                                    @ItemCategoryId,
                                                                    @ItemCategoryCode,
                                                                    @ItemCategoryName,
                                                                    @ItemCategoryIsSelected,
                                                                    @ItemCategoryRemarks,
                                                                    @ItemCategorySortOrder,
                                                                    @ItemCategoryStatus,
                                                                    @ItemCategoryVersion,
                                                                    @ItemCategoryCreatedById,
                                                                    @ItemCategoryCreatedOn,
                                                                    @ItemCategoryModifiedById,
                                                                    @ItemCategoryModifiedOn,
                                                                    @SourceType,
                                                                    @ItemCategoryIsAsset,
                                                                    @ItemCategoryIsItem,
                                                                    @TenantId   
                                                                );";
        
        public const string UPDATE_ITEMCATEGORY = @"UPDATE MITEMCATEGORY
                                                    SET
                                                        ITEMCATEGORYCODE       = @ItemCategoryCode,
                                                        ITEMCATEGORYNAME       = @ItemCategoryName,
                                                        ISSELECTED             = @ItemCategoryIsSelected,
                                                        REMARKS                = @ItemCategoryRemarks,
                                                        SORTORDER              = @ItemCategorySortOrder,
                                                        STATUS                 = @ItemCategoryStatus,
                                                        VERSION                = @ItemCategoryVersion,
                                                        MODIFIEDBYID           = @ItemCategoryModifiedById,
                                                        MODIFIEDON             = @ItemCategoryModifiedOn,
                                                        ISASSET                = @ItemCategoryIsAsset,
                                                        ISITEM                 = @ItemCategoryIsItem,
                                                        SOURCETYPE             = @SourceType,
                                                        TENANTID               = @TenantId
                                                    WHERE
                                                        ITEMCATEGORYID = @ItemCategoryId;";

        public const string GET_SELECTLIST_ITEMCATEGORY = @"SELECT 
                                                            IC.ITEMCATEGORYID     AS Id,
                                                            IC.ITEMCATEGORYCODE   AS Code,
                                                            IC.ITEMCATEGORYNAME   AS Name
                                                        FROM MITEMCATEGORY IC
                                                        ORDER BY IC.ITEMCATEGORYNAME;";

        public const string DELETE_ITEMCATEGORY = @"DELETE FROM MITEMCATEGORY
                                                    WHERE ITEMCATEGORYID = @itemcategoryid;";
    }
}