#=====================================================================
# SQL-Ledger ERP
# Copyright (C) 2006
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.com
#
#======================================================================
#
# backend code for human resources and payroll
#
#======================================================================

package HR;


sub get_employee {
  my ($self, $myconfig, $form, $nolock) = @_;

  my $dbh = $form->dbconnect($myconfig);

  $form->remove_locks($myconfig, $dbh, 'hr') unless $nolock;

  my $query;
  my $sth;
  my $ref;
  my $notid = "";
  my $rne;
  
  my %defaults = $form->get_defaults($dbh, \@{['company', 'address', 'tel', 'fax', 'businessnumber', 'precision', 'referenceurl']});
  for (keys %defaults) { $form->{$_} = $defaults{$_} }

  %defaults = $form->get_defaults($dbh, \@{['printer_%']});
  
  my $label;
  my $command;
  for (keys %defaults) {
    if ($_ =~ /printer_/) {
      ($label, $command) = split /=/, $defaults{$_};
      $form->{"${label}_printer"} = $command;
    }
  }

  if ($form->{id}) {
    $query = qq|SELECT e.*,
                ad.id AS addressid, ad.address1, ad.address2, ad.city,
		ad.state, ad.zipcode, ad.country,
		bk.name AS bankname, bk.iban, bk.bic,
		ad1.address1 AS bankaddress1,
		ad1.address2 AS bankaddress2,
		ad1.city AS bankcity,
		ad1.state AS bankstate,
		ad1.zipcode AS bankzipcode,
		ad1.country AS bankcountry,
		c1.accno AS ap, c1.description AS ap_description,
		tr1.description AS ap_translation,
		c2.accno AS payment, c2.description AS payment_description,
		tr2.description AS payment_translation,
		pm.description AS paymentmethod,
		r.description AS acsrole
                FROM employee e
		JOIN address ad ON (e.id = ad.trans_id)
		LEFT JOIN acsrole r ON (r.id = e.acsrole_id)
		LEFT JOIN bank bk ON (bk.id = e.id)
		LEFT JOIN address ad1 ON (bk.address_id = ad1.id)
		LEFT JOIN chart c1 ON (c1.id = e.apid)
		LEFT JOIN chart c2 ON (c2.id = e.paymentid)
		LEFT JOIN translation tr1 ON (tr1.trans_id = c1.id AND tr1.language_code = '$myconfig->{countrycode}')
		LEFT JOIN translation tr2 ON (tr2.trans_id = c2.id AND tr2.language_code = '$myconfig->{countrycode}')
		LEFT JOIN paymentmethod pm ON (pm.id = e.paymentmethod_id)
                WHERE e.id = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);
  
    $ref = $sth->fetchrow_hashref(NAME_lc);
    $ref->{employeelogin} = $ref->{login};
    for (qw(ap payment)) { $ref->{"${_}_description"} = $ref->{"${_}_translation"} if $ref->{"${_}_translation"} }
    for (qw(login ap_translation payment_translation)) { delete $ref->{$_} }
    for (keys %$ref) { $form->{$_} = $ref->{$_} }

    $sth->finish;
  
    # check if employee can be deleted, orphaned
    $form->{status} = "";
    if (!$form->{employeelogin}) {
      $query = qq|SELECT count(*) FROM ap
                  WHERE vendor_id = $form->{id}|;
      if (! $dbh->selectrow_array($query)) {
	$form->{status} = "orphaned";
      }
		  
      if (! $form->{status}) {
	$query = qq|SELECT count(*) FROM jcitems
		    WHERE employee_id = $form->{id}|;
	if (! $dbh->selectrow_array($query)) {
	  $form->{status} = "orphaned";
	}
      }
    }

   
    $query = qq|SELECT *
		FROM payrate
		WHERE trans_id = $form->{id}
		ORDER BY id|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      push @{ $form->{all_payrate} }, $ref;
    }
    $sth->finish;
    
    # get wage links
    $query = qq|SELECT w.*
		FROM employeewage ew
		JOIN wage w ON (ew.wage_id = w.id)
		WHERE ew.employee_id = $form->{id}
		ORDER BY ew.id|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      push @{ $form->{all_employeewage} }, $ref;
    }
    $sth->finish;

    # get deductions
    $query = qq|SELECT d.id, d.description, ed.exempt, ed.maximum,
                ed.id AS edid
		FROM employeededuction ed
		JOIN deduction d ON (ed.deduction_id = d.id)
		WHERE ed.employee_id = $form->{id}
		ORDER BY ed.id|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      push @{ $form->{all_employeededuction} }, $ref;
    }
    $sth->finish;

    $query = qq|SELECT d.id, da.trans_id, da.withholding, da.percent
		FROM deduct da
		JOIN deduction d ON (da.deduction_id = d.id)|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    my %deduct;
    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      $ref->{percent} ||= 1;
      push @{ $form->{deduct}{$ref->{trans_id}} }, $ref;
      $deduct{$ref->{id}} = 1;
    }
    $sth->finish;

    # reorder to calculate dependencies
    for $ref (@{ $form->{all_employeededuction} }) {
      if ($deduct{$ref->{id}}) {
	push @{$deduct{a}}, $ref;
      } else {
	push @{$deduct{b}}, $ref;
      }
    }
    @{ $form->{all_employeededuction} } = ();
    push @{ $form->{all_employeededuction} }, @{$deduct{a}};
    push @{ $form->{all_employeededuction} }, @{$deduct{b}};

    $notid = qq|AND id != $form->{id}|;

    # retrieve totals
    my $cd;
    if ($form->{transdate}) {
      $cd = substr($form->datetonum($myconfig, $form->{transdate}),0,4);
    } else {
      $query = qq|SELECT to_char(current_date, 'YYYY')|;
      ($cd) = $dbh->selectrow_array($query);
    }
    
    $query = qq|SELECT SUM(pt.amount)
		FROM pay_trans pt
		JOIN ap a ON (a.id = pt.trans_id)
		WHERE a.vendor_id = $form->{id}
		AND a.transdate >= '${cd}0101'
		AND a.transdate <= '${cd}1231'
		AND pt.id = ?|;
    $sth = $dbh->prepare($query);

    for $ref (@{ $form->{all_employeededuction} }) {
      $sth->execute($ref->{id});
      ($form->{total}{$ref->{id}}) = $sth->fetchrow_array;
      $sth->finish;
    }
    
    $query = qq|SELECT a.rn
		FROM acsrole a
		JOIN employee e ON (e.acsrole_id = a.id)
		WHERE e.id = $form->{id}|;
    ($rne) = $dbh->selectrow_array($query);
   
    $form->get_reference($dbh);

    $form->create_lock($myconfig, $dbh, $form->{id}, 'hr') unless $nolock;
 
  } else {

    $form->{startdate} = $form->current_date($myconfig);
  
  }

  my $login = $form->{login};
  $login =~ s/\@.*//;
  $query = qq|SELECT a.rn
              FROM acsrole a
	      JOIN employee e ON (e.acsrole_id = a.id)
	      WHERE e.login = '$login'|;
  my ($rnl) = $dbh->selectrow_array($query);

  $rnl *= 1;
  if ($rnl) {
    $form->{admin} = ($rne) ? $rnl < $rne : 1;
  }
  $form->{admin} = 1 if $login eq 'admin';

  # get acsrole
  $query = qq|SELECT id, description
              FROM acsrole
	      WHERE rn > $rnl
	      ORDER BY rn|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_acsrole} }, $ref;
  }
  $sth->finish;

  
  # get wages
  $query = qq|SELECT *
              FROM wage
	      ORDER BY 2|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_wage} }, $ref;
  }
  $sth->finish;

 
  # get deductions
  $query = qq|SELECT *
              FROM deduction
	      ORDER BY 2|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_deduction} }, $ref;
    # for payroll
    $form->{payrolldeduction}{$ref->{id}} = $ref;
  }
  $sth->finish;
  
  # get deductionrates
  $query = qq|SELECT *
              FROM deductionrate
	      ORDER BY trans_id, rn|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_deductionrate} }, $ref;
  }
  $sth->finish;
  
  my %ae = ( ap => { category => 'L', link => qq| AND c.link = 'AP' | },
	payment => { category => 'A', link => qq| AND c.link LIKE '%AP\_paid%' | } );
  for (qw(ap payment)) {
    $query = qq|SELECT c.accno, c.description,
		l.description AS translation
		FROM chart c
		LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
		WHERE c.charttype = 'A'
		AND c.category = '$ae{$_}{category}'
		$ae{$_}{link}
		ORDER BY c.accno|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      $ref->{description} = $ref->{translation} if $ref->{translation};
      push @{ $form->{"${_}_accounts"} }, $ref;
    }
    $sth->finish;
  }

  # get paymentmethod
  $query = qq|SELECT *
              FROM paymentmethod
	      ORDER BY rn|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  @{ $fiorm->{"all_paymentmethod"} } = ();
  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{"all_paymentmethod"} }, $ref;
  }
  $sth->finish;

  $dbh->disconnect;

}



