# HSN/SAC Wise Purchase and Sales GST Reports

## Overview
Complete implementation of HSN/SAC code-wise Purchase and Sales GST Reports with filtering, export, and print capabilities.

## Created Date
November 8, 2025

## Features

### HSN Wise Purchase Report
- **Location**: `PurchaseReports/hsn-wise-purchase-report.php`
- **URL**: `http://localhost/customized/UZRS-inventory/UZRS-inventory/PurchaseReports/hsn-wise-purchase-report.php`

### HSN Wise Sales Report
- **Location**: `SalesReports/hsn-wise-sales-report.php`
- **URL**: `http://localhost/customized/UZRS-inventory/UZRS-inventory/SalesReports/hsn-wise-sales-report.php`

## Key Features

### 1. Data Aggregation by HSN/SAC Code
- Groups purchase/sales transactions by HSN/SAC code
- Shows consolidated data for each unique HSN code
- Displays item names associated with each HSN code
- Calculates totals for quantity, taxable value, and taxes

### 2. Advanced Filtering
- **Date Range Filters**: 
  - Today, Yesterday
  - This Week, Last Week
  - This Month, Last Month
  - This Year
  - Custom Date Range
  
- **Additional Filters (Purchase)**:
  - Supplier
  - Purchase Type
  - Specific HSN/SAC Code
  
- **Additional Filters (Sales)**:
  - Customer
  - Sales Type
  - Specific HSN/SAC Code

### 3. Summary Dashboard
Eight summary cards displaying:
1. Total Purchase/Sales Value
2. Total Taxable Amount
3. Total CGST Amount
4. Total SGST Amount
5. Total IGST Amount
6. Total Tax Amount
7. Total Quantity
8. Unique HSN Code Count

### 4. Detailed HSN Table
Columns:
- S.No
- HSN/SAC Code (highlighted in purple)
- Item Names (truncated with ellipsis, full text on hover)
- Transaction Count
- Total Quantity
- Taxable Value
- CGST Amount
- SGST Amount
- IGST Amount
- Total Tax
- Total Amount

### 5. Export to Excel/CSV
- Exports complete report data
- Includes all filters and date range information
- Formatted for easy analysis in spreadsheet applications

### 6. Print Functionality
- Optimized print layout (A4 Landscape)
- Includes company information and logo
- Shows all filters applied
- Professional formatting for physical records

### 7. Responsive Design
- Mobile-friendly interface
- Adaptive grid layout for summary cards
- Horizontal scroll for table on small screens

## Files Created

### Purchase Reports (5 files)
```
PurchaseReports/
├── hsn-wise-purchase-report.php       # Main report page
├── print-hsn-wise-purchase.php        # Print view
├── api/
│   └── get-hsn-wise-purchases.php     # Data API endpoint
├── css/
│   └── hsn-wise-purchase-report.css   # Styling
└── js/
    └── hsn-wise-purchase-report.js    # Client-side logic
```

### Sales Reports (5 files)
```
SalesReports/
├── hsn-wise-sales-report.php          # Main report page
├── print-hsn-wise-sales.php           # Print view
├── api/
│   └── get-hsn-wise-sales.php         # Data API endpoint
├── css/
│   └── hsn-wise-sales-report.css      # Styling
└── js/
    └── hsn-wise-sales-report.js       # Client-side logic
```

## Database Tables Used

### For Purchase Reports:
- `tblpurchases` - Purchase transactions
- `tblpurchases_gst_details` - GST line items with HSN codes
- `tblitems` - Product master for item names
- `tblsuppliers` - Supplier information
- `tblpurchase_type_config` - Purchase type configuration

### For Sales Reports:
- `tblsales` - Sales transactions
- `tblsales_gst_details` - GST line items with HSN codes
- `tblitems` - Product master for item names
- `tblcustomers` - Customer information
- `tblsales_type_config` - Sales type configuration

## HSN Code Field
- Field Name: `HSNCode` (in GST details tables)
- Handles NULL/Empty values by showing "NOT SPECIFIED"
- Links to `tblitems.HSN` for product master data

## API Endpoints

### Purchase API
**Endpoint**: `PurchaseReports/api/get-hsn-wise-purchases.php`

**Parameters**:
- `startDate` (required): YYYY-MM-DD format
- `endDate` (required): YYYY-MM-DD format
- `supplierId` (optional): Supplier code
- `purchaseType` (optional): Purchase type name
- `hsnCode` (optional): Specific HSN/SAC code

**Response**:
```json
{
  "success": true,
  "hsnData": [
    {
      "HSNCode": "1234",
      "TransactionCount": 10,
      "TotalQuantity": "100.00",
      "TaxableAmount": "10000.00",
      "CGSTAmount": "900.00",
      "SGSTAmount": "900.00",
      "IGSTAmount": "0.00",
      "TotalTax": "1800.00",
      "TotalAmount": "11800.00",
      "AvgTaxRate": "18.00",
      "TaxType": "INTRA",
      "ItemNames": "Item 1, Item 2, Item 3"
    }
  ],
  "summary": {
    "totalPurchases": "50000.00",
    "taxableAmount": "45000.00",
    "cgstAmount": "4050.00",
    "sgstAmount": "4050.00",
    "igstAmount": "0.00",
    "totalTax": "8100.00",
    "totalQuantity": "500.00",
    "hsnCount": 5,
    "transactionCount": 50
  },
  "hsnList": ["1234", "5678", "9012"],
  "startDate": "2024-11-01",
  "endDate": "2024-11-30",
  "supplierInfo": {...},
  "purchaseTypeInfo": "..."
}
```

