unit MAIN_QUERY_SPJXCMX;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, MAIN_QUERY, DB, DBTables, ComCtrls, StdCtrls, TFlatComboBoxUnit,
  TFlatSpeedButtonUnit, Grids, DBGrids, DsFancyButton, ExtCtrls,myself,frmmain;

type
  TFrm_main_query_spjxcmx = class(TFrm_main_query)
    Label5: TLabel;
    CB_TYPE: TFlatComboBox;
    Query1: TQuery;
    procedure FormCreate(Sender: TObject);
    procedure CB_goodsChange(Sender: TObject);
    procedure CB_depotChange(Sender: TObject);
    procedure SB_queryClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure SB_printClick(Sender: TObject);
    procedure DBG_dataDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_main_query_spjxcmx: TFrm_main_query_spjxcmx;

implementation

uses PROGRASS, PRINT_SPJXCMX, PRINT_TEMP;

{$R *.dfm}

procedure TFrm_main_query_spjxcmx.FormCreate(Sender: TObject);
var
   B_find:Boolean;
   I_temp:integer;
begin
  inherited;
   self.Caption :='商品進銷存明細';
   with query_temp do
   begin
      close;
      sql.Clear ;
      sql.Text :='select name from depot where setid=999';
      open;
      CB_depot.Items.Clear ;
      CB_depot.Style :=csDropDownList;
      CB_depot.Items.add('所有');
      while not eof do
      begin
         CB_depot.Items.add(fieldbyname('name').asstring);
         next;
      end;
      close;
      sql.Clear ;
      sql.text:='select * from goodsmessage order by name';
      open;
      CB_goods.Items.Clear ;
      CB_goods.Style :=csDropDownList;
      CB_goods.Items.add('所有');
      while not eof do
      begin
         B_find:=false;
         for I_temp:=0 to CB_goods.items.Count-1 do
         begin
           if CB_goods.Items[I_temp]=fieldbyname('name').asstring then
             B_find:=true;          //找到相同名稱
         end;
         if not B_find then        //沒有找到時增加
           CB_goods.Items.add(fieldbyname('name').asstring);
         next;
      end;
      close;
   end;
end;                      

procedure TFrm_main_query_spjxcmx.CB_goodsChange(Sender: TObject);
begin
  inherited;
   with query_temp do
   begin
      close;
      sql.Clear ;
      sql.text:='select * from goodsmessage where name="'+cb_goods.Text+'" order by type ';
      open;
      cb_type.Items.Clear ;
      cb_type.Style :=csDropDownList;
      cb_type.Items.add('所有');
      while not eof do
      begin
         cb_type.Items.add(fieldbyname('type').asstring);
         next;
      end;
      close;
   end;
   CB_depotChange(sender);
end;

procedure TFrm_main_query_spjxcmx.CB_depotChange(Sender: TObject);
begin
  inherited;
   query_data.Close;
   sb_query.Enabled:=true;
end;

procedure TFrm_main_query_spjxcmx.SB_queryClick(Sender: TObject);
var
   S_depotname,S_goodsname,S_goodstype,S_begindate,S_enddate:string;
begin
  inherited;
   if not Checkrights(USERRIGHTS,'查看') then
   begin
      messagedlg(Errormsg0006+'0007',mterror,[mbok],0);
      exit;
   end;
   S_begindate:=formatdatetime('yyyy-mm-dd',dtp_begin.Date );
   S_enddate:=formatdatetime('yyyy-mm-dd',dtp_end.Date );
   if S_begindate>S_enddate then            //開始日期大于結(jié)束日期
   begin
      messagedlg(Errormsg0012+'0026',mtError,[mbok],0);
      dtp_begin.SetFocus ;
      exit;
   end;
   S_depotname:=cb_depot.Text ;
   S_goodsname:=cb_goods.Text ;
   S_goodstype:=cb_type.text;
   if (S_depotname='') or (S_depotname='所有') then
      S_depotname:='%';
   if (S_goodsname='') or (S_goodsname='所有') then
      S_goodsname:='%';
   if (S_goodstype='') or (S_goodstype='所有') then
      S_goodstype:='%';
   application.CreateForm (TFrm_prograss,Frm_prograss);
   Frm_prograss.P_message.Caption :='系統(tǒng)正在處理歷史數(shù)據(jù)，請稍候……';
   frm_prograss.FormStyle :=fsStayOnTop;
   frm_prograss.show;
   frm_prograss.Update ;
   with query_data do
   begin
      close;
      sql.Clear ;
      sql.Text :='SELECT Depot.NAME depotname, Startrecord.MYDATE, Goodsmessage.NAME goodsname,Goodsmessage.TYPE, "期初    " JXC,'     //期初
               +' Startrecord.NUMBER number, Startrecord.PRICE,"                    " PEOPLENAME, Startrecord.MEMO'
               +' FROM Startrecord INNER JOIN Depot'
               +' ON  (Startrecord.DEPOTID = Depot.ID) INNER JOIN Goodsmessage'
               +' ON  (Startrecord.GOODSID = Goodsmessage.ID)'
               +' WHERE DEPOT.NAME LIKE "'+S_depotname+'" AND GOODSMESSAGE.NAME LIKE "'
               + S_goodsname+ '" AND GOODSMESSAGE.TYPE LIKE "'+S_goodstype+'" AND STARTRECORD.MYDATE>="'
               + S_begindate+ '" AND STARTRECORD.MYDATE<="'+S_enddate+'"'
               +' UNION ALL'               //進貨
               +' SELECT Depot.NAME depotname, Stockrecord.MYDATE, Goodsmessage.NAME goodsname, Goodsmessage.TYPE, "進貨    " JXC,'
               +' Stockrecord.NUMBER number, Stockrecord.PRICE, "                    " peoplename, Stockrecord.MEMO'
               +' FROM Stockrecord INNER JOIN Depot'
               +' ON  (Stockrecord.DEPOTID = Depot.ID) INNER JOIN Goodsmessage'
               +' ON  (Stockrecord.GOODSID = Goodsmessage.ID)'
               +' WHERE DEPOT.NAME LIKE "'+S_depotname+'" AND GOODSMESSAGE.NAME LIKE "'
               + S_goodsname+ '" AND GOODSMESSAGE.TYPE LIKE "'+S_goodstype+'" AND Stockrecord.MYDATE>="'
               + S_begindate+ '" AND Stockrecord.MYDATE<="'+S_enddate+'"'
               +' UNION ALL'            //進貨退貨
               +' SELECT Depot.NAME depotname, Threcord.MYDATE, Goodsmessage.NAME goodsname, Goodsmessage.TYPE, "進貨退貨" JXC,'
               +' (0 - Threcord.NUMBER) number, Threcord.PRICE, "                    " peoplename, Threcord.MEMO'
               +' FROM Threcord INNER JOIN Depot'
               +' ON  (Threcord.DEPOTID = Depot.ID) INNER JOIN Goodsmessage'
               +' ON  (Threcord.GOODSID = Goodsmessage.ID)'
               +' WHERE DEPOT.NAME LIKE "'+S_depotname+'" AND GOODSMESSAGE.NAME LIKE "'
               + S_goodsname+ '" AND GOODSMESSAGE.TYPE LIKE "'+S_goodstype+'" AND Threcord.MYDATE>="'
               + S_begindate+ '" AND Threcord.MYDATE<="'+S_enddate+'"'
               +' UNION ALL'           //銷售
               +' SELECT Depot.NAME depotname, Sellrecord.MYDATE, Goodsmessage.NAME goodsname, Goodsmessage.TYPE, "銷售    " JXC,'
               +' Sellrecord.NUMBER number, Sellrecord.PRICE, People.Name peoplename, Sellrecord.MEMO'
               +' FROM Sellrecord INNER JOIN Depot'
               +' ON  (Sellrecord.DEPOTID = Depot.ID) INNER JOIN Goodsmessage'
               +' ON  (Sellrecord.GOODSID = Goodsmessage.ID)'
               +' INNER JOIN People ON  (Sellrecord.PEOPLEID = People.Id)'
               +' WHERE DEPOT.NAME LIKE "'+S_depotname+'" AND GOODSMESSAGE.NAME LIKE "'
               + S_goodsname+ '" AND GOODSMESSAGE.TYPE LIKE "'+S_goodstype+'" AND Sellrecord.MYDATE>="'
               + S_begindate+ '" AND Sellrecord.MYDATE<="'+S_enddate+'"'
               +' UNION ALL'           //銷售退貨
               +' SELECT Depot.NAME depotname, XSTHrecord.MYDATE, Goodsmessage.NAME goodsname, Goodsmessage.TYPE, "銷售退貨" JXC,'
               +' (0 - XSTHrecord.NUMBER) number, XSTHrecord.PRICE, People.Name peoplename, XSTHrecord.MEMO'
               +' FROM XSTHrecord INNER JOIN Depot'
               +' ON  (XSTHrecord.DEPOTID = Depot.ID) INNER JOIN Goodsmessage'
               +' ON  (XSTHrecord.GOODSID = Goodsmessage.ID)'
               +' INNER JOIN People ON  (XSTHrecord.PEOPLEID = People.Id)'
               +' WHERE DEPOT.NAME LIKE "'+S_depotname+'" AND GOODSMESSAGE.NAME LIKE "'
               + S_goodsname+ '" AND GOODSMESSAGE.TYPE LIKE "'+S_goodstype+'" AND XSTHrecord.MYDATE>="'
               + S_begindate+ '" AND XSTHrecord.MYDATE<="'+S_enddate+'"'   
               +' ORDER BY Depot.NAME,MYDATE';
      try
         open;
      except
         frm_prograss.Free ;
         messagedlg(Errormsg0001+'0017+0018+0020+0023+0025',mtError,[mbok],0);
         close;
         exit;
      end;
   end;
   frm_prograss.Free ;
end;

procedure TFrm_main_query_spjxcmx.FormShow(Sender: TObject);
begin
  inherited;
   SB_query.Click ;
end;

procedure TFrm_main_query_spjxcmx.SB_printClick(Sender: TObject);
begin
  inherited;
   try
      Application.CreateForm(TFrm_print_SPJXCMX, Frm_print_SPJXCMX);
      Application.CreateForm(TFrm_print_temp, Frm_print_temp);
   except
      messagedlg(Errormsg0011,mtError,[mbok],0);
      exit;
   end;
   Frm_print_SPJXCMX.QuickRep_SPJXCMX.preview;
   Frm_print_SPJXCMX.Free ;
   Frm_print_temp.Free ;
end;

procedure TFrm_main_query_spjxcmx.DBG_dataDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
  inherited;
   if (trim(query_data.FieldByName('JXC').AsString)='進貨')
      or (trim(query_data.FieldByName('JXC').AsString)='期初') then
      DBG_data.Canvas.Font.Color :=clblue;
   DBG_data.DefaultDrawColumnCell(rect,datacol,column,state);
end;

end.