admin/ayanova7
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
ayanova7/archive/ayanova 1.9.4 CE final release db schema 171/sp/SRCH.cpp

2645 lines
69 KiB
C++
Raw Permalink Blame History

// SRCH.cpp : implementation file
//
#include "stdafx.h"
#include "sp.h"
#include "SRCH.h"
#include "ted.h"
#include "SrchView.h"
#include "ClientsDlg.h"
#include "loanerseditdlg.h"
#include "ContactsViewDlg.h"
#include "unitsdlg.h"
#include "SnREdit.h"
#define WORDWINDOW 60 //# of characters on either side of keyword in window
#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>
/////////////////////////////////////////////////////////////////////////////
// SRCH
IMPLEMENT_DYNCREATE(SRCH, CFormView)
SRCH::SRCH()
: CFormView(SRCH::IDD)
, m_nCX(0)
{
//{{AFX_DATA_INIT(SRCH)
//}}AFX_DATA_INIT
m_pApp= (CSpApp*)AfxGetApp();
rs=m_pApp->rsPool->GetRS("SRCH (RS)");
rs2=m_pApp->rsPool->GetRS("SRCH (RS2)");
m_strOrderBy="ORDER BY srch.rank DESC";
}
SRCH::~SRCH()
{
DeActivate();
}
void SRCH::DoDataExchange(CDataExchange* pDX)
{
CFormView::DoDataExchange(pDX);
//{{AFX_DATA_MAP(SRCH)
DDX_Control(pDX, IDC_EDBAD, m_edBad);
DDX_Control(pDX, IDC_EDSEARCHTERMS, m_edSearchTerms);
DDX_Control(pDX, IDC_LBLBETWEEN, m_lblBetween);
DDX_Control(pDX, IDC_LBLAND, m_lblAnd);
DDX_Control(pDX, IDC_DTDATE2, m_dtDate2);
DDX_Control(pDX, IDC_LBLRESULTS, m_lblResults);
DDX_Control(pDX, IDC_LBLBAD, m_lblBad);
DDX_Control(pDX, IDC_BTNSEARCH, m_btnSearch);
DDX_Control(pDX, IDC_CBCLIENTS, m_cbClients);
DDX_Control(pDX, IDC_CKPREVIEW, m_ckPreview);
DDX_Control(pDX, IDC_CBDATE, m_cbDate);
DDX_Control(pDX, IDC_DTDATE, m_dtDate);
DDX_Control(pDX, IDC_REPORT, m_rc);
//}}AFX_DATA_MAP
DDX_Control(pDX, IDC_LBL_DATE_RANGE, m_lblDateRange);
DDX_Control(pDX, IDC_LBL_CLIENTS, m_lblClients);
DDX_Control(pDX, IDC_LBL_KEYWORDS, m_lblKeyWords);
}
BEGIN_MESSAGE_MAP(SRCH, CFormView)
//{{AFX_MSG_MAP(SRCH)
ON_BN_CLICKED(IDC_BTNSEARCH, OnBtnsearch)
ON_CBN_CLOSEUP(IDC_CBDATE, OnCloseupCbdate)
ON_NOTIFY(RVN_ITEMDBCLICK, IDC_REPORT, OnRvnItemDbClick)
ON_NOTIFY(RVN_COLUMNCLICK, IDC_REPORT, OnColumnClick)
ON_NOTIFY(RVN_ITEMDRAWPREVIEW, IDC_REPORT, OnRvnItemDrawPreview)
//}}AFX_MSG_MAP
ON_WM_SIZE()
ON_WM_HELPINFO()
ON_CBN_SELCHANGE(IDC_CBCLIENTS, OnCbnSelchangeCbclients)
END_MESSAGE_MAP()
/////////////////////////////////////////////////////////////////////////////
// SRCH diagnostics
#ifdef _DEBUG
void SRCH::AssertValid() const
{
CFormView::AssertValid();
}
void SRCH::Dump(CDumpContext& dc) const
{
CFormView::Dump(dc);
}
#endif //_DEBUG
/////////////////////////////////////////////////////////////////////////////
// SRCH message handlers
void SRCH::Activate()
{
#ifdef _WTF_
AfxMessageBox("SRCH activate");
#endif
/*
if(rs==NULL)
{
rs = new GZRset("Error in rs.SRCH");
rs->SetConnect(m_pApp->strConnectString);
}
*/
//Initialize recordset pointer
rs=m_pApp->rsPool->GetRS("SRCH (RS)");
rs2=m_pApp->rsPool->GetRS("SRCH (RS2)");
m_edSearchTerms.SetFocus();
//m_edSearchTerms.SetWindowText("print* problem tray not paper");
}
void SRCH::DeActivate()
{
#ifdef _WTF_
AfxMessageBox("SRCH De-activate");
#endif
m_pApp->rsPool->ReleaseRS(&rs->m_nID);
m_pApp->rsPool->ReleaseRS(&rs2->m_nID);
/*
if(rs!=NULL)
{
delete rs;
rs=NULL;
}
if(rs2!=NULL)
{
delete rs2;
rs2=NULL;
}
*/
/*
CString profile;
m_rc.WriteProfile(&profile);
AfxMessageBox(profile);
*/
}
void SRCH::OnRvnItemDbClick(NMHDR* pNMHDR, LRESULT* pResult)
{
CString str,strID,q,strData,strWOID;
COleDateTime dtData;
int nTable;
long lID,lData;
bool bQuickWO=false;
CSrchView sv;
LPNMREPORTVIEW lpnmrv = (LPNMREPORTVIEW)pNMHDR;
if(lpnmrv->iItem >=0)//-1 if clicked on invalid
{
//FLAG THIS LIST ITEM AS CLICKED HERE
/*if(rs==NULL)
{
rs = new GZRset("Error in Search.rs.OnSrchView");
rs->SetConnect(m_pApp->strConnectString);
}*/
nTable=atoi(m_rc.GetItemText(lpnmrv->iItem,5));
strID=m_rc.GetItemText(lpnmrv->iItem,4);
lID=atol(strID);
//GET ORIGINAL TABLE DATA DEPENDING ON TYPE IF NECESSARY
//AND DISPLAY
switch(nTable)
{
case 0://problems
{
q.Format("SELECT [users].[last] & \", \" & [users].[first] AS techname, labor.details, probs.brief, probs.notes, IIf(IsNull([company]),[clients].[last] & \", \" & [clients].[first],[company]) AS clientname, wo.quick, wo.id AS woid "
"FROM (((labor RIGHT JOIN probs ON labor.link = probs.id) LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN users ON labor.tech = users.id "
"WHERE (((probs.id)=%u));",lID);
#ifdef _DEBUG
//m_pApp->ShowStuff(q);
#endif
rs->QueryReadOnly(q);
rs->FetchField("woid",&lData);
strWOID.Format("%u",lData);
rs->FetchField("quick",&bQuickWO);
str="CLIENT: ";
rs->FetchField("clientname",&strData);
str+= strData + "\r\n\r\nTECH: ";
rs->FetchField("techname",&strData);
str+=strData + "\r\n";
if(!bQuickWO)
{
str+= "\r\nPROBLEM: ";
rs->FetchField("brief",&strData);
str+=strData + "\r\n";
rs->FetchField("notes",&strData);
str+=strData + "\r\n";
}
rs->FetchField("details",&strData);
str+="\r\nSERVICE DETAILS:\r\n" + strData;
str+="\r\n\r\n(note: click on EDIT button below to show entire workorder)";
//Display srchview for labour records
sv.m_pstrText=&str;
sv.m_pslHiWords=&strList;
if(m_bUseKeywords)
sv.m_nKeywords=m_nSWords;
else
sv.m_nKeywords=0;
sv.m_bQuickWO=bQuickWO;
sv.m_strWOID=strWOID;
sv.DoModal();
}
break;
case 1://labour
{
q.Format("SELECT [users].[last] & \", \" & [users].[first] AS techname, labor.details, probs.brief, probs.notes, IIf(IsNull([company]),[clients].[last] & \", \" & [clients].[first],[company]) AS clientname, wo.quick, wo.id AS woid "
"FROM (((labor LEFT JOIN probs ON labor.link = probs.id) LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN users ON labor.tech = users.id "
"WHERE (((labor.id)=%u));",lID);
rs->QueryReadOnly(q);
rs->FetchField("woid",&lData);
strWOID.Format("%u",lData);
rs->FetchField("quick",&bQuickWO);
str="Workorder #: " + strWOID + "\r\n";
str+="\r\nCLIENT: ";
rs->FetchField("clientname",&strData);
str+= strData + "\r\n\r\nTECH: ";
rs->FetchField("techname",&strData);
str+=strData + "\r\n";
if(!bQuickWO)
{
str+= "\r\nPROBLEM: ";
rs->FetchField("brief",&strData);
str+=strData + "\r\n";
rs->FetchField("notes",&strData);
str+=strData + "\r\n";
}
rs->FetchField("details",&strData);
str+="\r\nSERVICE DETAILS:\r\n" + strData;
str+="\r\n\r\n(note: click on EDIT button below to show entire workorder)";
//Display srchview for labour records
sv.m_pstrText=&str;
sv.m_pslHiWords=&strList;
if(m_bUseKeywords)
sv.m_nKeywords=m_nSWords;
else
sv.m_nKeywords=0;
sv.m_bQuickWO=bQuickWO;
sv.m_strWOID=strWOID;
sv.DoModal();
}
break;
case 2://subrepair
{
CSnREdit srd;
srd.m_strSubRepairID=strID;
srd.DoModal();
}
break;
case 3://rentals
{
CLoanersEditDlg ld;
ld.SetRentalID(&strID);
ld.DoModal();
}
break;
case 4://clients
{
CClientsDlg cd;
cd.m_strForceSelection.Format("%u",lID);
cd.DoModal();
}
break;
case 5://contacts
{
q.Format("SELECT contacts.id, contacts.clientlink "
"FROM contacts "
"WHERE (((contacts.id)=%u));",lID);
rs->QueryReadOnly(q);
rs->FetchField("clientlink",&lID);
strData.Format("%u",lID);
CContactsViewDlg cd;
cd.Setup(strData);
cd.DoModal();
}
break;
case 6://units
{
/*
q.Format("SELECT units.sn FROM units "
"WHERE (((units.id)=%u));",lID);
rs->Query(q);
rs->FetchField("sn",&strData);
*/
//strData.Format("%u",lID);
CString empty;
empty.Empty();
CUnitsDlg cd;
cd.SetReturnStrings(&strID,&empty);
//cd.m_strSelectedUnit=strData;
cd.DoModal();
}
break;
case 7://workorder header added May 10, 2001
{
q.Format("SELECT [users].[last] & \", \" & [users].[first] AS techname, labor.details, probs.brief, probs.notes, IIf(IsNull([company]),[clients].[last] & \", \" & [clients].[first],[company]) AS clientname, wo.quick, wo.id AS woid, "
"wo.notes, wo.invoice, wo.clientrefnum, wo.clientcontact, wo.ourref "
"FROM (((labor RIGHT JOIN probs ON labor.link = probs.id) LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN users ON labor.tech = users.id "
"WHERE (((wo.id)=%u));",lID);
#ifdef _DEBUG
//m_pApp->ShowStuff(q);
#endif
rs->QueryReadOnly(q);
rs->FetchField("woid",&lData);
strWOID.Format("%u",lData);
rs->FetchField("quick",&bQuickWO);
str.Format("WORKORDER: %s",strWOID);
str+= "\r\nCLIENT REFERENCE#: ";
rs->FetchField("clientrefnum",&strData);
str+=strData;
str+= "\r\nCLIENT CONTACT: ";
rs->FetchField("clientcontact",&strData);
str+=strData;
str+= "\r\nOUR REFERENCE#: ";
rs->FetchField("ourref",&strData);
str+=strData;
str+= "\r\n\r\nCLIENT: ";
rs->FetchField("clientname",&strData);
str+=strData + "\r\n";
str+= strData + "\r\n\r\nTECH: ";
rs->FetchField("techname",&strData);
str+=strData + "\r\n";
if(!bQuickWO)
{
str+= "\r\nINVOICE#: ";
rs->FetchField("invoice",&strData);
str+=strData + "\r\n";
rs->FetchField("wo.notes",&strData);
str+=strData + "\r\n";
}
/*rs->FetchField("details",&strData);
str+="\r\nSERVICE DETAILS:\r\n" + strData;
*/
str+="\r\n\r\n(note: click on EDIT button below to show entire workorder)";
//Display srchview for labour records
sv.m_pstrText=&str;
sv.m_pslHiWords=&strList;
if(m_bUseKeywords)
sv.m_nKeywords=m_nSWords;
else
sv.m_nKeywords=0;
sv.m_bQuickWO=bQuickWO;
sv.m_strWOID=strWOID;
sv.DoModal();
}
break;
}
/*
if(rs!=NULL)
{
delete rs;
rs=NULL;
}*/
rs->Close();
}
*pResult = FALSE;
}
//*************************************************************
void SRCH::OnBtnsearch()
{
CString strTerms,str,strWild,q,dispwords;
long lMatches=0;
bool bAllGoodWords=false;
int loopwords,searchtime,pos;
bool bNegative=false;
COleDateTime dtStart,dtEnd;
COleDateTimeSpan dtsDuration;
m_lResultCount=0;
m_lblResults.ShowWindow(FALSE);
m_lblBad.ShowWindow(FALSE);
m_edBad.ShowWindow(FALSE);
m_lblResults.SetFontBold(TRUE);
//m_lblResults.SetFontUnderline(TRUE);
m_edSearchTerms.GetWindowText(strTerms);
if(strTerms.IsEmpty())
return;
if(strTerms=="*" && m_cbClients.GetCurrentRowID()=="0")
{
AfxMessageBox("Select a client to use the * (all records) search\r\n");
return;
}
strTerms.MakeLower();
dtStart=COleDateTime::GetCurrentTime();
m_strCriteria.Empty();
m_bPreview=false;
m_bUseKeywords=false;
if(strTerms!="*")//* means don't use keywords just return everything
{ m_bPreview=m_ckPreview.GetCheck() ? true : false;
m_bUseKeywords=true;
if(strTerms.Left(4)=="not ")
{
AfxMessageBox("You can't use 'not' as the first word in a search term.\r\n"
"The first part of a search term needs to return matches");
return;
}
//CHECK FOR WILDCARDS NOT AT EDGE OF WORD...
pos=0;
pos=strTerms.Find("*",0);
while(pos!=-1)
{
if(pos!=0 && pos!=strTerms.GetLength()-1)
if(strTerms.Mid(pos-1,1)!=" " && strTerms.Mid(pos+1,1)!=" ")
{
AfxMessageBox("The wildcard (*) character can only be used at the start or end of a word");
return;
}
pos=strTerms.Find("*",pos+1);
}
/*
if(rs==NULL)
{
rs = new GZRset("Error in EXEC.Index.rs DBUTILS");
rs->SetConnect(m_pApp->strConnectString);
}
*/
SuckWords(&strTerms);
if(m_nSWords>0)
{
loopwords=m_nSWords;
bAllGoodWords=true;
strNotIn.Empty();
strStopWords.Empty();
for(int x=0;x<loopwords;x++)
{
lMatches=0;
str=strList.GetAt(strList.FindIndex(x));
if(str.Remove('-')!=0)//is negative
bNegative=true;
//see if it's a stop word:
q.Format("SELECT srchstopwords.word FROM srchstopwords "
"WHERE (((srchstopwords.word)=\"%s\"));",str);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())//it's a stop word
{
strStopWords+=str+" ";
//mark the word as blank if it's invalid
strList.SetAt(strList.FindIndex(x),"");
//Mark(str,true);
bAllGoodWords=false;
}
else//see if it's in the dictionary
{
//optimize query for performance, if don't need a wildcard search
//dont do it as adds about 10 times to length of time to search
strWild="=";
if(str.Find("%")!=-1)
strWild="Like";
q.Format("SELECT Count(srchdict.ID) AS wordcount "
"FROM srchdict "
"WHERE (((srchdict.word) %s '%s'));",strWild,str);
rs->QueryReadOnly(q);
rs->FetchField("wordcount",&lMatches);
if(lMatches==0)
{
//mark search terms word as invalid
strNotIn+=str +" ";
//set the word as blank if it's invalid
strList.SetAt(strList.FindIndex(x),"");
//Mark(str,false);
bAllGoodWords=false;
}
else
{
str.Replace("%","*");
if(bNegative)
str="not "+ str;
dispwords+=str + " ";
bNegative=false;
}
}
}
m_edSearchTerms.SetWindowText(dispwords);
if(!bAllGoodWords)
{
CString badwords;
badwords=strStopWords + " " + strNotIn;
badwords.TrimLeft(" ");
badwords.TrimRight(" ");
m_edBad.SetWindowText(badwords);
m_lblBad.ShowWindow(TRUE);
m_edBad.ShowWindow(TRUE);
/*
if(!strNotIn.IsEmpty())
Mark(strNotIn,false);
if(!strStopWords.IsEmpty())
Mark(strStopWords,true);
*/
}
}
/*
if(rs!=NULL)
{
delete rs;
rs=NULL;
}*/
rs->Close();
if(DoSearch())
{
//
FillView();
}
else
m_rc.DeleteAllItems();
}//if sterms="*"
else
{
bAllGoodWords=true;//There aren't any, so they must be all good!
if(DoNoKeywordSearch())
FillView();
else
m_rc.DeleteAllItems();
}
dtEnd=COleDateTime::GetCurrentTime();
dtsDuration=dtEnd-dtStart;
searchtime=(int)dtsDuration.GetTotalSeconds();
if(m_lResultCount>0)
{
m_lblResults.SetTextColor(RGB(0,0,255));
str.Format("%u matches found in %i seconds",m_lResultCount,searchtime);
m_lblResults.SetWindowText(str);
}
else
{
m_lblResults.SetTextColor(RGB(255,0,0));
if(!bAllGoodWords)
m_lblResults.SetWindowText("No matches - hit enter to search with revised search words");
else
m_lblResults.SetWindowText("No matches found");
}
m_lblResults.ShowWindow(TRUE);
}
//suck the words out of the search terms
//using same algorithm as was used to make the
//dictionary, ensuring matches to badly formed terms
//with punctuation marks etc.
bool SRCH::SuckWords(CString* pTerms)
{
CString str,strText,strWord,lchar,q;
long lPos;
m_nSWords=0;
int nLength;
bool firstword=true;
bool bNot=false;//indicates it's a "not" exclusive word
CStringList sl;
strList.RemoveAll();
//make sure there is something to parse
nLength=pTerms->GetLength();
if (nLength > 1)
{
strText.MakeLower(); //make all lower case
strWord.Empty();
//loop through all characters in strText
for(lPos=0;lPos < nLength+1;lPos++)
{
lchar=pTerms->Mid(lPos,1);
//is a regular character
if ( (lchar >= "a" && lchar <= "z") || (lchar >="0" && lchar <= "9") || (lchar=="*") || (lchar=="?"))
strWord=strWord+lchar;
else//is a non-regular text character
{
if(strWord.GetLength() > 0 )
{
//is it an exclusion word?
if(strWord=="not")
bNot=true;
else
{
//ANSI SQL conformity
strWord.Replace("*","%");
//UPDATED:not supported - strWord.Replace("?","_");
if(bNot)
strWord="-"+strWord;
if(firstword)
strList.AddHead(strWord);
else
strList.AddTail(strWord);
m_nSWords++;
firstword=false;
bNot=false;
}
}
strWord.Empty();
}//end of else for a non regular character
}//lPos loop
//have to add the very last strWord in manually, this may not really be necessary
//but does not hurt as all duplicates are purged anyway later on in the process
if(strWord.GetLength() > 0)
{
//ANSI SQL conformity
strWord.Replace("*","%");
//NOT SUPPORTED:strWord.Replace("?","_");
strList.AddTail(strWord);
m_nSWords++;
}
}//nLength if to make sure is longer than null
return true;
}
//***************************///////////*************
void SRCH::OnInitialUpdate()
{
#ifdef _WTF_
AfxMessageBox("top of: SRCH::OnInitialUpdate()");
#endif
CFormView::OnInitialUpdate();
CString strIndex,strData;
long lData;
m_ckPreview.SetCheck(TRUE);
/*
if(rs==NULL)
{
rs = new GZRset("Error in rs.SRCH");
rs->SetConnect(m_pApp->strConnectString);
}
*/
#ifdef _WTF_
AfxMessageBox("Fill client list: SRCH::OnInitialUpdate()");
#endif
m_cbClients.Clear();
m_cbClients.AddRow(" < Any Client > ","0");
rs->QueryReadOnly("SELECT clients.id, IIf(IsNull([company]),[last] & \", \" & [first],[company]) AS name "
"FROM clients ORDER BY IIf(IsNull([company]),[last] & \", \" & [first],[company]);");
if(!rs->IsEmpty())
{
do
{
rs->FetchField("name",&strData);
rs->FetchField("id",&lData);
strIndex.Format("%u",lData);
m_cbClients.AddRow(strData,strIndex);
}while(rs->MoveForward());
}
#ifdef _WTF_
AfxMessageBox("Done filling client list: SRCH::OnInitialUpdate()");
#endif
m_cbClients.Select(0);
//============================================
m_cbDate.SetCurSel(0);
#ifdef _WTF_
AfxMessageBox("Create report control: SRCH::OnInitialUpdate()");
#endif
//REPORT CONTROL
m_ilReport.Create(IDB_BM2, 16, 1, RGB(255,0,255));
m_rc.SetImageList(&m_ilReport);
//ShowWindow(SW_MAXIMIZE);
m_rc.InsertColor(0, 0x00C0D8C0);
m_rc.InsertColor(1, ::GetSysColor(COLOR_GRAYTEXT));
m_rc.InsertColor(2, 0x00D0C0C0);
m_rc.InsertColor(3, 0x00804000);
RVCOLUMN rvc;
rvc.nFormat = RVCF_TEXT;// | RVCF_EX_FIXEDWIDTH;
rvc.lpszText = "Relevance:";
rvc.iWidth = 75;
m_rc.DefineColumn(0, &rvc);
rvc.lpszText = _T("Source");
rvc.iWidth = 131;
m_rc.DefineColumn(1, &rvc);
rvc.lpszText = "Date";
rvc.iWidth = 105;
m_rc.DefineColumn(2, &rvc);
rvc.lpszText = "Client";
rvc.iWidth = 450;
m_rc.DefineColumn(3, &rvc);
rvc.lpszText = "id";
rvc.iWidth = 0;
m_rc.DefineColumn(4, &rvc);
rvc.lpszText = "table";
m_rc.DefineColumn(5, &rvc);
#ifdef _WTF_
AfxMessageBox("About to activate columns: SRCH::OnInitialUpdate()");
#endif
m_rc.ActivateColumn(0, 0);
m_rc.ActivateColumn(1, 1);
m_rc.ActivateColumn(2, 2);
m_rc.ActivateColumn(3, 3);
m_rc.ActivateColumn(4, 4);
m_rc.ActivateColumn(5, 5);
#ifdef _WTF_
AfxMessageBox("About to modify style: SRCH::OnInitialUpdate()");
#endif
m_rc.ModifyStyle(0, RVS_SINGLESELECT);
//m_rc.ModifyStyle(0, RVS_SHOWCOLORALTERNATE);
//turn off the grids
m_rc.ModifyStyle(RVS_SHOWVGRID, 0);
m_rc.ModifyStyle(RVS_SHOWHGRID, 0);
m_edSearchTerms.SetFocus();
#ifdef _WTF_
AfxMessageBox("Setting column sorts: SRCH::OnInitialUpdate()");
#endif
for(int xx=0;xx<COLUMNCOUNT;xx++)
bColumnSortAsc[xx]=FALSE;
#ifdef _WTF_
AfxMessageBox("bottom of: SRCH::OnInitialUpdate()");
#endif
DeActivate();
}
void SRCH::FillView()
{
CString strData,q;
COleDateTime dtData;
unsigned char ucData;
bool bFirst=true;
long lData;
m_lResultCount=0;
int x=0;
/*
if(rs==NULL)
{
rs = new GZRset("Error in Search.rs.DoSearch");
rs->SetConnect(m_pApp->strConnectString);
}
*/
rs->Query("SELECT * FROM defaults;");
q.Format("SELECT srch.* FROM srch WHERE (((srch.userid)=%u)) %s;",m_pApp->m_lusrID,m_strOrderBy);
rs->Query(q);
m_rc.DeleteAllItems();
slExcerpts.RemoveAll();
if(rs->IsEmpty())
{
/*
if(rs!=NULL)
{
delete rs;
rs=NULL;
}*/
rs->Close();
return;
}
// rs->MoveFirst();
do
{
rs->FetchField("excerpt",&strData);
if(bFirst)
{
slExcerpts.AddHead(strData);
bFirst=false;
}
else
slExcerpts.AddTail(strData);
rs->FetchField("rank",&lData);
strData.Format("%u",lData);
//RANK
RVITEM rvi;
rvi.iItem = x;
rvi.iSubItem = 0;
rvi.nMask = RVIM_TEXT|RVIM_PREVIEW|RVIM_STATE|RVIM_LPARAM;;
if(m_bPreview)
rvi.nPreview = 17;
else
rvi.nPreview = 0;
rvi.lpszText = strData.GetBuffer(strData.GetLength());
m_rc.InsertItem(&rvi);
//SOURCE
rs->FetchField("tabletype",&ucData);
switch (ucData)
{
case 0:
strData="Problems";
break;
case 1:
strData="Labour";
break;
case 2:
strData="Subrepairs";
break;
case 3:
strData="Rentals / Loaners";
break;
case 4:
strData="Clients";
break;
case 5:
strData="Client notebook";
break;
case 6:
strData="Units";
break;
case 7://added may 10th, 2001
strData="Workorder header";
break;
}
rvi.iSubItem = 1;
rvi.nMask = RVIM_TEXT;
rvi.lpszText = strData.GetBuffer(strData.GetLength());
m_rc.SetItem(&rvi);
//Date
rs->FetchField("date",&dtData);
strData=dtData.Format(VAR_DATEVALUEONLY);
rvi.iSubItem = 2;
rvi.lpszText = strData.GetBuffer(strData.GetLength());
m_rc.SetItem(&rvi);
//CLIENT
rs->FetchField("client",&strData);
strData.Replace("&","&&");
rvi.iSubItem = 3;
rvi.lpszText = strData.GetBuffer(strData.GetLength());
m_rc.SetItem(&rvi);
//TABLEID
rs->FetchField("tableid",&lData);
strData.Format("%u",lData);
rvi.iSubItem = 4;
rvi.lpszText = strData.GetBuffer(strData.GetLength());
m_rc.SetItem(&rvi);
//TABLETYPE
rs->FetchField("tabletype",&ucData);
strData.Format("%i",(int)ucData);
rvi.iSubItem = 5;
rvi.lpszText = strData.GetBuffer(strData.GetLength());
m_rc.SetItem(&rvi);
x++;
}while(rs->MoveForward());
/*
if(rs!=NULL)
{
delete rs;
rs=NULL;
}*/
rs->Close();
m_lResultCount=x;
}
/*
//mark a word as invalid in the richedit
//control. bStopWord means cross it out
//as it's a stop word, otherwise just underline it
//as not in dictionary.
void SRCH::Mark(CString strText,bool bStopWord)
{
FINDTEXTEX ft;
CHARFORMAT cf;
strText.TrimLeft(" ");
strText.TrimRight(" ");
m_lblBad.ShowWindow(TRUE);
m_redBadWords.ShowWindow(TRUE);
ft.chrg.cpMin = 0;
ft.chrg.cpMax = -1;
ft.lpstrText = (LPSTR) strText.GetBuffer(0);
long n = m_redBadWords.FindText(FR_MATCHCASE|FR_WHOLEWORD, &ft);
if (n != -1)
m_redBadWords.SetSel(ft.chrgText);
else
return;
//red stopword, blue not in dictionary word
//had to set bold, will not change
//color if some effect is not also applied
cf.dwMask = CFM_COLOR | CFM_BOLD;
if(bStopWord)
{
cf.crTextColor=RGB(255,0,0);
cf.dwEffects = CFE_BOLD;
}
else
{
cf.crTextColor=RGB(0,0,255);
cf.dwEffects = CFE_BOLD;
}
m_redBadWords.SetSelectionCharFormat(cf);
m_redBadWords.SetSel(0,0);
}
*/
void SRCH::OnCloseupCbdate()
{
int x=0;
x=m_cbDate.GetCurSel();
m_dtDate.ShowWindow(FALSE);
m_dtDate2.ShowWindow(FALSE);
m_lblBetween.ShowWindow(FALSE);
m_lblAnd.ShowWindow(FALSE);
if(x==2 || x==3)
m_dtDate.ShowWindow(TRUE);
if(x==1)
{
m_dtDate.ShowWindow(TRUE);
m_dtDate2.ShowWindow(TRUE);
m_lblBetween.ShowWindow(TRUE);
m_lblAnd.ShowWindow(TRUE);
}
}
void SRCH::OnRvnItemDrawPreview(NMHDR* pNMHDR, LRESULT* pResult)
{
CString strText;
LPNMRVDRAWPREVIEW lpnmrvdp = (LPNMRVDRAWPREVIEW)pNMHDR;
strText=slExcerpts.GetAt(slExcerpts.FindIndex(lpnmrvdp->iItem));
CReportCtrl& rc = m_rc;
CDC dc;
dc.Attach(lpnmrvdp->hDC);
if(lpnmrvdp->nState&RVIS_SELECTED)
dc.SetTextColor(::GetFocus()==rc.m_hWnd ? GetSysColor(COLOR_HIGHLIGHTTEXT):GetSysColor(COLOR_HIGHLIGHT));
else
dc.SetTextColor(GetSysColor(COLOR_HIGHLIGHT));
lpnmrvdp->rect.left += 40;
lpnmrvdp->rect.top += 2;
lpnmrvdp->rect.bottom -= 2;
dc.DrawText(strText, &lpnmrvdp->rect, DT_LEFT|DT_END_ELLIPSIS|DT_WORDBREAK|DT_NOPREFIX|DT_EXPANDTABS);
dc.Detach();
*pResult = FALSE;
}
//>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<
//*****************************************************
//Do the actual search
//using rs connection 09/10/2001
bool SRCH::DoSearch()
{
CString q,str,strWild;
long lRank,lTableID;
long debugcounter=0;
CString strKeep;
COleDateTime dtData;
CString strClient;
bool bNoMatch=false;
bool bFirst=true;
CWaitCursor wait;
BuildCriteria();
/*
if(rs==NULL)
{
rs = new GZRset("Error in Search.rs.DoSearch");
rs->SetConnect(m_pApp->strConnectString);
}
if(rs2==NULL)
{
rs2 = new GZRset("Error in Search.rs2.DoSearch");
rs2->SetConnect(m_pApp->strConnectString);
}
*/
//Empty previous srch results
q.Format("DELETE srch.*, srch.userid FROM srch "
"WHERE (((srch.userid)=%u));",m_pApp->m_lusrID);
rs2->Ex(q);
//each word has already been cleared
//cycle through each word, add to table srch
//add items and remove from table srch remove items
//until list is exhausted or srch is empty
//BUILD SRCH LIST FROM KEYWORDS
for(int x=0;x<m_nSWords;x++)
{
str=strList.GetAt(strList.FindIndex(x));
if(str.GetLength()>1)
{
if(bFirst==true)
{ //MAKE THE TABLE FROM THE FIRST WORD
//optimize query for performance, if don't need a wildcard search
//dont do it as adds about 10 times to length of time to search
strWild="=";
if(str.Find("%")!=-1)
strWild="Like";
//First word used to build initial list
q.Format("INSERT INTO srch ( tabletype, [tableid], keep, userid ) "
"SELECT DISTINCT [srchkey].[tabletype], [srchkey].[tableid], False AS keeper, %u AS [user] "
"FROM srchdict RIGHT JOIN srchkey ON [srchdict].[ID]=[srchkey].[wordid] "
"WHERE ((([srchdict].[word]) %s \"%s\") %s );",m_pApp->m_lusrID,strWild,strList.GetAt(strList.FindIndex(0)),m_strCriteria);
//m_pApp->ShowStuff(q);
/*
INSERT INTO srch ( tabletype, [tableid], keep, userid )
SELECT DISTINCT srchkey.tabletype, srchkey.tableid, False AS keeper, 1 AS [user]
FROM srchdict RIGHT JOIN srchkey ON srchdict.ID = srchkey.wordid
WHERE (((srchdict.word) Like "print*") AND ((srchkey.client)=268) AND ((DatePart("m",[srchkey].[date]))>=DatePart("m",Date())-5) AND ((DatePart("yyyy",[srchkey].[date]))=DatePart("yyyy",Date())));
*/
rs2->Ex(q);
bFirst=false;
}
else
{
if(str.Left(1)=="-")
{
strKeep="False";
//if processing a discard, flag all as true
q.Format("UPDATE srch SET srch.keep = True "
"WHERE (((srch.userid)=%u));",m_pApp->m_lusrID);
rs2->Ex(q);
}
else
{
strKeep="True";
//if processing a keeper, flag all as false
q.Format("UPDATE srch SET srch.keep = False "
"WHERE (((srch.userid)=%u));",m_pApp->m_lusrID);
rs2->Ex(q);
}
str.Remove('-');
//optimize query for performance, if don't need a wildcard search
//dont do it as adds about 10 times to length of time to search
strWild="=";
if(str.Find("%")!=-1)
strWild="Like";
//flag keepers
q.Format("UPDATE srch INNER JOIN (srchdict RIGHT JOIN "
"srchkey ON srchdict.ID = srchkey.wordid) ON "
"(srch.tableid = srchkey.tableid) AND "
"(srch.tabletype = srchkey.tabletype) "
"SET srch.keep = %s "
"WHERE (((srchdict.word) %s \"%s\") "
"AND ((srch.userid)=%u));",strKeep,strWild,str,m_pApp->m_lusrID);
rs2->Ex(q);
//Remove culls
q.Format("DELETE srch.*, srch.keep, srch.userid "
"FROM srch "
"WHERE (((srch.keep)=False) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs2->Ex(q);
//BUGBUG: POSSIBLE PROBLEM AREA REQUIRING FLUSHING
}//bFirst IF statement
//check to see if were still in business
q.Format("SELECT srch.userid FROM srch "
"WHERE (((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(rs->IsEmpty())
{
bNoMatch=true;
break;
}
}//if it's a deleted word
}//loop through all the words
//-------------------------------------------
if(!bNoMatch)
{
//Suck out excerpts and rank each item:
//PROBS
q.Format("SELECT srch.tableid,[probs].[brief], [probs].[notes], probs.created AS ddate, clients.id AS dclientid, IIf(IsNull([company]),[last] & \", \" & [first],[company]) AS dclient "
"FROM srch LEFT JOIN ((probs LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id) ON srch.tableid = probs.id "
"WHERE (((srch.tabletype)=0) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
//m_pApp->ShowStuff(q);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchAllTextFieldsAsOneString(&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=0) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
rs2->Ex(q);
}while(rs->MoveForward());
}
//AfxMessageBox("probs completed");
//LABOUR
q.Format("SELECT srch.tableid,labor.details, labor.stop AS ddate, clients.id AS dclientid, IIf(IsNull([company]),[last] & \", \" & [first],[company]) AS dclient "
"FROM (((srch LEFT JOIN labor ON srch.tableid = labor.id) LEFT JOIN probs ON labor.link = probs.id) LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id "
"WHERE (((srch.tabletype)=1) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
//Show(q);
rs->QueryReadOnly(q);
//AfxMessageBox("lab A");
//rs->MoveFirst();
/*
int xtest=0;
CString xstr;
*/
if(!rs->IsEmpty())
{
do{
rs->FetchAllTextFieldsAsOneString(&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
strClient.Replace("&"," and ");
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=1) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
rs2->Ex(q);
}while(rs->MoveForward());
}
//AfxMessageBox("subrepaircompleted");
//SUBREPAIR
q.Format("SELECT srch.tableid,subrepair.notes , subrepair.created AS ddate, clients.id AS dclientid, IIf(IsNull([company]),[last] & \", \" & [first],[company]) AS dclient "
"FROM (srch LEFT JOIN subrepair ON srch.tableid = subrepair.id) LEFT JOIN ((probs LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id) ON subrepair.link = probs.id "
"WHERE (((srch.tabletype)=2) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchAllTextFieldsAsOneString(&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=2) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
rs2->Ex(q);
}while(rs->MoveForward());
}
//AfxMessageBox("rentalscompleted");
//RENTALS
q.Format("SELECT srch.tableid,[rentals].[notes], [rentals].[loanedto], [rentals].[description], rentals.dateout AS ddate, [loanedto] & \" \" & IIf(IsNull([clients]![company]),[clients]![first] & \" \" & [clients]![last],[clients]![company]) AS dclient, clients.id AS dclientid "
"FROM (srch INNER JOIN rentals ON srch.tableid = rentals.id) LEFT JOIN clients ON rentals.clientlink = clients.id "
"WHERE (((srch.tabletype)=3) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchAllTextFieldsAsOneString(&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\",srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=3) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
rs2->Ex(q);
}while(rs->MoveForward());
}
//AfxMessageBox("clients completed");
//CLIENTS
q.Format(
"SELECT srch.tableid, clients.id AS dclientid,[generalnotes] , "
"[technotes] , [alert] , "
"[first] , [last] , [company] "
" , [mailaddress] , "
"[streetaddress] , [city] , "
"[stateprov] , [postal] , "
"[country] , [bizphone] , "
"[fax] , [email] , [acctnumber] , [phone2] , [phone3], "
"clients.created AS ddate, IIf(IsNull([clients]![company]),"
"[clients]![first] & \" \" & [clients]![last],[clients]![company]) AS dclient "
"FROM srch LEFT JOIN clients ON srch.tableid = clients.id "
"WHERE (((srch.tabletype)=4) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
// m_pApp->ShowStuff(q);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchAllTextFieldsAsOneString(&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=4) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
rs2->Ex(q);
}while(rs->MoveForward());
}
//CONTACTS
q.Format("SELECT srch.tableid, contacts.clientlink AS dclientid, contacts.notes , contacts.date AS ddate, IIf(IsNull([clients]![company]),[clients]![first] & \" \" & [clients]![last],[clients]![company]) AS dclient "
"FROM (srch LEFT JOIN contacts ON srch.tableid = contacts.id) LEFT JOIN clients ON contacts.clientlink = clients.id "
"WHERE (((srch.tabletype)=5) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchAllTextFieldsAsOneString(&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=5) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
rs2->Ex(q);
}while(rs->MoveForward());
}
}
//UNITS
q.Format("SELECT srch.tableid, units.client AS dclientid, "
"[units].[sn], [units].[id1],"
"[units].[id2],[units].[id3], "
"[units].[receipt], "
"[units].[notes], "
"[units].[description], IIf(IsNull([clients]![company]),[clients]![first] & \" \" & [clients]![last],[clients]![company]) AS dclient, IIf(IsNull([units].[purchasedate]),IIf(IsNull([units].[created]),IIf(IsNull([units].[modified]),#1/1/2000#,[units].[modified]),[units].[created]),[units].[purchasedate]) AS ddate "
"FROM (srch LEFT JOIN units ON srch.tableid = units.id) LEFT JOIN clients ON units.client = clients.id "
"WHERE (((srch.tabletype)=6) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchAllTextFieldsAsOneString(&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=6) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
rs2->Ex(q);
}while(rs->MoveForward());
}
//WORKORDER HEADER TABLE 7 added May 9th, 2001
q.Format("SELECT srch.tableid, [wo].[notes] , [wo].[invoice] , [wo].[clientrefnum] , "
"[wo].[clientcontact], [wo].[ourref] , wo.created AS ddate, clients.id AS dclientid, IIf(IsNull([company]),[last] & \", \" & [first],[company]) AS dclient "
"FROM srch LEFT JOIN (wo LEFT JOIN clients ON wo.client = clients.id) ON srch.tableid = wo.id "
"WHERE (((srch.tabletype)=7) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
/* q.Format("SELECT srch.tableid, units.client AS dclientid, \"SN:\" & [units].[sn] & \" \" & \"ID1:\" & [units].[id1] & \" ID2:\" & [units].[id2] & \" ID3:\" & [units].[id3] & \" Receipt#:\" & [units].[receipt] & \" NOTES:\" & [units].[notes] & \" DESCRIPTION:\" & [units].[description] AS [text], IIf(IsNull([clients]![company]),[clients]![first] & \" \" & [clients]![last],[clients]![company]) AS dclient, IIf(IsNull([units].[purchasedate]),IIf(IsNull([units].[created]),IIf(IsNull([units].[modified]),#1/1/2000#,[units].[modified]),[units].[created]),[units].[purchasedate]) AS ddate "
"FROM (srch LEFT JOIN units ON srch.tableid = units.id) LEFT JOIN clients ON units.client = clients.id "
"WHERE (((srch.tabletype)=6) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
*/
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchAllTextFieldsAsOneString(&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=7) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
rs2->Ex(q);
}while(rs->MoveForward());
}
//AfxMessageBox("part2completed");
//---------------------------------------------------------------------
/*
if(rs!=NULL)
{
delete rs;
rs=NULL;
}
if(rs2!=NULL)
{
delete rs2;
rs2=NULL;
}
*/
rs->Close();
rs2->Close();
//pConnection->Close();
//StopWatch("DoSearch part II",false);
if(bNoMatch)
return false;
return true;
}
/*
//>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<
//*****************************************************
//Do the actual search
bool SRCH::DoSearch()
{
CString q,str,strWild;
long lRank,lTableID;
long debugcounter=0;
CString strKeep;
COleDateTime dtData;
CString strClient;
bool bNoMatch=false;
bool bFirst=true;
CWaitCursor wait;
BuildCriteria();
//connection stuff here to avoid overhead
//of using one in gzrset
//create a pointer to a connection
_bstr_t strSQL;
_ConnectionPtr pConnection = NULL;
//instantiate it:
pConnection.CreateInstance(__uuidof(Connection));
//get connect string
_bstr_t strCnn(m_pApp->strConnectString);
//open the connection
pConnection->Open (strCnn, "", "", adConnectUnspecified);
//Empty previous srch results
q.Format("DELETE srch.*, srch.userid FROM srch "
"WHERE (((srch.userid)=%u));",m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->BeginTrans();
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
pConnection->CommitTrans();
//START OF SLOW LOOP
//StopWatch("",true);
//each word has already been cleared
//cycle through each word, add to table srch
//add items and remove from table srch remove items
//until list is exhausted or srch is empty
//BUILD SRCH LIST FROM KEYWORDS
for(int x=0;x<m_nSWords;x++)
{
str=strList.GetAt(strList.FindIndex(x));
if(str.GetLength()>1)
{
if(bFirst==true)
{ //MAKE THE TABLE FROM THE FIRST WORD
//optimize query for performance, if don't need a wildcard search
//dont do it as adds about 10 times to length of time to search
strWild="=";
if(str.Find("%")!=-1)
strWild="Like";
//First word used to build initial list
q.Format("INSERT INTO srch ( tabletype, [tableid], keep, userid ) "
"SELECT DISTINCT [srchkey].[tabletype], [srchkey].[tableid], False AS keeper, %u AS [user] "
"FROM srchdict RIGHT JOIN srchkey ON [srchdict].[ID]=[srchkey].[wordid] "
"WHERE ((([srchdict].[word]) %s \"%s\") %s );",m_pApp->m_lusrID,strWild,strList.GetAt(strList.FindIndex(0)),m_strCriteria);
//m_pApp->ShowStuff(q);
//convert the string to a bstr
strSQL=q;
//m_edSearchTerms.SetWindowText(q);
//execute the query
pConnection->BeginTrans();
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
pConnection->CommitTrans();
bFirst=false;
}
else
{
if(str.Left(1)=="-")
{
strKeep="False";
//if processing a discard, flag all as true
q.Format("UPDATE srch SET srch.keep = True "
"WHERE (((srch.userid)=%u));",m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->BeginTrans();
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
pConnection->CommitTrans();
}
else
{
strKeep="True";
//if processing a keeper, flag all as false
q.Format("UPDATE srch SET srch.keep = False "
"WHERE (((srch.userid)=%u));",m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->BeginTrans();
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
pConnection->CommitTrans();
}
str.Remove('-');
//optimize query for performance, if don't need a wildcard search
//dont do it as adds about 10 times to length of time to search
strWild="=";
if(str.Find("%")!=-1)
strWild="Like";
//flag keepers
q.Format("UPDATE srch INNER JOIN (srchdict RIGHT JOIN "
"srchkey ON srchdict.ID = srchkey.wordid) ON "
"(srch.tableid = srchkey.tableid) AND "
"(srch.tabletype = srchkey.tabletype) "
"SET srch.keep = %s "
"WHERE (((srchdict.word) %s \"%s\") "
"AND ((srch.userid)=%u));",strKeep,strWild,str,m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->BeginTrans();
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
pConnection->CommitTrans();
//Remove culls
q.Format("DELETE srch.*, srch.keep, srch.userid "
"FROM srch "
"WHERE (((srch.keep)=False) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->BeginTrans();
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
pConnection->CommitTrans();
//removed dec 9th obsolete call?
//rs->Flush(pConnection);
}//bFirst IF statement
//check to see if were still in business
q.Format("SELECT srch.userid FROM srch "
"WHERE (((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(rs->IsEmpty())
{
bNoMatch=true;
break;
}
}//if it's a deleted word
}//loop through all the words
//StopWatch("DoSearch Part I",false);
//**************** END OF SLOW LOOP ************
//AfxMessageBox("part1completed");
//Delay(5);
//-------------------------------------------
if(!bNoMatch)
{
//Suck out excerpts and rank each item:
//PROBS
q.Format("SELECT srch.tableid,[probs].[brief] & \" \" & [probs].[notes] AS [text], probs.created AS ddate, clients.id AS dclientid, IIf(IsNull([company]),[last] & \", \" & [first],[company]) AS dclient "
"FROM srch LEFT JOIN ((probs LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id) ON srch.tableid = probs.id "
"WHERE (((srch.tabletype)=0) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
//m_pApp->ShowStuff(q);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchField("text",&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=0) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
}while(rs->MoveForward());
}
//AfxMessageBox("probs completed");
//LABOUR
q.Format("SELECT srch.tableid,labor.details AS [text], labor.stop AS ddate, clients.id AS dclientid, IIf(IsNull([company]),[last] & \", \" & [first],[company]) AS dclient "
"FROM (((srch LEFT JOIN labor ON srch.tableid = labor.id) LEFT JOIN probs ON labor.link = probs.id) LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id "
"WHERE (((srch.tabletype)=1) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
//Show(q);
rs->QueryReadOnly(q);
//AfxMessageBox("lab A");
//rs->MoveFirst();
if(!rs->IsEmpty())
{
do{
rs->FetchField("text",&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
strClient.Replace("&"," and ");
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=1) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
}while(rs->MoveForward());
}
//AfxMessageBox("subrepaircompleted");
//SUBREPAIR
q.Format("SELECT srch.tableid,subrepair.notes AS [text], subrepair.created AS ddate, clients.id AS dclientid, IIf(IsNull([company]),[last] & \", \" & [first],[company]) AS dclient "
"FROM (srch LEFT JOIN subrepair ON srch.tableid = subrepair.id) LEFT JOIN ((probs LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id) ON subrepair.link = probs.id "
"WHERE (((srch.tabletype)=2) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchField("text",&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=2) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
}while(rs->MoveForward());
}
//AfxMessageBox("rentalscompleted");
//RENTALS
q.Format("SELECT srch.tableid,[rentals].[notes] & \" \" & [rentals].[loanedto] & \" \" & [rentals].[description] AS [text], rentals.dateout AS ddate, [loanedto] & \" \" & IIf(IsNull([clients]![company]),[clients]![first] & \" \" & [clients]![last],[clients]![company]) AS dclient, clients.id AS dclientid "
"FROM (srch INNER JOIN rentals ON srch.tableid = rentals.id) LEFT JOIN clients ON rentals.clientlink = clients.id "
"WHERE (((srch.tabletype)=3) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchField("text",&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\",srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=3) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
}while(rs->MoveForward());
}
//AfxMessageBox("clients completed");
//CLIENTS
q.Format("SELECT srch.tableid, clients.id AS dclientid,[generalnotes] & \" \" & [technotes] & \" \" & [alert] & \" \" & [first] & \" \" & [last] & \" \" & [company] & \" \" & [mailaddress] & \" \" & [streetaddress] & \" \" & [city] & \" \" & [stateprov] & \" \" & [postal] & \" \" & [country] & \" \" & [bizphone] & \" \" & [fax] & \" \" & [email] & \" \" & [acctnumber] AS [text], clients.created AS ddate, IIf(IsNull([clients]![company]),[clients]![first] & \" \" & [clients]![last],[clients]![company]) AS dclient "
"FROM srch LEFT JOIN clients ON srch.tableid = clients.id "
"WHERE (((srch.tabletype)=4) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
// m_pApp->ShowStuff(q);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchField("text",&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=4) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
}while(rs->MoveForward());
}
//CONTACTS
q.Format("SELECT srch.tableid, contacts.clientlink AS dclientid, contacts.notes AS [text], contacts.date AS ddate, IIf(IsNull([clients]![company]),[clients]![first] & \" \" & [clients]![last],[clients]![company]) AS dclient "
"FROM (srch LEFT JOIN contacts ON srch.tableid = contacts.id) LEFT JOIN clients ON contacts.clientlink = clients.id "
"WHERE (((srch.tabletype)=5) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchField("text",&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=5) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
}while(rs->MoveForward());
}
}
//UNITS
q.Format("SELECT srch.tableid, units.client AS dclientid, \"SN:\" & [units].[sn] & \" \" & \"ID1:\" & [units].[id1] & \" ID2:\" & [units].[id2] & \" ID3:\" & [units].[id3] & \" Receipt#:\" & [units].[receipt] & \" NOTES:\" & [units].[notes] & \" DESCRIPTION:\" & [units].[description] AS [text], IIf(IsNull([clients]![company]),[clients]![first] & \" \" & [clients]![last],[clients]![company]) AS dclient, IIf(IsNull([units].[purchasedate]),IIf(IsNull([units].[created]),IIf(IsNull([units].[modified]),#1/1/2000#,[units].[modified]),[units].[created]),[units].[purchasedate]) AS ddate "
"FROM (srch LEFT JOIN units ON srch.tableid = units.id) LEFT JOIN clients ON units.client = clients.id "
"WHERE (((srch.tabletype)=6) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchField("text",&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=6) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
}while(rs->MoveForward());
}
//WORKORDER HEADER TABLE 7 added May 9th, 2001
q.Format("SELECT srch.tableid, [wo].[notes] & \" \" & [wo].[invoice] & \" \" & [wo].[clientrefnum] & \" \" & "
"[wo].[clientcontact] & \" \" & [wo].[ourref] AS [text], wo.created AS ddate, clients.id AS dclientid, IIf(IsNull([company]),[last] & \", \" & [first],[company]) AS dclient "
"FROM srch LEFT JOIN (wo LEFT JOIN clients ON wo.client = clients.id) ON srch.tableid = wo.id "
"WHERE (((srch.tabletype)=7) AND ((srch.userid)=%u));",m_pApp->m_lusrID);
rs->QueryReadOnly(q);
if(!rs->IsEmpty())
{
do{
rs->FetchField("text",&str);
rs->FetchField("tableid",&lTableID);
rs->FetchField("dclient",&strClient);
rs->FetchField("ddate",&dtData);
ExtractAndRank(&str,&lRank);
q.Format("UPDATE srch SET srch.date = #%s#, srch.client= \"%s\", srch.excerpt = \"%s\", srch.rank = %u "
"WHERE (((srch.tabletype)=7) AND ((srch.tableid)=%u) AND ((srch.userid)=%u));"
,dtData.Format(_T("%m/%d/%Y")),strClient,str,lRank,lTableID,m_pApp->m_lusrID);
//convert the string to a bstr
strSQL=q;
//execute the query
pConnection->Execute(strSQL, NULL, adExecuteNoRecords);
}while(rs->MoveForward());
}
//AfxMessageBox("part2completed");
//---------------------------------------------------------------------
rs->Close();
rs2->Close();
pConnection->Close();
//StopWatch("DoSearch part II",false);
if(bNoMatch)
return false;
return true;
}
*/
//****************************************************
//take the passed in raw text, return an excerpt of
//25 words or less through the same string
//and return a ranking from 1 to 100 for accuracy
//currently only wildcard accepted is *(%) which
//for this function is simply stripped out since it can
//is irrelevant for matching left or right side strings
//NOTE:Wildcard * not at either end is invalid and not
//supported here, will mis-match
bool SRCH::ExtractAndRank(CString *excerpt, long *rank)
{
CString str,strwindow,str2,leftpad,rightpad,tempexcerpt;
int x,curpos,counter,lowcount,lowword,leftwindow,rightwindow;
int rawlength,wordlength;
int prox,bestprox,bestleftwindow,bestrightwindow;//number of other words within proximity window
int curpos2;
bool anymatches=false;
//v 1.9.4.6 added 09-may-2005
//because being used before initialized
bestrightwindow=0;
bestleftwindow=0;
lowcount=9999;//seed with a high number
lowword=0;
bestprox=0;
rawlength=excerpt->GetLength();
tempexcerpt=*excerpt;
tempexcerpt.MakeLower();
//loop through all the search words
//finding the word with the least occurences
for(x=0;x<m_nSWords;x++)
{
str=strList.GetAt(strList.FindIndex(x));
str.Remove('%');
if(str.GetLength() > 1 && str.Left(1)!="-")
{
counter=0;
curpos=tempexcerpt.Find(str,0);
while (curpos!=-1)
{ anymatches=true;
counter++;
curpos=tempexcerpt.Find(str,curpos+1);
}
}
if(counter< lowcount && counter > 0)
{
lowcount=counter;
lowword=x;
}
if(counter==1)
break;
}
//scan text looking for window with most keywords in it.
str=strList.GetAt(strList.FindIndex(lowword));
//STrip wildcards
str.Remove('%');
str.Remove('_');
wordlength=str.GetLength();
curpos=tempexcerpt.Find(str,0);
//counter=0;
while (curpos!=-1)
{
//counter++;
//LEFTWINDOW
if(curpos<WORDWINDOW)
leftwindow=0;
else
leftwindow=curpos-WORDWINDOW;
//RIGHTWINDOW
if(rawlength > (curpos+wordlength+WORDWINDOW))
rightwindow=(curpos+wordlength+WORDWINDOW);
else
rightwindow=rawlength;
//fill strwindow with all the text within
//the window, hopefully centered on the keyword
strwindow=tempexcerpt.Mid(leftwindow,(rightwindow-leftwindow));
//how many of the other keywords appear within that window
//Loop through all the keywords, if they appear in the window
//then add one to the count, eventually will result in a %
prox=0;
for(x=0;x<m_nSWords;x++)
{
str2=strList.GetAt(strList.FindIndex(x));
str2.Remove('%');
if(str2.GetLength() > 1 && str.Left(1)!="-")
{
curpos2=tempexcerpt.Find(str2,leftwindow);
while(curpos2!=-1 && curpos2< rightwindow)
{
prox++;//count of how many of each of the keywords appear in window
curpos2=tempexcerpt.Find(str2,curpos2+1);
}
}
}
if(prox > bestprox)
{
bestprox=prox;
bestleftwindow=leftwindow;
bestrightwindow=rightwindow;
}
curpos=tempexcerpt.Find(str,curpos+1);
}
*rank=bestprox;//number of keywords within window
*excerpt=excerpt->Mid(bestleftwindow,(bestrightwindow-bestleftwindow));
if(bestleftwindow==0)
leftpad.Empty();
else
leftpad="...";
if(bestrightwindow==rawlength)
rightpad.Empty();
else
rightpad="...";
*excerpt=leftpad+*excerpt+rightpad;
excerpt->Replace("'","");
excerpt->Replace("\"","");
excerpt->Replace("&"," AND ");
excerpt->Replace("\r"," ");
excerpt->Replace("\n","");
if(anymatches==false)//problem
{
//AfxMessageBox("There is a problem with the search index tables. Do a full re-index before attempting to search again.");
excerpt->Format("< Search item no longer in this record - you should reindex soon >");
return false;
}
return true;
}
void SRCH::StopWatch(CString msg, bool bStart)
{
CString str;
int searchtime;
if(bStart)
swStart=COleDateTime::GetCurrentTime();
else
{
swEnd=COleDateTime::GetCurrentTime();
swSpan=swEnd-swStart;
searchtime=(int)swSpan.GetTotalSeconds();
str.Format("%s - %i seconds",msg,searchtime);
AfxMessageBox(str);
}
}
void SRCH::Delay(int nSeconds)
{
COleDateTime dtStart,dtEnd;
COleDateTimeSpan dtSpan;
dtStart=COleDateTime::GetCurrentTime();
do {
dtEnd=COleDateTime::GetCurrentTime();
dtSpan=dtEnd-dtStart;
}while(nSeconds > (int)dtSpan.GetTotalSeconds());
}
//BUILD CRITERIA FROM CLIENT AND DATE SELECTIONS
void SRCH::BuildCriteria()
{
int nDateCrit;
CString strClientCrit,strDateCrit,strStartDate,strEndDate,client;
COleDateTime dtStart,dtEnd;
m_dtDate.GetTime(dtStart);
m_dtDate2.GetTime(dtEnd);
strStartDate=dtStart.Format(_T("%m/%d/%Y"));
strEndDate=dtEnd.Format(_T("%m/%d/%Y"));
nDateCrit=m_cbDate.GetCurSel();
m_strCriteria.Empty();
client=m_cbClients.GetCurrentRowID();
if(client!="0")//theres a client
{
//process client criteria to criteria string
strClientCrit.Format(" AND ((srchkey.client)=%s) ",client);
}
if(nDateCrit >0) //0=Anytime
{
switch(nDateCrit)
{
case 1://between...
strDateCrit.Format(" AND ((srchkey.date) Between #%s# And #%s#) ",strStartDate,strEndDate);
break;
case 2: //after...
strDateCrit.Format(" AND ((srchkey.date) > #%s#) ",strStartDate);
break;
case 3: //before...
strDateCrit.Format(" AND ((srchkey.date) < #%s#) ",strStartDate);
break;
case 4:///in the last week
strDateCrit=" AND ((DatePart(\"ww\",[srchkey].[date]))=DatePart(\"ww\",Date())) AND ((DatePart(\"yyyy\",[srchkey].[date]))=DatePart(\"yyyy\",Date())) ";
break;
case 5: //in the last 2 weeks
strDateCrit=" AND ((DatePart(\"ww\",[srchkey].[date]))>=DatePart(\"ww\",Date())-1) AND ((DatePart(\"yyyy\",[srchkey].[date]))=DatePart(\"yyyy\",Date())) ";
break;
case 6://within the last 45 days
strDateCrit=" AND (([srchkey].[date])>= (DateAdd(\"d\",-45, Date()))) ";
break;
case 7://in the last month
strDateCrit=" AND ((DatePart(\"m\",[srchkey].[date]))=DatePart(\"m\",Date())) AND ((DatePart(\"yyyy\",[srchkey].[date]))=DatePart(\"yyyy\",Date())) ";
break;
case 8://in the last 3 months
strDateCrit=" AND ((DatePart(\"m\",[srchkey].[date]))>=DatePart(\"m\",Date())-2) AND ((DatePart(\"yyyy\",[srchkey].[date]))=DatePart(\"yyyy\",Date())) ";
break;
case 9: //in the last 6 months
strDateCrit=" AND ((DatePart(\"m\",[srchkey].[date]))>=DatePart(\"m\",Date())-5) AND ((DatePart(\"yyyy\",[srchkey].[date]))=DatePart(\"yyyy\",Date())) ";
break;
case 10: //in the last year
strDateCrit=" AND (( DatePart(\"yyyy\",[srchkey].[date]))=DatePart(\"yyyy\",Date() ))";
break;
case 11://in the last 2 years
strDateCrit=" AND (( DatePart(\"yyyy\",[srchkey].[date]))>=DatePart(\"yyyy\",Date() )-1)";
break;
}
//pdv->sqlcriteria+=str1;
}
m_strCriteria=strClientCrit + strDateCrit;
}
//SEARCH FOR NON_KEYWORD items
bool SRCH::DoNoKeywordSearch()
{
CString q,strCrit;
BuildCriteria();
m_strCriteria=m_strCriteria.Right(m_strCriteria.GetLength()-4);//strip off the AND
CWaitCursor wait;
//hack for no criteria and no keywords
if(!m_strCriteria.IsEmpty())
strCrit.Format("WHERE ( %s )",m_strCriteria);
else
strCrit.Empty();
/*
if(rs==NULL)
{
rs = new GZRset("Error in Search.rs.DoNoKeywordSearch");
rs->SetConnect(m_pApp->strConnectString);
}
*/
//Empty previous srch results
q.Format("DELETE srch.*, srch.userid FROM srch "
"WHERE (((srch.userid)=%u));",m_pApp->m_lusrID);
rs->Ex(q);
q.Format("INSERT INTO srch ( tabletype, [tableid], userid, [date], client, rank ) "
"SELECT DISTINCT srchkey.tabletype, srchkey.tableid, %u AS [user], srchkey.date, IIf(IsNull([company]),[last] & \", \" & [first],[company]), 100 "
"FROM (srchdict RIGHT JOIN srchkey ON srchdict.ID = srchkey.wordid) INNER JOIN clients ON srchkey.client = clients.id "
"%s;",m_pApp->m_lusrID,strCrit);
rs->Ex(q);
rs->Close();
return true;
}
//Display a text string
void SRCH::Show(CString strTxt)
{
CTED d;
d.m_strText=strTxt;
d.DoModal();
}
//SET ORDER BY CLAUSE DEPENDING ON CLICK HERE
void SRCH::OnColumnClick(NMHDR* pNMHDR, LRESULT* pResult)
{
int x;
CString str;
CFlatHeaderCtrl* header;
header=m_rc.GetHeaderCtrl();
//FIND CLICKED COLUMN
x=COLUMNCOUNT-header->GetHotIndex();
x--;//because zero based
bColumnSortAsc[x]=!bColumnSortAsc[x];
switch(x)
{//DESC
case 0://relevance
m_strOrderBy="ORDER BY srch.rank";
break;
case 1://source
m_strOrderBy="ORDER BY srch.tabletype";
break;
case 2://DATE
m_strOrderBy="ORDER BY srch.date";
break;
case 3://client
m_strOrderBy="ORDER BY srch.client";
break;
}
if(!bColumnSortAsc[x])
m_strOrderBy+=" DESC";
header->SetSortColumn(header->GetHotIndex(),bColumnSortAsc[x]);
*pResult = TRUE;//TRUE means we handled it here thanks anyway
FillView();
}
void SRCH::LayoutControls(void)
{
CRect rectCtl,rectStat;
GetWindowRect(rectStat);
int nOffset=rectStat.top;
int nFullRight=rectStat.right;
int nLastBottom,nRight;
int nHeight,nWidth;
float fData;
m_rc.GetWindowRect(rectCtl);
rectCtl.right=rectStat.right-13;
rectCtl.top-=nOffset;
rectCtl.left=13;
rectCtl.bottom=rectStat.bottom-168-nOffset;
m_rc.MoveWindow(rectCtl,TRUE);
nLastBottom=rectCtl.bottom;
nRight=rectCtl.right;
m_lblResults.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom+25;
rectCtl.top=rectCtl.bottom-nHeight;
m_lblResults.MoveWindow(rectCtl,TRUE);
nLastBottom=rectCtl.bottom;
/*
nRight=rectCtl.right;
m_btnEditWO.GetWindowRect(rectCtl);
nWidth=rectCtl.Width();
rectCtl.right=nRight;
rectCtl.left=nRight-nWidth;
rectCtl.top-=nOffset;
rectCtl.bottom-=nOffset;
m_btnEditWO.MoveWindow(rectCtl,TRUE);
*/
m_ckPreview.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
nWidth=rectCtl.Width();
rectCtl.right=nRight;
rectCtl.left=nRight-nWidth;
m_ckPreview.MoveWindow(rectCtl,TRUE);
m_lblKeyWords.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom+17;
rectCtl.top=rectCtl.bottom-nHeight;
m_lblKeyWords.MoveWindow(rectCtl,TRUE);
nLastBottom=rectCtl.bottom;//because it just changed and the others below need it
m_lblDateRange.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_lblDateRange.MoveWindow(rectCtl,TRUE);
//"BETWEEN"
m_lblBetween.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
nWidth=rectCtl.Width();
rectCtl.right=nFullRight*0.74;
rectCtl.left=(nFullRight*0.74)-nWidth;
m_lblBetween.MoveWindow(rectCtl,TRUE);
nLastBottom+=24;
m_edSearchTerms.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_edSearchTerms.MoveWindow(rectCtl,TRUE);
m_cbDate.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_cbDate.MoveWindow(rectCtl,TRUE);
//START DATE
m_dtDate.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
nWidth=rectCtl.Width();
rectCtl.right=nFullRight*0.81;
rectCtl.left=(nFullRight*0.81)-nWidth;
m_dtDate.MoveWindow(rectCtl,TRUE);
nLastBottom+=18;
m_lblBad.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_lblBad.MoveWindow(rectCtl,TRUE);
m_lblClients.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_lblClients.MoveWindow(rectCtl,TRUE);
//"AND"
m_lblAnd.GetWindowRect(rectCtl);
nWidth=rectCtl.Width();
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
rectCtl.right=nFullRight*0.725;
rectCtl.left=(nFullRight*0.725)-nWidth;
m_lblAnd.MoveWindow(rectCtl,TRUE);
nLastBottom+=24;
m_edBad.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_edBad.MoveWindow(rectCtl,TRUE);
m_cbClients.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
fData=(float)rectCtl.right/(float)nFullRight;
nHeight=rectCtl.Height();
nWidth=rectCtl.Width();
rectCtl.right=nFullRight*0.63875;
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_cbClients.MoveWindow(rectCtl,TRUE);
//END DATE
m_dtDate2.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
nWidth=rectCtl.Width();
rectCtl.right=nFullRight*0.8075;
rectCtl.left=(nFullRight*0.8075)-nWidth;
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_dtDate2.MoveWindow(rectCtl,TRUE);
m_btnSearch.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
//fData=(float)rectCtl.right/(float)nFullRight;
nHeight=rectCtl.Height();
nWidth=rectCtl.Width();
rectCtl.right=nFullRight*0.90875;
rectCtl.left=(nFullRight*0.90875)-nWidth;
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_btnSearch.MoveWindow(rectCtl,TRUE);
/*
m_btnRefresh.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_btnRefresh.MoveWindow(rectCtl,TRUE);
m_btnClientNotes.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_btnClientNotes.MoveWindow(rectCtl,TRUE);
m_btnPrint.GetWindowRect(rectCtl);
nHeight=rectCtl.Height();
rectCtl.bottom=nLastBottom;
rectCtl.top=rectCtl.bottom-nHeight;
m_btnPrint.MoveWindow(rectCtl,TRUE);*/
}
void SRCH::OnSize(UINT nType, int cx, int cy)
{
CFormView::OnSize(nType, cx, cy);
if(cx>m_nCX)//to cut down on number of times called
{
m_nCX=cx;
if(m_rc.m_hWnd)
{
LayoutControls();
}
}
}
BOOL SRCH::OnHelpInfo(HELPINFO* pHelpInfo)
{
WinHelp (0x00020000 + IDD_SRCH_FORM,HELP_CONTEXT);
return TRUE;
}
void SRCH::OnCbnSelchangeCbclients()
{
// TODO: Add your control notification handler code here
}
Reference in New Issue View Git Blame Copy Permalink