vb_book/app/Models/HomeModel.php

<?php 
namespace App\Models;
use CodeIgniter\Model;
class HomeModel extends Model
{

    public function customers($where) {
        $query = $this->db->table('customers');

        $totalCustomers = $query->where($where)->countAll();
        $activeCustomers = $query->where($where)->countAllResults();
        
        $where['DATE(created_on)'] = date('Y-m-d');
        $todayCustomers = $query->where($where)->countAllResults();

        if ($totalCustomers > 0) {
            $customer['percentage'] = ($activeCustomers / $totalCustomers) * 100;
        } else {
            $customer['percentage'] = 0;
        }
        
        $customer['total'] = $totalCustomers;
        $customer['active'] = $activeCustomers;
        $customer['today'] = $todayCustomers;
        return $customer;
    }

    // public function invoice_dtls() {
    //     if ((int)date('m') <= 3) {
    //         $invoice['financial_year'] = (date('Y')-1) . '-' . date('Y');
    //         $start_date = (date('Y')-1).'-04-01';
    //         $end_date = date('Y').'-03-31';
    //     } else {
    //         $invoice['financial_year'] = date('Y') . '-' . (date('Y') + 1);
    //         $start_date = date('Y').'-04-01';
    //         $end_date = (date('Y')+1).'-03-31';
    //     }
    //     $date_where = ['DATE(created_on)' => date('Y-m-d'),'isactive'=>1];
    //     $date_query = $this->db->table('invoice')->where($date_where)->countAllResults();
    //     $month_where = ['MONTH(created_on)' => date('m'),'isactive'=>1];
    //     $month_query = $this->db->table('invoice')->where($month_where)->countAllResults();
    //     $fin_year_where = ['DATE(created_on) >= ' => $start_date , 'DATE(created_on) <= ' => $end_date,'isactive'=>1];
    //     $fin_year_query = $this->db->table('invoice')->where($fin_year_where)->countAllResults();
    //     $invoice['today'] = $date_query;
    //     $invoice['month'] = $month_query;
    //     $invoice['year'] = $fin_year_query;
    //     return $invoice;
    // }
    public function invoice_dtls() {
        if ((int)date('m') <= 3) {
            $invoice['financial_year'] = (date('Y')-1) . '-' . date('Y');
            $start_date = (date('Y')-1).'-04-01';
            $end_date = date('Y').'-03-31';
        } else {
            $invoice['financial_year'] = date('Y') . '-' . (date('Y') + 1);
            $start_date = date('Y').'-04-01';
            $end_date = (date('Y')+1).'-03-31';
        }
        
        // Today
        $date_where = ['DATE(created_on)' => date('Y-m-d'), 'invoice_type' => 1, 'isactive' => 1];
        $date_query = $this->db->table('invoice')->where($date_where)->where('status','Approved')->countAllResults();
        $date_amount_query = $this->db->table('invoice')->selectSum('total_amount')->where($date_where)->where('status','Approved')->get()->getRowArray();
        $invoice['today'] = [
            'count' => $date_query,
            'total_amount' => $date_amount_query['total_amount'] ?? 0,
        ];
    
        // Current Month
        $month_where = ['MONTH(created_on)' => date('m'), 'invoice_type' => 1, 'isactive' => 1];
        $month_query = $this->db->table('invoice')->where($month_where)->where('status','Approved')->countAllResults();
        $month_amount_query = $this->db->table('invoice')->selectSum('total_amount')->where($month_where)->where('status','Approved')->get()->getRowArray();
        $invoice['month'] = [
            'count' => $month_query,
            'total_amount' => $month_amount_query['total_amount'] ?? 0,
        ];
    
        // Current Financial Year
        $fin_year_where = ['DATE(created_on) >=' => $start_date, 'DATE(created_on) <=' => $end_date, 'invoice_type' => 1, 'isactive' => 1];
        $fin_year_query = $this->db->table('invoice')->where($fin_year_where)->where('status','Approved')->countAllResults();
        $fin_year_amount_query = $this->db->table('invoice')->selectSum('total_amount')->where($fin_year_where)->where('status','Approved')->get()->getRowArray();
        $invoice['year'] = [
            'count' => $fin_year_query,
            'total_amount' => $fin_year_amount_query['total_amount'] ?? 0,
        ];
        
    // print_r($invoice);die;
        return $invoice;
    }
    

