skycwbb2.pas

日昌餐饮管理系统是用Delphi7+SQL写的管理系统

unit skycwbb2;

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
  TskyForm2 = class(TForm)
    Label2: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    DataSource1: TDataSource;
    adodataset1: TADODataSet;
    DBGrid3: TDBGrid;
    prtsetQ: TADOQuery;
    BitBtn4: TBitBtn;
    ExcelApplication1: TExcelApplication;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    ADODataSet2: TADODataSet;
    DBGrid1: TDBGrid;
    DataSource2: TDataSource;
    ADODataSet2DSDesigner: TStringField;
    ADODataSet2DSDesigner2: TStringField;
    ADODataSet2DSDesigner3: TBCDField;
    adodataset1DSDesigner: TStringField;
    adodataset1DSDesigner2: TStringField;
    adodataset1DSDesigner3: TStringField;
    adodataset1DSDesigner4: TBCDField;
    adodataset1DSDesigner5: TBCDField;
    adodataset1Field: TFloatField;
    ADODataSet2Field: TFloatField;
    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 adodataset1CalcFields(DataSet: TDataSet);
    procedure adodataset1FilterRecord(DataSet: TDataSet;
      var Accept: Boolean);
    procedure ADODataSet2AfterScroll(DataSet: TDataSet);
    procedure ADODataSet2CalcFields(DataSet: TDataSet);
  private
    xx:string;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  zcxse:real; 
  skyForm2: TskyForm2;

implementation
uses rjsmain;
{$R *.dfm}

procedure TskyForm2.BitBtn1Click(Sender: TObject);
begin
     adodataset2.Close;
     adodataset2.Parameters.ParamByName('cs1').Value:=datetimepicker1.DateTime;
     adodataset2.Parameters.ParamByName('cs2').Value:=datetimepicker2.DateTime;
     adodataset2.Open;
     zcxse:=0;
     while not adodataset2.Eof do
     begin
        zcxse:=zcxse+adodataset2.FieldValues['金额'];
        adodataset2.Next;
     end;
     adodataset2.First;
     adodataset1.Close;
     adodataset1.Parameters.ParamByName('cs1').Value:=datetimepicker1.DateTime;
     adodataset1.Parameters.ParamByName('cs2').Value:=datetimepicker2.DateTime;
     adodataset1.Open;
     bitbtn2.Enabled:=true;
end;

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

procedure TskyForm2.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');
end;

procedure TskyForm2.FormClose(Sender: TObject; var Action: TCloseAction);
begin
     xx:='';
     adodataset1.Filtered:=false;
     adodataset2.Close;
     adodataset1.Close;
end;

function TskyForm2.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..100] 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 100 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);
  adodataset2.First;
  while not adodataset2.Eof do
  begin
//   disp_info:='       类名         金额   百分比  ';
//   display:=#27#97#1+disp_info+#27#64;
//   hxqlen:=length(display);
//   writefile(hcom,display,hxqlen,sendnumber,nil);
   disp_info:='   销 售 品 种   数量 金额 占销售% ';
   display:=#27#97#1+disp_info+#27#64;
   hxqlen:=length(display);
   writefile(hcom,display,hxqlen,sendnumber,nil);
   disp_info:='-----------------------------------';
   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
//    以下为打印正文
     disp_info:=copy(adodataset1.Fields[2].AsString,1,18)+copy('                  ',1,18-length(adodataset1.Fields[2].AsString));
     disp_info:=disp_info+copy(adodataset1.Fields[3].AsString,1,4)+copy('    ',1,4-length(adodataset1.Fields[3].AsString));
     disp_info:=disp_info+copy(adodataset1.Fields[4].AsString,1,6)+copy('      ',1,6-length(adodataset1.Fields[4].AsString));
     disp_info:=disp_info+copy(adodataset1.Fields[5].AsString,1,7)+copy('       ',1,7-length(adodataset1.Fields[5].AsString));
     display:=#27#97#1+disp_info+#27#64;
     hxqlen:=length(display);
     writefile(hcom,display,hxqlen,sendnumber,nil);
     adodataset1.Next;
   end;
   for ii:=2 to adodataset2.FieldCount -1 do
   begin
     xx[ii]:=floattostr(strtofloat(xx[ii]) + adodataset2.Fields[ii].AsFloat);
   end;
   disp_info:='===================================';
   display:=#27#97#1+disp_info+#27#64;
   hxqlen:=length(display);
   writefile(hcom,display,hxqlen,sendnumber,nil);
   disp_info:=copy(adodataset2.Fields[1].AsString,1,22)+copy('                      ',1,22-length(adodataset2.Fields[1].AsString));
   disp_info:=disp_info+copy(adodataset2.Fields[2].AsString,1,6)+copy('      ',1,6-length(adodataset2.Fields[2].AsString));
   disp_info:=disp_info+copy(adodataset2.Fields[3].AsString,1,7)+copy('       ',1,7-length(adodataset2.Fields[3].AsString));
   display:=#27#97#1+disp_info+#27#64;
   hxqlen:=length(display);
   writefile(hcom,display,hxqlen,sendnumber,nil);
   disp_info:='-----------------------------------';
   display:=#27#97#1+disp_info+#27#64;
   hxqlen:=length(display);
   writefile(hcom,display,hxqlen,sendnumber,nil);
   adodataset2.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 adodataset2.FieldCount -1 do
  begin
      disp_info:=adodataset2.Fields[ii].FieldName + ':'+xx[ii]+' ';
      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);
  adodataset2.First;
  adodataset1.First;
  closehandle(hcom);
  truc(comport_display,'');
end
else begin
  adodataset1.Close;
  closehandle(hcom);
end;
  result:=0;
end;
function TskyForm2.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 TskyForm2.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 TskyForm2.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);
 adodataset1.Filtered:=false;
 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;
 adodataset1.Filtered:=true;
end;

procedure TskyForm2.adodataset1CalcFields(DataSet: TDataSet);
begin
     if zcxse>0 then dataset.FieldByName('占销售%').AsFloat:=round((dataset.FieldByName('金额').AsFloat/zcxse *100)*100)/100
     else dataset.FieldByName('占销售%').AsFloat:=0;
end;

procedure TskyForm2.adodataset1FilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
     accept:=(dataset.FieldByName('类别名称').AsString=adodataset2.FieldByName('类别名称').AsString)
     and (dataset.FieldByName('餐厅名称').AsString=adodataset2.FieldByName('餐厅名称').AsString);
end;

procedure TskyForm2.ADODataSet2AfterScroll(DataSet: TDataSet);
begin
     adodataset1.Filtered:=false;
     adodataset1.Filtered:=true;
end;

procedure TskyForm2.ADODataSet2CalcFields(DataSet: TDataSet);
begin
     if zcxse>0 then dataset.FieldByName('占销售%').AsFloat:=round((dataset.FieldByName('金额').AsFloat/zcxse *100)*100)/100
     else dataset.FieldByName('占销售%').AsFloat:=0;
end;

end.