ERP/init_customer_orders.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
初始化客户订单数据
根据图片中的数据填充客户订单表
"""

import sqlite3
import os
from datetime import datetime, timezone, timedelta

# 数据库路径
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, 'server', 'data.db')

def get_beijing_time():
    """获取北京时间（UTC+8）的ISO格式字符串"""
    beijing_tz = timezone(timedelta(hours=8))
    return datetime.now(beijing_tz).isoformat()

# 从图片中提取的订单数据 - 客户：易泰勒
orders_data = [
    # 2025/4/28 - CGDD001695
    {'order_date': '2025-04-28', 'order_no': 'CGDD001695', 'customer_name': '易泰勒', 'material': 'ETAP05\n基站-5.0\nETAP05', 'quantity': 950, 'unit_price': 315.19},
    {'order_date': '2025-04-28', 'order_no': 'CGDD001695', 'customer_name': '易泰勒', 'material': 'WD1MK0SMD0551\n蓝牙模块\nPCBCOMPONENT_1_-_DUPLICATE', 'quantity': 950, 'unit_price': 1.3},
    {'order_date': '2025-04-28', 'order_no': 'CGDD001695', 'customer_name': '易泰勒', 'material': 'WA0000000040\nPCBA\nCH6121-MODULE-V14', 'quantity': 4750, 'unit_price': 8.05},
    
    # 2025/9/4 - CGDD002429
    {'order_date': '2025-09-04', 'order_no': 'CGDD002429', 'customer_name': '易泰勒', 'material': 'ETAP05\n基站-5.0\nETAP05', 'quantity': 1500, 'unit_price': 315.19},
    {'order_date': '2025-09-04', 'order_no': 'CGDD002429', 'customer_name': '易泰勒', 'material': 'WD1MK0SMD0551\n蓝牙模块\nPCBCOMPONENT_1_-_DUPLICATE', 'quantity': 1500, 'unit_price': 1.3},
    {'order_date': '2025-09-04', 'order_no': 'CGDD002429', 'customer_name': '易泰勒', 'material': 'WA0000000040\nPCBA\nCH6121-MODULE-V14', 'quantity': 7500, 'unit_price': 8.05},
    {'order_date': '2025-09-04', 'order_no': 'CGDD002429', 'customer_name': '易泰勒', 'material': 'AP-ET010\n基站-5.0\nETAP05', 'quantity': 500, 'unit_price': 315.19},
    {'order_date': '2025-09-04', 'order_no': 'CGDD002429', 'customer_name': '易泰勒', 'material': 'WD1MK0SMD0551\n蓝牙模块\nPCBCOMPONENT_1_-_DUPLICATE', 'quantity': 500, 'unit_price': 1.3},
    {'order_date': '2025-09-04', 'order_no': 'CGDD002429', 'customer_name': '易泰勒', 'material': 'WA0000000040\nPCBA\nCH6121-MODULE-V14', 'quantity': 2500, 'unit_price': 8.05},
    
    # 2025/10/23 - CGDD002878
    {'order_date': '2025-10-23', 'order_no': 'CGDD002878', 'customer_name': '易泰勒', 'material': 'AP-DZ006\n智能灯条基站\nETAP05-D1', 'quantity': 4000, 'unit_price': 239.2},
    {'order_date': '2025-10-23', 'order_no': 'CGDD002878', 'customer_name': '易泰勒', 'material': 'WD1MK0SMD0551\n蓝牙模块\nPCBCOMPONENT_1_-_DUPLICATE', 'quantity': 12000, 'unit_price': 1.1},
    
    # 2025/11/13 - CGDD003037
    {'order_date': '2025-11-13', 'order_no': 'CGDD003037', 'customer_name': '易泰勒', 'material': 'AP-DZ009\n智能灯条基站\nETAP05-D1', 'quantity': 500, 'unit_price': 229.61},
    {'order_date': '2025-11-13', 'order_no': 'CGDD003037', 'customer_name': '易泰勒', 'material': 'WD1MK0SMD0551\n蓝牙模块\nPCBCOMPONENT_1_-_DUPLICAT', 'quantity': 1500, 'unit_price': 1.1},
    {'order_date': '2025-11-13', 'order_no': 'CGDD003037', 'customer_name': '易泰勒', 'material': 'AP-DZ006\n智能灯条基站\nETAP05-D1', 'quantity': 4000, 'unit_price': 239.2},
    {'order_date': '2025-11-13', 'order_no': 'CGDD003037', 'customer_name': '易泰勒', 'material': 'WD1MK0SMD0551\n蓝牙模块\nPCBCOMPONENT_1_-_DUPLICATE', 'quantity': 12000, 'unit_price': 1.1},
]

def init_orders():
    """初始化客户订单数据"""
    if not os.path.exists(DB_PATH):
        print(f"错误: 数据库文件不存在: {DB_PATH}")
        return
    
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    
    # 检查表是否存在
    c.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='customer_orders'")
    if not c.fetchone():
        print("错误: customer_orders 表不存在，请先运行服务器以创建表")
        conn.close()
        return
    
    # 清空现有数据（可选）
    c.execute('DELETE FROM customer_orders')
    print("已清空现有订单数据")
    
    # 插入新数据
    now = get_beijing_time()
    inserted_count = 0
    
    for order in orders_data:
        try:
            c.execute('''INSERT INTO customer_orders(
                order_date, order_no, customer_name, material, quantity, unit_price, 
                created_by, created_at, updated_at
            ) VALUES(?,?,?,?,?,?,?,?,?)''', (
                order['order_date'],
                order['order_no'],
                order['customer_name'],
                order['material'],
                order['quantity'],
                order['unit_price'],
                'admin',  # 创建者
                now,
                now
            ))
            inserted_count += 1
        except Exception as e:
            print(f"插入订单失败: {order['order_no']} - {e}")
    
    conn.commit()
    conn.close()
    
    print(f"成功插入 {inserted_count} 条订单数据")

if __name__ == '__main__':
    init_orders()