#! /usr/bin/perl
use strict;
no strict 'refs';
use warnings;
use DBI;
use Date::Calc qw(:all);
use File::Temp qw/ tempfile tempdir /;

my $version = '1.0';

#############################################################################
#    CONTENTS
#############################################################################

#Section 1. Description and copyright statement
#Section 2. Setup and instructions
#Section 3. User definable variables
#Section 4. Code





#############################################################################
#    Section 1. DESCRIPTION AND COPYRIGHT STATEMENT
#############################################################################


#this script is used to enter walk in patients into OSCAR EMR

#	 (c) Robbie Coull, 2009
# 	 robbie@coull.net
#
#    This code is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This code is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    see <http://www.gnu.org/licenses/>.





#############################################################################
#    Section 2. SETUP AND INSTRUCTIONS
#############################################################################


# Copy this script to a suitable location (eg: /usr/local/scripts)

# Make sure that it can be used by any user
#     sudo chmod 777 /usr/local/scripts/wic.pl

#note: must have mysql root password set to '' and the test database created
#for this to work and make sure MySQL is running!

# sudo apt-get install mysql-server
# sudo /etc/init.d/mysql start

#For this script to work you need to install perl modules
# sudo perl -MCPAN -e shell

# cpan> install Bundle::CPAN
# cpan> install Bundle::DBI
# cpan> install Date::Calc
# cpan> install File::Temp
# cpan> exit

#once the modules are installed, make sure MySQL on the local server is NOT running
# sudo /etc/init.d/mysql stop

#turn MySQL off in the services section of admin to prevent it starting at boot


#if OSCAR is installed on a remote server
#then you will need to set up a keychain access to that server
#to allow this script to access the upload folders
#
#eg: ssh -D 8080 -fN $USER@$remoteserver
#    ssh -nNL 3306:127.0.0.1:3306 $USER@$remoteserver &> /dev/null &
#
#which provides SSH tunnel access to the server and to the MySQL server


#I recommend using three user logins for MySQL (read only, read write, admin)
#This helps prevent disasters when the wrong command is issued by accident.  
#If you do most of your database work from a read only user, then you can't 
#accidentally delete anything.
#
#Set up a read-only user and a read-write user as follows:
#log in to mysql
#mysql -uroot -p
#
#GRANT all ON oscar_mcmaster.* TO readonly@localhost IDENTIFIED BY '[password]';
#GRANT select ON oscar_mcmaster.* TO readwrite@localhost IDENTIFIED BY '[password]';
#
#exit
#
#You will need to enter the user names and passwords of these two users
#in the section below - make sure this script can only be accessed by trusted users!


#note on providers
#For Walk In Clinics the family physicans should all have the same first
#two digits for their provider numbers.  This makes selecting a physician from
#the list much easier. (eg: 7001, 7002, etc..)
#The exception is the 'current provider' (ie: the physician this script is
#tied to using the 'current provider' variable below).
#If the physicians have mixed provider numbers, then set $use_full_provider_no to 1.







#############################################################################
#    Section 3. USER VARIABLES
#############################################################################

my $province_code           = 'PE';
my $province_name           = 'PEI';
my $province_phone_code     = '902';
my $phn_length		        = '8';

my $current_provider        = '1000';
my $orphan_provider         = '8999';
my $program_id              = '10015';

my $canada_fee              = '30.00';
my $other_fee               = '60.00';
my $print_two_invoices      = 1;

my $use_full_provider_no    = qw{};
my $provider_no_prefix      = '70';

#this is the provider number for patients of a doctor not currently in the database
my $other_doctor            = '1000';
my $truncated_other_doctor  =   '00';


#server details
my $serveraddress 	= 'yourserver.net';
my $serverlogin 	= getlogin();
my $data_folder     = "/home/share/scripts/data";


#set diagnostics on or off
my $diagnostic;
#$diagnostic 		= 'on';


my %pref;
$pref{mysql_database}	= 'oscar_mcmaster';
$pref{mysql_read}	= 'read user';
$pref{mysql_read_pass}	= 'read password';
$pref{mysql_write}	= 'write user';
$pref{mysql_write_pass}	= 'write password';
$pref{mysql_host}	= '127.0.0.1';
$pref{mysql_port}	= '3306';
$pref{mysql_log}	= "/home/$serverlogin/oscar_scan_sql.log";


#month variables
my %month_names = (
	'JAN' 		=> '1',
	'FEB' 		=> '2',	
	'MAR' 		=> '3',	
	'APR' 		=> '4',	
	'MAY' 		=> '5',	
	'JUN' 		=> '6',	
	'JUL' 		=> '7',	
	'AUG' 		=> '8',	
	'SEP' 		=> '9',	
	'SEPT' 		=> '9',	
	'OCT' 		=> '10',	
	'NOV' 		=> '11',	
	'DEC' 		=> '12',	
	'JANUARY' 	=> '1',	
	'FEBUARY' 	=> '2',	
	'MARCH' 	=> '3',	
	'APRIL' 	=> '4',	
	'MAY' 		=> '5',	
	'JUNE' 		=> '6',	
	'JULY' 		=> '7',	
	'AUGUST' 	=> '8',	
	'SEPTEMBER' 	=> '9',	
	'OCTOBER' 	=> '10',	
	'NOVEMBER' 	=> '11',
	'DECEMBER'	=> '12',			
);
my $this_month_name;

my @month_names = qw(
  blank
  Jan
  Feb
  Mar
  Apr
  May
  Jun
  July
  Aug
  Sept
  Oct
  Nov
  Dec
);

my @day_names = qw(
	Sunday
	Monday
	Tuesday
	Wednesday
	Thursday
	Friday
	Saturday
);



#declare variables
my $provider_data;
my $family_doctor;
my $clinic_start_time = '1';
my $appt_length = '1';
my $this_patient;
my $print_line;
my $report_number=0;
my $tempdir = tempdir();




#declare subroutine variables
my $sec;
my $min;
my $hr;
my $mday;
my $mon;
my $year;
my $wday;
my $yday;
my $isdst;
my $longyr;
my $fixmo;
my $sql_date;
my $sql_time;
my $sql_datetime;
my $string_to_be_padded;
my $desired_string_length;
my $padding_character;
my $right_or_left_justify;
my $decimal_places;
my $decimals;
my $decimal_places_digit;
my $missing_digits;
my $mysql_user;
my $mysql_login;
my $mysql_pass;
my $dbh_name;
my $mysql_preferred_user;
my $mysql_original_user;
my $dbh_inuse;
my $sql;
my @mysql_row;
my $mysql_query;
my $mysql_record;
my @mysql_result;
my @mysql_insert;
my $mysql_update_table;
my $mysql_update_table_array;
my $mysql_disconnect;
my $error;
my $this_sql_insert;
my $mysql_insert_table;
my $sql_data;
my $tempath;
my $path;
my $mysql_result;
my $mysql_set_insert;
my %mysql_set_insert;
















#############################################################################
#    Section 4. CODE
#############################################################################



#get the date time
get_time();




#connect to the mysql database
mysql_connect();


#get the clinic details
my $clinic_details = mysql_hash_query("SELECT * FROM clinic");


#get the list of providers
mysql_hash_query("select * from provider where last_name!='' && provider_type='doctor' order by last_name, first_name");
my @providers = @mysql_result;




header();





#get the provider_no
my $provider_no = mysql_array_query("SELECT provider_no FROM security WHERE user_name='$serverlogin'");


until ($provider_no) {
	print "USER NAME:     [$serverlogin] ";
	my $login_id=<STDIN>;
	$login_id=~s/[^A-Z0-9\.\-\_]//gi;
	unless ($login_id) {
		$login_id = $serverlogin;
	}
    
	$provider_no = mysql_array_query("SELECT provider_no FROM security WHERE user_name='$login_id'");
	
    unless ($provider_no) {
		print "\n";
		print "Sorry, user name $login_id is not recognised.\n";
		print "\n";
	}
}

$provider_data = mysql_hash_query("SELECT * FROM provider WHERE provider_no='$provider_no'");




#get the clinic start time and appt lengths

while ($clinic_start_time!~m/^\d{2}\:\d{2}$/) {
    print "\n\nClinic Start Time: [eg. 10:30] ";
    $clinic_start_time=<STDIN>;
    $clinic_start_time=~s/[\.\ \-]/\:/g;
    $clinic_start_time=~s/[^\d\:]//gi;
}

(my $clinic_start_hour, my $clinic_start_min) = split (/\:/, $clinic_start_time);
my $end_hour;
my $end_min;

print "\nAppointment length (minutes): [5] ";
$appt_length=<STDIN>;
$appt_length=~s/[^\d]//gi;

unless ($appt_length=~m/^(5|10|15|20|25|30)$/) {
    $appt_length = 5;
}




my $continue_loop = 1;


