EXCELIA_DBARR/custom/bbus/class/api_bbus_helper.class.php

<?php

use Luracast\Restler\RestException;

include_once DOL_DOCUMENT_ROOT.'/societe/class/societe.class.php';

class ApiBBusHelper
{
	public $db;
	const EMAIL_TEMPLATE = 'multiticketprinting';
	const GLOBAL_CONF_SEND_TO_EMAIL = 'BBUS_INVOICE_PRINTING_ALERT_EMAIL';
	const GLOBAL_CONF_EMAIL_FROM = 'BBUS_INVOICE_PRINTING_ALERT_EMAIL_FROM';

	public function __construct()
	{
		global $db, $user;

		$this->db = $db;
	}

	public function getSaledItems($date)
	{
		global $user, $db;
		$from = $this->getGroupLoginDate($date);
		$to = $this->getGroupLogout($date, $from);
		$fulldate = date("Y-m-d H:i:s", dol_now());
		$facturesArray = [];
		if(strtotime($fulldate) > strtotime($to)){
			return $facturesArray;
		}

		$sql = "SELECT f.rowid, pa.fk_product_pere, pr.label, pa.fk_product_fils, fdet.multicurrency_total_ttc, fdet.multicurrency_code FROM
		llx_facture AS f
		INNER JOIN llx_facturedet AS fdet ON fdet.fk_facture = f.rowid
		INNER JOIN llx_product_association AS pa ON pa.fk_product_fils = fdet.fk_product
		INNER JOIN llx_product AS pr ON pr.rowid = pa.fk_product_pere
		WHERE f.fk_user_author = {$user->id}
		AND f.entity = {$user->entity}
		AND f.fk_statut = 2
		AND f.datec BETWEEN '{$from}' AND '{$to}'
		UNION 
		SELECT f.rowid, fdet.fk_product as fk_product_pere, pr.label, fdet.fk_product as fk_product_fils, fdet.multicurrency_total_ttc, fdet.multicurrency_code FROM
		llx_facture AS f
		INNER JOIN llx_facturedet AS fdet ON fdet.fk_facture = f.rowid
		INNER JOIN llx_product AS pr ON pr.rowid = fdet.fk_product
		INNER JOIN llx_product_extrafields as pre ON pre.fk_object = pr.rowid
		WHERE f.fk_user_author = {$user->id}
		AND f.entity = {$user->entity}
		AND f.fk_statut = 2
		AND pre.is_in_bundle IS NULL
		AND f.datec BETWEEN '{$from}' AND '{$to}'";
		//print  $sql;exit;
		$facatures = $db->query($sql);
		$this->checkValidation($facatures, 'Factures');
		while ($row = pg_fetch_assoc($facatures)) {
			$facturesArray[] = $row;
		}
		return $facturesArray;
	}

	public function getGroupLoginDate($date)
	{
		global $user, $db;
		$sqlusernaploLogin = "SELECT date_creation FROM public.llx_settlements_usernaplo
		WHERE user_id = $user->id
		AND status = 1
		ORDER BY rowid DESC LIMIT 1";
		$resultLogin = $db->query($sqlusernaploLogin);
		if(pg_num_rows($resultLogin) > 0 && strtotime($date) == strtotime(date('Y-m-d', dol_now()))){
			while ($row = pg_fetch_assoc($resultLogin)) {
				return $row['date_creation'];
			}
		}
		return $date . ' 00:00:00';
	}

	public function getGroupLogout($date, $from)
	{
		global $user, $db;
		$sqlusernaploLogin = "SELECT date_creation FROM public.llx_settlements_usernaplo
		WHERE user_id = $user->id
		AND status = 0
		ORDER BY rowid DESC LIMIT 1";
		$resultLogin = $db->query($sqlusernaploLogin);
		if(pg_num_rows($resultLogin) > 0){
			while ($row = pg_fetch_assoc($resultLogin)) {
				$to = $row['date_creation'];
			}
		}
		return $from > $to ? $date . ' ' . date('H:i:s', dol_now()) : $to;
	}

