@clawhub-kyawzo-ptcl-14ebb1530c
Connects to SQL Server to retrieve and generate monthly, quarterly, and yearly delivery order reports with performance comparisons and growth metrics.
# 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
}
}