diff --git a/application/modules/default/models/ProjectClone.php b/application/modules/default/models/ProjectClone.php index 10facc0e9..106c68e2a 100755 --- a/application/modules/default/models/ProjectClone.php +++ b/application/modules/default/models/ProjectClone.php @@ -1,168 +1,171 @@ . **/ class Default_Model_ProjectClone extends Default_Model_DbTable_ProjectClone { public function fetchOrigins($project_id) { $sql = " SELECT c.project_id as project_id_clone ,c.project_id_parent as project_id ,c.external_link ,c.member_id ,c.text ,' ' as catTitle ,(select project_category_id from project p where p.project_id = c.project_id_parent) project_category_id ,(select title from project p where p.project_id = c.project_id_parent) title ,(select image_small from project p where p.project_id = c.project_id_parent) image_small ,(select changed_at from project p where p.project_id = c.project_id_parent) changed_at FROM project_clone c WHERE c.is_deleted = 0 and c.is_valid = 1 and c.project_id = :project_id order by c.created_at desc "; $resultSet = $this->_db->fetchAll($sql, array('project_id' => $project_id)); return $this->generateRowSet($resultSet); // return $resultSet; } public function fetchClones($project_id) { $sql = " SELECT c.project_id as project_id ,c.project_id_parent as project_id_origin ,c.external_link ,c.member_id ,c.text ,' ' as catTitle ,(select project_category_id from project p where p.project_id = c.project_id_parent) project_category_id ,(select title from project p where p.project_id = c.project_id) title ,(select image_small from project p where p.project_id = c.project_id) image_small ,(select changed_at from project p where p.project_id = c.project_id) changed_at FROM project_clone c WHERE c.is_deleted = 0 and c.is_valid = 1 and c.project_id_parent = :project_id order by c.created_at desc "; $resultSet = $this->_db->fetchAll($sql, array('project_id' => $project_id)); return $this->generateRowSet($resultSet); // return $resultSet; } public function fetchParent($project_id) { $sql = " SELECT * FROM project_clone c WHERE c.is_deleted = 0 and c.is_valid = 1 and c.project_id = :project_id "; $resultSet = $this->_db->fetchRow($sql, array('project_id' => $project_id)); return $this->generateRowSet($resultSet); } public function fetchRelatedProducts($project_id) { $sql = " select distinct * from ( SELECT c.project_id as project_id ,c.external_link ,c.member_id ,c.text ,' ' as catTitle ,(select project_category_id from project p where p.project_id = c.project_id_parent) project_category_id ,(select title from project p where p.project_id = c.project_id) title ,(select image_small from project p where p.project_id = c.project_id) image_small ,(select changed_at from project p where p.project_id = c.project_id) changed_at FROM project_clone c + JOIN project p on p.project_id = c.project_id WHERE c.is_deleted = 0 and c.is_valid = 1 and c.project_id_parent = :project_id + AND p.`status` = 100 union SELECT c.project_id as project_id ,c.external_link ,c.member_id ,c.text ,' ' as catTitle ,(select project_category_id from project p where p.project_id = c.project_id_parent) project_category_id ,(select title from project p where p.project_id = c.project_id) title ,(select image_small from project p where p.project_id = c.project_id) image_small ,(select changed_at from project p where p.project_id = c.project_id) changed_at FROM project_clone c - WHERE c.project_id<> :project_id and c.is_deleted = 0 and c.is_valid = 1 and c.project_id_parent in ( + JOIN project p on p.project_id = c.project_id + WHERE c.project_id<> :project_id and c.is_deleted = 0 and c.is_valid = 1 AND p.`status` = 100 and c.project_id_parent in ( select project_id_parent from project_clone c where c.project_id = :project_id and c.is_valid = 1 and c.is_deleted = 0 ) ) a where a.catTitle is not null order by changed_at desc "; $resultSet = $this->_db->fetchAll($sql, array('project_id' => $project_id)); return $this->generateRowSet($resultSet); } public function fetchCredits() { $sql = " SELECT c.project_clone_id ,c.project_id ,c.project_id_parent ,c.external_link ,c.text ,c.member_id as reported_by ,(select username from member m where m.member_id = c.member_id) as reporter_username ,(select profile_image_url from member m where m.member_id = c.member_id) as reporter_profile_image_url ,(select cat_title from stat_projects p where p.project_id = c.project_id) catTitle ,(select title from stat_projects p where p.project_id = c.project_id) title ,(select image_small from stat_projects p where p.project_id = c.project_id) image_small ,(select changed_at from stat_projects p where p.project_id = c.project_id) changed_at ,(select laplace_score from stat_projects p where p.project_id = c.project_id) laplace_score ,(select member_id from stat_projects p where p.project_id = c.project_id) member_id ,(select username from stat_projects p where p.project_id = c.project_id) username ,(select cat_title from stat_projects p where p.project_id = c.project_id_parent) parent_catTitle ,(select title from stat_projects p where p.project_id = c.project_id_parent) parent_title ,(select image_small from stat_projects p where p.project_id = c.project_id_parent) parent_image_small ,(select changed_at from stat_projects p where p.project_id = c.project_id_parent) parent_changed_at ,(select laplace_score from stat_projects p where p.project_id = c.project_id_parent) parent_laplace_score ,(select member_id from stat_projects p where p.project_id = c.project_id_parent) parent_member_id ,(select username from stat_projects p where p.project_id = c.project_id_parent) parent_username FROM project_clone c WHERE c.is_deleted = 0 and c.is_valid = 0 order by c.created_at desc "; $resultSet = $this->_db->fetchAll($sql); return $this->generateRowSet($resultSet); } } \ No newline at end of file