#this is a script to dump the frequency table for MED codes 

#------------ make database connection -----------------------#

$db_name 	= ""; #CPMC database name
$db_user	= ""; 
$db_passwd 	= "";

use DBI;
$dbh = DBI->connect('dbi:Sybase:$db_name','$db_name','$db_passwd'); 

#-------------  query each table and determine frequency -------#

%patmed = (); # a hash of hash, %patmed {MRN}{MED_CODE} = 1

findFreq1Col('EVENT_CODE','LAB_EVENT'); #Lab Event
findFreq1Col('COMP_CODE','LAB_COMP'); #only 1 column has MED codes

findFreq1Col('EVENT_CODE','ORDER_EVENT'); # Order Event
findFreq2Col('COMP_CODE','CODED_VALUE','ORDER_COMP'); #2 columns have MED codes

findFreq1Col('EVENT_CODE','ASSESSMENT_EVENT'); #Assessment Event
findFreq2Col('COMP_CODE','CODED_VALUE','ASSESSMENT_COMP'); # 2 columns has MED codes

findFreq1Col('EVENT_CODE','SERVICE_EVENT'); #Service Event
findFreq2Col('COMP_CODE','CODED_VALUE','SERVICE_COMP'); # 2 columns has MED codes

#------------- de-identify and dump ---------------------------#
# output= 	id1|medcode1
#			id1|medcode2
#			id2|medcode1
#			....

$indx = 1;

foreach my $mrn (keys %patmed){		
	foreach my $med_code (keys %{$patmed{$mrn}}){
			print "$indx|$med_code\n";
	}
	$indx ++;
}



#----sub-routine that iterates sequentially thru row of each table----#

#argument_0 = column name, mostly its EVENT_CODE (except for LAB_COMP)
#argument_1 = table name

sub findFreq1col(){

	$sql = "select MRN,$_[0] from $_[1]";

	$sth = $dbh->prepare($sql);

	$sth->execute || 
           die "Could not execute SQL statement ... maybe invalid?";

	while(@row=$sth->fetchrow_array)
	{
		$mrn 	= $row[0];
		$arg1 	= $row[1];
		
		$patmed{$mrn}{$arg1} = 1; # set the value to 1 if present		
		
	}
}

#argument_0 = column name, COMP_CODE
#argument_1 = column name, CODED_VALUE
#argument_2 = table name

sub findFreq2col(){

	$sql = "select MRN, $_[0], $_[1] from $_[2]";

	$sth = $dbh->prepare($sql);

	$sth->execute || 
           die "Could not execute SQL statement ... maybe invalid?";

	while(@row=$sth->fetchrow_array)
	{
		$mrn 		= $row[0];
		$comp_code 	= $row[1];
		$coded_code	= $row[2];
		
		$patmed{$mrn}{$comp_code} = 1; # set the value to 1 if present		
		
		#when coded_value is a med code.. how to determine ? use VALUE_TYPE ? 
		$patmed{$mrn}{$coded_value} = 1;
		
	}
}