    // public function schemes(){
    //     $query = $this->db->table('category AS C')
    //             ->select('C.id,C.name,LEFT(C.name, 1) AS first_letter, COUNT(BC.category_id) AS count')
    //             ->join('book_categories AS BC', 'C.id = BC.category_id', 'left')
    //             ->where(['C.isactive'=>1,'C.business_id'=>2])
    //             ->groupBy('C.id');
    //     $results = $query->get()->getResultArray();
    //     return $results;
    // }

    public function active_schemes()
    {
        $query = $this->db->table('subscription')
            // ->select('S.business_id, BK.title as name, BK.short_code, BK.book_id as id, S.scheme_id, LEFT(BK.title, 1) AS first_letter, COUNT(S.customer_id) AS count, BI.wp_img_url')
            // ->select('COUNT(S.customer_id) AS overall, COUNT(IF(S.isactive = 1, S.customer_id, NULL)) AS activeoverall, COUNT(IF(S.is_renew = 0 AND S.isactive = 1, S.customer_id, NULL)) AS nonrenewal, COUNT(IF(S.is_renew = 1 AND S.isactive = 1, S.customer_id, NULL)) AS renewal, SUM(CASE WHEN S.created_on >= NOW() - INTERVAL 30 DAY THEN 1 ELSE 0 END) AS new_subscribers')
            // ->select('COUNT(IF(S.status = 1 and S.is_renew =0 ,S.customer_id, NULL)) AS active,COUNT(IF(S.status = 0, S.customer_id, NULL)) AS expired,COUNT(IF(S.is_renew > 0  AND S.status = 1, S.customer_id, NULL)) AS renew')
            // ->join('customers AS C', 'C.customer_id = S.customer_id AND C.isactive = 1', 'left')
            // ->join('books AS BK', 'BK.book_id = S.scheme_id', 'left')
            // ->join('book_categories AS BC', 'BC.book_id = S.scheme_id AND BC.isactive = 1', 'left')
            // ->join('category AS CM', 'CM.id = BC.category_id AND C.isactive = 1', 'left')
            // ->join('book_images AS BI', 'BI.book_id = S.scheme_id AND BI.isactive = 1', 'left')
            // ->where(['S.business_id' => 2, 'BK.isactive' => 1])
            // ->groupBy('S.scheme_id');
    // print_r($query);die;
     
    ->select('books.business_id,books.title as name ,scheme_id, books.short_code, count(*) as overall')
    ->join('books','books.book_id = subscription.scheme_id')
    ->where('to_subscription>=CURDATE()')
    ->where('subscription.isactive',1)
    ->groupBy('scheme_id');
        $results = $query->get()->getResultArray();
        return $results;
    }
    

    // public function expiring_membership_list(){
    //     $now = date('Y-m-d');
    //     $last30days = date('Y-m-d', strtotime('+30 days'));    
    //     return $this->db->table('subscription as S')
    //         ->join('customers as C', 'C.customer_id = S.customer_id', 'left')
    //         ->join('books as BK', 'BK.book_id = S.scheme_id', 'left')
    //         ->join('book_categories as BC', 'BC.book_id = S.scheme_id', 'left')
    //         ->join('category as CM', 'CM.id = BC.category_id', 'left')            
    //         ->select('CM.name, CONCAT(C.first_name, " ", C.last_name) as customer_name, C.email as customer_email, C.mobile_no as customer_mobile, S.sub_id, S.scheme_id, S.customer_id, S.business_id, S.to_subscription, S.from_subscription, BK.title,BK.short_code, BK.book_id,DATEDIFF(S.to_subscription, CURDATE()) as countdays,DATE_FORMAT(S.to_subscription, "%d/%m/%Y") as to_subscription_date_format')
    //         ->where('S.to_subscription >=', $now)
    //         ->where('S.to_subscription <=', $last30days)
    //         // ->where('BC.category_id', 5)
    //         ->where('LOWER(CM.name)', strtolower("Membership"))
    //         ->orderBy('S.to_subscription', 'ASC')
    //         ->groupBy('S.sub_id')
    //         ->get()
    //         ->getResultArray();
    // }
    public function expiring_membership_list(){
        $now = date('Y-m-d');
        $last30days = date('Y-m-d', strtotime('+30 days'));    
    
        $membershipList = $this->db->table('subscription as S')
            ->join('customers as C', 'C.customer_id = S.customer_id', 'left')
            ->join('books as BK', 'BK.book_id = S.scheme_id', 'left')
            ->join('book_categories as BC', 'BC.book_id = S.scheme_id', 'left')
            ->join('category as CM', 'CM.id = BC.category_id', 'left')            
            ->select('CM.name,CONCAT_WS(" ", C.first_name, C.last_name) as customer_name, C.email as customer_email, C.mobile_no as customer_mobile, S.sub_id, S.scheme_id, S.customer_id, S.business_id, S.to_subscription, S.from_subscription, BK.title, BK.short_code, BK.book_id, DATEDIFF(S.to_subscription, CURDATE()) as countdays, DATE_FORMAT(S.to_subscription, "%d/%m/%Y") as to_subscription_date_format')
            ->where('S.to_subscription >=', $now)
            ->where('S.to_subscription <=', $last30days)
            ->where('S.isactive', 1)
            ->where('LOWER(CM.name)', strtolower("Membership"))
            ->orderBy('S.to_subscription', 'ASC')
            ->groupBy('S.sub_id')
            ->get()
            ->getResultArray();
            // print_r($membershipList);
    // echo  $this->db->getLastQuery(); die;
        foreach ($membershipList as &$membership) {
            $notifications = $this->getNotifications($membership['customer_id']);
      
            $membership['notifications'] = $notifications;
        }
    
        return $membershipList;
    }
    
    private function getNotifications($customerId) {
        return $this->db->table('notification_campaign as NC')
            ->join('notification_history_parents as NH', 'NH.campaign_id = NC.campaign_id', 'left')
            ->join('notification_history_children as NP', 'NP.fkid = NH.id', 'left')
            ->select('NC.*, NH.*, NP.*')
            ->where('NP.customer_id', $customerId)
            ->get()
            ->getResultArray();
    }

