UPDATE `purchase_details` AS `dest`, (SELECT quantity FROM purchase_details WHERE square_foot = 0 ) AS `src` SET `dest`.`square_foot` = `src`.`quantity` WHERE `dest`.`square_foot` = 0


/*-----26-01-2023---------*/
=> SELECT * FROM `purchase_details` WHERE purchase_date >= '2023-01-01' AND purchase_date <= '2023-01-28' AND productcategory_id = 20 
=> SELECT p.product_name,p.code,c.name AS category_name,pd.rate,SUM(pd.quantity) AS quantity,SUM(pd.square_foot) AS square_foot,SUM(pd.now_stock) AS now_stock FROM `purchase_details` AS pd, products AS p, productcategories c WHERE p.id = pd.product_id AND pd.productcategory_id = 20 AND pd.purchase_date >= '2023-01-01' AND pd.purchase_date <= '2023-01-28' GROUP BY pd.product_id 
=> SELECT p.product_name,p.code,c.name AS category_name,pd.purchase_date,pd.rate,SUM(pd.quantity) AS quantity,SUM(pd.square_foot) AS square_foot,SUM(pd.now_stock) AS now_stock FROM `purchase_details` AS pd, products AS p, productcategories c WHERE p.id = pd.product_id AND pd.productcategory_id = 20 AND pd.purchase_date >= '2023-01-01' AND pd.purchase_date <= '2023-01-28' GROUP BY pd.product_id,pd.purchase_date ORDER BY pd.purchase_date 



/*-------------20-03-2024---------------*/
SELECT
    s.customer_id,
    s.grand_total_price,
    s.due_amount
FROM
    sales s
INNER JOIN customer_accounts cp ON
    s.customer_id = cp.customer_id
WHERE
    s.due_amount > 0 -- Ensure there's a due amount
    AND cp.payment_date IS NULL -- No payment recorded
    AND DATE_ADD(
        s.sale_date,
        INTERVAL - DAYOFWEEK(s.sale_date) + 6 DAY
    ) BETWEEN DATE_SUB(
        CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) DAY) AND DATE_SUB(CURDATE(), INTERVAL 1 DAY) -- Check due date within last week (Saturday to Thursday)
        AND DAYOFWEEK(s.sale_date) >= 6 AND DAYOFWEEK(s.sale_date) <= 4;
        
SELECT s.customer_id, s.due_amount FROM sales s INNER JOIN customer_accounts cp ON s.customer_id = cp.customer_id WHERE s.due_amount > 0 -- Ensure there's a due amount AND cp.payment_date IS NULL -- No payment recorded AND s.sale_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() -- Check sale date within last 7 days AND DAYOFWEEK(s.sale_date) >= 6 AND DAYOFWEEK(s.sale_date) <= 4;

=> step 3
SELECT
    s.id,
    s.sale_date,
    s.customer_id,
    s.grand_total_price,
    s.payment,
    s.due_amount
FROM
    sales s
LEFT JOIN(
    SELECT customer_id,
        MAX(payment_date) AS last_payment_date
    FROM
        customer_accounts
    GROUP BY
        customer_id
) cp
ON
    s.customer_id = cp.customer_id
WHERE
    s.due_amount > 0 AND(
        cp.last_payment_date IS NULL OR cp.last_payment_date < DATE_SUB(NOW(), INTERVAL 7 DAY));
        
=> step 4

SELECT
    c.name AS customer_name,
    SUM(s.grand_total_price) AS total_amount,
    SUM(s.payment) AS total_payment,
    SUM(s.due_amount) AS total_due
FROM
    sales s
INNER JOIN customers c ON
    s.customer_id = c.id
LEFT JOIN(
    SELECT customer_id,
        MAX(payment_date) AS last_payment_date
    FROM
        customer_accounts
    GROUP BY
        customer_id
) cp
ON
    s.customer_id = cp.customer_id
WHERE
    s.due_amount > 0 AND(
        cp.last_payment_date IS NULL OR cp.last_payment_date < DATE_SUB(NOW(), INTERVAL 7 DAY))
    GROUP BY
        c.name;