base_qry.pas

一个MRPII系统源代码版本

unit Base_Qry;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Panel, ActnList, ExtCtrls, ComCtrls, ToolWin, AdOdb, Db, DBGridEH,
  StdCtrls,Grids,Base_Condition,Base_Detail,ExtPrintReport,pr_PrintReportType,
  Sys_SortOrder, Sys_QuickLocate, Sys_SetColumn, Menus, Sys_Condition, jpeg;

Type
  TFrm_Base_Qry = Class(TFrm_Base_Panel)
    TlBtn_Sum: TToolButton;
    ToolButton5: TToolButton;
    TlBtn_Filter: TToolButton;
    TlBtn_Order: TToolButton;
    ToolButton9: TToolButton;
    TlBtn_Preview: TToolButton;
    TlBtn_Print: TToolButton;
    TlBtn_Excel: TToolButton;
    ToolButton13: TToolButton;
    AdoQry_Main: TAdoQuery;
    DataSource: TDataSource;
    Lbl_OrderTitle: TLabel;
    Lbl_ConditionTitle: TLabel;
    Lbl_Condition: TLabel;
    Lbl_Order: TLabel;
    DBGridEh: TDBGridEh;
    TlBtn_SetColumn: TToolButton;
    ToolButton3: TToolButton;
    ToolButton4: TToolButton;
    ExtPrintReport: TExtPrintReport;
    TlBtn_Locate: TToolButton;
    PopuPmenu: TPopuPmenu;
    HideColumn: TMenuItem;
    TlBtn_Look: TToolButton;
    procedure Act_OrderExecute(Sender: TObject);
    procedure Act_FilterExecute(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_SetColumnExecute(Sender: TObject);
    procedure Act_LocateExecute(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure HideColumnClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
  private
    { Private declarations }
    procedure SetReport;//报表打印参数设置
    procedure GetTransValue(FieldName,OldValue:String;var NewValue:String);//打印转换过程
  protected
    { protected declarations }
    OrderByFields:String;//当前排序字段信息  认大小写
    SelectFromSQL:String;//存放类似select...from...的SQL语句
    Condition:String;//存放 Where 后的条件  临时的
    ConditionUserDefine:String;//存放 Where 后的条件  永久的
    GroupByFields:String;//汇总分组字段信息

    Frm_Sys_Condition:TFrm_Base_Condition;//用于引用条件设置窗体;
    Frm_Sys_SortOrder:TFrm_Sys_SortOrder;//用于引用排序设置窗体;
    Frm_Sys_QuickLocate:TFrm_Sys_QuickLocate;//用于引用快速定位设置窗体;
    Frm_Sys_SetColumn:TFrm_Sys_SetColumn;//用于引用设置DBGridEh显示列设置窗体;

    procedure GetData; virtual;//SQL组装过程,并且打开AdoQry_Main
    procedure InitReport; virtual;//设置报表表头属性
    //子类重载,设置打印数据的转换过程
    procedure SetColumnsStyle(ItemIndex:Integer;FieldName:String); virtual;
  public
    { Public declarations }
    //初始化Form过程,子类都要重载
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);virtual;
  end;
  TAdoQueryExpress=Class(TAdoQuery)
  Public
    Property CommandTimeOut;
  end;
  

var
  Frm_Base_Qry: TFrm_Base_Qry;

implementation

uses Sys_Global;

{$R *.DFM}

procedure TFrm_Base_Qry.GetTransValue(FieldName,OldValue:String;var NewValue:String);
var
  i:integer;
begin
  NewValue:=OldValue;
  if DBGridEh<>nil then
    for i:=0 to DBGridEh.Columns.Count-1 do
      if(DBGridEh.Columns[i].FieldName=FieldName)then
      begin
        if(DBGridEh.Columns[i].Field.DataType in [ftFloat])
          and(TFloatField(DBGridEh.Columns[i].Field).DisplayFormat<>'')
          and(OldValue<>'')then
        begin//处理 定义了打印格式的字段
          NewValue:=FormatFloat(TFloatField(DBGridEh.Columns[i].Field).DisplayFormat,StrToFloat(OldValue));
        end
        else if DBGridEh.Columns[i].KeyList.IndexOf(OldValue)<>-1 then
        begin//处理 pickList 与keyList
          NewValue:=DBGridEh.Columns[i].PickList.Strings[DBGridEh.Columns[i].
            KeyList.IndexOf(OldValue)];
        end;
        break;
      end;
end;

procedure TFrm_Base_Qry.Act_OrderExecute(Sender: TObject);
var
  BookMArk:TBookMArk;
begin//排序字段设置处理过程
  inherited;
  if DBGridEh.DataSource.DataSet.Active=True then
  begin
    Frm_Sys_SortOrder.InitForm(DBGridEH,OrderByFields);
    if(Frm_Sys_SortOrder.ShowModal=mrOk)then
    begin
      OrderByFields:=Frm_Sys_SortOrder.OrderFields;
      Lbl_Order.Caption:=Frm_Sys_SortOrder.OrderCaption;
      BookMArk:=DBGridEh.DataSource.DataSet.GetBookmArk;
      TAdoQuery(DBGridEh.DataSource.DataSet).Sort:=OrderByFields;
      DBGridEh.DataSource.DataSet.GotoBookmArk(BookMArk);
      DBGridEh.DataSource.DataSet.FreeBookMArk(BookMArk);
    end;
  end;
end;

procedure TFrm_Base_Qry.GetData;
var
  SQLText:String;
begin//SQL组合过程
  if SelectFromSQL<>'' then
  begin
    SQLText:=SelectFromSQL;
    if(Condition<>'')then
    begin
      if(ConditionUserDefine<>'')then
        SQLText:=SQLText+' Where '+Condition+' and '+ConditionUserDefine
      else
        SQLText:=SQLText+' Where '+Condition;
    end
    else if(ConditionUserDefine<>'')then
      SQLText:=SQLText+' Where '+ConditionUserDefine;
    if(GroupByFields<>'')then
      SQLText:=SQLText+' Group By '+GroupByFields;

    DBGridEh.DataSource.DataSet.DisableControls;
    DBGridEh.DataSource.DataSet.Close;
    if DBGridEh.DataSource.DataSet is TAdoQuery then
    begin
      TAdoQuery(DBGridEh.DataSource.DataSet).SQL.Text:=SQLText;
      TAdoQuery(DBGridEh.DataSource.DataSet).Open;
      TAdoQuery(DBGridEh.DataSource.DataSet).Sort:=OrderByFields;
    end
    else if DBGridEh.DataSource.DataSet is TAdODataSet then
    begin
      TAdODataSet(DBGridEh.DataSource.DataSet).CommandText:=SQLText;
      TAdODataSet(DBGridEh.DataSource.DataSet).Open;
      TAdODataSet(DBGridEh.DataSource.DataSet).Sort:=OrderByFields;
    end;
    DBGridEh.DataSource.DataSet.EnableControls;
  end;
end;

procedure TFrm_Base_Qry.Act_FilterExecute(Sender: TObject);
var
 TableName:string;
begin//数据过滤处理过程
  inherited;
  TableName:= '#'+self.Name   ;
  if Frm_Sys_Condition=nil then
    Frm_Sys_Condition:=TFrm_Sys_Condition.Create(Application);
  Frm_Sys_Condition.SetUserParam(Param1,Param2,Param3,Param4,Param5,Param6);
  Frm_Sys_Condition.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
  Frm_Sys_Condition.SetDBConnect(DBConnect);

  if Frm_Sys_Condition is TFrm_Sys_Condition then
    TFrm_Sys_Condition(Frm_Sys_Condition).SetSrcGrid(DBGridEh,TableName);

  Frm_Sys_Condition.FatherForm:=Self;
  if Frm_Sys_Condition.Showmodal=mrOk then
  begin
    Condition:=Frm_Sys_Condition.Condition;
    Lbl_Condition.Caption:=Frm_Sys_Condition.ConditionHint;
    GetData;
  end;
end;

procedure TFrm_Base_Qry.FormClose(Sender: TObject;
  var Action: TCloseAction);

begin
  inherited;
  try
    AdoQry_tmp.Close;
    AdoQry_tmp.SQL.clear;
    AdoQry_tmp.SQL.Text :='Drop Table '+ '#'+self.Name+','+'#'+self.Name+'Chb';
    AdoQry_tmp.ExecSQL;
  except
  end;
  Action:=CaFree;
end;

procedure TFrm_Base_Qry.Act_SetColumnExecute(Sender: TObject);
begin//显示列设置处理过程
  inherited;
  Frm_Sys_SetColumn.SetUserParam(Param1,Param2,Param3,Param4,Param5,Param6);
  Frm_Sys_SetColumn.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
  Frm_Sys_SetColumn.SetDBConnect(DBConnect);
  Frm_Sys_SetColumn.SetSrcGrid(DBGridEH,self.Name);
  Frm_Sys_SetColumn.ShowModal;
end;

procedure TFrm_Base_Qry.Act_LocateExecute(Sender: TObject);
begin//快速定位处理过程
  inherited;
  Frm_Sys_QuickLocate.SetSrcGrid(DBGridEh);
  Frm_Sys_QuickLocate.Showmodal;
  if Frm_Sys_QuickLocate.ModalResult=mrok then
  begin
    DBGridEh.DataSource.DataSet.Locate(Frm_Sys_QuickLocate.LocateFieldName,Frm_Sys_QuickLocate.LocateFieldValue,[loPArtialKey]);
  end;
end;

procedure TFrm_Base_Qry.SetReport;
var
  i,j,k:integer;
  GroupCaption,ColumnTitle:String;
begin
  if DBGridEh.DataSource.DataSet.Active=False then
    Abort;
  GroupCaption:='';
  ExtPrintReport.DataSet:=nil;
  ExtPrintReport.Headers.clear;
  i:=0;
  k:=-1;
  with ExtPrintReport do
  begin
    for j:=0 to DBGridEH.Columns.Count-1 do
      if DBGridEH.Columns[j].Visible then
      begin//只打印可视的数据
        Headers.Add;
        ColumnTitle:=DBGridEH.Columns[j].Title.Caption;
        Headers.Items[i].Caption :=Copy(ColumnTitle,Pos('|',ColumnTitle)+1,Length(ColumnTitle)-Pos('|',ColumnTitle));
        Headers.Items[i].FieldName :=DBGridEH.Columns[j].FieldName;
        Headers.Items[i].DisplayWidth:=DBGridEH.Columns[j].Width div (DBGridEH.Canvas.TextWidth(' '));
        Headers.Items[i].Alignment :=DBGridEH.Columns[j].Alignment;
        if((DBGridEh.Columns[j].PickList.Count>0)
          and(DBGridEh.Columns[j].KeyList.Count>0))
          or(DBGridEh.Columns[j].Field.DataType in [ftFloat])then
        begin//定义了PickList或显示格式的传递一个转换过程
          Headers.Items[i].Style:=dsTransForm;
          Headers.Items[i].TransformFunction:=GetTransValue;
        end
        else//通知子类
          SetColumnsStyle(i,DBGridEH.Columns[j].FieldName);
        if (DBGridEH.UseMultiTitle)and(Pos('|',DBGridEH.Columns[j].Title.Caption)<>0) then
        begin//多层表头的处理
          ColumnTitle:=Copy(DBGridEH.Columns[j].Title.Caption,1,Pos('|',DBGridEH.Columns[j].Title.Caption)-1);
          if GroupCaption<>ColumnTitle then
          begin
            Inc(k);
            GroupCaption:=ColumnTitle;
            HeaderGroups.Add;
            HeaderGroups.Items[k].Caption:=GroupCaption;
            HeaderGroups.Items[k].StArtCol:=i+1;
            HeaderGroups.Items[k].endCol:=i+1;
          end
          else
          begin
            HeaderGroups.Items[k].endCol:=i+1;
          end;
        end
        else
          GroupCaption:='';
        Inc(i);
      end;
    DataSet:=DBGridEh.DataSource.DataSet;
    InitReport;
  end;
end;

procedure TFrm_Base_Qry.Act_PreviewExecute(Sender: TObject);
begin//预览
  SetReport;
  ExtPrintReport.Preview;
end;

procedure TFrm_Base_Qry.Act_PrintExecute(Sender: TObject);
begin//打印
  SetReport;
  ExtPrintReport.Print(self);
end;

procedure TFrm_Base_Qry.SetColumnsStyle(ItemIndex:Integer;FieldName:String);
begin
end;

procedure TFrm_Base_Qry.InitReport;
begin//定义表头
  inherited;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select SysParamValueC'
    +' From SysParam'
    +' Where SysParamCode=''Name0''';
  AdoQry_Tmp.Open;
  ExtPrintReport.Title1:=AdoQry_Tmp.fieldbyname('SysParamValueC').AsString;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select ReportName1,ISOCode1'
    +' From ReportCtrl'
    +' Where SysMenuId='+MenuId;
  AdoQry_Tmp.Open;
  ExtPrintReport.Title2:=AdoQry_Tmp.fieldbyname('ReportName1').AsString;
  ExtPrintReport.SubTitle1:=AdoQry_Tmp.fieldbyname('ISOCode1').AsString;

  ExtPrintReport.Subtitle2:=Lbl_Condition.Caption;
end;

procedure TFrm_Base_Qry.FormCreate(Sender: TObject);
var
  StrTemp:String;
begin//,------>/   创建必要窗体
  inherited;
  TAdoQueryExpress(AdoQry_Main).CommandTimeOut:=0;
  StrTemp:=Lbl_Condition.Caption;
  while pos(',',StrTemp)<>0 do
    StrTemp[pos(',',StrTemp)]:='/';
  Lbl_Condition.Caption:=StrTemp;
  StrTemp:=Lbl_Order.Caption;
  while pos(',',StrTemp)<>0 do
    StrTemp[pos(',',StrTemp)]:='/';
  Lbl_Order.Caption:=StrTemp;
  Frm_Sys_SortOrder:=TFrm_Sys_SortOrder.Create(Application);
  Frm_Sys_QuickLocate:=TFrm_Sys_QuickLocate.Create(Application);
  Frm_Sys_SetColumn:=TFrm_Sys_SetColumn.Create(Application);
end;

procedure TFrm_Base_Qry.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
var i:integer;
    sqltext:string;
begin//设置数据库连接
  inherited;
  SetDBConnect(AdOConnection);
  TAdoQuery(DBGridEh.DataSource.DataSet).Connection:=AdOConnection;
  for i:=0 to toolBar.ControlCount-1 do
   if (Ttoolbutton(toolBar.controls[i]).action = act_SetColumn) and (act_SetColumn.Enabled and act_SetColumn.Visible) then
     begin
       sqltext:='select top 1 * from Sys_SetColumn '
               +' where formName='+quotedstr(self.Name)
               +'  and  useEmployeeCode='+quotedstr(userCode);
       Executesql(AdoQry_tmp,sqltext,0);
       if AdoQry_tmp.RecordCount<>0 then
       dbgrideh.FrozenCols:=AdoQry_tmp.fieldbyname('frozencols').asinteger;
     end;   
end;

procedure TFrm_Base_Qry.HideColumnClick(Sender: TObject);
begin//弹出菜单隐藏列处理
  inherited;
  if(DBGridEh.SelectedIndex>=0)then
    DBGridEh.Columns[DBGridEh.SelectedIndex].Visible:=False;
end;

procedure TFrm_Base_Qry.FormDestroy(Sender: TObject);
begin//善后处理,释放资源
  inherited;
  if Frm_Sys_Condition<>nil then
    Frm_Sys_Condition.Release;
  if Frm_Sys_SortOrder<>nil then
    Frm_Sys_SortOrder.Release;
  if Frm_Sys_QuickLocate<>nil then
    Frm_Sys_QuickLocate.Release;
  if Frm_Sys_SetColumn<>nil then
    Frm_Sys_SetColumn.Release;
end;

procedure TFrm_Base_Qry.Act_ExcelExecute(Sender: TObject);
begin//输出Excel
  inherited;
  DBGridEhToExcel(DBGridEh);
end;

end.