    public function expiring_membership_with_notification_list() {
        $now = date('Y-m-d');
        $last30days = date('Y-m-d', strtotime('+30 days'));    
        $past30days = date('Y-m-d', strtotime('-30 days'));
        $membershipList = $this->db->table('subscription as S')
            ->join('customers as C', 'C.customer_id = S.customer_id', 'left')
            ->join('books as BK', 'BK.book_id = S.scheme_id', 'left')
            ->join('book_categories as BC', 'BC.book_id = S.scheme_id', 'left')
            ->join('category as CM', 'CM.id = BC.category_id', 'left')
            ->join('notification_history_children as NHC', 'NHC.subscription_id = S.sub_id AND NHC.receiving_status = 1 AND NHC.receiving_entity LIKE "%@%"', 'left')
            ->join('notification_history_parents as NHP', 'NHP.id = NHC.fkid', 'left')
            ->select('CM.name, CONCAT_WS(" ", C.first_name, C.last_name) as customer_name, 
                      C.email as customer_email, C.mobile_no as customer_mobile, 
                      S.sub_id, S.scheme_id, S.customer_id, S.business_id, 
                      S.to_subscription, S.from_subscription, BK.title, BK.short_code, BK.book_id, S.membership_id,
                      DATEDIFF(S.to_subscription, CURDATE()) as countdays, 
                      DATE_FORMAT(S.to_subscription, "%d/%m/%Y") as to_subscription_date_format,
                      S.is_renew,
                      MAX( NHC.sent_time ) as email_last_notify_on,(select is_renew from subscription as sub where S.sub_id = sub.is_renew LIMIT 1)as renewed')
            ->where('S.to_subscription >=', $past30days)
            ->where('S.to_subscription <=', $last30days)
            ->where('S.isactive', 1)
            ->where('LOWER(CM.name)', strtolower("Membership"))
            ->orderBy('S.to_subscription', 'ASC')
            ->groupBy('S.sub_id')
            ->get()
            ->getResultArray();
            // echo($this->db->getLastQuery());die();
        return $membershipList;
    }
    
    
    public function book_published_dtls(){

        if ((int)date('m') <= 3) {
            $start_date = (date('Y')-1).'-04-01';
            $end_date = date('Y').'-03-31';
        } else {
            $start_date = date('Y').'-04-01';
            $end_date = (date('Y')+1).'-03-31';
        }

        $time = strtotime(date('Y-m-d'));
        $one_month_ago = date('Y-m-d', strtotime("-1 month", $time));
        $one_month_ago_date = date("F d, Y", strtotime("-1 month", $time));            
        $this_month_today_date = date("F d, Y", $time);
        $this_month_name =date('M,Y', $time);

        $overall_where = ['isactive'=>1];
        $overall_query = $this->db->table('books')->where($overall_where)->countAllResults();
        $date_where = ['DATE(created_on)' => date('Y-m-d'),'isactive'=>1];
        $date_query = $this->db->table('books')->where($date_where)->countAllResults();
        $month_where = ['MONTH(created_on)' => date('m'),'isactive'=>1];
        $month_query = $this->db->table('books')->where($month_where)->countAllResults();
        $fin_year_where = ['DATE(created_on) >= ' => $start_date , 'DATE(created_on) <= ' => $end_date,'isactive'=>1];
        $fin_year_query = $this->db->table('books')->where($fin_year_where)->countAllResults();
            
        $last_one_month_dtls = $this->db->table('books as B')
            ->select("B.book_id, B.title, B.created_on, B.publisher, B.author, B.publication_date, DATE_FORMAT(B.publication_date, '%d/%m/%Y') as publication_date_format, GROUP_CONCAT(CM.name, ',') as categories")
            ->join('book_categories as BC', 'BC.book_id = B.book_id and BC.isactive = 1', 'left')
            ->join('category as CM', 'BC.category_id = CM.id', 'left')
            ->where('B.created_on >=', $one_month_ago)
            ->where('B.created_on <= CURDATE() + INTERVAL 1 DAY')
            ->where('B.isactive', 1)
            ->orderBy('B.publication_date', 'DESC')
            ->groupBy('B.book_id')
            ->get()
            ->getResultArray();

        $book_published['overall'] = $overall_query;
        $book_published['today'] = $date_query;
        $book_published['today_label'] = $this_month_today_date;
        $book_published['month'] = $month_query;
        $book_published['month_label'] = $this_month_name;
        $book_published['year'] = $fin_year_query;
        $book_published['book_published_label'] = $one_month_ago_date." - ".$this_month_today_date;
        $book_published['book_published'] = $last_one_month_dtls;
        
        return $book_published;
    }
    public function bookSales()
    {
        $result = [];

        // Fetch book sales for YTD
        $result['ytd'] = $this->getBookSalesCount('start_of_current_year');

        // Fetch book sales for MTD
        $result['mtd'] = $this->getBookSalesCount('start_of_current_month');

        // Fetch book sales for Today
        $result['today'] = $this->getBookSalesCount('today');
// print_r($result);die;
        return $result;
        
    }

    private function getBookSalesCount($dateFilter)
    {
        return $this->db->table('invoiceitems')
            ->join('invoice', 'invoice.invoice_id = invoiceitems.invoice_id', 'left')
            ->join('books', 'books.book_id = invoiceitems.product', 'left')
            ->select('books.book_id, books.title, COUNT(invoiceitems.invoice_child_id) as sales_count')
            ->where("DATE(invoice.invoice_date) >= '$dateFilter'")
            ->groupBy('books.book_id, books.title')
            ->get()
            ->getResultArray();
            
    }

    // Other existing methods...

}