sumsql.~cpp

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

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

#include <vcl.h>
#pragma hdrstop

#include "sumsql.h"
#include "datam.h"
#include "daysql.h"
#include "mainform.h"
#include "sas.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "SUIDBCtrls"
#pragma link "SUIEdit"
#pragma link "SUIForm"
#pragma link "SUIButton"
#pragma link "SUIStatusBar"
#pragma link "SUIComboBox"
#pragma resource "*.dfm"
Tsumm *summ;
//---------------------------------------------------------------------------
__fastcall Tsumm::Tsumm(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall Tsumm::DateTimePicker1Exit(TObject *Sender)
{
 AnsiString str="select itmaster.dept_code,itmaster.section_code ,itmaster.article_code ,detail.item_code ,itmaster.item_desc AS 商品名称,itmaster.price AS 价格,sum(QTY) AS 数量,sum(cust) as 顾客数,sum(NETAMT) AS 金额  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())+"#  group by article_code,itmaster.dept_code,itmaster.section_code,detail.item_code,itmaster.item_desc,price " ;
//ShowMessage(str);
 try{
dm->dayseq->Active=false;
dm->dayseq->SQL->Clear();
dm->dayseq->SQL->Add(str);
dm->dayseq->Active=true;

}
catch(...){
}

}
//---------------------------------------------------------------------------
void  __fastcall Tsumm::suiDBComboBox3Change(TObject *Sender)
{
float ctotal=0.0;
int qtotal=0;

 
if(!suiDBComboBox3->Text.IsEmpty()&&suiDBComboBox3->Text.Length()==4){
dm->dayseq->Filtered=false;
dm->dayseq->Filter="article_code='"+suiDBComboBox3->Text.Trim()+"'";
 dm->dayseq->Filtered=true;
suiDBComboBox1->Text=dm->dayseq->FieldByName("dept_code")->AsString;
suiDBComboBox2->Text=dm->dayseq->FieldByName("section_code")->AsString;
suiEdit1->Text=""  ;
dm->dayseq->DisableControls();
  dm->dayseq->First();
  while( !dm->dayseq->Eof){
  ctotal=ctotal+ dm->dayseq->FieldByName("金额")->AsFloat;
 qtotal=qtotal+dm->dayseq->FieldByName("数量")->AsInteger;
 dm->dayseq->Next();
  }
 dm->dayseq->EnableControls();
suiStatusBar1->SimpleText="小组别:"+suiDBComboBox3->Text+";  总金额:"+FloatToStr(ctotal)+";  总数量:"+IntToStr(qtotal);


}else
dm->dayseq->Filtered=false;



}
//---------------------------------------------------------------------------
void __fastcall Tsumm::FormShow(TObject *Sender)
{
DateTimePicker1->Date=Now()-1;
DateTimePicker2->Date=Now()-1;
dm->dept->Active=false;
   dm->sect->Active=false;
   dm->artcode->Active=false;
dm->dept->Active=true;
   dm->sect->Active=true;
   dm->artcode->Active=true;

      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();
}

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

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

void __fastcall Tsumm::suiEdit1Change(TObject *Sender)
{
float ctotal=0.0;
int stotal=0;
int qtotal=0;


if(!suiEdit1->Text.IsEmpty()&suiEdit1->Text.Length()==9){
dm->dayseq->Filtered=false;
dm->dayseq->Filter="item_code='"+suiEdit1->Text.Trim()+"'";
 dm->dayseq->Filtered=true;
suiDBComboBox1->Text=dm->dayseq->FieldByName("dept_code")->AsString;
suiDBComboBox2->Text=dm->dayseq->FieldByName("section_code")->AsString;
suiDBComboBox3->Text=dm->dayseq->FieldByName("article_code")->AsString;
  dm->dayseq->DisableControls();
  dm->dayseq->First();
  while( !dm->dayseq->Eof){
  ctotal=ctotal+ dm->dayseq->FieldByName("金额")->AsFloat;
  stotal=stotal+dm->dayseq->FieldByName("顾客数")->AsInteger;
 qtotal=qtotal+dm->dayseq->FieldByName("数量")->AsInteger;
 dm->dayseq->Next();
  }
 dm->dayseq->EnableControls();
suiStatusBar1->SimpleText="ITEM_CODE:"+suiEdit1->Text+";  总金额:"+FloatToStr(ctotal)+";  总顾客数:"+IntToStr(stotal)+";  总数量:"+IntToStr(qtotal);
}else
dm->dayseq->Filtered=false;
        
}
//---------------------------------------------------------------------------

void __fastcall Tsumm::suiDBComboBox2Change(TObject *Sender)
{
float ctotal=0.0;
int qtotal=0;

if(! suiDBComboBox2->Text.IsEmpty()&&suiDBComboBox2->Text.Length()==3){
dm->dayseq->Filtered=false;
dm->dayseq->Filter="section_code='"+suiDBComboBox2->Text.Trim()+"'";
 dm->dayseq->Filtered=true;
 suiDBComboBox1->Text=dm->dayseq->FieldByName("dept_code")->AsString;
 suiDBComboBox3->Text="";
 suiEdit1->Text="";
 dm->dayseq->DisableControls();
  dm->dayseq->First();
  while( !dm->dayseq->Eof){
  ctotal=ctotal+ dm->dayseq->FieldByName("金额")->AsFloat;
 qtotal=qtotal+dm->dayseq->FieldByName("数量")->AsInteger;
 dm->dayseq->Next();
  }
 dm->dayseq->EnableControls();
suiStatusBar1->SimpleText="组别:"+suiDBComboBox2->Text+";  总金额:"+FloatToStr(ctotal)+";  总数量:"+IntToStr(qtotal);

 }else
  dm->dayseq->Filtered=false;        
}
//---------------------------------------------------------------------------

void __fastcall Tsumm::suiDBComboBox1Change(TObject *Sender)
{
 float ctotal=0.0;
int qtotal=0;

if( !suiDBComboBox1->Text.IsEmpty()) {
dm->dayseq->Filtered=false;
dm->dayseq->Filter="dept_code='"+suiDBComboBox1->Text.Trim()+"'";
 dm->dayseq->Filtered=true;
suiDBComboBox2->Text="";
suiDBComboBox3->Text="";
suiEdit1->Text="";
dm->dayseq->DisableControls();
  dm->dayseq->First();
  while( !dm->dayseq->Eof){
  ctotal=ctotal+ dm->dayseq->FieldByName("金额")->AsFloat;
 qtotal=qtotal+dm->dayseq->FieldByName("数量")->AsInteger;
 dm->dayseq->Next();
  }
 dm->dayseq->EnableControls();
 suiStatusBar1->SimpleText="部门:"+suiDBComboBox1->Text+";  总金额:"+FloatToStr(ctotal)+";  总数量:"+IntToStr(qtotal);

}
else
 dm->dayseq->Filtered=false;
       
}
//---------------------------------------------------------------------------

void __fastcall Tsumm::FormPaint(TObject *Sender)
{
 AnsiString str="select itmaster.dept_code,itmaster.section_code ,itmaster.article_code ,detail.item_code ,itmaster.item_desc AS 商品名称,itmaster.price AS 价格,sum(QTY) AS 数量,sum(cust) as 顾客数,sum(NETAMT) AS 金额  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())+"#  group by article_code,itmaster.dept_code,itmaster.section_code,detail.item_code,itmaster.item_desc,price " ;
//ShowMessage(str);
 try{
dm->dayseq->Active=false;
dm->dayseq->SQL->Clear();
dm->dayseq->SQL->Add(str);
dm->dayseq->Active=true;

}
catch(...){
}        
}
//---------------------------------------------------------------------------


void __fastcall Tsumm::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 Tsumm::suiButton1Click(TObject *Sender)
{
 Tanay *form=new Tanay(this);
form->ShowModal();
delete form;       
}
//---------------------------------------------------------------------------