//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "weekquery.h"
#include "datam.h"
#include "daysql.h"
#include "mainform.h"
#include "sas.h"
#include "monsql.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "SUIDBCtrls"
#pragma link "SUIEdit"
#pragma link "SUIForm"
#pragma link "SUIButton"
#pragma link "SUIStatusBar"
#pragma link "SUIComboBox"
#pragma link "SUIGroupBox"
#pragma link "SUIImagePanel"
#pragma link "SUIListBox"
#pragma resource "*.dfm"
Tweekqu *weekqu;
//---------------------------------------------------------------------------
__fastcall Tweekqu::Tweekqu(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------

void __fastcall Tweekqu::suiDBGrid1DblClick(TObject *Sender)
{
suiEdit1->Text=dm->dayseq->FieldByName("item_code")->AsString;

}
//---------------------------------------------------------------------------






void __fastcall Tweekqu::suiButton2Click(TObject *Sender)
{
if (dm->dayseq->Active){
SaveDialog1->Filter = "EXCEL files (*.xls)|*.XLS";
  if(SaveDialog1->Execute()){
    dm->LOCAL->DatabaseName=SaveDialog1->InitialDir;
    dm->LOCAL->TableName=SaveDialog1->FileName;
   // SaveDialog1->FileName;
   try{
dm->TOEXCEL->Execute() ;
}catch(...){
}
}
 }      
}
//---------------------------------------------------------------------------

void __fastcall Tweekqu::suiButton1Click(TObject *Sender)
{
 Tanay *form=new Tanay(this);
form->ShowModal();
delete form;
}
//---------------------------------------------------------------------------

void __fastcall Tweekqu::FormCreate(TObject *Sender)
{
 DateTimePicker1->Date=Now()-9;
DateTimePicker2->Date=Now()-1;


   dm->dept->Active=true;
   dm->sect->Active=true;
   dm->artcode->Active=true;
   dm->dayseq->Active=false;

      dm->dept->First();
 while(!dm->dept->Eof){

  suiDBComboBox1->Items->Add(dm->dept->FieldByName("Dept_code")->AsString);
   dm->dept->Next();
     }
        dm->sect->First();
 while(!dm->sect->Eof){
  suiDBComboBox2->Items->Add(dm->sect->FieldByName("section_code")->AsString);
   dm->sect->Next();
  }


     
   dm->artcode->First();
 while(!dm->artcode->Eof){
 suiDBComboBox3->Items->Add(dm->artcode->FieldByName("article_code")->AsString);
 dm->artcode->Next();
}
dm->dept->Active=false;
   dm->sect->Active=false;
   dm->artcode->Active=false;
   suiButton2->Enabled=false;
   suiComboBox1->Text=store;
   flag=1;
    tempstr="";
 
 DateTimePicker1->Date=Now()-8;
DateTimePicker2->Date=Now()-1;
}
//---------------------------------------------------------------------------

void __fastcall Tweekqu::suiButton3Click(TObject *Sender)
{

   AnsiString str ;
suiButton2->Enabled=true;
str="select store,weeklastd ,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,sellweek.item_code,itmaster.item_desc , quantity, customs,round((SELL/quantity),2) as price , sell   FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
 
 str=str+" where quantity<>0 and weeklastd >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+StrToDate(DateTimePicker2->Date.DateString())+"# " ;
 
   tempstr=str;
 // tempstr=str+" and customs>0";
 try{
dm->dayseq->Active=false;
dm->dayseq->SQL->Clear();
dm->dayseq->SQL->Add(str);
dm->dayseq->Active=true;

}
catch(...){
}

suiEdit1->Text="";

flag=1;
suiStatusBar1->SimpleText="全店鋪   查詢時間從"+DateTimePicker1->Date.DateString()+"至"+DateTimePicker2->Date.DateString();

}
//---------------------------------------------------------------------------

void __fastcall Tweekqu::suiComboBox1DblClick(TObject *Sender)
{
 AnsiString str;
if (flag==1){
 str="select store,weeklastd ,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,sellweek.item_code,itmaster.item_desc ,quantity, customs,round((SELL/customs),2) as price , sell   FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where quantity<>0 and weeklastd >=# "+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+StrToDate(DateTimePicker2->Date.DateString())+"#" ;
 if(!suiComboBox1->Text.IsEmpty()){
str=str+" and store='"+suiComboBox1->Text.Trim()+"' ";
}
}
if (flag==2){
str="select store,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,sellweek.item_code,itmaster.item_desc,stock_onhand ,SUM(quantity) AS quantities ,SUM(customs) AS allcustoms ,itmaster.price ,SUM(SELL) as total  FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where weeklastd >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+StrToDate(DateTimePicker2->Date.DateString())+"#    ";
 if(!suiComboBox1->Text.IsEmpty()){
str=str+" and store='"+suiComboBox1->Text.Trim()+"' ";
}
}



  tempstr=str;
if (flag==2){
  if ( suiRadioButton2->Checked)
str=str+"  group by store,article_code,itmaster.dept_code,itmaster.section_code,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand  order by SUM(SELL) desc  " ;
else
str=str+"  group by store,article_code,itmaster.dept_code,itmaster.section_code,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(quantity) desc ";

}


 try{
dm->dayseq->Active=false;
dm->dayseq->SQL->Clear();
dm->dayseq->SQL->Add(str);
 dm->dayseq->Active=true;
 }
 catch(...){
 dm->dayseq->Active=false;
 }

  suiDBComboBox1->Text="";
suiDBComboBox2->Text="";
suiDBComboBox3->Text="";
suiEdit1->Text="";

suiStatusBar1->SimpleText="  查詢時間從"+DateTimePicker1->Date.DateString()+"至"+DateTimePicker2->Date.DateString()+"  庫存為本店庫存";;


}
//---------------------------------------------------------------------------

void __fastcall Tweekqu::suiDBComboBox1DblClick(TObject *Sender)
{

AnsiString str;
if (flag==1){
 str="select store,weeklastd ,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,sellweek.item_code,itmaster.item_desc ,quantity, customs,round((SELL/customs),2) as price , sell   FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where quantity<>0 and weeklastd >=# "+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+StrToDate(DateTimePicker2->Date.DateString())+"#" ;
 if(!suiComboBox1->Text.IsEmpty()){
str=str+" and store='"+suiComboBox1->Text.Trim()+"' ";
}
}
if (flag==2){
str="select store,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,sellweek.item_code,itmaster.item_desc,stock_onhand ,SUM(quantity) AS quantities ,SUM(customs) AS allcustoms ,itmaster.price ,SUM(SELL) as total  FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where weeklastd >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and monthlastd <=#"+StrToDate(DateTimePicker2->Date.DateString())+"#    ";
 if(!suiComboBox1->Text.IsEmpty()){
str=str+" and store='"+suiComboBox1->Text.Trim()+"' ";
}
}




if( !suiDBComboBox1->Text.IsEmpty()) {
str=str+" and  itmaster.dept_code='"+suiDBComboBox1->Text.Trim()+"'";
  tempstr=str;
if (flag==2){
  if ( suiRadioButton2->Checked)
str=str+"  group by store,article_code,itmaster.dept_code,itmaster.section_code,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand  order by SUM(SELL) desc  " ;
else
str=str+"  group by store,article_code,itmaster.dept_code,itmaster.section_code,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(quantity) desc ";

}



 try{
dm->dayseq->Active=false;
dm->dayseq->SQL->Clear();
dm->dayseq->SQL->Add(str);
 dm->dayseq->Active=true;
 }
 catch(...){
 dm->dayseq->Active=false;
 }






suiDBComboBox2->Text="";
suiDBComboBox3->Text="";
suiEdit1->Text="";
 suiStatusBar1->SimpleText="  查詢時間從"+DateTimePicker1->Date.DateString()+"至"+DateTimePicker2->Date.DateString()+"  庫存為本店庫存";;

}
       
}
//---------------------------------------------------------------------------