/**
*
* parametre olarak gelen array deki 'id' li kaydın, info_users_details tablosundaki
* active = 0 ve deleted = 0 olan kaydın active alanını 1 yapar !!
* @author Okan CIRAN
* @version v 1.0 29.01.2016
* @param array $params
* @return array
* @throws \PDOException
*/
public function setUserDetailsDisables($params = array())
{
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
// $pdo->beginTransaction();
$sql = "\n UPDATE info_users_detail\n SET\n c_date = timezone('Europe/Istanbul'::text, ('now'::text)::timestamp(0) with time zone) , \n active = 1 \n WHERE root_id = :id AND active = 0 AND deleted = 0 \n ";
$statement = $pdo->prepare($sql);
$statement->bindValue(':id', $params['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]);
}
return array("found" => true, "errorInfo" => $errorInfo, "affectedRowsCount" => $affectedRows);
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}
/**
*
* @author Okan CIRAN
* @ Aktif firma sayısını döndürür !!
* @version v 1.0 05.02.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function getConsultantFirmCount($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 COUNT(id) AS adet ,\n 'Firma Sayısı' AS aciklama\n FROM info_firm_profile \n WHERE deleted =0 AND active =0 AND \n consultant_id = " . intval($opUserIdValue) . "\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
* @ 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());
}
}
/**
* Datagrid fill function used for testing
* user interface datagrid fill operation
* @author Okan CIRAN
* @ Gridi doldurmak için CmpnyEqpmnt tablosundan kayıtları döndürür !!
* @version v 1.0 07.12.2015
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillGrid($args = array())
{
if (isset($args['page']) && $args['page'] != "" && isset($args['rows']) && $args['rows'] != "") {
$offset = (intval($args['page']) - 1) * intval($args['rows']);
$limit = intval($args['rows']);
} else {
$limit = 10;
$offset = 0;
}
$sortArr = array();
$orderArr = array();
if (isset($args['sort']) && $args['sort'] != "") {
$sort = trim($args['sort']);
$sortArr = explode(",", $sort);
if (count($sortArr) === 1) {
$sort = trim($args['sort']);
}
} else {
//$sort = "id";
$sort = "r_date";
}
if (isset($args['order']) && $args['order'] != "") {
$order = trim($args['order']);
$orderArr = explode(",", $order);
//print_r($orderArr);
if (count($orderArr) === 1) {
$order = trim($args['order']);
}
} else {
//$order = "desc";
$order = "ASC";
}
try {
$pdo = $this->getServiceLocator()->get('pgConnectFactory');
$sql = "SELECT \n rp.id as id, \n rp.project_id as project_id, \n rp.user_id as user_id, \n rp.report_jasper_id as report_jasper_id, \n rp.report_type_id as report_type_id, \n rp.r_date as r_date, \n rp.report_name as report_name,\n u.user_name as user_name,\n u.surname as surname,\n u.name as name,\n c.name as company_name,\n c.id as company_id\n FROM r_report_used_configurations rp\n INNER JOIN t_user u ON rp.user_id=u.id\n INNER JOIN t_cmpny c ON rp.company_id=c.id\n ORDER BY " . $sort . " " . "" . $order . " " . "LIMIT " . $pdo->quote($limit) . " " . "OFFSET " . $pdo->quote($offset) . " ";
$statement = $pdo->prepare($sql);
/**
* For debug purposes PDO statement sql
* uses 'Panique' library located in vendor directory
*/
$parameters = array('sort' => $sort, 'order' => $order, 'limit' => $pdo->quote($limit), 'offset' => $pdo->quote($offset));
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
* @ quest için npk lı firmanın danısman tarafından onaylanmış kayıtlarını döndürür !!
* @version v 1.0 24.06.2016
* @param array | null $args
* @return array
* @throws \PDOException
*/
public function fillUserProductsServicesNpkQuest($params = array())
{
try {
$pdo = $this->slimApp->getServiceManager()->get('pgConnectFactory');
$getUserIdValue = NULL;
$getUser = InfoUsers::getUserIdsForNetworkKey(array('network_key' => $params['network_key']));
if (\Utill\Dal\Helper::haveRecord($getUser)) {
$getUserIdValue = $getUser['resultSet'][0]['user_id'];
$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 iu.network_key as unpk,\n\t\t\ta.unspsc_codes_id,\n\t\t\tCOALESCE(NULLIF(sucx.unspsc_name, ''), suc.unspsc_name_eng) AS unspsc_name,\n\t\t\tsuc.unspsc_name_eng,\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 FROM info_users_products_services a\n INNER JOIN info_users_detail iud ON iud.root_id = a.user_id AND iud.active = 0 AND iud.deleted = 0 AND iud.language_parent_id =0\n INNER JOIN sys_language l ON l.id = iud.language_id AND l.deleted =0 AND l.active =0\n LEFT JOIN sys_language lx ON lx.id = " . intval($languageIdValue) . " AND lx.deleted =0 AND lx.active =0\n INNER JOIN info_users iu ON iu.id = a.user_id \n\t\t INNER JOIN sys_unspsc_codes suc ON suc.id = a.unspsc_codes_id AND suc.active = 0 AND suc.deleted = 0 AND suc.language_id =l.id AND suc.language_parent_id =0 \n\t\t LEFT JOIN sys_unspsc_codes sucx ON (sucx.id = suc.id OR sucx.language_parent_id = suc.id) AND sucx.active = 0 AND sucx.deleted = 0 AND sucx.language_id =lx.id\n\t\t WHERE \n a.user_id = " . intval($getUserIdValue) . " AND\n a.cons_allow_id =2 AND \n\t\t\ta.profile_public = 0 \n\t\t ORDER BY unspsc_name\n ";
$statement = $pdo->prepare($sql);
echo debugPDO($sql, $params);
$statement->execute();
$result = $statement->fetchAll(\PDO::FETCH_ASSOC);
$errorInfo = $statement->errorInfo();
if ($errorInfo[0] != "00000" && $errorInfo[1] != NULL && $errorInfo[2] != NULL) {
throw new \PDOException($errorInfo[0]);
}
return array("found" => true, "errorInfo" => $errorInfo, "resultSet" => $result);
} else {
$errorInfo = '23502';
// 23502 not_null_violation
$errorInfoColumn = 'unpk';
return array("found" => false, "errorInfo" => $errorInfo, "resultSet" => '', "errorInfoColumn" => $errorInfoColumn);
}
} catch (\PDOException $e) {
return array("found" => false, "errorInfo" => $e->getMessage());
}
}