dfm_saldate.pas

仓库管理系统 仓库管理系统

unit dfm_saldate;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ComCtrls, StdCtrls, Buttons, ExtCtrls, Grids, DBGrids, Db, DBTables, Mask,
  DBCtrls, ADODB, DBGridEh;

type
  Tdfmdatesal = class(TForm)
    StatusBar1: TStatusBar;
    Panel1: TPanel;
    Panel2: TPanel;
    postbtn: TBitBtn;
    dstbranch: TDataSource;
    tbltbranch: TADOTable;
    Label2: TLabel;
    Label11: TLabel;
    Label1: TLabel;
    Label5: TLabel;
    DateTimePicker1: TDateTimePicker;
    BitBtn1: TBitBtn;
    ComboBox1: TComboBox;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    ADOQuery1bmh: TWideStringField;
    ADOQuery1clabel: TWideStringField;
    ADOQuery1flh: TWideStringField;
    ADOQuery1esl: TFloatField;
    ADOQuery1esjje: TBCDField;
    ADOQuery1ezke: TBCDField;
    panel6: TPanel;
    Panel4: TPanel;
    Label3: TLabel;
    Bevel1: TBevel;
    DBGridEh1: TDBGridEh;
    Panel3: TPanel;
    Label4: TLabel;
    Bevel2: TBevel;
    DBGridEh2: TDBGridEh;
    ADOQuery3: TADOQuery;
    ADOQuery4: TADOQuery;
    atblsaday: TADOTable;
    atbldefault: TADOTable;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure quitbtnClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure tbltbranchPostError(DataSet: TDataSet; E: EDatabaseError;
      var Action: TDataAction);
    procedure FormKeyPress(Sender: TObject; var Key: Char);
    procedure BitBtn1Click(Sender: TObject);
    procedure delquery;
    procedure appquery;
    procedure DBGridEh2TitleClick(Column: TColumnEh);
    procedure DBGridEh1TitleClick(Column: TColumnEh);
    procedure postbtnClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  dfmdatesal: Tdfmdatesal;
  delstr,fst:string;

implementation

uses  dbmRainbowMis, chHeadUnit;

{$R *.DFM}

procedure Tdfmdatesal.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  tbltbranch.active:=false;
  atblsaday.Active := false;
  atbldefault.Active :=false;  
  action:=cafree;
end;

procedure Tdfmdatesal.quitbtnClick(Sender: TObject);
begin
  close;
end;

procedure Tdfmdatesal.FormCreate(Sender: TObject);
begin
  fst := ExtractFilePath(Application.ExeName);
  tbltbranch.active:=true;
  atblsaday.Active := true;
  atbldefault.Active :=true;
  chHeadUnit.combolist(combobox1,tbltbranch,'bmh','bmmc',2);
  combobox1.Text:=atbldefault['cbmh'];
end;

procedure Tdfmdatesal.tbltbranchPostError(DataSet: TDataSet;
  E: EDatabaseError; var Action: TDataAction);
begin
  Application.MessageBox('不允许空纪录或重复部门号，请检查后重新保存？',
        '提示信息', MB_OK);
  abort;      
end;

procedure Tdfmdatesal.FormKeyPress(Sender: TObject; var Key: Char);
begin
  chHeadUnit.tabventer(dfmdatesal,key); 
end;

procedure Tdfmdatesal.BitBtn1Click(Sender: TObject);
var
  fstr:string;
