-- name: CreateQcp4 :one
INSERT INTO qcp4 (
    packinglist_id,
    status,
    user_id,
    exportcompany_id
 ) VALUES (
     $1,
     $2,
     $3,
     $4
    )
RETURNING *;

-- name: GetPackhouseInspectorID :one
SELECT user_id FROM qcp4_recommendations
WHERE qcp4_id = $1 LIMIT 1;


-- name: CreateQcp4Others :one
INSERT INTO qcp4_others (
    qcp4_id,
    packinglist_id,
    product_name,
    noofboxes,
    sampledboxes,
    boxweight,
    productnetweight,
    totalproductnetweight,
    totalproductgrossweight,
    status,
    user_id,
    exportcompany_id
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6,
     $7,
     $8,
     $9,
     $10,
     $11,
     $12
    )
RETURNING *;

-- name: CreateQcp4Attributes :one
INSERT INTO qcp4_attributes (
    qcp4_id,
    qualityattributes_id,
    qcp4_product_id,
    user_id,
    exportcompany_id
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5
    )
RETURNING *;

-- name: CreateQcp4Recommendations :one
INSERT INTO qcp4_recommendations (
    qcp4_id,
    qcp4_product_id,
    status,
    reason,
    weight_rejected,
    weight_passed,
    user_id,
    exportcompany_id
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6,
     $7,
     $8
    )
RETURNING *;

-- name: GetQcp4Others :one
SELECT * FROM qcp4_others
WHERE id = $1 LIMIT 1;

-- name: GetQcp4 :one
SELECT * FROM qcp4
WHERE id = $1 LIMIT 1;


-- name: GetQcp4DetailsRes :one
SELECT qcp4.packinglist_id,qcp4.user_id,qcp4.status,qcp4.created_at, orders.consignment_no,qcp4.exportcompany_id FROM qcp4
JOIN   packinglist on packinglist.id = qcp4.packinglist_id
JOIN   orders      on orders.id      = packinglist.order_id
WHERE qcp4.id = $1 LIMIT 1;

-- name: GetPackingListIDFromQcp4 :one
SELECT id,user_id,exportcompany_id FROM qcp4
WHERE  packinglist_id= $1 LIMIT 1;

-- name: ListQcp4s :many
SELECT * FROM qcp4
ORDER BY id LIMIT $1
OFFSET $2;

 -- name: CheckPackListExistanceInQcp4 :one
SELECT packinglist_id FROM qcp4
WHERE packinglist_id = $1;

 -- name: CheckPackListExistanceInQcp4Others :one
SELECT packinglist_id FROM qcp4_others
WHERE packinglist_id = $1;

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

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


-- name: UpdateQcp4Others :one
UPDATE qcp4_others
  set
    qcp4_id = $2,
    product_name = $3,
    noofboxes = $4
WHERE id = $1
RETURNING *;

-- name: UpdateQcp4OthersSampled :one
UPDATE qcp4_others
  set
    sampledboxes = $2
WHERE id = $1
RETURNING *;

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


-- name: ListPendingQcp4s :many
SELECT crops.cropname,receptionform.order_id,
qcp4.user_id,users.fullname,qcp4.created_at,qcp4.status,
orders.consignment_no,qcp4.id qcp4_id,receptionform.traceability_code from qcp4
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders  on orders.id  = receptionform.order_id
LEFT JOIN crops   on crops.id   = receptionform.crop_id
LEFT JOIN users   on users.id   = qcp4.user_id
WHERE qcp4.exportcompany_id     = $3  AND qcp4.status = $4
LIMIT $1 OFFSET $2;


-- name: CreateQcp4Products :one
INSERT INTO qcp4_products (
    qcp4_id,
    traceability_code,
    product_id,
	totalboxes,
    boxweight,
    productnetweight,
    totalproductnetweight,
    totalproductgrossweight,
    sampledboxes,
    status
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6,
     $7,
     $8,
     $9,
     $10
    )
RETURNING *;

