skycwbb1.pas

来自「日昌餐饮管理系统是用Delphi7+SQL写的管理系统」· PAS 代码 · 共 345 行

unit skycwbb1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, ComCtrls, DBTables, DB, QRCtrls, QuickRpt,
  ExtCtrls, Grids, DBGrids, ADODB, Excel2000, OleServer;

type
  TskyForm1 = class(TForm)
    Label2: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    DataSource1: TDataSource;
    Label7: TLabel;
    ADODataSet1: TADODataSet;
    DBGrid3: TDBGrid;
    prtsetQ: TADOQuery;
    BitBtn4: TBitBtn;
    ExcelApplication1: TExcelApplication;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    ADODataSet2: TADODataSet;
    ADODataSet3: TADODataSet;
    Label1: TLabel;
    ComboBox1: TComboBox;
    ADOQuery1: TADOQuery;
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    function writcf(port:string;disp_info:string):integer;
    function truc(port:string;disp_info:string):integer;
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure ADODataSet1FilterRecord(DataSet: TDataSet;
      var Accept: Boolean);
  private
    xx:string;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  skyForm1: TskyForm1;

implementation
uses rjsmain;
{$R *.dfm}

procedure TskyForm1.BitBtn1Click(Sender: TObject);
begin
     if combobox1.Text='' then exit;
     adodataset1.Close;
     adodataset1.Filtered:=false;
     if combobox1.Text<>'*全部*' then
       adodataset1.CommandText:=adodataset3.CommandText
     else adodataset1.CommandText:=adodataset2.CommandText;
     adodataset1.Parameters.ParamByName('cs1').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs2').Value:=datetimepicker2.DateTime;
//     adodataset1.Parameters.ParamByName('cs3').Value:=datetimepicker1.DateTime;
//     adodataset1.Parameters.ParamByName('cs4').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs5').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs6').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs7').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs8').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs9').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs10').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs11').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs12').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs13').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs14').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs15').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs16').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs17').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs18').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs19').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs20').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs21').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs22').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs23').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs24').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs25').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs26').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs27').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs28').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs29').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs30').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs31').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs32').Value:=datetimepicker2.DateTime;
     adodataset1.Parameters.ParamByName('cs33').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs34').Value:=datetimepicker2.DateTime;
     adodataset1.Open;
     if combobox1.Text<>'*全部*' then
       adodataset1.Filtered:=true
     else adodataset1.Filtered:=false;
     bitbtn2.Enabled:=true;
end;

procedure TskyForm1.BitBtn3Click(Sender: TObject);
begin
     close;
end;

procedure TskyForm1.FormActivate(Sender: TObject);
begin
     if xx='xx' then exit;
     xx:='xx';
     bitbtn2.Enabled:=false;
     datetimepicker1.Date:=date;
     datetimepicker1.Time:=strtotime('00:00:00');
     datetimepicker2.Date:=date;
     datetimepicker2.Time:=strtotime('23:59:59');
     adoquery1.Close;
     adoquery1.SQL.Clear;
     adoquery1.SQL.Add('select * from userwj order by yhm ');
     adoquery1.Open;
     combobox1.Items.Clear;
     combobox1.Text:='';
     combobox1.Items.Add('*全部*');
     while not adoquery1.Eof do
     begin
       combobox1.Items.Add(adoquery1.FieldValues['yhmc']);
       adoquery1.Next;
     end;
     adodataset1.Close;
end;

procedure TskyForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
     xx:='';
     adoquery1.Close;
end;

function TskyForm1.writcf(port:string;disp_info:string):integer;
 var
   comport_display:string;
   hcom:Thandle;
   lpdcb:Tdcb;
   sendnumber:dword;
   display:string[48];
   hxqlen,ii:integer;
   xx:array[1..30] of string;
begin
   //票打驱动
  if pos(rjsmain.computer_name,port)>0 then
  begin
    ii:=pos(rjsmain.computer_name,port);
    comport_display:=copy(port,1,ii-1)+'.'+copy(port,ii+length(rjsmain.computer_name),length(port));
  end
  else comport_display:=port;
