unit sell_unit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, TEMPLETFORM, Menus, ComCtrls, ToolWin, StdCtrls, ExtCtrls,
  Grids, DBGrids, NewDBGrid, DB, ADODB, LabelButton, MoveImageButton,
  DBTables;

type
  Tsell = class(TTEMPLETF)
    NewDBGrid1: TNewDBGrid;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    Label2: TLabel;
    MIBtnOK: TMoveImgBtn;
    lbBtnOK: TLabelBtn;
    qry: TADOQuery;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    qry1: TADOQuery;
    qry2: TADOQuery;
    DataSource3: TDataSource;
    qry2goods_id: TStringField;
    qry2depot1: TFloatField;
    qry2depot2: TFloatField;
    qry2depot3: TFloatField;
    qry2depot4: TFloatField;
    qry2depot5: TFloatField;
    qry2Name: TStringField;
    procedure MIBtnOKClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  sell: Tsell;

implementation
uses data_unit;

{$R *.dfm}

procedure Tsell.MIBtnOKClick(Sender: TObject);
var date_b,date_a,depot,i:integer;
var date,date1:string;
begin
  inherited;

date:=datetostr(DateTimePicker1.date);
date1:=datetostr(DateTimePicker2.date);
date_b:=strtoint(copy(date,4,1)+copy(date,6,2)+copy(date,9,2));
date_a:=strtoint(copy(date1,4,1)+copy(date1,6,2)+copy(date1,9,2));


//計算倉庫1-------------------------------------------------------------------------------
with qry do
begin
close;
sql.Clear ;
sql.Add('select Product_ID,sum(distinct Amount) from usrdetail where right(left(Ordinal,7),5) between :date_b and :date_a and depot=:id GROUP BY Product_ID');
parameters.ParamByName('date_b').Value:=date_b;
parameters.ParamByName('date_a').Value:=date_a;
parameters.ParamByName('id').Value:=1;
open;
first;
end;


if qry.RecordCount<>0 then
begin

with qry1 do
begin
close;
sql.Clear ;
sql.Add('delete from sell_depot');
execsql;
while not qry.Eof do
begin
qry1.close;
qry1.sql.Clear ;
qry1.sql.Add('select * from sell_depot where goods_id=:id');
qry1.parameters.ParamByName('id').Value:=qry.Fields[0].Value;
qry1.open;

if qry1.RecordCount<>0 then
begin
qry1.Edit;
qry1.fields[1].Value:=(qry1.fields[1].Value)+(qry.fields[1].Value);
qry1.Post;
end
else
begin
qry1.Append ;
qry1.Fields[0].Value:=qry.fields[0].Value;
qry1.fields[1].Value:=qry.fields[1].Value;
qry1.Post;
end;
qry.Next ;
end;
end;
end;
//計算倉庫2-------------------------------------------------------------------------------
with qry do
begin
close;
sql.Clear ;
sql.Add('select Product_ID,sum(distinct Amount) from usrdetail where right(left(Ordinal,7),5) between :date_b and :date_a and depot=:id GROUP BY Product_ID');
parameters.ParamByName('date_b').Value:=date_b;
parameters.ParamByName('date_a').Value:=date_a;
parameters.ParamByName('id').Value:=2;
open;
first;
end;

if qry.RecordCount<>0 then
begin
with qry1 do
begin

while not qry.Eof do
begin
qry1.close;
qry1.sql.Clear ;
qry1.sql.Add('select * from sell_depot where goods_id=:id');
qry1.parameters.ParamByName('id').Value:=qry.Fields[0].Value;
qry1.open;

if qry1.RecordCount<>0 then
begin
qry1.Edit;
qry1.fields[2].Value:=(qry1.fields[2].Value)+(qry.fields[1].Value);
qry1.Post;
end
else
begin
qry1.Append ;
qry1.Fields[0].Value:=qry.fields[0].Value;
qry1.fields[2].Value:=qry.fields[1].Value;
qry1.Post;
end;
qry.Next ;
end;
end;
end;
//計算倉庫3-------------------------------------------------------------------------------
with qry do
begin
close;
sql.Clear ;
sql.Add('select Product_ID,sum(distinct Amount) from usrdetail where right(left(Ordinal,7),5) between :date_b and :date_a and depot=:id GROUP BY Product_ID');
parameters.ParamByName('date_b').Value:=date_b;
parameters.ParamByName('date_a').Value:=date_a;
parameters.ParamByName('id').Value:=3;
open;
first;
end;

if qry.RecordCount<>0 then
begin
with qry1 do
begin
while not qry.Eof do
begin
qry1.close;
qry1.sql.Clear ;
qry1.sql.Add('select * from sell_depot where goods_id=:id');
qry1.parameters.ParamByName('id').Value:=qry.Fields[0].Value;
qry1.open;

