# $File: //depot/RT/104/lib/RT/User_Local.pm $ $Author: autrijus $
# $Revision: #54 $ $Change: 10147 $ $DateTime: 2004/02/20 02:56:26 $

use strict;
no warnings 'redefine';
use Tie::DBI;

=begin test

    my $u = RT::User->new;
    $u->Load('root');
    $u->get('sex');	    # 'Female' (in chinese)
    $u->describe('sex');    # 'Gender' (in chinese)
    $u->normalize('sex');   # EMPLOYEE_SEX

=cut

my $lc;
my ($dbh, %table, %desc, %meta, @tables, %fields);
our %args;

sub init_args {
    return if %args;

    if ($^O ne 'MSWin32') {
	$RT::ExternalDatabaseDSN    = 'dbi:mysql:89390142';
	$RT::ExternalDatabaseUser   = 'root';
    }

    %args = (
    	db       => $RT::ExternalDatabaseDSN,
    	user     => $RT::ExternalDatabaseUser,
    	password => $RT::ExternalDatabasePass,
    	ENCODING => 'big5',
    	CLOBBER  => 0
    );
}

my %last_failure;
sub dbi_connect {
    my $time = $last_failure{ @args{qw( db user password )} };
    return if $time and abs(time - $time) < 60;

    my $rv;
    for (1..10) {
        $rv = eval { DBI->connect(@args{qw( db user password )}) };
	if ($rv) {
	    my ($date) = $rv->selectrow_array("select UPDATE_DATE from hrms_sys_flowchar ORDER BY UPDATE_DATE DESC");
	    last if $date;
	}
	sleep 1;
    }

    $last_failure{ @args{qw( db user password )} } = ( $rv ? 0 : time );
    return $rv;
}

my %Exception = (
    EMPLOYEE_GRADE_AC_DATE  => 'alteration_cause',
    EMPLOYEE_LEVEL_AC_DATE  => 'alteration_cause',
    APPOINT_REASON_ID	    => 'alteration_cause',
    APPOINT_REASON_ID_1	    => 'alteration_cause',
    TOPIC_FIRST_BOSS_ID	    => 'employee',
    TOPIC_SECOND_BOSS_ID    => 'employee',
);

sub dbh {
    $_[0]->init_args;
    $dbh ||= dbi_connect();
}

#   id		    ExternalAuthId
#   cname	    ExternalContactInfoId
#   no		    Gecos
#   job		    AuthSystem
#   firstname	    RealName
#   lastname	    NickName
#   department	    Organization
#   office_tel_1    WorkPhone
#   email_1	    EmailAddress

sub ValidateEmailAddress { 1 }

# Fill in RealName with Name unless it looks like an email...
sub RealName {
    my $self = shift;
    my $real_name = $self->SUPER::RealName;
    return $real_name if $real_name;
    $real_name = $self->Name;
    return $real_name unless $real_name =~ /\@/;
    return '';
}


sub get_hash {
    # get array of id and name pairs
    $RT::Logger->debug("Selecting $_[1]");

    init_args();
    $dbh ||= dbi_connect();

    Encode::_utf8_on($_[1]);
    my $ary_ref = eval { $dbh->selectcol_arrayref(
	Encode::encode(big5 => $_[1]),
	{ Columns => [1, 2] }
    ) };

    require Encode;
    return map { Encode::decode(big5 => $_) } @$ary_ref if wantarray;
    return { map { Encode::decode(big5 => $_) } @$ary_ref };
}

sub call_sp {
    # call some procedure
    my ($self, $key, $fields, $Ticket) = @_;
    my $Table = $self->ext_table('hrms_userdefined_procedure') or return undef;
    my $sql = $Table->{$key}{'hrms_userdefined_procedure_sql'} or return undef;

    require Encode;
    $RT::Logger->debug("Interpolating sp $sql\n");
    $sql =~ s/(\$Ticket[->\w]*)/"'" . $self->escaped(eval("$1")) . "'"/eg;
    $sql =~ s/\$(\w+)/"'" . $self->val_escaped($1) . "'"/eg;
    $sql =~ s/\@_/join(', ', map "'$_'", @$fields) || 'XXXX'/eg;
    $sql =~ s/,\s+XXXX//;
    $sql = Encode::encode(big5 => $sql, 512);
    $RT::Logger->debug("Calling sp $sql\n");

    init_args();
    my $dbh2 = dbi_connect();
    my $rv = $dbh2->selectcol_arrayref($sql) or die $dbh2->errstr;
    $dbh2->disconnect;

    return '' if !$rv or !length($rv->[0]);
    return Encode::decode(big5 => $rv->[0]);
}

