how do I reload attorneys' wheel? 1

cisst · Jun 7, 2010

Dearest experts,

I am a first timer here and I ask for your patience and heavenly assistance.

I have one serious issue.

The history:

I have 12 attorneys. This number doesn't change.

Each attorney is assigned a case, no more than one case a time.

The cases are assigned randomly.

First, a case is created, then each attorney is selected from the dropdown and assigned a case.

The dropdown is also known as a wheel.

Once a attorney is assigned a case, the attorney disappears from the dropdown (wheel).

Next time a case is created, it is assigned to another attorney.

This process goes on until all attorneys in the dropdown or wheel is assigned a case.

Once each attorney has been assigned a case, and no attorney is left on the dropdown, any attempt to assign another case to a attorney results to the following message I created:

"No attorneys available to handle case " & caseid (whatever the case number is)"

This is simply because ALL attorneys on the dropdown has been assigned a wheel.

These scenarios work great so far.

The problem I have now is that once all attorneys have each been assigned a case, and a new case is created, rather than this code below:

Code:

       If RS.EOF Then
           Response.Write "FATAL ERROR:  No attorneys available to handle case " & caseid
           RS.Close
           Conn.Close
           Response.End
       End If

we would like to reload the dropdown with SAME attorneys and start assigning new cases to each attorney all over again.

I tried this:

Code:

       If RS.EOF Then
           Response.Write "Reloading attorneys wheel"
       SQL = "SELECT j.AttorneyCode, j.AttorneyName " & _
		     "FROM Attorneys j " & _
		     "LEFT JOIN Cases c ON c.AttorneyCode = j.AttorneyCode " & _
		     "WHERE c.AttorneyCode IS NULL " & _
             "ORDER BY j.AttorneyName"
           RS.Close
           Conn.Close
           Response.End
       End If

But it isn't working.

I am really stumped as to how to handle this.

I would really, really appreciate your assistance with handling this portion ofthe problem.

3 tables are involved so far:

Attorney table (lookup table)
AttorneyCode nvarchar(50),
AttorneyName nvarchar(50)

Clients table (lookup table)
ClientID int pk identity key,
ClientName nvarchar(50)

Cases table
caseId int pk identity key,
attorneyCode nvarchar(50),
ClientId int,
CaseName nvarchar(50),
CaseType nvarchar(50

Do I need to make any changes to these tables to help solve this problem?

Please let me know.

Below is the entire working code.

Many thanks in advance for your assistance.

Code:

 <html>
<head>
<script>
function clearText(thefield){
	if (thefield.defaultValue==thefield.value) {
		thefield.value = "";
	}
}
function restoreText(thefield){
	if (thefield.value=="") {
		thefield.value = thefield.defaultValue;
	}
}
</script>
</head>
<body background="images/fcweb_bg.jpg">
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};" & _
 "Server=wgc;" & _
 "Address=wgc,1433;" & _
 "Network=DBMSSOCN;" & _
 "Database=ATTORNEYS" & _
 "Uid=***;" & _
 "Pwd=***"

If Request("POSTBACK") = "NEWCASE" Then

  ' new case requested
   client = Trim(Request("clientid"))
   casename = Trim(Request("casename"))
   casetype = Trim(Request("casetype"))

 If client <= 0 Then
    ' only generate a new client id if we *NEED* to!!!!!
    SQL = "Set Nocount on; " _
        & " Insert INTO Clients (ClientName) VALUES ('" & Replace(Request("newclientid"),"'","''") & "');" _
        & " Select @@identity; " _
        & " set nocount off"
    'response.write SQL
    'response.end
    Set RS = Conn.Execute(SQL)
    client = RS(0)
    RS.close
End If

   SQL = "INSERT INTO Cases (clientid,casename,casetype) " _
       & " VALUES(" & client & ",'" & casename & "','" & casetype & "')"
      ' response.write SQL & "<br>"
	  ' response.end

   conn.Execute SQL
   SQL = "SELECT @@IDENTITY"
   Set RS = conn.Execute(SQL)
   caseid = RS(0)
    ' response.write caseid& "<br>"
    ' response.end

   RS.Close
End If

If Request("POSTBACK") = "ASSIGNCASE" Then
   caseid = Trim(Request("caseid"))
   AttorneyCode = Trim(Request("Attorney"))
   SQL = "UPDATE Cases SET AttorneyCode = '" & AttorneyCode & "' WHERE caseid = " & caseid
   conn.Execute( SQL )
