UPDATE sale_vs_item SET deleted = '1' WHERE saleId = '1361'; ###UPDATE sale_vs_return SET deleted = '1' WHERE saleId = '1361'; ### UPDATE sale_vs_item SET id = '6284', purchase_vs_itemId = '770', selling_type = '50 POUND BOX', qty = '10', item_unit_cost = '5.00 / 20.00', price = '7.00', total = '70.00',  deleted = '0' WHERE id = '6284'; ### UPDATE sale_vs_item SET id = '6285', purchase_vs_itemId = '771', selling_type = 'Pallet 8 Ct', qty = '20', item_unit_cost = '4.00 / 10.00', price = '4.50', total = '90.00',  deleted = '0' WHERE id = '6285'; ###INSERT INTO sale_vs_return (id, sale_vs_itemId, lost_qty, lost_price, date, notes, saleId) VALUES ('', '6284', '5', '35.00', '2024-09-30', '', '1361')  ;###SET @gvpId = LAST_INSERT_ID();###INSERT INTO sale_vs_return (id, sale_vs_itemId, lost_qty, lost_price, date, notes, saleId) VALUES ('', '6285', '8', '36.00', '2024-09-30', '', '1361')  ;###SET @gvpId = LAST_INSERT_ID();###UPDATE sale SET code = 'INV001361', customer_id = '201', sale_date = '2024-03-30', due_date = '2024-04-30', email = 'cabrera.yeison@gmail.com', send_email = '', bill_to_address = '145 Main St', bill_to_apt = '1', bill_to_city = 'Brooklyn', bill_to_state = 'NY', bill_to_zip = '11204', ship_to_address = '145 Main St', ship_to_apt = '1', ship_to_city = 'Brooklyn', ship_to_state = 'NY', ship_to_zip = '11204', grand_total = '160.00', total_profit_pct = '30.00 / %23.08', ship_via = '', shipping_date = '0000-00-00', shipping_time = '00:00:00', shipping_status = 'preparing', terms = '30', delivered_date = '0000-00-00', tracking_number = '', email_message = '', notes = '', sale_statement = 'ALL RETURNS, CREDITS OR CHANGES MUST BE APPROVED BY YOUR SALESMAN AND SENT BACK TO US WITHIN 48 HOURS OR THEY WILL NOT BE ACCEPTED OR APPROVED UNDER ANY CIRCUMSTANCES.', sale_footer = 'The perishable agricultural comm. listed on this invoice. are sold subject to the statutory trust authority. by Section 5 of the Perishable Agricultural Commodities Act,1930(7 U.S.C. 499 e).The seller of these comm. retains a trust claim over these comm.,all inventories of food or other products derived from these comm.,and any receivables or proceeds from the sale of these comm. until full payment. is received.' WHERE id = '1361'###insert into entity_audit (entity, entity_id, code, action, changes, user_id) 
                        values ('sale',1361, 'INV001361','UPDATE', '{"userId":"131","input":[],"mt":[{"id":"sale_form_sale_vs_return","from":[],"to":[{"id":"","sale_vs_itemId":"6284","lost_qty":"5","lost_price":"35.00","date":"2024-09-30","notes":""},{"id":"","sale_vs_itemId":"6285","lost_qty":"8","lost_price":"36.00","date":"2024-09-30","notes":""}]}]}', 131)###
   	UPDATE sale_vs_item
SET 
    returned_qty = (
       COALESCE(
          (SELECT SUM(sale_vs_return.lost_qty)
           FROM sale_vs_return
           WHERE sale_vs_return.sale_vs_itemId = sale_vs_item.id
           AND sale_vs_return.deleted = '0'), 0
       )
    )
WHERE sale_vs_item.saleId = '1361'
AND sale_vs_item.deleted = '0'
###
UPDATE sale_vs_return 
JOIN sale_vs_item ON sale_vs_item.id = sale_vs_return.sale_vs_itemId
SET sale_vs_return.lost_price = (sale_vs_return.lost_qty * sale_vs_item.price)
WHERE sale_vs_return.saleId = '1361';
;
###
   	UPDATE sale
	SET grand_total = (
		SELECT SUM(svi.total)
		FROM sale_vs_item svi
		WHERE svi.saleId = '1361'
		AND svi.deleted = '0'
	),
	credit_amount = (
		SELECT SUM(svi.returned_qty * svi.price)
		FROM sale_vs_item svi
		WHERE svi.saleId = '1361'
		AND svi.deleted = '0'
	)

	WHERE sale.id = '1361'
	AND sale.deleted = '0'
###
   	UPDATE sale
    SET payment_status = 
        CASE
            WHEN (grand_total + spent_credit = paid_amount + credit_amount) THEN 'paid'
            WHEN ((paid_amount  + credit_amount < grand_total + spent_credit) AND paid_amount > 0) THEN 'partially_paid'
            WHEN ((paid_amount  + credit_amount > grand_total + spent_credit) AND paid_amount > 0) THEN 'credited'
            ELSE 'unpaid'
        END
    WHERE sale.id = '1361';