sub save_employee {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect_noauto($myconfig);
  my $query;
  my $sth;

  $form->remove_locks($myconfig, $dbh, 'hr');

  my $bank_address_id;
  
  if ($form->{id}) {
    $query = qq|SELECT login
		FROM employee
		WHERE id = $form->{id}|;
    my ($login) = $dbh->selectrow_array($query);
    
    if ($login) {
      $query = qq|UPDATE report SET
                  login = '$form->{employeelogin}'
		  WHERE login = '$login'|;
      $dbh->do($query) || $form->dberror($query);
    }
  
    $query = qq|SELECT address_id
                FROM bank
		WHERE id = $form->{id}|;
    ($bank_address_id) = $dbh->selectrow_array($query);

    $query = qq|DELETE FROM bank
                WHERE id = $form->{id}|;
    $dbh->do($query) || $form->dberror($query);
    
    $bank_address_id *= 1;
    $query = qq|DELETE FROM address
                WHERE trans_id = $bank_address_id|;
    $dbh->do($query) || $form->dberror($query);

    $form->{addressid} *= 1;
    $query = qq|DELETE FROM address
	        WHERE id = $form->{addressid}|;
    $dbh->do($query) || $form->dberror($query);
    
    $query = qq|DELETE FROM payrate
                WHERE trans_id = $form->{id}|;
    $dbh->do($query) || $form->dberror($query);
    
    $query = qq|DELETE FROM reference
                WHERE trans_id = $form->{id}|;
    $dbh->do($query) || $form->dberror($query);
    
  } else {
    my $uid = localtime;
    $uid .= $$;

    $query = qq|INSERT INTO employee (name)
                VALUES ('$uid')|;
    $dbh->do($query) || $form->dberror($query);
    
    $query = qq|SELECT id FROM employee
                WHERE name = '$uid'|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    ($form->{id}) = $sth->fetchrow_array;
    $sth->finish;
  }

  $form->{employeenumber} = $form->update_defaults($myconfig, "employeenumber", $dbh) if ! $form->{employeenumber};

  for (qw(ap payment)) { ($form->{$_}) = split /--/, $form->{$_} }
  for (qw(paymentmethod acsrole)) { ($null, $form->{"${_}_id"}) = split /--/, $form->{$_} }
  $form->{acsrole_id} ||= 'NULL';
  $form->{sales} *= 1;
  
  my $employeelogin = ($form->{employeelogin}) ? $dbh->quote($form->{employeelogin}) : 'NULL';
	      
  $query = qq|UPDATE employee SET
              employeenumber = |.$dbh->quote($form->{employeenumber}).qq|,
	      name = |.$dbh->quote($form->{name}).qq|,
	      workphone = '$form->{workphone}',
	      workfax = '$form->{workfax}',
	      workmobile = '$form->{workmobile}',
	      homephone = '$form->{homephone}',
	      homemobile = '$form->{homemobile}',
	      startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
	      enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|,
	      notes = |.$dbh->quote($form->{notes}).qq|,
	      sales = '$form->{sales}',
	      login = $employeelogin,
	      email = |.$dbh->quote($form->{email}).qq|,
	      ssn = '$form->{ssn}',
	      dob = |.$form->dbquote($form->{dob}, SQL_DATE).qq|,
	      payperiod = |.$form->dbquote($form->{payperiod}, SQL_INT).qq|,
	      apid = (SELECT id FROM chart WHERE accno = '$form->{ap}'),
	      paymentid = (SELECT id FROM chart WHERE accno = '$form->{payment}'),
	      paymentmethod_id = |.$dbh->quote($form->{paymentmethod_id}).qq|,
	      acsrole_id = $form->{acsrole_id},
	      acs = '$form->{acs}'
	      WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  # add address
  my $id;
  my $var;

  if ($form->{addressid}) {
    $id = "id, ";
    $var = "$form->{addressid}, ";
  }

  $query = qq|INSERT INTO address ($id trans_id, address1, address2,
              city, state, zipcode, country) VALUES ($var
	      $form->{id},
	      |.$dbh->quote($form->{address1}).qq|,
	      |.$dbh->quote($form->{address2}).qq|,
	      |.$dbh->quote($form->{city}).qq|,
	      |.$dbh->quote($form->{state}).qq|,
	      |.$dbh->quote($form->{zipcode}).qq|,
	      |.$dbh->quote($form->{country}).qq|)|;
  $dbh->do($query) || $form->dberror($query);
  
  my $ok;
  
  for (qw(iban bic membernumber clearingnumber)) {
    if ($form->{$_}) {
      $ok = 1;
      last;
    }
  }

  if (!$ok) {
    for (qw(name address1 address2 city state zipcode country)) {
      if ($form->{"bank$_"}) {
	$ok = 1;
	last;
      }
    }
  }
  
  if ($ok) {
    if ($bank_address_id) {
      $query = qq|INSERT INTO bank (id, name, iban, bic, membernumber,
                  clearingnumber, address_id)
                  VALUES ($form->{id}, |
		  .$dbh->quote(uc $form->{bankname}).qq|,|
		  .$dbh->quote($form->{iban}).qq|,|
		  .$dbh->quote($form->{bic}).qq|,|
		  .$dbh->quote($form->{membernumber}).qq|,|
		  .$dbh->quote($form->{clearingnumber}).qq|,
		  $bank_address_id
		  )|;
    } else {
      $query = qq|INSERT INTO bank (id, name, iban, bic, membernumber,
                  clearingnumber)
                  VALUES ($form->{id}, |
		  .$dbh->quote(uc $form->{bankname}).qq|,|
		  .$dbh->quote($form->{iban}).qq|,|
		  .$dbh->quote($form->{bic}).qq|,|
		  .$dbh->quote($form->{membernumber}).qq|,|
		  .$dbh->quote($form->{clearingnumber}).qq|
		  )|;
    }
    $dbh->do($query) || $form->dberror($query);

    $query = qq|SELECT address_id
                FROM bank
		WHERE id = $form->{id}|;
    ($bank_address_id) = $dbh->selectrow_array($query);
    
  }

  $ok = 0;
  for (qw(address1 address2 city state zipcode country)) {
    if ($form->{"bank$_"}) {
      $ok = 1;
      last;
    }
  }

  if ($ok) {
    if ($bank_address_id) {
      $query = qq|INSERT INTO address (id, trans_id, address1, address2,
                  city, state, zipcode, country) VALUES (
		  $bank_address_id, $bank_address_id,
		  |.$dbh->quote(uc $form->{bankaddress1}).qq|,
		  |.$dbh->quote(uc $form->{bankaddress2}).qq|,
		  |.$dbh->quote(uc $form->{bankcity}).qq|,
		  |.$dbh->quote(uc $form->{bankstate}).qq|,
		  |.$dbh->quote(uc $form->{bankzipcode}).qq|,
		  |.$dbh->quote(uc $form->{bankcountry}).qq|)|;
      $dbh->do($query) || $form->dberror($query);

    } else {
      $query = qq|INSERT INTO bank (id, name)
                  VALUES ($form->{id},
		  |.$dbh->quote(uc $form->{bankname}).qq|)|;
      $dbh->do($query) || $form->dberror($query);

      $query = qq|SELECT address_id
                  FROM bank
		  WHERE id = $form->{id}|;
      ($bank_address_id) = $dbh->selectrow_array($query);

      $query = qq|INSERT INTO address (id, trans_id, address1, address2,
                  city, state, zipcode, country) VALUES (
		  $bank_address_id, $bank_address_id,
		  |.$dbh->quote(uc $form->{bankaddress1}).qq|,
		  |.$dbh->quote(uc $form->{bankaddress2}).qq|,
		  |.$dbh->quote(uc $form->{bankcity}).qq|,
		  |.$dbh->quote(uc $form->{bankstate}).qq|,
		  |.$dbh->quote(uc $form->{bankzipcode}).qq|,
		  |.$dbh->quote(uc $form->{bankcountry}).qq|)|;
      $dbh->do($query) || $form->dberror($query);
    }
  }
  

  # insert wage, deduction and exempt for payroll
  $query = qq|DELETE FROM employeewage
              WHERE employee_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  $query = qq|INSERT INTO employeewage (employee_id, id, wage_id) VALUES
              ($form->{id},?,?)|;
  my $sth = $dbh->prepare($query) || $form->dberror($query);

  for ($i = 1; $i <= $form->{wage_rows}; $i++) {
    ($null, $wage_id) = split /--/, $form->{"wage_$i"};
    if ($wage_id) {
      $sth->execute($i,$wage_id) || $form->dberror($query);
    }
  }
  $sth->finish;

  $query = qq|DELETE FROM employeededuction
              WHERE employee_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  $query = qq|INSERT INTO employeededuction (employee_id, id, deduction_id,
              exempt, maximum) VALUES ($form->{id},?,?,?,?)|;
  my $sth = $dbh->prepare($query) || $form->dberror($query);

  for ($i = 1; $i <= $form->{deduction_rows}; $i++) {
    for (qw(exempt maximum)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
    ($null, $deduction_id) = split /--/, $form->{"deduction_$i"};
    if ($deduction_id) {
      $sth->execute($i, $deduction_id, $form->{"exempt_$i"}, $form->{"maximum_$i"}) || $form->dberror($query);
    }
  }
  $sth->finish;

  $query = qq|INSERT INTO payrate (trans_id, id, rate, above)
              VALUES ($form->{id},?,?,?)|;
  $sth = $dbh->prepare($query) || $form->dberror($query);
  
  for ($i = 1; $i <= $form->{payrate_rows}; $i++) {
    for (qw(rate above)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
    $form->{"above_$i"} = $form->parse_amount($myconfig, $form->{"above_$i"});
    if ($form->{"rate_$i"}) {
      $sth->execute($i, $form->{"rate_$i"}, $form->{"above_$i"}) || $form->dberror($query);
    }
  }
  $sth->finish;

  $form->save_reference($dbh);
  
  my $rc = $dbh->commit;
  $dbh->disconnect;

  $rc;

}


sub delete_employee {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect_noauto($myconfig);

  my $query;

  $query = qq|SELECT address_id
              FROM bank
              WHERE id = $form->{id}|;
  my ($bank_address_id) = $dbh->selectrow_array($query);
  
  $bank_address_id *= 1;
  $query = qq|DELETE FROM address
              WHERE trans_id = $bank_address_id|;
  $dbh->do($query) || $form->dberror($query);
  
  $query = qq|SELECT login
              FROM employee
	      WHERE id = $form->{id}|;
  my ($login) = $dbh->selectrow_array($query);
  
  if ($login) {
    $query = qq|UPDATE report
                SET login = ''
		WHERE login = '$login'|;
    $dbh->do($query) || $form->dberror($query);
  }

  # delete employee
  $query = qq|DELETE FROM $form->{db}
              WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  $query = qq|DELETE FROM address
              WHERE trans_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);
  
  $query = qq|DELETE FROM reference
              WHERE trans_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);
 
  $query = qq|DELETE FROM audittrail
	      WHERE employee_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  $form->remove_locks($myconfig, $dbh, 'hr');
 
  my $rc = $dbh->commit;
  $dbh->disconnect;

  $rc;

}


