// RPTS.cpp : implementation file
//

#include "stdafx.h"
#include "sp.h"
#include "RPTS.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif
//memory leak debugging help
#define _CRTDBG_MAP_ALLOC
#include <stdlib.h>
#include <crtdbg.h>
#include ".\rpts.h"
/////////////////////////////////////////////////////////////////////////////
// RPTS

IMPLEMENT_DYNCREATE(RPTS, CFormView)

RPTS::RPTS()
: CFormView(RPTS::IDD)
, m_bShowShortdateWarning(false)
{
	//{{AFX_DATA_INIT(RPTS)
	// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
	m_pApp= (CSpApp*)AfxGetApp();

	m_bListByDescription=true;
	m_pApp->m_bRefreshReportsScreen=true;
}

RPTS::~RPTS()
{
	//DeActivate();
}

void RPTS::DoDataExchange(CDataExchange* pDX)
{
	CFormView::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(RPTS)

	DDX_Control(pDX, IDC_BTNSQL, m_btnSQL);
	DDX_Control(pDX, IDC_BTNFIELDS, m_btnFields);
	DDX_Control(pDX, IDC_BTNTTX, m_btnTTX);
	DDX_Control(pDX, IDC_LBLBYMODEL, m_lblByModel);
	DDX_Control(pDX, IDC_LBLBYDESC, m_lblByDesc);
	DDX_Control(pDX, IDC_LBLMODEL, m_lblModel);
	DDX_Control(pDX, IDC_LBLMANUFACTURER, m_lblManufacturer);
	DDX_Control(pDX, IDC_CBMODEL, m_cbModel);
	DDX_Control(pDX, IDC_CBMANUFACTURER, m_cbManufacturer);
	DDX_Control(pDX, IDC_LBLZONE, m_lblZone);
	DDX_Control(pDX, IDC_CBZONE, m_cbZone);
	DDX_Control(pDX, IDC_LBLUNIT, m_lblUnit);
	DDX_Control(pDX, IDC_LBLTECH, m_lblTech);
	DDX_Control(pDX, IDC_LBLRATE, m_lblRate);
	DDX_Control(pDX, IDC_LBLPROJECT, m_lblProject);
	DDX_Control(pDX, IDC_LBLPART, m_lblPart);
	DDX_Control(pDX, IDC_LBLNONCLIENT, m_lblNonClient);
	DDX_Control(pDX, IDC_LBLDATERANGE, m_lblDateRange);
	DDX_Control(pDX, IDC_LBLCLIENT, m_lblClient);
	DDX_Control(pDX, IDC_LBLCATEGORY, m_lblCategory);
	DDX_Control(pDX, IDC_LBLBETWEEN, m_lblBetween);
	DDX_Control(pDX, IDC_LBLAND, m_lblAnd);
	DDX_Control(pDX, IDC_DTSTART, m_dtStart);
	DDX_Control(pDX, IDC_DTEND, m_dtEnd);
	DDX_Control(pDX, IDC_CBUNIT, m_cbUnit);
	DDX_Control(pDX, IDC_CBTECH, m_cbTech);
	DDX_Control(pDX, IDC_CBREPORT, m_cbReport);
	DDX_Control(pDX, IDC_CBRATE, m_cbRate);
	DDX_Control(pDX, IDC_CBPROJECT, m_cbProject);
	DDX_Control(pDX, IDC_CBPART, m_cbPart);
	DDX_Control(pDX, IDC_CBNONCLIENT, m_cbNonClient);
	DDX_Control(pDX, IDC_CBDATERANGE, m_cbDateRange);
	DDX_Control(pDX, IDC_CBCLIENT, m_cbClient);
	DDX_Control(pDX, IDC_CBCATEGORY, m_cbCategory);
	DDX_Control(pDX, IDC_BTNREPORT, m_btnReport);
	//}}AFX_DATA_MAP

}


BEGIN_MESSAGE_MAP(RPTS, CFormView)
	//{{AFX_MSG_MAP(RPTS)
	ON_BN_CLICKED(IDC_LBLBYMODEL, OnLblbymodel)
	ON_BN_CLICKED(IDC_LBLBYDESC, OnLblbydesc)
	ON_CBN_CLOSEUP(IDC_CBREPORT, OnCloseupCbreport)
	ON_BN_CLICKED(IDC_BTNREPORT, OnBtnreport)
	ON_CBN_CLOSEUP(IDC_CBDATERANGE, OnCloseupCbdaterange)
	ON_NOTIFY(DTN_DATETIMECHANGE, IDC_DTEND, OnDatetimechangeDtend)
	ON_NOTIFY(DTN_DATETIMECHANGE, IDC_DTSTART, OnDatetimechangeDtstart)
	ON_BN_CLICKED(IDC_BTNTTX, OnBtnttx)
	ON_BN_CLICKED(IDC_BTNFIELDS, OnBtnfields)
	ON_BN_CLICKED(IDC_BTNREFRESH, OnBtnrefresh)
	ON_BN_CLICKED(IDC_BTNSQL, OnBtnsql)
	//}}AFX_MSG_MAP
	ON_WM_HELPINFO()
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// RPTS diagnostics

