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

namespace LogisticsDAL.Query.Trip
{
    public static class TripQB
    {
        public const string GET_TRIP_SELECTLIST_SQL = @"
            WITH TripSelectList AS (
                SELECT
                    trip.TRIPID                 AS Id,
                    trip.TRIPNUMBER             AS Numbers,
                    trip.TRIPDATE               AS TripDate,
                    vehicle.VEHICLENUMBER       AS VehicleNumber,
                    startcity.CITYNAME          AS StartLocationName,
                    endcity.CITYNAME            AS EndLocationName,
                    trip.REFERENCENUMBER        AS ReferenceNumber,
                    ROW_NUMBER() OVER (ORDER BY trip.TRIPID DESC) AS RowNum
                FROM TTRIP trip, MVEHICLE vehicle, MCITY startcity, MCITY endcity, MPERIOD period
                WHERE trip.VEHICLEID = vehicle.VEHICLEID
                  AND trip.STARTLOCATIONID = startcity.CITYID
                  AND trip.ENDLOCATIONID = endcity.CITYID
                  AND trip.PERIODID = period.PERIODID
            )
            SELECT
                Id,
                Numbers,
                TripDate,
                VehicleNumber,
                StartLocationName,
                EndLocationName,
                ReferenceNumber
            FROM TripSelectList
            WHERE
                  (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);
            ";

        public const string GET_TRIP_SELECTLIST_PG = @"
            WITH ""TripSelectList"" AS (
                SELECT
                    trip.TRIPID                 AS ""Id"",
                    trip.TRIPNUMBER             AS ""Numbers"",
                    trip.TRIPDATE               AS ""TripDate"",
                    vehicle.VEHICLENUMBER       AS ""VehicleNumber"",
                    startcity.CITYNAME          AS ""StartLocationName"",
                    endcity.CITYNAME            AS ""EndLocationName"",
                    trip.REFERENCENUMBER        AS ""ReferenceNumber"",
                    ROW_NUMBER() OVER (ORDER BY trip.TRIPID DESC) AS ""RowNum""
                FROM TTRIP trip, MVEHICLE vehicle, MCITY startcity, MCITY endcity, MPERIOD period
                WHERE trip.VEHICLEID = vehicle.VEHICLEID
                  AND trip.STARTLOCATIONID = startcity.CITYID
                  AND trip.ENDLOCATIONID = endcity.CITYID
                  AND trip.PERIODID = period.PERIODID
            )
            SELECT
                ""Id"",
                ""Numbers"",
                ""TripDate"",
                ""VehicleNumber"",
                ""StartLocationName"",
                ""EndLocationName"",
                ""ReferenceNumber""
            FROM ""TripSelectList""
            WHERE
                  (:firstnumber = -1 AND :maxresult = -1)
               OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
            ";

        public const string GET_TRIP_SELECTLIST_ORACLE = @";";
        public const string GET_TRIP_SELECTLIST_MYSQL = @";";
    }
}