sub escaped {
    my ($self, $val) = @_;
    $val =~ s/~/~~/g;
    $val =~ s/,/~,/g;
    $val =~ s/\'/\'\'/g;
    return $val;
}

sub val_escaped {
    my ($self, $key) = @_;
    my $val = $self->val($key);
    $val =~ s/~/~~/g;
    $val =~ s/,/~,/g;
    $val =~ s/\'/\'\'/g;
    return $val;
}

sub _val {
    my ($self, $key) = @_;

    if ($key eq 'DSN') {
	# XXX: respect currentuser?
	if ($^O eq 'MSWin32') {
	    require Win32::TieRegistry;
	    return $Win32::TieRegistry::Registry->{"HKEY_LOCAL_MACHINE\\Software\\ODBC\\ODBC.INI\\rt"}{'database'};
	}

	my $dsn = $RT::ExternalDatabaseDSN;
	$dsn =~ s/^.*://;
	return $dsn;
    }

    my $id	= $self->ExternalAuthId;
    my $table	= $self->table('employee');
    my $main	= $table->{$id} or return undef;
    # die "Cannot find $id in $table". join(',', keys %$table);
    $key = $self->normalize($table, $key) or return undef;
    my $value = $main->{$lc->($key)};
    $value = $value->[0] if UNIVERSAL::isa($value, 'ARRAY');

    return $value;
}

sub vals {
    my ($self, $key) = @_;
    my $table = $self->table('employee');
    $key = $self->normalize($table, $key) or return undef;

    my $remark = eval { $self->load_meta(tied(%$table)->{table})->{$key}{remark} };
    return $remark if $remark and %$remark;

    # now we have to decide it this value is actually pointer to something else.
    if (my $t = eval { $self->table( $Exception{uc($key)} || $key ) }) {
	my %x;
	my $desc = $lc->($desc{tied(%$t)->{table}});
	foreach my $k (keys %$t) {
	    $x{$k} = $t->{$k}{$desc};
	}
	return \%x;
    }

    return {};
}

sub code {
    my ($self, $key) = @_;
    my $val = $self->val($key) or return undef;

    my ($code) = eval { $dbh->selectrow_array(
       	"SELECT ${key}_code FROM hrms_$key WHERE ${key}_id = $val"
    ) };

    return $code;
}

our %_104Cache;
sub check_cache {
    my ($self, $op, $id) = @_;

    my ($date) = $RT::Handle->dbh->selectrow_array(qq(
        select a_session from sessions where id = '104_HRMS_SYSTEM_SYNCHRONIZE_LOCK'
    ));

    if ($_104Cache{date} ne $date) {
	%_104Cache = ( date => $date );
    }
    
    $_104Cache{$op}{$id || $self->Id} ||= {};
}

sub get {
    my ($self, $key) = @_;
    my $cache = $self->check_cache('get');

    return $cache->{$key} if exists $cache->{$key};
    $cache->{$key} = $self->_get($key);
}

sub val {
    my ($self, $key) = @_;
    my $cache = $self->check_cache('val');

    return $cache->{$key} if exists $cache->{$key};
    $cache->{$key} = $self->_val($key);
}

sub _get {
    my ($self, $key) = @_;
    my $id	= $self->ExternalAuthId;
    my $table	= $self->table('employee');
    my $main	= $table->{$id} or return undef;
    # die "Cannot find $id in $table". join(',', keys %$table);

    $key = $self->normalize($table, $key) or return undef;

    my $remark	= $self->load_meta(tied(%$table)->{table})->{$key}{remark};
    my $value	= $main->{$lc->($key)};
    $value = $value->[0] if UNIVERSAL::isa($value, 'ARRAY');
    return $remark->{$value} if exists $remark->{$value};

    # now we have to decide it this value is actually pointer to something else.
    if (my $t = $self->table( $Exception{uc($key)} || $key )) {
	return '' unless $value;
	my $cache = $self->check_cache('table', -1);
	return $cache->{$value} if exists $cache->{$value};
	my $e = eval { $t->{$value} };
	return($cache->{$value} = $e ? $e->{$lc->($desc{tied(%$t)->{table}})} : '');
    }

    return '' if $value eq '1900-01-01 00:00:00';
    $value =~ s/ 00:00:00$//;	# warning: blind dating can make you sick!
    return $value;
}