-- name: UpdateQcp4Sampled :one
UPDATE qcp4_products
  set
    sampledboxes = $2
WHERE id = $1
RETURNING *;

-- name: Qcp4listPerConsignment :many
SELECT orders.id qcp4orderid,orders.id as qcp4_order_ids,qcp4.id qcp4ID,qcp4_products.traceability_code traceability_code,
qcp4_products.id row_id,qcp4_products.product_id product_id,qcp4_products.totalboxes total_boxes,crops.cropname qcp4_product_name,
qcp4_products.status qcp4_product_status,qcp4_products.boxweight qcp4_box_weight,
qcp4_products.productnetweight qcp4_productnet_weight,qcp4_products.totalproductnetweight total_product_netweight,
qcp4_products.totalproductgrossweight total_product_grossweight,qcp4_products.sampledboxes sampled_boxes, qcp4.packinglist_id from qcp4
LEFT JOIN qcp4_products on qcp4_products.qcp4_id = qcp4.id
LEFT JOIN packinglist   on packinglist.id        = qcp4.packinglist_id
LEFT JOIN orders        on orders.id             = packinglist.order_id
LEFT JOIN crops         on crops.id              = qcp4_products.product_id
LEFT JOIN users         on users.id              = qcp4.user_id
WHERE qcp4.exportcompany_id     = $3
AND   qcp4.status = $2
AND   orders.consignment_no = $1
GROUP BY qcp4.id,qcp4_products.id,crops.cropname,orders.id;


-- name: Qcp4PerConsignment :many
SELECT orders.id qcp4orderid,orders.id as qcp4_order_ids,qcp4.id qcp4ID,qcp4_products.traceability_code traceability_code,
qcp4_products.id row_id,qcp4_products.product_id product_id,qcp4_products.totalboxes total_boxes,crops.cropname qcp4_product_name,
qcp4_products.status qcp4_product_status,qcp4_products.boxweight qcp4_box_weight,
qcp4_products.productnetweight qcp4_productnet_weight,qcp4_products.totalproductnetweight total_product_netweight,
qcp4_products.totalproductgrossweight total_product_grossweight,qcp4_products.sampledboxes sampled_boxes, qcp4.packinglist_id from qcp4
LEFT JOIN qcp4_products on qcp4_products.qcp4_id = qcp4.id
LEFT JOIN packinglist   on packinglist.id        = qcp4.packinglist_id
LEFT JOIN orders        on orders.id             = packinglist.order_id
LEFT JOIN crops         on crops.id              = qcp4_products.product_id
LEFT JOIN users         on users.id              = qcp4.user_id
WHERE orders.consignment_no = $1
GROUP BY qcp4.id,qcp4_products.id,crops.cropname,orders.id;


-- name: Qcp4listOtherPerConsignment :many
SELECT orders.id qcp4orderid,qcp4_others.product_name other_product_name,qcp4_others.status qcp4_others_status,orders.id as qcp4_order_ids, qcp4_others.boxweight others_boxweight,
qcp4_others.productnetweight others_product_netweight,qcp4_others.totalproductnetweight others_total_productweight,qcp4_others.totalproductgrossweight others_total_productgrossweight,
qcp4.id qcp4ID,qcp4_others.noofboxes other_box_numbers,qcp4_others.sampledboxes other_sampled_boxes,qcp4_others.id row_other_id,
qcp4.packinglist_id from qcp4
LEFT JOIN qcp4_others   on qcp4_others.qcp4_id   = qcp4.id
LEFT JOIN packinglist   on packinglist.id        = qcp4.packinglist_id
LEFT JOIN orders        on orders.id             = packinglist.order_id
LEFT JOIN users         on users.id              = qcp4.user_id
WHERE qcp4.exportcompany_id     = $3
AND   qcp4.status = $2
AND   orders.consignment_no = $1
GROUP BY qcp4_others.id,qcp4.id,orders.id;


-- name: Qcp4OtherPerConsignment :many
SELECT orders.id qcp4orderid,qcp4_others.product_name other_product_name,qcp4_others.status qcp4_others_status,orders.id as qcp4_order_ids, qcp4_others.boxweight others_boxweight,
qcp4_others.productnetweight others_product_netweight,qcp4_others.totalproductnetweight others_total_productweight,qcp4_others.totalproductgrossweight others_total_productgrossweight,
qcp4.id qcp4ID,qcp4_others.noofboxes other_box_numbers,qcp4_others.sampledboxes other_sampled_boxes,qcp4_others.id row_other_id,
qcp4.packinglist_id from qcp4
LEFT JOIN qcp4_others   on qcp4_others.qcp4_id   = qcp4.id
LEFT JOIN packinglist   on packinglist.id        = qcp4.packinglist_id
LEFT JOIN orders        on orders.id             = packinglist.order_id
LEFT JOIN users         on users.id              = qcp4.user_id
WHERE orders.consignment_no = $1
GROUP BY qcp4_others.id,qcp4.id,orders.id;

-- name: CreateInspectionReport :one
INSERT INTO inspectionreport (
    consignment_no,
    notes,
    instruction,
    user_id,
    exportcompany_id
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5
    )
RETURNING *;

-- name: Qcp4OthersCount :one
SELECT COUNT(id) AS id_count
FROM   qcp4_others
WHERE  qcp4_id = $1;

-- name: CreateQcp4OtherQualityAttributes :one
INSERT INTO qcp4_other_attributes(
     qcp4_id,
     qcp4_other_id,
     qualityattributes_id,
     other ,
     user_id,
     exportcompany_id
) VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6
)
RETURNING *;

-- name: CreateQcp4OtherRecommendations :one
INSERT INTO qcp4_otherrecommendations (
    qcp4_id,
    qcp4_others_id,
    status,
    reason,
    weight_rejected,
    weight_passed,
    user_id,
    exportcompany_id
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6,
     $7,
     $8
    )
RETURNING *;

-- name: GetQcp4OtherRecommendations :one
SELECT * FROM qcp4_otherrecommendations
WHERE qcp4_id = $1 LIMIT 1;

-- name: GetQcp4Recommendations :many
SELECT * FROM qcp4_recommendations
WHERE qcp4_id = $1;

-- name: GetQcp4SingleRecommendations :one
SELECT * FROM qcp4_recommendations
WHERE qcp4_id = $1
AND   qcp4_product_id = $2;

-- name: GetQcp4OtherAttributes :many
SELECT * FROM qcp4_other_attributes
WHERE qcp4_id = $1;

-- name: GetQcp4Attributes :many
SELECT * FROM qcp4_attributes
WHERE qcp4_id = $1;

-- name: GetQcp4OthersByID :many
SELECT * FROM qcp4_others
WHERE qcp4_id = $1;

-- name: GetQcp4ProductsByID :many
SELECT qcp4_products.id,qcp4_products.status,
qcp4_products.qcp4_id,qcp4_products.traceability_code,
qcp4_products.totalboxes,qcp4_products.boxweight,qcp4_products.created_at,
qcp4_products.product_id,crops.cropname product_name,clients.companyname,
qcp4_products.sampledboxes,qcp4_products.productnetweight,
qcp4_products.totalproductnetweight,qcp4_products.totalproductgrossweight,orders.consignment_no,
exportcompanies.expname,orders.id as order_id,packinglist.id as packinglist_id
FROM  qcp4_products
JOIN  qcp4            on qcp4.id            = qcp4_products.qcp4_id
JOIN  packinglist     on packinglist.id     = qcp4.packinglist_id
JOIN  orders          on orders.id          = packinglist.order_id
JOIN  clients         on clients.id         = orders.client_id
JOIN  exportcompanies on exportcompanies.id = packinglist.exportcompany_id
JOIN  crops           on crops.id           = qcp4_products.product_id
WHERE qcp4_id = $1;



