<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
# ----------- Main Routine ---------
    date_default_timezone_set('Australia/Melbourne');
    $dbh = new mysqli('localhost','ansyd','topped','estate');
    if ($dbh->connect_error) {
       die('Connect Error(' . $dbh->connect_errno . ')'
           . $dbh->connect_error);
    }
    if (isset($_GET['u'])) {
        $request = $_GET['u'];
        if ($request == "Vendors") {
            showVendors();
        } elseif ($request == "GLSegments") {
            showGLSegments();
        } elseif (($request == "Leases") || ($request == "leases")) {
            showLeaseDetails();
        } elseif ($request == "AP") {
            showOpenItems();
        } elseif ($request == "work_list") {
            showOpenWork();
        } elseif ($request == "properties") {
            showPremises();
        } elseif ($request == "Updates") {
            showUpdates();
        } elseif ($request == "statuses") {
            showStatus();
        } elseif ($request == "areas") {
            showAreas();
        } elseif ($request == "inspections") {
            showInspections();
        } elseif ($request == "taxcodes") {
            showTaxCodes();
        } elseif ($request == "tasks") {
            showTasks();
        } else {
            logError($request);
        }
    } elseif (isset($_GET['leases'])) {
        $code = $_GET['leases'];
        showLeases($code);
    } elseif (isset($_GET['vendor'])) {
        $code = $_GET['vendor'];
        lookupVendor($code);
    } else {
        $postdata = file_get_contents('php://input');
        $stack = parseDocment($postdata);
        if (isset($stack['MESTYPE'][0]) ? $stack['MESTYPE'][0] : null) {
            $messtype = $stack['MESTYPE'][0];
            switch ($messtype) {
                case "INVOICE":
                    $ok = storeInvoice($stack);
                    break;
                case "INSPECTION":
                    $ok = storeInspection($stack);
                    break;
                case "WORK":
                    $ok = storeWork($stack);
                    break;
                case "TASK":
                    $ok = storeTask($stack);
                    break;
                case "SCHEDULE":
                    $ok = storeSchedule($stack);
                    break;
                case "NOSERIES":
                    $ok = storeNoSeries($stack);
                    break;
                case "LEASE":
                    $ok  = storeLease($stack);
                    break;
                default:
                    $s = print_r($stack, true);
                    logError($s);
                    $ok = false;
                break;
            }
            if ($ok == true) {
                http_response_code(200);
            } else {
                http_response_code(404);
            }
        } else {
            logError($postdata);
            http_response_code(404);
        }
    }
    $dbh->close();

