/**
* @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());
}
}
