# -*- coding: utf-8 -*-
"""按用户洞察修正:粘贴/删除时数据本身正确,关掉触发器直接写;只有编辑(改输入)才触发计算。
①计算引擎:开关表+触发器 WHEN 开关=0+刷新图号行聚合方法
②图号粘贴:箱号行/图号行 INSERT 直接抄源行的聚合值(不再写 0);管线开关=1 包裹(零重算)
③箱号粘贴:开关=1 包裹,管尾刷一次图号行聚合
④删除管线:开关=1 包裹,管尾刷一次图号行聚合"""
import io
ROOT = r"C:\Users\099978\Documents\SharpDevelop Projects\成套报价软件\成套报价软件"
n = 0
def edit(path, pairs, tag):
global n
s = io.open(path, encoding="utf-8-sig").read()
for old, new in pairs:
if old not in s:
if new in s:
print(tag, "已有(跳过):", old[:40].replace("\n", " "))
continue
raise AssertionError(tag + " 锚点缺失: " + old[:80])
s = s.replace(old, new, 1)
n += 1
io.open(path, "w", encoding="utf-8-sig").write(s)
print(tag, "ok", n, "累计")
# ============ ① 计算引擎 ============
p = ROOT + r"\计算引擎.cs"
edit(p, [
# 开关条件常量
(''' /// 触发器名前缀(重建时按前缀全清)。
public const string 触发器前缀 = "trgjs_";''',
''' /// 触发器名前缀(重建时按前缀全清)。
public const string 触发器前缀 = "trgjs_";
/// 触发器总开关条件:粘贴/删除批量写时置 开关=1(数据自带正确值,无需触发器计算)。
public const string 开关条件 = "(SELECT 开关 FROM _触发器开关 WHERE id=1)=0";'''),
# 触发器加 WHEN 开关=0(元件 u/i/d)
(''' Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(元件表) + "_u AFTER UPDATE OF " + of + " ON \\"" +
元件表 + "\\" FOR EACH ROW BEGIN UPDATE \\"" + 元件表 + "\\" SET " + selfSet +''',
''' Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(元件表) + "_u AFTER UPDATE OF " + of + " ON \\"" +
元件表 + "\\" FOR EACH ROW WHEN " + 开关条件 + " BEGIN UPDATE \\"" + 元件表 + "\\" SET " + selfSet +'''),
(''' Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(元件表) + "_i AFTER INSERT ON \\"" +
元件表 + "\\" FOR EACH ROW BEGIN UPDATE \\"" + 元件表 + "\\" SET " + selfSet +''',
''' Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(元件表) + "_i AFTER INSERT ON \\"" +
元件表 + "\\" FOR EACH ROW WHEN " + 开关条件 + " BEGIN UPDATE \\"" + 元件表 + "\\" SET " + selfSet +'''),
(''' Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(元件表) + "_d AFTER DELETE ON \\"" +
元件表 + "\\" FOR EACH ROW BEGIN " + 刷箱号 + 刷图号 + "END;");''',
''' Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(元件表) + "_d AFTER DELETE ON \\"" +
元件表 + "\\" FOR EACH ROW WHEN " + 开关条件 + " BEGIN " + 刷箱号 + 刷图号 + "END;");'''),
# 箱号行级 + i/d
(''' "_u AFTER UPDATE OF " + of + " ON \\"" + 箱号表 + "\\" FOR EACH ROW WHEN NEW.\\"内部ID\\"='" +
箱号ID.Replace("'", "''") + "' BEGIN UPDATE \\"" + 箱号表 + "\\" SET " + xhSetNew +''',
''' "_u AFTER UPDATE OF " + of + " ON \\"" + 箱号表 + "\\" FOR EACH ROW WHEN NEW.\\"内部ID\\"='" +
箱号ID.Replace("'", "''") + "' AND " + 开关条件 + " BEGIN UPDATE \\"" + 箱号表 + "\\" SET " + xhSetNew +'''),
(''' Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(箱号表) + "_i AFTER INSERT ON \\"" +
箱号表 + "\\" FOR EACH ROW BEGIN " + 刷图号 + "END;");
Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(箱号表) + "_d AFTER DELETE ON \\"" +
箱号表 + "\\" FOR EACH ROW BEGIN " + 刷图号 + "END;");''',
''' Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(箱号表) + "_i AFTER INSERT ON \\"" +
箱号表 + "\\" FOR EACH ROW WHEN " + 开关条件 + " BEGIN " + 刷图号 + "END;");
Exec(conn, tx, "CREATE TRIGGER " + 触发器前缀 + Safe(箱号表) + "_d AFTER DELETE ON \\"" +
箱号表 + "\\" FOR EACH ROW WHEN " + 开关条件 + " BEGIN " + 刷图号 + "END;");'''),
# 确保开关表(重建时)+开关设+刷新图号行聚合
(''' using (var cmd = new System.Data.SQLite.SQLiteCommand(
"CREATE TABLE IF NOT EXISTS _触发器开关 (id INTEGER PRIMARY KEY, 开关 INTEGER DEFAULT 0);", conn, tx))
cmd.ExecuteNonQuery();
Exec(conn, tx, "INSERT OR IGNORE INTO _触发器开关 (id, 开关) VALUES (1, 0);");
var old = new List();''',
''' var old = new List();'''),
(''' /// 新表(管线内建表后)即时补触发器——只处理该表自身。''',
''' /// 确保开关表存在(项目库;老库首次自动建)。
public static void 确保开关表(System.Data.SQLite.SQLiteConnection conn, System.Data.SQLite.SQLiteTransaction tx)
{
Exec(conn, tx, "CREATE TABLE IF NOT EXISTS _触发器开关 (id INTEGER PRIMARY KEY, 开关 INTEGER DEFAULT 0);");
Exec(conn, tx, "INSERT OR IGNORE INTO _触发器开关 (id, 开关) VALUES (1, 0);");
}
/// 开关设值(1=批量写跳过触发器;0=编辑自动计算)。
public static void 开关设(System.Data.SQLite.SQLiteConnection conn, System.Data.SQLite.SQLiteTransaction tx, int 值)
{
确保开关表(conn, tx);
Exec(conn, tx, "UPDATE _触发器开关 SET 开关=" + 值 + " WHERE id=1;");
}
/// 刷新图号行聚合(箱号增删后调用;箱号表名按表索引查;一条 UPDATE,公式来自公式表)。
public static void 刷新图号行聚合(System.Data.SQLite.SQLiteConnection conn, System.Data.SQLite.SQLiteTransaction tx,
string 项目库路径, string 图号ID, string 图号表名)
{
string xhTbl = null;
using (var cmd = new System.Data.SQLite.SQLiteCommand(
"SELECT 实际表名 FROM 表索引 WHERE 类型='箱号' AND 图号=@th LIMIT 1;", conn, tx))
{
cmd.Parameters.AddWithValue("@th", 图号ID);
object o = cmd.ExecuteScalar();
xhTbl = o == null || o == DBNull.Value ? null : o.ToString();
}
if (string.IsNullOrEmpty(xhTbl)) return;
using (var fakConn = new System.Data.SQLite.SQLiteConnection(
"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(项目库路径), "..", "fak.db") + ";Version=3;"))
{
fakConn.Open();
var 图号公式 = 加载公式(fakConn, "图号");
string thSet = 生成Set片段(图号公式, "ROW", xhTbl);
Exec(conn, tx, "UPDATE \\"" + 图号表名 + "\\" SET " + thSet + " WHERE \\"内部ID\\"='" + 图号ID.Replace("'", "''") + "';");
}
}
/// 新表(管线内建表后)即时补触发器——只处理该表自身。'''),
], "计算引擎.cs")
# ============ ② 图号粘贴管线:抄源聚合值 + 开关包裹 ============
p = ROOT + r"\主窗口.菜单与库.cs"
edit(p, [
# tx 后设开关=1(锚点:图号管线快照日志)
(''' Log("[粘贴图号] 快照: 事务内图号数=" + 快照图号数 + " xhMax=" + xhMax + " yjMax=" + yjMax);''',
''' Log("[粘贴图号] 快照: 事务内图号数=" + 快照图号数 + " xhMax=" + xhMax + " yjMax=" + yjMax);
// ★ 批量模式:粘贴数据自带正确计算结果(源行快照),无需触发器逐行重算
计算引擎.开关设(conn, tx, 1);'''),
# 图号行 INSERT 抄源聚合值(原来是 0,0,0,0,0,0)
(''' using (var cmd = new System.Data.SQLite.SQLiteCommand(
"INSERT INTO " + TuhaoTableName +
" (序号, 图号, 台数, 箱柜数量, 单价, 总价, 成本总价, 毛利, 毛利率, 内部ID) " +
"VALUES (@s, @th, @ts, 0, 0, 0, 0, 0, 0, @iid);", conn, tx))
{
cmd.Parameters.AddWithValue("@s", thSeqMax.ToString());
cmd.Parameters.AddWithValue("@th", nt);
cmd.Parameters.AddWithValue("@ts", GetSrcField(clip.TuhaoRow.Count > 0 ? clip.TuhaoRow[0] : null, "台数", "1"));
cmd.Parameters.AddWithValue("@iid", nt);
cmd.ExecuteNonQuery();
}''',
''' using (var cmd = new System.Data.SQLite.SQLiteCommand(
"INSERT INTO " + TuhaoTableName +
" (序号, 图号, 台数, 箱柜数量, 单价, 总价, 成本总价, 毛利, 毛利率, 内部ID) " +
"VALUES (@s, @th, @ts, @sl, @dj, @zj, @cbzj, @ml, @mll, @iid);", conn, tx))
{
// ★ 副本=源:聚合值直接抄源图号行(箱号/元件表是完整副本,聚合天然相等)
var srcTh = clip.TuhaoRow.Count > 0 ? clip.TuhaoRow[0] : null;
cmd.Parameters.AddWithValue("@s", thSeqMax.ToString());
cmd.Parameters.AddWithValue("@th", nt);
cmd.Parameters.AddWithValue("@ts", GetSrcField(srcTh, "台数", "1"));
cmd.Parameters.AddWithValue("@sl", GetSrcFieldDecimal(srcTh, "箱柜数量", 0));
cmd.Parameters.AddWithValue("@dj", GetSrcFieldDecimal(srcTh, "单价", 0));
cmd.Parameters.AddWithValue("@zj", GetSrcFieldDecimal(srcTh, "总价", 0));
cmd.Parameters.AddWithValue("@cbzj", GetSrcFieldDecimal(srcTh, "成本总价", 0));
cmd.Parameters.AddWithValue("@ml", GetSrcFieldDecimal(srcTh, "毛利", 0));
cmd.Parameters.AddWithValue("@mll", GetSrcFieldDecimal(srcTh, "毛利率", 0));
cmd.Parameters.AddWithValue("@iid", nt);
cmd.ExecuteNonQuery();
}'''),
# 箱号行 INSERT 抄源聚合值(原 7 个 0)
(''' xhSeq++;
using (var cmd = new System.Data.SQLite.SQLiteCommand(
"INSERT INTO \\"" + xhTbl + "\\" VALUES (@s, @xh, @xm, @model, @spec, @unit, @count," +
" 0, 0, 0, 0, 0, 0, 0, @type, @remark, @drawing, @iid);", conn, tx))
{
cmd.Parameters.AddWithValue("@s", xhSeq.ToString());
cmd.Parameters.AddWithValue("@xh", nx);
cmd.Parameters.AddWithValue("@xm", GetSrcField(xhRow, "箱名", ""));
cmd.Parameters.AddWithValue("@model", GetSrcField(xhRow, "型号", ""));
cmd.Parameters.AddWithValue("@spec", GetSrcField(xhRow, "规格", ""));
cmd.Parameters.AddWithValue("@unit", GetSrcField(xhRow, "单位", "台"));
cmd.Parameters.AddWithValue("@count", GetSrcField(xhRow, "数量", "1"));
cmd.Parameters.AddWithValue("@type", GetSrcField(xhRow, "箱柜类型", ""));
cmd.Parameters.AddWithValue("@remark", GetSrcField(xhRow, "备注", ""));
cmd.Parameters.AddWithValue("@drawing", GetSrcField(xhRow, "图纸", ""));
cmd.Parameters.AddWithValue("@iid", nx);
cmd.ExecuteNonQuery();
}''',
''' xhSeq++;
using (var cmd = new System.Data.SQLite.SQLiteCommand(
"INSERT INTO \\"" + xhTbl + "\\" VALUES (@s, @xh, @xm, @model, @spec, @unit, @count," +
" @dtcb, @cbj, @dtbc, @bcj, @lr, @lrl, @mgl, @type, @remark, @drawing, @iid);", conn, tx))
{
// ★ 副本=源:箱号行聚合值直接抄源行(单台成本/成本价/单台报出/报出价/利润/利润率/毛利率)
cmd.Parameters.AddWithValue("@s", xhSeq.ToString());
cmd.Parameters.AddWithValue("@xh", nx);
cmd.Parameters.AddWithValue("@xm", GetSrcField(xhRow, "箱名", ""));
cmd.Parameters.AddWithValue("@model", GetSrcField(xhRow, "型号", ""));
cmd.Parameters.AddWithValue("@spec", GetSrcField(xhRow, "规格", ""));
cmd.Parameters.AddWithValue("@unit", GetSrcField(xhRow, "单位", "台"));
cmd.Parameters.AddWithValue("@count", GetSrcField(xhRow, "数量", "1"));
cmd.Parameters.AddWithValue("@dtcb", GetSrcFieldDecimal(xhRow, "单台成本", 0));
cmd.Parameters.AddWithValue("@cbj", GetSrcFieldDecimal(xhRow, "成本价", 0));
cmd.Parameters.AddWithValue("@dtbc", GetSrcFieldDecimal(xhRow, "单台报出", 0));
cmd.Parameters.AddWithValue("@bcj", GetSrcFieldDecimal(xhRow, "报出价", 0));
cmd.Parameters.AddWithValue("@lr", GetSrcFieldDecimal(xhRow, "利润", 0));
cmd.Parameters.AddWithValue("@lrl", GetSrcFieldDecimal(xhRow, "利润率", 0));
cmd.Parameters.AddWithValue("@mgl", GetSrcFieldDecimal(xhRow, "毛利率", 0));
cmd.Parameters.AddWithValue("@type", GetSrcField(xhRow, "箱柜类型", ""));
cmd.Parameters.AddWithValue("@remark", GetSrcField(xhRow, "备注", ""));
cmd.Parameters.AddWithValue("@drawing", GetSrcField(xhRow, "图纸", ""));
cmd.Parameters.AddWithValue("@iid", nx);
cmd.ExecuteNonQuery();
}'''),
# 管尾:开关=0(Commit 前)
(''' using (var cmd = new System.Data.SQLite.SQLiteCommand(
"SELECT COUNT(*) FROM " + TuhaoTableName + ";", conn, tx))
Log("[粘贴图号] Commit前(事务内): 图号数=" + cmd.ExecuteScalar());''',
''' // ★ 恢复触发器(编辑自动计算)
计算引擎.开关设(conn, tx, 0);
using (var cmd = new System.Data.SQLite.SQLiteCommand(
"SELECT COUNT(*) FROM " + TuhaoTableName + ";", conn, tx))
Log("[粘贴图号] Commit前(事务内): 图号数=" + cmd.ExecuteScalar());'''),
# 箱号粘贴管线:开关包裹+管尾刷图号行
(''' using (var conn = new System.Data.SQLite.SQLiteConnection(GetConnStringStatic(dbPath)))
{
conn.Open();
using (var tx = conn.BeginTransaction())
{
foreach (var clip in clips)
{
string baseName = !string.IsNullOrWhiteSpace(clip.SrcXianghaoName) ? clip.SrcXianghaoName : "箱号";
string newXhId = MakeUniqueName(baseName,
candidate => NameExistsXhInConn2(conn, tx, th, candidate));
PasteXianghaoRowToDb(pid, th, newXhId, clip.XianghaoRow, conn, tx);
PasteYuanjianRowsToDb(pid, th, newXhId, clip.YuanjianRows, conn, tx);
pastedCount++;
lastXhId = newXhId;
}
tx.Commit();
}
// ★ 触发器已自动算好箱号/图号聚合;只补跨库项目汇总
计算引擎.刷新项目汇总(dbPath, pid, TuhaoTableName);''',
''' using (var conn = new System.Data.SQLite.SQLiteConnection(GetConnStringStatic(dbPath)))
{
conn.Open();
using (var tx = conn.BeginTransaction())
{
// ★ 批量模式:粘贴行自带源计算结果;关触发器,提交前刷一次图号行聚合
计算引擎.开关设(conn, tx, 1);
foreach (var clip in clips)
{
string baseName = !string.IsNullOrWhiteSpace(clip.SrcXianghaoName) ? clip.SrcXianghaoName : "箱号";
string newXhId = MakeUniqueName(baseName,
candidate => NameExistsXhInConn2(conn, tx, th, candidate));
PasteXianghaoRowToDb(pid, th, newXhId, clip.XianghaoRow, conn, tx);
PasteYuanjianRowsToDb(pid, th, newXhId, clip.YuanjianRows, conn, tx);
pastedCount++;
lastXhId = newXhId;
}
// 箱号行聚合抄源行已正确;仅图号行聚合(全箱号表 SUM)需刷新一次
计算引擎.刷新图号行聚合(conn, tx, dbPath, th, TuhaoTableName);
计算引擎.开关设(conn, tx, 0);
tx.Commit();
}
// 只补跨库项目汇总
计算引擎.刷新项目汇总(dbPath, pid, TuhaoTableName);'''),
], "主窗口.菜单与库.cs")
print("合计", n, "处")