sub employees {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  my %defaults = $form->get_defaults($dbh, \@{['company', 'precision']});
  for (keys %defaults) { $form->{$_} = $defaults{$_} }

  my $where = "1 = 1";
  $form->{sort} ||= "name";
  my @sf = qw(name);
  my $sortorder = $form->sort_order(\@sf);
  
  my $var;
  
  for (qw(name employeenumber notes)) {
    if ($form->{$_} ne "") {
      $var = $form->like(lc $form->{$_});
      $where .= " AND lower(e.$_) LIKE '$var'";
    }
  }
 
  if ($form->{startdatefrom}) {
    $where .= " AND e.startdate >= '$form->{startdatefrom}'";
  }
  if ($form->{startdateto}) {
    $where .= " AND e.startdate <= '$form->{startdateto}'";
  }
  if ($form->{status} eq 'sales') {
    $where .= " AND e.sales = '1'";
  }
  if ($form->{status} eq 'orphaned') {
    $where .= qq| AND e.login IS NULL
                  AND NOT e.id IN 
		          (SELECT DISTINCT employee_id
			   FROM jcitems)|;
  }
  if ($form->{status} eq 'active') {
    $where .= qq| AND e.enddate IS NULL|;
  }
  if ($form->{status} eq 'inactive') {
    $where .= qq| AND e.enddate <= current_date|;
  }

  my $query = qq|SELECT e.*, 
                 ad.address1, ad.address2, ad.city, ad.state,
		 ad.zipcode, ad.country,
		 bk.iban, bk.bic,
		 r.description AS acsrole
                 FROM employee e
		 LEFT JOIN address ad ON (ad.trans_id = e.id)
		 LEFT JOIN bank bk ON (bk.id = e.id)
		 LEFT JOIN acsrole r ON (r.id = e.acsrole_id)
                 WHERE $where
		 ORDER BY $sortorder|;

  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ref->{address} = "";
    for (qw(address1 address2 city state zipcode country)) { $ref->{address} .= "$ref->{$_} " }
    push @{ $form->{all_employee} }, $ref;
  }
  $sth->finish;
  
  $query = qq|SELECT c.accno, c.description, l.description AS translation
	      FROM chart c
	      LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
	      WHERE c.link = 'AP'
	      ORDER BY c.accno|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ref->{description} = $ref->{translation} if $ref->{translation};
    push @{ $form->{all_ap} }, $ref;
  }
  $sth->finish;
 
  $query = qq|SELECT c.accno, c.description, l.description AS translation
	      FROM chart c
	      LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
	      WHERE c.link LIKE '%AP\_paid%'
	      ORDER BY c.accno|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ref->{description} = $ref->{translation} if $ref->{translation};
    push @{ $form->{all_payment} }, $ref;
  }
  $sth->finish;
  
  $query = qq|SELECT *
              FROM paymentmethod
	      ORDER BY rn|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_paymentmethod} }, $ref;
  }
  $sth->finish;

  $form->get_peripherals($dbh);

  $form->all_languages($myconfig, $dbh);
  
  $form->all_projects($myconfig, $dbh);
  
  $form->all_departments($myconfig, $dbh, 'vendor');

  $dbh->disconnect;

}


