function createCurrency($company_id, $type) { $cf = new CurrencyFactory(); $cf->setCompany($company_id); $cf->setStatus(10); switch ($type) { case 10: //USD $cf->setName('US Dollar'); $cf->setISOCode('USD'); $cf->setConversionRate('1.000000000'); $cf->setAutoUpdate(FALSE); $cf->setBase(TRUE); $cf->setDefault(TRUE); break; case 20: //CAD $cf->setName('Canadian Dollar'); $cf->setISOCode('CAD'); $cf->setConversionRate('0.850000000'); $cf->setAutoUpdate(TRUE); $cf->setBase(FALSE); $cf->setDefault(FALSE); break; } if ($cf->isValid()) { $insert_id = $cf->Save(); Debug::Text('Currency ID: ' . $insert_id, __FILE__, __LINE__, __METHOD__, 10); return $insert_id; } Debug::Text('Failed Creating Currency!', __FILE__, __LINE__, __METHOD__, 10); return FALSE; }
function getISOCodesArray() { return CurrencyFactory::getISOCodesArray(); }
//Debug::setVerbosity(11); $smarty->assign('title', TTi18n::gettext($title = 'Currency List')); /* * Get FORM variables */ extract(FormVariables::GetVariables(array('action', 'page', 'sort_column', 'sort_order', 'ids'))); URLBuilder::setURL($_SERVER['SCRIPT_NAME'], array('sort_column' => $sort_column, 'sort_order' => $sort_order, 'page' => $page)); $sort_array = NULL; if ($sort_column != '') { $sort_array = array($sort_column => $sort_order); } Debug::Arr($ids, 'Selected Objects', __FILE__, __LINE__, __METHOD__, 10); $action = Misc::findSubmitButton(); switch ($action) { case 'update_rates': CurrencyFactory::updateCurrencyRates($current_company->getId()); Redirect::Page(URLBuilder::getURL(NULL, 'CurrencyList.php')); break; case 'add': Redirect::Page(URLBuilder::getURL(NULL, 'EditCurrency.php')); break; case 'delete' or 'undelete': if (strtolower($action) == 'delete') { $delete = TRUE; } else { $delete = FALSE; } $clf = new CurrencyListFactory(); if (isset($ids) and is_array($ids)) { foreach ($ids as $id) { $clf->getByIdAndCompanyId($id, $current_company->getId());
/** * Return user records based on advanced filter criteria. * * @param int $company_id Company ID * @param array $filter_data Filter criteria in array('id' => array(1,2), 'last_name' => 'smith' ) format, with possible top level array keys as follows: id, exclude_id, status_id, user_group_id, default_branch_id, default_department_id, title_id, currency_id, permission_control_id, pay_period_schedule_id, policy_group_id, sex_id, first_name, last_name, home_phone, work_phone, country, province, city, address1, address2, postal_code, employee_number, user_name, sin, work_email, home_email, tag, last_login_date, created_by, created_date, updated_by, updated_date * @param int $limit Optional. Restrict the number of records returned * @param int $page Optional. Specify the page of records to return * @param array $where Optional. Additional WHERE clauses in array( 'column' => 'value', 'column' => 'value' ) format. * @param array $order Optional. Sort order in array( 'column' => ASC, 'column2' => DESC ) format. * * @return object $this */ function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } if (isset($filter_data['user_status_id'])) { $filter_data['status_id'] = $filter_data['user_status_id']; } if (isset($filter_data['include_user_id'])) { $filter_data['id'] = $filter_data['include_user_id']; } if (isset($filter_data['exclude_user_id'])) { $filter_data['exclude_id'] = $filter_data['exclude_user_id']; } //Some of these are passed from Flex Schedule view. if (isset($filter_data['default_branch_ids'])) { $filter_data['default_branch_id'] = $filter_data['default_branch_ids']; } if (isset($filter_data['default_department_ids'])) { $filter_data['default_department_id'] = $filter_data['default_department_ids']; } if (isset($filter_data['group_id'])) { $filter_data['user_group_id'] = $filter_data['group_id']; } if (isset($filter_data['user_title_id'])) { $filter_data['title_id'] = $filter_data['user_title_id']; } if (isset($filter_data['user_tag'])) { $filter_data['tag'] = $filter_data['user_tag']; } //$additional_order_fields = array('b.name', 'c.name', 'd.name', 'e.name'); $additional_order_fields = array('default_branch', 'default_department', 'default_job', 'default_job_item', 'sex', 'user_group', 'title', 'currency', 'permission_control', 'pay_period_schedule', 'policy_group'); $sort_column_aliases = array('type' => 'type_id', 'status' => 'status_id', 'sex' => 'sex_id', 'full_name' => 'last_name'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('status_id' => 'asc', 'last_name' => 'asc', 'first_name' => 'asc', 'middle_name' => 'asc'); $strict = FALSE; } else { //Do order by column conversions, because if we include these columns in the SQL //query, they contaminate the data array. //Always try to order by status first so INACTIVE employees go to the bottom. if (!isset($order['status_id'])) { $order = Misc::prependArray(array('status_id' => 'asc'), $order); } //Always sort by last name,first name after other columns if (!isset($order['last_name'])) { $order['last_name'] = 'asc'; } if (!isset($order['first_name'])) { $order['first_name'] = 'asc'; } $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $compf = new CompanyFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $cf = new CurrencyFactory(); $pcf = new PermissionControlFactory(); $puf = new PermissionUserFactory(); $ppsuf = new PayPeriodScheduleUserFactory(); $ppsf = new PayPeriodScheduleFactory(); $pguf = new PolicyGroupUserFactory(); $pgf = new PolicyGroupFactory(); $egf = new EthnicGroupFactory(); if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) { $jf = new JobFactory(); $jif = new JobItemFactory(); } $ph = array('company_id' => $company_id); $query = ' select a.*, compf.name as company, b.name as default_branch, c.name as default_department, d.name as user_group, e.name as title, f.name as currency, f.conversion_rate as currency_rate, g.id as permission_control_id, g.name as permission_control, h.id as pay_period_schedule_id, h.name as pay_period_schedule, i.id as policy_group_id, i.name as policy_group, egf.name as ethnic_group, '; $query .= Permission::getPermissionIsChildIsOwnerSQL(isset($filter_data['permission_current_user_id']) ? $filter_data['permission_current_user_id'] : 0, 'a.id'); if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) { $query .= ' jf.name as default_job, jif.name as default_job_item, '; } $query .= ' y.first_name as created_by_first_name, y.middle_name as created_by_middle_name, y.last_name as created_by_last_name, z.first_name as updated_by_first_name, z.middle_name as updated_by_middle_name, z.last_name as updated_by_last_name from ' . $this->getTable() . ' as a LEFT JOIN ' . $compf->getTable() . ' as compf ON ( a.company_id = compf.id AND compf.deleted = 0) LEFT JOIN ' . $bf->getTable() . ' as b ON ( a.default_branch_id = b.id AND b.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as c ON ( a.default_department_id = c.id AND c.deleted = 0) LEFT JOIN ' . $ugf->getTable() . ' as d ON ( a.group_id = d.id AND d.deleted = 0 ) LEFT JOIN ' . $utf->getTable() . ' as e ON ( a.title_id = e.id AND e.deleted = 0 ) LEFT JOIN ' . $cf->getTable() . ' as f ON ( a.currency_id = f.id AND f.deleted = 0 ) LEFT JOIN ' . $egf->getTable() . ' as egf ON ( a.ethnic_group_id = egf.id AND egf.deleted = 0 ) '; if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) { $query .= ' LEFT JOIN ' . $jf->getTable() . ' as jf ON a.default_job_id = jf.id'; $query .= ' LEFT JOIN ' . $jif->getTable() . ' as jif ON a.default_job_item_id = jif.id'; } $query .= ' LEFT JOIN ( SELECT g2.*,g1.user_id FROM ' . $puf->getTable() . ' as g1, ' . $pcf->getTable() . ' as g2 WHERE ( g1.permission_control_id = g2.id AND g2.deleted = 0) ) as g ON ( a.id = g.user_id ) LEFT JOIN ( SELECT h2.*, h1.user_id FROM ' . $ppsuf->getTable() . ' as h1, ' . $ppsf->getTable() . ' as h2 WHERE ( h1.pay_period_schedule_id = h2.id AND h2.deleted = 0) ) as h ON ( a.id = h.user_id ) LEFT JOIN ( SELECT i2.*, i1.user_id FROM ' . $pguf->getTable() . ' as i1, ' . $pgf->getTable() . ' as i2 WHERE ( i1.policy_group_id = i2.id AND i2.deleted = 0) ) as i ON ( a.id = i.user_id ) '; $query .= Permission::getPermissionHierarchySQL($company_id, isset($filter_data['permission_current_user_id']) ? $filter_data['permission_current_user_id'] : 0, 'a.id'); $query .= ' LEFT JOIN ' . $this->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 ) LEFT JOIN ' . $this->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 ) where a.company_id = ? '; $query .= Permission::getPermissionIsChildIsOwnerFilterSQL($filter_data, 'a.id'); $query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('a.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL; if (isset($filter_data['status']) and trim($filter_data['status']) != '' and !isset($filter_data['status_id'])) { $filter_data['status_id'] = Option::getByFuzzyValue($filter_data['status'], $this->getOptions('status')); } $query .= isset($filter_data['status_id']) ? $this->getWhereClauseSQL('a.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL; if (isset($filter_data['include_subgroups']) and (bool) $filter_data['include_subgroups'] == TRUE) { $uglf = new UserGroupListFactory(); $filter_data['user_group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['user_group_id'], TRUE); } $query .= isset($filter_data['user_group_id']) ? $this->getWhereClauseSQL('a.group_id', $filter_data['user_group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['user_group']) ? $this->getWhereClauseSQL('d.name', $filter_data['user_group'], 'text', $ph) : NULL; $query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('a.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_branch']) ? $this->getWhereClauseSQL('b.name', $filter_data['default_branch'], 'text', $ph) : NULL; $query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('a.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_department']) ? $this->getWhereClauseSQL('c.name', $filter_data['default_department'], 'text', $ph) : NULL; $query .= isset($filter_data['title_id']) ? $this->getWhereClauseSQL('a.title_id', $filter_data['title_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['title']) ? $this->getWhereClauseSQL('e.name', $filter_data['title'], 'text', $ph) : NULL; $query .= isset($filter_data['ethnic_group_id']) ? $this->getWhereClauseSQL('a.ethnic_group_id', $filter_data['ethnic_group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['ethnic_group']) ? $this->getWhereClauseSQL('egf.name', $filter_data['ethnic_group'], 'text', $ph) : NULL; $query .= isset($filter_data['currency_id']) ? $this->getWhereClauseSQL('a.currency_id', $filter_data['currency_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['currency']) ? $this->getWhereClauseSQL('f.name', $filter_data['currency'], 'text', $ph) : NULL; $query .= isset($filter_data['permission_control_id']) ? $this->getWhereClauseSQL('g.id', $filter_data['permission_control_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['permission_control']) ? $this->getWhereClauseSQL('g.name', $filter_data['permission_control'], 'text', $ph) : NULL; $query .= isset($filter_data['pay_period_schedule_id']) ? $this->getWhereClauseSQL('i.pay_period_schedule_id', $filter_data['pay_period_schedule_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['pay_period_schedule']) ? $this->getWhereClauseSQL('h.name', $filter_data['pay_period_schedule'], 'text', $ph) : NULL; $query .= isset($filter_data['policy_group_id']) ? $this->getWhereClauseSQL('k.policy_group_id', $filter_data['policy_group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['policy_group']) ? $this->getWhereClauseSQL('i.name', $filter_data['policy_group'], 'text', $ph) : NULL; if (isset($filter_data['sex']) and trim($filter_data['sex']) != '' and !isset($filter_data['sex_id'])) { $filter_data['sex_id'] = Option::getByFuzzyValue($filter_data['sex'], $this->getOptions('sex')); } $query .= isset($filter_data['sex_id']) ? $this->getWhereClauseSQL('a.sex_id', $filter_data['sex_id'], 'text_list', $ph) : NULL; $query .= isset($filter_data['first_name']) ? $this->getWhereClauseSQL('a.first_name', $filter_data['first_name'], 'text_metaphone', $ph) : NULL; $query .= isset($filter_data['last_name']) ? $this->getWhereClauseSQL('a.last_name', $filter_data['last_name'], 'text_metaphone', $ph) : NULL; $query .= isset($filter_data['home_phone']) ? $this->getWhereClauseSQL('a.home_phone', $filter_data['home_phone'], 'phone', $ph) : NULL; $query .= isset($filter_data['work_phone']) ? $this->getWhereClauseSQL('a.work_phone', $filter_data['work_phone'], 'phone', $ph) : NULL; $query .= isset($filter_data['country']) ? $this->getWhereClauseSQL('a.country', $filter_data['country'], 'upper_text_list', $ph) : NULL; $query .= isset($filter_data['province']) ? $this->getWhereClauseSQL('a.province', $filter_data['province'], 'upper_text_list', $ph) : NULL; $query .= isset($filter_data['city']) ? $this->getWhereClauseSQL('a.city', $filter_data['city'], 'text', $ph) : NULL; $query .= isset($filter_data['address1']) ? $this->getWhereClauseSQL('a.address1', $filter_data['address1'], 'text', $ph) : NULL; $query .= isset($filter_data['address2']) ? $this->getWhereClauseSQL('a.address2', $filter_data['address2'], 'text', $ph) : NULL; $query .= isset($filter_data['postal_code']) ? $this->getWhereClauseSQL('a.postal_code', $filter_data['postal_code'], 'text', $ph) : NULL; $query .= isset($filter_data['employee_number']) ? $this->getWhereClauseSQL('a.employee_number', $filter_data['employee_number'], 'numeric', $ph) : NULL; $query .= isset($filter_data['user_name']) ? $this->getWhereClauseSQL('a.user_name', $filter_data['user_name'], 'text', $ph) : NULL; $query .= isset($filter_data['sin']) ? $this->getWhereClauseSQL('a.sin', $filter_data['sin'], 'numeric', $ph) : NULL; $query .= isset($filter_data['email']) ? 'AND (' . $this->getWhereClauseSQL('a.work_email', $filter_data['email'], 'text', $ph, NULL, FALSE) . ' OR ' . $this->getWhereClauseSQL('a.home_email', $filter_data['email'], 'text', $ph, NULL, FALSE) . ')' : NULL; $query .= isset($filter_data['work_email']) ? $this->getWhereClauseSQL('a.work_email', $filter_data['work_email'], 'text', $ph) : NULL; $query .= isset($filter_data['home_email']) ? $this->getWhereClauseSQL('a.home_email', $filter_data['home_email'], 'text', $ph) : NULL; $query .= isset($filter_data['tag']) ? $this->getWhereClauseSQL('a.id', array('company_id' => $company_id, 'object_type_id' => 200, 'tag' => $filter_data['tag']), 'tag', $ph) : NULL; //$query .= ( isset($filter_data['longitude']) ) ? $this->getWhereClauseSQL( 'a.longitude', $filter_data['longitude'], 'numeric', $ph ) : NULL; if (isset($filter_data['last_login_date']) and trim($filter_data['last_login_date']) != '') { $date_filter = $this->getDateRangeSQL($filter_data['last_login_date'], 'a.last_login_date'); if ($date_filter != FALSE) { $query .= ' AND ' . $date_filter; } unset($date_filter); } if (isset($filter_data['created_date']) and trim($filter_data['created_date']) != '') { $date_filter = $this->getDateRangeSQL($filter_data['created_date'], 'a.created_date'); if ($date_filter != FALSE) { $query .= ' AND ' . $date_filter; } unset($date_filter); } if (isset($filter_data['updated_date']) and trim($filter_data['updated_date']) != '') { $date_filter = $this->getDateRangeSQL($filter_data['updated_date'], 'a.updated_date'); if ($date_filter != FALSE) { $query .= ' AND ' . $date_filter; } unset($date_filter); } $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND ( a.deleted = 0 ) '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); //Debug::Arr($ph, 'Query: '. $query, __FILE__, __LINE__, __METHOD__,10); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function postSave() { $this->removeCache($this->getId()); $this->remoteSave(); if ($this->getDeleted() == FALSE) { //Add base currency for this new company. if ($this->getEnableAddCurrency() == TRUE) { $clf = new CurrencyListFactory(); $clf->getByCompanyId($this->getId()); if ($clf->getRecordCount() == 0) { Debug::text('Adding Default Currency', __FILE__, __LINE__, __METHOD__, 9); $cf = new CurrencyFactory(); $country_to_currency_map_arr = $cf->getOptions('country_currency'); if (isset($country_to_currency_map_arr[$this->getCountry()])) { $base_currency = $country_to_currency_map_arr[$this->getCountry()]; Debug::text('Found Base Currency For Country: ' . $this->getCountry() . ' Currency: ' . $base_currency, __FILE__, __LINE__, __METHOD__, 9); } else { Debug::text('DID NOT Find Base Currency For Country: ' . $this->getCountry() . ' Using default USD.', __FILE__, __LINE__, __METHOD__, 9); $base_currency = 'USD'; } $cf->setCompany($this->getId()); $cf->setStatus(10); $cf->setName($base_currency); $cf->setISOCode($base_currency); $cf->setConversionRate('1.000000000'); $cf->setAutoUpdate(FALSE); $cf->setBase(TRUE); $cf->setDefault(TRUE); if ($cf->isValid()) { $cf->Save(); } } } if ($this->getEnableAddPermissionGroupPreset() == TRUE) { Debug::text('Adding Preset Permission Groups', __FILE__, __LINE__, __METHOD__, 9); $pf = new PermissionFactory(); $pf->StartTransaction(); if ($this->getProductEdition() == 20) { $preset_flags = array('job' => 1, 'invoice' => 1, 'document' => 1); } else { $preset_flags = array(); } $preset_options = $pf->getOptions('preset'); foreach ($preset_options as $preset_id => $preset_name) { $pcf = new PermissionControlFactory(); $pcf->setCompany($this->getId()); $pcf->setName($preset_name); $pcf->setDescription(''); if ($pcf->isValid()) { $pcf_id = $pcf->Save(FALSE); $pf->applyPreset($pcf_id, $preset_id, $preset_flags); } } //$pf->FailTransaction(); $pf->CommitTransaction(); } if ($this->getEnableAddStation() == TRUE) { Debug::text('Adding Default Station', __FILE__, __LINE__, __METHOD__, 9); //Enable punching in from all stations $sf = new StationFactory(); $sf->setCompany($this->getId()); $sf->setStatus(20); $sf->setType(10); $sf->setSource('ANY'); $sf->setStation('ANY'); $sf->setDescription('All stations'); $sf->setGroupSelectionType(10); $sf->setBranchSelectionType(10); $sf->setDepartmentSelectionType(10); if ($sf->isValid()) { $sf->Save(); } } if ($this->getEnableAddPayStubEntryAccountPreset() == TRUE) { Debug::text('Adding Pay Stub Entry Account Presets', __FILE__, __LINE__, __METHOD__, 9); PayStubEntryAccountFactory::addPresets($this->getId()); } if ($this->getEnableAddCompanyDeductionPreset() == TRUE) { Debug::text('Adding Company Deduction Presets', __FILE__, __LINE__, __METHOD__, 9); CompanyDeductionFactory::addPresets($this->getId()); } if ($this->getEnableAddRecurringHolidayPreset() == TRUE) { Debug::text('Adding Recurring Holiday Presets', __FILE__, __LINE__, __METHOD__, 9); RecurringHolidayFactory::addPresets($this->getId(), $this->getCountry()); } } if ($this->getDeleted() == TRUE) { $ulf = new UserListFactory(); $ulf->getByCompanyId($this->getID()); if ($ulf->getRecordCount() > 0) { $ulf->StartTransaction(); foreach ($ulf as $u_obj) { Debug::text('Deleting User ID: ' . $u_obj->getId(), __FILE__, __LINE__, __METHOD__, 9); $u_obj->setDeleted(TRUE); if ($u_obj->isValid()) { $u_obj->Save(); } } $ulf->CommitTransaction(); } } return TRUE; }
public function get_currency_type_string($currency_type) { return CurrencyFactory::get_currency_type_string(json_decode($currency_type, TRUE)['currency']); }
function getBaseCurrencyHourlyRate($rate) { if ($rate == '') { return FALSE; } if (!is_object($this->getUserObject())) { return FALSE; } $clf = TTnew('CurrencyListFactory'); $clf->getByCompanyIdAndBase($this->getUserObject()->getCompany(), TRUE); if ($clf->getRecordCount() > 0) { $base_currency_obj = $clf->getCurrent(); //If current currency is the base currency, just return the rate. if ($base_currency_obj->getId() == $this->getUserObject()->getCurrency()) { return $rate; } else { //Debug::text(' Base Currency Rate: '. $base_currency_obj->getConversionRate() .' Hourly Rate: '. $rate , __FILE__, __LINE__, __METHOD__,10); return CurrencyFactory::convertCurrency($this->getUserObject()->getCurrency(), $base_currency_obj->getId(), $rate); } } return FALSE; }
* * In accordance with Section 7(b) of the GNU Affero General Public License * version 3, these Appropriate Legal Notices must retain the display of the * "Powered by TimeTrex" logo. If the display of the logo is not reasonably * feasible for technical reasons, the Appropriate Legal Notices must display * the words "Powered by TimeTrex". ********************************************************************************/ /* * $Revision: 417 $ * $Id: AddRecurringHoliday.php 417 2006-12-06 22:58:53Z ipso $ * $Date: 2006-12-06 14:58:53 -0800 (Wed, 06 Dec 2006) $ */ /* * Updates Currency Exchange Rates. * This file should run once a day. * */ require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . 'includes' . DIRECTORY_SEPARATOR . 'global.inc.php'; require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . 'includes' . DIRECTORY_SEPARATOR . 'CLI.inc.php'; //Debug::setVerbosity(11); $clf = new CompanyListFactory(); $clf->getAll(); if ($clf->getRecordCount() > 0) { foreach ($clf as $c_obj) { if ($c_obj->getStatus() != 30) { CurrencyFactory::updateCurrencyRates($c_obj->getId()); } } } Debug::writeToLog(); Debug::Display();
* $Id: EditCurrency.php 1246 2007-09-14 23:47:42Z ipso $ * $Date: 2007-09-14 16:47:42 -0700 (Fri, 14 Sep 2007) $ */ require_once '../../includes/global.inc.php'; require_once Environment::getBasePath() . 'includes/Interface.inc.php'; if (!$permission->Check('currency', 'enabled') or !($permission->Check('currency', 'edit') or $permission->Check('currency', 'edit_own'))) { $permission->Redirect(FALSE); //Redirect } $smarty->assign('title', TTi18n::gettext($title = 'Edit Currency')); // See index.php /* * Get FORM variables */ extract(FormVariables::GetVariables(array('action', 'id', 'data', 'data_saved'))); $cf = new CurrencyFactory(); $action = Misc::findSubmitButton(); $action = strtolower($action); switch ($action) { case 'submit': //Debug::setVerbosity(11); Debug::Text('Submit!', __FILE__, __LINE__, __METHOD__, 10); $cf->setId($data['id']); $cf->setCompany($current_company->getId()); $cf->setStatus($data['status']); $cf->setName($data['name']); $cf->setISOCode($data['iso_code']); $cf->setConversionRate($data['conversion_rate']); if (isset($data['auto_update']) and $data['auto_update'] == 1) { $cf->setAutoUpdate(TRUE); } else {
function postInstall() { global $cache; Debug::text('postInstall: ' . $this->getVersion(), __FILE__, __LINE__, __METHOD__, 9); //Configure currencies for Standard Edition. if ($this->getIsUpgrade() == TRUE) { $clf = new CompanyListFactory(); $clf->StartTransaction(); $clf->getAll(); if ($clf->getRecordCount() > 0) { foreach ($clf as $c_obj) { if ($c_obj->getStatus() == 10) { //Converting to new Accrual Policy table. Debug::text('Converting to new Accrual Policy Table: ' . $c_obj->getName() . ' ID: ' . $c_obj->getId(), __FILE__, __LINE__, __METHOD__, 9); $pglf = new PolicyGroupListFactory(); $pglf->getByCompanyId($c_obj->getId()); if ($pglf->getRecordCount() > 0) { foreach ($pglf as $pg_obj) { Debug::text('Accrual Policy ID: ' . $pg_obj->getColumn('accrual_policy_id'), __FILE__, __LINE__, __METHOD__, 9); if ($pg_obj->getColumn('accrual_policy_id') != '' and $pg_obj->getColumn('accrual_policy_id') != 0) { $pg_obj->setAccrualPolicy(array($pg_obj->getColumn('accrual_policy_id'))); if ($pg_obj->isValid()) { $pg_obj->Save(); } } } } Debug::text('Adding Currency Information to Company: ' . $c_obj->getName() . ' ID: ' . $c_obj->getId(), __FILE__, __LINE__, __METHOD__, 9); $crlf = new CurrencyListFactory(); $crlf->getByCompanyId($c_obj->getId()); if ($crlf->getRecordCount() == 0) { $cf = new CurrencyFactory(); $country_to_currency_map_arr = $cf->getOptions('country_currency'); if (isset($country_to_currency_map_arr[$c_obj->getCountry()])) { $base_currency = $country_to_currency_map_arr[$c_obj->getCountry()]; Debug::text('Found Base Currency For Country: ' . $c_obj->getCountry() . ' Currency: ' . $base_currency, __FILE__, __LINE__, __METHOD__, 9); } else { Debug::text('DID NOT Find Base Currency For Country: ' . $c_obj->getCountry() . ' Using default USD.', __FILE__, __LINE__, __METHOD__, 9); $base_currency = 'USD'; } $cf->setCompany($c_obj->getId()); $cf->setStatus(10); $cf->setName($base_currency); $cf->setISOCode($base_currency); $cf->setConversionRate('1.000000000'); $cf->setAutoUpdate(FALSE); $cf->setBase(TRUE); $cf->setDefault(TRUE); if ($cf->isValid()) { $base_currency_id = $cf->Save(); Debug::text('Base Currency ID: ' . $base_currency_id, __FILE__, __LINE__, __METHOD__, 10); //Set Employee Hire Defaults. $udlf = new UserDefaultListFactory(); $udlf->getByCompanyId($c_obj->getId()); if ($udlf->getRecordCount() > 0) { $ud_obj = $udlf->getCurrent(); $ud_obj->setCurrency($base_currency_id); $ud_obj->setLanguage('en'); if ($ud_obj->isValid()) { $ud_obj->Save(); } } unset($udlf, $ud_obj); if (is_numeric($base_currency_id)) { $ulf = new UserListFactory(); $ulf->getByCompanyId($c_obj->getId()); if ($ulf->getRecordCount() > 0) { foreach ($ulf as $u_obj) { $user_id = $u_obj->getID(); Debug::text('Setting Base Currency For User: '******' ID: ' . $user_id, __FILE__, __LINE__, __METHOD__, 10); $u_obj->setCurrency($base_currency_id); if ($u_obj->isValid()) { if ($u_obj->Save() == TRUE) { //Set User Default Language $uplf = new UserPreferenceListFactory(); $uplf->getByUserIDAndCompanyID($user_id, $c_obj->getId()); if ($uplf->getRecordCount() > 0) { $up_obj = $uplf->getCurrent(); $up_obj->setLanguage('en'); //Englist if ($up_obj->isValid()) { $up_obj->Save(); } } unset($uplf, $up_obj); Debug::text(' Setting Base Currency for Pay Stubs, User ID: ' . $user_id, __FILE__, __LINE__, __METHOD__, 10); //Change all pay stubs for this user to the base currency. //Do this in a single query for speed purposes. $ph = array('currency_id' => $base_currency_id, 'currency_rate' => '1.000000000', 'user_id' => $user_id); $query = 'update pay_stub set currency_id = ?, currency_rate = ? where user_id = ?'; Debug::text(' Pay Stub Currency Query: ' . $query, __FILE__, __LINE__, __METHOD__, 10); $u_obj->db->Execute($query, $ph); /* $pslf = new PayStubListFactory(); $pslf->getByUserIdAndCompanyId( $user_id, $c_obj->getId() ); if ( $pslf->getRecordCount() > 0 ) { foreach( $pslf as $ps_obj ) { //Debug::text(' Setting Base Currency for Pay Stub ID: '. $ps_obj->getId(), __FILE__, __LINE__, __METHOD__,10); $ps_obj->setCurrency( $base_currency_id ); if ( $ps_obj->isValid() ) { $ps_obj->setEnableLinkedAccruals( FALSE ); $ps_obj->setEnableCalcYTD( FALSE ); $ps_obj->setEnableProcessEntries( FALSE ); $ps_obj->Save(); } unset($ps_obj); } } unset($pslf); */ } else { Debug::text('Failed saving user ID: ' . $user_id, __FILE__, __LINE__, __METHOD__, 10); } } else { Debug::text('Failed saving user ID: ' . $user_id, __FILE__, __LINE__, __METHOD__, 10); } unset($u_obj, $user_id); } } unset($ulf); } } unset($cf); } } else { Debug::text('Company is not active! ' . $c_obj->getId(), __FILE__, __LINE__, __METHOD__, 10); } unset($c_obj, $base_currency, $base_currency_id, $crlf); } } //$clf->FailTransaction(); $clf->CommitTransaction(); } //Add currency updating to cron. $maint_base_path = Environment::getBasePath() . DIRECTORY_SEPARATOR . 'maint' . DIRECTORY_SEPARATOR; if (PHP_OS == 'WINNT') { $cron_job_base_command = 'php-win.exe ' . $maint_base_path; } else { $cron_job_base_command = 'php ' . $maint_base_path; } Debug::text('Cron Job Base Command: ' . $cron_job_base_command, __FILE__, __LINE__, __METHOD__, 9); $cjf = new CronJobFactory(); $cjf->setName('UpdateCurrencyRates'); $cjf->setMinute(45); $cjf->setHour(1); $cjf->setDayOfMonth('*'); $cjf->setMonth('*'); $cjf->setDayOfWeek('*'); $cjf->setCommand($cron_job_base_command . 'UpdateCurrencyRates.php'); $cjf->Save(); return TRUE; }
function convertCurrency($src_currency_id, $dst_currency_id, $amount) { return CurrencyFactory::convertCurrency($src_currency_id, $dst_currency_id, $amount); }
/** * @param Money|string|int|float $amount * @param Currency $currency * @param CurrencyConversionServiceInterface $currencyConversionService */ public function __construct($amount = '0', Currency $currency = null, CurrencyConversionServiceInterface $currencyConversionService = null) { $this->amount = Utils::toStringAmount($amount); $this->currency = $currency === null ? CurrencyFactory::makeDefault() : $currency; $this->currencyConversionService = $currencyConversionService === null ? CurrencyConversionServiceProvider::getCurrencyConversionService() : $currencyConversionService; }
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (isset($filter_data['include_user_id'])) { $filter_data['user_id'] = $filter_data['include_user_id']; } if (isset($filter_data['exclude_user_id'])) { $filter_data['exclude_id'] = $filter_data['exclude_user_id']; } if (isset($filter_data['qualification_group_id'])) { $filter_data['group_id'] = $filter_data['qualification_group_id']; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } $additional_order_fields = array('uf.first_name', 'uf.last_name', 'qf.name', 'ownership_id', 'cf.name', 'qgf.name', 'default_branch', 'default_department', 'user_group', 'title'); $sort_column_aliases = array('ownership' => 'ownership_id'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('qf.name' => 'asc', 'qgf.name' => 'asc'); $strict = FALSE; } else { if (isset($order['first_name'])) { $order['uf.first_name'] = $order['first_name']; unset($order['first_name']); } if (isset($order['last_name'])) { $order['uf.last_name'] = $order['last_name']; unset($order['last_name']); } if (isset($order['qualification'])) { $order['qf.name'] = $order['qualification']; unset($order['qualification']); } if (isset($order['currency'])) { $order['cf.name'] = $order['currency']; unset($order['currency']); } if (isset($order['group'])) { $order['qgf.name'] = $order['group']; unset($order['group']); } $strict = TRUE; } Debug::Arr($order, 'Order Data:', __FILE__, __LINE__, __METHOD__, 10); Debug::Arr($filter_data, 'Filter Data:', __FILE__, __LINE__, __METHOD__, 10); $uf = new UserFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $qf = new QualificationFactory(); $cf = new CurrencyFactory(); $usf = new UserSkillFactory(); $ulf = new UserLanguageFactory(); $qgf = new QualificationGroupFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, uf.first_name as first_name, uf.last_name as last_name, qf.name as qualification, qgf.name as "group", bf.id as default_branch_id, bf.name as default_branch, df.id as default_department_id, df.name as default_department, ugf.id as user_group_id, ugf.name as user_group, utf.id as user_title_id, utf.name as title, cf.name as currency, y.first_name as created_by_first_name, y.middle_name as created_by_middle_name, y.last_name as created_by_last_name, z.first_name as updated_by_first_name, z.middle_name as updated_by_middle_name, z.last_name as updated_by_last_name from ' . $this->getTable() . ' as a LEFT JOIN ' . $uf->getTable() . ' as uf ON ( a.user_id = uf.id AND uf.deleted = 0) LEFT JOIN ' . $usf->getTable() . ' as usf ON ( a.qualification_id = usf.qualification_id AND usf.deleted = 0) LEFT JOIN ' . $ulf->getTable() . ' as ulf ON ( a.qualification_id = ulf.qualification_id AND ulf.deleted =0 ) LEFT JOIN ' . $cf->getTable() . ' as cf ON ( a.currency_id = cf.id AND cf.deleted = 0 ) LEFT JOIN ' . $qf->getTable() . ' as qf ON ( a.qualification_id = qf.id AND qf.deleted = 0 ) LEFT JOIN ' . $bf->getTable() . ' as bf ON ( uf.default_branch_id = bf.id AND bf.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as df ON ( uf.default_department_id = df.id AND df.deleted = 0) LEFT JOIN ' . $ugf->getTable() . ' as ugf ON ( uf.group_id = ugf.id AND ugf.deleted = 0 ) LEFT JOIN ' . $utf->getTable() . ' as utf ON ( uf.title_id = utf.id AND utf.deleted = 0 ) LEFT JOIN ' . $qgf->getTable() . ' as qgf ON ( qf.group_id = qgf.id AND qgf.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 ) where qf.company_id = ?'; $query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['user_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL; if (isset($filter_data['ownership']) and trim($filter_data['ownership']) != '' and !isset($filter_data['ownership_id'])) { $filter_data['ownership_id'] = Option::getByFuzzyValue($filter_data['ownership'], $this->getOptions('ownership')); } $query .= isset($filter_data['qualification_id']) ? $this->getWhereClauseSQL('a.qualification_id', $filter_data['qualification_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['qualification']) ? $this->getWhereClauseSQL('qf.name', $filter_data['qualification'], 'text', $ph) : NULL; $query .= isset($filter_data['proficiency_id']) ? $this->getWhereClauseSQL('usf.proficiency_id', $filter_data['proficiency_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['fluency_id']) ? $this->getWhereClauseSQL('ulf.fluency_id', $filter_data['fluency_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['competency_id']) ? $this->getWhereClauseSQL('ulf.competency_id', $filter_data['competency_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['ownership_id']) ? $this->getWhereClauseSQL('a.ownership_id', $filter_data['ownership_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['currency_id']) ? $this->getWhereClauseSQL('a.currency_id', $filter_data['currency_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['currency']) ? $this->getWhereClauseSQL('cf.name', $filter_data['currency'], 'text', $ph) : NULL; $query .= isset($filter_data['group_id']) ? $this->getWhereClauseSQL('qf.group_id', $filter_data['group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['group']) ? $this->getWhereClauseSQL('qgf.name', $filter_data['group'], 'text', $ph) : NULL; $query .= isset($filter_data['qualification_type_id']) ? $this->getWhereClauseSQL('qf.type_id', $filter_data['qualification_type_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('uf.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('uf.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['tag']) ? $this->getWhereClauseSQL('a.id', array('company_id' => $company_id, 'object_type_id' => 255, 'tag' => $filter_data['tag']), 'tag', $ph) : NULL; $query .= isset($filter_data['renewal_date']) ? $this->getWhereClauseSQL('a.renewal_date', $filter_data['renewal_date'], 'date_range', $ph) : NULL; $query .= isset($filter_data['start_date']) ? $this->getWhereClauseSQL('a.start_date', $filter_data['start_date'], 'date_range', $ph) : NULL; if (isset($filter_data['membership_renewal_start_date']) and trim($filter_data['membership_renewal_start_date']) != '' and is_numeric($filter_data['membership_renewal_start_date'])) { $ph[] = $filter_data['membership_renewal_start_date']; $query .= ' AND a.renewal_date >= ?'; } if (isset($filter_data['membership_renewal_end_date']) and trim($filter_data['membership_renewal_end_date']) != '') { $ph[] = $filter_data['membership_renewal_end_date']; $query .= ' AND a.renewal_date <= ?'; } if (isset($filter_data['created_date']) and trim($filter_data['created_date']) != '') { $date_filter = $this->getDateRangeSQL($filter_data['created_date'], 'a.created_date'); if ($date_filter != FALSE) { $query .= ' AND ' . $date_filter; } unset($date_filter); } if (isset($filter_data['updated_date']) and trim($filter_data['updated_date']) != '') { $date_filter = $this->getDateRangeSQL($filter_data['updated_date'], 'a.updated_date'); if ($date_filter != FALSE) { $query .= ' AND ' . $date_filter; } unset($date_filter); } $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } if (isset($filter_data['title_id'])) { $filter_data['user_title_id'] = $filter_data['title_id']; } if (isset($filter_data['group_id'])) { $filter_data['user_group_id'] = $filter_data['group_id']; } $additional_order_fields = array('user_status_id', 'last_name', 'first_name', 'default_branch', 'default_department', 'user_group', 'title', 'country', 'province', 'currency'); $sort_column_aliases = array('user_status' => 'user_status_id', 'status' => 'status_id'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('a.transaction_date' => 'desc', 'b.last_name' => 'asc'); $strict = FALSE; } else { if (isset($order['transaction_date'])) { $order['last_name'] = 'asc'; } else { $order['transaction_date'] = 'desc'; } $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $uf = new UserFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $cf = new CurrencyFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, b.first_name as first_name, b.last_name as last_name, b.status_id as user_status_id, b.city as city, b.province as province, b.country as country, b.default_branch_id as default_branch_id, bf.name as default_branch, b.default_department_id as default_department_id, df.name as default_department, b.group_id as group_id, ugf.name as user_group, b.title_id as title_id, utf.name as title, cf.name as currency, y.first_name as created_by_first_name, y.middle_name as created_by_middle_name, y.last_name as created_by_last_name, z.first_name as updated_by_first_name, z.middle_name as updated_by_middle_name, z.last_name as updated_by_last_name from ' . $this->getTable() . ' as a LEFT JOIN ' . $uf->getTable() . ' as b ON ( a.user_id = b.id AND b.deleted = 0 ) LEFT JOIN ' . $bf->getTable() . ' as bf ON ( b.default_branch_id = bf.id AND bf.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as df ON ( b.default_department_id = df.id AND df.deleted = 0) LEFT JOIN ' . $ugf->getTable() . ' as ugf ON ( b.group_id = ugf.id AND ugf.deleted = 0 ) LEFT JOIN ' . $utf->getTable() . ' as utf ON ( b.title_id = utf.id AND utf.deleted = 0 ) LEFT JOIN ' . $cf->getTable() . ' as cf ON ( a.currency_id = cf.id AND cf.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 ) where b.company_id = ? '; $query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('b.id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('b.id', $filter_data['user_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['include_user_id']) ? $this->getWhereClauseSQL('b.id', $filter_data['include_user_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['exclude_user_id']) ? $this->getWhereClauseSQL('b.id', $filter_data['exclude_user_id'], 'not_numeric_list', $ph) : NULL; $query .= isset($filter_data['user_status_id']) ? $this->getWhereClauseSQL('b.status_id', $filter_data['user_status_id'], 'numeric_list', $ph) : NULL; if (isset($filter_data['include_subgroups']) and (bool) $filter_data['include_subgroups'] == TRUE) { $uglf = new UserGroupListFactory(); $filter_data['user_group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['user_group_id'], TRUE); } $query .= isset($filter_data['user_group_id']) ? $this->getWhereClauseSQL('b.group_id', $filter_data['user_group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('b.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('b.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['user_title_id']) ? $this->getWhereClauseSQL('b.title_id', $filter_data['user_title_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['sex_id']) ? $this->getWhereClauseSQL('b.sex_id', $filter_data['sex_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['currency_id']) ? $this->getWhereClauseSQL('b.currency_id', $filter_data['currency_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['pay_period_id']) ? $this->getWhereClauseSQL('a.pay_period_id', $filter_data['pay_period_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['country']) ? $this->getWhereClauseSQL('b.country', $filter_data['country'], 'upper_text_list', $ph) : NULL; $query .= isset($filter_data['province']) ? $this->getWhereClauseSQL('b.province', $filter_data['province'], 'upper_text_list', $ph) : NULL; $query .= isset($filter_data['city']) ? $this->getWhereClauseSQL('b.city', $filter_data['city'], 'text', $ph) : NULL; //Pay Stub Status. $query .= isset($filter_data['status_id']) ? $this->getWhereClauseSQL('a.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL; if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') { $ph[] = $this->db->BindTimeStamp($filter_data['start_date']); $query .= ' AND a.transaction_date >= ?'; } if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') { $ph[] = $this->db->BindTimeStamp($filter_data['end_date']); $query .= ' AND a.transaction_date <= ?'; } $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); //Debug::Arr($ph, 'Query: '. $query, __FILE__, __LINE__, __METHOD__,10); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function getAPILastWageSearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } if (!isset($filter_data['effective_date'])) { $filter_data['effective_date'] = TTDate::getTime(); } if (isset($filter_data['include_user_id'])) { $filter_data['user_id'] = $filter_data['include_user_id']; } if (isset($filter_data['exclude_user_id'])) { $filter_data['exclude_id'] = $filter_data['exclude_user_id']; } $additional_order_fields = array('wage_group'); if ($order == NULL) { $order = array('effective_date' => 'desc', 'wage_group_id' => 'asc', 'type_id' => 'asc'); $strict = FALSE; } else { //Always sort by last name,first name after other columns if (!isset($order['effective_date'])) { $order['effective_date'] = 'desc'; } $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $uf = new UserFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $cf = new CurrencyFactory(); $wgf = new WageGroupFactory(); $ph = array('effective_date' => $this->db->BindTimeStamp($filter_data['effective_date']), 'company_id' => $company_id); /* $query = ' select a.* from '. $this->getTable() .' as a, ( select z.user_id, max(effective_date) as effective_date from '. $this->getTable() .' as z where z.effective_date <= ? AND z.wage_group_id = 0 AND z.user_id in ('. $this->getListSQL( $user_id, $ph ) .') AND ( z.deleted = 0 ) GROUP BY z.user_id ) as b, '. $uf->getTable() .' as c WHERE a.user_id = b.user_id AND a.effective_date = b.effective_date AND a.user_id = c.id AND ( c.deleted = 0 AND a.deleted = 0) '; */ $query = ' select a.*, CASE WHEN a.wage_group_id = 0 THEN \'' . TTi18n::getText('-Default-') . '\' ELSE ab.name END as wage_group, b.first_name as first_name, b.last_name as last_name, b.country as country, b.province as province, c.id as default_branch_id, c.name as default_branch, d.id as default_department_id, d.name as default_department, e.id as group_id, e.name as user_group, f.id as title_id, f.name as title, g.id as currency_id, g.iso_code as iso_code, y.first_name as created_by_first_name, y.middle_name as created_by_middle_name, y.last_name as created_by_last_name, z.first_name as updated_by_first_name, z.middle_name as updated_by_middle_name, z.last_name as updated_by_last_name from ( select uwf.user_id as user_id, uwf.wage_group_id as wage_group_id, max(effective_date) as effective_date from ' . $this->getTable() . ' as uwf where uwf.effective_date <= ? AND uwf.deleted = 0 GROUP BY uwf.wage_group_id, uwf.user_id ) as uwf_b LEFT JOIN ' . $this->getTable() . ' as a ON ( a.user_id = uwf_b.user_id AND a.wage_group_id = uwf_b.wage_group_id AND a.effective_date = uwf_b.effective_date ) LEFT JOIN ' . $wgf->getTable() . ' as ab ON ( a.wage_group_id = ab.id AND ab.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as b ON ( a.user_id = b.id AND b.deleted = 0 ) LEFT JOIN ' . $bf->getTable() . ' as c ON ( b.default_branch_id = c.id AND c.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as d ON ( b.default_department_id = d.id AND d.deleted = 0) LEFT JOIN ' . $ugf->getTable() . ' as e ON ( b.group_id = e.id AND e.deleted = 0 ) LEFT JOIN ' . $utf->getTable() . ' as f ON ( b.title_id = f.id AND f.deleted = 0 ) LEFT JOIN ' . $cf->getTable() . ' as g ON ( b.currency_id = g.id AND g.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 ) where b.company_id = ? '; if (isset($filter_data['permission_children_ids']) and isset($filter_data['permission_children_ids'][0]) and !in_array(-1, (array) $filter_data['permission_children_ids'])) { $query .= ' AND a.user_id in (' . $this->getListSQL($filter_data['permission_children_ids'], $ph) . ') '; } if (isset($filter_data['id']) and isset($filter_data['id'][0]) and !in_array(-1, (array) $filter_data['id'])) { $query .= ' AND a.id in (' . $this->getListSQL($filter_data['id'], $ph) . ') '; } if (isset($filter_data['exclude_id']) and isset($filter_data['exclude_id'][0]) and !in_array(-1, (array) $filter_data['exclude_id'])) { $query .= ' AND a.id not in (' . $this->getListSQL($filter_data['exclude_id'], $ph) . ') '; } if (isset($filter_data['user_id']) and isset($filter_data['user_id'][0]) and !in_array(-1, (array) $filter_data['user_id'])) { $query .= ' AND a.user_id in (' . $this->getListSQL($filter_data['user_id'], $ph) . ') '; } if (isset($filter_data['type_id']) and isset($filter_data['type_id'][0]) and !in_array(-1, (array) $filter_data['type_id'])) { $query .= ' AND a.type_id in (' . $this->getListSQL($filter_data['type_id'], $ph) . ') '; } if (isset($filter_data['wage_group_id']) and isset($filter_data['wage_group_id'][0]) and !in_array(-1, (array) $filter_data['wage_group_id'])) { $query .= ' AND a.wage_group_id in (' . $this->getListSQL($filter_data['wage_group_id'], $ph) . ') '; } if (isset($filter_data['status_id']) and isset($filter_data['status_id'][0]) and !in_array(-1, (array) $filter_data['status_id'])) { $query .= ' AND b.status_id in (' . $this->getListSQL($filter_data['status_id'], $ph) . ') '; } if (isset($filter_data['group_id']) and isset($filter_data['group_id'][0]) and !in_array(-1, (array) $filter_data['group_id'])) { if (isset($filter_data['include_subgroups']) and (bool) $filter_data['include_subgroups'] == TRUE) { $uglf = new UserGroupListFactory(); $filter_data['group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['group_id'], TRUE); } $query .= ' AND b.group_id in (' . $this->getListSQL($filter_data['group_id'], $ph) . ') '; } if (isset($filter_data['default_branch_id']) and isset($filter_data['default_branch_id'][0]) and !in_array(-1, (array) $filter_data['default_branch_id'])) { $query .= ' AND b.default_branch_id in (' . $this->getListSQL($filter_data['default_branch_id'], $ph) . ') '; } if (isset($filter_data['default_department_id']) and isset($filter_data['default_department_id'][0]) and !in_array(-1, (array) $filter_data['default_department_id'])) { $query .= ' AND b.default_department_id in (' . $this->getListSQL($filter_data['default_department_id'], $ph) . ') '; } if (isset($filter_data['title_id']) and isset($filter_data['title_id'][0]) and !in_array(-1, (array) $filter_data['title_id'])) { $query .= ' AND b.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') '; } if (isset($filter_data['country']) and isset($filter_data['country'][0]) and !in_array(-1, (array) $filter_data['country'])) { $query .= ' AND b.country in (' . $this->getListSQL($filter_data['country'], $ph) . ') '; } if (isset($filter_data['province']) and isset($filter_data['province'][0]) and !in_array(-1, (array) $filter_data['province']) and !in_array('00', (array) $filter_data['province'])) { $query .= ' AND b.province in (' . $this->getListSQL($filter_data['province'], $ph) . ') '; } $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } //$additional_order_fields = array('b.name', 'c.name', 'd.name', 'e.name'); $additional_order_fields = array('default_branch', 'default_department', 'group', 'title', 'currency', 'permission_control', 'pay_period_schedule', 'policy_group'); $sort_column_aliases = array('type' => 'type_id', 'status' => 'status_id'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('status_id' => 'asc', 'last_name' => 'asc', 'first_name' => 'asc', 'middle_name' => 'asc'); $strict = FALSE; } else { //Do order by column conversions, because if we include these columns in the SQL //query, they contaminate the data array. //Always try to order by status first so INACTIVE employees go to the bottom. if (!isset($order['status_id'])) { $order = Misc::prependArray(array('status_id' => 'asc'), $order); } //Always sort by last name,first name after other columns if (!isset($order['last_name'])) { $order['last_name'] = 'asc'; } if (!isset($order['first_name'])) { $order['first_name'] = 'asc'; } $strict = TRUE; } Debug::Arr($order, 'Order Data:', __FILE__, __LINE__, __METHOD__, 10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $cf = new CurrencyFactory(); $pcf = new PermissionControlFactory(); $puf = new PermissionUserFactory(); $ppsuf = new PayPeriodScheduleUserFactory(); $ppsf = new PayPeriodScheduleFactory(); $pguf = new PolicyGroupUserFactory(); $pgf = new PolicyGroupFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, b.name as default_branch, c.name as default_department, d.name as group, e.name as title, f.name as currency, g.id as permission_control_id, g.name as permission_control, h.id as pay_period_schedule_id, h.name as pay_period_schedule, i.id as policy_group_id, i.name as policy_group, y.first_name as created_by_first_name, y.middle_name as created_by_middle_name, y.last_name as created_by_last_name, z.first_name as updated_by_first_name, z.middle_name as updated_by_middle_name, z.last_name as updated_by_last_name from ' . $this->getTable() . ' as a LEFT JOIN ' . $bf->getTable() . ' as b ON ( a.default_branch_id = b.id AND b.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as c ON ( a.default_department_id = c.id AND c.deleted = 0) LEFT JOIN ' . $ugf->getTable() . ' as d ON ( a.group_id = d.id AND d.deleted = 0 ) LEFT JOIN ' . $utf->getTable() . ' as e ON ( a.title_id = e.id AND e.deleted = 0 ) LEFT JOIN ' . $cf->getTable() . ' as f ON ( a.currency_id = f.id AND f.deleted = 0 ) LEFT JOIN ( SELECT g2.*,g1.user_id FROM ' . $puf->getTable() . ' as g1, ' . $pcf->getTable() . ' as g2 WHERE ( g1.permission_control_id = g2.id AND g2.deleted = 0) ) as g ON ( a.id = g.user_id ) LEFT JOIN ( SELECT h2.*, h1.user_id FROM ' . $ppsuf->getTable() . ' as h1, ' . $ppsf->getTable() . ' as h2 WHERE ( h1.pay_period_schedule_id = h2.id AND h2.deleted = 0) ) as h ON ( a.id = h.user_id ) LEFT JOIN ( SELECT i2.*, i1.user_id FROM ' . $pguf->getTable() . ' as i1, ' . $pgf->getTable() . ' as i2 WHERE ( i1.policy_group_id = i2.id AND i2.deleted = 0) ) as i ON ( a.id = i.user_id ) LEFT JOIN ' . $this->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 ) LEFT JOIN ' . $this->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 ) where a.company_id = ? '; if (isset($filter_data['permission_children_ids']) and isset($filter_data['permission_children_ids'][0]) and !in_array(-1, (array) $filter_data['permission_children_ids'])) { $query .= ' AND a.id in (' . $this->getListSQL($filter_data['permission_children_ids'], $ph) . ') '; } if (isset($filter_data['id']) and isset($filter_data['id'][0]) and !in_array(-1, (array) $filter_data['id'])) { $query .= ' AND a.id in (' . $this->getListSQL($filter_data['id'], $ph) . ') '; } if (isset($filter_data['exclude_id']) and isset($filter_data['exclude_id'][0]) and !in_array(-1, (array) $filter_data['exclude_id'])) { $query .= ' AND a.id not in (' . $this->getListSQL($filter_data['exclude_id'], $ph) . ') '; } if (isset($filter_data['status_id']) and isset($filter_data['status_id'][0]) and !in_array(-1, (array) $filter_data['status_id'])) { $query .= ' AND a.status_id in (' . $this->getListSQL($filter_data['status_id'], $ph) . ') '; } if (isset($filter_data['group_id']) and isset($filter_data['group_id'][0]) and !in_array(-1, (array) $filter_data['group_id'])) { if (isset($filter_data['include_subgroups']) and (bool) $filter_data['include_subgroups'] == TRUE) { $uglf = new UserGroupListFactory(); $filter_data['group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['group_id'], TRUE); } $query .= ' AND a.group_id in (' . $this->getListSQL($filter_data['group_id'], $ph) . ') '; } if (isset($filter_data['group']) and trim($filter_data['group']) != '') { $ph[] = strtolower(trim($filter_data['group'])); $query .= ' AND lower(d.name) LIKE ?'; } if (isset($filter_data['default_branch_id']) and isset($filter_data['default_branch_id'][0]) and !in_array(-1, (array) $filter_data['default_branch_id'])) { $query .= ' AND a.default_branch_id in (' . $this->getListSQL($filter_data['default_branch_id'], $ph) . ') '; } if (isset($filter_data['default_branch']) and trim($filter_data['default_branch']) != '') { $ph[] = strtolower(trim($filter_data['default_branch'])); $query .= ' AND lower(b.name) LIKE ?'; } if (isset($filter_data['default_department_id']) and isset($filter_data['default_department_id'][0]) and !in_array(-1, (array) $filter_data['default_department_id'])) { $query .= ' AND a.default_department_id in (' . $this->getListSQL($filter_data['default_department_id'], $ph) . ') '; } if (isset($filter_data['default_department']) and trim($filter_data['default_department']) != '') { $ph[] = strtolower(trim($filter_data['default_department'])); $query .= ' AND lower(c.name) LIKE ?'; } if (isset($filter_data['title_id']) and isset($filter_data['title_id'][0]) and !in_array(-1, (array) $filter_data['title_id'])) { $query .= ' AND a.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') '; } if (isset($filter_data['title']) and trim($filter_data['title']) != '') { $ph[] = strtolower(trim($filter_data['title'])); $query .= ' AND lower(e.name) LIKE ?'; } if (isset($filter_data['currency_id']) and isset($filter_data['currency_id'][0]) and !in_array(-1, (array) $filter_data['currency_id'])) { $query .= ' AND a.currency_id in (' . $this->getListSQL($filter_data['currency_id'], $ph) . ') '; } if (isset($filter_data['currency']) and trim($filter_data['currency']) != '') { $ph[] = strtolower(trim($filter_data['currency'])); $query .= ' AND lower(f.name) LIKE ?'; } if (isset($filter_data['permission_control_id']) and isset($filter_data['permission_control_id'][0]) and !in_array(-1, (array) $filter_data['permission_control_id'])) { $query .= ' AND g.permission_control_id in (' . $this->getListSQL($filter_data['permission_control_id'], $ph) . ') '; } if (isset($filter_data['permission_control']) and trim($filter_data['permission_control']) != '') { $ph[] = strtolower(trim($filter_data['permission_control'])); $query .= ' AND lower(g.name) LIKE ?'; } if (isset($filter_data['pay_period_schedule_id']) and isset($filter_data['pay_period_schedule_id'][0]) and !in_array(-1, (array) $filter_data['pay_period_schedule_id'])) { $query .= ' AND i.pay_period_schedule_id in (' . $this->getListSQL($filter_data['pay_period_schedule_id'], $ph) . ') '; } if (isset($filter_data['pay_period_schedule']) and trim($filter_data['pay_period_schedule']) != '') { $ph[] = strtolower(trim($filter_data['pay_period_schedule'])); $query .= ' AND lower(h.name) LIKE ?'; } if (isset($filter_data['policy_group_id']) and isset($filter_data['policy_group_id'][0]) and !in_array(-1, (array) $filter_data['policy_group_id'])) { $query .= ' AND k.policy_group_id in (' . $this->getListSQL($filter_data['policy_group_id'], $ph) . ') '; } if (isset($filter_data['policy_group']) and trim($filter_data['policy_group']) != '') { $ph[] = strtolower(trim($filter_data['policy_group'])); $query .= ' AND lower(i.name) LIKE ?'; } if (isset($filter_data['sex']) and trim($filter_data['sex']) != '' and !isset($filter_data['sex_id'])) { $filter_data['sex_id'] = Option::getByFuzzyValue($filter_data['sex'], $this->getOptions('sex')); } if (isset($filter_data['sex_id']) and isset($filter_data['sex_id'][0]) and !in_array(-1, (array) $filter_data['sex_id'])) { $query .= ' AND a.sex_id in (' . $this->getListSQL($filter_data['sex_id'], $ph) . ') '; } if (isset($filter_data['country']) and isset($filter_data['country'][0]) and !in_array(-1, (array) $filter_data['country'])) { $query .= ' AND a.country in (' . $this->getListSQL($filter_data['country'], $ph) . ') '; } if (isset($filter_data['province']) and isset($filter_data['province'][0]) and !in_array(-1, (array) $filter_data['province']) and !in_array('00', (array) $filter_data['province'])) { $query .= ' AND a.province in (' . $this->getListSQL($filter_data['province'], $ph) . ') '; } if (isset($filter_data['city']) and trim($filter_data['city']) != '') { $ph[] = strtolower(trim($filter_data['city'])); $query .= ' AND lower(a.city) LIKE ?'; } if (isset($filter_data['first_name']) and trim($filter_data['first_name']) != '') { $ph[] = strtolower(trim($filter_data['first_name'])); $query .= ' AND lower(a.first_name) LIKE ?'; } if (isset($filter_data['last_name']) and trim($filter_data['last_name']) != '') { $ph[] = strtolower(trim($filter_data['last_name'])); $query .= ' AND lower(a.last_name) LIKE ?'; } if (isset($filter_data['home_phone']) and trim($filter_data['home_phone']) != '') { $ph[] = trim($filter_data['home_phone']); $query .= ' AND a.home_phone LIKE ?'; } if (isset($filter_data['employee_number']) and trim($filter_data['employee_number']) != '') { $ph[] = trim($filter_data['employee_number']); $query .= ' AND a.employee_number LIKE ?'; } if (isset($filter_data['user_name']) and trim($filter_data['user_name']) != '') { $ph[] = strtolower(trim($filter_data['user_name'])); $query .= ' AND lower(a.user_name) LIKE ?'; } if (isset($filter_data['sin']) and trim($filter_data['sin']) != '') { $ph[] = trim($filter_data['sin']); $query .= ' AND a.sin LIKE ?'; } if (isset($filter_data['created_date']) and trim($filter_data['created_date']) != '') { $date_filter = $this->getDateRangeSQL($filter_data['created_date'], 'a.created_date'); if ($date_filter != FALSE) { $query .= ' AND ' . $date_filter; } unset($date_filter); } if (isset($filter_data['updated_date']) and trim($filter_data['updated_date']) != '') { $date_filter = $this->getDateRangeSQL($filter_data['updated_date'], 'a.updated_date'); if ($date_filter != FALSE) { $query .= ' AND ' . $date_filter; } unset($date_filter); } if (isset($filter_data['created_by']) and trim($filter_data['created_by']) != '') { $ph[] = $ph[] = strtolower(trim($filter_data['created_by'])); $query .= ' AND (lower(y.first_name) LIKE ? OR lower(y.last_name) LIKE ? ) '; } if (isset($filter_data['updated_by']) and trim($filter_data['updated_by']) != '') { $ph[] = $ph[] = strtolower(trim($filter_data['updated_by'])); $query .= ' AND (lower(z.first_name) LIKE ? OR lower(z.last_name) LIKE ? ) '; } $query .= ' AND ( a.deleted = 0 ) '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); Debug::Text('Query: ' . $query, __FILE__, __LINE__, __METHOD__, 10); if ($limit == NULL) { $this->rs = $this->db->Execute($query, $ph); } else { $this->rs = $this->db->PageExecute($query, $limit, $page, $ph); } return $this; }
function convertToUserCurrency($amount) { $user_currency_id = $this->getUserCurrency(); $country_currency_id = $this->getCountryPrimaryCurrencyID(); if ($user_currency_id !== FALSE and $country_currency_id !== FALSE) { $retval = CurrencyFactory::convertCurrency($this->getCountryPrimaryCurrencyID(), $this->getUserCurrency(), $amount); } else { $retval = $amount; } return $retval; }
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } $additional_order_fields = array('last_name'); if ($order == NULL) { $order = array('b.last_name' => 'asc'); $strict = FALSE; } else { //Always sort by last name,first name after other columns if (!isset($order['last_name'])) { $order['last_name'] = 'asc'; } $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $uf = new UserFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $cf = new CurrencyFactory(); $ph = array('company_id' => $company_id); //Since company bank accounts don't have user_ids set, we have to make the LEFT JOIN optional on deleted = 0. $query = ' select a.*, b.first_name as first_name, b.last_name as last_name, b.country as country, b.province as province, c.id as default_branch_id, c.name as default_branch, d.id as default_department_id, d.name as default_department, e.id as user_group_id, e.name as user_group, f.id as title_id, f.name as title, g.id as currency_id, g.iso_code as iso_code, y.first_name as created_by_first_name, y.middle_name as created_by_middle_name, y.last_name as created_by_last_name, z.first_name as updated_by_first_name, z.middle_name as updated_by_middle_name, z.last_name as updated_by_last_name from ' . $this->getTable() . ' as a LEFT JOIN ' . $uf->getTable() . ' as b ON ( a.user_id = b.id AND ( b.id IS NULL OR b.deleted = 0 ) ) LEFT JOIN ' . $bf->getTable() . ' as c ON ( b.default_branch_id = c.id AND ( c.id IS NULL OR c.deleted = 0 ) ) LEFT JOIN ' . $df->getTable() . ' as d ON ( b.default_department_id = d.id AND ( d.id IS NULL OR d.deleted = 0 ) ) LEFT JOIN ' . $ugf->getTable() . ' as e ON ( b.group_id = e.id AND ( e.id IS NULL OR e.deleted = 0 ) ) LEFT JOIN ' . $utf->getTable() . ' as f ON ( b.title_id = f.id AND ( f.id IS NULL OR f.deleted = 0 ) ) LEFT JOIN ' . $cf->getTable() . ' as g ON ( b.currency_id = g.id AND ( g.id IS NULL OR g.deleted = 0 ) ) LEFT JOIN ' . $uf->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 ) where a.company_id = ? '; if (isset($filter_data['permission_children_ids']) and isset($filter_data['permission_children_ids'][0]) and !in_array(-1, (array) $filter_data['permission_children_ids'])) { $query .= ' AND a.user_id in (' . $this->getListSQL($filter_data['permission_children_ids'], $ph) . ') '; } if (isset($filter_data['id']) and isset($filter_data['id'][0]) and !in_array(-1, (array) $filter_data['id'])) { $query .= ' AND a.id in (' . $this->getListSQL($filter_data['id'], $ph) . ') '; } if (isset($filter_data['exclude_id']) and isset($filter_data['exclude_id'][0]) and !in_array(-1, (array) $filter_data['exclude_id'])) { $query .= ' AND a.id not in (' . $this->getListSQL($filter_data['exclude_id'], $ph) . ') '; } if (isset($filter_data['user_id']) and isset($filter_data['user_id'][0]) and !in_array(-1, (array) $filter_data['user_id'])) { $query .= ' AND a.user_id in (' . $this->getListSQL($filter_data['user_id'], $ph) . ') '; } elseif (isset($filter_data['user_id']) and $filter_data['user_id'] == '') { $query .= ' AND ( a.user_id is NULL OR a.user_id = 0 )'; } else { $query .= ' AND ( a.user_id is NOT NULL AND a.user_id != 0 )'; } $query .= isset($filter_data['first_name']) ? $this->getWhereClauseSQL('b.first_name', $filter_data['first_name'], 'text_metaphone', $ph) : NULL; $query .= isset($filter_data['last_name']) ? $this->getWhereClauseSQL('b.last_name', $filter_data['last_name'], 'text_metaphone', $ph) : NULL; if (isset($filter_data['status_id']) and isset($filter_data['status_id'][0]) and !in_array(-1, (array) $filter_data['status_id'])) { $query .= ' AND b.status_id in (' . $this->getListSQL($filter_data['status_id'], $ph) . ') '; } if (isset($filter_data['group_id']) and isset($filter_data['group_id'][0]) and !in_array(-1, (array) $filter_data['group_id'])) { if (isset($filter_data['include_subgroups']) and (bool) $filter_data['include_subgroups'] == TRUE) { $uglf = new UserGroupListFactory(); $filter_data['group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['group_id'], TRUE); } $query .= ' AND b.group_id in (' . $this->getListSQL($filter_data['group_id'], $ph) . ') '; } if (isset($filter_data['default_branch_id']) and isset($filter_data['default_branch_id'][0]) and !in_array(-1, (array) $filter_data['default_branch_id'])) { $query .= ' AND b.default_branch_id in (' . $this->getListSQL($filter_data['default_branch_id'], $ph) . ') '; } if (isset($filter_data['default_department_id']) and isset($filter_data['default_department_id'][0]) and !in_array(-1, (array) $filter_data['default_department_id'])) { $query .= ' AND b.default_department_id in (' . $this->getListSQL($filter_data['default_department_id'], $ph) . ') '; } if (isset($filter_data['title_id']) and isset($filter_data['title_id'][0]) and !in_array(-1, (array) $filter_data['title_id'])) { $query .= ' AND b.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') '; } if (isset($filter_data['country']) and isset($filter_data['country'][0]) and !in_array(-1, (array) $filter_data['country'])) { $query .= ' AND b.country in (' . $this->getListSQL($filter_data['country'], $ph) . ') '; } if (isset($filter_data['province']) and isset($filter_data['province'][0]) and !in_array(-1, (array) $filter_data['province']) and !in_array('00', (array) $filter_data['province'])) { $query .= ' AND b.province in (' . $this->getListSQL($filter_data['province'], $ph) . ') '; } $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); //Debug::Arr($ph,'Query: '. $query, __FILE__, __LINE__, __METHOD__,10); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }