ufrmexportbusinessdata.pas

抽象三层访问数据库示例

      strSQL := Format('select business.id, business.name, std_process.main_buss_id from business'
        + #13#10'  left join std_process on business.id = std_process.buss_id'
        + #13#10'  where business.type = 1 and std_process.main_buss_id in (%s)',
        [strBusinessArea]);
      tmpProcess.Data := DataAccess.GetData(strSQL);
      //角色
      ShowExecuteInfo('正在查询角色数据');
      strSQL := Format('select id, name, if_valid from role'
        + #13#10'  where exists(select * from business_module_role_block_rel'
        + #13#10'    where (business_id in (%s)'
        + #13#10'      or exists(select * from business where type = 1'
        + #13#10'        and exists(select * from std_process where main_buss_id in (%s)'
        + #13#10'          and business.id = std_process.buss_id)'
        + #13#10'      and business_module_role_block_rel.business_id = business.id))'
        + #13#10'    and business_module_role_block_rel.role_id = role.id)',
        [strBusinessArea, strBusinessArea]);
      tmpRole.Data := DataAccess.GetData(strSQL);
      //入口菜单
      ShowExecuteInfo('正在查询菜单数据');
        //真实菜单
      strSQL := Format('select id, name, 0 as type from business'
        + #13#10'  where type = 2 and exists(select * from business_module_role_block_rel'
        + #13#10'    where (business_id in (%s)'
        + #13#10'      or exists(select * from business where type = 1'
        + #13#10'        and exists(select * from std_process where main_buss_id in (%s)'
        + #13#10'          and business.id = std_process.buss_id)'
        + #13#10'        and business_module_role_block_rel.business_id = business.id))'
        + #13#10'      and business_module_role_block_rel.menu_id = business.id)',
        [strBusinessArea, strBusinessArea]);
      {strSQL := strSQL + #13#10
        + Format('union select distinct menu_id as id, '''', 1 as type from business_module_role_block_rel'
        + #13#10'    where (business_id in (%s)'
        + #13#10'      or exists(select * from business where type = 1'
        + #13#10'        and exists(select * from std_process where main_buss_id in (%s)'
        + #13#10'          and business.id = std_process.buss_id)'
        + #13#10'        and business_module_role_block_rel.business_id = business.id))'
        + #13#10'      and (menu_id like ''Menu?'' or menu_id = ''MainForm'')',
        [strBusinessArea, strBusinessArea]);}
      tmpMenu.Data := DataAccess.GetData(StringReplace(strSQL, '?', '%', [rfReplaceAll]));
      //模块
      ShowExecuteInfo('正在查询模块数据');
      strSQL := Format('select id, name from app_module'
        + #13#10'  where exists(select * from business_module_role_block_rel'
        + #13#10'    where (business_id in (%s)'
        + #13#10'      or exists(select * from business where type = 1'
        + #13#10'        and exists(select * from std_process where main_buss_id in (%s)'
        + #13#10'          and business.id = std_process.buss_id)'
        + #13#10'        and business_module_role_block_rel.business_id = business.id))'
        + #13#10'      and business_module_role_block_rel.module_id = app_module.id)',
        [strBusinessArea, strBusinessArea]);
      tmpModule.Data := DataAccess.GetData(strSQL);
      //分区
      ShowExecuteInfo('正在查询分区数据');
      strSQL := Format('select * from app_block'
        + #13#10'  where exists(select * from app_module where exists(select * from business_module_role_block_rel'
        + #13#10'    where (business_id in (%s)'
        + #13#10'      or exists(select * from business where type = 1'
        + #13#10'        and exists(select * from std_process where main_buss_id in (%s)'
        + #13#10'          and business.id = std_process.buss_id)'
        + #13#10'        and business_module_role_block_rel.business_id = business.id))'
        + #13#10'      and business_module_role_block_rel.module_id = app_module.id)'
        + #13#10'    and app_block.mdl_id = app_module.id)',
        [strBusinessArea, strBusinessArea]);
      tmpBlock.Data := DataAccess.GetData(strSQL);
      //权限
      ShowExecuteInfo('正在查询权限数据');
      strSQL := Format('select * from business_module_role_block_rel'
        + #13#10'  where grant_type in (''20'', ''30'') and (business_id in (%s)'
        + #13#10'    or exists(select * from business where type = 1'
        + #13#10'      and exists(select * from std_process where main_buss_id in (%s)'
        + #13#10'        and business.id = std_process.buss_id)'
        + #13#10'      and business_module_role_block_rel.business_id = business.id))'
        + #13#10'  order by business_id, role_id, menu_id, module_id, block_id',
        [strBusinessArea, strBusinessArea]);
      tmpGrant.Data := DataAccess.GetData(strSQL);
      //格式化权限数据集
      ShowExecuteInfo('正在组织权限数据');
      while not tmpBusiness.Eof do
      begin
        AddGrantData(tmpBusiness.FieldByName('id').AsString, '', agtBusiness);
        GetGrant(tmpBusiness.FieldByName('id').AsString);
        tmpProcess.Filter := 'main_buss_id = ' + QuotedStr(tmpBusiness.FieldByName('id').AsString);
        tmpProcess.Filtered := true;
        while not tmpProcess.Eof do
        begin
          AddGrantData(tmpProcess.FieldByName('id').AsString, '', agtProcess);
          GetGrant(tmpProcess.FieldByName('id').AsString);
          tmpProcess.Next;
        end;
        tmpBusiness.Next;
      end;
      //流程配置数据
      ShowExecuteInfo('正在查询流程配置数据');
      strSQL := Format('select b.id as business_id, b.time_limit, b.organ_id, b.empid as emp_id,'
        + #13#10'  o.name as organ_name, e.name as emp_name'
        + #13#10'  from business b'
        + #13#10'  left join organ o on b.organ_id = o.id'
        + #13#10'  left join emp e on b.empid = e.id'
        + #13#10'  where b.id in (%s)',
        [strBusinessArea]);
      cdsBusinessConfig.Data := DataAccess.GetData(strSQL);
      //过程配置数据
      ShowExecuteInfo('正在查询过程配置数据');
      strSQL := Format('select p.buss_id as business_id, p.time_limit / 480 as time_limit, p.can_finish, p.hdl_dptid as organ_id, p.role_id, p.hdl_empid as emp_id,'
        + #13#10'  o.name as organ_name, r.name as role_name, e.name as emp_name'
        + #13#10'  from std_process p'
        + #13#10'  left join organ o on p.hdl_dptid = o.id'
        + #13#10'  left join role r on p.role_id = r.id'
        + #13#10'  left join emp e on p.hdl_empid = e.id'
        + #13#10'  where p.main_buss_id in (%s)',
        [strBusinessArea]);
      cdsProcessConfig.Data := DataAccess.GetData(strSQL);
      //过程更多操作部门、角色数据
      ShowExecuteInfo('正在查询过程办理部门、角色数据');
      strSQL := Format('select b.id as business_id, pr.hdl_dp_role_id as id, case when pr.hdl_type = 0 then o.name else r.name end as name, pr.hdl_type as type'
        + #13#10'  from std_process_hdl_rel pr'
        + #13#10'  left join std_process p1 on p1.id = pr.std_id'
        + #13#10'  left join business b on p1.buss_id = b.id'
        + #13#10'  left join organ o on pr.hdl_dp_role_id = o.id'
        + #13#10'  left join role r on pr.hdl_dp_role_id = r.id'
        + #13#10'  where exists(select * from std_process p2 where p2.id = pr.std_id and p2.main_buss_id in (%s))'
        + #13#10'  order by business_id, type, id',
        [strBusinessArea]);
      cdsProcessOperates.Data := DataAccess.GetData(strSQL);
  finally
    tmpBusiness.Free;
    tmpProcess.Free;
    tmpRole.Free;
    tmpMenu.Free;
    tmpModule.Free;
    tmpBlock.Free;
    tmpGrant.Free;
  end;
end;

procedure TfrmExportBusinessData.CreateBusinessGrantDataSet;
begin
  cdsBusinessGrant.Close;
  cdsBusinessGrant.FieldDefs.Clear;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftInteger;
    Name := 'id';
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'data_id';
    Size := 50;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'data_type';
    Size := 50;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'business_id';
    Size := 50;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'business_name';
    Size := 512;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'process_id';
    Size := 50;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'process_name';
    Size := 512;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'role_id';
    Size := 50;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'role_name';
    Size := 255;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'menu_id';
    Size := 50;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'menu_name';
    Size := 255;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'module_id';
    Size := 50;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'module_name';
    Size := 255;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'block_id';
    Size := 50;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'block_name';
    Size := 255;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'block_grant_id';
    Size := 50;
  end;
  with cdsBusinessGrant.FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Name := 'block_grant_name';
    Size := 255;
  end;
  cdsBusinessGrant.CreateDataSet;
end;

procedure TfrmExportBusinessData.cdsBusinessGrantNewRecord(DataSet: TDataSet);
begin
  DataSet.FieldByName('id').AsInteger := DataSet.RecordCount + 1;
end;

procedure TfrmExportBusinessData.btnExecuteClick(Sender: TObject);
begin
  if mmBusinessNameOrID.Text = '' then
    raise Exception.Create('请输入流程名称或者ID号，多个流程可以以英文半角分号分隔');
  if edtExportFile.Text = '' then
    raise Exception.Create('请输入文件名');
  if FileExists(edtExportFile.Text) then
    if Application.MessageBox(PChar(Format('文件 %s 已经存在，继续导出操作将覆盖该文件。', [edtExportFile.Text])),
      '系统提示', MB_ICONQUESTION + MB_YESNO) = idno then
      exit;