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

namespace LogisticsDAL.Query.Vehicle
{
    public static class VehicleQB
    {
        public const string GET_VEHICLE = @"SELECT 
                            V.VEHICLEID AS VehicleId,
                            V.VEHICLENUMBER AS VehicleNumber,
                            V.VEHICLETYPEID AS VehicleTypeId,
                            VT.GCMCODE AS VehicleTypeCode,
                            VT.GCMNAME AS VehicleTypeName,

                            V.DRIVERID AS DriverId,
                            D.DRIVERNAME AS DriverDriverName,

                            V.EMPTYWEIGHT AS VehicleEmptyWeight,
                            V.TONNAGE AS VehicleTonnage,
                            V.OWNERSHIPTYPE AS VehicleOwnerShipType,

                            CASE V.OWNERSHIPTYPE
                                WHEN 0 THEN 'Own'
                                WHEN 1 THEN 'Contract'
                                ELSE 'Transporter'
                            END AS VehicleOwnerShipTypeName,

                            V.TRANSPORTERID AS TransporterId,
                            P.PARTYCODE AS TransporterCode,
                            P.PARTYNAME AS TransporterName,

                            V.ASSETID AS AssetId,
                            A.ASSETCODE AS AssetCode,
                            A.ASSETNAME AS AssetName,

                            V.OUIDS AS VehicleOUIds,

                            V.YOM AS VehicleYOM,
                            V.ROADTAXVALIDITY AS VehicleRoadTaxValidity,
                            V.FCDATE AS VehicleFCDate,
                            V.FCEXPIRYDATE AS VehicleFCExpiryDate,

                            V.INSURANCEID AS InsuranceId,
                            I.POLICYNUMBER AS InsurancePolicyNumber,
                            I.ENDDATE AS InsuranceEndDate,

                            V.OWNERCONTACTID AS OwnerContactId,
                            C.NAME AS OwnerContactName,

                            V.MAKEID AS MakeId,
                            MK.GCMCODE AS MakeCode,
                            MK.GCMNAME AS MakeName,

                            V.FUELTYPEID AS FuelTypeId,
                            FT.ITEMCODE AS FuelTypeCode,
                            FT.ITEMNAME AS FuelTypeName,

                            V.CAPACITYRANGEID AS CapacityRangeId,
                            CR.GCMCODE AS CapacityRangeCode,
                            CR.GCMNAME AS CapacityRangeName,

                            V.MINIMUMTONNAGE AS VehicleMinimumTonnage,

                            V.CREATEDBYID AS VehicleCreatedById,
                            V.CREATEDON AS VehicleCreatedOn,
                            V.MODIFIEDBYID AS VehicleModifiedById,
                            V.MODIFIEDON AS VehicleModifiedOn,

                            V.SORTORDER AS VehicleSortOrder,
                            V.STATUS AS VehicleStatus,
                            V.VERSION AS VehicleVersion,
                            OU.ORGANIZATIONUNITNAME AS VehicleOUIdsName

                        FROM MVEHICLE V
                        LEFT JOIN MGCM VT ON VT.GCMID = V.VEHICLETYPEID
                        LEFT JOIN MDRIVER D ON D.DRIVERID = V.DRIVERID
                        LEFT JOIN MPARTY P ON P.PARTYID = V.TRANSPORTERID
                        LEFT JOIN MASSET A ON A.ASSETID = V.ASSETID
                        LEFT JOIN MINSURANCE I ON I.INSURANCEID = V.INSURANCEID
                        LEFT JOIN MCONTACT C ON C.CONTACTID = V.OWNERCONTACTID
                        LEFT JOIN MGCM MK ON MK.GCMID = V.MAKEID
                        LEFT JOIN MITEM FT ON FT.ITEMID = V.FUELTYPEID
                        LEFT JOIN MGCM CR ON CR.GCMID = V.CAPACITYRANGEID
                        LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = V.MAKEID
                        WHERE
                        V.VEHICLEID = @vehicleid;";

                        

