meal.pas

pasa人力资源考勤管理系统

// Programe  Writer By dyh 2004/07/16

unit meal;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Menus, ImgList, DBCtrls, ComCtrls, ToolWin, ExtCtrls, StdCtrls, Buttons,
  Grids, DBGrids, Db, ADODB, Mask, ToolEdit, ppCtrls, ppVar, ppPrnabl,
  ppClass, ppBands, ppCache, ppDB, ppProd, ppReport, ppComm, ppRelatv,
  ppDBPipe, ppDBBDE, Wwdbigrd, Wwdbgrid, wwdblook;

type
  TFormmeal = class(TForm)
    Dsmealoriginal: TDataSource;
    ImageList2: TImageList;
    ADOmealrecord: TADODataSet;
    ADOmealoriginal: TADODataSet;
    ADOmealoriginalmacname: TStringField;
    ADOmealoriginalcardno: TStringField;
    ADOmealoriginalrectime: TStringField;
    ADOmealoriginaldatetime: TStringField;
    ADOmealoriginalstate: TStringField;
    ADOmealoriginalreadok: TBooleanField;
    ADOmealoriginalrecdate: TDateTimeField;
    Dsmealrecord: TDataSource;
    ToolBar2: TToolBar;
    ToolButton5: TToolButton;
    ToolButton7: TToolButton;
    tbnsort: TToolButton;
    tbnfind: TToolButton;
    tbnprint: TToolButton;
    ToolButton9: TToolButton;
    DBNavigator1: TDBNavigator;
    PopupMenu2: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N4: TMenuItem;
    N3: TMenuItem;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Panel1: TPanel;
    Label12: TLabel;
    Label8: TLabel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label5: TLabel;
    MaskEdit1: TMaskEdit;
    MaskEdit2: TMaskEdit;
    Edit1: TEdit;
    TabSheet2: TTabSheet;
    DBGrid2: TDBGrid;
    DBGrid5: TDBGrid;
    Panel3: TPanel;
    Label6: TLabel;
    Label7: TLabel;
    Label9: TLabel;
    Label13: TLabel;
    MaskEdit3: TMaskEdit;
    Edit2: TEdit;
    Splitter1: TSplitter;
    DBGrid6: TDBGrid;
    DBGrid1: TDBGrid;
    TabSheet3: TTabSheet;
    Panel4: TPanel;
    Label10: TLabel;
    Label11: TLabel;
    Label14: TLabel;
    Label15: TLabel;
    Label16: TLabel;
    Label17: TLabel;
    MaskEdit4: TMaskEdit;
    MaskEdit5: TMaskEdit;
    Edit3: TEdit;
    DBGrid3: TDBGrid;
    DBGrid7: TDBGrid;
    Splitter2: TSplitter;
    DataSource2: TDataSource;
    ADODataSet2: TADODataSet;
    DataSource3: TDataSource;
    ADODataSet3: TADODataSet;
    ADODataSet2workno: TStringField;
    ADODataSet2cardno: TStringField;
    ADODataSet2name: TStringField;
    ADODataSet2dept: TStringField;
    ADODataSet2rq: TStringField;
    ADODataSet3cardno: TStringField;
    ADODataSet3workno: TStringField;
    ADODataSet3name: TStringField;
    ADODataSet3rq: TDateTimeField;
    ADODataSet3mealtime: TStringField;
    ADOQuery1: TADOQuery;
    ComboBox1: TComboBox;
    Label18: TLabel;
    Label19: TLabel;
    ComboBox2: TComboBox;
    Label20: TLabel;
    ComboBox3: TComboBox;
    Panel6: TPanel;
    Label21: TLabel;
    Label22: TLabel;
    Label23: TLabel;
    Animate1: TAnimate;
    DateTimePicker3: TDateTimePicker;
    BitBtn1: TBitBtn;
    DateTimePicker4: TDateTimePicker;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    ToolButton4: TToolButton;
    ADOmealrecordcardno: TStringField;
    ADOmealrecordworkno: TStringField;
    ADOmealrecordname: TStringField;
    ADOmealrecordrq: TDateTimeField;
    ADOmealrecorddept: TStringField;
    ADOmealrecordmealtime: TStringField;
    ADOmealrecordprice1: TFloatField;
    ADOmealrecordprice2: TFloatField;
    ADODataSet3price2: TFloatField;
    ADODataSet3price1: TFloatField;
    ADODataSet2price1: TBCDField;
    ADODataSet2price2: TBCDField;
    dsmealtotal: TDataSource;
    ADOmealtotal: TADODataSet;
    ADODataSet1: TADODataSet;
    DataSource1: TDataSource;
    ADOmealtotalworkno: TStringField;
    ADOmealtotalcardno: TStringField;
    ADOmealtotalname: TStringField;
    ADOmealtotaldeptno: TStringField;
    ADOmealtotalrq: TStringField;
    ADOmealtotalmoney1: TBCDField;
    ADOmealtotalmoney2: TBCDField;
    ADODataSet1rq: TStringField;
    ADODataSet1workno: TStringField;
    ADODataSet1cardno: TStringField;
    ADODataSet1name: TStringField;
    ADODataSet1dept: TStringField;
    ADODataSet1price1: TBCDField;
    ADODataSet1price2: TBCDField;
    ADOmealrecordmacname: TStringField;
    ADODataSet3dept: TStringField;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure tblexitClick(Sender: TObject);
    procedure DBGrid1KeyPress(Sender: TObject; var Key: Char);
    procedure PageControl1Changing(Sender: TObject;
      var AllowChange: Boolean);
    procedure ToolButton7Click(Sender: TObject);
    procedure ToolButton9Click(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure PageControl1Change(Sender: TObject);
    procedure tbnfindClick(Sender: TObject);
    procedure tbnsortClick(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure N4Click(Sender: TObject);
    procedure N3Click(Sender: TObject);
    procedure MaskEdit2Exit(Sender: TObject);
    procedure FormKeyPress(Sender: TObject; var Key: Char);
    procedure ToolButton5Click(Sender: TObject);
    procedure MaskEdit3Exit(Sender: TObject);
    procedure Edit2Exit(Sender: TObject);
    procedure Edit3Exit(Sender: TObject);
    procedure FormKeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure tbnprintClick(Sender: TObject);
    procedure DBGrid3TitleClick(Column: TColumn);
    procedure DBGrid7TitleClick(Column: TColumn);
    procedure Edit1Exit(Sender: TObject);
    procedure MaskEdit5Exit(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure ComboBox3Change(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure ToolButton4Click(Sender: TObject);
  private
    { Private declarations }
    manyfield:boolean;
    findex:array[1..5] of integer;
    fsort:array[1..5] of string;
    fcount:0..5;
    db:0..3;
    f:string;  //排序的变量
  public
    { Public declarations }
    procedure delete_duplicate_data(var min_date,max_date:string);
  end;

var
  Formmeal: TFormmeal;
  fmat1:string;
implementation

uses main,datamol,publicfunction,  original,
  canfind,  mealprint;
{$R *.DFM}

procedure TFormmeal.FormClose(Sender: TObject; var Action: TCloseAction);
begin
   adomealrecord.close;
   adomealtotal.Close;
   datamod.adopersonal.close;
   formmeal:=nil;
   action:=cafree;
end;

procedure TFormmeal.FormCreate(Sender: TObject);
var
  i:integer;
begin
  if fmat='/' then
    fmat1:='/  /';
  if fmat='-' then
    fmat1:='-  -';
  Adoquery1.SQL.Clear;
  Adoquery1.SQL.Add('select distinct  dept from mel04010');
  Adoquery1.Open;
  Adoquery1.First;
  for i:=0 to self.ComponentCount-1 do
  begin
    if components[i].ClassType=TCombobox then
    begin
       Tcombobox(Components[i]).items.clear;
       Tcombobox(components[i]).items.add('全部');
    end;
    end;
    while not Adoquery1.Eof do
    begin
      combobox1.Items.Add(Adoquery1.fieldbyname('dept').asstring);
      combobox2.Items.Add(Adoquery1.fieldbyname('dept').asstring);
      combobox3.Items.Add(Adoquery1.fieldbyname('dept').asstring);
      Adoquery1.Next;
    end;
end;
procedure TFormmeal.tblexitClick(Sender: TObject);
begin
  close;
end;
procedure TFormmeal.DBGrid1KeyPress(Sender: TObject; var Key: Char);
begin
     dbgrid_keypress(sender,key);
end;
procedure TFormmeal.delete_duplicate_data(var min_date,max_date:string);
begin
     datamod.query1.close;
     datamod.query1.sql.text:='delete from mel03010 where (select count(cardno) '+
      ' from mel03010 as b where b.cardno=mel03010.cardno and '+
      ' mel03010.datetime=b.datetime and '+
      ' (recdate between '''+min_date+''' and '''+max_date+''') )>1 '+
      ' and id=(Select Max(id) from mel03010 as C where mel03010.CardNo=C.CardNo and '+
      ' mel03010.DateTime=C.DateTime and (RecDate between '''+Max_Date+''' and '''+Min_Date+''') )';
     datamod.query1.execsql;
end;

procedure TFormmeal.PageControl1Changing(Sender: TObject;
  var AllowChange: Boolean);
begin
  (sender as Tpagecontrol).activepage.highlighted:=False;
end;

procedure TFormmeal.ToolButton7Click(Sender: TObject);
var
  nowrq:string;
  i:integer;
  rq:string;
begin
  if (pagecontrol1.ActivePageIndex=1)and(MaskEdit4.Text<>'    /  /  ')and(MaskEdit5.Text<>'    /  /  ') then //日就餐明细汇总
  begin
    Adoquery1.close;
    ADOQuery1.sql.clear;
    Adoquery1.sql.add('truncate table  mel08010');
    Adoquery1.sql.Add('insert into  mel08010 select workno,cardno,max(name) name , max(dept) dept, convert(varchar,rq,111) rq  ,sum(price1) money1,min(price2) money2 from mel04010 where rq between '''+maskedit4.text+''' and '''+maskedit5.text+'''  group by workno,cardno,rq');
    try
      Adoquery1.execsql;
      ADOdataset2.close;
      Adodataset2.open;
      adodataset3.close;
      adodataset3.open;
      Application.MessageBox('汇总日消费数据成功!!!','系统提示',32);
    except
            Application.MessageBox('汇总日消费数据失败!!!','系统提示',48);
    end;
  end
  else if(pagecontrol1.ActivePageIndex=2) and (maskedit3.text<>'    /  ') then
  begin
    Adoquery1.close;
    Adoquery1.SQL.Clear;
    Adoquery1.sql.add('truncate table  mel05010 ');
    Adoquery1.SQL.Add('insert mel05010(workno,cardno,name,deptno,money1,money2,rq) ');
    Adoquery1.SQL.Add('select workno,cardno,max(name) name ,max(dept) dept ,sum(price1),min(price2),max(left(rq,7)) ');
    Adoquery1.SQL.Add('from mel08010 group by workno,cardno ');
    Adoquery1.SQL.SaveToFile('c:\testmeal.sql');
    try
      Adoquery1.ExecSQL;
      ADOmealtotal.close;
      ADOmealtotal.open;
      Adodataset1.close;
      Adodataset1.open;
      Application.MessageBox('汇总月消费数据成功!!!!','系统提示',32);
    except
      Application.MessageBox('汇总月消费数据失败!!!!','系统提示',48);
  end;
    ADOQuery1.ExecSQL;
    AdoDataSet2.Close;
    MaskEdit4.Text:='    /  /  ';
    MaskEdit5.Text:='    /  /  ';
    AdoDataSet2.Open;
  end;




Application.Messagebox('处 理 完 毕,谢 谢 使 用 ！！！！！！','系统提示',48);
end;
procedure TFormmeal.ToolButton9Click(Sender: TObject);
begin
  close;
end;

procedure TFormmeal.DBGrid1TitleClick(Column: TColumn);
begin
  if f='DESC' THEN
    F:='ASC'
  ELSE
    F:='DESC';
  case pagecontrol1.activepageindex of
  0:Adomealrecord.sort:=column.fieldname+' '+f;
  1:Adomealtotal.sort:=column.fieldname+' '+f;
  2:AdoDataSet2.Sort:=column.fieldname+' '+f;
  3:AdoDataSet2.Sort:=Column.Fieldname+' '+f;
  end;
end;
procedure TFormmeal.PageControl1Change(Sender: TObject);
begin
 case pagecontrol1.ActivePageIndex of
    0:
    begin
      DBNavigator1.DataSource:=dsmealrecord;
      Adomealrecord.Open;
    end;
    1:
    begin
      DBNavigator1.DataSource:=dsmealtotal;
      Adomealtotal.Open;
      toolbutton7.Enabled:=true;
      toolbutton7.Caption :='日处理';
    end;
    2:
    begin
      DBNavigator1.DataSource:=DataSource2;
      Adodataset2.Open;
      Adodataset3.Open;
      toolbutton7.Enabled:=true;
      toolbutton7.Caption :='月处理';
    end;
  end;
end;
procedure TFormmeal.tbnfindClick(Sender: TObject);
begin
   if assigned(formcanfind) then  formcanfind.close;
  case pagecontrol1.ActivePageIndex of
  0:findrecorder(Adomealrecord,'Adomealrecord','mel04010');
  1:findrecorder(Adomealtotal,'Adomealtotal','mel05010');
  2:findrecorder(AdodataSet2,'AdodataSet2','mel08010');
  end;
end;

procedure TFormmeal.tbnsortClick(Sender: TObject);
var i:1..5;
begin
  if manyfield then
   begin
     manyfield:=false;
     tbnsort.hint:='设定多字段排序';
     popupmenu2.Items[0].Enabled :=false;
     popupmenu2.Items[1].Enabled :=false;
     popupmenu2.Items[2].Enabled :=false;
     popupmenu2.Items[3].Enabled :=false;
   end
  else begin
    manyfield:=true;
    tbnsort.hint:='取消多字段排序'; //说明现在dbgrid1正处于多字段排序中
    f:='';
    popupmenu2.Items[0].Enabled :=true;
    popupmenu2.Items[1].Enabled :=true;
    popupmenu2.Items[2].Enabled :=true;
    popupmenu2.Items[3].Enabled :=true;
    fcount:=0;
    for i:=1 to 5 do
     begin
      findex[i]:=0;
      fsort[i]:='';
     end;
  end;
end;

procedure TFormmeal.N1Click(Sender: TObject);
var i:integer;
   dbgrid:Tdbgrid;
begin
  if fcount>0 then
    if db<>pagecontrol1.ActivePageIndex then
     begin
      dbgrid:=tdbgrid(findcomponent('dbgrid'+inttostr(db+1)));
      with dbgrid do
      begin
        for i:=1 to fcount do
          dbgrid.Columns[findex[i]].Color :=clwindow;
      end;
      fcount:=0;
     end;
  db:=pagecontrol1.ActivePageIndex;
  dbgrid:=tdbgrid(findcomponent('dbgrid'+inttostr(db+1)));