#
# ------ Functions -----------
    function logError($error) {
       #$filename = uniqid() . '_err.txt';
       $filename = "error.txt";
       #file_put_contents($filename,$error);
       $fh = fopen($filename,"a+");
       fwrite($fh,"$error\n");
       fclose($fh);
    }

   function formatDate($inDate) {
       $l = strlen($inDate);
       if ($l == 6) {
           $yy = substr($inDate,0,2);
           $yy += 2000;
           $mm = substr($inDate,2,2);
           $dd = substr($inDate,4,2);
       } elseif ($l == 8) {
           $yy = substr($inDate,0,4);
           $mm = substr($inDate,4,2);
           $dd = substr($inDate,6,2);
       }
       $outDate = $yy . "-" . $mm . "-" . $dd;
       return $outDate;
   }

    function lookupTax($value,$taxcode) {
        global $dbh;
        $query = "select tax_rate from tax_codes where code = '$taxcode'";
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("StoreLease:" . $e->getMessage());
            return 0;
        }
        $numrows = mysqli_num_rows($result);
        if ($numrows > 0) {
            $row = $result->fetch_row();
            $taxamt = $value * ($row[0] / 100);
        } else {
            $taxamt = 0;
        }
        return $taxamt;
    }

   function getNextID($type) {
        global $dbh;
        $query = "select * from no_series where NS_CODE = '$type'";
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("getNextID:" . $e->getMessage());
            return false;
        }
        $numrows = mysqli_num_rows($result);
        if ($numrows > 0) {
            $row = $result->fetch_row();
            $nextnum = $row[3];
            $nextnum++;
            $prefix = $row[1];
            $suffix = $row[2];
            $len = $row[4];
            $query ="update no_series set NS_LAST_NUMBER = $nextnum";
            $query .= " where NS_CODE = '$type'";
        } else {
            $nextnum = 1;
            $prefix = "";
            $suffix = "";
            $len = 6;
            $query = "insert into no_series values('$type','$prefix','$suffix',";
            $query .= "$nextnum,$len)";
        }
        mysqli_free_result($result);
        try {
             mysqli_query($dbh, $query);
        } catch(Exception $e) {
            logError("getNextID:" . $e->getMessage());
            return false;
        }
        $len = $len - strlen($prefix) - strlen($suffix);
        $nextid = str_pad($nextnum,$len,0,STR_PAD_LEFT);
        $nextid = $prefix . $nextid . $suffix;
        return $nextid;
    }

   function showVendors() {
       global $dbh;
       $query = "select * from vendor where active = 'Y' order by name";
       $result = $dbh->query($query);
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Vendor');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   if ($row[$i]) {
                       $finfo = $result->fetch_field_direct($i);
                       $field = $finfo->name;
                       $type = $finfo->type;
                       $attrib = $dom->createAttribute('Type');
                       $attrib->value = $type;
                       $value = htmlentities($row[$i]);
                       $elem = $dom->createElement($field,$value);
                       $elem->appendChild($attrib);
                       $node->appendChild($elem);
                   }
               }
           }
           echo $dom->saveXML();
       }
   }

   function showGLSegments() {
       global $dbh;
       $query = "select * from gl_segments_v where active_gl_code = 'Y'";
       $query .= " and posting = 'Y'";
       $query .= " order by gl_description,segment_description";
       $query .= ",subsegment_description";
       $result = $dbh->query($query);
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('GLSegment');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $name = htmlspecialchars($row[1]);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       }
   }

   function showOpenItems() {
       global $dbh;
       $query = "select a.*,b.*,ifnull(ah_post_date,'N') as post_flag";
       $query .= " from ap_headers a inner join vendor b";
       $query .= " on a.ah_supplier = b.code";
       $query .= " where ah_payment_id is null";
       $query .= " order by ah_payment_date,ah_supplier ";
       $result = $dbh->query($query) or die (http_response_code(404));
       $numrows = mysqli_num_rows($result);
       if ($numrows > 0) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('PayableItem');
           while ($row = $result->fetch_row()) {
               $node = $dom->appendChild($node);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   if (!$row[$i]) {
                       $row[$i] = 'Null';
                   }
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           http_response_code(404);
       }
   }

   function showLeases($agent) {
       global $dbh;
       $query = "select a.lease_number,lm_leasee_name,rent_amount,c.end_date";
       $query .= ",e.description as 'payment_period',f.tax_rate";
       $query .= " from lease_income_lines a inner join lease_master ";
       $query .= " on lm_lease_number = a.lease_number inner join ";
       $query .= " (select ah_customer,max(ar_end_date) as end_date from ";
       $query .= " ar_transactions a inner join ar_headers b on a.ar_trans_id";
       $query .= " = b.ah_trans_id group by ah_customer) c on c.ah_customer =";
       $query .= " lease_number inner join lease_income_types d on d.lease_number = ";
       $query .= " lm_lease_number and d.income_type = a.income_type";
       $query .= " inner join payment_terms e on e.code = d.pay_freq";
       $query .= " inner join tax_codes f on d.tax_code = f.code";
       $query .= " where lm_agent = '$agent' and lm_status = 'Y'";
       $query .= " and effective_date = (select max(effective_date) from";
       $query .= " lease_income_lines where lease_number = lm_lease_number)";
       $query .= " Order by lm_leasee_name";
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Document');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $node = $dom->appendChild($node);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   if ($value) {
                       $value = htmlentities($row[$i]);
                   } else {
                       $value = "";
                   }
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           $err = mysqli_error($dbh);
           print $err;
           http_response_code(404);
       }
   }

   function showLeaseDetails() {
       global $dbh;
       $query = "select a.lease_number,lm_leasee_name,rent_amount,effective_date,name, c.description";
       $query .= ",lm_bond,p.code from lease_income_lines a inner join lease_income_types b on a.lease_number";
       $query .= " = b.lease_number and a.income_type = b.income_type";
       $query .= " inner join lease_master on lm_lease_number =";
       $query .= " a.lease_number inner join properties p on p.code = lm_premises inner join payment_terms c";
       $query .= " on c.code = b.pay_freq where lm_status = 'Y' and effective_date = (select min(effective_date)";
       $query .= " effective_date from lease_income_lines where lease_number = lm_lease_number) Order by a.lease_number";
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Document');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $node = $dom->appendChild($node);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   if ($row[$i]) {
                       $value = htmlentities($row[$i]);
                   } else {
                       $value = "";
                   }
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           $err = mysqli_error($dbh);
           print "<li>" . $err;
           http_response_code(404);
       }
   }

   function showOpenWork() {
       global $dbh;
       $id = "";
       if (array_key_exists('id',$_GET)) {
           $id = $_GET['id'];
       }
       $jn = "";
       if (array_key_exists('job',$_GET)) {
           $jn = $_GET['job'];
       }
       $p = "";
       if (isset($_GET['p']) ? $_GET['p'] : null) {
           $p = $_GET['p'];
       }
       $query = " select a.*,c.name,b.description from work_list a ";
       $query .= " inner join (select code, description from sundry_codes";
       $query .= " where type = 'ST') b on a.status = b.code";
       $query .= " left outer join properties c on c.code = a.premises";
       $query .= " where status < 8";
       if (($id != "new") && ($id != "")) {
           $query .= " and work_id = '$id'";
       } elseif ($jn == "new") {
           $query .= " and (job_no is null or job_no = ' ')";
       } elseif ($jn != "") {
           $query .= " and job_no = '$jn'";
       }
       if ($p != "") {
           $query .= " and a.premises = '$p'";
       }
       $query .= " order by a.work_id";
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Work');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $node = $dom->appendChild($node);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   if (!$row[$i]) {
                       $row[$i] = "None";
                   }
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           $query = "select a.work_id,line_no,note_date,work_requirement from work_requirements a";
           $query .= " inner join work_list b on a.work_id = b.work_id";
           $query .= " where b.status < 8";
           $rslt = $dbh->query($query);
           while ($trow = $rslt->fetch_row()) {
               $tnode = $dom->appendChild($node);
               $fldno = mysqli_num_fields($rslt);
               for ($x=0; $x < $fldno;$x++) {
                   $finfo = $rslt->fetch_field_direct($x);
                   $field = $finfo->name;
                   $value = htmlentities($trow[$x]);
                   $elem = $dom->createElement($field,$value);
                   $tnode->appendChild($elem);
               }
           }
           $query = "select a.work_id,line_no,scheduled_date,task from work_schedule a";
           $query .= " inner join work_list b on a.work_id = b.work_id";
           $query .= " where b.status < 8";
           $rslt = $dbh->query($query) or logError(mysqli_error($dbh));
           while ($trow = $rslt->fetch_row()) {
               $tnode = $dom->appendChild($node);
               $fldno = mysqli_num_fields($rslt);
               for ($x=0; $x < $fldno;$x++) {
                   $finfo = $rslt->fetch_field_direct($x);
                   $field = $finfo->name;
                   $value = htmlentities($trow[$x]);
                   $elem = $dom->createElement($field,$value);
                   $tnode->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           $err = mysqli_error($dbh);
           logError( $err );
           http_response_code(404);
       }
   }

   function showPremises() {
       global $dbh;
       $query = "select * from properties where active = 'Y'";
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Premises');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $tnode = $dom->appendChild($node);
               $fldno = mysqli_num_fields($result);
               for ($x=0; $x < $fldno;$x++) {
                   $finfo = $result->fetch_field_direct($x);
                   $field = $finfo->name;
                   if (!$row[$x]) {
                       $row[$x] = "None";
                   }
                   $value = htmlentities($row[$x]);
                   $elem = $dom->createElement($field,$value);
                   $tnode->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           $err = mysqli_error($dbh);
           print $err;
           http_response_code(404);
       }
   }

   function showAreas() {
       global $dbh;
       $query = " select a.* from premises_area a inner join properties b on a.premises = b.code";
       $query .= " where b.active = 'Y'";
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Areas');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $node = $dom->appendChild($node);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   if (!$row[$i]) {
                       $row[$i] = "Null";
                   }
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           $err = mysqli_error($dbh);
           print $err;
           http_response_code(404);
       }
   }

   function addData($s,$key,$val) {
       if (!array_key_exists($key,$s)) {
           $s[$key] = array();
       }
       $s[$key][] = $val;
       return $s;
   }

   function parseDocment($dataIn) {
       global $dbh;
       $stack = array();
       $p = xml_parser_create();
       xml_parse_into_struct($p,$dataIn,$vals,$tags);
       xml_parser_free($p);
       foreach ($vals as $val) {
           if ($val['type'] == "complete") {
               $t = $val['tag'];
               if (isset($val['value']) ? $val['value'] : null) {
                   $v = $val['value'];
                   $stack  = addData($stack,$t,$v);
               }
           }
       }
       return $stack;
    }

    function lookupSupplier($code) {
        global $dbh;
        $query = "select name from vendor where code = '$code'";
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("lookupSupplier:" . $e->getMessage());
            return 0;
        }
        $numrows = mysqli_num_rows($result);
        if ($numrows > 0) {
           $row = $result->fetch_row();
           return $row[0];
        } else {
           return "";
        }
    }

    function lookupSegment($code) {
        global $dbh;
        $query = "select gl_description from gl_segments_v where gl_segment_code = '$code'";
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("StoreLease:" . $e->getMessage());
            return 0;
        }
        $numrows = mysqli_num_rows($result);
        if ($numrows > 0) {
           $row = $result->fetch_row();
           return $row[0];
        } else {
           return "";
        }
    }

    function storeInvoice($s) {
        global $dbh;
        $invnum = $s['INVOICENUMBER'][0];
        $supplier = $s['VENDOR'][0];
        $invdate = formatDate($s['INVOICEDATE'][0]);
        $query = "select * from ap_headers where ah_supplier = '$supplier'";
        $query .= " and ah_invoice_num = '$invnum' and ah_invoice_date = ";
        $query .= "'$invdate'";
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("StoreLease:" . $e->getMessage());
            return false;
        }
        $row = $result->fetch_row();
        if ($row) {
            mysqli_free_result($result);
            return false;
        }
        $paydate = formatDate($s['PAYMENTDATE'][0]);
        $invamt = $s['INVOICEVALUE'][0];
        if (!$invamt) { $invamt = 0; }
        $apid = GetNextID("AP");
        $query = "insert into ap_headers values ('$apid','$invdate','$invnum'";
        $query .= ",'$supplier','$paydate',$invamt,null,null)";
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("StoreLease:" . $e->getMessage());
            return false;
        }
        foreach ($s['INVOICELINENUMBER'] as $key => $value) {
            $desc = $s['ITEMDESCRIPTION'][$key];
            $glcode = $s['GLSEGMENT'][$key];
            $qty = $s['QUANTITY'][$key];
            $price = $s['PRICE'][$key];
            $linevalue = $s['LINEVALUE'][$key];
            $tax = $s['TAXCODE'][$key];
            $taxamt = lookupTax($linevalue,$tax);
            $query = "insert into ap_transactions values('$apid',$key,";
            $query .= "'$desc',$qty,$price,$linevalue,'$glcode','$tax',$taxamt";
            $query .= ",'',1)";
            try {
            $result = $dbh->query($query);
            } catch(Exception $e) {
                logError("StoreLease:" . $e->getMessage());
                return false;
            }
        }
        if (array_key_exists('NARRATION',$s)) {
            $desc = $s['NARRATION'][0];
        } else {
            $desc = "";
        }
        return true;
    }

    function storeInspection($s) {
        global $dbh;
        if (array_key_exists('INSPECTIONID',$s)) {
            $id = $s['INSPECTIONID'][0];
	    } else {
	        return;
	    }
        if (array_key_exists('LEASENUMBER',$s)) {
            $lease = $s['LEASENUMBER'][0];
	    } else {
	        return;
	    }
        $date = formatDate($s['INSPECTIONDATE'][0]);
    	$query = "SELECT * FROM lease_inspections WHERE inspection_number = '$id'";
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("StoreInspection:" . $e->getMessage());
    	    return false;
        }
        $numrows = mysqli_num_rows($result);
        if ($numrows == 0) {
	        if (array_key_exists('INSPECTEDBY',$s)) {
	            $ib = $s['INSPECTEDBY'][0];
	        } else {
                $ib = "";
	        }
	        if (array_key_exists('PREMISES',$s)) {
	            $p = $s['PREMISES'][0];
	        } else {
                $p = "";
	        }
	        if (array_key_exists('OVERALLNOTES',$s)) {
	            $on = $s['OVERALLNOTES'][0];
	        } else {
                $on = "";
	        }
	        if (array_key_exists('OVERALLCONDITION',$s)) {
	            $oc = $s['OVERALLCONDITION'][0];
	        } else {
                $oc = "A";
	        }
	        $query = "INSERT INTO lease_inspections VALUES('$id','$lease','$date','$ib','$p','$oc','$on','N')";
            try {
                $result = $dbh->query($query);
            } catch(Exception $e) {
                logError("LEASE_INSPECTIONS:" . $e->getMessage());
	            return false;
            }
        }
	    if (array_key_exists('AREA',$s)) {
            $as = $s['AREA'];
		    foreach ($as as $key => $a) {
	            $c = $s['CONDITION'][$key];
		        $n = $s['NOTE'][$key];
		        $query = "INSERT INTO AREA_INSPECTIONS VALUES('$id','$p','$a','$c','$n')";
                try {
                    $result = $dbh->query($query);
                } catch(Exception $e) {
                    logError("AREA_INSPECTIONS:" . $e->getMessage());
                    return 0;
                }
		    # Premises Areas
		        $query = "SELECT * FROM PREMISES_AREA WHERE premises = '$p' AND area = '$a'";
                try {
                    $result = $dbh->query($query);
                } catch(Exception $e) {
                    logError("PREMISES_AREA:" . $e->getMessage());
                    return 0;
                }
                $numrows = mysqli_num_rows($result);
                if ($numrows == 0) {
		            $query = "INSERT INTO PREMISES_AREA VALUES('$p','$a','')";
                    try {
                        $result = $dbh->query($query);
                    } catch(Exception $e) {
                        logError("PREMISES_AREA:" . $e->getMessage());
                        return 0;
                    }
		       }
		   }
        }
        return true;
    }

    function storeWork($s) {
        global $dbh;
        if (array_key_exists('WORKDESCRIPTION',$s)) {
            $d = $s['WORKDESCRIPTION'][0];
        } else {
            $d = "";
        }
        if (array_key_exists('PREMISES',$s)) {
            $p = $s['PREMISES'][0];
        } else {
            $p = "";
        }
        if (array_key_exists('STATUS',$s)) {
            $st = $s['STATUS'][0];
        } else {
            $st = 1;
        }
        if (array_key_exists('PRIORITY',$s)) {
            $a = $s['PRIORITY'][0];
        } else {
            $a = "";
        }
        if (array_key_exists('WORKDONE',$s)) {
            $wd = $s['WORKDONE'][0];
        } else {
            $wd = "";
        }
        $jn = "";
        $id = $s['WORKID'][0];
        $query = "SELECT * FROM work_list WHERE work_id = '$id'";
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("StoreLease:" . $e->getMessage());
            return 0;
        }
        if (mysqli_num_rows($result) <= 0) {
            if ($id == "new") {
                $id = getNextID("WORK");
            }
            $query = "insert into work_list values ('$id','$d','$p',CURRENT_DATE";
            $query .= ",'',$st,'$a','$wd','$jn')";
        } else {
            $query = "update work_list set work_description = '$d'";
            if ($p != "") {
                $query .= ",premises = '$p'";
            }
            if ($st != "") {
                $query .= ",status = $st";
            }
            if ($a != "") {
                $query .= ", allocated = '$a'";
            }
            if ($wd != "") {
                $query .= ", work_done = '$wd'";
            }
            $query .= " where work_id = '$id'";
        }
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("StoreWork:" . $e->getMessage());
            return 0;
        }
        print $id;
        return true;
    }

    function storeTask($s) {
        global $dbh;
        if (array_key_exists('WORKID',$s)) {
            $t = $s['WORKID'][0];
            $ln = $s['LINENO'][0];
            $nd = $s['NOTEDATE'][0];
            $td = $s['WORKNOTE'][0];
            $st = $s['STATUS'][0];
            $query = "select * from work_requirements where work_id = '$t' and line_no = $ln";
            try {
                $result = $dbh->query($query);
            } catch(Exception $e) {
                logError("StoreTask:" . $e->getMessage());
                return 0;
            }
            $row = $result->fetch_row();
            if ($row) {
                if ($st == "D") {
                   $query = "delete from work_requirements where work_id = '$t'";
                   $query .= " and line_no = $ln";
                } else {
                    $query = "update work_requirements set work_requirement = '$td'";
                    $query .= " where work_id = '$t' and line_no = $ln";
                }
            } else {
                $query = "insert into work_requirements values ('$t',$ln,'$nd', '$td')";
            }
            try {
                $result = $dbh->query($query);
            } catch(Exception $e) {
                logError("StoreLease:" . $e->getMessage() . "\n" . $query);
                return 0;
            }
        }
        return true;
    }

    function storeSchedule($s) {
        global $dbh;
        if (array_key_exists('WORKID',$s)) {
            $t = $s['WORKID'][0];
            $ln = $s['LINENO'][0];
            $nd = $s['SCHEDULEDDATE'][0];
            $td = $s['SCHEDULEDWORK'][0];
            $query = "select * from work_schedule where work_id = '$t' and line_no = $ln";
            try {
                $result = $dbh->query($query);
            } catch(Exception $e) {
                logError("StoreLease:" . $e->getMessage());
                return 0;
            }
            $row = $result->fetch_row();
            if ($row) {
                $query = "update work_schedule set task = '$td'";
                $query .= ",scheduled_date = '$nd'";
                $query .= " where work_id = '$t' and line_no = $ln";
            } else {
                $query = "insert into work_schedule values ('$t',$ln,'$nd', '$td')";
            }
            try {
                $result = $dbh->query($query);
            } catch(Exception $e) {
                logError("StoreLease:" . $e->getMessage());
                return 0;
            }
        }
   }

   function showUpdates() {
       global $dbh;
       $query = "select * from no_series where ns_code in ('WORK','JOBNO','TASKS','INSPECTIONS')";
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Message');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $node = $dom->appendChild($node);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   if (!$row[$i]) {
                       $row[$i] = "Null";
                   }
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           $err = mysqli_error($dbh);
           logError( $err );
           http_response_code(404);
       }
    }

    function lookupVendor($code) {
       global $dbh;
       $query = "select a.*,b.num_days,b.num_months,b.day_of_month from";
       $query .= " vendor a inner join payment_terms b on a.payment_terms = ";
       $query .= "b.code where a.code = '$code'";
       $result = $dbh->query($query);
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Vendor');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   if (!$row[$i]) { $row[$i] = "null"; }
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       }
    }

    function storeTasks($s) {
        global $dbh;
        $query = "select * from tasks where task_id = '$id'";
        if ($result = $dbh->query($query)) {
            $query = "update tasks set task_description = '$d',due_date = '$i'";
            $query .= ",completed = '$st',note = '$n' where task_id = '$id'";
        } else {
            $id = GetNextID("TASK");
            $query = "insert into tasks values ('$id','$d','$w','$i','$st','$n')";
        }
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("StoreLease:" . $e->getMessage());
            return false;
        }
        return true;
    }

   function showStatus() {
       global $dbh;
       $query = "select code,description as name from sundry_codes where type = 'ST'";
       $result = $dbh->query($query);
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Status');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       }
    }

    function storeNoSeries($s) {
        global $dbh;
        $id = $s['CODE'][0];
        $d = $s['LASTNUMBER'][0];
        $query = "select * from no_series where ns_code = '$id'";
        if ($result = $dbh->query($query)) {
            $query = "update no_series set ns_last_number = $d where ns_code = '$id'";
            $result = $dbh->query($query) or logError(mysqli_error($dbh));
            return true;
        } else {
            return false;
        }
    }

   function showTaxCodes() {
       global $dbh;
       $query = " select * from tax_codes";
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Areas');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $node = $dom->appendChild($node);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   if (!$row[$i]) {
                       $row[$i] = "Null";
                   }
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           $err = mysqli_error($dbh);
           print $err;
           http_response_code(404);
       }
    }

    function storeLease($s) {
        global $dbh;
        $query = "select * from sys where sys_key = 'DEF'";
        try {
            $result = $dbh->query($query);
        } catch(Exception $e) {
            logError("StoreLease:" . $e->getMessage());
            return false;
        }
        $sys = $result->fetch_row();
        if (array_key_exists('TENANT',$s)) {
            $id = GetNextID("LEASE");
            $n = $s['TENANT'][0];
            $p = $s['PREMISES'][0];
            $td = $s['PAYFREQ'][0];
            $b = $s['BOND'][0];
            $query = "select code from payment_terms where description = '$td'";
            try {
                $result = $dbh->query($query);
            } catch(Exception $e) {
                logError("StoreLease:" . $e->getMessage());
                return false;
            }
            $row = $result->fetch_row();
            if ($row) {
                $td = $row[0];
            } else {
                $td = $sys[4];
            }
            $query = "insert into lease_master (lm_lease_number,lm_premises,lm_leasee_name,lm_pay_freq,lm_bond";
            $query .= ",lm_status,lm_control) values ('$id','$p','$n', '$td',$b,'Y','$sys[2]')";
            try {
                $result = $dbh->query($query);
            } catch(Exception $e) {
                logError("StoreLease:" . $e->getMessage());
                return false;
            }
        }
        return true;
    }

   function showInspections() {
       global $dbh;
       $query = " select a.* from lease_inspections a left outer join area_inspections b on a.inspection_number";
       $query .= " = b.inspection_number";
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Inspections');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $node = $dom->appendChild($node);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   if (!$row[$i]) {
                       $row[$i] = "Null";
                   }
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           $err = mysqli_error($dbh);
           print $err;
           http_response_code(404);
       }
   }

   function showTasks() {
       global $dbh;
       $query = " select * from tasks where completed = 'N'";
       if ($result = $dbh->query($query)) {
           $dom = new DOMDocument('1.0','iso-8859-1');
           $node = $dom->createElement('Inspections');
           $node = $dom->appendChild($node);
           while ($row = $result->fetch_row()) {
               $node = $dom->appendChild($node);
               $flds = mysqli_num_fields($result);
               for ($i=0; $i < $flds;$i++) {
                   $finfo = $result->fetch_field_direct($i);
                   $field = $finfo->name;
                   $type = $finfo->type;
                   $attrib = $dom->createAttribute('Type');
                   $attrib->value = $type;
                   if (!$row[$i]) {
                       $row[$i] = "Null";
                   }
                   $value = htmlentities($row[$i]);
                   $elem = $dom->createElement($field,$value);
                   $elem->appendChild($attrib);
                   $node->appendChild($elem);
               }
           }
           echo $dom->saveXML();
       } else {
           $err = mysqli_error($dbh);
           print $err;
           http_response_code(404);
       }
   }
