<?php

class Subscriptions {

    protected $info = array();
    private $db;
    //    private $perm;
    private $year_id;
    private $date_time;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->invoice = new Invoice();
        $this->payment = new Payment();
        $this->year_id = Session::getCurrentYearID();
        $this->controller_permission = Session::getPermissionsObject();
        $this->logged_id = smc_session()['user_logged_id'];
        $this->date_time = date('Y-m-d H:i:s');
    }

    public function setData($data) {
        $this->info = $data;
        return $this;
    }

    public function submitSubscriptionSetting() {
        $this->db->query('START TRANSACTION');
        $subscription_id = $this->info['subscription_id'];
        $subscription_details = json_decode($this->info['subscription_details'], true);
        $subscription_details_ids = json_decode($this->info['subscription_details_ids'], true);
        $subscription_detail_cond = !empty($subscription_details_ids) ? ' and detail_id not in(' . implode(',', $subscription_details_ids) . ') ' : '';
        $this->info['currency_round'] = !empty($this->info['currency_round']) ? $this->info['currency_round'] : 0;
        $this->info['category_id'] = !empty($this->info['category_id']) ? $this->info['category_id'] : NULL;
        if ($subscription_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_subscription
            (subscription_name,currency_id,is_ttc,is_active,period,period_unit,bill_start,paid_type,currency_round,round_type,category_id) 
            VALUES(?,?,?,?,?,?,?,?,?,?,?)', [&$this->info['subscription_name'], &$this->info['currency_id'],
                                             &$this->info['is_ttc'], &$this->info['is_active'],
                                             &$this->info['period'], &$this->info['period_unit'],
                                             &$this->info['bill_start'], &$this->info['paid_type'],
                                             &$this->info['currency_round'], &$this->info['round_type'],&$this->info['category_id']]);
            $subscription_id = $this->db->id();
        } else {
            $this->db->prepare_Stmt('Update  tbl_subscription
                                     set    subscription_name = ?,currency_id= ?,
                                            is_ttc=?,is_active = ?,period = ?,period_unit = ?,bill_start = ?, 
                                            paid_type= ?,currency_round = ?,round_type = ?,category_id = ?
                                     where subscription_id= ?', [&$this->info['subscription_name'],
                                                                 &$this->info['currency_id'],
                                                                 &$this->info['is_ttc'], &$this->info['is_active'],
                                                                 &$this->info['period'], &$this->info['period_unit'],
                                                                 &$this->info['bill_start'],
                                                                 &$this->info['paid_type'],
                                                                 &$this->info['currency_round'],
                                                                 &$this->info['round_type'],
                                                                 &$this->info['category_id'],
                                                                 &$subscription_id]);
            $this->db->prepare_Stmt('delete from tbl_subscription_details where subscription_id = ?' . $subscription_detail_cond, [&$subscription_id]);
        }
        foreach ($subscription_details as $details) {
            if ($details['detail_id'] == 0) {
                $this->db->prepare_Stmt('INSERT INTO tbl_subscription_details 
                                    (product_id,type_id,subscription_id,uom_id,depend_previous)
                                     VALUES(?,?,?,?,?)', [&$details['product_id'], &$details['type_id'],
                                                          &$subscription_id, &$details['uom_id'], &$details['depend_previous']]);
                $details['detail_id'] = $this->db->id();
            } else {
                $this->db->prepare_Stmt('update  tbl_subscription_details 
                                    set product_id = ?,type_id = ?,uom_id = ?,depend_previous = ? where detail_id = ? ', [
                                        &$details['product_id'], &$details['type_id'],
                                        &$details['uom_id'], &$details['depend_previous'], &$details['detail_id']]);
                $this->db->prepare_Stmt('delete from  tbl_subscription_details_fees where detail_id = ? ', [&$details['detail_id']]);
            }
            $subscription_details_fees = $details['subscription_details_fees'];
            foreach ($subscription_details_fees as $fees_details) {
                $this->db->prepare_Stmt('INSERT INTO  tbl_subscription_details_fees 
                                    (start,end,price,detail_id,fees_type_id)
                                     VALUES(?,?,?,?,?)', [&$fees_details['start'], &$fees_details['end'], &$fees_details['price'], &$details['detail_id'], &$fees_details['fees_type_id']]);
            }
        }
        $error = $this->db->error();
        //        die(print_r($error));
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function submitSubscriptionDetailsFees() {
        $this->db->query('START TRANSACTION');
        foreach ($this->info['subscription_details_fees'] as $fees_details) {
            $this->db->prepare_Stmt('update  tbl_subscription_details_fees 
                                    set price = ? where detail_fees_id = ? ', [&$fees_details['price'], &$fees_details['detail_fees_id']]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function deleteSubscriptionSetting() {
        $subscription_query = $this->db->query('select * from tbl_customer_subscription where subscription_id = "' . $this->info['subscription_id'] . '" ');
        $subscription_count = $this->db->rowsno($subscription_query);
        if ($subscription_count > 0) {
            return [CommonLanguage::getMessage('unable_delete_subscription')];
        }
        $subscription_fees_query = $this->db->query('select * from tbl_fees_subscription_main where subscription_id = "' . $this->info['subscription_id'] . '" ');
        $subscription_fess_count = $this->db->rowsno($subscription_fees_query);
        if ($subscription_fess_count > 0) {
            return [CommonLanguage::getMessage('unable_delete_subscription')];
        }
        $this->db->prepare_Stmt('Delete from tbl_subscription WHERE subscription_id = ?', [&$this->info['subscription_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function getSubscriptionSettingData($id) {
        $data = array();
        $subscription_main_query = $this->db->query('select * from subscription_list where subscription_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($subscription_main_query);
        $detail_ids_array = array();
        $subscription_details_query = $this->db->query('
                                        select details.*,
                                                uom.UOM_Name,
                                                concat_ws(" - ",products.Product_Code,products.Product_Name) as product_name,
                                                fees_details.entry_id
                                        from tbl_subscription_details details
                                        inner join tbl_stock_products products on products.Product_ID = details.product_id
                                        inner join tbl_stock_uom uom on uom.UOM_ID = details.uom_id
                                        left join tbl_fees_subscription_details fees_details on fees_details.subscription_detail_id  = details.detail_id
                                        where details.subscription_id = "' . $id . '"
        ');
        while ($row = $this->db->assoc($subscription_details_query)) {
            $row['fees_details'] = array();
            $data['details'][$row['detail_id']] = $row;
            $detail_ids_array[] = $row['detail_id'];
        }
        $subcription_details_fees_query = $this->db->query('select * from tbl_subscription_details_fees where detail_id in (' . implode(",", $detail_ids_array) . ')');
        while ($row = $this->db->assoc($subcription_details_fees_query)) {
            $data['details'][$row['detail_id']]['fees_details'][] = $row;
        }
        $data['details'] = !empty($data['details']) ? array_values($data['details']) : array();
        return $data;
    }

    public function checkCustomerSubscription($customer_subscription_id) {
        $query = $this->db->query('select * from tbl_fees_subscription_main where customer_subscription_id = "' . $customer_subscription_id . '" limit 1');
        $row = $this->db->assoc($query);
        return empty($row) ? 0 : 1;
    }

    public function submitCustomerSubscription() {
        $this->db->query('START TRANSACTION');
        $customer_subscription_id = $this->info['customer_subscription_id'];
        if ($customer_subscription_id == 0) {
            $customer_subscripiton = $this->info['cust_sub'];
            foreach($customer_subscripiton as $row){
                $next_due_date = self::getCustomerSubscriptionDueDate($row['subscription_id'], $row['start_date'], $row['bill_depend']);
                $row['project_id'] = empty($row['project_id']) ? NULL : $row['project_id'];
                $row['job_id'] = empty($row['job_id']) ? NULL : $row['job_id'];
                $row['block_id'] = empty($row['block_id']) ? NULL : $row['block_id'];
                $this->db->prepare_Stmt('INSERT INTO tbl_customer_subscription
            (customer_subscription_title,subscription_id,customer_id,is_active,start_date,
            note,bill_depend,next_due_date,job_id,project_id,block_id)
            VALUES(?,?,?,?,?,?,?,?,?,?,?)', [&$row['customer_subscription_title'], &$row['subscription_id'],
                                             &$this->info['customer_id'], &$row['is_active'], &$row['start_date'],
                                             &$row['note'], &$row['bill_depend'], &$next_due_date,
                                             &$row['job_id'], &$row['project_id'],&$row['block_id']]);
            }
        } else {
            $next_due_date = self::getCustomerSubscriptionDueDate($this->info['subscription_id'], $this->info['start_date'], $this->info['bill_depend']);
            $this->info['project_id'] = empty($this->info['project_id']) ? NULL : $this->info['project_id'];
            $this->info['job_id'] = empty($this->info['job_id']) ? NULL : $this->info['job_id'];
            $this->info['block_id'] = empty($this->info['block_id']) ? NULL : $this->info['block_id'];
            $next_due_date = $this->info['is_disabled'] == 1 ? $this->info['next_due_date'] : $next_due_date;
            $this->db->prepare_Stmt('Update  tbl_customer_subscription
                                     set   customer_subscription_title = ?, subscription_id = ?,customer_id= ?,
                                            is_active=?,start_date = ?,note = ?,bill_depend = ?,next_due_date = ?,
                                            job_id = ?,project_id = ?,block_id = ?
                                     where customer_subscription_id= ?', [&$this->info['customer_subscription_title'],
                                                                          &$this->info['subscription_id'],
                                                                          &$this->info['customer_id'], 
                                                                          &$this->info['is_active'],
                                                                          &$this->info['start_date'],
                                                                          &$this->info['note'], &$this->info['bill_depend'], &$next_due_date,
                                                                          &$this->info['job_id'], &$this->info['project_id'],&$this->info['block_id'],
                                                                          &$customer_subscription_id]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function getCustomerSubscriptionDueDate($subscription_id, $current_date, $bill_depend, $depend_paid_type = null) {
        $subs_query = $this->db->query('select * from tbl_subscription where subscription_id = "' . $subscription_id . '" ');
        $subs_data = $this->db->assoc($subs_query);
        $subs_paid_type = is_null($depend_paid_type) ? $subs_data['paid_type'] : 1;
        $subs_period = $subs_data['period'];
        $subs_period_unit = $subs_data['period_unit'];
        $subs_bill_start = $subs_data['bill_start'];

        if ($subs_period_unit == 1) {
            $week_days = [1 => 'monday', 2 => 'tuesday', 3 => 'wednesday', 4 => 'thursday', 5 => 'friday', 6 => 'saturday', 7 => 'sunday'];
            $bill_depend_date = date('Y-m-d', strtotime(" {$week_days[$subs_bill_start]} this week", strtotime($current_date)));
            if ($bill_depend_date < $current_date) {
                $bill_depend_date = date('Y-m-d', strtotime(" {$week_days[$subs_bill_start]} next week", strtotime($current_date)));
            }
        } else if ($subs_period_unit == 2) {
            $year_month = date('Y-m', strtotime($current_date));
            $bill_depend_date = $year_month . '-' . $subs_bill_start;
            $bill_depend_date = min($bill_depend_date, date('Y-m-t', strtotime($year_month)));
        } else {
            $year = date('Y', strtotime($current_date));
            $month = explode("-", $subs_bill_start)[0];
            $month_year = $year . '-' . $month;
            $bill_depend_date = $year . '-' . $subs_bill_start;
            $bill_depend_date = min(date('Y-m-t', strtotime($month_year)), $bill_depend_date);
        }
        if ($subs_paid_type == 0) {
            return $bill_depend == 1 ? $current_date : $bill_depend_date;
        } else {
            $start = $bill_depend == 1 ? $current_date : $bill_depend_date;
            if ($subs_period_unit == 1) {
                return date("Y-m-d", strtotime($start . " + " . ($subs_period * 7) . " days"));
            } else if ($subs_period_unit == 2) {
                return date('Y-m-d', min(strtotime($start . ' +' . ($subs_period - 1) . ' month last day of next month'), strtotime($start . ' +' . ($subs_period) . ' month')));
            } else {
                return date("Y-m-d", strtotime($start . " {$subs_period} year"));
            }
        }
    }

    public function deleteCustomerSubscription() {
        $customer_subscription_query = $this->db->query('select * from tbl_fees_subscription_main where customer_subscription_id = "' . $this->info['customer_subscription_id'] . '" ');
        $customer_subscription_count = $this->db->rowsno($customer_subscription_query);
        if ($customer_subscription_count > 0) {
            return [CommonLanguage::getMessage('unable_delete_customer_subscription')];
        }
        $this->db->prepare_Stmt('Delete from tbl_customer_subscription WHERE customer_subscription_id = ?', [&$this->info['customer_subscription_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function customerSubscriptionFees() {
        $data = array();
        $condition = '';
        $sub_fees_group = smc_session()['default_setting']['subscription_fees_group_by'];
        if (!empty($this->info['subscription_id']))
            $condition .= ' and sub.subscription_id = "' . $this->info['subscription_id'] . '"';
        if (!empty($this->info['customer_address']))
            $condition .= ' and customers.Customer_Address LIKE "' . $this->info['customer_address'] . '"';
        if (is_numeric($this->info['customer_id']))
            $condition .= ' and customers.Customer_ID = "' . $this->info['customer_id'] . '"';
        $condition .=!empty($this->info['period_unit']) ? ' and sub.period_unit = "' . $this->info['period_unit'] . '"' : ' and 1=2';
        if (!empty($this->info['period_unit'])) {
            if ($this->info['period_unit'] == 1) {
                $from_date = $this->info['week_from_date'];
                $to_date = $this->info['week_to_date'];
            } else if ($this->info['period_unit'] == 2) {
                $from_date = $this->info['month_from_date'];
                $to_date = $this->info['month_to_date'];
            } else {
                $from_date = $this->info['year_from_date'];
                $to_date = $this->info['year_to_date'];
            }
            $condition .= ' and cust_sub.next_due_date between "' . $from_date . '" and "' . $to_date . '"';
        }
        if(!empty($this->info['main_block_id'])){
            $block_ids = CommonFunctions::get_blocks($this->info['main_block_id']);
            $condition .= ' and cust_sub.block_id in ('.$block_ids.') ';
        }else{
            if(!empty(smc_session()['block_ids'])){
                $block_ids_str = '';
                $block_ids = explode(",",smc_session()['block_ids']);
                foreach($block_ids  as $block_id)
                    $block_ids_str .= ($block_ids_str == '' ? '' : ',').CommonFunctions::get_blocks($block_id);
                $condition .= ' and (cust_sub.block_id in ('.$block_ids_str.') or cust_sub.block_id is null)';
            }
        }
        $query = $this->db->query('select '.($sub_fees_group == 0 ? 'sub.subscription_name,customers.Customer_Name,customers.Customer_Address,' : 'customers.Customer_Name,customers.Customer_Address,sub.subscription_name,').'
										  sub.is_ttc,
										  customers.Customer_ID,
										  cust_sub.next_due_date,
                                          cust_sub.customer_subscription_title,
										  products.Product_Name,
										  products.is_service,
										  uom.UOM_Name,
										  sub_details.detail_id,
                                          cust_sub.subscription_id,
                                          cust_sub.customer_subscription_id,
                                          currency.Currency_Symbol,
                                          currency.Currency_ID,
                                          sub_details.depend_previous,
                                          sub_details.type_id,
                                          cust_sub.job_id,
                                          cust_sub.project_id
								   from tbl_customer_subscription cust_sub
								   inner join tbl_customers customers on customers.Customer_ID = cust_sub.customer_id
								   inner join tbl_subscription sub on sub.subscription_id = cust_sub.subscription_id
								   inner join tbl_subscription_details sub_details on sub_details.subscription_id = sub.subscription_id
								   inner join tbl_stock_products products on products.Product_ID = sub_details.product_id
								   inner join tbl_stock_uom uom on uom.UOM_ID = sub_details.uom_id
                                   inner join tbl_common_currency currency on currency.Currency_ID =sub.currency_id
								    where sub.is_active = 1 and cust_sub.is_active = 1 ' . $condition . '
								 order by  '.($sub_fees_group == 0 ? 'cust_sub.subscription_id,customers.Customer_ID,cust_sub.customer_subscription_id' : 'customers.Customer_ID,cust_sub.subscription_id,cust_sub.customer_subscription_id').' 
		');
        while ($row = $this->db->assoc($query)) {
            $row['next_due_date'] = '<span  data-is-tcc = "' . $row['is_ttc'] . '" data-currency-symbol="' . $row['Currency_Symbol'] . '"  data-currency-id="' . $row['Currency_ID'] . '" data-customer-id="' . $row['Customer_ID'] . '" data-customer-name="' . $row['Customer_Name'] . '" data-customer-subscription-id="' . $row['customer_subscription_id'] . '" data-subscription-id="' . $row['subscription_id'] . '" class="main_date" data-job-id="' . $row['job_id'] . '" data-project-id="' . $row['project_id'] . '">' . $row['next_due_date'] . '</span>';
            $subscription_details_type = self::checkSubscriptionDetailsType($row['detail_id']);
            $row['current_product_uom'] = '<span  data-subscription-detail-id="' . $row['detail_id'] . '" class="product_name_uom" data-product-is-service="' . $row['is_service'] . '">' . $row['Product_Name'] . ' - ' . $row['UOM_Name'] . '</span>';
            $row['previous'] = '<span class="previous_balance">' . ($row['depend_previous'] == 0 ? '-- --' : self::getSubscriptionPreviousBalance($row['subscription_id'], $row['detail_id'], $row['customer_subscription_id'])) . '</span>';
            $row['quantity'] = $row['current'] = '-- --';
            if ($this->controller_permission->hasPermission('submit_subscription_fees')) {
                $row['current'] = $row['depend_previous'] == 0 ? '-- --' : '<input type="number" class="form-control" name="current_balance"/>';
                if ($subscription_details_type == 2)
                    $row['quantity'] = '<input type="number" class="form-control" name="quantity" data-type-id="' . $row['type_id'] . '" data-depend-previous="' . $row['depend_previous'] . '"/>';
            }
            unset($row['subscription_id'], $row['Product_Name'], $row['UOM_Name'], $row['customer_subscription_id']);
            unset($row['detail_id'], $row['Currency_Symbol'], $row['Currency_ID'], $row['Customer_ID']);
            unset($row['is_ttc'], $row['depend_previous'], $row['type_id'], $row['job_id'], $row['project_id'], $row['is_service']);
            $data[] = $row;
        }
        return array_values($data);
    }

    public function checkSubscriptionDetailsType($detail_id) {
        $query = $this->db->query('select * from tbl_subscription_details_fees where detail_id = "' . $detail_id . '"');
        $fees_type_fixed = $total_rows = 0;
        while ($row = $this->db->assoc($query)) {
            $row['fees_type_id'] == 1 ? $fees_type_fixed ++ : '';
            $total_rows++;
        }
        if (($total_rows == 1 && $fees_type_fixed == 1) || ($total_rows > 1 && $total_rows == $fees_type_fixed))
            return 1;
        return 2;
    }

    public function getSubscriptionPreviousBalance($subscription_id, $detail_id, $customer_subscription_id) {
        $query = $this->db->query('select fees_main.date_time,fees_details.current_quantity
                                   from tbl_fees_subscription_details fees_details
                                   inner join tbl_fees_subscription_main fees_main using(main_id)
                                   where fees_main.subscription_id = "' . $subscription_id . '"
                                   and   fees_details.subscription_detail_id = "' . $detail_id . '"
                                   and fees_main.customer_subscription_id = "' . $customer_subscription_id . '"
                                   order by fees_main.date_time desc limit 1
                                   ');
        $row = $this->db->assoc($query);
        $previous_balance = !empty($row['current_quantity']) ? $row['current_quantity'] : 0;
        return $previous_balance;
    }

    public function submitCustomerSubscriptionFees() {
        $this->db->query('START TRANSACTION');
        //        echo '<pre>';
        //        print_r($this->info['subscription_fees']);
        //        die('ok');
        $this->invoice->stop_transaction_start_end = true;
        foreach ($this->info['subscription_fees'] as $subscription_fees) {
            $subscription_data = CommonFunctions::CommonView('tbl_subscription', ['subscription_id'], [$subscription_fees['subscription_id']])[0];
            //            print_r($subscription_data);
            $customer_account = Customer::getCustomerAccount($subscription_fees['customer_id'])['Account_ID'];
            $next_due_date = self::getCustomerSubscriptionDueDate($subscription_fees['subscription_id'], $subscription_fees['main_date'], 1, 1);
            $this->db->prepare_Stmt('INSERT INTO tbl_fees_subscription_main
            (customer_subscription_id,main_date,next_date,subscription_id,date_created,created_logged_id,date_time,logged_id) 
            VALUES(?,?,?,?,?,?,?,?)', [&$subscription_fees['customer_subscription_id'], &$subscription_fees['main_date'],
                                       &$next_due_date, &$subscription_fees['subscription_id'],
                                       &$this->date_time, &$this->logged_id, &$this->date_time, &$this->logged_id]);
            $main_id = $this->db->id();
            //            echo '<pre>';
            $products_array = array();
            $invoice_total = $total_vat = $invoice_sub_total = 0;
            foreach ($subscription_fees['subscription_details'] as $details) {
                $product_data = self::getSubscriptionConsumption($details['subscription_detail_id'], $details['quantity'], $subscription_fees['is_ttc']);
                //                print_r($product_data);
                $this->db->prepare_Stmt('INSERT INTO tbl_fees_subscription_details
            (main_id,current_quantity,subscription_detail_id) VALUES(?,?,?)', [&$main_id, &$details['current_quantity'],
                                                                               &$details['subscription_detail_id']]);
                $detail_id = $this->db->id();
                $products_array[] = ['Item_Discount' => 0, 'Discount_Amount' => 0,
                                     'vat_val' => $product_data['vat_val'], 'vat_percent' => $product_data['vat_percent'],
                                     'Item_Quantity' => $product_data['Item_Quantity'], 'Product_ID' => $product_data['Product_ID'],
                                     'UOM_ID' => $product_data['UOM_ID'], 'Item_Price' => $product_data['Item_Price'],
                                     'Product_Total_Price' => $product_data['total_price'],
                                     'subscription_fees_detail_id' => $detail_id, 'is_service' => $details['is_service']];
                $invoice_total += $product_data['total_price'];
                $total_vat += $product_data['vat_val'];
            }
            $subscription_currency_round = $subscription_data['currency_round'];
            $invoice_net_total = ($invoice_total + $total_vat);
            $default_round_type = $subscription_data['round_type'];
            if ($subscription_currency_round > 0) {
                $round_to = $subscription_currency_round;
                $difference = $invoice_net_total % $round_to;
                $final_total = floor($invoice_net_total / $round_to);
                if ($difference > 0) {
                    if (($difference >= ($round_to / 2) && empty($default_round_type)) || $default_round_type == 1) {
                        $final_total++;
                    }
                }
                $final_total = $final_total * $round_to;
            } else {
                $final_total = $invoice_net_total;
            }
            //            $subscription_currency_round = $subscription_data['currency_round'];
            //            if ($subscription_currency_round > 0) {
            //                $round_to = $subscription_currency_round;
            //                $difference = $invoice_total % $round_to;
            //                $final_total = floor($invoice_total / $round_to);
            //                if ($difference >= ($round_to / 2)) {
            //                    $final_total++;
            //                }
            //                $final_total = $final_total * $round_to;
            //            } else {
            //                $final_total = $invoice_total;
            //            }

            $invoice_round = $final_total - $invoice_net_total;
            if ($subscription_data['paid_type'] == 0) {
                $invoice_date = self::getSubscriptionFeeStartDate($subscription_data['period_unit'], $subscription_data['period'], $next_due_date);
            } else {
                $invoice_date = $subscription_fees['main_date'];
            }
            $invoice_sub_total = $invoice_total;
            $this->db->prepare_Stmt('update tbl_customer_subscription set next_due_date = ? where customer_subscription_id = ?', [&$next_due_date, &$subscription_fees['customer_subscription_id']]);
            $data_invoice = ['Card_Amount' => 0, 'Cash_Amount' => 0, 'Check_Amount' => 0,
                             'Card_Account' => NULL, 'Cash_Account' => NULL, 'Check_Account' => NULL,
                             'job_id' => $subscription_fees['job_id'],
                             'project_id' => $subscription_fees['project_id'],
                             'discount_amount' => 0, 'Invoice_Discount' => 0,
                             'shipping' => 0, 'created_logged_id' => $this->logged_id,
                             'Customer_ID' => $subscription_fees['customer_id'],
                             'Account_Code' => 0,
                             'Logged_ID' => $this->logged_id,
                             'Depot_ID' => smc_session()['default_setting']['default_depot'],
                             'Class_ID' => smc_session()['default_setting']['pricing_class_id'],
                             'Invoice_Date' => $invoice_date,
                             'tax_amount' => 0, 'Inv_Type' => 2, 'table_id' => 0, 'shipping_account' => NULL,
                             'Customer_Account' => $customer_account,
                             'Invoice_Currency' => $subscription_fees['currency_id'],
                             'Customer_Name' => $subscription_fees['customer_name'],
                             'CurrencySymbole' => $subscription_fees['currency_symbol'],
                             'Invoice_VAT' => $total_vat,
                             'Invoice_Sub_Total' => $invoice_sub_total,
                             'Invoice_Net_Total' => $invoice_net_total,
                             'currency_round' => $invoice_round,
                             'subscription_fees_id' => $main_id, 'Products' => $products_array
                            ];
            $this->db->prepare_Stmt('update tbl_fees_subscription_main  set currency_round = ?,total_amount = ? where main_id = ?', [&$invoice_round,$invoice_net_total, &$main_id]);
            //            print_r($data_invoice);
            $this->invoice->setData($data_invoice)->insertSalesInvoice();
        }
        ////        foreach($_POST['data'] as $emp){
        //           $this->invoice->setData()->insertSalesInvoice();
        //          
        ////        }
        $error = $this->db->error();
        //        print_r($error);
        //                die('ok');
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function submitCustSubOpenBalance() {
        $cust_sub_used = array();
        $this->db->query('START TRANSACTION');
        $this->info['is_open_balance'] = $this->info['current_balance_type'] == 'open' ? 1 : 2;
        if($this->info['is_open_balance'] == 1){
            foreach ($this->info['subscription_fees'] as $subscription_fees) {
                $cust_sub_used = self::check_cust_sub_used($subscription_fees['customer_subscription_id'], 0);
                if (!empty($cust_sub_used)) {
                    $cust_sub_used[] = $subscription_fees['customer_subscription_id'];
                } else {
                    $cust_sub_open_balance_used = self::check_cust_sub_used($subscription_fees['customer_subscription_id'], 1);
                    if (empty($cust_sub_open_balance_used)) {
                        $this->db->prepare_Stmt('INSERT INTO tbl_fees_subscription_main
            (customer_subscription_id,main_date,next_date,subscription_id,
            is_open_balance,date_created,created_logged_id,date_time,logged_id) 
            VALUES(?,?,?,?,1,?,?,?,?)', [&$subscription_fees['customer_subscription_id'], &$subscription_fees['main_date'],
                                         &$subscription_fees['main_date'], &$subscription_fees['subscription_id'],
                                         &$this->date_time, &$this->logged_id, &$this->date_time, &$this->logged_id]);
                        $main_id = $this->db->id();
                    } else {
                        $this->db->prepare_Stmt('update tbl_fees_subscription_main
            set main_date = ?,next_date = ?,date_time = ?,logged_id = ? where main_id  =?', [&$subscription_fees['main_date'], &$subscription_fees['main_date'],
                                                                                             &$this->date_time, &$this->logged_id, &$cust_sub_open_balance_used['main_id']]);

                        $this->db->prepare_Stmt('delete from tbl_fees_subscription_details where main_id = ?', [&$cust_sub_open_balance_used['main_id']]);
                        $main_id = $cust_sub_open_balance_used['main_id'];
                    }
                    foreach ($subscription_fees['subscription_details'] as $details) {
                        $this->db->prepare_Stmt('INSERT INTO tbl_fees_subscription_details
            (main_id,current_quantity,subscription_detail_id) VALUES(?,?,?)', [&$main_id, &$details['current_quantity'],
                                                                               &$details['subscription_detail_id']]);
                    }
                }
            } 
        }else{
            foreach ($this->info['subscription_fees'] as $subscription_fees) {
                $this->db->prepare_Stmt('INSERT INTO tbl_fees_subscription_main
            (customer_subscription_id,main_date,next_date,subscription_id,
            is_open_balance,date_created,created_logged_id,date_time,logged_id) 
            VALUES(?,?,?,?,2,?,?,?,?)', [
                &$subscription_fees['customer_subscription_id'], &$subscription_fees['main_date'],
                &$subscription_fees['main_date'], &$subscription_fees['subscription_id'],
                &$this->date_time, &$this->logged_id, &$this->date_time, &$this->logged_id]);
                $main_id = $this->db->id();
                foreach ($subscription_fees['subscription_details'] as $details) {
                    $this->db->prepare_Stmt('INSERT INTO tbl_fees_subscription_details
            (main_id,current_quantity,subscription_detail_id) VALUES(?,?,?)', [
                &$main_id, &$details['current_quantity'],&$details['subscription_detail_id']]);
                }
            }
        }

        $error = $this->db->error();
        if (!empty($cust_sub_used))
            $error = [CommonLanguage::getMessage('open_balance_customer_subs_used')];
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function check_cust_sub_used($customer_subscription_id, $balance_type) {
        $row = array();
        $query = $this->db->query('select * from tbl_fees_subscription_main where customer_subscription_id = "' . $customer_subscription_id . '" and is_open_balance = "' . $balance_type . '" limit 1');
        $row = $this->db->assoc($query);
        return $row;
    }

    public function getSubscriptionConsumption($subscription_detail_id, $quantity, $is_ttc) {
        $data = array();
        $main_price = 0;
        $current_quantity = 0;
        $query = $this->db->query('select sub_details.*,
                                          sub_fees_details.start,
                                          sub_fees_details.end,
                                          sub_fees_details.price,
                                          sub_fees_details.fees_type_id,
                                          products.Product_VAT,
                                          products.vat_percent
                                   from tbl_subscription_details_fees sub_fees_details 
                                   inner join tbl_subscription_details  sub_details using(detail_id)
                                   inner join tbl_stock_products products on products.Product_ID = sub_details.product_id
                                   where sub_fees_details.detail_id ="' . $subscription_detail_id . '"
                                   order by sub_fees_details.detail_fees_id asc
                                   ');
        while ($row = $this->db->assoc($query)) {
            $product_id = $row['product_id'];
            $uom_id = $row['uom_id'];
            $vat_percent = $row['vat_percent'];
            $product_vat = $row['Product_VAT'];
            if ($row['type_id'] == 1) {
                $main_price += $row['fees_type_id'] == 1 ? $row['price'] : ($row['price'] * $quantity);
                $current_quantity = $row['fees_type_id'] == 1 ? 1 : $quantity;
            } else if ($row['type_id'] == 2) {
                $quantity = $quantity == 0 ? 1 : $quantity;
                if (($quantity > $row['start'] && $quantity < $row['end']) ||
                    ($quantity > $row['start'] && ($quantity > $row['end'] || $quantity == $row['end']))) {
                    $main_price += $row['fees_type_id'] == 1 ? $row['price'] : ($row['price'] * $quantity);
                }
                $current_quantity = $quantity;
            } else {
                $quantity = $quantity == 0 ? 1 : $quantity;
                if ($quantity > $row['start'] && ($quantity > $row['end'] || $quantity == $row['end'])) {
                    $main_price += $row['fees_type_id'] == 1 ? $row['price'] : ($row['price'] * $quantity);
                } else if (($quantity > $row['start'] && $quantity < $row['end'])) {
                    $difference_quantity = $quantity - $row['start'];
                    $main_price += $row['fees_type_id'] == 1 ? $row['price'] : ($row['price'] * $difference_quantity);
                }
                $current_quantity = $quantity;
            }
        }
        //                echo $main_price.'--<br>';
        $vat_val = $total_price = 0;
        $unit_price = $main_price / $current_quantity;
        //                echo $unit_price.'===<br>';

        if ($product_vat == 1) {
            if ($is_ttc == 0) {
                $vat_val = ($main_price * $vat_percent) / 100;
                $total_price = $main_price;
            } else {
                $unit_price = $unit_price / (1 + ($vat_percent / 100));
                //                echo $main_price.'==='.$vat_percent;
                $vat_val = ($main_price * ($vat_percent / 100)) / (1 + ($vat_percent / 100));
                $total_price = $unit_price * $current_quantity;
            }
        }
        $data = array('Product_ID' => $product_id, 'UOM_ID' => $uom_id,
                      'Item_Price' => $unit_price,
                      'vat_percent' => ($product_vat == 1 ? $vat_percent : 0),
                      'vat_val' => ($product_vat == 1 ? $vat_val : 0),
                      'Item_Quantity' => $current_quantity,
                      'total_price' => ($product_vat == 1 ? $total_price : $main_price));
        return $data;
    }

    public function getSubscriptionsToPrint($main_ids) {
        $data = array();
        $query = $this->db->query('select fees_main.*,
                                        concat_ws("",inv.invoice_prefix,inv.Invoice_ID) as invoice_id,
                                        inv.Invoice_Currency,
                                        inv.Invoice_Net_Total,
                                        inv.currency_round,
                                        customers.Customer_Name,
                                        customers.Customer_Address,
                                        customers.Customer_CR,
                                        currency.Currency_Name,
                                        currency.Currency_Symbol,
                                        currency.Currency_ID,
                                        sub.subscription_name,
                                        sub.period_unit,
                                        sub.period,
                                        sub.paid_type,
                                        cust_sub.customer_subscription_title,
                                        fees_main.discount_amount
                                  from tbl_fees_subscription_main fees_main 
                                  inner join tbl_invoices inv on inv.subscription_fees_id = fees_main.main_id
                                  inner join tbl_customer_subscription cust_sub using (customer_subscription_id)
                                  inner join tbl_customers customers on customers.Customer_ID = cust_sub.customer_id
                                  inner join tbl_common_currency currency on currency.Currency_ID = inv.Invoice_Currency
                                  inner join tbl_subscription sub on sub.subscription_id = fees_main.subscription_id
                                  where fees_main.main_id IN (' . $main_ids . ')
                                   ');
        while ($row = $this->db->assoc($query)) {
            $row['details'] = array();
            $row['period_from_to'] = '';
            $row['discount_amount'] = !empty($row['discount_amount']) ? (CommonFunctions::formatNumber($row['discount_amount']) . ' ' . $row['Currency_Symbol']) : '';
            $row['discount_class'] = empty($row['discount_amount']) ? 'hidden' : '';
            $row['subscription_period_title'] = $row['period_unit'] == 1 ? 'Week' : ($row['period_unit'] == 2 ? 'Month' : 'Year');
            if ($row['paid_type'] == 0) {   // 0 paid before
                $row['period_start_date'] = $row['main_date'];
                $row['period_end_date'] = date('Y-m-d', strtotime($row['next_date'] . ' -1 days'));
            } else {    //1 paid after
                $row['period_end_date'] = date('Y-m-d', strtotime($row['main_date'] . ' -1 days'));
                $row['period_start_date'] = self::getSubscriptionFeeStartDate($row['period_unit'], $row['period'], $row['period_end_date']);
            }
            $row['period_from_to'] = $row['period_start_date'] . ' - ' . $row['period_end_date'];
            $row['total_amount_formatted'] = CommonFunctions::formatNumber($row['Invoice_Net_Total']+$row['currency_round']) . ' ' . $row['Currency_Symbol'];
            $row['net_total_in_lira'] = Forms::num_to_word($row['Invoice_Net_Total']+$row['currency_round'], $row['Currency_ID'], 'ar');
            $data[$row['main_id']] = $row;
        }
        $products_query = $this->db->query('select fees_details.*,
                                                   products.Product_Name,
                                                   uom.UOM_Name,
                                                   inv_details.Item_Quantity_Current,
                                                   inv_details.Product_Net_Price,
                                                   inv_details.Product_Total_Price,
                                                   inv_details.vat_amount,
                                                   subs_details.type_id,
                                                   subs_details.depend_previous
                                            from  tbl_fees_subscription_details fees_details
                                            inner join tbl_subscription_details subs_details on subs_details.detail_id = fees_details.subscription_detail_id
                                            inner join tbl_stock_products products on products.Product_ID =subs_details.product_id
                                            inner join tbl_stock_uom uom on uom.UOM_ID = subs_details.uom_id
                                            inner join tbl_invoice_products_details inv_details on inv_details.subscription_fees_detail_id = fees_details.entry_id
                                            where fees_details.main_id IN (' . $main_ids . ') ');
        while ($row = $this->db->assoc($products_query)) {
            $row['subscription_type_id'] = self::checkSubscriptionDetailsType($row['subscription_detail_id']);
            //            echo $row['subscription_type_id'].'<>>>>><br>';
            $row['other_dont_depend_previous'] = $row['other_depend_previous'] = 'hidden';
            $row['recuring_quantity'] = $row['recuring_fixed'] = 'hidden';
            if ($row['type_id'] == 1 && $row['subscription_type_id'] == 1 && $row['depend_previous'] == 0)
                $row['recuring_fixed'] = '';
            else if ($row['type_id'] == 1 && $row['subscription_type_id'] == 2 && $row['depend_previous'] == 1)
                $row['recuring_quantity'] = '';
            else if ($row['type_id'] != 1 && $row['depend_previous'] == 0)
                $row['other_dont_depend_previous'] = '';
            else if ($row['type_id'] != 1 && $row['depend_previous'] == 1)
                $row['other_depend_previous'] = '';
            $row['unit_price'] = CommonFunctions::formatNumber($row['Product_Net_Price'] + ($row['vat_amount'] / $row['Item_Quantity_Current']));
            $row['total_price_formatted'] = CommonFunctions::formatNumber($row['Product_Total_Price'] + $row['vat_amount']) . ' ' . $data[$row['main_id']]['Currency_Symbol'];
            $row['previous_balance'] = $row['current_quantity'] - $row['Item_Quantity_Current'];
            $data[$row['main_id']]['details'][] = $row;
            $data[$row['main_id']]['details_two'][] = $row;
        }
        //        die('ok');
        return $data;
    }

    public function getSubscriptionFeeStartDate($period_unit, $period, $end_date) {
        if ($period_unit == 1) {    // weekly
            $start_date = date("Y-m-d", strtotime($end_date . " -{$period} weeks "));
        } else if ($period_unit == 2) { // monthly
            $start_date = date("Y-m-d", strtotime($end_date . "-{$period} months"));
            $end_d = date('d', strtotime($end_date));
            $start_d = date('d', strtotime($start_date));
            if ($start_d < $end_d) //in case of 2020-03-30 and need to subtract 1 month
                $start_date = date("Y-m-t", strtotime(substr($start_date, 0, 4) . "-" . (date('m', strtotime($start_date)) - 1) . "-01"));
        } else {
            $start_date = date("Y-m-d", strtotime($end_date . "-{$period} years"));
            $end_m = date('m', strtotime($end_date));
            $start_m = date('m', strtotime($start_date));
            if ($start_m > $end_m) //in case of 2020-02-29 and need to subtract 1 year
                $start_date = date("Y-m-t", strtotime(substr($start_date, 0, 4) . "-{$end_m}-01"));
        }
        return $start_date;
    }

    public function edit_disc_cust_subscription() {
        $this->db->query('START TRANSACTION');


        //        echo '<pre>';die(print_r($this->info));
        $this->invoice->stop_transaction_start_end = true;
        foreach ($this->info['invoices'] as $invoices) {
            $invoice_total = $total_vat = $invoice_sub_total = $main_total_with_vat = 0;
            $products_array = array();
            $query = $this->db->query('select i.*,
                c.Customer_Name,cu.Currency_Symbol
                from tbl_invoices i join tbl_customers c using(Customer_ID)
                join tbl_common_currency cu on cu.Currency_ID = i.Invoice_Currency where Invoice_ID_Auto="' . $invoices['inv_id'] . '"');
            $inv_row = $this->db->assoc($query);
            $customer_account = Customer::getCustomerAccount($inv_row['Customer_ID'])['Account_ID'];
            //            $discount_percent = $invoices['discount_amount']/($inv_row['Invoice_Sub_Total']+$row['Invoice_VAT'])*100;
            //
            $query_detail = $this->db->query('select details.*, products.is_service
            from tbl_invoice_products_details details
            inner join tbl_stock_products products on products.Product_ID = details.Product_ID
            where Invoice_ID_Auto = "' . $invoices['inv_id'] . '"');
            while ($row = $this->db->assoc($query_detail)) {
                $total_price = ($row['Item_Quantity_Current'] * $row['Product_Net_Price']) - $row['Discount_Amount'];
                $vat_amount = $total_price * $row['vat_percent'] / 100;
                $total_with_vat = $total_price + $vat_amount;
                $products_array[] = ['Item_Discount' => $row['Product_Discount'], 'Discount_Amount' => $row['Discount_Amount'],
                                     'vat_val' => $vat_amount, 'vat_percent' => $row['vat_percent'],
                                     'Item_Quantity' => $row['Item_Quantity_Current'], 'Product_ID' => $row['Product_ID'],
                                     'UOM_ID' => $row['UOM_ID'], 'Item_Price' => $row['Product_Net_Price'],
                                     'Product_Total_Price' => $total_price,
                                     'subscription_fees_detail_id' => $row['subscription_fees_detail_id'],
                                     'is_service' => $row['is_service']];
                $main_total_with_vat += $total_with_vat;
                $invoice_total += $total_price;
                $total_vat += $vat_amount;
            }
            $last_net_total = 0;
            $last_vat_total = 0;
            $invoice_discount = ($invoices['discount_amount'] / $main_total_with_vat) * 100;
            //                        echo $invoice_discount.'===';die();

            foreach ($products_array as &$prod) {
                //                echo $prod['Product_Total_Price'].'=='.$invoice_discount.'==='.(100 - $invoice_discount).'=='.((1 - $invoice_discount) / 100).'<br>';
                $prod['Product_Total_Price'] = $prod['Product_Total_Price'] * ((100 - $invoice_discount) / 100);

                $prod['vat_val'] = $prod['Product_Total_Price'] * $prod['vat_percent'] / 100;

                $last_net_total += ($prod['Product_Total_Price'] + $prod['vat_val']);
                $last_vat_total += $prod['vat_val'];
            }
            //            echo "<pre>";die(print_r($products_array));
            $invoice_sub_total = $invoice_total;
            $last_disc_amount = $invoice_sub_total * $invoice_discount / 100;
            $data_invoice = ['Invoice_ID_Auto' => $invoices['inv_id'], 'Card_Amount' => 0, 'Cash_Amount' => 0, 'Check_Amount' => 0,
                             'Card_Account' => NULL, 'Cash_Account' => NULL, 'Check_Account' => NULL, 'job_id' => $inv_row['job_id'], 'project_id' => $inv_row['project_id'],
                             'discount_amount' => $last_disc_amount, 'Invoice_Discount' => $invoice_discount, 'shipping' => 0, 'created_logged_id' => $inv_row['created_logged_id'],
                             'Customer_ID' => $inv_row['Customer_ID'], 'Account_Code' => $inv_row['Account_ID'],
                             'Logged_ID' => $this->logged_id, 'Depot_ID' => $inv_row['Depot_ID'],
                             'Class_ID' => $inv_row['Class_ID'],
                             'Invoice_Date' => $inv_row['Invoice_Date'],
                             'tax_amount' => 0, 'Inv_Type' => 2, 'table_id' => 0, 'shipping_account' => NULL,
                             'Customer_Account' => $customer_account, 'Invoice_Currency' => $inv_row['Invoice_Currency'],
                             'Customer_Name' => $inv_row['Customer_Name'], 'CurrencySymbole' => $inv_row['Currency_Symbol'],
                             'Invoice_VAT' => $last_vat_total, 'Invoice_Sub_Total' => $invoice_sub_total, 'Invoice_Net_Total' => $last_net_total,
                             'currency_round' => $inv_row['currency_round'],
                             'subscription_fees_id' => $invoices['main_id'], 'Products' => $products_array
                            ];
            //            echo"<pre>";
            //            die(print_r($data_invoice));
            $this->invoice->setData($data_invoice)->editInvoice();
            $this->db->prepare_Stmt('update tbl_fees_subscription_main set discount_amount = ?,total_amount = ? where main_id = ?', [&$invoices['discount_amount'],&$last_net_total, &$invoices['main_id']]);
        }
        ////        foreach($_POST['data'] as $emp){
        //           $this->invoice->setData()->insertSalesInvoice();
        //          
        ////        }
        $error = $this->db->error();
        //                print_r($error);
        //                die('ok');
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function getCustomerSupscriptionIdsUsed() {
        $data = array();
        $query = $this->db->query('select distinct(customer_subscription_id) from tbl_fees_subscription_main where is_open_balance = 0 ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row['customer_subscription_id'];
        }
        return $data;
    }

    //    public function getCustomerSubscriptionOpenBalances(){
    //        $data = array();
    //        $condition = '';
    //        $customer_sub_ids_array = self::getCustomerSupscriptionIdsUsed();
    //        if (!empty($this->info['subscription_id']))
    //            $condition .= ' and subscription_id = "' . $this->info['subscription_id'] . '"';
    //        if (is_numeric($this->info['customer_id']))
    //            $condition .= ' and Customer_ID = "' . $this->info['customer_id'] . '"';
    //        if (!empty($this->info['period_unit']))
    //            $condition .= ' and period_unit = "' . $this->info['period_unit'] . '"';
    //        if (!empty($customer_sub_ids_array))
    //            $condition .= ' and customer_subscription_id not in (' . implode(",", $customer_sub_ids_array) . ')';
    //        $query = $this->db->query('select subscription_name,
    //                                           Customer_Name,
    //                                           Customer_Address,
    //                                           customer_subscription_title,
    //                                           product_uom,
    //                                           subscription_id,
    //                                           detail_id,
    //                                           customer_subscription_id,
    //                                           next_due_date
    //                                   from customer_subscription_setting_details 
    //                                   where sub_active_flag = 1 
    //                                   and cust_sub_active_flag = 1 
    //                                   and depend_previous = 1 '.$condition);
    //        while($row= $this->db->assoc($query)){
    //            $current_value = '';
    //            if ($this->info['current_type'] == 2) {
    //                $current_value = self::getSubscriptionPreviousBalance($row['subscription_id'], $row['detail_id'], $row['customer_subscription_id']);
    //            }
    //            $row['open_balance'] = '<input type="number" name="open_balance" class="form-control" data-customer-subscription-id="' . $row['customer_subscription_id'] . '" data-subscription-id="' . $row['subscription_id'] . '" data-subscription-detail-id="' . $row['detail_id'] . '" data-main-date="' . $row['next_due_date'] . '" value="' . $current_value . '"/>'; 
    //            unset($row['subscription_id'],$row['detail_id'],$row['customer_subscription_id'],$row['next_due_date']);
    //            $data[] = $row;
    //        }
    //        return array_values($data);
    //    }
    public function deleteSubscriptionFees() {
        $this->db->query('START TRANSACTION');
        $this->invoice->stop_transaction_start_end = true;
        $invoice_ids_arr = array();
        foreach ($this->info['subscription_fees'] as $subscription_fees) {
            $invoice_ids_arr[] = $subscription_fees['invoice_id'];
            $customer_subscription_id = $subscription_fees['customer_subscription_id'];
            $main_id = $subscription_fees['main_id'];
            $cust_sub_query = $this->db->query('select cust_sub.*,sub.period_unit,sub.period
            from tbl_customer_subscription cust_sub 
            inner join tbl_subscription sub using(subscription_id)
            where cust_sub.customer_subscription_id = "' . $customer_subscription_id . '"');
            $cust_sub_data = $this->db->assoc($cust_sub_query);

            $main_data_query = $this->db->query('select * from tbl_fees_subscription_main where is_open_balance = 0  and main_id <> "' . $main_id . '" and customer_subscription_id = "' . $customer_subscription_id . '"');
            $main_data_result = $this->db->rowsno($main_data_query);
            if ($main_data_result == 0) {
                $next_due_date = self::getCustomerSubscriptionDueDate($cust_sub_data['subscription_id'], $cust_sub_data['start_date'], $cust_sub_data['bill_depend']);
            } else {
                $next_due_date = self::getSubscriptionFeeStartDate($cust_sub_data['period_unit'], $cust_sub_data['period'], $cust_sub_data['next_due_date']);
            }
            $this->db->prepare_Stmt('delete from tbl_fees_subscription_details where main_id = ?', [&$main_id]);
            $this->db->prepare_Stmt('delete from tbl_fees_subscription_main where main_id = ?', [&$main_id]);
            $this->db->prepare_Stmt('update  tbl_customer_subscription set next_due_date = ? where customer_subscription_id = ?', [&$next_due_date, &$customer_subscription_id]);
        }
        $this->invoice->deleteSalesInvoice($invoice_ids_arr);

        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function deleteSubscriptionFeesPayments() {
        $this->db->query('START TRANSACTION');
        $this->payment->stop_transaction_start_end = true;
        foreach ($this->info['main_ids'] as $main_id) {
            $payment_query = $this->db->query('select pay_rec_id from tbl_payment_receipt where subscription_fees_id ="' . $main_id . '"');
            while($row = $this->db->assoc($payment_query)){
                $this->payment->delete_payment_receipt($row['pay_rec_id'], 1);
            }
            $this->db->prepare_Stmt('update  tbl_fees_subscription_main set paid_status = 1,paid_amount = 0 where main_id = ?', [&$main_id]);
        }
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    //    public function getCustomerSubscriptionPreviousDate($end_date, $subs_period_unit,$subs_period) {
    //        if ($subs_period_unit == 1) {
    //            return date("Y-m-d", strtotime($end_date . " - " . ($subs_period * 7) . " days"));
    //        } else if ($subs_period_unit == 2) {
    //            $start_date = date("Y-m-d", strtotime($end_date . "-{$subs_period} months"));
    //            $end_d = date('d', strtotime($end_date));
    //            $start_d = date('d', strtotime($start_date));
    //            if ($start_d < $end_d) //in case of 2020-03-30 and need to subtract 1 month
    //                $start_date = date("Y-m-t", strtotime(substr($start_date, 0, 4) . "-" . (date('m', strtotime($start_date)) - 1) . "-01"));
    //            return $start_date;
    //        } else {
    //            $start_date = date("Y-m-d", strtotime($end_date . "-{$subs_period} years"));
    //            $end_m = date('m', strtotime($end_date));
    //            $start_m = date('m', strtotime($start_date));
    //            if ($start_m > $end_m) //in case of 2020-02-29 and need to subtract 1 year
    //                $start_date = date("Y-m-t", strtotime(substr($start_date, 0, 4) . "-{$end_m}-01"));
    //            return $start_date;
    //        }
    //    }
    public function get_total_subscription_fees(){        
        $cond = '';

        if (empty($this->controller_permission->hasPermission('show_all_subscriptions')) && !empty( smc_session()['user_details']['Employee_ID'])) {
            $Employee_ID = smc_session()['user_details']['Employee_ID'];
            $cond .= ' and  `sales_emp_id`  = "' . $Employee_ID . '"';
        }
        if ($this->controller_permission->hasPermission('show_all_subscriptions') && is_numeric($_GET['salesman_id'])) {
            $Employee_ID = $this->info['salesman_id'];
            $cond .= ' and  `sales_emp_id`  = "' . $Employee_ID . '"';
        }

        if (!empty($this->info['from_paid_date']) && !empty($this->info['to_paid_date'])) {
            $cond .= ' and date_time between "' . $this->info['from_paid_date'] . '" AND  "' . $this->info['to_paid_date'] . '" or date_time like "' . $this->info['from_paid_date'] . '%" ';
        }


        if (!empty($this->info['from_date']) && !empty($this->info['to_date'])) {
            $cond .= ' and Invoice_Date between "'.$this->info['from_date'].'" AND  "'.$this->info['to_date'].'"';
        }
        if (is_numeric($this->info['customer_id'])) {
            $customer_id = $this->info['customer_id'];
            $cond .= ' and Customer_ID = "' . $customer_id . '"';
        }
        if (!empty($this->info['paid_status'])){
            if($_GET['paid_status'] == 4){
                $cond .= ' and paid_status in (1,2)';
            }else{
                $cond .= ' and paid_status ="' . $this->info['paid_status'] . '"';
            }
        }
        //            $cond .= ' and paid_status ="' . $this->info['paid_status'] . '"';  
        if (is_numeric($this->info['paid_type']))
            $cond .= ' and paid_type ="' . $this->info['paid_type'] . '"';
        if (!empty($this->info['subscription_id']))
            $cond .= ' and subscription_id ="' . $this->info['subscription_id'] . '"';

        if(!empty($this->info['category_id'])){
            $cond .= ' and category_id = "'.$this->info['category_id'].'"';
        }
        if(!empty($this->info['main_block_id']) && empty($this->info['block_id'])){
            $block_ids = CommonFunctions::get_blocks($this->info['main_block_id']);
            $cond .= ' and block_id in ('.$block_ids.') ';
        }else{
            if(!empty(smc_session()['block_ids']) && empty($this->info['block_id'])){
                $block_ids_str = '';
                $block_ids = explode(",",smc_session()['block_ids']);
                foreach($block_ids  as $block_id)
                    $block_ids_str .= ($block_ids_str == '' ? '' : ',').CommonFunctions::get_blocks($block_id);
                $cond .= ' and (block_id in ('.$block_ids_str.') or block_id is null) ';
            }
        }
        if(!empty($this->info['block_id'])){
            $block_ids = CommonFunctions::get_blocks($this->info['block_id']);
            $cond .= ' and block_id in ('.$block_ids.')';
        }

        $local_symbol = CurrencyFactory::getLocalCurrencySymbol();

        $query = $this->db->query('select 
                                        Invoice_Date,
                                        sum((Invoice_Net_Total+inv_currency_round)*current_rate) as local_total,
                                        sum((Invoice_Net_Total+inv_currency_round)*second_currency_rate) as second_total,
                                        Customer_ID,
                                        paid_status
                                    from subscription_fees_list 
                                    where current_year_id =  "'.$this->year_id.'"  ' . $cond);
        $row = $this->db->assoc($query);
        $total_sec = smc_session()['default_setting']['activate_second_currency'] == 1 ? '<br/>' . CommonFunctions::formatNumber($row['second_total']) . ' ' .  smc_session()['second_currency']['symbol']: '';

        $total = CommonFunctions::formatNumber($row['local_total']) . ' ' . $local_symbol.' '.$total_sec;
 
        $category_data = '';
        $query = $this->db->query('select 
                                        Invoice_Date,
                                        sum((Invoice_Net_Total+inv_currency_round)*current_rate) as local_total,
                                        sum((Invoice_Net_Total+inv_currency_round)*second_currency_rate) as second_total,
                                        Customer_ID,
                                        paid_status,
                                        category_name
                                    from subscription_fees_list 
                                    where current_year_id =  "'.$this->year_id.'"  ' . $cond.' group by category_id');
        while($row = $this->db->assoc($query)){
            $category_data .= ($category_data == '' ? '' : '<br>').($row['category_name'].':'.CommonFunctions::formatNumber($row['local_total']) . ' ' . $local_symbol);
        }

        return ['total'=>$total,'category_data'=>$category_data];
    }
    public function submit_subscription_category() {
        if ($this->info['category_id'] == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_subscription_category`( `category_name`,`sort_order`) 
                VALUES (?,?) ', [&$this->info['category_name'],&$this->info['sort_order']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_subscription_category SET category_name =?,sort_order =?
              WHERE category_id =? ', [&$this->info['category_name'],&$this->info['sort_order'], &$this->info['category_id']]);
        }
        $error = $this->db->error();
        return array_unique($error);
    }

    public function delete_subscription_category() {

        $query = $this->db->prepare_stmt('select * from tbl_subscription where category_id = ?', [&$this->info['entry_value']]);
        $row_count = $this->db->rowsno($query);
        if ($row_count == 0) {
            $this->db->prepare_Stmt('Delete from tbl_subscription_category where category_id = ?
                                         ', [&$this->info['entry_value']]);
            $error = $this->db->error();
        } else {
            $error = CommonLanguage::getMessage('subscription_category_used');
        }

        return $error;
    }
    public function submit_subscription_block() {
        if ($this->info['block_id'] == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_subscription_block`( `block_name`,`parent_id`) 
                VALUES (?,?) ', [&$this->info['block_name'],&$this->info['parent_id']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_subscription_block SET block_name =?
              WHERE block_id =? ', [&$this->info['block_name'], &$this->info['block_id']]);
        }
        $error = $this->db->error();
        return array_unique($error);
    }

    public function delete_subscription_block() {
        $query = $this->db->prepare_stmt('select * from tbl_customer_subscription where block_id = ?', [&$this->info['entry_value']]);
        $row_count = $this->db->rowsno($query);
        $block_childs = CommonFunctions::get_blocks($this->info['entry_value'],true);
        if ($row_count != 0 || $block_childs != '') {
            $error = CommonLanguage::getMessage('subscription_block_used');
        } else {
            $this->db->prepare_Stmt('Delete from tbl_subscription_block where block_id = ?
                                         ', [&$this->info['entry_value']]);
            $error = $this->db->error();  
        }

        return $error;
    }
    public function get_subscription_block_tree($parent_id = 0) {
        $data = array();
        $query = $this->db->query('select * from tbl_subscription_block where parent_id = "' . $parent_id . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function get_category_fees_header() {
        $header_arr = array();
        $header_arr = [['name' => CommonLanguage::getMessage('customer_name')],
                       ['name'=>CommonLanguage::getMessage('block')],
                       ['name'=>''],
                       ['name'=>CommonLanguage::getMessage('category_name')]
                    ];
       /* 
       because it does not translate to other language i stop this query and add manually       ['name'=>CommonLanguage::getMessage('category_name')]
       $query = $this->db->query('select category_id,category_name as name from tbl_subscription_category
            order by category_id ');
        while ($row = $this->db->assoc($query)) {
            $header_arr[] = $row;
        }*/ 
        $header_arr[] = ['name'=>CommonLanguage::getMessage('customer_total')];
        return $header_arr;
    }

    public function category_fees_report(){
        $cond = '';
        $data = array();
        $header = array('customer_name' => '','block_name'=>'','empty'=>'');
        $footer = array('customer_name' => CommonLanguage::getMessage('total'),'block_name'=>'','empty'=>'');
        if (!empty($this->info['from_date']) && !empty($this->info['to_date'])) {
            $cond .= ' and Invoice_Date between "'.$this->info['from_date'].'" AND  "'.$this->info['to_date'].'"';
        }
        if (is_numeric($this->info['customer_id'])) {
            $customer_id = $this->info['customer_id'];
            $cond .= ' and Customer_ID = "' . $customer_id . '"';
        }
        if (!empty($this->info['paid_status']))
            $cond .= ' and paid_status ="' . $this->info['paid_status'] . '"';  
        //        
        if (is_numeric($this->info['paid_type']))
            $cond .= ' and paid_type ="' . $this->info['paid_type'] . '"';
        //        
        if (!empty($this->info['subscription_id']))
            $cond .= ' and subscription_id ="' . $this->info['subscription_id'] . '"';

        if(!empty($this->info['main_block_id']) && empty($this->info['block_id'])){
            $block_ids = CommonFunctions::get_blocks($this->info['main_block_id']);
            $cond .= ' and block_id in ('.$block_ids.')';
        }else{
            if(!empty(smc_session()['block_ids']) && empty($this->info['block_id'])){
                $block_ids_str = '';
                $block_ids = explode(",",smc_session()['block_ids']);
                foreach($block_ids  as $block_id)
                    $block_ids_str .= ($block_ids_str == '' ? '' : ',').CommonFunctions::get_blocks($block_id);
                $cond .= ' and (block_id in ('.$block_ids_str.') or block_id is null) ';
            }
        } 
        if(!empty($this->info['block_id'])){
            $block_ids = CommonFunctions::get_blocks($this->info['block_id']);
            $cond .= ' and block_id in ('.$block_ids.')';
        }

        $query = $this->db->query('select * from tbl_subscription_category order by category_id' );
        while ($row = $this->db->assoc($query)) {
            $row['header_index'] = count($header);
            $category_arr[$row['category_id']] = $row;
            $header[$row['category_id'] . '_total'] = 0;
            $footer[$row['category_id'].'_total'] = 0;
        }
        $total = 0;
        //        echo '<pre>';
        $query = $this->db->query('select  
                                        sum((Invoice_Net_Total+inv_currency_round)*current_rate) as local_total,
                                        Customer_ID,
                                        category_id,
                                        Customer_Name,
                                        block_id,
                                        block_name
                                   from subscription_fees_list 
                                   where 1=1 '.$cond.'
                                   group by Customer_ID,block_id,category_id 
                                   having local_total > 0');
        while ($row = $this->db->assoc($query)) {
            if(!isset($data[$row['Customer_ID'].','.$row['block_id']])){
                $data[$row['Customer_ID'].','.$row['block_id']][0] = $row['Customer_Name'];
                $data[$row['Customer_ID'].','.$row['block_id']][1] = $row['block_name'];
                $data[$row['Customer_ID'].','.$row['block_id']][2] = '';
                foreach ($category_arr as $cat_id => $cat_row)
                    $data[$row['Customer_ID'].','.$row['block_id']][$cat_row['header_index']] = 0;
                $data[$row['Customer_ID'].','.$row['block_id']]['total_customer'] = 0;
            }
            $cat_indx = $category_arr[$row['category_id']]['header_index'];
            $data[$row['Customer_ID'].','.$row['block_id']][$cat_indx] = CommonFunctions::formatNumber($row['local_total']);
            $data[$row['Customer_ID'].','.$row['block_id']]['total_customer'] += $row['local_total'];

            $footer[$row['category_id'].'_total'] += $row['local_total'];
            $total += $row['local_total'];
        }
        $footer[] = $total;
        $footer = array_values($footer);
        $final_data = array();

        foreach($data as $row){
            $row['total_customer'] = CommonFunctions::formatNumber($row['total_customer']);
            $final_data[] = $row;  
        }
        $final_data = array_values($final_data);

        $footer =  array_map(function($val){
            if(is_numeric($val))
                return CommonFunctions::formatNumber($val);
            else
                return $val;
        }, $footer); 

        return ['body'=>$final_data,'footer'=>$footer];
    }
    public function subscription_block_report(){
        $cond = '';
        if (!empty($this->info['from_date']) && !empty($this->info['to_date'])) {
            $cond .= ' and Invoice_Date between "'.$this->info['from_date'].'" AND  "'.$this->info['to_date'].'"';
        }
        if (is_numeric($this->info['customer_id'])) {
            $customer_id = $this->info['customer_id'];
            $cond .= ' and Customer_ID = "' . $customer_id . '"';
        }
        if (is_numeric($this->info['paid_type']))
            $cond .= ' and paid_type ="' . $this->info['paid_type'] . '"';
        if (!empty($this->info['subscription_id']))
            $cond .= ' and subscription_id ="' . $this->info['subscription_id'] . '"';

        if(!empty($this->info['category_id'])){
            $cond .= ' and category_id = "'.$this->info['category_id'].'"';
        }
        $current_block =0;
        if(!empty($this->info['main_block_id'])){
            $current_block = $this->info['main_block_id'];  
        }
        //        echo '<pre>';

        //        $options = array();
        $data = array();
        $parents_arr = array();
        $blocks_arr = array();
        CommonFunctions::get_block_tree($current_block,$parents_arr,$blocks_arr);
        if(empty($current_block) && !empty(smc_session()['block_ids'])){
            $parents_arr[$current_block]  = explode(",",smc_session()['block_ids']);
        }
        if(!empty($parents_arr[$current_block])){
            foreach($parents_arr[$current_block] as $block_id){
                $current_arr = self::returnBlockBalance($block_id,$cond);
                $this->build_block_trial_arr($data, $block_id, $parents_arr, $blocks_arr,$current_arr);
                //            die('ok1');
            }  
        }
        //        die('okkk');
        $main_total = 0;
        $remaining_total = 0;
        $paid_total = 0;
        $final_data = array();
        foreach($data as $row){
            unset($row['block_id']);
            unset($row['is_parent']);
            if($row['is_included'] == 1){
                $remaining_total += $row['remaining'];
                $paid_total += $row['paid'];
            }
            unset($row['is_included']);
            //            $row['total']= $row['paid']+$row['remaining'];
            $row['paid'] =CommonFunctions::formatNumber($row['paid']);
            $row['remaining'] =CommonFunctions::formatNumber($row['remaining']);
            $row['total'] =CommonFunctions::formatNumber($row['total']);
            $final_data[]= array_values($row); 
        }
        return ['data'=>$final_data,'main_total'=>CommonFunctions::formatNumber($remaining_total+$paid_total),
                'remaining_total'=>CommonFunctions::formatNumber($remaining_total),
                'paid_total'=>CommonFunctions::formatNumber($paid_total)];
    }
    public function build_block_trial_arr(&$data, $block_id, $parents_arr, $blocks_arr,$current_arr) {
        //        echo $block_id.'<br>';
        $job_totals = $this->get_block_group_totals($block_id,$parents_arr, $blocks_arr,$current_arr);
        if (!empty($job_totals)) {
            $data[] = $job_totals;
        }
        if (!empty($parents_arr[$block_id])) {
            foreach ($parents_arr[$block_id] as $child_id) {
                $this->build_block_trial_arr($data, $child_id,$parents_arr, $blocks_arr,$current_arr);
            }
        }
    }
    public function get_block_group_totals($block_id, $parents_arr, $blocks_arr,$current_arr) {
        $total = $paid = $remaining = 0;
        $blocks = array($block_id);
        //        print_r($blocks);
        $this->get_block_childs($block_id, $blocks, $parents_arr);
        $is_parent = false;
        $is_included = 1;
        //        print_r($current_arr);
        if (!empty($parents_arr[$block_id]))
            $is_parent = true;
        foreach ($blocks as $current_id) {
            if (!empty($current_arr[$current_id])) {
                $remaining += $current_arr[$current_id]['remaining'];
                $paid += $current_arr[$current_id]['paid'];
            }
        }
        if($is_parent && empty($current_arr[$block_id]))
            $is_included = 0;
        if($is_parent && (!empty($current_arr[$block_id]['remaining']) || !empty($current_arr[$block_id]['paid']))){
            $remaining = $current_arr[$block_id]['remaining']; 
            $paid = $current_arr[$block_id]['paid'];
        }
        return array(
            "Name" => $blocks_arr[$block_id]['block_name'],
            "block_id" => $block_id,
            'total' => $total,
            'paid' => $paid,
            'remaining' => $remaining,
            "total"=>($paid+$remaining),
            "is_parent" => $is_parent,
            "is_included" => $is_included
        );
    }
    public function get_block_childs($block_id, &$data, $parents_arr, $is_direct = false) {
        if (!empty($parents_arr[$block_id])) {
            foreach ($parents_arr[$block_id] as $child_id) {
                $data[] = $child_id;
                if (!$is_direct) {
                    $this->get_block_childs($child_id, $data, $parents_arr);
                }
            }
        }
    }

    public function returnBlockBalance($block_id,$condition) {
        $block_arr = array();
        if(!empty($block_id)){
            $block_ids = CommonFunctions::get_blocks($block_id);
            if(!empty($block_ids))
                $condition .= ' and block_id in ('.$block_ids.')';
            else
                $condition .= ' and 1=2'; 
        }

        $query = $this->db->query('select  
                                        (Invoice_Net_Total+inv_currency_round)*current_rate as local_total,
                                        Customer_ID,
                                        category_id,
                                        Customer_Name,
                                        block_id,
                                        block_name,
                                        paid_status
                                   from subscription_fees_list 
                                   where 1=1 ' . $condition);
        while ($row = $this->db->assoc($query)) {
            $indx = $row['block_id'];
            if (!isset($block_arr[$indx])) {
                $block_arr[$indx] = array(
                    "total" => 0,
                    "paid" => 0,
                    "remaining" => 0
                );
            }
            if($row['paid_status'] == 1)
                $block_arr[$indx]["remaining"] += $row['local_total'];
            else
                $block_arr[$indx]["paid"] += $row['local_total'];
        }
        return $block_arr;
    }
    public function searchSubscriptionBlocks($term = null, $page = null, $limit= null) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and block_name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        if(array_key_exists('main_block_id',$this->info)){
            if(!empty($this->info['main_block_id'])){
                $block_ids = CommonFunctions::get_blocks($this->info['main_block_id'],true);
                if(!empty($block_ids))
                    $cond .= ' and block_id in ('.$block_ids.')';
                else
                    $cond .= ' and 1=2'; 
            }else{
                $cond .= ' and 1=2'; 
            }
        }else{
            if(!empty($this->info['is_parent'])){
                $cond .= ' and parent_id = 0';   
            }else{
                if(!empty(smc_session()['block_ids'])){
                    $block_ids_str = '';
                    $block_ids = explode(",",smc_session()['block_ids']);
                    foreach($block_ids  as $block_id)
                        $block_ids_str .= ($block_ids_str == '' ? '' : ',').CommonFunctions::get_blocks($block_id,true);
                    if($block_ids_str != '')
                        $cond .= ' and (block_id in ('.$block_ids_str.') or block_id is null) ';
                    else
                        $cond .= ' and 1=2 ';
                }else{
                    $cond .= ' and parent_id <> 0';   
                }
            }

        } 

        $query = $this->db->prepare_Stmt('select 
                                    block_id as id,
                                    block_name as text
                                from 
                                    tbl_subscription_block
                                where  1=1 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
         return $data;
    }
}
