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

namespace MMDAL.Query.PackSet
{
    public class PackSetQB
    {
        public const string GET_PACKSET = @"SELECT
                                            -- PackSet (Parent)
                                            PS.PACKSETID          AS PackSetId,
                                            PS.PACKSETCODE        AS PackSetCode,
                                            PS.PACKSETNAME        AS PackSetName,
                                            PS.SORTORDER          AS PackSetSortOrder,
                                            PS.STATUS             AS PackSetStatus,
                                            PS.VERSION            AS PackSetVersion,
                                            PS.SOURCETYPE         AS PackSetSourceType,
                                            PS.CREATEDBYID        AS PackSetCreatedById,
                                            PS.CREATEDON          AS PackSetCreatedOn,
                                            CU.USERNAME           AS PackSetCreatedByName,
                                            PS.MODIFIEDBYID       AS PackSetModifiedById,
                                            PS.MODIFIEDON         AS PackSetModifiedOn,
                                            MU.USERNAME           AS PackSetModifiedByName,
                                                        -- PackSet Detail (Child)
                                                        PSD.PACKSETDETAILID   AS PackSetDetailId,
                                                        PSD.PACKSETID         AS PackSetId,
                                                        PS.PACKSETCODE        AS PackSetCode,
                                                        PS.PACKSETNAME        AS PackSetName,
                                                        PSD.SLNO              AS PackSetDetailSlNo,
                                                        PSD.PACKID            AS PackId,
                                                        P.PACKCODE            AS PackCode,
                                                        P.PACKNAME            AS PackName,
                                                        PSD.CONVERSIONTYPE    AS PackSetDetailConversionType,
                                                        PSD.CONVERSIONFACTOR  AS PackSetDetailConversionFactor
                                        FROM MPACKSET PS
                                        LEFT JOIN MPACKSETDETAIL PSD ON PSD.PACKSETID = PS.PACKSETID
                                        LEFT JOIN MPACK P ON P.PACKID = PSD.PACKID
                                        LEFT JOIN MUSER CU ON CU.USERID = PS.CREATEDBYID
                                        LEFT JOIN MUSER MU ON MU.USERID = PS.MODIFIEDBYID
                                        WHERE PS.PACKSETID = @packsetid;";

        public const string SAVE_PACKSET = @"INSERT INTO MPACKSET
                                            (
                                                PACKSETID,
                                                PACKSETCODE,
                                                PACKSETNAME,
                                                SORTORDER,
                                                STATUS,
                                                VERSION,
                                                CREATEDBYID,
                                                CREATEDON,
                                                MODIFIEDBYID,
                                                MODIFIEDON,
                                                SOURCETYPE                                            )
                                            VALUES
                                            (
                                                @PackSetId,
                                                @PackSetCode,
                                                @PackSetName,
                                                @PackSetSortOrder,
                                                @PackSetStatus,
                                                @PackSetVersion,
                                                @PackSetCreatedById,
                                                @PackSetCreatedOn,
                                                @PackSetModifiedById,
                                                @PackSetModifiedOn,
                                                @PackSetSourceType                                            
                                            );";
        
        public const string SAVE_PACKSET_DETAIL= @"INSERT INTO MPACKSETDETAIL
                                                    (
                                                        PACKSETDETAILID,
                                                        PACKSETID,
                                                        SLNO,
                                                        PACKID,
                                                        CONVERSIONTYPE,
                                                        CONVERSIONFACTOR
                                                    )
                                                    VALUES
                                                    (
                                                        @PackSetDetailId,
                                                        @PackSetId,
                                                        @PackSetDetailSlNo,
                                                        @PackId,
                                                        @PackSetDetailConversionType,
                                                        @PackSetDetailConversionFactor
                                                    );";

        public const string UPDATE_PACKSET = @"UPDATE MPACKSET
                                            SET
                                                PACKSETCODE   = @PackSetCode,
                                                PACKSETNAME   = @PackSetName,
                                                SORTORDER     = @PackSetSortOrder,
                                                STATUS        = @PackSetStatus,
                                                VERSION       = @PackSetVersion,
                                                MODIFIEDBYID  = @PackSetModifiedById,
                                                MODIFIEDON    = @PackSetModifiedOn,
                                                SOURCETYPE    = @PackSetSourceType
                                            WHERE
                                                PACKSETID = @PackSetId;";

        public const string UPDATE_PACKSET_DETAIL = @"UPDATE MPACKSETDETAIL
                                                        SET
                                                            PACKSETID         = @PackSetId,
                                                            SLNO              = @PackSetDetailSlNo,
                                                            PACKID            = @PackId,
                                                            CONVERSIONTYPE    = @PackSetDetailConversionType,
                                                            CONVERSIONFACTOR  = @PackSetDetailConversionFactor
                                                        WHERE
                                                            PACKSETDETAILID = @PackSetDetailId;";

        public const string DELETE_PACKSET = @"DELETE FROM MPACKSET
                                                    WHERE PACKSETID = @packsetid;";

        public const string DELETE_PACKSET_DETAIL = @"DELETE FROM MPACKSETDETAIL
                                                            WHERE PACKSETID = @packsetid";

        public const string GET_SELECTLIST_PACKSET = @"SELECT
                                                            PACKSETID           AS Id,
                                                            PACKSETCODE         AS Code,
                                                            PACKSETNAME         AS Name
                                                        FROM MPACKSET;";

        public const string GET_PACKSET_COUNT= @"SELECT COUNT(1)
                                                    FROM dbo.MPACKSET
                                                    WHERE
                                                        (
                                                            @PackSetId <> -1 AND PACKSETID = @PackSetId
                                                        )
                                                        AND PACKSETID <> @PackSetId;";
    }
}