#ifdef _DEBUG
void RPTS::AssertValid() const
{
	CFormView::AssertValid();
}

void RPTS::Dump(CDumpContext& dc) const
{
	CFormView::Dump(dc);
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// RPTS message handlers

void RPTS::Activate()
{
	/*
	if(rs==NULL)
	{	//open with client side cursor due to crystal
	//reports bug resulting in blank memo fields
	//when using a server side cursor
	rs=new GZRset("Error in Reports RS:",true);
	rs->SetConnect(m_pApp->strConnectString);
	}
	*/
	//Initialize recordset pointer

	//rsPRINT:
	CString strTemp;
	rs=m_pApp->rsPool->GetRSPrint("RPTS");

	FillLists(false);

	char chBuff[50];
	memset(chBuff,0,sizeof(int));


	//short date format
	if(m_bShowShortdateWarning)//to avoid showing on program startup
	{
		if(GetLocaleInfo(LOCALE_USER_DEFAULT,LOCALE_ICENTURY ,chBuff,50))
		{
			strTemp.Format("%s",chBuff);
			if(strTemp=="0")//not 4 digit century
			{
				m_pApp->ShowStuff(

					"WARNING: Reports that use date ranges may not print what you expect.\r\n\r\n"

					"Windows on this computer is incorrectly set to display 2 digit centuries\r\n"
					"instead of full 4 digit centuries as required for Y2K readiness.\r\n\r\n"

					"This reports screen uses the years 1900 and 2500 to indicate all dates\r\n"
					"should be used when you select <Any date> which will appear incorrectly\r\n"
					"to both be the year 2000 because only the last two digits of the century\r\n"
					"are displayed on this computer due to the Windows short date format\r\n"
					"display setting.\r\n\r\n"

					"To make this message go away and avoid potential problems with not only \r\n"
					"reports in AyaNova but other post-y2k applications used on this computer, \r\n"
					"set Windows to use 4 digit centuries in the Windows Control panel's \r\n"
					"Regional Settings Short Date format as described in the \r\n\r\n"
					"\"Getting started -> Installation and configuration\"\r\n\r\n"
					"section of the AyaNova in-program help");


			}


		}
	}
	else
		m_bShowShortdateWarning=true;//Potentially show, not guranteed to show (if their settings are OK for example


}

void RPTS::DeActivate()
{
	//switch focus in case a combo has it
	//this way the switch wont fire a close up
	//and error on a nonvalid recordset
	m_btnReport.SetFocus();
	if(rs!=NULL)
	{	
		m_pApp->rsPool->ReleaseRS(&rs->m_nID);
		rs=NULL;
	}	
}

void RPTS::FillLists(bool bFillModelsOnly)
{

	if(!m_pApp->m_bRefreshReportsScreen)
		return;
	m_pApp->m_bRefreshReportsScreen=false;
	//Fill clients
	CString strData, strIndex, q;
	long lData;



	//Fillmodels
	m_cbModel.Clear();
	m_cbModel.AddRow(" <All models>","0");
	if(m_bListByDescription)
	{
		rs->QueryReadOnly("SELECT [company_person] & \" \" & [description] & \" \" & [model] AS item, unitmodels.id "
			"FROM unitmodels LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id "
			"ORDER BY [company_person] & \" \" & [description] & \" \" & [model];");

	}
	else
	{
		rs->QueryReadOnly("SELECT [model] & \" \" & [company_person] & \" \" & [description] AS item, unitmodels.id "
			"FROM unitmodels LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id "
			"ORDER BY [company_person] & \" \" & [description] & \" \" & [model];");

	}
	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("item",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbModel.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}

	m_cbModel.SetCurSel(0);



	//this function doubles as a fill model list
	//only option since user can select how to display
	//the model list by # or full description
	if(bFillModelsOnly==true) return;
bool bHO=false;
	m_cbClient.Clear();
	m_cbClient.AddRow(" < All Clients >","0");
	q.Format("SELECT clients.id, clients.company AS compname, isheadoffice FROM clients  "
		"ORDER BY clients.company;");
	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		do
		{
			rs->FetchField("isheadoffice",&bHO);
			
			
			VERIFY(rs->FetchField("compname",&strData));
			if(bHO)
				strData=strData+" (head office)";
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbClient.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}

	m_cbClient.SetCurSel(0);


	//fill techs
	m_cbTech.Clear();
	m_cbTech.AddRow(" < All Techs >","0");
	q="SELECT users.id, [last] & \", \" & [first] AS fullname FROM users WHERE (((users.tech)=True)) ORDER BY users.last;";
	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("fullname",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbTech.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}
	m_cbTech.SetCurSel(0);



	//fill projects
	m_cbProject.Clear();
	m_cbProject.AddRow(" < All Projects >","0");
	q="SELECT projects.id, projects.name FROM projects ORDER BY projects.name;";
	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("name",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbProject.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}

	m_cbProject.Select(0);



	//fill categories
	m_cbCategory.Clear();
	m_cbCategory.AddRow(" < All Categories >","0");
	q="SELECT wotypes.id, wotypes.category FROM wotypes ORDER BY wotypes.category;";
	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("category",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbCategory.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}
	m_cbCategory.SetCurSel(0);




	//fill units
	m_cbUnit.Clear();
	m_cbUnit.AddRow(" < All Units >","0");
	q="SELECT units.id, [sn] & \" - \" & [company_person] & \" \" & [unitmodels].[description] & \" \" & [unitmodels].[model] AS name "
		"FROM (units LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id;";
	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("name",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbUnit.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}
	m_cbUnit.SetCurSel(0);


	//fill parts
	m_cbPart.Clear();
	m_cbPart.AddRow(" < All Parts >","0");
	q="SELECT parts.id, [partnumber] & \" - \" & [description] AS name FROM parts "
		"ORDER BY [partnumber] & \" - \" & [description];";
	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("name",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbPart.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}
	m_cbPart.SetCurSel(0);



	//fill rates
	m_cbRate.Clear();
	m_cbRate.AddRow(" < All Rates >","0");
	q="SELECT rates.id, rates.name FROM rates ORDER BY rates.name;";

	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("name",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbRate.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}
	m_cbRate.SetCurSel(0);


	//fill daterange
	//instead of using date range inquery, this will
	//set the appropriate dates in the date boxes
	m_cbDateRange.Clear();
	m_cbDateRange.AddRow(" <Any date>","0");
	m_cbDateRange.AddRow("Between dates...","1");
	m_cbDateRange.AddRow("This month","2");
	m_cbDateRange.AddRow("Last month","3");
	m_cbDateRange.AddRow("Last 6 months","4");
	m_cbDateRange.AddRow("Last 12 months","5");
	m_cbDateRange.AddRow("This year","6");
	m_cbDateRange.AddRow("Last year","7");
	m_cbDateRange.SetCurSel(0);



	//FillManufacturers
	m_cbManufacturer.Clear();
	m_cbManufacturer.AddRow(" < All Manufacturers >","0");
	rs->QueryReadOnly("SELECT nonclients.* "
		"FROM nonclients "
		"WHERE (((nonclients.type)=3)) "
		"ORDER BY nonclients.company_person;");

	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("company_person",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbManufacturer.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}
	m_cbManufacturer.SetCurSel(0);

	//fill ZONES
	m_cbZone.Clear();
	m_cbZone.AddRow(" < All Zones >","0");
	q="SELECT zones.id, zones.name FROM zones ORDER BY zones.name;";

	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("name",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbZone.AddRow(strData,strIndex);
		}while(rs->MoveForward());

	}
	m_cbZone.SetCurSel(0);

	//fill REPORTS
	m_cbReport.Clear();
	q=	"SELECT rptsmaster.virtualname, rptsmaster.id "
		"FROM rptsmaster "
		"WHERE (((rptsmaster.x)=True)) "
		"ORDER BY rptsmaster.virtualname;";

	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		do
		{
			VERIFY(rs->FetchField("virtualname",&strData));
			VERIFY(rs->FetchField("id",&lData));
			strIndex.Format("%u",lData);
			m_cbReport.AddRow(strData,strIndex);
		}while(rs->MoveForward());
		m_cbReport.SetCurSel(0);
	}

	OnCloseupCbdaterange();
	OnCloseupCbreport();

	m_cbReport.SetFocus();

}












