Hello There is no response for this can you check please. what is the issue

Hi can you please answer my question please...
i am waiting for reponse
Thanks

Hello,

I will check the queries Monday morning and let you know about the reports.

For product stock not reducing on sales, is it happening with all items or some? If some, I will need the products' codes and access to database to check.

Thank you

Okay also i need to make a return report can you tell me how much cost it will be to make a return report where i want to show 3-4 columns and filter

Hello,

The products report uses this query

```sql
SET SESSION sql_mode = "";
SELECT
sma_products.code,
sma_products.name,
CONCAT(COALESCE(PCosts.purchasedQty, 0), '__', COALESCE(PCosts.totalPurchase, 0)) AS purchased,
CONCAT(COALESCE(PSales.soldQty, 0), '__', COALESCE(PSales.totalSale, 0)) AS sold,
(COALESCE(PSales.totalSale, 0) - COALESCE(PCosts.totalPurchase, 0)) AS Profit,
CONCAT(COALESCE(PCosts.balacneQty, 0), '__', COALESCE(PCosts.balacneValue, 0)) AS balance,
sma_products.id AS id
FROM
`sma_products`
LEFT JOIN (
SELECT
si.product_id,
s.date AS date,
s.created_by AS created_by,
SUM(si.quantity) soldQty,
SUM(si.quantity * si.sale_unit_price) totalSale
FROM
sma_costing si
JOIN sma_sales s ON s.id = si.sale_id
GROUP BY
si.product_id) PSales ON `sma_products`.`id` = `PSales`.`product_id`
LEFT JOIN (
SELECT
product_id,
p.date AS date,
p.created_by AS created_by,
SUM(
CASE WHEN pi.purchase_id IS NOT NULL THEN
quantity
ELSE
0
END) AS purchasedQty,
SUM(quantity_balance) AS balacneQty,
SUM(unit_cost * quantity_balance) balacneValue,
SUM((
CASE WHEN pi.purchase_id IS NOT NULL THEN
(pi.subtotal)
ELSE
0
END)) totalPurchase
FROM
sma_purchase_items pi
LEFT JOIN sma_purchases p ON p.id = pi.purchase_id
WHERE
pi.status = 'received'
GROUP BY
pi.product_id) PCosts ON `sma_products`.`id` = `PCosts`.`product_id`
WHERE
`sma_products`.`type` != 'combo'
GROUP BY
`sma_products`.`code`
ORDER BY
`sold` DESC,
`purchased` DESC
LIMIT 10;
```

and brands report

```sql
SET SESSION sql_mode = "";

SELECT
sma_brands.id AS id,
sma_brands.name,
SUM(COALESCE(PCosts.purchasedQty, 0)) AS PurchasedQty,
SUM(COALESCE(PSales.soldQty, 0)) AS SoldQty,
SUM(COALESCE(PCosts.totalPurchase, 0)) AS TotalPurchase,
SUM(COALESCE(PSales.totalSale, 0)) AS TotalSales,
(SUM(COALESCE(PSales.totalSale, 0)) - SUM(COALESCE(PCosts.totalPurchase, 0))) AS Profit
FROM
`sma_brands`
LEFT JOIN (
SELECT
sp.brand AS brand,
SUM(si.quantity) soldQty,
SUM(si.subtotal) totalSale
FROM
sma_products sp
LEFT JOIN sma_sale_items si ON sp.id = si.product_id
LEFT JOIN sma_sales s ON s.id = si.sale_id
GROUP BY
sp.brand) PSales ON `sma_brands`.`id` = `PSales`.`brand`
LEFT JOIN (
SELECT
pp.brand AS brand,
SUM(pi.quantity) purchasedQty,
SUM(pi.subtotal) totalPurchase
FROM
sma_products pp
LEFT JOIN sma_purchase_items pi ON pp.id = pi.product_id
LEFT JOIN sma_purchases p ON p.id = pi.purchase_id
GROUP BY
pp.brand) PCosts ON `sma_brands`.`id` = `PCosts`.`brand`
GROUP BY
`sma_brands`.`id`
ORDER BY
`sma_brands`.`name` ASC
LIMIT 10;
```

Adding a report could take 2 - 4 hours costing $100 - $200.

Thank you

Can you do it in 100$. I also want that return sale with opening stock and closing stock like last month how much stock was there. this i want

But Firstly can you tell me why both showing diffrent diffrent values in brand and purchase table when we see total sales

Above what query you write that is not usefull becuse i told you there is total issue of brand and if i will filter in brand and product resport with filter brand name both showing diffrent diffrent values

Hello,

SMA doesn't keep log for stock movement. There is no option to have opening & closing stock.

I have no idea why the numbers are different. I will need site and server details to check.

Thank you

how can i share here ? can i share here ?