sub payroll_links {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect($myconfig);
  
  $form->remove_locks($myconfig, $dbh);
  
  my @var;

  my %defaults = $form->get_defaults($dbh, \@{['company', 'precision', 'namesbynumber', 'referenceurl']});
  for (keys %defaults) { $form->{$_} = $defaults{$_} }
  
  my $sortorder = "name";
  if ($defaults{namesbynumber}) {
     $sortorder = "employeenumber";
  }

  my $query = qq|SELECT *
                 FROM employee
		 ORDER BY $sortorder|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_employee} }, $ref;
  }
  $sth->finish;

  $form->{datepaid} = $form->{transdate} = $form->current_date($myconfig);

  if ($form->{id}) {
    $query = qq|SELECT a.*, e.name AS employee, e.payperiod,
                d.description AS department,
		c1.accno AS ap_accno, c1.description AS ap_accno_description,
		t1.description AS ap_accno_translation,
		c2.accno AS payment_accno, c2.description AS payment_accno_description,
		t2.description AS payment_accno_translation,
		pm.description AS paymentmethod, e.paymentmethod_id
                FROM ap a
		JOIN employee e ON (e.id = a.vendor_id)
		LEFT JOIN department d ON (d.id = a.department_id)
		LEFT JOIN chart c1 ON (c1.id = e.apid)
		LEFT JOIN translation t1 ON (t1.trans_id = c1.id AND t1.language_code = '$myconfig->{countrycode}')
		LEFT JOIN chart c2 ON (c2.id = e.paymentid)
		LEFT JOIN translation t2 ON (t2.trans_id = c2.id AND t2.language_code = '$myconfig->{countrycode}')
		LEFT JOIN paymentmethod pm ON (pm.id = e.paymentmethod_id)
		WHERE a.id = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);
    $ref = $sth->fetchrow_hashref(NAME_lc);
    delete $ref->{id};
    
    for (qw(ap payment)) {
      $ref->{$_} = qq|$ref->{"${_}_accno"}--$ref->{"${_}_accno_description"}|;
      $ref->{$_} = qq|$ref->{"${_}_accno"}--$ref->{"${_}_accno_description"}| if $ref->{"${_}_accno_translation"};
    }
    
    for (qw(paymentmethod department)) {
      $ref->{$_} = qq|$ref->{$_}--$ref->{"${_}_id"}|;
    }
    
    $ref->{employee} = qq|$ref->{employee}--$ref->{vendor_id}|;
    
    for (keys %$ref) { $form->{$_} = $ref->{$_} }
    $sth->finish;
    
    # ap
    $query = qq|SELECT c.accno, c.description, t.description AS translation
                FROM acc_trans ac
		JOIN chart c ON (c.id = ac.chart_id)
		LEFT JOIN translation t ON (t.trans_id = c.id AND t.language_code = '$myconfig->{countrycode}')
		WHERE c.link = 'AP'
		AND ac.trans_id = $form->{id}|;
    (@var) = $dbh->selectrow_array($query);
    if (@var) {
      $form->{ap} = qq|$var[0]--$var[1]|;
      $form->{ap} = qq|$var[0]--$var[2]| if $var[2];
    }
    
    # payment
    $query = qq|SELECT ac.source, ac.memo, c.accno, c.description,
                t.description AS translation,
		pm.id AS pmid, pm.description AS paymentmethod
                FROM acc_trans ac
		JOIN chart c ON (c.id = ac.chart_id)
		LEFT JOIN translation t ON (t.trans_id = c.id AND t.language_code = '$myconfig->{countrycode}')
		JOIN payment p ON (p.trans_id = ac.trans_id and p.id = ac.id)
		LEFT JOIN paymentmethod pm ON (pm.id = p.paymentmethod_id)
		WHERE c.link LIKE '%AP_paid%'
		AND ac.trans_id = $form->{id}|;
    (@var) = $dbh->selectrow_array($query);
    if (@var) {
      $form->{source} = $var[0];
      $form->{memo} = $var[1];
      $form->{payment} = qq|$var[2]--$var[3]|;
      $form->{payment} = qq|$var[2]--$var[4]| if $var[4];
      $form->{paymentmethod} = qq|$var[6]--$var[5]|;
    }
    
    my %wage;
    
    # all wages for employee
    $query = qq|SELECT w.*
                FROM wage w
                JOIN employeewage ew ON (ew.wage_id = w.id)
		WHERE ew.employee_id = $form->{vendor_id}
		ORDER BY ew.id|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    $i = 0;
    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      $i++;
      $form->{"wage_$i"} = $ref->{description};
      $form->{"wage_id_$i"} = $ref->{id};
      $form->{"amount_$i"} = $ref->{amount};
      $wage{$ref->{id}} = $i;
    }
    $sth->finish;
    $form->{wage_rows} = $i;
    
    # wages and deductions
    $query = qq|SELECT pt.*, w.description AS wage, d.description AS deduction
                FROM pay_trans pt
		LEFT JOIN wage w ON (w.id = pt.id)
		LEFT JOIN deduction d ON (d.id = pt.id)
		WHERE pt.trans_id = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    my $i = 0;
    my $j = 0;
    
    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      if ($ref->{wage}) {
	for (qw(qty amount)) { $form->{"${_}_$wage{$ref->{id}}"} = $ref->{$_} }
      }
      if ($ref->{deduction}) {
	$j++;
	$form->{"deduction_$j"} = $ref->{deduction};
	$form->{"deduction_id_$j"} = $ref->{id};
      }
    }
    $sth->finish;
    $form->{deduction_rows} = $j;

    # payrates
    $query = qq|SELECT *
                FROM payrate
		WHERE trans_id = $form->{vendor_id}
		ORDER BY id|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    $i = 0;
    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      $i++;
      for (qw(rate above)) { $form->{"${_}_$i"} = $ref->{$_} }
    }
    $sth->finish;
    $form->{payrate_rows} = $i;

    $form->get_reference($dbh);

    $form->create_lock($myconfig, $dbh, $form->{id}, 'hr');
    
  }
 
  $query = qq|SELECT c.accno, c.description, l.description AS translation
	      FROM chart c
	      LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
	      WHERE c.link = 'AP'
	      ORDER BY c.accno|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ref->{description} = $ref->{translation} if $ref->{translation};
    push @{ $form->{all_ap} }, $ref;
  }
  $sth->finish;
 
  $query = qq|SELECT c.accno, c.description, l.description AS translation
	      FROM chart c
	      LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
	      WHERE c.link LIKE '%AP\_paid%'
	      ORDER BY c.accno|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ref->{description} = $ref->{translation} if $ref->{translation};
    push @{ $form->{all_payment} }, $ref;
  }
  $sth->finish;
  
  $query = qq|SELECT *
              FROM paymentmethod
	      ORDER BY rn|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_paymentmethod} }, $ref;
  }
  $sth->finish;

  $form->get_peripherals($dbh);

  $form->all_languages($myconfig, $dbh);
  
  $form->all_projects($myconfig, $dbh);
  
  $form->all_departments($myconfig, $dbh, 'vendor');

  $dbh->disconnect;

}


