kyawzo-ptcl

# SQL Reports

Query Delivery Orders from SQL Server and generate delivery reports for business analysis.

## Overview

This skill connects to a SQL Server database to extract and analyze delivery order data. It's designed for warehouse managers and logistics teams who need quick access to delivery statistics without writing SQL queries manually.

## Database Connection

- **Server:** LTEDP056\SQLEXPRESS
- **Port:** 1433
- **Database:** DeliveryDB
- **Driver:** ODBC Driver 17 for SQL Server

## Prerequisites

1. Python 3.8+ installed
2. pyodbc library installed: `pip install pyodbc`
3. ODBC Driver 17 for SQL Server installed on Windows
4. Network access to LTEDP056 server on port 1433

## Available Queries

### 1. summary_by_month
Returns monthly delivery summary with total orders, revenue, and delivery status breakdown.

**Output:** Table with Month, TotalOrders, TotalRevenue, Delivered, Pending, Cancelled columns

### 2. monthly_comparison
Compare delivery performance between two months.

**Input:** Month1 (YYYY-MM), Month2 (YYYY-MM)
**Output:** Side-by-side comparison table

### 3. quarterly_report
Generate comprehensive quarterly delivery report with trends.

**Input:** Year (YYYY), Quarter (1-4)
**Output:** Detailed report with weekly breakdown

### 4. summary_by_year
Annual delivery summary with year-over-year growth metrics.

**Input:** Year (YYYY)
**Output:** Annual statistics and growth indicators

## Usage

Run from command line:
```bash
python query.py summary_by_month
python query.py monthly_comparison 2025-01 2025-02
python query.py quarterly_report 2025 1
python query.py summary_by_year 2025
FILE:query.py
import pyodbc
import sys

conn_str = (
    'DRIVER={SQL Server};'
    'SERVER=LTEDP056\\SQLEXPRESS,1433;'
    'DATABASE=PDFExtraction;'
    'UID=ptcl-bot;PWD=ptcl-bot123'
)

def query_items_by_date(start_date=None, end_date=None):
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    
    if start_date and end_date:
        query = """
        SELECT h.DeliveryNo, h.ScheduledDeliveryDate, i.ItemNo, i.ItemCode, 
               i.ItemDescription, i.DeliveryQty, i.Unit, i.NetWeight, i.Batch
        FROM DeliveryHeaders h
        INNER JOIN DeliveryItems i ON h.DeliveryNo = i.DeliveryNo
        WHERE h.ScheduledDeliveryDate BETWEEN ? AND ?
        ORDER BY h.ScheduledDeliveryDate
        """
        cursor.execute(query, (start_date, end_date))
    else:
        query = """
        SELECT h.DeliveryNo, h.ScheduledDeliveryDate, i.ItemNo, i.ItemCode, 
               i.ItemDescription, i.DeliveryQty, i.Unit, i.NetWeight, i.Batch
        FROM DeliveryHeaders h
        INNER JOIN DeliveryItems i ON h.DeliveryNo = i.DeliveryNo
        ORDER BY h.ScheduledDeliveryDate
        """
        cursor.execute(query)
    
    cols = [c[0] for c in cursor.description]
    result = [" | ".join(cols), "-" * 120]
    for row in cursor.fetchall():
        result.append(" | ".join(str(x) for x in row))
    conn.close()
    return "\n".join(result)

def summary_by_day():
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    query = """
    SELECT CAST(h.ScheduledDeliveryDate AS DATE) AS Day, 
           COUNT(*) AS TotalDeliveries, SUM(i.DeliveryQty) AS TotalItems
    FROM DeliveryHeaders h
    INNER JOIN DeliveryItems i ON h.DeliveryNo = i.DeliveryNo
    GROUP BY CAST(h.ScheduledDeliveryDate AS DATE)
    ORDER BY Day
    """
    cursor.execute(query)
    cols = [c[0] for c in cursor.description]
    result = [" | ".join(cols), "-" * 60]
    for row in cursor.fetchall():
        result.append(" | ".join(str(x) for x in row))
    conn.close()
    return "\n".join(result)

def summary_by_month():
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    query = """
    SELECT YEAR(h.ScheduledDeliveryDate) AS Year, MONTH(h.ScheduledDeliveryDate) AS Month,
           COUNT(*) AS TotalDeliveries, SUM(i.DeliveryQty) AS TotalItems
    FROM DeliveryHeaders h
    INNER JOIN DeliveryItems i ON h.DeliveryNo = i.DeliveryNo
    GROUP BY YEAR(h.ScheduledDeliveryDate), MONTH(h.ScheduledDeliveryDate)
    ORDER BY Year, Month
    """
    cursor.execute(query)
    cols = [c[0] for c in cursor.description]
    result = [" | ".join(cols), "-" * 60]
    for row in cursor.fetchall():
        result.append(" | ".join(str(x) for x in row))
    conn.close()
    return "\n".join(result)

def summary_by_year():
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    query = """
    SELECT YEAR(h.ScheduledDeliveryDate) AS Year,
           COUNT(*) AS TotalDeliveries, SUM(i.DeliveryQty) AS TotalItems
    FROM DeliveryHeaders h
    INNER JOIN DeliveryItems i ON h.DeliveryNo = i.DeliveryNo
    GROUP BY YEAR(h.ScheduledDeliveryDate)
    ORDER BY Year
    """
    cursor.execute(query)
    cols = [c[0] for c in cursor.description]
    result = [" | ".join(cols), "-" * 60]
    for row in cursor.fetchall():
        result.append(" | ".join(str(x) for x in row))
    conn.close()
    return "\n".join(result)

if __name__ == "__main__":
    if len(sys.argv) > 1:
        arg = sys.argv[1].lower()
        if "day" in arg:
            print(summary_by_day())
        elif "month" in arg:
            print(summary_by_month())
        elif "year" in arg:
            print(summary_by_year())
        else:
            print(query_items_by_date())
FILE:skill.json
{
  "name": "sql-reports",
  "version": "1.0.0",
  "description": "Query Delivery Orders from SQL Server",
  "triggers": ["delivery report", "do report", "delivery summary", "delivery items"],
  "exec": {
    "command": "python",
    "args": ["{path}/query.py", "{input}"],
    "yield": true
  }
}