dtyz系统维护情况简要说明补充(针对第十、十二、十三处理步骤及给客户的处理建议的第二、三条)

--dtyz系统维护情况简要说明补充(针对第十、十二、十三处理步骤及给客户的处理建议的第二、三条)

declare @errid int

begin   tran

UPDATE T_Department
SET BatNum = 3
WHERE (BatNum IS NULL)
set @errid=@@error

UPDATE t_batnum
SET t_batnum.cardnum = grp_batnum.c
FROM (SELECT bm1.batnum, COUNT(*) AS c
        FROM T_Customers INNER JOIN
                  (SELECT 部门代码 AS dpcode, 部门名称 AS dpname, batnum
                 FROM (SELECT dpcode1 + dpcode2 + dpcode3 AS 部门代码, batnum,
                               dpname1 + '/' + dpname2 + '/' + dpname3 AS 部门名称
                         FROM T_Department
                         WHERE dpname1 IS NOT NULL AND dpname2 IS NOT NULL AND
                               dpname3 IS NOT NULL
                         UNION ALL
                         SELECT dpcode1 + dpcode2 + dpcode3 AS 部门代码, batnum,
                               dpname1 + '/' + dpname2 AS 部门名称
                         FROM T_Department
                         WHERE dpname1 IS NOT NULL AND dpname2 IS NOT NULL AND
                               dpname3 IS NULL
                         UNION ALL
                         SELECT dpcode1 + dpcode2 + dpcode3 AS 部门代码, batnum,
                               dpname1 AS 部门名称
                         FROM T_Department
                         WHERE dpname1 IS NOT NULL AND dpname2 IS NULL AND
                               dpname3 IS NULL) l) bm1 ON
              T_Customers.Account = bm1.dpcode
        GROUP BY bm1.batnum) grp_batnum
WHERE t_batnum.batnum = grp_batnum.batnum

set  @errid=@errid+@@error

DELETE FROM T_OldCardNo

set  @errid=@errid+@@error
/*    以上为补卡时提示唯一约束错误解决方案 (含无法解挂)
batnum c           
 25
4 28         70
3 1           1
2 11         32
1 3380     2892   */

/*以下为补卡时提示该卡已解挂或已补过卡错误的解决方案:*/
INSERT INTO T_Loss
SELECT CustomerID, CardNo, GETDATE() AS opdt, 1 AS statid, 1 AS cashid,
      1 AS reasonid
FROM T_Customers
WHERE (Status = 3) AND (CustomerID <> ALL
          (SELECT customerid
         FROM t_loss))

set  @errid=@errid+@@error

--以下为针对无法挂失解决方案

DELETE T_LsHMD
FROM T_Customers
WHERE T_LsHMD.CustomerId = T_Customers.CustomerID AND
      T_LsHMD.CardNo = T_Customers.CardNo AND T_Customers.status = 1

set  @errid=@errid+@@error

if @errid<>0
  begin
      rollback   tran
  end
else
  begin
     commit tran
  end

go