分类目录功能模型设计

--初始化载入条件示例代码 SQL SERVER 
ALTER PROCEDURE [dbo].[X9_ZRTJCS]
   (@GNBH VARCHAR(20),  --功能编号
  @YHMC VARCHAR(20))  --用户名称
AS
BEGIN TRY
  SET NOCOUNT ON;
  DECLARE @DYID INT,@GNMC NVARCHAR(20),@TJXH INT,@TJMC VARCHAR(20),@TJJG VARCHAR(80),@RXKZ VARCHAR(10),@TJSM NVARCHAR(100),
    @TJEDIT VARCHAR(100);
  DECLARE Zrtj_cursor CURSOR LOCAL FOR
    SELECT GNMC,TJXH,TJMC,TJJG,RXKZ,TJSM,TJEDIT FROM X9_GN_0ZRTJ
      WHERE GNBH=@GNBH AND YHMC='';
  OPEN Zrtj_cursor;
  FETCH FROM Zrtj_cursor
    INTO @GNMC,@TJXH,@TJMC,@TJJG,@RXKZ,@TJSM,@TJEDIT;
  WHILE @@FETCH_STATUS=0
  BEGIN
    IF NOT EXISTS(SELECT * FROM X9_GN_0ZRTJ WHERE GNBH=@GNBH AND TJMC=@TJMC AND TJSM=@TJSM AND YHMC=@YHMC)
      BEGIN
        SELECT @RXKZ=CASE WHEN @RXKZ='1' THEN '1' ELSE '0' END ; 
        EXEC DBO.X9_DJMID  'X9_GN_0ZRTJ','DYID',1,@DJMID=@DYID OUTPUT;
        INSERT INTO X9_GN_0ZRTJ (DYID,GNBH,GNMC,TJXH,TJMC,TJJG,RXKZ,TJSM,YHMC,TJEDIT) 
            VALUES(@DYID,@GNBH,@GNMC,@TJXH,@TJMC,@TJJG,@RXKZ,@TJSM,@YHMC,@TJEDIT)
      END
    FETCH NEXT FROM Zrtj_cursor
    INTO @GNMC,@TJXH,@TJMC,@TJJG,@RXKZ,@TJSM,@TJEDIT;
  END
  CLOSE Zrtj_cursor;
  DEALLOCATE Zrtj_cursor  
  END TRY
BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;
  SELECT 
    @ErrorMessage =ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();
  RAISERROR (@ErrorMessage, -- Message text.
         @ErrorSeverity, -- Severity.
         @ErrorState ); -- State.
END CATCH;

ALTER PROCEDURE [dbo].[X9_TJ_1FLML] 
   (@DQYH  NVARCHAR(10),  --用户名称
    @GNNAME  NVARCHAR(30),  --功能名称
  @TBNAME  NVARCHAR(30),  --数据表名
  @BMSTR NVARCHAR(MAX)) --提交的主键字符串，使用 @BMSTR LIKE '%(' + CHDH + ')%' 表达式做为条件;
AS
  --分类目录 提交后主要处理底层明细和计算助记码
  BEGIN TRY
    IF @GNNAME='账套用户角色维护' OR @GNNAME='账套用户设置' OR @GNNAME='功能列表定义'
      BEGIN
        --select * from x9_gn
        --删除多余行
        DELETE X9_GNYHQX WHERE (YHJS NOT IN (SELECT YHJS FROM X9_GNYHJS ));
        DELETE X9_GNYHQX WHERE (GNBH NOT IN (SELECT GNBH FROM X9_GN WHERE JC=2 AND SFQY=0)) ;
        DELETE X9_GNYHQX WHERE (SSMK NOT IN (SELECT MKMC FROM X9_GN_GNMK WHERE SFYY=1));
        --DELETE X9_GNYHQX WHERE YHJS='系统管理员';
        --增加缺少行
        DECLARE @MAXID INT;
        SELECT @MAXID =1+MAX(QXID) FROM X9_GNYHQX ;
        IF @MAXID IS NULL 
          SET @MAXID=1;
        WITH YH (YHJS) AS   (SELECT DISTINCT YHJS FROM X9_GNYHJS where YHJS<>'系统管理员')
        INSERT INTO X9_GNYHQX (QXID,YHJS,GNBH,GNMC,GNLY,GNLB,SSMK,JZZD,SFSQ,Xzbj,Xzdy,Xzck,Xzwj,Xzdc,XZQX,SFYC)
          SELECT @MAXID+(ROW_NUMBER() OVER(ORDER BY YH.YHJS, GN.GNBH)),
            YH.YHJS, GN.GNBH, GN.GNMC, GN.GNLY, GN.GNLB, GN.SSMK,'' AS JZZD, 
            0 AS SFSQ,1 AS XZBJ,1 AS XZDY,1 AS XZCK,
            1 AS XZWJ,1 AS XZDC ,'111111' AS XZQX,GN.SFYC
            FROM YH CROSS JOIN X9_GN GN
            WHERE GN.JC=2 AND SFQY=0 
              AND GN.SSMK IN (SELECT MKMC FROM X9_GN_GNMK WHERE SFYY=1) 
              AND (YH.YHJS+GN.GNBH) NOT IN(SELECT YHJS+GNBH FROM X9_GNYHQX);
        UPDATE  QX SET GNMC = GN.GNMC, GNLY = GN.GNLY, SSMK = GN.SSMK, GNLB = GN.GNLB, SFYC = GN.SFYC
          FROM X9_GNYHQX AS QX INNER JOIN X9_GN AS GN ON QX.GNBH = GN.GNBH;
      SELECT '用户权限初始检查已经完成';
      END 
      --SELECT DISTINCT YHJS,'' AS SJBH, ROW_NUMBER() OVER(ORDER BY YHJS) AS YHBH FROM X9_GNYH
      --UPDATE X9_GNYHQX SET SFSQ =1;
      --select * from X9_GNYHQX     
  IF @GNNAME='客户目录'
      BEGIN
        EXEC X9_REZJM @TBNAME;
        EXEC X9_DCMX @TBNAME;
        UPDATE MLWL SET ZKL=0 WHERE ZKL<0 OR ZKL>100;
        SELECT '客户目录提交后处理已完成';
        RETURN
      END
  IF @GNNAME='供应商目录'
      BEGIN
        EXEC X9_REZJM @TBNAME;
        EXEC X9_DCMX @TBNAME;
        SELECT '供应商目录提交后处理已完成';
        RETURN
      END 
    IF @GNNAME='部门目录'
      BEGIN
        EXEC X9_REZJM @TBNAME;
        EXEC X9_DCMX @TBNAME;
        SELECT '部门目录提交后处理已完成'
        RETURN
      END  
    IF @GNNAME='会计科目'
      BEGIN
        EXEC X9_REZJM @TBNAME;
        EXEC X9_DCMX @TBNAME;
        SELECT '会计科目提交后处理已完成'
        RETURN
      END  
    IF @GNNAME='数据字典'
      BEGIN
        SELECT '数据字典提交后处理已完成'
        RETURN
      END 
  END TRY
  BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity int,@ErrorState INT;
    SELECT  @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
    RAISERROR (@ErrorMessage,  @ErrorSeverity,  @ErrorState ); 
  END CATCH;