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

namespace MMDAL.Query.Pack
{
    public class PackQB
    {

        public const string GET_PACK = @"SELECT
                                            P.PACKID              AS PackId,
                                            P.PACKCODE            AS PackCode,
                                            P.PACKNAME            AS PackName,
                                            P.CONVERSIONTYPE      AS PackConversionType,
                                            P.CONVERSIONFACTOR    AS PackConversionFactor,
                                            P.SORTORDER           AS PackSortOrder,
                                            P.STATUS              AS PackStatus,
                                            P.VERSION             AS PackVersion,
                                            P.CREATEDBYID         AS PackCreatedById,
                                            P.CREATEDON           AS PackCreatedOn,
                                            CU.USERNAME           AS PackCreatedByName,
                                            P.MODIFIEDBYID        AS PackModifiedById,
                                            P.MODIFIEDON          AS PackModifiedOn,
                                            MU.USERNAME           AS PackModifiedByName
                                        FROM MPACK P
                                        LEFT JOIN MUSER CU ON CU.USERID = P.CREATEDBYID
                                        LEFT JOIN MUSER MU ON MU.USERID = P.MODIFIEDBYID
                                        WHERE P.PACKID = @packid;";

        public const string SAVE_PACK = @"INSERT INTO MPACK
                                            (
                                                PACKID,
                                                PACKCODE,
                                                PACKNAME,
                                                CONVERSIONTYPE,
                                                CONVERSIONFACTOR,
                                                SORTORDER,
                                                STATUS,
                                                VERSION,
                                                CREATEDBYID,
                                                CREATEDON,
                                                MODIFIEDBYID,
                                                MODIFIEDON
                                            )
                                            VALUES
                                            (
                                                @PackId,
                                                @PackCode,
                                                @PackName,
                                                @PackConversionType,
                                                @PackConversionFactor,
                                                @PackSortOrder,
                                                @PackStatus,
                                                @PackVersion,
                                                @PackCreatedById,
                                                @PackCreatedOn,
                                                @PackModifiedById,
                                                @PackModifiedOn
                                            );";

        public const string UPDATE_PACK = @"UPDATE MPACK
                                            SET
                                                PACKCODE          = @PackCode,
                                                PACKNAME          = @PackName,
                                                CONVERSIONTYPE    = @PackConversionType,
                                                CONVERSIONFACTOR  = @PackConversionFactor,
                                                SORTORDER         = @PackSortOrder,
                                                STATUS            = @PackStatus,
                                                VERSION           = VERSION + 1,
                                                MODIFIEDBYID      = @PackModifiedById,
                                                MODIFIEDON        = @PackModifiedOn
                                            WHERE PACKID = @PackId;";

        public const string DELETE_PACK = @"DELETE FROM MPACK 
                                              WHERE PACKID = @packid";

        public const string GET_SELECTLIST_PACK = @"SELECT
                                                        PACKID AS Id,
                                                        PACKCODE AS Code,
                                                        PACKNAME AS Name,
                                                        CONVERSIONTYPE AS ConversionFactor
                                                        FROM MPACK;";
    }
}