pffx1.pas

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

unit pffx1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, Grids, DBGrids, ComCtrls, Db, DBTables, DBCtrls, comobj,
  Buttons, Qrctrls, QuickRpt,variants;

type
  Tpffxf1 = class(TForm)
    Label1: TLabel;
    Panel1: TPanel;
    DBGrid1: TDBGrid;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Query1: TQuery;
    DataSource1: TDataSource;
    UpdateSQL1: TUpdateSQL;
    bmq: TQuery;
    pfq: TQuery;
    pfqcpmc: TStringField;
    pfqcpbm: TStringField;
    bm: TComboBox;
    SpeedButton3: TSpeedButton;
    SpeedButton1: TSpeedButton;
    Panel4: TPanel;
    QuickRep1: TQuickRep;
    DetailBand1: TQRBand;
    QRDBText1: TQRDBText;
    QRDBText4: TQRDBText;
    QRDBText3: TQRDBText;
    QRShape15: TQRShape;
    QRShape18: TQRShape;
    QRShape20: TQRShape;
    QRShape21: TQRShape;
    QRShape23: TQRShape;
    QRShape24: TQRShape;
    QRShape26: TQRShape;
    QRShape28: TQRShape;
    PageFooterBand1: TQRBand;
    QRSysData1: TQRSysData;
    QRSysData2: TQRSysData;
    QRLabel2: TQRLabel;
    QRShape7: TQRShape;
    PageHeaderBand1: TQRBand;
    QRShape1: TQRShape;
    QRLabel1: TQRLabel;
    QRShape2: TQRShape;
    QRShape4: TQRShape;
    QRShape6: TQRShape;
    QRShape8: TQRShape;
    QRShape9: TQRShape;
    QRShape10: TQRShape;
    QRShape11: TQRShape;
    QRLabel3: TQRLabel;
    QRLabel12: TQRLabel;
    QRLabel15: TQRLabel;
    QRLabel18: TQRLabel;
    QRLabel22: TQRLabel;
    QRLabel23: TQRLabel;
    QRLabel24: TQRLabel;
    QRLabel25: TQRLabel;
    QRLabel7: TQRLabel;
    QRExpr3: TQRExpr;
    scwj: TQuery;
    scwjsc: TFloatField;
    scwjcpbm: TStringField;
    Query1ylbm: TStringField;
    Query1ylmc: TStringField;
    Query1yldw: TStringField;
    Query1yldj: TFloatField;
    Query1ylyl: TFloatField;
    Query1id: TIntegerField;
    Query1sjyl: TFloatField;
    Query1cyz: TFloatField;
    Query1cl: TFloatField;
    lsq: TQuery;
    lsqylbm: TStringField;
    lsqylmc: TStringField;
    lsqyldw: TStringField;
    lsqyldj: TFloatField;
    lsqylyl: TFloatField;
    lsqid: TIntegerField;
    lsqsjyl: TFloatField;
    lsqcyz: TFloatField;
    lsqcl: TFloatField;
    UpdateSQL2: TUpdateSQL;
    bmqlbmc: TStringField;
    QRLabel4: TQRLabel;
    QRShape3: TQRShape;
    QRLabel5: TQRLabel;
    QRShape5: TQRShape;
    QRShape12: TQRShape;
    QRSysData3: TQRSysData;
    QRShape13: TQRShape;
    QRLabel6: TQRLabel;
    QRExpr2: TQRExpr;
    dtp1: TDateTimePicker;
    dtp2: TDateTimePicker;
    Button1: TButton;
    Query1ckyl: TFloatField;
    Query1ckje: TFloatField;
    dzb: TQuery;
    dzbbmbm: TStringField;
    dzbbmmc: TStringField;
    dzblbbm: TStringField;
    dzblbmc: TStringField;
    ckq1: TQuery;
    ckq1cpbm: TStringField;
    ckq1sl: TFloatField;
    ckq1je: TFloatField;
    ckq: TQuery;
    ckqsll: TFloatField;
    ckqrkdwbm: TStringField;
    ckqcpbm: TStringField;
    Query1je: TFloatField;
    Query1dw1: TStringField;
    ckq1dw: TStringField;
    QRLabel8: TQRLabel;
    QRShape14: TQRShape;
    QRExpr4: TQRExpr;
    QRExpr5: TQRExpr;
    QRShape16: TQRShape;
    QRExpr1: TQRExpr;
    QRExpr6: TQRExpr;
    bmqlbbm: TStringField;
    procedure FormActivate(Sender: TObject);
    procedure rqChange(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure SpeedButton3Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  pffxf1: Tpffxf1;
  rq:string;
implementation
uses rjsmain;
{$R *.DFM}

procedure Tpffxf1.FormActivate(Sender: TObject);
begin
     if rq<>'' then exit;
     rq:='aa';
     dtp1.date:=date;
     dtp2.date:=date;
     bmq.close;
     bmq.open;
     bm.Text:='';
     bm.Items.Clear;
     bm.Items.Add('全部');
     while not bmq.Eof do
     begin
       bm.Items.Add(bmqlbmc.asstring);
       bmq.Next;
     end;
     pfq.close;
     pfq.open;
end;

procedure Tpffxf1.rqChange(Sender: TObject);
begin
{     rqq.Close;
     rqq.ParamByName('cs0').asdatetime:=dtp1.date;
     rqq.ParamByName('cs1').asdatetime:=dtp2.date;
     rqq.Open;
     dtp1.text:=rqqmn.AsString;
     dtp2.text:=rqqmx.AsString;
     rqq.Close;}
end;

procedure Tpffxf1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
     rq:='';
     query1.Close;
     scwj.Close;
     bmq.Close;
     pfq.Close;
end;

procedure Tpffxf1.SpeedButton3Click(Sender: TObject);
var
   XL, XArr: Variant;
   i : Integer;
   j : Integer;
begin
     if not query1.Active then exit;
     XArr:=VarArrayCreate([1,dbgrid1.Columns.Count+4],varVariant);
     XL:=CreateOLEObject('Excel.Application');
     XL.WorkBooks.add;
     XL.visible:=true;
     j := 1;
     i:=1;
     while i<=dbgrid1.Columns.Count do begin
           XArr[i] :=dbgrid1.Columns[i-1].Title.caption;
           i := i+1;
     end;
     XL.Range['A'+IntToStr(j),
      CHR(64+dbgrid1.Columns.Count)+IntToStr(j)].Value := XArr;
     j := j + 1;

     query1.First;
     while not query1.Eof do begin
           i:=1;
           while i<=dbgrid1.Columns.Count do begin
                 XArr[i] :=dbgrid1.Columns[i-1].Field.value;
                 i := i+1;
           end;
           XL.Range['A'+IntToStr(j),
	           CHR(64+dbgrid1.Columns.Count)+IntToStr(j)].Value := XArr;
           query1.Next;
           j := j + 1;
     end;
     XL.Range['A1',CHR(64+query1.FieldCount)+IntToStr(j)].select;
     XL.Selection.Font.Name:='宋体';
     XL.Selection.Font.Size:=12;
     XL.selection.Columns.AutoFit;
     XL.Range['A1','A1'].select;
end;

procedure Tpffxf1.SpeedButton1Click(Sender: TObject);
begin
     if not query1.Active then exit;
     qrlabel1.Caption:=rjsmain.dwmc+'应使用原料汇总表';
     qrlabel23.Caption:=datetostr(dtp1.date)+' 到 '+datetostr(dtp2.date);
     qrlabel25.Caption:=bm.Text;
     quickrep1.Preview;
end;

procedure Tpffxf1.Button1Click(Sender: TObject);
var a3:real;
begin
     if bm.Text='' then begin
        messagedlg('请选择类别/部门！',mtwarning,[mbyes],0);
        bm.SetFocus;
        exit;
     end;
     scwj.Close;
     scwj.SQL.Clear;
     if bm.Text<>'全部' then
     begin
      bmq.Locate('lbmc',bm.text,[]);
      scwj.SQL.Add('select sum(sl) as sc,bh as cpbm from dcd where rq>=:cs0 and rq<=:cs1 and lb=:cs2 and fkbz=1 group by bh ');
      scwj.ParamByName('cs0').asdatetime:=dtp1.DateTime;
      scwj.ParamByName('cs1').asdatetime:=dtp2.DateTime;
      scwj.ParamByName('cs2').asstring:=bmq.FieldByName('lbbm').AsString;
      scwj.Open;
     end
     else
     begin
      scwj.SQL.Add('select sum(sl) as sc,bh as cpbm from dcd where rq>=:cs0 and rq<=:cs1 and fkbz=1 group by bh ');
      scwj.ParamByName('cs0').asdatetime:=dtp1.DateTime;
      scwj.ParamByName('cs1').asdatetime:=dtp2.DateTime;
      scwj.Open;
     end;
     query1.Close;
     query1.ParamByName('cs0').asstring:='';
     query1.Open;
     if scwj.RecordCount<1 then begin
        messagedlg('该区间没有生产。'+#13+'无法分析！',mtwarning,[mbok],0);
        scwj.Close;
        exit;
     end;
     scwj.First;
     while not scwj.Eof do
     begin
      lsq.Close;
      lsq.ParamByName('cs0').asstring:=scwjcpbm.AsString;
      lsq.Open;
      lsq.First;
      while not lsq.Eof do
      begin
       ckq.Close;
//       ckq.ParamByName('cs0').asstring:=bm.Text;
       ckq.ParamByName('cs1').asstring:=lsqylbm.AsString;
       ckq.ParamByName('cs2').asdatetime:=dtp1.date;
       ckq.ParamByName('cs3').asdatetime:=dtp2.date;
       ckq.Open;
       if ckqcpbm.AsString<>'' then a3:=ckqsll.Asfloat
       else a3:=0;
       ckq.Close;
       lsq.Edit;
       lsqylyl.Value:=lsqylyl.AsFloat*scwjsc.AsFloat;
       lsqsjyl.AsFloat:=a3;
       lsqcyz.AsFloat:=lsqsjyl.asfloat - lsqylyl.AsFloat;
       lsqcl.AsFloat:=scwjsc.AsFloat;
       if query1.Locate('ylbm',lsqylbm.asstring,[]) then
       begin
         query1.edit;
         query1ylyl.AsFloat:=query1ylyl.AsFloat+lsqylyl.AsFloat;
         query1sjyl.AsFloat:=query1sjyl.AsFloat+lsqsjyl.AsFloat;
         query1cl.AsFloat:=query1cl.AsFloat+lsqcl.AsFloat;
         query1cyz.AsFloat:=query1sjyl.asfloat - query1ylyl.AsFloat;
         query1.Post;
       end
       else begin
         query1.Append;
         query1ylbm.AsString:=lsqylbm.asstring;
         query1ylmc.AsString:=lsqylmc.AsString;
         query1yldw.AsString:=lsqyldw.AsString;
         query1yldj.AsFloat:=lsqyldj.AsFloat;
         query1ylyl.AsFloat:=lsqylyl.AsFloat;
         query1sjyl.AsFloat:=lsqsjyl.AsFloat;
         query1cl.AsFloat:=lsqcl.AsFloat;
         query1cyz.AsFloat:=query1sjyl.asfloat - query1ylyl.AsFloat;
         query1.Post;
       end;
       lsq.Next;
      end;
      scwj.Next;
     end;
     lsq.Close;
     scwj.Close;
     dzb.Close;
     dzb.ParamByName('cs').asstring:=bm.Text;
     dzb.Open;
     query1.First;
     while not query1.Eof do
     begin
       ckq1.Close;
       ckq1.ParamByName('cs3').asstring:=dzbbmbm.AsString;
       ckq1.ParamByName('cs1').asdatetime:=dtp1.Date;
       ckq1.ParamByName('cs2').asdatetime:=dtp2.Date;
       ckq1.ParamByName('cs0').asstring:=query1ylbm.AsString;
       ckq1.Open;
       query1.Edit;
       query1je.AsFloat:=query1yldj.AsFloat*query1ylyl.AsFloat;
       query1ylyl.AsFloat:=round(query1ylyl.AsFloat*100)/100;
       query1je.AsFloat:=round(query1je.AsFloat*100)/100;
       if ckq1cpbm.AsString=query1ylbm.AsString then
       begin
          query1ckyl.AsFloat:=ckq1sl.AsFloat;
          query1ckje.AsFloat:=ckq1je.AsFloat;
          query1dw1.AsString:=ckq1dw.AsString;  
       end;
       query1.Next;
    end;
    query1.First;
end;

end.