#!/usr/bin/perl

package ledgersmb;
use DBI;
use strict;
use Date::Manip;
use Data::Dumper;
use Carp qw(cluck);

sub new {
	my ($class,$params) = @_;
	my $self = bless({},$class);
	$self->setStart($params->{start});
	$self->setEnd($params->{end});
	$self->{destconn} = $self->_openDataSource($params->{destconn},$params->{destuser},$params->{destpass});
	$self->{prefix} = $params->{prefix};
	return $self;
}

sub shutdown {
	my $self = shift;

	foreach my $key (keys(%{$self->{destsths}})) {
		$self->{destsths}->{$key}->finish();
	}
	$self->{destconn}->disconnect();
}

sub commit {
	my $self = shift;

	$self->{destconn}->commit();
}

sub rollback {
	my $self = shift;

	$self->{destconn}->rollback();
}

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

	$self->{'startdate'} = $start;
}

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

	$self->{'enddate'} = $end;
}

# open a database connection and return handle
sub _openDataSource {
	my ($self,$connstr,$user,$pass) = @_;

	my $dh = DBI->connect($connstr,$user,$pass, {AutoCommit => 0, RaiseError => 1, sslmode => 'prefer'});
	die("Failed to open Datasource $connstr") unless $dh;
	$dh->do("SET CLIENT_ENCODING TO 'LATIN1'");
	return $dh;
}

# prepare a statement and store a ref to it so we can close it later.
sub _destPrepare {
	my ($self,$key,$sql) = @_;

	my $sth = $self->{destconn}->prepare($sql);
	$self->{destsths}->{$key} = $sth;
	return $sth;
}

# get a previously stored handle
sub _getDestStatement {
	my ($self,$key) = @_;

	return $self->{destsths}->{$key};
}

# get statement which will insert into the vendor table
sub _getVendorInsertStmt {
	my $self = shift;

	my $vendinsert = "INSERT INTO vendor (
	name,address1,address2,city,state,zipcode,country,contact,phone,fax,email,notes,
	terms,vendornumber,cc,bcc, business_id,taxnumber,sic_code,
	iban,bic,employee_id,language_code,pricegroup_id,curr,startdate,enddate
	) VALUES(
	?, -- name
	?, -- address1
	null, -- address2
	?, -- city
	?, -- state
	?, -- zip
	?, -- country
	null, -- contact
	?, -- phone
	null, -- fax
	?, -- email
	null, -- notes
	0, -- discount
	?, -- vendornumber
	null, -- cc
	null, -- bcc
	null, -- business_id
	null, -- taxnumber
	null, -- sic_code
	null, -- iban
	null, -- bic
	null, -- employee_id
	'en_us', -- language_code
	null, -- pricegroup_id
	'USD', -- curr (currency?)
	?, -- startdate
	null) -- enddate";

	my $sth = $self->_getDestStatement('vendinsert');
	$sth = $self->_destPrepare('vendinsert',$vendinsert) unless $sth;
	return $sth;
}

# get statement which will insert into GL table
sub _getGLInsertStmt {
	my $self = shift;

	my $glinsert = "INSERT INTO gl (
	reference,
	description,
	notes,
	transdate
	) VALUES(
	?, -- reference
	?, -- description
	?, -- notes
	?) -- transdate";
	my $sth = $self->_getDestStatement('glinsert');
	$sth = $self->_destPrepare('glinsert',$glinsert) unless $sth;
	return $sth;
}

# get statement which will select the internal key id for the current unrealized for a given gl account
sub _getGLCurrentUnrealizedStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('glcurrentunrealized');
	$sth = $self->_destPrepare('glcurrentunrealized',"SELECT id FROM gl WHERE reference = ? AND description = 'CURRENT'") unless $sth;
	return $sth;
}

sub _getAcctTransInsertStmt {
	my $self = shift;

	my $atinsert = "INSERT INTO acc_trans (
	trans_id,
	chart_id,
	amount,
	source,
	memo,
	transdate
	) VALUES(
	?, -- trans_id
	?, -- chart_id
	?, -- amount
	?, -- source
	?, -- memo
	?) -- transdate";
	my $sth = $self->_getDestStatement('acctransinsert');
	$sth = $self->_destPrepare('acctransinsert',$atinsert) unless $sth;
	return $sth;
}

sub _getGLReversedStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('glreversed');
	$sth = $self->_destPrepare('glreversed',"UPDATE gl SET description = 'REVERSED' WHERE id = ?") unless $sth;
	return $sth;
}