sub search_payroll {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect($myconfig);
  
  my %defaults = $form->get_defaults($dbh, \@{['namesbynumber']});
  
  my $sortorder = "name";
  if ($defaults{namesbynumber}) {
    $sortorder = "employeenumber";
  }

  my $query = qq|SELECT id, name, employeenumber
                 FROM employee
		 ORDER BY $sortorder|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_employee} }, $ref;
  }
  $sth->finish;
  
  $query = qq|SELECT *
              FROM paymentmethod
	      ORDER BY rn|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_paymentmethod} }, $ref;
  }
  $sth->finish;

  $form->all_projects($myconfig, $dbh);
  
  $form->all_departments($myconfig, $dbh, 'vendor');

  $dbh->disconnect;

}



sub payroll_transactions {
  my ($self, $myconfig, $form) = @_;

  my $dbh = $form->dbconnect($myconfig);

  my %defaults = $form->get_defaults($dbh, \@{['company', 'precision']});
  for (keys %defaults) { $form->{$_} = $defaults{$_} }

  my $where = "1 = 1";
  my $acwhere = "1 = 1";
  my $sortorder = "employee" || $form->{sort};

  my $null;
  my $id;
 
  $form->report_level($myconfig, $dbh);
  
  if ($form->{employee}) {
    ($null, $id) = split /--/, $form->{employee};
    $where .= qq|
                AND v.id = $id|;
  }
  
  if ($form->{department}) {
    ($null, $id) = split /--/, $form->{department};
    $where .= qq|
                AND a.department_id = $id|;
  }
  if ($form->{paymentmethod}) {
    ($null, $id) = split /--/, $form->{paymentmethod};
    $where .= qq|
                AND a.paymentmethod_id = $id|;
  }

  ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  
  $where .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
  $where .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
  
  my $query = qq|SELECT DISTINCT v.name AS employee,
                 a.transdate, a.invnumber, a.vendor_id, a.id, a.amount, a.paid
                 FROM pay_trans pt
		 JOIN ap a ON (a.id = pt.trans_id)
		 JOIN vendor v ON (v.id = a.vendor_id)
		 WHERE $where
		 ORDER BY $sortorder|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  # project ?
  ($null, $id) = split /--/, $form->{projectnumber};
  $query = qq|SELECT trans_id
	      FROM acc_trans
	      WHERE project_id = $id
	      AND trans_id = ?|;
  my $pth = $dbh->prepare($query);

  # gl
  $query = qq|SELECT reference
              FROM gl
	      WHERE id = ?|;
  my $gth = $dbh->prepare($query);
	      
  # wages / deductions
  $query = qq|SELECT *
              FROM pay_trans
	      WHERE trans_id = ?|;
  my $wth = $dbh->prepare($query);

  my $ok;
  my $ref;

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ok = 0;
    $wth->execute($ref->{id});

    while ($dref = $wth->fetchrow_hashref(NAME_lc)) {
      $ref->{glid} = $dref->{glid};
      if ($ref->{$dref->{id}} = $dref->{amount} * $dref->{qty}) {
	$form->{$dref->{id}} = 1;
	$ok = 1;
      }
    }
    $wth->finish;
    
    if ($ref->{glid}) {
      $gth->execute($ref->{glid});
      ($ref->{reference}) = $gth->fetchrow_array;
      $gth->finish;
    }

    if ($form->{projectnumber}) {
      $pth->execute($ref->{id});
      $ok = $pth->fetchrow_array;
      $pth->finish;
    }
    
    push @{ $form->{transactions} }, $ref if $ok;
    
  }
  $sth->finish;

  for (qw(wage deduction)) {
    $query = qq|SELECT id, description
		FROM $_
		ORDER BY 2|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      push @{ $form->{"all_$_"} }, $ref if $form->{$ref->{id}};
    }
    $sth->finish;
  }

  $dbh->disconnect;

}