void RPTS::ActivateCriteria()
{
	CString q;
	long lValue;	

	//FETCH criteria here based on id
	q.Format("SELECT rptsmaster.criteriafields, rptsmaster.query, rptsmaster.filename "
		"FROM rptsmaster "
		"WHERE (((rptsmaster.id)=%s));",m_cbReport.GetCurrentRowID());

	rs->QueryReadOnly(q);
	rs->FetchField("query",&m_strRawSQL);
	rs->FetchField("criteriafields",&lValue);
	rs->FetchField("filename",&m_strReportFileName);

	m_cbZone.EnableWindow(FALSE);
	m_lblZone.EnableWindow(FALSE);
	m_cbDateRange.EnableWindow(FALSE);
	m_lblDateRange.EnableWindow(FALSE);
	m_lblBetween.EnableWindow(FALSE);
	m_lblAnd.EnableWindow(FALSE);
	m_dtStart.EnableWindow(FALSE);
	m_dtEnd.EnableWindow(FALSE);
	m_cbNonClient.EnableWindow(FALSE);
	m_lblNonClient.EnableWindow(FALSE);
	m_cbRate.EnableWindow(FALSE);
	m_lblRate.EnableWindow(FALSE);
	m_cbPart.EnableWindow(FALSE);
	m_lblPart.EnableWindow(FALSE);
	m_cbUnit.EnableWindow(FALSE);
	m_lblUnit.EnableWindow(FALSE);
	m_cbCategory.EnableWindow(FALSE);
	m_lblCategory.EnableWindow(FALSE);
	m_cbProject.EnableWindow(FALSE);
	m_lblProject.EnableWindow(FALSE);
	m_cbClient.EnableWindow(FALSE);
	m_lblClient.EnableWindow(FALSE);
	m_cbTech.EnableWindow(FALSE);
	m_lblTech.EnableWindow(FALSE);

	m_cbModel.EnableWindow(FALSE);
	m_lblModel.EnableWindow(FALSE);
	m_cbManufacturer.EnableWindow(FALSE);
	m_lblManufacturer.EnableWindow(FALSE);

	if(lValue & TECH)
	{
		m_cbTech.EnableWindow(TRUE);
		m_lblTech.EnableWindow(TRUE);
	}

	if(lValue & CLIENT)
	{
		m_cbClient.EnableWindow(TRUE);
		m_lblClient.EnableWindow(TRUE);
	}

	if(lValue & PROJECT)
	{
		m_cbProject.EnableWindow(TRUE);
		m_lblProject.EnableWindow(TRUE);
	}

	if(lValue & CATEGORY)
	{
		m_cbCategory.EnableWindow(TRUE);
		m_lblCategory.EnableWindow(TRUE);
	}

	if(lValue & UNIT)
	{
		m_cbUnit.EnableWindow(TRUE);
		m_lblUnit.EnableWindow(TRUE);
	}

	if(lValue & PART)
	{
		m_cbPart.EnableWindow(TRUE);
		m_lblPart.EnableWindow(TRUE);
	}

	if(lValue & RATE)
	{
		m_cbRate.EnableWindow(TRUE);
		m_lblRate.EnableWindow(TRUE);
	}

	if(lValue & NONCLIENT)
	{
		m_cbNonClient.EnableWindow(TRUE);
		m_lblNonClient.EnableWindow(TRUE);
	}
	if(lValue & DATE)
	{
		m_cbDateRange.EnableWindow(TRUE);
		m_lblDateRange.EnableWindow(TRUE);
		m_lblBetween.EnableWindow(TRUE);
		m_lblAnd.EnableWindow(TRUE);
		m_dtStart.EnableWindow(TRUE);
		m_dtEnd.EnableWindow(TRUE);
	}

	if(lValue & ZONE)
	{
		m_cbZone.EnableWindow(TRUE);
		m_lblZone.EnableWindow(TRUE);
	}

	if(lValue & MODEL)
	{
		m_cbModel.EnableWindow(TRUE);
		m_lblModel.EnableWindow(TRUE);
	}


	if(lValue & MANUFACTURER)
	{
		m_cbManufacturer.EnableWindow(TRUE);
		m_lblManufacturer.EnableWindow(TRUE);
	}


}







