254 lines
18 KiB
Python
254 lines
18 KiB
Python
# -*- 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, [
|
|
# 开关条件常量
|
|
(''' /// <summary>触发器名前缀(重建时按前缀全清)。</summary>
|
|
public const string 触发器前缀 = "trgjs_";''',
|
|
''' /// <summary>触发器名前缀(重建时按前缀全清)。</summary>
|
|
public const string 触发器前缀 = "trgjs_";
|
|
|
|
/// <summary>触发器总开关条件:粘贴/删除批量写时置 开关=1(数据自带正确值,无需触发器计算)。</summary>
|
|
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<string>();''',
|
|
''' var old = new List<string>();'''),
|
|
(''' /// <summary>新表(管线内建表后)即时补触发器——只处理该表自身。</summary>''',
|
|
''' /// <summary>确保开关表存在(项目库;老库首次自动建)。</summary>
|
|
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);");
|
|
}
|
|
|
|
/// <summary>开关设值(1=批量写跳过触发器;0=编辑自动计算)。</summary>
|
|
public static void 开关设(System.Data.SQLite.SQLiteConnection conn, System.Data.SQLite.SQLiteTransaction tx, int 值)
|
|
{
|
|
确保开关表(conn, tx);
|
|
Exec(conn, tx, "UPDATE _触发器开关 SET 开关=" + 值 + " WHERE id=1;");
|
|
}
|
|
|
|
/// <summary>刷新图号行聚合(箱号增删后调用;箱号表名按表索引查;一条 UPDATE,公式来自公式表)。</summary>
|
|
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("'", "''") + "';");
|
|
}
|
|
}
|
|
|
|
/// <summary>新表(管线内建表后)即时补触发器——只处理该表自身。</summary>'''),
|
|
], "计算引擎.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, "处")
|