sub _getChartIdStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('getchartid');
	$sth = $self->_destPrepare('getchartid',"SELECT id FROM chart WHERE accno = ?") unless $sth;
	return $sth;
}

sub _getAPStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('getap');
	$sth = $self->_destPrepare('getap',"SELECT * FROM ap WHERE invnumber = ?") unless $sth;
	return $sth;
}

# get ap updating statement
sub _getAPUpdateStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('updateap');
	$sth = $self->_destPrepare('updateap',"UPDATE ap SET paid = amount, transdate = ?, datepaid = ? WHERE invnumber = ?") unless $sth;
	return $sth;
}

sub _getChartInsertStmt {
	my $self = shift;

	my $chartinsert = "INSERT INTO chart (
	accno,
	description,
	charttype,
	category,
	link
	) VALUES(
	?, -- accno
	?, -- description
	'A', -- charttype
	'L', -- category
	'AP' -- link
	)";

	my $sth = $self->_getDestStatement('chartinsert');
	$sth = $self->_destPrepare('chartinsert',$chartinsert) unless $sth;
	return $sth;
}

sub _getCustIDStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('getcustid');
	$sth = $self->_destPrepare('getcustid',"SELECT id FROM vendor WHERE vendornumber = ?") unless $sth;
	return $sth;
}

sub _getAPInsertStmt {
	my $self = shift;

	my $arinsert = "INSERT INTO ap (
	invnumber,
	transdate,
	vendor_id,
	amount,
	paid,
	datepaid,
	notes,
	curr,
	ordnumber,
	intnotes
	) VALUES(
	?, -- invnumber
	?, -- transdate
	?, -- vendor_id
	?, -- amount
	?, -- paid
	?, -- datepaid
	?, -- notes
	?, -- currency
	?, -- ordnumber
	? -- intnotes
	)";

	my $sth = $self->_getDestStatement('apinsert');
	$sth = $self->_destPrepare('apinsert',$arinsert) unless $sth;
	return $sth;
}

sub _getGLCurrentUnrealizedQtyStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('glurqtselect');
	$sth = $self->_destPrepare('glurqtselect',"SELECT amount FROM acc_trans WHERE trans_id = ?") unless $sth;
	return $sth;
}

sub _getAPKeySelectStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('apkeyselect');
	$sth = $self->_destPrepare('apkeyselect',"SELECT id FROM ap WHERE invnumber = ?") unless $sth;
	return $sth;
}

sub _getAcctKeyStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('acctkeyselect');
	$sth = $self->_destPrepare('acctkeyselect',"SELECT id FROM chart WHERE accno = ?") unless $sth;
	return $sth;
}

sub _getKeyStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('lastkeyselect');
	$sth = $self->_destPrepare('lastkeyselect',"SELECT currval('id')") unless $sth;
	return $sth;
}
	
sub _getGLLastKeyStmt {
	my $self = shift;

	my $sth = $self->_getDestStatement('gllastkeyselect');
	$sth = $self->_destPrepare('gllastkeyselect',"SELECT max(id) FROM gl WHERE reference = ?") unless $sth;
	return $sth;
}

# get the ledgersmb internal key id for an account
sub getAcctKey {
	my ($self,$account) = @_;

	my $sth = $self->_getAcctKeyStmt();
	$sth->bind_param(1,$account);
	$sth->execute();
	my $res = $sth->fetchrow_arrayref();
	$sth->finish();
	if($res == undef) {
		return $res;
	} else {
		return $res->[0];
	}
}

# get id of AP record given a tickno
sub getAPKey {
	my ($self,$tickno) = @_;

	my $gssth = $self->_getAPKeySelectStmt();
	$gssth->execute($tickno);
	my $lastval = $gssth->fetchrow_arrayref();
	if($lastval == undef) {
		return $lastval;
	} else {
		return $lastval->[0];
	}
}

sub insertAP {
	my ($self,$tickno,$tdate,$custkey,$amount,$op,$ordno,$comment) = @_;
#print "AP INSERT $tickno, $tdate, $custkey, $amount, $op, $ordno, $comment\n";
#print "WTF! $tickno, $tdate, $custkey, $amount, $op, $ordno, $comment\n";
	my $aristh = $self->_getAPInsertStmt();

	$aristh->bind_param(1,$tickno);
	$aristh->bind_param(2,$tdate);
	$aristh->bind_param(3,$custkey);
	$aristh->bind_param(4,$amount);
	$aristh->bind_param(5,$amount);
	$aristh->bind_param(6,$tdate);
	$aristh->bind_param(7,$op);
	$aristh->bind_param(8,'USD');
	$aristh->bind_param(9,$ordno);
	$aristh->bind_param(10,$comment);
	$aristh->execute();
#	return $self->getKey();
}

