module-vendor-sales-report/.github/copilot-instructions.md

# AI Coding Instructions for Vendor Sales Report Module

## Project Overview
This is a Magento 2 module (`Shopkeeper_VendorSalesReport`) that generates monthly vendor sales reports with automated email delivery. The module provides both manual report generation via admin interface and automated monthly reports via cron.

## Architecture & Key Components

### Core Models
- **`Model/ReportGenerator.php`**: Handles report data generation using raw SQL queries joining `sales_order`, `sales_order_item`, `sales_order_address`, and product tables. Includes state abbreviation logic and cost fallback from order item to current product cost.
- **`Model/EmailSender.php`**: Manages email delivery using Magento's TransportBuilder with CSV attachments.
- **`Helper/Data.php`**: Configuration helper using Magento's scope config for settings like order status filters, email recipients, and subject templates.

### Admin Interface
- **Controller Structure**: `Controller/Adminhtml/Report/` with `Index.php` (main page), `Grid.php` (data grid), `Export.php` (CSV download).
- **Menu Integration**: Located under `Reports > Sales > Vendor Sales Report` in admin panel.
- **Configuration**: System config at `Stores > Configuration > Shopkeeper > Vendor Sales Report` with general and email settings.

### Automation
- **`Cron/GenerateMonthlyReport.php`**: Executes monthly on the 1st at 2:00 AM, generates previous month's report, and emails it.
- **Cron Schedule**: `0 2 1 * *` (crontab.xml configuration).

## Critical Patterns & Conventions

### Report Generation Logic
```php
// Always exclude configurable products from reports
AND oi.product_type != 'configurable'

// Cost fallback hierarchy: order item cost -> current product cost -> 0
COALESCE(NULLIF(oi.base_cost, 0), current_cost.value, 0)

// State abbreviation: only abbreviate if full name, leave 2-char codes unchanged
protected function abbreviateState($stateName) {
    if (strlen($stateName) == 2) return $stateName;
    return $this->stateAbbreviations[$stateName] ?? $stateName;
}
```

### CSV Export Format
- **Filename**: Always `FF_SAC_730_MCQUEEN.csv` (hardcoded in `ReportGenerator::getFilename()`)
- **Columns**: Date, Vendor Product number, Description, Vendor code, Vendor name, Dealer product number, Country, City, State, Zip code, Quantity, Cost
- **Date Format**: MM/DD/YYYY
- **Country**: "United States" for US orders, otherwise country code
- **State**: Abbreviated for US addresses only

### Configuration Access
```php
// Use helper for all config access - never access scopeConfig directly in controllers/models
$this->configHelper->isEnabled()
$this->configHelper->getOrderStatus() // Returns array
$this->configHelper->getEmailRecipients() // Returns array
$this->configHelper->getEmailSubject($month, $year) // Handles {{month}}/{{year}} replacement
```

### Email Templates
- **Template ID**: `vendorsalesreport_email_template`
- **Variables**: `subject`, `month`, `year`
- **Attachment**: CSV file with `text/csv` MIME type

## Development Workflow

### Module Installation
```bash
# Enable module
php bin/magento module:enable Shopkeeper_VendorSalesReport
php bin/magento setup:upgrade
php bin/magento setup:di:compile
php bin/magento setup:static-content:deploy -f
php bin/magento cache:flush
```

### Testing Cron Jobs
```bash
# Manual cron execution
php bin/magento cron:run --group default

# Check cron_schedule table
SELECT * FROM cron_schedule WHERE job_code = 'shopkeeper_vendorsalesreport_monthly' ORDER BY scheduled_at DESC;
```

### Debugging
- **Logs**: Check `var/log/system.log` for module-specific messages
- **SQL Queries**: Report generation uses complex joins - examine `ReportGenerator::generateReportData()` for table relationships
- **Email Issues**: Verify SMTP configuration and check Magento's email settings

## Common Pitfalls

### Data Issues
- **Empty Reports**: Check order status filter (default: 'complete') and date ranges
- **Missing Costs**: Ensure products have cost attribute set, module falls back gracefully
- **State Abbreviations**: Only US states are abbreviated; international addresses remain unchanged

### Configuration
- **Email Recipients**: Must be comma-separated, validated before sending
- **Order Status**: Stored as comma-separated string, converted to array by helper
- **Subject Templates**: Support `{{month}}` and `{{year}}` placeholders for dynamic email subjects (e.g., "Monthly Report - {{month}} {{year}}")

### Performance
- **Raw SQL**: Used for performance with large datasets - avoid ORM for report queries
- **Memory Usage**: CSV generation uses `php://temp` stream to handle large reports
- **Cron Timing**: Runs early morning to avoid peak hours

## Extension Points

### Adding New Report Columns
1. Modify SQL query in `ReportGenerator::generateReportData()`
2. Update CSV headers in `ReportGenerator::generateCsv()`
3. Ensure column exclusion logic handles new fields appropriately

### Custom Email Logic
1. Extend `EmailSender` class
2. Modify email template variables in `email_templates.xml`
3. Update template file `view/adminhtml/email/report.html`

### Additional Filters
1. Add new config fields in `system.xml`
2. Update `Helper/Data.php` with new getter methods
3. Modify SQL WHERE clauses in `ReportGenerator::generateReportData()`

## File Structure Reference
```
etc/
├── module.xml              # Module dependencies
├── config.xml              # Default configuration values
├── crontab.xml             # Cron job definitions
├── email_templates.xml     # Email template registration
└── adminhtml/
    ├── menu.xml            # Admin menu placement
    ├── routes.xml          # URL routing
    └── system.xml          # Configuration UI

Model/                      # Business logic
├── ReportGenerator.php     # Core report generation
└── EmailSender.php         # Email delivery

Controller/Adminhtml/Report/ # Admin interface
├── Index.php              # Main report page
├── Grid.php               # Data grid (AJAX)
└── Export.php             # CSV download
```</content>
<parameter name="filePath">t:\Files\Documents\BIZ\Clients\McQueen\Repos\VendorSalesReport\.github\copilot-instructions.md