monsqla.cpp

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

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

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 ,NETAMT 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();

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

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


suiButton2->Enabled=false;

 AnsiString 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()+"' ";
}

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  ";
if ( suiRadioButton2->Checked)
str=str+" order by SUM(NETAMT) desc " ;
else
str=str+" order by SUM(QTY) desc ";
//ShowMessage(str);
 try{
dm->dayseq->Active=false;
dm->dayseq->SQL->Clear();
dm->dayseq->SQL->Add(str);
dm->dayseq->Active=true;

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


void __fastcall Tday::DateTimePicker1Change(TObject *Sender)
{
  suiStatusBar1->SimpleText="查询时间从"+DateTimePicker1->Date.DateString()+"至"+DateTimePicker2->Date.DateString();
             
}
//---------------------------------------------------------------------------

void __fastcall Tday::FormDestroy(TObject *Sender)
{
 dm->dayseq->Active=false;        
}
//---------------------------------------------------------------------------










void __fastcall Tday::suiComboBox1Change(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( !suiComboBox1->Text.IsEmpty()) {
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="";
suiDBComboBox2->Text="";
suiDBComboBox3->Text="";
suiEdit1->Text="";

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

}




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




void __fastcall Tday::suiListBox1DblClick(TObject *Sender)
{
AnsiString tstr;
 float ctotal=0.0;
 int qtotal=0;
 int stotal=0;
  if(!dm->dayseq->Active||flag!=1||!suiCheckBox1->Checked){
  return;
  }
 if (Trim(tempstr).IsEmpty()){

 tempstr=AnsiString(dm->dayseq->SQL->Text);
  tstr=dm->dayseq->SQL->Text+" and weekday(date)=CInt('"+Trim(IntToStr(suiListBox1->TopIndex+1))+"')" +" order by date";//+suiListBox1->TopIndex;

     } else{
  tstr=Trim(tempstr)+" and weekday(date)=CInt('"+Trim(IntToStr(suiListBox1->TopIndex+1))+"')"+" order by date";

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


  dm->dayseq->DisableControls();
  dm->dayseq->First();
 while( !dm->dayseq->Eof){
  ctotal=ctotal+ dm->dayseq->FieldByName("sell")->AsFloat;
 qtotal=qtotal+dm->dayseq->FieldByName("quantity")->AsInteger;
  stotal=stotal+dm->dayseq->FieldByName("Customs")->AsInteger;
dm->dayseq->Next();
  }
dm->dayseq->EnableControls();

 suiStatusBar1->SimpleText="该项目汇总结果 "+suiListBox1->Items->operator [](suiListBox1->TopIndex)+"销售数量:"+IntToStr(qtotal)+" 销售客数为"+IntToStr(stotal)+" 销售金额:"+FormatFloat("0.0",ctotal);


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






void __fastcall Tday::suiDBComboBox3Click(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(!suiDBComboBox3->Text.IsEmpty()&&suiDBComboBox3->Text.Length()==4){
str=str+ " and itmaster.article_code='"+suiDBComboBox3->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;
suiEdit1->Text=""  ;

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


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