db = $db; $this->langs = $langs; } function getNumberOfUsers() { $userCounter = []; $sql = "SELECT su.fk_group_tools_id, su.fk_groupid, count(sgu.rowid) as usercount FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup AS su INNER JOIN " . MAIN_DB_PREFIX . "settlements_groupusers AS sgu ON sgu.fk_settlements_group = su.fk_groupid GROUP BY su.fk_groupid, su.fk_group_tools_id"; $data = $this->db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('queryFail', '(Function $helper->getNumberOfUsers()) llx_settlements_usergrouptoolgroup'), 'errors'); return $userCounter; } while ($row = pg_fetch_assoc($data)) { $userCounter[$row['fk_group_tools_id']] = [$row['usercount'], $row['fk_groupid']]; } return $userCounter; } function getNumberOfDevices() { $deviceCounter = []; $sql = "SELECT pt.package_id, count(pt.device_id) FROM " . MAIN_DB_PREFIX . "settlements_packagetool AS pt group by pt.package_id"; //print $sql; $data = $this->db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('queryFail', '($helper->getNumberOfDevices()) queryFailpackagetool'), 'errors'); return $deviceCounter; } while ($row = pg_fetch_assoc($data)) { $deviceCounter[$row['package_id']] = $row['count']; } return $deviceCounter; } function createPopoupBox($numberOfUsers, $usersArray, $id) { $popoupBox = '
' . $numberOfUsers . '
'; return $popoupBox; } function createPopoupBoxForDeviceList($number, $array) { $popupBox = ''; $not115 = false; $statusesObj = new Statuses($this->db); $result = $statusesObj->getStatusIds($this->db); if ($number != '') { $popupBox = '
' . $number . '
'; if ($not115) { $popupBox .= '
  
'; } } return $popupBox; } function getGroupsFromGroupToolsClass($db, $selectedEntities) { $groups = []; $sql = "SELECT sgt.rowid, sgt.ref, e.label FROM " . MAIN_DB_PREFIX . "settlements_grouptools AS sgt INNER JOIN " . MAIN_DB_PREFIX . "entity as e ON e.rowid=sgt.fk_entity"; if ($selectedEntities != '') { $sql .= " AND sgt.fk_entity IN ({$selectedEntities})"; } $sql .= " AND sgt.rowid NOT IN (SELECT ugtg.fk_group_tools_id FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup as ugtg INNER JOIN " . MAIN_DB_PREFIX . "settlements_group as g ON g.rowid = ugtg.fk_groupid WHERE g.hotelgroup is true) ORDER BY sgt.ref ASC"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('queryFail', '($helper->getGroupsFromGroupToolsClass()) llx_settlements_grouptools'), 'errors'); return $groups; } while ($row = pg_fetch_assoc($data)) { //$groups[$row['rowid']] = $row['ref']; $groups[] = $row; } return $groups; } function getFilteredpackageGroups($string, $selectedEntities) { global $db; $groups = []; $sql = "SELECT sgt.rowid, sgt.ref, e.label FROM " . MAIN_DB_PREFIX . "settlements_grouptools AS sgt INNER JOIN " . MAIN_DB_PREFIX . "entity as e ON e.rowid=sgt.fk_entity WHERE sgt.ref LIKE '%{$string}%'"; if ($selectedEntities != '') { $sql .= " AND sgt.fk_entity IN ({$selectedEntities})"; } $sql .= " AND sgt.rowid NOT IN (SELECT ugtg.fk_group_tools_id FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup as ugtg INNER JOIN " . MAIN_DB_PREFIX . "settlements_group as g ON g.rowid = ugtg.fk_groupid WHERE g.hotelgroup is true) ORDER BY sgt.ref ASC"; //print $sql;exit; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('queryFail', '($helper->getFilteredpackageGroups()) llx_settlements_grouptools'), 'errors'); return $groups; } while ($row = pg_fetch_assoc($data)) { $groups[] = $row; } return $groups; } function getPackagesFromPackageClass($db, $selectedEntities) { $groups = []; $sql = "SELECT pac.rowid, pac.ref, e.label as entity FROM " . MAIN_DB_PREFIX . "settlements_package AS pac INNER JOIN " . MAIN_DB_PREFIX . "entity as e ON e.rowid=pac.fk_entity"; if ($selectedEntities != '') { $sql .= " AND pac.fk_entity IN ({$selectedEntities})"; } $sql .= " ORDER BY pac.ref ASC"; //print $sql; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getPackagesFromPackageClass()) llx_settlements_package'), 'errors'); return $groups; } while ($row = pg_fetch_assoc($data)) { //$groups[$row['rowid']] = $row['ref']; $groups[] = $row; } return $groups; } function getAllGroupsFromGroupClass($db) { $groups = []; $sql = "SELECT rowid, ref FROM " . MAIN_DB_PREFIX . "settlements_group ORDER BY ref ASC"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllGroupsFromGroupClass()) llx_settlements_group'), 'errors'); return $groups; } while ($row = pg_fetch_assoc($data)) { $groups[$row['rowid']] = $row['ref']; } return $groups; } function getGroupsFromGroupClass($db, $selectedEntities) { $groups = []; $sql = "SELECT sg.rowid, sg.ref, e.label FROM " . MAIN_DB_PREFIX . "settlements_group AS sg INNER JOIN " . MAIN_DB_PREFIX . "entity as e ON e.rowid=sg.fk_entity WHERE sg.single_user_group is null AND sg.hotelgroup is null"; if ($selectedEntities != '') { $sql .= " AND sg.fk_entity IN ({$selectedEntities})"; } $sql .= " ORDER BY sg.ref ASC"; //print $sql; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getGroupsFromGroupClass()) llx_settlements_group'), 'errors'); return $groups; } while ($row = pg_fetch_assoc($data)) { //$groups[$row['rowid']] = $row['ref']; $groups[] = $row; } return $groups; } public function getGroupRefByID($id) { global $db; if (isset($id)) { $sql = "SELECT ref FROM " . MAIN_DB_PREFIX . "settlements_group WHERE rowid = {$id}"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); } if (pg_num_rows($data) > 0) { while ($row = pg_fetch_assoc($data)) { return $row['ref']; } } } return ''; } function getFilteredGroups($string, $selectedEntities) { global $db; $string = strtoupper($string); $filteredGroups = []; $sql = "SELECT sg.rowid, sg.ref, e.label FROM " . MAIN_DB_PREFIX . "settlements_group AS sg INNER JOIN " . MAIN_DB_PREFIX . "entity as e ON e.rowid=sg.fk_entity WHERE sg.single_user_group is null AND sg.hotelgroup is null AND sg.ref LIKE '%{$string}%'"; if ($selectedEntities != '') { $sql .= " AND sg.fk_entity IN ({$selectedEntities})"; } $sql .= " ORDER BY sg.ref ASC"; //print $sql; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); } while ($row = pg_fetch_assoc($data)) { $filteredGroups[] = $row; } return $filteredGroups; } function getHotelGroupsFromGroupClass($db, $selectedEntities) { $groups = []; $sql = "SELECT sg.rowid, sg.ref, e.label FROM " . MAIN_DB_PREFIX . "settlements_group AS sg INNER JOIN " . MAIN_DB_PREFIX . "entity as e ON e.rowid=sg.fk_entity"; if ($selectedEntities != '') { $sql .= " AND fk_entity IN ({$selectedEntities})"; } $sql .= " WHERE sg.hotelgroup is NOT null ORDER BY sg.ref ASC"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getHotelGroupsFromGroupClass()) llx_settlements_group'), 'errors'); return $groups; } while ($row = pg_fetch_assoc($data)) { $groups[$row['rowid']] = $row['ref']; //$groups[] = $row; } return $groups; } function getSingleUserGroupsFromGroupClass($db) { $groups = []; $sql = "SELECT rowid, ref FROM " . MAIN_DB_PREFIX . "settlements_group WHERE single_user_group is not null ORDER BY ref ASC"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getSingleUserGroupsFromGroupClass()) llx_settlements_group'), 'errors'); return $groups; } while ($row = pg_fetch_assoc($data)) { $groups[$row['rowid']] = $row['ref']; } return $groups; } function getAllTools($db) { $allTools = []; $sql = "SELECT i.rowid, i.ref, i.title FROM " . MAIN_DB_PREFIX . "inventory as i WHERE rowid NOT IN (SELECT fk_inventory FROM " . MAIN_DB_PREFIX . "settlements_grouptoolstools) ORDER BY i.ref"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllTools()) llx_inventory'), 'errors'); return $allTools; } while ($row = pg_fetch_assoc($data)) { $allTools[] = $row; } return $allTools; } function getAllToolsFromSelectedEntity($db, $id) { $entity_id = $this->getEntityIdFromPackage($id); $allTools = []; $sql = "SELECT i.rowid, i.ref, i.title, ie.device_type, ie2.fk_object, i.status, i.fk_warehouse FROM " . MAIN_DB_PREFIX . "inventory as i INNER JOIN " . MAIN_DB_PREFIX . "inventory_extrafields as ie ON ie.fk_object = i.rowid LEFT JOIN " . MAIN_DB_PREFIX . "inventory_extrafields AS ie2 ON ie2.assigned_device::integer = i.rowid WHERE i.rowid NOT IN (SELECT device_id FROM " . MAIN_DB_PREFIX . "settlements_packagetool) AND ie.device_type::integer NOT IN (3,4) AND ie.fk_entity::integer = {$entity_id} ORDER BY i.rowid"; //print $sql; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllToolsFromSelectedEntity()) llx_inventory'), 'errors'); return $allTools; } while ($row = pg_fetch_assoc($data)) { $allTools['freeTools'][$row['rowid']] = $row; if (isset($row['fk_object'])) { $inventoryObj = new Inventory($db); $inventoryObj->fetch($row['fk_object']); $assigned_device_data = ['rowid' => $inventoryObj->id, 'title' => $inventoryObj->title, 'ref' => $inventoryObj->ref, 'device_type' => $inventoryObj->array_options['options_device_type'], 'status' => $inventoryObj->status, 'warehouse' => $inventoryObj->fk_warehouse]; $allTools['freeTools'][$row['rowid']]['assigned_device_data'] = $assigned_device_data; } } $sql = "SELECT i.rowid, i.ref, i.title, ie.device_type, ie2.fk_object, i.status, (SELECT ref FROM " . MAIN_DB_PREFIX . "settlements_package as package WHERE package.rowid = pt.package_id) as package_ref, pt.package_id as packageid, i.fk_warehouse FROM " . MAIN_DB_PREFIX . "inventory as i INNER JOIN " . MAIN_DB_PREFIX . "inventory_extrafields as ie ON ie.fk_object = i.rowid INNER JOIN " . MAIN_DB_PREFIX . "settlements_packagetool AS pt ON pt.device_id=i.rowid LEFT JOIN " . MAIN_DB_PREFIX . "inventory_extrafields AS ie2 ON ie2.assigned_device::integer = i.rowid WHERE i.rowid IN (SELECT device_id FROM " . MAIN_DB_PREFIX . "settlements_packagetool) AND ie.device_type::integer NOT IN (3,4) AND ie.fk_entity::integer = {$entity_id} ORDER BY i.rowid"; //print $sql; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllToolsFromSelectedEntity()) llx_inventory'), 'errors'); return $allTools; } while ($row = pg_fetch_assoc($data)) { $allTools['addedTools'][$row['packageid']][$row['rowid']] = $row; if (isset($row['fk_object'])) { $inventoryObj = new Inventory($db); $inventoryObj->fetch($row['fk_object']); $assigned_device_data = ['rowid' => $inventoryObj->id, 'title' => $inventoryObj->title, 'ref' => $inventoryObj->ref, 'device_type' => $inventoryObj->array_options['options_device_type'], 'status' => $inventoryObj->status, 'warehouse' => $inventoryObj->fk_warehouse]; $allTools['addedTools'][$row['packageid']][$row['rowid']]['assigned_device_data'] = $assigned_device_data; } $allTools['addedPackages'][$row['packageid']] = $row['package_ref']; } return $allTools; } function getAllPackagesFromSelectedEntity($id, $selectedEntities = '') { $entity_id = $this->getEntityIdFromgoupTools($id); $allPackages = []; $sql = "SELECT pa.rowid, pa.ref, pa.label FROM " . MAIN_DB_PREFIX . "settlements_package as pa WHERE pa.rowid NOT IN (SELECT fk_package FROM " . MAIN_DB_PREFIX . "settlements_grouptoolspackages)"; if ($selectedEntities != '') { $sql .= " AND pa.fk_entity IN ({$selectedEntities})"; } $sql .= " AND pa.fk_entity = '{$entity_id}' ORDER BY pa.ref"; //print $sql; //exit; $data = $this->db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllPackagesFromSelectedEntity()) llx_settlements_package'), 'errors'); return $allPackages; } while ($row = pg_fetch_assoc($data)) { $allPackages['freePackages'][$row['rowid']] = $row; } $sqlAddedPackages = "SELECT pa.rowid, pa.ref, pa.label, gp.rowid as gprowid, gp.ref as gpref, gp.label as gplabel FROM " . MAIN_DB_PREFIX . "settlements_grouptoolspackages as gpp INNER JOIN " . MAIN_DB_PREFIX . "settlements_package as pa ON pa.rowid = gpp.fk_package INNER JOIN " . MAIN_DB_PREFIX . "settlements_grouptools as gp ON gp.rowid = gpp.fk_group"; $data2 = $this->db->query($sqlAddedPackages); if (!$data2) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllPackagesFromSelectedEntity()) settlements_grouptoolspackages'), 'errors'); return $allPackages; } while ($row = pg_fetch_assoc($data2)) { $allPackages['addedPackages'][] = $row; } return $allPackages; } function getEntityIdFromgoupTools($id) { $entity = []; $sql = "SELECT fk_entity FROM " . MAIN_DB_PREFIX . "settlements_grouptools WHERE rowid = {$id}"; //print $sql; $data = $this->db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getEntityIdFromgoupTools()) llx_settlements_grouptools'), 'errors'); return $entity; } while ($row = pg_fetch_array($data)) { $entity[] = $row['fk_entity']; } return $entity[0]; } function getEntityIdFromPackage($id) { $entity = []; $sql = "SELECT fk_entity FROM " . MAIN_DB_PREFIX . "settlements_package WHERE rowid = {$id}"; $data = $this->db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getEntityIdFromPackage()) llx_settlements_package'), 'errors'); return $entity; } while ($row = pg_fetch_array($data)) { $entity[] = $row['fk_entity']; } return $entity[0]; } function getAllUsers($db) { $allUsers = []; $sql = "SELECT u.rowid, u.login, u.firstname, u.lastname, ue.nickname, ue.user_category FROM " . MAIN_DB_PREFIX . "user as u INNER JOIN " . MAIN_DB_PREFIX . "user_extrafields AS ue ON ue.fk_object = u.rowid WHERE ue.user_category = '1' AND u.rowid NOT IN (SELECT fk_user FROM " . MAIN_DB_PREFIX . "settlements_groupusers) ORDER BY ue.nickname"; $data = $db->query($sql); //print $sql; if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllUsers()) llx_user'), 'errors'); return $allUsers; } while ($row = pg_fetch_assoc($data)) { $row['openDailyClosing'] = 0; $dailyclosingSql = "SELECT * FROM llx_rollerstorage_packagehistory WHERE user_id = {$row['rowid']} AND status = 0 ORDER BY rowid DESC LIMIT 1"; $dailyclosingData = $db->query($dailyclosingSql); if ($db->num_rows($dailyclosingData) > 0) { $dCRow = $db->fetch_object($dailyclosingData); $row['openDailyClosing'] = $dCRow->rowid; } $allUsers['freeUsers'][] = $row; } $sqlAddedUsers = "SELECT gu.fk_user, u.firstname, u.lastname, u.login, ue.nickname, sg.ref as groupref, sg.rowid as groupid FROM " . MAIN_DB_PREFIX . "settlements_groupusers as gu INNER JOIN " . MAIN_DB_PREFIX . "user as u ON u.rowid = gu.fk_user INNER JOIN " . MAIN_DB_PREFIX . "user_extrafields as ue ON gu.fk_user = ue.fk_object INNER JOIN " . MAIN_DB_PREFIX . "settlements_group as sg ON sg.rowid = gu.fk_settlements_group ORDER BY ue.nickname"; //print $sql; $dataAddedUser = $db->query($sqlAddedUsers); if (!$dataAddedUser) { return $allUsers; } while ($rowAddedUser = pg_fetch_assoc($dataAddedUser)) { $allUsers['addedUsers'][] = $rowAddedUser; } //print_r($allUsers);exit; return $allUsers; } function getAllUsersForEdit($user_id) { global $db; $allUsers = []; //$sql = "SELECT u.rowid, u.login, u.firstname, u.lastname FROM " . MAIN_DB_PREFIX . "user as u ORDER BY u.lastname"; $sql = "SELECT u.rowid, u.login, u.firstname, u.lastname, ue.nickname, ue.user_category FROM " . MAIN_DB_PREFIX . "user as u INNER JOIN " . MAIN_DB_PREFIX . "user_extrafields AS ue ON ue.fk_object = u.rowid WHERE ue.user_category = '1'"; if ($user_id) { $sql .= " AND u.rowid NOT IN (SELECT fk_user FROM " . MAIN_DB_PREFIX . "settlements_groupusers WHERE fk_user != $user_id)"; } $sql .= " ORDER BY u.lastname"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllUsersForEdit()) llx_user'), 'errors'); return $allUsers; } while ($row = pg_fetch_assoc($data)) { $allUsers['freeUsers'][] = $row; } return $allUsers; } function getAllHotelsForEdit($hotel_id) { global $db; $allHotels = []; $sql = "SELECT rowid, label FROM " . MAIN_DB_PREFIX . "settlements_hotel WHERE status = 1 ORDER BY label ASC"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllHotelsForEdit()) llx_user'), 'errors'); return $allHotels; } while ($row = pg_fetch_assoc($data)) { $allHotels['freeHotels'][] = $row; } return $allHotels; } function getFilteredUsers($db, $string) { $allUsers = []; $sql = "SELECT * FROM " . MAIN_DB_PREFIX . "user as u WHERE rowid NOT IN (SELECT fk_user FROM " . MAIN_DB_PREFIX . "settlements_groupusers) AND (u.lastname LIKE '%{$string}%' OR u.firstname LIKE '%{$string}%') ORDER BY u.lastname"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); return $allUsers; } while ($row = pg_fetch_assoc($data)) { $allUsers[] = $row; } return $allUsers; } function getFilteredTools($db, $string) { $allUsers = []; $sql = "SELECT * FROM " . MAIN_DB_PREFIX . "inventory as i WHERE rowid NOT IN (SELECT fk_inventory FROM " . MAIN_DB_PREFIX . "settlements_grouptools) AND i.ref LIKE '%{$string}%' ORDER BY i.ref"; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); return $allUsers; } while ($row = pg_fetch_assoc($data)) { $allUsers[] = $row; } return $allUsers; } function getToolsById($db, $id) { $array = []; $sql = "SELECT gtt.rowid, i.ref, i.title, iextra.unique_identifier, iextra.device_type, gtt.device_id, ie2.fk_object, i.status, i.fk_warehouse FROM " . MAIN_DB_PREFIX . "settlements_packagetool as gtt INNER JOIN " . MAIN_DB_PREFIX . "inventory as i ON i.rowid = gtt.device_id LEFT JOIN " . MAIN_DB_PREFIX . "inventory_extrafields AS iextra ON iextra.fk_object = i.rowid LEFT JOIN " . MAIN_DB_PREFIX . "inventory_extrafields AS ie2 ON ie2.assigned_device::integer = i.rowid WHERE package_id = {$id} ORDER BY i.ref ASC"; //print $sql; $data = $db->query($sql); if (!$data) { dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getToolsById()) llx_settlements_packagetool'), 'errors'); return $array; } while ($row = pg_fetch_assoc($data)) { if (isset($row['fk_object'])) { $inventoryObj = new Inventory($db); $inventoryObj->fetch($row['fk_object']); $assigned_device_data = ['rowid' => $inventoryObj->id, 'title' => $inventoryObj->title, 'ref' => $inventoryObj->ref, 'device_type' => $inventoryObj->array_options['options_device_type'], 'status' => $inventoryObj->status, 'warehouse' => $inventoryObj->fk_warehouse]; } $array[$row['rowid']] = $row; $array[$row['rowid']]['assigned_device_data'] = $assigned_device_data; //$array[] = $row; } return $array; } function getPackagesById($id) { global $db; $array = []; $sql = "SELECT gtp.rowid, gtp.fk_package, pa.ref FROM " . MAIN_DB_PREFIX . "settlements_grouptoolspackages as gtp INNER JOIN " . MAIN_DB_PREFIX . "settlements_package as pa ON pa.rowid = gtp.fk_package WHERE gtp.fk_group = {$id} ORDER BY pa.ref ASC"; $data = $db->query($sql); if (!$data) { dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getPackagesById()) llx_settlements_grouptoolspackages'), 'errors'); return $array; } while ($row = pg_fetch_assoc($data)) { $array[] = $row; } return $array; } function getGroupIdBygroupToolId($id) { global $db; $array = []; if (isset($id)) { $sql = "SELECT fk_groupid FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup WHERE fk_group_tools_id = {$id}"; $data = $db->query($sql); if (!$data) { dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getGroupIdBygroupToolId()) llx_settlements_usergrouptoolgroup'), 'errors'); return $array; } $array = pg_fetch_assoc($data); return $array['fk_groupid']; } return $array; } function getUsersById($db, $id) { $usersOfGroup = []; $sql = "SELECT sug.rowid, u.login, u.lastname, u.firstname, sug.fk_user, u.api_key FROM " . MAIN_DB_PREFIX . "settlements_groupusers as sug INNER JOIN " . MAIN_DB_PREFIX . "user as u ON u.rowid = sug.fk_user WHERE fk_settlements_group = {$id} ORDER BY u.lastname ASC"; $data = $db->query($sql); if (!$data) { dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); //setEventMessage($this->langs->trans('($helper->getUsersById()) llx_settlements_groupusers'), 'errors'); return $usersOfGroup; } while ($row = pg_fetch_assoc($data)) { $usersOfGroup[] = $row; } return $usersOfGroup; } function getDevicesById($db, $id) { $devicesOfGroup = []; $sql = "SELECT pt.rowid, pt.device_id, i.title, i.ref, i.status FROM " . MAIN_DB_PREFIX . "settlements_packagetool AS pt INNER JOIN " . MAIN_DB_PREFIX . "inventory as i ON i.rowid = pt.device_id WHERE pt.package_id = {$id}"; //print $sql; $data = $db->query($sql); if (!$data) { dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage('A csoporthoz rendelt felhasználók lekérdezésébe valami hiba csúszott.', 'errors'); return $devicesOfGroup; } while ($row = pg_fetch_assoc($data)) { $devicesOfGroup[] = $row; } return $devicesOfGroup; } function getUsersByIdWithDate($db, $id, $date) { $usersOfGroup = []; $sql = "SELECT un.user_id, u.firstname as firstname, u.lastname as lastname, u.login as login FROM " . MAIN_DB_PREFIX . "settlements_usernaplo AS un INNER JOIN " . MAIN_DB_PREFIX . "user as u ON u.rowid = un.user_id WHERE un.group_user_id = {$id} AND un.date_creation BETWEEN '{$date} 00:00:00' AND '{$date} 23:59:59' AND un.status = 1 GROUP BY un.user_id, u.firstname, u.lastname, u.login ORDER BY u.lastname"; $data = $db->query($sql); if (!$data) { dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage('A csoporthoz rendelt felhasználók naplózási lekérdezésébe valami hiba csúszott.', 'errors'); return $usersOfGroup; } while ($row = pg_fetch_assoc($data)) { $usersOfGroup[] = $row; } return $usersOfGroup; } function getUsersOfGroupsFromNaploToday($db, $id) { $users = []; $now = date('Y-m-d', dol_now()); $nowDatetime = date('Y-m-d H:i:s', dol_now()); $sql = "SELECT user_id FROM " . MAIN_DB_PREFIX . "settlements_usernaplo WHERE group_user_id = {$id} AND status = 1 AND date_creation BETWEEN '{$now} 05:00:00' AND '{$now} 23:59:59' GROUP BY user_id"; //$sql = "SELECT user_id FROM " . MAIN_DB_PREFIX . "settlements_usernaplo WHERE group_user_id = {$id} AND status = 1 AND date_creation < '{$nowDatetime}' GROUP BY user_id"; $data = $db->query($sql); if (!$data) { dol_syslog("No users found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); //setEventMessage('A csoporthoz rendelt felhasználók naplózási lekérdezésébe valami hiba csúszott.', 'errors'); return $users; } while ($row = pg_fetch_assoc($data)) { $users[] = $row['user_id']; } return $users; } function getToolsFromNaploToday($db, $id) { $tools = []; $now = date('Y-m-d', dol_now()); $sql = "SELECT tool_id FROM " . MAIN_DB_PREFIX . "settlements_toolnaplo WHERE group_tool_id = {$id} AND status = 1 AND date_creation BETWEEN '{$now} 00:00:00' AND '{$now} 23:59:59' GROUP BY tool_id"; $data = $db->query($sql); if (!$data) { dol_syslog("No tool found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage('A csoporthoz rendelt eszközök naplózási lekérdezésébe valami hiba csúszott.', 'errors'); return $tools; } while ($row = pg_fetch_assoc($data)) { $tools[] = $row['user_id']; } return $tools; } function confirmButtonCheck($array1, $array2) { return empty(array_diff($array1, $array2)); } function getSum($db, $currency, $id, $users, $date = null) { $sum = []; foreach ($users as $user) { $usersArray[] = $user['user_id']; } $usersString = implode(',', $usersArray); $sql = "SELECT SUM(total_ht) as total_ht, SUM(total_tva) as total_tva, SUM(total_ttc) as total_ttc FROM " . MAIN_DB_PREFIX . "facture WHERE fk_user_closing IN({$usersString}) AND fk_statut = 2 AND multicurrency_code = '{$currency}' AND date_closing BETWEEN '{$date} 00:00:00' AND '{$date} 23:59:59'"; $data = $db->query($sql); if (!$data) { dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage('A számlák összesítésének számításába valami hiba csúszott.', 'errors'); return $sum; } $dataArray = pg_fetch_all($data); foreach ($dataArray as $row) { $sum[] = $row; } return $sum; } function getDeletedUsers($db, $id, $date) { $deletedUsersArray = []; $sql = "SELECT user_id, MAX(date_creation) as date FROM " . MAIN_DB_PREFIX . "settlements_usernaplo WHERE group_user_id = {$id} and date_creation BETWEEN '{$date} 00:00:00' AND '{$date} 23:59:59' AND status = 0 GROUP BY user_id "; $data = $db->query($sql); $dataArray = pg_fetch_all($data); if (empty($dataArray)) { dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); return $deletedUsersArray; } foreach ($dataArray as $row) { $deletedUsersArray[$row['user_id']] = $row['date']; } return $deletedUsersArray; } function correctNumber($number) { $integerPart = floor($number); $formattedNumber = number_format($integerPart, 0, '.', ' '); return $formattedNumber; } function createArray($daterange) { $dates = explode(" - ", $daterange); $startDate = new DateTime($dates[0]); $endDate = new DateTime($dates[1]); $dateArray = array(); $currentDate = clone $startDate; while ($currentDate <= $endDate) { $dateArray[] = $currentDate->format('Y-m-d'); $currentDate->modify('+1 day'); } return $dateArray; } function createDataArray($id, $action, $userId) { $data['id'] = $id; $data['action'] = $action; $data['userid'] = $userId; return $data; } function createDataToolArray($id, $action, $toolid) { $data['id'] = $id; $data['action'] = $action; $data['toolid'] = $toolid; return $data; } function getAllEntities() { $entitiesArray = []; $sql = "SELECT rowid, label FROM " . MAIN_DB_PREFIX . "entity ORDER BY label ASC"; $data = $this->db->query($sql); while ($row = pg_fetch_assoc($data)) { $entitiesArray[$row['rowid']] = $row['label']; } return $entitiesArray; } function getEntityDataById($id) { $entity_id = $this->getEntityIdFromPackage($id); $entitiArray = []; $sql = "SELECT name, value FROM " . MAIN_DB_PREFIX . "const WHERE entity = {$entity_id} AND name IN('MAIN_INFO_SOCIETE_ZIP', 'MAIN_INFO_SOCIETE_TOWN', 'MAIN_INFO_SOCIETE_ADDRESS', 'MAIN_INFO_SOCIETE_NOM', 'MAIN_INFO_TVAINTRA') ORDER BY rowid DESC"; $data = $this->db->query($sql); if (!$data) { dol_syslog("No tool found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage('Nincs ilyen cég.', 'errors'); return $entitiArray; } while ($row = pg_fetch_assoc($data)) { $entitiArray[$row['name']] = $row['value']; } return $entitiArray; } function createHTML($id) { $printbuttonData = explode('_', $id); $bpcard = $printbuttonData[2]; $dandc = $printbuttonData[3]; $riverride = $printbuttonData[4]; global $user; global $conf; global $db; global $langs; $entity = $this->getEntityDataById($printbuttonData[0]); $devicesArray = $this->getDevicesById($db, $printbuttonData[0]); $userDataObj = $this->getUserData($printbuttonData[1]); $address = $userDataObj->town != '' ? $userDataObj->zip . ' ' . $userDataObj->town . ', ' . $userDataObj->address : ''; $result = ''; $result .= ''; $result .= ''; $result .= ''; $result .= ''; $result .= ''; $result .= '
' . $langs->trans('transferingUserData') . '' . $langs->trans('receivingUserData') . '
' . $user->firstname . ' ' . $user->lastname . '' . $userDataObj->firstname . ' ' . $userDataObj->lastname . '
' . $entity['MAIN_INFO_SOCIETE_NOM'] . '
' . $entity['MAIN_INFO_TVAINTRA'] . '' . $userDataObj->array_options['options_tax_identification_number'] . '
' . $entity['MAIN_INFO_SOCIETE_ZIP'] . ', ' . $entity['MAIN_INFO_SOCIETE_TOWN'] . '' . $address . '
' . $entity['MAIN_INFO_SOCIETE_ADDRESS'] . '
'; $result .= '
' . date('Y.m.d H:i:s') . '
'; $result .= ''; $count = 1; foreach ($devicesArray as $device) { $result .= ''; $result .= ''; $count++; } $changes = $this->getChanges(); if ($changes) { foreach ($changes as $change) { $tmpArray = explode('_', $change['ref']); $result .= ''; $result .= ''; $count++; } } $result .= ''; $result .= ''; $count++; $result .= ''; $result .= ''; $count++; $result .= ''; $result .= ''; $result .= '
' . $count . '' . $device['ref'] . ' (' . $device['title'] . ')1 ' . $langs->trans('Pieces') . '
  ' . $device['unique_identifier'] . '
' . $count . '' . str_replace("_", " ", $change['ref']) . ' ' . $this->correctNumber($change['title']) . ' ' . $tmpArray[1] . '
  
' . $count . 'Budapest Card ' . $bpcard . '' . $langs->trans('Pieces') . '
  
' . $count . 'Dinner & Cruise ' . $dandc . '
  
' . $count . 'RiverRide ' . $riverride . '
  
'; $result .= '
' . date('Y.m.d H:i:s') . '
'; $result .= '
 
'; $result .= '
_________________________________ _________________________________
' . $langs->trans('transferingUser') . ' ' . $langs->trans('receivingUser') . '
'; return $result; } function createHTMLIncome($id, $BLock3rdData, $withholdingArray, $additionals) { //print_r($withholdingArray); $witholdings = $this->createWitholdings($withholdingArray); $printbuttonData = explode('_', $id); global $user, $langs, $conf, $db; $entity = $this->getEntityDataById($printbuttonData[0]); $devicesArray = $this->getDevicesById($db, $printbuttonData[0]); $userDataObj = $this->getUserData($printbuttonData[1]); $address = $userDataObj->town != '' ? $userDataObj->zip . ' ' . $userDataObj->town . ', ' . $userDataObj->address : ''; # # Fejléc # $result = ''; $result .= ''; $result .= ''; $result .= ''; $result .= ''; $result .= ''; $result .= '
' . $langs->trans('transferingUserData') . '' . $langs->trans('receivingUserData') . '
' . $userDataObj->firstname . ' ' . $userDataObj->lastname . '' . $user->firstname . ' ' . $user->lastname . '
' . $entity['MAIN_INFO_SOCIETE_NOM'] . '
' . $userDataObj->array_options['options_tax_identification_number'] . '' . $entity['MAIN_INFO_TVAINTRA'] . '
' . $address . '' . $entity['MAIN_INFO_SOCIETE_ZIP'] . ', ' . $entity['MAIN_INFO_SOCIETE_TOWN'] . '
' . $entity['MAIN_INFO_SOCIETE_ADDRESS'] . '
'; $result .= '
' . date('Y.m.d H:i:s') . '
'; $result .= ''; $result .= ''; # # Tartalom # $count = 1; foreach ($devicesArray as $device) { $withholding1 = $witholdings[$device['device_id']] == '' ? '' : $langs->trans('withhold') . ' ' . $witholdings[$device['device_id']] . ' HUF'; $result .= ''; $result .= ''; $count++; } //print_r($additionals);exit; $withholding1 = $witholdings['bp_card'] == '' ? '' : $langs->trans('withhold') . ' ' . $witholdings['bp_card'] . ' HUF'; $result .= ''; $result .= ''; $count++; $withholding1 = $witholdings['dnc_ticket'] == '' ? '' : $langs->trans('withhold') . ' ' . $witholdings['dnc_ticket'] . ' HUF'; $result .= ''; $result .= ''; $count++; $withholding1 = $witholdings['rr_ticket'] == '' ? '' : $langs->trans('withhold') . ' ' . $witholdings['rr_ticket'] . ' HUF'; $result .= ''; $result .= ''; $count++; $result .= ''; $changes = $this->getChanges(); if ($changes) { foreach ($changes as $change) { $tmpArray = explode('_', $change['ref']); $result .= ''; $result .= ''; $count++; } } $result .= ''; //print_r($BLock3rdData);exit; foreach ($BLock3rdData as $key => $value) { $withholding2 = $witholdings['daily_' . $key] == '' ? '' : $langs->trans('withhold') . ' ' . $witholdings['daily_' . $key] . ' HUF'; $keyIncomes = $this->getIncomesKey($key); $keyValue = $this->getValueKey($key); $result .= ''; if ($keyIncomes == 'Cash') { $result .= ''; } else { $result .= ''; } $count++; } $result .= '
' . $langs->trans('Devices') . '
' . $count . '' . $device['ref'] . ' (' . $device['title'] . ')1 ' . $langs->trans('pcs') . '
  ' . $withholding1 . '
' . $count . '' . $this->getAdditionalTitle('bp_card') . ' ' . $additionals['bp_card']['pieces'] . ' 
  ' . $withholding1 . '
' . $count . '' . $this->getAdditionalTitle('dnc_ticket') . ' ' . $additionals['dnc_ticket']['pieces'] . ' 
  ' . $withholding1 . '
' . $count . '' . $this->getAdditionalTitle('rr_ticket') . ' ' . $additionals['rr_ticket']['pieces'] . ' 
  ' . $withholding1 . '
' . $langs->trans('Changes') . '
' . $count . '' . str_replace("_", " ", $change['ref']) . ' ' . $this->correctNumber($change['title']) . ' ' . $tmpArray[1] . '
   
' . $langs->trans('Incomes') . '
' . $count . '' . $langs->trans('todaysIncomes') . ' (' . $langs->trans($keyIncomes) . ')' . $this->correctNumber($value) . ' ' . $keyValue . '
  ' . $withholding2 . '
   
'; $result .= '
 
'; $result .= '
' . date('Y.m.d H:i:s') . '
'; $result .= '
 
'; $result .= '
_________________________________ _________________________________
' . $langs->trans('transferingUser') . ' ' . $langs->trans('receivingUser') . '
'; return $result; } private function createWitholdings($withholdingArray) { foreach ($withholdingArray as $whkey => $whvalue) { if ($whkey !== 'daily_HUF' && $whkey !== 'daily_EUR') { $witholdings[$this->getDeviceIdFromString($whkey)] = $whvalue; } else { $witholdings[$whkey] = $whvalue; } } return $witholdings; } function getValueKey($key) { return strpos($key, 'HUF') !== false ? 'HUF' : 'EUR'; } function getIncomesKey($key) { return strpos($key, 'cash') !== false ? 'Cash' : 'Card'; } function getUserData($user_id) { global $db; $userObj = new User($db); $result = $userObj->fetch($user_id); return $userObj; } function getUserName($id) { $db = $this->db; $sql = "SELECT u.lastname, u.firstname, u.login FROM " . MAIN_DB_PREFIX . "settlements_groupusers AS sg INNER JOIN " . MAIN_DB_PREFIX . "user AS u ON u.rowid = sg.fk_user WHERE sg.fk_settlements_group = {$id} ORDER BY sg.rowid LIMIT 1"; $data = $db->query($sql); $dataArray = pg_fetch_all($data); $name = $dataArray[0]['lastname'] . ' ' . $dataArray[0]['firstname'] . ' (' . $dataArray[0]['login'] . ')'; return $name; } function getHotelName($id) { $db = $this->db; $sql = "SELECT h.label FROM " . MAIN_DB_PREFIX . "settlements_grouphotels AS sgh INNER JOIN " . MAIN_DB_PREFIX . "settlements_hotel AS h ON h.rowid = sgh.fk_hotel_id WHERE sgh.fk_group_id = {$id} ORDER BY sgh.rowid DESC LIMIT 1"; $data = $db->query($sql); $dataArray = pg_fetch_all($data); $name = $dataArray[0]['label']; return $name; } function getSelectedUserId($id) { $db = $this->db; $sql = "SELECT u.rowid FROM " . MAIN_DB_PREFIX . "settlements_groupusers AS sg INNER JOIN " . MAIN_DB_PREFIX . "user AS u ON u.rowid = sg.fk_user WHERE sg.fk_settlements_group = {$id} ORDER BY sg.rowid LIMIT 1"; $data = $db->query($sql); $dataArray = pg_fetch_all($data); return $dataArray[0]['rowid']; } function getSelectedHotelId($id) { $db = $this->db; $sql = "SELECT h.rowid FROM " . MAIN_DB_PREFIX . "settlements_grouphotels AS sgh INNER JOIN " . MAIN_DB_PREFIX . "settlements_hotel AS h ON h.rowid = sgh.fk_hotel_id WHERE sgh.fk_group_id = {$id} ORDER BY sgh.rowid DESC LIMIT 1"; $data = $db->query($sql); $dataArray = pg_fetch_all($data); return $dataArray[0]['rowid']; } function getGroupuserId($id) { $db = $this->db; $sql = "SELECT rowid FROM " . MAIN_DB_PREFIX . "settlements_groupusers WHERE fk_settlements_group = {$id} ORDER BY rowid DESC LIMIT 1"; $data = $db->query($sql); $dataArray = pg_fetch_all($data); return $dataArray[0]['rowid']; } function getGrouphotelsId($id) { $db = $this->db; $sql = "SELECT rowid FROM " . MAIN_DB_PREFIX . "settlements_grouphotels WHERE fk_group_id = {$id} ORDER BY rowid DESC LIMIT 1"; $data = $db->query($sql); $dataArray = pg_fetch_all($data); return $dataArray[0]['rowid']; } function getGroupuserIdByUserId($id) { $db = $this->db; $sql = "SELECT fk_settlements_group FROM " . MAIN_DB_PREFIX . "settlements_groupusers WHERE fk_user = {$id} ORDER BY rowid DESC LIMIT 1"; $data = $db->query($sql); $dataArray = pg_fetch_all($data); return $dataArray[0]['fk_settlements_group']; } /** * Output the buttons to submit a creation/edit form * * @param string $save_label Alternative label for save button * @param string $cancel_label Alternative label for cancel button * @param array $morebuttons Add additional buttons between save and cancel * @param bool $withoutdiv Option to remove enclosing centered div * @param string $morecss More CSS * @param string $dol_openinpopup If the button are shown in a context of a page shown inside a popup, we put here the string name of popup. * @return string Html code with the buttons */ public function buttonsSaveCancel($save_label = 'Save', $cancel_label = 'Cancel', $cancelURL = '', $morebuttons = array(), $withoutdiv = 0, $morecss = '', $dol_openinpopup = '') { global $langs; $buttons = array(); $save = array( 'name' => 'save', 'label_key' => $save_label, ); if ($save_label == 'Create' || $save_label == 'Add') { $save['name'] = 'add'; } elseif ($save_label == 'Modify') { $save['name'] = 'edit'; } $cancel = array( 'name' => 'cancel', 'label_key' => 'Cancel', ); !empty($save_label) ? $buttons[] = $save : ''; if (!empty($morebuttons)) { $buttons[] = $morebuttons; } $retstring = $withoutdiv ? '' : '
'; foreach ($buttons as $button) { $addclass = empty($button['addclass']) ? '' : $button['addclass']; $retstring .= ''; } if ($cancel_label == "Cancel") { $retstring .= '' . $langs->trans($cancel['label_key']) . ''; } $retstring .= $withoutdiv ? '' : '
'; if ($dol_openinpopup) { $retstring .= '' . "\n"; $retstring .= ''; } return $retstring; } function createButtonContent($db, $id, $toolsOfGroups, $confirmButtonDisplay, $PrintButtonDisplay) { global $langs; if (empty($this->getToolsFromNaploToday($db, $id)) && !empty($toolsOfGroups)) { return ''; } if (!empty($toolsOfGroups)) { return ''; } } function massactionDelete() { global $langs; return '
' . $langs->trans('MassDeleteConfirm') . '
' . $langs->trans('MassDeletConfirmContent') . '
'; } function confirmButtonConfirmeText() { global $langs; return '
' . $langs->trans('MassConfirm') . '
' . $langs->trans('MassConfirmContent') . '
'; } function saveToolsData($object, $package_id, $device_id, $langs, $massactionadd) { global $user; $toolNaplo = new ToolNaplo($this->db); $object->package_id = $package_id; $object->device_id = $device_id; $result = $object->create($user); if (!$result) { $error = pg_last_error($this->db); dol_syslog("Nem sikerult a packagetools tabla mentese.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($langs->trans("error"), 'errors'); } else { $data = $this->createDataToolArray($package_id, $massactionadd, $device_id); $toolNaplo->createToolsNaplo($user, $data); } } function savePackageData($grouptoolsPackages, $fk_group, $fk_package, $langs) { global $user; $packageNaplo = new PackageNaplo($this->db); $grouptoolsPackages->fk_group = $fk_group; $grouptoolsPackages->fk_package = $fk_package; $result = $grouptoolsPackages->create($user); if (!$result) { $error = pg_last_error($this->db); dol_syslog("Nem sikerult a grouptoolsPackages tabla mentese.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($langs->trans("duplicate"), 'errors'); } else { $data = $this->createDataToolArray($fk_group, 1, $fk_package); $packageNaplo->createNaplo($user, $data); } } function getListOfUsersRowid($data) { $rowidArray = []; while ($row = pg_fetch_assoc($data)) { $rowidArray[$row['rowid']]['fk_settlements_group'] = $row['fk_settlements_group']; $rowidArray[$row['rowid']]['fk_user'] = $row['fk_user']; } return !empty($rowidArray) ? $rowidArray : ''; } function getListOfToolsRowid($data) { $rowidArray = []; foreach ($data as $tool) { $rowidArray[$tool->id]['fk_group'] = $tool->fk_group; $rowidArray[$tool->id]['fk_inventory'] = $tool->fk_inventory; } return $rowidArray; } function getListOfPackagesRowid($data) { $rowidArray = []; foreach ($data as $tool) { $rowidArray[$tool->id]['fk_group'] = $tool->fk_group; $rowidArray[$tool->id]['fk_package'] = $tool->fk_package; } return $rowidArray; } function getListOfUsersFromTheGroups() { $sql = "SELECT rowid, fk_settlements_group, fk_user FROM " . MAIN_DB_PREFIX . "settlements_groupusers WHERE fk_settlements_group NOT IN(SELECT rowid FROM " . MAIN_DB_PREFIX . "settlements_group WHERE single_user_group IS NOT NULL OR hotelgroup IS NOT NULL) ORDER BY rowid ASC"; //print $sql;exit; $data = $this->db->query($sql); return $data; } function getListOfUsersFromTheGroupTable() { $data = $this->getListOfUsersFromTheGroups(); if (pg_num_rows($data) > 0) { $rows = pg_fetch_all($data); return $rows; } return []; } function userGroupsAreEmpty() { $data = $this->getListOfUsersFromTheGroups(); return pg_num_rows($data) == 0; } function getListOfToolsFromTheGroups() { $allObj = new PackageTool($this->db); $result = $allObj->fetchAll(); return $result; } function getListOfPackagesFromTheGroups() { $allObj = new GrouptoolsPackages($this->db); $result = $allObj->fetchAll(); return $result; } function toolGroupsAreEmpty() { $data = $this->getListOfToolsFromTheGroups(); return count($data) == 0; } function packageGroupsAreEmpty() { $data = $this->getListOfPackagesFromTheGroups(); return $data > 0 ? count($data) == 0 : true; } function createUserNaplo($record) { global $user; $userNaploObj = new UserNaplo($this->db); $userNaploObj->user_id = $record->fk_user; $userNaploObj->group_user_id = $record->fk_settlements_group; $userNaploObj->status = 0; $insertResult = $userNaploObj->create($user); return $insertResult; } function createToolNaplo($record) { global $user; $toolNaploObj = new ToolNaplo($this->db); $toolNaploObj->tool_id = $record->device_id; $toolNaploObj->group_tool_id = $record->package_id; $toolNaploObj->status = 0; return $toolNaploObj->create($user); } function createPackageNaplo($record) { global $user; $packageNaploObj = new PackageNaplo($this->db); $packageNaploObj->package_id = $record->fk_package; $packageNaploObj->group_tool_id = $record->fk_group; $packageNaploObj->status = 0; return $packageNaploObj->create($user); } function deleteLine($object, $key) { global $user; return $object->deleteline($user, $key); } function returnWindowLocation($url, $id, $idmenu) { echo ''; } function returnDailyClosingWindowLocation($url, $id, $user_id, $idmenu) { echo ''; } function returnDailyClosingHistoryWindowLocation($url, $id, $user_id, $idmenu) { echo ''; } function getAllSalesWithSQL($userObj, $sql) { $salesUsers = []; $result = $this->db->query($sql); while ($row = pg_fetch_assoc($result)) { $salesUsers[] = $row; } return $salesUsers; } function tableDrawer($allSalesUsers, $db, $idmenu) { $rownum = 0; foreach ($allSalesUsers as $user) { $url = '/custom/settlements/userloginnaplo_list.php?user_id=' . $user['rowid'] . '&idmenu=' . $idmenu . '&mainmenu=settlements&leftmenu='; $bgcolor = $this->setRowColor($rownum); print ' ' . $user['lastname'] . ' ' . $user['firstname'] . ''; print '' . $user['nickname'] . ''; print '' . $user['login'] . ''; print ''; $rownum++; } } private function setRowColor($rownum) { return $rownum % 2 == 0 ? '#b0c4de' : 'white'; } public function getChangedDateRecord($id) { global $db; $changedRecord = []; $logoutChangeNaploObj = new LogoutChangeNaplo($db); $result = $logoutChangeNaploObj->fetchAll('DESC', 'rowid', 1, 0, array('fk_logout' => $id)); if (!empty($result)) { foreach ($result as $record) { $changedRecord['id'] = $record->id; $changedRecord['corrected_date'] = $record->corrected_date; return $changedRecord; } } return false; } public function getEntities($entitiesArrayIsEmpty = false, $entities, $entitiesArray) { if ($entitiesArrayIsEmpty) { $array = []; foreach ($entities as $key => $value) { $array[] = $key; } $selectedEntities = implode(', ', $array); } else { $array = []; foreach ($entitiesArray as $entity) { if ($entity) $array[] = $entity; } $selectedEntities = implode(', ', $array); } return $selectedEntities; } function getTheUsersOfGroup($id, $nemkell) { global $db; $sql = "SELECT ugtg.fk_groupid, gu.fk_user, u. lastname, u.firstname, u.login, ue.nickname FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup as ugtg INNER JOIN " . MAIN_DB_PREFIX . "settlements_groupusers as gu ON gu.fk_settlements_group = ugtg.fk_groupid INNER JOIN " . MAIN_DB_PREFIX . "user as u ON u.rowid = gu.fk_user INNER JOIN " . MAIN_DB_PREFIX . "user_extrafields as ue ON ue.fk_object = gu.fk_user WHERE ugtg.fk_group_tools_id = {$id} AND ugtg.fk_groupid NOT IN (SELECT gu2.fk_settlements_group FROM " . MAIN_DB_PREFIX . "settlements_groupusers as gu2 WHERE date_creation < DATE_TRUNC('day', CURRENT_DATE) GROUP BY gu2.fk_settlements_group)"; if ($nemkell != '') { $sql .= " AND gu.fk_user NOT IN({$nemkell})"; } //print $sql . '
'; $data = $this->db->query($sql); $dataArray = pg_fetch_all($data); return $dataArray; } function savePackageUserAssignement($packageuser, $id) { global $db; global $user; global $langs; $packageHistoryObj = new PackageHistory($db); foreach ($packageuser as $key => $value) { $groupuser_id = $this->getGroupuserIdByUserId($value); $printbutton = $key . '_' . $value; $html = $this->createHTML($printbutton); $pdf = new SettlementsPDFPrint($html); $fullPath = $pdf->generateSettlementsPDF(); $package_entity = $this->getEntityOfPackage($key); $packageHistoryObj->package_manager_user = $user->id; $packageHistoryObj->package_id = $key; $packageHistoryObj->user_id = $value; $packageHistoryObj->status = 0; $packageHistoryObj->package_entity = $package_entity; $packageHistoryObj->fk_group_id = $id; $packageHistoryObj->fk_groupuser_id = $groupuser_id; $packageHistoryObj->delivery_pdf = $fullPath; //print_r($packageHistoryObj);exit; $result = $packageHistoryObj->create($user); if ($result < 0) { return -1; } //sleep(1); } return 0; } function saveDailyClosing($jsonArray, $user_id, $package_id, $fk_group_id) { global $db; global $user; global $langs; $packageHistoryObj = new PackageHistory($db); $fullPath = ''; $package_entity = $this->getEntityOfPackage($package_id); $packageHistoryObj->package_manager_user = $user->id; $packageHistoryObj->package_id = $package_id; $packageHistoryObj->user_id = $user_id; $packageHistoryObj->status = 1; $packageHistoryObj->package_entity = $package_entity; $packageHistoryObj->fk_group_id = $fk_group_id; $packageHistoryObj->income_pdf = $fullPath; $packageHistoryObj->note_public = $jsonArray; $result = $packageHistoryObj->create($user); if ($result < 0) { return -1; } return $result; } public function getEntityOfPackage($package_id) { global $db; $packageObj = new Package($db); $result = $packageObj->fetch($package_id); if ($result <= 0) { dol_syslog("No Entity found. (settlements->helper->getEntityOfPackage())", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage('No Entity found. (settlements->helper->getEntityOfPackage())', 'errors'); } return $packageObj->fk_entity; } function checkPackageUserAssignement($id) { $array = []; if (isset($id)) { global $db; $packageHistoryObj = new PackageHistory($db); $now = dol_now(); $from = date('Y-m-d H:i:s', strtotime("-2 day", strtotime(date('Y-m-d', $now)))); $to = date('Y-m-d H:i:s', $now); $result = $packageHistoryObj->fetchAll('DESC', 'rowid', 0, 0, ["customsql" => "fk_group_id = {$id} AND date_creation BETWEEN '{$from}' AND '{$to}'"]); if($result > 0){ foreach ($result as $key => $value) { $array[][$value->package_id] = $value->user_id; } } } return $array; } function getAssignementsOfThisPackage($id, $package_id) { $dataArray = []; global $db; $packageHistoryObj = new PackageHistory($db); $now = dol_now(); $from = date('Y-m-d H:i:s', strtotime("-7 day", strtotime(date('Y-m-d', $now)))); $to = date('Y-m-d H:i:s', $now); $sql = "SELECT rowid, package_id, fk_group_id, user_id, status FROM " . MAIN_DB_PREFIX . $packageHistoryObj->table_element . " WHERE fk_group_id = {$id} AND package_id = {$package_id} AND date_creation BETWEEN '{$from}' AND '{$to}'"; //print $sql . '
'; $result = $db->query($sql); if (pg_num_rows($result) > 0) { return pg_fetch_all($result); } return $dataArray; } function checkCLoasedPackageUserAssignement($id) { $array = []; global $db; $packageHistoryObj = new PackageHistory($db); $now = date("Y-m-d", dol_now()); $from = $now . ' 00:00:00'; $to = $now . ' 23:59:59'; $result = $packageHistoryObj->fetchAll('DESC', 'rowid', 0, 0, ["customsql" => "fk_group_id = {$id} AND status = 1 AND date_creation BETWEEN '{$from}' AND '{$to}'"]); foreach ($result as $key => $value) { $array[$value->package_id] = $value->user_id; } return $array; } function getDailyClosingId($user_id) { global $db; $now = dol_now(); $from = date('Y-m-d ', strtotime("-3 day", strtotime(date('Y-m-d', $now)))); //$from = date('Y-m-d', $now) . ' 00:00:00'; $to = date('Y-m-d H:i:s', $now); $sql = "SELECT ph.rowid FROM " . MAIN_DB_PREFIX . "rollerstorage_packagehistory as ph WHERE ph.user_id = {$user_id} AND ph.status = 0 --AND ph.date_creation BETWEEN '{$from} 00:00:00' AND '{$to}' ORDER BY ph.rowid DESC LIMIT 1"; //print $sql; $data = $this->db->query($sql); $dataArray = pg_fetch_object($data); return $dataArray->rowid; } function get1stBlockData($user_id) { global $db; $now = dol_now(); $from = date('Y-m-d ', strtotime("-2 day", strtotime(date('Y-m-d', $now)))); //$from = date('Y-m-d', $now) . ' 00:00:00'; $to = date('Y-m-d H:i:s', $now); $sql = "SELECT pack.label as packetlabel, gt.label as grouptool, ph.fk_group_id, ph.user_id, ph.package_id, ph.rowid/*, gu.fk_settlements_group, gr.ref as usergroup*/ FROM " . MAIN_DB_PREFIX . "rollerstorage_packagehistory as ph INNER JOIN " . MAIN_DB_PREFIX . "settlements_package as pack ON pack.rowid=ph.package_id INNER JOIN " . MAIN_DB_PREFIX . "settlements_grouptools as gt ON gt.rowid = ph.fk_group_id --INNER JOIN " . MAIN_DB_PREFIX . "settlements_groupusers as gu ON gu.fk_user = ph.user_id --INNER JOIN " . MAIN_DB_PREFIX . "settlements_group as gr ON gr.rowid=gu.fk_settlements_group WHERE ph.user_id = {$user_id} AND ph.status = 0 AND ph.date_creation BETWEEN '{$from} 00:00:00' AND '{$to}' ORDER BY ph.rowid DESC LIMIT 1"; //print $sql; $data = $this->db->query($sql); $dataArray = pg_fetch_object($data); return $dataArray; } function get2ndBlockData($package_id) { $sql = "SELECT pt.package_id, pt.device_id, inv.ref, inv.title, inve.device_type FROM " . MAIN_DB_PREFIX . "settlements_packagetool AS pt INNER JOIN " . MAIN_DB_PREFIX . "inventory as inv ON inv.rowid=pt.device_id INNER JOIN " . MAIN_DB_PREFIX . "inventory_extrafields as inve ON inve.fk_object = inv.rowid WHERE pt.package_id = {$package_id} ORDER BY inv.ref ASC "; //print $sql; $data = $this->db->query($sql); $dataArray = pg_fetch_all($data); return $dataArray; } function get3rdBlockData($packageHistoryRecordObj, $user_id) { $commmissionhandler = new CommissionHandler(); $now = dol_now(); $from = date("Y-m-d H:i:s", $packageHistoryRecordObj->date_creation); $to = date("Y-m-d H:i:s", $now); $allComissionInvoicesOfUserInTheGroupFromUserInvoice = ''; $commissionHUFCash = $commmissionhandler->getCashCommisonByUserId($user_id, 'HUF', $from, $to, $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $packageHistoryRecordObj->package_entity, 'LIQ'); $commissionEURCash = $commmissionhandler->getCashCommisonByUserId($user_id, 'EUR', $from, $to, $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $packageHistoryRecordObj->package_entity, 'LIQ'); $commissionHUFCard = $commmissionhandler->getCashCommisonByUserId($user_id, 'HUF', $from, $to, $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $packageHistoryRecordObj->package_entity, 'CB'); $commissionEURCard = $commmissionhandler->getCashCommisonByUserId($user_id, 'EUR', $from, $to, $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $packageHistoryRecordObj->package_entity, 'CB'); $array = $this->GiveMeAllCommissionsArray($commissionHUFCash, $commissionEURCash, $commissionHUFCard, $commissionEURCard); return $array; } function get3rdBlockDataHistory($entity, $user_id, $id = null) { $commmissionhandler = new CommissionHandler(); $allComissionInvoicesOfUserInTheGroupFromUserInvoice = ''; $commissionHUFCash = $commmissionhandler->getCashCommisonByUserIdHistory($user_id, 'HUF', $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $id, $entity, 'LIQ'); $commissionEURCash = $commmissionhandler->getCashCommisonByUserIdHistory($user_id, 'EUR', $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $id, $entity, 'LIQ'); $commissionHUFCard = $commmissionhandler->getCashCommisonByUserIdHistory($user_id, 'HUF', $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $id, $entity, 'CB'); $commissionEURCard = $commmissionhandler->getCashCommisonByUserIdHistory($user_id, 'EUR', $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $id, $entity, 'CB'); $array = $this->GiveMeAllCommissionsArray($commissionHUFCash, $commissionEURCash, $commissionHUFCard, $commissionEURCard); return $array; } function GiveMeAllCommissionsArray($commissionHUFCash, $commissionEURCash, $commissionHUFCard, $commissionEURCard) { $array['HUFcash'] = $commissionHUFCash; $array['EURcash'] = $commissionEURCash; $array['HUFcard'] = $commissionHUFCard; $array['EURcard'] = $commissionEURCard; return $array; } function getAllFactures($packageHistoryRecordObj, $user_id) { $array = []; $commissionHandler = new CommissionHandler(); $from = $commissionHandler->calculateFromDate($packageHistoryRecordObj->id); $to = $commissionHandler->calculateToDate($user_id, $from); $sql = "SELECT f.rowid, f.ref, f.date_closing, f.multicurrency_code, f.multicurrency_total_ttc, fe.marked_for_storno, p.libelle, fe.commission_deduction FROM " . MAIN_DB_PREFIX . "facture as f INNER JOIN " . MAIN_DB_PREFIX . "facture_extrafields as fe ON fe.fk_object = f.rowid INNER JOIN " . MAIN_DB_PREFIX . "c_paiement as p ON p.id = f.fk_mode_reglement WHERE f.fk_user_closing = {$user_id} AND f.entity = {$packageHistoryRecordObj->package_entity} AND f.date_closing BETWEEN '{$from}' AND '{$to}' AND f.fk_statut = 2 ORDER BY f.ref DESC"; //print $sql; $data = $this->db->query($sql); if (pg_num_rows($data) > 0) { while ($row = pg_fetch_assoc($data)) { $array[] = $row; } } return $array; } function getAddititonalDevices($rowid) { global $db; $dataArray = []; $sql = "SELECT bp_card, dnc_ticket, rr_ticket FROM " . MAIN_DB_PREFIX . "settlements_package_additionals WHERE fk_package_history_id = {$rowid}"; $data = $this->db->query($sql); if ($data) { $dataArray = pg_fetch_assoc($data); } return $dataArray; } function getAddititonalChangesDevices($rowid) { global $db; $dataArray = []; $sql = "SELECT change_huf, change_eur FROM " . MAIN_DB_PREFIX . "settlements_package_additionals WHERE fk_package_history_id = {$rowid}"; $data = $this->db->query($sql); if ($data) { $dataArray = pg_fetch_assoc($data); } return $dataArray; } function getPDFName($package_id, $user_id) { global $db; if (isset($user_id)) { $packageHistoryObj = new PackageHistory($db); $now = dol_now(); $from = date('Y-m-d', $now) . ' 00:00:00'; $to = date('Y-m-d H:i:s', $now); $result = $packageHistoryObj->fetchAll('DESC', 'date_creation', 1, 0, ['customsql' => "package_id = {$package_id} AND user_id = {$user_id} AND date_creation BETWEEN '$from' AND '$to' AND status = 0"]); foreach ($result as $doc) { return basename($doc->delivery_pdf); } } } function getPDFName2($user_id, $package_id, $group_id) { //print $group_id.'
'; print $package_id.'
'; print $user_id.'
'; global $db; if (isset($user_id)) { $packageHistoryObj = new PackageHistory($db); $now = dol_now(); $from = date('Y-m-d', $now) . ' 00:00:00'; $to = date('Y-m-d H:i:s', $now); $result = $packageHistoryObj->fetchAll('DESC', 'rowid', 1, 0, ["customsql" => "fk_group_id = {$group_id} AND package_id = {$package_id} AND user_id = {$user_id} AND status = 0 AND date_creation BETWEEN '{$from}' AND '{$to}'"]); foreach ($result as $doc) { return basename($doc->delivery_pdf); } } } function checkStatus($package_id, $user_id) { global $db; $packageHistoryObj = new PackageHistory($db); $now = dol_now(); $from = date('Y-m-d', $now) . ' 00:00:00'; $to = date('Y-m-d H:i:s', $now); $result = $packageHistoryObj->fetchAll('DESC', 'date_creation', 1, 0, ['customsql' => "package_id = {$package_id} AND user_id = {$user_id}"]); foreach ($result as $doc) { return $doc->status; } //return true; } function checkStatusToUsergroups($user, $id) { global $db; /* kikeresem a llx_settlements_groupusers táblából a date_creation értékét Ha van ilyen rekord (jelenleg benne van a csoportban), akkor ezt a dátumot használom FROM-nak A FROM és a jelenlegi idő között, group_id, user_id segítségével megnézem a rollerstorage_packagehistory táblában van-e nyitott státuszú rekord */ $now = dol_now(); $from = date("Y-m-d H:i:s", $now); $sqlGroupUser = "SELECT date_creation FROM " . MAIN_DB_PREFIX . "settlements_groupusers WHERE fk_Settlements_group = {$id} AND fk_user = {$user['fk_user']}"; $data = $db->query($sqlGroupUser); if (pg_num_rows($data) > 0) { $dataArray = pg_fetch_assoc($data); $from = $dataArray['date_creation']; } $to = date("Y-m-d H:i:s", $now); // kikeresem az eszközcsoport számát, a usercsoport alapján amiben a user van (" . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup) $sql = "SELECT rowid, status, fk_group_id FROM " . MAIN_DB_PREFIX . "rollerstorage_packagehistory WHERE user_id = {$user['fk_user']} AND fk_group_id = (SELECT fk_group_tools_id FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup WHERE fk_groupid = {$id}) AND date_creation BETWEEN '{$from}' AND '{$to}'"; //print $sql; $data = $db->query($sql); $dataArray = pg_fetch_assoc($data); if (isset($dataArray['status'])) { return $dataArray; } return []; } function getPDFNameIncome($package_id, $user_id) { global $db; $packageHistoryObj = new PackageHistory($db); $now = dol_now(); $from = date('Y-m-d', $now) . ' 00:00:00'; $to = date('Y-m-d H:i:s', $now); $result = $packageHistoryObj->fetchAll('DESC', 'date_creation', 1, 0, ['customsql' => "package_id = {$package_id} AND user_id = {$user_id} AND date_creation BETWEEN '$from' AND '$to'"]); foreach ($result as $doc) { return basename($doc->income_pdf); } } public function searchAndFetchDailyClosingRow($Block1stData, $user_id, $status) { global $db; $PackageHistory = new PackageHistory($db); /*$now = dol_now(); $from = date('Y-m-d', strtotime("-3 day", strtotime(date('Y-m-d', $now)))); $checkFrom = $from . ' 00:00:00'; $checkTo = date('Y-m-d H:i:s', $now);*/ $checkHistory = $PackageHistory->fetchAll('DESC', 'rowid', 1, 0, [ "customsql" => " package_id={$Block1stData->package_id} AND fk_group_id = {$Block1stData->fk_group_id} AND user_id = {$user_id} AND status = {$status}" ]); //AND date_creation BETWEEN '$checkFrom' AND '$checkTo'" if (!empty($checkHistory) && $checkHistory > -1) { foreach ($checkHistory as $record) { $theRowid = $record->id; } $PackageHistory->fetch($theRowid); return $PackageHistory; } return -1; } public function checkForm($deviceArray, $amountArray) { if (!empty($deviceArray)) { foreach ($deviceArray as $dkey => $dvalue) { if ($dvalue == 'x') { if ($amountArray[$dkey . '_amount'] == '') { return true; } } } } else { return true; } } public function updatePackageHistory($packageHistoryObj, $BLock3rdData) { global $db, $user; $error = $this->commonUpdatePackageHistory($packageHistoryObj, $BLock3rdData); if ($error) { $db->rollback(); return -1; } $db->commit(); return 1; } public function updatePackageHistoryFromHistory($id, $BLock3rdData) { global $db, $user; $packageHistoryObj = new PackageHistory($db); $packageHistoryObj->fetch($id); $error = $this->commonUpdatePackageHistory($packageHistoryObj, $BLock3rdData); if ($error) { $db->rollback(); return -1; } $db->commit(); return 1; } private function commonUpdatePackageHistory($packageHistoryObj, $BLock3rdData) { global $db, $user; $error = 0; $dailyClosing = new DailyClosing(); $withholdingArray = $this->createWitholdingsArray(); $withholdingRecords = $dailyClosing->getWithholdingRecords($withholdingArray); $packageName = $this->getPackageLabelById($packageHistoryObj->package_id); //package name $groupName = $this->getGroupLabelById($packageHistoryObj->fk_group_id); // grouptool name $usergroupName = $this->getUserGroupRefById($packageHistoryObj->fk_groupuser_id); // usergroup name # 1stBlock $block1stArray = ["package_name" => $packageName, "grouptool_name" => $groupName, "groupuser_name" => $usergroupName]; // groups # 2ndBlock $devicesArray = $this->getDevicesById($db, $packageHistoryObj->package_id); $block2ndArray = $dailyClosing->getDevicesList($devicesArray, $withholdingRecords); // devices list $additionals = [ 'bp_card' => ["pieces" => $this->getDataFromPackageAdditionals($packageHistoryObj->id, 'bp_card'), "withholding" => $withholdingRecords['bp_card']], 'dnc_ticket' => ["pieces" => $this->getDataFromPackageAdditionals($packageHistoryObj->id, 'dnc_ticket'), "withholding" => $withholdingRecords['dnc_ticket']], 'rr_ticket' => ["pieces" => $this->getDataFromPackageAdditionals($packageHistoryObj->id, 'rr_ticket'), "withholding" => $withholdingRecords['rr_ticket']] ]; # 3rdBlock $changes = $this->getChanges(); $block3rdArray = $dailyClosing->getDailyIncomes($changes); //Daily incomes # 4thBlock $block4thArray = $BLock3rdData; //total incomes if (isset($withholdingRecords['daily_HUFcash'])) { $block4thArray['WHHUFcash'] = $withholdingRecords['daily_HUFcash']; } if (isset($withholdingRecords['daily_EURcash'])) { $block4thArray['WHEURcash'] = $withholdingRecords['daily_EURcash']; } $fullBlockForJSON = ["block1" => $block1stArray, "block2" => $block2ndArray, "additionals" => $additionals, "block3" => $block3rdArray, "block4" => $block4thArray]; $savedDataJSON = json_encode($fullBlockForJSON); $fullPath = $this->getFullPath($packageHistoryObj, $BLock3rdData, $withholdingArray, $additionals); $jsonArray = $dailyClosing->getJSONArray($_REQUEST['withholding']); $db->begin(); $packageHistoryObj->note_public = json_encode($jsonArray); $packageHistoryObj->status = 1; $packageHistoryObj->income_pdf = $fullPath; $packageHistoryObj->date_closing = time(); $packageHistoryObj->dailyclosing_json = $savedDataJSON; $result = $packageHistoryObj->update($user); if (!$result) { $error++; } # WithHolding foreach ($withholdingArray as $key => $value) { $now = dol_now(); $deviceName = $this->getDeviceNameFromInventoryById($this->getDeviceIdFromString($key)); $withholdingObj = new UserInvoice($db); $withholdingObj->ref = $this->generateRefForWithold(); $withholdingObj->label = 'Visszatartás / Withold ' . date("Y-m-d", $now); $withholdingObj->description = $this->createDescriptionToWithold($key, $value, $now, $packageName, $groupName, $deviceName); $withholdingObj->user_id = $packageHistoryObj->user_id; $withholdingObj->payment_type = 1; $withholdingObj->amount = $value; $saved = $withholdingObj->create($user); if (!$saved) { $error++; } } # Kick off from UsersGroup $groupUsersObj = new GroupUsers($db); $sql = "SELECT rowid, date_creation FROM " . MAIN_DB_PREFIX . $groupUsersObj->table_element . " WHERE fk_user = {$packageHistoryObj->user_id}"; $data = $db->query($sql); $dataArray = pg_fetch_assoc($data); $selectedGroupId = $dataArray['rowid']; $selectedGroupDateCreation = $dataArray['date_creation']; if (isset($selectedGroupId)) { $resultKickOff = $groupUsersObj->deleteLine($user, $selectedGroupId); if (!$resultKickOff) { $error++; } } # Set Usernaplo if (isset($selectedGroupId)) { $userLogedOut = $this->checkUsernaploLogout($packageHistoryObj->user_id, $packageHistoryObj->fk_groupuser_id, $selectedGroupDateCreation); if (!$userLogedOut) { $userNaploObj = new UserNaplo($db); $userNaploObj->user_id = $packageHistoryObj->user_id; $userNaploObj->group_user_id = $packageHistoryObj->fk_groupuser_id; $userNaploObj->status = 0; $resultSetUserNaplo = $userNaploObj->create($user); if (!$resultSetUserNaplo) { $error++; } } } return $error; } public function checkUsernaploLogout($user_id, $group_id, $date_creation) { global $db; $sql = "SELECT date_creation, status FROM llx_settlements_usernaplo WHERE group_user_id = {$group_id} AND user_id = {$user_id} AND date_creation > '{$date_creation}' AND status = 0 ORDER BY date_creation DESC LIMIT 1"; //print $sql;exit; $data = $db->query($sql); while ($row = pg_fetch_assoc($data)) { return true; } return false; } public function getDeviceNameFromInventoryById($id) { global $db; $inventoryObj = new Inventory($db); $result = $inventoryObj->fetch($id); return $inventoryObj->ref; } private function getDeviceIdFromString($key) { return substr($key, 1); } public function getPackageLabelById($id) { global $db; $packageObj = new Package($db); $packageObj->fetch($id); return $packageObj->label; } public function getGroupLabelById($id) { global $db; $groupToolsObj = new Grouptools($db); $groupToolsObj->fetch($id); return $groupToolsObj->label; } public function getUserGroupRefById($id) { global $db; $groupObj = new Group($db); $groupObj->fetch($id); return $groupObj->ref; } private function createDescriptionToWithold($key, $value, $now, $packageName, $groupName, $deviceName) { $string = 'Date: ' . date("Y-m-d H:i:s", $now); $string .= 'Összeg / Amount: ' . $value; $string .= 'Csomag / Package: ' . $packageName; $string .= 'Csoport / Group: ' . $groupName; $string .= 'Eszköz / Device name: ' . $deviceName; return $string; } function generateRefForWithold() { return 'withhold_' . dol_now() . '_' . rand(1000000, 9999999); } private function createWitholdingsArray() { $withholdinArray = []; foreach ($_REQUEST['withholding']['withholdingAmount'] as $key => $value) { if ($value != '') { $newkey = str_replace('_amount', '', $key); $withholdinArray[$newkey] = $value; } } return $withholdinArray; } private function getFullPath($PackageHistoryOpen, $BLock3rdData, $withholdingArray, $additionals) { $data = $PackageHistoryOpen->package_id . '_' . $PackageHistoryOpen->user_id; $html = $this->createHTMLIncome($data, $BLock3rdData, $withholdingArray, $additionals); $pdf = new SettlementsPDFPrint($html); return $pdf->generateSettlementsPDF(); } public function updateUserEntityInUser($userId, $groupEntity) { global $user, $db; $userId = intval($userId); $sql = "UPDATE " . MAIN_DB_PREFIX . "user SET entity = {$groupEntity} WHERE rowid = {$userId};"; $result = $db->query($sql); return $result; } public function getPackageName($packages, $id) { foreach ($packages as $package) { if ($package['rowid'] == $id) { return $package['ref']; } } } public function isClosed($package_id) { $packageHistoryObj = new PackageHistory($this->db); $result = $packageHistoryObj->fetchAll('DESC', 'rowid', 1, 0, ["customsql" => "package_id = {$package_id}"]); if (!empty($result)) { foreach ($result as $record) { //print $record->status; return $record->status; } } else { return true; } } public function assignedUserFromPackageHistory($package_id) { global $db, $langs; $sql = "SELECT u.firstname, u.lastname, u.login FROM " . MAIN_DB_PREFIX . "rollerstorage_packagehistory ph INNER JOIN " . MAIN_DB_PREFIX . "user as u ON u.rowid = ph.user_id WHERE ph.package_id = {$package_id} ORDER BY ph.rowid DESC LIMIT 1"; $data = $db->query($sql); $dataArray = pg_fetch_assoc($data); $name = $dataArray['lastname'] . ' ' . $dataArray['firstname'] . ' (' . $dataArray['login'] . ')'; $name = '' . $name . ''; $noUser = '' . $langs->trans('NoAssignedUser') . ''; return $dataArray['login'] != '' ? $name : $noUser; } public function getChanges() { global $db; $sql = "SELECT ref, title FROM llx_inventory AS i INNER JOIN llx_inventory_extrafields as ie ON ie.fk_object = i.rowid WHERE ie.device_type::integer IN(3,4)"; $data = $db->query($sql); $dataArray = pg_fetch_all($data); return $dataArray; } public function getUsersArrayOfSelector($eztHasznalomAzosszerendelesekre, $package) { $usersArrayOfSelector = []; foreach ($eztHasznalomAzosszerendelesekre as $package1 => $userOfPackage) { if ($userOfPackage > -1 && $package1 != $package['fk_package']) { $usersArrayOfSelector[] = $userOfPackage; } } return implode(',', $usersArrayOfSelector); } public function getUserSelector($eztHasznalomAzosszerendelesekre, $package, $guser) { if ($eztHasznalomAzosszerendelesekre[$package['fk_package']] == $guser['fk_user']) { return 'selected'; } else { return ''; } } public function isRecordClosed($user_id, $package_id, $group_id) { global $db; $packageHistoryObj = new PackageHistory($db); $now = date("Y-m-d", dol_now()); $from = $now . ' 00:00:00'; $to = $now . ' 23:59:59'; $result = $packageHistoryObj->fetchAll('DESC', 'rowid', 1, 0, ["customsql" => "fk_group_id = {$group_id} AND package_id = {$package_id} AND user_id = {$user_id} AND status = 1 AND date_creation BETWEEN '{$from}' AND '{$to}'"]); if ($result == -1) { return false; } foreach ($result as $record) { return $record->status; } } public function checkEquality($id) { global $db; if (isset($id)) { $userCounter = $this->getNumberOfUsers(); $usersCount = $userCounter[$id][0]; $assignedPackagesCount = count($this->getPackagesById($id)); return $usersCount == $assignedPackagesCount; } return true; } public function checkSumAndUsers($toselectnewpackage, $id) { global $db; $userCounter = $this->getNumberOfUsers(); $userCounter[$id][0]; $sum = count($this->getPackagesById($id)) + count($toselectnewpackage); $users = $userCounter[$id][0]; return $sum > $users; } public function userSelectorOptions($usersOfThisGroup, $package, $eztHasznalomAzosszerendelesekre, $id) { $pdfArray = []; if (!empty($usersOfThisGroup)) { foreach ($usersOfThisGroup as $guser) { $pdfArray[$guser['fk_user']] = $this->getPDFName2($guser['fk_user'], $package['fk_package'], $id); $selectedUserInSelector = $this->getUserSelector($eztHasznalomAzosszerendelesekre, $package, $guser); $nickname = isset($guser['nickname']) ? $guser['nickname'] . ', ' : ''; $userSelector .= ''; } } return [$userSelector, $pdfArray]; } public function createPopupBox($package, $DeviceCounter) { global $db, $langs; $numberOfDevices = ''; $devicesArray = []; if (isset($DeviceCounter[$package['fk_package']])) { $numberOfDevices = '(' . $DeviceCounter[$package['fk_package']] . ' ' . $langs->trans('device') . ')'; $devicesArray = $this->getDevicesById($db, $package['fk_package']); } $popupBox2 = $this->createPopoupBoxForDeviceList($numberOfDevices, $devicesArray); if ($popupBox2 == '') { $popupBox2 = '
' . $langs->trans('emptyPackage') . '
'; } return $popupBox2; } public function getSelectableUsers($fk_group_id) { global $db; $userArray = []; $sql = "SELECT t.rowid, t.lastname,t.firstname, t.login FROM " . MAIN_DB_PREFIX . "user as t WHERE rowid IN(SELECT gu.fk_user FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup AS ugtg INNER JOIN " . MAIN_DB_PREFIX . "settlements_groupusers as gu ON gu.fk_settlements_group = ugtg.fk_groupid WHERE fk_group_tools_id = {$fk_group_id} /*AND date_trunc('day', gu.date_creation) = CURRENT_DATE*/ AND gu.fk_user NOT IN(SELECT ph.user_id FROM " . MAIN_DB_PREFIX . "rollerstorage_packagehistory AS ph WHERE ph.fk_group_id = {$fk_group_id} AND ph.status in (0) AND date_trunc('day', ph.date_creation) = CURRENT_DATE ORDER BY rowid DESC)) ORDER BY t.lastname ASC, t.firstname ASC"; //print $sql; $data = $db->query($sql); $dataArray = pg_fetch_all($data); if (is_array($dataArray)) { foreach ($dataArray as $row) { $userArray[$row['rowid']] = $row['firstname'] . ' ' . $row['lastname'] . ' (' . $row['login'] . ')'; } } return $userArray; } public function getPackageHistoryId($package_id, $fk_group_id, $user_id) { global $db; $packageHistoryObj = new PackageHistory($db); $result = $packageHistoryObj->fetchAll('DESC', 'rowid', 1, 0, ["customsql" => "package_id = {$package_id} AND fk_group_id = {$fk_group_id} AND user_id = {$user_id}"]); foreach ($result as $row) { return $row->id; } } public function getAdditionalTitle($key) { switch ($key) { case 'bp_card': return 'Budapest Card'; break; case 'dnc_ticket': return 'Dinner & Cruise'; break; case 'rr_ticket': return 'RiverRide'; break; case 'change_huf': return 'Change HUF'; break; case 'change_eur': return 'Change EUR'; break; default: # code... break; } } private function getDataFromPackageAdditionals($id, $name) { global $db; $packageAdditionalsObj = new Package_additionals($db); $result = $packageAdditionalsObj->fetchAll('', '', 0, 0, ["customsql" => "fk_package_history_id = {$id}"]); if ($result > 0) { foreach ($result as $row) { return ($row->$name); } } return ''; } public function checkClose($id) { global $db; $packageHistoryObj = new PackageHistory($db); $packageHistoryObj->fetch($id); return $packageHistoryObj->status; } public function getJSON($id, $record) { global $db; $packageHistoryObj = new PackageHistory($db); $packageHistoryObj->fetch($id); return $packageHistoryObj->$record; } public function generateDeliveryJSON($object, $bpcard = '', $dandc = '', $riverride = '') { global $db; $dailyClosing = new DailyClosing(); $withholdingRecords = []; $additionals = ["bp_card" => $bpcard, "dnc_ticket" => $dandc, "rr_ticket" => $riverride]; $packageName = $this->getPackageLabelById($object->package_id); //package name $groupName = $this->getGroupLabelById($object->fk_group_id); // grouptool name $usergroupName = $this->getUserGroupRefById($object->fk_groupuser_id); // usergroup name $block1stArray = ["package_name" => $packageName, "grouptool_name" => $groupName, "groupuser_name" => $usergroupName]; // groups # 2ndBlock $devicesArray = $this->getDevicesById($db, $object->package_id); $block2ndArray = $dailyClosing->getDevicesList($devicesArray, $withholdingRecords); // devices list $fullBlockForJSON = ["block1" => $block1stArray, "block2" => $block2ndArray, "additionals" => $additionals, "changes" => $this->getChanges()]; return $fullBlockForJSON; } public function packageClosed($id) { $packageHistoryObj = new PackageHistory($this->db); $packageHistoryObj->fetch($id); return $packageHistoryObj->status; } public function checkConfirmation($id) { global $db; $sql = "SELECT * FROM " . MAIN_DB_PREFIX . "settlements_groupusers WHERE date_trunc('day', date_creation) = CURRENT_DATE AND fk_settlements_group = (SELECT fk_groupid FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup WHERE fk_group_tools_id = {$id}) ORDER BY rowid DESC"; $data = $db->query($sql); $dataArray = pg_fetch_all($data); if (is_array($dataArray)) { return true; } return false; } public function groupList($groups, $id) { global $langs, $db; $string = ''; $string .= ''; $stringEmpty = ''; $stringEmpty .= '
'; foreach ($groups as $group) { $usersOfGroups = $this->getUsersById($db, $group['rowid']); if (count($usersOfGroups) > 0) { $peopleOrPerson = count($usersOfGroups) > 1 ? $langs->trans('people') : $langs->trans('person'); $headcount = count($usersOfGroups) > 0 ? count($usersOfGroups) . ' ' . $peopleOrPerson : ' '; $coloredLine = $id == $group['rowid'] ? ' background-color: #b0c4de;' : ''; $string .= ''; } else { $peopleOrPerson = count($usersOfGroups) > 1 ? $langs->trans('people') : $langs->trans('person'); $headcount = count($usersOfGroups) > 0 ? count($usersOfGroups) . ' ' . $peopleOrPerson : ' '; $coloredLine = $id == $group['rowid'] ? ' background-color: #b0c4de;' : ''; $stringEmpty .= ''; } } $string .= '
' . $group['ref'] . '

' . $group['label'] . '
' . $headcount . '

' . $group['ref'] . '

' . $group['label'] . '
' . $headcount . '

'; $string .= '
Empty groups
'; $stringEmpty .= ''; return $string . $stringEmpty; } function getFilteredPackages($string, $groupid) { global $db; $allPackages = []; $entity_id = $this->getEntityIdFromgoupTools($groupid); $sql = "SELECT pa.rowid, pa.ref, pa.label FROM " . MAIN_DB_PREFIX . "settlements_package as pa WHERE pa.rowid NOT IN (SELECT fk_package FROM " . MAIN_DB_PREFIX . "settlements_grouptoolspackages)"; $sql .= " AND pa.fk_entity = '{$entity_id}' AND pa.ref LIKE '%{$string}%' ORDER BY pa.ref"; //print $sql; $data = $db->query($sql); if (!$data) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); return $allPackages; } while ($row = pg_fetch_assoc($data)) { $allPackages['freePackages'][$row['rowid']] = $row; if (isset($row['fk_object'])) { $inventoryObj = new Inventory($db); $inventoryObj->fetch($row['fk_object']); $assigned_device_data = ['rowid' => $inventoryObj->id, 'title' => $inventoryObj->title, 'ref' => $inventoryObj->ref, 'device_type' => $inventoryObj->array_options['options_device_type']]; $allPackages['freePackages'][$row['rowid']]['assigned_device_data'] = $assigned_device_data; } } $sqlAddedPackages = "SELECT pa.rowid, pa.ref, pa.label, gp.rowid as gprowid, gp.ref as gpref, gp.label as gplabel FROM " . MAIN_DB_PREFIX . "settlements_grouptoolspackages as gpp INNER JOIN " . MAIN_DB_PREFIX . "settlements_package as pa ON pa.rowid = gpp.fk_package INNER JOIN " . MAIN_DB_PREFIX . "settlements_grouptools as gp ON gp.rowid = gpp.fk_group"; $data2 = $this->db->query($sqlAddedPackages); if (!$data2) { dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR); setEventMessage($this->langs->trans('($helper->getAllPackagesFromSelectedEntity()) settlements_grouptoolspackages'), 'errors'); return $allPackages; } while ($row = pg_fetch_assoc($data2)) { $allPackages['addedPackages'][] = $row; } return $allPackages; } public function getGTAndGUArray() { global $db; $sql = "SELECT fk_groupid, fk_group_tools_id FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup"; $result = $db->query($sql); if (pg_num_rows($result) > 0) { while ($row = pg_fetch_assoc($result)) { $array[$row['fk_groupid']] = $row['fk_group_tools_id']; } return $array; } return []; } public function freeToolsDiv($allTools) { $string = ''; global $langs; $string .= ''; foreach ($allTools['freeTools'] as $freeTool) { $status3 = $freeTool['status'] != 115 ? '' : ''; $url_3 = '/custom/rollerstorage/card.php?id=' . $freeTool['rowid'] . '&inventory_id=' . $freeTool['fk_warehouse'] . '&backtopage=/custom/rollerstorage/rollerstorageinventoryindex.php?inventory_id=' . $freeTool['fk_warehouse'] . '&idmenu=1599&leftmenu='; if ($freeTool['device_type'] == 1) { $disabled = !isset($freeTool['assigned_device_data']) ? 'disabled' : ''; $string .= ' '; $statusAssigned3 = $freeTool['assigned_device_data']['status'] != 115 ? '' : ''; $string .= ''; if (!isset($freeTool['assigned_device_data'])) { $string .= ''; } else { $string .= ''; } $string .= ''; } elseif ($freeTool['device_type'] == 0 || $freeTool['device_type'] == 3 || $freeTool['device_type'] == 4 || $freeTool['device_type'] == 5) { $string .= ' '; } } $string .= '
' . $freeTool['ref'] . ' - ' . $freeTool['title'] . ' ' . $status3 . '
    ' . $langs->trans('NoCardreadder') . '     ' . $freeTool['assigned_device_data']['ref'] . ' - ' . $freeTool['assigned_device_data']['title'] . ' ' . $statusAssigned3 . '
' . $freeTool['ref'] . ' - ' . $freeTool['title'] . ' ' . $status3 . '
'; return $string; } public function addedToolsDiv($allTools) { global $langs; $string = ''; $string .= '
'; if ($allTools['addedTools']) { foreach ($allTools['addedTools'] as $addedKey => $addedValue) { $string .= ''; foreach ($addedValue as $record) { $statusAddedTool = $record['status'] != 115 ? '' : ''; if ($record['device_type'] == 1) { $url_added = '/custom/rollerstorage/card.php?id=' . $record['rowid'] . '&inventory_id=' . $record['fk_warehouse'] . '&backtopage=/custom/rollerstorage/rollerstorageinventoryindex.php?inventory_id=' . $record['fk_warehouse'] . '&idmenu=1599&leftmenu='; $string .= ' '; $statusAssigned3 = $record['assigned_device_data']['status'] != 115 ? '' : ''; $url_added = '/custom/rollerstorage/card.php?id=' . $record['assigned_device_data']['rowid'] . '&inventory_id=' . $record['fk_warehouse'] . '&backtopage=/custom/rollerstorage/rollerstorageinventoryindex.php?inventory_id=' . $record['fk_warehouse'] . '&idmenu=1599&leftmenu='; $string .= ''; if (!isset($record['assigned_device_data'])) { $string .= ''; } else { $string .= ''; } $string .= ''; } elseif ($record['device_type'] == 0 || $record['device_type'] == 3 || $record['device_type'] == 4 || $record['device_type'] == 5) { $url_added = '/custom/rollerstorage/card.php?id=' . $record['rowid'] . '&inventory_id=' . $record['fk_warehouse'] . '&backtopage=/custom/rollerstorage/rollerstorageinventoryindex.php?inventory_id=' . $record['fk_warehouse'] . '&idmenu=1599&leftmenu='; $string .= ' '; } } } } $string .= '
' . $allTools['addedPackages'][$addedKey] . '
' . $record['ref'] . ' - ' . $record['title'] . '
' . $langs->trans('NoCardreadder') . ' ' . $record['assigned_device_data']['ref'] . ' - ' . $record['assigned_device_data']['title'] . ' ' . $statusAssigned3 . '
' . $record['ref'] . ' - ' . $record['title'] . ' ' . $statusAddedTool . '
'; return $string; } public function addedPackageDiv($allPackages, $DeviceCounter) { global $langs, $db; $string = ''; foreach ($allPackages['addedPackages'] as $addedPackage) { $numberOfDevices4 = ''; $devicesArray4 = []; if (isset($DeviceCounter[$addedPackage['gprowid']])) { $numberOfDevices4 = '(' . $DeviceCounter[$addedPackage['gprowid']] . ' ' . $langs->trans('device') . ')'; $devicesArray4 = $this->getDevicesById($db, $addedPackage['rowid']); } $popupBox4Text = $this->createPopoupBoxForDeviceList($numberOfDevices4, $devicesArray4); $popupBox4 = $popupBox4Text != '' ? $popupBox4Text : '' . $langs->trans('emptyPackage') . ''; print '
' . $addedPackage['gplabel'] . '
' . $addedPackage['ref'] . ' ' . $popupBox4 . '
'; } } }