<?php 
/****************************************************************/
/*          e-mo.pl © 2017                                      */
/*          http://www.e-mo.com.pl                              */
/*          e-mail: info@e-mo.pl                                */
/*          produkt: Action2osCommerce                          */
/*          wersja: 1.9.7                                       */
/*          webserwis: oscGold                                  */
/*          sciezka: https://www.e-mo.com.pl/plikit,1918,533545b244d7d.php*/
/*          data: 2017-09-25                                    */
/*          godz: 10:45:28                                      */
/****************************************************************/

class orders { 
	public $Conn = 0;
	public $errMsg = '';
	public $errNo= 0;
	public $orders_id=0;
	public $customers_id=0;
	public $customers_name=0;
	public $customers_company=0;
	public $customers_street_address=0;
	public $customers_suburb=0;
	public $customers_city=0;
	public $customers_postcode=0;
	public $customers_state=0;
	public $customers_country=0;
	public $customers_telephone=0;
	public $customers_email_address=0;
	public $customers_address_format_id=0;
	public $delivery_name=0;
	public $delivery_company=0;
	public $delivery_street_address=0;
	public $delivery_suburb=0;
	public $delivery_city=0;
	public $delivery_postcode=0;
	public $delivery_state=0;
	public $delivery_country=0;
	public $delivery_address_format_id=0;
	public $billing_name=0;
	public $billing_company=0;
	public $billing_street_address=0;
	public $billing_suburb=0;
	public $billing_city=0;
	public $billing_postcode=0;
	public $billing_state=0;
	public $billing_country=0;
	public $billing_address_format_id=0;
	public $payment_method=0;
	public $cc_type=0;
	public $cc_owner=0;
	public $cc_number=0;
	public $cc_expires=0;
	public $last_modified=0;
	public $date_purchased=0;
	public $orders_status=0;
	public $orders_date_finished=0;
	public $currency=0;
	public $currency_value=0;
	public $dok_KluczObcy =0;
	public $bezStatusu =0;