while ($continue_loop) {
 
    get_time();
    
    my $health_card_valid   = qw{};
    my $health_card_data    = qw{};
    my $hin                 = qw{};
    my $hc_prov             = qw{};
    my $dob                 = qw{};    
    my $name                = '1';
    my $sex                 = '1';
    my $first_name          = '1';
    my $middle_initial      = '1';
    my $last_name           = '1';
    my $parsed_first_name   = '1';
    my $parsed_last_name    = '1';
    my $date_of_birth       = 'X';
    my $month_of_birth      = 'X';
    my $day_of_birth        = 'X';
    my $year_of_birth       = 'X';
    my $hc_expiry_year      = 'X';
    my $hc_expiry_month     = 'X';
    my $phone               = 'X';
    my @first_names         = qw();
    $family_doctor          = 'X';
    $this_patient           = qw{};
    
    if ($clinic_start_min=~m/^\d$/) {
	$clinic_start_min="0$clinic_start_min";
    }
    
    if ($clinic_start_hour=~m/^\d$/) {
	$clinic_start_hour="0$clinic_start_hour";
    }

    header();   
    print "Next Appointment: $clinic_start_hour:$clinic_start_min \n\n";

       
    until ($health_card_valid) {
        
        print "SWIPE HEALTH CARD\n\n";
        print "or enter patient Province Code and Health # [PE 12345678]\n";
        print "or enter 'none' if patient does not have a health card\n";
        print "or enter new appt time [hh:mm]\n";
        print "\n";
        print "AB - ALBERTA\n";
        print "BC - BRITISH COLUMBIA\n";
        print "MB - MANITOBA\n";
        print "NB - NEW BRUNSWICK\n";
        print "NL - NEWFOUNDLAND LABRADOR\n";
        print "NT - NORTHWEST TERRITORIES\n";
        print "NS - NOVA SCOTIA\n";
        print "NU - NUNAVUT\n";
        print "ON - ONTARIO\n";
        print "PE - PRINCE EDWARD ISLAND\n";
        print "QC - QUEBEC\n";
        print "SK - SASKATCHEWAN\n";
        print "YT - YUKON\n";
        print "US - UNITED STATES\n";
        print "ZZ - OUTSIDE US & CANADA\n";
        print "\n";
        
        $health_card_data=<STDIN>;
        chomp $health_card_data;
        
        print "\n";

        if ($health_card_data=~m/^\d{2}\:\d{2}$/) {
            ($clinic_start_hour, $clinic_start_min) = split (/\:/, $health_card_data);
                header();   
                print "Next Appointment: $clinic_start_hour:$clinic_start_min \n\n";
        }
        
        elsif ($health_card_data=~m/none/i) {	
            #the user wants to enter the data manually for a user with no health card
            $health_card_data   = "none";
            $hin                = qw{};
            $hc_prov            = qw{};
            $health_card_valid  = 9;
        }

        elsif ($health_card_data=~m/^\d{$phn_length}$/) {
            #the user wants to enter the data manually for a local user
            $hc_prov            = $province_code;
            $hin                = $health_card_data;
            $health_card_data   = "$province_code $health_card_data";
            $health_card_valid  = 9;
        }
        
        elsif ($health_card_data=~m/^(AB|BC|MB|NB|NL|NT|NS|NU|ON|PE|QC|SK|YT|US|ZZ)[\ \:\-\,\.]?(\d{6,14})$/) {	
            #the user wants to enter the data manually'
            $health_card_data   = "$1 $2";
            $hc_prov            = $1;
            $hin                = $2;
            $health_card_valid  = 9;
        }
        
        elsif ($health_card_data=~m/^\%(\d{8})\^([\w\ ]{1,200})\^([MF])\^(\d{4})(\d{2})(\d{2})\^(\d{2})(\d{2})\?\;(\d{8})\=(\d{1})\=(\d{4})(\d{2})(\d{2})\=(\d{2})(\d{2})\?$/i) {
            
            #this appears to be a valid PEI health card
            $health_card_valid = 1;
            $hc_prov = 'PE';
            
            $hin            = $1;
            $name           = $2;
            $sex            = $3;
            $year_of_birth  = $4;
            $month_of_birth = $5;
            $day_of_birth   = $6;
            $hc_expiry_year = $7;
            $hc_expiry_month= $8;
            
            $date_of_birth  = "$year_of_birth-$month_of_birth-$day_of_birth";
            
            my $confirm_hin            = $9;
            my $confirm_sex            = $10;
            my $confirm_year_of_birth  = $11;
            my $confirm_month_of_birth = $12;
            my $confirm_day_of_birth   = $13;
            my $confirm_hc_expiry_year = $14;
            my $confirm_hc_expiry_month= $15;
            
            #check the confirmation data matches the original card data
            unless (
                ($hin eq $confirm_hin)
                && ($year_of_birth eq $confirm_year_of_birth)
                && ($month_of_birth eq $confirm_month_of_birth)
                && ($day_of_birth eq $confirm_day_of_birth)
                && ($hc_expiry_year eq $confirm_hc_expiry_year)
                && ($hc_expiry_month eq $confirm_hc_expiry_month)
                && (($sex eq 'M' && $confirm_sex eq '1') || ($sex eq 'F' && $confirm_sex eq '0'))
                )
            {
                
                #the data does not match, so the card needs to be swiped again
                $health_card_valid = qw{};
                header();
                print "Problem reading card - try swiping card again.\n";
                print "\n";
                
            }
            
            #PEI health cards have a 2 digit expiry year - we need a four digit one
            $hc_expiry_year = "20$hc_expiry_year";
            
            
            #Parse out the first and last names
            ($first_name, $middle_initial, $last_name) = split(/\ /, $name);
            
            unless ($last_name) {
                $last_name = $middle_initial;
            }

            if ($first_name=~m/^\w$/) {
                $first_name = $middle_initial;
            }
            
        }
        
        #elsif {$hin=~m/[insert province card regex]/} {
            #this appears to be a valid [insert province name] health card
            #$health_card_valid = 1;
            #$hc_prov = '[insert province code]';
            
            #$hin            = $1;
            #$name           = $2;
            #$sex            = $3;
            #$year_of_birth  = $4;
            #$month_of_birth = $5;
            #$day_of_birth   = $6;
            #$hc_expiry_year = $7;
            #$hc_expiry_month= $8;
        #}
        
        else {
            #this is not a valid PEI health card, so ask for the data again
            header();
            print "Health card data not recognised.\n";
            print "-Check that this is a $province_name Health Card.\n";
            print "-Try swiping card again.\n";
            print "-If problem persists, enter patient data manually.\n";
            print "\n";
        }
        
    }
 
     
    #default province/state is PE
    unless ($hc_prov) {
        $hc_prov = ' ';
    }
    
    unless ($hc_prov=~m/^(AB|BC|MB|NB|NL|NT|NS|NU|ON|PE|QC|SK|YT|US|ZZ)$/) {
        $hc_prov = $province_code; 
    }       
       
        
    #try and locate the health number and province codes in the database
    if ($hin) {
        $this_patient = mysql_hash_query("SELECT * FROM demographic WHERE hin='$hin' && hc_type='$hc_prov'");
    }
    
    if ($this_patient) {
        #patient is in the system
        
        header();
        
        print "${$this_patient}{'first_name'} ${$this_patient}{'last_name'}\n";
        print "${$this_patient}{'hin'}\n";
        print "\n";
        print "\n";
        
        #update the health card expiry date if necessary
        if (($hc_expiry_year) && ($hc_expiry_month)) {
                mysql_write_simple(
                    "UPDATE demographic
                    SET hc_renew_date='$hc_expiry_year-$hc_expiry_month-01'
                    WHERE demographic_no='${$this_patient}{'demographic_no'}'"
                );
        }
        
        #check the patient still has the same family doctor
        my $this_patients_provider = mysql_hash_query("SELECT * FROM provider WHERE provider_no='${$this_patient}{'provider_no'}'");
        
        print "Is Dr. ${$this_patients_provider}{'first_name'} ${$this_patients_provider}{'last_name'} still this patient\'s family doctor? [Y/N]";
        my $same_family_doctor=<STDIN>;
        chomp $same_family_doctor;

        #if not the same family doctor, pick a new one and update the database
        if ($same_family_doctor=~m/^N/i) {    
            family_doctor();
            mysql_write_simple(
                "UPDATE demographic
                SET provider_no='$family_doctor'
                WHERE demographic_no='${$this_patient}{'demographic_no'}'"
            );
        }        
        
        #check the patient still has the same phone number
        print "Patient's phone number [${$this_patient}{'phone'}]:";
        my $same_phone_number=<STDIN>;
        chomp $same_phone_number;

        #if phone number entered and not the same as one on file, update the database
        if ($same_phone_number=~m/^[\d\ \-\(\)]{7,15}$/) {
                if ($same_phone_number ne ${$this_patient}{'phone'}) {  
                ${$this_patient}{'phone'} = $same_phone_number;
                    mysql_write_simple(
                        "UPDATE demographic
                        SET phone='${$this_patient}{'phone'}'
                        WHERE demographic_no='${$this_patient}{'demographic_no'}'"
                    );
                }
        }
        
        #set the variables with this patient's data
        $last_name                                      = ${$this_patient}{'last_name'};
        $first_name                                     = ${$this_patient}{'first_name'};
        $phone                                          = ${$this_patient}{'phone'};
        $year_of_birth                                  = ${$this_patient}{'year_of_birth'};
        $month_of_birth                                 = ${$this_patient}{'month_of_birth'};
        $day_of_birth                                   = ${$this_patient}{'date_of_birth'};
        $hin                                            = ${$this_patient}{'hin'};
        $family_doctor                                  = ${$this_patient}{'provider_no'};
        $sex                                            = ${$this_patient}{'sex'};
        $hc_prov                                        = ${$this_patient}{'hc_type'};
        ($hc_expiry_year, $hc_expiry_month, my $temp)   = split (/-/, ${$this_patient}{'eff_date'});
        
      
    }
    
    else {
        
        #patient is not in the system
        
        
        #we'll need to get the rest of the data
          
        until ($first_name=~m/^[A-Z\ \-]*$/i) {
            print "First Name:                        ";
            $first_name=<STDIN>;
            $first_name = uc $first_name;
            chomp $first_name;
            unless ($first_name) {
                $first_name = $parsed_first_name;
            }
        }
          
        until ($last_name=~m/^[A-Z\ \-]*$/i) {
            print "Last Name:                         ";
            $last_name=<STDIN>;
            $last_name = uc $last_name;
            chomp $last_name;
            unless ($last_name) {
                $last_name = $parsed_last_name;
            }
        }
        
        until ($date_of_birth=~m/^\d{4}-\d{1,2}-\d{1,2}$/) {
        
            print "Date of Birth:         [YYYY-MM-DD] ";
            $date_of_birth=<STDIN>;
            chomp $date_of_birth;
            $date_of_birth = uc $date_of_birth;
        
            #substitute other separators
            $date_of_birth=~s/[\ \.\:]/-/g;
            
            #separate words from figures if no separator entered
            if ($date_of_birth=~m/^(\d{1,4})([A-Z]*)(\d{1,4})$/i) {
                $date_of_birth="$1-$2-$3";
            }
        
            #look for word versions of months
            for my $this_month_name (keys %month_names) {
                $date_of_birth=~s/[\-\ \.]?$this_month_name[\-\ \.]?/\-$month_names{$this_month_name}\-/;
            }
            
            #correct if DD-MM-YYYY format entered
            if ($date_of_birth=~m/^(\d{1,2})\-(\d{1,2})\-(\d{2,4})$/) {
                $date_of_birth="$3-$2-$1";
            }
            
            #swtich month and day if DD-MM format entered
            if ($date_of_birth=~m/(\d{4})-(\d{1,2})\-(\d{1,2})/) {
                if ($2 > 12) {
                    $date_of_birth="$1-$3-$2";
                }
            }
            
            $date_of_birth=~m/^(\d{4})-(\d{1,2})-(\d{1,2})$/;
            $year_of_birth  = $1;
            $month_of_birth = $2;
            $day_of_birth   = $3;
        }
          
        until ($sex=~m/^[MF]$/i) {
            print "Sex:                          [M/F] ";
            $sex=<STDIN>;
            chomp $sex;
            $sex = uc $sex;
        }
        
        if ($hin) {
        
            until ( ($hc_expiry_year=~m/^\d{4}$/i) && ($hc_expiry_year > $longyr-10) && ($hc_expiry_year <= $longyr+10) ) {
                print "Health Card Exp.Year:        [2000] ";
                $hc_expiry_year=<STDIN>;
                chomp $hc_expiry_year;
                unless ($hc_expiry_year) {
                    $hc_expiry_year = '2000';
                }
            }
              
            until ( ($hc_expiry_month=~m/^\d{1,2}$/i) && ($hc_expiry_month > 0) && ($hc_expiry_month < 13) ) {
                print "Health Card Exp.Month:         [01] ";
                $hc_expiry_month=<STDIN>;
                chomp $hc_expiry_month;
                unless ($hc_expiry_month) {
                    $hc_expiry_month = '01';
                }
            }
        
        }
          
        until (($phone=~m/^\(\d{3}\) \d{3}-\d{4}$/i) || ($phone eq 'NONE')) {
            print "Phone number:                       ";
            $phone=<STDIN>;
            chomp $phone;
            $phone = uc $phone;
            
            unless ($phone) {
                last;
            }
            
            if ($phone=~m/^\(?(\d{3})\)?[\ \-]?(\d{3})[\ \-]?(\d{4})$/i) {
                $phone = "($1) $2-$3";
            }
            
            if ($phone=~m/^(\d{3})\ ?(\d{4})$/i) {
                $phone = "$1-$2";
            }
            
            if (($phone=~m/^(\d{3})[\ \-]?(\d{4})$/i) && ($hc_prov eq $province_code)) {
                $phone = "($province_phone_code) $1-$2";
            }
        }
          
        family_doctor();
        
        
        #insert this patient into the system
        %mysql_set_insert=(
            'last_name'         => $last_name,
            'first_name'        => $first_name,
            'phone'             => $phone,
            'phone2'            => '',
            'email'             => '',
            'pin'               => '',
            'year_of_birth'     => $year_of_birth,
            'month_of_birth'    => $month_of_birth,
            'date_of_birth'     => $day_of_birth,
            'hin'               => $hin,
            'ver'               => '',
            'roster_status'     => 'NR',
            'patient_status'    => 'IN',
            'date_joined'       => $sql_date,
            'chart_no'          => '',
            'provider_no'       => $family_doctor,
            'sex'               => $sex,
            'end_date'          => '0001-01-01',
            'hc_type'           => $hc_prov,
            'eff_date'          => "$hc_expiry_year-$hc_expiry_month-01",
            'hc_renew_date'     => "$hc_expiry_year-$hc_expiry_month-01",
            'family_doctor'     => '<rdohip></rdohip><rd></rd>',
            'sin'               => '',
            'country_of_origin' => '-1',  
            'newsletter'        => 'Unknown'         
        );
        mysql_set_insert('demographic');
   
               
       
        
        #reload and double check we now have patient data
        $this_patient = mysql_hash_query("SELECT * FROM demographic WHERE hin='$hin' && hc_type='$hc_prov' && year_of_birth='$year_of_birth' && month_of_birth='$month_of_birth' && date_of_birth='$day_of_birth' && last_name='$last_name' && first_name='$first_name'");
    
        unless ($this_patient) {
            print "ERROR: PATIENT DATA NOT STORED PROPERLY\n\n\n";
            exit;
            
        }       
 
 
      
        #add an admission record linking this patient to the clinic provider
        %mysql_set_insert=(
            'client_id'                 => ${$this_patient}{'demographic_no'},
            'program_id'                => $program_id,
            'provider_no'               => $current_provider,
            'admission_date'            => $sql_date,
            'admission_from_transfer'   => '0',
            'discharge_from_transfer'   => '0',
            'admission_status'          => 'current',
            'team_id'                   => '0',
            'temporary_admission_flag'  => '0',
            'radioDischargeReason'      => '0',
            'clientstatus_id'           => '0',
            'automatic_discharge'       => '0'
        );
        mysql_set_insert('admission');
 
       
          
    }

    
    #print "\n1. SH=$clinic_start_hour, SM=$clinic_start_min, AL=$appt_length, EH=$end_hour, EM=$end_min\n";
    
    my $actual_end_hour = $end_hour = $clinic_start_hour;
    
    $end_min  = $clinic_start_min + $appt_length;
    
    my $actual_end_min = $end_min -1;
    
    if ($actual_end_min > 59) {
        $actual_end_min = $actual_end_min - 60;
        ++$actual_end_hour;
    }
    
    if ($end_min > 59) {
        $end_min = $end_min - 60;
        ++$end_hour;
    }
    
     #print "2. SH=$clinic_start_hour, SM=$clinic_start_min, AL=$appt_length, EH=$end_hour, EM=$end_min\n";
     
       
    
    #now add the appointment to the database
    %mysql_set_insert=(
        'provider_no'       => $current_provider,
        'appointment_date'  => "$longyr-$fixmo-$mday",
        'start_time'        => "$clinic_start_hour:$clinic_start_min",
        'end_time'          => "$actual_end_hour:$actual_end_min:00",
        'name'              => "${$this_patient}{'last_name'}, ${$this_patient}{'first_name'}",
        'demographic_no'    => "${$this_patient}{'demographic_no'}",
        'notes'             => '',
        'reason'            => '#WIC',
        'location'          => '',
        'resources'         => '',
        'type'              => '',
        'remarks'           => '',
        'status'            => 'H',
        'createdatetime'    => $sql_datetime,
        'creator'           => "${$provider_data}{'last_name'}, ${$provider_data}{'first_name'}"
    );
    mysql_set_insert('appointment');
    
    #print "3. SH=$clinic_start_hour, SM=$clinic_start_min, AL=$appt_length, EH=$end_hour, EM=$end_min\n\n";
    my $family_doctor_name = mysql_array_query("SELECT CONCAT(first_name,' ', last_name) AS provider_name FROM provider WHERE provider_no='$family_doctor'");
    
    if ($clinic_start_min < 10) {
        $clinic_start_min = "0$clinic_start_min";
    }
    
    header();
    print "\n";
    print "APPOINTMENT ADDED\n";
    print "\n";
    print "Appointment time: $clinic_start_hour:$clinic_start_min\n";
    print "\n";
    print "Name:             ${$this_patient}{'first_name'} ${$this_patient}{'last_name'}\n";
    print "Phone number:     ${$this_patient}{'phone'}\n";
    print "Date of birth:    ${$this_patient}{'year_of_birth'}-$month_names[${$this_patient}{'month_of_birth'}]-${$this_patient}{'date_of_birth'}\n";
    print "Sex               ${$this_patient}{'sex'}\n";
    print "Family Doctor:    $family_doctor_name\n";
    print "Health #          ${$this_patient}{'hin'}\n";
    print "Province:         ${$this_patient}{'hc_type'}\n";
    print "Exp. date:        ${$this_patient}{'eff_date'}\n";
    print "\n";
    print "\n";
    print "\n";
    
    
    
    
    #check if this patient needs to pay.
    my $fee_due = 1;
    my $card_expired = 0;
    my $fee_reason = ' ';
 
    if (  (${$this_patient}{'roster_status'} eq 'FS') || ($card_expired) ) {
        $fee_due = $canada_fee;
        $fee_reason = 'Fee for Service roster status';
    }

    elsif (  (${$this_patient}{'hc_type'} eq 'QC') || ($card_expired)  ) {
        $fee_due = $canada_fee;
        $fee_reason = 'Quebec resident';
    }
    
    elsif (  (${$this_patient}{'hc_type'} eq 'US') || (${$this_patient}{'hc_type'} eq 'ZZ')  ) {
        $fee_reason = 'Foreign resident';
           $fee_due = $other_fee;
    }
    
    elsif ( ($hin eq '') || ($hc_expiry_year < $longyr) || ( ($hc_expiry_year == $longyr) && ($hc_expiry_month < $fixmo) )  ) {
        $card_expired = 1;
        $fee_reason = 'Health Card Expired/Missing';
    }
       
    if ($fee_due > 1) {
        print "######################################################################\n";
        print "      $fee_reason";
        print "      FEE DUE:  \$$fee_due\n";
        print "######################################################################\n";
        print "\n";
        print "Press enter to print invoice.\n";
        my $continue=<STDIN>;
        
        my $message2 = "${$clinic_details}{'clinic_name'}\n";
	$message2.= "\n";
	$message2.= "${$clinic_details}{'clinic_address'}\n";
	$message2.= "${$clinic_details}{'clinic_city'}\n";
	$message2.= "${$clinic_details}{'clinic_province'} ${$clinic_details}{'clinic_postal'}\n";
	$message2.= "\n";
	$message2.= "Tel. ${$clinic_details}{'clinic_phone'}\n";
	$message2.= "Fax. ${$clinic_details}{'clinic_fax'}\n";
	$message2.= "_______________________________________________________________________________________\n";
	$message2.= "\n";
	$message2.= "DOCTOR:              Dr. ${$provider_data}{'first_name'} ${$provider_data}{'last_name'}\n";		
        $message2.= "BILLING #:           ${$provider_data}{'billing_no'}\n";
	$message2.= "DATE OF SERVICE:     $sql_date\n";
	$message2.= "_______________________________________________________________________________________\n";
	$message2.= "\n";
	$message2.= "PATIENT NAME:        ${$this_patient}{'last_name'}, ${$this_patient}{'first_name'}\n";
	$message2.= "DoB:                 ${$this_patient}{'year_of_birth'}-${$this_patient}{'month_of_birth'}-${$this_patient}{'date_of_birth'}\n";
	$message2.= "HEALTH #:            ${$this_patient}{'hin'}\n";
	$message2.= "_______________________________________________________________________________________\n";
	$message2.= "\n";
	$message2.= "INVOICE FOR MEDICAL CONSULTATION\n";
	$message2.= "\n";
	$message2.= "REASON FOR FEE:      $fee_reason\n";
	$message2.= "\n";
	$message2.= "FEE PAID:            \$$fee_due\n";
	$message2.= "\n";
	$message2.= "\n";
	$message2.= "\n";
	$message2.= "\n";
	$message2.= "\n";
	$message2.= "__________________________________________________________________________________________\n";
	$message2.= "OSCAR - the free Canadian Electronic Medical Record - http://blog.oscarpei.net";
		
	#print the billing sheet out   
	++$report_number;
	$tempath = "$tempdir/report.$report_number.txt"; 
	open (MESSAGE, ">$tempath");
	print MESSAGE $message2;
	close (MESSAGE);
	system ("enscript --header-font='Bookman-Demi14' --header='INVOICE||OSCAR ELECTRONIC MEDICAL RECORD' --footer='' --word-wrap $tempath");
        
        if ($print_two_invoices) {
		++$report_number;
		$message2 = "COPY INVOICE\n\n".$message2;
		$tempath = "$tempdir/report.$report_number.txt"; 
		open (MESSAGE, ">$tempath");
		print MESSAGE $message2;
		close (MESSAGE);		
	system ("enscript --header-font='Bookman-Demi14' --header='COPY INVOICE||OSCAR ELECTRONIC MEDICAL RECORD' --footer='' --word-wrap $tempath");
        }
        
        print "\n";
	print "Report sent to printer.\n";
        print "\n";
        print "\n";
    }
 
 
    #update the clinic start time  
    $clinic_start_hour = $end_hour;
    $clinic_start_min  = $end_min;



   
    print "Press 'enter' to add another patient [Q to quit]\n";
    my $continue=<STDIN>;

    if ($continue=~m/Q/i) {
        
        print "\n";
        print "Are you sure you want to quit? [Y/N]";
        my $continue=<STDIN>;
        
        if ($continue=~m/Y/i) {
            print "\n";
            print "Goodbye!\n";
            print "\n";
            exit;
        }
        
        exit;
    }

    
}























