daysql.cpp

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

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

#include <vcl.h>
#pragma hdrstop
#include <systdate.h>
#include "daysql.h"
#include "mainform.h"
//#include "Unit1.h"
#include "datam.h"
#include "sas.h"
#include "goods.h"
#include "monsql.h"
#include "weeksql.h"
#include "curr.h"
#include <stdio.h>
#include "seccon.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;
 tempstr="";

   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 ,round(NETAMT,2) 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="select *  FROM tmpsum where ";
//str=str+" where date >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and date <=#"+StrToDate(DateTimePicker2->Date.DateString())+"# ";
if(!suiComboBox1->Text.IsEmpty()){
str=str+" store='"+suiComboBox1->Text.Trim()+"' ";
}

}




if( !suiDBComboBox1->Text.IsEmpty()) {
str=str+" and  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  " ;
 str=str+" order by sell 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 ";
 str=str+" order by quantity desc ";
}


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

QueryForm->act->Active=false;
QueryForm->act->SQL->Clear();
QueryForm->act->SQL->Add(str);
QueryForm->act->Active=true;
 }
 catch(...){
QueryForm->act->Active=false;
 }


  }


suiDBComboBox2->Text="";
suiDBComboBox3->Text="";
suiEdit1->Text="";
 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;

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

void __fastcall Tday::suiButton2Click(TObject *Sender)
{
 if (dm->dayseq->RecordCount==0||suiEdit1->Text.IsEmpty())
     return;
Tanay *form=new Tanay(this);
form->ShowModal();
delete form;
}
//---------------------------------------------------------------------------


void __fastcall Tday::suiButton1Click(TObject *Sender)
{
  if (dm->dayseq->RecordCount==0)
     return;

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->Source=dm->dayseq;
dm->TOEXCEL->Execute() ;
}catch(...){
}
}
 }
}
//---------------------------------------------------------------------------


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

   AnsiString str ;
suiButton2->Enabled=true;

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 ,round(NETAMT,2) as sell   FROM DETAIL left join itmaster on detail.item_code=itmaster.item_code" ;

 // ="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()+"' ";
}
  tempstr=str+" and cust>0";
 try{
dm->dayseq->Active=false;
dm->dayseq->SQL->Clear();
dm->dayseq->SQL->Add(str);
dm->dayseq->Active=true;

}
catch(...){
}



flag=1;
suiStatusBar1->SimpleText="全店铺   查询时间从"+DateTimePicker1->Date.DateString()+"至"+DateTimePicker2->Date.DateString();

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