[SOLVED] problem with back dates...

[rsammy]

i am working on a patient application. i have these tables:

visit_mgr - with visit_pat_id, visit-location, visit_date, visit_time, visit_status fields among others;

admission - with admit_date, discharge_date, pat_id, encounters, location, status fields among others;

admit_stat - with admit_date, status, admit_visit-date, admit_pat_id, location, reason among other fields.

 

the visit_pat_id field holds the patient ssn that is also stored in patient_info table. every time a doctor sees a patient he enters this info on the screen and it puts this info in the visit_mgr table first. next, it inserts the patient info in the patient_info table with all relevant patient info. this table also has a field - pat_ID - that is auto-incremented.

 

the other tables in the database are linked to thepatient_info table by this pat_ID field. admission table has the pat_id field and admit_stat table has admit_pat_id.

 

based on the status in teh visit_mgr table(either follow or do-not-follow), the status in the tow admission tables(admission and admit-stat) are updated. if the patient is in follow status, admission table is updated with relevant info and discharge_date remains 0000-00-00 and admit_stat table too has this info stored.

 

if status is do-not-follow, then admit_stat table value is deleted for the particular patient (admit_pat_id) and admission table is updated with the status info and discharge_date info!

 

now, what if the doctor realizes taht he did not enter an encounter(patients visit) earlier? say, the patient visited the doc and was admitted on jan 03, 2007. the doc then visits him on jan 04. then again jan 05. (each visit by the doc is counted as one encounter and status is follow). then the doc releases him(do-no-follow) on jan 07, 2007 and then updates the discharge_date field. this is also an encounter. now we have 4 encounters. once the status field in admission table shows do-not-follow, the info from admit-stat for this patient is wiped off.

 

the doc suddenly realizes that he had visited and released the patient on jan 06, 2007. the patient had come back on jan 07, 2007 on a follow-up-call. (this is another encounter). so, the doc goes back to the system and enters the visit info for jan 06, 2007 as do-not-follow and updates teh info in admission table for discharge-date field as that date.

 

this now, should become encounter numbre 4 and the visit on jan 07, 2007 shud become a fresh encounter - a new encounter - encounter 1 for this patient in the next chain.  which means i need to break an existing chain of encounters and build two out of it!

 

i need some help here. how do i do this? how do i get the info(for the new encounter) and create a new chain in admission table based on the visit_date and status?

 

im hoping ive made myself clear!

 

[rsammy]

this is the exiting code. i need to make changes to this one based on date anf status..

 