	public function getSaledItemsArray($facturesArray)
	{
		$pereArray = [];
		foreach ($facturesArray as $item) {
			$pereArray[$item['rowid']]['pere'] = $item['fk_product_pere'];
			$pereArray[$item['rowid']]['label'] = $item['label'];
			if (isset($item['rowid'])) {
				$pereArray[$item['rowid']]['sum'] += $item['multicurrency_total_ttc'];
			} else {
				$pereArray[$item['rowid']]['sum'] = $item['multicurrency_total_ttc'];
			}
			$pereArray[$item['rowid']]['currency'] = $item['multicurrency_code'];
		}
		$resultArray = [];
		foreach ($pereArray as $record) {
			if (isset($record['pere'])) {
				$resultArray[$record['pere']]['sum'] += $record['sum'];
			} else {
				$resultArray[$record['pere']]['sum'] = $record['sum'];
			}
			if (isset($record['pere'])) {
				$resultArray[$record['pere']]['count']++;
				;
			} else {
				$resultArray[$record['pere']]['count'] = 1;
			}
			$resultArray[$record['pere']]['label'] = $record['label'];
			$resultArray[$record['pere']]['currency'] = $record['currency'];
		}

		$array = [];
		foreach ($resultArray as $row) {
			$array[] = $row;
		}

		return $array;
	}

	private function checkValidation($res, $name)
	{
		if (pg_num_rows($res) == 0) {
			throw new RestException(404, $name . ' not found');
		}
	}

	public function getFactureIdForInvoicePrinting($ref)
	{
		global $db;
		$sql = "SELECT * FROM llx_facture WHERE ref = '{$ref}'";
		$resultBBT = $db->query($sql);
		if (pg_num_rows($resultBBT) < 1) {
			throw new RestException(404, 'Invoice not found');
		}
		while ($row = pg_fetch_assoc($resultBBT)) {
			return $row['rowid'];
		}
	}

	public function setPrintingInvoiceObject($user, $facture_id, $datetime, $datetime_timestamp, $ticket, $ref)
	{
		global $db;
		$newCopy = new BbTicketInvoicePrinting($db);
		$newCopy->fk_user_api_key = $user->api_key;
		$newCopy->fk_facture = $facture_id;
		$newCopy->printing_date = $datetime;
		$newCopy->printing_date_timestamp = $datetime_timestamp;
		$newCopy->ticket_id = $ticket->id; //bbticket -> rowid
		$newCopy->product_id = $ticket->ticket_id; // product -> rowid = bbticket->ticket_id
		$newCopy->invoice_number = $ref; // product -> rowid = bbticket->ticket_id
		if ($newCopy->create($user) > 0) {
			// successful saving
		} else {
			dol_syslog("Nem sikerult a bbticketinvoiceprinting insertje. facture_id: {$facture_id} datetime: {$datetime}");
			throw new RestException(404, 'Insert failed');
		}
	}

	public function getTicketIdsByFactureId($facture_id)
	{
		$object = new stdClass();
		$object->fk_facture = $facture_id;
		$object->fk_product = $this->getproductIdFromFActuredet($facture_id);
		$bbTicketHandler = new BbTicketHandler();
		// uj rekordot veszek fel a bbticket tablaba és visszaterek a rogzitett rekordok roid-javal
		return $bbTicketHandler->addTicketForPrinting($object);
	}

	private function getproductIdFromFActuredet($facture_id)
	{
		global $db;
		$array = [];
		$sql = "SELECT fk_product FROM public.llx_facturedet WHERE fk_facture = " . $facture_id;
		$result = $db->query($sql);
		while ($facturedetrecord = pg_fetch_assoc($result)) {
			$array[] = $facturedetrecord['fk_product'];
		}
		return $array;
	}

