<?php

use app\libraries\CommonHelper;
use app\libraries\DateHelper;
use app\libraries\Tenants;

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Invoicing_model extends MY_Model
{
    use Tenants;

    const STATUS_DRAFT = 'draft';
    const STATUS_PENDING = 'pending';
    const STATUS_READY = 'ready';
    const STATUS_OPEN = 'open';
    const STATUS_PARTIAL_PAID = 'partial paid';
    const STATUS_PAID = 'paid';
    const STATUS_DELETED = 'deleted';
    const STATUS_UNCOLLECTIBLE = 'uncollectible';
    const STATUS_VOID = 'void';

    public $short = "i";
    public $table;
    public $tbl;
    public $invoicestatus = [
        'open' => '有效',
         'merged' => '已合併',
          'completed' => '已繳清',
           'cancelmerged' => '取消合併', 
           'openaftercancelmerged' => '有效', 
           'void' => '無效'
    ];
    public $paymentstatus = ['pending' => '未繳費', 'partial paid' => '欠繳費', 'paid' => '已繳費'];
    private $actionlogtbl;
    private $studenttbl;

    public function __construct()
    {
        parent::__construct();
        $this->load->model('Student_model');
        $this->load->model('Receipt_model');
        $this->load->model('Receipt_item_model');
        $this->load->model('Actionlog_model');
        $this->database = $this->getTenantNo();
        $this->tbl = 'invoicing_invoice';
        $this->table = $this->tbl . ' ' . $this->short;

        $this->actionlogtbl = $this->Actionlog_model->tbl;
        $this->studenttbl = $this->Student_model->tbl;
    }

    public static function active_statuses()
    {
        return [self::STATUS_PENDING, self::STATUS_READY, self::STATUS_OPEN, self::STATUS_PARTIAL_PAID];
    }

    public function unpaid_badge($studentId)
    {
        $this->db = $this->load->database($this->database, true);

        return $this->db->from($this->table_prefix . $this->tbl)
            ->where('user_id', $studentId)
            ->where_in('invoice_status', self::active_statuses())
            ->get()
            ->num_rows();
    }

    public function getUsersForTransfer($invoiceId)
    {
        return [$this->Student_model->all(), $this->detail($invoiceId)['user_id']];
    }

    public function getbystudent($name = '', $studentno = '', $mobile = '', $ipage = '', $mpage = '')
    {
        $db = $this->load->database($this->database, TRUE);
        $db->join($this->table_prefix . $this->table, 's.id=' . $this->short . '.user_id');
        if ($name !== '') {
            $db->like('s.chi_name', $name);
        }
        if ($studentno !== '') {
            $db->like('s.student_no', $studentno);
        }
        if ($mobile !== '') {
            $db->group_start();
            $db->like('s.content1_phone', $mobile);
            $db->or_like('s.content2_phone', $mobile);
            $db->or_like('s.content3_phone', $mobile);
            $db->group_end();
        }
        $db->select('s.id');
        $students = $db->get($this->table_prefix . $this->studenttbl . ' s')->result_array();
        //        log_message('debug', $db->last_query());
        if (empty($students)) {
            return null;
        }
        $studentIds = [];
        foreach ($students as $k => $v) {
            $studentIds[] = $v['id'];
        }
        $db->reset_query();
        $fields = [
            "s.id as studentId",
            "s.chi_name,s.eng_name",
            "s.student_no",
            $this->short . '.id',
            $this->short . '.pic_1',
            $this->short . '.discount',
            $this->short . '.original_amount',
            $this->short . '.amount_paid',
            $this->short . '.amount_to_pay',
            $this->short . '.invoice_status',
            $this->short . '.payment_status',
            $this->short . '.issued_name',
            $this->short . '.issued_date',
            $this->short . '.mergedcode',
            'ii.period_startdate', 'ii.date_range', 'ii.period_enddate', 'ii.course_count', 'ii.item_timeslot', 'ii.item_name AS course_name',
        ];
        //        log_message('debug','studentid:'.implode(',',$studentIds));//2,3,4
        $this->load->model('Invoice_item_model');
        $db->where('user_id!=', 0);
        $db->where('invoice_status!=', InvoiceStatus::void);
        $db->where('invoice_status!=', InvoiceStatus::merged);
        $db->from($this->table_prefix . $this->table);
        $itotal = $db->count_all_results();
        //		log_message('debug', 'itotal:' . $itotal);

        $db->reset_query();
        //'user_id' => 0, 'status' => 1,
        $db->where('user_id', 0);
        $db->where('status', 1);
        $db->where('invoice_status!=', InvoiceStatus::cancelmerged);
        $db->from($this->table_prefix . $this->table);
        $mtotal = $db->count_all_results();
        //		log_message('debug', 'mtotal:' . $mtotal);

        /*--pagination--*/
        $ipaging = array();
        $mpaging = array();
        //		log_message('debug','invoices count:'.count($invoices));
        $limit = 1;
        $ipages = ceil($itotal / $limit);
        $ipage = empty($ipage) ? 1 : $ipage;

        $ioffset = ($ipage - 1) * $limit;
        $istart = $ioffset + 1;
        $iend = min(($ioffset + $limit), $itotal);
        //		log_message('debug','ipage:'.$ipage.';ioffset:'.$ioffset.';istart:'.$istart.';iend:'.$iend);
        $iprevlink = ($ipage > 1) ? '<a class="paging" href="javascript:void(0);" data-href="ipage=1" title="首頁">首頁</a>&nbsp;&nbsp;<a class="paging" href="javascript:void(0);" data-href="ipage=' . ($ipage - 1) . '" title="上一頁">上一頁</a>' : '<span class="disabled">首頁</span>&nbsp;&nbsp;<span class="disabled">上一頁</span>';
        $inextlink = ($ipage < $ipages) ? '<a class="paging" href="javascript:void(0);" data-href="ipage=' . ($ipage + 1) . '" title="下一頁">下一頁</a>&nbsp;&nbsp;<a class="paging" href="javascript:void(0);" data-href="ipage=' . $ipages . '" title="末頁">末頁</a>' : '<span class="disabled">下一頁</span>&nbsp;&nbsp;<span class="disabled">末頁</span>'; //
        //		$db->reset_query();
        //		$db->limit($limit, $moffset);
        //		$db->join($this->table_prefix . $this->studenttbl . ' s', $this->short . '.user_id=s.id');
        //		$db->join($this->table_prefix . $this->Invoice_item_model->tbl . ' ii', $this->short . '.id=ii.invoice_id');
        //		$db->where_in('s.id', $studentIds);
        //		$db->where('invoice_status!=', InvoiceStatus::void);
        //		$db->select(implode(',', $fields));
        //		$mresult = $db->get($this->table_prefix . $this->table)->result_array();

        //		$invoices = [];
        //		$invoicesinmerge = [];
        //		if (!empty($result)) {
        //			$inmergeIds = [];
        //			foreach ($result as $row) {
        //				if ($row['invoice_status'] !== InvoiceStatus::merged) {
        //					$invoices[] = $this->_geninvoicerow($row);
        //				}
        //				foreach ($allinvoicesinmerge as $m) {
        //					if ($row['mergedcode'] === $m['mergedcode'] && !in_array($m['id'], $inmergeIds)) {
        //						$invoicesinmerge[] = $this->_geninvoicerow($m);
        //						$inmergeIds[] = $m['id'];
        //					}
        //				}
        //			}
        //		}
        $db->reset_query();
        $db->limit($limit, $ioffset);
        $db->join($this->table_prefix . $this->studenttbl . ' s', $this->short . '.user_id=s.id');
        $db->join($this->table_prefix . $this->Invoice_item_model->tbl . ' ii', $this->short . '.id=ii.invoice_id');
        $db->where_in('s.id', $studentIds);
        $db->where('user_id!=', 0);
        $db->where('invoice_status!=', InvoiceStatus::void);
        $db->where('invoice_status!=', InvoiceStatus::merged);
        $db->select(implode(',', $fields));
        $iresult = $db->get($this->table_prefix . $this->table)->result_array();
        log_message('debug', 'ipage:' . $ipage . ';ioffset:' . $ioffset . ';iresult count:' . count($iresult) . ';sql:' . $db->last_query());
        if (!empty($iresult)) {
            foreach ($iresult as $row) {
                $ipaging[] = $this->_geninvoicerow($row);
            }
        }
        //		$allinvoicesinmerge = $this->getAll($this->database, $this->tbl, ['id', 'mergedcode', 'amount_to_pay', 'amount_paid', 'invoice_status', 'payment_status'], ['user_id' => 0, 'status' => 1, 'invoice_status!=' => InvoiceStatus::cancelmerged]);

        $mpages = ceil($mtotal / $limit);
        $mpage = empty($mpage) ? 1 : $mpage;
        $moffset = ($mpage - 1) * $limit;
        $mstart = $moffset + 1;
        $mend = min(($moffset + $limit), $mtotal);
        $mprevlink = ($mpage > 1) ? '<a class="paging" href="javascript:void(0);" data-href="mpage=1" title="首頁">首頁</a>&nbsp;&nbsp;<a class="paging" href="javascript:void(0);" data-href="mpage=' . ($mpage - 1) . '" title="上一頁">上一頁</a>' : '<span class="disabled">首頁</span>&nbsp;&nbsp;<span class="disabled">上一頁</span>';
        $mnextlink = ($mpage < $mpages) ? '<a class="paging" href="javascript:void(0);" data-href="mpage=' . ($mpage + 1) . '" title="下一頁">下一頁</a>&nbsp;&nbsp;<a class="paging" href="javascript:void(0);" data-href="mpage=' . $mpages . '" title="末頁">末頁</a>' : '<span class="disabled">下一頁;</span>&nbsp;&nbsp;<span class="disabled">末頁</span>';

        $fields = [
            $this->short . '.id',
            $this->short . '.pic_1',
            $this->short . '.discount',
            $this->short . '.original_amount',
            $this->short . '.amount_paid',
            $this->short . '.amount_to_pay',
            $this->short . '.invoice_status',
            $this->short . '.payment_status',
            $this->short . '.issued_name',
            $this->short . '.issued_date',
            $this->short . '.mergedcode',
        ];
        $db->reset_query();
        $db->limit($limit, $moffset);
        $db->where('user_id', 0);
        $db->where($this->short . '.status', 1);
        $db->select(implode(',', $fields));
        $mresult = $db->get($this->table_prefix . $this->table)->result_array();
        //		log_message('debug','mpage:'.$mpage.';moffset:'.$moffset.';mresult count:'.count($mresult).';sql:'.$db->last_query());
        if (!empty($mresult)) {
            foreach ($mresult as $row) {
                $mpaging[] = $this->_geninvoicerow($row);
            }
        }
        return [$ipaging, $istart, $iend, $iprevlink, $inextlink, $mpaging, $mstart, $mend, $mprevlink, $mnextlink];
    }

    public function getbyselectedno($selectedno)
    {
        $student = $this->Student_model->getByStudentno($selectedno, $this->session->userdata('tenant_no'));
        //        log_message('debug','empty student?'.empty($student));
        if (empty($student)) {
            return array([], []);
        }
        $studentId = $student['id'];

        $db = $this->load->database($this->database, TRUE);
        //        log_message('debug','studentid:'.implode(',',$studentId));//4
        //unmerged:
        $sql = 'SELECT s.id as studentId,s.chi_name,s.eng_name,s.student_no, i.id,i.period_startdate,i.date_range,i.period_enddate,i.course_count,i.item_timeslot,
i.item_name AS course_name,i.amount
FROM ' . $this->table_prefix . $this->bitbl . ' i 
JOIN ' . $this->table_prefix . $this->studenttbl . ' s ON i.user_id = s.id Where s.id = ? and i.invoice_status!=?';
        $result = $db->query($sql, array($studentId, InvoiceStatus::void))->result_array();
        //        log_message('debug','sql:'.$db->last_query());
        $data = [];
        if (!empty($result)) {
            //        log_message('debug','result count:'.count($result));
            foreach ($result as $row) {
                $data[] = $this->_geninvoicerow($row);
            }
        }
        return array($student, $data);
    }

    public function listing_month_invoice($eventId, $enrollmentId, $studentId, $selectedMonths)
    {
        $this->db = $this->load->database($this->database, TRUE);

        if (!is_array($enrollmentId)) $enrollmentId = [$enrollmentId];
        return $this->db
            ->from('mas_invoicing_invoice')
            ->join('mas_invoicing_invoice_item', 'mas_invoicing_invoice.id = mas_invoicing_invoice_item.invoice_id')
            ->where_in('mas_invoicing_invoice_item.event_enrollment_id', $enrollmentId)
            ->where('mas_invoicing_invoice.user_id', $studentId)
            ->where('CONCAT(year,"-",month) IN ("' . implode('","', $selectedMonths) . '")', null, false)
            ->order_by('CONCAT(year,"-",month)', 'asc', false)
            ->select('
                    mas_invoicing_invoice.*, 
                    CONCAT(year,"-",month) AS selected_month,
                    mas_invoicing_invoice_item.attend_startdate,
                    mas_invoicing_invoice_item.item_name,
                    mas_invoicing_invoice_item.unit_price,
                    mas_invoicing_invoice_item.qty,
                    mas_invoicing_invoice_item.discount,
                    mas_invoicing_invoice_item.amount,
                ', false)
            ->get()
            ->result_array();
    }

    public function listing_lumpsum_invoice($eventId, $enrollmentId, $studentId)
    {
        $this->db = $this->load->database($this->database, TRUE);

        if (!is_array($enrollmentId)) $enrollmentId = [$enrollmentId];
        return $this->db
            ->from('mas_invoicing_invoice')
            ->join('mas_invoicing_invoice_item', 'mas_invoicing_invoice.id = mas_invoicing_invoice_item.invoice_id')
            ->where_in('mas_invoicing_invoice_item.event_enrollment_id', $enrollmentId)
            ->where('mas_invoicing_invoice.user_id', $studentId)
            ->select('
                    mas_invoicing_invoice.*, 
                    mas_invoicing_invoice_item.attend_startdate,
                    mas_invoicing_invoice_item.item_name,
                    mas_invoicing_invoice_item.unit_price,
                    mas_invoicing_invoice_item.qty,
                    mas_invoicing_invoice_item.discount,
                    mas_invoicing_invoice_item.amount,
                ', false)
            ->get()
            ->row_array();
    }

    public function clone_month_pay_entry($eventId, $enrollmentId, $studentId, $year, $month)
    {
        $this->db = $this->load->database($this->database, TRUE);
        $this->db->trans_start();

        $existing = $this->db
            ->from('mas_invoicing_invoice')
            ->join('mas_invoicing_invoice_item', 'mas_invoicing_invoice.id = mas_invoicing_invoice_item.invoice_id')
            ->where('mas_invoicing_invoice_item.event_enrollment_id', $enrollmentId)
            ->where('mas_invoicing_invoice.user_id', $studentId)
            ->order_by('CONCAT(year,"-",month)', 'asc', false)
            ->select('
                mas_invoicing_invoice.*, 
                mas_invoicing_invoice_item.attend_startdate,
                mas_invoicing_invoice_item.item_name,
                mas_invoicing_invoice_item.unit_price,
                mas_invoicing_invoice_item.qty,
                mas_invoicing_invoice_item.discount,
                mas_invoicing_invoice_item.amount,
            ', false)
            ->get()
            ->row_array();

        if (!$existing) {
            $this->throw_exception('找不到發票記錄。');
        }
        if ($existing['year'] == $year && $existing['month'] == $month) {
            $this->throw_exception(sprintf('學生在此時段已有 %s-%s 的發票記錄，不能重覆繳交費用。', $year, $month));
        }

        $this->db->reset_query();
        $hasInvoice = $this->db
            ->from('mas_invoicing_invoice')
            ->where('year', $year)
            ->where('month', $month)
            ->where('payment_status != "void"', null, false)
            ->get()
            ->row_array();

        if ($hasInvoice) {
            $this->throw_exception(sprintf('學生在此時段已有 %s-%s 的發票記錄，不能重覆繳交費用。', $year, $month));
        }

        $counter = 0;
        $itemDates = $itemTimeslots = [];
        $this->load->model('Event_timeslot_model');
        $this->load->model('Invoice_item_model');
        $timeslots = $this->Event_timeslot_model->listing_monthly($eventId, $year, $month);

        foreach ($timeslots as $timeslot) {
            $timeslotTimestamp = intval($timeslot['startdateTimestamp']);

            $counter++;
            array_push($itemDates, date('Y-m-d', $timeslotTimestamp));
            $this->push_timeslot($itemTimeslots, $timeslot);
        }

        $first = $itemDates[0];
        $last = $itemDates[count($itemDates) - 1];
        $firstTimestamp = DateTime::createFromFormat('Y-m-d', $first, new DateTimeZone('Asia/Hong_Kong'))->getTimestamp();
        $lastTimestamp = DateTime::createFromFormat('Y-m-d', $last, new DateTimeZone('Asia/Hong_Kong'))->getTimestamp();
        $range = sprintf('%s - %s', $first, $last);

        $this->create([
            // no extra fee lumpsum entries
            'L' => [],
            // invoicing_invoice fields
            'selected_month' => sprintf('%s-%s', $year, $month),
            // 'pic_1' => $existing['pic_1'],
            // 'room_id' => $existing['room_id'],
            'user_id' => $studentId,
            'original_amount' => $existing['original_amount'],
            'discount_amount' => $existing['discount'],
            'discount' => $existing['discount_percent'],
            // invoicing_invoice_item fields
            'event_id' => $eventId,
            'event_enrollment_id' => $enrollmentId,
            'attend_startdate' => $existing['attend_startdate'],
            'date_range' => $range,
            'period_startdate' => $firstTimestamp,
            'period_enddate' => $lastTimestamp,
            'is_trial' => 0,
            'course_count' => $counter,
            'item_name' => $existing['item_name'],
            'item_timeslot' => implode('||', $itemTimeslots),
            'unit_price' => $existing['unit_price'],
            'qty' => $existing['qty'],
            // 'discount' => $existing['discount'],
            'amount' => $existing['amount'],
        ]);
    }

    protected function push_timeslot(&$array, $timeslot)
    {
        array_push($array, sprintf(
            '%s(%s) %s-%s',
            date('Y-m-d', $timeslot['startdateTimestamp']),
            DateHelper::getDefaultWeekdays($timeslot['startdateTimestamp']),
            DateHelper::getAmpm($timeslot['starttime'], false),
            DateHelper::getAmpm($timeslot['endtime'], true, false)
        ));
    }

    public function create($params)
    {
        $db = $this->load->database("default", TRUE);
        
        $newissuedname = CommonHelper::getNewIssuedName_Invoice($db, $this->table_prefix . $this->tbl, $this->session->userdata('tenant_no'));
        $_items = $params['L'];
        $extraFeeLumpsum = 0;
        if (!empty($_items)) {
            foreach ($_items as $_item) {
                if ($_item['name'] == "" && $_item['amount'] == "") {
                    continue;
                } else {
                    $extraFeeLumpsum += intval($_item['amount']);
                }
            }
        }
        $amounttopay = floatval($params['amount']) + $extraFeeLumpsum;
        $this->load->model('Event_model');
        $row = $this->getOne("default", $this->Event_model->table, ['pic_1', 'room_id'], $params['event_id'], 'id', false);
      

        if (!empty($row)) {
            $db->trans_start();
            $discount = $params['discount'];
            $discountAmount = $params['discount_amount'];
            $discountPercent = 0;

            if ($discount && strpos($discount, "%") !== false) {
                $discountPercent = str_replace('%', '', $discount);
                $discount = $discountAmount;
            } else if ($discount) {
                $discountPercent = 0;
                $discount = $discount;
            }

            $insertParams = [
                'issued_name' => $newissuedname,
                'issued_date' => time(),
                'create_time' => time(),
				'event_enrollment_id' => $params['event_enrollment_id'],
                'user_id' => $params['user_id'],
                'original_amount' => $params['original_amount'],
                'discount' => $params['discount_amount'],
				'discount_value' => $params['discount_amount'],
//                'discount_percent' => $params['discount'],
                'amount_to_pay' => $amounttopay,
                'invoice_status' => InvoiceStatus::open,
                'payment_status' => PaymentStatus::pending,
                'pic_1' => $row['pic_1'],
                'room_id' => $row['room_id']
            ];

            if (!empty($params['selected_month'])) {
                $selectedMonth = $params['selected_month'];
                list($year, $month) = explode('-', $selectedMonth);
                $insertParams['year'] = $year;
                $insertParams['month'] = $month;
            }

            $db->insert($this->table_prefix . $this->tbl, $insertParams);
            $newInvoiceId = $db->insert_id();
            log_message('debug', 'newinvoiceid:' . $newInvoiceId);
            $this->load->model('Invoice_item_model');
            $itemtable = $this->table_prefix . $this->Invoice_item_model->tbl;
            /*
			  $this->Invoicing_model->create([
			  'user_id' => $enrollment['student_id'],
			  'event_id' => $enrollment['event_id'],
			  'event_enrollment_id' => $enrollment['id'],
			  'attend_startdate' => $params['first_date'],
			  'date_range' => $dateRange,
			  'period_startdate' => $periodStartdate,
			  'period_enddate' => $periodEnddate,
			  'is_trial' => $params['is_trial'],
			  'course_count' => $itemCount,
			  'item_name' => $itemName,
			  'item_timeslot' => $itemTimeslots,
			  'unit_price' => $price,
			  'qty' => $qty,
			  'discount' => $discount,
			  'discount_amount'=>$discount_amount,
			  'amount' => $amount,
			  'original_amount'=>floatval($price)*intval($qty),
			  ]);
			 */
            $_items = $params['L'];
            $params['discount'] = $params['discount_amount'];
            $params = array_merge(['invoice_id' => $newInvoiceId], $params);
            unset($params['discount_amount']);
            unset($params['user_id']);
            unset($params['original_amount']);
            unset($params['L']);
            unset($params['selected_month']);
            //            log_message('debug','itemtable:'.$itemtable.';count params:'.count($params));
            $db->insert($itemtable, $params);
            $newItemId = $db->insert_id();
            $extraFees = [];
            foreach ($_items as $_item) {
                //                log_message('debug','itemname:'.$_item['name'].';itemamount:'.$_item['amount']);// itemname:extrafee item 1;itemamount:30
                if ($_item['name'] == "" && $_item['amount'] == "") {
                    continue;
                } else {
                    array_push($extraFees, array('invoicing_item_id' => $newItemId, 'name' => $_item['name'], 'amount' => $_item['amount']));
                }
            }
            if (!empty($extraFees)) {
                $db->insert_batch($this->table_prefix . 'invoicing_invoice_item_extra_fee', $extraFees);
            }
            $db->trans_complete();
        }
    }

    public function merge($ids)
    {
        $invoices = $this->getAllIn($this->database, $this->tbl, [], 'id', $ids);
        $amountpaid = 0;
        $amounttopay = 0;
        foreach ($invoices as $k => $v) {
            $amountpaid += floatval($v['amount_paid']);
            $amounttopay += floatval($v['amount_to_pay']);
        }
        $mergedcode = uniqid();
        $db = $this->load->database($this->database, TRUE);
        $db->trans_start();
        $userid = $this->session->userdata('userid');
        $db->insert($this->table_prefix . $this->tbl, ['mergedcode' => $mergedcode, 'create_time' => time(), 'created_by' => $userid, 'amount_to_pay' => $amounttopay, 'amount_paid' => $amountpaid, 'invoice_status' => InvoiceStatus::open, 'payment_status' => PaymentStatus::pending]);
        $insertId = $db->insert_id();
        $update = [];
        foreach ($ids as $k => $v) {
            $update[] = ['id' => $v, 'invoice_status' => InvoiceStatus::merged, 'additional_notes' => '合併發票', 'mergedcode' => $mergedcode];
        }
        $db->update_batch($this->table_prefix . $this->tbl, $update, 'id');
        $db->trans_complete();

        $db = $this->load->database('default', TRUE);
        $action = ['tenant_no' => $this->session->userdata('tenant_no'), 'table_name_from' => $this->tbl, 'page' => 'invoicing', 'table_name_to' => $this->tbl, 'record_id_from' => implode(',', $ids), 'record_id_to' => $insertId, 'action' => Action::merged, 'amount_affected' => $amounttopay, 'created_by' => $userid, 'create_time' => time()];
        $db->insert($this->table_prefix . $this->actionlogtbl, $action);
        return $insertId;
    }

    public function unmerge($id)
    {
        $this->load->model('Invoicing_model');
        $this->load->model('Invoice_item_model');
        $this->load->model('Receipt_model');
        $this->load->model('Receipt_item_model');
        $this->load->model('Student_credit_model');

        $db = $this->load->database($this->database, TRUE);
        $db->trans_start();

        $invoice = $this->detail($id);
        //get original invoices
        $invoices = $this->getAll($this->database, $this->Invoicing_model->tbl, ['id', 'amount_to_pay', 'mergedcode', 'user_id'], ['invoice_status' => InvoiceStatus::merged, 'mergedcode' => $invoice['mergedcode']]);
        $update = [];
        $ids = [];
        foreach ($invoices as $i) {
            $ids[] = $i['id'];
            $update[] = ['id' => $i['id'], 'invoice_status' => InvoiceStatus::open, 'additional_notes' => '取消合併後重開', 'update_time' => time(), 'updated_by' => $this->session->userdata('user_id')];
        }
        $db->update_batch($this->table_prefix . $this->tbl, $update, 'id');

        $this->saveDB($this->database, $this->tbl, ['status' => 0, 'additional_notes' => '取消合併', 'invoice_status' => InvoiceStatus::cancelmerged], $id); //disable the invoice itself

        $db->trans_complete();

        $db = $this->load->database('default', TRUE);
        $action = ['tenant_no' => $this->session->userdata('tenant_no'), 'table_name_from' => $this->tbl, 'table_name_to' => $this->tbl, 'page' => 'invoicing', 'record_id_from' => $id, 'record_id_to' => implode(',', $ids), 'action' => Action::unmerged, 'amount_affected' => $this->getCurrentAmountPaid($id), 'created_by' => $this->session->userdata('userid'), 'create_time' => time()];
        $db->insert($this->table_prefix . $this->actionlogtbl, $action);
    }


    public function getunmerged($id)
    {
        $this->load->model('Invoice_item_model');
        $invoiceItems = $this->Invoice_item_model->getItemsByInvoiceId($id);
        $unmerged = [];
        foreach ($invoiceItems as $e) {
            $row = $this->getOneRow($this->database, $this->bitbl, [], array('event_id' => $e['event_id'], 'event_enrollment_id' => $e['event_enrollment_id']));
            $timeslot = CommonHelper::getReadableTimeslot(trim($row['item_timeslot']));
            if (is_numeric(trim($row['period_startdate'])) && is_numeric(trim($row['period_enddate']))) {
                $daterange = date("Y-m-d", trim($row['period_startdate'])) . ' - ' . date("Y-m-d", trim($row['period_enddate']));
            } else {
                $daterange = trim($row['date_range']);
            }
            $unmerged[] = [
                'id' => trim($row['id']),
                'amount' => CommonHelper::money(trim($row['amount'])),
                'course_name' => trim($row['item_name']),
                'date_range' => $daterange,
                'timeslot' => $timeslot,
                'select' => '<input type="checkbox" class="checkbox-delete" id="' . trim($row['id']) . '" />',
            ];
        }
        return $unmerged;
    }

    //get the invoice that has just been merged into one
    public function getmerged($id)
    {
        return $this->detailforread($id);
    }

    //get the invoice(s) that has/have been merged
    public function getallmerged($mergedcode)
    {
        $invoiceinmerge = $this->getinvoiceinmerge($mergedcode);
        if ($invoiceinmerge['invoice_status'] === InvoiceStatus::open) {
            $data = $this->getAll($this->database, $this->tbl, [], ['invoice_status' => InvoiceStatus::merged, 'mergedcode' => $mergedcode]);
            foreach ($data as $k => $v) {
                $data[$k] = $this->_geninvoicerow($v);
            }
        }
        if ($invoiceinmerge['invoice_status'] === InvoiceStatus::cancelmerged) {
            $data = $this->getAll($this->database, $this->tbl, [], ['invoice_status' => InvoiceStatus::open, 'mergedcode' => $mergedcode]);
            foreach ($data as $k => $v) {
                $data[$k] = $this->_geninvoicerow($v);
            }
        }
        return $data;
    }

    public function getCurrentAmountPaid($id)
    {
        $row = $this->getOne("default", $this->tbl, ['amount_paid'], $id);
        return $row['amount_paid'];
    }

    public function detail($id)
    {
        return $this->getOne("default", $this->tbl, ['id', 'pic_1', 'room_id', 'user_id', 'discount', 'original_amount', 'amount_to_pay', 'amount_paid', 'issued_name', 'issued_date', 'payment_status', 'source', 'pathname', 'invoice_status', 'payment_status', 'mergedcode', 'status'], $id);
    }

    public function detailforread($id)
    {
        return $this->_geninvoicerow($this->detail($id));
    }

    public function detailbyinvoiceno($invoiceno)
    {
        $this->load->model('Invoice_item_model');
        $this->load->model('Student_model');
        $fields = [$this->short . '.id', $this->short . '.pic_1', $this->short . '.room_id', $this->short . '.user_id', $this->short . '.discount', $this->short . '.original_amount', $this->short . '.amount_to_pay', $this->short . '.amount_paid', $this->short . '.issued_name', $this->short . '.issued_date', $this->short . '.payment_status', $this->short . '.source', $this->short . '.pathname', $this->short . '.invoice_status', $this->short . '.payment_status', 'ii.date_range', 'ii.item_name as course_name', 's.chi_name', 's.student_no'];
        $db = $this->load->database($this->database, TRUE);
        $db->like('issued_name', $invoiceno);
        $db->join($this->table_prefix . $this->Invoice_item_model->tbl . ' ii', $this->short . '.id=ii.invoice_id');
        $db->join($this->table_prefix . $this->Student_model->tbl . ' s', $this->short . '.user_id=s.id');
        $data = $db->select(implode(',', $fields))->get($this->table_prefix . $this->table)->result_array();
        if (empty($data)) {
            return [];
        }
        foreach ($data as $k => $v) {
            $data[$k] = $this->_geninvoicerow($v);
        }
        return $data;
    }

    private function _geninvoicerow($row)
    { 
        $id = $row['invoiceId']; //= $row['id'];
        $invoice = [];
        $invoice['id'] = $invoice['invoiceId'] = $id;
        $invoice['studentId'] = isset($row['studentId']) ? $row['studentId'] : 0;
        $invoice['chi_name'] = isset($row['chi_name']) ? $row['chi_name'] : '';
        $invoice['studentno'] = isset($row['student_no']) ? $row['student_no'] : '';
        $invoice['course_name'] = isset($row['course_name']) ? $row['course_name'] : '';
        $invoice['date_range'] = isset($row['date_range']) ? $row['course_name'] : '';
        $invoice['pic_1'] = $row['pic_1'] ?? '';
        $invoice['room_id'] = isset($row['room_id']) ? $row['room_id'] : 0;
        $invoice['user_id'] = isset($row['user_id']) ? $row['user_id'] : 0;
        $invoice['discount'] = $row['disount'] ?? 0;
        $invoice['original_amount'] = isset($row['original_amount']) ? CommonHelper::money(trim($row['original_amount'])) : '';
        $amountpaid = trim($row['amount_paid']);
        $invoice['amountpaid'] = $amountpaid;
        $invoice['amount_paid'] = CommonHelper::money($amountpaid);
        $amounttopay = trim($row['amount_to_pay']);
        $invoice['amounttopay'] = $amounttopay;
        $invoice['amount_to_pay'] = CommonHelper::money($amounttopay);
        $netamountpayable = floatval($amounttopay) - floatval($amountpaid);
        $invoice['net_amount_payable'] = $netamountpayable > 0 ? CommonHelper::money($netamountpayable) : CommonHelper::money(0);
        $invoice['issued_date'] = empty($row['issued_date']) ? '---' : date("Y-m-d", trim($row['issued_date']));
        $invoice['issued_name'] = empty($row['issued_name']) ? '---' : trim($row['issued_name']);
        $invoice['pathname'] = empty($row['pathname']) ? '---' : trim($row['pathname']);
        $invoice['invoice_status'] = $row['invoice_status'];
        $invoice['invoice_status_trans'] = $this->invoicestatus[$row['invoice_status']];
        $invoice['payment_status'] = $row['payment_status'];
        $invoice['payment_status_trans'] = $this->paymentstatus[$row['payment_status']];
        $invoice['details'] = '<a class="details" href="/invoicing_read/' . $id . '">詳情</a>';
        $invoice['mergedcode'] = isset($row['mergedcode']) ? empty($row['mergedcode']) ? '' : trim($row['mergedcode']) : '';
        $invoice['status'] = isset($row['status']) ?? 1;
     
        return $invoice;
    }

  

    public function getInvoices()
    {
        $invoices = [];
        $sql = 'SELECT  i.id as invoiceId, i.year,i.month,i.pic_1,i.discount,i.original_amount,i.amount_to_pay,i.amount_paid,i.invoice_status,i.payment_status,i.source,i.issued_name,i.issued_date,i.pathname
        FROM ' . $this->table_prefix . 'invoicing_invoice i where i.status=1';
        $db = $this->load->database("default", TRUE);
        $result = $db->query($sql)->result_array();
        
        if (empty($result)) {
            return [];
        }
        foreach ($result as $row) {
            $invoices[] = $this->_geninvoicerow($row);
        }
        return $invoices;
    }

    public function linkinvoice($invoiceId, $receiptId)
    {
        $invoice = $this->detail($invoiceId);
        $netamtpayable = floatval($invoice['amount_to_pay']) - floatval($invoice['amount_paid']);
        $amtpaidbyreceipt = 0;
        $receipt = $this->Receipt_model->detail($receiptId);
        $reitems = $this->Receipt_item_model->getItemsByReceiptId($receiptId);
        foreach ($reitems as $v) {
            $amtpaidbyreceipt += floatval($v['amount_paid']);
        }
        // invoiceid:2;receiptid:1;netamtpayable:1000;amtpaidbyreceipt:10
        //        log_message('debug', 'invoiceid:' . $invoiceId . ';receiptid:' . $receiptId . ';netamtpayable:' . $netamtpayable . ';amtpaidbyreceipt:' . $amtpaidbyreceipt);
        if ($netamtpayable >= $amtpaidbyreceipt) {
            $db = $this->load->database($this->database, TRUE);
            //            $db->trans_start();
            $this->Receipt_model->create_in_checkout($invoice, $invoiceId, $receipt['payment_type_id'], $receipt['cash_amount'], $receipt['cheque_amount'], $receipt['cheque_bank_name'], $receipt['cheque_no'], $receipt['remark'], $receipt['atm_id'], $receiptId);
            //            $db->trans_complete();

            $db = $this->load->database('default', TRUE);
            $action = [
                'tenant_no' => $this->session->userdata('tenant_no'),
                'table_name_from' => $this->Receipt_model->tbl,
                'table_name_to' => $this->tbl,
                'page' => 'invoicing',
                'record_id_from' => $receiptId,
                'record_id_to' => $invoiceId,
                'action' => Action::linkedorphanreceipts, 'amount_affected' => $this->getCurrentAmountPaid($invoiceId), 'created_by' => $this->session->userdata('userid'), 'create_time' => time()
            ];
            $db->insert($this->table_prefix . $this->actionlogtbl, $action);
            return true;
        } else {
            return false;
        }
    }

    public function all()
    {
        //public function getAllIn($database, $table, $fields = [], $whereField = '', $whereValue = [],$tableprefix=true, $where=[])
        $invoices = $this->getAll($this->database, $this->tbl);
        foreach ($invoices as $k => $v) {
            $invoices[$k] = $this->_geninvoicerow($v);
        }
        $merged = $this->getAll($this->database, $this->tbl, [], ['invoice_status' => InvoiceStatus::merged]);
        foreach ($merged as $k => $v) {
            $merged[$k] = $this->_geninvoicerow($v);
        }
        return [$invoices, $merged];
    }

    public function listing_invoices_studentId($studentId)
    {
        $this->db = $this->load->database($this->database, true);
        $tenantNo = $this->getTenantNo();

        $mergeCodeSubquery = $this->db
            ->from('mas_invoicing_invoice')
            ->where('mergedcode IS NOT NULL', null, false)
            ->where('invoice_status', 'merged')
            ->where('user_id', $studentId)
            ->select('mergedcode')
            ->group_by('mergedcode')
            ->get_compiled_select();

        $invoices = $this->db
            ->from('mas_invoicing_invoice')
            ->group_start()
            ->where_in('invoice_status', ['open', 'openaftercancelmerged'])
            ->where('user_id', $studentId)
            ->where('mergedcode IS NULL', null, false)
            ->group_end()
            ->or_group_start()
            ->where('invoice_status', 'open')
            ->where(sprintf('mergedcode IN (%s)', $mergeCodeSubquery), null, false)
            ->group_end()
            ->select('
                id AS invoice_id,
                invoice_status, 
                issued_name, 
                issued_date, 
                amount_to_pay, 
                amount_paid,
                amount_to_pay - amount_paid as net_amount_payable,
                payment_status,
                create_time
            ', false)
            ->group_by('id')
            ->get()
            ->result_array();

        // echo $this->db->last_query(); die();
        $res = [];
        foreach ($invoices as $item) {
            $message = $item['payment_status'] == 'partial paid' ? '您還尚欠的學費未清，請到分店繳交。' : '您還未繳交學費，請到分店繳交。';

            array_push($res, [
                'invoice_id' => $item['invoice_id'],
                'issued_date' => date('Y-m-d H:i', $item['issued_date'] ? $item['issued_date'] : $item['create_time']),
                'issued_name' => $item['issued_name'] ?: "",
                'message' => $message,
                'amount_to_pay' => $item['amount_to_pay'],
                'amount_paid' => $item['amount_paid'],
                'net_amount_payable' => $item['net_amount_payable'],
                // 'url' => sprintf('/api/invoicing_invoice?invoice_id=%s&tenant_no=%s&output=F', $item['invoice_id'], $tenantNo),
            ]);
        }

        return $res;
    }

    public function listing_receipts_studentId($studentId)
    {
        $this->db = $this->load->database($this->database, true);
        $tenantNo = $this->getTenantNo();

        $mergeCodeSubquery = $this->db
            ->from('mas_invoicing_invoice')
            ->where('mergedcode IS NOT NULL', null, false)
            ->where('invoice_status', 'merged')
            ->where('user_id', $studentId)
            ->select('mergedcode')
            ->group_by('mergedcode')
            ->get_compiled_select();

        $receipts = $this->db
            ->from('mas_invoicing_receipt')
            ->where('mas_invoicing_receipt.user_id', $studentId)
            ->or_group_start()
            ->where_in('mas_invoicing_invoice.invoice_status', ['open', 'openaftercancelmerged'])
            ->where(sprintf('mas_invoicing_invoice.mergedcode IN (%s)', $mergeCodeSubquery), null, false)
            ->group_end()
            ->join('mas_invoicing_invoice', 'mas_invoicing_receipt.invoice_id = mas_invoicing_invoice.id')
            ->join('mas_invoicing_receipt_item', 'mas_invoicing_receipt.id = mas_invoicing_receipt_item.receipt_id')
            ->select('
                mas_invoicing_receipt.invoice_id,
                mas_invoicing_receipt.id as receipt_id,
                mas_invoicing_receipt.issued_name, 
                mas_invoicing_receipt.issued_date, 
                SUM(mas_invoicing_receipt_item.amount_paid) AS amount_paid
            ', false)
            ->group_by('mas_invoicing_receipt.id')
            ->order_by('issued_date', 'desc')
            ->get()
            ->result_array();

        $res = [];
        foreach ($receipts as $item) {
            $message = '已繳交學費。';

            array_push($res, [
                'invoice_id' => $item['invoice_id'],
                'receipt_id' => $item['receipt_id'],
                'issued_date' => date('Y-m-d H:i', $item['issued_date']),
                'issued_name' => $item['issued_name'] ?: "",
                'message' => $message,
                'amount_paid' => $item['amount_paid'],
                // 'url' => sprintf('/api/invoicing_receipt?receipt_id=%s&tenant_no=%s&output=F', $item['receipt_id'], $tenantNo),
            ]);
        }

        return $res;
    }

    public function listing_paid_statuses()
    {
        $this->db = $this->load->database($this->database, true);

        $_mergeCodes = $this->db
            ->from('mas_invoicing_invoice')
            ->where('mergedcode IS NOT NULL', null, false)
            ->where_not_in('invoice_status', ['merged', 'completed', 'deleted', 'uncollectible', 'draft', 'cancelmerged'])
            ->select('id, mergedcode, payment_status')
            ->get()
            ->result_array();


        $mergeCodes = [];

        if (!empty($_mergeCodes)) {
            $existingMergeCodes = [];
            foreach ($_mergeCodes as $item) {
                if (!in_array($item['mergedcode'], $existingMergeCodes)) {
                    array_push($existingMergeCodes, $item['mergedcode']);
                    $mergeCodes[$item['id']] = $item['payment_status'];
                }
            }
        }

        $invoices = $this->db
            ->from('mas_invoicing_invoice')
            ->group_start()
            ->where_in('invoice_status', ['open', 'openaftercancelmerged'])
            ->group_end()
            ->or_group_start()
            ->where_not_in('invoice_status', ['completed', 'deleted', 'uncollectible', 'draft', 'cancelmerged'])
            ->group_end()
            ->join('mas_invoicing_invoice_item', 'mas_invoicing_invoice.id = mas_invoicing_invoice_item.invoice_id')
            ->select('
                mas_invoicing_invoice.id,
                mas_invoicing_invoice.user_id AS student_id,
                mas_invoicing_invoice_item.event_enrollment_id AS event_enrollment_id,
                mas_invoicing_invoice.year,
                mas_invoicing_invoice.month,
                CASE 
                    WHEN mas_invoicing_invoice.payment_status = "paid" THEN "paid"
                    ELSE "not_paid"
                END AS paid_status
            ', false)
            ->get()
            ->result_array();

        $res = [];

        foreach ($invoices as $invoice) {
            $studentId = $invoice['student_id'];
            $enrollmentId = $invoice['event_enrollment_id'];
            $year = $invoice['year'];
            $month = $invoice['month'];
            $paidStatus = $mergeCodes[$invoice['id']] ?? $invoice['paid_status'];
            // Monthly payment
            if (!empty($year) && !empty($month)) {
                $selectedMonth = DateTime::createFromFormat('Y-m', $year . '-' . $month)->format('Y-m');
                $key = sprintf('%s-%s-%s', $studentId, $enrollmentId, $selectedMonth);
                $res[$key] = $paidStatus;
            } else {
                // Lumpsum
                $key = sprintf('%s-%s', $studentId, $enrollmentId);
                $res[$key] = $paidStatus;
            }
        }

        //        log_message('debug', $this->db->last_query());

        return $res;
    }

    public function getExtra($invoiceItemId)
    {
        return $this->getAll($this->database, 'invoicing_invoice_item_extra_fee', [], ['invoicing_item_id' => $invoiceItemId], TRUE);
    }

    public function update($params, $id)
    {
        return $this->saveDB($this->database, $this->tbl, $params, $id);
    }

    public function getinvoiceinmerge($mergedcode)
    {
        return $this->getOneRow($this->database, $this->tbl, [], ['mergedcode' => $mergedcode, 'user_id' => 0, 'invoice_status!=' => InvoiceStatus::merged]);
    }

    public function getAllInvoicesByStudentId($studentId)
	{
		$this->db = $this->load->database($this->database, true);
		$tenantNo = $this->getTenantNo();

		$invoices = $this->db
			->from($this->table_prefix . $this->table)
			->join($this->table_prefix . 'student st', 'i.user_id = st.id')
			->join($this->table_prefix . 'event_enrollment ee', 'i.event_enrollment_id = ee.id')
			->join($this->table_prefix . 'event e', 'ee.event_id = e.id')
//			->where_in('invoice_status', ['open'])
			->where('user_id', $studentId)
			->select('
                i.id AS invoice_id,
                e.display_name AS display_name,
                chi_name AS student_name,
                st.id AS student_id,
                student_no AS student_code,
                event_enrollment_id,
                ee.event_id AS event_id,
                invoice_status, 
                issued_name, 
                issued_date, 
                amount_to_pay, 
                amount_paid,
                amount_to_pay - amount_paid as net_amount_payable,
                payment_status,
                i.create_time
            ', false)
			->get()
			->result_array();

		// echo $this->db->last_query(); die();
		$this->load->model('Event_makeup_quota_model');
		$res = [];
		foreach ($invoices as $item) {
			$quota = $this->Event_makeup_quota_model->get_quota($item['student_id'], $item['event_enrollment_id']);
			$message = $item['payment_status'] == 'partial paid' ? '您還尚欠的學費未清，請到分店繳交。' : '您還未繳交學費，請到分店繳交。';
			$timeslots = $this->db
				->from($this->table_prefix . 'event_enrollment_item eei')
				->join($this->table_prefix . 'event_timeslot et', 'eei.event_timeslot_id = et.id')
				->where('eei.event_id', $item['event_id'])
				->where('event_enrollment_id', $item['event_enrollment_id'])
				->where('holiday_skip IS NULL', NULL, FALSE)
				->select("et.id, CONCAT(SUBSTRING(FROM_UNIXTIME(startdate), 1, 10), ' ', starttime, '-', endtime) AS course_date")
				->get()
				->result_array();
			$receipts = $this->Receipt_model->listing_in_invoice_read($item['invoice_id']);
			array_push($res, [
				'invoice_id' => $item['invoice_id'],
				'event_id' => $item['event_id'],
				'tenant_no' => $tenantNo,
				'student_id' => $item['student_id'],
				'student_name' => $item['student_name'],
				'student_code' => $item['student_code'],
				'display_name' => $item['display_name'],
				'issued_date' => date('Y-m-d H:i', $item['issued_date'] ? $item['issued_date'] : $item['create_time']),
				'issued_name' => $item['issued_name'] ?: "",
				'message' => $message,
				'amount_to_pay' => $item['amount_to_pay'],
				'amount_paid' => $item['amount_paid'],
				'net_amount_payable' => $item['net_amount_payable'],
				'timeslots' => $timeslots,
				'quota' => $quota,
				'receipts' => $receipts,
				// 'url' => sprintf('/api/invoicing_invoice?invoice_id=%s&tenant_no=%s&output=F', $item['invoice_id'], $tenantNo),
			]);
		}

		return $res;
	}
}
