{
$CRET_SUB = "1001";
$CRET_MSTR_BC = "1002";
$CRET_SUB_BC = "1003";
$CRET_MSTR = "1004";
$DEBT_MSTR_SUB = "1401";
$DEBT_MSTR_BC = "1402";
$DEBT_SUB_BC = "1403";

$GEN_PAY = "2001";
$DIR_PAY = "2002";
$BO_PAY = "2003";
$GEN_SAL_PAY = "2004";
$FAC_SAL_PAY = "2005";
$HRL_SAL_PAY = "2006";
$REQ_PAY = "2007";

$FAC_SAL_ENC = "6001";
$LIM_SAL_ENC = "6002";
$ACD_SAL_ENC = "6003";
$EIT_SAL_ENC = "6004";
$STU_AST_ENC = "6005";
$STU_HLP_ENC = "6006";
$CLS_PRM_ENC = "6007";
$CLS_LTE_ENC = "6008";
$CLS_PRJ_ENC = "6009";
$SAL_OTH_ENC = "6010";
$GEN_REQ_ENC = "6011";
$GEN_ENC     = "6012";

#connect to database
my $dbh = DBI->connect("DBI:mysql:database=$DB;host=www.geology.wisc.edu",
		      $USER, $PASS, {'RaiseError' => 1});

%TTAB = { } ;
%budtab = { } ;
%cctab = { } ;

#calculate datecode
$datecode = &date_code();
print "$datecode \n";

# fill transaction type info
#&ttable($dbh);
&cctable($dbh);

while (($v, $value) = each %cctab) { 
#   print $v, '=',  $value, "\n";
 }

while (($v, $value) = each %TTAB)  {
#   print $v, '=',  $value, "\n";
          }
print "\n" ;


# Prepare account insert statement
$act_h = $dbh->prepare("INSERT INTO 
			account(fiscal_year, fund_account, udds, sub_account, sub_sub_account, 
			budget_cat, account_name, active, budget, created, end_date, class_range, 
			building_no, owner, operator_num, mod_date) 
			VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

# Prepare ledger insert statement

$ledg_h = $dbh->prepare("INSERT INTO 
			 ledger(fiscal_year, account_seq, trans_table, table_seq, trans_seq, 
			 activity_date, activity, description, class, transaction_type, 
			 payment, enc, enc_date, paid_date, invoice_num, voucher, reconcile, 
			 operator_code, mod_date) 
			 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )");

$ledg_enc_h = $dbh->prepare("INSERT INTO 
			 ledger(fiscal_year, account_seq, trans_table, table_seq, trans_seq, 
			 activity_date, activity, description, class, transaction_type, 
			 enc, enc_date, invoice_num, voucher, reconcile, 
			 operator_code, mod_date) 
			 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )");

$ledg_pay_h = $dbh->prepare("INSERT INTO 
			 ledger(fiscal_year, account_seq, trans_table, table_seq, trans_seq, 
			 activity_date, activity, description, class, transaction_type, 
			 payment, paid_date, invoice_num, voucher, reconcile, 
			 operator_code, mod_date) 
			 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )");

# Prepare ledgcode insert statement
$ledgcd_ih= $dbh->prepare("INSERT INTO 
			   ledgcode(ledgcode, subcode, account_seq, operator_num, mod_date) 
			   VALUES (?,?,?,?,?)");

# Prepare ledgcode select statement
$ledgcd_sh = $dbh->prepare("SELECT account_seq from ledgcode WHERE ledgcode = ? and subcode = ?") ;

# Prepare class code descript select statement
$cc_sh = $dbh->prepare("SELECT class_name from class2sfs WHERE legacy_class = ?") ;

# Prepare req insert statement
$req_ih = $dbh->prepare("INSERT INTO 
			 requisition(requisition_num, requisition_date, trans_seq, req_type,
			 fiscal_year, who_for, deliver_to, delivery_add1, delivery_add2,
			 delivery_add3, call, bo_start, bo_end, fob, terms, delivery,
			 reference, bid_no, waiver, bulletin, recorded, prev_po, operator_code,
			 mod_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

# Prepare req select statement
$req_sh = $dbh->prepare("SELECT requisition_seq from requisition where requisition_num = ?
			 ORDER by mod_date DESC ");

# Prepare vendor insert statement
$vend_ih = $dbh->prepare("INSERT INTO 
			  vendors(vendor, vendor_code, vendor_add1, vendor_add2, 
			  vendor_add3, ven_default, operator_code, mod_date)
	                  VALUES (?,?,?,?,?,?,?,?)");


# Prepare vendor select statement
$vend_sh = $dbh->prepare("SELECT vendor_num from vendors 
			  WHERE  vendor_code = ? and vendor = ? and vendor_add1 = ? and 
				 vendor_add2 = ? and vendor_add3 = ? 
			  ORDER BY mod_date DESC ");


# Prepare vendor_used insert statement
$vend_used_ih = $dbh->prepare("INSERT INTO 
			  vendor_used(requisition_seq, vendor_num, operator_num, mod_date)
	                  VALUES (?,?,?,?)");

$trans_seq_sh = $dbh->prepare("SELECT trans_seq from requisition where requisition_num = ? 
			  ORDER by mod_date DESC "); 



					       
}


sub insert_ledger 
{
my ($dbh, $act_h, $ledg_h, $ledgcd_ih, $ledgcd_sh, $rdate, $fy, $ledgcode, $subcode, 
    $reqnum, $act, $class, $vendor, $reqamt, $enc, $pd, $pdamt, $descript) = @_ ;

if (($account_seq = &if_ledg_acct_exists($dbh, $ledgcode, $subcode)))  {
     if ($reqnum eq "") {
         $trans_seq = &new_trans_seq($dbh);
         print "New trans #: $trans_seq\n ";
	 $xdate = $rdate/1000 ;
         if ($xdate >= 2000) { $fy = $fy + 2000 };
         if ((1990 <= $xdate) and ($xdate <= 1999 )) { $fy = $fy + 1990 };
         if ((1980 <= $xdate) and ($xdate <= 1989 )) { $fy = $fy + 1980 }; 
$account_seq ne '' ? $account_seq : 0 ;
	 $enc_flag = ($descript =~ m/^enc/i ? 1:0) ;
         print "FY: $fy  Enc_flag: $enc_flag \n";
         if ($enc_flag == 0) {
	    $desc = 'Zim Payment' ;
	    $desc = 'Zim Salary payment:VDR' if $class =~ m/1017|1019|1030|1041|1061|1411/ ;

PAY:{       $trans_code = $FAC_SAL_PAY, last PAY if $class =~ m/= 1010|1111|1311|1411|1511/ ;
            $trans_code = $GEN_SAL_PAY, last PAY if $class =~ m/= 1019|1119|1319|1419|1519|
                                                                  1017|1117|1317|1417|1517|
                                                                  1061|1361|1461|
							          1171|1371|1471|1571/ ;
	    $trans_code = $HRL_SAL_PAY, last PAY if $class =~ m/= 1030|1031|1060|1130/ ; 
	    $trans_code = $GEN_PAY;
	    $nothing = 0 ;
	    }
	    $descript = join(':', $desc, $vendor, $cctab{$class},$descript) ;
	    $x = pd_date_cor($pd, $fy) ;
	    print "X: $x \n" ;

            $ledg_h->execute($fy, $account_seq, "", $account_seq, $trans_seq, $rdate, $act, $descript, 
			  $class, $trans_code, $pdamt, $enc_amt, $enc_date, $rdate, $invoice_num, $voucher, $reconcile,
			  $ZIM_OPCODE, $datecode) ;
            }
	  else {
	    $desc = 'Zim Encumber' ;
ENC: {
	    $trans_code = $FAC_SAL_ENC, last ENC  if $class =~ m/1010|1111|1311|1411|1511/ ;
	    $trans_code = $ACD_SAL_ENC, last ENC  if $class =~ m/1019|1119|1319|1419|1519|
					  			 1017|1117|1317|1417|1517|
								 1061|1361|1461|
								 1171|1371|1471|1571/ ;
	    $trans_code = $STU_HLP_ENC, last ENC  if $class =~ m/1030|1031|1060|1130/ ; 
	    $trans_code = $CLS_LTE_ENC, last ENC  if $class =~ m/1522/ ;
	    $trans_code = $CLS_PRJ_ENC, last ENC  if $class =~ m/1526/ ;
	    $trans_code = $SAL_OTH_ENC, last ENC  if $class =~ m/1800|1801|1802|1803|1900|3232|5712/ ;
	    $trans_code = $GEN_ENC;
	    $nothing  = 0;
	    }
	    $descript = join(':', $desc, $vendor, $cctab{$class},$descript) ;
	    $x = pd_date_cor($paid_date, $fy) ;
	    print "X: $x \n" ;
            $ledg_h->execute($fy, $account_seq, "", $account_seq, $trans_seq, $rdate, $act, $descript, 
			  $class, $trans_code, "", $pdamt, $rdate, "", $invoice_num, $voucher, "",
			  $ZIM_OPCODE, $datecode) ;
            }
	    }

#enc trans_code
#6001	Faculty Salary		1010, 1111, 1311, 1411, 1511,
#6003	Academic Salary		1019, 1119, 1319, 1419, 1519	
#6003	Research Assoc		1017, 1117, 1317, 1417, 1517
#6003	Research Assist		1061, 1361, 1461
#6003	Teaching Assist		1171, 1371, 1471, 1571
#6005	Student Assist
#6006	Student Hourly		1030, 1031, 1060, 1130, 
#6007	Classified Perm		
#6008	Classified LTE 		1522
#6009	Classified Prj		1526
#6010	Sal, Other		1800, 1801, 1802, 1803, 1900, 3232, 5712
#6011	Gen Requisition		
#6012	Gen Encumbrance		3238

}
}



sub create_accounts
{
my ($dbh, $sth, $sth1, $sth2, $ledgcode, $budgid, $period, $subcode, $ledgname, $owner, $budget, $salary, 
 $fees, $fringe, $capital, $supplies, $travel, $pubs, $salescr, $overhead,
$tdesc_create_mstr, $tdesc_create_sub, $tdesc_create_mstr_bc, $tdesc_create_sub_bc,
$tdesc_debt_mstr_sub, $tdesc_debt_mstr_bc, $tdesc_debt_sub_bc) = @_;
$start_date =""; $end_date ="" ;
($start_date, $end_date) = &getperiod($period) ;
print ("Start:", $start_date,"\tEnd:", $end_date, "\n") ;

## Lock the database
my $sth_lock = $dbh->do("LOCK TABLES account WRITE, ledger WRITE, transaction_seq WRITE, ledgcode WRITE");

$trans_seq = &new_trans_seq($dbh);
print "New trans #: $trans_seq\n ";

print "FY:", $fy,"LC:", $ledgcode, "UDDS:", $UDDS,"LNM:",  $ledgname, "OWR:", $subledg, "\n";


# Check for main account, create if not present
if (! if_ledg_acct_exists ($dbh, $ledgcode, "")) {
   $sub_account = 0;

# create master account
   $sth->execute($fy, $ledgcode, $UDDS, $sub_account, "0", "0", $ledgname, "Y", $budget, $start_date, $end_date, "", "$BLDG", $owner, $ZIM_OPCODE, $datecode);

   $account_seq = $dbh->{'mysql_insertid'};
   print ("Account_seq:", $account_seq, "\n");

# Create ledgcode-subledg <-> account_seq  entry
   $sth2->execute($ledgcode, $subcode, $account_seq, $ZIM_OPCODE, $datecode);

# Create ledger entry
   $sth1->execute($fy, $account_seq, $ACCOUNT, $account_seq, $trans_seq, $start_date, "0", $TTAB{$CRET_MSTR}, "0", $CRET_MSTR, -$budget, $datecode, $ZIM_OPCODE, $datecode);

   $lline = $dbh->{'mysql_insertid'};
   print ("Master account ledger line:", $lline, "\n");
} 


# Check for subcode, and create sub account if not present 
if ($subcode ne "") { 
   print ("Start Sub account subcode: $subcode \n");
   
if (! if_ledg_acct_exists ($dbh, $ledgcode, $subcode)) {
   print ("Executing Sub account \n");

   $sub_account = get_new_sub_account($dbh,$ledgcode);

   $sth->execute( $fy, $ledgcode, $UDDS, $sub_account, "0", "0", $ledgname, "Y", $budget, $start_date, $end_date, "", "$BLDG", $owner, $ZIM_OPCODE, $datecode);

   $account_seq = $dbh->{'mysql_insertid'};
   print ("Account_seq:", $account_seq, "\n");

# Create ledgcode-subledg <-> account_seq  entry
   $sth2->execute($ledgcode, $subcode, $account_seq, $ZIM_OPCODE, $datecode);

# Create ledger entries
   $sth1->execute($fy, $account_seq, $ACCOUNT, $account_seq, $trans_seq, $start_date, "0", $TTAB{$CRET_SUB}, 
                  "0", $CRET_SUB, -$budget, $datecode, $ZIM_OPCODE, $datecode);

   $sth1->execute($fy, $account_seq, $ACCOUNT, $account_seq, $trans_seq, $start_date, "0", $TTAB{$DEBT_MSTR_SUB}, 
                  "0", $DEBT_MSTR_SUB, $budget, $datecode, $ZIM_OPCODE, $datecode);
   $lline = $dbh->{'mysql_insertid'};
   print ("ledger line:", $lline, "\n");
} 
}

print "Budget ID: $budgid \n";
# Itemized Budget categories
if ($budgid eq 'B') {

   foreach $i ('Salary', 'Fees', 'Fringe', 'Capital', 'Supplies','Travel','Pubs','Sales_credit', 'Overhead') {
         $sth->execute( $fy, $ledgcode, $UDDS, $sub_account, "0", $i, $ledgname, "Y", $budtab{$i}, $start_date, 
                        $end_date, "", "$BLDG", $owner, $ZIM_OPCODE, $datecode);
         $account_seq = $dbh->{'mysql_insertid'};
         $trans_seq = &new_trans_seq($dbh);
         #print "New trans #: $trans_seq\n ";
         #print ("Account_seq:", $account_seq, "\n");

# Create ledger entries
         if ($sub_account == 0) 
               {$tcode1 = $CRET_MSTR_BC; $tdesc1 = $TTAB{$CRET_MSTR_BC}; 
		$tcode2 = $DEBT_MSTR_BC; $tdesc2 = $TTAB{$DEBT_MSTR_BC}; 
         }

         else  {$tcode1 = $CRET_SUB_BC; $tdesc1 = $TTAB{$CRET_SUB_BC}; 
		$tcode2 = $DEBT_SUB_BC; $tdesc2 = $TTAB{$DEBT_SUB_BC}; 
         }

         $sth1->execute($fy, $account_seq, $ACCOUNT, $account_seq, $trans_seq, $start_date, "0", 
                        $tdesc1, "0", $tcode1, -$budtab{$i}, $datecode, 
                        $ZIM_OPCODE, $datecode);
         $sth1->execute($fy, $account_seq, $ACCOUNT, $account_seq, $trans_seq, $start_date, "0", 
                        $tdesc2, "0", $tcode2, $budtab{$i}, $datecode, 
                        $ZIM_OPCODE, $datecode);
  

       }
  }

}



sub getperiod
{
my ($period) = @_ ;
if ($period == "") {
  $start_date = "19000101" ;
  $end_date = "29991231"  ;
  return ($start_date, $end_date);
}
   
# check $period for  short yy-yy or "to" variety
my $len = length($period) ;
my $t = substr($period, 2, 1) ;
if (($len == 5) && (substr($period, 2, 1) eq '-' )) {
   ($start, $end) = split(/-/,$period);
   }
else {
   ($start, $end) = split(/to/,$period);
}
foreach ($start, $end) {
#  change / to -  
   s/\//-/g;
}
my($sm, $sd, $sy) = split(/-/,$start);
my($em, $ed, $ey) = split(/-/,$end);
foreach ($sm, $sd, $sy, $em, $ed, $ey) {
    s/^\s+//;
    s/\s+$//;
}  

# date corections and fiscal year
my ($m1, $d1, $y1) = &datecorrect($sm, $sd, $sy, "S") if ($start ne "") ;
$fy = "" ;
$fy = &fiscalyear($m1, $d1, $y1) if ($start ne "") ;
my ($m2, $d2, $y2) = &datecorrect($em, $ed, $ey, "E") if ($end ne "" ); 
my $start_date = sprintf("%04d%02d%02d",$y1, $m1, $d1);
my $end_date = sprintf("%04d%02d%02d",$y2, $m2, $d2);
($start_date, $end_date);
}
 
sub leap_year {
my ($yr) = @_ ;
if ($yr % 100 == 0 ) {
   return (1) if ($yr % 4 == 0) ;
   return (0) ;
   }
return (1) if ($yr %4 == 0) ;
return (0) ;
}

sub datecorrect
{
my ($mm, $dd, $yy, $Start_or_End) = @_ ;
 return if ($mm eq "") ;
if ($Start_or_End eq "S") {
 if ($dd eq "") {
   $yy = $mm ; 
   $dd = '01'; 
   $mm = '07' ; 
 }
 else {
 if ($yy eq "") {
   $yy = $mm ; 
   $mm = $dd ; 
   $dd = '01' ;
 }
}
}

else {
 if ($dd eq "") {
   $yy = $mm ; 
   $dd = '30'; 
   $mm = '06' ; 
 }

 else {
 if ($yy eq "") {
   $yy = $mm ; 
   $mm = $dd ; 
   $dd = '30' ;
 }
}
}

if ($yy > 32) {
    $yy = 1900 + $yy ;
  }
else {
    $yy = 2000 + $yy ;
}

($mm, $dd, $yy);
}


sub pd_date_cor {
my ($pd, $fy) = @_ ;
print "pd_date_cor $pd: $fy \n" if $DEBUG;
my $pd1  = ($fy * 10000) + ($pd * 100) ;
my $paid_date = $pd1 + 31 ;
$paid_date = $pd1 + 30 if ($pd =~ m/4|6|9|11/);
if ($pd =~ m/2/ ) { 
    $paid_date = 28 ;
    $paid_date = $paid_date + 1 if (leap_year($fy)) ;
    }
($paid_date) ;
}



sub fiscalyear
{
my ($sm, $sd, $sy) = @_;
if ($sm > 6) {$sy = $sy + 1 };
$sy;
}

sub cctable
{
($dbh) = @_;

# fill classcode table for future use.
my $a = $dbh->prepare("SELECT * from class2sfs order by class_num"); 
          $a->execute();
	while (($row = $a->fetchrow_hashref) > 0 ) {
	  $cctab{$row->{'legacy_class'}} = $row->{'class_name'};
          }

$a->finish();

}
#$CRET_SUB = "1001"; $CRET_MSTR_BC = "1002";
#$CRET_SUB_BC = "1003"; $CRET_MSTR = "1004";
#$DEBT_MSTR_SUB = "1401"; $DEBT_MSTR_BC = "1402";
#$DEBT_SUB_BC = "1403";
#
#$GEN_PAY = "2001"; $DIR_PAY = "2002";
#$BO_PAY = "2003"; $GEN_SAL_PAY = "2004";
#$FAC_SAL_PAY = "2005"; $HRL_SAL_PAY = "2006";
#$REQ_PAY = "2007";

sub ttable
{
($dbh) = @_;

# get transaction descriptions from transaction table
my $a = $dbh->prepare("SELECT * from trans_types where trans_code = ?"); 

foreach $tcode ($CRET_SUB, $CRET_MSTR_BC, $CRET_SUB_BC, $CRET_MSTR, $DEBT_MSTR_SUB,
          $DEBT_MSTR_BC, $DEBT_SUB_BC, $GEN_PAY, $DIR_PAY, $BO_PAY, $GEN_SAL_PAY, 
          $FAC_SAL_PAY, $HRL_SAL_PAY, $REQ_PAY,
	  $FAC_SAL_ENC, $LIM_SAL_ENC, $ACD_SAL_ENC, $EIT_SAL_ENC,
          $STU_AST_ENC, $STU_HLP_ENC, $CLS_PRM_ENC, $CLS_LTE_ENC,
          $CLS_PRJ_ENC, $SAL_OTH_ENC, $GEN_REQ_ENC, $GEN_ENC   ) {

          $a->execute($tcode);
	  my $row = $a->fetchrow_hashref;
	  $tdesc = $row->{'trans_desc'};
	  $TTAB{$tcode} = $tdesc;
          }

$a->finish();

}

# date
sub date_code
{
my ($sec,$min,$hour,$mday, $mon, $year, $wday, $yday,$isdst) = localtime(time);
$year = $year + 1900 ;
$mon = $mon + 1;
my $datecode = sprintf("%04d%02d%02d",$year, $mon, $mday);
return ($datecode);
}

# get new transaction_seq number
sub new_trans_seq
{
my ($dbh) = @_;
my $ts = $dbh->do("INSERT INTO transaction_seq(trans_seq) VALUES('')");
my $t_seq = $dbh->{'mysql_insertid'};
$ts = $dbh->do("DELETE from transaction_seq where trans_seq <> $t_seq");
$t_seq;
}

sub if_acct_exists 
{
my ($dbh, $fy, $udds, $fund_acct, $sub_account, $sub_sub_account, $budget_cat) = @_ ;

print ("If Exists:",$dhb, $fy,"-",$udds, "-", $fund_acct,"-", $sub_account, "-", $sub_sub_account,"-", $budget_cat,"\n");
my $q1 = $dbh->prepare("SELECT account_seq from account 
		   where fiscal_year = '$fy' and udds = '$udds'
		   and   fund_account = '$fund_acct' and sub_account = '$sub_account'
		   and   sub_sub_account = '$sub_sub_account' and budget_cat = '$budget_cat'"); 
$q1->execute();
($account_seq) = $q1->fetchrow_array ;
print ("Rows:  $account_seq \n") if $DEBUG ;
$account_seq ne '' ? $account_seq : 0 ;

}

sub if_ledg_acct_exists 
{
my ($dbh, $ledgcode, $subcode) = @_ ;
my $q1 = $dbh->prepare("SELECT account_seq from ledgcode 
		   where ledgcode = '$ledgcode' and subcode = '$subcode'");
$q1->execute();
($account_seq) = $q1->fetchrow_array ;
print ("if ledg_account Rows:  $account_seq \n") if $DEBUG;
$account_seq ne '' ? $account_seq : 0 ;
}

sub sub_accounts {
my ($dbh, $account_name) = @_ ;
} 


sub get_new_sub_account {
my ($dbh, $ledgcode) = @_ ; 
my $q1 = $dbh->prepare("SELECT sub_account FROM account 
                        WHERE fund_account = '$ledgcode' 
                        ORDER BY sub_account DESC LIMIT 1");
$q1->execute();
my ($sub_account) = $q1->fetchrow_array ;
$sub_account++;
print "new sub_account: $sub_account \n";
$sub_account;

}

sub is_sub_account {
my ($dbh, $account_seq) = @_ ;
my $q1 = $dbh->prepare("SELECT sub_acccount FROM account
			WHERE account_seq = '$account_seq'");
$q1->execute();
my ($sub_account) = $q1->fetchrow_array ;
$sub_account ne '' ? $sub_account : 0 ;
}

sub is_sub_sub_account {
my ($dbh, $account_seq) = @_ ;
my $q1 = $dbh->prepare("SELECT sub_sub_acccount FROM account
			WHERE account_seq = '$account_seq'");
$q1->execute();
my ($sub_sub_account) = $q1->fetchrow_array ;
$sub_sub_account ne '' ? $sub_sub_account : 0 ;
}

sub is_budg_class {
my ($dbh, $account_seq) = @_ ;
my $q1 = $dbh->prepare("SELECT budget_cat FROM account
			WHERE account_seq = '$account_seq'");
$q1->execute();
my ($budget_cat) = $q1->fetchrow_array ;
$budget_cat ne '' ? $budget_cat : 0 ;
}
