ufrmqueryoutput.pas

一个会议签到系统

{*******************************************************************************
*                    通用查询取数模块 
*                    20030319          
*                    拖一列在此，可以按此列分组  问题
*-------------------------------------------------------------------------------
*                    网格的保存问题 是按名字来保存的
*                    所以 如果该名了 就不利于外边的访问
*                    可以在保存时间 和 读取时间该名字 该后有该回 dxGBGrid1
*                    200303022  符林
*-------------------------------------------------------------------------------
*                    网格序号的算法 一个 很通用简单 实用的办法
*-------------------------------------------------------------------------------
*                    系统查询 个人的权限模块应该到  个人菜单表中去找
*                    是个很好的办法   很简单的办法
*ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
*                    20030509 将通用模板该为不很通用的模板
*                    用语有一个主表 2个以上的从表
*                    该为:不通用查询专用模板                        
*******************************************************************************}
unit uFrmQueryOutPut;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  uFrmTop, AppEvnts, ActnList, ExtCtrls, ComCtrls, ToolWin, ImgList,uDataSys,
  Db, DBClient, StdCtrls,Buttons, dxCntner, dxTL, dxDBCtrl, dxDBGrid, Menus;
{** 用来记录查询条件的记录 FZID 对应的 SQL**}
type
  TFZIDAndFilterSQL = Record
    sFZID : String;
    sFilterE:String;
    sFilterSQL:String;
  end;
{******************************************************************************}  
type
  TFrmQueryOutPut = class(TFrmTop)
    IMGTree: TImageList;
    PnlGrid: TPanel;
    dxDBGrid1: TdxDBGrid;
    DataSource1: TDataSource;
    ClientDataSet1: TClientDataSet;
    PnlToolBar: TPanel;
    Panel7: TPanel;
    PnlGridFilter: TPanel;
    Splitter1: TSplitter;
    SpbtnRefresh: TSpeedButton;
    SpbtnAdd: TSpeedButton;
    SpbtnEdit: TSpeedButton;
    SpbtnDel: TSpeedButton;
    SpbtnSave: TSpeedButton;
    SpbtnCancel: TSpeedButton;
    SpbtnExport: TSpeedButton;
    SpbtnPrint: TSpeedButton;
    SpbtnFirst: TSpeedButton;
    SpbtnPrivor: TSpeedButton;
    SpbtnNext: TSpeedButton;
    SpbtnLast: TSpeedButton;
    SpbtnExit: TSpeedButton;
    SpbtnFind: TSpeedButton;
    TrvQueryFilter: TTreeView;
    Bevel3: TBevel;
    ActQueryDBSoure: TAction;
    ActDeleteQX: TAction;
    ActGetSQLData: TAction;
    ActChineseToGrid: TAction;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N3: TMenuItem;
    N4: TMenuItem;
    N5: TMenuItem;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    CDSField: TClientDataSet;
    MemoFilter: TMemo;
    MemoFilterSQL: TMemo;
    N6: TMenuItem;
    N7: TMenuItem;
    DgS_ExprotExecl: TSaveDialog;
    Bevel1: TBevel;
    Bevel2: TBevel;
    ActSaveCtrlS: TAction;
    ActEditCtrlE: TAction;
    ActAddCtrlA: TAction;
    ActGetDataCtrlQ: TAction;
    Bevel5: TBevel;
    Bevel4: TBevel;
    Bevel6: TBevel;
    Bevel7: TBevel;
    Bevel8: TBevel;
    ActSetSumToGrid: TAction;
    SPbtnNewData: TSpeedButton;
    Bevel9: TBevel;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormShow(Sender: TObject);
    procedure SpbtnExitClick(Sender: TObject);
    procedure ActGetSQLDataExecute(Sender: TObject);
    procedure SpbtnRefreshClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);

    procedure ActChineseToGridExecute(Sender: TObject);
    procedure SpbtnAddClick(Sender: TObject);

    procedure TrvQueryFilterChange(Sender: TObject; Node: TTreeNode);
    procedure TrvQueryFilterDblClick(Sender: TObject);
    procedure N3Click(Sender: TObject);
    procedure N5Click(Sender: TObject);
    procedure PopupMenu1Popup(Sender: TObject);
    procedure SpbtnSaveClick(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N4Click(Sender: TObject);
    procedure SpbtnDelClick(Sender: TObject);
    procedure N7Click(Sender: TObject);
    procedure SpbtnExportClick(Sender: TObject);
    procedure TrvMoKuaiKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure ActSaveCtrlSExecute(Sender: TObject);
    procedure ActEditCtrlEExecute(Sender: TObject);
    procedure TrvQueryFilterKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure ActAddCtrlAExecute(Sender: TObject);
    procedure ActGetDataCtrlQExecute(Sender: TObject);
    procedure TrvQueryFilterClick(Sender: TObject);
    procedure SpbtnEditClick(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpbtnFirstClick(Sender: TObject);
    procedure SpbtnLastClick(Sender: TObject);
    procedure SpbtnPrivorClick(Sender: TObject);
    procedure SpbtnNextClick(Sender: TObject);
    procedure ActSetSumToGridExecute(Sender: TObject);
    procedure SPbtnNewDataClick(Sender: TObject);
    procedure N8Click(Sender: TObject);
    procedure MenuItem3Click(Sender: TObject);
    procedure MenuItem4Click(Sender: TObject);
    procedure dxDBGrid1DblClick(Sender: TObject);
  private
    Procedure PInitTrvQueryFilter();
    Procedure PSaveCurrentNode();
    Procedure PAddNodeToTrvFilter();
    Procedure PFilterSQLGetDBData();
    Procedure PDelNldeToTrvFilter();
    function  FGetFilterArrayID():Integer;
    Procedure PInitFilterTree();
    { Private declarations }
  public
    saFilter:array[0..100] of String;//用于过滤树的节点信息
    RFZBMToTreeMoKuai:array[0..2000] of RFZBianMa;//树中最多只能有两千条数据
    sQueryFZID,sQueryFZIDSQL:String;   //记录当前 查询模块的FZID 和 对应定制SQL
    sQueryFilterSQL:String;            //过滤的   SQL
    raFZIDAndFilterSQL:array of TFZIDAndFilterSQL;//记录查询条件树对应的其他记录
    iCurrentTrvFilterIndex:Integer;//记录查询条件树的当前节点序号
    iSureORCancel:Integer;         // 0 = 新建  50 更改  返回时 =100 标识确认
    sConstNoSave:String;           // 常量
    sOldQueryFZID:String;          // 记录 QueryFZID 是否发生变化

    
    Procedure PFindarrayToEdit();

    
 
 
    { Public declarations }
  end;

var
  FrmQueryOutPut: TFrmQueryOutPut;

implementation

uses uDM,uFuntionSys, uFrmFilterSQL;

{$R *.DFM}

procedure TFrmQueryOutPut.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  inherited;
  PSaveReadFormINI(Self,nil,False);
  dxDBGrid1.SaveToIniFile('dxGridSaveIni');
  Action:=caFree;
  FrmQueryOutPut:=nil;
end;

procedure TFrmQueryOutPut.FormShow(Sender: TObject);
begin
  inherited;
  dxDBGrid1.Align:=alClient;
  PSaveReadFormINI(Self,nil,True);  

  {**设置鼠标**}
  if FChangeCursor <> 0 then begin
     Screen.Cursors[1]:=FChangeCursor;//不放在这儿速度其慢
     Self.PnlToolBar.Cursor:=1;
  end;
  sConstNoSave:='没有保存';
  dxDBGrid1.LoadFromIniFile('dxGridSaveIni');

  PInitTrvQueryFilter();
  
end;

procedure TFrmQueryOutPut.SpbtnExitClick(Sender: TObject);
begin
  inherited;
  Self.Close; 
end;

{*******************************************************************************
*             根据定制的SQL 和字段别名 是否可见来查询数据
*******************************************************************************}
procedure TFrmQueryOutPut.ActGetSQLDataExecute(Sender: TObject);
var
  sSQL:String;
begin
  if sQueryFZID = '' then begin
     DlgWarningA('没有选种查询模块');
     Exit;
  end;
  sSQL:='Select * from  TSY52011_ChaXun where FZID='+''''+sQueryFZID+'''';
  FCarryOutSQL(DM.CDSSysA,sSQL,True,False);
  if DM.CDSSysA.RecordCount <= 0 then begin
     DlgWarningA('在数据库中没有找到查询的定制条件');
     sQueryFZIDSQL:='';
     Exit;
  end;
  sSQL:=Trim(DM.CDSSysA.FieldByName('sSQLTxT').AsString);
  sQueryFZIDSQL:=sSQL;   
end;

procedure TFrmQueryOutPut.SpbtnRefreshClick(Sender: TObject);
begin
  {**从数据库中提取数据**}
  PFilterSQLGetDBData();
end;

procedure TFrmQueryOutPut.FormCreate(Sender: TObject);
begin
  inherited;
  sQueryFZID:=''; 
end;

{*******************************************************************************
*                 汉化 DxGrid 的标题  不可见的设置为不可见
*                 如果可见的列宽过小 或 = 0  设置为更宽  
*******************************************************************************}
procedure TFrmQueryOutPut.ActChineseToGridExecute(Sender: TObject);
var
  sSQL,Str,StrX:String;
  I:integer;
begin
  {**检查记录是否发生了变化 以免重复打开**}
  if sOldQueryFZID <> sQueryFZID then begin
     sOldQueryFZID:=sQueryFZID;
     sSQL:='Select * from  TSY52012_ChaXun where PFZID='+''''+sQueryFZID+'''';
     FCarryOutSQL(CDSField,sSQL,True,False);
     if CDSField.RecordCount <= 0 then begin
         DlgWarningA('在没有找到查询的定制条件字段别名');
         Exit;
     end;
  end;
  for I:=0 to dxDBGrid1.DataSource.DataSet.FieldCount-1 do begin
      Str:=dxDBGrid1.Columns[I].FieldName;
      CDSField.First;
      if Str = 'FZINTID' then  dxDBGrid1.Columns[I].Caption:='网格序号';
      while not CDSField.Eof do begin
        StrX:=Trim(CDSField.FieldByName('sZDM').AsString);        
        if StrX = Str then begin
           dxDBGrid1.Columns[I].Caption:=Trim(CDSField.FieldByName('sZDBM').AsString);;
           StrX:=Trim(CDSField.FieldByName('sSFKJ').AsString);
           if StrX = '不可见' then  begin
              dxDBGrid1.Columns[I].Visible:=False;
           end else if  dxDBGrid1.Columns[I].Visible = False then
              dxDBGrid1.Columns[I].Visible:=True;
        end;
        CDSField.Next;
      end;
  end;

end;

procedure TFrmQueryOutPut.SpbtnAddClick(Sender: TObject);
begin
  N3Click(Sender);   
end;

{*******************************************************************************
*                       初始化查询条件树
*******************************************************************************}
procedure TFrmQueryOutPut.PInitTrvQueryFilter;
var
  sSQL,Str:String;
  I,K:Integer;
begin
  if  sQueryFZID ='' then Exit;
  for I:=TrvQueryFilter.Items.Count-1 downto 2 do begin
      TrvQueryFilter.Items[I].Delete;
  end;
  sSQL:='  Select * from  TSY52013_ChaXun  Where  PFZID='+''''+sQueryFZID+''''
       +'  And CFZID='+''''+sSYSUserFZID+'''';
  FCarryOutSQL(DM.CDSSysA,sSQL,True,False);
  K:=DM.CDSSysA.RecordCount;
  if K <= 0 then Exit;

  I:=0;SetLength(raFZIDAndFilterSQL,K);
  DM.CDSSysA.First;
  While not  DM.CDSSysA.Eof do begin
    I:=I+1;
    Str:=InttoStr(I-1)+'   '+Trim(DM.CDSSysA.FieldByName('FZID').AsString);
    raFZIDAndFilterSQL[I-1].sFZID:=Trim(DM.CDSSysA.FieldByName('FZID').AsString);
    raFZIDAndFilterSQL[I-1].sFilterSQL:=Trim(DM.CDSSysA.FieldByName('sFilterSQL').AsString);
    raFZIDAndFilterSQL[I-1].sFilterE:=Trim(DM.CDSSysA.FieldByName('sFilterE').AsString);
    TrvQueryFilter.Items.Add(TrvQueryFilter.Items.Item[1],Str);
    TrvQueryFilter.Items.Item[I+1].ImageIndex:=4;
    TrvQueryFilter.Items.Item[I+1].SelectedIndex:=4;
    DM.CDSSysA.Next;
  end;
  TrvQueryFilter.Items[0].Expand(True);

  PInitFilterTree();

end;
{*******************************************************************************
*                       查询条件树的当前节点
*******************************************************************************}
procedure TFrmQueryOutPut.TrvQueryFilterChange(Sender: TObject;
  Node: TTreeNode);
var
  I,K:Integer;
  sID:String;
begin
  if TrvQueryFilter.Selected = nil             then Exit;
  if TrvQueryFilter.Selected.AbsoluteIndex = 0 then Exit;
  iCurrentTrvFilterIndex:=TrvQueryFilter.Selected.AbsoluteIndex;
  if TrvQueryFilter.Selected.AbsoluteIndex = 1 then  begin
     MemoFilter.Text:='没有过滤条件';
     MemoFilterSQL.Text:='';
  end;   
  if TrvQueryFilter.Selected.data = nil then Exit;
  sID:=String(TrvQueryFilter.Selected.data^);
  K:=Length(raFZIDAndFilterSQL);
  for I:=0 to K-1 do begin
      if raFZIDAndFilterSQL[I].sFZID = sID then begin
         MemoFilterSQL.Text:=raFZIDAndFilterSQL[I].sFilterSQL;
         MemoFilter.Text:=raFZIDAndFilterSQL[I].sFilterE;
         Break;
      end;
  end;
end;
{*******************************************************************************
*                       保存当前节点 查询条件
*******************************************************************************}
procedure TFrmQueryOutPut.PSaveCurrentNode;
var
  sSQLDel,sSQLAdd,Str:String;
begin
  if (Pos('''',MemoFilter.Text) > 0) or  (Pos('''',MemoFilterSQL.Text) > 0) then begin
     DlgWarningA('条件中有单引号，请用双引号代替');
     Exit;
  end;

  PInitFilterTree();//节点指针问题

  Str:=TrvQueryFilter.Items[iCurrentTrvFilterIndex].Text;
  if Pos(sConstNoSave,Str) <= 0 then Exit;//已经保存退出
  Str:=String(TrvQueryFilter.Items[iCurrentTrvFilterIndex].data^); 
  sSQLDel:='Delete From  TSY52013_ChaXun where FZID='+''''+Str+'''';
  if not bSYSDBORCorSQL then sSQLDel:=sSQLDel+';';
  sSQLAdd:=FCreateInSertOneSQL(['PFZID','FZID','sFilterSQL','sFilterE','CFZID'],
                               [sQueryFZID,Str,MemoFilterSQL.Text,MemoFilter.Text,sSYSUserFZID],
                               [0,1,2,3,4],'TSY52013_ChaXun');
  Str:=sSQLDel+'  '+sSQLAdd;
  if not FCarryOutSQL(nil,Str,False,True) then begin
     DlgWarningX('保存出错');
     Exit;
  end;
  {**清除未保存标识**}
  Str:=MyCutOutStringB(sConstNoSave,TrvQueryFilter.Items[iCurrentTrvFilterIndex].Text);
  TrvQueryFilter.Items[iCurrentTrvFilterIndex].Text:=Trim(Str);
end;
{*******************************************************************************
*          在树中添加一个节点      并且要在对应的数组中记录
*******************************************************************************}
procedure TFrmQueryOutPut.PAddNodeToTrvFilter;
var
  I:integer;
  Str,sID:String;
begin
  I:=Length(raFZIDAndFilterSQL);//TrvQueryFilter.Items.Count;//20030326
  sID:=GetFZID10();
  Str:=IntToStr(I)+sConstNoSave+'   '+sID;
  I:=TrvQueryFilter.Items.Count; 
  TrvQueryFilter.Items.Add(TrvQueryFilter.Items.Item[1],Str);
  TrvQueryFilter.Items.Item[I].ImageIndex:=4;
  TrvQueryFilter.Items.Item[I].SelectedIndex:=4;

  {**数量不多 只加不删除}
  I:=Length(raFZIDAndFilterSQL);
  SetLength(raFZIDAndFilterSQL,I+1);
  raFZIDAndFilterSQL[I].sFZID:=sID;
  raFZIDAndFilterSQL[I].sFilterSQL:=MemoFilterSQL.Text;
  raFZIDAndFilterSQL[I].sFilterE:=MemoFilter.Text;

end;
{*******************************************************************************
**         0 = 新建  50 更改  返回时 =100 标识确认                **
*******************************************************************************}
procedure TFrmQueryOutPut.TrvQueryFilterDblClick(Sender: TObject);
begin
  {**从数据库中提取数据**}