<?php
namespace WeDevs\Deals;

use \WeDevs\ORM\Eloquent\Facades\DB;
use \WeDevs\Deals\Models\Deal as DealModel;

/**
 * Statistics class
 *
 * @since 1.0.0
 */
class Statistics {

    /**
     * Initializes the class
     *
     * Checks for an existing instance
     * and if it doesn't find one, creates it.
     *
     * @since 1.0.0
     *
     * @return object Class instance
     */
    public static function instance() {
        static $instance = false;

        if ( ! $instance ) {
            $instance = new self();
        }

        return $instance;
    }

    /**
     * Class constructor
     *
     * @since 1.0.0
     *
     * @return void
     */
    public function __construct() {
    }

    /**
     * The main caller function of this stat class
     *
     * @since 1.0.0
     *
     * @param array $filters
     *
     * @return array
     */
    public function get_overview( $filters = [] ) {
        $company_currency = erp_get_currency();

        return [
            'company_currency'          => $company_currency,
            'company_currency_symbol'   => ! empty( $company_currency ) ? erp_get_currency_symbol( $company_currency ) : '',
            'deal_summery'              => $this->get_deal_summery(),
            'deals_progress_by_stages'  => $this->deals_progress_by_stages( $filters['deal_progress'] ),
            'qualified_leads_by_stages' => $this->deals_by_pipeline_stages( $filters['qualified_leads'] ),
            'activity_progress'         => $this->activity_progress( $filters['activity_progress'] ),
            'last_open_deals'           => $this->last_open_deals(),
            'last_won_deals'            => $this->last_won_deals(),
            'won_lost_deals'            => $this->get_filtered_deals_count( $filters['won_lost_deals'] ),
            'lead_generation_chart'     => $this->lead_generation_chart_data( $filters['lead_generation_chart'] ),
            'lead_response_chart'       => $this->lead_response_chart_data( $filters['lead_response_chart'] ),
            'deal_lost_reason_chart'    => $this->deal_lost_reason_data( $filters['deal_lost_reason_chart'] ),
            'follow_up_meetings'        => $this->follow_up_meetings_with_companies( $filters['follow_up_meetings'] ),
            'top_sales_person'          => $this->top_sales_person( $filters['top_sales_person'] ),
        ];
    }

    /**
     * Deal summery overview data
     *
     * @since 1.0.0
     *
     * @return array
     */
    public function get_deal_summery() {
        $summery = [ 'last_month' => [], 'this_month' => [] ];

        $last_month_start   = date( 'Y-m-d 00:00:00', strtotime( 'first day of last month' ) );
        $this_month_start   = date( 'Y-m-d 00:00:00', strtotime( 'first day of this month' ) );
        $this_month_end     = current_time( 'mysql' );

        // last month
        $last_month_deals = DealModel::select( 'value', 'won_at', 'lost_at' )
                                ->where( 'created_at', '>=', $last_month_start )
                                ->where( 'created_at', '<', $this_month_start );

        if ( erp_crm_is_current_user_crm_agent() ) {
            $last_month_deals = $last_month_deals->where( 'created_by', '=', get_current_user_id() );
        }

        $last_month_deals = $last_month_deals->get();

        $summery['last_month']['new'] = [
            'total' => $last_month_deals->count(),
            'value' => $last_month_deals->sum( 'value' )
        ];

        $last_month_won = $last_month_deals->filter( function ( $deal ) {
            return !empty( $deal->won_at );
        } );

        $summery['last_month']['won'] = [
            'total' => $last_month_won->count(),
            'value' => $last_month_won->sum( 'value' )
        ];

        $last_month_lost = $last_month_deals->filter( function ( $deal ) {
            return !empty( $deal->lost_at );
        } );

        $summery['last_month']['lost'] = [
            'total' => $last_month_lost->count(),
            'value' => $last_month_lost->sum( 'value' )
        ];

        // this month
        $this_month_deals = DealModel::select( 'value', 'won_at', 'lost_at' )
                                ->where( 'created_at', '>=', $this_month_start )
                                ->where( 'created_at', '<=', $this_month_end );

        if ( erp_crm_is_current_user_crm_agent() ) {
            $this_month_deals = $this_month_deals->where( 'created_by', '=', get_current_user_id() );
        }

        $this_month_deals = $this_month_deals->get();

        $summery['this_month']['new'] = [
            'total' => $this_month_deals->count(),
            'value' => $this_month_deals->sum( 'value' )
        ];

        $this_month_won = $this_month_deals->filter( function ( $deal ) {
            return !empty( $deal->won_at );
        } );

        $summery['this_month']['won'] = [
            'total' => $this_month_won->count(),
            'value' => $this_month_won->sum( 'value' )
        ];

        $this_month_lost = $this_month_deals->filter( function ( $deal ) {
            return !empty( $deal->lost_at );
        } );

        $summery['this_month']['lost'] = [
            'total' => $this_month_lost->count(),
            'value' => $this_month_lost->sum( 'value' )
        ];

        return $summery;
    }

    /**
     * Deal progress report
     *
     * @since 1.0.0
     *
     * @param array $filters
     *
     * @return array Report of deal progress by pipeline stages
     */
    public function deals_progress_by_stages( $filters = [] ) {
        $prefix = DB::instance()->db->prefix;

        $defaults = [
            'pipeline_id' => 0,
            'agent_id'    => 0,
            'time'        => 'month'
        ];

        $filters = wp_parse_args( $filters, $defaults );

        if ( erp_crm_is_current_user_crm_agent() ) {
            $filters['agent_id'] = get_current_user_id();
        }

        if ( empty( $filters['pipeline_id'] ) ) {
            $pipeline = \WeDevs\Deals\Models\Pipeline::orderBy( 'id', 'asc' )->first();
        } else {
            $pipeline = \WeDevs\Deals\Models\Pipeline::find( $filters['pipeline_id'] );
        }

        $stages = $pipeline->stages()->orderBy( 'order', 'asc' )->get( [ 'id', 'title', 'order' ] )->toArray();

        $stage_ids = wp_list_pluck( $stages, 'id' );

        $history = DB::table( 'erp_crm_deals_stage_history as i' )
                     ->select(
                        'i.stage_id as id',
                        DB::raw( "count(i.deal_id) as deal_count" ),
                        DB::raw( "SUM(d.value) as total_value" ),
                        DB::raw( "SUM( TIMESTAMPDIFF(DAY, o.in, o.out) ) as total_days_to_reach" )
                     )
                     ->leftJoin( "{$prefix}erp_crm_deals_pipeline_stages as s", 'i.stage_id', '=', 's.id' )
                     ->leftJoin( "{$prefix}erp_crm_deals_stage_history as o", function ( $join ) {
                        $join->on( 'i.in', '=', 'o.out' )->where( 'i.deal_id', '=', DB::raw('o.deal_id') );
                     } )
                     ->leftJoin( "{$prefix}erp_crm_deals as d", 'i.deal_id', '=', 'd.id' )
                     ->whereIn( 'i.stage_id', $stage_ids )
                     ->whereNull( 'd.deleted_at' )
                     ->groupBy( 'i.stage_id' );


        if ( !empty( $filters['agent_id'] ) ) {
            $history->where( 'd.owner_id', $filters['agent_id'] );
        }

        switch ( $filters['time'] ) {
            case 'week':
                $first_day = date( 'Y-m-d 00:00:00', strtotime( 'monday this week' ) );
                break;

            case 'year':
                $first_day = date( 'Y-01-01 00:00:00', strtotime( current_time( 'mysql' ) ) );
                break;

            default:
                $first_day = date( 'Y-m-d 00:00:00', strtotime( 'first day of this month' ) );
                break;
        }

        $history->where( 'i.in', '>=', $first_day );

        $history->where( function ( $query ) {
            $query->where( 'o.in', '<=', current_time( 'mysql' ) )
                  ->orWhereNull( 'o.in' );
        } );

        $history = $history->get()->toArray();

        foreach ( $stages as $i => $stage ) {
            $stage_history = array_filter( $history, function ( $item ) use ( $stage ) {
                return absint( $stage['id'] ) === absint( $item->id );
            });

            $stage_history = (array) array_pop( $stage_history );

            if ( empty( $stage_history ) ) {
                $stage_history = [
                    'deal_count' => 0,
                    'total_value' => 0.00,
                    'total_days_to_reach' => 0,
                ];
            }

            $stages[ $i ] = array_merge( $stage, $stage_history );
        }

        return $stages;
    }

    /**
     * Finds deals by pipeline stages
     *
     * @param array $filters
     *
     * @return array $data
     */
    public function deals_by_pipeline_stages( $filters = [] ) {
        global $wpdb;
        $data = [];

        $defaults = [
            'pipeline_id' => 0,
            'date'        => [],
            'status'      => 'open',
        ];

        $filters = wp_parse_args( $filters, $defaults );

        if ( !empty( $filters['pipeline_id'] ) ) {
            $pipeline_id = $filters['pipeline_id'];

        } else {
            $pipeline_id = Helpers::get_pipelines()->first()->id;
        }

        $start = date( 'Y-m-d 00:00:00', strtotime( $filters['date']['start'] ) );
        $end = date( 'Y-m-d 23:59:59', strtotime( $filters['date']['end'] ) );

        $stages = $wpdb->get_results(
                                "SELECT id, title
                                FROM `{$wpdb->prefix}erp_crm_deals_pipeline_stages`
                                WHERE pipeline_id = '{$pipeline_id}'
                                ORDER BY `order`"
                            );

        foreach ( $stages as $stage ) {

            switch ( $filters['status'] ) {
                case 'won':
                    $sql = "SELECT COUNT(id) as deals, SUM(value) as amount
                        FROM `{$wpdb->prefix}erp_crm_deals`
                        WHERE won_at IS NOT NULL
                        AND deleted_at IS NULL
                        AND stage_id = '{$stage->id}'
                        AND won_at >= '{$start}'
                        AND won_at <= '{$end}'";

                    break;

                case 'lost':
                    $sql = "SELECT COUNT(id) as deals, SUM(value) as amount
                        FROM `{$wpdb->prefix}erp_crm_deals`
                        WHERE lost_at IS NOT NULL
                        AND deleted_at IS NULL
                        AND stage_id = '{$stage->id}'
                        AND lost_at >= '{$start}'
                        AND lost_at <= '{$end}'";

                    break;

                case 'deleted':
                    $sql = "SELECT COUNT(id) as deals, SUM(value) as amount
                        FROM `{$wpdb->prefix}erp_crm_deals`
                        WHERE deleted_at IS NOT NULL
                        AND stage_id = '{$stage->id}'
                        AND deleted_at >= '{$start}'
                        AND deleted_at <= '{$end}'";

                    break;

                default:
                    $sql = "SELECT COUNT(id) as deals, SUM(value) as amount
                        FROM `{$wpdb->prefix}erp_crm_deals`
                        WHERE won_at IS NULL
                        AND lost_at IS NULL
                        AND deleted_at IS NULL
                        AND stage_id = '{$stage->id}'
                        AND created_at >= '{$start}'
                        AND created_at <= '{$end}'";

                    break;
            }

            if ( erp_crm_is_current_user_crm_agent() ) {
                $cur_user_id = get_current_user_id();
                $sql .= " AND created_by = {$cur_user_id}";
            }

            $deal = $wpdb->get_results( $sql );

            $data['stage'][] = $stage->title;
            $data['deal'][] = intval( $deal[0]->deals );

            if ( null !== $deal[0]->amount ) {
                $data['amount'][] = intval( $deal[0]->amount );
            } else {
                $data['amount'][] = 0;
            }
        }

        return $data;
    }

    /**
     * Activity progress overview data
     *
     * @since 1.0.0
     *
     * @param array $filters
     *
     * @return array
     */
    public function activity_progress( $filters = [] ) {
        global $wpdb;
        $prefix = DB::instance()->db->prefix;

        $defaults = [
            'agent_id'    => 0,
            'time'        => 'month'
        ];

        $filters = wp_parse_args( $filters, $defaults );

        if ( erp_crm_is_current_user_crm_agent() ) {
            $filters['agent_id'] = get_current_user_id();
        }

        $types = Helpers::get_activity_types();

        $activity_types = [];

        $types->each( function ( $type ) use ( &$activity_types, $filters ) {
            $query = $type->activities()->select( 'done_at' );

            switch ( $filters['time'] ) {
                case 'week':
                    $first_day = date( 'Y-m-d 00:00:00', strtotime( 'monday this week' ) );
                    break;

                case 'year':
                    $first_day = date( 'Y-01-01 00:00:00', strtotime( current_time( 'mysql' ) ) );
                    break;

                default:
                    $first_day = date( 'Y-m-d 00:00:00', strtotime( 'first day of this month' ) );
                    break;
            }

            $last_day = date( 'Y-m-d 23:59:59', strtotime( current_time( 'mysql' ) ) );

            if ( ! empty( $filters['agent_id'] ) ) {
                $query->where( 'assigned_to_id', $filters['agent_id'] );
            }

            $query->where( 'start', '>=', $first_day )->where( 'start', '<=', $last_day );

            $activities = $query->get();

            $total = $activities->count();

            $marked_as_done  = $activities->filter( function ( $activity ) {
                return ! empty( $activity->done_at );
            } );

            $done = $marked_as_done->count();

            $activity_types[] = [
                'id'    => $type->id,
                'title' => $type->title,
                'order' => $type->order,
                'icon'  => $type->icon,
                'total' => $total,
                'done'  => $done,
            ];
        } );

        return $activity_types;
    }

    /**
     * Most recent open deals
     *
     * @since 1.0.0
     *
     * @return object Eloquent Collection object of Deal models
     */
    public function last_open_deals() {
        $deals = DealModel::select( 'id', 'title', 'value', 'created_at' )
                   ->whereNull( 'won_at' )
                   ->whereNull( 'lost_at' );

        if ( erp_crm_is_current_user_crm_agent() ) {
            $deals = $deals->where( 'created_by', '=', get_current_user_id() );
        }

        $deals = $deals->orderBy( 'id', 'desc' )
                         ->take( 5 )
                         ->get();
        return $deals;

    }

    /**
     * Most recent won deals
     *
     * @since 1.0.0
     *
     * @return object Eloquent Collection object of Deal models
     */
    public function last_won_deals() {
        $deals = DealModel::select( 'id', 'title', 'value', 'created_at' )
                   ->whereNotNull( 'won_at' );

        if ( erp_crm_is_current_user_crm_agent() ) {
            $deals = $deals->where( 'created_by', '=', get_current_user_id() );
        }

        $deals = $deals->orderBy( 'id', 'desc' )
                       ->take( 5 )
                       ->get();
        return $deals;

    }

     /**
     * Retrieves deal information by filtering
     *
     * @param array $filters
     *
     * @return array
     */
    public function get_filtered_deals_count( $filters = [] ) {
        global $wpdb;
        $deals = [];

        $defaults = [
            'pipeline_id' => 0,
            'date'        => []
        ];

        $filters = wp_parse_args( $filters, $defaults );

        if ( empty( $filters['pipeline_id'] ) ) {
            $pipeline = \WeDevs\Deals\Models\Pipeline::orderBy( 'id', 'asc' )->first();
        } else {
            $pipeline = \WeDevs\Deals\Models\Pipeline::find( $filters['pipeline_id'] );
        }

        $stages    = $pipeline->stages()->orderBy( 'order', 'asc' )->get( ['id'] )->toArray();

        $stage_ids = wp_list_pluck( $stages, 'id' );

        $start = date( 'Y-m-d 00:00:00', strtotime( $filters['date']['start'] ) );
        $end = date( 'Y-m-d 23:59:59', strtotime( $filters['date']['end'] ) );

        $range = ( strtotime( $end ) - strtotime( $start ) ) / 60 / 60 / 24;
        $ending = $end;

        if ( $range < 0 ) {
            return $deals;
        }

        if ( $range >= 0 && $range <= 10 ) {
            for( $i = 0; $i <= $range; ++ $i ) {
                $start_date[ $i ] = $start;
                $end              = date( 'Y-m-d 23:59:59', strtotime( $start ) );
                $end_date[ $i ]   = $end;
                $start            = date( 'Y-m-d 00:00:00', strtotime( $end . '+1 second' ) );
            }

            $slug = 'week';

        } else if ( $range > 10 && $range <= 77 ) {
            $week = floor( $range / 7 );
            $rem   = fmod( $range, 7 );

            for( $i = 0; $i < $week; ++ $i ) {
                $start_date[ $i ] = $start;
                $end              = date( 'Y-m-d 23:59:59', strtotime( $start . '+6 days' ) );
                $end_date[ $i ]   = $end;
                $start            = date( 'Y-m-d 00:00:00', strtotime( $end . '+1 second' ) );
            }

            if ( $rem ) {
                $start_date[ $week ] = date( 'Y-m-d 00:00:00', strtotime( $end_date[ $week - 1 ] . '+1 second' ) );
                $end_date[ $week ]   = $ending;
            }

            $slug = 'month';

        } else {
            $month = floor( $range / 30 );
           $rem   = fmod( $range, 30 );

            for( $i = 0; $i < $month; ++ $i ) {
                $start_date[ $i ] = $start;
                $end              = date( 'Y-m-t 23:59:59', strtotime( $start ) );
                $end_date[ $i ]   = $end;
                $start            = date( 'Y-m-d 00:00:00', strtotime( $end . '+1 second' ) );
            }

            if ( $rem ) {
                $start_date[ $month ] = date( 'Y-m-d 00:00:00', strtotime( $end_date[ $month - 1 ] . '+1 second' ) );
                $end_date[ $month ]   = $ending;
            }

            $slug = 'year';

        }

        for ( $i = 0; $i < sizeof( $start_date ); ++ $i ) {
            $won = DealModel::select( 'id' )
                                ->whereNotNull( 'won_at' )
                                ->where( 'won_at', '>=', $start_date[ $i ] )
                                ->where( 'won_at', '<=', $end_date[ $i ] )
                                ->whereIn( 'stage_id', $stage_ids );

            if ( erp_crm_is_current_user_crm_agent() ) {
                $won = $won->where( 'created_by', '=', get_current_user_id() );
            }

            $won = $won->get();



            $lost = DealModel::select( 'id' )
                                ->whereNotNull( 'lost_at' )
                                ->where( 'lost_at', '>=', $start_date[ $i ] )
                                ->where( 'lost_at', '<=', $end_date[ $i ] )
                                ->whereIn( 'stage_id', $stage_ids );

            if ( erp_crm_is_current_user_crm_agent() ) {
                $lost = $lost->where( 'created_by', '=', get_current_user_id() );
            }

            $lost = $lost->get();



            $won_deals[ $i ]  = $won->count();
            $lost_deals[ $i ] = $lost->count();
        }

        $deals['won']   = $won_deals;
        $deals['lost']  = $lost_deals;
        $deals['start'] = $start_date;
        $deals['end']   = $end_date;
        $deals['slug']  = $slug;

        return $deals;
    }

    /**
     * Gets data for Leads Generation Chart
     * @param $filters
     * @return array
     */
    public function lead_generation_chart_data( $filters ) {
        global $wpdb;

        $data = [];

        $start_date = $filters['time']['startDate'];
        $end_date = $filters['time']['endDate'];

        $contact_owner = "";

        if ( erp_crm_is_current_user_crm_agent() ) {
            $cur_user_id    = get_current_user_id();
            $contact_owner  = " AND contact_owner={$cur_user_id}";
        }

        $count = $wpdb->get_row("SELECT COUNT('id') AS lead_count FROM {$wpdb->prefix}erp_peoplemeta
                                            WHERE (meta_key = 'source')AND erp_people_id IN (
                                            SELECT id FROM `{$wpdb->prefix}erp_peoples`
                                            WHERE created BETWEEN '{$start_date}' AND '{$end_date}' {$contact_owner})");

        $leads = $wpdb->get_results("SELECT meta_value AS source, COUNT(*) AS count
                                            FROM `{$wpdb->prefix}erp_peoplemeta`
                                            WHERE meta_key='source'
                                            AND erp_people_id IN (
                                            SELECT id FROM `{$wpdb->prefix}erp_peoples`
                                            WHERE created BETWEEN '{$start_date}' AND '{$end_date}' {$contact_owner}
                                        )
                                        GROUP BY meta_value
                                        ORDER BY count DESC");

        $data['lead_count'] = $count->lead_count;
        $data['leads'] = $leads;
        return $data;
    }

    /**
     * Gets data for Leads Response Chart
     * @param $filters
     * @return array
     */
    public function lead_response_chart_data( $filters ) {
        global $wpdb;
        $data = [];

        $start_date = $filters['time']['startDate'];
        $end_date = $filters['time']['endDate'];

        $pipeline_id = $filters['pipeline_id'];

        $created_by = "";

        if ( erp_crm_is_current_user_crm_agent() ) {
            $cur_user_id    = get_current_user_id();
            $created_by     = " AND {$wpdb->prefix}erp_crm_deals.created_by={$cur_user_id}";
        }

        $sql = "SELECT `{$wpdb->prefix}erp_crm_deals`.`created_at` AS deal_created, R.`created_at` AS responsed_at,
                TIMEDIFF(R.`created_at`, `{$wpdb->prefix}erp_crm_deals`.created_at) AS response_dif
                    FROM `{$wpdb->prefix}erp_crm_deals`
                    INNER JOIN (
                        select * from `{$wpdb->prefix}erp_crm_deals_activities` as t1 where not exists ( select 1 from `{$wpdb->prefix}erp_crm_deals_activities` as t2 where t2.deal_id = t1.deal_id and t2.created_at < t1.created_at )
                    ) AS R
                    ON R.`deal_id` = `{$wpdb->prefix}erp_crm_deals`.`id`
                    AND `{$wpdb->prefix}erp_crm_deals`.`stage_id` IN (
                        SELECT id AS stage_ids FROM `{$wpdb->prefix}erp_crm_deals_pipeline_stages` WHERE `{$wpdb->prefix}erp_crm_deals_pipeline_stages`.`pipeline_id` = $pipeline_id
                    )
                    AND `{$wpdb->prefix}erp_crm_deals`.`created_at` BETWEEN '{$start_date}' AND '{$end_date}' {$created_by}
                    ORDER BY response_dif";

        $data['lead_response'] = $wpdb->get_results($sql);
        return $data;
    }

    /**
     * Gets data for lost reason chart
     * @param $filters
     * @return array
     */
    public function deal_lost_reason_data( $filters ) {
        global $wpdb;
        $data = [];

        $start_date = $filters['time']['startDate'];
        $end_date = $filters['time']['endDate'];

        $pipeline_id = $filters['pipeline_id'];

        $created_by_lrs = "";
        $created_by_ors = "";

        if ( erp_crm_is_current_user_crm_agent() ) {
            $cur_user_id    = get_current_user_id();
            $created_by_lrs     = " WHERE {$wpdb->prefix}erp_crm_deals.created_by={$cur_user_id}";
            $created_by_ors     = " AND {$wpdb->prefix}erp_crm_deals.created_by={$cur_user_id}";
        }

        $lost_reason_sql = "SELECT `{$wpdb->prefix}erp_crm_deals`.`lost_reason_id`, COUNT(`{$wpdb->prefix}erp_crm_deals`.`lost_reason_id`) AS lost_reason_count, `{$wpdb->prefix}erp_crm_deals_lost_reasons`.`reason` as reason
                FROM `{$wpdb->prefix}erp_crm_deals`
                INNER JOIN `{$wpdb->prefix}erp_crm_deals_lost_reasons`
                ON `{$wpdb->prefix}erp_crm_deals`.`lost_reason_id` = `{$wpdb->prefix}erp_crm_deals_lost_reasons`.`id`
                AND `{$wpdb->prefix}erp_crm_deals`.`lost_at` BETWEEN '{$start_date}' AND '{$end_date}'
                AND `{$wpdb->prefix}erp_crm_deals`.`stage_id` IN (
                    SELECT id AS stage_ids FROM `{$wpdb->prefix}erp_crm_deals_pipeline_stages` WHERE `{$wpdb->prefix}erp_crm_deals_pipeline_stages`.`pipeline_id` = $pipeline_id
                )
                {$created_by_lrs}
                GROUP BY `{$wpdb->prefix}erp_crm_deals`.`lost_reason_id`";

        $other_reason_sql = "SELECT DISTINCT `{$wpdb->prefix}erp_crm_deals`.`lost_reason` AS reason, COUNT(`{$wpdb->prefix}erp_crm_deals`.`lost_reason`) AS count
                            FROM `{$wpdb->prefix}erp_crm_deals`
                            WHERE `{$wpdb->prefix}erp_crm_deals`.`lost_reason` IS NOT NULL
                            {$created_by_ors}
                            AND `{$wpdb->prefix}erp_crm_deals`.`lost_at` BETWEEN '{$start_date}' AND '{$end_date}'
                            AND `{$wpdb->prefix}erp_crm_deals`.`stage_id` IN (
                                SELECT id AS stage_ids FROM `{$wpdb->prefix}erp_crm_deals_pipeline_stages` WHERE `{$wpdb->prefix}erp_crm_deals_pipeline_stages`.`pipeline_id` = $pipeline_id
                            )
                            GROUP BY `{$wpdb->prefix}erp_crm_deals`.`lost_reason`";
        $data['deal_lost_reason'] = $wpdb->get_results($lost_reason_sql);
        $data['deal_other_reason'] = $wpdb->get_results($other_reason_sql);
        return $data;
    }

    /**
     * Finds number of companies per follow up meeting frequency
     *
     * @param array $filters
     *
     * @return array
     */
    public function follow_up_meetings_with_companies( $filters ) {
        global $wpdb;
        $prefix = $wpdb->prefix;
        $data   = [];

        $defaults = [
            'pipeline_id' => 0,
            'time'        => 'all',
            'date'        => []
        ];

        $filters  = wp_parse_args( $filters, $defaults );

        $pipeline = intval( $filters['pipeline_id'] );

        $end      = erp_current_datetime()->format( 'Y-m-d H:i:s' );

        switch ( $filters['time'] ) {
            case 'week':
                $start = date( 'Y-m-d 00:00:00', strtotime( 'monday this week' ) );
                break;

            case 'month':
                $start = date( 'Y-m-d 00:00:00', strtotime( 'first day of this month' ) );
                break;

            case 'year':
                $start = erp_current_datetime()->format( 'Y-01-01 00:00:00' );
                break;

            case 'custom':
                $start = erp_current_datetime()->modify( $filters['date']['start'] )->format( 'Y-m-d 00:00:00' );
                $end   = erp_current_datetime()->modify( $filters['date']['end'] )->format( 'Y-m-d 23:59:59' );
                break;

            default:
                $start = '1970-01-01 00:00:00';
                break;
        }

        $sql = "SELECT COUNT(crm.comp) AS tot_comp, crm.act AS tot_act, crm.stage AS stage
                FROM (
                    SELECT COUNT(a.id) AS act, a.company_id AS comp, d.stage_id AS stage
                    FROM `{$prefix}erp_crm_deals_activities` AS a
                    LEFT JOIN `{$prefix}erp_crm_deals` AS d
                    ON a.deal_id = d.id
                    WHERE a.done_at IS NOT NULL
                    AND a.done_at >= '{$start}'
                    AND a.done_at <= '{$end}'
                    GROUP BY comp
                ) crm
                WHERE stage IN (
                    SELECT id
                    FROM `{$prefix}erp_crm_deals_pipeline_stages`
                    WHERE pipeline_id = '{$pipeline}'
                )
                GROUP BY tot_act
                ORDER BY tot_act";

        $no_act = $wpdb->get_var(
                            "SELECT COUNT(DISTINCT a.company_id)
                            FROM `{$prefix}erp_crm_deals_activities` AS a
                            LEFT JOIN `{$prefix}erp_crm_deals` AS d
                            ON a.deal_id = d.id
                            WHERE a.company_id NOT IN (
                                SELECT company_id
                                FROM `{$prefix}erp_crm_deals_activities`
                                WHERE `done_at` IS NOT NULL
                            )
                            AND d.stage_id IN (
                                SELECT id
                                FROM `{$prefix}erp_crm_deals_pipeline_stages`
                                WHERE pipeline_id = '{$pipeline}'
                            )
                            AND a.created_at >= '{$start}'
                            AND a.created_at <= '{$end}'"
                        );

        if ( intVal( $no_act ) ) {
            $data['activity'][] = 0;
            $data['company'][]  = intVal( $no_act );
        }

        $results = $wpdb->get_results( $sql );

        foreach( $results as $result ) {
            $data['activity'][] = intVal( $result->tot_act );
            $data['company'][]  = intVal( $result->tot_comp );
        }

        return $data;
    }

    /**
     * Determines the top five sales person based on filters and activities
     *
     * @param array $filters
     *
     * @return array
     */
    public function top_sales_person( $filters ) {
        global $wpdb;
        $prefix = $wpdb->prefix;
        $data   = [];

        $defaults = [
            'pipeline_id' => 0,
            'time'        => 'all',
            'date'        => [],
            'criteria'    => 'win',
        ];

        $filters  = wp_parse_args( $filters, $defaults );

        $pipeline = intval( $filters['pipeline_id'] );

        $end      = erp_current_datetime()->format( 'Y-m-d H:i:s' );

        switch ( $filters['time'] ) {
            case 'week':
                $start = date( 'Y-m-d 00:00:00', strtotime( 'monday this week' ) );
                break;

            case 'month':
                $start = date( 'Y-m-d 00:00:00', strtotime( 'first day of this month' ) );
                break;

            case 'year':
                $start = erp_current_datetime()->format( 'Y-01-01 00:00:00' );
                break;

            case 'custom':
                $start = erp_current_datetime()->modify( $filters['date']['start'] )->format( 'Y-m-d 00:00:00' );
                $end   = erp_current_datetime()->modify( $filters['date']['end'] )->format( 'Y-m-d 23:59:59' );
                break;

            default:
                $start = '1970-01-01 00:00:00';
                break;
        }

        switch ( $filters['criteria'] ) {
            case 'lost':
                $sql = "SELECT owner_id AS user, COUNT(id) AS val
                        FROM `{$prefix}erp_crm_deals`
                        WHERE lost_at IS NOT NULL
                        AND lost_at >= '{$start}'
                        AND lost_at <= '{$end}'
                        AND stage_id IN (
                            SELECT id
                            FROM `{$prefix}erp_crm_deals_pipeline_stages`
                            WHERE pipeline_id = '{$pipeline}'
                        )
                        GROUP BY user
                        ORDER BY val";

                break;

            case 'lead':
                $sql = "SELECT owner_id AS user, COUNT(id) AS val
                        FROM `{$prefix}erp_crm_deals`
                        WHERE won_at IS NULL
                        AND lost_at IS NULL
                        AND created_at >= '{$start}'
                        AND created_at <= '{$end}'
                        AND stage_id IN (
                            SELECT id
                            FROM `{$prefix}erp_crm_deals_pipeline_stages`
                            WHERE pipeline_id = '{$pipeline}'
                        )
                        GROUP BY user
                        ORDER BY val DESC";

                break;

            case 'value':
                $sql = "SELECT owner_id AS user, SUM(value) AS val
                        FROM `{$prefix}erp_crm_deals`
                        WHERE won_at IS NOT NULL
                        AND won_at >= '{$start}'
                        AND won_at <= '{$end}'
                        AND stage_id IN (
                            SELECT id
                            FROM `{$prefix}erp_crm_deals_pipeline_stages`
                            WHERE pipeline_id = '{$pipeline}'
                        )
                        GROUP BY user
                        ORDER BY val DESC";

                break;

            case 'activity':
                $sql = "SELECT a.assigned_to_id AS user, COUNT(a.id) AS val
                    FROM `{$prefix}erp_crm_deals_activities` AS a
                    LEFT JOIN `{$prefix}erp_crm_deals` AS d
                    ON a.deal_id = d.id
                    WHERE a.done_at IS NOT NULL
                    AND a.done_at >= '{$start}'
                    AND a.done_at <= '{$end}'
                    AND d.stage_id IN (
                        SELECT id
                        FROM `{$prefix}erp_crm_deals_pipeline_stages`
                        WHERE pipeline_id = '{$pipeline}'
                    )
                    GROUP BY user
                    ORDER BY val DESC";

                break;

            default:
                $sql = "SELECT owner_id AS user, COUNT(id) AS val
                        FROM `{$prefix}erp_crm_deals`
                        WHERE won_at IS NOT NULL
                        AND won_at >= '{$start}'
                        AND won_at <= '{$end}'
                        AND stage_id IN (
                            SELECT id
                            FROM `{$prefix}erp_crm_deals_pipeline_stages`
                            WHERE pipeline_id = '{$pipeline}'
                        )
                        GROUP BY user
                        ORDER BY val DESC";

                break;
        }

        $results = $wpdb->get_results( $sql );

        foreach ( $results as $result ) {
            $fname           = get_user_meta( intVal( $result->user ), 'first_name', true );
            $lname           = get_user_meta( intVal( $result->user ), 'last_name', true );
            $user            = $fname . ' ' . $lname;

            $data['user'][]  = $user;
            $data['value'][] = intVal( $result->val );
        }

        return $data;
    }
}