#disconnect from the database
mysql_disconnect();

print "\n\nGoodbye.\n\n";
                        
exit;

























###########################
#subroutines
###########################

sub family_doctor {
    until ($family_doctor=~m/^\d{4}$/i) {
        
        #with 3 columns, work out how many lines we need
        my %lines           = qw{};
        my $current_line    = 1;
        my $add_to_line     = qw{};
    
        my $lines_needed    = int(scalar(@providers)/3);
        unless (int(scalar(@providers)/3) == (scalar(@providers)/3)) {
            ++$lines_needed
        }
        
        #print out a list of providers
        for my $this_provider (@providers) {
            
            if ($use_full_provider_no) {
                $add_to_line = pad_for_tabulation(${$this_provider}{'provider_no'}, 4, ' ', 'R');
            }

            else {
                ${$this_provider}{'provider_no'}=~m/(\d{1,2}$)/;
                my $truncated_provider_no=$1;
                $add_to_line = pad_for_tabulation($truncated_provider_no, 2, ' ', 'R');
            }
            
            $add_to_line.= ".";
            $add_to_line.= pad_for_tabulation("${$this_provider}{'last_name'}", 12, '.', 'L');
            $add_to_line.= pad_for_tabulation(${$this_provider}{'first_name'},  12, ' ', 'L');
            $add_to_line.= "|";
            $lines{$current_line}.= $add_to_line;
            
            ++$current_line;
            if ($current_line > $lines_needed) {
                $current_line = 1;
            }
        }
        
        for ($current_line = 1; $current_line <= $lines_needed; ++$current_line) {
            print "$lines{$current_line}\n";
        }
        
        
        $current_provider=~m/(\d{1,2}$)/;
        my $truncated_provider_no=$1;
        
        print "Family doctor:               ";
        
        if ($use_full_provider_no) {
            print "[$other_doctor] ";
        }
        else {
            print "  [$truncated_other_doctor] ";
        }
        
        $family_doctor=<STDIN>;
        chomp $family_doctor;
        $family_doctor=~s/\D//g;
        
        unless ($family_doctor) {
            $family_doctor = $other_doctor;
        }
        
        
        if ($family_doctor=~m/^\d{1}$/) {
            $family_doctor =  $provider_no_prefix."0$family_doctor";
        }
    
        if ($family_doctor=~m/^\d{2}$/) {
            $family_doctor =  "$provider_no_prefix$family_doctor";
        }
        
        #check the provider is listed
        if ($family_doctor=~m/^\d{4}$/i) {
            
            my $check_family_doctor = mysql_hash_query("select * from provider where provider_no='$family_doctor' && last_name!='' && provider_type='doctor' order by last_name, first_name");
            
            unless (${$check_family_doctor}{'provider_no'}) {
                $family_doctor = qw{};
            }
        
        }
        
    }
}