//  comport_display:=ExtractFilePath(Application.ExeName)+'writcf.txt';
  hxqlen:=0;
  while comport_display=comport_display do
  begin
   hcom:=createfile(pchar(comport_display), generic_write,0,Nil,OPEN_EXISTING,
            file_attribute_normal,0);
   if hcom<>4294967295 then break;
   hxqlen:=hxqlen+1;
   if hxqlen>1000 then
    begin
     messagedlg('打开 '+port+'端口失败！'+#13+'请检查打印机及其线路!',mtwarning,[mbok],0);
     exit;
    end;
  end;
  getcommstate(hcom,lpdcb);
  lpdcb.baudrate:=9600;
  lpdcb.DCBlength:=48;
  lpdcb.Parity:=0;
  lpdcb.ByteSize:=8;
  lpdcb.StopBits:=1;
  lpdcb.XonLim:=2048;
  lpdcb.XoffLim:=512;
  lpdcb.XonChar:=#17;
  lpdcb.XoffChar:=#19;
  setcommstate(hcom,lpdcb);
if adodataset1.RecordCount>0 then
begin
  for ii:=1 to 30 do xx[ii]:='0'; 
  disp_info:=' 收银员财务报表';
  display:=#27#97#1+disp_info+#27#64;
  hxqlen:=length(display);
  writefile(hcom,display,hxqlen,sendnumber,nil);
  disp_info:='打印时间'+formatdatetime('yyyy-mm-dd hh:mm:ss',date+time);
  display:=#27#97#1+disp_info+#27#64;
  hxqlen:=length(display);
  writefile(hcom,display,hxqlen,sendnumber,nil);
  disp_info:='开始日期'+formatdatetime('yyyy-mm-dd hh:mm:ss',datetimepicker1.datetime);
  display:=#27#97#1+disp_info+#27#64;
  hxqlen:=length(display);
  writefile(hcom,display,hxqlen,sendnumber,nil);
  disp_info:='终止日期'+formatdatetime('yyyy-mm-dd hh:mm:ss',datetimepicker2.datetime);
  display:=#27#97#1+disp_info+#27#64;
  hxqlen:=length(display);
  writefile(hcom,display,hxqlen,sendnumber,nil);
  adodataset1.First;
  while not adodataset1.Eof do
  begin
//    以下为打印正文
     if adodataset1.FieldByName('注册销售').AsFloat>0 then
     begin
       for ii:=0 to adodataset1.FieldCount -1 do
       begin
              disp_info:=adodataset1.Fields[ii].FieldName + ':'+adodataset1.FieldByname(adodataset1.Fields[ii].FieldName).AsString+' ';
            display:=#27#97#1+disp_info+#27#64;
            hxqlen:=length(display);
            writefile(hcom,display,hxqlen,sendnumber,nil);
            if ii>1 then
            begin
              xx[ii]:=floattostr(strtofloat(xx[ii]) + adodataset1.Fields[ii].AsFloat);
            end;
       end;
     end;
     adodataset1.Next;
  end;
// 打印页尾
{  disp_info:='------------以下合计---------------';
  display:=#27#97#1+disp_info+#27#64;
  hxqlen:=length(display);
  writefile(hcom,display,hxqlen,sendnumber,nil);
  for ii:=2 to adodataset1.FieldCount -1 do
  begin
            if strtofloat(xx[ii])>0 then disp_info:=adodataset1.Fields[ii].FieldName + ':'+xx[ii]+' '
            else disp_info:=adodataset1.Fields[ii].FieldName + ':'+'  ';
            display:=#27#97#1+disp_info+#27#64;
            hxqlen:=length(display);
            writefile(hcom,display,hxqlen,sendnumber,nil);
  end;
}  disp_info:='-----------------------------------';
  display:=#27#97#1+disp_info+#27#64;
  hxqlen:=length(display);
  writefile(hcom,display,hxqlen,sendnumber,nil);
  adodataset1.First;
  closehandle(hcom);
  truc(comport_display,'');
end
else begin
  adodataset1.Close;
  closehandle(hcom);
end;
  result:=0;
end;
function TskyForm1.truc(port:string;disp_info:string):integer;
 var
   comport_display:string;
   hcom:Thandle;
   lpdcb:Tdcb;
   sendnumber:dword;
   display:string[40];
begin
  //票打切纸
  comport_display:=port;
  hcom:=createfile(pchar(comport_display), generic_write,0,Nil,open_always,
            file_attribute_normal,0);

  getcommstate(hcom,lpdcb);
  lpdcb.baudrate:=9600;
  if lpdcb.ByteSize=7 then lpdcb.ByteSize:=8;
  setcommstate(hcom,lpdcb);
  display:=#27#97+#6+disp_info+ #13;
  writefile(hcom,display,length(display)+1,sendnumber,nil);
  display:=#27#100+#1;
  writefile(hcom,display,length(display)+1,sendnumber,nil);
  closehandle(hcom);
  result:=0;
end;


procedure TskyForm1.BitBtn2Click(Sender: TObject);
begin
       prtsetq.Close;
       prtsetq.SQL.Clear;
       prtsetq.SQL.Add('select * from prtset where type='+''''+'S'+''''+' and port like '+''''+'%'+rjsmain.computer_name+'%'+'''');
       prtsetq.Open;
       if prtsetq.RecordCount>0 then
       begin
           writcf(prtsetq.fieldbyname('port').AsString,'');
       end
       else messagedlg('没有定义串口收银打印机!',mtwarning,[mbok],0);
       prtsetq.Close;
end;

procedure TskyForm1.BitBtn4Click(Sender: TObject);
var
 i,row,column:integer;
begin
 Try
  ExcelApplication1.Connect;
 Except
  MessageDlg('Excel 没有安装,无法报表！！',
  mtError, [mbOk], 0);
  Abort;
 End;  
 ExcelApplication1.Visible[0]:=True;
 ExcelApplication1.Caption:='Excel 报表';
 ExcelApplication1.Workbooks.Add(Null,0);
 ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
 ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);
 ExcelWorksheet1.Cells.Item[1,3]:='收款员财务报表';
 ExcelWorksheet1.Cells.Item[2,2]:='生成日期';
 ExcelWorksheet1.Cells.Item[2,3]:=formatdatetime('yyyy-mm-dd hh:mm:ss',date+time);
 ExcelWorksheet1.Cells.Item[3,1]:='开始日期';
 ExcelWorksheet1.Cells.Item[3,2]:=formatdatetime('yyyy-mm-dd hh:mm:ss',datetimepicker1.DateTime);
 ExcelWorksheet1.Cells.Item[3,4]:='终止日期';
 ExcelWorksheet1.Cells.Item[3,5]:=formatdatetime('yyyy-mm-dd hh:mm:ss',datetimepicker2.DateTime);
 column:=1;
 for i:=1 to adodataset1.FieldCount do
   begin
    ExcelWorksheet1.Cells.Item[4,column]:=adodataset1.fields[i-1].DisplayLabel;
    column:=column+1;
   end;
 row:=5;
 adodataset1.First;
 While Not(adodataset1.Eof) do
 begin
   column:=1;
   for i:=1 to adodataset1.FieldCount do
   begin
    ExcelWorksheet1.Cells.Item[row,column]:=adodataset1.fields[i-1].AsString;
    column:=column+1;
   end;
   adodataset1.Next;
   row:=row+1;
 end;
 ExcelWorksheet1.Name:='收款员财务报表(窄行)';
 ExcelApplication1.Visible[0]:=true;
 ExcelWorkbook1.SaveCopyAs('收款员财务报表(窄行).xls');
 ExcelApplication1.Workbooks.Close(1);
 ExcelApplication1.Visible[0]:=false;
 ExcelApplication1.Quit;
end;

procedure TskyForm1.ADODataSet1FilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
   accept:=dataset.FieldByName('收银员').AsString=combobox1.Text;
end;

end.