formbase.pas

批量修改约束关系, 只对sql server

UNIT FormBase;

INTERFACE

USES
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, StdCtrls,
  Dialogs, ImgList, RzButton, ExtCtrls, RzPanel, RzStatus, DB, ADODB, FindPublic,
  PrnDbgeh, DBGridEh, RzCommon, Menus, uKingFilter, FormColSelect, RzPrgres;

TYPE
  TBaseFrom=CLASS(TForm)
    RzToolbar1:TRzToolbar;
    BtnInsertRecord:TRzToolButton;
    BtnDeleteRecord:TRzToolButton;
    BtnEditRecord:TRzToolButton;
    BtnExit:TRzToolButton;
    Img:TImageList;
    StatBar:TRzStatusBar;
    Stat0:TRzStatusPane;
    Stat1:TRzStatusPane;
    Stat2:TRzStatusPane;
    StatSpace:TRzStatusPane;
    BtnCancel:TRzToolButton;
    RzSpacer1:TRzSpacer;
    RzSpacer2:TRzSpacer;
    RzSpacer3:TRzSpacer;
    PrintGrid:TPrintDBGridEh;
    BtnSave:TRzToolButton;
    FrameCtrl:TRzFrameController;
    btnData:TRzMenuButton;
    pmGrid:TPopupMenu;
    pmiShow:TMenuItem;
    pmiQuery:TMenuItem;
    pmiFilter:TMenuItem;
    N1:TMenuItem;
    pmiPrint:TMenuItem;
    N3:TMenuItem;
    pmiPreview:TMenuItem;
    N5:TMenuItem;
    pmiExport:TMenuItem;
    PBar:TRzProgressBar;
    pmCopy:TPopupMenu;
    pmiCopy:TMenuItem;
    PROCEDURE FormShow(Sender:TObject);
    PROCEDURE BtnFirstRecordClick(Sender:TObject);
    PROCEDURE BtnPreviousRecordClick(Sender:TObject);
    PROCEDURE BtnNextRecordClick(Sender:TObject);
    PROCEDURE BtnLastRecordClick(Sender:TObject);
    PROCEDURE BtnInsertRecordClick(Sender:TObject);
    PROCEDURE BtnDeleteRecordClick(Sender:TObject);
    PROCEDURE BtnEditRecordClick(Sender:TObject);
    PROCEDURE BtnExitClick(Sender:TObject);
    PROCEDURE BtnCancelClick(Sender:TObject);
    PROCEDURE FormCloseQuery(Sender:TObject; VAR CanClose:Boolean);
    PROCEDURE BtnSaveClick(Sender:TObject);
    PROCEDURE pmiShowClick(Sender:TObject);
    PROCEDURE pmiQueryClick(Sender:TObject);
    PROCEDURE pmiFilterClick(Sender:TObject);
    PROCEDURE FormClose(Sender:TObject; VAR Action:TCloseAction);
    PROCEDURE pmiExportClick(Sender:TObject);
    PROCEDURE pmiPreviewClick(Sender:TObject);
    PROCEDURE pmiPrintClick(Sender:TObject);
    PROCEDURE FormCreate(Sender:TObject);
    PROCEDURE pmiCopyClick(Sender:TObject);
  PRIVATE
    { Private declarations }
    bModi:Boolean;
    sFileName:STRING;
    PROCEDURE DataAfterScroll(DataSet:TDataSet);
    PROCEDURE DataBeforeEdit(DataSet:TDataSet);
    PROCEDURE DataBeforeInsert(DataSet:TDataSet);
    PROCEDURE GridTitleBtnClick(Sender:TObject; ACol:Integer; Column:TColumnEh);
  PUBLIC
    FFormName:STRING;
    AData:TDataSet;
    AGrid:TDBGridEh;
    ReportTitle:STRING;
    procedure InitShow;
    procedure InitClose;
    PROCEDURE GridSortMarkingChanged(Sender:TObject);
    PROCEDURE SetButtonStat;
    PROCEDURE SetColTitleToCenter(FGrid:TDBGridEh);
    PROCEDURE SetGridProp(FGrid:TDBGridEh);
    PROCEDURE SetIDFieldToReadonly(FGrid:TDBGridEh);
    FUNCTION OpenWorker(AList:TStrings):Integer; OVERLOAD;
    FUNCTION OpenWorker(AQ:TADOQuery):Boolean; OVERLOAD;
    FUNCTION OpenHouseKind(AList:TStrings):Integer;
    FUNCTION OpenHousePay(AList:TStrings):Integer;
    FUNCTION OpenWorthKind(AList:TStrings):Integer; //土地使用证
    PROCEDURE SetSelectList(FGrid:TDBGridEh); //置选择列表
    FUNCTION GetUseList(AList:TStrings):Integer;
    FUNCTION CopyRecord:Integer;
  END;