sub header {
	system ("clear");
	print "-------------------------------\n";
	print "OSCAR EMR - WALK IN CLINIC\n";
	print "-------------------------------\n";
	print "\n" 	;
}
	


sub get_time {	
	
	#get the date time
	( $sec, $min, $hr, $mday, $mon, $year, $wday, $yday, $isdst) = localtime(time);
	
	$longyr = $year + 1900;
	$fixmo = $mon + 1;
	
	if ($isdst == 1) {
		my $tz = "CDT";
	} else {
		my $tz = "CST";
	}
	
	if ($hr<10) {
		$hr="0".$hr;
	}
	
	if ($min<10) {
		$min="0".$min;
	}
	
	my $sqlmo=$fixmo;
	if ($sqlmo<10) {
		$sqlmo="0".$sqlmo;
	}
	
	my $sqlmday=$mday;
	if ($sqlmday<10) {
		$sqlmday="0".$sqlmday;
	}
	
	$sql_date="$longyr-$fixmo-$mday";
	$sql_time="$hr:$min:$sec";
	$sql_datetime="$sql_date $sql_time";

}



sub mysql_log {

	if ($_[0]) {
		$sql_data=$_[0];
	}

	if ($pref{mysql_log}) {
		unless ($sql_data) {	$sql_data=$sql;	}
		$sql_data=~s/\s/ /g;
		open (MYSQL,">>$pref{mysql_log}");
		flock (MYSQL, 2);
		print MYSQL "$longyr-$fixmo-$mday $hr:$min:$sec - $sql_data\n";
		close (MYSQL);
	}
	return;

}





