Kasse/api/controllers/RequestController.php

<?php
if(!function_exists("array_column"))
{
	function array_column($array,$column_name)
	{
		return array_map(function($element) use($column_name){return $element[$column_name];}, $array);
	}
}


class RequestController
{
	private $mdate;
	private $dbh;
    private $project;
    private $project_id;

	function __construct ($path = "", $path2 = "")
	{
        date_default_timezone_set('Europe/Berlin');
		$this->mdate = date('Y-m-d H:i:s', strtotime("now"));
		$this->dbh = new PDO("mysql:host=localhost;dbname=kasse", "root", "gc01mysql");
		$this->dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
        $q = $this->dbh->query("SELECT * FROM projects			 				  
                                WHERE active = '1' 
                                ORDER BY project_id DESC
                                LIMIT 1");
		$this->project = $q->fetch(PDO::FETCH_ASSOC);
        $this->project_id = $this->project['project_id'];
        
	}


	function bootstrap ($data)
	{
		$result = array();

		switch ($_REQUEST['a']) {
			case 'pos_token':
				$result = $this->posToken($data);
				break;
			case 'pos_invoices':
				$result = $this->posInvoices($data);
				break;
            case 'pos_print':
                $result = $this->posPrint($data);
                break;
			case 'pos_infos':
				$result = $this->posInfos($data);
				break;
			case 'pos_infos2':
				$result = $this->posInfos2($data);
				break;
			case 'pos_cancel':
				$result = $this->posCancel($data);
				break;
            case 'projects':
				$result = $this->projects($data);
				break;
            case 'sellers':
				$result = $this->sellers($data);
				break;
		}

		$this->setJsonHeader();
		echo json_encode($result);
	}

	private function setJsonHeader ()
	{
		header('Access-Control-Allow-Origin: *');
		header('Access-Control-Allow-Methods: GET, POST, PUT');
		header('Access-Control-Allow-Headers: accept, content-type');
		header('Cache-Control: no-cache, must-revalidate');
		header('Content-type: application/json');
	}

	private function posToken ($data)
	{
		$disabled = array('token' => 0, 'active' => 0, 'disabled' => 1);

		$q = $this->dbh->query("SELECT * FROM pos			 				  
						  		WHERE pos_id = '{$data['pos_id']}' LIMIT 1");
		$result = $q->fetch(PDO::FETCH_ASSOC);

		if (!$result || $result['disabled'] != 0) {
			return $disabled;
		}

		if ($data['token'] == 0) {
			if ($result['token'] != 0) {
				return $disabled;
			}

			$result['token'] = rand(1, 65000);
			$result['active'] = 1;

		} else {
			if ($data['token'] != $result['token']) {
				return $disabled;
			}
			$result['active'] = ($result['disabled'] == 1) ? 0 : $data['active'];
			$result['disabled'] = $data['disabled'];
		}

		$this->dbh->exec("UPDATE pos SET token = '{$result['token']}', active = '{$result['active']}', disabled = '{$result['disabled']}', mdate = '{$this->mdate}' 
							   WHERE pos_id = '{$result['pos_id']}' ");

		return $result;
	}

	private function posInvoices ($data)
	{
		/*
		if (!isset($data['params']) || isset($data['params']['token']) || $data['params']['token'] == 0) {
			return false;
		}
		*/

		if (isset($data['invoice'])) {
			$d = $data['invoice'];

			if ($d['invoice_number'] == 0) {
				$this->dbh->exec("INSERT INTO invoice_header (project_id, pos_id, invoice_date, cashier, line_count, total, paid, mdate, cdate) 
							   VALUES ( '{$this->project_id}','{$d['pos_id']}',  '{$d['invoice_date']}',
										'{$d['cashier']}','{$d['line_count']}', '{$d['total']}','{$d['paid']}', 
										'{$this->mdate}','{$this->mdate}' )");
				$data['invoice_number'] = $this->dbh->lastInsertId();
			} else {
				$data['invoice_number'] = $d['invoice_number'];
				$this->dbh->exec("UPDATE invoice_header SET
									pos_id = '{$d['pos_id']}', 
									invoice_date = '{$d['invoice_date']}', 
									cashier = '{$d['cashier']}', 
									line_count = '{$d['line_count']}', 
									total = '{$d['total']}', 
									paid = '{$d['paid']}', 
									mdate = '{$this->mdate}'
							   WHERE invoice_number = '{$data['invoice_number']}' ");
			}

			$this->dbh->exec("DELETE FROM invoice_details
	 					   WHERE invoice_number = '{$data['invoice_number']}' ");

			foreach ($d['details'] as $item) {
                if ($item['item'] != "") {
				$this->dbh->exec("INSERT INTO invoice_details (project_id, invoice_number, line_number, item, seller_id, item_id, price) 
										   VALUES ( '{$this->project_id}','{$data['invoice_number']}', '{$item['line_number']}',
										   			'{$item['item']}','{$item['seller_id']}','{$item['item_id']}', 
										   	   		'{$item['price']}' )");
                }
			}
		}
		if (!isset($data['invoice_number'])) {
			$data['invoice_number'] = $data['invoice']['invoice_number'];
		}

		$q = $this->dbh->query("SELECT * FROM invoice_header WHERE invoice_number = '{$data['invoice_number']}' LIMIT 1");
		$result = $q->fetch(PDO::FETCH_ASSOC);
		if ($result) {
			$q = $this->dbh->query("SELECT * FROM invoice_details WHERE invoice_number = '{$data['invoice_number']}' ORDER BY line_number DESC ");
			$result['details'] = $q->fetchAll(PDO::FETCH_ASSOC);
		}
		return $result;
	}

	private function posInfos ($data)
	{
		$result = array('items' => array(), 'invoices' => array());
		$q = $this->dbh->query("SELECT item FROM invoice_details WHERE project_id = '{$this->project_id}' AND item NOT IN ('000-000','123-456') AND cancelled = 0 ORDER BY item ");
		if ($q) {
			$r = $q->fetchAll(PDO::FETCH_ASSOC);
			$result['items'] = array_column($r, 'item');
		}

		if ($data['pos_id'] == 0) {
            $q = $this->dbh->query("SELECT * FROM `view_invoice_header` a
								WHERE a.project_id = '{$this->project_id}' ORDER BY a.invoice_number ");
		    
        } else {
            $q = $this->dbh->query("SELECT * FROM `view_invoice_header` a
								WHERE a.project_id = '{$this->project_id}' AND a.pos_id = '{$data['pos_id']}' ORDER BY a.invoice_number ");
        }

		if ($q) {
			$result['invoices'] = $q->fetchAll(PDO::FETCH_ASSOC);
		}
				
		return $result;
	}

    private function posInfos2 ($data)
	{
		$result = array('items' => array(), 'invoices' => array());

		if ($data['pos_id'] == 0) {
            $q = $this->dbh->query("SELECT * FROM `view_invoice_header` a
								WHERE a.project_id = '{$this->project_id}' ORDER BY a.invoice_number ");
		    
        } else {
            $q = $this->dbh->query("SELECT * FROM `view_invoice_header` a
								WHERE a.project_id = '{$this->project_id}' AND a.pos_id = '{$data['pos_id']}' ORDER BY a.invoice_number ");
        }

		if ($q) {
			$result['invoices'] = $q->fetchAll(PDO::FETCH_ASSOC);
		}
				
		return $result;
	}
    
    private function posPrint ($data)
    {
        $result = $this->posInvoices($data);
        $this->dbh->exec("UPDATE invoice_header SET
									printed = '1'
							   WHERE invoice_number = '{$result['invoice_number']}' ");

        // print options
        $ptr = printer_open("POS-58");
        printer_set_option($ptr, PRINTER_MODE, "raw");

        printer_write($ptr, $this->project['print_header']);
        printer_write($ptr, str_repeat("\r\n", 2));
        
        printer_write($ptr, "Rechn.-Nr. " . $result['invoice_number'] . "\r\n");
        printer_write($ptr, "Kasse " . $result['pos_id'] . ", " . $result['cashier'] . "\r\n");
        printer_write($ptr, "\r\n");

        $result['details'] = array_reverse($result['details']);

        foreach ($result['details'] as $entry) {
            printer_write($ptr, "#" . str_pad(str_replace(".",",",$entry['line_number']), 2, " ", STR_PAD_LEFT) .  ": " . $entry['item'] . "" . str_pad(str_replace(".",",",$entry['price']), 19, " ", STR_PAD_LEFT) .  "\r\n");
        }

        printer_write($ptr, "________________________________\r\n");
        printer_write($ptr, str_pad(str_replace(".",",",$result['total']), 31, " ", STR_PAD_LEFT) . "\r\n");

        printer_write($ptr, $this->project['print_footer']);
        printer_write($ptr, str_repeat("\r\n", 7));
        printer_close($ptr);

        return $result;
    }

    private function posCancel ($data)
    {
        $result = "error";
        
        if (isset($data['item']) && $data['item'] != "") {
			$item = $data['item'];
            
            $this->dbh->exec("UPDATE invoice_details SET
									cancelled = '1'
							   WHERE item = '{$item}' ");
            $result = "OK";
        }
        return $result;
    }

    private function projects ($data)
	{
		if (isset($data['sellers']) && is_array($data['sellers']) && count($data['sellers']) > 0) {
            if (in_array($seller['seller_id'], $seller_ids)) {
                $this->dbh->exec("UPDATE projects 
                                       SET event_date = '{$data['event_date']}',
                                           event_location = '{$data['event_location']}', 
                                           print_header = '{$data['print_header']}',
                                           print_footer = '{$data['print_footer']}', 
                                           active = '{$data['active']}', 
                                           mdate = '{$this->mdate}' 
                                       WHERE project_id = '{$data['project_id']}' ");
            } else {
                $this->dbh->exec("INSERT INTO projects (project_id, event_date, event_location, print_header, print_footer, active, mdate, cdate) 
                                       VALUES ( '{$data['project_id']}','{$data['event_date']}',  '{$data['event_location']}',  '{$data['print_header']}',
                                                '{$data['print_footer']}',  '{$data['active']}','{$this->mdate}','{$this->mdate}' )");
            }
		}

		$q = $this->dbh->query("SELECT * FROM projects ORDER BY project_id");
		return $q->fetchAll(PDO::FETCH_ASSOC);
	}
    
	private function sellers ($data)
	{
		if (isset($data['sellers']) && is_array($data['sellers']) && count($data['sellers']) > 0) {
			$q = $this->dbh->query("SELECT seller_id FROM sellers WHERE project_id = '{$this->project_id}' ORDER BY seller_id");
			$r = $q->fetchAll(PDO::FETCH_ASSOC);
			$seller_ids = array();
			if ($r) {
				$seller_ids = array_column($r, 'seller_id');
			}

			foreach ($data['sellers'] as $seller) {
				if (in_array($seller['seller_id'], $seller_ids)) {
					$this->dbh->exec("UPDATE sellers 
										   SET name = '{$seller['name']}',
										       first_name = '{$seller['first_name']}', 
										   	   address = '{$seller['address']}',
										   	   zip_code = '{$seller['zip_code']}', 
										   	   place = '{$seller['place']}', 
										   	   mail = '{$seller['mail']}', 
										   	   phone = '{$seller['phone']}', 
										   	   mdate = '{$this->mdate}' 
							   			   WHERE project_id = '{$this->project_id}' AND seller_id = '{$seller['seller_id']}' ");
				} else {
					$this->dbh->exec("INSERT INTO sellers (project_id, seller_id, name, first_name, address, zip_code, place, mail, phone, mdate, cdate) 
										   VALUES ( '{$this->project_id}','{$seller['seller_id']}',  '{$seller['name']}',  '{$seller['first_name']}',
										   			'{$seller['address']}',  '{$seller['zip_code']}', '{$seller['place']}','{$seller['mail']}', 
										   	   		'{$seller['phone']}','{$this->mdate}','{$this->mdate}' )");
				}
			}
		}

		$q = $this->dbh->query("SELECT * FROM sellers WHERE project_id = '{$this->project_id}' ORDER BY seller_id");
		return $q->fetchAll(PDO::FETCH_ASSOC);
	}

	public function CsvExport ($data)
	{
		$q = $this->dbh->query("SELECT seller_id, name, first_name, address, zip_code, place, mail, phone, 0.0 as total, 0.0 as provision, 0.0 as cash_out  
								FROM sellers WHERE project_id = '{$this->project_id}' ORDER BY seller_id");
		
		$sellers = array();
		foreach ($q->fetchAll(PDO::FETCH_ASSOC) as $s) {
			$sellers[$s['seller_id']] = $s;

			for ($j = 1; $j <= 50; $j++) {
				$item_id = substr("00".$j, -3);
				$sellers[$s['seller_id']][$item_id] = "n.v.";
			}
		}		
		
		// Verkäufe nachladen, 
		$q = $this->dbh->query("SELECT * FROM invoice_details WHERE project_id = '{$this->project_id}' AND cancelled = 0");
		
		while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
            if (isset($sellers[$row['seller_id']])) {
			    $sellers[$row['seller_id']][$row['item_id']] = $row['price'];
            } else {
                $sellers[] = $row;
            }
		}
		
		// Summen berechnen
		$q = $this->dbh->query("SELECT seller_id, sum(price) as price FROM invoice_details WHERE project_id = '{$this->project_id}' AND cancelled = 0 GROUP BY seller_id ");
		while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
            if (isset($sellers[$row['seller_id']])) {
                $sellers[$row['seller_id']]['total'] = $row['price'];
                $sellers[$row['seller_id']]['provision'] = $row['price'] * 0.2;
                $sellers[$row['seller_id']]['cash_out'] = $row['price'] * 0.8;
            } else {
                $sellers[] = $row;
            }
		}
		
		$result = "Verk-Nr;Name;Vorname;Adresse;PLZ;Ort;E-Mail;Telefon;Gesamt;Provision;Auszahlung;A001;A002;A003;A004;A005;A006;A007;A008;A009;A010;" .
			      "A011;A012;A013;A014;A015;A016;A017;A018;A019;A020;A021;A022;A023;A024;A025;A026;A027;A028;A029;A030;A031;A032;A033;A034;A035;" .
			      "A036;A037;A038;A039;A040;A041;A042;A043;A044;A045;A046;A047;A048;A049;A050\r\n";

        $blacklist = array("seller_id", "name", "first_name", "address", "zip_code", "place", "mail", "phone");

		foreach ($sellers as $s) {
            foreach ($s as $k => $v) {
                if (!in_array($k, $blacklist) && $v != "n.v.") {
                    $s[$k] = @number_format($v, 2, ",", ".");
                }
            }
			$result .= implode(";", $s) . "\r\n";
		}

		return $result;
	}

	private function jsonLastErrorMsg ()
	{
		switch (json_last_error()) {
			case JSON_ERROR_NONE:
				return ' - No errors';
			case JSON_ERROR_DEPTH:
				return ' - Maximum stack depth exceeded';
			case JSON_ERROR_STATE_MISMATCH:
				return ' - Underflow or the modes mismatch';
			case JSON_ERROR_CTRL_CHAR:
				return ' - Unexpected control character found';
			case JSON_ERROR_SYNTAX:
				return ' - Syntax error, malformed JSON';
			case JSON_ERROR_UTF8:
				return ' - Malformed UTF-8 characters, possibly incorrectly encoded';
			default:
				return ' - Unknown error';
		}
	}
}