End If

' see if there are any cases pending that need a Attorney assigned to them:
SQL = "SELECT TOP 1 CaseID, ClientID, casename, casetype FROM Cases WHERE Coalesce(AttorneyCode,'0') = '0' ORDER BY CaseID "
'Response.write sql & "<br>"
'Response.end
Set RS = conn.Execute(SQL)
If Not RS.EOF Then
   ' aha!  have a case that needs assignment
   caseid = RS("caseID")
   clientid = RS("clientID")
   casename = RS("casename")
   casetype = RS("casetype")

   ' now, does this client have any cases assigned to any Attorney?
   SQL = "SELECT TOP 1 AttorneyCode FROM Cases WHERE clientID = " & clientid & " AND Coalesce(AttorneyCode,'0') <> '0' "
  'Response.write sql & "<br>"
  'Response.end

  Set RS = conn.Execute(SQL)
   If Not RS.EOF Then
       lcode = RS(0) ' yes!  already has this code assigned
       SQL = "UPDATE Cases SET AttorneyCode = '" & lcode & "' WHERE caseid = " & caseid
       conn.Execute SQL
       Response.Write "<h3>Assigned " & lcode & " to case " & caseid & " for client " & clientid & "</h3>" _
                    & "<br/>Case name: " & casename & "<br/>Case type: " & casetype _
                    & "<br/>(same client as a prior case)</br></hr>" & vbNewLine
   Else
       ' if here, this is first case for this client...so find an available attorney:
      ' SQL = "SELECT AttorneyCode, AttorneyName FROM Attorneys " _
      '     & " WHERE AttorneyCode NOT IN (SELECT AttorneyCode FROM Cases) " _
      '     & " ORDER BY AttorneyName "
       'Response.write sql & "<br>"
       'Response.end

     '    SQL = "SELECT Attorneys.AttorneyCode, AttorneyName " & _
	'		 "FROM Attorneys " & _
	'		 "LEFT JOIN Cases ON Cases.AttorneyCode = Attorneys.AttorneyCode " & _
	'		 "WHERE Cases.CaseId IS NULL " & _
	'		 "ORDER BY AttorneyName "

       SQL = "SELECT j.AttorneyCode, j.AttorneyName " & _
		     "FROM Attorneys j " & _
		     "LEFT JOIN Cases c ON c.AttorneyCode = j.AttorneyCode " & _
		     "WHERE c.AttorneyCode IS NULL " & _
             "ORDER BY j.AttorneyName"

       Set RS = Conn.Execute( SQL )
       If RS.EOF Then
           Response.Write "FATAL ERROR:  No attorneys available to handle case " & caseid
           RS.Close
           Conn.Close
           Response.End
       End If
       ' so let the clerk choose a attorney:
%>
       <hr>
       <br/>
       <h2>Choose Attorney for Case</h2>
       <br/>
       Case id: <%=caseid%><br/>
       Client id: <%=clientid%><br/>
       Case Name: <%=casename%><br/>
       Case Type: <%=casetype%><br/>
       <form name="attorneyForm">
           <input type="hidden" name="POSTBACK" value="ASSIGNCASE" />
           <input type="hidden" name="caseid" value="<%=caseid%>" />
           Choose an available attorney:
           <select name="attorney">
<%
       Do Until RS.EOF
%>
         <option value="<%=RS("AttorneyCode")%>"><%=RS("AttorneyCode")%></option>
<%
           RS.MoveNext
       Loop
       RS.Close
%>
          </select>
          <br/>
          <input type="submit" value="Assign Attorney"/>
       </form>
       </body></html>
<%
        conn.Close
        Response.End ' don't display more...must choose a attorney
    End If
End If
%>
<img src="images/casewheel.gif">
<h2>Create a new Case</h2>
<table width="74%" style="border:1 solid #F3EFE0;" cellspacing=0>
<tr style="background: #F3EFE0;color:#003;vertical-align:top;margin-top:0;height: 25px;">
<td>
<form name="caseForm">
<input type="hidden" name="POSTBACK" value="NEWCASE" />

    <select name="clientid">
    <option value="0">Choose client</option>
<%
Set RS = Conn.Execute("SELECT ClientID, ClientName FROM Clients ORDER BY ClientName")
Do Until RS.EOF %>
    <option value="<%=rs(0)%>"><%=rs(1)%></option>