-- name: GetQcp4ProductsByPackinglistID :many
SELECT qcp4_products.id,qcp4_products.status,
qcp4_products.qcp4_id,qcp4_products.traceability_code,
qcp4_products.totalboxes,qcp4_products.productnetweight,qcp4_products.boxweight,
qcp4_products.product_id,qcp4_products.totalproductgrossweight,
qcp4_products.totalproductnetweight,qcp4_products.sampledboxes FROM qcp4_products
JOIN  qcp4 on qcp4.id   = qcp4_products.qcp4_id
WHERE qcp4.packinglist_id = $1;


-- name: GetQcp4OthersByPackinglistID :many
SELECT * FROM qcp4_others
WHERE  qcp4_others.packinglist_id = $1;


-- name: Qcp4Report :one
SELECT qcp4.exportcompany_id,qcp4.created_at,exportcompanies.expname exportcompany_name,users.fullname user_name, qcp4.packinglist_id,
qcp4.user_id,qcp4.status qcp4_status,clients.companyname,countries.countryname,crops.cropname,qcp4_recommendations.status recommendation_status,qcp4_recommendations.weight_passed,qcp4_recommendations.weight_rejected FROM qcp4
LEFT JOIN qcp4_recommendations on qcp4_recommendations.qcp4_id = qcp4.id
LEFT JOIN packinglist          on packinglist.id               = qcp4.packinglist_id
LEFT JOIN receptionform        on receptionform.id             = packinglist.receptionform_id
LEFT JOIN crops                on crops.id                     = receptionform.crop_id
LEFT JOIN orders               on orders.id                    = receptionform.order_id
LEFT JOIN clients              on clients.id                   = orders.client_id
LEFT JOIN countries            on countries.id                 = clients.country_id
LEFT JOIN exportcompanies      on exportcompanies.id           = receptionform.exportcompany_id
LEFT JOIN users                on users.id                     = receptionform.user_id
WHERE qcp4.id = $1;

-- name: Qcp4ProductsReport :many
SELECT qcp4_products.qcp4_id,qcp4_products.traceability_code,
qcp4_products.product_id,qcp4_products.totalboxes,
qcp4_products.boxweight,qcp4_products.productnetweight,
qcp4_products.totalproductnetweight,qcp4_products.totalproductgrossweight,
crops.cropname,qcp4_products.sampledboxes,qcp4_products.status,qualityattributes.description
FROM qcp4_products
LEFT JOIN qcp4_attributes   on qcp4_attributes.qcp4_id = qcp4_products.qcp4_id
LEFT JOIN qualityattributes on qualityattributes.id    = qcp4_attributes.qualityattributes_id
LEFT JOIN crops             on crops.id                = qcp4_products.product_id
WHERE qcp4_products.qcp4_id = $1;



