-- name: CreatePlannedConsignment :one
INSERT INTO plannedconsignments (
    id,
    start_date,
    end_date,
    user_id,
    exportcompany_id,
    status
 ) VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6
)
RETURNING *;

-- name: GetPlannedConsignment :one
SELECT plannedconsignments.start_date,packhouses.phname,
exportcompanies.expregno,exportcompanies.exprexno,
exithandlers.exhdescription,exitpoints.expdescription,
countries.countryname,modesoftransport.motdescription,airlines.airlinename,
plannedconsignments_tpdetails.country_id,plannedconsignments_tpdetails.modesoftransport_id,
plannedconsignments_tpdetails.exit_date,plannedconsignments_tpdetails.exitpoint_id,
plannedconsignments_tpdetails.airline_id,plannedconsignments_tpdetails.exithandler_id,
plannedconsignments.end_date,plannedconsignments.user_id,plannedconsignments.exportcompany_id,
plannedconsignments.status,orders.id order_id,exportcompanies.expname,plannedconsignments.created_at FROM plannedconsignments
LEFT JOIN  plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN  orders             on orders.consignment_no         = plannedconsignments.id
LEFT JOIN  exitpoints         on exitpoints.id                 = plannedconsignments_tpdetails.exitpoint_id
LEFT JOIN  airlines           on airlines.id                   = plannedconsignments_tpdetails.airline_id
LEFT JOIN  exithandlers       on exithandlers.id               = plannedconsignments_tpdetails.exithandler_id
LEFT JOIN  countries          on countries.id                  = plannedconsignments_tpdetails.country_id
LEFT JOIN  modesoftransport   on modesoftransport.id                    = plannedconsignments_tpdetails.modesoftransport_id
LEFT JOIN  exportcompanies    on exportcompanies.id                     = plannedconsignments.exportcompany_id
LEFT JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id    = plannedconsignments.exportcompany_id
LEFT JOIN  packhouses         on packhouses.id    = packhouse_exporter.packhouse_id
WHERE plannedconsignments.id        = $1
LIMIT 1;

-- name: ListPlannedConsigments :many
SELECT plannedconsignments.start_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname FROM plannedconsignments
JOIN  exportcompanies    on exportcompanies.id                  = plannedconsignments.exportcompany_id
JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id = plannedconsignments.exportcompany_id
JOIN  packhouses         on packhouses.id                       = packhouse_exporter.packhouse_id
WHERE plannedconsignments.exportcompany_id              = $1
AND   plannedconsignments.status  = $2
ORDER BY plannedconsignments.created_at DESC
LIMIT $3 OFFSET $4;


-- name: ListAcceptedPlannedConsigments :many
SELECT DISTINCT plannedconsignments.id consignment_no,plannedconsignments.start_date,plannedconsignments.end_date,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id = packhouse_exporter.packhouse_id
WHERE plannedconsignments.exportcompany_id = $1
AND   plannedconsignments.status = $2
ORDER BY plannedconsignments.created_at DESC
LIMIT $3 OFFSET $4;

-- name: ListAcceptedApprovalConsigments :many
SELECT DISTINCT plannedconsignments.id consignment_no,plannedconsignments.start_date,plannedconsignments.end_date,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id = packhouse_exporter.packhouse_id
WHERE plannedconsignments.exportcompany_id = $1
AND   plannedconsignments.status in ('Accepted','AwaitingUpdate')
ORDER BY plannedconsignments.created_at DESC
LIMIT $2 OFFSET $3;

-- name: ListAwaitingBorderInspectionConsigments :many
SELECT DISTINCT plannedconsignments.id consignment_no,plannedconsignments.start_date,plannedconsignments.end_date,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id = packhouse_exporter.packhouse_id
WHERE plannedconsignments.exportcompany_id = $1
AND   plannedconsignments.status = 'AwaitingBorderInspection'
ORDER BY plannedconsignments.created_at DESC
LIMIT $2 OFFSET $3;

-- name: ListQueriedPlannedConsigments :many
SELECT plannedconsignments.start_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id = packhouse_exporter.packhouse_id
WHERE plannedconsignments.exportcompany_id = $1
AND plannedconsignments.status = $2
ORDER BY plannedconsignments.created_at DESC;