# given the mt4 login for a transaction, get the vendor id from ledgersmb
sub getCustID {
	my ($self,$login) = @_;

	my $custsth = $self->_getCustIDStmt();
	$custsth->bind_param(1,$login);
	$custsth->execute();
	my $foo = $custsth->fetchrow_arrayref();
	if($foo == undef) {
		return $foo;
	} else {
		return $foo->[0];
	}
}

# update an ap record identified by tickno. Sets paid = amount and sets the transaction date to $tdate
sub updateAP {
	my ($self,$tdate,$tickno) = @_;

	my $sth = $self->_getAPUpdateStmt();
	$sth->bind_param(1,$tdate);
	$sth->bind_param(2,$tdate);
	$sth->bind_param(3,$tickno);
	$sth->execute();
}

# get the AP record for a given ticket
sub getAP {
	my ($self,$tickno) = @_;

	my $sth = $self->_getAPStmt();
	$sth->bind_param(1,$tickno);
	$sth->execute();
	return $sth->fetchrow_hashref();
}

# insert a record into the chart of accounts
sub chartInsert {
	my ($self,$acctno,$desc) = @_;

	my $acctsth = $self->_getChartInsertStmt();

	$acctsth->bind_param(1,$acctno);
	$acctsth->bind_param(2,$desc);
	eval { $acctsth->execute(); };
}

# Get internal key id of the last inserted AP, AR, or GL record
sub getKey {
	my ($self) = @_;

	my $sth = $self->_getKeyStmt();
	return $sth->fetchrow_arrayref()->[0];
}

# insert a record into the general ledger table
sub glInsert {
	my ($self,$reference,$description,$notes,$transdate) = @_;

	my $glsth = $self->_getGLInsertStmt();
	$glsth->bind_param(1,$reference);
	$glsth->bind_param(2,$description);
	$glsth->bind_param(3,$notes);
	$glsth->bind_param(4,$transdate);
	$glsth->execute();
	return $self->getGLLastKey($reference);
}


# insert data into the vendors table
sub vendorInsert {
	my ($self,$name,$address,$city,$state,$zip,$country,$phone,$email,$vendno,$startdate) = @_;

	my $ledgsth = $self->_getVendorInsertStmt();

	$ledgsth->bind_param(1,$name);
	$ledgsth->bind_param(2,$address);
	$ledgsth->bind_param(3,$city);
	$ledgsth->bind_param(4,$state);
	$ledgsth->bind_param(5,$zip);
	$ledgsth->bind_param(6,$country);
	$ledgsth->bind_param(7,$phone);
	$ledgsth->bind_param(8,$email);
	$ledgsth->bind_param(9,$vendno);
	$ledgsth->bind_param(10,$startdate);
	eval { $ledgsth->execute(); };
}

# get the internal key id of the GL record containing the current unrealized for the given account
sub getGLCurrentUnrealizedKey {
	my ($self,$inacct) = @_;

	my $gssth = $self->_getGLCurrentUnrealizedStmt();
	$gssth->execute($inacct);
	my $res = $gssth->fetchrow_arrayref();
	if($res) {
		return $res->[0];
	} else {
		return undef;
	}
}

# get the current unrealized quantity from the associated ib current unrealized acct.
sub getGLCurrentUnrealized {
	my ($self,$glrkey) = @_;

	my $glcusth = $self->_getGLCurrentUnrealizedQtyStmt();
	$glcusth->execute($glrkey);
	my $res = $glcusth->fetchrow_arrayref();
	if($res) {
		return $res->[0];
	} else {
		return undef;
	}
}

# insert into acc_trans table
sub AcctTransInsert {
	my ($self,$lastval,$inacctkey,$oldpnl,$inacct,$notes,$date) = @_;
#print "AcctTransInsert $lastval, $inacctkey, $oldpnl, $inacct, $notes, $date\n";
	my $atisth = $self->_getAcctTransInsertStmt();
	$atisth->bind_param(1,$lastval);
	$atisth->bind_param(2,$inacctkey);
	$atisth->bind_param(3,$oldpnl);
	$atisth->bind_param(4,$inacct);
	$atisth->bind_param(5,$notes);
	$atisth->bind_param(6,$date);
	$atisth->execute();
}

