<%@ Language=VBScript %>
TEAMS SEARCH
<%
G_Type1=Request("terms_connection")
G_Type2=Request("terms_connection2")
G_Term1=Request("term1")
G_Item1=Request("kouzo1")
G_Method1=Request("method1")
G_Term2=Request("term2")
G_Item2=Request("kouzo2")
G_Method2=Request("method2")
G_Term3=Request("term3")
G_Item3=Request("kouzo3")
G_Method3=Request("method3")
G_Can=UCase(Request("gCan"))
gDispImage=UCase(Request("gDispImage"))
varResearcherID_fk=Request.Form("txtUserName")
vartxtUserPwd=Request.Form("txtUserPwd")
'lTMSUserID=Request.Form("txtUserName")
id=Request.QueryString("id")
gvarResearcherID_fk=Request.Form("lvarResearcherID_fk")
gvartxtUserPwd=Request.Form("gtxtUserPwd")
'lTMSUserID=Request.Form("lvarResearcherID_fk")
varTotal=Request.Form("g_Total")
lswDumaanNa = False
If id=1 then
'frmAction="http://10.10.0.9/TMSearchSimilar/searchText.asp?id2=2"
frmAction="searchText2.asp"
End If
'==
id2=Request.Form("id2")
If id2=2 then
varSimCount_tx =Trim(Request.Form("g_SimCount_tx"))
varMainAppID_fk=Trim(Request.Form("g_MainAppID_fk"))
lcheckcount = 0
swOk = "N"
For intctr = 1 to varTotal
varCheckBox = Request("g_checkbox" & intctr)
If varCheckBox = "on" then
lcheckcount = lcheckcount + 1
End If
Next
If Len(varSimCount_tx) <> 0 AND Len(varMainAppID_fk) <> 0 Then
If lcheckcount = 0 then
%>
No boxes have been checked!
<%
Response.end
Else
swOk = "Y"
End If
End If
End If
if swOK = "Y" then
On Error Resume next
Err.Clear
Set DB=Server.CreateObject("ADODB.Connection")
'DB.ConnectionString = "Provider=SQLOLEDB;Data Source=10.10.0.9;Database=dbTEAMSApps;UID=rptuser;PWD=rptuser;"
'DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=true;User ID=sa;Password=sa;Database=dbTEAMSApps;Data Source=10.10.0.9"
DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=true;User ID=" & gvarResearcherID_fk & ";Password=" & gvartxtUserPwd & ";Database=dbTEAMSApps;Data Source=10.10.0.9"
DB.open
Set DBCommon=Server.CreateObject("ADODB.Connection")
DBCommon.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=true;User ID=rptuser;Password=rptuser;Database=dbTEAMSCommon;Data Source=10.10.0.9"
DBCommon.open
If DB.Errors.Count > 0 Then
Set objErr = Server.CreateObject("ADODB.Errors")
For Each objErr In DB.Errors
If objErr.Number <> 0 then
Response.Write "Number: " & objErr.Number & "
"
blnCritical = True
End IF
Next
Set objErr = Nothing
If blnCritical then
Response.End
End IF
End If
strSQL = "SELECT TMSUSerID FROM dbTEAMSCommon..TMSearchUSer WHERE TMSUSERID ='" & varResearcherID_fk & "' "
Set oExamRS = DB.Execute(strSQL)
If Not oExamRS.EOF Then
'Response.write "This username has already been used. Please try another one!"
'response.end
Else
strSQL = "INSERT INTO dbTEAMSCommon..TMSearchUSer(TMSUSerID) VALUES('" & varResearcherID_fk & "')"
Set oRS = DB.Execute(strSQL)
End IF
oRS.Close
Set oRS=Nothing
On Error Resume next
For intctr = 1 to varTotal
varCheckBox = Request.Form("g_checkbox" & intctr)
varSimAppID_fk=Request.Form("g_serno" & intctr)
If varCheckBox = "on" then
if Err.number = 0 then
If TRIM(varMainAppID_fk) <> "" Then
'Look first in tmExamination if the AppL# is assigned to the logged Researcher
strSQL = "SELECT ResearcherID_fk, Research_dt, Resr2ndID_fk, Res2nd_dt FROM tmExamination WHERE AppID_fk='" & varMainAppID_fk &"' " & _
" AND (Research_dt IS NULL OR Res2nd_dt IS NULL) "
Set oExamRS = DB.Execute(strSQL)
'==
If DB.Errors.Count > 0 Then
Set objErr = Server.CreateObject("ADODB.Errors")
For Each objErr In DB.Errors
If objErr.Number <> 0 then
Response.Write "Number: " & objErr.Number & "
"
blnCritical = True
End IF
Next
Set objErr = Nothing
If blnCritical then
Response.End
End IF
End If
'==
If Not oExamRS.EOF Then
' if Len(oExamRS.fields("ResearcherID_fk"))= 0 OR IsNull(oExamRS.fields("ResearcherID_fk"))And _
' Len(oExamRS.fields("Resr2ndID_fk"))= 0 OR IsNull(oExamRS.fields("Resr2ndID_fk"))Then
if Len(oExamRS.fields("ResearcherID_fk"))= 0 And Len(oExamRS.fields("Resr2ndID_fk"))= 0 Then
lswResearcher = False
Else
lswResearcher = True
End If
If Len(TRIM(UCASE(oExamRS.fields("Resr2ndID_fk")))) = 0 then
if TRIM(UCASE(oExamRS.fields("ResearcherID_fk"))) <> TRIM(UCASE(gvarResearcherID_fk)) then
'Response.write "Either the Application No. is NOT assigned to you OR it was already tag as FINISHED FOR SEARCHED!"
%>
1The Application No. is NOT ASSIGNED to you for 1st search!
<%
Response.end
End If
Else
if TRIM(UCASE(oExamRS.fields("Resr2ndID_fk"))) <> TRIM(UCASE(gvarResearcherID_fk)) then
'Response.write "Either the Application No. is NOT assigned to you OR it was already tag as FINISHED FOR SEARCHED!"
%>
2The Application No. is NOT ASSIGNED to you for 2nd search!
<%
End If
End If
Else
oExamRS.Close
Set oExamRS=Nothing
'Response.Write "The Application No. does not exist!"
%>
The Application No. DOES NOT exist!
<%
Response.end
End If
If Not IsNull(oExamRS.fields("Res2nd_dt")) And Len(oExamRS.fields("Res2nd_dt")) <> 0 Then
%>
The Application No. was already tag as FINISHED FOR 2nd SEARCHED!
<%
Response.End
Else 'check for 1st search
If Trim(varSimCount_tx) = "1" Then
If (Not IsNull(oExamRS.fields("Research_dt")) And Len(oExamRS.fields("Research_dt")) <> 0) Then
%>
The Application No. was already tag as FINISHED FOR 1st SEARCHED!
<%
Response.End
End If
End If
End If
End if
If lswResearcher = True Then
strSQL = "Exec usp_tmSimilar '" + varMainAppID_fk + "','" + varSimAppID_fk + "','" + varSimCount_tx + "' "
'strSQL = "INSERT INTO tmSimilar(MainAppID_fk,SimAppID_fk,SimCount_tx) VALUES('" & varMainAppID_fk & "','" & varSimAppID_fk & "','" & varSimCount_tx & "')"
End If
if Err.number = 0 then
Set oRS = DB.Execute(strSQL)
On Error Resume Next
if Err.number <> 0 then
oRS.Close
Set oRS=Nothing
DB.Close
Set DB=Nothing
Response.Write "Open Error has occured [ERROR:" & Err.source & Err.Number & "]"
On Error Resume Next
ElseIf Err.number = -2147217900 then
oRS.Close
Set oRS=Nothing
DB.Close
Set DB=Nothing
Response.Write "Open Error has occured [ERROR:" & Err.source & Err.Number & "]"
On Error Resume Next
Else
'Response.write "Searched Marks have been saved succesfully!!"
lswSave = True
lswDumaanNa = True
On Error Resume Next
end if
else
oRS.Close
Set oRS=Nothing
DB.Close
Set DB=Nothing
Response.Write "Open Error has occured [ERROR:" & Err.source & Err.Number & "]"
On Error Resume Next
end if
Else
Response.Write "Connection Error!"
End if
End IF
Next
oRS.Close
Set oRS=Nothing
DB.Close
Set DB=Nothing
End If 'swOK
'==
%>
<%
'Main Body
'Modified : COF - August 2003
'********************************************
If lswDumaanNa = True Then
'Response.Write "Searched Marks have been saved succesfully!"
If lswResearcher = True Then
%>
Searched Marks have been saved succesfully!
<%
Response.end
Else
%>
3The Application No. is NOT ASSIGNED to you!
<%
End If
End If
'oRS.Close
'Set oRS=Nothing
strSQLREC = "SELECT * " &_
" FROM tmintsearchgoods WHERE"
'Response.write strSQLREC
'strSQLREC = "SELECT SERNO, TRDMRK, APPLICANT, NCCLS,FILENAME_TX " &_
'strSQLCNT="SELECT COUNT(*) AS CNT FROM vwIntSearch WHERE"
strSQLCNT="SELECT COUNT(DISTINCT(SERNO)) AS CNT FROM tmintsearchgoods WHERE"
cr=0
G_ctr=G_ctr+1
dim marr(3,100)
strSQLTOTAL=""
'--for strings only-- START
if (Len(G_Term1) <> 0 ) then
strSQLTARM=""
fE = True
wT=1
wLen=Len(G_Term1)
On error Resume next
Do while (wLen >= wT)
if (Mid(G_Term1,wT,1) = ",") then 'Search Token
wT=wT+1
else 'Get Token
Token="" 'CT#4
Do while ( (wLen >= wT) AND (Mid(G_Term1,wT,1) <> ",") )
if (Mid(G_Term1,wT,1) = "'") then
Token=Token + "''"
else
Token=Token + Mid(G_Term1,wT,1)
end if
wT=wT+1
Loop
marr(1,cr)=token
cr=cr+1
if ( Len(strSQLTARM) <> 0 ) then
strSQLTARM = strSQLTARM + " OR "
end if
if (G_Method1="exact") then
strSQLTARM=strSQLTARM + "(" + G_Item1 + " LIKE '" + TRIM(Token) + "')"
end if
if (G_Method1="contains") then
strSQLTARM=strSQLTARM + "(" + G_Item1 + " LIKE '%" + TRIM(Token) + "%')"
end if
if (G_Method1="like") then
strSQLTARM=strSQLTARM + "(" + G_Item1 + " LIKE '" + TRIM(Token) + "%')"
end if
end if
Loop
strSQLTOTAL = strSQLTOTAL + strSQLTARM
end if
if (Len(G_Term2) <> 0 ) then
cr=0
strSQLTARM=""
fE = True
wT=1
wLen=Len(G_Term2)
Do while (wLen >= wT)
if (Mid(G_Term2,wT,1) = ",") then 'Search Token
wT=wT+1
else 'Get Token
Token="" 'CT#4
Do while ( (wLen >= wT) AND (Mid(G_Term2,wT,1) <> ",") )
if (Mid(G_Term2,wT,1) = "'") then
Token=Token + "''"
else
Token=Token + Mid(G_Term2,wT,1)
end if
wT=wT+1
Loop
marr(2,cr)=token
cr=cr+1
if ( Len(strSQLTARM) <> 0 ) then
strSQLTARM = strSQLTARM + " OR "
end if
if (G_Method1="exact") then
strSQLTARM=strSQLTARM + "(" + G_Item2 + " LIKE '" + TRIM(Token) + "')"
end if
if (G_Method1="contains") then
strSQLTARM=strSQLTARM + "(" + G_Item2 + " LIKE '%" + TRIM(Token) + "%')"
end if
if (G_Method1="like") then
strSQLTARM=strSQLTARM + "(" + G_Item2 + " LIKE '" + TRIM(Token) + "%')"
end if
end if
Loop
if strSQLTOTAL <> "" then 'CT#4
strSQLTOTAL = strSQLTOTAL + " " + G_Type1 + " (" + strSQLTARM + ")"
else 'CT#4
strSQLTOTAL = strSQLTOTAL + "(" + strSQLTARM + ")" 'CT#4
end if 'CT#4
end if
if (Len(G_Term3) <> 0 ) then
cr=0
strSQLTARM=""
fE = True
wT=1
wLen=Len(G_Term3)
Do while (wLen >= wT)
if (Mid(G_Term3,wT,1) = ",") then
wT=wT+1
else
Token="" 'CT#4
Do while ( (wLen >= wT) AND (Mid(G_Term3,wT,1) <> ",") )
if (Mid(G_Term3,wT,1) = "'") then
Token=Token + "''"
else
Token=Token + Mid(G_Term3,wT,1)
end if
wT=wT+1
Loop
marr(3,cr)=token
cr=cr+1
if ( Len(strSQLTARM) <> 0 ) then
strSQLTARM = strSQLTARM + " OR "
end if
if (G_Method1="exact") then
strSQLTARM=strSQLTARM + "(" + G_Item3 + " LIKE '" + TRIM(Token) + "')"
end if
if (G_Method1="contains") then
strSQLTARM=strSQLTARM + "(" + G_Item3 + " LIKE '%" + TRIM(Token) + "%')"
end if
if (G_Method1="like") then
strSQLTARM=strSQLTARM + "(" + G_Item3 + " LIKE '" + TRIM(Token) + "%')"
end if
end if
Loop
if strSQLTOTAL <> "" then 'CT#4
strSQLTOTAL = strSQLTOTAL + " " + G_Type2 + " (" + strSQLTARM + ")"
else 'CT#4
strSQLTOTAL = strSQLTOTAL + "(" + strSQLTARM + ")" 'CT#4
end if 'CT#4
end if
if strSQLTOTAL <> "" then
strSQL=strSQL + " (" + strSQLTOTAL + ")"
end if
'--for strings only-- END
'Response.write strSQLTOTAL
'================================
'strSQLREC = strSQLREC + strSQL + " ORDER BY SERNO ASC"
'strSQLCNT = strSQLCNT + strSQL
if g_Can = "N" then
strSQLREC = strSQLREC + strSQL + " And AppStat_tx <> 'Deemed Cancelled' ORDER BY SERNO ASC"
strSQLCNT = strSQLCNT + strSQL + " And AppStat_tx <> 'Deemed Cancelled'"
Else
strSQLREC = strSQLREC + strSQL + " ORDER BY SERNO ASC"
strSQLCNT = strSQLCNT + strSQL
End If
Total=0
DispC=0
On Error Resume next
Err.Clear
Set DB=Server.CreateObject("ADODB.Connection")
Set DBCommon=Server.CreateObject("ADODB.Connection")
'DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=true;User ID=rptuser;Password=rptuser;Database=dbTEAMSApps;Data Source=10.10.0.9"
'DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=true;User ID=cofernandez;Password=cofernandez;Database=dbTEAMSApps;Data Source=10.10.0.9"
'DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=true;User ID=vgalo;Password=vgalo;Database=dbTEAMSApps;Data Source=10.10.0.9"
DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=true;User ID=" & varResearcherID_fk & ";Password=" & vartxtUserPwd & ";Database=dbTEAMSApps;Data Source=10.10.0.9"
DBCommon.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=true;User ID=rptuser;Password=rptuser;Database=dbTEAMSCommon;Data Source=10.10.0.9"
'DB.ConnectionString = "Provider=SQLOLEDB;Data Source=10.10.0.9;Database=dbTEAMSApps;UID=rptuser;PWD=rptuser;"
'DB.ConnectionString = "Provider=SQLOLEDB;Data Source=TISS_SERVER;Database=dbTEAMSApps;UID=sa;PWD=sa;"
DB.open
DBCommon.open
prmoption1 = G_Item1
prmoption2 = G_Item2
prmoption3 = G_Item3
prmmethod1 = G_Method1
prmmethod2 = G_Method2
prmmethod3 = G_Method3
prmkeyword1 = G_Term1
prmkeyword2 = G_Term2
prmkeyword3 = G_Term3
prmoperator1 = G_Type1
prmoperator2 = G_Type2
'---
'prmoption1 = "trdmrk"
'prmoption2 = "applicant"
'prmoption3 = "trdmrk"
'prmmethod1 = "exact"
'prmmethod2 = "contains"
'prmmethod3 = "like"
'prmkeyword1 = "honda"
'prmkeyword2 = "paramount"
'prmkeyword3 = "lifebuoy"
'prmoperator1 = "OR"
'prmoperator2 = "OR"
if Err.number = 0 then '3 DB Error
if Err.number = 0 then '2 DB Error
strSQLREC = "Exec sp_tmIntSearchNew '" + prmoption1 + "','" + prmoption2 + "','" + prmoption3 + "','" + prmmethod1 + "','" + prmmethod2 + "','" + prmmethod3 + "','" + prmkeyword1 + "','" + prmkeyword2 + "','" + prmkeyword3 + "','" + prmoperator1 + "','" + prmoperator2 + "' " 'cynthia
strSQLCNT = "Exec usp_tmIntSearchNew '" + prmoption1 + "','" + prmoption2 + "','" + prmoption3 + "','" + prmmethod1 + "','" + prmmethod2 + "','" + prmmethod3 + "','" + prmkeyword1 + "','" + prmkeyword2 + "','" + prmkeyword3 + "','" + prmoperator1 + "','" + prmoperator2 + "' " 'cynthia
Set CntRec = DB.Execute(strSQLCNT)
Set Rec = DB.Execute(strSQLREC) 'orig SQL
Total=CntRec.Fields("CNT")
if Err.number=0 then '1 DB Error
if Rec.EOF=False then 'AND Total <> 0
Total=CntRec.Fields("CNT")
'total = 2000
if total>=5000 then
%>
SEARCH RESULT : Found
<%=Total%> item(s)
Your search request exceeds 5000
results. Please trim down your search items and Search Again.