monsqla.cpp

销售分析,JUSCO,日月周查询,待完善.

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

#include <vcl.h>
#pragma hdrstop
#include <systdate.h>
#include "monsqla.h"
#include "mainform.h"
//#include "Unit1.h"
#include "datam.h"
#include "sas.h"
#include "goods.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "SUIForm"
#pragma link "SUIDBCtrls"
#pragma link "SUIButton"
#pragma link "SUIEdit"
#pragma link "SUIStatusBar"
#pragma link "SUIComboBox"
#pragma link "SUIGroupBox"
#pragma link "SUIImagePanel"
#pragma link "SUIListBox"
#pragma resource "*.dfm"
Tday *day;
int flag;
AnsiString tempstr="";
//---------------------------------------------------------------------------
__fastcall Tday::Tday(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------




void __fastcall Tday::FormCreate(TObject *Sender)
{


DateTimePicker1->Date=Now()-1;
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="";
// Tday::suiButton3Click(this);

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











void __fastcall Tday::suiDBComboBox2Change(TObject *Sender)
{


AnsiString str;
if (flag==1){
 str="select store,date ,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,detail.item_code,itmaster.item_desc ,QTY as quantity,cust  as customs,round((NETAMT/qty),2) as price ,NETAMT as sell   FROM DETAIL left join itmaster on detail.item_code=itmaster.item_code" ;
str=str+" where qty<>0 and date >=# "+StrToDate(DateTimePicker1->Date.DateString())+"# and date <=#"+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,detail.item_code,itmaster.item_desc,stock_onhand ,SUM(QTY) AS quantity ,SUM(cust) AS customs ,itmaster.price ,SUM(NETAMT) as sell  FROM DETAIL left join itmaster on detail.item_code=itmaster.item_code" ;
str=str+" where date >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and date <=#"+StrToDate(DateTimePicker2->Date.DateString())+"#    ";
 if(!suiComboBox1->Text.IsEmpty()){
str=str+" and store='"+suiComboBox1->Text.Trim()+"' ";
}
}

if(! suiDBComboBox2->Text.IsEmpty()&&suiDBComboBox2->Text.Length()==3){
str=str+" and itmaster.section_code='"+suiDBComboBox2->Text.Trim()+"'";
tempstr=str;
 if (flag==2){
  if ( suiRadioButton2->Checked)
str=str+"  group by store,article_code,itmaster.dept_code,itmaster.section_code,detail.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand  order by SUM(NETAMT) desc  " ;
else
str=str+"  group by store,article_code,itmaster.dept_code,itmaster.section_code,detail.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(QTY) 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=dm->dayseq->FieldByName("dept_code")->AsString;
 suiDBComboBox3->Text="";
 suiEdit1->Text="";

suiStatusBar1->SimpleText="  查询时间从"+DateTimePicker1->Date.DateString()+"至"+DateTimePicker2->Date.DateString()+"  库存为本店库存";;

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





void __fastcall Tday::suiDBComboBox1Change(TObject *Sender)
{
//float ctotal=0.0;
//int qtotal=0;
AnsiString str;

if (flag==1){
 str="select store, date ,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,detail.item_code,itmaster.item_desc ,QTY as quantity,cust  as customs,round((NETAMT/qty),2) as price,NETAMT as sell   FROM DETAIL left join itmaster on detail.item_code=itmaster.item_code" ;
str=str+" where qty<>0 and date >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and date <=#"+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,detail.item_code,itmaster.item_desc,stock_onhand ,SUM(QTY) AS quantity ,SUM(cust) AS customs ,itmaster.price ,SUM(NETAMT) as sell  FROM DETAIL left join itmaster on detail.item_code=itmaster.item_code" ;
str=str+" where date >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and date <=#"+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,detail.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand  order by SUM(NETAMT) desc  " ;
else
str=str+"  group by store,article_code,itmaster.dept_code,itmaster.section_code,detail.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(QTY) 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="";
//dm->dayseq->DisableControls();
//  dm->dayseq->First();
//  while( !dm->dayseq->Eof){
//  ctotal=ctotal+ dm->dayseq->FieldByName("sell")->AsFloat;
// qtotal=qtotal+dm->dayseq->FieldByName("quantity")->AsInteger;
// dm->dayseq->Next();
//  }
 //dm->dayseq->EnableControls();
 suiStatusBar1->SimpleText="  查询时间从"+DateTimePicker1->Date.DateString()+"至"+DateTimePicker2->Date.DateString()+"  库存为本店库存";;

}

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



















void __fastcall Tday::suiEdit1Change(TObject *Sender)
{

AnsiString str;
 if (flag==1){
 str="select store, date ,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,detail.item_code,itmaster.item_desc ,QTY as quantity,cust  as customs,round((NETAMT/qty),2) as price ,NETAMT as sell   FROM DETAIL left join itmaster on detail.item_code=itmaster.item_code" ;
str=str+" where qty<>0 and date >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and date <=#"+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,detail.item_code,itmaster.item_desc,stock_onhand ,SUM(QTY) AS quantity ,SUM(cust) AS customs ,itmaster.price ,SUM(NETAMT) as sell  FROM DETAIL left join itmaster on detail.item_code=itmaster.item_code" ;
str=str+" where date >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and date <=#"+StrToDate(DateTimePicker2->Date.DateString())+"#    ";
if(!suiComboBox1->Text.IsEmpty()){
str=str+" and store='"+suiComboBox1->Text.Trim()+"' ";
}
}


if(!suiEdit1->Text.IsEmpty()&suiEdit1->Text.Length()==9){
str=str+" and detail.item_code='"+suiEdit1->Text.Trim()+"'";
tempstr=str;

 if(flag==2){
  if ( suiRadioButton2->Checked)
str=str+" group by store, article_code,itmaster.dept_code,itmaster.section_code,detail.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(NETAMT) desc " ;
else
str=str+" group by store,article_code,itmaster.dept_code,itmaster.section_code,detail.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(QTY) 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=dm->dayseq->FieldByName("dept_code")->AsString;
suiDBComboBox2->Text=dm->dayseq->FieldByName("section_code")->AsString;
suiDBComboBox3->Text=dm->dayseq->FieldByName("article_code")->AsString;
}



// suiStatusBar1->SimpleText="全店铺   总金额:"+FloatToStrF(ctotal,ffCurrency,12,2)+";  总数量:"+IntToStr(qtotal)+"  查询时间从"+DateTimePicker1->Date.DateString()+"至"+DateTimePicker2->Date.DateString();

suiStatusBar1->SimpleText="  查询时间从"+DateTimePicker1->Date.DateString()+"至"+DateTimePicker2->Date.DateString()+"  库存为本店库存";;

 ;



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


void __fastcall Tday::suiDBGrid1DblClick(TObject *Sender)
{
if ( !dm->dayseq->Active)
return;
 suiEdit1->Text=dm->dayseq->FieldByName("item_code")->AsString;