begin
  fstr:='SELECT t_saledet.bmh, t_saledet.clabel, t_ware.flh,sum(t_saledet.sl) as esl, sum(t_saledet.sjje) as esjje,sum(t_saledet.zke) as ezke FROM t_saledet,t_ware'
       +' WHERE t_saledet.clabel=t_ware.clabel and t_saledet.spbh=t_ware.spbh and t_saledet.bmh like ''%s'' AND ([t_saledet.rq])=#%s#'
       +' group by t_saledet.bmh, t_ware.flh, t_saledet.clabel';
  with ADOQuery1 do
  begin
    close;
    sql.Clear ;
    sql.Add(Format(fstr,[chHeadUnit.getbmh(ComboBox1.text)+'%',datetostr(DateTimePicker1.Date)]));
    Prepared;
    open;
  end;
  delstr:='delete from t_stoindel';{清空盘点进货差异表}
  delquery;
  delstr:='delete from t_stooutdel';{清空盘点出货差异表}
  delquery;
  delstr:='insert into t_stoindel (cinceptbra,clabel,spbh,sl) select cinceptbra,clabel,spbh,sl from t_stoin where cinceptbra like ''%s'' AND cdate=#%s#';
  appquery;
  delstr:='insert into t_stooutdel (csendbra,clabel,spbh,sl) select csendbra,clabel,spbh,sl from t_stoout where csendbra like ''%s'' AND cdate=#%s#';
  appquery;
  fstr:='SELECT t_store.bmh, t_store.clabel, Sum(t_store.dqsl) AS sdqsl, Sum(t_stoindel.sl) AS sisl, Sum(t_stooutdel.sl) AS sosl, t_ware.flh'
       +' FROM ((t_store LEFT JOIN t_stoindel ON (t_store.bmh = t_stoindel.cinceptbra) AND (t_store.spbh = t_stoindel.spbh) AND (t_store.clabel = t_stoindel.clabel))'
       +' LEFT JOIN t_stooutdel ON (t_store.clabel = t_stooutdel.clabel) AND (t_store.spbh = t_stooutdel.spbh) AND (t_store.bmh = t_stooutdel.csendbra))'
       +' LEFT JOIN t_ware ON (t_store.clabel = t_ware.clabel) AND (t_store.spbh = t_ware.spbh)'
       +' where t_store.bmh=''%s'''
       +' GROUP BY t_store.bmh, t_store.clabel, t_ware.flh';
  with ADOQuery2 do
  begin
    close;
    sql.Clear ;
    sql.Add(Format(fstr,[chHeadUnit.getbmh(ComboBox1.text)]));
    Prepared;
    open;
  end;
end;

procedure Tdfmdatesal.delquery;
begin
  with ADOQuery3 do
  begin
    SQL.Clear;
    SQL.Add(delstr);
    ExecSQL ;
  end;
end;

procedure Tdfmdatesal.appquery;
begin
  with ADOQuery3 do
  begin
    sql.Clear ;
    sql.Add(Format(delstr,[chHeadUnit.getbmh(ComboBox1.text)+'%',datetostr(DateTimePicker1.Date)]));
    ExecSQL;
  end;
end;

procedure Tdfmdatesal.DBGridEh2TitleClick(Column: TColumnEh);
begin
  chHeadunit.ehtitclick(column); 
end;

procedure Tdfmdatesal.DBGridEh1TitleClick(Column: TColumnEh);
begin
  chHeadunit.ehtitclick(column); 
end;

procedure Tdfmdatesal.postbtnClick(Sender: TObject);
var
  fstr:string;
begin
  if Application.MessageBox('你确信数据正确，进行日结吗？',
        '提示信息', MB_OKcancel)=idok then
  begin
    if not atblsaday.Locate ('cdate',formatdatetime('yyyy-mm-dd',datetimepicker1.Date),[loPartialKey]) then
    begin
      atblsaday.Append;
      atblsaday['cdate']:=formatdatetime('yyyy-mm-dd',datetimepicker1.Date);
      atblsaday.Post;
    end;
    fstr:='SELECT bmh, rq, spbh, sum(t_saledet.sl) as sl, round(avg(t_saledet.lsj)) as lsj, sum(t_saledet.sjje) as sjje, sum(t_saledet.zke) as zke, clabel FROM t_saledet'
       +' where bmh like ''%s'' and rq=#%s#'
       +' GROUP BY rq, bmh, clabel, spbh'
       +' order by rq, bmh, clabel, spbh';
    with ADOQuery4 do
    begin
      close;
      sql.Clear ;
      sql.Add(Format(fstr,[chHeadUnit.getbmh(ComboBox1.text)+'%',datetostr(DateTimePicker1.Date)]));
      Prepared;
      open;
    end;
    ADOQuery4.SaveToFile(fst+'dataout\'+trim(chHeadUnit.getbmh(ComboBox1.text))+'xs'+formatdatetime('mmdd',DateTimePicker1.Date)+'.dtg');
  end
  else
    abort;    
end;

end.