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

namespace FrameworkDAL.Query.Port
{
    public static class PortQB
    {
        public const string GET_PORT = @" 
                                    SELECT 
                                    MP.PORTID       AS PortId,
                                    MP.PORTNAME     AS PortName,
                                    MP.COUNTRYID    AS CountryId,
                                    MP.SORTORDER    AS PortSortOrder,
                                    MP.STATUS       AS PortStatus,
                                    MP.VERSION      AS PortVersion,
                                    MP.CREATEDBYID  AS PortCreatedById,
                                    MP.CREATEDON    AS PortCreatedOn,
                                    MP.MODIFIEDBYID AS PortModifiedById,
                                    MP.MODIFIEDON   AS PortModifiedOn,
                                    MP.PORTCODE     AS PortCode,
                                    MP.SOURCETYPE   AS SourceType,
                                    MP.GSTPORTCODE  AS PortGstPortCode
                                    FROM MPORT MP
                                    WHERE
                                    MP.PORTID = @portid
                                    ";

        public const string GET_SELECTLIST_PORT = @"SELECT
                                            PORTID AS Id,
                                            PORTCODE AS Code,
                                            PORTNAME AS Name 
                                            FROM MPORT;";

        public const string SAVE_PORT = @"INSERT INTO MPORT (
                                            PORTID,
                                            PORTNAME,
                                            COUNTRYID,
                                            PORTCODE,
                                            GSTPORTCODE,
                                            SORTORDER,
                                            STATUS,
                                            VERSION,
                                            SOURCETYPE,
                                            CREATEDBYID,
                                            CREATEDON,
                                            MODIFIEDBYID,
                                            MODIFIEDON
                                        )
                                        VALUES (
                                            @PortId,
                                            @PortName,
                                            @CountryId,
                                            @PortCode,
                                            @PortGstPortCode,
                                            @PortSortOrder,
                                            @PortStatus,
                                            @PortVersion,
                                            @SourceType,
                                            @PortCreatedById,
                                            @PortCreatedOn,
                                            @PortModifiedById,
                                            @PortModifiedOn
                                        );
                                        ";

        public const string UPDATE_PORT = @"UPDATE MPORT
                                            SET
                                                PORTNAME = @PortName,
                                                COUNTRYID = @CountryId,
                                                PORTCODE = @PortCode,
                                                GSTPORTCODE = @PortGstPortCode,
                                                SORTORDER = @PortSortOrder,
                                                STATUS = @PortStatus,
                                                VERSION = @PortVersion,
                                                SOURCETYPE = @SourceType,
                                                MODIFIEDBYID = @PortModifiedById,
                                                MODIFIEDON = @PortModifiedOn
                                            WHERE
                                                PORTID = @PortId
                                                 AND NOT EXISTS (
                                                        SELECT 1 
                                                        FROM MPORT 
                                                        WHERE PORTCODE = @PORTCODE 
                                                        AND PORTID <> @PORTID)";

        public const string DELETE_PORT = @"DELETE FROM MPORT 
                                          WHERE PORTID = @portid;";
    }
}