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