<?php

// gets select of All Customers
function selcus(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT DISTINCT cname FROM customers ORDER BY cname") or die (mysqli_error($db->connection));
while ($row = mysqli_fetch_array($sql,  MYSQLI_ASSOC)){
    echo '<option value="'.$row['cname'].'" >'.$row['cname'].'</option>';
    }
$db->close_connection();	
}

// gets all Customers in json for autocomplete
function allcustomers(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT DISTINCT cname FROM customers ORDER BY cname;") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql,  MYSQLI_ASSOC)){
    $row_array['value'] = $row['cname'];
    array_push($larray, $row_array); 
    }
return json_encode($larray);    
$db->close_connection();
}

// gets select of All imeino in stock
function selimei(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT imeino FROM devstock WHERE jcno=0 ORDER BY imeino") or die (mysqli_error($db->connection));
while ($row = mysqli_fetch_array($sql,  MYSQLI_ASSOC)){
    echo '<option value="'.$row['imeino'].'" >'.$row['imeino'].'</option>';
    }
$db->close_connection();	
}


// gets all Jobcards in json for autocomplete
function alljcards(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT jcno, jdate, cname, regno  FROM jcards ORDER BY jcno DESC;") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql,  MYSQLI_ASSOC)){
	$jd = substr($row['jdate'],8,2)."-".substr($row['jdate'],5,2)."-".substr($row['jdate'] ,0,4);
    $row_array['value'] = $row['jcno'].' - '.$jd.' - '.$row['cname'].' - '.$row['regno'];
    array_push($larray, $row_array); 
    }
return json_encode($larray);    
$db->close_connection();
}

// gets all insurance invoices in json for autocomplete
function allinsbooks(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT insno, idate, insurer, regno  FROM insrecs ORDER BY insno DESC;") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql,  MYSQLI_ASSOC)){
	$jd = substr($row['idate'],8,2)."-".substr($row['idate'],5,2)."-".substr($row['idate'] ,0,4);
    $row_array['value'] = $row['insno'].' - '.$jd.' - '.$row['insurer'].' - '.$row['regno'];
    array_push($larray, $row_array); 
    }
return json_encode($larray);    
$db->close_connection();
}

// gets all Technicians in json for autocomplete
function alltechs(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT DISTINCT techn FROM jcards ORDER BY techn;") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $row_array['value'] = $row['techn'];
    array_push($larray, $row_array); 
    }
return json_encode($larray);    
$db->close_connection();
}

// gets select of all income accounts
function selincomes(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT accname FROM incomeaccounts ORDER BY accname") or die (mysqli_error($db->connection));
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    echo '<option value="'.$row['accname'].'" >'.$row['accname'].'</option>';
    }
$db->close_connection();	
}


// ---------------------------------------------------------------------
//  Increment Document No.
// ---------------------------------------------------------------------
function getnewno($column){
$db=new MySQLDatabase();
$db->open_connection();
$tablename="refnos"; 
$q=mysqli_query($db->connection, "SELECT $column from $tablename" );
while ($row = mysqli_fetch_array($q, MYSQLI_ASSOC)) { $result=intval($row[$column]); }
$result=$result+1;
$q1=mysqli_query($db->connection, "UPDATE $tablename set `$column`='$result'" ) or die(mysqli_error($db->connection));
return $result;
$db->close_connection();
}

// gets select of recpnos from recptrans
function selallrecps(){
$db=new MySQLDatabase();
$db->open_connection();
echo '<option value=""></option>';
$sql = mysqli_query($db->connection, "SELECT rdate, recpno, cname, rtype FROM recptrans WHERE revsd=0 ORDER BY recpno DESC;") or die (mysqli_error($db->connection));
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $rdate = substr($row['rdate'],8,2)."-".substr($row['rdate'],5,2)."-".substr($row['rdate'] ,0,4);
    echo '<option value="'.$row['recpno'].'" >'.$row['recpno'].' - '.$rdate.' - '.$row['cname'].' - '.$row['rtype'];
    }
$db->close_connection();	
}

// gets json of recpnos from recptrans
function allrecps(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT rdate, recpno, cname, rtype FROM recptrans WHERE revsd=0 ORDER BY recpno DESC;") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $rdate = substr($row['rdate'],8,2)."-".substr($row['rdate'],5,2)."-".substr($row['rdate'] ,0,4);
    $row_array['value'] = $row['recpno'].' - '.$rdate.' - '.$row['cname'].' - '.$row['rtype'];
    array_push($larray, $row_array); 
    }
return json_encode($larray); 
$db->close_connection();
}

// gets select of all Bank accounts
function selallbanks(){
$db=new MySQLDatabase();
$db->open_connection();
$lb = '';	
if($_SESSION['reusergroup'] === 2){ $sql = mysqli_query($db->connection, "SELECT bacc FROM banks WHERE bc = 'C' ORDER BY bacc") or die (mysqli_error($db->connection)); }
else { $sql = mysqli_query($db->connection, "SELECT bacc FROM banks ORDER BY bacc") or die (mysqli_error($db->connection)); }
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $lb .= '<option value="'.$row['bacc'].'" >'.$row['bacc'].'</option>';
}
return $lb;	
$db->close_connection();
};


