<?php
// connect to db
function connect() {
  $server = "localhost";
  $user = "autoAdmin";
  $pass = "happy8";
  $db = "autotech";
	$mysqli = new mysqli($server, $user, $pass, $db);
	if ($mysqli->connect_errno)	{
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
	}
    return $mysqli;
}
  // clean inputs
  function h($dirty) {
      $clean = htmlspecialchars($dirty,ENT_QUOTES);
      return $clean;
  }

  function runningTotal() {
    $mysqli = connect();
    $stmt = $mysqli->prepare('SELECT invoice_school, invoice_cust, invoice_inst, PO
    FROM tbl_invoice');
    $stmt->execute();
    $stmt->bind_result($db_school,$db_cust,$db_inst,$db_po);
    while($stmt->fetch()) {
      if($db_po > 0){
        $poCnt++;
        $grand = $grand + $db_inst;
      }
      else {
        $total = $total + $db_school;
        $total = $total + $db_cust;
        $total = $total + $db_inst;
      }
    }
    $stmt->close();
    $mysqli->close();
    if($poCnt > 0) {
      $output = (3000 - $total) + $grand;
    }
    else {
      $output = 3000 - $total;
    }
    return $output;
  }


  function grabInvoice($mySearch) {
    $mysqli = connect();
    $stmt = $mysqli->prepare('SELECT invoice_id, invoice_num, invoice_date,
    invoice_school, invoice_cust, invoice_inst, invoice_memo, PO
  	FROM tbl_invoice
    WHERE (invoice_num LIKE ?)
  	ORDER BY invoice_id DESC');
    $stmt->bind_param('s',$mySearch);
  	$stmt->execute();
  	$stmt->bind_result($db_id,$db_num,$db_date,$db_school,$db_cust,$db_inst,$db_memo,$PO);
    $out = '<table class="table table-bordered table-hover table-striped"><thead><tr><th>Invoice Number</th><th>Date</th><th>School Vehicle Price</th><th>Customer Service</th><th>Instructional Supplies</th><th>Total</th><th>Memo</th></tr></thead>';
  	while($stmt->fetch()) {
      $total = 0;
      if($PO == 1) {
        $totalPO = $totalPO + $db_inst;
        $out .= '<tr><td>'.$db_num.'</td><td>'.$db_date.'</td><td class="text-info"> $'.$db_school.'</td><td class="text-info"> $'.$db_cust.'</td><td class="text-info"> $'.$db_inst.'</td><td class="text-success">+ $'.$totalPO.'</td><td>'.$db_memo.'</td></tr>';
      }
      else {
        $total = $total + $db_school;
        $total = $total + $db_cust;
        $total = $total + $db_inst;
        $out .= '<tr><td>'.$db_num.'</td><td>'.$db_date.'</td><td class="text-info"> $'.$db_school.'</td><td class="text-info"> $'.$db_cust.'</td><td class="text-info"> $'.$db_inst.'</td><td class="text-danger">- $'.$total.'</td><td>'.$db_memo.'</td></tr>';
      }
  	}
    $out .= '</table>';
  	$stmt->close();
  	$mysqli->close();
    return $out;
  }

  function addInvoice($InvNum,$InvDate,$InvSchool,$InvCust,$InvInst,$InvMemo,$PO) {
		$mysqli = connect();
		$stmt = $mysqli->prepare('INSERT INTO tbl_invoice
		(invoice_num, invoice_date, invoice_school, invoice_cust, invoice_inst, invoice_memo,PO) VALUES (?,?,?,?,?,?,?)');
		$stmt->bind_param('isdddsi',$InvNum,$InvDate,$InvSchool,$InvCust,$InvInst,$InvMemo,$PO);
		$stmt->execute();
		$stmt->close();
    $mysqli->close();
    $addInvoiceYes = '<h1 class="text-success">Invoice successfully added!</h1>';
    return $addInvoiceYes;
  }


  function addPO($InvNum,$InvDate,$InvInst,$InvMemo,$PO) {
    $mysqli = connect();
    $no = 0;
    $stmt = $mysqli->prepare('INSERT INTO tbl_invoice
    (invoice_num, invoice_date, invoice_school, invoice_cust, invoice_inst, invoice_memo,PO) VALUES (?,?,?,?,?,?,?)');
    $stmt->bind_param('isiidsi',$InvNum,$InvDate,$no,$no,$InvInst,$InvMemo,$PO);
    $stmt->execute();
    $stmt->close();
    $mysqli->close();
    $addInvoiceYes = '<h1 class="text-success">Invoice successfully added!</h1>';
    return $addInvoiceYes;
  }

?>