<?php

class Customer {
	public $Name, $Email, $AccountNumber, $Salesman, $LastLogin, $Language, $Active, $Phone, $MainNumber, $PriceLevel, $Contact, $DisableCheck, $CreditLimit, $DiscountDays, $Comments, $WaiveShipping, $SendOrderEmail;
	public $Address1, $Address2, $ZipCode, $State, $TaxID1, $TaxID2, $TaxIDExpiration1, $TaxIDExpiration2, $hasQuotes;
	public function __construct($AccountNumber) {
		$query = "SELECT * FROM CUSTOMER WHERE AccountNumber LIKE '$AccountNumber'";
		if (stripos($AccountNumber, "-")>-1) {
			$this->MainNumber = explode("-", $AccountNumber);
			$this->MainNumber = $this->MainNumber[0];			
		}else {
			$this->MainNumber = $AccountNumber;
		}
		
		
		
		global $con;
		$result = mysqli_query($con, $query);
		$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
		$this->OriginalColumns = array();
		foreach($row as $key => $value){
			$this->$key = $value;
			array_push($this->OriginalColumns, $key);
		}
		$this->SalesmanID = $this->Salesman;
		$this->Salesman = new Salesman($this->Salesman);
		$this->TaxExempt1 = $this->TaxExempt1 && ($row['TaxID1'] != "" && strtotime($row['TaxIDExpiration1']) > time());
		$this->TaxExempt2 = $this->TaxExempt2 && ($row['TaxID2'] != "" && strtotime($row['TaxIDExpiration2']) > time());
		if($this->TaxExempt1){
			$this->TaxExempt1 = 1;
		}else{
			$this->TaxExempt1 = 0;
		}
		if($this->TaxExempt2){
			$this->TaxExempt2 = 1;
		}else{
			$this->TaxExempt2 = 0;
		}
		//
		//$this->Name = $row['Name'];
		//$this->City = $row['City'];
		//$this->Phone = $row['Phone'];
		//$this->Contact = $row['Contact'];
		//$this->ContactTitle = $row['ContactTitle'];
		//$this->Email = $row['Email'];
		//$this->SendOrderEmail = $row['SendOrderEmail'];
		//$this->AccountNumber = $row['AccountNumber'];
		//$this->CreditLimit = $row['CreditLimit'];
		//$this->Address1 = $row['Address1'];
		//$this->Address2 = $row['Address2'];
		//$this->ZipCode = $row['ZipCode'];
		//$this->State = $row['State'];
		//
		//$this->ShipToAddress1 = $row['ShipToAddress1'];
		//$this->ShipToAddress2 = $row['ShipToAddress2'];
		//$this->ShipToCity = $row['ShipToCity'];
		//$this->ShipToZip = $row['ShipToZip'];
		//$this->ShipToState = $row['ShipToState'];
		//
		//$this->Terms = $row['Terms'];
		//$this->DiscountDays = $row['DiscountDays'];
		//$this->Salesman = new Salesman($row['Salesman']);
		//$this->Comments = $row['Comments'];
		//$this->LastLogin = $row['LastLogin'];
		//$this->Password = $row['Password'];
		//$this->Language = $row['Language'];
		//$this->DisableCheck = $row['DisableCheck'];
		//$this->Active = $row['Active'];
		//$this->TaxID1 = $row['TaxID1'];
		//$this->TaxID2 = $row['TaxID2'];
		//$this->TaxIDExpiration1 = $row['TaxIDExpiration1'];
		//$this->TaxIDExpiration2 = $row['TaxIDExpiration2'];
		////$this->TaxExempt1 = ($row['TaxID1'] != "" && strtotime($row['TaxIDExpiration1']) > time());
		//$this->TaxExempt1 = false;
		//$this->TaxExempt2 = ($row['TaxID2'] != "" && strtotime($row['TaxIDExpiration2']) > time());
		//$this->PriceLevel =$row['PriceLevel'];
		//$this->WaiveShipping = $row['WaiveShipping'];
		//
		//
		// Get Columns Names and either if they need quotes in a query or not.
		//
		$this->hasQuotes = [];
		$query = "SHOW COLUMNS FROM `CUSTOMER`";
		$result = mysqli_query($con,$query);
		while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
			if(stripos($row['Type'], "int") === 0 || stripos($row['Type'], "double") === 0 || stripos($row['Type'], "tinyint") === 0 || stripos($row['Type'], "numeric") === 0 || stripos($row['Type'], "double") === 0 || stripos($row['Type'], "decimal") === 0 || stripos($row['Type'], "float") === 0 || stripos($row['Type'], "bigint") === 0 || stripos($row['Type'], "real") === 0 || stripos($row['Type'], "bool") === 0){
				$this->hasQuotes[$row['Field']] = false;
			}else{
				$this->hasQuotes[$row['Field']] = true;
			}
		}
		
	}
    public function save(){
	
		
		
		global $con;
		$this->Salesman = $this->SalesmanID;
		$query = "UPDATE CUSTOMER SET ";
		$saveQuery = [];
		foreach($this->hasQuotes as $fieldName => $quotes){
			if($fieldName != "AccountNumber"){
				$saveQuery[] = $this->saveString($fieldName, $this->$fieldName);	
			}
		}
		$query .= implode(", ", $saveQuery) . " WHERE `AccountNumber` = '" . $this->AccountNumber . "'";
		mysqli_query($con, $query);
		if($_SESSION['Username'] == "esanchez"){
			//echo $query;
		}
		//echo $query . "\n";
		$this->Salesman = new Salesman($this->SalesmanID);	
		
	}
	private function saveString($Column, $Value){
		
		if(is_null($Value)){
			$returnValue = "NULL";	
		}elseif(strtoupper($Value) == "NOW()"){
			$returnValue = "NOW()";
		}else{
			$returnValue = ($this->hasQuotes[$Column])?"'$Value'":"$Value";	
		}
		return " `$Column` = $returnValue ";
	}
	
	

	public function getInvoices() {
		global $con;
		if($this->MainNumber != $this->AccountNumber){
			$query = "SELECT INVOICE_HEAD.*, ROUND(INVOICE_HEAD.Subtotal-SUM(PaymentAmount),2) AS Balance, PAYMENT_DETAIL.PartialPayment, ROUND(SUM(PAYMENT_DETAIL.PaymentAmount),2) as PaymentAmount  FROM INVOICE_HEAD LEFT JOIN PAYMENT_DETAIL ON INVOICE_HEAD.InvoiceID = PAYMENT_DETAIL.InvoiceID WHERE `AccountNumber`='" . $this->AccountNumber . "' AND (PartialPayment = 1 OR PartialPayment IS NULL) AND INVOICE_HEAD.Status = 1 AND INVOICE_HEAD.InvoiceID NOT IN (SELECT InvoiceID FROM PAYMENT_DETAIL WHERE PartialPayment = 0) GROUP BY InvoiceID  ORDER BY InvoiceDate ASC";
			
		}else{
			//$query = "SELECT INVOICE_HEAD.*, ROUND(INVOICE_HEAD.Subtotal-SUM(PaymentAmount),2) AS Balance, PAYMENT_DETAIL.PartialPayment, ROUND(SUM(PAYMENT_DETAIL.PaymentAmount),2) as PaymentAmount  FROM INVOICE_HEAD LEFT JOIN PAYMENT_DETAIL ON INVOICE_HEAD.InvoiceID = PAYMENT_DETAIL.InvoiceID WHERE `AccountNumber`='" . $this->AccountNumber . "' AND (PartialPayment = 1 OR PartialPayment IS NULL) AND INVOICE_HEAD.Status = 1 AND INVOICE_HEAD.InvoiceID NOT IN (SELECT InvoiceID FROM PAYMENT_DETAIL WHERE PartialPayment = 0) GROUP BY InvoiceID  ORDER BY InvoiceDate ASC";
			$query = "SELECT INVOICE_HEAD.*, ROUND(INVOICE_HEAD.Subtotal-SUM(PaymentAmount),2) AS Balance, PartialPayment, ROUND(SUM(DETAILED_PAYMENT.PaymentAmount),2) as PaymentAmount FROM INVOICE_HEAD LEFT JOIN DETAILED_PAYMENT USING(InvoiceID) WHERE INVOICE_HEAD.`AccountNumber`='" . $this->AccountNumber . "' AND (PartialPayment = 1 OR PartialPayment IS NULL) AND INVOICE_HEAD.Status = 1 AND INVOICE_HEAD.InvoiceID not IN (SELECT InvoiceID FROM DETAILED_PAYMENT WHERE PartialPayment = 0 and Void = 0) GROUP BY InvoiceID ORDER BY InvoiceDate ASC";
			//echo $query;
		}
		$result = mysqli_query($con, $query);
		$invoices = array();
		while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
			$row['Subtotal'] = ($row['Balance'] == null)?$row['Subtotal']:$row['Balance'];
			$query2 = "SELECT SUM(OverAmount) as Overpayment, GROUP_CONCAT(Reason SEPARATOR '--') as Reasons, GROUP_CONCAT(User SEPARATOR '--') as User  FROM INVOICE_ADJUST WHERE InvoiceNumber = '" . $row['InvoiceNumber'] . "' GROUP BY InvoiceNumber";
			$result2 = mysqli_query($con, $query2);
			if(mysqli_num_rows($result2) > 0){
				$row2 = mysqli_fetch_array($result2, MYSQLI_ASSOC);
				$row['Subtotal'] -= $row2['Overpayment'];
				$row['Balance'] -= $row2['Overpayment'];
				$row['Overpayment'] = $row2['Overpayment'];
				$row['Reasons'] = $row2['Reasons'];
				$row['User'] = $row2['User'];
			}
			array_push($invoices,$row);
		}
		return $invoices;
	}
	public function getCredits() {
		global $con;
		if($this->MainNumber != $this->AccountNumber){
			$query = "SELECT * FROM CREDIT WHERE `AccountNumber`='" . $this->MainNumber . "' AND `Job`='" . substr($this->AccountNumber,stripos($this->AccountNumber,"-")+1) . "' AND CREDIT.CreditNumber NOT IN (SELECT CreditNumber FROM PAYMENT_CREDIT)";
			//echo $query;
		}else{
			$query = "SELECT * FROM CREDIT WHERE `AccountNumber`='" . $this->AccountNumber . "' AND CREDIT.CreditNumber NOT IN (SELECT CreditNumber FROM PAYMENT_CREDIT)";
		}
		
		$result = mysqli_query($con, $query);
		$credits = array();
		while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
			array_push($credits,$row);
		}
		return $credits;
	}
	public function setPassword($oldPassword, $newPassword) {
		global $con;
		$query = "UPDATE CUSTOMER SET `Password` = '" . md5($newPassword) . "' WHERE AccountNumber '" . $this->AccountNumber . "' AND `Password` = '" . md5($oldPassword) . "'";
		$result = mysqli_query($query);
		if(mysqli_affected_rows($con) == 0){
			echo "Old Password may be incorrect";
		}	
	}
	public function getDueDays(){
		global $con;
		if($this->MainNumber != $this->AccountNumber){
			$query = "SELECT TIMESTAMPDIFF(DAY, MIN(INVOICE.InvoiceDate), NOW()) AS DueDays FROM INVOICE LEFT JOIN PAYMENT_DETAIL ON INVOICE.InvoiceNumber = PAYMENT_DETAIL.InvoiceNumber WHERE `AccountNumber`='" . $this->MainNumber . "' AND `Job`='" . substr($this->AccountNumber,stripos($this->AccountNumber,"-")+1) . "' AND (PartialPayment = 1 OR PartialPayment IS NULL) AND INVOICE.InvoiceNumber NOT IN (SELECT InvoiceNumber FROM PAYMENT_DETAIL WHERE PartialPayment = 0) GROUP BY AccountNumber, Job";
		}else{
			$query = "SELECT TIMESTAMPDIFF(DAY, MIN(INVOICE.InvoiceDate), NOW()) AS DueDays FROM INVOICE LEFT JOIN PAYMENT_DETAIL ON INVOICE.InvoiceNumber = PAYMENT_DETAIL.InvoiceNumber WHERE `AccountNumber`='" . $this->AccountNumber . "' AND (PartialPayment = 1 OR PartialPayment IS NULL) AND INVOICE.InvoiceNumber NOT IN (SELECT InvoiceNumber FROM PAYMENT_DETAIL WHERE PartialPayment = 0) GROUP BY AccountNumber";
		}
		$result = mysqli_query($con, $query);
		$row = mysqli_fetch_array($result);
		if(!$row['DueDays']){
			$row['DueDays']= 0;
		}
		return $row['DueDays'];
		
	}
	public function updateDueDays(){
		global $con;
		$query = "UPDATE CUSTOMER SET DueDays = '" . $this->getDueDays() . "', DueDaysCalcDate = NOW() WHERE AccountNumber = '" . $this->AccountNumber . "'";
		$result = mysqli_query($con, $query);
		if($result){
			event_log("CRONJOB", "UPDATE CUSTOMER DUE DAYS", "AccountNumber: " . $this->AccountNumber . "\nCustomerName: " . $this->Name . "\nDueDays: " . $this->getDueDays());
		}
	}
}

?>