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

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

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 ?