se/SE/se-lib/CompanyHelper.php

<?php


class CompanyHelper {

	private $_db;
	private $_tbl;

	public function __construct($db) {
		$this->_db = $db;
		$this->_tbl = "COMPANIES";
	}

	public function getById($id) {
		if (!$this->_db) return false;
		$user = null;
		$sql = "select bua.`id_users`
				, bua.`id_users` as ID_BILLING_USERS
				, bua.`is_firma`
				, bua.`P_NAME`
				, bua.`P_NAME_SECOND`
				, bua.`P_NIP`
				, bua.`P_PESEL`
				, bua.`P_PHONE`
				, bua.`P_ADDRESS_REGION`
				, bua.`P_ADDRESS_CITY`
				, bua.`P_ADDRESS_POST_CODE`
				, bua.`P_ADDRESS_STREET`
				, bua.`P_ADDRESS_HOME`
				, bua.`P_ADDRESS_HOUSE`
				, bua.`user_mail_contact`
				, bu.`BILLING_OWNER`
			from `BILLING_USERS_ADD` as bua
				left join `BILLING_USERS` as bu on(bu.`ID`=bua.`id_users`)
			where bua.`id_users`='{$id}'
		";
		$sql = "select c.`ID`
				, c.`ID` as id_users
				, c.`ID` as ID_BILLING_USERS
				, c.`is_firma`
				, c.`P_NAME`
				, c.`P_NAME_SECOND`
				, c.`P_NIP`
				, c.`P_PESEL`
				, c.`P_PHONE`
				, c.`P_ADDRESS_REGION`
				, c.`P_ADDRESS_CITY`
				, c.`P_ADDRESS_POST_CODE`
				, c.`P_ADDRESS_STREET`
				, c.`P_ADDRESS_HOME`
				, c.`P_ADDRESS_HOUSE`
				, c.`user_mail_contact`
				, c.`BILLING_OWNER`
				, c.`CRM_LISTA_ZASOBOW_ID`
			from `{$this->_tbl}` as c
			where c.`ID`='{$id}'
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$user = $r;
		}
		return $user;
	}

	public function getByNip($nip) {
		if (!$this->_db) return false;
		$user = null;
		$sqlNip = trim(str_replace('-', '', $nip));
		$sql = "select c.`ID`
				, c.`ID` as id_users
				, c.`ID` as ID_BILLING_USERS
				, c.`is_firma`
				, c.`P_NAME`
				, c.`P_NAME_SECOND`
				, c.`P_NIP`
				, c.`P_PESEL`
				, c.`P_PHONE`
				, c.`P_ADDRESS_REGION`
				, c.`P_ADDRESS_CITY`
				, c.`P_ADDRESS_POST_CODE`
				, c.`P_ADDRESS_STREET`
				, c.`P_ADDRESS_HOME`
				, c.`P_ADDRESS_HOUSE`
				, c.`user_mail_contact`
				, c.`BILLING_OWNER`
				, c.`CRM_LISTA_ZASOBOW_ID`
			from `{$this->_tbl}` as c
			where replace(c.`P_NIP`, '-', '')='{$sqlNip}'
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$user = $r;
		}
		return $user;
	}

	/**
	 * @param object $company @see getById
	 */
	public function getBillingOwner($company) {
		if (!$this->_db) return false;
		if ($company) {
			if ($company->BILLING_OWNER > 0) {
				$owner = null;
				/*
					CREATE TABLE IF NOT EXISTS `BILLING_OWNER` (
						`id` int(10) NOT NULL AUTO_INCREMENT,
						`name1` varchar(255) NOT NULL DEFAULT '',
						`name2` varchar(255) NOT NULL DEFAULT '',
						`kod` varchar(6) NOT NULL DEFAULT '',
						`miasto` varchar(150) NOT NULL DEFAULT '',
						`ulica` varchar(255) NOT NULL DEFAULT '',
						`numer_dom` int(5) DEFAULT NULL,
						`numer_pos` tinyint(5) DEFAULT NULL,
						`uwagi` text NOT NULL,
						`tel` varchar(100) NOT NULL DEFAULT '',
						`fax` varchar(100) NOT NULL DEFAULT '',
						`nip` varchar(13) NOT NULL DEFAULT '',
						`regon` varchar(10) NOT NULL DEFAULT '',
						`bank` varchar(255) NOT NULL DEFAULT '',
						`nr_rach` varchar(255) NOT NULL DEFAULT '',
						`NR_RACH_MASS_PAY` varchar(24) DEFAULT NULL,
						`BILLING_OWNER_EMAIL` varchar(100) NOT NULL DEFAULT '',
						`CRM_LISTA_ZASOBOW_ID` int(11) NOT NULL,
						UNIQUE KEY `id` (`id`)
					) ENGINE=MyISAM  DEFAULT CHARSET=latin2 AUTO_INCREMENT=3 ;

					INSERT INTO `BILLING_OWNER` (`id`, `name1`, `name2`, `kod`, `miasto`, `ulica`, `numer_dom`, `numer_pos`, `uwagi`, `tel`, `fax`, `nip`, `regon`, `bank`, `nr_rach`, `NR_RACH_MASS_PAY`, `BILLING_OWNER_EMAIL`, `CRM_LISTA_ZASOBOW_ID`) VALUES
					(1, '"BIALL-NET" Sp. z o.o.', '', '80-174', 'Gdańsk, Otomin', 'Słoneczna', 43, NULL, '', '0-58 320-72-92', '0-58 320-72-96', '593-22-68-672', '192120212', 'Bank Zachodni WBK SA I O/Gdansk', '46 1090 1098 0000 0001 0253 7156', '109000049887', 'biall-net@biall.net.pl', 12889),
					(2, '"NET-DAY" s.c.', 'Adrian i Ewa Wieczorkowscy', '80-809', 'Gdańsk', 'Kopeckiego', 9, 24, '', '0-58 741 84 54', '0-58 741 84 56', '583-27-54-031', '192578721', '', '84 1500 1171 1211 7002 9997 0000', '109000049669', 'netday@netday.pl', 12919);

				 */
				$sql = "select o.*
					from `BILLING_OWNER` as o
					where o.`ID`='{$company->BILLING_OWNER}'
				";
				$res = $this->_db->query($sql);
				if ($r = $this->_db->fetch($res)) {
					$owner = $r;
				}
				return $owner;
			}
		}
		return null;
	}

	/**
	 * Powiązanie zasobu z companies za pomocą $company->CRM_LISTA_ZASOBOW_ID
	 */
	public function getZasobPodmiot($company, $billingOwner) {
		if (!$this->_db) return false;
		if (!$company || !$billingOwner) return false;
		if (!$billingOwner->CRM_LISTA_ZASOBOW_ID) return false;

		if (!$company->CRM_LISTA_ZASOBOW_ID) return null;

		$zasob = null;
		$sql = "select z.*
			from `CRM_LISTA_ZASOBOW` as z
			where z.`ID`='{$company->CRM_LISTA_ZASOBOW_ID}'
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$zasob = $r;
		}
		return $zasob;
		//if ($zasob->PARENT_ID == $billingOwner->CRM_LISTA_ZASOBOW_ID) {
		//	return $zasob;
		//} else {
		//	return false;
		//}
	}

	public function createZasobPodmiot($company, $billingOwner) {
		if (!$this->_db) return false;
		if (!$company || !$billingOwner) return false;
		if (!$billingOwner->CRM_LISTA_ZASOBOW_ID) return false;

		if ($company->CRM_LISTA_ZASOBOW_ID > 0) return null;

		$podmiot = new stdClass();
		$podmiot->PARENT_ID = $billingOwner->CRM_LISTA_ZASOBOW_ID;
		$podmiot->TYPE = 'PODMIOT';
		$podmiot->DESC = "{$company->P_NAME} {$company->P_NAME_SECOND}";

		$zasobId = $this->_db->ADD_NEW_OBJ('CRM_LISTA_ZASOBOW', $podmiot);
		if ($zasobId > 0) {
			$sqlCom = new stdClass();
			$sqlCom->ID = $company->ID;
			$sqlCom->CRM_LISTA_ZASOBOW_ID = $zasobId;
			$affeted = $this->_db->UPDATE_OBJ($this->_tbl, $sqlCom);
		}
		return $zasobId;
	}

	/**
	 * Powiązane konta do autoryzacji w systemie
	 */
	public function getAccountsList($company) {
		if (!$this->_db) return false;
		if (!$company) return false;
		if (!$company->CRM_LISTA_ZASOBOW_ID) return false;

		$accList = null;
		$sql = "select u.*
				, count(up.ID) as cntProfile
			from `ADMIN_USERS` as u
				left join `CRM_AUTH_PROFILE` as up on(up.`REMOTE_ID`=u.`ID` and up.`REMOTE_TABLE`='ADMIN_USERS')
			where (u.`ADM_ACCOUNT` like 'c{$company->ID}%' or u.`ID_BILLING_USERS`='{$company->ID}')
			group by u.`ID`
		";
		$res = $this->_db->query($sql);
		while ($r = $this->_db->fetch($res)) {
			$accList[$r->ID] = $r;
		}
		return $accList;
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_deals_active_by_user($ID_BILLING_USERS, $only_active = true)
	 */
	public function getDealsFull($ID_BILLING_USERS, $only_active = true) {
		$active_deals_by_id = $this->getDealsActive(array('ID_BILLING_USERS'=>$ID_BILLING_USERS, 'return_by'=>'ID', 'P_DEALNUMBER'=>true));
		$this->addDealsActiveInfo($active_deals_by_id, $ID_BILLING_USERS, $only_active);
		return $active_deals_by_id;
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::deals_active_add_info(&$active_deals_by_id, $ID_BILLING_USERS, $only_active = false)
	 */
	public function addDealsActiveInfo(&$active_deals_by_id, $ID_BILLING_USERS, $only_active = false) {
		if (empty($active_deals_by_id)) {
			return;
		}
		$uslugi = $this->getServicesByDeals($ID_BILLING_USERS, array_keys($active_deals_by_id), $only_active);
		foreach ($active_deals_by_id as $k_deal_id => $v_deal) {
			if (array_key_exists($k_deal_id, $uslugi)) {
				$active_deals_by_id[$k_deal_id]->services = $uslugi[$k_deal_id];
				$active_deals_by_id[$k_deal_id]->services_by_id = array();
				foreach ($uslugi[$k_deal_id] as $k_p_service => $v_srv_arr) {
					foreach ($v_srv_arr as $k_srv_id => $v_srv) {
						$active_deals_by_id[$k_deal_id]->services_by_id[$k_srv_id] = $v_srv;
					}
				}
			}
		}

		// add $v_deal->ses_x_a
		$out_id_count = array();
		$uslugi_params = $this->getServicesParams($uslugi);
		foreach ($active_deals_by_id as $k_deal_id => $v_deal) {
			$active_deals_by_id[$k_deal_id]->ses_x_a = array();
			foreach ($v_deal->services as $k_p_service => $v_srv_arr) {
				foreach ($v_srv_arr as $k_srv_id => $v_srv) {
					$v_deal->services[$k_p_service][$k_srv_id]->offer_p_service = $k_p_service;

					// add SERVICE params if exists
					if (array_key_exists($k_srv_id, $uslugi_params)) {
						$active_deals_by_id[$k_deal_id]->ses_x_a[$k_srv_id] = $uslugi_params[$k_srv_id];
						if ($v_srv->NAME_LIST_SERVICES == 'TV' && $uslugi_params[$k_srv_id]->P_SERVICE_CLASS != '') {
							$v_deal->services[$k_p_service][$k_srv_id]->offer_p_service = $uslugi_params[$k_srv_id]->P_SERVICE_CLASS;
						}
					}

					// add out_id - @used in JavaScript overview
					if (!in_array($v_srv->offer_p_service, $out_id_count)) {
						$out_id_count[$v_srv->offer_p_service] = 1;
					} else {
						$out_id_count[$v_srv->offer_p_service] += 1;
					}
					$v_deal->services[$k_p_service][$k_srv_id]->out_id = $v_srv->offer_p_service . '_' . $out_id_count[$v_srv->offer_p_service];
				}
			}
		}
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_services_by_user_deals($user_id, $deals_ids = array(), $only_active = false)
	 */
	public function getServicesByDeals($user_id, $deals_ids = array(), $only_active = false) {
		$deals = array();
		if (empty($deals_ids)) {
			return $deals;
		}
		$db = DB::getDB();
		$sql = "select
				s.`ID`, s.`NAME_LIST_SERVICES`, s.`A_STATUS`
				, s.`ID_OFFERS`
				, s.`ID_DEALS_TABLE`
				, s.`P_ID_SERVICES`
				, s.`ABONAMENT_VALUE` as ABONAMENT_VALUE
				, s.`P_ID_SERVICES` as P_ID
				, s.`HANGUP_STATUS`
				, s.`HANGUP_FROM`
				, s.`HANGUP_TILL`
				, ls.`description` as description
		--		, lst.`name` as A_STATUS_DESC
				, '' as A_STATUS_DESC
			from `SERVICES` as s
				left join `LIST_SERVICES` as ls on(ls.`name`=s.`NAME_LIST_SERVICES`)
		--		left join `LIST_status` as lst on(lst.`ses_users2`=s.`A_STATUS`)
		--		left join `LIST_status` as lst on(lst.`ses_users2`=s.`HANGUP_STATUS` and lst.`ID`>2)
			where `ID_BILLING_USERS`='" . $user_id . "'
				and `ID_DEALS_TABLE` in(" . implode(",", $deals_ids) . ")
		--		and s.`A_STATUS`='NORMAL'
		";
		if(V::get('DBG_SQL', 0, $_GET, 'int') > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {

			// TODO: only active services - @from $A_STATUS_L2_SQL
			// $r->HANGUP_STATUS = ('NORMAL', 'OFF_SOFT', 'OFF_HARD')
			$r->HANGUP_TILL = (!$r->HANGUP_TILL || $r->HANGUP_TILL == '0000-00-00')? null : $r->HANGUP_TILL;
			$r->HANGUP_FROM = (!$r->HANGUP_FROM || $r->HANGUP_FROM == '0000-00-00')? null : $r->HANGUP_FROM;
			$r->_status = null;
			$today = date("Y-m-d");
			if ($r->HANGUP_STATUS == $r->A_STATUS) {
				$r->_status = $r->A_STATUS;
			} else if (!$r->HANGUP_TILL && $r->HANGUP_FROM > '' && $today > $r->HANGUP_FROM) {
				$r->_status = $r->HANGUP_STATUS;
			} else if (!$r->HANGUP_TILL && $r->HANGUP_FROM > '' && $today < $r->HANGUP_FROM) {
				$r->_status = $r->A_STATUS;
			} else if ($r->HANGUP_TILL > '' && $r->HANGUP_FROM > '' && $today > $r->HANGUP_FROM && $today < $r->HANGUP_TILL) {
				$r->_status = $r->HANGUP_STATUS;
			} else if ($r->HANGUP_TILL > '' && $r->HANGUP_FROM > '' && $today < $r->HANGUP_FROM && $today < $r->HANGUP_TILL) {
				$r->_status = $r->A_STATUS;
			} else if (!$r->HANGUP_TILL && !$r->HANGUP_FROM) {
				$r->_status = $r->A_STATUS;
			} else if ($r->HANGUP_TILL > '' && $r->HANGUP_FROM > '' && $today > $r->HANGUP_FROM && $today > $r->HANGUP_TILL) {
				$r->_status = $r->A_STATUS;
			} else if ($r->HANGUP_TILL > '' && $r->HANGUP_FROM > '' && $today > $r->HANGUP_FROM && $today > $r->HANGUP_TILL && $r->HANGUP_FROM < $r->HANGUP_TILL) {
				$r->_status = 'ERROR1';
			} else {
				$r->_status = 'ERROR2';
			}

			$r->A_STATUS_DESC = $this->getStatusName($r->_status);

			if ($only_active && !in_array($r->_status, array('NORMAL'))) {
				continue;
			}

			// TODO: P_SERVICE used?
			$r->P_SERVICE = $r->NAME_LIST_SERVICES;
			if ($r->P_SERVICE == 'USERS2') $r->P_SERVICE = 'NET';
			else if ($r->P_SERVICE == 'VOIP') $r->P_SERVICE = 'TEL';

			$deals[$r->ID_DEALS_TABLE][$r->P_SERVICE][$r->ID] = $r;
		}
		return $deals;
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_l2_list_status_name($status)
	 */
	function getStatusName($status) {
		if (!$this->_db) return false;
		if (!$this->_l2_list_status) {
			$this->_l2_list_status = array();
			$sql = "select lst.`ses_users2`, lst.`name`, lst.`description`
				from `LIST_status` as lst
				where lst.`ID`>2
			";
			$res = $this->_db->query($sql);
			while ($r = $this->_db->fetch($res)) {
				$this->_l2_list_status[$r->ses_users2] = $r->name;
			}
		}
		if (array_key_exists($status, $this->_l2_list_status)) {
			return $this->_l2_list_status[$status];
		}
		return null;
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_services_params($uslugi)
	 */
	public function getServicesParams($uslugi) {
		$ses_x_a = array();
		if (empty($uslugi)) return $ses_x_a;

		$allowed_name_list_srv = array('USERS2', 'TV', 'VOIP');

		$load_ses_x_a_ids = array();
		foreach ($uslugi as $k_deal_id => $v_deal_arr) {
			foreach ($v_deal_arr as $k_p_service => $v_srv_arr) {
				if (empty($v_srv_arr)) continue;
				foreach ($v_srv_arr as $k_srv_id => $v_srv) {
					$load_ses_x_a_ids [$v_srv->NAME_LIST_SERVICES] []= $v_srv->ID;
				}
			}
		}
		// load from DB
		$db = DB::getDB();
		foreach ($load_ses_x_a_ids as $k_name_list_srv => $v_srv_ids) {
			if (!in_array($k_name_list_srv, $allowed_name_list_srv)) continue;
			$sql = "select * from `SES_" . $k_name_list_srv . "_A` where `ID_SERVICES` in (" . implode(", ", $v_srv_ids) . ")";
			$res = $db->query($sql);
			while ($r = $db->fetch($res)) {
				$ses_x_a [$r->ID_SERVICES] = $r;
			}
		}
		return $ses_x_a;
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_deals_active($params)
	 */
	public function getDealsActive($params = array()) {
		if (!$this->_db) return false;
		$sqlReturnBy = V::get('return_by', '', $params);

		$forceUpdate = false;
		Lib::loadClass('ColumnDealsStatus');
		ColumnDealsStatus::run_update($forceUpdate, $dbg_msgs);

		$dealsActive = array();
		$sql_left_join = "";
		$sql_select_arr = array();
		$sql_select_arr []= "ds.`ID_DEALS`";
		$sql_select_arr []= "ds.`ID_BILLING_USERS`";
		$sql_select_arr []= "ds.`SERVICES_S_ADDRESS_STREET`";
		$sql_select_arr []= "concat(c.`P_NAME`, ' ', c.`P_NAME_SECOND`) as P_NAME";
		if ('' != V::get('ID_BILLING_USERS', '', $params)) {
			$sql_left_join .= "\n left join `DEALS_TABLE` as d on (d.`ID`=ds.`ID_DEALS`)";
			$sql_select_arr []= "d.`P_DEALNUMBER`";
			$sql_select_arr []= "d.`P_DEALDATE`";
			$sql_select_arr []= "d.`P_DEALDATE_TERM`";
			$sql_select_arr []= "d.`ID_OFFERS_GROUP`";
			$sql_select_arr []= "d.`ID_OFFERS_ON`";
			$sql_select_arr []= "d.`ID_OFFERS_OFF`";
			$sql_select_arr []= "d.`S_ADDRESS_STREET`";
		}
		$sql_where_arr = array();
		$sql_where_arr []= "ds.`DEALS_ACTIVE`=1";
		if ('' != ($user_id = V::get('ID_BILLING_USERS', '', $params))) {
			$sql_where_arr []= "ds.`ID_BILLING_USERS`='{$user_id}'";
		}
		if ('' != ($street = V::get('S_ADDRESS_STREET', '', $params))) {
			$sql_where_arr []= "(ds.`SERVICES_S_ADDRESS_STREET`='{$street}' or concat(',', ds.`SERVICES_S_ADDRESS_STREET`, ',') like '%,{$street},%')";
		}
		$sql_where_arr = implode("\n and ", $sql_where_arr);
		$sql = "select " . implode("\n, ", $sql_select_arr) . "
			from `temp_DEALS_STATUS` as ds
				left join `{$this->_tbl}` as c on (c.`ID`=ds.`ID_BILLING_USERS`)
				{$sql_left_join}
			where {$sql_where_arr}
			order by ds.`ID_DEALS` DESC
		";
		if(V::get('DBG_SQL', 0, $_GET, 'int') > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $this->_db->query($sql);
		while ($r = $this->_db->fetch($res)) {
			if ($sqlReturnBy == 'ID') {
				$dealsActive [$r->ID_DEALS]= $r;
			} else {
				$dealsActive [$r->ID_BILLING_USERS] []= $r;
			}
		}
		return $dealsActive;
	}

	/**
	 * Emulates TypespecialVariable->getValuesWithExports($query)
	 * TODO: only groups visible for current Partner - $company
	 */
	public function searchTypeSpecialPartnerGroups($company, $query) {
		$values = array();
		$db = DB::getDB();
		$query = trim($query, ' %');
		$query = $db->_($query);
		$sqlLimit = 20;
		$sql = "select z.`ID`, z.`DESC`, z.`TYPE`
			from `CRM_LISTA_ZASOBOW` as z
			where z.`A_STATUS` in('NORMAL', 'WAITING')
				and z.`TYPE` in('STANOWISKO', 'PODMIOT', 'DZIAL')
				and (z.`DESC` like '%{$query}%' or z.`ID` like '%{$query}%')
			group by z.`DESC`
			limit {$sqlLimit}
		";
		if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$values[] = (object)array('id'=>$r->ID, 'param_out'=>$r->TYPE . ' ' . $r->DESC);
		}
		if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
		return $values;
	}

	public function setTableName($tbl) {
		$this->_tbl = $tbl;
	}

}