//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Unit1.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "cxClasses"
#pragma link "cxControls"
#pragma link "cxCustomData"
#pragma link "cxData"
#pragma link "cxDataStorage"
#pragma link "cxDBData"
#pragma link "cxEdit"
#pragma link "cxFilter"
#pragma link "cxGraphics"
#pragma link "cxGrid"
#pragma link "cxGridCustomTableView"
#pragma link "cxGridCustomView"
#pragma link "cxGridDBTableView"
#pragma link "cxGridLevel"
#pragma link "cxGridTableView"
#pragma link "cxStyles"
#pragma resource "*.dfm"
TForm1 *Form1;
int sw = 0;

//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
	: TForm(Owner)
{
	AnsiString tDate = DateToStr(TDateTime().CurrentDate());
	DateTimePicker1->Date = tDate;

}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{


		sDate = DateTimePicker1->Date.FormatString("yyyymmdd");
		Label1->Caption = sDate;


		nDate = DateTimePicker1->Date.operator +(1).FormatString("yyyymmdd");

		if(sw == 1){
			//pGrid->selSelectRows(0,cnt-1);
			pGrid->SelectAll();
			pGrid->DeleteSelection();
		}

		sw = 1;

		Search_Query();

}
//---------------------------------------------------------------------------
void __fastcall TForm1::Search_Query()
{
	AnsiString qStr = "";

	ADOQuery1->SQL->Clear();

	qStr =  "SELECT  aaa.bus_route_no, aaa.bus_no, bbb.ox, bbb.collect_rate, aaa.b62_cnt, ";
	qStr += "aaa.c63_cnt, aaa.tot_cnt,  nvl (bbb.runcnt, '0') runcnt, ccc.COUNT " ;
	qStr += "FROM (SELECT z.bus_route_no, z.bus_no, b.cnt b62_cnt, c.cnt c63_cnt,  d.cnt tot_cnt ";
	qStr += " FROM (SELECT DISTINCT a.bus_route_no, b.bus_no, b.bus_id ";
	qStr += "FROM tm_bus_route a, tm_bus b, tm_bus_busroute c WHERE a.bus_route_id = c.bus_route_id ";
	qStr += " AND b.bus_id = c.bus_id) z, ";
	qStr += "(SELECT   a.bus_id, COUNT (*) cnt ";
	qStr += "FROM th_bus_eventinfo a WHERE a.indatetime between '"+sDate+"020000' and '"+nDate+"040000' ";
	qStr += "AND a.msgkindcode = '011' GROUP BY a.bus_id) b, ";
	qStr += "(SELECT   a.bus_id, COUNT (*) cnt ";
	qStr += " FROM th_bus_eventinfo a WHERE a.indatetime between '"+sDate+"020000' and '"+nDate+"040000'  ";
	qStr += "AND a.msgkindcode = '012' GROUP BY a.bus_id) c, ";
	qStr += "(SELECT   a.bus_id, COUNT (*) cnt  FROM th_bus_eventinfo a ";
	qStr += "WHERE a.indatetime between '"+sDate+"020000' and '"+nDate+"040000'   GROUP BY a.bus_id) d ";
	qStr += "WHERE z.bus_id = b.bus_id(+) AND z.bus_id = c.bus_id(+) AND z.bus_id = d.bus_id(+)) aaa, ";
	qStr += "(SELECT bus_route_no, bus_no, DECODE (NVL (b.bus_id, '1'), b.bus_id, 'O', ' ') ox, ";
	qStr += " ave_collect_rate collect_rate, runcnt  ";
	qStr += " FROM (SELECT DISTINCT a.bus_route_no, b.bus_no, b.bus_id, a.bus_route_id ";
	qStr += " FROM tm_bus_route a, tm_bus b, tm_bus_busroute c WHERE a.bus_route_id = c.bus_route_id ";
	qStr += " AND b.bus_id = c.bus_id) a, ";
	qStr += " (SELECT DISTINCT bus_id, lineno FROM th_bus_eventinfo ";
	qStr += " WHERE indatetime between '"+sDate+"020000' and '"+nDate+"040000' ) b, ";
	qStr += " (SELECT DISTINCT bus_id, ave_collect_rate, runcnt  FROM ts_route_busavs ";
	qStr += " WHERE sdatetime like '"+sDate+"%' ) c WHERE a.bus_id = b.bus_id(+) AND a.bus_id = c.bus_id(+) ";
	qStr += "AND a.bus_route_no = b.lineno(+)) bbb, ";
	qStr += "((SELECT   aa.bus_route_no, aa.bus_no, COUNT (bus_no) AS COUNT ";
	qStr += " FROM (SELECT DISTINCT a.bus_route_no, b.bus_no, b.bus_id ";
	qStr += " FROM tm_bus_route a, tm_bus b,  tm_bus_busroute c ";
	qStr += "WHERE a.bus_route_id = c.bus_route_id AND b.bus_id = c.bus_id) aa, ";
	qStr += "(SELECT   a.bus_id, a.runseq, COUNT (*) cnt FROM th_bus_inoutcnt a ";
	qStr += " WHERE a.indatetime between '"+sDate+"020000' and '"+nDate+"040000'  GROUP BY a.bus_id, a.runseq) bb ";
	qStr += "WHERE aa.bus_id = bb.bus_id(+) AND bb.runseq IS NOT NULL ";
	qStr += "GROUP BY bus_route_no, bus_no) ";
	qStr += "UNION ";
	qStr += "(SELECT   aa.bus_route_no, aa.bus_no, 0 AS COUNT ";
	qStr += " FROM (SELECT DISTINCT a.bus_route_no, b.bus_no, b.bus_id ";
	qStr += "  FROM tm_bus_route a, tm_bus b, tm_bus_busroute c  ";
	qStr += "WHERE a.bus_route_id = c.bus_route_id AND b.bus_id = c.bus_id) aa, ";
	qStr += "(SELECT   a.bus_id, a.runseq, COUNT (*) cnt  FROM th_bus_inoutcnt a ";
	qStr += "WHERE a.indatetime between '"+sDate+"020000' and '"+nDate+"040000'  GROUP BY a.bus_id, a.runseq) bb ";
	qStr += "WHERE aa.bus_id = bb.bus_id(+) AND bb.runseq IS NULL  GROUP BY bus_route_no, bus_no)) ccc ";
	qStr += "WHERE aaa.bus_route_no = bbb.bus_route_no ";
	qStr += " AND bbb.bus_route_no = ccc.bus_route_no ";
	qStr += "AND aaa.bus_no = bbb.bus_no ";
	qStr += "AND bbb.bus_no = ccc.bus_no ";
	qStr += "ORDER BY aaa.bus_route_no, aaa.bus_no ";

	ADOQuery1->SQL->Add(qStr);
	ADOQuery1->Open();

	Save_RouteNO();
	Save_BusNo();
	Save_Ox();
	Save_Collect_Rate();
	Save_62Event();
	Save_63Event();
	Save_Tot_Event();
	Save_Drive_Cnt();
	Modify_Collect_Rate();

}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button2Click(TObject *Sender)
{
	Close();
}
//---------------------------------------------------------------------------