$queryy=("select * from visit_mgr where visit_palm_db_id='$unqID'");
$resulty=mysql_query($queryy);
if (! $resulty)
{
$error="Error 330";
}
$num_rowy= mysql_num_rows($resulty);
if ($num_rowy == 0)
{
//**********addded new field palm_tran_ID in visit_mgr table on 01/05/2007 for billing module!!!**********
$visitquery=("INSERT INTO visit_mgr(visit_phy_id, visit_pat_id, visit_pat_ssn, visit_type, visit_status, visit_loc, visit_room_no, visit_date, visit_time, client_id, palm_tran_ID, visit_pat_first_name, visit_pat_last_name, visit_pat_dob, visit_pat_sex) VALUES ('$phy_id', '$pat_ssn',  '$visit_pat_ssn', 'ICN', '$admit_status', '$location', '$room_no', '$visit_date_reformat', '$visit_time', '$client_id', '$unqID', '$pat_fname', '$pat_lname', '$pat_dob_reformat', '$pat_sex')") ;
$resultvisitquery=mysql_query($visitquery);
$visit_id = mysql_insert_id();
//echo "$visit_id";

if (! $resultvisitquery)
{
	$error="Error 331";
}
}
else
{
$rowy=mysql_fetch_array($resulty);
$visit_id=$rowy["visit_id"];
}
$querypat=("SELECT * FROM pat_dgraphics WHERE (pat_ssn = '$pat_ssn')");
$results=mysql_query($querypat);
if(! $results)
{
$error="Error 332";
}
$num_rowsj = mysql_num_rows($results);
if($num_rowsj == 0)
{
$patquery=("INSERT INTO pat_dgraphics (pat_first_name, pat_last_name, pat_ssn, pat_dob, pat_sex, pat_entry, pat_phy_id, pat_client_id) VALUES ('$pat_fname', '$pat_lname', '$pat_ssn', '$pat_dob_reformat', '$pat_sex', '$phy_id', '', '1')");

$resultg=mysql_query($patquery);
if (! $resultg)
{
	 $error="Error 332a";
}
$pat_id = mysql_insert_id();


//CHECKING FOR ENTRY IN admit_stat table
//IN FOLLOW LOGIC
$admitquerya=("SELECT * FROM admit_stat where admit_stat.admit_pat_id ='$pat_id'");
$resulta=mysql_query($admitquerya);
if (! $resulta)
{
	$error= "Error 333";
}
$num_rowsa = mysql_num_rows($resulta);

if ($admit_status == 'Follow')
{

	if($num_rowsa == 0)
	{
		//IF PATIENT IS NEW OR NO ENTRY IS AVAILABLE FOR THIS PATIENT ID IN EITHER admit_stat OR admission TABLES!!!
		$admitquery=("Insert into admit_stat (admit_phy_id, admit_pat_id, admit_status, admit_visit_loc, admit_visit_date, admit_room_no, admit_client_id) VALUES ('$phy_id', '$pat_id', '$admit_status', '$location', '$visit_date_reformat', '$room_no', '$client_id')");
		$resultadmitquery=mysql_query($admitquery);
		$admit_id = mysql_insert_id();
		if (! $resultadmitquery)
		{
			 $error="Error 333a";
		}
		$admissionquery=("Insert into admission(admit_phy_id, pat_id, admit_status, admit_loc, admit_date, admit_room_no, admit_id, admission_client_id, admit_encounter) VALUES ('$phy_id', '$pat_id', '$admit_status', '$location', '$visit_date_reformat', '$room_no', '$admit_id', '$client_id', '1')");
		$resultadmissionquery=mysql_query($admissionquery);
		if (! $resultadmissionquery)
		{
			$error="Error 334";
		}
	}

}
elseif ($admit_status == 'Do Not Follow')
{
	//IN DO NOT FOLLOW LOGIC
	if($num_rowsa == 0)
	{
		//SINGLE ENCOUNTER PATIENT OR PATIENT READMITTED AND DISCHARGED IMMEDIATELY WITHOUT ANOTHER FOLLOW
		//IF PATIENT IS NEW OR NO ENTRY IS AVAILABLE FOR THIS PATIENT ID IN EITHER admit_stat OR admission TABLES OR IF PATIENT HAS BEEN RE-ADMITTED AFTER BEING EARLIER DISCHARGED - NO ENTRY WOULD BE FOUND IN admit_stat TABLE!!!
		//THIS IS A ONETIME ENTRY OR SINGLE ENCUONTER
		$admissionquery=("Insert into admission(admit_phy_id, pat_id, admit_status, admit_loc, admit_date, discharge_date,  admit_room_no, admit_id, admission_client_id, admit_encounter) VALUES ('$phy_id', '$pat_id', '$admit_status', '$location', '$visit_date_reformat', '$visit_date_reformat', '$room_no', '$admit_id', '$client_id', '1')");
		$resultadmissionquery=mysql_query($admissionquery);
		if (! $resultadmissionquery)
		{
			$error="Error 334a";
		}
	}

}


}
//END OF NEW PATIENT LOGIC - FOR FOLLOW/DO NOT FOLLOW

