daysql.cpp

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

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

AnsiString str;
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 ( 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;
 }

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

}





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()+"  库存为本店库存";;


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














void __fastcall Tday::suiButton5Click(TObject *Sender)
{
AnsiString str;

 str="select * from tmpdetail where article_code='"+suiDBComboBox3->Text.Trim()+"'";


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

 }

 str="select * from tmpsumm where article_code='"+suiDBComboBox3->Text.Trim()+"'";

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


//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()+"  库存为本店库存";;



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



void __fastcall Tday::Button2Click(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(...){
}
 try{
 dm->tmpdetail->Active=false;
dm->tmpdetail->EmptyTable();
dm->todetail->Execute();
 }catch(...){
 }


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

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















void __fastcall Tday::Button1Click(TObject *Sender)
{
AnsiString  str1;
 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()+"' ";
}


if ( suiRadioButton2->Checked) {
 str1=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) asc  " ;
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{
 str1=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) asc ";

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 ";
     }
Button2->Visible=false;
     QueryForm->localsum->Active=false;
         QueryForm->localsum->EmptyTable();
BackgroundQuery("QueryName","aeo","admin","",str);
BackgroundQuery("QueryName1","aeo","admin","",str1);


 Button2->Visible=true;
 //flag=2;


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



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

AnsiString str;
str="select * from tmpsum";

  try{

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