createjob.asp example
<%@ Language=VBScript %>
<%
'------------------FOR DEMO USE ONLY----------------------------------
'-------- Copyright by Artem Saveliev artem@savelev.com --------------
'---------------------------------------------------------------------
Server.ScriptTimeout = 600
if cint(session("userlevel"))<3 then
Response.Redirect "/admin/index.asp?pgname="&Request.ServerVariables("URL")
end if
%>
<!--#include virtual="/html/htmllib.asp"-->
<!--#include virtual="/html/edittable.asp"-->
<!--#include file="menu.asp"-->
<%
dim add,remove,references
add = false
remove = false
set con = getcon()
con.CommandTimeout = 600
if Request.Form("add") <> "" then
add = true
elseif Request.Form("remove") <> "" then
remove = true
end if
if add or remove then
if Request.Form("reference") <> "" then
references = split(Request.Form("reference"),",")
for i = 0 to ubound(references)
references(i) = "'"&trim(sqlstr(references(i)))&"'"
next
references = join(references,",")
if add then
sql = "select gid into #gids from guser join vendorreferral on xcompid=vendorid "&_
"where referral in ("&references&"); "&_
"exec createcrmids; "&_
"insert into crmprequeue select "&session("guserid")&",contactid from #crmids; drop table #gids;"
else
sql = "delete from crmprequeue where userid = "&session("guserid")&_
" and exists (select crmdata.contactid from crmprequeue Q join crmdata on Q.contactid=crmdata.contactid "&_
"join guser on datatype = 5 and datavalue = gid join master on xcompid = vendor "&_
"join vendorreferral on xcompid=vendorid and referral in ("&references&") "&_
"where Q.userid = "&session("guserid")&" and crmprequeue.contactid=crmdata.contactid and datatype = 5)"
end if
end if
if Request.Form("naics") <> "" then
naics = sqlstr(Request.Form("naics"))
if add then
sql = "select gid into #gids from guser join master on xcompid=vendor "&_
"where naics like '%"&naics&"%'; "&_
"exec createcrmids; "&_
"insert into crmprequeue select "&session("guserid")&",contactid from #crmids; drop table #gids;"
else
sql = "delete from crmprequeue where userid = "&session("guserid")&_
" and exists (select crmdata.contactid from crmprequeue Q join crmdata on Q.contactid=crmdata.contactid "&_
"join guser on datatype = 5 and datavalue = gid join master on xcompid = vendor "&_
"where Q.userid = "&session("guserid")&" and crmprequeue.contactid=crmdata.contactid and datatype = 5 and naics like '%"&naics&"%')"
end if
end if
if Request.Form("mail") <> "" or Request.Form("startdate") <> "" or Request.Form("enddate") <> "" then
if add then
sql = "from crmcontactdata where"
if Request.Form("mail") <> "" then sql = sql & " contactsubtype in ("&sqlstr(Request.Form("mail"))&") and"
if Request.Form("startdate") <> "" then sql = sql & " contactdate > convert(datetime,'" & sqlstr(Request.Form("startdate")) & "') and"
if Request.Form("enddate") <> "" then sql = sql & " contactdate < convert(datetime,'" & sqlstr(dateadd("d",1,Request.Form("enddate"))) & "') and"
sql = "insert into crmprequeue select " & session("guserid") & ",contactid " & left(sql,len(sql)-3)
else
sql = "delete crmprequeue from crmprequeue "&_
"join crmcontactdata on crmprequeue.contactid = crmcontactdata.contactid "&_
"where crmprequeue.userid = "&session("guserid") & " and contacttype=1"
if Request.Form("mail") <> "" then sql = sql & " and contactsubtype in ("&sqlstr(Request.Form("mail"))&")"
if Request.Form("startdate") <> "" then sql = sql & " and contactdate > convert(datetime,'" & sqlstr(Request.Form("startdate")) & "')"
if Request.Form("enddate") <> "" then sql = sql & " and contactdate < convert(datetime,'" & sqlstr(dateadd("d",1,Request.Form("enddate"))) & "')"
end if
end if
if Request.Form("curstatus") <> "" then
curstatus = split(Request.Form("curstatus"),",")
for i = 0 to ubound(curstatus)
curstatus(i) = cint(curstatus(i))
next
curstatus = join(curstatus,",")
if add then
sql = "select gid into #gids from guser join master on xcompid=vendor "&_
"where curstatus in ("&curstatus&"); "&_
"exec createcrmids; "&_
"insert into crmprequeue select "&session("guserid")&",contactid from #crmids; drop table #gids;"
else
sql = "delete from crmprequeue where " &_
"userid = "&session("guserid") & " and " &_
"contactid in (select crmdata.contactid from crmprequeue Q join crmdata on Q.contactid=crmdata.contactid "&_
"join guser on datatype = 5 and datavalue = gid join master on xcompid = vendor "&_
"where Q.userid = "&session("guserid") &" and datatype = 5 and curstatus in ("&curstatus&"))"
end if
end if
if Request.Form("customsite") <> "" then
allbuyers = false
sites = split(Request.Form("customsite"),",")
for i = 0 to ubound(sites)
if sites(i) = "0" then allbuyers = true
sites(i) = "'"&trim(sqlstr(sites(i)))&"'"
next
sites = join(sites,",")
if add then
if allbuyers then
sql = "select gid into #gids from guser "&_
"where xtype='buyer'; "&_
"exec createcrmids; "&_
"insert into crmprequeue select "&session("guserid")&",contactid from #crmids; drop table #gids;"
else
sql = "select gid into #gids from guser join buyerregistration on gid=userid "&_
"where xtype='buyer' and site in ("&sites&"); "&_
"exec createcrmids; "&_
"insert into crmprequeue select "&session("guserid")&",contactid from #crmids; drop table #gids;"
end if
else
if allbuyers then
sql = "delete from crmprequeue where userid = "&session("guserid")&_
" and contactid in (select crmdata.contactid from crmprequeue Q join crmdata on Q.contactid=crmdata.contactid join guser on "&_
"datatype = 5 and datavalue = gid "&_
"where Q.userid="&session("guserid")&" and xtype='buyer')"
else
sql = "delete from crmprequeue where userid = "&session("guserid")&_
" and contactid in (select crmdata.contactid from crmprequeue Q join crmdata on Q.contactid=crmdata.contactid join guser on "&_
"datatype = 5 and datavalue = gid join buyerregistration on gid=buyerregistration.userid "&_
"where Q.userid="&session("guserid")&" and xtype='buyer' and site in ("&sites&"))"
end if
end if
end if
if Request.Form("primeid") <> "" then
sites = split(Request.Form("primeid"),",")
for i = 0 to ubound(sites)
sites(i) = clng(sites(i))
next
sites = join(sites,",")
if add then
sql = "select gid into #gids from guser join prime on xcompid=primeid "&_
"where xtype='primeuser' and primeid in ("&sites&"); "&_
"exec createcrmids; "&_
"insert into crmprequeue select "&session("guserid")&",contactid from #crmids; drop table #gids;"
else
sql = "delete from crmprequeue where userid = "&session("guserid")&_
" and contactid in (select contactid from crmdata join guser on "&_
"datatype = 5 and datavalue = gid join prime on xcompid=primeid "&_
"where xtype='primeuser' and primeid in ("&sites&"))"
end if
end if
if sql <> "" then
if add then
on error resume next
con.execute("create table #crmids (contactid int)")
if err > 0 then con.execute("delete * from #crmids")
on error goto 0
end if
con.execute(sql)
if add then con.execute("drop table #crmids")
end if
con.close()
elseif Request.Form("deleteall") <> "" then
sql = "delete from crmprequeue where userid="&session("guserid")
con.execute(sql)
con.close()
end if
%>
<HTML>
<HEAD>
<!--#include virtual="/html/jsverification.asp"-->
<SCRIPT LANGUAGE="JavaScript" SRC="/html/calendar.js"></SCRIPT>
</HEAD>
<style>
.selection
{
font-family:sans-serif;
font-size:25px;
}
</style>
<script>
function panel(id)
{
var panelsarr = new Array("suppliers","buyers","accounts");
for(var i=0;i<panelsarr.length;i++)
document.getElementById(panelsarr[i]).style.display = "none";
document.getElementById(panelsarr[id]).style.display = "block";
}
</script>
<BODY>
<%header()
call items(0)
%>
<h1>Prepare list of contacts</h1>
<form action="filterjob.asp" method=post id=form2 name=form2>
<%call systemvals()
set con = getcon()
set rs = con.execute("select count(*) from crmprequeue where userid = "&session("guserid"))
Response.Write "Contacts currently selected: "&rs(0)
if rs(0) > 0 then
%>
<input type=submit value="Next Step >>">
<%
end if
rs.close
set rs = nothing
%>
</form>
<form action="<%=Request.ServerVariables("URL")%>" method=post onsubmit="filtered.value='false'" id=form1 name=form1>
<%call systemvals()%>
<table border=1>
<tr><td>Add more contacts:</td></tr>
<tr><td>
<table width=100%><tr>
<td background="/images/ggrad.gif" align=center style="cursor:pointer;" onclick="panel(0)"><span class="selection"><img src="/images/transparent.gif" height=30 width=1 align=absmiddle>Suppliers</span></td>
<td background="/images/ggrad.gif" align=center style="cursor:pointer;" onclick="panel(1)"><span class="selection">Buyers</span></td>
<td background="/images/ggrad.gif" align=center style="cursor:pointer;" onclick="panel(2)"><span class="selection">Accounts</span></td>
</tr></table>
<table id="suppliers"><tr>
<td valign=top>
Referral:<br>
<select name="reference" size=20 multiple>
<%
sql = "select distinct referral from vendorreferral order by 1"
set rs = con.execute(sql)
do while not rs.eof
if not emptyvalue(rs(0)) then Response.Write "<option value="""&rs(0)&""">"&rs(0)
rs.movenext
loop
%>
</select>
</td>
<td>OR</td>
<td valign=top>
This mail already sent:<br>
<select name="mail" size=20 multiple>
<option>-- ANY E-MAIL --
<%
sql = "select distinct mailtypid,maildescription from crmmailtext join crmmailtype on mailid=mailtypid"
set rs = con.execute(sql)
do while not rs.eof
if not emptyvalue(rs(0)) then Response.Write "<option value="""&rs(0)&""">"&rs(1)
rs.movenext
loop
%>
</select>
<br>
sent after <input size=10 name=startdate <%=validatedate()%>><%=calendar("form1.startdate")%>
<br>and before <input size=10 name=enddate <%=validatedate()%>><%=calendar("form1.enddate")%>
<br>(dates inclusive)
</td>
<td>OR</td>
<td valign=top>
Status:<br>
<select name="curstatus" size=20 multiple>
<%
sql = "select distinct curstatus,statusname from master left join vendorstatuses on vendorstatus=curstatus order by 1"
set rs = con.execute(sql)
do while not rs.eof
if not emptyvalue(rs(0)) then Response.Write "<option value="""&rs(0)&""">"
if emptyvalue(rs(1)) then Response.Write rs(0) else Response.Write rs(1)
rs.movenext
loop
%>
</select>
</td>
</tr>
<tr><td colspan=5 align=center><hr>OR</td></tr>
<tr><td colspan=5>NAICS Codes: <input name="naics" size=30> </td></tr>
</table>
<table id="buyers" style="display:none">
<tr>
<td valign=top>
Buyer on:<br>
<select name="customsite" size=20 multiple>
<option value="0">-- All buyers --</option>
<%
sql = "select distinct site from buyerregistration left join guser on userid=gid order by 1"
set rs = con.execute(sql)
do while not rs.eof
if not emptyvalue(rs(0)) then Response.Write "<option value="""&rs(0)&""">"
if emptyvalue(rs(0)) then Response.Write rs(0) else Response.Write rs(0)
rs.movenext
loop
%>
</select>
</td>
<td valign=top>
From prime:<br>
<select name="primeid" size=20 multiple>
<%
sql = "select primeid,fullname from prime"
set rs = con.execute(sql)
do while not rs.eof
Response.Write "<option value="""&rs(0)&""">"&rs(1)
rs.movenext
loop
%>
</select>
</td>
</tr>
</table>
<table id="accounts" style="display:none">
</table>
</td></tr>
<tr>
<td align=center>
<input type="submit" name="add" style="width:200pt" value="+ Add"> <input type="submit" style="width:200pt" name="remove" value="- Subtract">
</td></tr>
<tr>
<td align=center>
<br><br>
<input type="submit" name="deleteall" value="Clear Queue" onclick="return confirm('You are about to delete all contacts from list\nProceed?')">
<input type="button" name="edit" value="Manualy Edit Queue" onclick="window.open('editqueue.asp','','resizable=yes,scrollbars=yes,width=700,height=300')">
</td>
</tr>
</table>
</form>
</BODY>
</HTML>