        public const string SAVE_VEHICLE = @"INSERT INTO MVEHICLE
(
    VEHICLEID,
    VEHICLENUMBER,
    VEHICLETYPEID,
    DRIVERID,
    EMPTYWEIGHT,
    TONNAGE,
    OWNERSHIPTYPE,
    TRANSPORTERID,
    ASSETID,
    OUIDS,
    YOM,
    ROADTAXVALIDITY,
    FCDATE,
    FCEXPIRYDATE,
    INSURANCEID,
    OWNERCONTACTID,
    MAKEID,
    FUELTYPEID,
    CAPACITYRANGEID,
    MINIMUMTONNAGE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    STATUS,
    VERSION
)
VALUES
(
    @VehicleId,
    @VehicleNumber,
    @VehicleTypeId,
    @DriverId,
    @VehicleEmptyWeight,
    @VehicleTonnage,
    @VehicleOwnerShipType,
    @TransporterId,
    @AssetId,
    @VehicleOUIds,
    @VehicleYOM,
    @VehicleRoadTaxValidity,
    @VehicleFCDate,
    @VehicleFCExpiryDate,
    @InsuranceId,
    @OwnerContactId,
    @MakeId,
    @FuelTypeId,
    @CapacityRangeId,
    @VehicleMinimumTonnage,
    @VehicleCreatedById,
    @VehicleCreatedOn,
    @VehicleModifiedById,
    @VehicleModifiedOn,
    @VehicleSortOrder,
    @VehicleStatus,
    @VehicleVersion
);
";

        public const string UPDATE_VEHICLE = @"UPDATE MVEHICLE
SET
    VEHICLENUMBER = @VehicleNumber,
    VEHICLETYPEID = @VehicleTypeId,
    DRIVERID = @DriverId,
    EMPTYWEIGHT = @VehicleEmptyWeight,
    TONNAGE = @VehicleTonnage,
    OWNERSHIPTYPE = @VehicleOwnerShipType,
    TRANSPORTERID = @TransporterId,
    ASSETID = @AssetId,
    OUIDS = @VehicleOUIds,
    YOM = @VehicleYOM,
    ROADTAXVALIDITY = @VehicleRoadTaxValidity,
    FCDATE = @VehicleFCDate,
    FCEXPIRYDATE = @VehicleFCExpiryDate,
    INSURANCEID = @InsuranceId,
    OWNERCONTACTID = @OwnerContactId,
    MAKEID = @MakeId,
    FUELTYPEID = @FuelTypeId,
    CAPACITYRANGEID = @CapacityRangeId,
    MINIMUMTONNAGE = @VehicleMinimumTonnage,
    MODIFIEDBYID = @VehicleModifiedById,
    MODIFIEDON = @VehicleModifiedOn,
    SORTORDER = @VehicleSortOrder,
    STATUS = @VehicleStatus,
    VERSION = @VehicleVersion
WHERE VEHICLEID = @VehicleId;
";


        public const string DELETE_VEHICLE = @"DELETE FROM MVEHICLE
WHERE VEHICLEID = @VehicleId;
";

        public const string GET_VEHICLE_NUMBERS =
@"SELECT 
    a.VEHICLEID AS Id,
    a.VEHICLENUMBER AS Numbers,
    a.TRANSPORTERID AS TransporterId,
    b.PartyName AS TransporterName
FROM MVEHICLE a
INNER JOIN MPARTY b ON a.TRANSPORTERID = b.PartyId
WHERE (CONVERT(VARCHAR, OUIDS) LIKE '%' + @loginouid + '%' OR CONVERT(VARCHAR, OUIDS) = '')";

        public const string GET_VEHICLE_NUMBERS_ORACLE =
@"SELECT 
    a.VEHICLEID AS Id,
    a.VEHICLENUMBER AS Numbers
FROM MVEHICLE a
WHERE (OUIDS LIKE '%' || :loginouid || '%' OR OUIDS IS NULL OR OUIDS = '')";

        public const string GET_ALL_VEHICLE_NUMBERS =
@"SELECT 
    a.VEHICLEID AS Id,
    a.VEHICLENUMBER AS Numbers,
    a.TRANSPORTERID AS TransporterId,
    b.PartyName AS TransporterName
FROM MVEHICLE a
INNER JOIN MPARTY b ON a.TRANSPORTERID = b.PartyId
WHERE 1=1";

        public const string GET_VEHILE_DETAILS =
@"SELECT 
    a.VEHICLETYPEID AS VehicleTypeId,
    b.GCMCODE AS VehicleTypeCode,
    b.GCMNAME AS VehicleTypeName,
    a.DRIVERID AS DriverId,
    c.DRIVERNAME AS DriverName,
    a.TRANSPORTERID AS TransporterId,
    d.PARTYCODE AS TransportCode,
    d.PARTYNAME AS TransportName,
    a.OWNERSHIPTYPE AS Source
FROM MVEHICLE a
INNER JOIN MGCM b ON a.VEHICLETYPEID = b.GCMID
INNER JOIN MDRIVER c ON a.DRIVERID = c.DRIVERID
INNER JOIN MPARTY d ON a.TRANSPORTERID = d.PARTYID
WHERE a.VEHICLEID = @vehicleid";
    }
}