-- name: ListRejectedPlannedConsigments :many
SELECT plannedconsignments.start_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id = packhouse_exporter.packhouse_id
WHERE plannedconsignments.exportcompany_id = $1
AND plannedconsignments.status = $2
ORDER BY plannedconsignments.created_at DESC;

-- name: ListCompletedPlannedConsigments :many
SELECT plannedconsignments.start_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id = packhouse_exporter.packhouse_id
WHERE plannedconsignments.exportcompany_id = $1
AND plannedconsignments.status = $2
ORDER BY plannedconsignments.created_at DESC;

-- name: ListAllPlannedConsigments :many
SELECT plannedconsignments.start_date,plannedconsignments.end_date, plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,
packhouses.phname,orders.status order_status,clients.companyname,countries.countryname,orders.created_at order_created_at,orders.id FROM plannedconsignments
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id = packhouse_exporter.packhouse_id
LEFT JOIN orders     on orders.consignment_no = plannedconsignments.id
LEFT JOIN clients    on clients.id       = orders.client_id
LEFT JOIN countries  on countries.id     = clients.country_id
WHERE    plannedconsignments.exportcompany_id = $4
AND      plannedconsignments.status = $3
ORDER BY plannedconsignments.created_at DESC
LIMIT $1 OFFSET $2;


-- name: UpdatePlannedConsigment :one
UPDATE plannedconsignments
  set
    start_date       = $2,
    end_date         = $3,
    user_id          = $4,
    exportcompany_id = $5
WHERE id = $1
RETURNING *;

-- name: UpdatePlannedConsigmentStatus :one
UPDATE plannedconsignments
  set
    status = $2
WHERE id = $1
RETURNING *;


-- name: DeletePlannedConsigment :exec
DELETE FROM plannedconsignments WHERE id= $1;


-- name: CreatePlannedOtherDocs :one
INSERT INTO plannedconsignments_otherdocuments (
    plannedconsignments_id,
    name,
    description
 ) VALUES (
    $1,
    $2,
    $3
)
RETURNING *;

-- name: SubmittedPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM plannedconsignments
WHERE plannedconsignments.status= 'Submitted'
AND exportcompany_id = $1;

-- name: AcceptedPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM  plannedconsignments
WHERE plannedconsignments.status in ('Accepted','AwaitingUpdate')
AND   plannedconsignments.exportcompany_id = $1;

-- name: CompletedPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM plannedconsignments
WHERE plannedconsignments.status= 'Completed'
AND exportcompany_id = $1;

-- name: AwaitingBorderPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM plannedconsignments
WHERE plannedconsignments.status= 'AwaitingBorderInspection'
AND exportcompany_id = $1;

-- name: QueriedPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM plannedconsignments
WHERE plannedconsignments.status= 'Queried'
AND exportcompany_id = $1;

-- name: RejectedPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM  plannedconsignments
WHERE plannedconsignments.status= 'Intercepted'
AND exportcompany_id = $1;

-- name: PendingPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM  plannedconsignments
WHERE plannedconsignments.status= 'Pending'
AND exportcompany_id = $1;

-- name: ResortingPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM plannedconsignments
WHERE plannedconsignments.status= 'Resort'
AND exportcompany_id = $1;

-- name: ReDocumentPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM plannedconsignments
WHERE plannedconsignments.status= 'ReDocument'
AND exportcompany_id = $1;


-- name: DestroyedPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM plannedconsignments
WHERE plannedconsignments.status= 'Destroyed'
AND exportcompany_id = $1;


-- name: StoppedPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM plannedconsignments
WHERE plannedconsignments.status IN ('Rejected', 'Retreatment', 'Destroyed');

-- name: AwaitingPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM  plannedconsignments
WHERE plannedconsignments.status
IN ('AwaitingUpdateDestination','AwaitingBorderInspection', 'AwaitingPackinglistUpdate','FitForExport','AwaitingReDocumentation');


-- name: ListAwaitedBoarderPlannedConsigments :many
SELECT plannedconsignments.start_date,plannedconsignments_tpdetails.exit_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id  = packhouse_exporter.packhouse_id
WHERE plannedconsignments.status IN ('AwaitingUpdateDestination','AwaitingBorderInspection', 'AwaitingPackinglistUpdate','FitForExport','AwaitingReDocumentation')
ORDER BY plannedconsignments.created_at DESC;