void __fastcall TForm1::Button3Click(TObject *Sender)
{
	AnsiString aFName = "";
	if(SaveDialog1->Execute()){
		aFName = SaveDialog1->FileName;
		ExportGridToExcel(aFName,cxGrid1,true,true,true,"xls");

	}
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Save_RouteNO()
{
	AnsiString aItem;
	int i;
	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery1->First();

	cnt = ADOQuery1->RecordCount;

	for(i = 0; i < cnt; i++){
		pGrid->AppendRecord();

		aItem = Trim(ADOQuery1->FieldByName("BUS_ROUTE_NO")->AsString);
		pGrid->Values[i][0] = aItem;
		ADOQuery1->Next();

		}

	pGrid->EndUpdate();
}
//----------------------------------------------------------------------------
void __fastcall TForm1::Save_BusNo()
{
	AnsiString aItem;
	int i;
	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery1->First();

	for(i = 0; i < cnt; i++){

		aItem = Trim(ADOQuery1->FieldByName("BUS_NO")->AsString);
		pGrid->Values[i][1] = aItem;
		ADOQuery1->Next();

		}

	pGrid->EndUpdate();

}
//----------------------------------------------------------------------------
void __fastcall TForm1::Save_Ox()
{
	AnsiString aItem;
	int i;
	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery1->First();

	for(i = 0; i < cnt; i++){

		aItem = Trim(ADOQuery1->FieldByName("ox")->AsString);
		pGrid->Values[i][2] = aItem;
		ADOQuery1->Next();

		}

	pGrid->EndUpdate();

}
//----------------------------------------------------------------------------
void __fastcall TForm1::Save_Collect_Rate()
{
	AnsiString aItem;
	int i;
	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery1->First();

	for(i = 0; i < cnt; i++){

		aItem = Trim(ADOQuery1->FieldByName("collect_rate")->AsString);
		pGrid->Values[i][3] = aItem;
		ADOQuery1->Next();

		}

	pGrid->EndUpdate();

}
//----------------------------------------------------------------------------
void __fastcall TForm1::Save_62Event()
{
	AnsiString aItem;
	int i;
	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery1->First();

	for(i = 0; i < cnt; i++){

		aItem = Trim(ADOQuery1->FieldByName("b62_cnt")->AsString);
		pGrid->Values[i][4] = aItem;
		ADOQuery1->Next();

		}

	pGrid->EndUpdate();

}
//----------------------------------------------------------------------------
void __fastcall TForm1::Save_63Event()
{
	AnsiString aItem;
	int i;
	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery1->First();

	for(i = 0; i < cnt; i++){

		aItem = Trim(ADOQuery1->FieldByName("c63_cnt")->AsString);
		pGrid->Values[i][5] = aItem;
		ADOQuery1->Next();

		}

	pGrid->EndUpdate();

}
//----------------------------------------------------------------------------
void __fastcall TForm1::Save_Tot_Event()
{
	AnsiString aItem;
	int i;
	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery1->First();

	for(i = 0; i < cnt; i++){

		aItem = Trim(ADOQuery1->FieldByName("tot_cnt")->AsString);
		pGrid->Values[i][6] = aItem;
		ADOQuery1->Next();

		}

	pGrid->EndUpdate();

}
//----------------------------------------------------------------------------
void __fastcall TForm1::Save_Drive_Cnt()
{
	AnsiString aItem;
	int i;
	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery1->First();

	for(i = 0; i < cnt; i++){

		aItem = Trim(ADOQuery1->FieldByName("runcnt")->AsString);
		pGrid->Values[i][8] = aItem;
		ADOQuery1->Next();

	}

	pGrid->EndUpdate();

}
//----------------------------------------------------------------------------
void __fastcall TForm1::Modify_Collect_Rate()
{
	int i;
	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery1->First();

	for (i = 0; i < cnt; i++){
		if(pGrid->Values[i][3] == ""){
			if(pGrid->Values[i][2] == "O"){
				pGrid->Values[i][3] = "0";
			}
		}
	}

	pGrid->EndUpdate();


	if(CheckBox1->Checked == true){
		Period_Query();
	}
}
//----------------------------------------------------------------------------
void __fastcall TForm1::Period_Query()
{
	//

	AnsiString qStr = "";
	AnsiString aItem;
	AnsiString aBus;
	int i;

	ADOQuery2->SQL->Clear();

	qStr = "select b.BUS_ROUTE_NO, a.BUS_NO, ff.cnt from tm_bus a, tm_bus_route b, tm_bus_busroute c, ";
	qStr +="	(select f.BUS_ID, count(f.BUS_ID) cnt  from th_bus_periodicinfo f ";
	qStr +=" where f.INDATETIME between '"+sDate+"020000' and '"+nDate+"040000'  ";
	qStr +="	 group by f.BUS_ID) ff  ";
	qStr +="	where a.BUS_ID = ff.BUS_ID(+) and a.BUS_ID = c.BUS_ID and c.BUS_ROUTE_ID = b.BUS_ROUTE_ID ";
	qStr +=" order by b.BUS_ROUTE_NO, a.BUS_NO ";

	ADOQuery2->SQL->Add(qStr);
	ADOQuery2->Open();


	pGrid = cxGrid1TableView1->DataController;

	pGrid->BeginUpdate();

	ADOQuery2->First();

   cxGrid1TableView1Column9->Visible = true;

	cnt = ADOQuery1->RecordCount;

	for(i = 0; i < cnt; i++){

		aItem = Trim(ADOQuery2->FieldByName("cnt")->AsString);
		aBus = pGrid->Values[i][1];
		if(ADOQuery2->FieldByName("BUS_NO")->AsString != aBus)
		{
			pGrid->Values[i][7] = "";

		}else{
			pGrid->Values[i][7] = aItem;
			ADOQuery2->Next();
		}

	}

	pGrid->EndUpdate();


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