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

namespace FrameworkDAL.Query.State
{
    public static class StateQB
    {
        public const string GET_SELECTLIST_STATE = @"
SELECT
    S.STATEID        AS Id,
    S.STATECODE      AS Code,
    S.STATENAME      AS Name,
    S.TINSTART       AS TINStart,
    S.GSTSTATECODE   AS GSTStateCode,
    C.COUNTRYID      AS CountryId,
    C.COUNTRYCODE    AS CountryCode,
    C.COUNTRYNAME    AS CountryName
FROM MSTATE S
JOIN MCOUNTRY C 
    ON C.COUNTRYID = S.COUNTRYID
/**WHERE_CLAUSE**/
ORDER BY S.STATENAME";




        public const string GET_STATE = @"SELECT
                                            S.STATEID AS StateId,
                                            S.STATECODE AS StateCode,
                                            S.STATENAME AS StateName,
                                            S.GSTSTATECODE AS StateGSTStateCode,
                                            S.TINSTART AS StateTINStart,
                                            S.COUNTRYID AS CountryId,
                                            C.COUNTRYCODE AS CountryCode,
                                            C.COUNTRYNAME AS CountryName,

                                            S.CREATEDBYID AS StateCreatedById,
                                            S.CREATEDON AS StateCreatedOn,
                                            UC.USERNAME AS StateCreatedByName,

                                            S.MODIFIEDBYID AS StateModifiedById,
                                            S.MODIFIEDON AS StateModifiedOn,
                                            UM.USERNAME AS StateModifiedByName,

                                            S.SORTORDER AS StateSortOrder,
                                            S.STATUS AS StateStatus,
                                            S.VERSION AS StateVersion,
                                            S.SOURCETYPE AS StateSourceType,
                                            S.GEOLOCATION AS StateGeolocation,
                                            S.BORDER AS StateBorder

                                        FROM MSTATE S
                                        LEFT JOIN MUSER UC ON S.CREATEDBYID = UC.USERID
                                        LEFT JOIN MUSER UM ON S.MODIFIEDBYID = UM.USERID
                                        LEFT JOIN MCOUNTRY C ON S.COUNTRYID = C.COUNTRYID
                                        WHERE S.STATEID = @StateId;";

        public const string SAVE_STATE = @"INSERT INTO MSTATE (
                                            STATEID,
                                            STATECODE,
                                            STATENAME,
                                            GSTSTATECODE,
                                            TINSTART,
                                            COUNTRYID,
                                            SORTORDER,
                                            STATUS,
                                            VERSION,
                                            CREATEDBYID,
                                            CREATEDON,
                                            MODIFIEDBYID,
                                            MODIFIEDON,
                                            SOURCETYPE,
                                            GEOLOCATION,
                                            BORDER
                                        )
                                        VALUES (
                                            @StateId,
                                            @StateCode,
                                            @StateName,
                                            @StateGSTStateCode,
                                            @StateTINStart,
                                            @CountryId,
                                            @StateSortOrder,
                                            @StateStatus,
                                            @StateVersion,
                                            @StateCreatedById,
                                            @StateCreatedOn,
                                            @StateModifiedById,
                                            @StateModifiedOn,
                                            @StateSourceType,
                                            @StateGeolocation,
                                            @StateBorder
                                        );";

        public const string UPDATE_STATE = @"UPDATE MSTATE
                                            SET
                                                STATECODE = @StateCode,
                                                STATENAME = @StateName,
                                                GSTSTATECODE = @StateGSTStateCode,
                                                TINSTART = @StateTINStart,
                                                COUNTRYID = @CountryId,
                                                SORTORDER = @StateSortOrder,
                                                STATUS = @StateStatus,
                                                VERSION = @StateVersion,
                                                CREATEDBYID = @StateCreatedById,
                                                CREATEDON = @StateCreatedOn,
                                                MODIFIEDBYID = @StateModifiedById,
                                                MODIFIEDON = @StateModifiedOn,
                                                SOURCETYPE = @StateSourceType,
                                                GEOLOCATION = @StateGeolocation,
                                                BORDER = @StateBorder
                                            WHERE STATEID = @StateId;";

        public const string DELETE_State = @"DELETE FROM MSTATE WHERE STATEID = @StateId;";
    }
}