void RPTS::OnLblbymodel() 
{
	if(m_bListByDescription==false)
		return;
	m_bListByDescription=false;
	m_pApp->m_bRefreshReportsScreen=true;
	FillLists(true);
	m_lblByDesc.SetTextColor(RGB(0,0,255));
	m_lblByModel.SetTextColor(RGB(0,0,0));
	m_lblByDesc.SetFontUnderline(TRUE);
	m_lblByModel.SetFontUnderline(FALSE);


}

void RPTS::OnLblbydesc() 
{
	if(m_bListByDescription==true)
		return;
	m_bListByDescription=true;
	m_pApp->m_bRefreshReportsScreen=true;
	FillLists(true);
	m_lblByDesc.SetTextColor(RGB(0,0,0));
	m_lblByModel.SetTextColor(RGB(0,0,255));
	m_lblByDesc.SetFontUnderline(FALSE);
	m_lblByModel.SetFontUnderline(TRUE);


}

void RPTS::OnInitialUpdate() 
{
	CFormView::OnInitialUpdate();

	//	m_strChosenReport.Empty();

	m_lblByDesc.SetTextColor(RGB(0,0,0));
	m_lblByDesc.SetFontUnderline(FALSE);
	m_lblByDesc.SetLink(TRUE);
	m_lblByDesc.SetLinkCursor(m_pApp->hcHand);
	m_lblByDesc.SetLinkURL("");

	m_lblByModel.SetTextColor(RGB(0,0,255));
	m_lblByModel.SetFontUnderline(TRUE);
	m_lblByModel.SetLink(TRUE);
	m_lblByModel.SetLinkCursor(m_pApp->hcHand);
	m_lblByModel.SetLinkURL("");




	if(m_pApp->m_lusrID==1) 
	{
		m_btnTTX.ShowWindow(TRUE);
		m_btnFields.ShowWindow(TRUE);
		m_btnSQL.ShowWindow(TRUE);
	}



	// set initial date to match <anytime> default in date 
	//range combo
	COleDateTime dtData(1900, 1, 1, 0, 0, 0);
	m_dtStart.SetTime(dtData);
	dtData.SetDateTime(2500,1,1,0,0,0);
	m_dtEnd.SetTime(dtData);

	Activate();
	DeActivate();

}

void RPTS::OnCloseupCbreport() 
{
	/*CString strRpt;
	strRpt=m_cbReport.GetCurrentRowID();
	if(strRpt==m_strChosenReport) return;
	else
	m_strChosenReport=strRpt;
	*/

	ActivateCriteria();

}

