bank.pas

来自「pasa人力资源考勤管理系统」· PAS 代码 · 共 334 行

unit bank;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ImgList, ExtCtrls, DBCtrls, ComCtrls, ToolWin, Db, ADODB, Grids, DBGrids,
  StdCtrls, Mask,shellapi, OleServer, Word97,publicfunction,Excel97;

type
  TFormbank = class(TForm)
    export: TButton;
    Button2: TButton;
    Qrybank: TADOQuery;
    Label3: TLabel;
    Edit2: TMaskEdit;
    pbar1: TProgressBar;
    GroupBox2: TGroupBox;
    excel: TRadioButton;
    Button1: TButton;
    qry1: TADOQuery;
    word: TRadioButton;
    ExcelApplication1: TExcelApplication;
    CheckBox1: TCheckBox;
    SaveDialog1: TSaveDialog;
    procedure Button2Click(Sender: TObject);
    procedure exportClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormActivate(Sender: TObject);
    procedure formkeyup(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure Button1Click(Sender: TObject);
    procedure ExportData(Table:TDataset);

  private
    { Private declarations }
    sp,pe:string;
  public
    { Public declarations }
  end;

var
  Formbank: TFormbank;

implementation

uses main,datamol,cvcode;
{$R *.DFM}

procedure TFormbank.Button2Click(Sender: TObject);
begin
     close;
end;

procedure TFormbank.ExportData(Table:TDataset);
var
  RangeE: Excel97.Range;
  I, Row: Integer;
  Bookmark: TBookmarkStr;
  s,s1:integer;
  AdoQuery1:TAdoquery;

begin
try
  AdoQuery1:=TAdoquery.Create(Application);
  Adoquery1.Connection:=datamod.Database1;
  Adoquery1.Close;
//离职资料的人员
  Adoquery1.SQL.Add(' select count(per15010.workno) workno from per15010  where not  per15010.workno in(select workno from per16010) ');
  AdoQuery1.Open;
  s:=Adoquery1.fieldbyname('workno').AsInteger ;
  Adoquery1.Close;
  Adoquery1.SQL.Clear;
  Adoquery1.SQL.Add('select count(workno) workno  from per24010 where leave=0');
  Adoquery1.Open;
  s1:=Adoquery1.fieldbyname('workno').AsInteger ;

  ExcelApplication1.Visible [1] := True;
  ExcelApplication1.Workbooks.Add (NULL, 0);
  RangeE := ExcelApplication1.ActiveCell;
   rangee.value:='公司名称：'+pe;
   rangee:=rangee.next;
   rangee:=rangee.next;
   rangee.value:='总户数：'+qry1.fieldbyname('c').asstring;
   rangee:=rangee.next;
   rangee:=rangee.next;
   rangee.value:='总金额：'+qry1.fieldbyname('factmoney').asstring;
  row:=2 ;
  RangeE := ExcelApplication1.Range ['A' + IntToStr (Row),'A' + IntToStr (Row)];

  for I := 0 to Table.Fields.Count - 1 do
  begin
    RangeE.Value := Table.Fields [I].DisplayLabel;
    RangeE := RangeE.Next;
  end;
{    RangeE.value:=companyname1;
    RangeE.value:=s+s1;
    Adoquery1.Close;
    Adoquery1.Free; }
  Table.DisableControls;
  try
    Bookmark := Table.Bookmark;
    try
      if Table.State=dsInactive then
        Table.Open;
      Table.First;
      Row :=3;

      while not Table.EOF do
      begin
        RangeE := ExcelApplication1.Range ['A' + IntToStr (Row),'A' + IntToStr (Row)];
        for I := 0 to Table.Fields.Count - 1 do
        begin
          if Table.Fields[i].DataType=ftString then
             if numstring(table.fields[i].AsString) or (length(table.fields[i].asstring)>10) then
                RangeE.Value:='~'+Table.Fields[I].AsString
                  //RangeE.Value:=Table.Fields[I].AsString
             else
                RangeE.Value:=Table.Fields[I].AsString
          else
             RangeE.Value:=Table.Fields[i].Value;
          RangeE:=RangeE.next;
        end;
        Table.Next;
        Inc (Row);
      end;
    finally
      Table.Bookmark := Bookmark;
    end;
  finally
    Table.EnableControls;
  end;
  //RangeE.AutoFormat(0,NULL,Null,null,null,null,null);
except
 ;
end;
end;


procedure TFormbank.exportClick(Sender: TObject);
var
   b:Textfile;
   p,sp,text:string;
   handle:integer;
begin
    qry1.Close;
    qry1.SQL.Clear;
    qry1.Sql.Text:='Select * from pub01010';
    qry1.Open;  //在状态栏中要显示的参数
    pe:=qry1.Fieldbyname('name1').asstring;
    qry1.Close;
    qry1.SQL.Clear;
    if checkbox1.Checked then
     qry1.SQL.Add('select count(b.workno) c,sum(b.factmoney) factmoney from per24010 a left join sal21010 b on a.workno=b.workno where b.monthy='''+edit2.Text+'''')
    else
     qry1.SQL.Add('select count(b.workno) c,sum(b.factmoney) factmoney from per24010 a left join sal21010 b on a.workno=b.workno where b.monthy='''+edit2.Text+''' and isnull(a.account,'''')<>''''');
    qry1.Open;

  if word.Checked=true  then
  begin
    if SaveDialog1.Execute then
     p:=savedialog1.FileName+'.txt';
    //p:=extractfilepath(application.exename)+'data\bank.txt';
    if not fileexists(p) then
    begin
            handle:=FileCreate(p);
            if handle>0 then
                FileClose(Handle);
    end;
    assignfile(b,p);
    rewrite(b);
    append(b);
    qrybank.Close;
    qrybank.SQL.Clear;
    qrybank.SQL.Add('select * From bankset');
    qrybank.Open;
    if qrybank.RecordCount=0 then
      application.MessageBox('对不起，请先设好参数！','提示',mb_ok+mb_iconwarning)
    else
    begin
       if qrybank.FieldByName('head').asboolean then
       begin
        text:=copychar(' ',qrybank.fieldbyname('headtext1').asinteger-1)+copy(pe,1,qrybank.fieldbyname('headtext1count').asinteger)+copychar(' ',qrybank.fieldbyname('headtext1count').asinteger-length(pe));
        text:=text+copychar(' ',qrybank.fieldbyname('headtext2').asinteger-length(text)-1)+copy(qry1.fieldbyname('c').asstring,1,qrybank.fieldbyname('headtext2count').asinteger)+copychar(' ',qrybank.fieldbyname('headtext2count').asinteger-length(qry1.fieldbyname('c').asstring));
        text:=text+copychar(' ',qrybank.fieldbyname('headtext3').asinteger-length(text)-1)+copy(qry1.fieldbyname('factmoney').asstring,1,qrybank.fieldbyname('headtext3count').asinteger)+copychar(' ',qrybank.fieldbyname('headtext3count').asinteger-length(qry1.fieldbyname('factmoney').asstring));
        text:=text+'51';//copychar(' ',qrybank.fieldbyname('headtext4').asinteger-length(text)-1)+copy(qry1.fieldbyname(;
        writeln(b,text);
       end;
      qry1.Close;
      qry1.SQL.Clear;
      qry1.SQL.Add('select a.workno,a.account,a.name,a.peopleID, b.factmoney from per24010 a left join sal21010 b on a.workno=b.workno where b.monthy='''+edit2.Text+''' and isnull(a.account,'''')<>''''');
      qry1.Open;
      qry1.First;
      while not qry1.Eof do
      begin
        append(b);
        text:=copychar(' ',qrybank.fieldbyname('data1').asinteger-1)+copy(qry1.fieldbyname('workno').asstring,1,qrybank.fieldbyname('data1count').asinteger)+copychar(' ',qrybank.fieldbyname('data1count').asinteger-length(qry1.fieldbyname('workno').asstring));
        text:=text+copychar(' ',qrybank.fieldbyname('data2').asinteger-length(text)-1)+copy(qry1.fieldbyname('account').asstring,1,qrybank.fieldbyname('data2count').asinteger)+copychar(' ',qrybank.fieldbyname('data2count').asinteger-length(qry1.fieldbyname('account').asstring));
        text:=text+copychar(' ',qrybank.fieldbyname('data3').asinteger-length(text)-1)+copy(qry1.fieldbyname('name').asstring,1,qrybank.fieldbyname('data3count').asinteger)+copychar(' ',qrybank.fieldbyname('data3count').asinteger-length(qry1.fieldbyname('name').asstring));
        text:=text+copychar(' ',qrybank.fieldbyname('data4').asinteger-length(text)-1)+copy(qry1.fieldbyname('peopleid').asstring,1,qrybank.fieldbyname('data4count').asinteger)+copychar(' ',qrybank.fieldbyname('data4count').asinteger-length(qry1.fieldbyname('peopleid').asstring));
        text:=text+copychar(' ',qrybank.fieldbyname('data5').asinteger-length(text)-1)+copy(qry1.fieldbyname('factmoney').asstring,1,qrybank.fieldbyname('data5count').asinteger)+copychar(' ',qrybank.fieldbyname('data5count').asinteger-length(qry1.fieldbyname('factmoney').asstring));
        text:=text+'51';//copychar(' ',qrybank.fieldbyname('headtext4').asinteger-length(text)-1)+copy(qry1.fieldbyname(;

        writeln(b,text);
        qry1.Next;
      end;
      closefile(b);
      shellexecute(handle,'open',pchar(p),'','',SW_SHOWMaximized);
    end;
  end
  else if excel.Checked=true then
  begin
    qrybank.Close;
    qrybank.SQL.Clear;
    qrybank.SQL.Add('select a.workno,a.account,a.name,a.peopleID, b.factmoney from per24010 a left join sal21010 b on a.workno=b.workno where b.monthy='''+edit2.Text+''' and isnull(a.account,'''')<>''''');
    qrybank.Open;
    formbank.exportdata(qrybank);
  end;
end;
procedure TFormbank.FormCreate(Sender: TObject);
begin
  Edit2.text:=formatdatetime('yyyy/mm',now);
end;
procedure TFormbank.FormClose(Sender: TObject; var Action: TCloseAction);
begin
     formbank:=nil;
     action:=cafree;
end;
procedure TFormbank.FormActivate(Sender: TObject);
begin
     left:=10;
     top:=5;
end;
procedure TFormbank.formkeyup(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if key=vk_f1 then
  begin
   Application.helpfile :=extractfilepath(application.exename) +'help\pasahelp.hlp';
   application.HelpJump('shm_contents0037');
  end;
end;
procedure TFormbank.Button1Click(Sender: TObject);
var
   b:Textfile;
   p,sp,text:string;
   handle:integer;
begin

  if word.Checked=true  then
  begin
    if SaveDialog1.Execute then
     p:=savedialog1.FileName+'.txt';
    //p:=extractfilepath(application.exename)+'data\bank.txt';
    if not fileexists(p) then
    begin
            handle:=FileCreate(p);
            if handle>0 then
                FileClose(Handle);
    end;
    assignfile(b,p);
    if  checkbox1.Checked=false then
    begin
        qry1.Close;
        qry1.SQL.Clear;
        qry1.Sql.Text:='Select * from pub01010';
        qry1.Open;  //在状态栏中要显示的参数
        pe:=qry1.Fieldbyname('name1').asstring;
        qry1.Close;
        qry1.SQL.Clear;
        qry1.SQL.Add('select count(b.workno) c,sum(b.factmoney) factmoney from per24010 a left join sal21010 b on a.workno=b.workno where b.monthy='''+edit2.Text+''' and isnull(a.account,'''')=''''');
        qry1.Open;
        rewrite(b);
        append(b);
        qrybank.Close;
        qrybank.SQL.Clear;
        qrybank.SQL.Add('select * From bankset');
        qrybank.Open;
        if qrybank.RecordCount=0 then
          application.MessageBox('对不起，请先设好参数！','提示',mb_ok+mb_iconwarning)
        else
           if qrybank.FieldByName('head').asboolean then
           begin
            text:=copychar(' ',qrybank.fieldbyname('headtext1').asinteger-1)+copy(pe,1,qrybank.fieldbyname('headtext1count').asinteger)+copychar(' ',qrybank.fieldbyname('headtext1count').asinteger-length(pe));
            text:=text+copychar(' ',qrybank.fieldbyname('headtext2').asinteger-length(text)-1)+copy(qry1.fieldbyname('c').asstring,1,qrybank.fieldbyname('headtext2count').asinteger)+copychar(' ',qrybank.fieldbyname('headtext2count').asinteger-length(qry1.fieldbyname('c').asstring));
            text:=text+copychar(' ',qrybank.fieldbyname('headtext3').asinteger-length(text)-1)+copy(qry1.fieldbyname('factmoney').asstring,1,qrybank.fieldbyname('headtext3count').asinteger)+copychar(' ',qrybank.fieldbyname('headtext3count').asinteger-length(qry1.fieldbyname('factmoney').asstring));
            text:=text+'51';//copychar(' ',qrybank.fieldbyname('headtext4').asinteger-length(text)-1)+copy(qry1.fieldbyname(;
            writeln(b,text);
           end;
      end;
        append(b);
        qrybank.Close;
        qrybank.SQL.Clear;
        qrybank.SQL.Add('select * From bankset');
        qrybank.Open;
        if qrybank.RecordCount=0 then
          application.MessageBox('对不起，请先设好参数！','提示',mb_ok+mb_iconwarning)
        else
        begin
            qry1.Close;
            qry1.SQL.Clear;
            qry1.SQL.Add('select a.workno,a.account,a.name,a.peopleID, b.factmoney from per24010 a left join sal21010 b on a.workno=b.workno where b.monthy='''+edit2.Text+''' and isnull(a.account,'''')=''''');
            qry1.Open;
            qry1.First;
            while not qry1.Eof do
            begin
              append(b);
              text:=copychar(' ',qrybank.fieldbyname('data1').asinteger-1)+copy(qry1.fieldbyname('workno').asstring,1,qrybank.fieldbyname('data1count').asinteger)+copychar(' ',qrybank.fieldbyname('data1count').asinteger-length(qry1.fieldbyname('workno').asstring));
              text:=text+copychar(' ',qrybank.fieldbyname('data2').asinteger-length(text)-1)+copy(qry1.fieldbyname('account').asstring,1,qrybank.fieldbyname('data2count').asinteger)+copychar(' ',qrybank.fieldbyname('data2count').asinteger-length(qry1.fieldbyname('account').asstring));
              text:=text+copychar(' ',qrybank.fieldbyname('data3').asinteger-length(text)-1)+copy(qry1.fieldbyname('name').asstring,1,qrybank.fieldbyname('data3count').asinteger)+copychar(' ',qrybank.fieldbyname('data3count').asinteger-length(qry1.fieldbyname('name').asstring));
              text:=text+copychar(' ',qrybank.fieldbyname('data4').asinteger-length(text)-1)+copy(qry1.fieldbyname('peopleid').asstring,1,qrybank.fieldbyname('data4count').asinteger)+copychar(' ',qrybank.fieldbyname('data4count').asinteger-length(qry1.fieldbyname('peopleid').asstring));
              text:=text+copychar(' ',qrybank.fieldbyname('data5').asinteger-length(text)-1)+copy(qry1.fieldbyname('factmoney').asstring,1,qrybank.fieldbyname('data5count').asinteger)+copychar(' ',qrybank.fieldbyname('data5count').asinteger-length(qry1.fieldbyname('factmoney').asstring));
              text:=text+'51';//copychar(' ',qrybank.fieldbyname('headtext4').asinteger-length(text)-1)+copy(qry1.fieldbyname(;

              writeln(b,text);
              qry1.Next;
            end;
            closefile(b);
            shellexecute(handle,'open',pchar(p),'','',SW_SHOWMaximized);
        end;
  end
  else if excel.Checked=true then
  begin
    qrybank.Close;
    qrybank.SQL.Clear;
    qrybank.SQL.Add('select a.workno,a.account,a.name,a.peopleID, b.factmoney from per24010 a left join sal21010 b on a.workno=b.workno where b.monthy='''+edit2.Text+''' and isnull(a.account,'''')=''''');
    qrybank.Open;
    formbank.exportdata(qrybank);
  end;
end;

end.