mainunit.pas

通过delphi和sql的使用

unit MainUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Menus, Grids, DBGrids, DB, ADODB, ComCtrls, ExtCtrls, StdCtrls,
  DBCtrls, Buttons, ImgList,comobj,shellApi, FR_Class, FR_DSet, FR_DBSet;

type
  TMainForm = class(TForm)
    MainMenu1: TMainMenu;
    ADOTable1: TADOTable;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    FileMenu: TMenuItem;
    ExitSubMenu: TMenuItem;
    Edit1: TMenuItem;
    View1: TMenuItem;
    Help1: TMenuItem;
    StatusBar1: TStatusBar;
    Timer1: TTimer;
    N1: TMenuItem;
    PassSubMenu: TMenuItem;
    About1: TMenuItem;
    ADOQuery1: TADOQuery;
    DateTimePicker1: TDateTimePicker;
    Label1: TLabel;
    Edit2: TEdit;
    Label2: TLabel;
    Label3: TLabel;
    Edit3: TEdit;
    Memo1: TMemo;
    Label5: TLabel;
    Panel1: TPanel;
    FirstBtn: TSpeedButton;
    PriorBtn: TSpeedButton;
    LastBtn: TSpeedButton;
    NextBtn: TSpeedButton;
    ModifBtn: TSpeedButton;
    AddBtn: TSpeedButton;
    SaveBtn: TSpeedButton;
    CancelBtn: TSpeedButton;
    PrintBtn: TSpeedButton;
    ImageList1: TImageList;
    FirstSubMenu: TMenuItem;
    PriorSubMenu: TMenuItem;
    NextSubMenu: TMenuItem;
    LastSubMenu: TMenuItem;
    N6: TMenuItem;
    AddSubMenu: TMenuItem;
    ModiSubMenu: TMenuItem;
    SaveSubMenu: TMenuItem;
    N10: TMenuItem;
    DelSubMenu: TMenuItem;
    N12: TMenuItem;
    PrintSubMenu: TMenuItem;
    CompressSubMenu: TMenuItem;
    RepairSubMenu: TMenuItem;
    HelpSubMenu: TMenuItem;
    N17: TMenuItem;
    DelBtn: TSpeedButton;
    CancelSubMenu: TMenuItem;
    ADOTable2: TADOTable;
    Label4: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    InEdit: TEdit;
    OutEdit: TEdit;
    N2: TMenuItem;
    ExcelSubMenu: TMenuItem;
    OverEdit: TEdit;
    SaveDialog1: TSaveDialog;
    Panel2: TPanel;
    Label8: TLabel;
    ProgressBar1: TProgressBar;
    N3: TMenuItem;
    RunNotepadMenu: TMenuItem;
    RunCalcMenu: TMenuItem;
    ADOConnection1: TADOConnection;
    frReport1: TfrReport;
    frDBDataSet1: TfrDBDataSet;
    procedure ExitSubMenuClick(Sender: TObject);
    procedure Timer1Timer(Sender: TObject);
    procedure FormResize(Sender: TObject);
    procedure About1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure PassSubMenuClick(Sender: TObject);
    procedure FirstSubMenuClick(Sender: TObject);
    procedure PriorSubMenuClick(Sender: TObject);
    procedure NextSubMenuClick(Sender: TObject);
    procedure LastSubMenuClick(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure AddSubMenuClick(Sender: TObject);
    procedure ModiSubMenuClick(Sender: TObject);
    procedure SaveSubMenuClick(Sender: TObject);
    procedure DelSubMenuClick(Sender: TObject);
    procedure CancelSubMenuClick(Sender: TObject);
    procedure PrintSubMenuClick(Sender: TObject);
    procedure Edit2Exit(Sender: TObject);
    procedure Edit3Exit(Sender: TObject);
    procedure Edit2KeyPress(Sender: TObject; var Key: Char);
    procedure DBGrid1KeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure FormDestroy(Sender: TObject);
    procedure CompressSubMenuClick(Sender: TObject);
    procedure RepairSubMenuClick(Sender: TObject);
    procedure ExcelSubMenuClick(Sender: TObject);
    procedure HelpSubMenuClick(Sender: TObject);
    procedure RunNotepadMenuClick(Sender: TObject);
    procedure RunCalcMenuClick(Sender: TObject);
  private
    { Private declarations }
    fn:string;
    IsAdd:integer;              //1为新增，2为修改
    procedure SetupStatusBar ;
    procedure AffirmSecurity ;
    Procedure OpenTable;
    Procedure ShowMoney;
    procedure showrecord;
    function  CompressRepairMDB:Boolean;
    Function  ToExcel(const fn:string):boolean;
    procedure showHint(sender:TObject);

  public
    { Public declarations }

  end;

var
  MainForm: TMainForm;

Const
  link='Provider=Microsoft.Jet.OLEDB.4.0;'+
        'Data Source=%s;Persist Security Info=False;'+
        'Jet OLEDB:Database Password=dltjy';

implementation

uses About, Pass, SetupPass, guide;


{$R *.dfm}

procedure TMainForm.ExitSubMenuClick(Sender: TObject);
begin
  close;
end;

procedure TMainForm.SetupStatusBar;
begin
   statusbar1.Panels[6].Width:=120;
   statusbar1.Panels[5].Width:=70;
   statusbar1.Panels[4].Width:=50;
   statusbar1.Panels[3].Width:=70;
   statusbar1.Panels[2].Width:=50;
   statusbar1.Panels[1].Width:=70;
   statusbar1.Panels[0].Width:=statusbar1.Width-statusbar1.Panels[6].Width
                               -statusbar1.Panels[5].Width
                               -statusbar1.Panels[4].Width
                               -statusbar1.Panels[3].Width
                               -statusbar1.Panels[2].Width
                               -statusbar1.Panels[1].Width  ;
end;

procedure TMainForm.Timer1Timer(Sender: TObject);
begin
  //statusbar1.Panels[6].Text:=DateToStr(Date())+' '+TimeToStr(time());
  StatusBar1.Panels[6].Text:=DateTimeToStr(now);

end;

procedure TMainForm.FormResize(Sender: TObject);
begin
  setupstatusbar();

end;

procedure TMainForm.About1Click(Sender: TObject);
begin
  AboutBox:=TaboutBox.Create(self);
  AboutBox.ShowModal;
  AboutBox.Free;
end;

procedure TMainForm.FormCreate(Sender: TObject);
begin
   Application.OnHint:=showHint;
   Panel2.Visible:=false;
   fn:=Extractfilepath(application.ExeName)+'Data.mdb';
   isAdd:=0;
   ADOConnection1.ConnectionString:=format(link,[fn]);
   ADOConnection1.Connected:=true;

   AffirmSecurity;
   OpenTable;

end;

procedure TMainForm.PassSubMenuClick(Sender: TObject);      //修改登陆密码
var
  oldpass,newpass:string;
  label AA1;
begin
  Form2:=Tform2.Create(self);
  AA1: IF form2.ShowModal<>mrOK then
       begin
         form2.Free;
         exit;
       end;

  if (form2.NewPassEdit.Text='') or (form2.AffirmPassEdit.Text='') then
  begin
     messagedlg('密码不能为空！！',mtError,[mbOK],0);
     goto AA1;
  end;

  if form2.NewPassEdit.Text<>form2.AffirmPassEdit.Text then
  begin
     messagedlg('两个新密码不匹配！！',mtError,[mbOK],0);
     goto AA1;
  end;

  oldpass:=Form2.OldPassEdit.Text;
  newpass:=Form2.NewPassEdit.Text;
  form2.Free;

  ADOTable1.Close;
  //ADOTable1.ConnectionString:=format(link,[fn]);
  ADOTable1.TableName:='pass';
  ADOTable1.Open;
  ADOTable1.First;

   if oldpass<>ADOTable1.FieldByName('password').AsString then
   begin
      messagedlg('密码错误！！'+#13+'密码修改不成功！！',mtError,[mbOK],0);
      AdoTable1.Close;
      Exit;
   end ;

   ADOTable1.Edit;
   ADOTable1.FieldByName('password').AsString:=newpass;
   ADOTable1.Post;
   ADOTable1.Close;
   messagedlg('密码修改成功！！',mtInformation,[mbOK],0);

end;

procedure TMainForm.AffirmSecurity;         //登陆密码验证
var
  ps:string;
begin
   Form1:=TForm1.Create(self);
   if Form1.ShowModal<>mrOK then
   begin
       application.Terminate;
       exit;
   end;
   ps:=Form1.Edit1.Text;
   Form1.Free;

   AdoTable1.Close;
   //AdoTable1.ConnectionString:=format(link,[fn]);
   AdoTable1.TableName:='pass';
   AdoTable1.Open;
   AdoTable1.First;

   if ps<>ADOTable1.FieldByName('password').AsString then
   begin
      messagedlg('密码错误！！',mtError,[mbOK],0);
      AdoTable1.Close;
      Application.Terminate;
   end ;
   ADOTable1.Close;

end;

procedure TMainForm.OpenTable;
Var
   i:integer;
   FCount:integer;
begin
   DBGrid1.DataSource:=Datasource1;
   DataSource1.DataSet:=ADOTable2;
   Datasource1.Enabled:=True;
   ADOTable2.Close;
   //ADOTable2.ConnectionString:=format(link,[fn]);
   AdoTable2.TableName:='Account';

   ADOTable2.Open;
   ADOTable2.First;

   FCount:=ADOTable2.FieldCount;

   for i:=0 to FCount-1  do
   begin
       DBGrid1.Columns[i].Width:=135;
       DBGrid1.Columns[i].Title.Alignment := taCenter ;
       DBGrid1.Columns[i].Title.Caption:=ADOTable2.Fields[i].FieldName;
   end;

   showrecord;
   showmoney;

  FirstBtn.Enabled:=false;
  priorBtn.Enabled:=False;
  NextBtn.Enabled:=true;
  LastBtn.Enabled:=true;
  ModifBtn.Enabled:=true;
  AddBtn.Enabled:=True;
  SaveBtn.Enabled:=false;
  CancelBtn.Enabled:=False;
  DelBtn.Enabled:=True;
  PrintBtn.Enabled:=True;

  FirstSubMenu.Enabled:=False;
  priorsubmenu.Enabled:=False;
  NextSubMenu.Enabled:=true;
  LastSubMenu.Enabled:=true;
  ModiSubMenu.Enabled:=true;
  AddSubMenu.Enabled:=True;
  SaveSubMenu.Enabled:=false;
  CancelSubMenu.Enabled:=false;
  DelSubMenu.Enabled:=True;
  PrintSubMenu.Enabled:=True;

end;

Procedure TMainForm.ShowMoney ;      //统计金额
var
  inMoney,OutMoney :real ;
  CurrentRecord    :TBookMark;
begin
   IF not ADOTable2.Active  then
   begin
      ADOTable2.Close;
      //ADOTable2.ConnectionString:=format(link,[fn]);
      ADOTable2.TableName:='Account';
      ADOTable2.Open;
   end;

   Screen.Cursor:=crHourGlass;
   CurrentRecord:=ADOTable2.GetBookmark;
   ADOTable2.DisableControls;
   ADOTable2.First;
   inMoney:=0.00;
   OutMoney:=0.00;
   while not ADOTable2.Eof do
   begin
      inMoney:=InMoney+ADOTable2.Fields[1].AsFloat;
      OutMoney:=OutMoney+ADOTable2.Fields[2].AsFloat;
      ADOTable2.Next;
   end;
   ADOTable2.EnableControls;
   ADOTable2.GotoBookmark(CurrentRecord);
   ADOTable2.FreeBookmark(CurrentRecord);

   Inedit.Text:=floattostr(inmoney);
   OutEdit.Text:=FloatToStr(OutMoney);
   OverEdit.Text:=FloatToStr(InMoney-OutMoney);
   statusbar1.Panels[2].Text:=inttostr(ADOTable2.RecordCount) ;
   statusbar1.Panels[4].Text:=OverEdit.Text;
   Screen.Cursor:=crDefault;
end;

procedure TMainForm.FirstSubMenuClick(Sender: TObject);
begin
  if not ADOTable2.Active then exit;
  ADOTable2.First;
  showrecord;

  FirstBtn.Enabled:=false;
  priorBtn.Enabled:=False;
  NextBtn.Enabled:=true;
  LastBtn.Enabled:=true;
  ModifBtn.Enabled:=true;
  AddBtn.Enabled:=True;
  SaveBtn.Enabled:=false;
  CancelBtn.Enabled:=false;
  DelBtn.Enabled:=True;
  PrintBtn.Enabled:=True;