sub post_transaction {
  my ($self, $myconfig, $form) = @_;
  
  my $dbh = $form->dbconnect_noauto($myconfig);
  my $ap = new Form;
  my $gl = new Form;
  
  my %defaults = $form->get_defaults($dbh, \@{['expense_accno_id']});
  
  my $query;
  my $sth;
  my $ref;
  my $user_id;

  $query = qq|SELECT *
              FROM wage
	      WHERE id = ?|;
  my $wth = $dbh->prepare($query) || $form->dberror($query);

  $query = qq|SELECT *
              FROM deduction
	      WHERE id = ?|;
  my $dth = $dbh->prepare($query) || $form->dberror($query);

  $query = qq|INSERT INTO pay_trans (trans_id, id, qty, amount)
              VALUES (?,?,?,?)|;
  my $pth = $dbh->prepare($query) || $form->dberror($query);
	      
  $query = qq|SELECT curr
              FROM curr
	      ORDER BY rn|;
  ($ap->{currency}) = $dbh->selectrow_array($query);
  
  $query = qq|SELECT accno
              FROM chart
	      WHERE id = ?|;
  my $cth = $dbh->prepare($query) || $form->dberror($query);

  my ($employee, $employee_id) = split /--/, $form->{employee};

  $cth->execute($defaults{expense_accno_id});
  my ($expense_accno) = $cth->fetchrow_array;
  $cth->finish;
  
  $query = qq|SELECT id
              FROM vendor
	      WHERE id = $employee_id|;

  if (! $dbh->selectrow_array($query)) {
    $query = qq|SELECT *
                FROM employee
		WHERE id = $employee_id|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    $ref = $sth->fetchrow_hashref(NAME_lc);

    ($null, $user_id) = $form->get_employee($dbh);
    my $vendornumber = $form->update_defaults($myconfig, 'vendornumber');
    
    $query = qq|INSERT INTO vendor (id, name, phone,
                fax, email, notes,
		vendornumber, employee_id, curr,
		startdate, enddate, arap_accno_id,
		payment_accno_id, paymentmethod_id)
                VALUES (
                $employee_id, '$employee', '$ref->{workphone}',
		'$ref->{workfax}', '$ref->{email}', '$ref->{notes}',
		$vendornumber, $user_id, '$ap->{currency}',|
		.$form->dbquote($ref->{startdate}, SQL_DATE).qq|, |
		.$form->dbquote($ref->{enddate}, SQL_DATE).qq|, |
		.$dbh->quote($ref->{apid}).qq|, |
		.$dbh->quote($ref->{paymentid}).qq|, |
		.$dbh->quote($ref->{paymentmethod_id}).qq|)|;
    $dbh->do($query) || $form->dberror($query);

    $query = qq|INSERT INTO contact (trans_id, typeofcontact,
                phone, fax, mobile, email)
                VALUES ($employee_id, 'company',
		'$ref->{workphone}', '$ref->{workfax}', '$ref->{workmobile}',
		'$ref->{email}')|;
    $dbh->do($query) || $form->dberror($query);

    $sth->finish;
  }
  
  if ($form->{id}) {
    $query = qq|SELECT pt.glid, g.reference
		FROM pay_trans pt
		JOIN ap a ON (a.id = pt.trans_id)
		JOIN gl g ON (g.id = pt.glid)
		WHERE pt.trans_id = $form->{id}|;
    ($gl->{id}, $gl->{reference}) = $dbh->selectrow_array($query);

    $query = qq|DELETE FROM pay_trans
                WHERE trans_id = $form->{id}|;
    $dbh->do($query) || $form->dberror($query);
  }
 
  $ap->{login} = $form->{login};
  $ap->{id} = $form->{id};
  $ap->{invnumber} = $form->{invnumber};
  $ap->{vendor_id} = $employee_id; 
  $ap->{defaultcurrency} = $ap->{currency};
  $ap->{department} = $form->{department};
  $ap->{vc} = 'vendor';
  $ap->{transdate} = $form->{transdate};
  $ap->{duedate} = $form->{transdate};
  $ap->{AP} = $form->{ap};
  $ap->{description} = $form->{description};
  
  my $i = 0;
  my $j;
  my $employerpays;
  my $amount;
  
  for $j (1 .. $form->{wage_rows}) {
    
    if ($form->{"wage_id_$j"}) {
      
      $wth->execute($form->{"wage_id_$j"});
      $ref = $wth->fetchrow_hashref(NAME_lc);
      
      # accno
      $cth->execute($ref->{chart_id});
      ($accno) = $cth->fetchrow_array;
      $cth->finish;

      if ($ref->{defer}) {
	# deferred wages
	$cth->execute($ref->{defer});
	($defer) = $cth->fetchrow_array;
	$cth->finish;
	
	if ($amount = $form->round_amount($form->{"pay_$j"}, 10)) {
	  push @employerpays, { employeraccno => $defer, employeeaccno => $accno, description => $form->{"wage_$j"}, amount => $amount };
	}
	
      } else {
	
	$i++;

	# acc_trans amount
	$ap->{"amount_$i"} = $form->{"pay_$j"};
	$ap->{"AP_amount_$i"} = $accno || $expense_accno;
	$ap->{"description_$i"} = $form->{"wage_$j"};
	$ap->{"projectnumber_$i"} = $form->{project};
      }
      $wth->finish;

      
    }
  }
  
  for $j (1 .. $form->{deduction_rows}) {
    $dth->execute($form->{"deduction_id_$j"});
    $ref = $dth->fetchrow_hashref(NAME_lc);

    # employee
    if ($ref->{employeepays}) {
      # accno
      $cth->execute($ref->{employee_accno_id});
      ($accno) = $cth->fetchrow_array;
      $cth->finish;
      
      $accno ||= $expense_accno;
      $i++;

      # acc_trans amount
      $ap->{"amount_$i"} = $form->{"deduct_$j"};
      $ap->{"AP_amount_$i"} = $accno;
      $ap->{"description_$i"} = $form->{"deduction_$j"};
      $ap->{"projectnumber_$i"} = $form->{project};
    }

    # employer
    if ($ref->{employerpays}) {
      $cth->execute($ref->{employer_accno_id});
      ($employeraccno) = $cth->fetchrow_array;
      $cth->finish;
      
      $employeraccno ||= $form->{ap};
      
      $amount = $form->parse_amount($myconfig, $form->{"deduct_$j"}) * $ref->{employerpays};

      if ($amount) {
	push @employerpays, { employeraccno => $employeraccno, employeeaccno => $accno, description => $form->{"deduction_$j"}, amount => $amount };
      }
      
    }
 
    $dth->finish;
  }
  $ap->{rowcount} = $i;
  
  # payment
  $ap->{paidaccounts} = 1;
  $ap->{"datepaid_1"} = $form->{datepaid};
  $ap->{"paid_1"} = $form->{paid};
  $ap->{"AP_paid_1"} = $form->{payment};
  $ap->{"source_1"} = $form->{source};
  $ap->{"memo_1"} = $form->{memo};
  $ap->{"paymentmethod_1"} = $form->{paymentmethod};

  $i = 0;
  for (1 .. $form->{reference_rows}) {
    if ($form->{"referenceid_$_"}) {
      $i++;
      $ap->{"referenceid_$i"} = $form->{"referenceid_$_"};
      $ap->{"referencedescription_$i"} = $form->{"referencedescription_$_"};
      $gl->{"referenceid_$i"} = $form->{"referenceid_$_"};
      $gl->{"referencedescription_$i"} = $form->{"referencedescription_$_"};
    }
  }
  $ap->{reference_rows} = $i;
  $gl->{reference_rows} = $i;
  
  AA->post_transaction($myconfig, $ap, $dbh);
  
  # pay_trans entries
  for $j (1 .. $form->{wage_rows}) {
    for (qw(qty amount)) { $form->{"${_}_$j"} = $form->parse_amount($myconfig, $form->{"${_}_$j"}) }

    if ($form->round_amount($form->{"qty_$j"} * $form->{"amount_$j"},10)) {
      $pth->execute($ap->{id}, $form->{"wage_id_$j"}, $form->{"qty_$j"}, $form->{"amount_$j"});
      $pth->finish;
    }
  }

  for $j (1 .. $form->{deduction_rows}) {
    if ($form->{"deduct_$j"} = $form->parse_amount($myconfig, $form->{"deduct_$j"})) {
      $pth->execute($ap->{id}, $form->{"deduction_id_$j"}, 1, $form->{"deduct_$j"});
      $pth->finish;
    }
  }

  $gl->{login} = $form->{login};
  
  # employer pays
  if (@employerpays) {
    $gl->{transdate} = $form->{transdate};
    $gl->{department} = $form->{department};
    $gl->{currency} = $ap->{currency};
    $gl->{defaultcurrency} = $ap->{currency};
    $gl->{description} = $employee;
    $gl->{notes} = qq|$form->{description}|;

    $i = 1;
    for (@employerpays) {

      if ($_->{amount}) {
	$gl->{"accno_$i"} = $_->{employeeaccno} || $form->{expense};
	$gl->{"memo_$i"} = $_->{description};
	$gl->{"projectnumber_$i"} = $form->{projectnumber};

	if ($_->{amount} < 0) {
	  $gl->{"debit_$i"} = $form->format_amount($myconfig, $_->{amount}, $form->{precision});
	} else {
	  $gl->{"credit_$i"} = $form->format_amount($myconfig, $_->{amount} * -1, $form->{precision});
	}
	$i++;
	
	$gl->{"accno_$i"} = $_->{employeraccno} || $form->{ap};
	$gl->{"memo_$i"} = $_->{description};
	$gl->{"projectnumber_$i"} = $form->{projectnumber};

	if ($_->{amount} < 0) {
	  $gl->{"credit_$i"} = $form->format_amount($myconfig, $_->{amount}, $form->{precision});
	} else {
	  $gl->{"debit_$i"} = $form->format_amount($myconfig, $_->{amount} * -1, $form->{precision});
	}
	  
	$i++;
      }
    }
    $gl->{rowcount} = $i;

    GL->post_transaction($myconfig, $gl, $dbh);

    $query = qq|UPDATE pay_trans SET
                glid = $gl->{id}
		WHERE trans_id = $ap->{id}|;
    $dbh->do($query) || $form->dberror($query);

  }

  my $rc = $dbh->commit;
  
  $dbh->disconnect;
  
  $rc;
  
}


