/** * @author Okan CIRAN * @ connection_log tablosuna yeni bir kayıt oluşturur. !! * @version v 1.0 10.03.2016 * @param type $params * @return array * @throws \PDOException */ public function insert($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectLogFactory'); $pdo->beginTransaction(); $userId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($userId)) { $userIdValue = $userId['resultSet'][0]['user_id']; $addSql = " op_user_id,"; $addSqlValue = " " . intval($userIdValue) . ", "; } $sql = "\n INSERT INTO connection_log(\n pk, \n type_id,\n log_datetime,\n url, \n path, \n ip, \n params,\n " . $addSql . " \n method\n \n )\n VALUES (\n :pk,\n :type_id,\n :log_datetime,\n :url, \n :path, \n :ip, \n :params,\n " . $addSqlValue . " \n :method \n ) "; $statement = $pdo->prepare($sql); $statement->bindValue(':pk', $params['pk'], \PDO::PARAM_STR); $statement->bindValue(':type_id', $params['type_id'], \PDO::PARAM_INT); $statement->bindValue(':log_datetime', $params['log_datetime'], \PDO::PARAM_STR); $statement->bindValue(':url', $params['url'], \PDO::PARAM_STR); $statement->bindValue(':path', $params['path'], \PDO::PARAM_STR); $statement->bindValue(':ip', $params['ip'], \PDO::PARAM_STR); $statement->bindValue(':params', $params['params'], \PDO::PARAM_STR); $statement->bindValue(':method', $params['method'], \PDO::PARAM_STR); echo debugPDO($sql, $params); $result = $statement->execute(); $insertID = $pdo->lastInsertId('connection_log_id_seq'); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "lastInsertId" => $insertID); } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ Quest için npk lı firmanın danısman tarafından onaylanmış kayıtların sayısını döndürür !! * @version v 1.0 24.06.2016 * @param array | null $args * @return array * @throws \PDOException */ public function fillUserProductsServicesNpkQuestRtc($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $getUserIdValue = NULL; $getUser = InfoUsers::getUserIdsForNetworkKey(array('network_key' => $params['network_key'])); if (\Utill\Dal\Helper::haveRecord($getUser)) { $getUserIdValue = $getUser['resultSet'][0]['user_id']; $sql = " \n SELECT \n COUNT(a.id) AS count\n FROM info_users_products_services a\n INNER JOIN info_users_detail iud ON iud.root_id = a.user_id AND iud.active = 0 AND iud.deleted = 0 AND iud.language_parent_id =0\n INNER JOIN sys_language l ON l.id = iud.language_id AND l.deleted =0 AND l.active =0 \n INNER JOIN info_users iu ON iu.id = a.user_id \n\t\t INNER JOIN sys_unspsc_codes suc ON suc.id = a.unspsc_codes_id AND suc.active = 0 AND suc.deleted = 0 AND suc.language_id =l.id AND suc.language_parent_id =0 \t\t \n\t\t WHERE \n a.user_id = " . intval($getUserIdValue) . " AND\n a.cons_allow_id =2 AND \n\t\t\ta.profile_public = 0 \n\t\t ORDER BY unspsc_name\n "; $statement = $pdo->prepare($sql); //echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'unpk'; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ sys_osb_person tablosundan parametre olarak gelen id kaydın aktifliğini * 0(aktif) ise 1 , 1 (pasif) ise 0 yapar. !! * @version v 1.0 29.08.2016 * @param type $params * @return array * @throws \PDOException */ public function makeActiveOrPassive($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; if (isset($params['id']) && $params['id'] != "") { $sql = " \n UPDATE sys_osb_person\n SET active = ( SELECT \n CASE active\n WHEN 0 THEN 1\n ELSE 0\n END activex\n FROM sys_osb_person\n WHERE id = " . intval($params['id']) . "\n ),\n op_user_id = " . intval($opUserIdValue) . "\n WHERE id = " . intval($params['id']); $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $update = $statement->execute(); $afterRows = $statement->rowCount(); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } } $xc = $this->makeActiveOrPassiveInfoUsers(array('id' => $params['id'], 'op_user_id' => $opUserIdValue)); if ($xc['errorInfo'][0] != "00000" && $xc['errorInfo'][1] != NULL && $xc['errorInfo'][2] != NULL) { throw new \PDOException($xc['errorInfo']); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $afterRows); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ firmanın sözel verilerinin danısman bilgisini döndürür !! * @version v 1.0 23.05.2016 * @param array | null $args * @return array * @throws \PDOException */ public function getFirmVerbalConsultant($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $getFirm = InfoFirmProfile::getCheckIsThisFirmRegisteredUser(array('cpk' => $params['cpk'], 'op_user_id' => $opUserIdValue)); if (\Utill\Dal\Helper::haveRecord($getFirm)) { $getFirmId = $getFirm['resultSet'][0]['firm_id']; $languageId = NULL; $languageIdValue = 647; if (isset($params['language_code']) && $params['language_code'] != "") { $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } } $sql = "\n SELECT DISTINCT \n a.id AS firm_id, \n ifv.consultant_id, \n iud.name, \n iud.surname,\n iud.auth_email, \n ifk.network_key,\n\t\t CASE COALESCE(NULLIF(TRIM(iud.picture), ''),'-') \n WHEN '-' THEN CONCAT(COALESCE(NULLIF(concat(sps.folder_road,'/'), '/'),''),sps.members_folder,'/' ,'image_not_found.png')\n ELSE CONCAT(COALESCE(NULLIF(concat(sps.folder_road,'/'), '/'),''),sps.members_folder,'/' ,TRIM(iud.picture)) END AS cons_picture \n FROM info_firm_profile a\n INNER JOIN sys_project_settings sps ON sps.op_project_id = 1 AND sps.active =0 AND sps.deleted =0 \n INNER JOIN info_firm_keys ifk ON ifk.firm_id = a.act_parent_id \n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0 \n LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND l.deleted =0 AND l.active =0 \n\t\tLEFT JOIN info_firm_profile ax ON (ax.id = a.id OR ax.language_parent_id = a.id) AND ax.language_id = lx.id AND ax.active =0 AND ax.deleted =0\n\t\tINNER JOIN info_firm_verbal ifv ON ifv.firm_id = ifk.firm_id AND ifv.deleted = 0 AND ifv.active =0 AND ifv.language_parent_id=0 \n\t\tLEFT JOIN info_firm_verbal ifvx ON (ifvx.id = ifv.id OR ifvx.language_parent_id = ifv.id) AND ifvx.deleted = 0 AND ifvx.active =0 AND ifvx.language_id = lx.id\n INNER JOIN info_users u ON u.id = ifv.consultant_id AND u.role_id in (1,2,6)\n INNER JOIN info_users_detail iud ON iud.root_id = u.id AND iud.cons_allow_id = 2 \n WHERE \n a.act_parent_id = " . intval($getFirmId) . " \n ORDER BY iud.name, iud.surname \n "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'cpk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { //$debugSQLParams = $statement->debugDumpParams(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ info_users_addresses tablosundan parametre olarak gelen id kaydın active alanını 1 yapar ve * yeni yeni kayıt oluşturarak deleted ve active = 1 olarak yeni kayıt yapar. ! * @version v 1.0 02.02.2016 * @param array | null $args * @return array * @throws \PDOException */ public function deletedActTemp($params = array()) { try { $pdo = $this->getServiceLocator()->get('pgConnectFactory'); $pdo->beginTransaction(); $userId = InfoUsers::getUserIdTemp(array('pktemp' => $params['pktemp'])); if (\Utill\Dal\Helper::haveRecord($userId)) { $userIdValue = $userId['resultSet'][0]['user_id']; $addSql = ""; $addSqlValue = ""; if (isset($params['act_parent_id'])) { $act_parent_id = intval($params['act_parent_id']); $addSql .= " act_parent_id, "; if ($act_parent_id == 0) { $act_parent_id = intval($params['id']); } $addSqlValue .= intval($act_parent_id) . ", "; } if (isset($params['operation_type_id'])) { $addSql .= " operation_type_id, "; $addSqlValue .= intval($params['operation_type_id']) . ", "; } $this->makePassive(array('id' => $params['id'])); $statementInsert = $pdo->prepare(" \n INSERT INTO info_users_addresses (\n user_id, \n active, \n deleted,\n op_user_id, \n " . $addSql . "\n \n language_code, \n language_id,\n address_type_id, \n address1, \n address2, \n postal_code, \n country_id, \n city_id, \n borough_id, \n city_name, \n description, \n description_eng\n profile_public,\n \n f_check, \n consultant_id,\n consultant_confirm_type_id, \n confirm_id, \n language_parent_id ,\n history_parent_id,\n consultant_id,\n consultant_confirm_type_id,\n confirm_id,\n act_parent_id\n ) \n \n SELECT\n user_id,\n 1 AS active, \n 1 AS deleted, \n " . intval($userIdValue) . " AS op_user_id, \n " . $addSqlValue . " \n \n language_code, \n language_id,\n address_type_id, \n address1, \n address2, \n postal_code, \n country_id, \n city_id, \n borough_id, \n city_name, \n description, \n description_eng\n profile_public, \n\n f_check, \n consultant_id, \n consultant_confirm_type_id, \n confirm_id, \n language_parent_id ,\n history_parent_id,\n consultant_id,\n consultant_confirm_type_id,\n confirm_id,\n act_parent_id\n FROM info_users_addresses \n WHERE id =" . intval($params['id']) . " \n )"); $insertAct = $statementInsert->execute(); $affectedRows = $statementInsert->rowCount(); $errorInfo = $statementInsert->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows); } else { $errorInfo = '23502'; /// 23502 not_null_violation $errorInfoColumn = 'pk / op_user_id'; $pdo->rollback(); return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ info_firm_profile tablosuna yeni bir kayıt oluşturur. !! * @version v 1.0 06.01.2016 * @param array | null $args * @return array * @throws \PDOException */ public function insertTemp($params = array()) { try { $pdo = $this->getServiceLocator()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserIdTemp(array('pktemp' => $params['pktemp'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $kontrol = $this->haveRecords($params); if (!\Utill\Dal\Helper::haveRecord($kontrol)) { $addSql = " op_user_id, "; $addSqlValue = " " . $opUserIdValue . ","; $addSql .= " operation_type_id, "; $addSqlValue .= " 1,"; $addSql .= " owner_user_id, "; $addSqlValue .= " " . $opUserIdValue . ","; $getConsultant = SysOsbConsultants::getConsultantIdForCompany(array('category_id' => 1)); if (\Utill\Dal\Helper::haveRecord($getConsultant)) { $ConsultantId = $getConsultant['resultSet'][0]['consultant_id']; } else { $ConsultantId = 1001; } $addSql .= " consultant_id, "; $addSqlValue .= " " . intval($ConsultantId) . ","; if (isset($params['foundation_year']) && $params['foundation_year'] != "") { $foundationYear = $params['foundation_year']; $addSql .= " foundation_year, "; $addSqlValue .= " '" . $foundationYear . "',"; } $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } else { $languageIdValue = 647; } $addSql .= " language_id, "; $addSqlValue .= " " . intval($languageIdValue) . ","; $sql = " \n INSERT INTO info_firm_profile(\n profile_public, \n country_id, \n firm_name, \n web_address, \n tax_office, \n tax_no, \n sgk_sicil_no, \n ownership_status_id, \n language_code, \n " . $addSql . " \n firm_name_short,\n act_parent_id, \n description,\n description_eng,\n duns_number\n )\n VALUES (\n " . intval($params['profile_public']) . ", \n " . intval($params['country_id']) . ", \n :firm_name, \n :web_address, \n :tax_office, \n :tax_no, \n :sgk_sicil_no, \n " . intval($params['ownership_status_id']) . ", \n :language_code, \n " . $addSqlValue . " \n :firm_name_short,\n (SELECT last_value FROM info_firm_profile_id_seq), \n :description,\n :description_eng,\n :duns_number\n ) "; $statementInsert = $pdo->prepare($sql); $statementInsert->bindValue(':firm_name', $params['firm_name'], \PDO::PARAM_STR); $statementInsert->bindValue(':web_address', $params['web_address'], \PDO::PARAM_STR); $statementInsert->bindValue(':tax_office', $params['tax_office'], \PDO::PARAM_STR); $statementInsert->bindValue(':tax_no', $params['tax_no'], \PDO::PARAM_STR); $statementInsert->bindValue(':sgk_sicil_no', $params['sgk_sicil_no'], \PDO::PARAM_STR); $statementInsert->bindValue(':language_code', $params['language_code'], \PDO::PARAM_STR); $statementInsert->bindValue(':firm_name_short', $params['firm_name_short'], \PDO::PARAM_STR); $statementInsert->bindValue(':description', $params['description'], \PDO::PARAM_STR); $statementInsert->bindValue(':description_eng', $params['description_eng'], \PDO::PARAM_STR); $statementInsert->bindValue(':duns_number', $params['duns_number'], \PDO::PARAM_STR); $result = $statementInsert->execute(); $insertID = $pdo->lastInsertId('info_firm_profile_id_seq'); $errorInfo = $statementInsert->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "lastInsertId" => $insertID); } else { // 23505 unique_violation $errorInfo = '23505'; $errorInfoColumn = 'firm_name'; $pdo->rollback(); return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ npk lı firmanın danısman tarafından onaylanmış kayıtların sayısını döndürür !! * @version v 1.0 30.05.2016 * @param array | null $args * @return array * @throws \PDOException */ public function fillFirmSectorNpkRtc($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $userId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($userId)) { // $opUserIdValue = $userId ['resultSet'][0]['user_id']; $firmIdValue = NULL; $getFirm = InfoFirmProfile::getFirmIdsForNetworkKey(array('network_key' => $params['network_key'])); if (\Utill\Dal\Helper::haveRecord($getFirm)) { $firmIdValue = $getFirm['resultSet'][0]['firm_id']; $sql = " \n SELECT \n COUNT(a.id) AS count \n FROM info_firm_sectoral a\n INNER JOIN info_users u ON u.id = a.op_user_id\n INNER JOIN info_firm_profile fp ON fp.act_parent_id = a.firm_id AND fp.cons_allow_id=2 AND fp.language_parent_id =0\n INNER JOIN sys_language l ON l.id = fp.language_id AND l.deleted =0 AND l.active =0 \n INNER JOIN info_firm_keys ifk ON fp.act_parent_id = ifk.firm_id \n\t\t INNER JOIN sys_sectors ss ON ss.id = a.sector_id AND ss.deleted =0 AND ss.active =0 AND ss.language_parent_id =0 \n\t\t WHERE \n a.firm_id = " . intval($firmIdValue) . " AND\n a.cons_allow_id =2 AND\n\t\t\ta.profile_public=0\t \t \n "; $statement = $pdo->prepare($sql); //echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'npk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } else { $errorInfo = '23502'; // 23502 user_id not_null_violation $errorInfoColumn = 'pk'; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ sys_countrys tablosuna yeni bir kayıt oluşturur. !! * @version v 1.0 08.12.2015 * @param type $params * @return array * @throws \PDOException */ public function insert($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $kontrol = $this->haveRecords($params); if (!\Utill\Dal\Helper::haveRecord($kontrol)) { $languageId = NULL; $languageIdValue = 647; if (isset($params['language_code']) && $params['language_code'] != "") { $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } } $statement = $pdo->prepare("\n INSERT INTO sys_countrys(\n name, \n name_eng, \n language_id, \n op_user_id, \n flag_icon_road, \n country_code2,\n country_code3, \n priority)\n VALUES (\n :name,\n :name_eng, \n " . intval($languageIdValue) . ",\n " . intval($opUserIdValue) . ",\n :user_id,\n :flag_icon_road, \n :country_code2,\n :country_code3, \n :priority \n ) "); $statement->bindValue(':name', $params['name'], \PDO::PARAM_STR); $statement->bindValue(':name_eng', $params['name_eng'], \PDO::PARAM_STR); $statement->bindValue(':flag_icon_road', $params['flag_icon_road'], \PDO::PARAM_STR); $statement->bindValue(':country_code2', $params['country_code2'], \PDO::PARAM_STR); $statement->bindValue(':country_code3', $params['country_code3'], \PDO::PARAM_STR); $statement->bindValue(':priority', $params['priority'], \PDO::PARAM_INT); $result = $statement->execute(); $insertID = $pdo->lastInsertId('sys_countrys_id_seq'); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "lastInsertId" => $insertID); } else { $errorInfo = '23505'; $pdo->rollback(); $result = $kontrol; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => ''); } } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ Gridi doldurmak için consultant ların yaptığı operasyon kayıtlarını döndürür !! * @version v 1.0 08.02.2016 * @param array | null $args * @return array * @throws \PDOException */ public function getConsOpDetailedReviewForUser($params = array()) { if (isset($params['page']) && $params['page'] != "" && isset($params['rows']) && $params['rows'] != "") { $offset = (intval($params['page']) - 1) * intval($params['rows']); $limit = intval($params['rows']); } else { $limit = 10; $offset = 0; } $sortArr = array(); $orderArr = array(); if (isset($params['sort']) && $params['sort'] != "") { $sort = trim($params['sort']); $sortArr = explode(",", $sort); if (count($sortArr) === 1) { $sort = trim($params['sort']); } } else { $sort = "fp.s_date ASC, fp.c_date"; } if (isset($params['order']) && $params['order'] != "") { $order = trim($params['order']); $orderArr = explode(",", $order); if (count($orderArr) === 1) { $order = trim($params['order']); } } else { $order = "ASC"; } // sql query dynamic for filter operations $sorguStr = null; if (isset($params['filterRules'])) { $filterRules = trim($params['filterRules']); //print_r(json_decode($filterRules)); $jsonFilter = json_decode($filterRules, true); //print_r($jsonFilter[0]->field); $sorguExpression = null; foreach ($jsonFilter as $std) { if ($std['value'] != null) { switch (trim($std['field'])) { case 'operation_type_id': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= ' AND fpu.username' . $sorguExpression . ' '; break; case 'company_name': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= ' AND fp.firm_name' . $sorguExpression . ' '; break; case 's_date': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= 'AND TO_CHAR(fp.s_date, \'DD/MM/YYYY\')' . $sorguExpression . ' '; break; default: break; } } } } else { $sorguStr = null; $filterRules = ""; } $sorguStr = rtrim($sorguStr, "AND "); //if($sorguStr!="") $sorguStr = "WHERE ".$sorguStr; try { $pdo = $this->getServiceLocator()->get('pgConnectFactory'); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $sql = "\n SELECT \n fp.id AS id, \n fpu.s_date, \n fp.firm_name AS company_name, \n fpu.username AS username \n FROM sys_osb_consultants a \n LEFT JOIN info_firm_profile fp ON fp.consultant_id = a.user_id AND fp.deleted = 0 \n INNER JOIN info_users fpu ON fpu.id = fp.op_user_id \n WHERE fpu.auth_allow_id = 0 AND \n \n a.user_id =" . intval($opUserIdValue) . " \n " . $sorguStr . "\n ORDER BY " . $sort . " " . "" . $order . " " . "LIMIT " . $pdo->quote($limit) . " " . "OFFSET " . $pdo->quote($offset) . " "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { //$debugSQLParams = $statement->debugDumpParams(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * sys_unit_systems tablosuna parametre olarak gelen id deki kaydın bilgilerini günceller !! * @version v 1.0 05.03.2016 * @param type $params * @return array * @throws \PDOException */ public function update($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $kontrol = $this->haveRecords($params); if (!\Utill\Dal\Helper::haveRecord($kontrol)) { $languageId = NULL; $languageIdValue = 647; if (isset($params['language_code']) && $params['language_code'] != "") { $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } } $sql = "\n UPDATE sys_unit_systems\n SET \n system_eng = :system_eng, \n system = :system, \n op_user_id = :op_user_id \n WHERE id = " . intval($params['id']); $statement = $pdo->prepare($sql); $statement->bindValue(':system_eng', $params['system_eng'], \PDO::PARAM_STR); $statement->bindValue(':system', $params['system'], \PDO::PARAM_STR); $statement->bindValue(':op_user_id', $opUserIdValue, \PDO::PARAM_INT); // echo debugPDO($sql, $params); $update = $statement->execute(); $affectedRows = $statement->rowCount(); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows); } else { // 23505 unique_violation $errorInfo = '23505'; $errorInfoColumn = 'system'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * sys_language tablosuna parametre olarak gelen id deki kaydın bilgilerini günceller !! * @version v 1.0 07.12.2015 * @param type $params * @return array * @throws \PDOException */ public function update($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $kontrol = $this->haveRecords($params); if (!\Utill\Dal\Helper::haveRecord($kontrol)) { $pdo->beginTransaction(); $statement = $pdo->prepare("\n UPDATE sys_language\n SET \n country_name = :country_name, \n country_name_eng = :country_name_eng, \n country_id = :country_id, \n language_parent_id = :language_parent_id, \n icon_road = :icon_road, \n op_user_id = " . intval($opUserIdValue) . ",\n country_code3 = :country_code3, \n link = :link, \n language_code = :language_code, \n language_id = :language_id, \n parent_id = :parent_id, \n language_eng = :language_eng, \n language_main_code = :language_main_code, \n language = :language, \n priority = :priority\n WHERE id = :id"); //Bind our value to the parameter :id. $statement->bindValue(':id', $params['id'], \PDO::PARAM_INT); //Bind our :model parameter. $statement->bindValue(':country_name', $params['country_name'], \PDO::PARAM_STR); $statement->bindValue(':country_name_eng', $params['country_name_eng'], \PDO::PARAM_STR); $statement->bindValue(':country_id', $params['country_id'], \PDO::PARAM_INT); $statement->bindValue(':language_parent_id', $params['language_parent_id'], \PDO::PARAM_INT); $statement->bindValue(':icon_road', $params['icon_road'], \PDO::PARAM_STR); $statement->bindValue(':country_code3', $params['country_code3'], \PDO::PARAM_STR); $statement->bindValue(':link', $params['link'], \PDO::PARAM_STR); $statement->bindValue(':language_code', $params['language_code'], \PDO::PARAM_INT); $statement->bindValue(':language_id', $params['language_id'], \PDO::PARAM_INT); $statement->bindValue(':parent_id', $params['parent_id'], \PDO::PARAM_INT); $statement->bindValue(':language_eng', $params['language_eng'], \PDO::PARAM_STR); $statement->bindValue(':language_main_code', $params['language_main_code'], \PDO::PARAM_STR); $statement->bindValue(':language', $params['language'], \PDO::PARAM_STR); $statement->bindValue(':priority', $params['priority'], \PDO::PARAM_INT); //Execute our UPDATE statement. $update = $statement->execute(); $affectedRows = $statement->rowCount(); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows); } else { // 23505 unique_violation $errorInfo = '23505'; $errorInfoColumn = 'language_code'; $pdo->rollback(); // $result = $kontrol; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * delete olayında önce kaydın active özelliğini pasif e olarak değiştiriyoruz. * daha sonra deleted= 1 ve active = 1 olan kaydı oluşturuyor. * böylece tablo içerisinde loglama mekanizması için gerekli olan kayıt oluşuyor. * @version 10.08.2016 * @param type $id * @param type $params * @return array * @throws PDOException */ public function deletedAct($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $this->makePassive(array('id' => $params['id'])); $operationIdValue = 3; $sql = " \n INSERT INTO info_firm_consultants(\n firm_id,\n user_id, \n op_user_id, \n operation_type_id, \n act_parent_id,\n deleted\n )\n SELECT \n firm_id,\n user_id,\n " . intval($opUserIdValue) . " AS op_user_id, \n " . intval($operationIdValue) . " AS operation_type_id, \n act_parent_id,\n 1 \n FROM info_firm_consultants \n WHERE id = " . intval($params['id']) . " \n "; $statement_act_insert = $pdo->prepare($sql); // echo debugPDO($sql, $params); $insert_act_insert = $statement_act_insert->execute(); $affectedRows = $statement_act_insert->rowCount(); $insertID = $pdo->lastInsertId('info_firm_consultants_id_seq'); $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * sys_acl_action_rrp_restservices tablosuna parametre olarak gelen id deki kaydın bilgilerini günceller !! * @version v 1.0 16-08-2016 * @param type $params * @return array * @throws \PDOException */ public function update($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $kontrol = $this->haveRecords($params); if (!\Utill\Dal\Helper::haveRecord($kontrol)) { $sql = "\n UPDATE sys_acl_action_rrp_restservices\n SET \n rrp_id = " . intval($params['rrp_id']) . ",\n restservices_id = " . intval($params['restservices_id']) . ",\n description = '" . $params['description'] . "',\n op_user_id = " . intval($opUserIdValue) . "\n WHERE id = " . intval($params['id']); $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $update = $statement->execute(); $affectedRows = $statement->rowCount(); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows); } else { $errorInfo = '23505'; $errorInfoColumn = 'restservice'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * * @author Okan CIRAN * @ Danışmanın onay bekleyen firmalarının bilgilerini döndürür !! * @version v 1.0 05.02.2016 * @param array | null $args * @return array * @throws \PDOException */ public function getConsWaitingForConfirm($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $languageId = NULL; $languageIdValue = 647; if (isset($params['language_code']) && $params['language_code'] != "") { $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } } $sql = " \n SELECT \n id,\n operation_type_id,\n operation_name AS aciklama,\n operation_name,\n operation_name_eng,\t\n category_id,\n category,\n category_eng,\t \n table_name, \n table_column_id, \n membership_types_id,\n membership_types_name,\n membership_types_name_eng,\n sys_membership_periods_id, \t\t\t\n period_name,\t\t\t\n period_name_eng,\n preferred_language_id,\n preferred_language,\n language_id,\n language_name,\n op_user_id,\n op_user_name,\n cons_id,\n cons_name,\t\t\t\t\t\t \n op_cons_id,\n op_cons_name,\n cons_operation_type_id,\n cons_operation_name,\n cons_operation_name_eng,\n\t\t CASE\n WHEN sure_int > 50000 THEN CAST(SUBSTRING(sure FROM 1 FOR POSITION(' ' IN sure )-1 ) AS integer)\n ELSE 0 \n END AS sure,\n s_date,\n c_date,\n priority\n FROM ( \n SELECT \n a.id,\n a.operation_type_id,\n COALESCE(NULLIF(sotx.operation_name, ''), sot.operation_name_eng) AS operation_name,\n sot.operation_name_eng,\n sot.category_id,\n COALESCE(NULLIF(soccx.category, ''), socc.category_eng) AS category,\n socc.category_eng,\n sot.table_name,\n a.table_column_id,\n smt.id AS membership_types_id,\n COALESCE(NULLIF(smtx.mem_type, ''), smt.mem_type_eng) AS membership_types_name,\n smt.mem_type_eng AS membership_types_name_eng,\n a.sys_membership_periods_id,\n COALESCE(NULLIF(spx.period_name, ''), sp.period_name_eng) AS period_name,\n sp.period_name_eng,\n a.preferred_language_id,\n COALESCE(NULLIF(lpx.language, ''), lp.language_eng) AS preferred_language,\n COALESCE(NULLIF(lx.id, NULL), 385) AS language_id,\n COALESCE(NULLIF(lx.language, ''), l.language_eng) AS language_name,\n a.op_user_id,\n opuc.username AS op_user_name,\n a.cons_id,\n uc.username AS cons_name,\n a.op_cons_id,\n u.username AS op_cons_name,\n a.cons_operation_type_id,\n COALESCE(NULLIF(sotconsx.operation_name, ''), sotcons.operation_name_eng) AS cons_operation_name,\n sotcons.operation_name_eng AS cons_operation_name_eng,\n EXTRACT(EPOCH FROM age(a.s_date)) AS sure_int, \n CAST(CURRENT_TIMESTAMP - a.s_date AS VARCHAR(20)) AS sure,\n a.s_date,\n a.c_date,\n smt.priority\n FROM act_process_confirm a\n INNER JOIN sys_operation_types sot ON sot.base_id = a.operation_type_id AND sot.active =0 AND sot.deleted = 0 AND sot.language_parent_id =0 \n INNER JOIN sys_language l ON l.id = sot.language_id AND l.deleted =0 AND l.active = 0\n LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n INNER JOIN sys_language lp ON lp.id = a.preferred_language_id AND lp.deleted =0 AND lp.active = 0\n LEFT JOIN sys_language lpx ON (lpx.id = lp.id OR lpx.language_parent_id = lp.id) AND lpx.deleted =0 AND lpx.active =0\n INNER JOIN sys_osb_consultant_categories socc ON socc.id= sot.category_id AND socc.active =0 AND socc.deleted = 0 AND socc.language_parent_id =0 AND l.id = socc.language_id \n INNER JOIN info_users uc ON uc.id = a.cons_id \n INNER JOIN info_users opuc ON opuc.id = a.op_user_id \n LEFT JOIN info_users u ON u.id = a.op_cons_id \n LEFT JOIN sys_membership_periods smp ON smp.id = a.sys_membership_periods_id\n LEFT JOIN sys_membership_types smt ON smt.id = smp.mems_type_id AND smt.language_parent_id =0 AND l.id = smt.language_id\n LEFT JOIN sys_membership_types smtx ON (smtx.id = smt.id OR smtx.language_parent_id = smt.id) AND lx.id = smtx.language_id\n LEFT JOIN sys_operation_types sotx ON (sotx.id = sot.id OR sotx.language_parent_id = sot.id) AND sotx.deleted =0 AND sotx.active =0 AND lx.id = sotx.language_id\n LEFT JOIN sys_osb_consultant_categories soccx ON (soccx.id = socc.id OR soccx.language_parent_id = socc.id) AND soccx.deleted =0 AND soccx.active =0 AND lx.id = soccx.language_id\n LEFT JOIN sys_operation_types sotcons ON sotcons.base_id = a.cons_operation_type_id AND sotcons.active =0 AND sotcons.deleted = 0 AND sotcons.language_parent_id =0\n LEFT JOIN sys_operation_types sotconsx ON (sotconsx.id = sotcons.id OR sotconsx.language_parent_id = sotcons.id) AND sotconsx.deleted =0 AND sotconsx.active =0 AND lx.id = sotconsx.language_id\n LEFT JOIN sys_periods sp ON sp.id = smp.period_id AND sp.language_parent_id =0 AND l.id = sp.language_id\n LEFT JOIN sys_periods spx ON (spx.id = sp.id OR spx.language_parent_id = sp.id) AND spx.deleted =0 AND spx.active =0 AND lx.id = spx.language_id\n WHERE a.cons_id = " . intval($opUserIdValue) . "\n AND a.c_date IS NULL\n ) AS xtable\n ORDER BY priority, s_date, membership_types_name , sure desc \n \n LIMIT 6\n \n "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_CLASS); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } //return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); return json_encode($result); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * sys_certifications tablosuna parametre olarak gelen id deki kaydın bilgilerini günceller !! * @version v 1.0 29.03.2016 * @param type $params * @return array * @throws \PDOException */ public function update($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $languageId = NULL; $languageIdValue = 647; if (isset($params['language_code']) && $params['language_code'] != "") { $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } } $addSql = null; if (isset($params['priority']) && $params['priority'] != "") { $Priority = $params['priority']; $addSql = " priority = " . intval($Priority) . ","; } $sql = " \n UPDATE sys_navigation_left\n SET \n language_id = " . intval($languageIdValue) . ", \n certificate = :certificate , \n certificate_short = :certificate_short, \n description = :description, \n certificate_eng = :certificate_eng, \n certificate_short_eng = :certificate_short_eng, \n description_eng = :description_eng, \n logo = :logo,\n " . $addSql . "\n op_user_id = " . intval($opUserIdValue) . " \n WHERE id = :id"; $statement = $pdo->prepare($sql); $statement->bindValue(':id', $params['id'], \PDO::PARAM_INT); $statement->bindValue(':certificate', $params['certificate'], \PDO::PARAM_STR); $statement->bindValue(':certificate_short', $params['certificate_short'], \PDO::PARAM_STR); $statement->bindValue(':description', $params['description'], \PDO::PARAM_STR); $statement->bindValue(':certificate_eng', $params['certificate_eng'], \PDO::PARAM_STR); $statement->bindValue(':certificate_short_eng', $params['certificate_short_eng'], \PDO::PARAM_STR); $statement->bindValue(':description_eng', $params['description_eng'], \PDO::PARAM_STR); $statement->bindValue(':logo', $params['logo'], \PDO::PARAM_STR); // echo debugPDO($sql, $params); $update = $statement->execute(); $affectedRows = $statement->rowCount(); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ sys_acl_action_rrp tablosuna yeni bir kayıt oluşturur. !! * @version v 1.0 13.08.2016 * @param type $params * @return array * @throws \PDOException */ public function transferRolesActionPrivilege($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $sql = " \n INSERT INTO sys_acl_action_rrp(\n role_id, \n resource_id, \n privilege_id,\n op_user_id\n )\n VALUES (\n " . intval($params['role_id']) . ", \n " . intval($params['resource_id']) . ",\n " . intval($params['privilege_id']) . ",\n " . intval($opUserIdValue) . "\n )"; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $result = $statement->execute(); $insertID = $pdo->lastInsertId('sys_acl_action_rrp_id_seq'); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "lastInsertId" => $insertID); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ gtip li ürünü üreten firmaların sayısını döndürür !! * @version v 1.0 18.05.2016 * @param array | null $args * @return array * @throws \PDOException */ public function fillFirmProductsGtipRtc($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $userId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($userId)) { $addSql = NULL; if (isset($params['gtip_no_id']) && $params['gtip_no_id'] != "") { $addSql .= " AND a.gtip_no_id = " . intval($params['gtip_no_id']); } if (isset($params['gtip_key']) && $params['gtip_key'] != "") { $addSql .= " AND sgc.cnkey LIKE '%" . intval($params['gtip_key']) . "%'"; } if (isset($params['gtip']) && $params['gtip'] != "") { $addSql .= " AND LOWER(sgc.description) LIKE LOWER('%" . intval($params['gtip']) . "%')"; } if (isset($params['gtip_eng']) && $params['gtip_eng'] != "") { $addSql .= " AND LOWER(sgc.description_eng) LIKE LOWER('%" . intval($params['gtip_eng']) . "%')"; } if (isset($params['product_name']) && $params['product_name'] != "") { $addSql .= " AND LOWER(a.product_name) LIKE LOWER('%" . intval($params['product_name']) . "%')"; } $sql = " \n SELECT \n COUNT(a.id) as count\n FROM info_firm_products a \n INNER JOIN sys_project_settings sps ON sps.op_project_id = 1 AND sps.active =0 AND sps.deleted = 0\n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active = 0 \n INNER JOIN info_firm_profile fp ON fp.act_parent_id = a.firm_id AND fp.cons_allow_id = 2 AND fp.language_parent_id = 0 \n INNER JOIN info_firm_keys ifk ON fp.act_parent_id = ifk.firm_id \n\t\t INNER JOIN sys_gtip_codes sgc ON sgc.id = a.gtip_no_id AND sgc.active = 0 AND sgc.deleted = 0 AND sgc.language_id = l.id AND sgc.language_parent_id = 0 \t\t \n WHERE \n a.cons_allow_id =2 AND \n a.language_parent_id = 0 AND\n\t\t\ta.profile_public=0\n\t\t " . $addSql . "\n\t\t ORDER BY a.firm_id, product_name \n \n "; $statement = $pdo->prepare($sql); //echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 user_id not_null_violation $errorInfoColumn = 'pk'; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * * @author Okan CIRAN * @ Danışmanın onay bekleyen firmalarının bilgilerini döndürür !! * @version v 1.0 05.02.2016 * @param array | null $args * @return array * @throws \PDOException */ public function getConsWaitingForConfirm($params = array()) { try { $pdo = $this->getServiceLocator()->get('pgConnectFactory'); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $sql = " \n SELECT \n aciklama , \n CASE\n WHEN sure_int > 50000 THEN CAST(SUBSTRING(sure FROM 1 FOR POSITION(' ' IN sure )-1 ) AS integer)\n ELSE 0 \n END AS sure\n FROM (\n SELECT a.id,\n EXTRACT(EPOCH FROM age(a.s_date)) AS sure_int, \n\t\t\ta.firm_name AS aciklama, \n CAST(CURRENT_TIMESTAMP - a.s_date AS VARCHAR(20)) AS sure\n FROM info_firm_profile a \n INNER JOIN info_users u ON u.id = a.consultant_id \n INNER JOIN sys_operation_types op ON op.parent_id = 1 AND a.operation_type_id = op.id AND op.active = 0 AND op.deleted =0\n WHERE \n a.consultant_id = " . intval($opUserIdValue) . " \n ) AS asdasd\n ORDER BY sure DESC\n LIMIT 6\n \n "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_CLASS); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } //return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); return json_encode($result); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * delete olayında önce kaydın active özelliğini pasif e olarak değiştiriyoruz. * daha sonra deleted= 1 ve active = 1 olan kaydı oluşturuyor. * böylece tablo içerisinde loglama mekanizması için gerekli olan kayıt oluşuyor. * @version 09.05.2016 * @param type $id * @param type $params * @return array * @throws PDOException */ public function deletedAct($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $this->makePassive(array('id' => $params['id'])); $operationIdValue = -3; $operationId = SysOperationTypes::getTypeIdToGoOperationId(array('parent_id' => 3, 'main_group' => 3, 'sub_grup_id' => 29, 'type_id' => 3)); if (\Utill\Dal\Helper::haveRecord($operationId)) { $operationIdValue = $operationId['resultSet'][0]['id']; } $sql = " \n INSERT INTO info_firm_socialmedia(\n firm_id, \n profile_public, \n op_user_id, \n operation_type_id, \n consultant_id, \n sys_socialmedia_id, \n act_parent_id, \n firm_link,\n active,\n deleted\n )\n SELECT \n firm_id,\n profile_public, \n " . intval($opUserIdValue) . " AS op_user_id, \n " . intval($operationIdValue) . " AS operation_type_id, \n consultant_id, \n sys_socialmedia_id,\n act_parent_id, \n firm_link,\n 1,\n 1 \n FROM info_firm_socialmedia \n WHERE id = " . intval($params['id']) . " \n "; $statement_act_insert = $pdo->prepare($sql); // echo debugPDO($sql, $params); $insert_act_insert = $statement_act_insert->execute(); $affectedRows = $statement_act_insert->rowCount(); $insertID = $pdo->lastInsertId('info_firm_socialmedia_id_seq'); /* * ufak bir trik var. * işlem update oldugunda update işlemini yapan kişinin dil bilgisini kullanıcaz. * ancak delete işlemi oldugunda delete işlemini yapan user in dil bilgisini değil * silinen kaydı yapan kişinin dil bilgisini alıcaz. */ $consIdAndLanguageId = SysOperationTypes::getConsIdAndLanguageId(array('table_name' => 'info_firm_socialmedia', 'id' => $params['id'])); if (\Utill\Dal\Helper::haveRecord($consIdAndLanguageId)) { $ConsultantId = $consIdAndLanguageId['resultSet'][0]['consultant_id']; $languageIdValue = $consIdAndLanguageId['resultSet'][0]['language_id']; } $xjobs = ActProcessConfirm::insert(array('op_user_id' => intval($opUserIdValue), 'operation_type_id' => intval($operationIdValue), 'table_column_id' => intval($insertID), 'cons_id' => intval($ConsultantId), 'preferred_language_id' => intval($languageIdValue))); if ($xjobs['errorInfo'][0] != "00000" && $xjobs['errorInfo'][1] != NULL && $xjobs['errorInfo'][2] != NULL) { throw new \PDOException($xjobs['errorInfo']); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ Gridi doldurmak için info_firm_address tablosundan firmanın kayıtlarını döndürür !! * @version v 1.0 09.02.2016 * @param array | null $args * @return array * @throws \PDOException */ public function fillSingularFirmAddressRtc($params = array()) { try { $addSql = NULL; $sorguStr = null; if (isset($args['filterRules'])) { $filterRules = trim($args['filterRules']); $jsonFilter = json_decode($filterRules, true); $sorguExpression = null; foreach ($jsonFilter as $std) { if ($std['value'] != null) { switch (trim($std['field'])) { case 'firm_name': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND COALESCE(NULLIF(fpx.firm_name, ''), fp.firm_name_eng)" . $sorguExpression . ' '; break; case 'firm_name_eng': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND fp.firm_name_eng" . $sorguExpression . ' '; break; case 'firm_building_type': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND COALESCE(NULLIF(sd4x.description, ''), sd4.description_eng)" . $sorguExpression . ' '; break; case 'address': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND a.address" . $sorguExpression . ' '; break; case 'cons_allow': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND COALESCE(NULLIF(sd14x.description, ''), sd14.description_eng)" . $sorguExpression . ' '; break; case 'cons_allow': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND COALESCE(NULLIF(sd14x.description, ''), sd14.description_eng)" . $sorguExpression . ' '; break; case 'osb_name': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND osb.name" . $sorguExpression . ' '; break; case 'country_name': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND COALESCE(NULLIF(cox.name , ''), co.name_eng)" . $sorguExpression . ' '; break; case 'city_name': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND COALESCE(NULLIF(ctx.name , ''), ct.name_eng)" . $sorguExpression . ' '; break; case 'borough_name': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND COALESCE(NULLIF(box.name , ''), bo.name_eng)" . $sorguExpression . ' '; break; case 'operation_name': $sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' '; $sorguStr .= " AND COALESCE(NULLIF(opx.operation_name, ''), op.operation_name_eng)" . $sorguExpression . ' '; break; default: break; } } } } else { $sorguStr = null; $filterRules = ""; } $sorguStr = rtrim($sorguStr, "AND "); $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $languageId = NULL; $languageIdValue = 647; if (isset($params['language_code']) && $params['language_code'] != "") { $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } } $networkKeyValue = '-1'; if (isset($params['network_key']) && $params['network_key'] != "") { $networkKeyValue = $params['network_key']; } $sql = "\n SELECT \n count(a.id) AS COUNT \n FROM info_firm_address a \n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0\n LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n LEFT JOIN info_firm_address ax ON (ax.id = a.id OR ax.language_parent_id = a.id) AND ax.deleted = 0 AND ax.active = 0 AND ax.language_id = lx.id\n INNER JOIN info_users u ON u.id = a.op_user_id\n INNER JOIN info_firm_profile fp ON fp.act_parent_id = a.firm_id AND fp.deleted = 0 AND fp.active = 0 AND fp.language_parent_id =0 \n LEFT JOIN info_firm_profile fpx ON (fpx.act_parent_id = fp.act_parent_id OR fpx.language_parent_id= fp.act_parent_id) AND fpx.deleted = 0 AND fpx.active = 0 AND fpx.language_id =lx.id \n INNER JOIN info_firm_keys ifk ON fp.act_parent_id = ifk.firm_id\n\t\t INNER JOIN sys_operation_types op ON op.id = a.operation_type_id AND op.deleted =0 AND op.active =0 AND op.language_parent_id =0\n LEFT JOIN sys_operation_types opx ON (opx.id = a.operation_type_id OR opx.language_parent_id = a.operation_type_id) and opx.language_id =lx.id AND opx.deleted =0 AND opx.active =0\n\n\t\t INNER JOIN sys_specific_definitions sd4 ON sd4.main_group = 4 AND a.firm_building_type_id = sd4.first_group AND sd4.deleted =0 AND sd4.active =0 AND sd4.language_parent_id =0\n\t\t INNER JOIN sys_specific_definitions sd14 ON sd14.main_group = 14 AND a.cons_allow_id = sd14.first_group AND sd14.deleted =0 AND sd14.active =0 AND sd14.language_parent_id =0\n\t\t INNER JOIN sys_specific_definitions sd15 ON sd15.main_group = 15 AND sd15.first_group= a.deleted AND sd15.deleted =0 AND sd15.active =0 AND sd15.language_parent_id =0\n\t\t INNER JOIN sys_specific_definitions sd16 ON sd16.main_group = 16 AND sd16.first_group= a.active AND sd16.deleted = 0 AND sd16.active = 0 AND sd16.language_parent_id =0\n\t\t INNER JOIN sys_specific_definitions sd19 ON sd19.main_group = 19 AND sd19.first_group= a.profile_public AND sd19.deleted = 0 AND sd19.active = 0 AND sd19.language_parent_id =0\n\n\t\t LEFT JOIN sys_specific_definitions sd4x ON sd4x.language_id = lx.id AND (sd4x.id = sd4.id OR sd4x.language_parent_id = sd4.id) AND sd4x.deleted =0 AND sd4x.active =0\n LEFT JOIN sys_specific_definitions sd14x ON sd14x.language_id = lx.id AND (sd14x.id = sd14.id OR sd14x.language_parent_id = sd14.id) AND sd14x.deleted =0 AND sd14x.active =0\n LEFT JOIN sys_specific_definitions sd15x ON sd15x.language_id =lx.id AND (sd15x.id = sd15.id OR sd15x.language_parent_id = sd15.id) AND sd15x.deleted =0 AND sd15x.active =0 \n LEFT JOIN sys_specific_definitions sd16x ON sd16x.language_id = lx.id AND (sd16x.id = sd16.id OR sd16x.language_parent_id = sd16.id) AND sd16x.deleted = 0 AND sd16x.active = 0\n LEFT JOIN sys_specific_definitions sd19x ON sd19x.language_id = lx.id AND (sd19x.id = sd19.id OR sd19x.language_parent_id = sd19.id) AND sd19x.deleted = 0 AND sd19x.active = 0\n \n INNER JOIN sys_countrys co ON co.id = a.country_id AND co.deleted = 0 AND co.active = 0 AND co.language_id = a.language_id\n INNER JOIN sys_city ct ON ct.id = a.city_id AND ct.deleted = 0 AND ct.active = 0 AND ct.language_id = a.language_id \n INNER JOIN sys_borough bo ON bo.id = a.borough_id AND bo.deleted = 0 AND bo.active = 0 AND bo.language_id = a.language_id \n\n LEFT JOIN sys_countrys cox ON (cox.id = co.id OR cox.language_parent_id = co.id) AND cox.deleted = 0 AND cox.active = 0 AND cox.language_id = lx.id\n LEFT JOIN sys_city ctx ON (ctx.id = ct.id OR ctx.language_parent_id = ct.id) AND ctx.deleted = 0 AND ctx.active = 0 AND ctx.language_id = lx.id\n LEFT JOIN sys_borough box ON (box.id = bo.id OR box.language_parent_id = bo.id) AND box.deleted = 0 AND box.active = 0 AND box.language_id = lx.id \n\n LEFT JOIN sys_osb osb ON osb.id = a.osb_id\n WHERE \n a.deleted = 0 AND \n a.active =0 AND\n a.language_parent_id =0 AND \n ifk.network_key = '" . $networkKeyValue . "' \n " . $addSql . "\n " . $sorguStr; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $parameters); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); $affectedRows = $statement->rowCount(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 user_id not_null_violation $errorInfoColumn = 'pk'; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { //$debugSQLParams = $statement->debugDumpParams(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * Datagrid fill function used for testing * user interface datagrid fill operation * @param array | null $args * @return Array * @throws \PDOException */ public function fillUsersInformationNpk($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $languageId = NULL; $languageIdValue = 647; if (isset($params['language_code']) && $params['language_code'] != "") { $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } } $sql = " \n SELECT\n a.network_key as unpk,\n a.s_date AS registration_date, \n ad.name, \n ad.surname,\n ad.auth_email, \n ad.language_id, \n l.language_eng as user_language,\n\t\t\tCOALESCE(NULLIF(lx.id, NULL), 385) AS language_id,\n\t\t COALESCE(NULLIF(lx.language, ''), 'en') AS language_name, \n ifk.network_key as npk,\n COALESCE(NULLIF(fpx.firm_name, ''), fp.firm_name_eng) AS firm_name,\n fp.firm_name_eng,\n COALESCE(NULLIF(ifux.title, ''), ifu.title_eng) AS title,\n ifu.title_eng,\n ad.root_id = u.id AS userb\n FROM info_users a\n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0\n LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n INNER JOIN info_users_detail ad ON ad.deleted =0 AND ad.active =0 AND ad.root_id = a.id AND ad.language_parent_id = 0\n INNER JOIN info_users u ON u.id = " . intval($opUserIdValue) . "\n LEFT JOIN info_firm_users ifu ON ifu.user_id = a.id AND ifu.cons_allow_id =2 \n LEFT JOIN info_firm_users ifux ON (ifux.language_parent_id = ifu.id OR ifux.id=ifu.id) AND ifux.cons_allow_id =2 AND ifux.language_id = lx.id \n LEFT JOIN info_firm_profile fp ON (fp.act_parent_id = ifu.firm_id) AND fp.cons_allow_id =2 AND fp.language_id = l.id \n LEFT JOIN info_firm_profile fpx ON (fpx.language_parent_id = fp.id OR fpx.id=fp.id) AND fpx.cons_allow_id =2 AND fpx.language_id = lx.id \n LEFT JOIN info_firm_keys ifk ON ifk.firm_id = fp.act_parent_id \n WHERE a.deleted =0 \n and a.network_key = '" . $params['network_key'] . "' \n "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 user_id not_null_violation $errorInfoColumn = 'pk'; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { //$debugSQLParams = $statement->debugDumpParams(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * sys_machine_groups_property_definition tablosuna parametre olarak gelen id deki kaydın bilgilerini siler !! * @version v 1.0 27.06.2016 * @param type $params * @return array * @throws \PDOException */ public function deletePropertyMachine($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $sql = "\n UPDATE sys_machine_tool_properties\n SET \n active = 1, \n deleted = 1, \n op_user_id = " . intval($opUserIdValue) . " \n WHERE machine_tool_property_definition_id = " . intval($params['property_id']) . " AND \n machine_tool_id = " . intval($params['machine_id']) . " \n AND active = 0 AND deleted = 0 \n "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $update = $statement->execute(); $affectedRows = $statement->rowCount(); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
<div id="block_info_user"> <h3>Личный кабинет</h3> <?php require_once "config.php"; require_once "lib/User.php"; echo "<p>Логин <b>" . $_SESSION["login"] . "</b></p>"; echo "<p>Статус <b>" . $_SESSION["group_user"] . "</b></p>"; if ($_SESSION["admin"]) { echo '<form action="" method="get"> <p>Информация по id пользователя <input type="text" id="show_info" name="show_info" maxlength="10" size="2" title="Введите id пользователя" /> <input type="submit" name="show_info_user" value="show" /></p> </form>'; } echo '<p id="exit_cab"><a href="exit.php">Выход</a></p>'; if (isset($_GET["show_info_user"])) { $show_info = new InfoUsers(); $show_info->getInfoById($_GET["show_info"]); } ?> </div> <div id="block_all_users"> <h2>Список пользователей</h2> <?php $info = new InfoUsers(); $info->ShowAllUsers(); ?> </div> </body> </html>
/** * @author Okan CIRAN * @ firman ın kullanıcılarının socialmedia bilgilerinin sayısını döndürür !! * @version v 1.0 21.04.2016 * @param array | null $args * @return array * @throws \PDOException */ public function fillCompanyUsersSocialMediaNpkRtc($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $userId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($userId)) { $addSql = ""; $networkKey = "-1"; if (isset($params['network_key']) && $params['network_key'] != "") { $networkKey = $params['network_key']; } $sql = " \n SELECT\n count(a.id) AS COUNT \n FROM info_users_socialmedia a\n INNER JOIN info_users_detail iud on iud.root_id = a.user_id AND iud.deleted =0 AND iud.active =0 \t\t\n INNER JOIN sys_language l ON l.id = iud.language_id AND l.deleted =0 AND l.active = 0 \n INNER JOIN sys_socialmedia sm ON sm.id = a.sys_socialmedia_id AND sm.deleted =0 AND sm.active =0 AND sm.language_id = l.id\t\t\n\t\tINNER JOIN info_firm_users ifu ON ifu.user_id = a.user_id AND ifu.active = 0 AND ifu.deleted = 0 AND ifu.language_parent_id =0 \n\t\tINNER JOIN info_firm_profile fp ON fp.act_parent_id = ifu.firm_id AND fp.active = 0 AND fp.deleted = 0 AND fp.language_parent_id =0 \n\t \tINNER JOIN info_firm_keys fk ON fp.act_parent_id = fk.firm_id \n WHERE \t\n a.cons_allow_id=2 AND \n fk.network_key= '" . $networkKey . "' AND\n iud.language_parent_id =0\n "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 user_id not_null_violation $errorInfoColumn = 'pk'; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ firm_language_id li firmaların danısman tarafından onaylanmış kayıtların sayısını döndürür !! * @version v 1.0 30.05.2016 * @param array | null $args * @return array * @throws \PDOException */ public function fillFindFirmLanguageIdRtc($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $userId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($userId)) { $FirmLanguageId = 385; if (isset($params['firm_language_id']) && $params['firm_language_id'] != "") { $FirmLanguageId = $params['firm_language_id']; } $sql = " \n SELECT \n COUNT(a.id) AS count \n FROM info_firm_language_info a\n INNER JOIN sys_project_settings sps ON sps.op_project_id = 1 AND sps.active =0 AND sps.deleted =0 \n INNER JOIN info_users u ON u.id = a.op_user_id\n INNER JOIN info_firm_profile fp ON fp.act_parent_id = a.firm_id AND fp.cons_allow_id=2 AND fp.language_parent_id =0\n INNER JOIN sys_language l ON l.id = fp.language_id AND l.deleted =0 AND l.active =0 \n INNER JOIN info_firm_keys ifk ON fp.act_parent_id = ifk.firm_id \n\t\t INNER JOIN sys_language ssl ON ssl.id = a.firm_language_id AND ssl.deleted =0 AND ssl.active =0 AND ssl.language_parent_id =0 AND ssl.lang_choose=1 \n\t\t WHERE \n a.firm_language_id = " . intval($FirmLanguageId) . " AND\n a.cons_allow_id =2 AND\n\t\t\ta.profile_public=0\t\t \n "; $statement = $pdo->prepare($sql); //echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 user_id not_null_violation $errorInfoColumn = 'pk'; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * * @author Okan CIRAN * @ public key e ait danışmanın gerçekleştirdiği operasyonları ve adetlerinin döndürür !! * @version v 1.0 08.03.2016 * @param array | null $args * @return array * @throws \PDOException */ public function getConsultantOperation($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; //// su anda kullanılmıyor. $sql = " \n SELECT count(a.id) AS adet , \n a.operation_type_id,\n op.operation_name as aciklama\n FROM sys_activation_report a \n INNER JOIN sys_operation_types op ON op.parent_id = 2 AND op.id = a.operation_type_id AND op.deleted =0 AND op.active =0\n INNER JOIN sys_language l ON l.language_main_code = a.language_code AND l.deleted =0 AND l.active =0 \n INNER JOIN info_users u ON u.id = a.op_user_id \n INNER JOIN sys_acl_roles acl ON acl.id = u.role_id \n WHERE \n a.op_user_id = " . intval($opUserIdValue) . "\n GROUP BY a.operation_type_id, op.operation_name\n ORDER BY op.operation_name\n "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_CLASS); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } //return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); return json_encode($result); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { // $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * user interface fill operation * @author Okan CIRAN * @ tree doldurmak için sys_machine_tool tablosundan tüm kayıtları döndürür !! * @version v 1.0 19.02.2016 * @param array | null $args * @return array * @throws \PDOException */ public function fillUsersFirmMachineProperties($params = array()) { try { $pdo = $this->getServiceLocator()->get('pgConnectFactory'); $userId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($userId)) { $ownerUser = $userId['resultSet'][0]['user_id']; $addSql = ""; $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } else { $languageIdValue = 647; } if (isset($params['machine_id'])) { $addSql .= " AND a.sys_machine_tool_id = " . intval($params['machine_id']) . " "; } $sql = " \n \tSELECT \n mtp.id, \n cast(a.sys_machine_tool_id as text) as machine_id ,\t\t \n\t\t COALESCE(NULLIF(pd.property_name, ''), pd.property_name_eng) AS property_names,\n pd.property_name_eng,\n\t\t mtp.property_value, \n\t\t u.id AS unit_id,\n COALESCE(NULLIF(u.unitcode, ''), u.unitcode_eng) AS unitcodes \n FROM info_firm_machine_tool a\n\t\tINNER JOIN sys_language lx ON lx.id =" . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0 \n\t\tINNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0 \t\t\n\t\tINNER JOIN info_firm_users ifu ON ifu.user_id = " . intval($ownerUser) . " AND ifu.language_id = l.id \n INNER JOIN info_firm_profile ifp ON (ifp.id = ifu.firm_id OR ifp.language_parent_id = ifu.firm_id) AND ifp.active =0 AND ifp.deleted =0 AND ifp.language_id = l.id\n INNER JOIN sys_machine_tools mt ON (mt.id = a.sys_machine_tool_id OR mt.language_parent_id = a.sys_machine_tool_id )AND mt.language_id = lx.id\n LEFT JOIN sys_machine_tool_properties mtp ON mtp.machine_tool_id = a.sys_machine_tool_id AND mtp.language_id = lx.id\n LEFT JOIN sys_machine_tool_property_definition pd ON (pd.id = mtp.machine_tool_property_definition_id OR pd.language_parent_id = mtp.machine_tool_property_definition_id) AND pd.language_id = lx.id \n LEFT JOIN sys_units u ON (u.id = mtp.unit_id OR u.language_parent_id = mtp.unit_id) AND u.language_id = lx.id\n WHERE a.deleted =0 AND a.active =0 \n AND a.language_parent_id =0 \n " . $addSql . "\n "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 user_id not_null_violation $errorInfoColumn = 'pk'; return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * sys_operation_types_tools tablosuna parametre olarak gelen id deki kaydın bilgilerini günceller!! * @param type $params * @return array * @throws \PDOException * @author Mustafa Zeynel Dağlı * @since 11/02/2016 */ public function update($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $pdo->beginTransaction(); $opUserId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (!\Utill\Dal\Helper::haveRecord($opUserId)) { $opUserIdValue = $opUserId['resultSet'][0]['user_id']; $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } else { $languageIdValue = 647; } $statement = $pdo->prepare("\n UPDATE sys_operation_types_tools\n SET \n parent_id = :parent_id,\n role_name = :role_name, \n role_name_eng = :role_name_eng,\n language_id :language_id,\n op_user_id = :op_user_id,\n language_parent_id = :language_parent_id, \n language_code = :language_code \n WHERE base_id = :id"); $statement->bindValue(':id', $params['id'], \PDO::PARAM_INT); $statement->bindValue(':parent_id', $params['parent_id'], \PDO::PARAM_INT); $statement->bindValue(':role_name', $params['operation_name'], \PDO::PARAM_STR); $statement->bindValue(':role_name_eng', $params['operation_name_eng'], \PDO::PARAM_STR); $statement->bindValue(':language_id', $languageIdValue, \PDO::PARAM_INT); $statement->bindValue(':op_user_id', $opUserIdValue, \PDO::PARAM_INT); $statement->bindValue(':language_parent_id', $params['language_parent_id'], \PDO::PARAM_INT); $statement->bindValue(':language_code', $params['language_code'], \PDO::PARAM_STR); $update = $statement->execute(); $affectedRows = $statement->rowCount(); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } $pdo->commit(); return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows); } else { $errorInfo = '23502'; // 23502 not_null_violation $errorInfoColumn = 'pk'; $pdo->rollback(); return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { $pdo->rollback(); return array("found" => false, "errorInfo" => $e->getMessage()); } }
/** * @author Okan CIRAN * @ firma elemanlarının socialmedia bilgilerini kayıtlarını döndürür !! * @version v 1.0 21.04.2016 * @param array | null $args * @return array * @throws \PDOException */ public function fillCompanyUsersSocialMediaNpk($params = array()) { try { $pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory'); $userId = InfoUsers::getUserId(array('pk' => $params['pk'])); if (\Utill\Dal\Helper::haveRecord($userId)) { $opUserIdValue = $userId['resultSet'][0]['user_id']; $addSql = ""; $languageId = NULL; $languageIdValue = 647; if (isset($params['language_code']) && $params['language_code'] != "") { $languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code'])); if (\Utill\Dal\Helper::haveRecord($languageId)) { $languageIdValue = $languageId['resultSet'][0]['id']; } } if (isset($params['user_id']) && $params['user_id'] != "") { $addSql .= " AND iud.root_id = " . intval($params['user_id']); } $sql = " \n SELECT \n a.id, \n iud.root_id AS user_id,\n iud.name,\n iud.surname,\n COALESCE(NULLIF(smx.name, ''),sm.name_eng) AS socialmedia_name,\n sm.name_eng AS socialmedia_eng,\n a.user_link, \n a.deleted,\n COALESCE(NULLIF(sd15x.description, ''), sd15.description_eng) AS state_deleted,\n a.active,\n COALESCE(NULLIF(sd16x.description , ''), sd16.description_eng) AS state_active,\n COALESCE(NULLIF(lx.id, NULL), 385) AS language_id,\n COALESCE(NULLIF(lx.language, ''), l.language_eng) AS language_name,\t\t\t \n a.op_user_id,\n u.username AS op_user_name,\n a.operation_type_id , \n COALESCE(NULLIF(opx.operation_name, ''), op.operation_name_eng) AS operation_name,\n sm.abbreviation \n FROM info_users_socialmedia a\n INNER JOIN info_users_detail iud on iud.root_id = a.user_id AND iud.deleted =0 AND iud.active =0 \n\t\tINNER JOIN info_firm_users ifu ON ifu.user_id = a.user_id AND ifu.active = 0 AND ifu.deleted = 0 AND ifu.language_parent_id =0 \n INNER JOIN info_firm_profile fp ON fp.act_parent_id = ifu.firm_id AND fp.active = 0 AND fp.deleted = 0 AND fp.language_parent_id =0\n INNER JOIN info_firm_keys fk ON ifu.firm_id = fk.firm_id \n INNER JOIN sys_language l ON l.id = iud.language_id AND l.deleted =0 AND l.active = 0 \n LEFT JOIN sys_language lx ON lx.id = 647 /*" . intval($languageIdValue) . " */ AND lx.deleted =0 AND lx.active =0\n INNER JOIN sys_socialmedia sm ON sm.id = a.sys_socialmedia_id AND sm.deleted =0 AND sm.active =0 AND sm.language_id = l.id\n\t\tLEFT JOIN sys_socialmedia smx ON (smx.id = sm.id OR smx.language_parent_id = sm.id) AND smx.language_id = lx.id AND smx.active =0 AND smx.deleted =0 \n INNER JOIN sys_operation_types op ON op.id = a.operation_type_id AND op.language_id =l.id AND op.deleted =0 AND op.active =0\n LEFT JOIN sys_operation_types opx ON (opx.id = a.operation_type_id OR opx.language_parent_id = a.operation_type_id) and opx.language_id =lx.id AND opx.deleted =0 AND opx.active =0\n INNER JOIN sys_specific_definitions sd15 ON sd15.main_group = 15 AND sd15.first_group= a.deleted AND sd15.language_id = l.id AND sd15.deleted = 0 \n INNER JOIN sys_specific_definitions sd16 ON sd16.main_group = 16 AND sd16.first_group= a.active AND sd16.language_id = l.id AND sd16.deleted = 0\n INNER JOIN info_users u ON u.id = a.op_user_id \n LEFT JOIN sys_specific_definitions sd15x ON (sd15x.id = sd15.id OR sd15x.language_parent_id = sd15.id) AND sd15x.language_id =lx.id AND sd15x.deleted =0 AND sd15x.active =0 \n LEFT JOIN sys_specific_definitions sd16x ON (sd16x.id = sd16.id OR sd16x.language_parent_id = sd16.id) AND sd16x.language_id = lx.id AND sd16x.deleted = 0 AND sd16x.active = 0 \n WHERE a.deleted =0 AND iud.language_parent_id =0\n\t\t\tAND fk.network_key = '" . $params['network_key'] . "'\n " . $addSql . " \n ORDER BY iud.language_id, iud.root_id\n "; $statement = $pdo->prepare($sql); // echo debugPDO($sql, $params); $statement->execute(); $result = $statement->fetchAll(\PDO::FETCH_ASSOC); $errorInfo = $statement->errorInfo(); if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) { throw new \PDOException($errorInfo[0]); } return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result); } else { $errorInfo = '23502'; // 23502 user_id not_null_violation $errorInfoColumn = 'pk'; return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn); } } catch (\PDOException $e) { return array("found" => false, "errorInfo" => $e->getMessage()); } }