if qry1.RecordCount<>0 then
begin
qry1.Edit;
qry1.fields[3].Value:=(qry1.fields[3].Value)+(qry.fields[1].Value);
qry1.Post;
end
else
begin
qry1.Append ;
qry1.Fields[0].Value:=qry.fields[0].Value;
qry1.fields[3].Value:=qry.fields[1].Value;
qry1.Post;
end;
qry.Next ;
end;
end;
end;
//計算倉庫4-------------------------------------------------------------------------------
with qry do
begin
close;
sql.Clear ;
sql.Add('select Product_ID,sum(distinct Amount) from usrdetail where right(left(Ordinal,7),5) between :date_b and :date_a and depot=:id GROUP BY Product_ID');
parameters.ParamByName('date_b').Value:=date_b;
parameters.ParamByName('date_a').Value:=date_a;
parameters.ParamByName('id').Value:=4;
open;
first;
end;

if qry.RecordCount<>0 then
begin
with qry1 do
begin
while not qry.Eof do
begin
qry1.close;
qry1.sql.Clear ;
qry1.sql.Add('select * from sell_depot where goods_id=:id');
qry1.parameters.ParamByName('id').Value:=qry.Fields[0].Value;
qry1.open;

if qry1.RecordCount<>0 then
begin
qry1.Edit;
qry1.fields[4].Value:=(qry1.fields[4].Value)+(qry.fields[1].Value);
qry1.Post;
end
else
begin
qry1.Append ;
qry1.Fields[0].Value:=qry.fields[0].Value;
qry1.fields[4].Value:=qry.fields[1].Value;
qry1.Post;
end;
qry.Next ;
end;
end;
end;
//計算倉庫5------------------------------------------------------------------------------
with qry do
begin
close;
sql.Clear ;
sql.Add('select Product_ID,sum(distinct Amount) from usrdetail where right(left(Ordinal,7),5) between :date_b and :date_a and depot=:id GROUP BY Product_ID');
parameters.ParamByName('date_b').Value:=date_b;
parameters.ParamByName('date_a').Value:=date_a;
parameters.ParamByName('id').Value:=5;
open;
first;
end;

if qry.RecordCount<>0 then
begin
with qry1 do
begin
while not qry.Eof do
begin
qry1.close;
qry1.sql.Clear ;
qry1.sql.Add('select * from sell_depot where goods_id=:id');
qry1.parameters.ParamByName('id').Value:=qry.Fields[0].Value;
qry1.open;

if qry1.RecordCount<>0 then
begin
qry1.Edit;
qry1.fields[5].Value:=(qry1.fields[5].Value)+(qry.fields[1].Value);
qry1.Post;
end
else
begin
qry1.Append ;
qry1.Fields[0].Value:=qry.fields[0].Value;
qry1.fields[5].Value:=qry.fields[1].Value;
qry1.Post;
end;
qry.Next ;
end;
end;
end;

//---------------------------------------------
with qry2 do
begin
close;
sql.Clear ;
sql.Add('select * from v_sell_depot') ;
open;
if recordcount=0 then
application.MessageBox('沒有找到任何銷售記錄！','系統信息',mb_ok+mb_iconinformation);
end;

end;

procedure Tsell.FormShow(Sender: TObject);
type data=array[1..6] of string ;
var a:data;
    i:integer;
begin
  inherited;

DateTimePicker1.Date:=date();
DateTimePicker2.Date:=date();

i:=1;
with qry do
begin
close;
sql.Clear ;
sql.Add('select * from depot order by depot_id');
open;
first;
while not eof do
begin
a[i]:=fields[1].Value ;
i:=i+1;
next;
end;

//newdbgrid1.Columns[2].Title.caption:=trim(a[1]);
//newdbgrid1.Columns[2].Width:=64;
//newdbgrid1.Columns[2].Title.alignment:=tacenter;

newdbgrid1.Columns[2].Title.caption:=trim(a[2]);
newdbgrid1.Columns[2].Width:=80;
newdbgrid1.Columns[2].Title.alignment:=tacenter;

newdbgrid1.Columns[3].Title.caption:=trim(a[3]);
newdbgrid1.Columns[3].Width:=80;
newdbgrid1.Columns[3].Title.alignment:=tacenter;

newdbgrid1.Columns[4].Title.caption:=trim(a[4]);
newdbgrid1.Columns[4].Width:=80;
newdbgrid1.Columns[4].Title.alignment:=tacenter;

newdbgrid1.Columns[5].Title.caption:=trim(a[5]);
newdbgrid1.Columns[5].Width:=80;
newdbgrid1.Columns[5].Title.alignment:=tacenter;

newdbgrid1.Columns[6].Title.caption:=trim(a[6]);
newdbgrid1.Columns[6].Width:=80;
newdbgrid1.Columns[6].Title.alignment:=tacenter;
end;

end;

end.