# mark an unrealized gl record as having been reversed
sub markGLReversed {
	my ($self,$glrkey) = @_;

	my $glrsth = $self->_getGLReversedStmt();
	$glrsth->execute($glrkey);
}

# get the internal keyid of the last entry to a given account in the general ledger
# I'm not sure how safe this is, but its the only game in town...
sub getGLLastKey {
	my ($self,$acctno) = @_;

	my $sth = $self->_getGLLastKeyStmt();
	$sth->execute($acctno);
	return $sth->fetchrow_arrayref()->[0];
}

# get the chart id of a gl account with the given account number
sub getChartId {
	my ($self,$accno) = @_;
 print "ACCNO IS: $accno\n";
	my $sth = $self->_getChartIdStmt();
#cluck "var is (\$sth)";
	$sth->execute($accno);
	return $sth->fetchrow_arrayref()->[0];
	#print  $sth;
}

# add a customer to ledgersmb as a vendor
sub doAddVendor {
	my ($self,$cust) = @_;
#print ("prefix should be $self->{'prefix'}\n");
	$self->vendorInsert(substr($cust->{'NAME'},0,64),
		substr($cust->{'ADDRESS'},0,32),
		substr($cust->{'CITY'},0,32),
		substr($cust->{'STATE'},0,32),
		substr($cust->{'ZIPCODE'},0,10),
		substr($cust->{'COUNTRY'},0,32),
		substr($cust->{'PHONE'},0,20),
		$cust->{'EMAIL'},
		$cust->{'LOGIN'},
		$cust->{'REGDATE'});

	die("No vendor prefix!") unless defined($self->{'prefix'});
	my $prefix = $self->{'prefix'};
	my $acctno = $prefix."-".$cust->{'LOGIN'}; #TODO figure out how this works
	my $desc = $cust->{'LOGIN'} . " - " . $cust->{'NAME'};
	eval { $self->chartInsert($acctno,$desc); }; # ignore dup keys, its OK, customer already exists
}

# post a trade
sub doPostTrade {
	my ($self,$trans,$inacct,$outacct) = @_;
	 
   # print Dumper($outacct);	
	#cluck "var is (\$trans)";

	my $amount = $trans->{'PROFIT'} + $trans->{'SWAPS'} + $trans->{'COMMISSION'};
	my $existing = $self->getAP($trans->{'TICKET'});
	if($existing && $trans->{'CLOSE_TIME'} ne '1970-01-01 00:00:00') { # exists and is not an open position
		$self->updateAP($trans->{'CLOSE_TIME'},$trans->{'TICKET'}); # mark it as paid
	} else { # is an open position or doesn't exist at all, so insert transaction
		my $custkey = $self->getCustID($trans->{'LOGIN'});
#print("Tried to find stinking custkey for ".$trans->{'LOGIN'}." and got $custkey\n");
#		die "Failed to find customer id for account ".$trans->{'LOGIN'} if $custkey == undef;
		if($custkey != undef) { # missing customer key probably means its a subaccount, so ignore this trade.
			$self->insertAP ($trans->{'TICKET'},
				$trans->{'CLOSE_TIME'},
				$custkey,
				$amount,
				$trans->{'OPERATION'},
				undef,
				$trans->{'COMMENT'});
#print "GOT PAST FUCKING insertAP\n";	
			my $lastval = $self->getAPKey($trans->{'TICKET'});
			my $inacctkey = $self->getAcctKey($inacct);
			$self->AcctTransInsert($lastval,$inacctkey,$amount * -1,
				$trans->{'TICKET'},$trans->{'OPERATION'},$trans->{'CLOSE_TIME'});
#print "DID THE FIRST HALF\n";	
			my $outacctkey = $self->getAcctKey($outacct.$trans->{'LOGIN'});
#print "THE OUTACCTKEY IS $outacctkey\n";
			$self->AcctTransInsert($lastval,$outacctkey,$amount,
				$trans->{'TICKET'},$trans->{'OPERATION'},$trans->{'CLOSE_TIME'});
		}
	}
}


