Injured table
InjuredID autoNumber primary key int,
passenger1 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50),
passenger2 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50),
passenger3 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50),
passenger4 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50),
passenger5 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50)
I am of course I have more tables like vehicle but what I am having problem with are these 3.
We are converting manual forms into electric forms.
Currently, using the manual form, when an accident occurs, a form is filled by completing information about the driver, and slots filled for up five passengers in the car.
So you a scenario like:
DriverID,
age,
streetAddress ,
city,
state,
zip,
passenger1,
age,
streetAddress ,
city,
state,
zip,
passenger2
age,
streetAddress ,
city,
state,
zip,
passenger3
age,
streetAddress ,
city,
state,
zip,
passenger4
age,
streetAddress ,
city,
state,
zip,
passenger5
age,
streetAddress ,
city,
state,
zip
What is the best way to handle this situation using asp/form?
Thank you in advance
Ribeyed
03-20-2003, 07:42 AM
hi,
first rule of normalization, remove any repeating fields and create a new relation/table for each set of data.
age, city, state, zip are repeating fields. Street Address can possible be normalized further by splitting houseNumber and StreetAddress and then making a relation/table for streetaddress.
I would certainly create a new relation/table for age, city, state and zip.
Sorry to take you away from your original question, personally i feel under your present database structure you are going to find it very hard to write any code. Once you have you're database fix i am sure one of us will be able to give you some code to get you started.
simflex
03-20-2003, 08:30 AM
hi Dave and ]SWR] Ribeyed!
Thanks to both of you for your input.
Now I can understand normalizing the Injured table further, for instance, adding an InjuredType such as
InjuredType
InjuredTypeID
InjuredTypeName
then have InjuredTypeID in the Injured table.
But don't you think it is a bit overnormalizing when you break them up like these:
Age Table
AgeID autoNumber primary key int,
Age int,
City Table
CityID autoNumber primary key int,
City varchar(50),
StateID foreign key int,
State Table
StateID autoNumber primary key int,
State varchar(50),
Zip Table
ZipID autoNumber primary key int,
ZipCode varchar(8),
CityID foreign key int
Ribeyed
03-20-2003, 09:19 AM
hi,
lol, "over normalized" normalization is a technique. When we design a database for a relational system, the main objective in developing a logical data model is to create an accurate representation of the data, its relationships, and constraints. To achieve this objective, we must identify a suitable set of relations. A technique that we use to help identify such relations is called normalization.
A major aim of relational database design is to group attributes into relations so as to minimize data redundancy and thereby reduce the file storage space required by the implemented base relations.
Relations that have redundant data may have problems called update anomalies, which are classified as insertion, deletion or modification anomalies.
The database structure you posted would have insertion anomalies to start with. You can insert into the injured table a record about an injury without any relating record in the Driver table. This is bad.
Your database would also have deletion anomalies. If you deleted a record from the driver table you would have a record left in the otherdriver table, this would cause problems.
And again your database would have modification anomalies. If driver 1 changed his details and he was in an accident if you changed his details in the driver table you would be left with the old details in the injured table.
You would have more problems when it comes to inserting into the database with your injured table. You have 4 age, streetaddress, state, city etc. in the one table, there are many reasons why this is not done, one would be; it would not know which one you want to insert data into. Say I said
state = "Mississippi"
Then if you inserted this into the database it would know which state field to write to.
The driver and otherdriver tables are not normalized you should have 1 table for all drivers and another table that will hold information about which driver, first or second, they are.
so something like this would be normalized:
Impressive lesson on rules of normalization.
I thought I know it atleast to the 3rd normal form.
F.C Codd must be turning in his sleep now.
Anyway taking further, here is my full design in relation to the accident project.
Damage table (this is damage to car and has prepoluated data from police dept)
damageId int primary,
DamageDesc varchar(1000)
eg damageId DamageDesc
1 total damage
Collision table (two cars involbed also prepopulated from police dept)
collisionID int primary key,
CollisionDesc varchar(1000)
eg
collisionID collisionDesc
1 bumper to bumper
Age Table
AgeID autoNumber primary key int,
Age int
City Table
CityID autoNumber primary key int,
City varchar(50),
StateID foreign key int
State Table
StateID autoNumber primary key int,
State varchar(50)
Zip Table
ZipID autoNumber primary key int,
ZipCode varchar(8),
CityID foreign key int
DriverConditon table (also prepopulated and taken from police dept. This determines if the driver is under influence of alcohol or something.
conditionID in primary key,
conditionDesc varchar(1000)
Notice that all those with prepopulated data do not have autoNumbers.
Police table
policeBadgeId int primary key autonumber
officersName varchar(50),
dept varchar(50),
caseNumber int,
accidentEventID int (from accidentEvent table)
factors table (contributing factors to the accident, again prepoluated and taken from the police dept)
factorsID int primary key,
factorsDesc varchar(1000),
eg,
factorsID factorsDesc
1 failed to yield
insurance table
insuranceId autonumber int primary key,
insuranceCo varchar(50),
accidentEventID int (from accidentEvent table)
workmansComp table
compID int primary key autoNumber,
name varchar(50),
dept varchar(50),
agent varchar(50),
accidentEventID int (from accidentEvent table)
Location table (location of accident)
locationID int primary key autonumber,
locationDesc varchar(100),
accidentEventID int (from accidentEvent table)
vehicle table
vehicleID int primary key autoNumber,
LicenceNumber varchar(1000),
make varchar(50),
Model varchar(50),
vehicleYr varchar(50),
accidentEventID int (from accidentEvent table)
weather table (also prepopulated and taken from police dept)
weatherID int primary key,
weatherDesc varchar(1000)
eg:
weatherID weatherDesc
1 clear
The main table
AccidentEvent table
accidentEventID int autoNumber, primary Key,
condID int (from driverCondition table),
factorsID int (from factorstable),
compID int (from workmansComp table),
collisionID (from Collision table),
damageId (from damage table),
weatherId (from weather table),
comments varchar(2000),
drugtest varchar(50),
result varchar(1000),
atfault varchar(50)
This is basically what I have as far as design is concerned.
I know this is a lot of information to digest but take a look and see if there are any additonal design changes to be made.
Thanks to you folks again
Ribeyed
03-20-2003, 07:45 PM
hi,
can you post the form you have for taking input from the user?
simflex
03-20-2003, 08:22 PM
hi [SWR]Ribeyed
This is what I have so far. It is not very respectable right now because of the problem I was having that led to my original posting.
<body>
<form name="Insert" method="post" action="">
<p>Event location:</p>
<p>
<input name="txtEventLocation" type="text" maxlength="50" value="<%request.form("txtEventLocation")%>">
</p>
<p>Event description:</p>
<p>
<textarea name="txtEventDescr" cols="40" rows="7" value="<%request.form("txtEventDescr")%>"></textarea>
</p>
<p>Involved persons:</p>
<table width="57%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Name</td>
<td>Age</td>
<td>Location</td>
<td>Type</td>
</tr>
<%response.write(strGrid)%>
</table>
</form>
</body>
</html>
<%
End Function
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Function BuildPersonTypeMenu(intSelection, intCount)
'-----------------------------------------------------
'build ADODB.Connection here, should be called "objConn"
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "" 'accident'
'-----------------------------------------------------
'retrieve all availble Injured types from database
'--------------------------------------
strSQL = "SELECT * FROM injuredType"
set rsData = Server.CreateObject("ADODB.Recordset")
rsData.Open strSQL, objConn
'--------------------------------------
'start building the menu as strMenu. intCount is used to keep the menu
'value unique in the grid:
strMenu = "<select name=""lstinjuredTypeMenu"""&intCount&">"& vbNewLine
Do While Not rsData.EOF
strMenu = strMenu &"<option value="
strMenu = strMenu & rsData("TypeID")
'check if there was selected a value before. If so, make sure it's displayed
'as selected, so the user does not have to re-select that value, every time a
'line is added to the grid
If intSelection <> 0 and CInt(rsData("TypeID")) = Cint(intSelection) then
strMenu = strMenu & " selected>"
else
strMenu = strMenu & ">"
end if
strMenu = strMenu & rsData("TypeName")
strMenu = strMenu & "</option>"&vbNewLine
rsData.MoveNext
loop
strMenu = strMenu & "</select>"& vbNewLine
'kill the recordset
rsData.close
set rsData = nothing
'kill the connection
objConn.close
set objConn = nothing
'asign the menu to return value function
BuildInjuredTypeMenu = strMenu
End Function
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Function Build_PersonGrid(strGrid)
Dim intCount, x
If request.form("Count") = "" then
'set default number of lines in the grid, in this case 5(!)
intCount = 4
else
'retrieve current amount of lines in the grid, increment by 1
intCount = Cint(request.form("Count") + 1)
end if
Build_InjuredGrid = strGrid
End Function
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Sub Process_InsertEvent
Dim strSQL, intCount, x
intCount = request.form("Count")
'-----------------------------------------------------
'build ADODB.Connection here, should be called "objConn"
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "" 'accident'
'-----------------------------------------------------
response.write("Action was a succes. All data was processed in database")
End Sub
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Sub Main
Dim intAction
If request.form("cmd_AddNewLine") <> "" then
intAction = Cint(0)
elseif request.form("cmd_Process") <> "" then
intAction = Cint(1)
Else
intAction = Cint(0)
end if
Select Case intAction
Case 0
strGrid = Build_injuredGrid(strTemp)
Display_EventInsert(strGrid)
Case 1
Process_InsertEvent
End Select
End Sub
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Call Main()
%>
Ribeyed
03-21-2003, 05:28 AM
hi,
looking at your code i see you haven't got much of a form for taking the input for all your tables from the user. If you can build the form with all the textboxes/textareas that you need to insert into the database the we can help you with your insert statements. At the moment you have a form with only 1 textbox and 1 textarea for around 6 tables and 30 fields. So spend a few days preparing the form and i will help you process it.
simflex
03-21-2003, 08:26 AM
Hi [SWR]Ribeyed
Thanks for attempting to help me with this.
This is really appreciated.
A few things to make you aware of.
There are two forms. First called accidentReport.asp. This is carried over to the next form called propertyDamage.asp. The way the forms are designed is exactly the way the client wants it to look like.
They want to be able to enter data on the first form and click continue and complete data entry on the next form before submitting.
Once they hit the submit button, the other asp page that actually does the insert will be invoked.
If you find it necessary to make any changes, please do so.
I will probably post the forms seperately due to space limitations on the forum.
Again, thanks a bunch.
*******************
This is accidentReport.asp
***************************
</TD>
</TR>
*********************
I am splitting this page here. I will attach the other half.
*************************
simflex
03-21-2003, 08:35 AM
my apology for all these codes.
Here is a zipped file of the two pages.
Ribeyed
03-21-2003, 06:45 PM
hi,
ok you say you have 2 pages but you posted code for propertyDamage.asp, accidentReport.asp and you provided a zip file with a page called accidentTrac.asp, which pages am i working with?
simflex
03-21-2003, 08:45 PM
sorry!
I posted propertyDamage and when I attempted to post accidentReport page, I realized I was making the forum look vey untidy because the pages were long.
So I zipped them up.
If you don't mind, please ignore everything else I had posted and just grad the zipped file.
It contains both pages.
Thanks a lot for the help!
Ribeyed
03-21-2003, 08:59 PM
hi,
thats no problem. What database are you using?
simflex
03-21-2003, 09:45 PM
hi!
We have sql db but I would have no problems converting from access.
Please whatever db you are comfortable with.
thank you (can I just call you Ribeyed?)
Ribeyed
03-22-2003, 10:36 AM
hi Simfix,
right i have you code for the 2 pages, some small minor HTML syntax errors but nothing to worry about. I have taken your tables and fields and created an access database for testing purposes. Please find attached a .jpeg off the relation and relationships you have given me.
Ok lets take about your relationships between tables. First thing is the relationship between the location table and the AccidentEvent table. If you look at the image you will see that the relationship is showing as a one-to-many but you have each accidentevent can have many locations but each location can belong to only one accidentevent. This should be the other way around. Each AccidentEvent has only one location but each location can have many accidentevent.
Now I am looking at the age table is this relating to the Drivers table? Does all the drivers (first/second) have ages?
I will come back to the age table again. The damage and injured tables have no relationship with anything.
I see the vehicle table has a relationship with the accidentevent table but there is no relationship between the vehicle and the driver table. The type of relationship would depend on weather or not a driver can have an accident in one vehicle possible be able to have an accident in another vehicle? This can make this a many-to-many relationship and would require a relationship table with the primary key from the vehicle table and the primary key of the driver table as a composite key for the new table.
Now remember your first table structure you had, you had fields for up to 4 passengers. I will presume that you need to store data on all the passengers in the accident, how does this fit into your structure?
You haven’t said what the project is, what is it for? This would help me understand what will be happening with the database structure.
Structuring the database correctly will save a lot of hassle in the long run.
simflex
03-22-2003, 12:20 PM
hi [SWR]Ribeyed,
Relation between Location table and AccidentEvent:
You are right. It should be each accident occurring in 1 location and one location could have many accidents.
Age table: Each Driver/Other driver/passengers should have ages.
So age should have a one to many relationship with injured table, driver and otherdriver tables since many drivers/injured persons can the same age.
Vehicle: This is really a many to many relationship as one driver can driver many different company vehicles just as one vehicle can be driven by many different drivers.
This would create a bridge table perhaps called vehicleDriver table that will have a one to many relation with driver table and a one to many relation with the vehicle table and will have a structure like this:
vehicleDriver table:
vehicleDriverID autonumber int primary key,
vehicleId int (from vehicle table),
driverID int (from driver table)
Now remember your first table structure you had, you had fields for up to 4 passengers. I will presume that you need to store data on all the passengers in the accident, how does this fit into your structure?
This is where the injured table and injuredType tables come in.
Because each driver can have up to 5 passengers and in the case of an accident, info similar to my original datastructure will be obtained from each passenger or injured persons (presumable) on a given vehicle.
This is what led to the original posting and the first form I posted on the forum for you.
In this structure, injuredType will be like a look up table for injured table.
So injuredType table will contain:
InjuredTypeID,
InjuredTypeName
and on the injured table, you will have:
InjuredID auto number int primary key,
injuredTypeID (from injuredType table)
and the injured table will have a relationship with vehicle.
You haven’t said what the project is, what is it for? This would help me understand what will be happening with the database structure.
A brief history of this project:
A client company has employees who drive their vehicles.
As indicated earlier, each of their employees can drive any of their vehicles on a given day for a field trip and to run other errands.
There have been several cases of vehicular accidents involving their employees and non employees and this, according to them, happens ever so frequently that it is costing them a lot of money, not only in taking car of other drivers vehicles but also taking car of injuries/deaths in the event those happen.
The client has been manually tracking accidents and damages/compensations paid as a result.
Among the things the client is interested in is when an accident occurs, where, who is at fault, their employee or other driver.
If their employee, what caused the accident? was the employee under influence of something?
what was the weather like when accident occured, what are contributing factors. how many people in each vehicle and how many, if any, was injured?
the police involved in the accident, if sobreity test was given, result of that;
info about the police involved, police or accident report. day/hour /am or pm when accident occured.
damage to vehicles, injuires, if there is workman's comp as a result of the accident.
the amount of money each accident costs the company and how many times has each employee been involved in an accident (this will be obtained in a report).
I hope i did not leave anything out.
You seem to have a pretty good handle on database design and normalization.
If i missed something, please assist me.
thank you again for taking your valuable time to help.
Ribeyed
03-22-2003, 01:02 PM
hi,
ok that answered a good few questions, so but here is some more for you. I stil want to deal with the Driver/Other Driver/Passengers part. Was it your intention to use the Driver table to store Driver/Other Driver/Passengers together then have the drivertype table looking something like this:
TypeID TypeName
1 Primary Driver
2 Second Driver
3 Passenger
Are all Passengers part of the company?
Are some Passengers also Driver and Other Driver?
I could also see drivers being also second drivers and vise versa.
We may still have unnormalized tables in this structure.
Ribeyed
03-22-2003, 01:16 PM
hi,
i have another question this time about the Accident table to the police table. Currently you have a one-to-many relationship going each accident can have many police but each police can only have one accident, this is not the case. Can you confirm this situation because based on the information you have given i would presume that each accidentevents can have many police but also each police can have many accidentevents, which is a many-to-many relationship and would require a relation table.
simflex
03-22-2003, 02:00 PM
hi [SWR]Ribeyed,
Actually, I had Driver table and OtherDriver Table.
OtherDriver table has similar info as Driver Table, all relating back to the vehicle and accidentevents tables.
Now if it is your recommendation to have it the way you have it structured, I am up for that.
From what I have seen so far, I tend to respect your design skills a lot
Are all Passengers part of the company?
Yes, according to the client, a few employees could be on an offsite business carpooling which means that any one of the drivers can be a passenger and are all either part of the company or are from other non company vehicle.
I am having to think here on how to normalize this better.
I think we can proceed as it is unless of course you can come up with something before I do.
i would presume that each accidentevents can have many police but also each police can have many accidentevents, which is a many-to-many relationship and would require a relation table.
I like you already!
You are absolutely right. This is another design flaw on my part.
Let's break this up into another part call the bridge table accidentPolice table.
This would result to accidentPolice table having a one to many relationship with accidentevents and police tables.
accidenPolice
accidentPoliceID int primary key autonumber,
accidenteventID (from accidentevent table),
policeID (foreign key from police table).
You are really good. I am impressed.
Thank you for all the brainstorming.
Ribeyed
03-22-2003, 02:29 PM
hi,
ok, please find attached a new relationship diagram, have a look at it and let me know what you think. I am about 90% happy with the database structure and feel we have enough to start working on code. However i am still not sure about the relationships with driver/other driver/passenger. Let me try to explain why, all the drivers or other drivers are employees but not all passengers are employees. Based on this we could see a lot of repeated fields and records in the driver table, for example;
Jim drivers vehicle 1 and has an accident. 2 months later Jim is second driver in another accident, 1 year later Jim is a passanger in a third accident and then 6 months later Jim has his second crash as a driver and so on....
Unlucky Jim, lol :D
We could be looking a a lot of repeated data. Also we have the problem that not all passengers are employees
I am still having a think about this one. Maybe you have a suggestion about this?
Ribeyed
03-22-2003, 02:36 PM
hi
sorry:o
simflex
03-22-2003, 03:06 PM
hi [SWR]Ribeyed,
I am looking at the data model. Great job! and I thank you very very much for the help.
I do have a question, though.
I don't see injured table that uses injuredType as a lookup.
Did you blend that in somewhere, somehow?
all the drivers or other drivers are employees but not all passengers are employees
As far as the driver/other driver/passengers are concerned, let me break that down a bit.
The driver is the company driver. The other driver is the non company driver. What this means is when an employee is driving, that employee is the driver in a vehicle.
When accident occurs, the other driver is not an employee driver.
So as you can see from this, driver from company can have up to 5 passengers who can also be drivers but in the event of an accident, we treat the driver as the company driver and other employees in the car as passengers numbering up to 5.
Same holds true for the other driver who is not an employee and who could potentially be driving with with passehgers.
so drivers could be employees or non employees.
Passengers could be employees or non employees.
One other thing that I don't think I mentioned, and I don't know how this is going to throw us off.
There is an injury table.
This is a prepoluated table taken from the police dept.
It has this structure:
injuryID int primary key,
injuryDesc varchar(2000)
eg
injuryid injurydesc
1 head injury.
This is a look up take for injury table and probably should replace the injuredType table.
sorry about this.
Ribeyed
03-22-2003, 03:10 PM
hi,
the tblInjuredType is there its down right corner of image relating to the driver table.
Ribeyed
03-22-2003, 03:12 PM
by the sound of things Driver is different from Other driver and passenger but Other driver and passenger seem to be the same thing in this case. Maybe look at it as Drivers and others table , others table would be other driver or passanger because it seems to not matter if your an other driver or a passenger.
Ribeyed
03-22-2003, 03:36 PM
hi,
i have created an storeRec.asp page to start building the code for inserting this into the database. I usually run the bellow code to check to see what variables and values are being posted.
<body>
<%
for each item in request.Form
response.write item & " = " & request.form(item) & "<BR>"
next
%>
</body>
</html>
Run this code and have a look at the results. I see a few values not coming through to the storeRec.asp. I also se multiple values for certain variables, nothing wrong with that, but to make things easier can you make them all seperate e.g. street1, street2, street3 and driver1, passenger1, passenger2, passenger3 etc.
simflex
03-22-2003, 04:37 PM
hi,
Thank you very mucn for all this help.
Can you please tell me how this code is going to insert records into the database we created?
To me, this is the more challenging part.
Ribeyed
03-22-2003, 04:51 PM
all in good time we will get to the actual code for this. There is no way i can do any inserting data into database until the database structure is working the way we need it. At the moment you haven't answered any of my above questions regarding the relationships between driver/other driver/passenger.
The last post i ask you to fix problems the the values and variables that make up the insert statements. Looking at the information shown by the storeRec.asp i am finding it hard to match them with the fields in the database. You have fields in the database that don't apear in the posted form from the propertyDamage.asp to the storeRec.asp page. There are also a lot of variables being posted from the form to the storeRec.asp that i don't see fields for in the database.
Lets get this sorted out before it starts causing problems when coding.
simflex
03-22-2003, 06:52 PM
ok, I see where you are coming from.
Give me time to look more into the driver/otherdriver/passenger situation.
Meanwhile, I don't have storeRec.asp.
The first form I sent you is not storeRec.asp.
There will, however be a page called storeRec.asp.
The other thing to note is the database structure we have been working do not have all the fields matching the two forms I posted - propertyDamage and accidentReport.
I did that on purpose. The reason I did that was once we figure out how to insert records into the database with the structure we have, I was going to add the rest of the fields.
But give sometime to put them all together and post back.
You have been of tremendous help, thank you.
Ribeyed
03-22-2003, 07:14 PM
hi,
are there any more fields needed in the database?
simflex
03-22-2003, 08:34 PM
yes, there are!
They are insignificant in the sense that they will affect relationships between tables.
Let me put all the entities and attributes we talked about so far, plus resolve the driver/otherdriver/passenger issues and I will post an attachment to you.
You have been extraodinarily helpful so far.
Thank you.
simflex
03-23-2003, 08:54 AM
hi,
I have made some modifications to the relationship tables.
A few things to note.
1, in addition to what we had, I added a few tables. They are dept table that determines what dept the company driver is in; injured table, this will be the passengers table. The only time the passenger table comes into play is when there is an accident. This table just lists names of passengers in the car. If we can somehow incorporate a part of the first code I sent, the part that has a grid and defaults to 5 persons in the vehicle.
If we can incorporate that then that should take of the passenger (injured) information.
The grid defaults to 5 with the ability to add more but we can make the grid default to 1 with the ability to add more, should there be more.
I have prepopulated those tables that need to be by using the information obtained from the police. Those are tblweather, tblcollision, tbldamage, tblfactors, tbldriverContition and tblInjuryType. These are lookup tables and data in them are real.
The dept will also be prepopulated. Right now, I entered dummy data into it for testing purposes only.
In addition to tbldriver and tbldrivertype, I added tblotherdriver.
This will allow us to obtain information about other driver.
I believe that I have captured all the fields on the two forms I sent you. However, I have not changed the streets to city1, state1, etc.
I am hoping to do those once I receive the storeRec.asp.
L also added insurance co table. This allows us to obtain information about vehicles and their insurance co.
I am enclosing the entire tables and a screen shot of the erd.
Please let me know if they have all come together.
I was up till 3am playing around with this.
Thanks wholeheartedly for the continued assistance.
simflex
03-23-2003, 09:06 AM
I forgot to attach the db and the relationship diagram.
the db is too big even after it is zipped up.
I will just attach the relationship screen shot.
Ribeyed
03-23-2003, 09:18 AM
hi,
good stuff, but it would be handy if i was able to get the database as well, let me see the data in these tables, can you private me and maybe we can arrange to ftp the database somewhere?
simflex
03-23-2003, 09:47 AM
well the forum is not allowing me to send any of those files even though they are zipped.
Is there anyway I can send those files to you.
It is signifcant to get them to you rather than explain them because not only do they address the driver/otherdriver/passenger issue but all the fields matching the two forms are also included.
Please let me know.
Thanks
Ribeyed
03-23-2003, 09:56 AM
hi,
i set up my ftp server so you can ftp to here:
ftp://80.195.1.157
Let me know when you have transfered the database and files.
simflex
03-23-2003, 10:26 AM
hi [SWR]Ribeyed!
I have ftp'ed the db files and the relationship.jpg.
Please let me know if they all fit together.
Thank you several t imes for your help.
Ribeyed
03-23-2003, 10:37 AM
hi,
just quickly when creating relationships in access you need to check the checkbox Enforce Referentail Integrity. Im still not happy with the tblDriver and tblOtherDriver, its the same fields in both tables, surely these 2 tables should only be 1.
Ribeyed
03-23-2003, 12:28 PM
hi simflex,
can you go back to my ftp site and download the database. I have made some changes and i think we should be ready now if you agree whith what i did.
simflex
03-23-2003, 12:29 PM
hi,
can we make it one then and just use drivertype to determine if the driver is a company driver or non company driver?
Can we do that; rhis way, drivertype becomes our lookup table.
Please advise.
I honestly appreciate you trying to make me look good.
simflex
03-23-2003, 12:36 PM
can you go back to my ftp site and download the database. I have made some changes and i think we should be ready now if you agree whith what i did.
hi,
I hadn't seen this post before my last.
please disregard last post.
I have taken a look and it looks really good.
thanks really much.
Ribeyed
03-23-2003, 01:12 PM
hi Simflex,
ok so you had a look at the structure, my thinking is you have a driver which you need drivernumber etc. Everyone else is a passanger. Using the tblPassengertype we can tell if the passanger is the other driver or a normal passanger. Now every driver and passanger is entered into tbldriver and tblpassenger tables. If the driver if injured a record is entered into the tbldriverinjured table same for passengers. The tblWorkmansComp needs to generate the CompID for the tbldriverInjured and tblPassengerInjured tables.
I think this can work and we will have more idea when we get to coding it. Now all we need is the forms you have updated to tie with the database structure.
simflex
03-23-2003, 03:34 PM
hi,
one minor but important change.
On the tblvehicle table, damageCost should not be there.
it should be in accidentevent table.
I will be sending the forms shortly.
Great work and I am truly grateful.
Ribeyed
03-23-2003, 03:35 PM
why do you need to store damagecost twice?
edited:
Forget this never read your post correctly. My thinking was storing the vehicle damge cost in the tblvehicle would be added to all the cost for comp and a total for the whole accident can be shown and can be display spit up to vehicle damage and comp costs. Would wouldn't store this anywhere because all the values are already there we just need to calculate it.
simflex
03-23-2003, 09:39 PM
hi [SWR]Ribeyed,
Sorry it took awhile.
Here is the updated version of the two forms.
I hope that everything in there is fine.NameLegal text,
OtherNameLegal
Witnesses
WitnessID int autoNumber primary key,
StreetAdd text,
cityID int,
stateID int,
zipID int
I do have two more modifications to the data model.
Please forgive me for this oversight.
There will be no more as I have taken the time to ensure this.
One change is the legal name of the company vehicle and one for the other vehicle.
These are called NameLegal and OtherNameLegal. They should be in the accidentEvent table.
Then a table that I overlooked. It is called the witness table.
The witness table has witnessID an autonumber, name of witness(s) and city, state, zip and accidentEventID.
I apologize for this.
Here is the details.
For accidentEvent table
NameLegal varchar(50),
OtherNameLegal varchar(50)
Witnesses table
WitnessID int autoNumber primary key,
StreetAdd varchar(50),
cityID int (from city table),
stateID int (from state table),
zipID int ( from the zip table),
accidentEventID (from accidentEvent table)
I can't tell you how truly grateful I am.
I am attaching the updated forms
Ribeyed
03-24-2003, 08:16 PM
HI Simflex,
i got your 2 pages and i still have a lot of problems with it. Please have a look at the 2 images i have put on my ftp site.
ftp://80.195.1.157
This is the output of all the form being passed to the storeRec.asp page. Let me go through some of the list. The top one, OtherNameLegal, where does this go in the database? Further down you have Namelegal where does this go in the database?
You have InsuredStreeAdd and ownerStreetadd again where do these go?
You said that there can be 1 driver and maximum of 5 passengers. Each of these have names, addresses, cities, zipcode, states, i only see 3 cities, 3 states, i see 6 zip codes all with obscure names with 2 grouped together. You have Curb2 twice. You have rateofspeed and rateofspeed2 but only 1 field in the database. Same with sideofstreet. This leads me to think that you need to store data about the other vehicle in the accident, but there is no table for this. You have otherdamageEstimate which again i think is meant for the other car again not field or table for this. You have only 1 where taken which i think is for any driver or passenger that was injured, you need 6 of these. Same with by whom, what happens is 6 people are injured and 6 people are taken to hospital from 6 different people?
You say multiple police can be related to one accident why does your form only allow for 1 entry?
Again i see a lot of othervehicle stuff but there is no table for this.
Funny one this, sorry but you have streetlights and streelights2, i put yes to one an no to the other, which one would it be?
You have address2 and vehicleModel but no value for them.
I think you need to review your 2 forms to coincide with the database structure, make room for taking 1 driver and up to 5 passengers.
The police part is a bit more tricky. Because you don't know how many police details you need to take you would need to build this on the fly.
Maybe on the first page have a drop down box with numbers for how many police attended the accident. You can then provide the correct number of spaces for the police on the second form.
simflex
03-25-2003, 08:53 AM
hi,
First thank you!
I am sure most helpers would probably have given up on me by now.
This started out looking simple. As I attemped to design the db, it became increasingly more difficult and it is still difficult because the more we do it, the more questions come up.
We will resolve them all. Please continue to be patient.
Second, I can't access your ftp site anymore. Not sure why. So I can't see the two images in there.
About the many legitimate questions you raised, the client has a hard copy form they use which they are trying to automate.
That form looks exactly like the two forms I sent to you, except some typing errors on my part.
If you take a look at the post I sent last night (one with the updatedAccidentTrac attachment, I indicated that I added two new fields to the AccidentEvent table.
They are nameLegal and OthernameLegal.
The first nameLegal refers to who is the legal owner of the vehicle the company employee is driving. This by default, will be the company since the whole essence of this project is to document the accidents that occur while someone is driving company vehicle.
OtherNameLegal field is for the owner of the other driver of the other car.
Bear in mind that the driver of a car is not necessarily the owner of the car.
So these fields should go the accidentEvent tabe.
The InsuredStreetAdd should be InjuredStreetAdd.
Anything that said Insured, eg InsuredAge, InsuredCity, InsuredState, InsuredZipCode, The word Insured Should be Injured.
Again a typo on my part.
We have tblPasssengerInjured and tblDriverInjured.
We just need to add InjuredStreetAdd to tblPassengerInjured table and CityID, StateID, AgeID and ZipID to that table.
So tblePassengerInjured should now look like this:
InjuredID,
PassengerID,
InjuredPhone,
InjuredStreetAdd,
CityID,
StateID,
ZipID,
WhereTaken,
ByWhom,
CompID,
InjuryTypeID
Similar adjustment should be made to tblDriverInjured table.
DriverID,
DriverTypeID,
InjuryTypeID,
WhereTaken,
ByWhom,
InjuredPhone,
InjuredStreetAdd,
CityID,
StateID,
ZipID,
AgeID
i see 6 zip codes all with obscure names with 2 grouped together
I am confused about this. Please help clarify this statement.
You have Curb2 twice - I only see one. It is possible that I have two but I only see one.
One is called Curb and the other should be Curd2.
The first Curb refers to how far from the Curb the the Company driver is.
The second Curb, Curb2 refers to how far the otherDriver is from the Curb.
The following should be on the accidentEvent table:
nameLegal
OthernameLegal
rateOfspeed,
RateofSpeed2,
Curb,
Curb2,
sideOfstreet,
SideOfstreet2,
streetLights,
StreetLights2
otherdamageEstimate - this is why I suggested we move damageEstimate from tblvehicle to accidentEvent table.
Funny one this, sorry but you have streetlights and streelights2, i put yes to one an no to the other, which one would it be?
Again, streetLights and streetLights2 refer to "Were Your street Lights On? Two questions, one for company driver and the other for otherDriver.
Again i see a lot of othervehicle stuff but there is no table for this
can we have a table called tblVehicleType. This will contain
vehicleTypeID - relates to tblVehicle,
vehicleType - this determines if the vehicle is company vehicle or nonCompany vehicle
VehicleDesc - Honda accord, toyota 2dr etc.
If not, then we just duplicate fields from tblVehicle except we will call it otherVehcile and every field will contain the prefix "Other". Please let me know what you think about this.
You have address2 and vehicleModel but no value for them.
when you say no value, do you mean data?
If yes, data will be entered as accidents occur
If this is what you mean:
<input type="hidden" name="ADDRESS2" value="<%= Request.Form("ADDRESS2") %>">
<input type="hidden" name="CITY" value="<%= Request.Form("CITY") %>">
<input type="hidden" name="STATE" value="<%= Request.Form("STATE") %>">
<input type="hidden" name="ZIPCODE" value="<%= Request.Form("ZIPCODE") %>">
then I have removed this part. I always taught it is a good iead to have address2 but I have removed this.
this correction was made to propertyDamage form. I don't know if you caught it:
I think you need to review your 2 forms to coincide with the database structure, make room for taking 1 driver and up to 5 passengers.
The police part is a bit more tricky. Because you don't know how many police details you need to take you would need to build this on the fly.
If you go back and look at my original thread, this is exactly what led to the first question I posted.
I am having all kinds of problems designing the form when it comes to 1 police to many accidents or 1 driver with 5 passengers.
That is why I refered to the original form that has 5 default grids for processing information about the injured.
Please let me know what you want me to do next.
I am sorry it is not looking clear.
I am trying.
Ribeyed
03-25-2003, 09:26 AM
hi Simflex,
You are doing fine, yes your right the database has been tricky but we are almost there now. Please be patient with me i do work full time developing e-commerce sites so i only get a few hours to help out here.
I was fully aware at the start the amount of help you would need and this is not just one small problem more of a whole project problem. I understand that this will be a large learning curve for you but be assured i will be patient.
Because of the complexity of your project it has been hard for me to understand what you are trying to do obviously because i haven't had any access to the project specifications accept from the information you have given me in the posts.
Let me do some modifications with the database to take into consideration the Other Car/Driver Details and I’ll put it on the ftp site. The ftp should be working but this is on my own PC and therefore not on 24/7, keeps checking back.
If you have any more information about the project specifications maybe you can put them on the ftp for me to take a look at. I feel from what you have said that your forms don't cover the specifications and i would like to look at this further.
Later on today I’ll give you and example of the forms i would use to do this. We can then workout of anything is missing or not correct then we can take it from there.
Ribeyed
03-25-2003, 04:32 PM
hi Simflex,
i have been working on your first form, in zip file bellow bellow. I have set up an ODBC database connection, you may have to change this to your own way. Please go to:ftp://80.195.1.157
Download the new database, you will notice a few changes, this is mainly based on the information gathered from previous posts. IF you have any questions about the page or database just ask away.
simflex
03-25-2003, 07:04 PM
hi [SWR]Ribeyed,
I apologize for the late response.
We had a meeting that last several hours and I left after the meeting.
I want to thank you again for all the help.
I understand that you are busy and I won't ask of you more than you can give. Quite frankly, you have given a lot already.
Now I have taken a look at the new data model.
I looks good and fully normalized. I just have one question.
I don't see the following fields: Curb2, streetlights2, sideofstreet2. nameLegal and OtherNamelegal.
I don't think I left something out.
How are we addressing those?
Oh, never mind, the form clearly captures that.
Speaking of the form, it REALLY looks GREAT. You have a greate test for layouts.
The layout looks wonderful. I wouldn't have thought of doing it that way.
My questions (always one) is I don't see where it says "Describe Accident", "Describe damage to car"?
Are we going to just use the preopulated data or is the accident report going to be used.
Lastly, how are we going to capture police info, like name, badgeno, headquarter?
As far as the police is concerned, maybe we can use one input text and enter as many as police are on an accident scene and comma-delimit them. What do you think?
I can't thank you enough.
Ribeyed
03-25-2003, 07:16 PM
hi,
Glad you're almost happy with the database and the form.
The fields Curb2, streetlights2 etc. are not needed. On the form you take details for 2 vehicles, 2 records are entered in to the tblvehicle. The relating table tblVehicletype has first vehicle and second vehicle with a vehicletypeID foriegn key in the tblvehicle.
At the bottom of the page there is a section that asks for accident details, which includes Describe Accident populated from the tblCollisions. On the form Vechile 1 section and Vehicle 2 section have list/menu for damage to car.
If you noticed on the form in vehicle 1 and vehicle 2 questions it asks how many passengers this is so we know how many textboxes we need for the passengers in each vehicle. Following this there is a textbox that takes the no of police at the accident. Say you enter 4 the next page will provide 4 spaces for badge number, 4 spaces for police details etc.
simflex
03-25-2003, 08:14 PM
Great stuff! quite understood.
Now NameLegal and OtherNameLegal.
They specifically ask that we give names of legal owners of vehicle.
As you know, the driver of a car is not necessarily the owner; can we enter this information on the sections that ask "Please tell us about this vehicle"
Although I don't see that in the data model, can we create two fields in tblvehicle:
NameLegalOwner and OtherNameLegal Owner.
I know the company one will default to the company name but the other will be entered in case of an accident.
please let me know what you think about this.
Thanks a heck of a lot for all the help!
Ribeyed
03-26-2003, 05:23 AM
hi,
got that will add in a field into tblVehicle for the legal owner. We only need 1 field for this, remember 2 records will go in this table for every accident. I have added to the first form i sent you and i will add room for legal owners asap.
Ribeyed
03-26-2003, 10:10 PM
hi SimFlex,
I have attached a new propertyDamage.asp page. Again any questions just ask. Need to do very minor work on the database but its looking like it is almost complete.
Again depending on the number of passengers per vehicle, witnesses and police the correct number of textboxes will be created for all the details needed.
simflex
03-26-2003, 11:55 PM
hi,
thanks again for all these work.
I want you to know that it is really appreciated.
When I ran this code, it is almost blank.
I have attached a screen shot of the page I am getting when I run the propertydamage.asp.
Please tell me if it is supposed to be like that or I am doing something wrong.
Ribeyed
03-27-2003, 04:29 AM
hi Simflex,
This form will be almost blank if the following scenario occurs
0 Passengers in vehicle 1
0 Injured Passengers in vehicle 1
0 passengers in vehicle 2
0 Injured Passengers in vehicle 2
0 Police attended the accident
0 witnesses to the accident.
Remember i am only give you suggestions on the way to do this, you will have to go over these 2 pages and add some form of validation to make sure the fields you require on the first form are filled in and completed. I suspect you just ran the first page never filled in anything and submitted the form, if this was the case sure the next form would be almost blank, should be totally blank.
simflex
03-27-2003, 06:56 AM
hi [SWR]Ribeyed,
I see now. It looks really good.
I have one question, though.
When I fill the first page and go to the second page, only passenger information about the first page gets displayed.
For example, when I select 3 passengers from first vehicle and say 2 passengers from the second vehicle and click "continus to next page, the next page does not display information about the second vehicle.
It displays information about passengers in the first vehicle and police info.
Any ideas what I might be doing wrong?
thanks for all the time and efforts.
Ribeyed
03-27-2003, 02:55 PM
hi Simflex,
Ok please find attache some new pages. Nothing much changed apart from fixing some errors in the logic of the 2 pages.
The second form page is build on the fly using values from the first form page. These are:
Number of Passanger in vehicle 1
Number of Passanger in vehicle 2
Number of Injured Passangers in vehicle 1
Number of Injured Passangers in vehicle 2
Number of whitnesses
Number of police at the accident.
So if the answer to all these are 0 then nothing needs to be done so the page will move past and on to the last page.
Say the user enters 3 passengers for vehicle 1 and 1 was injured then 1 injuered passenger and 2 passengers sections will be displayed to take details.
As i mentioned in previous post you will have to add some error trapping and form validation code to stop the user submitting somthing like this:
Number of Passanger in vehicle 1 = 3
Number of Injured Passangers in vehicle 1 = 4
this will cause errors.
I added some list/menus for the NoOfwhitnesses and NoOfPolice just to force the user to select from 0-5 (you can change this).
Your question about information for the passengers in vehicle 2, well it worked ok for me so i'm thinking maybe you had something like this in the form:
Number of Passanger in vehicle 2 = 3
Number of Injured Passangers in vehicle 2 = 4
try testing a few combinations of no of passengers in vehicle one and two, no of injured passengers in vehicle 1 and 2, no of whitnesses 0 or 5 and same thing with no of police, finally test with all at 0.
Once your happy with these forms we can begin the inputting of all this into the database.
simflex
03-27-2003, 03:12 PM
you are really a great human being.
I have just downloaded it.
I will test in a sec.
Thank you, really!!
simflex
03-27-2003, 04:12 PM
[SWR]Ribeyed, this is perfect. No more changes are necessary.
It looks really, really good and everything works great.
You are a great thinker.
Thanks again and again, I am 100% happy with it.
Ribeyed
03-27-2003, 06:48 PM
Hi Simflex,
good stuff m8, i'll start on the storerec.asp page this will not take long as all the information for everything is now there and the database structure is ready to take the input.
Will post a page to you soon.
Ribeyed
03-30-2003, 03:44 PM
hi Simflex,
Haven't forgot about you been busy this week with work. I've started your storerec.asp tonight it shouldn't take long, but not sure if it will be finished tonight. Will post soon.:D
simflex
03-30-2003, 07:37 PM
hi [SWR]Ribeyed, you are highly appreciated and I totally understand how busy you are.
Your job MUST come first, please.
Thank you again for everything.
Ribeyed
04-01-2003, 04:12 PM
hi Simflex,
just to give you an update, go to ftp://80.195.1.157 and download the new database, storerec.asp, accidentreport.asp and proertydamage.asp files.
These are not the finished pages but are in working order so you can run what is there so far. This will let you see how i am going about inserting all this data into the database. I am still very limited in the amount of time i can spend doing this for you, but don't worry it shouldn't be long now:D
simflex
04-01-2003, 04:42 PM
hi Ribeyed,
I am truly indebted to you for all your help.
I do recognize how tied up you are and I am even more grateful that you still have time to work on.
I wouldn't have gotten this far without your assistance; besides, this project is not easy at all.
I thank you Ribeyed wholeheartedly.
simflex
04-02-2003, 10:48 AM
hi Ribeyed,
I have got a few questions.
First, when I tried to submit data to the database just so I can get a better sense of what is going on with storeRec.asp and the two forms, I received the following errors:
accidentDate is an invalid field in the db.
I looked into the database that I downloaded, I saw DateOfRpt but not accidentDate,
I also received errors that CaseNumber is a required field in in tblePolice table but it is not on the insert statement in storeRec.asp.
Then CaseNumber is being inserted into tblaccidentPolice table but based on what I downloaded there is no CaseNumber in tblAccidentPolice table.
I tried downloading the database but could not gain access.
Can you please let me download the database again; I may have mixed it up in the midst of my euphoria.
Again, you have been extremely kind.
Ribeyed
04-02-2003, 12:18 PM
hi,
will look at these for you, you can download database most of tonight.
simflex
04-03-2003, 08:30 PM
hi Ribeyed,
Trust me I am extremely uncomfortable asking you this favor because I recognize how busy you are.
Can you spare a few momemts to kind of explain what else is left to be done to complete storeRec.asp.
I tried inserting data into the database just to get a sense of what is going with the code but after storeRec had been submitted, most of the tables, including some that have been coded, like tblPolice, tblaccidentevent do not have data.
I have been studying your code and are fairly easy to follow (Not easy to code) because you did a great job of commenting them but I still lost as to what to do next.
Our client are coming over tomorrow to ask us to show them what we have so far.
As a result, I am calling in sick tomorrow to avoid trouble.
Recognizing how busy you are, I was wondering if you can just let me know what else needs to be done so I can try and see if I can complete this before heading back to work on Monday.
I still have viewAccidentRec.asp to code and I have designed the form but can't do much with it until this is done.
I can't tell you how truly appreciative I am of what you have been able to do for me so far given the scope, complexity of the task and your own busy schedule.
Thank you much
Ribeyed
04-03-2003, 08:35 PM
hi, give me 1 hour to see what i can get done for you
Ribeyed
04-03-2003, 08:42 PM
hi simflex,
o just ran the pages and everything i have coded so far works at my end. Let me ask a few silly questions.
you are filling all the text areas, list/menus etc?
When a number your submitting a number?
When asked home many passengers and how many injured passengers you are making the passenger higher or equal to the injured passengers?
Ribeyed
04-03-2003, 08:47 PM
hi simflex,
ok so far on the storeRec.asp page we have;
created an accidentevent then inserted the police and witness details into the database. Next thing i will be doning is sorting and inserting the 2 sets of vehicle details. This will generate a vehicleID which we will use to link the driver details to the vehicles. Next we will insert the driver details.
simflex
04-03-2003, 09:31 PM
hi Ribeyed!
I added an error check like you instructed so that if a user inserts more injuredPassengers than passengers, an error is thrown.
So when I test, I test with either same number of passengers as injured or more passengers than injured.
And I filled every field before submitting.
Infact, I like the fact that you printed a copy of the forms after submitting.
Remember response.write ("item") = request.form("item").
I went through the entire list and everything was there but when I submitted, several of the tables were blank.
Ribeyed
04-03-2003, 09:34 PM
hi simflex,
yes several tables are blank because i haven't written the code yet. IF that page displays the information from the form then the code worked. Look in tblpolice, tblAccidentevent, tblwitnesses, tblcity, tblzip, tblstate you will find data there.
Ribeyed
04-06-2003, 02:09 PM
Hi Simflex,
been working on the storeRec.asp page. Had to make sight adjustments to the database and the 2 form pages. If you go to my ftp site:ftp://80.195.1.157 you can download the new files. I have also put a new storeRec.asp there so you can see what i have been doing.
simflex
04-06-2003, 07:30 PM
Hi Ribeyed,
I can't explain in a way that you will understand how truly thankful I am for the help I am getting from you especially given how busy you are.
Thank you sincerely.
I will sip through them to understand what changes for my own benefit.
You are truly blessed with the ability to learn and share.
Thank you!
simflex
04-06-2003, 10:53 PM
hi [SWR]Ribeyed,
You ought to be very proud of yourself for you are truly gifted.
I do believe I am up to speed on asp but I can't even breath the same air as you.
I made one slight change.
I changed the True/False to 1 and 0 respectfully since I am using SQL server db.
SQL Server is rejecting it. When I code the displayAccidentRec.asp, I can use CASE statement to convert them back to True/False.
I also changed this part:
thissql = "SELECT tblPolice.badgeID FROM tblPolice WHERE tblPolice.BadgeID = "&OfficerBadgeID&""
set PoliceIDSet = safetyDB.Execute(thissql)
PoliceID = PoliceIDSet("PoliceID")
TO
thissql = "SELECT tblPolice.PoliceID FROM tblPolice WHERE tblPolice.BadgeID = "&OfficerBadgeID&""
set PoliceIDSet = safetyDB.Execute(thissql)
PoliceID = PoliceIDSet("PoliceID")
beccause I was getting error that PoliceID is invalid on this line: PoliceID = PoliceIDSet("PoliceID")
It seems to insert data now in database.
You deserve some stars.
Ribeyed
04-20-2003, 07:01 PM
hi Simflex,
been doing some more work on your pages please go to my ftp site and d/l the new versions.
ftp://80.195.1.157
Ribeyed
04-21-2003, 08:30 AM
Hi Simflex,
quick question where is the data for the tblWorkmansComp comming from?
fields: agency, med_pymts, wc_pymts and inj_lv_Pd what are these and where does the data come from?
can you please explain them to me thanks.
simflex
04-21-2003, 06:00 PM
hi,
Bless your heart!
Sorry, I haven't checked this for 2 days.
Hope all is well with you.
The WorkmansComp table is intended for logging information about workers who get injured while an accident.
If an accident occurs, the safety admin is responsible for determining if the accident is severe enough for the driver to be on workmans' comp.
If that is the case, the medical cost of his/her injury (Med_Pymts) and workmans compensation paid to the individual (WC_Pymts) are then logged into the workmanscomp table.
The Agency field is an identification number for the driver.
Thanks for all the help, despite your busy schedule.
Ribeyed
04-21-2003, 07:28 PM
hi,
does or can the passengers also get payments?
simflex
04-22-2003, 07:35 AM
hi Ribeyed,
Yes, only if they are employees of the company.
-s
Ribeyed
04-23-2003, 07:57 AM
HI Simflex,
right hmmm..... that could cause a slight problem, but it will not effect all that we have done to date. Ok i'm going to code the rest right up to this bit, then we can deal with this problem at the end.
Ribeyed
04-23-2003, 08:55 AM
Hi Simflex,
quick question, does it really matter if a passenger is a second driver, is it going to make any difference anywhere, do you have to know that a passenger was a second driver?
I just can't see where it would matter unless they would get more or less comp for the the accident.
simflex
04-23-2003, 11:51 AM
Please ignore the workmansComp issue.
I will code that myself.
Perhaps I should have told you that yesterday.
One of the things I noticed from the code I tested so far is that if there is a situation where there is no passenger or no witness but there is a police present (as it is always the case in the case of an accident), the system forces you to enter some data for passengers and witnesses.
It will be nice if the client doesn't have to enter any data in any field if s/he chooses not to.
simflex
04-26-2003, 08:33 AM
hi Ribeyed,
Sorry I went back to the forum and saw this question:
Hi Simflex, quick question, does it really matter if a passenger is a second driver, is it going to make any difference anywhere, do you have to know that a passenger was a second driver?
I just can't see where it would matter unless they would get more or less comp for the the accident.
I had not read it until now. You are right, it doesn't matter if a passenger is a second driver.
I have got a couple of questions, though:
One, how much do you think is left to be done and don't you think I can complete it?
As you know, this is a real project and like any project, it has a deadline.
Over the past week, I am getting pressured to show that I am making some progress with this giving that it has been more than a month since I started.
As a result of this pressure, I wrote a stored procedure that will insert all the records but there have been some problems with it.
To make the stored procedure work, I had to make several changes to what you did.
I saved what you have done so far because it is eventually what I have to use because it is more robust and better designed than what I have shown them.
You have really put in a ton of your time into this and I even respect more the fact that you are extremely busy with other stuff.
So please tell me what else is left to be done what it will take me to complete them.
The other question is can I download the most current version that you have?
Thanks a million for all the help.
I can assure you that I would have been worse without your help.
Ribeyed
04-26-2003, 04:10 PM
hi Simflex,
yes i will push along and try get you something to show your boss/client. I am working on the passengers now and then the last bit will be the injured passengers. Once i have all the data going into the database it will be up to you to work on valitation. This will be nessesary because at the moment a lot of errors will occur if the wrong data is submitted. What you would need to do is to go through the code and stop the wrong datafrom being submitted.
Will post again tomorrow and let you know that the updated files are ready for to be downloaded.
simflex
04-26-2003, 07:45 PM
hi Ribeyed,
Thanks again for great effort you have put, and continue to put into this to make it happen for me.
It is indeed very well appreciated.
One thing to note and I am not sure if it is deliberate on your part or it is just an oversight.
The witness table has not been coded; as a result, no data is being stored in it.
If you haven't already coded, I am attaching the code I wrote following your example.
If you have already done, thanks again and accept my apology for my incursion.
The other thing is, as I indicated in an earlier thread (about a day or two ago), the client need to ability to skip inserting data into any category or table.
For instance, right now, if you select 0 witness, you won't be allowed to submit data to the database until some data is entered for witness. I am just using witness as an example.
It happens for all of the them.
I have been trying to ascertain what portion of the code is making it mandatory that data is entered in every field.
If you can redline the part(s) of code that is doing that, I can try and remove that restriction.
Again, thank you very much.
Your reward may not come from me but it will certainly come your way one day.
simflex
04-26-2003, 07:54 PM
Oh, here is that piece of code for witness.
Again, I did it because I did it because I did not see it in your version.
This witness insert statement was created after the witnesszip.
I am not even sure it was done correctly; I just know that it inserts records into the witness table correctly.
'now lets insert into the witness table
safetyDB.Execute = "INSERT INTO tblWitnesses (WitnessName, WitnessHomePhone,WitnessWorkPhone,WitnessAddress,CityID,StateID,ZipID,AccidentEventID) VALUES ('"&WitnessName&"', '"&WitnessHomePhone&"','"&WitnessWorkPhone&"','"&WitnessAddress&"', "&CityID&","&StateID&","&witnessZip&","&AccidentEventID&")"
'now the select. We know that the last witnesses that was insterted is going to have the highest WitnessID
'because of the autonumber for the primary key field so lets order the select to have the highest WitnessID
'first, this way we are sure we get the correct WitnessID
thissql = "SELECT tblWitnesses.WitnessID FROM tblWitnesses ORDER BY tblWitnesses.WitnessID DESC"
set WitSet = safetyDB.Execute(thissql)
WitnessID = WitSet("WitnessID")
WitSet.Close
set WitSet = nothing
Ribeyed
04-26-2003, 09:14 PM
well spotted and well done with the witness code, yes another oversight on my part, i have added part of your code into the page. You don't need the bottom select statement and if we did it would be wrong. The reason being that it is inside a loop therefore the first witnessID would get written over by the second witnessID.
The other thing is, as I indicated in an earlier thread (about a day or two ago), the client need to ability to skip inserting data into any category or table.
Yes I know that, that is the norm, ok just a wire crossed here. I am writing the code to insert all the data into the database, for everything.
Personally i have found it easier to code to insert all the data for every field in every table first. Once i have this done then i go back over my code and the code for scenarios where data is missing, data is inaccurate etc. What i am saying is you will have to work through the code adding in the validation code. This should do things like allow for no data to be entered. You should also write code that will stop the user from selecting 2 passengers and selected 3 of them to be injured.
For instance, right now, if you select 0 witnesses, you won't be allowed to submit data to the database until some data is entered for witness. I am just using witness as an example.
ok can you be more specific?
Having a look at the information being gathered on the 2 forms i can only see 1 or 2 textboxes that may or may not be filled in, all the rest look to be mandatory. However if the data from the first form has 0 for passengers, injured passengers, witnesses and police we will need to add code to skip the second form/page and go to the store page but pass through all the details, this I know about.
I think you need to either decide to use JavaScript to validate the forms or validate it using ASP. JavaScript is fine if you want to use it, I prefer not to use it and tend to validate using ASP. If you decide to validate using ASP then your going to have to combine all 3 pages into the one page. Combine the 3 pages would also help with the scenario where there are no witnesses, passengers, injured passengers or police. Again it would help you to make sure the user doesn’t select 2 passengers and have 3 injured passengers etc.
Other things you need to trap and validate is where the user fills in only part of the vehicle information, you need to write code that would make sure that if the user enters some of the details the will need to complete all the textboxes that are associated with that vehicle.
You need to validate that if the driver’s first name is entered and the surname is not, the address is entered but not the zip code. If the driver was injured and the users selects say “serious” as the injury then the takebywhom and wheretaken textboxes have information entered for them.
These are a few issues that you will need to work on.
I have been trying to ascertain what portion of the code is making it mandatory that data is entered in every field
there is not code anywhere that makes it mandatory only lack of validation code as explained above.
simflex
04-27-2003, 12:20 AM
ok, good stuff and point well taken.
I have already written the validation code for passengers.
As it stands, a user CAN'T select more injured passengers than there are passengers in a vehicle.
That is taken care of.
As far as police infor is concerned, there spec says that in any accident, there must be atleast one police officer present or it is NOT an accident.
So there must be at lease one police officer info recorded by client.
Other validation issues you raised, I will have to take care of.
Again, thank you!
______________
ok can you be more specific?
_________________
You have already addressed this.
This means that as at now, no textbox can be blank.
I am working on fixing this.
Ribeyed
04-27-2003, 05:47 PM
hi Simflex,
got to new files (ftp://80.195.1.157) and download the new files.
simflex
04-28-2003, 09:30 AM
how Ribeyed,
thanks very much for everything.
simflex
05-15-2003, 01:34 PM
hello there,
I know you are pretty busy so I am not asking you to do anything.
I just have one quick question.
Looking at your version of storeRec.asp, I realized you pretty much have it done.
The only thing left now, if I am not mistaking is to code the InjuredPassengers.
My question then is if I follow the code you used for the NotInjuredPassenger, will that work for InjuredPassenger?
As I indicated before, I have a version I banged out and manipulated to make it work.
Surprisingly, as much as they were harrassing me about it, they have not started using it.
So given that I have some free time now, I am trying to complete yours and use it instead of mine since yours is more professional and more efficient.
Thanks a bunch for your time and all the assistance.
Ribeyed
05-15-2003, 04:03 PM
HI Simflex,
Yes you are correct the most recent code I gave you is only missing the injured passenger code, but if you follow the code you will see that the code so far only handles the passengers and injured passengers in the first vehicle. To do that all i would do is copy the code for the first vehicle then change the variables from 1 to 2.
I haven't forgotten about you and I would like to see your project finished very soon. You may have been following the other project I have been work on this has been take up my free time, which is ok but it has been at the expense of your project.
I will try to get more of it done during next week and hopefully competed within the fortnight.
If you feel comfortable with the coding by all means give it a go writing the last of the code. I will also continue to complete the code, that way you can compare mine to yours.
Anyway I will post again soon, bye for now.
simflex
05-15-2003, 04:45 PM
just to be certain, I don't believe we handled the Injuredpassengers in vehicle1.
We did handle the not injuredpassengers(regular passengers) for vehicle 1 unless I am looking at an old version of storeRec.
If that is the case, please let me know and let me know how I can get the right version.
If it is not the case, then again advise if I can use the not Injured passenger code for the injured.
Thanks,
Ribeyed
05-15-2003, 05:00 PM
hi simflex,
you can d/l the storerec.asp from here (ftp://80.195.1.157)
simflex
05-15-2003, 06:04 PM
hi Ribeyed,
You are incredible human being.
I have downloaded that version and I am working on it as we speak.
Hopefully, I won't have a difficult bug to fix.
Meanwhile, good luck with the other stuff(s) you've got going on.
Thanks again for everything.
--s
simflex
05-17-2003, 04:44 PM
hi Ribeyed,
Hope you are having a lovely weekend.
When you get a moment, whenever that is, please take a look at this code snip.
This entire code is not working.
It is not giving an error but it is not inserting anything into tblpassengers.
I have spent almost all day trying to figure what is wrong with it but to no avail.
I have resolved minor bugs and everything is inserting great up until this passenger part of the code.
I had hoped to have gotten this part working because obviously creating a display page will be different from what I currently have.
I will continue to work on it and if I figure it out before you get a chance to look at it, I will post back.
By the way, how do you use response.write to track down errors.
obviously your coding style is different from what I am accustomed to.
I would normally use response.write (strSQL) and response.end to try and figure out why an insert is not working.
I am not sure how to do that with safetyDB.execute.
here is the code that is not cooperating:
(well since the entire message is too long, I will post the code right after this message.
simflex
05-17-2003, 04:50 PM
....continuing...>
this is the original code from you:
'now we have a complex task for the passengers. We need to insert all the passenger from both
'vehicles into tblpassenger then we have to add the injured passengers to the injuered table.
'first lets get the number of passengers in each vehicle.
Driver1VPassengers = request.Form("Driver1VPassengers")
Driver2VPassengers = request.Form("Driver2VPassengers")
'and now the number of injured passengers.
Driver1VNoOfInjuredPass = request.Form("Driver1VNoOfInjuredPass")
Driver2VNoOfInjuredPass = request.Form("Driver2VNoOfInjuredPass")
'lets workout the number of passengers not injured.
vehicle1notinjured = Driver1VPassengers - Driver1VNoOfInjuredPass
vehicle2notinjured = Driver2VPassengers - Driver2VNoOfInjuredPass
'ok same as before lets break this up and deal with 1 vehicle at a time. We are going to deal with vehicle 1
'first. So were going to build a loop that loops the same amount as the number of passenger in vehicle 1 then
'we will insert this into the database.
'so lets loop
for x = 1 to vehicle1notinjured
'now we have to progmatically build the request part as we don't actually know the form element that we are requesting, for example:
'we don't know how many not injured passenger there is going to be so we will not know how many names etc. to request. we can't
'even just go Driver1PassengerName1 because the end number will be different for each one.
'what we can be sure of is that its going to be something like Driver1PassengerName and then a number, so lets work with that.
'this is the same idea as the witnesses etc. This following line would not work
'PassengerName = request.form("Driver1PassengerName"&x&")
'this is because when it gets to the second " thats it nothing else will be picked up so we need to build the request.form part first, create
'a variable then use the variable for the requested for element.
varPassengerName = "Driver1PassengerName" & x
Driver1PassengerName = request.form(""&varPassengerName&"")
varPassengerAge = "Driver1PassengerAge" & x
Driver1PassengerAgeID = request.form(""&varPassengerAge&"")
varPassengerCity = "Driver1PassengerCity" & x
Driver1PassengerCity = request.form(""&varPassengerCity&"")
varPassengerZip = "Driver1PassengerZip" & x
Driver1PassengerZip = request.form(""&varPassengerZip&"")
varPassengerPhoneHome = "Driver1PassengerHomePhoneNum" & x
Driver1PassengerPhoneHome = request.form(""&varPassengerPhoneHome&"")
varPassengerPhoneWork = "Driver1PassengerWorkHomeNum" & x
Driver1PassengerPhoneWork = request.form(""&varPassengerPhoneWork&"")
varPassengerState = "Driver1PassengerState" & x
Driver1PassengerState = request.form(""&varPassengerState&"")
varPassengerGender = "Driver1PassengerGender" & x
Driver1PassengerGender = request.form(""&varPassengerGender&"")
'again same as before we have a little work to do with the data for the city, zip.
'first the state
sql = "SELECT * FROM tblState Where tblState.State = '"&Driver1PassengerState&"'"
set PassengerStateSet = safetyDB.Execute(sql)
if not PassengerStateSet.EOF then
StateID = PassengerStateSet("StateID")
else
SafetyDB.Execute = "INSERT INTO tblState (State) VALUES ('"&Driver1PassengerState&"')"
sql = "SELECT * FROM tblState Where tblState.State = '"&Driver1PassengerState&"'"
set CurrPassengerstateset = safetyDB.Execute(sql)
StateID = CurrPassengerstateset("StateID")
CurrPassengerstateset.close
set CurrPassengerstateset = nothing
end if
PassengerStateSet.close
set PassengerStateSet = nothing
'and the city
sql = "SELECT * FROM tblCity Where tblCity.City = '"&Driver1PassengerCity&"' AND tblcity.stateID = "&stateID&""
set PassengerCitySet = safetyDB.Execute(sql)
if not PassengerCitySet.EOF then
CityID = PassengerCitySet("CityID")
else
SafetyDB.Execute = "INSERT INTO tblCity (City, StateID) VALUES ('"&Driver1PassengerCity&"', "&StateID&")"
sql = "SELECT * FROM tblCity Where tblCity.City = '"&Driver1PassengerCity&"' AND tblCity.StateID = "&StateID&""
set CurrPassengerCityset = safetyDB.Execute(sql)
CityID = CurrPassengerCityset("CityID")
CurrPassengerCityset.close
set CurrPassengerCityset = nothing
end if
PassengerCitySet.close
set PassengerCitySet = nothing
'and now the zipcode.
sql = "SELECT * FROM tblzip Where tblzip.zipcode = '"&Driver1Passengerzip&"'"
set PassengerzipSet = safetyDB.Execute(sql)
if not PassengerzipSet.EOF then
zipID = PassengerzipSet("zipID")
else
SafetyDB.Execute = "INSERT INTO tblzip (zipcode, cityid) VALUES ('"&Driver1Passengerzip&"', "&CityID&")"
sql = "SELECT * FROM tblzip Where tblzip.zipcode = '"&DriverPassenger1zip&"'"
set CurrPassengerzipset = safetyDB.Execute(sql)
zipID = CurrPassengerzipset("zipID")
CurrPassengerzipset.close
set CurrPassengerzipset = nothing
end if
PassengerzipSet.close
set PassengerzipSet = nothing
'so now that we have all the details lets insert the data into the database.
safetyDB.execute = "INSERT INTO tblPassengers (accidentEventID, PassengerGender, VehicleID, AgeID, CityID, ZipID, PhoneHome, PhoneWork, PassengerName) VALUES ("&accidentEventID&", '"&Driver1PassengerGender&"', "&Vehicle1ID&", "&Driver1PassengerAgeID&", "&CityID&", "&ZipID&", '"&Driver1PassengerPhoneHome&"', '"&Driver1PassengerPhoneWork&"', '"&Driver1PassengerName&"')"
next
************************
it is still too long but check out the rest of code on storeRec.asp from this code I posted till end of page.
***********************
simflex
06-18-2003, 05:51 PM
hey Ribeyed,
I didn't want to start a new thread since this thread has something to do with this current thread.
I multi task a lot as you can see.
This is the third project I am currently working on alone.
I have a question for you.
A year ago, I wrote an email program that tracks order status for clients.
The way that program works now is if an order is placed by a client and is not processed until the due date passes, the client is automatically sent an email reminding him/her that his/her order is past due by x number of days.
Right now, my boss has made a client a promise that we could write an email program that notifies them when an accident occurs (remember the accident thing we worked on?).
The way client wants it is that if an accident occurs, one of the employees who is driving the company vehicle will call their front desk to report the accident.
As soon as the call is made, the front desk person, collects as much information from the caller as possible, enters all of that info on the system and clicks the submit button.
As soon as the submit button is clicked, the info that was submitted goes to the database and all parties involved in handling accident reports will be sent an email notifying them of an accident occuring, and advising them of how much time that each respective individual has to take action on the accident.
For instance, as soon as an accident occurs, the immediate supervisor of the employee involved in the accident has 24 hours to complete a form and pass that along to the safety officer.
The safety officer has 24 hours to complete his own part of the form and pass it on the dept head for final signature; the dept head has 24 hours to sign and return to risk management office for processing.
Now there are ways that this requirement differs from the one I did in the past.
The one I did in the past was done entirely using sql server stored procedure. This may well be also, but I hope I can find an easier way because that one was very complicated.
Second, that one sends the email automatically and is incorporated with sql server jobs which was used to schedule notifications on a weekly basis.
This one is going to happen in 2 stages.
Stage one happens when the front desk employee fills accident report and click the submit button.
At this stage, an email will be sent out reminding the 3 principles mentioned above of what they need to do and the time required to do those things.
The second email reminds each one that his/her task is due in a few hours before due date. For instance, if you have been notified from the original email that you have 24 hours to take action on something, then before the 24 hours elapses those who still have not completed their forms are notified again until all forms are processed and turned to risk management.
I was wondering if you have any idea how I can proceed with this?
Ribeyed
06-18-2003, 06:54 PM
hi Simflex,
just a quick post to say that i have looked at this and will help you out. Can you post me the pages/database again?
Not sure if you can achieve what you want without using the sql store procedures with the automatic email. ASP needs a trigger, the first one would be no problem that’s trigger when the page is submitted, it’s the recurring one, or when a certain time has elapsed. The page would need to be hit for the code to run. Note sure how to get around this.
Just to check if I have got this correct. An accident occurs; the call is made to the front desk person who fills in the form. Everyone gets and email instruction them that the accident has occurred. This advises them how long they have to complete there part of the process. Ok so everyone gets the first email, but the email is slightly different for each person; immediate supervisor gets 24 hours, safety officer gets 48 hours and dept head gets 72 hours. Now if none of them visit the page in there allotted time then you would have no trigger for to send the recurring emails. “Trigger” being a call to the page.
Now I am not sure how the sqlserver automatic email works, never tried it before, nor have I needed to do anything like what you are required to do, but I will still give you a help where I can.
What you can do but this would be fully the full requirements, but you can have code built into the page that would check the time and then email the 2nd and 3rd person, but not the first, in regard to the recurring email.
Thanks
simflex
06-18-2003, 10:49 PM
hi Ribeyed,
I believe you've got the process correct.
Now I need to give a brief overview of how the current email program that I wrote with stored procedure works.
Infact from your email, I can actually see how I can utilize a lot of the code from that email program but first let me clarify a few things.
One,
we don't need to go back to original database design.
This is an isolated program.
This is just to initial step that is completely divorced from the accident program we just wrote.
This one in question takes the following input:
Caller Name (the person who just made the call to report an accident),
Name of the person involved in the accident, if different from the caller,
date and time of accident (am/pm taken into account).
location of accident,
the persons superviser(the person involved in the accident),
general description of the accident,
the email address.
case number
Tracking# (this will be auto number concatenated with current date).
That is the only table we need as far as inputting data is concerned.
I have designed this table yet neither have I designed any peages.
These two wont take time to put together.
I just wanted to have a discussion on this so I can see if the current email program that I have have play any significant part in getting this done.
Now back to the email prgram I have written, there are some stuff we can borrow from it.
For instance, the way that one works is you have tables for inserting orders and customer info.
Then you have another table called notification table.
Now once you create your insert statement, and you hit the submit button, data gets inserted into the orders table.
Then there is the email code that says if any others have not been processed on the due data, pick those others and insert them into the notification table.
Once any others are inserted into the notification table, an email program is triggered into picking that information and sending an email to the concerned parties.
I see a similarity here as far as inserting into the callcenter table.
After inserting into call center table, we can then create another table called reminder table whereby an email is sent to those who have responsibility to take certain actions.
My stored procedure can go that far.
The questions that I need answers to that will help me utilize that option is how do I write the logic that will instruct the email program to pick up certain info from the callcenter table and insert into reminder table if certain conditions are true?
How do I instruct the email program to recognize that there are some pending actions to be taken and need to be taken before x date?
How do I instruct the code to continue to do this until that pending issue has been resolved?
I recognize that there will be a begin date and action date.
These questions will help me solve this with the email program that I have.
Ribeyed
06-20-2003, 03:14 PM
Hi Simflex,
I see what you are trying to do, well I think I do, but there is still a problem you didn’t provide an answer for. All the checking of the notification table depends on the user hitting the submit button, which if fine the first time, but what happens if no others are submitted for a substantial amount of time?
You question about coding for instructing the email program to pick up certain info from the callcenter table and inserting into reminder table before a certain date. This is just a select statement and insert statement with using date functions to work out lengths etc.
How do I instruct the email program to recognize that there are some pending actions to be taken and need to be taken before x date?
Not sure on this, can you explain this more, bellow is my reason?
Again it needs some sort of trigger, i.e. the user calling the page or submitting the page. I don’t think you can write an ASP page have it on the server and somehow instruct IIS to run this page after a certain period of time.
The code you would write to check the notification table has to be on a page that is run by a user. The page has to be called for the code to run. Now you can code to send emails every time a new accident is logged to the required parties, you can then run a database query to select any records in the notification table which time limit is greater than 24hrs, 48hrs, 72hrs. This all would work great, but my point is the very first time this is up and running and the first accident is logged, and all parties receive there notification email. Now say there isn’t another accident for 3 weeks, bummer, the code will not be run for 3 weeks. Now the second accident is logged after 3 weeks, great the code will run and everyone that hasn’t completed paperwork for the first accident would then receive the reminder. If this is ok then sure this would be simple to do else I’m not sure how to do it simflex.
simflex
06-21-2003, 07:21 AM
Ribeyed, the way I am thinking of doing this is using sql server jobs to schedule the notifications.
Once the accident occurs, the accident info is logged into the system and all interested parties are sent emails notifying them that an accident has occurred and that each has 24 hours to complete their respective paperwork.
To me this is where I am having the most problem.
How to code this initial notification ensuring that each of these folks know exactly how much time they need to complete their part of the paperwork.
Remember, even though you brilliantly said that the first has 24 hours, the second has 48 hours and the third has 72 hours, what happens if it takes the first only 2 hours to complete his/her own paperwork and passes the info to the next person to complete his?
Does this next person say, hey I have 48hrs?
We need to code this in such a way that the if it takes the first person 2 hours, the next has 24 hours from the time he/she receives the paperwork.
I still believe all of this should and can be done in the backend and does not require asp programming.
I am just not sure how to proceed.
Unless you come up with a better set up than I have because my set up says, write the asp that will submit accident info into the accident table.
Once that is done, select the info where (this is the problem here) a certain condition occurs or does not happen.
There is got to be a field on the database that can be used as a flag.
I attempted to have a field called status with default open.
That way, I can select from the accidentinfo table and insert into email to where status is open.
I don't like this very much.
The other option I have thought up is to have a two fields one called action_required and the other called last_action_taken.
Action_required will either be 1, for supervsisor, 2 for safety Officer and 3 for deptuy.
Lastion_action_taken will either be something like getdate() > 24 hours or getdate() < 24hours.
Pick info from accidentinfo and insert into emailtable where action has not been taken in 24hours and keep sending email until action is taken.
This logic has not come together yet for me.
Now say there isn’t another accident for 3 weeks, bummer, the code will not be run for 3 weeks. Now the second accident is logged after 3 weeks, great the code will run and everyone that hasn’t completed paperwork for the first accident would then receive the reminder. If this is ok then sure this would be simple to do else I’m not sure how to do it simflex.
I believe this will work.
Can we come up with the code that does the initial notification.
I can schedule a job in sql server, just like i did with the first email program to pick up info from accidentInfo and insert it into email table where certain conditions are true.
Then as long as those conditions continue to be true, sql server jobs will continue to send them email reminders every x number of hours until they all respond completely.
My question again becomes, how do we keep sending emails that each of individual?
My last email program was sending notification to one custome at a time.
This will send to 3 people that have 3 different timelines.
Ribeyed
06-21-2003, 09:08 AM
HI Simflex,
ok the coding for this shouldn't be a problem, however can i have never automated SQL Server to send out emails, can you provide me a guide on how to set this up. This will give me the rest of the information i require to code this.
Can you send me the database with the tables you have?
Then as long as those conditions continue to be true, sql server jobs will continue to send them email reminders every x number of hours until they all respond completely.
I need to know how to set this up, can you up?
Not sure the way to code this of the top of my head, but it can be done. Can you make up the pages or page we need where the frount desk does the submit thing to start it off and send them to me?
simflex
06-21-2003, 12:21 PM
hi Ribeyed,
ok, I am attaching two compressed files.
One is the database table with all fields.
The other attachment has 2 files, the input form and the insert form.
I have removed a few things to protect our client but they are nothing that will affect what we are doing.
Please feel free to add additional fields to the table that will make the implementation easy.
however can i have never automated SQL Server to send out emails, can you provide me a guide on how to set this up. This will give me the rest of the information i require to code this.
The entire process that I mentioned that I did was done entirely in sql server stored procedure with the exception of the two files. The input form and insert form.
The rest were done in sql server.
From what I gathered from you, you don't have sql server database.
If this is true, I can set up jobs to run as we want it to run.
Setting the sql server jobs to do what we want it to do takes two processes.
Process1: requires us to execute the email program in the sql server jobs scheduling utility so that it will know where and when to send email messages.
process2: requires setting the email program we just executed there to run (send emails) on a specified date and time).
This is really easy to do.
So I am not sure what you really need.
Do you need the original program that I wrote to send reminders to our customers?
I can set this up very quickly as soon as our codes are up and running.
As I indicated earlier, getting the logic that will recognize how to extract info from the accidentInfo table and insert them into mailnotification table is the most significant step becaue we have to come up with the logic that recognizes that email needs to be sent to three different folks that will take some action at three different timelines.
If this works, I can then incorporate this into our email program.
I can then furnish you with the rest of email program (codes) that and how they are set up in sql server jobs or even Oracle depending on the database.
I have not put together the email notification table.
I will put that together but basically, it will fetch names of those who will be notified, their email addresses, the email subject and content.
Again I am uploading one file and go back and upload another.
Is there a way to upload multiple files here?
simflex
06-21-2003, 12:23 PM
here is the other file, the database.
simflex
06-27-2003, 10:48 AM
Ribeyed,
Have you been able to come up with anything yet?
I am still struggling with the logic of determining when to send email to supervisor, when to send it to safety_officer, or when to send the email to the deputy.
There is a field in the database called status that defaults to "Open".
Right now, I just have a code that says once the accident information is logged into our database, go to the database, select those records where status is "Open" and insert them into the email table.
My email program will then send general emails to these 3 advising them that an accident has occured, each has 24 hours to complete their respective forms beginning with the supervisor. Once supervisor completes his/her form and passes it along to safety, safety_officer has 24 hours to complete his/her form; same with deputy.
That is the verbiage I am currently using.
It works this way but that is not the way the client wants it.
The client wants to send an email message addressed to Supervisor letting him/her know that he/she has 24 hours to complete his/paperwork.
Once completed, it needs to sent to the safety_officer.
Safety_Officer and Deputy will be copied on the email.
Sending the email to Supervisor and copying Safety_Officer and Deputy won't be a problem.
What is stumping me is finding to let the code know when one individual has completed his/her part of the form and then getting to code to send the email to next person in line and copying the other ones.
Now We can update the status to Complete once supervisor completes form but how do I reset it to Open for the next person in line?
If we can do this, then my code will always select from accident_Info table where status is OPen but it needs to know who to send it to and which ones to copy.
If I can get help in coding this part correctly, we all will be home free.
Ribeyed
06-27-2003, 01:53 PM
Hi Simflex,
sorry for the delay, agian been busy myself. I did have a look at your callcenter.asp page, but there are a few HTML code errors that need sorted first. Not sure what you are using to code your pages but you need to watch you HTML code. Come to think of it i remember your other ones had the same sort of HTML errors, anyway need to fix them first so i can view the page to get an idea of what your doing.
Will try to fix the HTML in the next few days and i will try get back to you on some ASP code for this.
simflex
06-27-2003, 02:20 PM
hi Ribeyed,
what editor do you use to code?
Perhaps I need to try that because I don't have any errors in the pages I sent you and I use TextPad to code.
Ribeyed
06-27-2003, 03:35 PM
Hi Simflex,
i have followed the dreamweaver root since version 3 an now use Dreamweaver MX and wouldn't dream using anything else. I find it useful for colour coding code and for picking up HTML errors, it highlights them. It also has a useful function where it automatically inserts the end HTML tags. Give me an email if you can't get it i will see what i can do, but would have to be away from this forum ;)
simflex
06-27-2003, 06:53 PM
Ribeyed,
we do have dreamwever mx. Infact we have dreamweaver down to the oldest version but I have grown accustomed to using textpad.
The only time I use dreamweaver is if my text or table are not getting aligned the way I want them then I use dreamweaver to accomplish that.
My only take as far as why you get html errors is probably the fact that before I post a code that has our client's info, I remove their info.
Sometimes, you remove something important.
That is not to sugges that I don't make errors.
On another note, do you think that the reason we don't have someone else contribute to this discussion is because they are not following this thread anymore or what?
I believe what we need is ideas that can help us to implement this.
Ribeyed
06-27-2003, 08:27 PM
Hi Simflex,
had a look at the project inhand, hmm.....i see an unnormalized table in your database, this will not work. Functionality like this is not so hard if your table are normalized, simflex. Ok so here goes with the 10 thousand questions before i can normalize your table :)
Ok your going to need a table that stores the emails, now depending on if each staff memeber can have mor than one email address. Your going to need a staff table with will hold the staff name, email, a primary key and a foreign key to an employee type table. The employeeType table should consist of; a primary key, type (DPWStaffName, Supervisor, Deputy) and email notification time limit. Field Org, not sure what this is???
Location looks like a repeating field which may have to go into table in its own. Casenumber, Accidentnotes and status should stay where they are, but change status to a yes/no field or (sql) bit field.
Now your going to need a relationship table between employees and accidentinfo table. Have a look at this database let me know if there is anything i have missed out.
simflex
06-28-2003, 08:23 AM
hi Ribeyed,
I have taken a look at the new db design.
That is fine. I thought a lot and realized we probably needed a new table for employees. So your thinking is correct there.
A couple of things first.
1, the field Org is the employees organization.
In this example, it is constant at 201.
So we can just set the default for Org as 201. THis never changes as I was told.
The other thing I mentioned earlier is the field status.
I had included that field because I wanted to use it to determine what records get sent to the emailer table before email is sent to someone.
For instance, I wanted to say if status is 'Open' which means no action has been taken yet by a particular employee, then send that employee an email.
So you can remove it if you don't see a need for it.
The other question I have is what is emailNoteTime?
Please review this new idea and see if anything in it makes sense to you:
it seems to me that we can add a datetime column and use GetNow() to update it whenever you process an update to the accident table. that would allow us to calculate the elapsed time from the time the notification gets sent until we receive the incremental update.
since we now have a normalized table we can also add a datetime of update, a processing role and a pending action indicator. when the person_accident record is first created, the reporting person is set as "completed", the next person in the chain could be set as "processing" and any follow on people would be flagged as "queued" (or something like that). when the person who is "processing" submits their data, their status is switched to "completed" and their datetime value is set to GetDate(). the next person in "queued" status (maybe with a lookup based on assigned accident role) gets their data moved to the emailer table and has their status moved to "processing" with a datetime value of GetDate().
I don't know how this will be implemented but just ideas.
But please feel free to proceed with what you've got.
It looks fine to me.
The one last thing is that employee table will be prepopulated.
In this case, we can just use it as a look up table.
Again feel free to do whatever makes coding easy.
Thanks again for all your help and time.
Ribeyed
06-28-2003, 07:50 PM
Hi Simflex,
i have attached a new database and callcenter.asp file, i don't see a need for the other page anymore.
This database is still not the final structure but it will not change for the code i have already writen in the callcenter.asp page. I have took the code as far as prepering the form data and inserting the accident and population the emplyaccident table. At this point i am about to send out emails but i still not sure which way you want to go with this. Firstly i have noted all your points in your previous post.
Am i correct that the 3 people involved get an email to start with with 12, 14, 48 hours to complete there process?
Now i could code this in ASP to send these emails or what?
if ASP what mail are you using, CDONTS, ASPMail?
I think we send all 3 emails, then we update the tblemployaccident.numemail to 1.
Simflex where do the employees come to fill in their part of the process, i don't have that page?
When the first person complete this page the code on that page will update the tblemployaccident.completed and email the next person, confused again here on what happens. Does this person now only have 12 hours or still 24 hours?
same thing happens when person 2 completes their part, person 3 recieves a email saying 24 hours or 48 hours?
so after all that code, comes the code for checking any other accidents that employees have not completed their parts. Using the emailNoteTime, numemail, current datetime, and the new field datetimelog (comibine date and time fields in tblaccidentInfo) we can determine it another reminder email is need to be sent.
example:
datetimelog = "23/06/2003 12:55:00"
emailNoteTime = 12
currentdatetime = now()
numemail = 1
so we have the above values so if we do the following then we should know if this person needs a reminder
do a date difference between datetimelog and currentdatetime
multiply numemail by emailNoteTime
take away the first one from the second one and if the remainder if greater than emailNoteTime then this person need a reminder.
Well i hope that will work;) not coded it yet, lol
simflex
06-29-2003, 09:40 AM
hi Ribeyed,
At this point i am about to send out emails but i still not sure which way you want to go with this
I am not sure what you mean here but whichever way you think is more expidient in terms ease of coding and as long as that works, any which way is fine with me.
Am i correct that the 3 people involved get an email to start with with 12, 14, 48 hours to complete there process?
Yes 3 people get emails but the first has 24 hours to complete his/her form and the second has 24 hours to do same and 3 the same.
What needs to be noted here is that it doesn't matter how long it takes each to complete their papework.
What matters is that by the time the next person receives his/her paperwork, he/she has 24 hours to complete it.
The paperwork can be completed in less than 24 hours.
For instance, if it takes the first one say, 5 hours to complete his/her paperwork, and the next one receives it, he/she has 24 hours to complete it.
The whole idea is to stop fingerpointing.
According to the client, no one is owning up to their responsibilities.
As a result, one person blames the other.
What we are trying to do for them is to make sure that if the tracking is not complete, we can go back and say, ok, right now, the reason this is not completed is because person 1 or person 2 or person 3 has not done his/her part and will continue to receive reminders until it is done.
Please bear in mind that alot of the times when I come to a forum like this is to see if the functionality can be implemented.
If not, I can go back to them ask them to modify their requirements.
Now i could code this in ASP to send these emails or what?
If it can be done in ASP, that would be great!
where do the employees come to fill in their part of the process, i don't have that page?
Good question.
It is a manual form that gets passed around until the last person, the deputy, approves it.
I am thinking along the line of asking each person to send an email to the receiptionist who will be managing this application to let her know that his/her portion of form is completed and has been forwarded to the next person.
The receiptionist will do what you suggested here:
When the first person complete this page the code on that page will update the tblemployaccident.completed and email the next person, confused again here on what happens
Will it work that way?
Thanks so much!
Don't know how I would have handled this without you.
Ribeyed
06-30-2003, 08:50 PM
Hi Simflex,
here is an update.:)
simflex
07-01-2003, 08:39 AM
hi Ribeyed,
I have downloaded the new files... thank you (again).
I have been trying since last night to digest what we are trying to do.
We have a login page, I have inserted dummy values to try to login but keep getting this message user define message:
you couldn't login because the details you provided did not match any details in the database, plz try again.
If you don't mind, can you please take a moment to explain what we are doing.
Second, as I indicated, staffname, deputy and supervisor are part of the employee table and have already been prepopulated with names of client's employees.
I can see from callcenter.asp that we are hardcoding the employeeID.
This is fine if we only have those 3 employees. In this case, there are more employees.
So do I look for their ids and hardcode them or can we allow those to be input params?
last question, I forgot to indicate the type of os we have and I am happy you correctly use CDO since we use windows xp but the question is, does the email get sent after the form is completed and submitted?
Please help me explain the flow.
I did indicate that the forms are being completed manually which means that the person completing the form will have to either send an email to receiptionist handling the form or go over and tell the receiptionist so she can update the form; this way, emails will be sent to the next person.
They indicated that at some point, they will automate the form completion process but for now, they want it manual.
Thanks for saving again.
Ribeyed
07-01-2003, 09:03 AM
Hi Simflex,
First of all make sure your working with the database I gave you. Before you can login to the login page I gave you, as a user, you need to have your email, password and an email from the system with the TrackingNumber.
So the order of things, accident happens, caller phones front desk, front desk brings up callcenter.asp and proceeds to complete the form. On submit an automatic email is sent to 1 staff member, 1 deputy and 1 supervisor selected from the select boxes on the form. Each email has a different message depending on there type, but the all get the TrackingNumber.
Now staff starts the chain of by filling in the form manually and passing it on to deputy, but staff has to login to the callcenterlogin.asp page and log that he/she has completed the form and passed it on to the next person. Now I have written the code for this yet but what will happen is that once staff updates the form to say they have passed them on, I will automatically send an email to deputy, then search the database for anyone that is late and re-email them also at the same time.
Ok I¡¦m sure you explained that this is to stop employees passing the buck, well forcing them to complete ¡§something¡¨ to say that have passed it on will help to tighten thinks up.
If for example staff doesn¡¦t complete the form but said he/she did, they would have to log this, but when they do to cover there tracks, the next person is automatically notified and if he/she doesn¡¦t have the form then I think deputy will be murdering staff and staff will get sacked for lying ƒº
Now I am also going to add more code to callcenter.asp, just to check if any re-emails need to be sent out at that time.
simflex
07-01-2003, 02:53 PM
hi Ribeyed,
Before you can login to the login page I gave you, as a user, you need to have your email, password and an email from the system with the TrackingNumber
I see that the email, username and password are on the tblEmployee table, are these created by your system or is it something I create differently.
I am trying to determine how they come up with their usernames, emails and passwords.
Second, giving that employee names come from tblEmployee table, are we going to change the way we obtain staff, supervisor and deputy names?
Other than these questions, everything else looks great!
I am grateful to you for the all the help.
Ribeyed
07-01-2003, 05:36 PM
Hi Simflex,
first the email and name should already be in the employee table, the password on the other hand, you will need to decide with your client on how they should be asigned.
Second, giving that employee names come from tblEmployee table, are we going to change the way we obtain staff, supervisor and deputy names?
not sure what you mean?
simflex
07-01-2003, 05:51 PM
hi Ribeyed,
what I mean is on your query on callcenter.asp, you have something like:
sql = "SELECT tblEmployee.EmployeeID, tblEmployee.EmName FROM tblEmployee WHERE tblEmployee.TypeID = 2"
In the real employee table, id is different.
Do I search out this id and replace 2 or 3 or whatever the number might be with the id from db?
If yes, what if that employee leaves?
Ribeyed
07-01-2003, 05:56 PM
Hi Simflex,
2 is not the employee number silly, lol its the typeID, typeID being the staff member type, weather their staff, deputy or supervisor.
simflex
07-02-2003, 11:35 PM
hello Ribeyed,
Please bear with me.
I am experiencing problems with the email section of callcenter.asp.
This is the error:
Error Type:
CDO.Message.1 (0x8004020C)
At least one recipient is required, but none were found.
/Safety/callcenter.asp, line 335
All I did was change the emailaddresses to mine so emails can come to me instead of you.
I am trying to resolve.
Ribeyed
07-03-2003, 08:19 AM
Hi Simflex,
did you submitted it once before you change all the email addresses?
did you change the one hard coded into the page as well as the ones in the database?
simflex
07-03-2003, 08:44 AM
hi Ribeyed,
The answer to your first question is No.
I did not submit once before changing the email address because I didn't want it bothering you by sending email to you.
did you change the one hard coded into the page as well as the ones in the database?
Yes.
Infact, if I hardcode them, it seems to get past that error which means that it is working if supervisor, staff and deputy email addresses are hardcoded.
For some reason, it is not picking them up from the database.
When I hardcode them, I get this error:
CDO.Message.1 (0x80040213)
The transport failed to connect to the server.
I have never seen that error before.
I am trying to research it to see if it has to do with smpt server to starting or something.
Ribeyed
07-03-2003, 09:02 AM
hi Simflex,
yes that error is wrong configuration of SMTP server. The reason i asked if you submitted it first is because i got 2 emails from the system, lol. Anyway you code is fine i tested it and it works here, as i said that error is now to do with the SMTP server configuration and not the code.
simflex
07-03-2003, 09:17 AM
Hello Ribeyed,
Strange that you got an email.
I am not sure how that is possible giving that smpt server is not even working.
Could it be that the reason email is not getting picked up from the database is because I am picking them up from our real employee table?
If so what could I possibly be missing?
Second, how difficult will be to store email, username and password on a separate table?
The real employee table does not have email, username or password fields and I am not sure if it is a good idea to ask them add every employees email, password and username into the employee table.
We can, if it won't be a pain, just store emails, usernames and passwords of those involved with the program and somehow tie that info to employee in employee table.
What do you think?
Ribeyed
07-03-2003, 09:26 AM
HI,
maybe you are correct about the emails, they could have come from myself, just took a while to go through the system, no matter.
Could it be that the reason email is not getting picked up from the database is because I am picking them up from our real employee table?
Possible if you haven't got the changes in the code to look at the new emplayee table, or just something is missing from the old to the new code.
The real employee table does not have email, username or password fields and I am not sure if it is a good idea to ask them add every employees email, password and username into the employee table.
problem. Ok picture this; you are emplyee Simflex@youremail.com, and you get an accident email with the tracckingnumber, i am employee david@myemail.com and i also got an email with the same trackingnumber.
Say i wanted to fraud the reports bescuse i messed up, i know the trackingnumber becuase we both got it in our emails, i also know your email address beacuse we work at the same company, what would stop me from login into the system with your email address and my trackingnumber?
The answer is nothing! I can login with your email and the trackingnumber and i can do anything to your side of the process. To stop this you need to also include a password, which no one should know apart from yourself.
simflex
07-03-2003, 09:43 AM
I can login with your email and the trackingnumber and i can do anything to your side of the process. To stop this you need to also include a password, which no one should know apart from yourself.
I agree with you but if they have a separate table that contains email address, username and password, you cannot login as me if you don't know my password.
Remember now the login you created asks for email AND password.
So someone has to know your password to log in as you.
Ribeyed, thanks a lot again for all this help and your patience and I truly apologize for any inconvenience.
Ribeyed
07-03-2003, 09:54 AM
Hi Simflex,
ok so we agree that the need the following to login; email, password and trackingnumber. Its your call how the password will be stored in the database. To generate and assign a password to everyone i could write a script that would do all that in one go plus email the password to everyone.
Right so let me recap where we are with this. You have your callcenter page around 75% coded, up to the point where the 3 employees get there first email to say an accident has occured. You have your callcenterlogin page where the come to login to the system to update there part of the process, this page is around 75% coded. So all you need now is the code on the callcenter page that checks all the previous accidents for outstanding input from the employee and then automatically sends them a reminder. On the callcenterlogin page you need the code to email the next person inline informing them that the previous preson has completed there paperwork and it is now up to them to complete theirs. Then again check any previous accidents and email any employee with outstanding paperwork to complete.
Ok with this?
simflex
07-03-2003, 10:39 AM
Perfect guru, just perfect!!!
simflex
07-03-2003, 02:03 PM
sorry to ask you a stupid question.
I have never used cdo before.
how do I determine how to get the value for these:
Flds(cdoSMTPServer) = "80.195.1.157"
Flds(cdoSMTPServerPort) = 25
simflex
07-03-2003, 02:12 PM
hey Ribeyed,
Never the last email.
The email program is working fine now.
Thanks a bunch!!!!!!!!!!!!!!!!
Ribeyed
07-03-2003, 02:18 PM
hi Simflex,
Flds(cdoSMTPServer) = "80.195.1.157"
This is the SMTP server address. This can also be this:
the 80.195.1.157 is my SMTP server, which is in IP address form because i don't have a domain name.
Flds(cdoSMTPServerPort) = 25
Is the port number this is the port number that used by the SMTP server and should not change.
Ribeyed
07-03-2003, 02:54 PM
Hi Simflex,
there are just a few things not right about your form. I take it the date and time that is entered into the form is the date and time of the accident, so where is the date and time for when the accident was logged?
Secondly i don't think you should have a drop down box with status open and closed. What you going to do if the user decides to select closed?
As i suggested before this should be a yes/no box. The user doesn't have to select open you just mark each new one as open in the database. When all 3 users have completed the callcenterlogin.asp page i will automatically set the accident to closed.
simflex
07-03-2003, 03:19 PM
You are right again, Ribeyed!!!!
So then we add date and time accident info is logged in and we change the status from open/closed to yes/no with no selected as default value until changed.
Thanks for your brilliant ideas. It has helped get this thing this far.
Ribeyed
07-03-2003, 05:19 PM
Hi Simflex,
here is another update for you is the updated callcenter which works out who needs another email reminder, enjoy!
simflex
07-04-2003, 08:29 AM
hi Ribeyed,
You have done a real bang job on this.
Once again, please accept my most profound appreciation.
You are indeed the ultimate supreme master of the web.
A few clarifications.
One, I can now remove the status field from tblAccidentInfo since we are now using the completed field on tblEmployAccident, is this correct?
two, when email is sent out the first time, does it go to all 3 required people advising of the accident that occured and other verbiages?
three, if they need to cc a few more people, all they need to do is just add; .cc or does it need to be cdo.addcc?
Do I need to follow with ; to add more or do I need to continue enter keyword .cc for each until all who need to be copied are copied?
four, are we expecting to move email, username, and password to another table as we discussed?
If yes, does it mean then that all email info on callcenter.asp will be derived from that table?
five, You indicated you will make some changes with callcenterlogin to add forgotten password, is this still plausible?
Is callcenter.asp ready to go?
I am about to test out callcenterlogin.asp.
I tried it once and was having problem getting it to accept my login credentials.
Last and perhaps most importantly, can you, if you don't mind, give me some general overview of what we did so I can document and be able to walk them through it.
I think I know all that you did but I need more like:
Accident occurs, a caller calls in and info is recorded and submitted to the db.
Once info is submitted to db, original email gets sent out to required people and then the clock starts ticking.
If a form is completed, the person logs in and completes the form and once that form is completed, the next person on line automatically gets sent an email letting him/her know to complete form within 24 hours.
As long as a form remains uncompleted, email reminders will continue to go out.
Did I get everything based on what we did?
Thanks again partner for all your help!
Hope you get rewarded for all your help to others and for your immense talent.
Ribeyed
07-04-2003, 09:58 AM
Hi Simflex,
Thanks again for your kind comments.
One, I can now remove the status field from tblAccidentInfo since we are now using the completed field on tblEmployAccident, is this correct?
No, I have changed the status to a yes/no field. When the last person completes the callcenterlogin.asp page I will then set the status to yes for completed. You are correct in theory we don’t need it, but it will help make things slightly easier.
two, when email is sent out the first time, does it go to all 3 required people advising of the accident that occured and other verbiages?
correct.
three, if they need to cc a few more people, all they need to do is just add; .cc or does it need to be cdo.addcc?
yes, but need to check the syntax for you, will pick this one up later.
Do I need to follow with ; to add more or do I need to continue enter keyword .cc for each until all who need to be copied are copied?
I’m sure its just a comma.
four, are we expecting to move email, username, and password to another table as we discussed?
if that’s what you want, not sure how this will effect the code, need to look at this one later.
If yes, does it mean then that all email info on callcenter.asp will be derived from that table?
if the answer above was yes, then yes.
five, You indicated you will make some changes with callcenterlogin to add forgotten password, is this still plausible?
can remember discussing this, but it shouldn’t be a problem adding in but, you need to clarify what happens next? Does it email someone who will retrieve the password? Or is it going to be automated if so what information needs to be supplied to verify that the person is the correct person requesting the forgotten password?
Is callcenter.asp ready to go?
Almost, but you should be testing it make sure there are no errors.
I am about to test out callcenterlogin.asp.
ok.
I tried it once and was having problem getting it to accept my login credentials.
Make sure everything is an exact match, if you notice I have only I select statement to check for username, password and trackingnumber so there is got to be a match.
Last and perhaps most importantly, can you, if you don't mind, give me some general overview of what we did so I can document and be able to walk them through it.
sure. The accident occurs, caller phones front desk to log the accident. Front desk opens up callcenter.asp page and enters the accident details; date, time, description etc., staff member, deputy and supervisor.
On submit of this page by front desk, the code finds the email addresses for each person and an automatic email is sent to staff member, deputy and supervisor. The callcenter.asp also has some background processes that have nothing to do with the current accident that is being logged. The background process has been developed to comply to the specifications of recurring emails. Firstly it finds all the accidentInfo’s that status if set to no (this is why we need status), then doing a 3 table join we pull out all the emplyaccident’s that are uncompleted, join with email address, numemail from the tblemplyee and join with tblemplyeetype typeID and typeName. This is then grouped by the accidentInfo.trackingnumber and order descending with the typeID, this is so that staff member would be first, deputy second and supervisor third. Now we do a calculation to determine if the first person form each accidentinfo has not completed there paperwork and not logged this within 24 hours. It will workout how many reminder emails that have been sent multiply this by 24 because everyone gets 24 hours example:
“Everyone gets the first email.
Staff member has 24 hours to complete their paperwork.
When staff member completes paperwork and updates callcenterlogin.asp deputy gets automatic email saying you now have 24 hours to complete your paperwork.
Deputy now has 24 hours to complete paperwork.
When deputy completes paperwork and updates callcenterlogin.asp supervisor gets automatic email saying you now have 24 hours to complete your paperwork.
Supervisor now has 24 hours to complete paperwork.”
So multiplying number of previous reminder emails by 24 will give us total number of hours this person has received reminder emails. Using the current time and the time the accident was logged we can use a date diference function to work out how many hours have pasted since the accident was logged. Now we take away total number of hours this person has received emails from the total number of hours since the accident was logged and if above 24 then this person is due another email.
Ok so all that works in the background, so on the callcenterlogin.asp page this is to basically so that each person can confirm that they have completed the manual paperwork, this will set the tblemployaccident.completed to completed, this person will not receive any reminders. The callcenterlogin.asp will then automatically email then next person inline. Example:
“Everyone receives the first email saying accident has occurred.
Staff member has 24 hours to complete their paperwork and the callcenterlogin.asp page.
When staff member logins into callcenterlogin.asp with the correct email, password and trackingnumber the accident details will be displayed.
Staff member checks the 2 check boxes to confirm that they have completed the manual form and that the have passed the paperwork on to deputy.
Staff member submits the form.
tblEmployAccident.competed is then updated to say staff member for this accident has completed the required paperwork and the callcenterlogin.asp page.
An automatic email is then sent to deputy saying deputy you have 24 hours to complete your paperwork and complete the callcenterlogin.asp page.
Deputy completes paperwork and then logins in to callcenterlogin.asp to update their record for this accident. Deputy checks the 2 boxes to say that they have completed the paperwork and passed them on to the supervisor. On submit the tblemplyaccident.completed is updated to say that this person has completed there paperwork and passed it to the supervisor.
An automatic email is then sent to supervisor saying supervisor you have 24 hours to complete your paperwork and complete the callcenterlogin.asp page.
Again tblEmployAccident.completed is updated but this time no email is send out to anyone.
AccidentInfor status is then set to complete.
”
Now after all that code is done we do the exact same background processing code as what is on the callcenter.asp page, to check for anyone that needs a reminder.
simflex
07-04-2003, 01:55 PM
Beautifully done [SWR]Ribeyed!!!!!!!!!!!!!
I couldn't have explained this project that I own any better.
I do have a few things for you to note, though.
First, the staff member is only getting copied on emails because this staff member is the one maintaining the system.
So it should be.
Supvervisor gets emailed, then safety gets emailed and finally deputy gets emailed.
These are the 3 central figures. The staff member gets copied on all emails because he/she manages the system.
I like the way you coded the staff, supervisor and deputy code; this way, we can have anyone be a deputy or supervisor or staff.
So we can just add another of the codes for safety officer and give the safety typeID = 4.
This way, we direct our accident notices to these 3 and copy the staff.
The other thing is we can just add a field in the db and call it ccList and give it a typeID.
This way, once they tell me who needs to be cc'ed, we can just add email addresses for those and then do another select where typeId = 5 or some number.
Then on the email side, we can select tblemployee.ename, email where typeid = "&ccListEmail&"
This way, all we need to do is just add to that list on the database side.
The other point I need to bring to your attention is the status.
In sql server, which is what they use, the sql server version of yes or no is 0 or 1.. 0 for False, 1 for true.
I have always had problem doing this conversion.
I have always had problem saying if 1 then 'yes' else 'no'.
It doesn't work because it is a bit datatype.
Maybe we can use a char with data length of 4 then we can just assign it yes or no. Or may be true or false.
I haven't test the reminder side of the email but everything else looks good.
Again, you have done a swail job.
I still believe it is better to have a separate table for emai, password, and username but if I can convince them to just add those 3 fields for only those using the system then great.
I have a forgotten password code, although I made a mistake of using cdonts to write it versus cdo.
I will look to change the code and use it for this project.
Ribeyed
07-04-2003, 03:41 PM
Hi Simflex,
First, the staff member is only getting copied on emails because this staff member is the one maintaining the system.
So it should be.
Supvervisor gets emailed, then safety gets emailed and finally deputy gets emailed.
dam, so the order would be supervisor, safety, deputy?
But staff member also gets the first email, is staff static? I mean is it the same person regardless of the accident?
I like the way you coded the staff, supervisor and deputy code; this way, we can have anyone be a deputy or supervisor or staff.
Yes anyone can either either, so you have have 20 supervisors or 10 deputies or 30 saftey, whatever you want.
So we can just add another of the codes for safety officer and give the safety typeID = 4.
Nope, this would severally mess up the coding. The code where it checks for who needs reminders relies on selecting and joining the employees with the types and works out who gets email next. If there was an extra type, but it is not included in the further emails then the code I have written would be no good, have to rethink this one Simflex.
The other thing is we can just add a field in the db and call it ccList and give it a typeID. This way, once they tell me who needs to be cc'ed, we can just add email addresses for those and then do another select where typeId = 5 or some number.
I need to understand more who receives or should I say who gets included here and how many, first before deciding where this would fit into the scheme of things. Again a further typeID, not sure how this would fit into present code
The other point I need to bring to your attention is the status.
In sql server, which is what they use, the sql server version of yes or no is 0 or 1.. 0 for False, 1 for true.
I have always had problem doing this conversion.
I have always had problem saying if 1 then 'yes' else 'no'.
It doesn't work because it is a bit datatype.
Maybe we can use a char with data length of 4 then we can just assign it yes or no. Or may be true or false.
Access you use 0 and 1 for no yes respectively, SQL uses True and False for the bit field. So access would be:
WHERE tblEmployAccident.Completed = 1 or WHERE tblEmployAccident.Completed = 0
SQL would be:
WHERE tblEmployAccident.Completed = True or WHERE tblEmployAccident.Completed = False
I still believe it is better to have a separate table for email, password, and username but if I can convince them to just add those 3 fields for only those using the system then great.
Well you need to get your skates on be firm and tell your client that your doing it the way you decide, but back up your reason why with good arguments.
I have a forgotten password code, although I made a mistake of using cdonts to write it versus cdo.
I will look to change the code and use it for this project.
Still not sure how you are going to handle this, this is similar to what I described about the callcenterlogin.asp page in other posts. Say you forget your password, or say I want to seal your password what would stop me giving your email address, well what else would you ask for to prove there identity? You need to think of security here and come up with a solid password recovery policy before rushing into this one.
simflex
07-04-2003, 09:41 PM
hello [SWR]Ribeyed,
Please allow me to respond to the important points/issues you raised.
----------------------------------------
You need to think of security here and come up with a solid password recovery policy before rushing into this one.
-----------------------------------------------------
Ok, let's put this one on hold for now till further notice.
They didn't ask for this functionality; it was something I wanted to give them but let's wait for now.
-------------------------------
Well you need to get your skates on be firm and tell your client that your doing it the way you decide, but back up your reason why with good arguments.
---------------------------------------------
I believe I can convince them to accept the way we did it.
------------------------------------------
SQL would be:
WHERE tblEmployAccident.Completed = True or WHERE tblEmployAccident.Completed = False
------------------------------------------
If I code it the way you suggested, I get this error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'False'.
If I put a single quote around it, I get this error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'False' to a column of data type bit.
It has frustrated me so many times and I couldn't find a workaround to it that I just use 0 or 1.
------------------------------------------
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'False' to a column of data type bit.
------------------------------------------
If we can figure out how to use cc, then this would not be necessary because they wanted to know if mass mailing is possible.
So if we figure out how to use cc to copy one or more people, then we will be fine.
----------------------------------------
Nope, this would severally mess up the coding. The code where it checks for who needs reminders relies on selecting and joining the employees with the types and works out who gets email next
-----------------------------------------
Ok, in this case then, we can pretend that the staff name is the safety officer.
In other words, we can just assign the safety officer the staff typeid and then just copy the staff.
What I mean here is we know that the code says staff, we can change the name from staff to safety and still keep the typeID. Only thing that will change is the name, code won't change. We can just cc the staff person.
---------------------------------------
dam, so the order would be supervisor, safety, deputy?
But staff member also gets the first email, is staff static? I mean is it the same person regardless of the accident?
------------------------------------------------
Sorry Ribeyed, this has been our original spec.
Per my last response before this response, we can just cc the staff person.
We are only carbon copying the staff member because he/she maintains the system and needs to know where things are.
The staff person is not static from the standpoint that the staff member can be transferred, promoted, fired, quit.
A good number of scenarios could come up so we can make it, if possible, the same way as deputy, supervisor.. this way, anyone can be staff member.
The staff person doesn't need to get reminders.
He/she just needs to get the original email and then when next person completes his/her form, she/he needs to know also.
Sorry if this causes more additional headache.
Ribeyed
07-05-2003, 07:36 AM
hi Simflex,
sorry got mixed up with the SQL statement, sql is 0 and 1 fot bit, and acess if true false for yes/no. Sorry again :)
Ok as there is only 1 staff getting an email we can add staffID into accidentinfo field type numeric, and we can add the staff memberID to this. That way its not interfering with anything else and its normalized. When i said static i just meant that there will be only 1 staff getting 1 email and thats it, it doesn't change as in accidentinfo 1 has 1 staff member to get extra email and thats it. No where will there be 2 staff members getting the 1 email or 3 or 4. I mean this value never varies its allways only 1 staff member.
everything else as far as i can see is OK.
simflex
07-05-2003, 09:16 AM
hi Ribeyd,
About the SQL bit datatype, that's alright.
In the past, I have used a workaround to accomplish what I want.
For instance, I would define the datatype as nvarchar(50).
We know that when using nvarchar, with a length of 50 such as above, if you only get to use a length of 3, the rest of the space are freed.
So in our case, we would use give status 1 for Yes and 0 for No and we would do something like:
<td class="normaltxt" width="84"> <% If statusset("status") = 1 Then
Response.Write "<font color=""#009900"">Yes</font>"
ElseIF statusset("status") = 0 Then
Response.Write "<font color=""#FF0000"">No</font></td>"
It always works.
About the callcenterlogin page, I was able to login and tried to ocmplete the questions.
After completing the questions, I click "Complete Accident Paperwork". It takes me back to login screen and asks for my password and accident id.
Something is amiss.
Could it be because of this code:
else
'ok you are now login in so we want to display the data.
%>
<form name="form1" method="post" action="">
This piece is not posting to anything.
Ribeyed
07-05-2003, 10:23 AM
Hi Simlex,
not got around to what happens when you submit the callcenterlogin.asp page, will post soon.
Also your if statement is long winded can be just htis:
if status = "True" then
'1 was returned
else
'0 was returned
end if
simflex
07-05-2003, 10:53 AM
hi Ribeyed,
With the status thing, I think that it depends on what you are trying to accomplish.
In our case, I was thinking, that if we are going to display status for the users to see, it will be more meaningful for them to see Yes or No versus 1 or 0.
If they need to know whether a form has been completed, they are probably better off seeing somthing like, is form Completed? Yes Or No versus 1 or 0 unless of course the processing for this is done behind the scene.
Ribeyed
07-07-2003, 02:08 PM
Hi Simflex,
have a look at these files.
simflex
07-07-2003, 04:37 PM
Hi Ribeyed,
Hope all is well with you.
I have downloaded the new version you attached. (Thanks).
I am looking it over because right now, the email progrm is currently not working.
I am now trying to figure out where the problem is coming from.
Just curious, is any of the two pages ready?
Thanks again for all the assistance.
simflex
07-08-2003, 12:21 PM
Ribeyed, hi,
I have been trying to test both the callcenter.asp and callcenterlogin.ap but I am getting strange results.
The callcenter.asp is no longer sending emails; not sure why.
The callcenterlogin.asp is always giving me the message:
"You have already completed this page".
it doesn't matter what email/password/accidentid I use, I get that message.
I know you are only helping me and I appreciate it very much but I am really worried that this may not be ready for my demo on July 16th.
Anything you can do will be greatly appreciated as always.
Maybe there is something that I am missing.
I also will be grateful if I know how far along we are.
That will help me try to lobby for more time.
Ribeyed
07-08-2003, 01:10 PM
Hi Simflex,
Very disappointed, seems to me that you haven’t even looked through the code after you found that it isn’t sending emails. I commented out the .send line on the emails for testing purposes and forgot to uncomment them when I sent them to you. This just looks to me that you haven’t bothered looking over the code and understanding the code, which in turn just makes it look like I am doing all you work here. I don’t mind helping in fact I like helping out but you got to at least make an effort here as well. If you looked at the code I marked as the email code you would have seen the .send line commented out. Anyway Simflex just uncomment them and the emails will work.
In regard to the callcenterlogin.asp there is nothing wrong with the code it’s the data in the tables that is the problem. All email addresses and Passwords are the same because of this the recordset is returning more than 1 record. I am presuming that you used the database I sent you so you would need to at least give all employees different passwords until end of testing then change the email addresses.
simflex
07-08-2003, 01:45 PM
hi Ribyed,
Please, please don't be disappointed in me.
I did look at the .send to see if it was the problem.
I saw that it was commented out and I also saw that the one you sent before that I said working is also commented out so I didn't think that was the problem.
So again, please don't be disappointed and don't be angry.
I am studying your code.
You have a unique style that requires me to spend a bit more time to understand your style of coding and I will do so, so please be patient.
simflex
07-08-2003, 02:21 PM
hi Again, I did uncomment the .Send and I am getting an error:
The event class for this subscription is in an invalid partition
I am researching hopefully, I will figure out the problem.
On this point which I forgot to address last time,
I am presuming that you used the database I sent you so you would need to at least give all employees different passwords until end of testing then change the email addresses.
We use sql server here.
So any time you send a new db, first thing I do is to ensure that all fields match up correctly with the fields on the sql server table I created. I also make sure that the data types and length are same.
After, I create my own records to test with.
So everything I have been testing with so far are new records that I have created.
New email/new password/new accident Id.
I will clean my db and create new records to work with to see if I get a different result.
Ribeyed
07-08-2003, 03:33 PM
ok, post back after your testing, i ran page again here and it work.
simflex
07-08-2003, 05:07 PM
Great job, man!
I got our network specialist to take a look at our smtp server to try and figure out why my email suddenly stopped working.
Well, I don't know what he did but he basiscally stopped smtp server, restarted it and email started working again.
So the code for callcenter.asp is working fine, again.
I deleted all values on my db and ran callcenterlogin.asp, it failed but I found out why it is failing.
the field Completed is not working.
As you know, we talked about yes/no and 0/1.
Well, in sql server 7 and up, bit datatype can have null values.
So for some reason, when I changed the value from false to 0, it is not recording that 0 value in the db. Instead, it is defaulting to NULL as a result, callcenterlogin.asp could not find the value of 0.
I had to manually enter 0 value for callcenterlogin.asp to work.
But it is working.
Before I leave here today, I would have found out how to make it store a value of 0 into the db.
It is however, updating to 1.
Numemail, is also by default null.
So I hope that is fine but for now, I feel a lot better because I could not understand why callcenter that was sending email on Friday suddenly stopped.
Thank you for all your help.
Yesterday, I wanted to know if you have finished coding on it that way, I will be testing various combinations knowing that we have finished coding based on info we have so far.
Can you please let me know about this.
Again, accept my heartfelt appreciation and don't be angry or disappointed because trust me I am working hard to dissect everything you did here.
What did you have in mind with this message?
address found message sent by: simflex@hotmail.com To: Back to login page
Ribeyed
07-08-2003, 06:45 PM
Hi Simflex,
once you get back to me saying you got what i gave you working then all i have to do is add in the bit where it emails the staff person.
What did you have in mind with this message?
nothing jsut comment it out or replace with a response.redirect to somewhere its up to you, you can even display a thank you message.
simflex
07-08-2003, 09:31 PM
Ribeyed,
thanks a lot for all the help and unnecessary burden I imposed on you.
I will extensive testing tomorrow after I had fixed the complete yes/no problem.
simflex
07-09-2003, 01:05 PM
ok, Ribeyed, so far things are looking really good.
I mentioned yesterday that status and completed fields have null values.
Well, the reason for that is they are not anywhere on our insert statements... insert into tblaccidentInfo or tblemployaccident.
So I added them there and defaulted their values to 0 meaning false.
When I logged in to update records of form completion, it updated completed to 1; that is working good.
It also sent email to the next person in line.
That is working fine also.
I am going to leave everything else as they are today with the hope that when I get here tomorrow, I will get email reminders that my form has not been completed.
Once that is working, then the ONLY thing left to be done is to copy staff member on emails being sent to those folks.
I like the way you are planning to do that.
This way, we can assign as many people as they want the value of staffmember and mass email them if necessary.
Fantastic work.
I am hoping to spend an awful lof time studying your thinking methodology so I can one day replicate your work to an extent that will get the job done.
Thank you, Ribeyed.
Ribeyed
07-09-2003, 01:48 PM
Hi Simflex,
Once that is working, then the ONLY thing left to be done is to copy staff member on emails being sent to those folks.
I like the way you are planning to do that.
This way, we can assign as many people as they want the value of staffmember and mass email them if necessary.
err..not exactly the way i was going to do this. This is going to need some thought. You can't just add in another employeetype staff. You said that only one staff member will get the first email and that was it, however this must be able to change because staff come and go. Not sure how this will work have you thought about how you would go about actually changing this?
simflex
07-09-2003, 02:32 PM
I thought you were using the same concept that you used for supervisor, safety, and deputy.
With these, at least so far , I have been able to assign any deputy with the typeID of 2; supervisor, typeID of 1; and staff(safety Officer) with a 3.
So right now, the client has 6 deputies, several supervisors and 2 safety officers.
So it has been a matter of assigning them email addresses and passwords.
When I need to send an email, anyone with a 3, 2 or 1 gets an email.
I was thinking that that is the way staffmail will be utilized.
If not, that is fine too.
If I can use .cc, then I can cc anyother person they want cc'ed.
Remember, I asked this cc question before, and you told that you haven't used but you will look into that.
That will actually solve the staff email problem because as I indicated, it is just a matter of letting the staff person know what is going on.
Ribeyed
07-09-2003, 03:25 PM
hi Simflex,
I thought you were using the same concept that you used for supervisor, safety, and deputy.
do you mean add in a further record with typeID 4 and call it staff? I already explain this would cause problem with the code we have already.
With these, at least so far , I have been able to assign any deputy with the typeID of 2; supervisor, typeID of 1; and staff(safety Officer) with a 3.
So right now, the client has 6 deputies, several supervisors and 2 safety officers.
So it has been a matter of assigning them email addresses and passwords.
When I need to send an email, anyone with a 3, 2 or 1 gets an email.
Yes that’s fine, no problem with that, that’s what it does.
I was thinking that that is the way staffmail will be utilized.
If not, that is fine too.
how? Do you mean add in a table and do the same again or add a further record with typeID 4, again this is not possible.
If I can use .cc, then I can cc anyother person they want cc'ed.
this sounds like you want the cc dynamic, can be 1,2,3,4 whatever, but where will the code get this number from?
Remember, I asked this cc question before, and you told that you haven't used but you will look into that.
That will actually solve the staff email problem because as I indicated, it is just a matter of letting the staff person know what is going on.
yes I remember but still you haven’t indicated how the code will know how many to cc?
Sounds easy if you say “it’s just the matter of letting the staff person know”, but how does the code know who the staff person is?. Say we have a field somewhere in the database with a value that relates to a record in the employee table, this is fine so think on the users side, the user completes the form to log the accident does this person have to select his/her own email address so this can be added as the staff member?
This is hard to explain, Lynsey is at front desk, Vicky is the staff member to receive the email for all accidents, Vicky’s employeeID is 5, so either Lynsey has to select “Vicky employeeID 5” or we have to hard code staff = employee 5 to the code. Second one is out of the question because staff come and go. Option one is no good either, means Lynsey would have to select staff member from another select box on the callcenter.asp every time a accident occurs.
How do you plan to make cc dynamic?
Have 1 email but create a loop for the cc part? Some code:
For x = 1 to somevalue
.cc = “&staffemail&”
next
This concept would work but where does “somevalue” come from? Again either Lynsey would have to select all the cc on the callcenter.asp page or somevalue = 6 or 2 or 4 would need to be hard coded again?
simflex
07-09-2003, 04:02 PM
you are right, ok gee, so what are we planning to do with staffmail then?.
Let me think too and see what, if anything I can come up with.
simflex
07-10-2003, 03:53 PM
hi Ribeyed,
a few things to bring to your attention, please.
a, are these supposed to be uncommented?
I did uncomment them and I didn't get what I had in mind when I uncommented.
if hourssincelastemail > 24 then
'response.write checkset(0) & " - " & checkset(1) & " - " & checkset(2) & " - " & checkset(3) & " - "
'response.write checkset(4) &"<BR>"
'response.write "hourssincelastemail = " &hourssincelastemail &"<BR>"
'if this is running this person is due another email
What I had in mind when I went home last night was that I would get an email reminder.
If you don't mind me asking, needs to happen before an email is sent.
I understand what your comment said as far as the above.
b, when you log into the system and send check that form is completed, and then you click submit, it wasn't printing the accident id (tracking number). When I replaced this:
currentTrackingNumber with this: accidentNumber, it started printing accident numbers once you send out an email. Not sure if that is the you wanted it.
Same this line: response.write "message sent by: Company From: db010a6370@blueyonder.co.uk To: "&staffemail&"" (line 333) on my editor.
It isn't displaying the name of the person sent email.
I didn't change that.
c,
About the email to staff, if we have to do it manaully, that is manually cc'ing the staff member, then so be it.
I mean, I have researched and found out how to cc someone but I am not sure it is going to fit into what you have done already.
At this point, I am bushed.
So rather than depress me, I can just tell them to use manual method while I try to sort it all out.
Thanks Ribeyed
Ribeyed
07-12-2003, 06:25 PM
hi Simlex,
sorry for not getting back sooner been ill :(
a, are these supposed to be uncommented?
I did uncomment them and I didn't get what I had in mind when I uncommented.
if hourssincelastemail > 24 then
'response.write checkset(0) & " - " & checkset(1) & " - " & checkset(2) & " - " & checkset(3) & " - "
'response.write checkset(4) &"<BR>"
'response.write "hourssincelastemail = " &hourssincelastemail &"<BR>"
'if this is running this person is due another email
yes this code was just for testing to make sure that the values are being retrived.
What I had in mind when I went home last night was that I would get an email reminder.
If you don't mind me asking, needs to happen before an email is sent.
I understand what your comment said as far as the above.
? sorry my friend don’t understand this.
b, when you log into the system and send check that form is completed, and then you click submit, it wasn't printing the accident id (tracking number). When I replaced this:
currentTrackingNumber with this: accidentNumber, it started printing accident numbers once you send out an email. Not sure if that is the you wanted it.
Same this line: response.write "message sent by: Company From: ************* To: "&staffemail&"" (line 333) on my editor.
It isn't displaying the name of the person sent email.
I didn't change that.
Not sure about what you mean by this either, but hmmm… can you remove my email address from your post thanks.
[quote]
c,
About the email to staff, if we have to do it manaully, that is manually cc'ing the staff member, then so be it.
I mean, I have researched and found out how to cc someone but I am not sure it is going to fit into what you have done already.
At this point, I am bushed.
[quote]
not sure what you mean here, but it came to me in a flash while writing this, I could just add a cc to the first email that is sent to the safety officer, so staff and safety officer could receive the same email for the first time only.
Ribeyed
07-12-2003, 06:29 PM
hi Simflex,
the last thought, you would still have to hard code the staff email
simflex
07-13-2003, 08:59 AM
hi Ribeyed,
I understand from your email that you are ill; I hope you are feeling better.
About my last thread, I apologize for the ambiguity with most of my questions.
Let me start here:
? sorry my friend don’t understand this.
What I meant here was that once the first email is sent out, the supervisor first has to complete his form; he/she has 24 hours to do so.
If he/she does not respond withing 24 hours, he/she gets an email reminder.
What I am saying here is that after testing, I didn't get a reminder.
I was wondering what needed to happen before a reminder is sent.
The reason I asked this question is to see if there is something that I need to modify to make that work.
Not sure about what you mean by this either, but hmmm… can you remove my email address from your post thanks.
First, I apologize for entering your email.
I didn't know you would mind your email being out, given that anyone on the forum could have downloaded what you sent me and your email.
ok, after the original email is sent out to notify everyone that an accident has occurred, the next step is for supervisor to complete his/her form. Once that form is completed, he/she logs into callcenterlogin.asp to indicate his/her form is completed.
He/she answers 2 questions, have you completed to form?
Have you passed the form to next person?
he/she clicks to submit the form.
Then there is a message that reads:
"Please compelete your paperwork for Tracking Number: "¤tTrackingNumber&""
When you run this code, currentTrackingNumber is not being displayed on the email being sent out but when I replaced currentTrackingNumber with accidentNumber, then the number would display. I just wanted to bring that to your attention to see if that was just an error.
Same with this line:
response.write "Email has been sent by: simflex@hotmail.com To: "&staffemail&""
This is not displaying the name of the staffemail.
Again I wanted to know what is wrong here.
not sure what you mean here, but it came to me in a flash while writing this, I could just add a cc to the first email that is sent to the safety officer, so staff and safety officer could receive the same email for the first time only.
This, I believe will solve this problem of cc'ing staff member.
Again Ribeyed, the email steps are:
supervisor first, safety officer next and deputy last.
the last thought, you would still have to hard code the staff email
That is fine.
Look Ribeyed, as I have said many, many times in this forum, you have come to my rescue a lot. I can't and won't stop appreciating it but please don't lose interest in helping me to get this to work.
I have my demo this coming wednesday and I know it is my problem but there are issues I need to get resolved for this to be a success.
Only you can help. I am trying my best to solve but I am having difficulties. Since you decided to help, please help all the way.
There are questions that I still have such as, if for instance I have 3 people with typeID 2.
If I select one of them from the dropdown list and submit the callcenter.asp form, is the one I selected the only one to receive email or will all 3 people with typeID 2 receive email.
I still need to understand how the reminder works.
Please be patient and help through this; it is amost over with.
Ribeyed
07-13-2003, 09:46 AM
Hi Simflex,
thx but i still feel i'll at the moment i think i have a summer cold.
Ok 24 hours need to pass from the time you added the accidentInfo until an email reminder will be sent/until you run the code.
Please check to make sure you are using the correct format for your dates. Check to make sure it is US format and not UK format, and make sure your system clock and time is set to US. If you still having problems manually change the date to make sure it is 24 hours before you run the code.
When i ran this code i had 24 accidents all logged at least 24 hours previous, so when i ran the code 24 emials where sent out as reminders.
I’m sure this “Please compelete your paperwork for Tracking Number: "¤tTrackingNumber&""” should only be displayed if you never checked the checkbox??????? But I will look into why they are not displaying.
If you select say a supervisor from the drop down list then only that supervisor should get an email not all the supervisors in the drop down list.
Ok an idea for the staff thing, add a field into accidentInfo which will hold the employeeID for the staff member. This is filled in the background from a value from a completely different table.
TblConstants
ConstantID *
ConstantValue
And you add 1 record in here that holds the employeeID. All you would have to do is create a simple administration page that would allow them to change this value when required. The page will look to this table for the constant and automatically file the accidentinfo.staffID with the value retrieved from tblconstants.constantvalue.
simflex
07-13-2003, 10:38 AM
hi Ribeyed,
Again, I want to wish you a quick recovery and thanks for your response to my last thread.
.Cc = "user@domain.com;user@domain.com"
’m sure this “Please compelete your paperwork for Tracking Number: "¤tTrackingNumber&""” should only be displayed if you never checked the checkbox??????? But I will look into why they are not displaying.
Based on the tests I have run so far, this email is sent to the next person who is on line to complete paperwork next.
So once one person updates callcenterlogin to indicate his/her paperwork has been completed and passed on, then above message is sent to the next person.
At least that is the behavior I am getting so far.
As far as date is concerned, I will involve some of our staff members tomorrow the test the reminder and typeID allocations.
The staff thing, I will try and follow your advise to see if I can make it work before wednesday because I am comprehending the logic here. There is a field called staffMem you added to tblAccidentInfo already. So I will into how to get employeeid for that and somehow tie it to tblconstants.
If not, I can just hardcode to make through my demo without looking too bad.
One other thing, status field; I am still not sure what it is being used for.
I see it ONLY in a select statement and nothing else.
What about numEmails?
I have added that field in the tblEmployAccident insert statement and assigned it a default value of 0.
By Monday, I expect to see if it does what I think it is supposed to do.
Ribeyed
07-13-2003, 11:04 AM
numemails is needed for working out if a reminder email is required.
simflex
07-14-2003, 09:35 AM
hi Ribeyed,
I hate to be bothering a man who is sick because that makes it that much more painful; hope you are feeling much better.
I ran my code this morning and received reminders.
That led me to this response from you:
Ok 24 hours need to pass from the time you added the accidentInfo until an email reminder will be sent/until you run the code.
Do I have to run the code for an email reminder to be sent?
Please tell me "no".
Thanks Ribeyed.
Ribeyed
07-14-2003, 09:41 AM
HI Simflex,
you make me laugh, yes it does mean that the code has to run for the reminders to be send, this was the very first point i raised with you at the begining, the code needs a trigger for it to run, the trigger in this case is the page being run.
simflex
07-14-2003, 11:00 AM
hi Ribeyed,
Yes, you did raise that point at the beginning and it was wrong for me to have assumed(bad thing) that you had found a workaround to that problem.
Anyway, this won't work for 2 reasons.
1, any time you run the code, you are sending new email notifications, something that needs to happen only when an accident occurs.
2, the human factor.
Someone has to remember to run the code after 24 hours had elapsed.
Is callcenterlogin.asp the same way?
If yes, how much more trouble will I cause you if I ask you to remove the reminders from both callcenter and callcenterlogin pages.
I will write a stored procedure to do the reminders and set the stored proc up on sql scheduling utility to run send reminders to the recipients.
There are 2 advantages here.
1, I can send the reminders twice - 12 hours before the due deadline and 12 hours after deadline as long as completed is equal to 0.
The other advantage is that it will be done automatically eliminating potential error that may occur as a result of someone having to manually run the code.
The downside is that instead of sending reminders individually, to supervisor, to safety and to deputy, all will keep receiving reminders even if the person has not received paperwork for completion.
I will send the stored proc code if you want it even though you don't use sql server db.
simflex
07-17-2003, 10:07 AM
hi Ribeyed,
I know you have done as much as you can for me on this and again I truly appreciate the time and effort.
Ater my demo, yesterday, a couple of changes were recommended by the client.
Clients always change their requirements.
I have shopped the questions around hoping not to bother you with this anymore but unfortunately I have not gotten any response or suggestions on how to resolve them.
One change they recommended concerns this code:
<td> <select name="staff">
<OPTION value="-1" SELECTED><Choose One></OPTION>
<%
sql = "SELECT theEmp.empID, theEmp.LName+', '+theEmp.FName FROM theEmp WHERE theEmp.TypeID = 2"
set staffset = emailDB.execute(sql)
while not staffset.eof
%>
<option value="<%=staffset(0)%>"><%=staffset(1)%></option>
<%
staffset.Movenext
wend
staffset.close
set staffset = nothing
%>
</select></td>
They want this dpwstaffname to be derived based on org.
For instance, they want org to be a dropdown menu also.
This way, if for instance there are 7 org numbers, if you select org number 1, then they want that org to be associated with a particular dpwstaffmember.
I have written a javascript onchange even handler that would handle that but the problem is it works for me if the dropdown for staffset is hardcoded.
As far as I can tell, if I hardcode the staffset dropdown, then the where condition won't work anymore as far as knowing who to send emails to.
Is there any way you can help me with this?
I have tried this code so I can mess with the dynamic dropdown for staffset but I am getting errors:
if (optSelect == "5401") {
<%
'lets queueu up the recordset, check if it has records, then output some options
If NOT staffset.EOF Then staffset.MoveFirst
Do Until staffset.EOF
'lets dynamically add to the javascript
Response.Write "selbox.options[selbox.options.length] = new Option('" & staffset("empid") & "','" & staffset("lname") & "');" & vbCrLf
staffset.MoveNext
Loop
%>
}
Second issue, they don't want to use the callcenterlogin the way it is set up because they want only the hotline receptionist to update callcenterlogin when each individual has completed his/her form.
They still want the email sent to the next person just like we have but they want the receptionist to login and update the record and trigger an email be sent to the next person.
I was wondering if you could help in fixing this.
3rd, they want me to put them in stages.
For instance, stage one is supervisor completing his form.
If it is not completed, we still have the completed = 0 but they want to be able to see visually, stage1 supervisor to safety and status of that.
stage2 safety to deputy and status of that.
and stage 3 deputy to risk management.
Any help/suggestions you can give me, as usual, will be greatly appreciated.
Thanks,
ps. I have done the stored procedure and set up jobs to send reminders.
thanks (again) for everything.
Ribeyed
07-18-2003, 09:05 AM
ok i will look in to them.
simflex
07-22-2003, 12:32 PM
hi Ribeyed!
I am pretty sure it is not your intention to get me this far and leave me out in the cold.
So I have been doing the best I can to resolve the issues I indicated in my last thread because I am sure you are very busy.
I have resolved the dynamic situation.
So as it is now, when a user selects an org, the org code they select automatically populates the name of the deputy associated with that org in the second dropdown list.
This is working real well now.
I still need your help, at least an advice on how to proceed on these:
Second issue, they don't want to use the callcenterlogin the way it is set up because they want only the hotline receptionist to update callcenterlogin when each individual has completed his/her form.
They still want the email sent to the next person just like we have but they want the receptionist to login and update the record and trigger an email be sent to the next person.
I was wondering if you could help in fixing this.
3rd, they want me to put them in stages.
For instance, stage one is supervisor completing his form.
If it is not completed, we still have the completed = 0 but they want to be able to see visually, stage1 supervisor to safety and status of that.
stage2 safety to deputy and status of that.
and stage 3 deputy to risk management.
Any help/suggestions you can give me, as usual, will be greatly appreciated.
Thanks,
ps. I have done the stored procedure and set up jobs to send reminders.
thanks (again) for everything.
webdeveloper.com
Copyright Internet.com Inc., All Rights Reserved.