### Sales API
**Endpoint**: `SalesReports/api/get-hsn-wise-sales.php`

**Parameters**:
- `startDate` (required): YYYY-MM-DD format
- `endDate` (required): YYYY-MM-DD format
- `customerId` (optional): Customer code
- `salesType` (optional): Sales type name
- `hsnCode` (optional): Specific HSN/SAC code

**Response**: Similar structure to Purchase API

## Design Highlights

### Color Scheme
- Primary Accent: Purple (`#6f42c1`) - Represents HSN focus
- Success: Teal (`#1cc88a`) - For positive amounts
- Borders: Light gray (`#e3e6f0`)
- Text: Dark (`#5a5c69`)

### Visual Features
- HSN codes displayed in bold purple
- Item names truncated with ellipsis, expandable on hover
- Even/odd row striping for readability
- Gradient header backgrounds
- Color-coded summary cards

### User Experience
- Select2 dropdowns for enhanced filtering
- Quick date filters for common periods
- Auto-loading of HSN codes in dropdown
- Instant CSV export
- One-click print in new window

## Integration Steps

### 1. Add Links to Report Dashboards

**PurchaseReports/index.php** - Add this card:
```php
<div class="col-xl-3 col-md-6 mb-4">
    <a href="hsn-wise-purchase-report.php" class="text-decoration-none">
        <div class="card border-left-purple shadow h-100 py-2">
            <div class="card-body">
                <div class="row no-gutters align-items-center">
                    <div class="col mr-2">
                        <div class="text-xs font-weight-bold text-purple text-uppercase mb-1">
                            HSN Wise Purchase Report
                        </div>
                        <div class="h6 mb-0 text-gray-800">View GST by HSN/SAC Code</div>
                    </div>
                    <div class="col-auto">
                        <i class="fas fa-tags fa-2x text-purple"></i>
                    </div>
                </div>
            </div>
        </div>
    </a>
</div>
```

**SalesReports/index.php** - Add this card:
```php
<div class="col-xl-3 col-md-6 mb-4">
    <a href="hsn-wise-sales-report.php" class="text-decoration-none">
        <div class="card border-left-purple shadow h-100 py-2">
            <div class="card-body">
                <div class="row no-gutters align-items-center">
                    <div class="col mr-2">
                        <div class="text-xs font-weight-bold text-purple text-uppercase mb-1">
                            HSN Wise Sales Report
                        </div>
                        <div class="h6 mb-0 text-gray-800">View GST by HSN/SAC Code</div>
                    </div>
                    <div class="col-auto">
                        <i class="fas fa-tags fa-2x text-purple"></i>
                    </div>
                </div>
            </div>
        </div>
    </a>
</div>
```

### 2. CSS for Purple Theme (if needed in dashboard)
```css
.border-left-purple {
    border-left: 0.25rem solid #6f42c1 !important;
}
.text-purple {
    color: #6f42c1 !important;
}
```

## Usage Instructions

### Accessing the Reports
1. Navigate to Purchase Reports dashboard or Sales Reports dashboard
2. Click on "HSN Wise Purchase Report" or "HSN Wise Sales Report"
3. Select date range using quick filters or custom dates
4. (Optional) Apply additional filters for Supplier/Customer, Type, or specific HSN code
5. Click "Generate Report" to load data

### Exporting Data
1. Generate the report first
2. Click "Export" button
3. CSV file will download automatically with all report data

### Printing
1. Generate the report first
2. Click "Print" button
3. New window opens with print-optimized layout
4. Use browser's print function (Ctrl+P)
5. Select printer or Save as PDF

## Technical Notes

### Dependencies
- jQuery 3.x
- Bootstrap 4.x
- Select2 4.1.0
- Font Awesome 5.x

### Browser Compatibility
- Chrome 90+
- Firefox 88+
- Edge 90+
- Safari 14+

### Performance
- Queries use proper indexing on date fields
- GROUP BY optimization for HSN aggregation
- Pagination not implemented (assumes reasonable data volume)
- Consider adding pagination if HSN count exceeds 100

### Security
- Session-based authentication required
- SQL injection prevention via prepared statements
- XSS protection via htmlspecialchars()
- CSRF protection should be added for production

## Future Enhancements
1. Add pagination for large datasets
2. Add drill-down to view transactions for specific HSN
3. Add comparison mode (e.g., month-over-month)
4. Add graphical visualizations (charts)
5. Add email/schedule report functionality
6. Add PDF export option
7. Add HSN rate analysis (track tax rate changes)
8. Add HSN-wise profit analysis (for sales)

## Testing Checklist
- [ ] Reports load without errors
- [ ] Date filters work correctly
- [ ] Supplier/Customer filters work
- [ ] HSN code filter works
- [ ] Summary totals match detail totals
- [ ] Export creates valid CSV
- [ ] Print view displays correctly
- [ ] Mobile responsive layout works
- [ ] Empty data handled gracefully
- [ ] NULL HSN codes shown as "NOT SPECIFIED"

## Support
For issues or questions, refer to:
- Database: uzrs-inventorydb
- MySQL: C:\wamp64\bin\mysql\mysql8.0.21
- Root access (no password)

## Version History
- **v1.0** (Nov 8, 2025): Initial implementation with complete HSN-wise reporting for Purchase and Sales

---
**Implementation by**: AI Assistant
**Date**: November 8, 2025
**Status**: ✅ Complete and Ready for Testing