-- name: ListAwaitedBoarderPlannedConsigmentsPaginated :many
SELECT DISTINCT ON (plannedconsignments.id)
    plannedconsignments.id AS consignment_no,
    plannedconsignments.start_date,
    plannedconsignments_tpdetails.exit_date,
    plannedconsignments.end_date,
    plannedconsignments.created_at,
    plannedconsignments.status,
    exportcompanies.expname,
    packhouses.phname
FROM plannedconsignments
LEFT JOIN plannedconsignments_tpdetails
    ON plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN exportcompanies
    ON exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter
    ON packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses
    ON packhouses.id = packhouse_exporter.packhouse_id
WHERE plannedconsignments.status IN (
    'AwaitingUpdateDestination',
    'AwaitingBorderInspection',
    'AwaitingPackinglistUpdate',
    'FitForExport',
    'AwaitingReDocumentation'
)
ORDER BY plannedconsignments.id, plannedconsignments.created_at DESC
LIMIT $1 OFFSET $2;

-- name: ListAllBoarderPlannedConsigmentsByStatus :many
SELECT plannedconsignments.start_date,plannedconsignments_tpdetails.exit_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id  = packhouse_exporter.packhouse_id
WHERE plannedconsignments.status = $3
ORDER BY plannedconsignments.created_at
DESC LIMIT $1 OFFSET $2;

-- name: ListStoppedBoarderPlannedConsigments :many
SELECT plannedconsignments.start_date,plannedconsignments_tpdetails.exit_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id  = packhouse_exporter.packhouse_id
WHERE plannedconsignments.status IN ('Rejected', 'Retreatment', 'Destroyed')
ORDER BY plannedconsignments.created_at DESC;

-- name: ListStoppedBoarderPlannedConsigmentsPaginated :many
SELECT plannedconsignments.start_date,plannedconsignments_tpdetails.exit_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id  = packhouse_exporter.packhouse_id
WHERE
    plannedconsignments.status IN ('Rejected', 'Retreatment', 'Destroyed')
ORDER BY
    plannedconsignments.created_at DESC
LIMIT
    $1 OFFSET $2;


-- name: ListCompletedBoarderPlannedConsigments :many
SELECT plannedconsignments.start_date,plannedconsignments_tpdetails.exit_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id  = packhouse_exporter.packhouse_id
WHERE plannedconsignments.status = 'Completed'
ORDER BY plannedconsignments.created_at DESC;


-- name: ListCompletedBoarderPlannedConsigmentsPaginated :many
SELECT plannedconsignments.start_date,plannedconsignments_tpdetails.exit_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
LEFT JOIN plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses on packhouses.id  = packhouse_exporter.packhouse_id
WHERE
    plannedconsignments.status IN ('Completed')
ORDER BY
    plannedconsignments.created_at DESC
LIMIT
    $1 OFFSET $2;



-- name: CompletedBoarderPlannedConsignmentsSum :one
SELECT COUNT(id)
FROM plannedconsignments
WHERE plannedconsignments.status = 'Completed';


-- name: ConsignmentRegimes :many
SELECT DISTINCT plannedconsignments.id, orders.consignment_no FROM plannedconsignments
LEFT JOIN orders                 on orders.consignment_no        = plannedconsignments.id
LEFT JOIN order_crops            on order_crops.order_id         = orders.id
LEFT JOIN crops                  on crops.id                     = order_crops.crop_id
LEFT JOIN receptionform          on receptionform.id             = orders.id
LEFT JOIN qcp1                   on qcp1.receptionform_id        = receptionform.id
LEFT JOIN qcp1_attributes        on qcp1_attributes.qcp1_id      = qcp1.id
LEFT JOIN qcp1_recommendation    on qcp1_recommendation.qcp1_id  = qcp1.id
LEFT JOIN qcp2                   on qcp2.reception_id            =  receptionform.id
LEFT JOIN qcp2_attributes        on qcp2_attributes.qcp2_id      =  qcp2.id
LEFT JOIN qcp2_recommendation    on qcp2_recommendation.qcp2_id  =  qcp2.id
LEFT JOIN qcp3                   on qcp3.reception_id            =  receptionform.id
LEFT JOIN qcp3_attributes        on qcp3_attributes.qcp3_id      =  qcp3.id
LEFT JOIN qcp3_recommendation    on qcp3_recommendation.qcp3_id  =  qcp3.id
LEFT JOIN packinglist            on packinglist.receptionform_id =  receptionform.id
LEFT JOIN qcp4                   on qcp4.packinglist_id          =  packinglist.id
LEFT JOIN qcp4_attributes        on qcp4_attributes.qcp4_id      =  qcp4.id
LEFT JOIN qcp4_recommendations   on qcp4_recommendations.qcp4_id  =  qcp4.id
LEFT JOIN qcp5                   on qcp5.packinglist_id          =  packinglist.id
LEFT JOIN qcp5_attributes        on qcp5_attributes.qcp5_id      =  qcp5.id
LEFT JOIN qcp5_recommendations   on qcp5_recommendations.qcp5_id  =  qcp5.id;