	public function Dodaj(){
		$zwrot=true;
		$query="Insert orders (customers_id, customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, last_modified, date_purchased, orders_status, orders_date_finished, currency, currency_value) VALUES (:customers_id, :customers_name, :customers_company, :customers_street_address, :customers_suburb, :customers_city, :customers_postcode, :customers_state, :customers_country, :customers_telephone, :customers_email_address, :customers_address_format_id, :delivery_name, :delivery_company, :delivery_street_address, :delivery_suburb, :delivery_city, :delivery_postcode, :delivery_state, :delivery_country, :delivery_address_format_id, :billing_name, :billing_company, :billing_street_address, :billing_suburb, :billing_city, :billing_postcode, :billing_state, :billing_country, :billing_address_format_id, :payment_method, :cc_type, :cc_owner, :cc_number, :cc_expires, :last_modified, :date_purchased, :orders_status, :orders_date_finished, :currency, :currency_value) ";
		$sth = $this->Conn->prepare($query);

		$sth->bindParam(':customers_id', $this->customers_id, PDO::PARAM_INT);
		$sth->bindParam(':customers_name', $this->customers_name, PDO::PARAM_STR);
		$sth->bindParam(':customers_company', $this->customers_company, PDO::PARAM_STR);
		$sth->bindParam(':customers_street_address', $this->customers_street_address, PDO::PARAM_STR);
		$sth->bindParam(':customers_suburb', $this->customers_suburb, PDO::PARAM_STR);
		$sth->bindParam(':customers_city', $this->customers_city, PDO::PARAM_STR);
		$sth->bindParam(':customers_postcode', $this->customers_postcode, PDO::PARAM_STR);
		$sth->bindParam(':customers_state', $this->customers_state, PDO::PARAM_STR);
		$sth->bindParam(':customers_country', $this->customers_country, PDO::PARAM_STR);
		$sth->bindParam(':customers_telephone', $this->customers_telephone, PDO::PARAM_STR);
		$sth->bindParam(':customers_email_address', $this->customers_email_address, PDO::PARAM_STR);
		$sth->bindParam(':customers_address_format_id', $this->customers_address_format_id, PDO::PARAM_INT);
		$sth->bindParam(':delivery_name', $this->delivery_name, PDO::PARAM_STR);
		$sth->bindParam(':delivery_company', $this->delivery_company, PDO::PARAM_STR);
		$sth->bindParam(':delivery_street_address', $this->delivery_street_address, PDO::PARAM_STR);
		$sth->bindParam(':delivery_suburb', $this->delivery_suburb, PDO::PARAM_STR);
		$sth->bindParam(':delivery_city', $this->delivery_city, PDO::PARAM_STR);
		$sth->bindParam(':delivery_postcode', $this->delivery_postcode, PDO::PARAM_STR);
		$sth->bindParam(':delivery_state', $this->delivery_state, PDO::PARAM_STR);
		$sth->bindParam(':delivery_country', $this->delivery_country, PDO::PARAM_STR);
		$sth->bindParam(':delivery_address_format_id', $this->delivery_address_format_id, PDO::PARAM_INT);
		$sth->bindParam(':billing_name', $this->billing_name, PDO::PARAM_STR);
		$sth->bindParam(':billing_company', $this->billing_company, PDO::PARAM_STR);
		$sth->bindParam(':billing_street_address', $this->billing_street_address, PDO::PARAM_STR);
		$sth->bindParam(':billing_suburb', $this->billing_suburb, PDO::PARAM_STR);
		$sth->bindParam(':billing_city', $this->billing_city, PDO::PARAM_STR);
		$sth->bindParam(':billing_postcode', $this->billing_postcode, PDO::PARAM_STR);
		$sth->bindParam(':billing_state', $this->billing_state, PDO::PARAM_STR);
		$sth->bindParam(':billing_country', $this->billing_country, PDO::PARAM_STR);
		$sth->bindParam(':billing_address_format_id', $this->billing_address_format_id, PDO::PARAM_INT);
		$sth->bindParam(':payment_method', $this->payment_method, PDO::PARAM_STR);
		$sth->bindParam(':cc_type', $this->cc_type, PDO::PARAM_STR);
		$sth->bindParam(':cc_owner', $this->cc_owner, PDO::PARAM_STR);
		$sth->bindParam(':cc_number', $this->cc_number, PDO::PARAM_STR);
		$sth->bindParam(':cc_expires', $this->cc_expires, PDO::PARAM_STR);
		$sth->bindParam(':last_modified', $this->last_modified, PDO::PARAM_STR);
		$sth->bindParam(':date_purchased', $this->date_purchased, PDO::PARAM_STR);
		$sth->bindParam(':orders_status', $this->orders_status, PDO::PARAM_INT);
		$sth->bindParam(':orders_date_finished', $this->orders_date_finished, PDO::PARAM_STR);
		$sth->bindParam(':currency', $this->currency, PDO::PARAM_STR);
		$sth->bindParam(':currency_value', $this->currency_value, PDO::PARAM_STR);

		$sth->execute();

		if($sth->errorCode() != 0){
			$zwrot=false;
			$this->errMsg = $sth->errorInfo();
			$this->errNo = $sth->errorCode();
		}

		 return $zwrot;
	 } 
	public function Zmien(){
		$zwrot=true;
		$query="Update orders SET customers_id=:customers_id, customers_name=:customers_name, customers_company=:customers_company, customers_street_address=:customers_street_address, customers_suburb=:customers_suburb, customers_city=:customers_city, customers_postcode=:customers_postcode, customers_state=:customers_state, customers_country=:customers_country, customers_telephone=:customers_telephone, customers_email_address=:customers_email_address, customers_address_format_id=:customers_address_format_id, delivery_name=:delivery_name, delivery_company=:delivery_company, delivery_street_address=:delivery_street_address, delivery_suburb=:delivery_suburb, delivery_city=:delivery_city, delivery_postcode=:delivery_postcode, delivery_state=:delivery_state, delivery_country=:delivery_country, delivery_address_format_id=:delivery_address_format_id, billing_name=:billing_name, billing_company=:billing_company, billing_street_address=:billing_street_address, billing_suburb=:billing_suburb, billing_city=:billing_city, billing_postcode=:billing_postcode, billing_state=:billing_state, billing_country=:billing_country, billing_address_format_id=:billing_address_format_id, payment_method=:payment_method, cc_type=:cc_type, cc_owner=:cc_owner, cc_number=:cc_number, cc_expires=:cc_expires, last_modified=:last_modified, date_purchased=:date_purchased, orders_status=:orders_status, orders_date_finished=:orders_date_finished, currency=:currency, currency_value=:currency_value Where orders_id=:orders_id";
		$sth = $this->Conn->prepare($query);

		$sth->bindParam(':customers_id', $this->customers_id, PDO::PARAM_INT);
		$sth->bindParam(':customers_name', $this->customers_name, PDO::PARAM_STR);
		$sth->bindParam(':customers_company', $this->customers_company, PDO::PARAM_STR);
		$sth->bindParam(':customers_street_address', $this->customers_street_address, PDO::PARAM_STR);
		$sth->bindParam(':customers_suburb', $this->customers_suburb, PDO::PARAM_STR);
		$sth->bindParam(':customers_city', $this->customers_city, PDO::PARAM_STR);
		$sth->bindParam(':customers_postcode', $this->customers_postcode, PDO::PARAM_STR);
		$sth->bindParam(':customers_state', $this->customers_state, PDO::PARAM_STR);
		$sth->bindParam(':customers_country', $this->customers_country, PDO::PARAM_STR);
		$sth->bindParam(':customers_telephone', $this->customers_telephone, PDO::PARAM_STR);
		$sth->bindParam(':customers_email_address', $this->customers_email_address, PDO::PARAM_STR);
		$sth->bindParam(':customers_address_format_id', $this->customers_address_format_id, PDO::PARAM_INT);
		$sth->bindParam(':delivery_name', $this->delivery_name, PDO::PARAM_STR);
		$sth->bindParam(':delivery_company', $this->delivery_company, PDO::PARAM_STR);
		$sth->bindParam(':delivery_street_address', $this->delivery_street_address, PDO::PARAM_STR);
		$sth->bindParam(':delivery_suburb', $this->delivery_suburb, PDO::PARAM_STR);
		$sth->bindParam(':delivery_city', $this->delivery_city, PDO::PARAM_STR);
		$sth->bindParam(':delivery_postcode', $this->delivery_postcode, PDO::PARAM_STR);
		$sth->bindParam(':delivery_state', $this->delivery_state, PDO::PARAM_STR);
		$sth->bindParam(':delivery_country', $this->delivery_country, PDO::PARAM_STR);
		$sth->bindParam(':delivery_address_format_id', $this->delivery_address_format_id, PDO::PARAM_INT);
		$sth->bindParam(':billing_name', $this->billing_name, PDO::PARAM_STR);
		$sth->bindParam(':billing_company', $this->billing_company, PDO::PARAM_STR);
		$sth->bindParam(':billing_street_address', $this->billing_street_address, PDO::PARAM_STR);
		$sth->bindParam(':billing_suburb', $this->billing_suburb, PDO::PARAM_STR);
		$sth->bindParam(':billing_city', $this->billing_city, PDO::PARAM_STR);
		$sth->bindParam(':billing_postcode', $this->billing_postcode, PDO::PARAM_STR);
		$sth->bindParam(':billing_state', $this->billing_state, PDO::PARAM_STR);
		$sth->bindParam(':billing_country', $this->billing_country, PDO::PARAM_STR);
		$sth->bindParam(':billing_address_format_id', $this->billing_address_format_id, PDO::PARAM_INT);
		$sth->bindParam(':payment_method', $this->payment_method, PDO::PARAM_STR);
		$sth->bindParam(':cc_type', $this->cc_type, PDO::PARAM_STR);
		$sth->bindParam(':cc_owner', $this->cc_owner, PDO::PARAM_STR);
		$sth->bindParam(':cc_number', $this->cc_number, PDO::PARAM_STR);
		$sth->bindParam(':cc_expires', $this->cc_expires, PDO::PARAM_STR);
		$sth->bindParam(':last_modified', $this->last_modified, PDO::PARAM_STR);
		$sth->bindParam(':date_purchased', $this->date_purchased, PDO::PARAM_STR);
		$sth->bindParam(':orders_status', $this->orders_status, PDO::PARAM_INT);
		$sth->bindParam(':orders_date_finished', $this->orders_date_finished, PDO::PARAM_STR);
		$sth->bindParam(':currency', $this->currency, PDO::PARAM_STR);
		$sth->bindParam(':currency_value', $this->currency_value, PDO::PARAM_STR);
		$sth->bindParam(':orders_id', $this->orders_id, PDO::PARAM_INT);

		$sth->execute();

		if($sth->errorCode() != 0){
			$zwrot=false;
			$this->errMsg = $sth->errorInfo();
			$this->errNo = $sth->errorCode();
		}

		 return $zwrot;
	 } 
	 public function ZmienStatus(){
		$zwrot=true;
		if($this->bezStatusu==1)
			$query="Update orders SET dok_KluczObcy=:dok_KluczObcy Where orders_id=:orders_id";
		else
			$query="Update orders SET orders_status=:orders_status, dok_KluczObcy=:dok_KluczObcy Where orders_id=:orders_id";
			
		$sth = $this->Conn->prepare($query);
		if($this->bezStatusu==0)
			$sth->bindParam(':orders_status', $this->orders_status, PDO::PARAM_INT);
		$sth->bindParam(':dok_KluczObcy', $this->dok_KluczObcy, PDO::PARAM_INT);
		$sth->bindParam(':orders_id', $this->orders_id, PDO::PARAM_INT);

		$sth->execute();

		if($sth->errorCode() != 0){
			$zwrot=false;
			$this->errMsg = $sth->errorInfo();
			$this->errNo = $sth->errorCode();
		}

		 return $zwrot;
	 } 
	public function ZmienStatus2(){
		$zwrot=true;
		$query="Update orders SET orders_status=:orders_status Where dok_KluczObcy=:dok_KluczObcy ";
		$sth = $this->Conn->prepare($query);

		$sth->bindParam(':orders_status', $this->orders_status, PDO::PARAM_INT);
		$sth->bindParam(':dok_KluczObcy', $this->dok_KluczObcy, PDO::PARAM_INT);
		

		$sth->execute();

		if($sth->errorCode() != 0){
			$zwrot=false;
			$this->errMsg = $sth->errorInfo();
			$this->errNo = $sth->errorCode();
		}

		 return $zwrot;
	 } 
	public function DodajKluczObcy(){
		$zwrot=true;
		$query="Update orders SET dok_KluczObcy=:dok_KluczObcy Where orders_id=:orders_id";
		$sth = $this->Conn->prepare($query);

		$sth->bindParam(':dok_KluczObcy', $this->dok_KluczObcy, PDO::PARAM_INT);
		$sth->bindParam(':orders_id', $this->orders_id, PDO::PARAM_INT);

		$sth->execute();

		if($sth->errorCode() != 0){
			$zwrot=false;
			$this->errMsg = $sth->errorInfo();
			$this->errNo = $sth->errorCode();
		}

		 return $zwrot;
	 }
	public function Usun(){
		$zwrot=true;
		$query="Delete from orders Where orders_id=:orders_id";

		$sth = $this->Conn->prepare($query);
		$sth->bindParam(':orders_id', $this->orders_id, PDO::PARAM_INT);

		$sth->execute();

		if($sth->errorCode() != 0){
			$zwrot=false;
			$this->errMsg = $sth->errorInfo();
			$this->errNo = $sth->errorCode();
		}

		 return $zwrot;
	 } 
	public function Lista(){
		$query="SELECT o.orders_id as 'dok_Id', o.orders_id as 'dok_Licznik', o.orders_id as 'dok_Numer', 0 as 'dok_Referencja', 1 as 'dok_Typ', o.date_purchased as 'dok_Data',o.date_purchased as 'dok_DataRz', o.customers_id as 'dok_Klient', o.customers_id as 'dok_User', 0 as 'dok_Platnosc', o.payment_method as 'dok_PlatnoscTekst', 0 as 'dok_PlatnoscDni',   '-' as 'dok_Bank', '-' as 'dok_Konto', '127.0.0.1' as 'dok_Host', b.value 'dok_Suma', o.orders_status as 'dok_Status', 1 as 'dok_Widok', o.currency as 'dok_Waluta', o.currency_value as 'dok_Kurs','/osc' as 'dok_Prefix', o.customers_name as 'f_Nazwa', customers_nip as 'f_NIP', case when c.customers_email_address is null then o.customers_email_address else c.customers_email_address end as 'f_Mail', case when c.customers_id is null then o.orders_id else  c.customers_id end as 'f_Id', '' as 'f_REGON',  case when LENGTH(o.customers_telephone) > 0 then o.customers_telephone when c.customers_telephone is null then ''  else c.customers_telephone end as 'f_Telefon', case when c.customers_fax is null  then '' else c.customers_fax end  as 'f_Faks', 0 as 'f_Zrodlo', 0 as 'f_KluczObcy', '' as 'f_Modyfikacja', '' as 'f_Dodanie', (Select comments from orders_status_history osh Where osh.orders_id=o.orders_id and osh.orders_status_id=1 Limit 1) as dok_Uwaga, (SELECT CONCAT(Round(kgo.value,2),'PLN') from  orders_total kgo Where kgo.orders_id=o.orders_id and kgo.class='ot_kgo') as dok_KGO, (SELECT title FROM orders_total WHERE orders_id=o.orders_id AND class='ot_shipping') as dok_DostawaTekst from orders o Inner JOIN orders_total b on (o.orders_id=b.orders_id and b.class='ot_total') Left JOIN customers c on (c.customers_id=o.customers_id) Where o.dok_KluczObcy is null Order by o.orders_id";
		$sth = $this->Conn->prepare($query);
		$sth->execute();		
		$tablica = array();
		$x=0;
		
		while ($row = $sth->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT))
		{
			foreach ($row as $k => $v)
			{
				$tablica[$x][$k]=htmlspecialchars(trim($v), ENT_QUOTES);
			}
			$x++;
		}

