<?php 
class Users_model extends CI_Model {
    public function __construct()
    {
            parent::__construct();
    }
    public function getExpiredMembers()
    {
        $res = $this->db->query('select * from (select * from (SELECT shop_orders.payment_method_id,shop_orders.cart_id,shop_orders.time,usr_users.customer_id,shop_orders.created,shop_articles_carts.article_id,`usr_users`.`id` as `user_id`,`usr_users`.`email` as `email`, `usr_users`.`cc_ref_id`, `shop_articles`.`userstatus` FROM `shop_articles` INNER JOIN `shop_articles_carts` ON `shop_articles_carts`.`article_id` = `shop_articles`.`id` INNER JOIN `shop_carts` ON `shop_carts`.`id` = `shop_articles_carts`.`cart_id` INNER JOIN `usr_users` ON `usr_users`.`id` = `shop_carts`.`user_id` INNER JOIN `shop_orders` ON `shop_orders`.`cart_id` = `shop_carts`.`id` WHERE ( `shop_articles_carts`.`cancelled` != 1 OR `shop_articles_carts`.`cancelled` IS NULL ) AND `shop_orders`.`payed` = 1 AND time > 1 ORDER BY `usr_users`.`id` DESC, `shop_carts`.`created` DESC) as a GROUP BY a.user_id ORDER BY a.user_id DESC) as b where b.created <('.time().' - (time * 86400)) AND article_id in (5,4) order by b.created DESC');
         $result = $res->result_array();
        return $result;    
    }
    public function getMembers()
    {
        $res = $this->db->query('select * from (select * from (SELECT shop_orders.created,`usr_users`.`id` as `user_id`,`usr_users`.`email` as `email`, `usr_users`.`cc_ref_id`, `shop_articles`.`userstatus` FROM `shop_articles` INNER JOIN `shop_articles_carts` ON `shop_articles_carts`.`article_id` = `shop_articles`.`id` INNER JOIN `shop_carts` ON `shop_carts`.`id` = `shop_articles_carts`.`cart_id` INNER JOIN `usr_users` ON `usr_users`.`id` = `shop_carts`.`user_id` INNER JOIN `shop_orders` ON `shop_orders`.`cart_id` = `shop_carts`.`id` WHERE  `shop_articles`.`reoccuring` IS NOT NULL AND `shop_articles`.`reoccuring` >0 AND ( `shop_articles_carts`.`cancelled` != 1 OR `shop_articles_carts`.`cancelled` IS NULL ) AND `shop_orders`.`payed` = 1 ORDER BY `usr_users`.`id` DESC, `shop_carts`.`created` DESC) as a GROUP BY a.user_id ORDER BY a.user_id DESC) as b where b.created > ('.time().' - (365 * 86400)) order by b.user_id');
        $result = $res->result_array();
        return $result; 
    }
    public function getMembershipDetails($userId='')
    {
         
		$res = $this->db->query('SELECT shop_articles_carts.article_id as id,shop_orders.payment_method_id,shop_orders.cart_id,shop_orders.time,usr_users.customer_id,shop_orders.created,shop_articles_carts.article_id,`usr_users`.`id` as `user_id`,`usr_users`.`email` as `email`, `usr_users`.`cc_ref_id`, `shop_articles`.`userstatus`,`usr_users`.`last_login` FROM `shop_articles` INNER JOIN `shop_articles_carts` ON `shop_articles_carts`.`article_id` = `shop_articles`.`id` INNER JOIN `shop_carts` ON `shop_carts`.`id` = `shop_articles_carts`.`cart_id` INNER JOIN `usr_users` ON `usr_users`.`id` = `shop_carts`.`user_id`  INNER JOIN `shop_orders` ON `shop_orders`.`cart_id` = `shop_carts`.`id` WHERE ( `shop_articles_carts`.`cancelled` != 1 OR `shop_articles_carts`.`cancelled` IS NULL ) AND `shop_orders`.`payed` = 1 AND shop_orders.created > ('.time().' - (time * 365 * 86400)) AND article_id in (5,4) AND user_id = '.$userId.' ORDER BY shop_orders.created DESC');

        $result = $res->row_array();
        return $result; 
    }
    public function groupConversation($from_id = '')
    { 
        $res = $this->db->query('select feu_userdetails.username,feu_userdetails.accounttype,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, online_status, msg_messages.created,msg_conversations_users.user_id,msg_messages.text,msg_conversations.id as id from msg_conversations left join msg_messages on msg_conversations.id = msg_messages.conversation_id left join msg_conversations_users on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id =msg_conversations_users.user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where feu_userdetails.user_id > 0  and (from_user_id = '.$from_id.') or (msg_conversations_users.user_id = '.$from_id.') order by msg_messages.created DESC');
        
        $result = $res->row_array();
        return $result; 
    }
    public function conversation($from_id = '', $to_id = '')
    {
        $res = $this->db->query('select msg_messages.image_path,feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.created,msg_messages.text,msg_messages.conversation_id as id from msg_conversations_users join msg_messages on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = msg_messages.from_user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where msg_messages.text is not NULL and (from_user_id = '.$from_id.' and msg_conversations_users.user_id = '.$to_id.') or (from_user_id = '.$to_id.' and msg_conversations_users.user_id = '.$from_id.') order by msg_messages.created ASC');
        $result = $res->result_array();
        return $result; 
    }
    public function allconversation($from_id = '')
    {
        $res = $this->db->query('select latest_read,feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.created,msg_messages.text,msg_conversations.id as id from msg_conversations left join msg_messages on msg_conversations.id = msg_messages.conversation_id left join msg_conversations_users on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = CASE WHEN msg_messages.from_user_id != '.$this->session->userdata('user_id').' THEN msg_messages.from_user_id WHEN msg_messages.from_user_id = '.$this->session->userdata('user_id').' THEN msg_conversations_users.user_id END  left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where ( msg_conversations_users.user_id = '.$from_id.' and latest_read = 0) and latest_read = 0 order by msg_messages.created ASC');
        $result = $res->result_array();
        return $result; 
    }
    public function updateConversation($from_id = '')
    {
        $res = $this->db->query('select msg_messages.conversation_id,feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.created,msg_messages.text,msg_conversations.id as id from msg_conversations left join msg_messages on msg_conversations.id = msg_messages.conversation_id left join msg_conversations_users on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = msg_messages.from_user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where (from_user_id = '.$from_id.' ) or ( msg_conversations_users.user_id = '.$from_id.') and latest_read = 0 order by msg_messages.created ASC');
        $result = $res->result_array();
        if(!empty($result))
        {
            foreach ($result as $value) {
                $resUpdate = $this->db->query('Update msg_conversations_users set latest_read = 1 where conversation_id = '.$value['conversation_id']);
            }
        }
        return 1; 
    }
    public function latestConversation()
    {
        $res = $this->db->query('select * from (select feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.text,msg_messages.conversation_id as id from msg_conversations_users join msg_messages on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = CASE WHEN msg_messages.from_user_id != '.$this->session->userdata('user_id').' THEN msg_messages.from_user_id WHEN msg_messages.from_user_id = '.$this->session->userdata('user_id').' THEN msg_conversations_users.user_id END left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where (from_user_id = '.$this->session->userdata('user_id').') or (msg_conversations_users.user_id = '.$this->session->userdata('user_id').') order by msg_messages.created DESC) a group by username order by created DESC');
        $result = $res->result_array();
        return $result; 
    }
    public function latestConversationByUser($username)
    {
        $res = $this->db->query('select * from (select online_status,feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.text,msg_messages.conversation_id as id from msg_conversations_users join msg_messages on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = CASE WHEN msg_messages.from_user_id != '.$this->session->userdata('user_id').' THEN msg_messages.from_user_id WHEN msg_messages.from_user_id = '.$this->session->userdata('user_id').' THEN msg_conversations_users.user_id END left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where (from_user_id = '.$this->session->userdata('user_id').') or (msg_conversations_users.user_id = '.$this->session->userdata('user_id').') order by msg_messages.created DESC) a where a.username like "%'.$username.'%" group by username order by created DESC limit 0,6');
        $result = $res->result_array();
        return $result; 
    }
    public function number_to_restrict()
    {
        $res = $this->db->query('select * from (select feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.text,msg_messages.conversation_id as id from msg_conversations_users join msg_messages on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = CASE WHEN msg_messages.from_user_id != '.$this->session->userdata('user_id').' THEN msg_messages.from_user_id WHEN msg_messages.from_user_id = '.$this->session->userdata('user_id').' THEN msg_conversations_users.user_id END left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where (msg_conversations_users.user_id = '.$this->session->userdata('user_id').') order by msg_messages.created DESC) a WHERE text!= "" AND username != "" AND YEAR(FROM_UNIXTIME(created)) = '.date('Y').' AND MONTH(FROM_UNIXTIME(created)) = '.date('m').' group by username order by created DESC');
        $result = $res->result_array();
        return $result; 
    }
    public function latestConversationLimit($limit,$page)
    {
        $res = $this->db->query('select * from (select online_status,feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.text,msg_messages.conversation_id as id from msg_conversations_users join msg_messages on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = CASE WHEN msg_messages.from_user_id != '.$this->session->userdata('user_id').' THEN msg_messages.from_user_id WHEN msg_messages.from_user_id = '.$this->session->userdata('user_id').' THEN msg_conversations_users.user_id END left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where (from_user_id = '.$this->session->userdata('user_id').') or (msg_conversations_users.user_id = '.$this->session->userdata('user_id').') order by msg_messages.created DESC) a WHERE username != "" group by username order by created DESC limit '.$page.','.$limit);
        $result = $res->result_array();
        return $result; 
    }
    public function conversationLimit($from_id = '', $to_id = '',$lasttime=0, $limit=5,$page=0)
    {
        if($lasttime>0)
        {
            $res = $this->db->query('select msg_messages.image_path,feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.created,msg_messages.text,msg_messages.conversation_id as id from msg_conversations_users join msg_messages on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = msg_messages.from_user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where ((from_user_id = '.$from_id.' and msg_conversations_users.user_id = '.$to_id.') or (from_user_id = '.$to_id.' and msg_conversations_users.user_id = '.$from_id.')) and msg_messages.created < '.$lasttime.' order by msg_messages.created DESC limit '.$page.','.$limit);
        }
        else
        {
            $res = $this->db->query('select msg_messages.image_path,feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.created,msg_messages.text,msg_messages.conversation_id as id from msg_conversations_users join msg_messages on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = msg_messages.from_user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where (from_user_id = '.$from_id.' and msg_conversations_users.user_id = '.$to_id.') or (from_user_id = '.$to_id.' and msg_conversations_users.user_id = '.$from_id.') order by msg_messages.created DESC limit '.$page.','.$limit);
        }
        $result = $res->result_array();
        return $result; 
    }
    public function conversationCurrentLimit($from_id = '', $to_id = '',$finaltime)
    {
        $res = $this->db->query('select msg_messages.image_path,feu_userdetails.username,sys_files.path, sys_files.filename, sys_files.extension, msg_messages.from_user_id, msg_messages.created,msg_conversations_users.user_id,msg_messages.created,msg_messages.text,msg_messages.conversation_id as id from msg_conversations_users join msg_messages on msg_conversations_users.conversation_id = msg_messages.conversation_id left join feu_userdetails on feu_userdetails.user_id = msg_messages.from_user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id where ((from_user_id = '.$from_id.' and msg_conversations_users.user_id = '.$to_id.') or (from_user_id = '.$to_id.' and msg_conversations_users.user_id = '.$from_id.')) and msg_messages.created > '.$finaltime.' order by msg_messages.created DESC');
        $result = $res->result_array();
        return $result; 
    }
    public function contactlist($page='',$limit='',$types='')
    {
        $qry = 'SELECT *,NULL noticreated FROM feu_relationships left join feu_userdetails on feu_userdetails.user_id = CASE WHEN feu_relationships.user_id_1 = '.$this->session->userdata('user_id').' THEN feu_relationships.user_id_2 WHEN feu_relationships.user_id_2 = '.$this->session->userdata('user_id').' THEN feu_relationships.user_id_1 END left join feu_countries on feu_countries.country_code = feu_userdetails.country left join sys_files on sys_files.id = feu_userdetails.avatar_file_id left join ugal_images_user_privacy on ugal_images_user_privacy.to_user_id = feu_userdetails.user_id where status = "accepted" and feu_relationships.type = "friend" and (username is not NULL OR username != "") and (user_id_1 = '.$this->session->userdata('user_id').' or user_id_2 = '.$this->session->userdata('user_id').') ';
        if($types)
        {
            $qry .= ' and accounttype in ('.$types.')';
        }
        $qry .= ' group by feu_userdetails.user_id order by privacy DESC,username ASC';
        if($page!='' && $limit!='')
        {
            $qry .= ' limit '.$page.','.$limit;
        }
        $res = $this->db->query($qry);
        $result = $res->result_array();
        return $result; 
    }
}