sub get_deduction {
  my ($self, $myconfig, $form) = @_;

  my $dbh = $form->dbconnect($myconfig);
  my $query;
  my $sth;
  my $ref;
  my $item;
  my $i;

  $form->remove_locks($myconfig, $dbh, 'hr');
  
  my %defaults = $form->get_defaults($dbh, \@{['precision', 'company']});
  for (keys %defaults) { $form->{$_} = $defaults{$_} }

  if ($form->{id}) {
    $query = qq|SELECT d.*,
		 c1.accno AS employee_accno,
		 c1.description AS employee_accno_description,
		 l1.description AS employee_accno_translation,
		 c2.accno AS employer_accno,
		 c2.description AS employer_accno_description,
		 l2.description AS employer_accno_translation,
		 b.description AS basedesc
                 FROM deduction d
		 LEFT JOIN chart c1 ON (c1.id = d.employee_accno_id)
		 LEFT JOIN translation l1 ON (l1.trans_id = c1.id AND l1.language_code = '$myconfig->{countrycode}')
		 LEFT JOIN chart c2 ON (c2.id = d.employer_accno_id)
		 LEFT JOIN translation l2 ON (l2.trans_id = c2.id AND l2.language_code = '$myconfig->{countrycode}')
		 LEFT JOIN deduction b ON (d.basedon = b.id)
                 WHERE d.id = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);
  
    $ref = $sth->fetchrow_hashref(NAME_lc);
    for (qw(employee employer)) { $ref->{"${_}_accno_description"} = $ref->{"${_}_translation"} if $ref->{"${_}_translation"} }
    for (keys %$ref) { $form->{$_} = $ref->{$_} }

    $sth->finish;
  
    # check if orphaned
    $form->{status} = "";
    $query = qq|SELECT count(*) FROM employeededuction
                WHERE deduction_id = $form->{id}|;
    if (! $dbh->selectrow_array($query)) {
      $form->{status} = 'orphaned';
    }

    # get the rates
    $query = qq|SELECT *
                FROM deductionrate
                WHERE trans_id = $form->{id}
		ORDER BY rn|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      push @{ $form->{deductionrate} }, $ref;
    }
    $sth->finish;

    $query = qq|SELECT d.id, d.description, da.withholding, da.percent
                FROM deduction d
		JOIN deduct da ON (da.deduction_id = d.id)
                WHERE da.trans_id = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      push @{ $form->{deduct} }, $ref;
    }
    $sth->finish;

    # build selection list for basedon
    $query = qq|SELECT id, description
                FROM deduction
	        WHERE id != $form->{id}
		ORDER BY 2|;
		
    $form->create_lock($myconfig, $dbh, $form->{id}, 'hr');

  } else {
    # build selection list for basedon
    $query = qq|SELECT id, description
		FROM deduction
		ORDER BY 2|;
  }
  
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_deduction} }, $ref;
  }
  $sth->finish;

  $query = qq|SELECT c.accno, c.description,
	      l.description AS translation
	      FROM chart c
	      LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
	      WHERE c.charttype = 'A'
	      AND c.link LIKE '%AP\_amount%'
	      ORDER BY c.accno|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ref->{description} = $ref->{translation} if $ref->{translation};
    push @{ $form->{accounts} }, $ref;
  }
  $sth->finish;
  
  $dbh->disconnect;

}


sub deductions {
  my ($self, $myconfig, $form) = @_;

  my $dbh = $form->dbconnect($myconfig);

  my %defaults = $form->get_defaults($dbh, \@{['precision', 'company']});
  for (keys %defaults) { $form->{$_} = $defaults{$_} }

  my $query = qq|SELECT d.id, d.description, d.employeepays, d.employerpays,
                 b.description AS basedon,
                 c1.accno AS employee_accno,
                 c2.accno AS employer_accno,
                 dr.*
                 FROM deduction d
		 LEFT JOIN deduction b ON (b.id = d.basedon)
		 LEFT JOIN deductionrate dr ON (dr.trans_id = d.id)
		 LEFT JOIN chart c1 ON (d.employee_accno_id = c1.id)
		 LEFT JOIN chart c2 ON (d.employer_accno_id = c2.id)
                 ORDER BY 2, dr.rn|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);
  
  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_deduction} }, $ref;
  }
  
  $sth->finish;
  $dbh->disconnect;

}