sub describe {
    my ($self, $key) = @_;
    my $table	= $self->table('employee');
    $key = $self->normalize($table, $key) or return undef;
    my $descr = eval { $self->load_meta(tied(%$table)->{table})->{$key}{desc} } or return undef;
    $descr =~ s/ID$//;
    $descr;
}

sub meta {
    my ($self, $key) = @_;
    my $table	= $self->table('employee') or return undef;
    return sort keys(%{$self->load_meta(tied(%$table)->{table})});
}

# this turns 'sex' into 'EMPLOYEE_SEX'.
sub normalize {
    my ($self, $table, $key) = @_;
    my $name = eval { tied(%$table)->{table} } or return '';
    my $meta = $self->load_meta( $name );
    return $key if exists $meta->{$key};

    die "No meta for $name" unless %$meta;

    $name = lc($name);
    $name =~ s/^hrms_//;
    $name =~ s/s$//;

    foreach my $col ( keys %$meta ) {
	return $col if lc($col) =~ /^(?:hrms_)?(?:\Q$name\Es?_)?\Q$key\Es?(?:_?id)?$/;
    }

    return;
    die "Can't normalize $key\n";
}

sub table {
    my ($self, $table) = @_;

    init_args();
    if (!exists $table{$table}) { eval {
	my ($t, $i, $d) = $self->src($table) or return undef;
	tie %{$table{$table}||={}}, 'Tie::DBI', {
	    %args, table => $t, key => $i,
	}
    } }

    $table{$table} ||= undef;
}

sub ext_table {
    my ($self, $table, $key) = @_;

    init_args();
    if (!exists $table{$table}) { eval {
	tie %{$table{$table}||={}}, 'Tie::DBI', {
	    %args, table => $table, key => ($key || "${table}_id"),
	}
    } }

    $table{$table} ||= undef;
}

sub tables {
    return @tables if @tables;

    init_args();
    $dbh ||= dbi_connect() or return ();
    my $driver = $dbh->{Driver}{Name};
    return(@tables = map {
	s/.*"(.*)".*/$1/ if ($driver eq 'ODBC'); $_;
    } $dbh->tables);
}

sub src {
    my ($self, $key) = @_;
    $key = lc($key);
    $key =~ s/^hrms_//;
    $key =~ s/_id$//;

    my ($id, $desc, $table);
    foreach my $t ($self->tables) {
	next unless lc($t) =~ /^\W*(?:hrms_)?\Q$key\Es?\W*$/;

	foreach my $f ($self->fields($t)) {
	    $id	    = $f if lc($f) =~ /^\W*(?:hrms_)?\Q$key\Es?(?:_id)?\W*$/;
	    $desc   = $f if lc($f) =~ /^\W*(?:hrms_)?\Q$key\Es?_(?:cname|desc)\W*$/;
	}

	$table = $t;
	last;
    }

    return unless $id and $desc;

    $desc{$table} = $desc;
    return ($table, $id, $desc);
}