-- name: GetCheckAwaitingStatus :one
SELECT EXISTS (
  SELECT 1 FROM plannedconsignments
  LEFT JOIN orders on orders.consignment_no = plannedconsignments.id
  WHERE plannedconsignments.status = 'AwaitingUpdate' AND orders.id = $1
);

-- name: GetPl :one
Select * FROM plannedconsignments
LEFT JOIN orders on orders.consignment_no = plannedconsignments.id
WHERE status = 'AwaitingUpdate' AND orders.id = $1;

-- name: GetPlExitPoint :many
SELECT plannedconsignments.start_date,plannedconsignments.end_date,plannedconsignments.id consignment_no,
plannedconsignments.created_at,plannedconsignments.status,exportcompanies.expname,packhouses.phname FROM plannedconsignments
JOIN exportcompanies on exportcompanies.id = plannedconsignments.exportcompany_id
JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
JOIN packhouses on packhouses.id = packhouse_exporter.packhouse_id
JOIN plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
WHERE plannedconsignments_tpdetails.exitpoint_id = $1
AND plannedconsignments.status = 'AwaitingBorderInspection';



-- name: GetPackinglistsUnderConsignment :many
SELECT DISTINCT packinglist.id FROM packinglist
JOIN  orders        on orders.id        = packinglist.order_id
WHERE orders.consignment_no = $1;

-- name: ConsignmentReport :many
SELECT plannedconsignments.start_date,packhouses.phname,
exportcompanies.expregno,exportcompanies.exprexno,
exithandlers.exhdescription,exitpoints.expdescription,
countries.countryname,modesoftransport.motdescription,airlines.airlinename,
plannedconsignments_tpdetails.country_id,plannedconsignments_tpdetails.modesoftransport_id,
plannedconsignments_tpdetails.exit_date,plannedconsignments_tpdetails.exitpoint_id,
plannedconsignments_tpdetails.airline_id,plannedconsignments_tpdetails.exithandler_id,
plannedconsignments.end_date,plannedconsignments.user_id,plannedconsignments.exportcompany_id,
plannedconsignments.status,orders.id order_id,exportcompanies.expname,exportcompanies.logo FROM plannedconsignments
LEFT JOIN  plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN  orders             on orders.consignment_no         = plannedconsignments.id
LEFT JOIN  exitpoints         on exitpoints.id                 = plannedconsignments_tpdetails.exitpoint_id
LEFT JOIN  airlines           on airlines.id                   = plannedconsignments_tpdetails.airline_id
LEFT JOIN  exithandlers       on exithandlers.id               = plannedconsignments_tpdetails.exithandler_id
LEFT JOIN  countries          on countries.id                  = plannedconsignments_tpdetails.country_id
LEFT JOIN  modesoftransport   on modesoftransport.id                    = plannedconsignments_tpdetails.modesoftransport_id
LEFT JOIN  exportcompanies    on exportcompanies.id                     = plannedconsignments.exportcompany_id
LEFT JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id    = plannedconsignments.exportcompany_id
LEFT JOIN  packhouses         on packhouses.id    = packhouse_exporter.packhouse_id
WHERE      plannedconsignments.id  = $1;