sub mysql_connect {

	#set up login and password for this user
	unless ($mysql_user) {
		$mysql_user="read";
	}
	$mysql_login="mysql_".$mysql_user;
	$mysql_pass="mysql_".$mysql_user."_pass";
	$dbh_name="dbh_$mysql_user";

	# Connect to the database
	mysql_log("mysql_connect : mysql_user=$mysql_user, dbh_name=$dbh_name, mysql_login=$mysql_login, mysql_pass=$mysql_pass");
	${$dbh_name} = DBI->connect("DBI:mysql:$pref{mysql_database}:$pref{mysql_host}:$pref{mysql_port}","$pref{$mysql_login}","$pref{$mysql_pass}") || ErrorMessage('Could not connect to database');
	
	#record that this database is in use
	$dbh_inuse="dbh_inuse_$mysql_user";
	${$dbh_inuse}=1;

	return;

}




sub mysql_disconnect {
	# Disconnect the current user ($mysql_user) from the database
	# but don't disconnect the read-only user
	#get the passed user name if passed directly
	if ($_[0]) {
		$mysql_user=$_[0];
	}
	
	if (($mysql_user) && ($mysql_user ne "read")) {
	
		#check that the user is connected first
		$dbh_inuse="dbh_inuse_$mysql_user";
		
		if (${$dbh_inuse}) {
			#this user is  connected
			
			$dbh_name="dbh_$mysql_user";
			mysql_log("mysql_disconnect : mysql_user=$mysql_user, dbh_name=$dbh_name, mysql_login=$mysql_login, mysql_pass=$mysql_pass");
			${$dbh_name}->disconnect || mysql_log('ERROR could not disconnect database');
			
			#record that this database is no longer in use
			${$dbh_inuse}=0;
		}	
	}
	
	return;

}