TYPE
  TTmpField=RECORD
    Name:STRING;
    value:Variant;
  END;

VAR
  BaseFrom:TBaseFrom;

IMPLEMENTATION

{$R *.dfm}

USES PublicFunction, DBGridEhImpExp, DBGridEhToExcel, yjkType, DM, ADOFuncs;

PROCEDURE TBaseFrom.FormShow(Sender:TObject);
BEGIN
  Self.InitShow;
END;

PROCEDURE TBaseFrom.BtnFirstRecordClick(Sender:TObject);
BEGIN
  IF AData<>NIL THEN
    AData.First;
END;

PROCEDURE TBaseFrom.BtnPreviousRecordClick(Sender:TObject);
BEGIN
  IF AData<>NIL THEN
    AData.Prior;
END;

PROCEDURE TBaseFrom.BtnNextRecordClick(Sender:TObject);
BEGIN
  IF AData<>NIL THEN
    AData.Next;
END;

PROCEDURE TBaseFrom.BtnLastRecordClick(Sender:TObject);
BEGIN
  IF AData<>NIL THEN
    AData.Last;
END;

PROCEDURE TBaseFrom.BtnInsertRecordClick(Sender:TObject);
BEGIN
  IF AData<>NIL THEN
    AData.Append;
  SetButtonStat;
END;

PROCEDURE TBaseFrom.BtnDeleteRecordClick(Sender:TObject);
BEGIN
  IF MyConfirmation('是否真的要删除该记录， 请谨慎操作....?') THEN
  BEGIN
    IF AData<>NIL THEN
      AData.Delete;
  END;
END;

PROCEDURE TBaseFrom.BtnEditRecordClick(Sender:TObject);
BEGIN
  IF AData<>NIL THEN
    AData.Edit;
  SetButtonStat;
END;

PROCEDURE TBaseFrom.BtnExitClick(Sender:TObject);
BEGIN
  Self.Close;
END;

PROCEDURE TBaseFrom.SetButtonStat;
VAR bHaveData:Boolean;
BEGIN
  {  bHaveData:=(AData.RecordCount>0);
    bModi:=(AData.State=dsEdit)OR(AData.State=dsInsert);
    BtnInsertRecord.Enabled:=AData.Active AND(AData.State=dsBrowse);
    BtnDeleteRecord.Enabled:=AData.Active AND(AData.State=dsBrowse)AND bHaveData;
    BtnEditRecord.Enabled:=AData.Active AND(AData.State=dsBrowse)AND bHaveData;
    BtnSave.Enabled:=AData.Active AND bModi;
    BtnCancel.Enabled:=AData.Active AND bModi; }
END;

PROCEDURE TBaseFrom.BtnCancelClick(Sender:TObject);
BEGIN
  IF AData<>NIL THEN
    AData.Cancel;
  SetButtonStat;
END;

PROCEDURE TBaseFrom.FormCloseQuery(Sender:TObject; VAR CanClose:Boolean);
BEGIN
  IF AData=NIL THEN
    CanClose:=true
  ELSE
  BEGIN
    bModi:=(AData.State=dsEdit)OR(AData.State=dsInsert);
    IF bModi THEN
    BEGIN
      IF MyConfirmation('数据未保存，是否真的要退出?') THEN
      BEGIN
        AData.Cancel;
        CanClose:=true;
      END
      ELSE
        MyInformation('请先保存增加或修改数据......');
      CanClose:=false;
    END;
  END;