	public function sendMail($fk_facture, $datetime, $now): void
	{
		$const = new GlobalConst;
		$recipients = $const->load(self::GLOBAL_CONF_SEND_TO_EMAIL);
		$emailFrom = $const->load(self::GLOBAL_CONF_EMAIL_FROM);
		//$recipients = 'szollosi.laszlo@urbanms.hu';
		//$emailFrom = 'bbus@urbanms.hu';
		if (!empty($recipients) && !empty($emailFrom)) {
			$emailTemplateHandler = new EmailTemplateHandler;
			$template = $emailTemplateHandler->load(self::EMAIL_TEMPLATE);
			$templateObject = new stdClass();
			foreach ($template as $key => $value) {
				$templateObject->$key = $value;
			}
			if ($templateObject) {
				$vars = [
					'__FACTURE__' => $fk_facture,
					'__DATETIME__' => $datetime,
					'__PRINTINGDATE__' => $now
				];
				$template = $emailTemplateHandler->prepareTemplate($templateObject, $vars);

				$mail = new CMailFile(
					$template->topic,
					$recipients,
					$emailFrom,
					$template->content,
					null,
					null,
					null,
					'',
					'',
					0,
					1
				);
				// Send Email
				$mail->sendfile();

				if ($mail->error) {
					//print_r($mail->error);
					//exit;
					// do something...
					dol_syslog('CRON ALERT EMAIL ERROR: ' . $mail->error, LOG_ERR);
				}
			}
		}
	}
	/**
	 * 
	 */
	public function getAppCustomers(): array
	{		
		$customers = [];

		$table = (new Societe($this->db))->table_element;

		$sql = "
			SELECT soc.rowid AS id, soc.nom AS name, soc.address, soc.zip, soc.town, (CASE WHEN soc.nom ILIKE '%eur%' THEN 'EUR' ELSE 'HUF' END) AS currency
			FROM ".MAIN_DB_PREFIX.$table." as soc
			INNER JOIN ".MAIN_DB_PREFIX.$table."_extrafields as socex ON soc.rowid = socex.fk_object
			WHERE socex.is_general_customer = 1
			ORDER BY soc.rowid ASC 
		";
		$rows = $this->db->query($sql);		
		if ($rows) {
			while ($row = $this->db->fetch_object($rows)) {
				
				$row->full_address = implode(' ', [
					$row->zip,
					$row->town,
					$row->address
				]);
				
				$customers[$row->currency] = $row;				
			}
		}

		return $customers;
	}

	function getAccountRowid($entity)
	{
		$sql = "SELECT ba.currency_code, ba.rowid FROM llx_bank_account as ba WHERE entity = {$entity} ORDER BY rowid ASC";
		//$sql = "SELECT mc.code, mc.rowid FROM llx_multicurrency as mc WHERE entity = {$entity}";
		$data = $this->db->query($sql);
		$accounts = [];
		while ($row = pg_fetch_array($data)) {
			$accounts[$row['currency_code']] = $row['rowid'];
			//$accounts[$row['code']] = $row['rowid'];
		}
		return $accounts;
	}

	function getCurrenciesRowid($entity)
	{
		$sql = "SELECT mc.rowid, mc.code FROM llx_multicurrency as mc WHERE entity = {$entity} ORDER BY rowid ASC";
		$data = $this->db->query($sql);
		$currencies = [];
		while ($row = pg_fetch_array($data)) {
			$currencies[$row['code']] = $row['rowid'];
		}
		return $currencies;
	}

	function getPaymentsMode($entity)
	{
		$paymentsModeArray = [];
		$table = (new Paiement($this->db))->table_element;
		$sql = "SELECT id, code, libelle FROM " . MAIN_DB_PREFIX. 'c_' . $table . " WHERE code IN ('LIQ', 'CB') AND entity = {$entity}";
		$rows = $this->db->query($sql);
		if(pg_num_rows($rows) > 0){
			$result = pg_fetch_all($rows);
			foreach($result as $record){
				$paymentsModeArray[] = ['id' => $record['id'], 'label' => $record['libelle'], 'code' => $record['code']];
			}
		}
		return $paymentsModeArray;
	}

