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

-- name: CreateQcp5Others :one
INSERT INTO qcp5_others (
    qcp5_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: CreateQcp5Attributes :one
INSERT INTO qcp5_attributes (
    qcp5_id,
    qualityattributes_id,
    qcp5_product_id,
    user_id,
    exportcompany_id
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5
    )
RETURNING *;

-- name: CreateQcp5Recommendations :one
INSERT INTO qcp5_recommendations (
    qcp5_id,
    qcp5_product_id,
    status,
    reason,
    weight_rejected,
    weight_passed,
    user_id,
    exportcompany_id
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6,
     $7,
     $8
    )
RETURNING *;

-- name: GetQcp5Others :one
SELECT * FROM qcp5_others
WHERE id = $1 LIMIT 1;

-- name: GetQcp5 :one
SELECT * FROM qcp5
WHERE id = $1 LIMIT 1;

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

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

-- name: GetBorderInspectorID :one
SELECT user_id FROM qcp5_recommendations
WHERE qcp5_id = $1 LIMIT 1;

 -- name: CheckPackListExistanceInQcp5 :one
SELECT packinglist_id FROM qcp5
WHERE packinglist_id = $1;

 -- name: CheckPackListExistanceInQcp5Others :one
SELECT packinglist_id FROM qcp5_others
WHERE packinglist_id = $1;

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

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


-- name: UpdateQcp5Others :one
UPDATE qcp5_others
  set
    qcp5_id = $2,
    product_name = $3,
    noofboxes = $4
WHERE id = $1
RETURNING *;

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

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


-- name: ListPendingQcp5s :many
SELECT crops.cropname,receptionform.order_id,
qcp5.user_id,users.fullname,qcp5.created_at,qcp5.status,
orders.consignment_no,qcp5.id qcp5_id,receptionform.traceability_code from qcp5
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   = qcp5.user_id
WHERE qcp5.exportcompany_id     = $3  AND qcp5.status = $4
LIMIT $1 OFFSET $2;


-- name: CreateQcp5Products :one
INSERT INTO qcp5_products (
    qcp5_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: UpdateQcp5Sampled :one
UPDATE qcp5_products
  set
    sampledboxes = $2
WHERE id = $1
RETURNING *;

-- name: Qcp5listPerConsignment :many
SELECT  orders.id order_id,qcp5.id qcp5ID,qcp5.packinglist_id,qcp5_products.traceability_code traceability_code,qcp5_products.id row_id,qcp5_products.product_id product_id,
qcp5_products.totalboxes total_boxes,crops.cropname qcp5_product_name,qcp5_products.status qcp5_product_status,
qcp5_products.boxweight boxweight,qcp5_products.totalproductnetweight totalproductnetweight,
qcp5_products.totalproductgrossweight totalproductgrossweight,
qcp5_products.sampledboxes sampled_boxes,qcp5_products.productnetweight productnetweight from qcp5
LEFT JOIN qcp5_products on qcp5_products.qcp5_id = qcp5.id
LEFT JOIN crops         on crops.id   = qcp5_products.product_id
LEFT JOIN users         on users.id   = qcp5.user_id
LEFT JOIN packinglist   on packinglist.id = qcp5.packinglist_id
LEFT JOIN orders        on orders.id      = packinglist.order_id
WHERE    qcp5.status = $2
AND      orders.consignment_no = $1
GROUP BY qcp5.id,orders.id, qcp5_products.id,crops.cropname;


-- name: Qcp5PerConsignment :many
SELECT orders.id order_id,qcp5.id qcp5ID,qcp5.packinglist_id,
qcp5_products.traceability_code traceability_code,
qcp5_products.id row_id,qcp5_products.product_id product_id,
qcp5_products.totalboxes total_boxes,crops.cropname qcp5_product_name,
qcp5_products.status qcp5_product_status,
qcp5_products.boxweight boxweight,qcp5_products.totalproductnetweight totalproductnetweight,
qcp5_products.totalproductgrossweight totalproductgrossweight,
qcp5_products.sampledboxes sampled_boxes,
qcp5_products.productnetweight productnetweight from qcp5
LEFT JOIN qcp5_products on qcp5_products.qcp5_id = qcp5.id
LEFT JOIN crops         on crops.id   = qcp5_products.product_id
LEFT JOIN users         on users.id   = qcp5.user_id
LEFT JOIN packinglist   on packinglist.id = qcp5.packinglist_id
LEFT JOIN orders        on orders.id      = packinglist.order_id
WHERE  orders.consignment_no = $1
GROUP BY qcp5.id,orders.id, qcp5_products.id,crops.cropname;

-- name: Qcp5OthersPerConsignment :many
SELECT  qcp5_others.qcp5_id,qcp5_others.packinglist_id,qcp5_others.product_name,qcp5_others.noofboxes,
qcp5_others.sampledboxes,qcp5_others.boxweight,qcp5_others.productnetweight,
qcp5_others.totalproductnetweight,qcp5_others.totalproductgrossweight,qcp5_others.status,
qcp5_others.user_id,qcp5_others.exportcompany_id from qcp5_others
LEFT JOIN qcp5          on qcp5_others.qcp5_id   = qcp5.id
LEFT JOIN users         on users.id              = qcp5.user_id
LEFT JOIN packinglist   on packinglist.id        = qcp5.packinglist_id
LEFT JOIN orders        on orders.id             = packinglist.order_id
WHERE  orders.consignment_no = $1
GROUP BY qcp5_others.qcp5_id,qcp5.id,orders.id;


-- name: CreateQcp5OtherQualityAttributes :one
INSERT INTO qcp5_other_attributes(
     qcp5_id,
     qcp5_other_id,
     qualityattributes_id,
     other ,
     user_id,
     exportcompany_id
) VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6
)
RETURNING *;

-- name: CreateQcp5OtherRecommendations :one
INSERT INTO qcp5_otherrecommendations (
    qcp5_id,
    qcp5_others_id,
    status,
    reason,
    weight_rejected,
    weight_passed,
    user_id,
    exportcompany_id
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6,
     $7,
     $8
    )
RETURNING *;

-- name: GetQcp5OtherRecommendations :one
SELECT * FROM qcp5_otherrecommendations
WHERE qcp5_id = $1 LIMIT 1;

-- name: GetQcp5OtherProductRecommendations :one
SELECT * FROM qcp5_otherrecommendations
WHERE qcp5_others_id = $1 LIMIT 1;

-- name: GetQcp5Recommendations :many
SELECT * FROM qcp5_recommendations
WHERE qcp5_id = $1;

-- name: GetQcp5ProductRecommendations :one
SELECT * FROM qcp5_recommendations
WHERE qcp5_product_id = $1;

-- name: GetQcp5OtherAttributes :many
SELECT * FROM qcp5_other_attributes
WHERE qcp5_id = $1;

-- name: GetQcp5Attributes :many
SELECT  qcp5_attributes.qcp5_id,qcp5_attributes.qualityattributes_id,
qcp5_attributes.qcp5_product_id,qcp5_attributes.user_id,qualityattributes.description,
qcp5_attributes.exportcompany_id, crops.cropname FROM qcp5_attributes
JOIN qualityattributes on qualityattributes.id = qcp5_attributes.qualityattributes_id
JOIN crops             on crops.id             = qcp5_attributes.qcp5_product_id
WHERE qcp5_id = $1;

-- name: GetQcp5OthersByID :many
SELECT * FROM qcp5_others
WHERE qcp5_id = $1;

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


-- name: Qcp5ContentPerOrder :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 qcp5
JOIN  packinglist        on packinglist.id       = qcp5.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 qcp5.id = $1;

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

-- name: Qcp5OthersContentPerOrder :many
select qcp5_others.qcp5_id,qcp5_others.noofboxes,qcp5_others.product_name,
qcp5_others.boxweight,qcp5_others.productnetweight,qcp5_others.totalproductnetweight,
qcp5_others.totalproductgrossweight,qcp5_others.status,qcp5_others.sampledboxes,
qcp5_others.created_at from qcp5_others
WHERE qcp5_others.qcp5_id = $1;

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

-- name: Qcp5AttributesContent :many
Select qcp5_attributes.qcp5_id,qualityattributes.description from qcp5_attributes
JOIN   qualityattributes on qualityattributes.id = qcp5_attributes.qualityattributes_id
WHERE  qcp5_id = $1
AND    qcp5_attributes.qcp5_product_id = $2;

-- name: GetQcp5SingleRecommendations :one
SELECT * FROM qcp5_recommendations
WHERE qcp5_id = $1
AND   qcp5_product_id = $2;

-- name: Qcp5OtherAttributesContent :many
Select qualityattributes.description, qcp5_others.product_name from qcp5_other_attributes
JOIN   qualityattributes  on qualityattributes.id = qcp5_other_attributes.qualityattributes_id
JOIN   qcp5_others        on qcp5_others.id       = qcp5_other_attributes.qcp5_other_id
JOIN   users              on users.id             = qcp5_other_attributes.user_id
WHERE  qcp5_other_attributes.qcp5_id = $1
AND    qcp5_others.product_name = $2;

-- name: UpdateQcp5OtherProductsStatusRows :one
Update qcp5_others
    SET
    status   = $5
    WHERE qcp5_id  = $1
    AND   packinglist_id = $2
    AND   product_name = $3
    AND   boxweight = $4
RETURNING *;


-- name: UpdateQcp5ProductsStatusRows :one
Update qcp5_products
    SET
    status   = $5
    WHERE qcp5_id  = $1
    AND   traceability_code = $2
    AND   product_id = $3
    AND   boxweight = $4
RETURNING *;


-- name: Qcp5ProductsExactRow :one
SELECT * FROM  qcp5_products
WHERE qcp5_id           = $1
AND   traceability_code = $2
AND   product_id        = $3
AND   boxweight         = $4;


-- name: ListAllCompaniesQcp5s :many
SELECT qcp5.packinglist_id,qcp5.id as qcp5_id,
qcp5.status,qcp5.user_id,qcp5.created_at,
qcp5.exportcompany_id, users.fullname,exportcompanies.expname FROM qcp5
JOIN exportcompanies on exportcompanies.id = qcp5.exportcompany_id
JOIN users           on users.id           = qcp5.user_id
Where qcp5.status = $1 AND qcp5.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: ListAllCompaniesQcp5WFs :many
SELECT qcp5.packinglist_id,qcp5.id as qcp5_id,
qcp5.status,qcp5.user_id,qcp5.created_at,
qcp5.exportcompany_id, users.fullname,exportcompanies.expname FROM qcp5
JOIN exportcompanies on exportcompanies.id = qcp5.exportcompany_id
JOIN users           on users.id           = qcp5.user_id
Where qcp5.status = $1 LIMIT $2 OFFSET $3;


-- name: ListAllCompaniesQcp5Ds :many
SELECT qcp5.packinglist_id,qcp5.id as qcp5_id,
qcp5.status,qcp5.user_id,qcp5.created_at,
qcp5.exportcompany_id, users.fullname,exportcompanies.expname FROM qcp5
JOIN exportcompanies on exportcompanies.id = qcp5.exportcompany_id
JOIN users           on users.id           = qcp5.user_id
Where qcp5.status = $1;


-- name: GetConsignmentNoByQcp5ID :one
SELECT orders.consignment_no, qcp5.id as qcp5_id from qcp5
LEFT JOIN packinglist  on packinglist.id   = qcp5.packinglist_id
LEFT JOIN orders       on orders.id        = packinglist.order_id
WHERE qcp5.id = $1;


-- name: GlobalQcp5ProductsReport :many
SELECT qcp5_products.qcp5_id,qcp5_products.traceability_code,
qcp5_products.product_id,qcp5_products.totalboxes,qcp5_products.boxweight,qcp5_products.productnetweight,
qcp5_products.totalproductnetweight,qcp5_products.totalproductgrossweight,crops.cropname,qcp5_products.sampledboxes,
qcp5_products.status,
qcp5.packinglist_id,qcp5.id as qcp5_id,
qcp5.status,qcp5.user_id,qcp5.exportcompany_id,receptionform.traceability_code,orders.id as order_id,
exportcompanies.expname,qcp5.user_id,users.fullname,clients.companyname,countries.countryname,
qcp5_recommendations.status,qcp5_recommendations.weight_passed,qcp5_recommendations.weight_rejected,
farmers.fmname,farmers.fmcode,qcp5.status,qcp5.created_at,orders.consignment_no FROM qcp5_products
LEFT JOIN  qcp5 on qcp5.id = qcp5_products.qcp5_id
LEFT JOIN  qcp5_recommendations on qcp5_recommendations.qcp5_product_id = qcp5_products.id
LEFT JOIN  packinglist  on packinglist.id    = qcp5.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 = qcp5_products.product_id
LEFT JOIN  exportcompanies on exportcompanies.id = qcp5.exportcompany_id
LEFT JOIN  users on  users.id = qcp5.user_id
LIMIT $1 OFFSET $2;


-- name: GlobalQcp5ProductsReportFilterStatus :many
SELECT qcp5_products.qcp5_id,qcp5_products.traceability_code,
qcp5_products.product_id,qcp5_products.totalboxes,qcp5_products.boxweight,qcp5_products.productnetweight,
qcp5_products.totalproductnetweight,qcp5_products.totalproductgrossweight,crops.cropname,qcp5_products.sampledboxes,
qcp5_products.status,
qcp5.packinglist_id,qcp5.id as qcp5_id,
qcp5.status,qcp5.user_id,qcp5.exportcompany_id,receptionform.traceability_code,orders.id as order_id,
exportcompanies.expname,qcp5.user_id,users.fullname,clients.companyname,countries.countryname,
qcp5_recommendations.status,qcp5_recommendations.weight_passed,qcp5_recommendations.weight_rejected,
farmers.fmname,farmers.fmcode,qcp5.status,qcp5.created_at,orders.consignment_no FROM qcp5_products
LEFT JOIN  qcp5 on qcp5.id = qcp5_products.qcp5_id
LEFT JOIN  qcp5_recommendations on qcp5_recommendations.qcp5_product_id = qcp5_products.id
LEFT JOIN  packinglist  on packinglist.id    = qcp5.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 = qcp5_products.product_id
LEFT JOIN  exportcompanies on exportcompanies.id = qcp5.exportcompany_id
LEFT JOIN  users on  users.id = qcp5.user_id
WHERE   qcp5_products.status = $1
LIMIT   $2 OFFSET $3;



-- name: GlobalQcp5ProductsReportFilterDate :many
SELECT qcp5_products.qcp5_id,qcp5_products.traceability_code,
qcp5_products.product_id,qcp5_products.totalboxes,qcp5_products.boxweight,qcp5_products.productnetweight,
qcp5_products.totalproductnetweight,qcp5_products.totalproductgrossweight,crops.cropname,qcp5_products.sampledboxes,
qcp5_products.status,
qcp5.packinglist_id,qcp5.id as qcp5_id,
qcp5.status,qcp5.user_id,qcp5.exportcompany_id,receptionform.traceability_code,orders.id as order_id,
exportcompanies.expname,qcp5.user_id,users.fullname,clients.companyname,countries.countryname,
qcp5_recommendations.status,qcp5_recommendations.weight_passed,qcp5_recommendations.weight_rejected,
farmers.fmname,farmers.fmcode,qcp5.status,qcp5.created_at,orders.consignment_no FROM qcp5_products
LEFT JOIN  qcp5 on qcp5.id = qcp5_products.qcp5_id
LEFT JOIN  qcp5_recommendations on qcp5_recommendations.qcp5_product_id = qcp5_products.id
LEFT JOIN  packinglist  on packinglist.id    = qcp5.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 = qcp5_products.product_id
LEFT JOIN  exportcompanies on exportcompanies.id = qcp5.exportcompany_id
LEFT JOIN  users on  users.id = qcp5.user_id
WHERE   qcp5_products.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: GlobalQcp5ProductsReportFilterDateStatus :many
SELECT qcp5_products.qcp5_id,qcp5_products.traceability_code,
qcp5_products.product_id,qcp5_products.totalboxes,qcp5_products.boxweight,qcp5_products.productnetweight,
qcp5_products.totalproductnetweight,qcp5_products.totalproductgrossweight,crops.cropname,qcp5_products.sampledboxes,
qcp5_products.status,
qcp5.packinglist_id,qcp5.id as qcp5_id,
qcp5.status,qcp5.user_id,qcp5.exportcompany_id,receptionform.traceability_code,orders.id as order_id,
exportcompanies.expname,qcp5.user_id,users.fullname,clients.companyname,countries.countryname,
qcp5_recommendations.status,qcp5_recommendations.weight_passed,qcp5_recommendations.weight_rejected,
farmers.fmname,farmers.fmcode,qcp5.status,qcp5.created_at,orders.consignment_no FROM qcp5_products
LEFT JOIN  qcp5 on qcp5.id = qcp5_products.qcp5_id
LEFT JOIN  qcp5_recommendations on qcp5_recommendations.qcp5_product_id = qcp5_products.id
LEFT JOIN  packinglist  on packinglist.id    = qcp5.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 = qcp5_products.product_id
LEFT JOIN  exportcompanies on exportcompanies.id = qcp5.exportcompany_id
LEFT JOIN  users on  users.id = qcp5.user_id
WHERE   qcp5_products.created_at
BETWEEN TO_TIMESTAMP($1, 'YYYY-MM-DD HH24:MI:SS')
AND     TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
AND     qcp5_products.status = $3
LIMIT   $4 OFFSET $5;


-- name: Qcp5OthersCount :one
SELECT COUNT(id) AS id_count
FROM   qcp5_others
WHERE  qcp5_id = $1;


-- name: Qcp5listOtherPerConsignment :many
SELECT orders.id qcp5orderid,qcp5_others.product_name other_product_name,qcp5_others.status qcp5_others_status,orders.id as qcp5_order_ids, qcp5_others.boxweight others_boxweight,
qcp5_others.productnetweight others_product_netweight,qcp5_others.totalproductnetweight others_total_productweight,qcp5_others.totalproductgrossweight others_total_productgrossweight,
qcp5.id qcp5ID,qcp5_others.noofboxes other_box_numbers,qcp5_others.sampledboxes other_sampled_boxes,qcp5_others.id row_other_id,
qcp5.packinglist_id from qcp5
LEFT JOIN qcp5_others   on qcp5_others.qcp5_id   = qcp5.id
LEFT JOIN packinglist   on packinglist.id        = qcp5.packinglist_id
LEFT JOIN orders        on orders.id             = packinglist.order_id
LEFT JOIN users         on users.id              = qcp5.user_id
WHERE qcp5.status = $2
AND   orders.consignment_no = $1
GROUP BY qcp5_others.id,qcp5.id,orders.id;