-- name: FilterQcp4PerOrder :many
Select distinct on(qcp4_attributes.qualityattributes_id, qcp4_other_attributes.qualityattributes_id) qcp4_attributes.qualityattributes_id as qcp4_attri_id,
qcp4_other_attributes.qualityattributes_id as qcp4_other_attri_id,qcp4_products.qcp4_id,qcp4_products.traceability_code,
qcp4_products.product_id,qcp4_products.totalboxes,qcp4_products.boxweight,qcp4_products.productnetweight,
qcp4_products.totalproductnetweight,qcp4_products.totalproductgrossweight,crops.id as crop_id,crops.cropname,crops.variant,qcp4_products.sampledboxes,qcp4_products.status,
qualityattributes.description,qcp4_others.product_name others_product_name,qcp4_others.noofboxes others_boxes_number, qcp4_others.boxweight other_box_weight,
qcp4_others.productnetweight others_product_netweight,qcp4_others.totalproductnetweight others_total_productweight,qcp4_others.totalproductgrossweight others_total_productgrossweight,
qcp4_others.sampledboxes other_sampled_boxes,qcp4_others.status others_status,orders.created_at order_date,clients.companyname,countries.countryname,exportcompanies.expname,exportcompanies.expregno,qcp4_recommendations.status recommendation_status,
qcp4_recommendations.weight_passed qcp4_weight_pass,qcp4_recommendations.weight_rejected as weight_rejected,packhouses.phname,orders.consignment_no,orders.status as order_status, orders.id as order_id, qcp4.user_id as inspector_id,
packinglist.user_id as qualitycontroller_id,exportcompanies.id as exportcompany_id FROM qcp4
LEFT JOIN qcp4_products           on qcp4_products.qcp4_id                  = qcp4.id
LEFT JOIN qcp4_others             on qcp4_others.qcp4_id                    = qcp4.id
LEFT JOIN qcp4_other_attributes   on qcp4_other_attributes.qcp4_id          = qcp4.id
LEFT JOIN qcp4_attributes         on qcp4_attributes.qcp4_product_id        = qcp4_products.id
LEFT JOIN qcp4_recommendations    on qcp4_recommendations.qcp4_product_id   = qcp4_products.id
LEFT JOIN qualityattributes       on qualityattributes.id                   = qcp4_attributes.qualityattributes_id
LEFT JOIN packinglist             on packinglist.id                         = qcp4.packinglist_id
LEFT JOIN orders                  on orders.id                              = packinglist.order_id
LEFT JOIN clients                 on clients.id                             = orders.client_id
LEFT JOIN countries               on countries.id                           = clients.country_id
LEFT JOIN exportcompanies         on exportcompanies.id                     = qcp4.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 crops                   on crops.id                               = qcp4_products.product_id
LEFT JOIN users                   on users.id                               = qcp4.user_id
WHERE qcp4.id = $1;


-- name: Qcp4AttributesContent :many
Select qcp4_attributes.qcp4_id ,qualityattributes.description from qcp4_attributes
JOIN   qualityattributes on qualityattributes.id = qcp4_attributes.qualityattributes_id
WHERE  qcp4_id = $1
AND    qcp4_attributes.qcp4_product_id = $2;

-- name: Qcp4OtherAttributesContent :many
Select qualityattributes.description, qcp4_others.product_name from qcp4_other_attributes
JOIN   qualityattributes  on qualityattributes.id = qcp4_other_attributes.qualityattributes_id
JOIN   qcp4_others        on qcp4_others.id       = qcp4_other_attributes.qcp4_other_id
JOIN   users              on users.id             = qcp4_other_attributes.user_id
WHERE  qcp4_other_attributes.qcp4_id = $1
AND    qcp4_others.product_name = $2;

-- name: Qcp4ContentPerOrder :many
Select clients.companyname,countries.countryname,exportcompanies.expname,exportcompanies.expregno,
packhouses.phname, orders.consignment_no,orders.status,orders.created_at,
orders.id,orders.consignment_no as consignment__no from qcp4
JOIN  packinglist        on packinglist.id       = qcp4.packinglist_id
JOIN  orders             on orders.id            = packinglist.order_id
JOIN  exportcompanies    on exportcompanies.id   = orders.exportcompany_id
JOIN  clients            on clients.id           = orders.client_id
JOIN  countries          on countries.id         = clients.country_id
JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
JOIN  packhouses         on packhouse_exporter.packhouse_id     = packhouses.id
WHERE qcp4.id = $1;

-- name: Qcp4Recommendations :many
Select qcp4_recommendations.weight_passed,qcp4_recommendations.weight_rejected,
qcp4_recommendations.status from qcp4_recommendations
WHERE qcp4_id = $1
AND   qcp4_product_id = $2;

-- name: Qcp4ProductsContentPerOrder :many
select qcp4_products.qcp4_id,qcp4_products.totalboxes,
qcp4_products.boxweight,qcp4_products.productnetweight,crops.cropname,crops.id,crops.variant,
crops.crophscode,qcp4_products.totalproductnetweight,qcp4_products.traceability_code,
qcp4_products.totalproductgrossweight,qcp4_products.status,
qcp4_products.sampledboxes,qcp4_products.created_at,qcp4_products.id as product_row_id,qcp4_products.product_id from qcp4_products
JOIN crops on crops.id = qcp4_products.product_id
WHERE qcp4_products.qcp4_id = $1;

-- name: Qcp4OthersContentPerOrder :many
select qcp4_others.qcp4_id,qcp4_others.noofboxes,qcp4_others.product_name,
qcp4_others.boxweight,qcp4_others.productnetweight,qcp4_others.totalproductnetweight,
qcp4_others.totalproductgrossweight,qcp4_others.status,qcp4_others.sampledboxes,
qcp4_others.created_at from qcp4_others
WHERE qcp4_others.qcp4_id = $1;

-- name: Qcp4InspectorUpdates :one
UPDATE qcp4
  set
    user_id = $2
WHERE id = $1
RETURNING *;

-- name: Qcp4InspectorOtherUpdates :one
UPDATE qcp4_others
  set
    user_id = $2
WHERE id = $1
RETURNING *;

-- name: Qcp4ProductsResortUpdate :one
UPDATE qcp4_products
  set
    totalboxes              = $2,
    productnetweight        = $3,
    totalproductnetweight   = $4,
    totalproductgrossweight = $5,
    sampledboxes            = $6,
    status                  = $7
  WHERE id = $1
RETURNING *;

-- name: Qcp4ProductRow :one
SELECT qcp4.id as qcp4_id,qcp4_products.status as qcp4_product_status, qcp4_products.id as qcp4_row_id  from qcp4_products
JOIN   qcp4 on qcp4.id = qcp4_products.qcp4_id
JOIN   packinglist on packinglist.id = qcp4.packinglist_id
WHERE  qcp4_products.product_id=$1
AND    qcp4_products.traceability_code=$2
AND    qcp4_products.boxweight=$3
AND    qcp4.packinglist_id = $4;


-- name: Qcp4IDByPackingListID :one
SELECT qcp4.id as qcp4_id, packinglist.id as packinglist_id FROM qcp4
INNER JOIN packinglist on packinglist.id = qcp4.packinglist_id
WHERE packinglist.id = $1;


-- name: ListAllCompaniesQcp4s :many
SELECT qcp4.packinglist_id,qcp4.id as qcp4_id,
qcp4.status,qcp4.user_id,qcp4.exportcompany_id,
exportcompanies.expname,qcp4.user_id,users.fullname,
qcp4.status,qcp4.created_at FROM qcp4
JOIN  exportcompanies on exportcompanies.id = qcp4.exportcompany_id
JOIN  users on  users.id = qcp4.user_id
WHERE qcp4.status = $1 AND qcp4.created_at
BETWEEN TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
AND     TO_TIMESTAMP($3, 'YYYY-MM-DD HH24:MI:SS')
LIMIT   $4 OFFSET $5;


-- name: GlobalQcp4ProductsReport :many
SELECT qcp4_products.qcp4_id,qcp4_products.traceability_code,
qcp4_products.product_id,qcp4_products.totalboxes,qcp4_products.boxweight,qcp4_products.productnetweight,
qcp4_products.totalproductnetweight,qcp4_products.totalproductgrossweight,crops.cropname,qcp4_products.sampledboxes,
qcp4_products.status,
qcp4.packinglist_id,qcp4.id as qcp4_id,
qcp4.status,qcp4.user_id,qcp4.exportcompany_id,receptionform.traceability_code,orders.id as order_id,
exportcompanies.expname,qcp4.user_id,users.fullname,clients.companyname,countries.countryname,
qcp4_recommendations.status,qcp4_recommendations.weight_passed,qcp4_recommendations.weight_rejected,
farmers.fmname,farmers.fmcode,qcp4.status,qcp4.created_at,orders.consignment_no FROM qcp4_products
LEFT JOIN  qcp4 on qcp4.id = qcp4_products.qcp4_id
LEFT JOIN  qcp4_recommendations on qcp4_recommendations.qcp4_product_id = qcp4_products.id
LEFT JOIN  packinglist  on packinglist.id    = qcp4.packinglist_id
LEFT JOIN  orders       on orders.id              = packinglist.order_id
LEFT JOIN  clients      on clients.id              = orders.client_id
LEFT JOIN  countries     on countries.id           = clients.country_id
LEFT JOIN  receptionform on receptionform.order_id = orders.id
LEFT JOIN  farmers on farmers.id = receptionform.supplier_id
LEFT JOIN  crops on crops.id = qcp4_products.product_id
LEFT JOIN  exportcompanies on exportcompanies.id = qcp4.exportcompany_id
LEFT JOIN  users on  users.id = qcp4.user_id
LIMIT $1 OFFSET $2;