// -------------------------------------------------------------------------
//convert number to words
// -------------------------------------------------------------------------
function convert_number_to_words($number) {
$db=new MySQLDatabase();
$db->open_connection(); 
    $hyphen      = '-';
    $conjunction = ' and ';
    $separator   = ', ';
    $negative    = 'negative ';
    $decimal     = ' point ';
    $dictionary  = array(
        0                   => 'zero',
        1                   => 'one',
        2                   => 'two',
        3                   => 'three',
        4                   => 'four',
        5                   => 'five',
        6                   => 'six',
        7                   => 'seven',
        8                   => 'eight',
        9                   => 'nine',
        10                  => 'ten',
        11                  => 'eleven',
        12                  => 'twelve',
        13                  => 'thirteen',
        14                  => 'fourteen',
        15                  => 'fifteen',
        16                  => 'sixteen',
        17                  => 'seventeen',
        18                  => 'eighteen',
        19                  => 'nineteen',
        20                  => 'twenty',
        30                  => 'thirty',
        40                  => 'fourty',
        50                  => 'fifty',
        60                  => 'sixty',
        70                  => 'seventy',
        80                  => 'eighty',
        90                  => 'ninety',
        100                 => 'hundred',
        1000                => 'thousand',
        1000000             => 'million',
        1000000000          => 'billion',
        1000000000000       => 'trillion',
        1000000000000000    => 'quadrillion',
        1000000000000000000 => 'quintillion'
    );
   
    if (!is_numeric($number)) {
        return false;
    }
   
    if (($number >= 0 && (int) $number < 0) || (int) $number < 0 - PHP_INT_MAX) {
        // overflow
        trigger_error(
            'convert_number_to_words only accepts numbers between -' . PHP_INT_MAX . ' and ' . PHP_INT_MAX,
            E_USER_WARNING
        );
        return false;
    }

    if ($number < 0) {
        return $negative . convert_number_to_words(abs($number));
    }
   
    $string = $fraction = null;
   
    if (strpos($number, '.') !== false) {
        list($number, $fraction) = explode('.', $number);
    }
   
    switch (true) {
        case $number < 21:
            $string = $dictionary[$number];
            break;
        case $number < 100:
            $tens   = ((int) ($number / 10)) * 10;
            $units  = $number % 10;
            $string = $dictionary[$tens];
            if ($units) {
                $string .= $hyphen . $dictionary[$units];
            }
            break;
        case $number < 1000:
            $hundreds  = $number / 100;
            $remainder = $number % 100;
            $string = $dictionary[$hundreds] . ' ' . $dictionary[100];
            if ($remainder) {
                $string .= $conjunction . convert_number_to_words($remainder);
            }
            break;
        default:
            $baseUnit = pow(1000, floor(log($number, 1000)));
            $numBaseUnits = (int) ($number / $baseUnit);
            $remainder = $number % $baseUnit;
            $string = convert_number_to_words($numBaseUnits) . ' ' . $dictionary[$baseUnit];
            if ($remainder) {
                $string .= $remainder < 100 ? $conjunction : $separator;
                $string .= convert_number_to_words($remainder);
            }
            break;
    }
   
    if (null !== $fraction && is_numeric($fraction)) {
        $string .= $decimal;
        $words = array();
        foreach (str_split((string) $fraction) as $number) {
            $words[] = $dictionary[$number];
        }
        $string .= implode(' ', $words);
    }
   
    return $string;
	$db->close_connection();
}


// gets select of paynos from paytrans
function selpays(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT paytrans.pdate, paytrans.payno, suppliers.sname FROM paytrans, suppliers WHERE paytrans.supp = suppliers.id AND paytrans.revsd = 0 AND paytrans.supp > 0 
					UNION SELECT paytrans.pdate, paytrans.payno, reporders.cname FROM paytrans, reporders WHERE paytrans.repono = reporders.repono AND paytrans.repono > 0 ORDER BY payno DESC") or die (mysqli_error($db->connection));
echo '<option value=""></option>';
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $pdate = substr($row['pdate'],8,2)."-".substr($row['pdate'],5,2)."-".substr($row['pdate'] ,0,4);
    echo '<option value="'.$row['payno'].'" >'.$row['payno'].' - '.$pdate.' - '.$row['sname'].'</option>';
    }
$db->close_connection();
}

// gets json of paynos from paytrans
function allpayms(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT pdate, payno, chqno FROM paytrans WHERE revsd = 0 ORDER BY payno DESC") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $pdate = substr($row['pdate'],8,2)."-".substr($row['pdate'],5,2)."-".substr($row['pdate'] ,0,4);
    $row_array['value'] = $row['payno'].' - '.$pdate.' - '.$row['chqno'];
    array_push($larray, $row_array); 
    }
return json_encode($larray);
$db->close_connection(); 
}