sub mysql_check_connection {
	#check that the preferred user is the one currently selected
	if ($mysql_user eq $mysql_preferred_user) {
		$mysql_original_user=$mysql_user;
	} else {
		$mysql_original_user=$mysql_user;
		$mysql_user=$mysql_preferred_user;
	}
	
	$dbh_name="dbh_$mysql_user";
	$dbh_inuse="dbh_inuse_$mysql_user";
	$mysql_disconnect=0;
	
	#check that the preferred user is connected
	unless (${$dbh_inuse}) {
		#this user is not connected
		$mysql_disconnect=1;
		mysql_connect();
	}	
	return;

}


sub mysql_check_connection_finish {	

	#check if this user was only connected for this event
	if ($mysql_disconnect) {
		mysql_disconnect();
	}
	
	#reset the current user to the original user
	$mysql_user=$mysql_original_user;
	
	return $mysql_user;

}



sub mysql_simple {
	#this subroutine is passed a full statement as $sql and sends it to the database as a 'read' user
	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
 	
 	$mysql_preferred_user="read";
	mysql_submit_simple($sql);
	return 1;

}


sub mysql_write_simple {
	#this subroutine is passed a full statement as $sql and sends it to the database as a 'write' user
 	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
	
	#print "\n\n**test mode** sub mysql_write_simple has had mysql_submit_simple($sql) commented out - your data has NOT been saved!\n\n$sql\n\n";
	
	$mysql_preferred_user="write";
	mysql_submit_simple($sql);
	return 1;

}