-- name: GlobalQcp4ProductsReportFilterStatus :many
SELECT qcp4_products.qcp4_id,qcp4_products.traceability_code,
qcp4_products.product_id,qcp4_products.totalboxes,qcp4_products.boxweight,qcp4_products.productnetweight,
qcp4_products.totalproductnetweight,qcp4_products.totalproductgrossweight,crops.cropname,qcp4_products.sampledboxes,
qcp4_products.status,
qcp4.packinglist_id,qcp4.id as qcp4_id,
qcp4.status,qcp4.user_id,qcp4.exportcompany_id,receptionform.traceability_code,orders.id as order_id,
exportcompanies.expname,qcp4.user_id,users.fullname,clients.companyname,countries.countryname,
qcp4_recommendations.status,qcp4_recommendations.weight_passed,qcp4_recommendations.weight_rejected,
farmers.fmname,farmers.fmcode,qcp4.status,qcp4.created_at,orders.consignment_no FROM qcp4_products
LEFT JOIN  qcp4 on qcp4.id = qcp4_products.qcp4_id
LEFT JOIN  qcp4_recommendations on qcp4_recommendations.qcp4_product_id = qcp4_products.id
LEFT JOIN  packinglist  on packinglist.id    = qcp4.packinglist_id
LEFT JOIN  orders       on orders.id              = packinglist.order_id
LEFT JOIN  clients      on clients.id              = orders.client_id
LEFT JOIN  countries     on countries.id           = clients.country_id
LEFT JOIN  receptionform on receptionform.order_id = orders.id
LEFT JOIN  farmers on farmers.id = receptionform.supplier_id
LEFT JOIN  crops on crops.id = qcp4_products.product_id
LEFT JOIN  exportcompanies on exportcompanies.id = qcp4.exportcompany_id
LEFT JOIN  users on  users.id = qcp4.user_id
WHERE qcp4_products.status = $1
LIMIT $2 OFFSET $3;


-- name: GlobalQcp4ProductsReportFilterDate :many
SELECT qcp4_products.qcp4_id,qcp4_products.traceability_code,
qcp4_products.product_id,qcp4_products.totalboxes,qcp4_products.boxweight,qcp4_products.productnetweight,
qcp4_products.totalproductnetweight,qcp4_products.totalproductgrossweight,crops.cropname,qcp4_products.sampledboxes,
qcp4_products.status,
qcp4.packinglist_id,qcp4.id as qcp4_id,
qcp4.status,qcp4.user_id,qcp4.exportcompany_id,receptionform.traceability_code,orders.id as order_id,
exportcompanies.expname,qcp4.user_id,users.fullname,clients.companyname,countries.countryname,
qcp4_recommendations.status,qcp4_recommendations.weight_passed,qcp4_recommendations.weight_rejected,
farmers.fmname,farmers.fmcode,qcp4.status,qcp4.created_at,orders.consignment_no FROM qcp4_products
LEFT JOIN  qcp4 on qcp4.id = qcp4_products.qcp4_id
LEFT JOIN  qcp4_recommendations on qcp4_recommendations.qcp4_product_id = qcp4_products.id
LEFT JOIN  packinglist  on packinglist.id    = qcp4.packinglist_id
LEFT JOIN  orders       on orders.id              = packinglist.order_id
LEFT JOIN  clients      on clients.id              = orders.client_id
LEFT JOIN  countries     on countries.id           = clients.country_id
LEFT JOIN  receptionform on receptionform.order_id = orders.id
LEFT JOIN  farmers on farmers.id = receptionform.supplier_id
LEFT JOIN  crops on crops.id = qcp4_products.product_id
LEFT JOIN  exportcompanies on exportcompanies.id = qcp4.exportcompany_id
LEFT JOIN  users on  users.id = qcp4.user_id
WHERE   qcp4.created_at
BETWEEN TO_TIMESTAMP($1, 'YYYY-MM-DD HH24:MI:SS')
AND     TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
LIMIT $3 OFFSET $4;



