1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
| -- 从旧系统同步发票信息到新系统的SQL脚本
| -- 同步旧系统的发票数据到新系统，避免重复同步
|
| -- 方案1: 使用INSERT IGNORE插入新数据
| INSERT IGNORE INTO sys_invoice (
| legacy_invoice_id,
| legacy_service_order_id,
| invoice_type,
| invoice_name,
| invoice_money,
| invoice_remarks,
| company_address,
| company_bank,
| company_bank_no,
| zip_code,
| mail_address,
| contact_name,
| contact_phone,
| contact_email,
| status,
| invoice_no,
| invoice_url,
| apply_time,
| audit_time,
| audit_remarks,
| sync_status
| )
| SELECT
| i.InvoiceID as legacy_invoice_id,
| i.ServiceOrderIDPK as legacy_service_order_id,
| CASE
| WHEN i.InvoiceType = 1 THEN 1 -- 个人发票
| WHEN i.InvoiceType = 2 THEN 2 -- 企业发票
| ELSE 1 -- 默认个人发票
| END as invoice_type,
| i.InvoiceName as invoice_name,
| CAST(i.InvoiceMoney AS DECIMAL(10,2)) as invoice_money,
| i.InvoiceMakeout as invoice_remarks,
| i.InvoiceCompanyAdd as company_address,
| i.InvoiceCompanyBank as company_bank,
| i.InvoiceCompanyBankNo as company_bank_no,
| i.InvoiceZipCode as zip_code,
| i.Invoice_strAdd as mail_address,
| i.Invoice_strName as contact_name,
| i.Invoice_strPhone as contact_phone,
| i.Invoice_strEmail as contact_email,
| CASE
| WHEN i.AuditStatus = 1 THEN 1 -- 已通过
| WHEN i.AuditStatus = 2 THEN 2 -- 已驳回
| ELSE 0 -- 待审核
| END as status,
| i.InvoiceNo as invoice_no,
| COALESCE(i.InvoiceURL, i.EleCloud_PDF) as invoice_url,
| i.ApplicationTime as apply_time,
| i.AuditTime as audit_time,
| i.AuditMakeout as audit_remarks,
| 1 as sync_status -- 标记为已同步
| FROM InvoiceData i
| WHERE i.InvoiceID NOT IN (
| SELECT legacy_invoice_id
| FROM sys_invoice
| WHERE legacy_invoice_id IS NOT NULL
| );
|
| -- 方案2: 使用LEFT JOIN确保只插入不存在的记录
| -- INSERT INTO sys_invoice (
| -- legacy_invoice_id,
| -- legacy_service_order_id,
| -- invoice_type,
| -- invoice_name,
| -- invoice_money,
| -- invoice_remarks,
| -- company_address,
| -- company_bank,
| -- company_bank_no,
| -- zip_code,
| -- mail_address,
| -- contact_name,
| -- contact_phone,
| -- contact_email,
| -- status,
| -- invoice_no,
| -- invoice_url,
| -- apply_time,
| -- audit_time,
| -- audit_remarks,
| -- sync_status
| -- )
| -- SELECT
| -- i.InvoiceID,
| -- i.ServiceOrderIDPK,
| -- CASE
| -- WHEN i.InvoiceType = 1 THEN 1
| -- WHEN i.InvoiceType = 2 THEN 2
| -- ELSE 1
| -- END,
| -- i.InvoiceName,
| -- CAST(i.InvoiceMoney AS DECIMAL(10,2)),
| -- i.InvoiceMakeout,
| -- i.InvoiceCompanyAdd,
| -- i.InvoiceCompanyBank,
| -- i.InvoiceCompanyBankNo,
| -- i.InvoiceZipCode,
| -- i.Invoice_strAdd,
| -- i.Invoice_strName,
| -- i.Invoice_strPhone,
| -- i.Invoice_strEmail,
| -- CASE
| -- WHEN i.AuditStatus = 1 THEN 1
| -- WHEN i.AuditStatus = 2 THEN 2
| -- ELSE 0
| -- END,
| -- i.InvoiceNo,
| -- COALESCE(i.InvoiceURL, i.EleCloud_PDF),
| -- i.ApplicationTime,
| -- i.AuditTime,
| -- i.AuditMakeout,
| -- 1
| -- FROM InvoiceData i
| -- LEFT JOIN sys_invoice si ON i.InvoiceID = si.legacy_invoice_id
| -- WHERE si.legacy_invoice_id IS NULL;
|
| -- 更新统计信息
| ANALYZE TABLE sys_invoice;
|
|