-- name: ListAllCompaniesConsignmentReports :many
SELECT plannedconsignments.start_date,packhouses.phname,
exportcompanies.expregno,exportcompanies.exprexno,
plannedconsignments.id as consignment_no,
exithandlers.exhdescription,exitpoints.expdescription,
countries.countryname,modesoftransport.motdescription,airlines.airlinename,
plannedconsignments_tpdetails.country_id,plannedconsignments_tpdetails.modesoftransport_id,
plannedconsignments_tpdetails.exit_date,plannedconsignments_tpdetails.exitpoint_id,
plannedconsignments_tpdetails.airline_id,plannedconsignments_tpdetails.exithandler_id,
plannedconsignments.end_date,plannedconsignments.user_id,plannedconsignments.exportcompany_id,
plannedconsignments.status,plannedconsignments.created_at,orders.id order_id,exportcompanies.expname,exportcompanies.logo FROM plannedconsignments
LEFT JOIN  plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN  orders             on orders.consignment_no         = plannedconsignments.id
LEFT JOIN  exitpoints         on exitpoints.id                 = plannedconsignments_tpdetails.exitpoint_id
LEFT JOIN  airlines           on airlines.id                   = plannedconsignments_tpdetails.airline_id
LEFT JOIN  exithandlers       on exithandlers.id               = plannedconsignments_tpdetails.exithandler_id
LEFT JOIN  countries          on countries.id                  = plannedconsignments_tpdetails.country_id
LEFT JOIN  modesoftransport   on modesoftransport.id                    = plannedconsignments_tpdetails.modesoftransport_id
LEFT JOIN  exportcompanies    on exportcompanies.id                     = plannedconsignments.exportcompany_id
LEFT JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id    = plannedconsignments.exportcompany_id
LEFT JOIN  packhouses         on packhouses.id    = packhouse_exporter.packhouse_id
WHERE plannedconsignments.status =  ANY($1::text[])
AND   plannedconsignments.created_at BETWEEN TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
AND   TO_TIMESTAMP($3, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY plannedconsignments.created_at DESC LIMIT $4 OFFSET $5;




-- name: ListAllCompaniesSingleConsignmentReports :many
SELECT plannedconsignments.start_date,packhouses.phname,
exportcompanies.expregno,exportcompanies.exprexno,
plannedconsignments.id as consignment_no,
exithandlers.exhdescription,exitpoints.expdescription,
countries.countryname,modesoftransport.motdescription,airlines.airlinename,
plannedconsignments_tpdetails.country_id,plannedconsignments_tpdetails.modesoftransport_id,
plannedconsignments_tpdetails.exit_date,plannedconsignments_tpdetails.exitpoint_id,
plannedconsignments_tpdetails.airline_id,plannedconsignments_tpdetails.exithandler_id,
plannedconsignments.end_date,plannedconsignments.user_id,plannedconsignments.exportcompany_id,
plannedconsignments.status,plannedconsignments.created_at,orders.id order_id,exportcompanies.expname,exportcompanies.logo FROM plannedconsignments
LEFT JOIN  plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN  orders             on orders.consignment_no         = plannedconsignments.id
LEFT JOIN  exitpoints         on exitpoints.id                 = plannedconsignments_tpdetails.exitpoint_id
LEFT JOIN  airlines           on airlines.id                   = plannedconsignments_tpdetails.airline_id
LEFT JOIN  exithandlers       on exithandlers.id               = plannedconsignments_tpdetails.exithandler_id
LEFT JOIN  countries          on countries.id                  = plannedconsignments_tpdetails.country_id
LEFT JOIN  modesoftransport   on modesoftransport.id                    = plannedconsignments_tpdetails.modesoftransport_id
LEFT JOIN  exportcompanies    on exportcompanies.id                     = plannedconsignments.exportcompany_id
LEFT JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id    = plannedconsignments.exportcompany_id
LEFT JOIN  packhouses         on packhouses.id    = packhouse_exporter.packhouse_id
WHERE plannedconsignments.status =  $1
ORDER BY plannedconsignments.created_at DESC LIMIT $2 OFFSET $3;




-- name: ListAllCompaniesMultipleConsignmentReports :many
SELECT plannedconsignments.start_date,packhouses.phname,
exportcompanies.expregno,exportcompanies.exprexno,
plannedconsignments.id as consignment_no,
exithandlers.exhdescription,exitpoints.expdescription,
countries.countryname,modesoftransport.motdescription,airlines.airlinename,
plannedconsignments_tpdetails.country_id,plannedconsignments_tpdetails.modesoftransport_id,
plannedconsignments_tpdetails.exit_date,plannedconsignments_tpdetails.exitpoint_id,
plannedconsignments_tpdetails.airline_id,plannedconsignments_tpdetails.exithandler_id,
plannedconsignments.end_date,plannedconsignments.user_id,plannedconsignments.exportcompany_id,
plannedconsignments.status,plannedconsignments.created_at,orders.id order_id,exportcompanies.expname,exportcompanies.logo FROM plannedconsignments
LEFT JOIN  plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN  orders             on orders.consignment_no         = plannedconsignments.id
LEFT JOIN  exitpoints         on exitpoints.id                 = plannedconsignments_tpdetails.exitpoint_id
LEFT JOIN  airlines           on airlines.id                   = plannedconsignments_tpdetails.airline_id
LEFT JOIN  exithandlers       on exithandlers.id               = plannedconsignments_tpdetails.exithandler_id
LEFT JOIN  countries          on countries.id                  = plannedconsignments_tpdetails.country_id
LEFT JOIN  modesoftransport   on modesoftransport.id                    = plannedconsignments_tpdetails.modesoftransport_id
LEFT JOIN  exportcompanies    on exportcompanies.id                     = plannedconsignments.exportcompany_id
LEFT JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id    = plannedconsignments.exportcompany_id
LEFT JOIN  packhouses         on packhouses.id    = packhouse_exporter.packhouse_id
WHERE plannedconsignments.status =  ANY($1::text[])
ORDER BY plannedconsignments.created_at DESC LIMIT $2 OFFSET $3;




-- name: ListAllCompaniesGlobalConsignmentReports :many
SELECT plannedconsignments.start_date,packhouses.phname,
exportcompanies.expregno,exportcompanies.exprexno,
plannedconsignments.id as consignment_no,
exithandlers.exhdescription,exitpoints.expdescription,
countries.countryname,modesoftransport.motdescription,airlines.airlinename,
plannedconsignments_tpdetails.country_id,plannedconsignments_tpdetails.modesoftransport_id,
plannedconsignments_tpdetails.exit_date,plannedconsignments_tpdetails.exitpoint_id,
plannedconsignments_tpdetails.airline_id,plannedconsignments_tpdetails.exithandler_id,
plannedconsignments.end_date,plannedconsignments.user_id,plannedconsignments.exportcompany_id,
plannedconsignments.status,plannedconsignments.created_at,orders.id order_id,exportcompanies.expname,exportcompanies.logo FROM plannedconsignments
LEFT JOIN  plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN  orders             on orders.consignment_no         = plannedconsignments.id
LEFT JOIN  exitpoints         on exitpoints.id                 = plannedconsignments_tpdetails.exitpoint_id
LEFT JOIN  airlines           on airlines.id                   = plannedconsignments_tpdetails.airline_id
LEFT JOIN  exithandlers       on exithandlers.id               = plannedconsignments_tpdetails.exithandler_id
LEFT JOIN  countries          on countries.id                  = plannedconsignments_tpdetails.country_id
LEFT JOIN  modesoftransport   on modesoftransport.id                    = plannedconsignments_tpdetails.modesoftransport_id
LEFT JOIN  exportcompanies    on exportcompanies.id                     = plannedconsignments.exportcompany_id
LEFT JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id    = plannedconsignments.exportcompany_id
LEFT JOIN  packhouses         on packhouses.id    = packhouse_exporter.packhouse_id
ORDER BY plannedconsignments.created_at DESC LIMIT $1 OFFSET $2;


-- name: ListAllCompaniesDateFiltersConsignmentReports :many
SELECT plannedconsignments.start_date,packhouses.phname,
exportcompanies.expregno,exportcompanies.exprexno,
plannedconsignments.id as consignment_no,
exithandlers.exhdescription,exitpoints.expdescription,
countries.countryname,modesoftransport.motdescription,airlines.airlinename,
plannedconsignments_tpdetails.country_id,plannedconsignments_tpdetails.modesoftransport_id,
plannedconsignments_tpdetails.exit_date,plannedconsignments_tpdetails.exitpoint_id,
plannedconsignments_tpdetails.airline_id,plannedconsignments_tpdetails.exithandler_id,
plannedconsignments.end_date,plannedconsignments.user_id,plannedconsignments.exportcompany_id,
plannedconsignments.status,plannedconsignments.created_at,orders.id order_id,exportcompanies.expname,exportcompanies.logo FROM plannedconsignments
LEFT JOIN  plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN  orders             on orders.consignment_no         = plannedconsignments.id
LEFT JOIN  exitpoints         on exitpoints.id                 = plannedconsignments_tpdetails.exitpoint_id
LEFT JOIN  airlines           on airlines.id                   = plannedconsignments_tpdetails.airline_id
LEFT JOIN  exithandlers       on exithandlers.id               = plannedconsignments_tpdetails.exithandler_id
LEFT JOIN  countries          on countries.id                  = plannedconsignments_tpdetails.country_id
LEFT JOIN  modesoftransport   on modesoftransport.id                    = plannedconsignments_tpdetails.modesoftransport_id
LEFT JOIN  exportcompanies    on exportcompanies.id                     = plannedconsignments.exportcompany_id
LEFT JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id    = plannedconsignments.exportcompany_id
LEFT JOIN  packhouses         on packhouses.id    = packhouse_exporter.packhouse_id
WHERE plannedconsignments.created_at BETWEEN TO_TIMESTAMP($1, 'YYYY-MM-DD HH24:MI:SS')
AND   TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY plannedconsignments.created_at DESC LIMIT $3 OFFSET $4;





-- name: ListAllCompaniesAllFiltersConsignmentReports :many
SELECT plannedconsignments.start_date,packhouses.phname,
exportcompanies.expregno,exportcompanies.exprexno,
plannedconsignments.id as consignment_no,
exithandlers.exhdescription,exitpoints.expdescription,
countries.countryname,modesoftransport.motdescription,airlines.airlinename,
plannedconsignments_tpdetails.country_id,plannedconsignments_tpdetails.modesoftransport_id,
plannedconsignments_tpdetails.exit_date,plannedconsignments_tpdetails.exitpoint_id,
plannedconsignments_tpdetails.airline_id,plannedconsignments_tpdetails.exithandler_id,
plannedconsignments.end_date,plannedconsignments.user_id,plannedconsignments.exportcompany_id,
plannedconsignments.status,plannedconsignments.created_at,orders.id order_id,exportcompanies.expname,exportcompanies.logo FROM plannedconsignments
LEFT JOIN  plannedconsignments_tpdetails on plannedconsignments_tpdetails.plannedconsignments_id = plannedconsignments.id
LEFT JOIN  orders             on orders.consignment_no         = plannedconsignments.id
LEFT JOIN  exitpoints         on exitpoints.id                 = plannedconsignments_tpdetails.exitpoint_id
LEFT JOIN  airlines           on airlines.id                   = plannedconsignments_tpdetails.airline_id
LEFT JOIN  exithandlers       on exithandlers.id               = plannedconsignments_tpdetails.exithandler_id
LEFT JOIN  countries          on countries.id                  = plannedconsignments_tpdetails.country_id
LEFT JOIN  modesoftransport   on modesoftransport.id                    = plannedconsignments_tpdetails.modesoftransport_id
LEFT JOIN  exportcompanies    on exportcompanies.id                     = plannedconsignments.exportcompany_id
LEFT JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id    = plannedconsignments.exportcompany_id
LEFT JOIN  packhouses         on packhouses.id    = packhouse_exporter.packhouse_id
WHERE plannedconsignments.status = ANY($1::text[])
AND   plannedconsignments.created_at BETWEEN TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
AND   TO_TIMESTAMP($3, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY plannedconsignments.created_at DESC LIMIT $4 OFFSET $5;


-- name: AllReceptionConsignmentDocuments :many
SELECT reception_documents.document_id,reception_documents.document,
documents.name,documents.description from reception_documents
JOIN  receptionform          on receptionform.id       = reception_documents.receptionform_id
JOIN  orders                 on orders.id              = receptionform.order_id
JOIN  plannedconsignments    on plannedconsignments.id = orders.consignment_no
JOIN  documents              on documents.id           = reception_documents.document_id
WHERE plannedconsignments.id = $1;


-- name: AllPlannedConsignmentDocuments :many
SELECT plannedconsignments_documents.document_id,
plannedconsignments_documents.document as doc,
documents.name,documents.description
from   plannedconsignments_documents
JOIN   documents on documents.id       = plannedconsignments_documents.document_id
WHERE  plannedconsignments_documents.plc_id = $1;