	public function getCompany(): array
	{
		global $user;

		$entity = null;
		$result = [];

		$data = $this->getCompanyData($user->id);
		//print_r($data);exit;
		if (empty($data)) {
			$entityId = (empty($user->entity)) ? 1 : $user->entity;
			$data = $this->getCompanyData(null, $entityId);
		}

		if (!empty($data)) {
			foreach ($data as $row) {
				$newStr = substr($row['name'], strlen('MAIN_INFO_'));
				if ($newStr != 'TVAINTRA') {
					$newStr = substr($newStr, strlen('SOCIETE_'));
				}
				$result[$newStr] = $row['value'];

				if (!empty($row['entity']) && is_null($entity)) {
					$entity = intval($row['entity']);
				}
			}
		}


		/*
																	$sql = "
																		SELECT c.* 
																		FROM ".MAIN_DB_PREFIX."settlements_groupusers gu
																		INNER JOIN ".MAIN_DB_PREFIX."settlements_group AS g ON g.rowid = gu.fk_settlements_group
																		INNER JOIN ".MAIN_DB_PREFIX."const AS c ON c.entity = g.fk_entity
																		WHERE gu.fk_user = {$user->id} AND c.name IN (
																			'MAIN_INFO_SOCIETE_COUNTRY', 
																			'MAIN_INFO_SOCIETE_NOM', 
																			'MAIN_INFO_SOCIETE_ADDRESS', 
																			'MAIN_INFO_SOCIETE_TOWN',
																			'MAIN_INFO_SOCIETE_ZIP',
																			'MAIN_INFO_SOCIETE_STATE',
																			'MAIN_INFO_TVAINTRA'
																		)";
																	$entityData = $this->db->query($sql);
																	while ($row = $this->db->fetch_array($entityData)) {
																		$newStr = substr($row['name'], strlen('MAIN_INFO_'));
																		if ($newStr != 'TVAINTRA') {
																			$newStr = substr($newStr, strlen('SOCIETE_'));
																		}
																		$result[$newStr] = $row['value'];

																		if (!empty($row['entity']) && is_null($entity)) {
																			$entity = intval($row['entity']);
																		}

																	}
																	*/


		return [
			'company_data' => $result,
			'entity' => $entity
		];
	}

	public function getCompanyData(int $userId = null, int $entity = null): array
	{
		$result = [];

		$where = '';
		if (!empty($userId)) {
			$where = 'gu.fk_user = ' . $userId;
		} else if (!empty($entity)) {
			$where = 'c.entity = ' . $entity;
		}

		if (!empty($where)) {
			$where .= ' AND ';
		}

		$sql = "
			SELECT c.* 
			FROM " . MAIN_DB_PREFIX . "settlements_groupusers gu
			INNER JOIN " . MAIN_DB_PREFIX . "settlements_group AS g ON g.rowid = gu.fk_settlements_group
			INNER JOIN " . MAIN_DB_PREFIX . "const AS c ON c.entity = g.fk_entity
			WHERE {$where} c.name IN (
				'MAIN_INFO_SOCIETE_COUNTRY', 
				'MAIN_INFO_SOCIETE_NOM', 
				'MAIN_INFO_SOCIETE_ADDRESS', 
				'MAIN_INFO_SOCIETE_TOWN',
				'MAIN_INFO_SOCIETE_ZIP',
				'MAIN_INFO_SOCIETE_STATE',
				'MAIN_INFO_TVAINTRA'
			)";
		$entityData = $this->db->query($sql);
		if ($entityData) {
			while ($row = $this->db->fetch_array($entityData)) {
				$result[] = $row;
			}
		}

		return $result;
	}
}