Create Inventory Report

[diego-torres]

Define and create inventory report with its filter.

[diego-torres]

working on Inventory report, current query:

SELECT array_to_string(array_agg(pens.barnyard_code),',') AS pen, quality.quality_name, SUM(inventory.heads) as heads, purchased.heads as purchased, sold_detail.heads as sold FROM ctrl_inventory inventory INNER JOIN cat_barnyard pens ON inventory.barnyard_id = pens.barnyard_id INNER JOIN cat_cattle_quality quality ON inventory.quality_id = quality.quality_id LEFT JOIN vw_purchased purchased ON quality.quality_name = purchased.quality_name AND purchase_date >= CURRENT_DATE LEFT JOIN ctrl_sale sold ON sold.sale_date >= CURRENT_DATE LEFT JOIN ctrl_sale_detail sold_detail ON inventory.quality_id = sold_detail.quality_id and sold.sale_id = sold_detail.sale_id GROUP BY quality.quality_name, purchased.heads, sold_detail.heads;

pending to calculate weight and feeding details.

[diego-torres]

SELECT array_to_string(array_agg(pens.barnyard_code),',') AS pen, quality.quality_name, SUM(inventory.heads) as inventory_heads, purchased.heads as purchased_heads, sold_detail.heads as sold_heads, SUM(inventory.weight) as inventory_weight, purchased.weight as purchased_weight, sold_detail.weight as sold_weight, SUM(feed.quantity) as feed_quantity FROM ctrl_inventory inventory INNER JOIN cat_barnyard pens ON inventory.barnyard_id = pens.barnyard_id INNER JOIN cat_cattle_quality quality ON inventory.quality_id = quality.quality_id LEFT JOIN vw_purchased purchased ON quality.quality_name = purchased.quality_name AND purchase_date >= CURRENT_DATE LEFT JOIN ctrl_sale sold ON sold.sale_date >= CURRENT_DATE LEFT JOIN ctrl_sale_detail sold_detail ON inventory.quality_id = sold_detail.quality_id and sold.sale_id = sold_detail.sale_id LEFT JOIN ctrl_feed_us feed ON inventory.inventory_id = feed.inventory_id GROUP BY quality.quality_name, purchased.heads, purchased.weight, sold_detail.heads, sold_detail.weight;

pending to set report file and properties.