weekquery.~cpp

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

void __fastcall Tsumm::suiDBComboBox2DblClick(TObject *Sender)
{
AnsiString str;
if (flag==1){
 str="select store,weeklastd ,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,sellweek.item_code,itmaster.item_desc ,quantity, customs,round((SELL/customs),2) as price , sell   FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where quantity<>0 and weeklastd >=# "+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+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,sellweek.item_code,itmaster.item_desc,stock_onhand ,SUM(quantity) AS quantities ,SUM(customs) AS allcustoms ,itmaster.price ,SUM(SELL) as total  FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where weeklastd >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+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,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand  order by SUM(SELL) desc  " ;
else
str=str+"  group by store,article_code,itmaster.dept_code,itmaster.section_code,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(quantity) 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 Tsumm::suiDBComboBox3DblClick(TObject *Sender)
{
 AnsiString str;
if (flag==1){
 str="select store,weeklastd ,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,sellweek.item_code,itmaster.item_desc ,quantity, customs,round((SELL/customs),2) as price , sell   FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where quantity<>0 and weeklastd >=# "+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+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,sellweek.item_code,itmaster.item_desc,stock_onhand ,SUM(quantity) AS quantities ,SUM(customs) AS allcustoms ,itmaster.price ,SUM(SELL) as total  FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where weeklastd >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+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,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand  order by SUM(SELL) desc  " ;
else
str=str+"  group by store,article_code,itmaster.dept_code,itmaster.section_code,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(quantity) desc ";

}

  ShowMessage(str);

 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 Tsumm::suiEdit1DblClick(TObject *Sender)
{
  AnsiString str;
if (flag==1){
 str="select store,weeklastd ,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,sellweek.item_code,itmaster.item_desc ,quantity, customs,round((SELL/customs),2) as price , sell   FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where quantity<>0 and weeklastd >=# "+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+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,sellweek.item_code,itmaster.item_desc,stock_onhand ,SUM(quantity) AS quantities ,SUM(customs) AS allcustoms ,itmaster.price ,SUM(SELL) as total  FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where weeklastd >=#"+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+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 sellweek.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,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(SELL) desc " ;
else
str=str+" group by store,article_code,itmaster.dept_code,itmaster.section_code,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand order by SUM(quantity) 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 Tsumm::suiButton4Click(TObject *Sender)
{
 suiButton2->Enabled=false;

 AnsiString str="select store,itmaster.dept_code,itmaster.section_code,itmaster.article_code,itmaster.vendor_code,sellweek.item_code,itmaster.item_desc,stock_onhand ,SUM(quantity) AS quantities ,SUM(customs) AS allcustoms ,itmaster.price ,SUM(SELL) as total  FROM sellweek left join itmaster on sellweek.item_code=itmaster.item_code" ;
str=str+" where weeklastd >=# "+StrToDate(DateTimePicker1->Date.DateString())+"# and weeklastd <=#"+StrToDate(DateTimePicker2->Date.DateString())+"#  ";


str=str+" group by store,article_code,itmaster.dept_code,itmaster.section_code,sellweek.item_code,itmaster.vendor_code,itmaster.item_desc,price,stock_onhand  ";
if ( suiRadioButton2->Checked)
str=str+" order by SUM(SELL) desc " ;
else
str=str+" order by SUM(quantity) desc ";
//ShowMessage(str);
 try{
dm->dayseq->Active=false;
dm->dayseq->SQL->Clear();
dm->dayseq->SQL->Add(str);
dm->dayseq->Active=true;

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



void __fastcall Tsumm::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 month(weeklastd)="+(suiListBox1->TopIndex+1)+" order by weeklastd";//+suiListBox1->TopIndex;

     } else{
  tstr=Trim(tempstr)+" and month(weeklastd)="+(suiListBox1->TopIndex+1)+" order by weeklastd";

 }

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

   if (!suiEdit1->Text.IsEmpty()){
  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);
}
}
//---------------------------------------------------------------------------