void RPTS::BuildCriteria()
{
	CString strData;
	long lData=0;



	if(m_cbClient.GetCurrentRowID()!="0")
		lData=lData | CLIENT;
	if(m_cbTech.GetCurrentRowID()!="0")
		lData=lData | TECH;
	if(m_cbProject.GetCurrentRowID()!="0")
		lData=lData | PROJECT;
	if(m_cbCategory.GetCurrentRowID()!="0")
		lData=lData | CATEGORY;
	if(m_cbUnit.GetCurrentRowID()!="0")
		lData=lData | UNIT;
	if(m_cbPart.GetCurrentRowID()!="0")
		lData=lData | PART;
	if(m_cbRate.GetCurrentRowID()!="0")
		lData=lData | RATE;
	if(m_cbNonClient.GetCurrentRowID()!="0")
		lData=lData | NONCLIENT;
	if(m_cbDateRange.GetCurrentRowID()!="0")
		lData=lData | DATE;
	if(m_cbZone.GetCurrentRowID()!="0")
		lData=lData | ZONE;
	if(m_cbManufacturer.GetCurrentRowID()!="0")
		lData=lData | MANUFACTURER;
	if(m_cbModel.GetCurrentRowID()!="0")
		lData=lData | MODEL;
	strData.Format("Criteria: %u",lData);
	AfxMessageBox(strData);
}

void RPTS::OnBtnreport() 
{
	//	BuildCriteria();
	CWaitCursor cw;
	CString q;
	long lReport=atol(m_cbReport.GetCurrentRowID());
	if(lReport==0) return;
	//Setup sql for selected report then open it
	BuildSql();//substitute criteria tokens in query
	cw.Restore();
#ifdef _DEBUG
	//m_pApp->ShowStuff(m_strSQL);

#endif
	//open report
	rs->QueryReadOnly(m_strSQL);
	if(rs->IsEmpty())
	{
		if(m_strSQL.Find("woparts")!=-1 || m_strSQL.Find("hours")!=-1)
			AfxMessageBox("There is no data to report.\r\n\r\n"
			"If you are trying to print a report that \r\n"
			"contains work order information\r\n"
			"such as parts or labor you will get this error if\r\n"
			"there are no work orders within your criteria choices\r\n"
			"that have parts or labor charges on them.");
		else
			AfxMessageBox("There is no data that matches\r\n"
			"your criteria choices");
	}
	else
	{
		//AfxMessageBox(m_strSQL);
		m_pApp->PrintCMReportRDC(m_cbReport.GetCurrentRowText(),rs->RecordSetPointer());
		//m_pApp->CreateTTX("gzmonthly.ttx",rs->RecordSetPointer());


	}

	cw.Restore();



}


/*
#define CLIENT			1	CUST
#define TECH			2	TECH
#define PROJECT			4	PROJ
#define CATEGORY		8	CAT_

#define UNIT			16	UNIT
#define PART			32	PART
#define RATE			64	RATE
#define NONCLIENT		128

#define DATE			256	STRT & END_
#define ZONE			512	ZONE

#define MANUFACTURER	1024	MANU
#define MODEL			2048	MODL
Additional criteria:
CUSTNAME
TECHNAME
PROJNAME
CAT_NAME
UNITNAME
PARTNAME
RATENAME
ZONENAME
MANUNAME
MODLNAME
USERNAME
DATERANGE
CRITERIA


*/

