-- 同步6个业绩表的门店ID字段
-- 开单和消耗业绩表：通过开单编号（glkdbh）关联开单记录表（lq_kd_kdjlb），获取门店ID（djmd）
-- 退卡业绩表：通过退卡编号（gltkbh）关联退卡表（lq_hytk_hytk），获取门店ID（md）

-- 给业绩表的关联字段加索引
-- 注意：如果索引已存在会报错，可以忽略该错误继续执行后续UPDATE语句
-- 开单健康师业绩表
CREATE INDEX idx_kd_jksyj_glkdbh ON lq_kd_jksyj(glkdbh);
-- 开单科技老师业绩表
CREATE INDEX idx_kd_kjbsyj_glkdbh ON lq_kd_kjbsyj(glkdbh);
-- 消耗健康师业绩表
CREATE INDEX idx_xh_jksyj_glkdbh ON lq_xh_jksyj(glkdbh);
-- 消耗科技老师业绩表
CREATE INDEX idx_xh_kjbsyj_glkdbh ON lq_xh_kjbsyj(glkdbh);
-- 退卡健康师业绩表
CREATE INDEX idx_hytk_jksyj_gltkbh ON lq_hytk_jksyj(gltkbh);
-- 退卡科技老师业绩表
CREATE INDEX idx_hytk_kjbsyj_gltkbh ON lq_hytk_kjbsyj(gltkbh);

-- 1. 开单健康师业绩表：通过glkdbh关联开单记录表获取门店ID
UPDATE lq_kd_jksyj yj
INNER JOIN lq_kd_kdjlb kd ON yj.glkdbh = kd.F_Id
SET yj.F_StoreId = kd.djmd
WHERE yj.glkdbh IS NOT NULL 
    AND yj.glkdbh != ''
    AND kd.djmd IS NOT NULL
    AND kd.djmd != '';

-- 2. 开单科技老师业绩表：通过glkdbh关联开单记录表获取门店ID
UPDATE lq_kd_kjbsyj yj
INNER JOIN lq_kd_kdjlb kd ON yj.glkdbh = kd.F_Id
SET yj.F_StoreId = kd.djmd
WHERE yj.glkdbh IS NOT NULL 
    AND yj.glkdbh != ''
    AND kd.djmd IS NOT NULL
    AND kd.djmd != '';

-- 3. 消耗健康师业绩表：通过glkdbh关联开单记录表获取门店ID
UPDATE lq_xh_jksyj yj
INNER JOIN lq_xh_hyhk xh ON yj.glkdbh = xh.F_Id
SET yj.F_StoreId = xh.md
WHERE (yj.F_StoreId IS NULL OR yj.F_StoreId = '')
    AND yj.glkdbh IS NOT NULL 
    AND yj.glkdbh != ''
    AND xh.F_Id IS NOT NULL
    AND xh.md IS NOT NULL
    AND xh.md != ''
    AND xh.F_IsEffective = 1;

-- 4. 消耗科技老师业绩表：通过glkdbh关联开单记录表获取门店ID
UPDATE lq_xh_kjbsyj yj
INNER JOIN lq_xh_hyhk xh ON yj.glkdbh = xh.F_Id
SET yj.F_StoreId = xh.md
WHERE (yj.F_StoreId IS NULL OR yj.F_StoreId = '')
    AND yj.glkdbh IS NOT NULL 
    AND yj.glkdbh != ''
    AND xh.F_Id IS NOT NULL
    AND xh.md IS NOT NULL
    AND xh.md != ''
    AND xh.F_IsEffective = 1;

-- 5. 退卡健康师业绩表：通过gltkbh关联退卡表获取门店ID
UPDATE lq_hytk_jksyj yj
INNER JOIN lq_hytk_hytk tk ON yj.gltkbh = tk.F_Id
SET yj.F_StoreId = tk.md
WHERE yj.gltkbh IS NOT NULL 
    AND yj.gltkbh != ''
    AND tk.md IS NOT NULL
    AND tk.md != '';

-- 6. 退卡科技老师业绩表：通过gltkbh关联退卡表获取门店ID
UPDATE lq_hytk_kjbsyj yj
INNER JOIN lq_hytk_hytk tk ON yj.gltkbh = tk.F_Id
SET yj.F_StoreId = tk.md
WHERE yj.gltkbh IS NOT NULL 
    AND yj.gltkbh != ''
    AND tk.md IS NOT NULL
    AND tk.md != '';