// gets select of all Expense accounts
function selallexpaccs(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT accname FROM expenseaccounts ORDER BY accname") or die (mysqli_error($db->connection));
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    echo '<option value="'.$row['accname'].'" >'.$row['accname'].'</option>';
    }
$db->close_connection();	
}

// get Expense Account Name
function getexpaccname($aid){
$db=new MySQLDatabase();
$db->open_connection();
if($aid) {
$sql = mysqli_query($db->connection, "SELECT accname FROM expenseaccounts WHERE id = '$aid'") or die (mysqli_error($db->connection));
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    return $row['accname'];
    }
}else{ return '';}	
$db->close_connection();
}

// gets all suppliers in json for autocomplete
function allsuppliers(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT sname FROM suppliers ORDER BY sname;") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $row_array['value'] = $row['sname'];
    array_push($larray, $row_array); 
    }
return json_encode($larray);  
$db->close_connection();  
}

// gets select of suppliers
function selsupplier(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT id, sname FROM suppliers ORDER BY sname") or die (mysqli_error($db->connection));
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    echo '<option value="'.$row['id'].'" >'.$row['sname'].'</option>';
    }
$db->close_connection();	
}

// get name of supplier of id $supp
function getsupplier($supp){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT sname FROM suppliers WHERE id = '$supp';") or die (mysqli_error($db->connection));
if ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    return $row['sname'];
    }
$db->close_connection();	
}

// gets json of Reposession order nos from reporders
function allreponos(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT odate, repono, cname FROM reporders ORDER BY repono DESC") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $odate = substr($row['odate'],8,2)."-".substr($row['odate'],5,2)."-".substr($row['odate'] ,0,4);
    $row_array['value'] = $row['repono'].' - '.$odate.' - '.$row['cname'];
    array_push($larray, $row_array); 
    }
return json_encode($larray);
$db->close_connection(); 
}

// gets all Expense Categories in json for autocomplete
function allexpcats(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT DISTINCT ecat FROM paytrans ORDER BY ecat;") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $row_array['value'] = $row['ecat'];
    array_push($larray, $row_array); 
    }
return json_encode($larray);    
$db->close_connection();
}

// gets all Auctioneers in json for autocomplete
function allauctioneers(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT DISTINCT auct FROM reporders ORDER BY auct;") or die (mysqli_error($db->connection));
$larray = array();
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $row_array['value'] = $row['auct'];
    array_push($larray, $row_array); 
    }
return json_encode($larray);    
$db->close_connection();
}



// gets select of dexpnos (Expense Vouchers) from dexptrans
function selexpvouchs(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT dexpno, ddate, expacc FROM dexptrans WHERE revsd = 0 ORDER BY dexpno DESC") or die (mysqli_error($db->connection));
echo '<option value=""></option>';
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    $ddate = substr($row['ddate'],8,2)."-".substr($row['ddate'],5,2)."-".substr($row['ddate'] ,0,4);
    echo '<option value="'.$row['dexpno'].'" >'.$row['dexpno'].' - '.$ddate.' - '.$row['expacc'].'</option>';
    }
$db->close_connection();	
}

// gets select of All bank JVs
function selbankjvs(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT jvno, jdate, rmks FROM bktrans ORDER BY jvno DESC;") or die (mysqli_error($db->connection));
echo '<option value=""></option>';
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
	$jdate = substr($row['jdate'],8,2)."-".substr($row['jdate'],5,2)."-".substr($row['jdate'] ,0,4);
    echo '<option value="'.$row['jvno'].'" >'.$row['jvno'].' - '.$jdate.' - '.$row['rmks'].'</option>';
    }
$db->close_connection();	
}

// gets select of insurances billed for certificates
function selcuscert(){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT insno, idate, insurer, IFNULL( agpno, ' ' ) AS agpno, 0 AS insext
FROM insrecs WHERE (invno IS NOT NULL)
UNION SELECT insno, idate, insurer, IFNULL( agpno1, ' ' ) AS agpno, 1 AS insext
FROM insrecs WHERE (invno1 IS NOT NULL) 
UNION SELECT insno, idate, insurer, IFNULL( agpno2, ' ' ) AS agpno, 2 AS insext
FROM insrecs WHERE (invno2 IS NOT NULL) 
UNION SELECT insno, idate, insurer, IFNULL( agpno3, ' ' ) AS agpno, 3 AS insext
FROM insrecs WHERE (invno3 IS NOT NULL) 
ORDER BY idate DESC ") or die (mysqli_error($db->connection));
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    echo '<option value="'.$row['insno'].'-'.$row['insext'].'" >'.$row['insno'].' - '.$row['insurer'].' - '.$row['agpno'].'</option>';
    }
$db->close_connection();	
}

// get PAID status of insurance invoice
function getinvstat($inv){
$db=new MySQLDatabase();
$db->open_connection();
$sql = mysqli_query($db->connection, "SELECT recpno FROM insinvoices WHERE invno = '$inv';") or die (mysqli_error($db->connection));
if ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
    return ($row['recpno']!=='0' ? 'Paid' : '');
    }
$db->close_connection();	
}
?>