void RPTS::BuildSql()
{

	//Added June 24th 2003 to automatically 
	//morph query to work with head office if one selected
	//also sets m_strProcessedSQL to contents of m_strRawSQL
	//so can not be removed as rest now relies on m_strProcessedSQL
	SubHeadOffice();

	CString strData,token,txt,strSubString, strTemp;

	COleDateTime dtData;
	int len=m_strProcessedSQL.GetLength();
	int x=0;
	int nEndOfToken,nCharsToFetch;

	m_strSQL.Empty();
	//loop through m_strSQL
	for(x=0;x<len;x++)
	{
		txt=m_strProcessedSQL.GetAt(x);
		if(txt=="~")
		{
			nEndOfToken=0;
			x++;

			//determine how large a substring to pull out
			if((len-x)<20)
				nCharsToFetch=len-x;
			else
				nCharsToFetch=20;

			//pull out a substring to search for the delimiter
			strSubString=m_strProcessedSQL.Mid(x,nCharsToFetch);

			//find the first breaking character
			nEndOfToken=strSubString.FindOneOf("#) ");
			ASSERT(nEndOfToken!=-1);
			if(nEndOfToken==-1)
				nEndOfToken=5;



			token=m_strProcessedSQL.Mid(x,nEndOfToken);
			x+=token.GetLength()-1;


			//Substitutions:

			//TECH
			if(token=="TECH")
			{
				strData=m_cbTech.GetCurrentRowID();
				if(strData=="0")//change v1.9.4.4 -1 is not scheduled so needs to be -2 to completely avoid the problem
					m_strSQL+="<>-2";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}


			//CLIENT
			if(token=="CUST")
			{

				strData=m_cbClient.GetCurrentRowID();
				//Normal processing to replace CUST token
				if(strData=="0")
					m_strSQL+="<>-1";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}

			//PROJECT
			if(token=="PROJ")
			{
				strData=m_cbProject.GetCurrentRowID();
				if(strData=="0")
					m_strSQL+="<>-1";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}

			//CATEGORY
			if(token=="CAT_")
			{
				strData=m_cbCategory.GetCurrentRowID();
				if(strData=="0")
					m_strSQL+="<>-1";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}

			//UNIT
			if(token=="UNIT")
			{
				strData=m_cbUnit.GetCurrentRowID();
				if(strData=="0")
					m_strSQL+="<>-1";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}


			//PART
			if(token=="PART")
			{
				strData=m_cbPart.GetCurrentRowID();
				if(strData=="0")
					m_strSQL+="<>-1";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}

			//RATE
			if(token=="RATE")
			{
				strData=m_cbRate.GetCurrentRowID();
				if(strData=="0")
					m_strSQL+="<>-1";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}


			//SDATE
			if(token=="STRT")
			{

				m_dtStart.GetTime(dtData);
				//strData=dtData.Format(_T("%m/%d/%Y"));
				strData=dtData.Format(_T("%m/%d/%Y 12:00:00 AM"));
				m_strSQL+=strData;


				goto DONE;
			}

			//END DATE
			if(token=="END_")
			{

				m_dtEnd.GetTime(dtData);
				//strData=dtData.Format(_T("%m/%d/%Y"));
				strData=dtData.Format(_T("%m/%d/%Y 11:59:59 PM"));
				m_strSQL+=strData;


				goto DONE;
			}


			//ZONE
			if(token=="ZONE")
			{
				strData=m_cbZone.GetCurrentRowID();
				if(strData=="0")
					m_strSQL+="<>-1";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}

			//MANU Facturer
			if(token=="MANU")
			{
				strData=m_cbManufacturer.GetCurrentRowID();
				if(strData=="0")
					m_strSQL+="<>-1";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}


			//MODEL
			if(token=="MODL")
			{
				strData=m_cbModel.GetCurrentRowID();
				if(strData=="0")
					m_strSQL+="<>-1";//meaning ignore
				else
					m_strSQL+="="+strData;

				goto DONE;
			}


			//NAME TOKENS
			//CUSTNAME
			if(token=="CUSTNAME")
			{

				m_cbClient.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";


				goto DONE;
			}


			//TECHNAME
			if(token=="TECHNAME")
			{

				m_cbTech.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;


				goto DONE;
			}

			//PROJNAME
			if(token=="PROJNAME")
			{

				m_cbProject.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;


				goto DONE;
			}

			//CAT_NAME
			if(token=="CAT_NAME")
			{

				m_cbCategory.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;


				goto DONE;
			}

			//UNITNAME
			if(token=="UNITNAME")
			{

				m_cbUnit.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;


				goto DONE;
			}

			//PARTNAME
			if(token=="PARTNAME")
			{

				m_cbPart.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;


				goto DONE;
			}

			//RATENAME
			if(token=="RATENAME")
			{

				m_cbRate.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;


				goto DONE;
			}

			//ZONENAME
			if(token=="ZONENAME")
			{

				m_cbZone.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;


				goto DONE;
			}

			//MANUNAME
			if(token=="MANUNAME")
			{

				m_cbManufacturer.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;
				goto DONE;
			}

			//MODLNAME
			if(token=="MODLNAME")
			{

				m_cbModel.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;
				goto DONE;
			}

			//USERNAME
			if(token=="USERNAME")
			{

				strData=m_pApp->m_strCurrentUserName;
				m_strSQL+="\""+strData+"\"";;


				goto DONE;
			}

			//DATERANGE
			if(token=="DATERANGE")
			{

				m_cbDateRange.GetWindowText(strData);
				m_strSQL+="\""+strData+"\"";;


				goto DONE;
			}

			//CRITERIA
			//added 08/03/2001 makes a descriptive string of all criteria
			//selected
			if(token=="CRITERIA")
			{

				//go through all controls and build a descriptive string
				strData.Empty();

				//dates
				if(m_cbDateRange.IsWindowEnabled())
				{
					//DATE
					m_cbDateRange.GetWindowText(strTemp);
					if(strTemp.Find("Any")==-1)
					{
						strData+="<Between ";
						m_dtStart.GetWindowText(strTemp);
						strData+=strTemp+" and ";

						m_dtEnd.GetWindowText(strTemp);
						strData+=strTemp+"> ";

					}
					else
						strData += strTemp + " ";
				}
				//client
				if(m_cbClient.IsWindowEnabled())
				{
					m_cbClient.GetWindowText(strTemp);
					if(strTemp.Find("All Clients")==-1)
					{
						strData+="<Client: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}



				//project
				if(m_cbProject.IsWindowEnabled())
				{
					m_cbProject.GetWindowText(strTemp);
					if(strTemp.Find("All Projects")==-1)
					{
						strData+="<Project: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}


				//rate
				if(m_cbRate.IsWindowEnabled())
				{
					m_cbRate.GetWindowText(strTemp);
					if(strTemp.Find("All Rate")==-1)
					{
						strData+="<Rate: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}


				//zone
				if(m_cbZone.IsWindowEnabled())
				{
					m_cbZone.GetWindowText(strTemp);
					if(strTemp.Find("All Zone")==-1)
					{
						strData+="<Zone: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}


				//tech
				if(m_cbTech.IsWindowEnabled())
				{
					m_cbTech.GetWindowText(strTemp);
					if(strTemp.Find("All Tech")==-1)
					{
						strData+="<Tech: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}


				//category
				if(m_cbCategory.IsWindowEnabled())
				{
					m_cbCategory.GetWindowText(strTemp);
					if(strTemp.Find("All Categor")==-1)
					{
						strData+="<Category: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}

				//part 
				if(m_cbPart.IsWindowEnabled())
				{
					m_cbPart.GetWindowText(strTemp);
					if(strTemp.Find("All Part")==-1)
					{
						strData+="<Part: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}


				//unit
				if(m_cbUnit.IsWindowEnabled())
				{
					m_cbUnit.GetWindowText(strTemp);
					if(strTemp.Find("All Unit")==-1)
					{
						strData+="<Unit: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}


				//manufacturer
				if(m_cbManufacturer.IsWindowEnabled())
				{
					m_cbManufacturer.GetWindowText(strTemp);
					if(strTemp.Find("All Manufact")==-1)
					{
						strData+="<Manufacturer: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}


				//model
				if(m_cbModel.IsWindowEnabled())
				{
					m_cbModel.GetWindowText(strTemp);
					if(strTemp.Find("All model")==-1)
					{
						strData+="<Model: " + strTemp + "> ";

					}
					else
						strData+=strTemp + " ";
				}











				m_strSQL+="\""+strData+"\"";;
				goto DONE;
			}

		}
		else
			m_strSQL+=txt;

DONE:
		;

	}

#ifdef _DEBUG
	//	m_pApp->ShowStuff(m_strSQL);
#endif

	//AfxMessageBox(m_strSQL);

}

void RPTS::OnCloseupCbdaterange() 
{
	/*
	m_cbDateRange.AddRow(" <Any date>","0");
	m_cbDateRange.AddRow("Between dates...","1");
	m_cbDateRange.AddRow("This month","2");
	m_cbDateRange.AddRow("Last month","3");
	m_cbDateRange.AddRow("Last 6 months","4");
	m_cbDateRange.AddRow("Last 12 months","5");
	m_cbDateRange.AddRow("This year","6");
	m_cbDateRange.AddRow("Last year","7");
	m_cbDateRange.SetCurSel(0);
	*/

	CString strData;
	int x;
	COleDateTime dtStart,dtEnd,dtNow;
	dtNow=COleDateTime::GetCurrentTime();
	x=atoi(m_cbDateRange.GetCurrentRowID());

	switch(x)
	{
	case 0://Anytime
		dtStart.SetDateTime(1900,1,1,0,0,0);
		dtEnd.SetDateTime(2500,1,1,0,0,0);
		break;
	case 1://between dates
		return;
		//do nothing
		break;
	case 2://this month
		dtStart.SetDate(dtNow.GetYear(),dtNow.GetMonth(),1);
		dtEnd=dtStart;
		GetMonthEnd(&dtEnd);
		break;
	case 3://last month
		dtStart=dtNow;
		GetFirstDayLastMonth(&dtStart);
		dtEnd=dtStart;
		GetMonthEnd(&dtEnd);
		break;
	case 4://last 6 months
		dtStart=dtEnd=dtNow;
		GetDateXMonthsAgo(&dtStart,6);
		break;
	case 5://last 12 months
		dtStart=dtEnd=dtNow;
		GetDateXMonthsAgo(&dtStart,12);
		break;
	case 6://this year
		dtStart.SetDate(dtNow.GetYear(),1,1);//jan 1st this year
		dtEnd.SetDate(dtNow.GetYear(),12,31);//dec 31st this year
		break;
	case 7://last year
		dtStart.SetDate(dtNow.GetYear()-1,1,1);//jan 1st this year
		dtEnd.SetDate(dtNow.GetYear()-1,12,31);//dec 31st this year
		break;


	}


	m_dtStart.SetTime(dtStart);
	m_dtEnd.SetTime(dtEnd);

}


//Modifies passed COleDateTime object
//to = last day of month that was
//originally stored in the date time object
void RPTS::GetMonthEnd(COleDateTime *dtData)
{
	COleDateTimeSpan span;
	COleDateTime dt=*dtData;
	int x=0;
	int curmonth;

	//add 1 day until the month changes
	curmonth=dtData->GetMonth();
	while(dt.GetMonth()==curmonth)
	{	
		x++;
		span.SetDateTimeSpan(x,0,0,0);
		dt=*dtData+span;
	}

	span.SetDateTimeSpan(-1,0,0,0);
	*dtData=dt+span;
}


//Modifies passed in COleDateTime object
//to equal the 1st of the previous month
void RPTS::GetFirstDayLastMonth(COleDateTime *dtData)
{
	COleDateTimeSpan span;
	COleDateTime dt=*dtData;
	int x=0;
	int curmonth;

	//add 1 day until the month changes
	curmonth=dtData->GetMonth();
	while(dt.GetMonth()==curmonth)
	{	
		x--;
		span.SetDateTimeSpan(x,0,0,0);
		dt=*dtData+span;
	}



	dtData->SetDate(dt.GetYear(),dt.GetMonth(),1);

}




void RPTS::GetDateXMonthsAgo(COleDateTime* dtData, int iMonths)
{
	ASSERT(iMonths>0);
	if(iMonths<1) return;
	int iNewYear=dtData->GetYear();
	int iDays = dtData->GetDay();
	int iNewMonths ;
	div_t div_result = div(iMonths, 12);
	iNewYear = dtData->GetYear() - div_result.quot;
	iNewMonths=dtData->GetMonth()-div_result.rem;
	if(iNewMonths<1)
	{
		iNewMonths=12+iNewMonths;	
		iNewYear--;

	}


	dtData->SetDate(iNewYear,iNewMonths,iDays);


	while (dtData->GetStatus() == COleDateTime::invalid && iDays>28) {
		iDays--;
		dtData->SetDate(iNewYear, iNewMonths, iDays);
	}

	return;
}


void RPTS::OnDatetimechangeDtend(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	//OnCloseupCbdaterange();
	m_cbDateRange.Select(1);//between
	*pResult = 0;
}

void RPTS::OnDatetimechangeDtstart(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	//OnCloseupCbdaterange();
	m_cbDateRange.Select(1);//between
	*pResult = 0;
}

void RPTS::OnBtnttx() 
{

	//	BuildCriteria();
	CString q, strData;
	long lReport=atol(m_cbReport.GetCurrentRowID());
	if(lReport==0) return;
	//Setup sql for selected report then open it
	BuildSql();//substitute criteria tokens in query

	//open report
	rs->QueryReadOnly(m_strSQL);
	if(rs->IsEmpty())
		AfxMessageBox("There is no data that matches\r\n"
		"your criteria choices");
	else
	{
		strData=m_strReportFileName;
		strData=strData.Left(strData.GetLength()-4);
		strData=strData+".ttx";
		//AfxMessageBox(m_strSQL);
		//m_pApp->PrintCMReport(m_cbReport.GetCurrentRowText(),rs->RecordSetPointer());
		m_pApp->CreateTTX(strData,rs->RecordSetPointer());


	}






}

void RPTS::OnBtnfields() 
{
	BuildCriteria();
#ifdef _DEBUG
	//BuildSql();
#endif	
}



void RPTS::OnBtnrefresh() 
{
	m_pApp->m_bRefreshReportsScreen=true;
	FillLists(false);

}

void RPTS::OnBtnsql() 
{
	CString strDisplay;
	long lReport=atol(m_cbReport.GetCurrentRowID());
	if(lReport==0) return;
	//Setup sql for selected report then show
	BuildSql();//substitute criteria tokens in query
	strDisplay="<Master query (in rptsmaster table)>\r\n\r\n"+
		m_strRawSQL;

	if(m_strRawSQL!=m_strProcessedSQL)//subs have been made
		strDisplay +=
		"\r\n\r\n\r\n\r\n< Head office was selected, master query changed to this>\r\n\r\n"+
		m_strProcessedSQL;
	
	strDisplay += 
		"\r\n\r\n\r\n\r\n<Report query (after token substitution)>\r\n\r\n"+
		m_strSQL + "\r\n";

	m_pApp->ShowStuff(strDisplay);
}

BOOL RPTS::OnHelpInfo(HELPINFO* pHelpInfo)
{

	WinHelp (0x00020000 + IDD_RPTS_FORM,HELP_CONTEXT);
	return TRUE;
}



// Substitute head offices in query if one was selected as criteria
void RPTS::SubHeadOffice(void)
{
	//start afresh
	m_strProcessedSQL=m_strRawSQL;

	CString strClient;
	strClient=m_cbClient.GetCurrentRowID();

	//See if it's an "<All clients>" selection which doesn't require processing.
	if(strClient=="0")
		return;

	//See if there is a cust token to bother continuing
	if(m_strProcessedSQL.Find("~CUST")==-1)
		return;

	//Next see if it's a head office
	CString q;
	bool bHeadOffice=false;
	q.Format("SELECT clients.isheadoffice FROM clients WHERE (((clients.id)=%s));",strClient);
	rs->QueryReadOnly(q);
	if(!rs->IsEmpty())
	{
		rs->FetchField("isheadoffice",&bHeadOffice);
	}

	//Not a head office?  Then bail
	if(false==bHeadOffice)
		return;
	
	//OK, it is a head office, now see if there is anything that can
	//be done with the query and substitute if so..
	
	if(m_strProcessedSQL.Find("JOIN clients")!=-1 || m_strProcessedSQL.Find("clients.")!=-1 
		|| m_strProcessedSQL.Find("[clients]")!=-1)
	{
		//yes we have a join to clients so start substituting
		m_strProcessedSQL.Replace("(wo.client)~CUST","(clients.headoffice)~CUST");
		m_strProcessedSQL.Replace("(clients.id)~CUST","(clients.headoffice)~CUST");

		//versions with spaces in case user hand typed it in
		m_strProcessedSQL.Replace("(wo.client) ~CUST","(clients.headoffice)~CUST");
		m_strProcessedSQL.Replace("(clients.id) ~CUST","(clients.headoffice)~CUST");
	}	
}