tbltreeview.pas

pde专用vcl

    itemList := TStringList.Create;
    while not adoquery.Eof do
    begin
      itemList.Add('M' + adoquery.Fields[0].AsString);
      itemList.Add('MMROOM');
      itemList.Add(adoquery.Fields[1].AsString);
      itemList.Add(adoquery.Fields[0].AsString);
      itemList.Add('');
      itemList.Add('');
      itemList.Add('');
      TreeAddItem(itemList);
      itemList.Clear;
      adoquery.Next;
    end;
    adoquery.Close;
    adoquery.SQL.Clear;
    //建树
    //strSql :=
    //  'SELECT ID,C_TYPE,C_TBLNAME,I_MMROOMID,C_PROPERTY,C_TBLTYPE,I_PARENTID FROM SYS_TBL WHERE (C_TYPE = ''CLSC'' OR C_TYPE = ''USET'') ORDER BY C_TYPE,I_LEVEL,I_ORDER,I_PARENTID';
    strSql := GetCreateTreeSql(Data);
    adoquery.SQL.add(strSql);
    adoquery.Open;

    while not adoquery.Eof do
    begin
      itemList.Add(adoquery.Fields[0].AsString);
      itemList.Add(adoquery.Fields[1].AsString);
      if adoquery.Fields[1].AsString = 'CLSC' then
        itemList.Add(adoquery.Fields[2].AsString)
      else
      begin
        sT :=
          GetValuesBySQL('SELECT C_TSET,C_YSET FROM SYS_GROUPAR WHERE I_TBLID = '
          + adoquery.Fields[0].AsString + ' AND C_GROUPCODE = ''' + RoleCode +
          '''');
        if sT.Count = 0 then
          itemList.Add(adoquery.Fields[2].AsString + '　　［无授权］')
        else
        begin
          strShowPower := adoquery.Fields[2].AsString + '　　';
          if sT[0] = 'R' then
            strShowPower := strShowPower + '［只读目录］'
          else
            strShowPower := strShowPower + '［读写目录］';
          if sT[1] = 'R' then
            strShowPower := strShowPower + '［只读原文］'
          else if sT[1] = 'W' then
            strShowPower := strShowPower + '［读写原文］'
          else
            strShowPower := strShowPower + '［禁读原文］';
          itemList.Add(strShowPower);
        end;
      end;
      itemList.Add(adoquery.Fields[3].AsString);
      itemList.Add(adoquery.Fields[4].AsString);
      itemList.Add(adoquery.Fields[5].AsString);
      itemList.Add(adoquery.Fields[6].AsString);
      TreeAddItem(itemList);
      itemList.Clear;
      adoquery.Next;
    end;
    adoquery.Close;
    adoquery.Destroy;
    if Self.Items.Count > 0 then
    begin
      Self.Items.Item[0].Selected := true;
      Self.FullCollapse;
      Self.Items.Item[0].Expand(false);
      //Self.Items.Item[0].Selected:=true;
    end;
  except
    on E: Exception do
      Messagebox(self.Handle,
        PAnsiChar('数据库操作失败！请检查数据连接是否正常。' + chr(10) + chr(13)
        +
        '详细错误信息如下：' + chr(10) + chr(13) + E.Message),
        PAnsiChar('信息'),
        MB_OK + MB_ICONINFORMATION);
  end;
end;
//用户的档案权限树

procedure TTBLTreeView.TreeCreateByUserPower(UserID: string; DATA: string);
var
  adoquery: TADOQuery;
  itemList: TStrings;
  strSql: string;
  sT: TStrings;
  strShowPower: string;
begin
  try
    //建立档案室
    strSql := 'SELECT ID,C_MMROOMNAME FROM SYS_MMROOM ORDER BY C_MMROOMCODE';

    adoquery := TADOQuery.Create(self);
    adoquery.Connection := FADOconn;

    adoquery.SQL.add(strSql);
    adoquery.Open;
    Self.Items.Clear;
    itemList := TStringList.Create;
    while not adoquery.Eof do
    begin
      itemList.Add('M' + adoquery.Fields[0].AsString);
      itemList.Add('MMROOM');
      itemList.Add(adoquery.Fields[1].AsString);
      itemList.Add(adoquery.Fields[0].AsString);
      itemList.Add('');
      itemList.Add('');
      itemList.Add('');
      TreeAddItem(itemList);
      itemList.Clear;
      adoquery.Next;
    end;
    adoquery.Close;
    adoquery.SQL.Clear;
    //建树
    {strSql :=
      'SELECT ID,C_TYPE,C_TBLNAME,I_MMROOMID,C_PROPERTY,C_TBLTYPE,I_PARENTID FROM SYS_TBL WHERE (C_TYPE = ''CLSC'' OR C_TYPE = ''USET'') ORDER BY C_TYPE,I_LEVEL,I_ORDER,I_PARENTID';
    }
    strSql := GetCreateTreeSql(Data);
    adoquery.SQL.add(strSql);
    adoquery.Open;

    while not adoquery.Eof do
    begin
      itemList.Add(adoquery.Fields[0].AsString);
      itemList.Add(adoquery.Fields[1].AsString);
      if adoquery.Fields[1].AsString = 'CLSC' then
        itemList.Add(adoquery.Fields[2].AsString)
      else
      begin
        sT :=
          GetValuesBySQL('SELECT I_SET,C_TSET,C_YSET FROM SYS_USERAR WHERE I_TBLID = '
          + adoquery.Fields[0].AsString + ' AND I_USERID = ' + UserID);
        if sT.Count = 0 then
          itemList.Add(adoquery.Fields[2].AsString + '　　［无授权］')
        else
        begin
          if strtoint(sT[0]) = -1 then
            strShowPower := adoquery.Fields[2].AsString + '　　［拒绝授权］'
          else
          begin
            strShowPower := adoquery.Fields[2].AsString + '　　';
            if sT[1] = 'R' then
              strShowPower := strShowPower + '［只读目录］'
            else
              strShowPower := strShowPower + '［读写目录］';
            if sT[2] = 'R' then
              strShowPower := strShowPower + '［只读原文］'
            else if sT[2] = 'W' then
              strShowPower := strShowPower + '［读写原文］'
            else
              strShowPower := strShowPower + '［禁读原文］';
          end;
          itemList.Add(strShowPower);
        end;
      end;
      itemList.Add(adoquery.Fields[3].AsString);
      itemList.Add(adoquery.Fields[4].AsString);
      itemList.Add(adoquery.Fields[5].AsString);
      itemList.Add(adoquery.Fields[6].AsString);
      TreeAddItem(itemList);
      itemList.Clear;
      adoquery.Next;
    end;
    adoquery.Close;
    adoquery.Destroy;
    if Self.Items.Count > 0 then
    begin
      Self.Items.Item[0].Selected := true;
      Self.FullCollapse;
      Self.Items.Item[0].Expand(false);
    end;
  except
    on E: Exception do
      Messagebox(self.Handle,
        PAnsiChar('数据库操作失败！请检查数据连接是否正常。' + chr(10) + chr(13)
        +
        '详细错误信息如下：' + chr(10) + chr(13) + E.Message),
        PAnsiChar('信息'),
        MB_OK + MB_ICONINFORMATION);
  end;
end;

function TTBLTreeView.GetCreateTreeSql(Data: string): string;
var
  adoquery: TADOQuery;
  itemList: TStrings;
  iLoop: integer;
  strSql: string;
  sUserAdmin: string; //用户身份 ,是否档案主管
  sShowCTree: string; //是否拥有显示全部档案类别树权限
  sWhereQZCode: string;
begin
  try
    sShowCTree := '0';
    sUserAdmin := GetValueBySQL('SELECT C_ADMIN FROM SYS_USERS WHERE ID = ' +
      FUserID);
    //建立档案室
    if Trim(FQZCode) <> '' then
      sWhereQZCode := ' where C_QZCODE=' + QuotedStr(FQZCode) + ' '
    else
      sWhereQZCode := '';
    if FMMROOMCODE = '' then
    begin
      if sUserAdmin = '1' then
        strSql := 'SELECT ID,C_MMROOMNAME FROM SYS_MMROOM ' + sWhereQZCode +
          ' ORDER BY C_MMROOMCODE '
      else
      begin
        if DATA = 'MSSQL' then
          sShowCTree :=
            GetValueBySQL('SELECT SUM(I_SET) AS S FROM (SELECT 1 AS I_SET FROM SYS_GROUPOP WHERE C_GROUPCODE IN (SELECT C_GROUPCODE FROM SYS_USERS WHERE ID = '
            + FUserID +
            ') AND C_OPCODE = ''ShowCTree'' UNION SELECT I_SET FROM SYS_USEROP WHERE I_USERID = ' + FUserID
            + ' AND C_OPCODE = ''ShowCTree''' + ') AS T');
        if DATA = 'ORACLE' then
          sShowCTree :=
            GetValueBySQL('SELECT SUM(I_SET) AS S FROM (SELECT 1 AS I_SET FROM SYS_GROUPOP WHERE C_GROUPCODE IN (SELECT C_GROUPCODE FROM SYS_USERS WHERE ID = '
            + FUserID +
            ') AND C_OPCODE = ''ShowCTree'' UNION SELECT I_SET FROM SYS_USEROP WHERE I_USERID = ' + FUserID
            + ' AND C_OPCODE = ''ShowCTree''' + ')');
        if sShowCTree = '' then
          sShowCTree := '0';
        if strtoint(sShowCTree) > 0 then
          strSql := 'SELECT ID,C_MMROOMNAME FROM SYS_MMROOM ' + sWhereQZCode +
            'ORDER BY C_MMROOMCODE'
        else
        begin
          if Trim(FQZCode) <> '' then
            sWhereQZCode := ' C_QZCODE=' + QuotedStr(FQZCode) + ' AND '
          else
            sWhereQZCode := '';
          strSql :=
            'SELECT ID,C_MMROOMNAME FROM SYS_MMROOM WHERE ' + sWhereQZCode +
            ' ID IN (SELECT DISTINCT I_MMROOMID FROM SYS_TBL WHERE ID IN (SELECT I_TBLID FROM ';
          strSql := strSql +
            '(SELECT 1 as I_SET,I_TBLID FROM SYS_GROUPAR WHERE C_GROUPCODE IN (SELECT C_GROUPCODE FROM SYS_USERS WHERE ID = ' + FUserID
            + ') ';
          if DATA = 'MSSQL' then
            strSql := strSql +
              'UNION SELECT I_SET,I_TBLID FROM SYS_USERAR WHERE I_USERID = ' + FUserID
              + ') as T GROUP BY I_TBLID HAVING SUM(I_SET) > 0))';
          if DATA = 'ORACLE' then
            strSql := strSql +
              'UNION SELECT I_SET,I_TBLID FROM SYS_USERAR WHERE I_USERID = ' + FUserID
              + ') GROUP BY I_TBLID HAVING SUM(I_SET) > 0))';
        end;
      end;
    end
    else
    begin
      strSql := 'SELECT ID,C_MMROOMNAME FROM SYS_MMROOM WHERE C_MMROOMCODE = '''
        + FMMROOMCODE + '''';
    end;

    adoquery := TADOQuery.Create(self);
    adoquery.Connection := FADOconn;

    adoquery.SQL.add(strSql);
    adoquery.Open;
    Self.Items.Clear;
    itemList := TStringList.Create;
    while not adoquery.Eof do
    begin
      itemList.Add('M' + adoquery.Fields[0].AsString);
      itemList.Add('MMROOM');
      itemList.Add(adoquery.Fields[1].AsString);
      itemList.Add(adoquery.Fields[0].AsString);
      itemList.Add('');
      itemList.Add('');
      itemList.Add('');
      TreeAddItem(itemList);
      itemList.Clear;
      adoquery.Next;
    end;
    adoquery.Close;
    adoquery.SQL.Clear;
    //建树
    if sUserAdmin = '1' then
    begin
      if FHowShow = '1' then
        strSql := 'SELECT ID,C_TYPE,C_TBLNAME,I_MMROOMID,C_PROPERTY,C_TBLTYPE,I_PARENTID FROM SYS_TBL WHERE C_TYPE = ''CLSC'' ORDER BY C_TYPE,I_LEVEL,I_ORDER,I_PARENTID'
      else
        strSql :=
          'SELECT ID,C_TYPE,C_TBLNAME,I_MMROOMID,C_PROPERTY,C_TBLTYPE,I_PARENTID FROM SYS_TBL WHERE (C_TYPE = ''CLSC'' OR C_TYPE = ''USET'') ORDER BY C_TYPE,I_LEVEL,I_ORDER,I_PARENTID';
    end
    else
    begin
      //if strtoint(sAddTClass) > 0 then
      if FHowShow = '1' then
      begin
        strSql :=
          'SELECT ID,C_TYPE,C_TBLNAME,I_MMROOMID,C_PROPERTY,C_TBLTYPE,I_PARENTID FROM SYS_TBL WHERE C_TYPE = ''CLSC'' ORDER BY C_TYPE,I_LEVEL,I_ORDER,I_PARENTID';
      end
      else
      begin
        strSql :=
          'Select ID,C_TYPE,C_TBLNAME,I_MMROOMID,C_PROPERTY,C_TBLTYPE,I_PARENTID,I_ORDER From (Select ID,C_TYPE,C_TBLNAME,I_MMROOMID,C_PROPERTY,C_TBLTYPE,I_PARENTID,I_ORDER,I_LEVEL from sys_tbl where c_type = ''CLSC'' Union ';
        strSql := strSql +
          'SELECT ID,C_TYPE,C_TBLNAME,I_MMROOMID,C_PROPERTY,C_TBLTYPE,I_PARENTID,I_ORDER,I_LEVEL FROM sys_tbl WHERE ID IN (SELECT I_TBLID FROM ';
        strSql := strSql +
          '(SELECT 1 as I_SET,I_TBLID FROM SYS_GROUPAR WHERE  C_TSET=''W'' AND C_YSET=''W'' AND C_GROUPCODE IN (SELECT C_GROUPCODE FROM SYS_USERS WHERE ID = ' + FUserID
          + ') ';
        if DATA = 'MSSQL' then
          strSql := strSql +
            'UNION SELECT I_SET,I_TBLID FROM SYS_USERAR WHERE C_TSET=''W'' AND C_YSET=''W'' AND I_USERID = ' + FUserID
            + ') as T GROUP BY I_TBLID HAVING SUM(I_SET) > 0)) AS TT ORDER BY C_TYPE,I_LEVEL,I_ORDER,I_PARENTID';
        if DATA = 'ORACLE' then
          strSql := strSql +
            'UNION SELECT I_SET,I_TBLID FROM SYS_USERAR WHERE C_TSET=''W'' AND C_YSET=''W'' AND I_USERID = ' + FUserID
            + ') GROUP BY I_TBLID HAVING SUM(I_SET) > 0)) ORDER BY C_TYPE,I_LEVEL,I_ORDER,I_PARENTID';
      end;
    end;
    Result := strSql;
  finally
  end;
end;

end.