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

namespace FrameworkDAL.Query.City
{
    public static class CityQB
    {
       public const string GET_CITY = @"SELECT 
                                            MC.CITYID             AS CityId,
                                            MC.CITYCODE           AS CityCode,
                                            MC.CITYNAME           AS CityName,
                                            MC.COUNTRYID          AS CountryId,
                                            C.COUNTRYCODE         AS CountryCode,
                                            C.COUNTRYNAME         AS CountryName,
                                            MC.STATEID            AS StateId,
                                            S.STATECODE           AS StateCode,
                                            S.STATENAME           AS StateName,
                                            S.TINSTART            AS StateTINStart,
                                            MC.SORTORDER          AS CitySortOrder,
                                            MC.STATUS             AS CityStatus,
                                            MC.VERSION            AS CityVersion,
                                            MC.SOURCETYPE         AS CitySourceType,
                                            MC.CREATEDBYID        AS CityCreatedById,
                                            MC.CREATEDON          AS CityCreatedOn,
                                            MC.MODIFIEDBYID       AS CityModifiedById,
                                            MC.MODIFIEDON         AS CityModifiedOn,
                                            MC.TRAFFICREMARKS     AS TrafficRemarks,
                                            MC.GEOLOCATION        AS CityGeolocation,
                                            MC.BORDER             AS CityBorder,
                                            MC.STDCODE            AS StdCode,
                                            MC.CITYCLASSID        AS CityClassId,
                                            G.GCMCODE             AS CityClassCode,
                                            G.GCMNAME             AS CityClassName,
                                            MC.TENANTID           AS TenantId
                                        FROM MCITY MC WITH (NOLOCK)
                                        LEFT JOIN MCOUNTRY C WITH (NOLOCK) ON MC.COUNTRYID = C.COUNTRYID
                                        LEFT JOIN MSTATE S WITH (NOLOCK) ON MC.STATEID = S.STATEID
                                        LEFT JOIN MGCM   G WITH (NOLOCK) ON MC.CITYCLASSID = G.GCMID
                                        WHERE MC.CITYID = @cityid;";


        public const string SAVE_CITY = @"INSERT INTO MCITY (
                                                        CITYID,
                                                        CITYCODE,
                                                        CITYNAME,
                                                        COUNTRYID,
                                                        STATUS,
                                                        VERSION,
                                                        SOURCETYPE,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        STATEID,
                                                        TRAFFICREMARKS,
                                                        STDCODE
                                                    )
                                                    VALUES (
                                                        @CityId,
                                                        @CityCode,
                                                        @CityName,
                                                        @CountryId,
                                                        @CityStatus,
                                                        @CityVersion,
                                                        @CitySourceType,
                                                        @CityCreatedById,
                                                        @CityCreatedOn,
                                                        @CityModifiedById,
                                                        @CityModifiedOn,
                                                        @StateId,
                                                        @TrafficRemarks,
                                                        @StdCode  
                                                    );
                                                    ";

        public const string UPDATE_CITY = @"UPDATE MCITY SET
                                                CITYCODE = @CityCode,
                                                CITYNAME = @CityName,
                                                COUNTRYID = @CountryId,
                                                STATUS = @CityStatus,
                                                VERSION = @CityVersion,
                                                SOURCETYPE = @CitySourceType,
                                                CREATEDBYID = @CityCreatedById,
                                                CREATEDON = @CityCreatedOn,
                                                MODIFIEDBYID = @CityModifiedById,
                                                MODIFIEDON = @CityModifiedOn,
                                                STATEID = @StateId,
                                                TRAFFICREMARKS = @TrafficRemarks,
                                                STDCODE = @StdCode
                                            WHERE CITYID = @CityId;
                                        ";

        public const string GET_CITYPICKLIST_FOR_SELECT2 = @"
WITH PagedCity AS (
    SELECT 
        city.CITYID as Id, 
        city.CITYCODE as Code, 
        city.CITYNAME as Name, 
        states.STATEID as StateId, 
        states.STATENAME as StateName, 
        country.COUNTRYID as CountryId, 
        country.COUNTRYNAME as CountryName,
        ROW_NUMBER() OVER (ORDER BY city.CITYID) AS RowNum
    FROM 
        MCity city
        INNER JOIN MCOUNTRY country ON country.COUNTRYID = city.COUNTRYID
        INNER JOIN MSTATE states ON states.STATEID = city.STATEID
    WHERE 1=1
    /**WHERE_CLAUSE**/
)
SELECT Id, Code, Name, StateId, StateName, CountryId, CountryName
FROM PagedCity
WHERE 
(
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult)
)";
    


        public const string DELETE_CITY = @"DELETE FROM MCITY
                                            WHERE CITYID = @cityid;";
    }
}