sub load_meta {
    my ($self, $table) = @_;

    $table =~ s/\W//g;
    return $meta{$table} if $meta{$table};

    my $rv;

    # begins 104-specific code
    require Encode;

    my $query = "select TABLE_NAME, COLUMN_NAME, COLUMN_DESC, COLUMN_REMARK from hrms_tables, hrms_table_columns WHERE hrms_tables.id = hrms_table_columns.table_id AND table_name = '$table'";

    # t.id AS TABLE_ID,
    if ($^O eq 'MSWin32') {
	$query = << ".";
SELECT
     o.name AS TABLE_NAME,
     c.NAME AS COLUMN_NAME,
     SUBSTRING(
	CAST(P.VALUE AS VARCHAR(4000)),
	1,
	CASE CHARINDEX('|', CAST(P.VALUE AS VARCHAR(4000)))
	WHEN 0
	    THEN LEN(CAST(P.VALUE AS VARCHAR(4000)))
	    ELSE CHARINDEX('|',CAST(P.VALUE AS VARCHAR(4000)))-1
	END
    )       AS COLUMN_DESC,
    SUBSTRING(
	CAST(P.VALUE AS VARCHAR(4000)),
	CASE CHARINDEX('|',CAST(P.VALUE AS VARCHAR(4000)))
	WHEN 0
	    THEN LEN(CAST(P.VALUE AS VARCHAR(4000)))+1
	    ELSE CHARINDEX('|',CAST(P.VALUE AS VARCHAR(4000)))+1
	END,
	LEN(CAST(P.VALUE AS VARCHAR(4000)))
    )       AS COLUMN_REMARK
FROM syscolumns AS c
LEFT JOIN sysobjects AS o    ON o.id=c.id
LEFT JOIN hrms_tables AS t   ON t.table_name=o.name
LEFT JOIN sysproperties AS p ON p.id=c.id
			    AND p.smallid=c.colorder
			    AND p.name='MS_Description'
WHERE o.xtype = 'U'
  AND t.table_id IS NOT NULL
  AND o.name = '$table'
ORDER BY t.id
.
    }

    my $arrayref = $dbh->selectall_arrayref( $query ) or return $rv;

    my %m;
    foreach my $row (@$arrayref) {
	next unless length $row->[1];
	my $m = ($m{$row->[1]} = {});

	if ($row->[2]) {
	    $row->[2] =~ s/(?:\s|\xA1\@)+$//;
	    $m->{desc} = Encode::decode(big5 => $row->[2]);
	};

	next unless $row->[3];
	my $remark = Encode::decode(big5 => $row->[3]);
	foreach my $option (split(/ /, $remark)) {
	    my ($key, $val) = split(/:/, $option, 2) or next;
	    $m->{remark}{$key} = $val;
	}
    }

    $meta{$table} = \%m;
}

sub fields {
    my ($self, $table) = @_;

    return $fields{$table} if $fields{$table};

    my $driver = $dbh->{Driver}{Name};

    if ($driver eq 'ODBC') {
	$lc ||= sub { lc($_[0]) };
        $fields{$table} = [map $_->{COLUMN_NAME}, values(
            %{$dbh->selectall_hashref( "SP_COLUMNS $table;", 'COLUMN_NAME' ) || {}}
        )];
    }
    else {
	$lc ||= sub { lc($_[0]) };
        # only tested on mysql, but should work elsewhere too
        $fields{$table} = [map $_->[0], @{$dbh->selectall_arrayref("DESCRIBE $table;")}];
    }

    return @{$fields{$table}};
}

sub field_values {
    my ($self, $field) = @_;

    my $sql = eval { $self->ext_table('hrms_userdefined_field')->{$field}->{
	'hrms_userdefined_field_sql'
    } } or return {};

    $sql =~ s/\$(\w+)/$self->val($1)/ge;
    return $self->get_hash($sql);
}

sub field_cname {
    my ($self, $field) = @_;
    return $self->ext_table('hrms_userdefined_field')->{$field}->{hrms_userdefined_field_cname};
}

sub fields_cname {
    $_[0]->column_hash('hrms_userdefined_field', 'cname');
}

sub procedures_cname {
    $_[0]->column_hash('hrms_userdefined_procedure', 'cname');
}

sub column_hash {
    my ($self, $table, $field) = @_;
    my $rv = $self->ext_table($table);

    if ($RT::CompanySpecific and my $company = $self->Attribute('Company')) {
	return {
	    map {$_ => $rv->{$_}{"${table}_$field"}}
	    grep {
		my $rv_company = $rv->{$_}{"company_id"};
		(!$rv_company or $rv_company == $company);
	    }
	    keys %$rv
	};
    }
    else {
	return { eval { map {$_ => $rv->{$_}{"${table}_$field"}} keys %$rv } };
    }
}

sub no_to_id {
    my ($self, $no) = @_;
    return $self->get_hash(
	"SELECT EMPLOYEE_NO, EMPLOYEE_ID FROM HRMS_EMPLOYEE WHERE EMPLOYEE_NO = '$no';"
    )->{$no};
}

1;
