viewing report problem

[jeena]

I'm creating this system that reports incidents from several different location's.The reports are made by a user filling in certain forms.Those forms are then forwarded to Supervisors in charge of those location.
What I want to know is that;
when the Supervisor logs in the system, I want the Supervisor to see the reports of the location that he is in charge of,before he makes the report of that particular incident.

I'm using IIS version 2.0

Can someone give me an idea or an example, on how I can do that????
Please......:confused:

[raf]

Kind of a wild question.

I think you need two tings: when the incident is reported, you need to store a locationID or so inside the incidents record.
+ you need a table in your db (locations-table) that has a supervisors column (where you insert the supervisorID)


Whe a superviser logs in, you need to run a selctquety to select all incidents from the ocations he is asigned to. This requires a join between the incidents and the location table.

If you need moe info, you need to tell us someting more about your db-design.

[jeena]

I understand what u r trying to say but not so clearly.
I don't really understand when you say that

"Whe a superviser logs in, you need to run a selectquery to select all incidents from the locations he is asigned to. This requires a join between the incidents and the location table."

Can you elaborate or give me an example...
Anyway i'm using an Access Databse and below are some information about my db: -

the table for the first form has the following fields:
id,reportperson,dateincident,datereport,location and unit.

the table for the Supervisor form has the following fields
id,name,summaryOfIncident

Regards,
jeena

[raf]

you could have a table with all locations in it + the supervisor for each of those locations (assuing there is only one supervisor for each location).
Say this table has the following design
locarions:
locationID, supervisorID, locationname

locationID is an autonum column. In the table where the incidents-info is registered, you should have this locationID in the locationvariable
supervisorID hold the id from the relevant record in the supervisor tabe
locationname is the name of the location. Should not be in the incidents table

When you want to display the incidents for that supervisor, you select the info you need from the tables with a query like

sql="SELECT formstable.*, locations.locationname FROM formstable INNER JOIN locations ON formstable.locations = locations.locationID WHERE supervisorID = theID ORDER BY formstable.ID desc"
sql=replace(sql,"theID",request.form("supervisor"))

so you'll you the table with the incidents, with the one that makes the link between location and supervisor. Thye where clause will make sure you only get the records for this supervisor. In this code, the supervisorsID was in a form on the previous page, but there are other options.

Might also be a good idea to have a sort of 'status'-variable in the table with the incidents (registered, in process, closed, that sort a stuff) + a registrationdate-variable

Just let me know if you need more info

[jeena]

Ok now i get it.....there is one thing i forgot to tell you; actually it depends on two fields to determine which supervisor is in charge....location and unit.
So, can I design my database like that:-

locationID unitID supervisorID locationname unitname

i'll keep locationID as autonum because in some locations there are no units...

And in the table where incident info is registered...i shall create two new fields which is locationID and unitID



Is that ok???
Do let me know if i'm wrong....
---------------------------------------------
in the query that you sent me, u stated that:-

sql=replace(sql,"theID",request.form("supervisor"))

how does the above statement work..can you explain the above statement.....theID is the SupervisorsID rite???

:confused:

[raf]

So, can I design my database like that:-

locationID unitID supervisorID locationname unitname

i'll keep locationID as autonum because in some locations there are no units...

Actually, i would have 3 separate tables:
1. for the units, where i'd have the unitID and the unitname and the rest of the units info in (--> who's in charge of it, department etc etc) + in this table, i'd have a dummy unit for locations where there are no units
2. Table for the locations --> locationID, locationname, address and all the other locationdata
3. a 'factstable' where you bring everything together --> an ID variable (locsupID or so), forlocationID, forunitID, forsupervisorID
In this last table, you only have id's --> foreign key to the other tabels.

You can then select the records from the incidents table and get all the info from joining the other tabels.
When you record the incident, you can also select the supervisor for that location-unit and save his ID in the incidents-table. It's not necessary and includes some dangers (imagen that there's a change in locations, units or supervisor organisation, what sort of updating will that take ...) but it micht speed up things. But a stict start-design (like above, 1 factstable and 3 dimensiontabels) is really flexible and once you'r familiar with joining tabels, it's ideal.


