?? daysql.cpp
字號:
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;
}
}
//---------------------------------------------------------------------------
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -