/**
* @author Okan CIRAN
* @ kullanıcının socialmedia bilgilerinin kayıtlarını döndürür !!
* @version v 1.0 09.05.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillSingularFirmSocialMedia($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$userId = InfoUsers::getUserId(array('pk' => $params['pk']));
if (\Utill\Dal\Helper::haveRecord($userId)) {
$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 a.id,\n fp.act_parent_id AS firm_id,\n COALESCE(NULLIF(fpx.firm_name, ''),fpx.firm_name_eng) AS firm_name,\n fpx.firm_name_eng,\n\t\t COALESCE(NULLIF(smx.name, ''),sm.name_eng) AS socialmedia_name,\n sm.name_eng As socialmedia_eng,\n a.firm_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,\n a.op_user_id,\n u.username AS op_user_name,\n a.profile_public,\n COALESCE(NULLIF(sd19x.description, ''), sd19.description_eng) AS state_profile_public,\n a.operation_type_id,\n COALESCE(NULLIF(opx.operation_name, ''), op.operation_name_eng) AS operation_name,\n fk.network_key,\n a.act_parent_id,\n a.s_date,\n a.c_date,\n CASE COALESCE(NULLIF(sm.logo, ''),'-') \n WHEN '-' THEN CONCAT(COALESCE(NULLIF(concat(sps.folder_road,'/'), '/'),''),sps.logos_folder,'/' ,COALESCE(NULLIF(sm.logo, ''),'image_not_found.png'))\n ELSE CONCAT(COALESCE(NULLIF(concat(sps.folder_road,'/'), '/'),''),sps.logos_folder,'/' ,COALESCE(NULLIF(sm.logo, ''),'image_not_found.png')) END AS logo\n FROM info_firm_socialmedia a\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_project_settings sps ON sps.op_project_id = 1 AND sps.active =0 AND sps.deleted =0\n INNER JOIN info_firm_keys fk ON a.firm_id = fk.firm_id \n INNER JOIN sys_language l ON l.id = fp.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_profile fpx ON (fpx.id = fp.id OR fpx.language_parent_id = fp.id) AND fpx.cons_allow_id = 2 AND fpx.language_id = l.id\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 sys_specific_definitions sd19 ON sd19.main_group = 19 AND sd19.first_group= a.profile_public AND sd19.language_id = l.id AND sd19.deleted = 0 AND sd19.active = 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 LEFT JOIN sys_specific_definitions sd19x ON (sd19x.id = sd19.id OR sd19x.language_parent_id = sd19.id) AND sd19x.language_id = lx.id AND sd19x.deleted = 0 AND sd19x.active = 0\n WHERE a.deleted = 0 AND a.active =0 AND \n fp.cons_allow_id = 2 AND \n fp.language_parent_id =0 AND \n fk.network_key = '" . $networkKeyValue . "'\n ORDER BY fp.language_id, socialmedia_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 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
* @ 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
* sys_osb_person tablosuna parametre olarak gelen id deki kaydın bilgilerini günceller !!
* @version v 1.0 29.08.2016
* @param type $params
* @return array
* @throws \PDOException
*/
public function update($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$pdo->beginTransaction();
$userId = $this->getUserId(array('pk' => $params['pk'], 'id' => $params['id']));
if (\Utill\Dal\Helper::haveRecord($userId)) {
$opUserIdValue = $userId['resultSet'][0]['user_id'];
$kontrol = $this->haveRecords($params);
if (\Utill\Dal\Helper::haveRecord($kontrol)) {
$languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
} else {
$languageIdValue = 647;
}
$sql = "\n UPDATE sys_osb_person\n SET \n osb_id= :osb_id, \n country_id= :country_id, \n active= :active, \n user_id = :user_id, \n language_id= :language_id, \n language_code= :language_code, \n op_user_id= :op_user_id \n WHERE id = " . intval($params['id']);
$statement = $pdo->prepare($sql);
$statement->bindValue(':osb_id', $params['osb_id'], \PDO::PARAM_INT);
$statement->bindValue(':country_id', $params['country_id'], \PDO::PARAM_INT);
$statement->bindValue(':active', $params['active'], \PDO::PARAM_INT);
$statement->bindValue(':user_id', $params['user_id'], \PDO::PARAM_INT);
$statement->bindValue(':language_id', $languageIdValue, \PDO::PARAM_INT);
$statement->bindValue(':language_code', $params['language_code'], \PDO::PARAM_INT);
$statement->bindValue(':op_user_id', $opUserIdValue, \PDO::PARAM_INT);
$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';
// $kontrol ['resultSet'][0]['message'];
$pdo->rollback();
$result = $kontrol;
return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '');
}
} else {
// 23505 unique_violation
$errorInfo = '23505';
// $kontrol ['resultSet'][0]['message'];
$pdo->rollback();
$result = $kontrol;
return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '');
}
} catch (\PDOException $e) {
$pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ listbox ya da combobox doldurmak için info_users_addresses tablosundan user_id nin adres tiplerini döndürür !!
* @version v 1.0 02.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillUserAddressesTypesTemp($params = array())
{
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
$userId = InfoUsers::getUserIdTemp(array('pktemp' => $params['pktemp']));
if (\Utill\Dal\Helper::haveRecord($userId)) {
$userIdValue = $userId['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;
}
$sql = " \n SELECT \n a.id ,\t\n sd8.description AS name,\n sd8.description_eng AS name_eng \n FROM info_users_addresses a \n INNER JOIN sys_specific_definitions sd8 ON sd8.main_group = 17 AND sd8.first_group= a.address_type_id AND sd8.language_id = a.language_id AND sd8.deleted = 0 AND sd8.active = 0 \n WHERE \n a.active =0 AND a.deleted = 0 AND \n a.language_id = :language_id AND \n a.user_id = :user_id \n ORDER BY name \n ";
$statement = $pdo->prepare($sql);
$statement->bindValue(':language_id', $languageIdValue, \PDO::PARAM_INT);
$statement->bindValue(':user_id', $userIdValue, \PDO::PARAM_INT);
// 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 = '23505';
// 23505 unique_violation
$errorInfoColumn = 'pk';
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn);
}
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ dropdown ya da tree ye doldurmak için sys_universities tablosundan kayıtları döndürür !!
* @version v 1.0 15.07.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillUniversityDdList($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$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'];
}
}
$CountryId = 91;
if (isset($params['country_id']) && $params['country_id'] != "") {
$CountryId = intval($params['country_id']);
}
$statement = $pdo->prepare(" \n SELECT \n a.id, \t\n COALESCE(NULLIF(sd.name, ''), a.name_eng) AS name, \n a.name_eng,\n a.logo, \n a.active,\n 'open' AS state_type ,\n CASE COALESCE(NULLIF(a.logo, ''),'-')\n WHEN '-' THEN CONCAT(COALESCE(NULLIF(concat(sps.folder_road,'/'), '/'),''),sps.logos_folder,'/' ,'image_not_found.png')\n ELSE CONCAT(sps.folder_road ,'/',sps.logos_folder,'/' ,COALESCE(NULLIF(a.logo, ''),'image_not_found.png')) END AS logo \n FROM sys_universities 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\t\tLEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0 \t\t\n LEFT JOIN sys_universities sd ON (sd.id =a.id OR sd.language_parent_id = a.id) AND sd.deleted =0 AND sd.active =0 AND lx.id = sd.language_id \n WHERE \n a.deleted = 0 AND\n\t\t a.country_id = " . intval($CountryId) . " AND \n a.language_parent_id =0 \n ORDER BY a.language_id, name\n ");
$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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ network_key verilen firmanın danısmanın onayladıgı adres kayıtlarını döndürür !!
* @version v 1.0 19.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillUsersFirmAddressNpk($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$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 a.id,\n a.firm_id,\n COALESCE(NULLIF(fpx.firm_name, ''), fp.firm_name_eng) AS firm_name,\n fp.firm_name_eng,\n a.firm_building_type_id,\n COALESCE(NULLIF(sd4x.description, ''), sd4.description_eng) AS firm_building_type,\n COALESCE(NULLIF(ax.firm_building_name, ''), a.firm_building_name_eng) AS firm_building_name,\n a.firm_building_name_eng,\n\t\t\ta.address,\n COALESCE(NULLIF(sd4x.description, ''), sd4.description_eng) AS firm_building_type,\n COALESCE(NULLIF(lx.id, NULL), 385) AS language_id,\n\t\t COALESCE(NULLIF(lx.language, ''), 'en') AS language_name,\n a.country_id,\n\t\t COALESCE(NULLIF(cox.name , ''), co.name_eng) AS country_name,\n\t\t a.city_id,\n\t\t COALESCE(NULLIF(ctx.name , ''), ct.name_eng) AS city_name,\n\t\t a.borough_id,\n\t\t COALESCE(NULLIF(box.name , ''), bo.name_eng) AS borough_name,\n a.osb_id,\n COALESCE(NULLIF(osb.name, ''),' ') AS osb_name,\n ifk.network_key,\n COALESCE(NULLIF(ifc.tel, ''),' ') AS tel,\n COALESCE(NULLIF(ifc.fax, ''),' ') AS fax,\n COALESCE(NULLIF(ifc.email, ''),' ') AS email,\n COALESCE(NULLIF(fp.web_address, ''),' ') AS web_address\n FROM info_firm_address a\n INNER JOIN info_firm_keys fk ON a.firm_id = fk.firm_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 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.cons_allow_id = 2 AND ax.language_id = lx.id\n LEFT JOIN info_firm_communications ifc ON ifc.firm_id = a.firm_id AND ifc.cons_allow_id = 2 AND ifc.language_parent_id =0 AND ifc.building_id = a.firm_building_type_id \n \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 LEFT JOIN info_firm_profile fpx ON (fpx.id = a.firm_id OR fpx.language_parent_id=a.firm_id) AND fpx.cons_allow_id =2 AND fpx.language_id =lx.id \n INNER JOIN info_firm_keys ifk ON fp.act_parent_id = ifk.firm_id\n\t\t \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\t\t \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 \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 LEFT 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 LEFT 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\t \t \n LEFT JOIN sys_osb osb ON osb.id = a.osb_id\n\n WHERE ifk.network_key= '" . $params['network_key'] . "' AND \n a.deleted = 0 AND \n a.active =0 AND \n a.cons_allow_id = 2 AND \n a.language_parent_id =0 \n \n ORDER BY a.firm_building_type_id, firm_building_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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ sys_osb bilgilerini döndürür !!
* filterRules aktif
* @version v 1.0 19.08.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillMachinePropertiesSubGridList($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$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'];
}
}
$sort = " machine_tool_name, property_name,unitcode ";
$sorguStr2 = " AND 1 = 2 ";
if (isset($params['machine_tool_id']) && $params['machine_tool_id'] != "") {
$sorguStr2 = " AND a.machine_tool_id = " . $params['machine_tool_id'];
}
$sql = " \n\t\t SELECT \n a.id, \n a.machine_tool_id, \n COALESCE(NULLIF(mt.machine_tool_name, ''), mt.machine_tool_name_eng) AS machine_tool_name,\n mt.machine_tool_name_eng,\n a.machine_tool_property_definition_id, \n COALESCE(NULLIF(mtpd.property_name, ''), mtpd.property_name_eng) AS property_name,\n mtpd.property_name_eng,\n a.property_value, \n a.property_string_value,\n a.unit_id,\n COALESCE(NULLIF(su.unitcode, ''), su.unitcode_eng) AS unitcode,\n su.unitcode_eng\n FROM sys_machine_tool_properties a\n INNER JOIN sys_specific_definitions sd16 ON sd16.main_group = 16 AND sd16.first_group= a.active AND sd16.language_id = " . intval($languageIdValue) . " AND sd16.deleted = 0 AND sd16.active = 0\n INNER JOIN info_users u ON u.id = a.op_user_id\n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active = 0 \n INNER JOIN sys_machine_tools mt ON mt.id = a.machine_tool_id and mt.active =0 and mt.deleted =0 AND mt.language_parent_id =0\n INNER JOIN sys_units su ON su.id = a.unit_id AND su.active = 0 AND su.deleted =0 AND su.language_id = l.id \n INNER JOIN sys_machine_tool_property_definition mtpd ON mtpd.id = a.machine_tool_property_definition_id AND mtpd.active =0 AND mtpd.deleted =0 AND mtpd.language_id = l.id \n WHERE \n a.active =0 AND a.deleted =0 AND a.language_parent_id =0\n " . $sorguStr2 . "\n ORDER BY " . $sort . " \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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ sys_navigation_left tablosunda parent id ye sahip alt elemanlar var mı ?
* @version v 1.0 07.03.2016
* @param type $params
* @return array
* @throws \PDOException
*/
public function haveMenuRecords($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$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.menu_name AS name , \n a.parent = " . $params['id'] . " \n AS control,\n 'Bu Menu Altında Alt Menu Kaydı Bulunmakta. Lütfen Kontrol Ediniz !!!' AS message \n FROM sys_navigation_left 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.deleted =0 AND lx.active =0 AND lx.id = " . intval($languageIdValue) . "\n LEFT JOIN sys_navigation_left ax ON (ax.id = a.id OR ax.language_parent_id = a.id) AND ax.language_id = lx.id\n WHERE a.parent = " . $params['id'] . "\n AND a.language_parent_id =0 \n AND a.deleted =0 \n LIMIT 1 \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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ sosyal medya bilgilerini dropdown ya da tree ye doldurmak için sys_mail_server tablosundan kayıtları döndürür !!
* @version v 1.0 24.05.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillMailServerList($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$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 SELECT \n a.id,\n a.host_name,\n a.host_address, \n a.active,\n 'open' AS state_type \n FROM sys_mail_server a \n INNER JOIN sys_specific_definitions sd15 ON sd15.main_group = 15 AND sd15.first_group= a.deleted AND sd15.language_id = 647 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 = 647 AND sd16.deleted = 0\n INNER JOIN info_users u ON u.id = a.op_user_id \n WHERE a.deleted = 0 \n ORDER BY a.host_name\n ");
$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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ danısman a atanmış firma isimleri ddslick için info_firm_profile tablosundan kayıtları döndürür !!
* @version v 1.0 19.08.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillConsultantAllowFirmListDds($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'];
}
}
$statement = $pdo->prepare(" \n SELECT\n a.id,\n\t\t cast( COALESCE(NULLIF( COALESCE(NULLIF(ax.firm_name_short\t, ''), a.firm_name_short_eng) , '' ), ax.firm_name) as character varying(30)) AS name, \n cast( a.firm_name_short_eng as character varying(30)) AS name_eng,\n a.active,\n 'closed' AS state_type \n FROM info_firm_profile a\n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0 \n\t\tLEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n LEFT JOIN info_firm_profile ax ON (ax.id =a.id OR ax.language_parent_id = a.id) AND ax.deleted =0 AND ax.active =0 AND lx.id = ax.language_id\n WHERE \n a.alliance_type_id >-1 and \n a.deleted = 0 AND\n a.language_parent_id =0 and\n a.active =0 and \n a.consultant_id = " . intval($opUserIdValue) . " AND \n a.cons_allow_id =2\n ");
$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';
$pdo->rollback();
return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn);
}
} catch (\PDOException $e) {
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());
}
}
/**
* user interface datagrid fill operation get row count for widget
* @author Okan CIRAN
* @ Gridi doldurmak için sys_machine_tools tablosundan çekilen kayıtlarının kaç tane olduğunu döndürür !!
* @version v 1.0 15.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillGridRowTotalCount($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
} else {
$languageIdValue = 647;
}
$whereSQL = " WHERE a.deleted =0 AND a.language_id = " . intval($languageIdValue) . ",";
$sql = "\n SELECT \n COUNT(a.id) AS COUNT \n FROM sys_machine_tools a \n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active = 0 \n INNER JOIN sys_machine_tool_groups mtg ON mtg.id = a.machine_tool_grup_id AND mtg.active = 0 AND mtg.deleted = 0 AND mtg.language_id = a.language_id\n INNER JOIN sys_specific_definitions sd ON sd.main_group = 15 AND sd.first_group= a.deleted AND sd.language_code = 'tr' AND sd.deleted = 0 AND sd.active = 0\n INNER JOIN sys_specific_definitions sd1 ON sd1.main_group = 16 AND sd1.first_group= a.active AND sd1.language_code = 'tr' AND sd1.deleted = 0 AND sd1.active = 0\n " . $whereSQL . "\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);
} catch (\PDOException $e) {
//$debugSQLParams = $statement->debugDumpParams();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ firm_language_id li firmaların danısman tarafından onaylanmış kayıtlarını döndürür !!
* @version v 1.0 30.05.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillFindFirmLanguageId($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'];
}
$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.id,\n\t\t\ta.firm_id,\t\t\t\n\t\t\tCOALESCE(NULLIF(COALESCE(NULLIF(fpx.firm_name, ''), fp.firm_name_eng), ''), fp.firm_name) AS firm_name,\n\t\t\tfp.firm_name_eng,\n\t\t\tCOALESCE(NULLIF(COALESCE(NULLIF(fpx.firm_name_short, ''), fp.firm_name_short_eng), ''), fp.firm_name_short) AS firm_name_short,\n\t\t\tfp.firm_name_short_eng,\n\t\t\tfp.web_address,\t\t\t\n\t\t\ta.firm_language_id,\n\t\t\tssl.language_main_code,\t\t\t\n\t\t\tCOALESCE(NULLIF(sslx.language, ''), ssl.language_eng) AS language,\n\t\t\tssl.language_eng,\n\t\t\tssl.language_main_code,\t\n\t\t\ta.act_parent_id,\n COALESCE(NULLIF(lx.id, NULL), 385) AS language_id,\n\t\t COALESCE(NULLIF(lx.language, ''), 'en') AS language_name,\n a.active,\n ifk.network_key,\n\t\t\tCASE COALESCE(NULLIF(fp.logo, ''),'-') \n WHEN '-' THEN CONCAT(COALESCE(NULLIF(concat(sps.folder_road,'/'), '/'),''),sps.logos_folder,'/' ,COALESCE(NULLIF(fp.logo, ''),'image_not_found.png'))\n ELSE CONCAT(ifk.folder_name ,'/',ifk.logos_folder,'/' ,COALESCE(NULLIF(fp.logo, ''),'image_not_found.png')) END AS logo,\n\t\t\tfp.place_point\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 LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n LEFT JOIN info_firm_profile fpx ON (fpx.id = fp.id OR fpx.language_parent_id = fp.id) AND fpx.language_id = lx.id AND fpx.active =0 AND fpx.deleted =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 LEFT JOIN sys_language sslx ON (sslx.id = ssl.id OR sslx.language_parent_id = ssl.id) and sslx.language_id =lx.id AND sslx.deleted =0 AND sslx.active =0 \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\n\t\t ORDER BY firm_name\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());
}
}
/**
* 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
* @ quest için npk lı firmanın danısman tarafından onaylanmış kayıtlarını döndürür !!
* @version v 1.0 30.05.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillFirmSectorNpkQuest($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$firmIdValue = NULL;
$getFirm = InfoFirmProfile::getFirmIdsForNetworkKey(array('network_key' => $params['network_key']));
if (\Utill\Dal\Helper::haveRecord($getFirm)) {
$firmIdValue = $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 \n a.firm_id,\n\t\t\ta.sector_id,\t\t\t\n\t\t\tCOALESCE(NULLIF(ssx.name, ''), ss.name_eng) AS sector_name,\t\t\t\n\t\t\tss.name_eng as sector_name_eng \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 LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.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 LEFT JOIN sys_sectors ssx ON (ssx.id = ss.id OR ssx.language_parent_id = ss.id) and ssx.language_id =lx.id AND ssx.deleted =0 AND ssx.active =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\n\t\t ORDER BY sector_name\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);
}
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* user interface fill operation
* @author Okan CIRAN
* @ tree doldurmak için sys_machine_tool_property_definition tablosundan machine_tool_grup_id si
* verilen kayıtları döndürür !! machine_tool_grup_id değeri boş yada bulunamazsa tüm kayıtları döndürür.
* @version v 1.0 17.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillMachineToolGroupPropertyDefinitions($params = array())
{
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
$machineToolGrupId = 0;
$whereSql = " WHERE a.deleted = 0 ";
if (isset($params['machine_tool_grup_id']) && $params['machine_tool_grup_id'] != "") {
$machineToolGrupId = $params['machine_tool_grup_id'];
$whereSql .= " AND a.machine_tool_grup_id = " . $machineToolGrupId . " ";
} else {
$whereSql .= "";
}
$languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
} else {
$languageIdValue = 647;
}
$whereSql .= " AND a.language_id = " . intval($languageIdValue);
$statement = $pdo->prepare(" \n SELECT \n a.id, \n COALESCE(NULLIF(a.property_name, ''), a.property_name_eng) AS name, \n a.property_name_eng as name_eng,\n a.unit_grup_id,\n a.active , \n 'open' AS state_type,\n 'false' AS root_type \n FROM sys_machine_tool_property_definition a \n " . $whereSql . " \n ORDER BY name \n \n ");
$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);
} catch (\PDOException $e) {
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 29.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillNaceCodes($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$addSql = "";
$languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
} else {
$languageIdValue = 647;
}
$addSql = " WHERE a.active=0 AND a.deleted= 0 AND a.language_parent_id =0 ";
if (isset($params['parent_id'])) {
$addSql .= " AND a.parent_id = " . intval($params['parent_id']) . " ";
} else {
$addSql .= " AND a.parent_id = 0 ";
}
$sql = " \n SELECT \n a.id,\n a.parent_id,\n CONCAT(a.nace_code, ' - ', COALESCE(NULLIF(sd.description, ''), a.description_eng)) AS descriptions, \n CONCAT(a.nace_code, ' - ', a.description_eng) AS description_engs,\t\t \n CASE \n (SELECT DISTINCT 1 state_type FROM sys_nace_codes ax WHERE ax.parent_id = a.id AND ax.deleted = 0 AND ax.active = 0 AND a.language_parent_id=0) \n WHEN 1 THEN 'closed'\n ELSE 'open' \n END AS state_type \n FROM sys_nace_codes a\n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0 \n\t\tLEFT JOIN sys_language lx ON lx.id =" . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0 \t\t\n LEFT JOIN sys_nace_codes sd ON (sd.id =a.id OR sd.language_parent_id = a.id) AND sd.deleted =0 AND sd.active =0 AND lx.id = sd.language_id \t\t\n " . $addSql . "\n ORDER BY a.nace_code \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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ dropdown ya da tree ye doldurmak için sys_language tablosundan kayıtları döndürür !!
* @version v 1.0 25.07.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillCertificationsDdList($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$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 SELECT \n a.id,\n COALESCE(NULLIF(su.certificate, ''), a.certificate_eng) AS certificate_name, \n a.certificate_eng AS certificate_name_eng,\n\t\t COALESCE(NULLIF(su.certificate_short, ''), a.certificate_short_eng) AS certificate_shorts, \n a.certificate_short_eng,\n\t\t COALESCE(NULLIF(su.description, ''), a.description_eng) AS descriptions, \n a.description_eng,\n a.active,\n COALESCE(NULLIF(sd16x.description, ''), sd16.description_eng) AS state_active,\n\t\t CASE COALESCE(NULLIF(a.logo, ''),'-')\n WHEN '-' THEN CONCAT(COALESCE(NULLIF(concat(sps.folder_road,'/'), '/'),''),sps.logos_folder,'/' ,COALESCE(NULLIF(a.logo, ''),'image_not_found.png'))\n ELSE CONCAT(COALESCE(NULLIF(concat(sps.folder_road,'/'), '/'),''),sps.logos_folder,'/' ,COALESCE(NULLIF(a.logo, ''),'image_not_found.png')) END AS logo\t\n FROM sys_certifications 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\t\tLEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =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 AND sd16.active = 0\n\t\tLEFT JOIN sys_specific_definitions sd16x ON (sd16x.id = sd16.id OR sd16x.language_parent_id = sd16.id) AND sd16x.deleted =0 AND sd16x.active =0 AND lx.id = sd16x.language_id\n LEFT JOIN sys_certifications su ON (su.id =a.id OR su.language_parent_id = a.id) AND su.deleted =0 AND su.active =0 AND lx.id = su.language_id \n WHERE a.language_parent_id = 0 AND a.active =0 AND a.deleted =0 \n ORDER BY a.priority, certificate_name\n\n ");
$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);
} catch (\PDOException $e) {
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
* @ Gridi doldurmak için sys_osb_clusters_alliance tablosundan döndürülen kayıtların adedini döndürür !!
* @version v 1.0 27.06.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillGridAllianceListRtc($args = array())
{
$languageId = NULL;
$languageIdValue = 647;
if (isset($args['language_code']) && $args['language_code'] != "") {
$languageId = SysLanguage::getLanguageId(array('language_code' => $args['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
}
}
$sorguStr = null;
if (isset($params['filterRules']) && $params['filterRules'] != "") {
$filterRules = trim($params['filterRules']);
$jsonFilter = json_decode($filterRules, true);
$sorguExpression = null;
foreach ($jsonFilter as $std) {
if ($std['value'] != null) {
switch (trim($std['field'])) {
case 'osb':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND osb" . $sorguExpression . ' ';
break;
case 'clusters':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND clusters" . $sorguExpression . ' ';
break;
case 'alliance_type_name':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND alliance_type_name" . $sorguExpression . ' ';
break;
case 'alliance_name':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND alliance_name" . $sorguExpression . ' ';
break;
case 'country_name':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND country_name" . $sorguExpression . ' ';
break;
case 'city_name':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND city_name" . $sorguExpression . ' ';
break;
case 'borough_name':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND borough_name" . $sorguExpression . ' ';
break;
default:
break;
}
}
}
} else {
$sorguStr = null;
$filterRules = "";
if (isset($params['osb_id']) && $params['osb_id'] != "") {
$sorguStr .= " AND osb_id = " . $params['osb_id'];
}
if (isset($params['clusters_id']) && $params['clusters_id'] != "") {
$sorguStr .= " AND a.osb_clusters_id " . $params['clusters_id'];
}
if (isset($params['alliance_type_id']) && $params['alliance_type_id'] != "") {
$sorguStr .= " AND alliance_type_id = " . $params['alliance_type_id'];
}
if (isset($params['alliance_name']) && $params['alliance_name'] != "") {
$sorguStr .= " AND alliance_name Like '%" . $params['unitcode_eng'] . "%'";
}
if (isset($params['country_id']) && $params['country_id'] != "") {
$sorguStr .= " AND country_id = " . $params['country_id'];
}
if (isset($params['city_id']) && $params['city_id'] != "") {
$sorguStr .= " AND city_id = " . $params['city_id'];
}
if (isset($params['borough_id']) && $params['borough_id'] != "") {
$sorguStr .= " AND borough_id = " . $params['borough_id'];
}
}
$sorguStr = rtrim($sorguStr, "AND ");
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$sql = "\n SELECT COUNT(id) AS count FROM (\n SELECT id,osb_id,osb,clusters,\n alliance_type_id,alliance_type_name,alliance_name,\n country_id,country_name,city_id,\n city_name,borough_id,borough_name \n FROM (\n SELECT \n a.id,\n soc.osb_id,\n COALESCE(NULLIF(sox.name, ''), so.name_eng) AS osb,\n COALESCE(NULLIF(scx.name, ''), sc.name_eng) AS clusters, \n\t\t\ta.alliance_type_id, \n\t\t\tCOALESCE(NULLIF(sd20x.description , ''), sd20.description_eng) AS alliance_type_name,\n\t\t\tCASE \n\t\t\t a.alliance_type_id \n\t\t\t\tWHEN 1 THEN (SELECT c.firm_name FROM info_firm_profile c WHERE c.act_parent_id = a.alliance_id AND c.active =0 AND c.deleted =0) \n\t\t\t\tWHEN 2 THEN (SELECT concat(d.name, ' ', surname) FROM info_users_detail d WHERE d.root_id = a.alliance_id AND d.active =0 AND d.deleted =0) \n\t\t\t\tELSE NULL \n\t\t\tEND AS alliance_name, \n so.country_id,\n co.name AS country_name,\n\t\t\tso.city_id, \n\t\t\tct.name AS city_name,\n\t\t\tso.borough_id, \n\t\t\tbo.name AS borough_name\n FROM sys_osb_clusters_alliance a \n INNER JOIN sys_osb_clusters soc ON soc.id = a.osb_clusters_id AND soc.active =0 AND soc.deleted =0 \n INNER JOIN sys_osb so ON so.id = soc.osb_id AND so.deleted =0 AND so.active =0 AND so.language_parent_id =0 \n INNER JOIN sys_language l ON l.id = so.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 sys_osb sox ON (sox.id = so.id OR sox.language_parent_id = so.id) AND sox.deleted =0 AND sox.active =0 AND lx.id = sox.language_id\n\t\tINNER JOIN sys_clusters sc ON sc.id = soc.clusters_id AND sc.deleted =0 AND sc.active =0 AND l.id = sc.language_id\n LEFT JOIN sys_clusters scx ON (scx.id = sc.id OR scx.language_parent_id = sc.id) AND scx.deleted =0 AND scx.active =0 AND lx.id = scx.language_id\t\t\n\t\tLEFT JOIN sys_countrys co ON co.id = so.country_id AND co.deleted = 0 AND co.active = 0 AND co.language_id = so.language_id \n\t\tLEFT JOIN sys_city ct ON ct.id = so.city_id AND ct.deleted = 0 AND ct.active = 0 AND ct.language_id = so.language_id \n\t \tLEFT JOIN sys_borough bo ON bo.boroughs_id = so.borough_id AND bo.city_id = so.city_id AND bo.deleted = 0 AND bo.active = 0 AND bo.language_id = so.language_id \n\n\t \tINNER JOIN sys_specific_definitions sd20 ON sd20.main_group = 20 AND sd20.first_group= a.alliance_type_id AND sd20.language_id = so.language_id AND sd20.deleted = 0 \n\t \tLEFT JOIN sys_specific_definitions sd20x ON (sd20x.id = sd20.id OR sd20x.language_parent_id = sd20.id ) AND sd20x.language_id = lx.id AND sd20x.deleted = 0 AND sd20x.active = 0\n WHERE a.deleted =0 AND a.active =0 \n\t\t) AS tempxx \n " . $sorguStr . " \n ";
$statement = $pdo->prepare($sql);
// echo debugPDO($sql, $parameters);
$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);
} catch (\PDOException $e) {
//$debugSQLParams = $statement->debugDumpParams();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* user interface datagrid fill operation get row count for widget
* @author Okan CIRAN
* @ combobox ı doldurmak için sys_city tablosundan çekilen kayıtları döndürür !!
* @version v 1.0 17.12.2015
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillComboBox($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$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\t\ta.city_id AS id,\n COALESCE(NULLIF( COALESCE(NULLIF(sd.name, ''), a.name_eng),''), a.name) AS name,\n a.name_eng,\n CASE (SELECT COUNT(z.id) FROM sys_borough z WHERE z.country_id = a.country_id) \n\t\t\tWHEN 0 THEN false\n\t\t\tELSE true END AS boroughlist,\n a.active\n FROM sys_city a\n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0\n\t\tLEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n\t\tLEFT JOIN sys_city sd ON (sd.id =a.id OR sd.language_parent_id = a.id) AND sd.deleted =0 AND sd.active =0 AND lx.id = sd.language_id \n WHERE a.active = 0 AND a.deleted = 0 \n AND a.country_id = " . intval($params['country_id']) . " \n ORDER BY a.priority ASC, 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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* user interface datagrid fill operation get row count for widget
* @author Okan CIRAN
* @ Gridi doldurmak için sys_machine_tool_properties tablosundan çekilen kayıtlarının kaç tane olduğunu döndürür !!
* @version v 1.0 17.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillMachineToolFullPropertiesRtc($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
} else {
$languageIdValue = 647;
}
$parentId = 0;
if (isset($params['id']) && $params['id'] != "") {
$parentId = $params['id'];
}
$whereSql = " WHERE \n a.deleted =0 AND \n a.active = 0 AND\n a.language_parent_id = 0 AND \n a.parent_id = " . intval($parentId) . " AND \n a.language_id = " . intval($languageIdValue);
$sql = "\n SELECT \n COUNT(a.id) AS COUNT \n FROM sys_machine_tool_property_definition a\n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0 \t \n " . $whereSql . "\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);
} catch (\PDOException $e) {
//$debugSQLParams = $statement->debugDumpParams();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ firma kullanıcılarının socialmedia bilgilerini 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)) {
$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'];
}
}
$networkKey = "-1";
if (isset($params['network_key']) && $params['network_key'] != "") {
$networkKey = $params['network_key'];
}
$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_name_eng,\n a.user_link, \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 sm.abbreviation, \n fk.network_key \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 LEFT JOIN sys_language lx ON lx.id = " . 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\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 ORDER BY iud.language_id, 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]);
}
// print_r( array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result) ) ;
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());
}
}
public function fillUnspscCodesTreeRtc($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
} else {
$languageIdValue = 647;
}
$whereSql = " WHERE a.active =0 AND a.deleted = 0 AND a.language_parent_id = 0 ";
if (isset($params['parent_id']) && $params['parent_id'] != "") {
$whereSql .= " AND a.parent_id = " . intval($params['parent_id']);
} else {
$whereSql .= " AND a.parent_id = 0 ";
}
$sql = "\n SELECT \n COUNT(a.id ) as COUNT \n FROM sys_unspsc_codes a \n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0 \n\t\tLEFT JOIN sys_language lx ON lx.id =" . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0 \t\t\n LEFT JOIN sys_unspsc_codes su ON (su.id =a.id OR su.language_parent_id = a.id) AND su.deleted =0 AND su.active =0 AND lx.id = su.language_id \n " . $whereSql . " \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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* user interface datagrid fill operation get row count for widget
* @param array | null $params
* @return array
* @throws \PDOException
*/
public function fillGridRowTotalCount($params = array())
{
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
$languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
} else {
$languageIdValue = 647;
}
$whereSql .= " a.language_id = " . intval($languageIdValue);
$whereSql1 .= " WHERE a1.deleted = 0 AND a1.language_id = " . intval($languageIdValue);
$whereSql2 .= " WHERE a2.deleted = 1 AND a2.language_id = " . intval($languageIdValue);
$sql = "\n SELECT \n count(a.id) as count ,\n (SELECT count(a1.id) AS toplam FROM info_users a1 \t\t \n INNER JOIN sys_operation_types op1 ON op1.id = a1.operation_type_id and op1.language_id = a1.language_id\n INNER JOIN sys_specific_definitions sd1 ON sd1.main_group = 13 AND sd1.language_id = a1.language_id AND a1.auth_allow_id = sd1.first_group \n INNER JOIN sys_specific_definitions sd11 ON sd11.main_group = 14 AND sd11.language_code = a1.language_code AND a1.cons_allow_id = sd11.first_group \n INNER JOIN sys_specific_definitions sd21 ON sd21.main_group = 15 AND sd21.first_group= a1.deleted AND sd21.language_id = a1.language_id AND sd21.deleted =0 AND sd21.active =0 \n INNER JOIN sys_specific_definitions sd31 ON sd31.main_group = 16 AND sd31.first_group= a1.active AND sd31.language_id = a1.language_id AND sd31.deleted = 0 AND sd31.active = 0\n INNER JOIN sys_specific_definitions sd41 ON sd41.main_group = 3 AND sd41.first_group= a1.active AND sd41.language_id = a1.language_id AND sd41.deleted = 0 AND sd41.active = 0\n INNER JOIN sys_language l1 ON l1.id = a1.language_id AND l1.deleted =0 AND l1.active =0 \n INNER JOIN info_users u1 ON u1.id = a1.user_id \n " . $whereSql1 . ") AS undeleted_count, \n (SELECT count(a2.id) AS toplam FROM info_users a2\n INNER JOIN sys_operation_types op2 ON op2.id = a2.operation_type_id and op2.language_id = a2.language_id\n INNER JOIN sys_specific_definitions sd2 ON sd2.main_group = 13 AND sd2.language_id = a2.language_id AND a2.auth_allow_id = sd2.first_group \n INNER JOIN sys_specific_definitions sd12 ON sd12.main_group = 14 AND sd12.language_id = a2.language_id AND a2.cons_allow_id = sd12.first_group \n INNER JOIN sys_specific_definitions sd22 ON sd22.main_group = 15 AND sd22.first_group = a2.deleted AND sd22.language_id = a2.language_id AND sd22.deleted =0 AND sd22.active =0 \n INNER JOIN sys_specific_definitions sd32 ON sd32.main_group = 16 AND sd32.first_group = a2.active AND sd32.language_id = a2.language_id AND sd32.deleted = 0 AND sd32.active = 0\n INNER JOIN sys_specific_definitions sd42 ON sd42.main_group = 3 AND sd42.first_group = a2.active AND sd42.language_id = a2.language_id AND sd42.deleted = 0 AND sd42.active = 0\n INNER JOIN sys_language l2 ON l2.id = a2.language_id AND l2.deleted =0 AND l2.active =0 \n INNER JOIN info_users u2 ON u2.id = a2.user_id \n " . $whereSql2 . " ) AS deleted_count \n FROM info_users a \t\t \n\t\t INNER JOIN sys_operation_types op ON op.id = a.operation_type_id and op.language_id = a.language_id\n\t\t INNER JOIN sys_specific_definitions sd ON sd.main_group = 13 AND sd.language_id = a.language_id AND a.auth_allow_id = sd.first_group \n\t\t INNER JOIN sys_specific_definitions sd1 ON sd1.main_group = 14 AND sd1.language_id = a.language_id AND a.cons_allow_id = sd1.first_group \n\t\t INNER JOIN sys_specific_definitions sd2 ON sd2.main_group = 15 AND sd2.first_group= a.deleted AND sd2.language_id = a.language_id AND sd2.deleted =0 AND sd2.active =0 \n\t\t INNER JOIN sys_specific_definitions sd3 ON sd3.main_group = 16 AND sd3.first_group= a.active AND sd3.language_id = a.language_id AND sd3.deleted = 0 AND sd3.active = 0\n\t\t INNER JOIN sys_specific_definitions sd4 ON sd4.main_group = 3 AND sd4.first_group= a.active AND sd4.language_id = a.language_id AND sd4.deleted = 0 AND sd4.active = 0\n\t\t INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0 \n\t\t INNER JOIN info_users u ON u.id = a.user_id \t\t \n " . $whereSql . " \n ";
$statement = $pdo->prepare($sql);
$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);
} catch (\PDOException $e) {
//$debugSQLParams = $statement->debugDumpParams();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ info_firm_working_personnel bilgilerinin sayısını döndürür !!
* filterRules aktif
* @version v 1.0 18.07.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillFirmWorkingPersonalListGridRtc($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$sorguStr = null;
if (isset($params['filterRules']) && $params['filterRules'] != "") {
$filterRules = trim($params['filterRules']);
$jsonFilter = json_decode($filterRules, true);
$sorguExpression = null;
foreach ($jsonFilter as $std) {
if ($std['value'] != null) {
switch (trim($std['field'])) {
case 'name':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND name" . $sorguExpression . ' ';
break;
case 'surname':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND surname" . $sorguExpression . ' ';
break;
case 'positions':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND positions" . $sorguExpression . ' ';
break;
case 'positions_eng':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND positions_eng" . $sorguExpression . ' ';
break;
case 'title':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND title" . $sorguExpression . ' ';
break;
case 'title_eng':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND title_eng" . $sorguExpression . ' ';
break;
case 'sex_name':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND sex_name" . $sorguExpression . ' ';
break;
case 'op_user_name':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND op_user_name" . $sorguExpression . ' ';
break;
case 'state_profile_public':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND state_profile_public" . $sorguExpression . ' ';
break;
default:
break;
}
}
}
} else {
$sorguStr = null;
$filterRules = "";
if (isset($params['name']) && $params['name'] != "") {
$sorguStr .= " AND a.name Like '%" . $params['name'] . "%'";
}
if (isset($params['surname']) && $params['surname'] != "") {
$sorguStr .= " AND surname Like '%" . $params['surname'] . "%'";
}
if (isset($params['positions']) && $params['positions'] != "") {
$sorguStr .= " AND positions Like '%" . $params['positions'] . "%'";
}
if (isset($params['positions_eng']) && $params['positions_eng'] != "") {
$sorguStr .= " AND positions_eng Like '%" . $params['positions_eng'] . "%'";
}
if (isset($params['title']) && $params['title'] != "") {
$sorguStr .= " AND title Like '%" . $params['title'] . "%'";
}
if (isset($params['title_eng']) && $params['title_eng'] != "") {
$sorguStr .= " AND title_eng Like '%" . $params['title_eng'] . "%'";
}
if (isset($params['sex_id']) && $params['sex_id'] != "") {
$sorguStr .= " AND sex_id = " . intval($params['sex_id']);
}
if (isset($params['profile_public']) && $params['profile_public'] != "") {
$sorguStr .= " AND profile_public = " . intval($params['profile_public']);
}
if (isset($params['active']) && $params['active'] != "") {
$sorguStr .= " AND active = " . intval($params['active']);
}
if (isset($params['op_user_name']) && $params['op_user_name'] != "") {
$sorguStr .= " AND op_user_name Like '%" . $params['op_user_name'] . "%'";
}
}
$sorguStr = rtrim($sorguStr, "AND ");
$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 COUNT(id) AS count \n FROM ( \n SELECT \n a.id,\n a.firm_id,\t\t\t\t\n\t\t\ta.name, \n\t\t\ta.surname, \t\t\t \n\t\t\tCOALESCE(NULLIF(ax.positions, ''), a.positions_eng) AS positions,\n\t\t\ta.positions_eng,\n\t\t\tCOALESCE(NULLIF(ax.title, ''), a.title_eng) AS title,\n\t\t\ta.title_eng,\n\t\t\ta.sex_id,\t\t\n\t\t\tCOALESCE(NULLIF(sd3x.description, ''), sd3.description_eng) AS sex_name,\n\t\t\ta.s_date,\n a.c_date,\n a.profile_public,\n COALESCE(NULLIF(sd19x.description, ''), sd19.description_eng) AS state_profile_public,\n a.operation_type_id,\n COALESCE(NULLIF(opx.operation_name, ''), op.operation_name_eng) AS operation_name,\n\t\t\ta.act_parent_id,\n COALESCE(NULLIF(lx.id, NULL), 385) AS language_id,\n\t\t COALESCE(NULLIF(lx.language, ''), 'en') AS language_name,\n a.active,\n COALESCE(NULLIF(sd16x.description, ''), sd16.description_eng) AS state_active,\n a.deleted,\n\t\t\tCOALESCE(NULLIF(sd15x.description, ''), sd15.description_eng) AS state_deleted,\n a.op_user_id,\n u.username AS op_user_name,\n\t\t\ta.consultant_id, \n\t\t\ta.consultant_confirm_type_id, \n\t\t\ta.confirm_id,\n a.cons_allow_id,\n COALESCE(NULLIF(sd14x.description, ''), sd14.description_eng) AS cons_allow\n FROM info_firm_working_personnel 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.active = 0 AND fp.deleted = 0 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 LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n INNER JOIN info_firm_keys ifk ON fp.act_parent_id = ifk.firm_id \n LEFT JOIN info_firm_working_personnel ax ON (ax.id = a.id OR ax.language_parent_id = a.id) and ax.language_id =lx.id AND ax.deleted =0 AND ax.active =0\n\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 = op.id OR opx.language_parent_id = op.id) and opx.language_id =lx.id AND opx.deleted =0 AND opx.active =0\n \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\t\t INNER JOIN sys_specific_definitions sd3 ON sd3.main_group = 3 AND sd3.first_group= a.sex_id AND sd3.deleted = 0 AND sd3.active = 0 AND sd3.language_parent_id =0\n \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 LEFT JOIN sys_specific_definitions sd3x ON sd3x.language_id = lx.id AND (sd3x.id = sd3.id OR sd3x.language_parent_id = sd3.id) AND sd3x.deleted = 0 AND sd3x.active = 0\n WHERE a.deleted =0 AND a.c_date IS NULL AND a.language_parent_id =0\n ) AS xtable WHERE deleted =0 \n " . $sorguStr . " \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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* Fill function used for testing
* user interface combobox fill operation
* @author Okan CIRAN
* @ İletişim adresleri dropdown ya da tree ye doldurmak için sys_specific_definitions tablosundan kayıtları döndürür !!
* @version v 1.0 03.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillSexTypes($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$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 SELECT \n a.first_group AS id, \t\n COALESCE(NULLIF(sd.description, ''), a.description_eng) AS name, \n a.description_eng AS name_eng,\n a.parent_id,\n a.active,\n CASE \n (SELECT DISTINCT 1 state_type FROM sys_specific_definitions WHERE parent_id = a.id AND deleted = 0) \n WHEN 1 THEN 'closed'\n ELSE 'open' \n END AS state_type \n FROM sys_specific_definitions a \n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0 \n\t\tLEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0 \t\t\n LEFT JOIN sys_specific_definitions sd ON (sd.id =a.id OR sd.language_parent_id = a.id) AND sd.deleted =0 AND sd.active =0 AND lx.id = sd.language_id \n WHERE \n a.main_group = 3 AND \n a.deleted = 0 AND\n a.language_parent_id =0 \n ORDER BY a.id, a.parent_id \n\n ");
$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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ user in adres , cominication , ad soyad , networkkey bilgilerinin sayısını döndürür !!
* varsa network_key, name, surname, email , communication_number parametrelerinin like ile arar
* @version v 1.0 23.06.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillPropertieslistRtc($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$languageId = NULL;
$languageIdValue = 647;
if (isset($args['language_code']) && $args['language_code'] != "") {
$languageId = SysLanguage::getLanguageId(array('language_code' => $args['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
}
}
$sorguStr = null;
if (isset($params['filterRules']) && $params['filterRules'] != "") {
$filterRules = trim($params['filterRules']);
$jsonFilter = json_decode($filterRules, true);
$sorguExpression = null;
foreach ($jsonFilter as $std) {
if ($std['value'] != null) {
switch (trim($std['field'])) {
case 'property_name':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND COALESCE(NULLIF(smtpdx.property_name, ''), a.property_name_eng)" . $sorguExpression . ' ';
break;
case 'property_name_eng':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND a.property_name_eng" . $sorguExpression . ' ';
break;
case 'unitcode':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND COALESCE(NULLIF(sux.unitcode, ''), su.unitcode_eng)" . $sorguExpression . ' ';
break;
case 'unitcode_eng':
$sorguExpression = ' ILIKE \'%' . $std['value'] . '%\' ';
$sorguStr .= " AND su.unitcode_eng" . $sorguExpression . ' ';
break;
default:
break;
}
}
}
} else {
$sorguStr = null;
$filterRules = "";
if (isset($params['property_name']) && $params['property_name'] != "") {
$sorguStr .= " AND COALESCE(NULLIF(smtpdx.property_name, ''), a.property_name_eng) Like '%" . $params['property_name'] . "%'";
}
if (isset($params['property_name_eng']) && $params['property_name_eng'] != "") {
$sorguStr .= " AND a.property_name_eng Like '%" . $params['property_name_eng'] . "%'";
}
if (isset($params['unitcode']) && $params['unitcode'] != "") {
$sorguStr .= " AND COALESCE(NULLIF(sux.unitcode, ''), su.unitcode_eng) Like '%" . $params['unitcode'] . "%'";
}
if (isset($params['unitcode_eng']) && $params['unitcode_eng'] != "") {
$sorguStr .= " AND su.unitcode_eng Like '%" . $params['unitcode_eng'] . "%'";
}
}
$sorguStr = rtrim($sorguStr, "AND ");
$sql = "\n SELECT count(id) FROM (\n\t\tSELECT \n a.id, \n COALESCE(NULLIF(smtpdx.property_name, ''), a.property_name_eng) AS property_name,\n a.property_name_eng, \n COALESCE(NULLIF(sux.unitcode, ''), su.unitcode_eng) AS unitcode,\n su.unitcode_eng, \n a.active,\n COALESCE(NULLIF(sd16x.description , ''), sd16.description_eng) AS state_active \n FROM sys_machine_tool_property_definition 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 sys_specific_definitions sd16 ON sd16.main_group = 16 AND sd16.first_group= a.active AND sd16.language_id = a.language_id AND sd16.deleted = 0 AND sd16.active = 0\n LEFT JOIN sys_specific_definitions sd16x ON sd16x.main_group = 16 AND sd16x.first_group= a.active AND sd16x.language_id = lx.id AND sd16x.deleted = 0 AND sd16x.active = 0\n LEFT JOIN sys_machine_tool_property_definition smtpdx ON (smtpdx.id = a.id OR smtpdx.language_parent_id = a.id) AND smtpdx.deleted =0 AND smtpdx.active =0 AND lx.id = smtpdx.language_id \n INNER JOIN sys_unit_groups_property_definition sugpd ON sugpd.property_id = a.id AND sugpd.active =0 AND sugpd.deleted =0 \n\t\tINNER JOIN sys_units su ON su.id = sugpd.unit_grup_id AND su.deleted =0 AND su.active =0 AND su.language_id = a.language_id \n\t\tINNER JOIN sys_units sux ON sux.id = sugpd.unit_grup_id AND sux.deleted =0 AND sux.active =0 AND sux.language_id = lx.id \n WHERE a.deleted =0 AND a.language_parent_id = 0\n " . $sorguStr . "\n ) as xtemp \n ";
$statement = $pdo->prepare($sql);
$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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* returns operation type tools
* @param array $params
* @return array
* @throws \PDOException
* @author Okan CIRAN
* @since 11/02/2016
*/
public function fillConsultantOperationsConfirmTools($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$languageId = SysLanguage::getLanguageId(array('language_code' => $params['language_code']));
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
} else {
$languageIdValue = 647;
}
$addSql = "WHERE \n a.active =0 AND a.deleted = 0 AND \n a.language_parent_id=0 AND\n a.parent_id = 0 ";
if (isset($params['main_group']) && $params['main_group'] != "") {
$addSql .= " AND a.main_group = " . intval($params['main_group']);
} else {
//$addSql .= " a.main_group in (1,2) AND " ;
$addSql .= " ";
}
$sql = "\n SELECT \n a.base_id AS id, \t\n COALESCE(NULLIF(sd.role_name, ''), a.role_name_eng) AS name,\n a.role_name_eng AS name_eng \n FROM sys_operations_types_tools a \n INNER JOIN sys_language l ON l.id = a.language_id AND l.deleted =0 AND l.active =0 \n\t\tLEFT JOIN sys_language lx ON lx.id =" . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0 \n\t\tLEFT JOIN sys_operations_types_tools sd ON (sd.id =a.id OR sd.language_parent_id = a.id) AND sd.deleted =0 AND sd.active =0 AND lx.id = sd.language_id \n " . $addSql . " \n ORDER BY 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);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ userin sectiği firmanın sözel kayıtlarını döndürür !!
* @version v 1.0 25.04.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillUsersDescForFirmVerbalNpkGuest($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
// $userId = InfoUsers::getUserId(array('pk' => $params['pk']));
// if (\Utill\Dal\Helper::haveRecord($userId)) {
$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'];
}
}
$networkKey = "-1";
if (isset($params['network_key']) && $params['network_key'] != "") {
$networkKey = $params['network_key'];
}
$sql = " \n SELECT * FROM (\n SELECT \n CAST(random()*100-1 AS int) AS ccc,\n a.user_id,\n ud.name, \n ud.surname, \n COALESCE(NULLIF(ifux.title, ''), ifu.title_eng) AS title,\n ifu.title_eng, \n a.firm_id,\n COALESCE(NULLIF(ax.verbal1_title, ''), a.verbal1_title_eng) AS verbal1_title,\n a.verbal1_title_eng,\n COALESCE(NULLIF(ax.verbal1, ''), a.verbal1_eng) AS verbal1,\n a.verbal1_eng,\n COALESCE(NULLIF(lx.id, NULL), 385) AS language_id,\n COALESCE(NULLIF(lx.language, ''), 'en') AS language_name,\n CASE COALESCE(NULLIF(ud.picture, ''),'-')\n WHEN '-' THEN CONCAT(COALESCE(NULLIF(CONCAT(sps.folder_road,'/'), '/'),''),sps.members_folder,'/' ,'image_not_found.png')\n ELSE\n CONCAT(ifks.folder_name ,'/',ifks.members_folder,'/' ,COALESCE(NULLIF(ud.picture, ''),'image_not_found.png')) END AS picture\n FROM info_firm_user_desc_for_company a \n INNER JOIN info_users_detail ud ON ud.root_id = a.user_id AND ud.cons_allow_id = 2\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 LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n LEFT JOIN info_firm_user_desc_for_company ax ON (ax.id = a.id OR ax.language_parent_id=a.id) AND ax.language_id =lx.id AND ax.cons_allow_id =2\n INNER JOIN info_firm_users ifu ON ifu.user_id = a.user_id AND ifu.cons_allow_id = 2\n LEFT JOIN info_firm_users ifux ON ifux.id = ifu.id AND ifu.cons_allow_id = 2\n INNER JOIN info_firm_keys ifks ON ifks.firm_id =1 \n WHERE \n a.cons_allow_id=2 AND \n a.language_parent_id =0 AND\n a.firm_id = 1\n ORDER BY ccc DESC\n limit 2 \n ) AS xtable \n union \n (\n SELECT \n CAST(random()*100-1 AS int) AS ccc,\n a.user_id,\n ud.name, \n ud.surname, \n COALESCE(NULLIF(ifux.title, ''), ifu.title_eng) AS title,\n ifu.title_eng,\n a.firm_id,\n COALESCE(NULLIF(ax.verbal1_title, ''), a.verbal1_title_eng) AS verbal1_title,\n a.verbal1_title_eng,\n COALESCE(NULLIF(ax.verbal1, ''), a.verbal1_eng) AS verbal1,\n a.verbal1_eng,\n COALESCE(NULLIF(lx.id, NULL), 385) AS language_id,\n COALESCE(NULLIF(lx.language, ''), 'en') AS language_name,\n CASE COALESCE(NULLIF(ud.picture, ''),'-')\n WHEN '-' THEN CONCAT(COALESCE(NULLIF(CONCAT(sps.folder_road,'/'), '/'),''),sps.members_folder,'/' ,'image_not_found.png')\n ELSE CONCAT(ifk.folder_name ,'/',ifk.members_folder,'/' ,COALESCE(NULLIF(ud.picture, ''),'image_not_found.png')) END AS picture \n FROM info_firm_user_desc_for_company a \n INNER JOIN info_firm_users ifu ON ifu.user_id = a.user_id AND ifu.cons_allow_id = 2\n LEFT JOIN info_firm_users ifux ON ifux.id = ifu.id AND ifu.cons_allow_id = 2\n INNER JOIN info_users_detail ud ON ud.root_id = a.user_id AND ud.cons_allow_id = 2\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 LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n LEFT JOIN info_firm_user_desc_for_company ax ON (ax.id = a.id OR ax.language_parent_id=a.id) AND ax.language_id =lx.id AND ax.cons_allow_id =2\n LEFT JOIN info_firm_users ifux ON ifux.id = ifu.id AND ifu.cons_allow_id = 2\n INNER JOIN info_users_detail ud ON ud.root_id = a.user_id AND ud.cons_allow_id = 2\n INNER JOIN info_firm_keys ifk ON a.firm_id = ifk.firm_id\n where\n a.cons_allow_id = 2 AND \n a.language_parent_id =0 AND\n a.profile_public =0 AND \n ifk.network_key = '" . $networkKey . "'\n ORDER BY ccc DESC\n ) \n ORDER BY firm_id DESC\n limit 2 \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());
}
}