END;

PROCEDURE TBaseFrom.BtnSaveClick(Sender:TObject);
BEGIN
  bModi:=(AData.State=dsEdit)OR(AData.State=dsInsert);
  IF bModi THEN
    AData.Post;
  SetButtonStat;
END;

PROCEDURE TBaseFrom.pmiShowClick(Sender:TObject);
BEGIN
  ShowGridColEditor(AGrid);
END;

PROCEDURE TBaseFrom.pmiQueryClick(Sender:TObject);
VAR
  S:STRING;
  i:Integer;
BEGIN
  IF AGrid IS TDBGridEh THEN
    FindPublicShow(TDBGridEh(AGrid).DataSource.DataSet, S, i);
END;

PROCEDURE TBaseFrom.pmiFilterClick(Sender:TObject);
VAR sFilter:STRING;
BEGIN
  IF AGrid IS TDBGridEh THEN
  BEGIN
    sFilter:=ShowFilterForm(TCustomADODataSet(TDBGridEh(AGrid).DataSource.DataSet));

    WITH TCustomADODataSet(AGrid.DataSource.DataSet) DO
    BEGIN
      Filtered:=false;
      Filter:=sFilter;
      Filtered:=true;
    END;
  END;
END;

PROCEDURE TBaseFrom.FormClose(Sender:TObject; VAR Action:TCloseAction);
BEGIN
  InitClose;
  Action:=caFree;
END;

PROCEDURE TBaseFrom.pmiExportClick(Sender:TObject);
VAR ExpClass:TDBGridEhExportClass;
  Ext:STRING;
  DBGridEhToExcel:TDBGridEhToExcel;
BEGIN
  IF AGrid=NIL THEN exit;

  FDM.dlgSave.FileName:='file1';
  FDM.dlgSave.InitialDir:=AppPath.Report;
  FDM.dlgSave.Filter:='*.txt||*.csv||*.htm||*.rtf||*.xls';
  FDM.dlgSave.FilterIndex:=5;
  {
   IF FDM.dlgSave.Execute THEN
   BEGIN
     CASE FDM.dlgSave.FilterIndex OF
       1:
         BEGIN
           ExpClass:=TDBGridEhExportAsText; Ext:='txt'; END;
       2:
         BEGIN
           ExpClass:=TDBGridEhExportAsCSV; Ext:='csv'; END;
       3:
         BEGIN
           ExpClass:=TDBGridEhExportAsHTML; Ext:='htm'; END;
       4:
         BEGIN
           ExpClass:=TDBGridEhExportAsRTF; Ext:='rtf'; END;
       5:
         BEGIN
           ExpClass:=TDBGridEhExportAsXLS; Ext:='xls'; END;
     ELSE
       ExpClass:=NIL; Ext:='';
     END;
     IF ExpClass<>NIL THEN
     BEGIN
       IF UpperCase(Copy(FDM.dlgSave.FileName, Length(FDM.dlgSave.FileName)-2, 3))<>
         UpperCase(Ext) THEN
         FDM.dlgSave.FileName:=FDM.dlgSave.FileName+'.'+Ext;
       SaveDBGridEhToExportFile(ExpClass, TDBGridEh(ActiveControl),
         FDM.dlgSave.FileName, false);
     END;
   END;  }

  DBGridEhToExcel:=TDBGridEhToExcel.Create(NIL);
  TRY
    DBGridEhToExcel.TitleName:=ReportTitle;
    DBGridEhToExcel.BeginDate:='开始日期：'; //+FormatDateTime('yyyy-MM-dd', edtBeginDate.Date);
    DBGridEhToExcel.EndDate:='结束日期：'; //+FormatDateTime('yyyy-MM-dd', edtEndDate.Date);
    DBGridEhToExcel.UserName:='';
    DBGridEhToExcel.DBGridEh:=AGrid;
    DBGridEhToExcel.ShowProgress:=true;
    FDM.dlgSave.Filter:='execl(*.xls)|*.xls';
    FDM.dlgSave.DefaultExt:='*.xls';
    FDM.dlgSave.InitialDir:=AppPath.Self;

    IF FDM.dlgSave.Execute THEN
      DBGridEhToExcel.FileName:=FDM.dlgSave.FileName;
    DBGridEhToExcel.ExportToExcel;
  FINALLY
    DBGridEhToExcel.Free;
  END;
