/**
* @author Okan CIRAN
* @ sys_village tablosundan parametre olarak gelen id kaydını siler. !!
* @version v 1.0 07.12.2015
* @param type $params
* @return array
* @throws \PDOException
*/
public function delete($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$pdo->beginTransaction();
$userId = $this->getUserId(array('pk' => $params['pk']));
if (\Utill\Dal\Helper::haveRecord($userId)) {
$userIdValue = $userId['resultSet'][0]['user_id'];
$statement = $pdo->prepare(" \n UPDATE sys_village\n SET deleted= 1 , active = 1 ,\n op_user_id = " . $userIdValue . " \n WHERE id = :id");
//Execute our DELETE statement.
$update = $statement->execute();
$afterRows = $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" => $afterRows);
} else {
$errorInfo = '23502';
/// 23502 not_null_violation
$pdo->rollback();
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '');
}
} catch (\PDOException $e) {
$pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ connection_log tablosuna yeni bir kayıt oluşturur. !!
* @version v 1.0 10.03.2016
* @param type $params
* @return array
* @throws \PDOException
*/
public function insert($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectLogFactory');
$pdo->beginTransaction();
$userId = InfoUsers::getUserId(array('pk' => $params['pk']));
if (\Utill\Dal\Helper::haveRecord($userId)) {
$userIdValue = $userId['resultSet'][0]['user_id'];
$addSql = " op_user_id,";
$addSqlValue = " " . intval($userIdValue) . ", ";
}
$sql = "\n INSERT INTO connection_log(\n pk, \n type_id,\n log_datetime,\n url, \n path, \n ip, \n params,\n " . $addSql . " \n method\n \n )\n VALUES (\n :pk,\n :type_id,\n :log_datetime,\n :url, \n :path, \n :ip, \n :params,\n " . $addSqlValue . " \n :method \n ) ";
$statement = $pdo->prepare($sql);
$statement->bindValue(':pk', $params['pk'], \PDO::PARAM_STR);
$statement->bindValue(':type_id', $params['type_id'], \PDO::PARAM_INT);
$statement->bindValue(':log_datetime', $params['log_datetime'], \PDO::PARAM_STR);
$statement->bindValue(':url', $params['url'], \PDO::PARAM_STR);
$statement->bindValue(':path', $params['path'], \PDO::PARAM_STR);
$statement->bindValue(':ip', $params['ip'], \PDO::PARAM_STR);
$statement->bindValue(':params', $params['params'], \PDO::PARAM_STR);
$statement->bindValue(':method', $params['method'], \PDO::PARAM_STR);
echo debugPDO($sql, $params);
$result = $statement->execute();
$insertID = $pdo->lastInsertId('connection_log_id_seq');
$errorInfo = $statement->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
$pdo->commit();
return array("found" => true, "errorInfo" => $errorInfo, "lastInsertId" => $insertID);
} catch (\PDOException $e) {
$pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* 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());
}
}
/**
* @author Okan CIRAN
* @ sys_osb_person tablosundan parametre olarak gelen id kaydın aktifliğini
* 0(aktif) ise 1 , 1 (pasif) ise 0 yapar. !!
* @version v 1.0 29.08.2016
* @param type $params
* @return array
* @throws \PDOException
*/
public function makeActiveOrPassive($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$pdo->beginTransaction();
$opUserId = InfoUsers::getUserId(array('pk' => $params['pk']));
if (\Utill\Dal\Helper::haveRecord($opUserId)) {
$opUserIdValue = $opUserId['resultSet'][0]['user_id'];
if (isset($params['id']) && $params['id'] != "") {
$sql = " \n UPDATE sys_osb_person\n SET active = ( SELECT \n CASE active\n WHEN 0 THEN 1\n ELSE 0\n END activex\n FROM sys_osb_person\n WHERE id = " . intval($params['id']) . "\n ),\n op_user_id = " . intval($opUserIdValue) . "\n WHERE id = " . intval($params['id']);
$statement = $pdo->prepare($sql);
// echo debugPDO($sql, $params);
$update = $statement->execute();
$afterRows = $statement->rowCount();
$errorInfo = $statement->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
}
$xc = $this->makeActiveOrPassiveInfoUsers(array('id' => $params['id'], 'op_user_id' => $opUserIdValue));
if ($xc['errorInfo'][0] != "00000" && $xc['errorInfo'][1] != NULL && $xc['errorInfo'][2] != NULL) {
throw new \PDOException($xc['errorInfo']);
}
$pdo->commit();
return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $afterRows);
} else {
$errorInfo = '23502';
// 23502 not_null_violation
$errorInfoColumn = 'pk';
$pdo->rollback();
return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn);
}
} catch (\PDOException $e) {
$pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
*
* @author Okan CIRAN
* @ public key e ait danışmanın gerçekleştirdiği operasyonları ve adetlerinin döndürür !!
* @version v 1.0 08.03.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function getConsultantOperation($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$opUserId = InfoUsers::getUserId(array('pk' => $params['pk']));
if (\Utill\Dal\Helper::haveRecord($opUserId)) {
$opUserIdValue = $opUserId['resultSet'][0]['user_id'];
//// su anda kullanılmıyor.
$sql = " \n SELECT count(a.id) AS adet , \n a.operation_type_id,\n op.operation_name as aciklama\n FROM sys_activation_report a \n INNER JOIN sys_operation_types op ON op.parent_id = 2 AND op.id = a.operation_type_id AND op.deleted =0 AND op.active =0\n INNER JOIN sys_language l ON l.language_main_code = a.language_code AND l.deleted =0 AND l.active =0 \n INNER JOIN info_users u ON u.id = a.op_user_id \n INNER JOIN sys_acl_roles acl ON acl.id = u.role_id \n WHERE \n a.op_user_id = " . intval($opUserIdValue) . "\n GROUP BY a.operation_type_id, op.operation_name\n ORDER BY op.operation_name\n ";
$statement = $pdo->prepare($sql);
// echo debugPDO($sql, $params);
$statement->execute();
$result = $statement->fetchAll(\PDO::FETCH_CLASS);
$errorInfo = $statement->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
//return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result);
return json_encode($result);
} else {
$errorInfo = '23502';
// 23502 not_null_violation
$errorInfoColumn = 'pk';
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn);
}
} catch (\PDOException $e) {
// $pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @param type $id
* @param type $params
* @return array
* @throws PDOException
*/
public function deletedAct($params = array())
{
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
$pdo->beginTransaction();
$userId = $this->getUserId(array('pk' => $params['pk']));
if (\Utill\Dal\Helper::haveRecord($userId)) {
$userIdValue = $userId['resultSet'][0]['user_id'];
$this->setUserDetailsDisables(array('id' => $userIdValue));
$this->makeUserDeleted(array('id' => $userIdValue));
$sql = " \n INSERT INTO info_users_detail(\n profile_public, \n f_check,\n s_date,\n c_date,\n operation_type_id, \n name,\n surname, \n auth_email, \n act_parent_id,\n auth_allow_id,\n cons_allow_id,\n language_code,\n language_id,\n root_id,\n op_user_id,\n language_id,\n password, \n active,\n deleted,\n ) \n SELECT \n profile_public, \n f_check, \n s_date, \n timezone('Europe/Istanbul'::text, ('now'::text)::timestamp(0) with time zone) , \n 3,\n name,\n surname, \n auth_email, \n act_parent_id,\n auth_allow_id,\n cons_allow_id,\n language_code,\n language_id,\n root_id, \n " . intval($userIdValue) . " AS op_user_id,\n language_id,\n password, \n 1,\n 1\n FROM info_users_detail \n WHERE root_id =" . intval($userIdValue) . " \n AND active =0 AND deleted =0 \n \n ";
$insert_act_insert = $statement_act_insert->execute();
$affectedRows = $statement_act_insert->rowCount();
$errorInfo = $statement_act_insert->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
$pdo->commit();
return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows);
} else {
$errorInfo = '23502';
/// 23502 not_null_violation
$pdo->rollback();
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '');
}
} catch (\PDOException $e) {
$pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ info_users_addresses tablosundan parametre olarak gelen id kaydın active alanını 1 yapar ve
* yeni yeni kayıt oluşturarak deleted ve active = 1 olarak yeni kayıt yapar. !
* @version v 1.0 02.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function deletedActTemp($params = array())
{
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
$pdo->beginTransaction();
$userId = InfoUsers::getUserIdTemp(array('pktemp' => $params['pktemp']));
if (\Utill\Dal\Helper::haveRecord($userId)) {
$userIdValue = $userId['resultSet'][0]['user_id'];
$addSql = "";
$addSqlValue = "";
if (isset($params['act_parent_id'])) {
$act_parent_id = intval($params['act_parent_id']);
$addSql .= " act_parent_id, ";
if ($act_parent_id == 0) {
$act_parent_id = intval($params['id']);
}
$addSqlValue .= intval($act_parent_id) . ", ";
}
if (isset($params['operation_type_id'])) {
$addSql .= " operation_type_id, ";
$addSqlValue .= intval($params['operation_type_id']) . ", ";
}
$this->makePassive(array('id' => $params['id']));
$statementInsert = $pdo->prepare(" \n INSERT INTO info_users_addresses (\n user_id, \n active, \n deleted,\n op_user_id, \n " . $addSql . "\n \n language_code, \n language_id,\n address_type_id, \n address1, \n address2, \n postal_code, \n country_id, \n city_id, \n borough_id, \n city_name, \n description, \n description_eng\n profile_public,\n \n f_check, \n consultant_id,\n consultant_confirm_type_id, \n confirm_id, \n language_parent_id ,\n history_parent_id,\n consultant_id,\n consultant_confirm_type_id,\n confirm_id,\n act_parent_id\n ) \n \n SELECT\n user_id,\n 1 AS active, \n 1 AS deleted, \n " . intval($userIdValue) . " AS op_user_id, \n " . $addSqlValue . " \n \n language_code, \n language_id,\n address_type_id, \n address1, \n address2, \n postal_code, \n country_id, \n city_id, \n borough_id, \n city_name, \n description, \n description_eng\n profile_public, \n\n f_check, \n consultant_id, \n consultant_confirm_type_id, \n confirm_id, \n language_parent_id ,\n history_parent_id,\n consultant_id,\n consultant_confirm_type_id,\n confirm_id,\n act_parent_id\n FROM info_users_addresses \n WHERE id =" . intval($params['id']) . " \n )");
$insertAct = $statementInsert->execute();
$affectedRows = $statementInsert->rowCount();
$errorInfo = $statementInsert->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
$pdo->commit();
return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows);
} else {
$errorInfo = '23502';
/// 23502 not_null_violation
$errorInfoColumn = 'pk / op_user_id';
$pdo->rollback();
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn);
}
} catch (\PDOException $e) {
$pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* sys_acl_roles tablosuna parametre olarak gelen id deki kaydın bilgilerini günceller !!
* @version v 1.0 07.01.2016
* @param type $params
* @return array
* @throws \PDOException
*/
public function update($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$pdo->beginTransaction();
$kontrol = $this->haveRecords($params);
if (!\Utill\Dal\Helper::haveRecord($kontrol)) {
$sql = "\n UPDATE sys_acl_roles\n SET \n name = :name, \n user_id = :user_id \n WHERE id = " . intval($params['id']);
$statement = $pdo->prepare($sql);
$statement->bindValue(':name', $params['name'], \PDO::PARAM_STR);
$statement->bindValue(':user_id', $params['user_id'], \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" => true, "errorInfo" => $errorInfo, "resultSet" => '');
}
} catch (\PDOException $e) {
$pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
*
* @author Okan CIRAN
* @ Danışmanın onay bekleyen firmalarının bilgilerini döndürür !!
* @version v 1.0 05.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function getConsWaitingForConfirm($params = array())
{
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
$opUserId = InfoUsers::getUserId(array('pk' => $params['pk']));
if (\Utill\Dal\Helper::haveRecord($opUserId)) {
$opUserIdValue = $opUserId['resultSet'][0]['user_id'];
$sql = " \n SELECT \n aciklama , \n CASE\n WHEN sure_int > 50000 THEN CAST(SUBSTRING(sure FROM 1 FOR POSITION(' ' IN sure )-1 ) AS integer)\n ELSE 0 \n END AS sure\n FROM (\n SELECT a.id,\n EXTRACT(EPOCH FROM age(a.s_date)) AS sure_int, \n\t\t\ta.firm_name AS aciklama, \n CAST(CURRENT_TIMESTAMP - a.s_date AS VARCHAR(20)) AS sure\n FROM info_firm_profile a \n INNER JOIN info_users u ON u.id = a.consultant_id \n INNER JOIN sys_operation_types op ON op.parent_id = 1 AND a.operation_type_id = op.id AND op.active = 0 AND op.deleted =0\n WHERE \n a.consultant_id = " . intval($opUserIdValue) . " \n ) AS asdasd\n ORDER BY sure DESC\n LIMIT 6\n \n ";
$statement = $pdo->prepare($sql);
// echo debugPDO($sql, $params);
$statement->execute();
$result = $statement->fetchAll(\PDO::FETCH_CLASS);
$errorInfo = $statement->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
//return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result);
return json_encode($result);
} else {
$errorInfo = '23502';
// 23502 not_null_violation
$errorInfoColumn = 'pk';
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn);
}
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @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());
}
}
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
* @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
* @ 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());
}
}
/**
* @author Okan CIRAN
* @ sys_acl_action_rrp tablosuna yeni bir kayıt oluşturur. !!
* @version v 1.0 13.08.2016
* @param type $params
* @return array
* @throws \PDOException
*/
public function transferRolesActionPrivilege($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$pdo->beginTransaction();
$opUserId = InfoUsers::getUserId(array('pk' => $params['pk']));
if (\Utill\Dal\Helper::haveRecord($opUserId)) {
$opUserIdValue = $opUserId['resultSet'][0]['user_id'];
$sql = " \n INSERT INTO sys_acl_action_rrp(\n role_id, \n resource_id, \n privilege_id,\n op_user_id\n )\n VALUES (\n " . intval($params['role_id']) . ", \n " . intval($params['resource_id']) . ",\n " . intval($params['privilege_id']) . ",\n " . intval($opUserIdValue) . "\n )";
$statement = $pdo->prepare($sql);
// echo debugPDO($sql, $params);
$result = $statement->execute();
$insertID = $pdo->lastInsertId('sys_acl_action_rrp_id_seq');
$errorInfo = $statement->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
$pdo->commit();
return array("found" => true, "errorInfo" => $errorInfo, "lastInsertId" => $insertID);
} else {
$errorInfo = '23502';
// 23502 not_null_violation
$errorInfoColumn = 'pk';
$pdo->rollback();
return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn);
}
} catch (\PDOException $e) {
$pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* @author Okan CIRAN
* @ 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
* @ 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());
}
}
/**
*
* parametre olarak gelen array deki 'id' li kaydın, info_firm_keys tablosundaki private key ve value değerlerini oluşturur !!
* @author Okan CIRAN
* @version v 1.0 17.03.2016
* @param array $params
* @return array
* @throws \PDOException
*/
public function setNetworkKey($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$pdo->beginTransaction();
$CountryCode = NULL;
$CountryCodeValue = 'TR';
if (isset($params['country_id']) && $params['country_id'] != "") {
$CountryCode = SysCountrys::getCountryCode(array('country_id' => $params['country_id']));
if (\Utill\Dal\Helper::haveRecord($CountryCode)) {
$CountryCodeValue = $CountryCode['resultSet'][0]['country_code'];
}
}
$statement = $pdo->prepare("\n UPDATE info_firm_keys\n SET \n network_key = CONCAT('" . $CountryCodeValue . "',ostim_id_generator())\n WHERE \n firm_id = :firm_id");
$statement->bindValue(':firm_id', $params['firm_id'], \PDO::PARAM_INT);
$update = $statement->execute();
$affectedRows = $statement->rowCount();
$errorInfo = $statement->errorInfo();
$pdo->commit();
return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows);
} catch (\PDOException $e) {
$pdo->rollback();
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());
}
}
/**
* 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());
}
}
/**
* @author Okan CIRAN
* @ Quest için npk lı firmanın danısman tarafından onaylanmış kayıtların sayısını döndürür !!
* @version v 1.0 30.05.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillFirmSectorNpkQuestRtc($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'];
$sql = " \n SELECT \n COUNT(a.id) AS count \n FROM info_firm_sectoral a\n INNER JOIN info_users u ON u.id = a.op_user_id\n INNER JOIN info_firm_profile fp ON fp.act_parent_id = a.firm_id AND fp.cons_allow_id=2 AND fp.language_parent_id =0\n INNER JOIN sys_language l ON l.id = fp.language_id AND l.deleted =0 AND l.active =0 \n INNER JOIN info_firm_keys ifk ON fp.act_parent_id = ifk.firm_id \n\t\t INNER JOIN sys_sectors ss ON ss.id = a.sector_id AND ss.deleted =0 AND ss.active =0 AND ss.language_parent_id =0 \n\t\t WHERE \n a.firm_id = " . intval($firmIdValue) . " AND\n a.cons_allow_id =2 AND\n\t\t\ta.profile_public=0 \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());
}
}
/**
* @author Okan CIRAN
* @ firman ın kullanıcılarının socialmedia bilgilerinin sayısını döndürür !!
* @version v 1.0 21.04.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillCompanyUsersSocialMediaNpkRtc($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$userId = InfoUsers::getUserId(array('pk' => $params['pk']));
if (\Utill\Dal\Helper::haveRecord($userId)) {
$addSql = "";
$networkKey = "-1";
if (isset($params['network_key']) && $params['network_key'] != "") {
$networkKey = $params['network_key'];
}
$sql = " \n SELECT\n count(a.id) AS COUNT \n FROM info_users_socialmedia a\n INNER JOIN info_users_detail iud on iud.root_id = a.user_id AND iud.deleted =0 AND iud.active =0 \t\t\n INNER JOIN sys_language l ON l.id = iud.language_id AND l.deleted =0 AND l.active = 0 \n INNER JOIN sys_socialmedia sm ON sm.id = a.sys_socialmedia_id AND sm.deleted =0 AND sm.active =0 AND sm.language_id = l.id\t\t\n\t\tINNER JOIN info_firm_users ifu ON ifu.user_id = a.user_id AND ifu.active = 0 AND ifu.deleted = 0 AND ifu.language_parent_id =0 \n\t\tINNER JOIN info_firm_profile fp ON fp.act_parent_id = ifu.firm_id AND fp.active = 0 AND fp.deleted = 0 AND fp.language_parent_id =0 \n\t \tINNER JOIN info_firm_keys fk ON fp.act_parent_id = fk.firm_id \n WHERE \t\n a.cons_allow_id=2 AND \n fk.network_key= '" . $networkKey . "' AND\n iud.language_parent_id =0\n ";
$statement = $pdo->prepare($sql);
// echo debugPDO($sql, $params);
$statement->execute();
$result = $statement->fetchAll(\PDO::FETCH_ASSOC);
$errorInfo = $statement->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result);
} else {
$errorInfo = '23502';
// 23502 user_id not_null_violation
$errorInfoColumn = 'pk';
return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn);
}
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
* 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
* @ 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
* @ 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());
}
}
/**
* user interface datagrid fill operation get row count for widget
* @author Okan CIRAN
* @ combobox ı doldurmak için sys_osb tablosundan çekilen kayıtları döndürür !!
* @version v 1.0 09.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillComboBox($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
if (\Utill\Dal\Helper::haveRecord($languageId)) {
$languageIdValue = $languageId['resultSet'][0]['id'];
} else {
$languageIdValue = 647;
}
$countryId = 91;
if (isset($params['country_id']) && $params['country_id'] != "") {
$countryId = $params['country_id'];
}
$addSql = "";
if (isset($params['city_id']) && $params['city_id'] != "") {
$addSql .= " AND a.city_id = " . $params['city_id'];
}
$sql = " \n SELECT \n a.city_id AS id,\n COALESCE(NULLIF(sd.name, ''), a.name_eng) AS name \n FROM sys_osb 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_osb 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 AND a.language_parent_id =0 \n AND a.country_id = " . intval($countryId) . " \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
* @ 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());
}
}
/**
* 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
* sys_acl_rrpmap tablosuna parametre olarak gelen id deki kaydın bilgilerini günceller !!
* @version v 1.0 13-01-2016
* @param type $params
* @return array
* @throws \PDOException
*/
public function update($params = array())
{
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
$pdo->beginTransaction();
$kontrol = $this->haveRecords($params);
if (!\Utill\Dal\Helper::haveRecord($kontrol)) {
$sql = "\n UPDATE sys_acl_rrpmap\n SET \n role_id = :role_id,\n resource_id = :resource_id, \n privilege_id =:privilege_id, \n user_id = :user_id,\n description= :description \n WHERE id = :id ";
$statement = $pdo->prepare($sql);
$statement->bindValue(':id', $params['id'], \PDO::PARAM_INT);
$statement->bindValue(':role_id', $params['role_id'], \PDO::PARAM_INT);
$statement->bindValue(':resource_id', $params['resource_id'], \PDO::PARAM_INT);
$statement->bindValue(':privilege_id', $params['privilege_id'], \PDO::PARAM_INT);
$statement->bindValue(':description', $params['description'], \PDO::PARAM_STR);
$statement->bindValue(':user_id', $params['user_id'], \PDO::PARAM_INT);
// echo debugPDO($sql, $params);
$update = $statement->execute();
$affectedRows = $statement->rowCount();
$errorInfo = $statement->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
$pdo->commit();
return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows);
} else {
$errorInfo = '23505';
$pdo->rollback();
$result = $kontrol;
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '');
}
} catch (\PDOException $e) {
$pdo->rollback();
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
* sys_acl_privilege tablosuna parametre olarak gelen id deki kaydın bilgilerini günceller !!
* @version v 1.0 13-01-2016
* @param type $params
* @return array
* @throws \PDOException
*/
public function update($params = array())
{
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
$pdo->beginTransaction();
$kontrol = $this->haveRecords($params);
if (!\Utill\Dal\Helper::haveRecord($kontrol)) {
$statement = $pdo->prepare("\n UPDATE sys_acl_privilege\n SET \n name = :name, \n icon_class = :icon_class, \n active = :active, \n user_id= :user_id, \n description = :description \n WHERE id = :id");
$statement->bindValue(':id', $params['id'], \PDO::PARAM_INT);
$statement->bindValue(':name', $params['name'], \PDO::PARAM_STR);
$statement->bindValue(':icon_class', $params['icon_class'], \PDO::PARAM_STR);
$statement->bindValue(':active', $params['active'], \PDO::PARAM_INT);
$statement->bindValue(':user_id', $params['user_id'], \PDO::PARAM_INT);
$statement->bindValue(':description', $params['description'], \PDO::PARAM_STR);
$update = $statement->execute();
$affectedRows = $statement->rowCount();
$errorInfo = $statement->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
$pdo->commit();
return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows);
} else {
$errorInfo = '23505';
// 23505 unique_violation
$pdo->rollback();
// $result= $kontrol;
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => '');
}
} catch (\PDOException $e) {
$pdo->rollback();
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