sql=replace(sql,"theID",request.form("supervisor"))

how does the above statement work..can you explain the above statement.....theID is the SupervisorsID rite???


it's 'my' style of coding. I first write the complete sql-statement, and use 'dummys' for values or variabelnames or conditions.
Then after it, i replace these dummy's by values i get from the querystring, the form on the previous page, the sessionobject.
I find it easier to code, read and debug (shorter SQL string + not jumping in and out sql or sql-logic + keeping all modifications to the original values near the sql-statement). Especially when using stringvalues (where you else needto have a replace inside the sql-string) or where i first perform some modifications to the values.
But you can use another codingstyle, of course (i see a lot of people first creating variabels, storing the values from the form/querystring etc in these variabels, and then inserting these values in the sql string, which isn't easy to read or debug and creates some avoidable load on the server)

replace works like this
variabel=replace(variabel where you want to replace something in, "string1=what needs to be replaced","string2=whith what to replace")
'TheID' is indeed the supervisorsID

Just let us know if you need more info.

[jeena]

Let me see if I relly understand....



okie....so, I should create 3 new tables in my database:-

location---> where I have location ID,supervisorname,locationame
unit--->where I should have unitID, unit name
factstable--->where I should have all the ID

+ I should create a new column called locationID in the incident table.....And I assume thats how the location table and the incident table are connected(i.e through the location ID)

So, if I want the report to be viewed I just need to add the sql query in the form where i want to display the records....

quote:
--------------------------------------------------------------------------------

sql=replace(sql,"theID",request.form("supervisor"))
--------------------------------------------------------------------------------
And in the above statement I would replace theID with The current supervisorID.

Is that how it goes......??

PS: I'm sorry to have trouble u..I don't have a choice but to keep asking as i'm quite new in ASP

:o

[raf]

okie....so, I should create 3 new tables in my database:-

location---> where I have location ID,supervisorname,locationame
unit--->where I should have unitID, unit name
factstable--->where I should have all the ID

+ I should create a new column called locationID in the incident table

+ add a column with the unitID to the incidents table. Well, that is what i would do (based on the info i got from you.)

.....And I assume thats how the location table and the incident table are connected(i.e through the location ID)

Hmm. It's possible, but not necessary.
When you want to select all incidents for that user, your going to join the facttable with the incidents table, with a join on 2x2 fields, with a where clause so that you only get the incidents where this supervisor is responsable for.
Something like


select incidentstable.* FROM incidentstable INNER JOIN factstable ON (incidentstable.locationID=factstable.locationID AND incidentstable.unitID=factstable.unitID) WHERE factstable.supervisorID = theID ORDER BY incidentstable.ID desc"
sql=replace(sql,"theID",request.form("supervisor"))

This will give you all variabels from this supervisor. (the value for the supervisor is, in this example, pulled from a form that was posted to this page, where it was the value of a formelement with name="supervisor" --> for instance a dropdowbox with all supervisors (which can very easely be build from the supervisors-table)). But you could also get it from a sessionvariable or so (that's what i would do --> if you have a loginform (and you should have one), then just select the supervisorID when you validate his usernamem and password, and store the ID in a sessionvariable (like session("IDsuperv") = rsRecordsetname.Fields("supervisorID") ). Then it would just be
sql=replace(sql,"theID",session("IDsuperv"))

But of course, you'll probably be interested in geting the locationsname and unitsname, so you'll need a four table join.

select incidentstable.*, location.locationname, units.unitname FROM ((incidentstable INNER JOIN factstable ON (incidentstable.locationID=factstable.locationID AND incidentstable.unitID=factstable.unitID)) INNER JOIN locations ON incidentstable.locationID=locations.locationID) INNER JOIN units ON incidents.unitID=units.unitID) WHERE factstable.supervisorID = theID ORDER BY incidentstable.ID desc"
sql=replace(sql,"theID",request.form("supervisor"))

It might look complicated, but if you read it a few times, you'll see it makes sense.
Don't think that you can make it easy by registering the ID from the factstable (or the supervisorID from the factstable) inside the incidentstable (instead of the locationID and unitID) or by storing the location and unitsname inside the incidentstabel and factstable, because if you start appointing different supervisors to a unit, you'll be in trouble. Idem for if you want to change the location or unitsname.

It's better to stick with a clean, flexible datamodel where you get the info you need from the dimensiontables by making a few joins.
Just ask if you have questions or need more info. That's the whole point of this forum ;)

[jeena]

hmmm...wait a minute...I understand that the sql query will output the report sent....

In the login part theres a common login ...meaning that all users use the same login password to access the system.

But how does the information from incidentform gets carried to Supervisor form??????
How does it recognize that incidentB is for supervisorB???/

i'm kind of confused


:confused:

[raf]

OK. Lets run through it. Say 'a user' registers a new incident. On the registrationform, he'll need to select his location and unit in two dropdowns (generated from the locations and units table. I suppose you could have him pick a location, and then build the unitsdropdown with all units for that location (or the other way around). Only the locationID and unitID are registerd in the incidentstable when the user submits the form.

When the superviser logs in, he'll need to identify himself (by selecting his name from a dropdown (generated from the supervisors table).

Then you'll have this select. You need all incidents where this supervisor is responsable for. That is, for all combinations of a specified location and unit. Thats's whats in the factstable
location unit supervisor
1 1 5
1 4 5
1 3 3

So for supervisor 5 we would need all incidents with location 1 and unit 1 or 4. The inner join with the 2x2 fields does that
((incidentstable INNER JOIN factstable ON (incidentstable.locationID=factstable.locationID AND incidentstable.unitID=factstable.unitID))
it builds a temporarely table ('view') with fields from both tables.
The other joins get us the locationname and unitsname (which the system (the RDBM) looks up in the location and units table.

If you need some info on joining, best open the access helpfunction and look into the sql sections (DML-part).

In the login part theres a common login ...meaning that all users use the same login password to access the system.
That's not verry wise. Suppose everyone has his own login (even without a password). The the user that enters a new incident wouldn't need to select the location and unit (since you can pull it from the db) (buth you could still give them this option --> if user can input incident from other units/locations). When the supervisor logs in, he to wouldn't have to identify himself. On the first page he sees, you could (should?) show him automatically the new incidents that are assigned to him. If you include a password, other users wouldn't even be able to look into those (if necessary).

It's not that hard to set up a login procedure. Jest run a search her, or let me know if you need some code.


Does this answer your question?

[jeena]

Thanx....raf; now I get what ur trying to say:thumbsup:

quote
----------------------------------------------
It's not that hard to set up a login procedure. Jest run a search her, or let me know if you need some code.
-----------------------------------------------

Would appreciate if u could send me some code on Login, B'coz I don't think my login procedure is very good...



Thanx again.....:D

[jeena]

Thanx....raf; now I get what ur trying to say:thumbsup:

quote
----------------------------------------------
It's not that hard to set up a login procedure. Jest run a search her, or let me know if you need some code.
-----------------------------------------------

Would appreciate if u could send me some code on Login, B'coz I don't think my login procedure is very good...

(If u wanna have a look at my login procedure, its in the attachement)



Thanx again.....:D

[raf]

Glad you got it :thumbsup: (if i could have shown you it would have been a lot easier ...)
Hmm. I don't think that login works . I mean, say i enter a wrong password. the recordset wil be empty and the "If (not rst.BOF) and (not rst.EOF) then" will prevent the processing of the following code (with the "Your password is incorrect." message) . No ?
Actually, the
If (request.form("password")=rst("password"))And (request.form("LoginID")=rst("LoginID")) Then
response.redirect("irwardclinictestcdc.asp")
else
If you check for EOF, then you need to supply some code in case there are no records returned
isn't necessary since the condition of your select only selects records that will pass this validation.
Also, don't use "select * " unless you really need all fields. Else, just select the fields you need.

What's more, you didn't include that code, but if you don't check if the user is logged in (i don't see any reistration in your database or sessionvariabel or cookie ...) and i type in "url/irwardclinictestcdc.asp" in the adress bar, then i simply bypassed the login. So on each page, you need to check if the client is logged in + if he is allowed to see that page.

I posted some code (and comments on it) in an earlier thread
http://www.codingforums.com/showthread.php?s=&threadid=18372&highlight=login

The interesting thing in that code, is that i included setting a securityprofile. For instance, in your app, you could have profiles for "unitmember" (=1), "supervisor"(=2), "manager" (=3) etc
On each page, you can then specify which securityprofile the client needs to see the page, or, which links/info should be shown or to which page he needs to be redirected. For instance: a unitmember could only be allowed to see the incidents from himself or his unit. A supervisor will see all incidents for his locations/units, A manager could see all incidents.
All you need to do is store a profile (just the numer) in your userstable, select it when you validate the password and username and store it in a sessionvariabel. From then on, all you need to do is check for the sessionvariabel to determine if he can see the page/link/etc or what sql statement needs to be used or ...

Just ask if you need more info on some of the things.

[jeena]

thanx....how does the conadmin.asp work...i mean what file is that.....

[raf]

it a SSI --> Server Side Include that has the connectionstrings for your db in it. Most people would have used .inc extension, but if you give it a .asp or .php or .jsp extension, it can't be read by a browser that types in it's adress in the adress-bar (if you browse it, it gets parsed by the webserver, and you'll see nothing).

You should always store your connectionstrings in a SSI + have one of the extensions above.
It is safer (if the webserver is overloaded and falls over, it could sent the asp code to the client, instead of parsing it, so the client could then read your connectioninfo (which is all he needs to ...). If you store it in an include, he'll only see the includes adress. If this include is called connection.inc, he can browse to it and open it. If it's called connection.asp, he woun't see anything (unless this page to wasn't parsed)
It is also easier to switch database. You only need to change the include once.

In your case, this file would contain something like

<%
set cnn = Server.CreateObject("ADODB.Connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("db/dbireport.mdb")
cnn.Open DSNtemp
%>

[jeena]

hi,....and thanx I always wanted to know how this SSI files worked....

---------------------------------------------------------------------------
You should always store your connectionstrings in a SSI + have one of the extensions above.
----------------------------------------------------------------------------

How do I store a connectionstring in an SSI??....

The reason why I never used a session variable or a cookie in my login coding was that I don't know how it works???
Yes, I do know that a cookie is suppose to store some info abt the user and a session retains info in a particular session but how do I use it in my code's ....how do I declare it....etc???

Regards,
jeena

[raf]

How do I store a connectionstring in an SSI??...

In my previous post, you find an example for your code (the code at the borrom). Just store the connectionstrings, like you now use them in your code, in a seperate file. Make sure you don't forget the asp tags, cause when you call the SSI, your not in ASP mode --> see the code in the posted files to know how to call the SSI.
The reason why I never used a session variable or a cookie in my login coding was that I don't know how it works???
Yes, I do know that a cookie is suppose to store some info abt the user and a session retains info in a particular session but how do I use it in my code's ....how do I declare it....etc???


sesionvariabels. Well, it's very easy. To set a sessionvariable and store a value in it, just use
session("name")=value
to read the value, use
session("name")
like
session("username")="raf"
response.write("Welcome, " & session("username"))
They don't need to be declared. Just initiate them when you store the value. If you destroy the session (or the sesion times out), the instance of the sessionobject for that user is deleted and all resources are released

cookies --> i've posted some info about using them here
http://www.codingforums.com/showthread.php?s=&threadid=21689
but sessionvariabels are easier and safer (don't save info in a cookie that should not be displayed to others!) but take up more resources from the webserver. When use cookies --> when you wan't to store info thats not sensitive, or needs to be used over different settings (and you don't want to store it in a db or textfile on your server) or if webserver-resources is a big concern

[jeena]

got it!!:thumbsup:
thanx 4 all the help;)

[raf]

You're welcome.