report

gross_proft:


select sd_headers.dr_type as type, items.description as item_desc, item_categories.name as item_cat, item_subcategories.name as item_sub_cat,
sd_details.qty, case 
	when sd_details.type = 'base' then (SELECT bulk_units.name from bulk_units where bulk_units.id = sd_details.uom)
	ELSE
		(select short from uoms where uoms.id = sd_details.uom)
end as uom, sd_details.price as net_price, sd_details.qty * sd_details.price as sales_price, sd_details.price as cost, 
(sd_details.qty * sd_details.price) - sd_details.price as margin
from sd_details
INNER JOIN sd_headers on sd_details.dr_id = sd_headers.id
INNER JOIN items on sd_details.item_id = items.id
LEFT JOIN item_categories on items.category_id = item_categories.id
LEFT JOIN item_subcategories on items.subcategory_id = item_subcategories.id
where sd_headers.status = 'Finalized' and sd_headers.a_status in ('1','2')
and items.id = 2343


sales:

select sd_headers.dr_date as date, sd_headers.id as dr_id, partners.name as partner_name, item_categories.name as item_cat, item_subcategories.name as item_sub_cat,
sd_details.price as amount, if(sd_headers.bill_status = 'Unbilled', 'Unpaid', 'Paid') as status
 from sd_details
inner join sd_headers on sd_details.dr_id = sd_headers.id
INNER JOIN items on sd_details.item_id = items.id
LEFT JOIN item_categories on items.category_id = item_categories.id
LEFT JOIN item_subcategories on items.subcategory_id = item_subcategories.id
INNER JOIN partners on sd_headers.partner_id = partners.id
where sd_headers.status = 'Finalized' and sd_headers.a_status in ('1','2')

and sd_details.item_id = 2343