sub mysql_submit_simple {
	#this subroutine is passed a full statement as $sql and sends it to the database as the user $preferred_user
	
 	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
 	
	@mysql_row=();
	
	mysql_check_connection();
	
	mysql_log("mysql_submit_simple : $sql");
	$mysql_query = ${$dbh_name}->do ($sql) || ErrorMessage('Could not submit data to the database');
	
	mysql_check_connection_finish();
	return 1;

}



sub mysql_array_query {
 
 	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
	
	$mysql_preferred_user="read";
	mysql_check_connection();
	
	$mysql_query = ${$dbh_name}->prepare ($sql);
	@mysql_result=();
	
	if (defined($mysql_query)) {
	
		mysql_log("mysql_array_query : $sql");
		$mysql_query->execute() || ErrorMessage('Could not execute array query on database');
		
		while (@mysql_row = $mysql_query->fetchrow_array()) {
			@mysql_result=@mysql_row;
		}
		
	} else {
		mysql_log("mysql_array_query (not found) : $sql");
		ErrorMessage('Could not find query to submit to database');
	}
	$mysql_query->finish();
	
	mysql_check_connection_finish();
	
	#pass the first result back as a return value (or undefined, if no result)
	if ($mysql_result[0])	{
		return $mysql_result[0];
	}
	
	return;
	
}



sub mysql_hash_query {
 
 	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
 
	$mysql_preferred_user="read";
	mysql_check_connection();
	$mysql_query = ${$dbh_name}->prepare ($sql);
	@mysql_result=();
	
	if (defined($mysql_query)) {
		
		mysql_log("mysql_hash_query : $sql");
		$mysql_query->execute() || ErrorMessage('Could not execute hash query on database');
		
		
		
		@mysql_result=();
		while ($mysql_record = $mysql_query->fetchrow_hashref()) {
			push (@mysql_result, $mysql_record);
		}

		
	} else {
		mysql_log("mysql_hash_query (not found) : $sql");
		ErrorMessage('Could not find hash query to submit to database');
	}
	$mysql_query->finish();
	
	mysql_check_connection_finish();
	
	#return the mysql_result array (containing hash references), or undefined if no result
	if ($mysql_result[0])	{
		return $mysql_result[0];
	}
		
	return;
}


sub mysql_set_insert {

	#this subroutine is passed:
	#	1. the table name ($mysql_update_table)
	#	2. a hash of elements to include (%mysql_update)
	#and it then creates a mysql statement ($sql) and sends it to the database as a 'write' user

	#get the passed table name if passed directly
	if ($_[0]) {
		$mysql_insert_table=$_[0];
	}
	
	$mysql_preferred_user="write";
	mysql_check_connection();

	#insert this line in the database
	$error="mysql_insert";
	$sql="INSERT INTO $mysql_insert_table SET ";
	for $this_sql_insert (keys %mysql_set_insert) {
		$mysql_set_insert{$this_sql_insert}=~s/\'/\`/g;
		$sql.="$this_sql_insert='$mysql_set_insert{$this_sql_insert}', ";
	}
	$sql=~s/, $//;
	
    mysql_log("mysql_set_insert : $sql");
	mysql_write_simple($sql);
	
	mysql_check_connection_finish();
	
	return;

}


sub pad_for_tabulation {

	#pads out the string
	
	#pass the following variables:
	#1. the string to be padded - including decimals, decimal places, and thousands separators
	#2. the desired length
	#3. the character to use for padding (usually ' ' or '0', defaults to '0')
	#4. right of left justify ('R' or 'L')
	#5. the number of decimal places to use, preceeded by T if thousands commas to be used (eg: '2', 'T2' or just'T' to just include thousands)
	
	($string_to_be_padded, $desired_string_length, $padding_character, $right_or_left_justify, $decimal_places) = @_;
	
	unless ($padding_character) {
		$padding_character='0';
	}
	
	#add decimal places
	if ($decimal_places) {
		$decimals=q{};
		if ($decimal_places =~m/\d/) {
			$decimal_places_digit=$decimal_places;
			$decimal_places_digit=~s/^T//;
			($string_to_be_padded, $decimals) = split (/\./, $string_to_be_padded);	
			$decimals.="0"x$decimal_places_digit;
			$decimals=substr ($decimals, 0, $decimal_places_digit);
			$decimals=".$decimals";
		}
		
		#add thousands separators if required
		if  ($decimal_places =~m/T/) {
			$string_to_be_padded=~ s/(^[-+]?\d+?(?=(?>(?:\d{3})+)(?!\d))|\G\d{3}(?=\d))/$1,/g;
		}
		
		$string_to_be_padded.=$decimals;
	}
	
	#next check if the string is too long, and truncate if required
	if (length $string_to_be_padded > $desired_string_length) {
		$string_to_be_padded=substr ($string_to_be_padded, 0, $desired_string_length);
	}
	
	
	$missing_digits = $desired_string_length - (length $string_to_be_padded);
	$missing_digits="$padding_character"x$missing_digits;
	if ($right_or_left_justify eq 'L') {
		$string_to_be_padded=$string_to_be_padded.$missing_digits;
	}
	else {
		$string_to_be_padded=$missing_digits.$string_to_be_padded;
	}

	return $string_to_be_padded;

}


sub rand_id {

	my $rand_id_digits = $_[0];
        
        unless ($rand_id_digits) {
            $rand_id_digits = 5;
        }
        
        my $rand_id='';
        
	while (length $rand_id < $rand_id_digits) {
		my $ftemp=int(rand 9)+1;
		$rand_id.=$ftemp;
	}
	
	return $rand_id;
	
}


sub ErrorMessage {
	print "\n";
	print "\n";
	print "Error: $_\n";
	exit;
}