//FOR EXISTING PATIENT - FOLLOW/DO NOT FOLLOW
else if ($rowj = mysql_fetch_array($results))
{
$pat_id = $rowj["pat_ID"];
$queryz=("SELECT * FROM admit_stat where admit_pat_id ='$pat_id'");
$resultz=mysql_query($queryz);
if (! $resultz)
{
	$error="Error 333b";
}
$num_rowsz = mysql_num_rows($resultz);
$admit_id=$num_rowsz["admit_id"];

if ($admit_status == 'Follow')
{

	if($num_rowsz == 0)
	{


		//EXISTING PATIENT - NO ENTRY IN admit_stat TABLE
		//IF PATIENT IS RE-ADMITTED AFTER BEING DISCHARGED EARLIER - NO ENTRY IS AVAILABLE IN admit_stat FOR THIS PATIENT ID!!!
		$admitquery=("Insert into admit_stat (admit_phy_id, admit_pat_id, admit_status, admit_visit_loc, admit_visit_date, admit_room_no, admit_client_id) VALUES ('$phy_id', '$pat_id', '$admit_status', '$location', '$visit_date_reformat', '$room_no', '$client_id')");
		$resultadmitquery=mysql_query($admitquery);
		$admit_id = mysql_insert_id();
		if (! $resultadmitquery)
		{
			 $error="Error 333c";
		}
		$admissionquery=("Insert into admission(admit_phy_id, pat_id, admit_status, admit_loc, admit_date, admit_room_no, admit_id, admission_client_id, admit_encounter) VALUES ('$phy_id', '$pat_id', '$admit_status', '$location', '$visit_date_reformat', '$room_no', '$admit_id', '$client_id', '1')");
		$resultadmissionquery=mysql_query($admissionquery);
		if (! $resultadmissionquery)
		{
			$error="Error 334b";
		}
	}
	else
	{
		$admitidquery=("SELECT admit_id from admit_stat where admit_pat_id='$pat_id' ");
		$resultadmitquery=mysql_query($admitidquery);
		$rowadmitid=mysql_fetch_array($resultadmitquery);
		$admit_id=$rowadmitid["admit_id"];
		$updateadmitstatusquery=("Update admit_stat set admit_status='$admit_status', admit_visit_loc='$location', admit_room_no='$room_no' WHERE admit_pat_id='$pat_id' AND admit_id='$admit_id'");
		$resultupdateadmitstatusquery=mysql_query($updateadmitstatusquery);
		if (! $resultupdateadmitstatusquery)
		{
			 $error="Error 333d";
		}
		$updateencounterquery=("Update admission set admit_encounter= admit_encounter + 1, admit_status='$admit_status' WHERE pat_id='$pat_id' AND admit_id='$admit_id'");
		$resultupdateencounterquery=mysql_query($updateencounterquery);
		if (!$resultupdateencounterquery)
		{
			$error= "Error 334c";
		}
	}

}
else
{
	//IN DO NOT FOLLOW LOGIC FOR EXISTING PATIENT...!
	if($num_rowsz == 0) //$num_rowsz returns rows from admit_stat query
	{
		//SINGLE ENCOUNTER PATIENT OR PATIENT READMITTED AND DISCHARGED IMMEDIATELY WITHOUT ANOTHER FOLLOW
		//IF PATIENT IS NEW OR NO ENTRY IS AVAILABLE FOR THIS PATIENT ID IN EITHER admit_stat OR admission TABLES OR IF PATIENT HAS BEEN RE-ADMITTED AFTER BEING EARLIER DISCHARGED - NO ENTRY WOULD BE FOUND IN admit_stat TABLE!!!
		//THIS IS A ONETIME ENTRY OR SINGLE ENCUONTER
		$admissionquery=("Insert into admission(admit_phy_id, pat_id, admit_status, admit_loc, admit_date, discharge_date,  admit_room_no, admit_id, admission_client_id, admit_encounter) VALUES ('$phy_id', '$pat_id', '$admit_status', '$location', '$visit_date_reformat', '$visit_date_reformat', '$room_no', '$admit_id', '$client_id', '1')");
		$resultadmissionquery=mysql_query($admissionquery);
		if (! $resultadmissionquery)
		{
			$error="Error 334d";
		}
	}
	else
	{
		//EXISTING PATIENT - ENTRY FOUND IN admit_stat TABLE
		//IF admit_stat TABLE CONTAINS AN ENTRY FOR THIS PATIENT ID, DELETE FROM THIS TABLE AND UPDATE admission TABLE WITH THE DISCHARGE DATE!!!
		$admitidquery=("SELECT admit_id from admit_stat where admit_pat_id='$pat_id' ");
		$resultadmitquery=mysql_query($admitidquery);
		$rowadmitid=mysql_fetch_array($resultadmitquery);
		$admit_id=$rowadmitid["admit_id"];
		$deletestatusquery= ("DELETE from admit_stat where admit_pat_id='$pat_id' ");
		$resultdeletestatus=mysql_query($deletestatusquery);
		if (! $resultdeletestatus)
		{
			$error="Error 333e";
		}

		$updateadmissionquery=("Update admission set admit_encounter= admit_encounter + 1, admit_status='$admit_status', discharge_date='$visit_date_reformat' WHERE pat_id='$pat_id' AND admit_id='$admit_id'");
		$resultupdateadmissionquery=mysql_query($updateadmissionquery);
		if (!$resultupdateadmissionquery)
		{
			$error= "Error 334e";
		}
	}
}
}
//END CHECK FOR EXISTING PATIENT

[rsammy]

bump!!!

 

help? anyone?

[rsammy]

nobody to help????????

[redarrow]

What part is wrong exspain in short ok.

[rsammy]

nothing is wrong in the above code. i need to add a new check for back-dating.

 

the existing functionality is:

 

when a doctor sees a patient, the info is inserted into the visit_mgr table and admission and admit_stat tables depending on the satus(Follow or Do Not Follow).

 

if status is Follow and patient is being visited for the first time, then admission table is inserted with relevant info and encounter is set to 1 and admit_stat table is also populated with relevant info like admit-date, room no, location, etc.

 

if Follow and patient has already been seen before and is still not been discharged, then admission table is updated by incrementing the encounter and admit_stat table remains unchanged.

 

if Do Not Folow and patient being visited for the first time, then admission table is inserted with the relevant infoand encounter is set to 1. admit_stat table has no entry for this patient as the patient has been released immediately.

 

if Do Not Follow and patient has alreayd been seen before, then admission table is updated with dishcarge_date info and encounter is incremented and the admit_stat table value for this patient is removed/deleted.

 

the proposed change is:

 

if patient has been visited on 02/16/2007, 02/17/2007, 02/19/2007 and seen and released from hospital on 02/21/2007, the doctor enters this info. this is 4 encounters on the whole and admission table has this info stored. each visit increments the encounter by 1. when patient is discharged/released, admit_stat record is removed.

 

now, the doctor realizes that he had visited thsi particular patient on 02/20/2007 and released him that day. then again the patient had visited him on 02/21/2007 and was sent back the same day. t

 

this is where i need help! i will have to break the chain of Follows from 02/16/2007 to 02/21/2007 as Follow from 02/16/2007 to 02/19/2007. Do Not follow on 02/20/2007. which is 4 encounters. then again start another chain on 02/21/2007 which is a seperate encounter.

 

how do i break this chain and create a new one! i have to do that for six other scenarios. but, i guess help with this one will go a long way in my being able to fix the others.

 

thanx for your response - waiting for help here!

 

 

[redarrow]

So it your database construction that is wrong then example.

 

This is an example of a doctors surgery database for pasent info

<?php

PASHENT INFO

pashent_id
pashent_nhs_ref
pashent_doctor
pashent_current_medecation
pashents_current_perscrition
pashent_total_visits
pashent_cancel_appointment
pashent name
pashent address
pashent postcode
pashent phone number
pashent_email_address
pashent_registaration_date
regestration_status
pashent_notes

?>

[redarrow]

<?php

PASHENT INFO

pashent_id
pashent_nhs_ref
pashent_doctor
pashent_current_medecation
pashents_current_perscrition
pashent_total_visits
pashent_cancel_appointment
pashent name
pashent address
pashent postcode
pashent phone number
pashent_email_address
pashent_registaration_date
regestration_status
pashent_notes

?>

 

<?php

DOCTOR APPOINTMENTS

pashent_id
pashent_nhs_ref
pashent_doctor
doctors_name_for_appointment
date_for_appointment
time_for_appointment
pashent_illness
?>

 

 

<?php

PASHENTS HISTORY

pashent_id
pashent_nhs_ref
pashent_doctor
doctors_name_of_the_appointment
date_for_the_appointments
time_for_the_appointments
pashent_illness
pashent_perscritption
?>

[rsammy]

thanx for replying. im quite sure its not the database issue.

 

i need to figure out a way to include a back-dated patient visit to be included in the exact order (order by visit date) and then break a chain depending on the status(follow or do not follow). its kinda playing with the dates -  i'll need to sort the admission table by date (order by admit_date desc) and then get the status of each record within that. then compare the date(of new record) with exisiting admit_dates and see where it fits in the table.

 

if this new record is a do not follow within a chain of follow(s), then i need to break the chain where i encounter a do not follow and update the dishcarge date with that date and also increment encounter field by 1 and then start a new chain from the next instance of this record!

 

hope im clear now

 

[rsammy]

figured out. thanx for ur reply anyways