unitqueryform.pas

手机批发商进销存管理

unit UnitQueryForm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, ComCtrls, RzButton, DB, ADODB, Grids,
  DBGrids;

type
  TQueryForm = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    Label4: TLabel;
    ComboBox1: TComboBox;
    RzBitBtn1: TRzBitBtn;
    DBGrid1: TDBGrid;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    Label5: TLabel;
    Label6: TLabel;
    Label_CurNum: TLabel;
    Label_LastNum: TLabel;
    Label_TotNum: TLabel;
    ADOQuery1CurDate: TWideStringField;
    ADOQuery1GoodsName: TWideStringField;
    ADOQuery1GoodsType: TWideStringField;
    ADOQuery1CurNum: TIntegerField;
    ADOQuery1LastNum: TIntegerField;
    ADOQuery1TotNum: TIntegerField;
    ADOQuery1GoodsOwner: TWideStringField;
    ADOQuery1button1: TWideStringField;
    ADOQuery1button2: TWideStringField;
    ADOQuery1button3: TWideStringField;
    Label7: TLabel;
    ComboBox2: TComboBox;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure FormCreate(Sender: TObject);
    procedure RzBitBtn1Click(Sender: TObject);
    procedure DateTimePicker1KeyPress(Sender: TObject; var Key: Char);
    procedure DateTimePicker2KeyPress(Sender: TObject; var Key: Char);
    procedure ComboBox1KeyPress(Sender: TObject; var Key: Char);
    procedure DBGrid1EditButtonClick(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure ComboBox2KeyPress(Sender: TObject; var Key: Char);
    procedure ComboBox1Change(Sender: TObject);
  private
    { Private declarations }
    selectColumn, selectCurDate, selectGoodsOwner, selectGoodsName, selectGoodsType: string;
    procedure ClearTempData;
    procedure CountData(iFlag: integer);
    procedure ShowDetail(iFlag: integer; selectColumn, selectCurDate, selectGoodsOwner, selectGoodsName, selectGoodsType: string);
  public
    { Public declarations }
  end;

implementation

uses UnitDM, UnitDetailForm;

{$R *.dfm}

procedure TQueryForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  ADOQuery1.Close;
  Action := caFree;
end;

procedure TQueryForm.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key=27 then Close;
end;

procedure TQueryForm.FormCreate(Sender: TObject);
var
  i: integer;
begin
  DatetimePicker1.DateTime := StrToDateTime(FormatDateTime('yyyy',now())+'-'+FormatDateTime('mm',now())+'-01');
  DateTimePicker2.DateTime := Now();
  ComboBox1.ItemIndex := 0;
  Label_CurNum.Caption := '0';
  Label_LastNum.Caption := '0';
  Label_TotNum.Caption := '0';
  ComboBox2.Items.Clear;
  ComboBox2.Items.Add(' ');
  for i:=0 to TsGoodsOwner.Count-1 do
    ComboBox2.Items.Add(TsGoodsOwner.Strings[i]);
  ComboBox2.Items.Add('总仓');    
  ComboBox2.ItemIndex := 0;
  ADOQuery1.Close;
end;

procedure TQueryForm.ClearTempData;
begin
  try
    DM1.ADOConnection1.BeginTrans;

    DM1.ADOQuery1.Close;
    DM1.ADOQuery1.SQL.Clear;
    DM1.ADOQuery1.SQL.Text := 'delete from Tab_TempQuery';
    DM1.ADOQuery1.ExecSQL;

    DM1.ADOConnection1.CommitTrans;
    DM1.ADOQuery1.Close;
  except
    DM1.ADOConnection1.RollbackTrans;
    DM1.ADOQuery1.Close;
  end;
end;

procedure TQueryForm.DateTimePicker1KeyPress(Sender: TObject;
  var Key: Char);
begin
  if Key=#27 then DateTimePicker1.SetFocus;
end;

procedure TQueryForm.DateTimePicker2KeyPress(Sender: TObject;
  var Key: Char);
begin
  if Key=#27 then ComboBox1.SetFocus;
end;

procedure TQueryForm.ComboBox1KeyPress(Sender: TObject; var Key: Char);
begin
  if Key=#27 then ComboBox2.SetFocus;
end;

procedure TQueryForm.ComboBox2KeyPress(Sender: TObject; var Key: Char);
begin
  if Key=#27 then RzBitBtn1.Click;
end;

procedure TQueryForm.ComboBox1Change(Sender: TObject);
begin
  case ComboBox1.ItemIndex of
    1,2:
      begin
        ComboBox2.Enabled := True;
        ComboBox2.Color := clWindow;
      end;
    else
      begin
        ComboBox2.ItemIndex := 0;
        ComboBox2.Enabled := False;
        ComboBox2.Color := clBtnface;        
      end;
  end;
end;

procedure TQueryForm.RzBitBtn1Click(Sender: TObject);
var
  i, CurNum, LastNum, TotNum: integer;
  Column: TColumn;
begin
  if ComboBox1.ItemIndex=-1 then
  begin
    Application.MessageBox('请选择查询类型!', '提示', MB_OK + MB_ICONINFORMATION);
    ComboBox1.SetFocus;
    Exit;
  end;

  //初始化
  Label_CurNum.Caption := '0';
  Label_LastNum.Caption := '0';
  Label_TotNum.Caption := '0';  
  ADOQuery1.Close;
  ClearTempData;

  //处理
  for i:=0 to DBGrid1.Columns.Count-1 do
    DBGrid1.Columns.Items[i].Visible := True;
  Label5.Visible := true;
  Label_LastNum.Visible := true;
  Label6.Visible := true;
  Label_TotNum.Visible := true;
  case ComboBox1.ItemIndex of
    0://采购表
      begin
        DBGrid1.Columns.Items[1].Visible := false;//删除柜台字段
      end;
    1://库存表
      begin

      end;
    2://销售表
      begin
        DBGrid1.Columns.Items[9].Visible := false;//删除串号字段
        DBGrid1.Columns.Items[8].Visible := false;//删除累计库存数量字段
        DBGrid1.Columns.Items[7].Visible := false;//删除串号字段
        DBGrid1.Columns.Items[6].Visible := false;//删除上期库存数量字段
        Label5.Visible := false;//隐藏汇总字段
        Label_LastNum.Visible := false;
        Label6.Visible := false;
        Label_TotNum.Visible := false;
      end;
    3://售后表
      begin
        DBGrid1.Columns.Items[9].Visible := false;//删除串号字段
        DBGrid1.Columns.Items[8].Visible := false;//删除累计库存数量字段
        DBGrid1.Columns.Items[7].Visible := false;//删除串号字段
        DBGrid1.Columns.Items[6].Visible := false;//删除上期库存数量字段
        DBGrid1.Columns.Items[1].Visible := false;//删除柜台字段
        Label5.Visible := false;//隐藏汇总字段
        Label_LastNum.Visible := false;
        Label6.Visible := false;
        Label_TotNum.Visible := false;
      end;
    4://回访表
      begin
        DBGrid1.Columns.Items[9].Visible := false;//删除串号字段
        DBGrid1.Columns.Items[8].Visible := false;//删除累计库存数量字段
        DBGrid1.Columns.Items[7].Visible := false;//删除串号字段
        DBGrid1.Columns.Items[6].Visible := false;//删除上期库存数量字段
        DBGrid1.Columns.Items[1].Visible := false;//删除柜台字段
        Label5.Visible := false;//隐藏汇总字段
        Label_LastNum.Visible := false;
        Label6.Visible := false;
        Label_TotNum.Visible := false;
      end;
    else Exit;
  end;

  //计算
  CountData(ComboBox1.ItemIndex);
  
  //显示
  try
    ADOQuery1.Open;
  except
    Application.MessageBox('查询出错', '提示', MB_OK + MB_ICONERROR);
    Exit;
  end;

  //汇总
  CurNum := 0;
  LastNum := 0;
  TotNum := 0;
  try
    ADOQuery1.DisableControls;
    ADOQuery1.First;
    while not ADOQuery1.Eof do
    begin
      CurNum := CurNum + ADOQuery1.FieldByName('CurNum').AsInteger;
      LastNum := LastNum + ADOQuery1.FieldByName('LastNum').AsInteger;
      TotNum := TotNum + ADOQuery1.FieldByName('TotNum').AsInteger;
      ADOQuery1.Next;
    end;
    ADOQuery1.First;
    Label_CurNum.Caption := IntToStr(CurNum);
    Label_LastNum.Caption := IntToStr(LastNum);
    Label_TotNum.Caption := IntToStr(TotNum);
  finally
    ADOQuery1.EnableControls;
  end;
end;

procedure TQueryForm.CountData(iFlag: integer);
var
  sqlInsert: string;
  startDate, endDate, gt: string;
  CurDate,GoodsName,GoodsType,CurNum,LastNum,TotNum,GoodsOwner: string;
  oldCur: TCursor;
begin
  oldCur := Screen.Cursor;
  startDate := FormatDateTime('yyyymmdd', DatetimePicker1.DateTime);
  endDate := FormatDateTime('yyyymmdd', DatetimePicker2.DateTime);
  gt := Trim(ComboBox2.Items.Strings[ComboBox2.ItemIndex]);
  sqlInsert := ' insert into Tab_TempQuery (CurDate,GoodsName,GoodsType,CurNum,LastNum,TotNum,GoodsOwner,button1,button2,button3) '
       + ' values(:CurDate,:GoodsName,:GoodsType,:CurNum,:LastNum,:TotNum,:GoodsOwner,:button1,:button2,:button3) ';
  try
    Screen.Cursor := crHourGlass;
    DM1.ADOConnection1.BeginTrans;

    case ComboBox1.ItemIndex of
      0://采购表
        begin
          //当期
          DM1.ADOQuery1.Close;
          DM1.ADOQuery1.SQL.Text := ' select A.InDate, B.GoodsName, B.GoodsType, count(1) as CurNum '
                                  + ' from Tab_Goods A '
                                  + ' left join Tab_GoodsNo B on A.GoodsNo=B.GoodsNo '
                                  + ' where A.InDate>=''' + startDate + ''' and A.InDate<=''' + endDate + ''' '
                                  + ' group by A.InDate, B.GoodsName, B.GoodsType '
                                  + ' order by A.InDate, B.GoodsName, B.GoodsType ';
          DM1.ADOQuery1.Open;
          //上期
          DM1.ADOQuery2.Close;
          DM1.ADOQuery2.Filter := '';
          DM1.ADOQuery2.Filtered := true;          
          DM1.ADOQuery2.SQL.Text := ' select A.InDate, B.GoodsName, B.GoodsType, count(1) as LastNum '
                                  + ' from Tab_Goods A '
                                  + ' left join Tab_GoodsNo B on A.GoodsNo=B.GoodsNo '
                                  + ' where A.InDate<''' + startDate + ''' '
                                  + ' group by A.InDate, B.GoodsName, B.GoodsType '
                                  + ' order by A.InDate, B.GoodsName, B.GoodsType ';
          DM1.ADOQuery2.Open;
          //写入
          DM1.ADOQuery1.First;
          while not DM1.ADOQuery1.Eof do