<%
RS.MoveNext
Loop
RS.Close
%>
</select>
<INPUT Name="newclientid" size="32" onFocus="this.form.clientid.selectedIndex=0;"> <--Type in client name if not in the dropdown list<br>
<br/>
<input name="casename" onfocus="clearText(this)" onblur="restoreText(this)" size="50" value="Enter case name" />
<br/><br>
<input name="casetype"  onfocus="clearText(this)" onblur="restoreText(this)" size="50" value="Enter case type" /> 
<input style="font-size: x-small; font-weight: bold; color: #003;" type=submit value="Create Case" name=case>
</form>
</td>
</tr>
</table>
</body>
</html>

foxbox · Jun 8, 2010

we would like to reload the dropdown with SAME attorneys and start assigning new cases to each attorney all over again."

What exactly do you want. not clear to me.
If the 12 attorneys all have 1 case, you want a dropdown list with 12 attorneys for case 13?
So "Each attorney is assigned a case, no more than one case a time." is not true?

When case 13 is assigned you have 11 attorneys with 1 case and 1 with 2. Then for case 14 you want to see those 11? etc?

cisst · Jun 8, 2010

Hi foxbox and many thanks for your response.

You asked:
What exactly do you want. not clear to me.
If the 12 attorneys all have 1 case, you want a dropdown list with 12 attorneys for case 13?

Well sort of Yes.

I think I erred by leaving out some key points.

1, The dropdown box acts as a wheel.

This wheel contains 12 attorneys.

Each time an attorney is asigned a case, that attorney drops off the wheel or dropdown.

This case assignment process goes on till ALL 12 attorneys have been assigned a case each.

After all 12 attorneys have been assigned a case, rather than receive this message when attempting to add a new case:
FATAL ERROR: No attorneys available to handle case " & caseid, we would like the dropdown or wheel reloaded with same 12 attorneys again and begin new case assignments.

In other words, the requirement that no attorney gets more than one case at a time is for one wheel circle.

That's why I coded it in such that the attorney drops off from wheel after getting assigned a case.

Hope this explains this a bit clearer.

You asked:
So "Each attorney is assigned a case, no more than one case a time." is not true?

Yes in one wheel circle. In other words, after 12 attorneys have received one case, then the dropdown is to be reloaded so they can started getting cases assigned again.

As far as db is concerned, an attorney gets as many cases as possible.

It is just that no attorney in current wheel or dropdown can get more than one case at a time.

You asked:

When case 13 is assigned you have 11 attorneys with 1 case and 1 with 2. Then for case 14 you want to see those 11? etc?

If you run the entire code I posted, you will see that initially,the dropdown has 12 attorneys.

Anytime you assign a case to an attorney, say Attorney1, Attorney1 disappears from the dropdown. This way, we make sure no attorney in current dropdown or wheel gets more than one case.

The code works great as I described so far.

My issue is that once all 12 attorneys have disappeared from the dropdown (meaning each one now has one case), rather than this:

Code:

       If RS.EOF Then
           Response.Write "FATAL ERROR:  No attorneys available to handle case " & caseid
           RS.Close
           Conn.Close
           Response.End
       End If

We would like something like this:

Code:

       If RS.EOF Then
           Response.Write "Reloading Attorney wheel..."
       SQL = "SELECT j.AttorneyCode, j.AttorneyName " & _
             "FROM Attorneys j " & _
             "LEFT JOIN Cases c ON c.AttorneyCode = j.AttorneyCode " & _
             "WHERE c.AttorneyCode IS NULL " & _
             "ORDER BY j.AttorneyName"
         
           RS.Close
           Conn.Close
           Response.End
       End If

so,we can start selecting from the dropdown all over again.

That attempt didn't work though.

I hope I have cleared things up.

Thanks a lot and sorry for late response.

foxbox · Jun 8, 2010

You want to devide the cases equally. So each time when you need to assign a case we start with this:

Code:

select max(noofcases), min(noofcases)
from
(
select count(*) noofcases
 from cases
 group by AttorneyCode
 )

If max() and min() are equal, then we show a pulldown with all attorneys, based on a select from the Attorney table.

If they are NOT the same, then the pulldown must show all attorneys that have min() cases.

Code:

select attorneycode
 from cases
 group by attorneycode
 having count(*) = {min()}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

how do I reload attorneys' wheel? 1

cisst

Programmer

foxbox

Programmer

cisst

Programmer

foxbox

Programmer

Similar threads

Part and Inventory Search

Sponsor