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

namespace MMDAL.Query.Delivery
{
    public static class DeliveryQB
    {
        public const string GET_DELIVERY = @"SELECT
                            D.DELIVERYID AS DeliveryId,
                            D.TYPE AS DeliveryType,
                            D.FROMCOUNTRYID AS FromCountryId,
                            CO.COUNTRYCODE AS FromCountryCode,
                            CO.COUNTRYNAME AS FromCountryName,
                            D.TOCOUNTRYID AS ToCountryId,
                            CON.COUNTRYCODE AS ToCountryCode,
                            CON.COUNTRYNAME AS ToCountryName,
                            D.FROMPORTID AS FromPortId,
                            P.PORTNAME AS FromPortName,
                            D.TOPORTID AS ToPortId,
                            PO.PORTNAME AS ToPortName,
                            D.MODEOFDELIVERY AS DeliveryModeOfDelivery,
                            D.NOOFDAYS AS DeliveryNoOfDays,
                            D.SORTORDER AS DeliverySortOrder,
                            D.VERSION AS DeliveryVersion,
                            D.STATUS AS DeliveryStatus,
                            D.CREATEDBYID AS DeliveryCreatedById,
                            D.CREATEDON AS DeliveryCreatedOn,
                            D.MODIFIEDBYID AS DeliveryModifiedById,
                            D.MODIFIEDON AS DeliveryModifiedOn,
                            D.FROMCITYID AS FromCityId,
                            CI.CITYCODE AS FromCityCode,
                            CI.CITYNAME AS FromCityName,
                            D.TOCITYID AS ToCityId,
                            CT.CITYCODE AS ToCityCode,
                            CT.CITYNAME AS ToCityName
                            FROM MDELIVERY D
                            LEFT JOIN MCOUNTRY CO ON D.FROMCOUNTRYID = CO.COUNTRYID
                            LEFT JOIN MCOUNTRY CON ON D.TOCOUNTRYID = CON.COUNTRYID
                            LEFT JOIN MPORT P ON D.FROMPORTID = P.PORTID
                            LEFT JOIN MPORT PO ON D.TOPORTID = PO.PORTID
                            LEFT JOIN MCITY CI ON D.FROMCITYID = CI.CITYID
                            LEFT JOIN MCITY CT ON D.TOCITYID = CT.CITYID
                            WHERE D.DELIVERYID = @deliveryid;
                            ";

        public const string SAVE_DELIVERY = @"INSERT 
                            INTO MDELIVERY (
                                 DELIVERYID,
                                 TYPE,
                                 FROMCOUNTRYID,
                                 TOCOUNTRYID,
                                 FROMPORTID,
                                 TOPORTID,
                                 MODEOFDELIVERY,
                                 NOOFDAYS,
                                 SORTORDER,
                                 VERSION,
                                 STATUS,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 FROMCITYID,
                                 TOCITYID
                            ) VALUES (
                                 @DeliveryId,
                                 @DeliveryType,
                                 @FromCountryId,
                                 @ToCountryId,
                                 @FromPortId,
                                 @ToPortId,
                                 @DeliveryModeOfDelivery,
                                 @DeliveryNoOfDays,
                                 @DeliverySortOrder,
                                 @DeliveryVersion,
                                 @DeliveryStatus,
                                 @DeliveryCreatedById,
                                 @DeliveryCreatedOn,
                                 @DeliveryModifiedById,
                                 @DeliveryModifiedOn,
                                 @FromCityId,
                                 @ToCityId
                            );
                            ";

        public const string UPDATE_DELIVERY = @"UPDATE 
                            MDELIVERY SET
                            TYPE = @DeliveryType,
                            FROMCOUNTRYID = @FromCountryId,
                            TOCOUNTRYID = @ToCountryId,
                            FROMPORTID = @FromPortId,
                            TOPORTID = @ToPortId,
                            MODEOFDELIVERY = @DeliveryModeOfDelivery,
                            NOOFDAYS = @DeliveryNoOfDays,
                            SORTORDER = @DeliverySortOrder,
                            VERSION = @DeliveryVersion,
                            STATUS = @DeliveryStatus,
                            MODIFIEDBYID = @DeliveryModifiedById,
                            MODIFIEDON = @DeliveryModifiedOn,
                            FROMCITYID = @FromCityId,
                            TOCITYID = @ToCityId
                            WHERE DELIVERYID = @DeliveryId;
                            ";

        public const string DELETE_DELIVERY = @"DELETE 
                            FROM MDELIVERY
                            WHERE DELIVERYID = @deliveryid;
                            ";
    }
}