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

namespace FrameworkDAL.Query.Country
{
    public static class CountryQB
    {
        public const string GET_COUNTRY = @"
                                         SELECT 
                                         MC.COUNTRYID   AS CountryId,
                                         MC.COUNTRYCODE AS CountryCode,
                                         MC.COUNTRYNAME AS CountryName,
                                         MC.SORTORDER   AS CountrySortOrder,
                                         MC.STATUS      AS CountryStatus,
                                         MC.VERSION     AS CountryVersion,
                                         MC.SOURCETYPE  AS CountrySourceType,
                                         MC.CREATEDBYID AS CountryCreatedById,
                                         MC.CREATEDON   AS CountryCreatedOn,
                                         MC.MODIFIEDBYID AS CountryModifiedById,
                                         MC.MODIFIEDON  AS CountryModifiedOn,
                                         MC.GEOLOCATION AS CountryGeolocation,
                                         MC.BORDER      AS CountryBorder,
                                         MC.ISDCODE     AS CountryISDCode,
                                         MC.GSTCOUNTRYCODE AS CountryGstCountryCode
                                         FROM 
                                         MCOUNTRY MC
                                         WHERE 
                                         MC.COUNTRYID = @countryid;";

        //public const string GET_SELECTLIST_COUNTRY = @"WITH PagedCountries AS (
        //                                        SELECT
        //                                            MCOUNTRY.COUNTRYID AS Id,
        //                                            MCOUNTRY.COUNTRYCODE AS Code,
        //                                            MCOUNTRY.COUNTRYNAME AS Name,
        //                                            MCOUNTRY.ISDCODE AS ISDCode,
        //                                            ROW_NUMBER() OVER (ORDER BY MCOUNTRY.COUNTRYID) AS RowNum
        //                                        FROM MCOUNTRY
        //                                    )
        //                                    SELECT Id, Code, Name, ISDCode
        //                                    FROM PagedCountries
        //                                    WHERE 
        //                                     (@firstnumber = -1 AND @maxresult = -1)
        //                                     OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_COUNTRY = @"SELECT
                                                        COUNTRYID AS Id,
                                                        COUNTRYCODE AS Code,
                                                        COUNTRYNAME AS Name,
                                                        ISDCODE AS ISDCode
                                                    FROM
                                                        MCOUNTRY
                                                    ORDER BY
                                                        COUNTRYNAME ASC;";

        public const string SAVE_COUNTRY = @"INSERT INTO MCOUNTRY 
                                                (
                                                    COUNTRYID,
                                                    COUNTRYCODE,
                                                    COUNTRYNAME,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    ISDCODE,
                                                    GSTCOUNTRYCODE
                                                )
                                                VALUES 
                                                (
                                                    @CountryId,
                                                    @CountryCode,
                                                    @CountryName,
                                                    @CountryStatus,
                                                    @CountryVersion,
                                                    @CountryCreatedById,
                                                    @CountryCreatedOn,
                                                    @CountryModifiedById,
                                                    @CountryModifiedOn,
                                                    @CountryISDCode,
                                                    @CountryGstCountryCode
                                                );";

        public const string UPDATE_COUNTRY = @"UPDATE MCOUNTRY
                                                SET 
                                                    COUNTRYCODE       = @CountryCode,
                                                    COUNTRYNAME       = @CountryName,
                                                    STATUS            = @CountryStatus,
                                                    VERSION           = @CountryVersion,
                                                    MODIFIEDBYID      = @CountryModifiedById,
                                                    MODIFIEDON        = @CountryModifiedOn,
                                                    ISDCODE           = @CountryISDCode,
                                                    GSTCOUNTRYCODE    = @CountryGstCountryCode
                                                WHERE COUNTRYID = @CountryId;";

        public const string DELETE_COUNTRY = @"DELETE FROM MCOUNTRY
                                            WHERE COUNTRYID = @countryid;";

    }   
}