-- name: GlobalQcp4ProductsReportFilterDateStatus :many
SELECT qcp4_products.qcp4_id,qcp4_products.traceability_code,
qcp4_products.product_id,qcp4_products.totalboxes,qcp4_products.boxweight,qcp4_products.productnetweight,
qcp4_products.totalproductnetweight,qcp4_products.totalproductgrossweight,crops.cropname,qcp4_products.sampledboxes,
qcp4_products.status,
qcp4.packinglist_id,qcp4.id as qcp4_id,
qcp4.status,qcp4.user_id,qcp4.exportcompany_id,receptionform.traceability_code,orders.id as order_id,
exportcompanies.expname,qcp4.user_id,users.fullname,clients.companyname,countries.countryname,
qcp4_recommendations.status,qcp4_recommendations.weight_passed,qcp4_recommendations.weight_rejected,
farmers.fmname,farmers.fmcode,qcp4.status,qcp4.created_at,orders.consignment_no FROM qcp4_products
LEFT JOIN  qcp4 on qcp4.id = qcp4_products.qcp4_id
LEFT JOIN  qcp4_recommendations on qcp4_recommendations.qcp4_product_id = qcp4_products.id
LEFT JOIN  packinglist  on packinglist.id    = qcp4.packinglist_id
LEFT JOIN  orders       on orders.id              = packinglist.order_id
LEFT JOIN  clients      on clients.id              = orders.client_id
LEFT JOIN  countries     on countries.id           = clients.country_id
LEFT JOIN  receptionform on receptionform.order_id = orders.id
LEFT JOIN  farmers on farmers.id = receptionform.supplier_id
LEFT JOIN  crops on crops.id = qcp4_products.product_id
LEFT JOIN  exportcompanies on exportcompanies.id = qcp4.exportcompany_id
LEFT JOIN  users on  users.id = qcp4.user_id
WHERE   qcp4.created_at
BETWEEN TO_TIMESTAMP($1, 'YYYY-MM-DD HH24:MI:SS')
AND     TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
AND     qcp4.status = $3
LIMIT $4 OFFSET $5;

-- name: ListAllCompaniesQcp4WFs :many
SELECT qcp4.packinglist_id,qcp4.id as qcp4_id,
qcp4.status,qcp4.user_id,qcp4.exportcompany_id,
exportcompanies.expname,qcp4.user_id,users.fullname,
qcp4.status,qcp4.created_at FROM qcp4
JOIN  exportcompanies on exportcompanies.id = qcp4.exportcompany_id
JOIN  users on  users.id = qcp4.user_id
WHERE qcp4.status = $1 LIMIT $2 OFFSET $3;


-- name: ListAllCompaniesQcp4Ds :many
SELECT qcp4.packinglist_id,qcp4.id as qcp4_id,
qcp4.status,qcp4.user_id,qcp4.exportcompany_id,
exportcompanies.expname,qcp4.user_id,users.fullname,
qcp4.status,qcp4.created_at FROM qcp4
JOIN  exportcompanies on exportcompanies.id = qcp4.exportcompany_id
JOIN  users on  users.id = qcp4.user_id
WHERE qcp4.status = $1;

-- name: GetConsignmentNoByQcp4ID :one
SELECT orders.consignment_no, qcp4.id as qcp4_id from qcp4
LEFT JOIN packinglist  on packinglist.id   = qcp4.packinglist_id
LEFT JOIN orders       on orders.id        = packinglist.order_id
WHERE qcp4.id = $1;
