| import sqlite3 |
| import uuid |
|
|
| def generate_uuid(): |
| return str(uuid.uuid4()) |
|
|
| def insert_mock_data(): |
| conn = sqlite3.connect('hello_earth_data_2.db') |
| cursor = conn.cursor() |
|
|
| mock_data = [] |
|
|
| |
| project_1_id = generate_uuid() |
| deliverable_1_1_id = generate_uuid() |
| deliverable_1_2_id = generate_uuid() |
| mock_data.append({ |
| "project": { |
| "project_id": project_1_id, |
| "title": "Mangrove Restoration", |
| "status": "started", |
| "detail_information": "Restoring coastal ecosystems using mangrove plantations.", |
| "start_date": "2025-01-01", |
| "end_date": "2025-12-31" |
| }, |
| "deliverables": [ |
| { |
| "deliverable_id": deliverable_1_1_id, |
| "title": "Area Assessment", |
| "status": "done", |
| "risk_level": "green", |
| "risk_level_rationale": "No environmental threats detected.", |
| "start_date": "2025-01-05", |
| "end_date": "2025-03-01" |
| }, |
| { |
| "deliverable_id": deliverable_1_2_id, |
| "title": "Planting Phase", |
| "status": "ongoing", |
| "risk_level": "yellow", |
| "risk_level_rationale": "Tide schedule interference.", |
| "start_date": "2025-03-15", |
| "end_date": "2025-08-30" |
| } |
| ], |
| "budgets": [ |
| { |
| "id": generate_uuid(), |
| "deliverable_id": deliverable_1_1_id, |
| "wage": 1000.0, |
| "materials": 1500.0, |
| "tools_equipment": 500.0, |
| "services": 800.0, |
| "misc": 100.0 |
| }, |
| { |
| "id": generate_uuid(), |
| "deliverable_id": deliverable_1_2_id, |
| "wage": 1200.0, |
| "materials": 2000.0, |
| "tools_equipment": 700.0, |
| "services": 1000.0, |
| "misc": 150.0 |
| } |
| ], |
| "expenses": [ |
| { |
| "expense_id": generate_uuid(), |
| "associated_deliverable_id": deliverable_1_1_id, |
| "seller_name": "EcoTools Ltd.", |
| "seller_address": "101 Earth Ave", |
| "seller_phone_number": "0811111111", |
| "buyer_name": "Mangrove Org", |
| "buyer_address": "900 Forest Blvd", |
| "transaction_date": "2025-01-20", |
| "total_payment_amount": 1800.0, |
| "expense_description": "Survey equipment rental", |
| "status": "approved" |
| }, |
| { |
| "expense_id": generate_uuid(), |
| "associated_deliverable_id": deliverable_1_1_id, |
| "seller_name": "Green Supplies Co.", |
| "seller_address": "202 Eco Rd", |
| "seller_phone_number": "0822222222", |
| "buyer_name": "Mangrove Org", |
| "buyer_address": "900 Forest Blvd", |
| "transaction_date": "2025-02-10", |
| "total_payment_amount": 1200.0, |
| "expense_description": "Protective gear and safety kits", |
| "status": "approved" |
| }, |
| { |
| "expense_id": generate_uuid(), |
| "associated_deliverable_id": deliverable_1_1_id, |
| "seller_name": "FieldTech Services", |
| "seller_address": "303 Survey Lane", |
| "seller_phone_number": "0833333333", |
| "buyer_name": "Mangrove Org", |
| "buyer_address": "900 Forest Blvd", |
| "transaction_date": "2025-02-25", |
| "total_payment_amount": 2000.0, |
| "expense_description": "Field technician support", |
| "status": "approved" |
| } |
|
|
| ] |
| }) |
|
|
| |
| project_2_id = generate_uuid() |
| deliverable_2_1_id = generate_uuid() |
| deliverable_2_2_id = generate_uuid() |
| mock_data.append({ |
| "project": { |
| "project_id": project_2_id, |
| "title": "Urban Tree Planting", |
| "status": "started", |
| "detail_information": "Planting trees across the city for shade and air quality.", |
| "start_date": "2025-02-01", |
| "end_date": "2025-11-30" |
| }, |
| "deliverables": [ |
| { |
| "deliverable_id": deliverable_2_1_id, |
| "title": "Community Engagement", |
| "status": "done", |
| "risk_level": "green", |
| "risk_level_rationale": "High public interest and participation.", |
| "start_date": "2025-02-05", |
| "end_date": "2025-04-01" |
| } |
| ], |
| "budgets": [ |
| { |
| "id": generate_uuid(), |
| "deliverable_id": deliverable_2_1_id, |
| "wage": 1500.0, |
| "materials": 1000.0, |
| "tools_equipment": 300.0, |
| "services": 500.0, |
| "misc": 50.0 |
| } |
| ], |
| "expenses": [] |
| }) |
|
|
| |
| project_3_id = generate_uuid() |
| deliverable_3_1_id = generate_uuid() |
| deliverable_3_2_id = generate_uuid() |
| mock_data.append({ |
| "project": { |
| "project_id": project_3_id, |
| "title": "River Clean-up Campaign", |
| "status": "started", |
| "detail_information": "Mobilizing volunteers to clean plastic from major rivers.", |
| "start_date": "2025-03-01", |
| "end_date": "2025-09-30" |
| }, |
| "deliverables": [ |
| { |
| "deliverable_id": deliverable_3_1_id, |
| "title": "Equipment Procurement", |
| "status": "done", |
| "risk_level": "green", |
| "risk_level_rationale": "Procurement completed on time.", |
| "start_date": "2025-03-05", |
| "end_date": "2025-04-01" |
| }, |
| { |
| "deliverable_id": deliverable_3_2_id, |
| "title": "Volunteer Training", |
| "status": "ongoing", |
| "risk_level": "yellow", |
| "risk_level_rationale": "Training attendance dropped by 10%.", |
| "start_date": "2025-04-10", |
| "end_date": "2025-06-30" |
| } |
| ], |
| "budgets": [], |
| "expenses": [] |
| }) |
|
|
| |
| for item in mock_data: |
| project = item["project"] |
| cursor.execute(''' |
| INSERT INTO Project (project_id, title, status, detail_information, start_date, end_date) |
| VALUES (?, ?, ?, ?, ?, ?) |
| ''', ( |
| project["project_id"], project["title"], project["status"], |
| project["detail_information"], project["start_date"], project["end_date"] |
| )) |
|
|
| for d in item.get("deliverables", []): |
| cursor.execute(''' |
| INSERT INTO Deliverable (deliverable_id, project_id, title, status, risk_level, |
| risk_level_rationale, start_date, end_date) |
| VALUES (?, ?, ?, ?, ?, ?, ?, ?) |
| ''', ( |
| d["deliverable_id"], project["project_id"], d["title"], d["status"], |
| d["risk_level"], d["risk_level_rationale"], d["start_date"], d["end_date"] |
| )) |
|
|
| for b in item.get("budgets", []): |
| cursor.execute(''' |
| INSERT INTO Deliverable_Budget (id, deliverable_id, wage, materials, tools_equipment, services, misc) |
| VALUES (?, ?, ?, ?, ?, ?, ?) |
| ''', ( |
| b["id"], b["deliverable_id"], b["wage"], b["materials"], |
| b["tools_equipment"], b["services"], b["misc"] |
| )) |
|
|
| for e in item.get("expenses", []): |
| cursor.execute(''' |
| INSERT INTO Expense (expense_id, associated_deliverable_id, seller_name, seller_address, seller_phone_number, |
| buyer_name, buyer_address, transaction_date, total_payment_amount, expense_description, status) |
| VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) |
| ''', ( |
| e["expense_id"], e["associated_deliverable_id"], e["seller_name"], e["seller_address"], |
| e["seller_phone_number"], e["buyer_name"], e["buyer_address"], |
| e["transaction_date"], e["total_payment_amount"], e["expense_description"], e["status"] |
| )) |
|
|
| conn.commit() |
| conn.close() |
| print("✅ Multiple projects inserted with UUIDs.") |
|
|
| if __name__ == '__main__': |
| insert_mock_data() |
|
|