END;

PROCEDURE TBaseFrom.pmiPreviewClick(Sender:TObject);
BEGIN
  IF AGrid<>NIL THEN
  BEGIN
    PrintGrid.DBGridEh:=AGrid;
    PrintGrid.Preview;
  END;
END;

PROCEDURE TBaseFrom.pmiPrintClick(Sender:TObject);
BEGIN
  IF AGrid<>NIL THEN
  BEGIN
    PrintGrid.DBGridEh:=AGrid;
    PrintGrid.print;
  END;
END;

PROCEDURE TBaseFrom.SetColTitleToCenter(FGrid:TDBGridEh);
VAR i:Integer;
BEGIN
  IF FGrid=NIL THEN exit;

  IF FGrid.Columns.Count<=1 THEN exit;

  FOR i:=0 TO FGrid.Columns.Count-1 DO
  BEGIN
    FGrid.Columns[i].Title.Alignment:=taCenter;
    FGrid.Columns[i].Layout:=tlCenter;
    FGrid.Columns[i].Title.Color:=clSkyBlue;
    FGrid.Columns[i].Title.Font.Name:='宋体';
    FGrid.Columns[i].Title.Font.Size:=10;
    FGrid.Columns[i].Title.Font.Style:= [fsBold];
    FGrid.Columns[i].Title.Font.Color:=clBlue;
    FGrid.Columns[i].Title.Font.Charset:=GB2312_CHARSET;
    FGrid.Columns[i].AlwaysShowEditButton:=true;
    FGrid.Columns[i].Title.TitleButton:=true;
    IF FGrid.Columns[i].Field<>NIL THEN
    BEGIN
      CASE FGrid.Columns[i].Field.DataType OF
        ftFloat, ftCurrency, ftBCD:FGrid.Columns[i].DisplayFormat:=',0.00';
        ftDate, ftDateTime:FGrid.Columns[i].DisplayFormat:='yyyy-MM-dd';
      END;
    END;
  END;
  Self.SetGridProp(FGrid);
  Self.SetIDFieldToReadonly(FGrid);
END;

PROCEDURE TBaseFrom.SetGridProp(FGrid:TDBGridEh);
BEGIN
  IF AGrid=NIL THEN exit;

  FGrid.OptionsEh:= [dghHighlightFocus, dghEnterAsTab, dghDblClickOptimizeColWidth,
    dghAutoSortMarking, dghMultiSortMarking, dghRowHighlight];
  FGrid.RowSizingAllowed:=true;
  FGrid.Flat:=true;
  FGrid.DrawMemoText:=true;
  FGrid.RowLines:=0;
  FGrid.TitleHeight:=30;
  FGrid.RowHeight:=24;
  FGrid.Font.Name:='宋体';
  FGrid.Font.Size:=10;
  FGrid.Font.Charset:=GB2312_CHARSET;
  FGrid.Align:=alClient;
  FGrid.Flat:=true;

  Self.SetSelectList(FGrid);
END;

PROCEDURE TBaseFrom.SetIDFieldToReadonly(FGrid:TDBGridEh);
VAR i:Integer;
BEGIN
  IF FGrid=NIL THEN exit;
  {
    FOR i:=0 TO FGrid.Columns.Count-1 DO
    BEGIN
      IF FGrid.Columns[i].Field.FieldName='序号' THEN
        FGrid.Columns[i].Field.ReadOnly:=true;
      IF FGrid.Columns[i].Field.FieldName='录入人' THEN
        FGrid.Columns[i].Visible:=false;
      IF FGrid.Columns[i].Field.FieldName='审核人' THEN
        FGrid.Columns[i].Visible:=false;
    END;      }
END;

PROCEDURE TBaseFrom.DataAfterScroll(DataSet:TDataSet);
BEGIN
  SetButtonStat;
END;