		return $tablica;
	}
	public function Lista2($_data){
		$query="SELECT o.orders_id as 'dok_Id', o.orders_id as 'dok_Licznik', o.orders_id as 'dok_Numer', 0 as 'dok_Referencja', 1 as 'dok_Typ', o.date_purchased as 'dok_Data',o.date_purchased as 'dok_DataRz', o.customers_id as 'dok_Klient', o.customers_id as 'dok_User', 0 as 'dok_Platnosc', o.payment_method as 'dok_PlatnoscTekst', 0 as 'dok_PlatnoscDni',   '-' as 'dok_Bank', '-' as 'dok_Konto', '127.0.0.1' as 'dok_Host', b.value 'dok_Suma', o.orders_status as 'dok_Status', 1 as 'dok_Widok', o.currency as 'dok_Waluta', o.currency_value as 'dok_Kurs','/osc' as 'dok_Prefix', o.customers_name as 'f_Nazwa', customers_nip as 'f_NIP', case when c.customers_email_address is null then o.customers_email_address else c.customers_email_address end as 'f_Mail', case when c.customers_id is null then o.orders_id else  c.customers_id end as 'f_Id', '' as 'f_REGON',  case when LENGTH(o.customers_telephone) > 0 then o.customers_telephone when c.customers_telephone is null then ''  else c.customers_telephone end as 'f_Telefon', case when c.customers_fax is null  then '' else c.customers_fax end  as 'f_Faks', 0 as 'f_Zrodlo', 0 as 'f_KluczObcy', '' as 'f_Modyfikacja', '' as 'f_Dodanie', (Select comments from orders_status_history osh Where osh.orders_id=o.orders_id and osh.orders_status_id=1 Limit 1) as dok_Uwaga, (SELECT CONCAT(Round(kgo.value,2),'PLN') from  orders_total kgo Where kgo.orders_id=o.orders_id and kgo.class='ot_kgo') as dok_KGO, (SELECT title FROM orders_total WHERE orders_id=o.orders_id AND class='ot_shipping') as dok_DostawaTekst, o.dok_KluczObcy as dok_KluczObcy  from orders o Inner JOIN orders_total b on (o.orders_id=b.orders_id and b.class='ot_total') Left JOIN customers c on (c.customers_id=o.customers_id) Where DATE(o.date_purchased)>=:Data Order by o.orders_id"; 
		// o.dok_KluczObcy as dok_KluczObcy, (doda� aby aktualizowa�o zam�wienia)
		$sth = $this->Conn->prepare($query);
		$sth->bindParam(':Data', $_data, PDO::PARAM_STR);
		$sth->execute();		
		$tablica = array();
		$x=0;
		
		while ($row = $sth->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT))
		{
			foreach ($row as $k => $v)
			{
				$tablica[$x][$k]=htmlspecialchars(trim($v), ENT_QUOTES);
			}
			$x++;
		}

		return $tablica;
	}
	
	public function ListaUser($_id){
		$query="SELECT case when c.customers_id is null then o.orders_id else c.customers_id end as u_Id, case when c.customers_email_address is null then o.customers_email_address else c.customers_email_address end as u_User, case when c.customers_firstname is null then o.customers_name else c.customers_firstname end as u_Imie,  c.customers_lastname as u_Nazwisko FROM orders o Left Join customers c on (c.customers_id=o.customers_id) Where orders_id=:orders_id";
		$sth = $this->Conn->prepare($query);
		$sth->bindParam(':orders_id', $_id, PDO::PARAM_INT);
		$sth->execute();
		
		$tablica = array();
		
		while ($row = $sth->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT))
		{
			foreach ($row as $k => $v)
			{
				$tablica[$k]=htmlspecialchars(trim($v), ENT_QUOTES);
			}			
		}

		return $tablica;
	}
	public function ListaAdresy($_id){
		$query="Select orders_id as dka_Id, orders_id as dka_Dokument, billing_street_address as dka_Ulica, billing_postcode as dka_Kod, billing_city as dka_Miasto, case when LENGTH(customers_company) > 0 then billing_company else billing_name end dka_Nazwa, 2 dka_Typ,billing_nip as dka_NIP, customers_country as dka_Kraj from orders Where orders_id=:orders_id Union ALL Select orders_id as dka_Id, orders_id as dka_Dokument, delivery_street_address as dka_Ulica, delivery_postcode as dka_Kod, delivery_city as dka_Miasto, case when LENGTH(delivery_company) > 0 then delivery_company else delivery_name end dka_Nazwa, 3 dka_Typ, delivery_nip as dka_NIP, delivery_country as dka_Kraj  from orders Where orders_id=:orders_id2";
		$sth = $this->Conn->prepare($query);
		$sth->bindParam(':orders_id', $_id, PDO::PARAM_INT);
		$sth->bindParam(':orders_id2', $_id, PDO::PARAM_INT);
		$sth->execute();
		
		$tablica = array();
		
		while ($row = $sth->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT))
		{
			foreach ($row as $k => $v)
			{
				$tablica[1][$k]=htmlspecialchars(trim($v), ENT_QUOTES);
				$tablica[$row['dka_Typ']][$k]=htmlspecialchars(trim($v), ENT_QUOTES);
			}	
			
		}

		return $tablica;
	}

	 public static function GetIdObce($_klucz, $_Conn){
		$zwrot=true;
		$query="Select orders_id from orders Where dok_KluczObcy=:dok_KluczObcy ";
		$sth = $_Conn->prepare($query);
		$sth->bindParam(':dok_KluczObcy', $_klucz, PDO::PARAM_INT);	
		$sth->execute();

		while ($row = $sth->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT))
		{
			$zwrot = $row['orders_id'];
			
		}

		 return $zwrot;
	 } 
	
}
?>
