# Item-wise Purchase Report - Implementation Summary

## Overview
Created a comprehensive Item-wise Purchase Report for the UZRS Inventory System that tracks:
- Quantity purchased
- Quantity sold
- Remaining stock
- Purchase value
- Sold value (sales rate is after discount + tax as per FinalRate field)
- Stock value

## Files Created

### 1. Main Report Page
**File:** `PurchaseReports/item-wise-purchase-report.php`
- Full-featured report interface with filters
- Select2 dropdowns for categories, items, suppliers, and purchase types
- Quick date filters (Today, Yesterday, This Week, Last Week, This Month, Last Month, This Year, Custom Range)
- Summary cards showing totals
- Responsive table with item details

### 2. API Endpoint
**File:** `PurchaseReports/api/get-item-wise-purchases.php`
- Fetches purchase data with stock tracking
- Calculates sold quantities from sales data
- Computes remaining stock (purchased - sold)
- Calculates values (purchase value, sold value, stock value)
- **Sales rate uses FinalRate field** (which includes discount and tax calculations)
- Supports filtering by:
  - Date range
  - Category
  - Item
  - Supplier (by Code or ID)
  - Purchase type

### 3. Stylesheet
**File:** `PurchaseReports/css/item-wise-purchase-report.css`
- Consistent styling with danger/red theme for purchases
- Responsive design for mobile devices
- Color-coded summary cards
- Professional table layout

### 4. JavaScript
**File:** `PurchaseReports/js/item-wise-purchase-report.js`
- Report data loading and rendering
- Quick filter date calculations
- Excel export functionality
- Print view integration
- Select2 initialization for dropdowns

### 5. Print View
**File:** `PurchaseReports/print-item-wise-purchase.php`
- Print-optimized layout (A4 landscape)
- Professional header with company details
- Filter information display
- Auto-print on load
- Includes all summary data and item details

### 6. Dashboard Update
**File:** `PurchaseReports/index.php`
- Updated Item-wise Purchase Report card to link to the new report
- Changed from "Coming Soon" to active link

## Key Features

### Data Tracking
1. **Purchase Data**
   - Total quantity purchased in selected period
   - Average purchase rate (after discounts)
   - Total purchase value
   - Invoice count

2. **Sales Data**
   - Total quantity sold in selected period
   - Average sales rate (FinalRate = after discount + tax)
   - Total sold value

3. **Stock Analysis**
   - Remaining stock = Purchased - Sold
   - Stock value = Remaining × Average Purchase Rate
   - Color-coded stock status:
     - Green: Positive stock
     - Yellow/Orange: Zero stock
     - Red: Negative stock (oversold)

### Filters Available
- **Date Range:** Quick filters + custom date selection
- **Category:** Filter by product category
- **Item:** Filter by specific item
- **Supplier:** Filter by supplier
- **Purchase Type:** GST, NON-GST, IMPORT, etc.

### Summary Cards Display
1. Total Items
2. Quantity Purchased
3. Quantity Sold
4. Stock Remaining
5. Purchase Value (in rupees)
6. Sold Value (in rupees)
7. Stock Value (in rupees)
8. Total Invoices

### Report Table Columns
1. Serial Number
2. Item Code
3. Item Name
4. Category
5. Brand
6. Unit
7. Quantity Purchased
8. Quantity Sold
9. Stock Remaining (color-coded)
10. Average Purchase Rate
11. Purchase Value
12. Average Sales Rate (after discount + tax)
13. Sold Value
14. Stock Value

## Important Notes

### Sales Rate Calculation
The sales rate shown is the **FinalRate** from `tblsales_details` which represents:
- Base rate
- After discount applied
- After tax applied

This is correctly implemented in the SQL query:
```sql
COALESCE((
    SELECT AVG(sd.FinalRate)
    FROM tblsales_details sd
    INNER JOIN tblsales s ON sd.SaleID = s.ID
    WHERE sd.ProductCode = pd.ProductCode
    AND s.SaleDate BETWEEN ? AND ?
), 0) as AvgSalesRate
```

### Supplier Filtering
The system handles both:
- Suppliers with SupplierCode (alphanumeric)
- Suppliers with only ID (numeric)

The API automatically detects and uses the appropriate field.

### Stock Calculation
Stock remaining is calculated as:
```
Stock Remaining = Total Purchased - Total Sold
```

Both quantities are for the selected date range, allowing you to see the flow of inventory during that period.

## Usage Instructions

1. **Access the Report:**
   - Navigate to Purchase Reports Dashboard
   - Click on "Item-wise Purchase Report"

2. **Generate Report:**
   - Select date range using quick filters or custom dates
   - Optionally filter by category, item, supplier, or purchase type
   - Click "Generate Report" button

3. **Export/Print:**
   - Click "Export" for Excel download
   - Click "Print" for print-optimized view

## Database Tables Used

- `tblpurchases` - Purchase invoices
- `tblpurchases_details` - Purchase line items
- `tblsales` - Sales invoices (for sold qty calculation)
- `tblsales_details` - Sales line items (for sold qty/value)
- `tblitems` - Item master data
- `tblcategories` - Category information
- `tblbrands` - Brand information
- `tblunits` - Unit of measurement
- `tblsuppliers` - Supplier information

## Report Grouping

The report is **grouped by item** (ProductCode), showing:
- All purchases of that item in the date range
- All sales of that item in the date range
- Calculated stock position

This is different from a bill-wise report which would show individual purchase invoices.

## Color Coding

- **Purchase Value:** Red/Danger color (money going out)
- **Sold Value:** Green/Success color (money coming in)
- **Stock Value:** Yellow/Warning color (inventory investment)
- **Stock Remaining:** 
  - Green for positive stock
  - Yellow for zero stock
  - Red for negative stock

## Access Control

The report respects the existing session authentication:
- Requires active user session (`$_SESSION['uid']`)
- Redirects to logout if not authenticated