# make a post to the given general ledger account for the given amount as of the given date
sub doPostGL {
	my ($self,$acctno,$amount,$date,$desc,$notes) = @_;
#print "POSTING $amount to $acctno\n";
	my $chartid = $self->getChartId($acctno);
	# insert record into GL
#	$self->glInsert($acctno,$desc,$notes,$date);
#	my $glrkey = $self->getGLLastKey($acctno);
	my $glrkey = $self->glInsert($acctno,$desc,$notes,$date);
	# insert record into acc_trans
	$self->AcctTransInsert($glrkey,$chartid,$amount,$acctno,$desc,$date);
	return $glrkey;
}

sub doPostGLTrans {
	my ($self,$inacctno,$outacctno,$amount,$date,$desc,$notes) = @_;

	my $glrkey = $self->doPostGL($inacctno,$amount,$date,$desc,$notes);
	my $chartid = $self->getChartId($outacctno);
	$self->AcctTransInsert($glrkey,$chartid,$amount*-1,$outacctno,$desc,$date);
}

# make a post to the given AP account for the given amount as of the given date
sub doPostAP {
	my ($self,$acctno,$amount,$date,$desc,$notes) = @_;
#print "POSTING $amount to $acctno\n";
	my $chartid = $self->getChartId($acctno);
	# insert record into GL
#print "GOT THE FUCK HERE 1\n";
	$self->glInsert($acctno,$desc,$notes,$date);
#print "GOT THE FUCK HERE 2\n";
	my $glrkey = $self->getGLLastKey($acctno);
#print "GOT THE FUCK HERE 3\n";

#print("WHAT THE FUCK IS GOING INTO ACC_TRANS $glrkey, $chartid, $amount, $acctno, $desc, $date\n");
	$self->AcctTransInsert($glrkey,$chartid,$amount,$acctno,$desc,$date);
}

# reverse the previous unrealized and insert a new one, this transaction is dated by the given date.
sub doGLAcctUnrealized2 {
	my ($self,$inacct,$newunrealized,$date) = @_;
#print "I am not used";
	my $chartid = $self->getChartId($inacct);
	# get the existing current unrealized key so we can reverse it.
	my $glrkey = $self->getGLCurrentUnrealizedKey($inacct);
	if($glrkey) {
		my $oldpnl = $self->getGLCurrentUnrealized($glrkey);
		$self->markGLReversed($glrkey); # change comment on current record so it now shows reversed
		$self->glInsert($inacct,"REVERSE","UNREALIZED P&L",$date); # insert reversing record
		$self->AcctTransInsert($glrkey,$chartid,$oldpnl * -1,$inacct,"REVERSE",$date);
	}
	# now add a new record for current unrealized
	# insert record into GL
	$self->glInsert($inacct,"CURRENT","UNREALIZED P&L",$date);
	$glrkey = $self->getGLCurrentUnrealizedKey($inacct);
	# insert record into acc_trans
	$self->AcctTransInsert($glrkey,$chartid,$newunrealized,$inacct,"CURRENT",$date);
}

# update the unrealized P&L of the GL account with the given account id
sub doGLAcctUnrealized {
	my ($self,$inacct,$newunrealized) = @_;
	# cluck "var is (\$newunrealized)";
#print "Doing unrealized for account $inacct\n";
	my $chartid = $self->getChartId($inacct);
#print "Chart ID is $chartid\n";
	# get the existing current unrealized key so we can reverse it.
	my $glrkey = $self->getGLCurrentUnrealizedKey($inacct);
#print "GLRKEY is $glrkey\n";
	if($glrkey) {

		my $oldpnl = $self->getGLCurrentUnrealized($glrkey);
#print "OLDPNL IS $oldpnl\n";
		$self->markGLReversed($glrkey); # change comment on current record so it now shows reversed

		$self->glInsert($inacct,"REVERSE","UNREALIZED P&L",$self->{end}); # insert reversing record
		$self->AcctTransInsert($glrkey,$chartid,$oldpnl * -1,$inacct,"REVERSE",$self->{end});
		
	}

	# now add a new record for current unrealized
	# insert record into GL
	$self->glInsert($inacct,"CURRENT","UNREALIZED P&L",$self->{end});
	$glrkey = $self->getGLCurrentUnrealizedKey($inacct);
#print "NEW GLRKEY IS $glrkey\n";
	# insert record into acc_trans
	$self->AcctTransInsert($glrkey,$chartid,$newunrealized,$inacct,"CURRENT",$self->{end});
#	$self->acctTransInsert($glrkey
}

1;