sub save_deduction {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect_noauto($myconfig);

  my $null;
  my $deduction_id;
  my $query;
  my $sth;

  ($null, $form->{basedon}) = split /--/, $form->{basedon};
  
  if (! $form->{id}) {
    my $uid = localtime;
    $uid .= $$;

    $query = qq|INSERT INTO deduction (description)
                VALUES ('$uid')|;
    $dbh->do($query) || $form->dberror($query);
    
    $query = qq|SELECT id FROM deduction
                WHERE description = '$uid'|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    ($form->{id}) = $sth->fetchrow_array;
    $sth->finish;
  }


  for (qw(employee employer)) {
    ($form->{"${_}_accno"}) = split /--/, $form->{"${_}_accno"};
    $form->{"${_}pays"} = $form->parse_amount($myconfig, $form->{"${_}pays"});
  }
  
  $query = qq|UPDATE deduction SET
	      description = |.$dbh->quote($form->{description}).qq|,
	      employee_accno_id =
	           (SELECT id FROM chart
	            WHERE accno = '$form->{employee_accno}'),
	      employer_accno_id =
	           (SELECT id FROM chart
	            WHERE accno = '$form->{employer_accno}'),
	      employerpays = '$form->{employerpays}',
	      employeepays = '$form->{employeepays}',
	      fromage = |.$form->dbquote($form->{fromage}, SQL_INT).qq|,
	      toage = |.$form->dbquote($form->{toage}, SQL_INT).qq|,
	      basedon = |.$dbh->quote($form->{basedon}).qq|,
	      agedob = |.$dbh->quote($form->{agedob}).qq|
	      WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);


  $query = qq|DELETE FROM deductionrate
              WHERE trans_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);
  
  $query = qq|INSERT INTO deductionrate
	      (rn, trans_id, rate, amount, above, below) VALUES (?,?,?,?,?,?)|;
  $sth = $dbh->prepare($query) || $form->dberror($query);
 
  for ($i = 1; $i <= $form->{rate_rows}; $i++) {
    for (qw(rate amount above below)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
    $form->{"rate_$i"} /= 100;

    if ($form->{"rate_$i"} + $form->{"amount_$i"} + $form->{"above_$i"} + $form->{"below_$i"}) {
      $sth->execute($i, $form->{id}, $form->{"rate_$i"}, $form->{"amount_$i"}, $form->{"above_$i"}, $form->{"below_$i"}) || $form->dberror($query);
    }
  }
  $sth->finish;

  $query = qq|DELETE FROM deduct
              WHERE trans_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  $query = qq|INSERT INTO deduct
              (trans_id, deduction_id, withholding, percent) VALUES (?,?,?,?)|;
  $sth = $dbh->prepare($query) || $form->dberror($query);

  for ($i = 1; $i <= $form->{deduct_rows}; $i++) {
    ($null, $deduction_id) = split /--/, $form->{"deduct_$i"};
    if ($deduction_id) {
      $form->{"percent_$i"} = $form->parse_amount($myconfig, $form->{"percent_$i"});
      $form->{"percent_$i"} /= 100;

      $sth->execute($form->{id}, $deduction_id, $form->{"withholding_$i"}, $form->{"percent_$i"}) || $form->dberror($query);
    }
  }
  $sth->finish;
  
  $form->remove_locks($myconfig, $dbh, 'hr');

  $dbh->commit;
  $dbh->disconnect;

}


sub delete_deduction {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect_noauto($myconfig);

  # delete deduction
  my $query = qq|DELETE FROM deduction
	         WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);
  
  $query = qq|DELETE FROM deductionrate
	         WHERE trans_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);
  
  $query = qq|DELETE FROM deduct
	         WHERE trans_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  $form->remove_locks($myconfig, $dbh, 'hr');

  $dbh->commit;
  $dbh->disconnect;

}


sub get_wage {
  my ($self, $myconfig, $form) = @_;

  my $dbh = $form->dbconnect($myconfig);
  my $query;
  my $sth;
  my $ref;

  $form->remove_locks($myconfig, $dbh, 'hr');

  my %defaults = $form->get_defaults($dbh, \@{['precision', 'company']});
  for (keys %defaults) { $form->{$_} = $defaults{$_} }

  if ($form->{id}) {
    $query = qq|SELECT w.*,
		 c.accno, c.description AS accno_description,
		 l.description AS accno_translation,
		 c1.accno AS defer, c1.description AS defer_description,
		 l1.description AS defer_translation
                 FROM wage w
		 LEFT JOIN chart c ON (c.id = w.chart_id)
		 LEFT JOIN chart c1 ON (c1.id = w.defer)
		 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
		 LEFT JOIN translation l1 ON (l1.trans_id = c1.id AND l1.language_code = '$myconfig->{countrycode}')
                 WHERE w.id = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    $ref = $sth->fetchrow_hashref(NAME_lc);
    $ref->{"accno_description"} = $ref->{"accno_translation"} if $ref->{"accno_translation"};
    $ref->{"defer_description"} = $ref->{"defer_translation"} if $ref->{"defer_translation"};
    for (keys %$ref) { $form->{$_} = $ref->{$_} }

    $sth->finish;
  
    # check if orphaned
    $form->{status} = "";
    $query = qq|SELECT count(*) FROM employeewage
                WHERE wage_id = $form->{id}|;
    if (! $dbh->selectrow_array($query)) {
      $form->{status} = 'orphaned';
    }

    $form->create_lock($myconfig, $dbh, $form->{id}, 'hr');

  }

  $query = qq|SELECT c.accno, c.description,
	      l.description AS translation
	      FROM chart c
	      LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
	      WHERE c.charttype = 'A'
	      AND c.link LIKE '%AP\_amount%'
	      ORDER BY c.accno|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ref->{description} = $ref->{translation} if $ref->{translation};
    push @{ $form->{accounts} }, $ref;
  }
  $sth->finish;
  
  $dbh->disconnect;

}


sub wages {
  my ($self, $myconfig, $form) = @_;

  my $dbh = $form->dbconnect($myconfig);

  my %defaults = $form->get_defaults($dbh, \@{['precision', 'company']});
  for (keys %defaults) { $form->{$_} = $defaults{$_} }

  my $query = qq|SELECT w.*,
                 c.accno, c1.accno AS defer
                 FROM wage w
		 JOIN chart c ON (w.chart_id = c.id)
		 LEFT JOIN chart c1 ON (c1.id = w.defer)
		 ORDER BY 2|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);
  
  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_wage} }, $ref;
  }
  
  $sth->finish;
  $dbh->disconnect;

}


sub save_wage {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect_noauto($myconfig);

  my $query;
  my $sth;

  if (! $form->{id}) {
    my $uid = localtime;
    $uid .= $$;

    $query = qq|INSERT INTO wage (description)
                VALUES ('$uid')|;
    $dbh->do($query) || $form->dberror($query);
    
    $query = qq|SELECT id FROM wage
                WHERE description = '$uid'|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    ($form->{id}) = $sth->fetchrow_array;
    $sth->finish;
  }

  $form->{exempt} *= 1;
  $form->{amount} = $form->parse_amount($myconfig, $form->{amount});
  ($form->{accno}) = split /--/, $form->{accno};
  ($form->{defer}) = split /--/, $form->{defer};
  
  $query = qq|UPDATE wage SET
	      description = |.$dbh->quote($form->{description}).qq|,
	      defer =
	           (SELECT id FROM chart
	            WHERE accno = '$form->{defer}'),
	      exempt = '$form->{exempt}',
	      chart_id =
	           (SELECT id FROM chart
	            WHERE accno = '$form->{accno}'),
	      amount = $form->{amount}
	      WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  $form->remove_locks($myconfig, $dbh, 'hr');

  $dbh->commit;
  $dbh->disconnect;

}


sub delete_wage {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect_noauto($myconfig);

  # delete deduction
  my $query = qq|DELETE FROM wage
	         WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);
 
  $form->remove_locks($myconfig, $dbh, 'hr');

  $dbh->commit;
  $dbh->disconnect;

}


1;

