<?php

include "header.php";

$sum_facility = 0;
// M1
$sum_A40002 = 0;
// 設備
$sum_A40009 = 0;
// 安裝
$sum_A40010 = 0;
$sum_total_budget = 0;
$average_budget = 0;
$average_A40002 = 0;
$average_A40009 = 0;
$average_A40010 = 0;


$follower = find_follow($user_id);
$arrayData = [];
$receivable_array = [];
// T8所有合約號
$sql_contractNumber = "SELECT Distinct BillNo FROM salSalesOrder WHERE ModeId = 'T' AND (CurrentState=2 OR CurrentState=4) ";

// T8發票 (有專案的發票)
$sql_invoice = "SELECT DISTINCT	
a.InvoiceNo, a.InvoiceTime,a.OAmountWithTax,a.InvoiceState, 
b.FromSalSalesOrder 
FROM  arSellInvoice as a 
LEFT JOIN (SELECT CH.FromSalSalesOrder, SE.* FROM arSellInvoiceMaterial AS SE 
LEFT JOIN arCheckBillDetail AS CH ON SE.FromBillNo=CH.BillNo) as b 
ON a.BillNo=b.BillNo 
WHERE DATALENGTH(b.FromSalSalesOrder)>0
ORDER BY InvoiceTime ASC";

// T8核銷
$sql_received = "SELECT 
a.BillNo, a.WriteOffBizPartnerId,a.PayWriteOffOAmount,
b.FromBillDate AS ReceivedDate,b.CurrWOFeeOAmt AS Fee, 
c.OrderBillNo, c.checkBillNo, c.InvoiceNo, c.BillDate AS CheckBillDate
FROM arWriteOffBill AS a 
LEFT JOIN arWriteOffBillRec AS b ON a.BillNo=b.BillNo 
LEFT JOIN 
    (SELECT temp1.*,arWriteOffBillDetail.*  FROM arWriteOffBillDetail 
    LEFT JOIN 
        (SELECT 
        arCheckBill.BillNo AS checkBillNo, arCheckBill.BillDate,arCheckBillInvInfo.InvoiceNo 
        FROM arCheckBill 
        LEFT JOIN arCheckBillInvInfo 
        ON arCheckBill.InvoiceBillNo=arCheckBillInvInfo.InvoiceBillNo) AS temp1 
    ON temp1.checkBillNo = arWriteOffBillDetail.FromBillNo) AS c 
ON a.BillNo=c.BillNo
WHERE DATALENGTH(c.OrderBillNo) >0";


// 期初收款資訊
$sql_opening = "
SELECT * FROM account_received ";
// 設定權限：看到自己的/下屬的，資訊處跟財會處可以看全部的
if ((in_array($user_id, array('M0060', 'M0175'))) || (in_array(accountidToDepartId($user_id), array('220', '210')))) {
} else {
    $sql_opening .= " OR person_id = '$user_id'";
    $sql_contractNumber .= " AND (PersonId = '$user_id'";
    if (count($follower) > 0) {
        $column_str = "('$user_id'" . ",'";
        $column_str .= implode("','", $follower);
        $column_str .= "')";
        $sql_contractNumber .= " OR PersonId IN $column_str)";
        $sql_opening .= " OR person_id IN $column_str)";
    } else {
        $sql_opening .= ")";
        $sql_contractNumber .= ")";
    };
}

$contractNumbers = array();
$query_contract_number = $conn->query($sql_contractNumber);
foreach ($query_contract_number as $contract_numbers) {
    array_push($contractNumbers, $contract_numbers['BillNo']);
    $str_numbers = "('" . implode("','", $contractNumbers) . "')";
}
//作番大日程
$sql = "SELECT
tmp2.*,
d.name AS depart_name
FROM(
SELECT 
 a.manager,
 a2.name as manager_name,
 a.name,
 a.department_id,
 w.salesid,
 w.contractno,
 w.facilityno,
 w.estimated_shipping_date,
 w.real_contract_arrival_date,
w.actual_tofactory_date,
 w.real_arrival_date,
 w.install_end_date,
 w.tryrun_end_date,
 w.official_check_date,
 w.delivery_date
from wipwholestatus AS w
left join account AS a
ON w.salesid = a.accountid
left join account AS a2
ON a2.accountid = a.manager
where w.status = '1' AND  w.contract_type='B'
)AS tmp2
left join (
SELECT DISTINCT 
 department_id,
 name
FROM department

) AS d
ON d.department_id = tmp2.department_id";

//T8 銷售訂單 階段收款計畫
$sql_contract = "SELECT a.BillNo,s.BillDate, a.RowNo, a.PayStage, a.PlanPayAmt, a.PlanPayDate,s.BizPartnerId,c.BizPartnerName, s.PersonId, s.DeptId, s.DeptName,
c.EnterpriseName, c.TaxNo, c.ContactAddress FROM salOrderStagePay AS a 
LEFT JOIN (SELECT O.*, DE.DeptName FROM salSalesOrder AS O LEFT JOIN comDepartment AS DE ON O.DeptId=DE.DeptId) AS s on a.BillNo = s.BillNo 
LEFT JOIN comBusinessPartner AS c ON s.BizPartnerId=c.BizPartnerId ";
if (isset($str_numbers)) {
    $sql_contract .= "WHERE s.BillNo IN $str_numbers ";
}


// T8 銷售訂單 作番金額
$sql_contract_budget = "SELECT A.BillNo,A.MaterialId, A.OAmountWithTax,A.CU_MaterialId FROM salSalesOrderDetail AS A 
LEFT JOIN salSalesOrder AS B ON A.BillNo=B.BillNo 
WHERE B.ModeId='T' AND A.ItemType=1 ";
if (isset($str_numbers)) {
    $sql_contract_budget .= "AND A.BillNo IN $str_numbers ";
}
$contract = $conn->query($sql_contract);
$contract_data = array();
// 將T8合約收款階段資料依據款別分類放進arraData裡
foreach ($contract as $cont) {
    $BillNo = $cont['BillNo'];
    $BillDate = $cont['BillDate'];
    $PayStage = $cont['PayStage'];
    $PlanPayDate = $cont['PlanPayDate'];
    $PayAmount = $cont['PlanPayAmt'];
    $partnerName = $cont['BizPartnerName'];
    $EnterpriseName = $cont['EnterpriseName'];
    $TaxNo = $cont['TaxNo'];
    $ContactAddress = $cont['ContactAddress'];
    $RowNo = $cont['RowNo'];
    $salesId = $cont['PersonId'];

    //['type'] A：比照新梯、B：純分期、C:其他
    //['sign'] 0 款項名稱   1 簽約金額   2 簽約日期 3 應收金額  min 最早應收月份   max 最晚應收月份   ['second'] 0 二次款名  1 二次款金額  2 收款日期   ['arrive'] 0 貨抵工地款名稱   1 貨抵工地款金額   2 貨抵工地收款日期   ['install'] 0 安裝款名   1 安裝金額   2 安裝收款日期     
    //['tryrun'] 0 試車款名   1試車金額   2 試車收款日期   ['check'] 0 官檢款名   1 官檢金額   2 官檢收款日期   ['delivery'] 0 交車款名  1 交車金額  2 交車收款日期  ['final'] 0 尾款名  1 尾款金額  2 尾款收款日期 
    //['facility'] [作番號][款別]0 款項名稱  1 合約金額  2 應收日期  3 應收金額  4 已收金額  5 催收金額  6 催收次數
    if (!(isset($arrayData[$BillNo]))) {
        // 0 部門id  1 部門名稱   2 經理名稱  3 營業員id  4 營業員名稱 5 客戶名稱 6 抬頭 7 統編 8 通訊地址 9 T8單據日期 10 合約號
        $arrayData[$BillNo] = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];
        $arrayData[$BillNo][0] = $cont['DeptId'];
        $arrayData[$BillNo][1] = $cont['DeptName'];
        $arrayData[$BillNo][3] = $salesId;
        $arrayData[$BillNo][5] = $partnerName;
        $arrayData[$BillNo][6] = $EnterpriseName;
        $arrayData[$BillNo][7] = $TaxNo;
        $arrayData[$BillNo][8] = $ContactAddress;
        $arrayData[$BillNo][9] = $BillDate;
        $arrayData[$BillNo][10] = $BillNo;
        $arrayData[$BillNo]['total_budget'] = 0;
        $arrayData[$BillNo]['invoice'] = array();
        $arrayData[$BillNo]['received_budget'] = 0;
        $arrayData[$BillNo]['invoice_budget'] = 0;
        $arrayData[$BillNo]['A40002'] = $arrayData[$BillNo]['A40009'] = $arrayData[$BillNo]['A40010'] = 0;
        $arrayData[$BillNo]['total_facility_num'] = 0;
    }
    $arrayData[$BillNo]['PayStage'][$RowNo]['type'] = 0;
    $arrayData[$BillNo]['PayStage'][$RowNo]['name'] = $PayStage;
    $arrayData[$BillNo]['PayStage'][$RowNo]['PlanPayDate'] = $PlanPayDate;
    $arrayData[$BillNo]['PayStage'][$RowNo]['PayAmount'] = $PayAmount;
    // 判斷type
    $wipStage = ['簽約', '訂金', '貨抵工地', '完工', '試車', '安裝', '驗收', '完工', '交車', '尾款', '合約'];
    foreach ($wipStage as $wipstage) {
        if (strpos($PayStage, $wipstage) !== false) {
            $arrayData[$BillNo]['PayStage'][$RowNo]['type'] = "A";
        }
    }
    $haveNumber = (int) filter_var(str_replace('-', '', $PayStage), FILTER_SANITIZE_NUMBER_INT);
    if ($haveNumber > 0 && $arrayData[$BillNo]['PayStage'][$RowNo]['type'] === 0) {
        $arrayData[$BillNo]['PayStage'][$RowNo]['type'] = "B";
    } elseif ($haveNumber > 0 && $arrayData[$BillNo]['PayStage'][$RowNo]['type'] == 'A') {
        $arrayData[$BillNo]['PayStage'][$RowNo]['type'] = "C";
    }
}
$received_array = $conn->query($sql_received);
$opening_data = mysqli_query($link, $sql_opening);
$invoice_data = $conn->query($sql_invoice);
$contract_budget_data = $conn->query($sql_contract_budget);

// 查WMS 契約員主管
$sql_manager = "SELECT A.accountid, A.name, A.manager, B.name AS manager_name FROM account AS A 
LEFT JOIN account AS B ON A.manager = B.accountid ";
$managers = mysqli_query($link, $sql_manager);

$find_manager = array();
foreach ($managers as $manager) {
    $find_manager[$manager['accountid']] = $manager;
}
// 將T8銷售訂單明細 每個作番金額存入arrayData
$contract_budget = array();
foreach ($contract_budget_data as $cont) {
    $BillNo = $cont['BillNo'];
    if (isset($arrayData[$BillNo])) {
        $salesid = $arrayData[$BillNo][3];
        if (isset($find_manager[$salesid])) {
            $arrayData[$BillNo][2] = $find_manager[$salesid]['manager_name'];
            $arrayData[$BillNo][4] = $find_manager[$salesid]['name'];
            $arrayData[$BillNo][11] = $find_manager[$salesid]['manager'];
        }
        $arrayData[$BillNo]['total_budget'] += $cont['OAmountWithTax'];
        $sum_total_budget += $cont['OAmountWithTax'];
        $arrayData[$BillNo][$cont['MaterialId']] += $cont['OAmountWithTax'];
        switch ($cont['MaterialId']) {
            case "A40002":
                $sum_A40002 += $cont['OAmountWithTax'];
                break;
            case "A40009":
                $sum_A40009 += $cont['OAmountWithTax'];
                break;
            case "A40010":
                $sum_A40010 += $cont['OAmountWithTax'];
                break;
        }
        if (isset($arrayData[$BillNo]['facility'][$cont['CU_MaterialId']])) {
            $arrayData[$BillNo]['facility'][$cont['CU_MaterialId']]['total_budget'] += $cont['OAmountWithTax'];
            $arrayData[$BillNo]['facility'][$cont['CU_MaterialId']][$cont['MaterialId']] += $cont['OAmountWithTax'];
        } else {
            $sum_facility += 1;
            $arrayData[$BillNo]['total_facility_num'] += 1;
            $arrayData[$BillNo]['facility'][$cont['CU_MaterialId']]['total_budget'] = $cont['OAmountWithTax'];
            $arrayData[$BillNo]['facility'][$cont['CU_MaterialId']]['A40002'] = $arrayData[$BillNo]['facility'][$cont['CU_MaterialId']]['A40009'] = $arrayData[$BillNo]['facility'][$cont['CU_MaterialId']]['A40010'] = 0;
            $arrayData[$BillNo]['facility'][$cont['CU_MaterialId']][$cont['MaterialId']] = $cont['OAmountWithTax'];
        }
    }
    if (isset($contract_budget[$cont['BillNo']])) {
        $contract_budget[$cont['BillNo']]['total'] += $cont['OAmountWithTax'];
    } else {
        $contract_budget[$cont['BillNo']]['total'] = $cont['OAmountWithTax'];
    }
    if (isset($contract_budget[$cont['BillNo']][$cont['MaterialId']])) {
        $contract_budget[$cont['BillNo']][$cont['MaterialId']] += $cont['OAmountWithTax'];
    } else {
        $contract_budget[$cont['BillNo']][$cont['MaterialId']] = $cont['OAmountWithTax'];
    }

    if (isset($contract_budget[$cont['BillNo']]['facility'][$cont['CU_MaterialId']])) {
        $contract_budget[$cont['BillNo']]['facility'][$cont['CU_MaterialId']]['total'] += $cont['OAmountWithTax'];
        $contract_budget[$cont['BillNo']]['facility'][$cont['CU_MaterialId']][$cont['MaterialId']] += $cont['OAmountWithTax'];
    } else {
        $contract_budget[$cont['BillNo']]['facility'][$cont['CU_MaterialId']]['total'] = $cont['OAmountWithTax'];
        $contract_budget[$cont['BillNo']]['facility'][$cont['CU_MaterialId']]['A40002'] = 0;
        $contract_budget[$cont['BillNo']]['facility'][$cont['CU_MaterialId']]['A40009'] = 0;
        $contract_budget[$cont['BillNo']]['facility'][$cont['CU_MaterialId']]['A40010'] = 0;
        $contract_budget[$cont['BillNo']]['facility'][$cont['CU_MaterialId']][$cont['MaterialId']] = $cont['OAmountWithTax'];
    };
}

/*計算作番佔合約的金額比例
@param $facility_no string => 作番號
@param $contract_budget_billno array => $contract_budget[合約號]
return float */
function get_contract_ratio($facility_no, $contract_budget_billno)
{
    $ratio = 0;
    if (isset($contract_budget_billno['facility'][$facility_no])) {
        if (empty($contract_budget_billno['total'])) {
        } else {

            $ratio += $contract_budget_billno['facility'][$facility_no]['total'] / $contract_budget_billno['total'];
        }
    }
    return $ratio;
}

/* 比較應收日期
@param $a array
@param $b array
return int */
function comparePlanPayDate($a, $b)
{
    return strtotime($a['PlanPayDate']) - strtotime($b['PlanPayDate']);
}

//取作番大日程作番與時程 進arrayData
// real_contract_arrival_date 預計出貨日
// real_arrival_date 實際出貨日
$today = strtotime(date('Ymd'));
$wipwhole_array = mysqli_query($link, $sql);
$wipwhole_status = array();
foreach ($wipwhole_array as $wip) {
    //[合約號]['facility'] [作番號][款別]0 款項名稱  1 合約金額  2 應收日期  3 應收金額  4 已收金額  5 催收金額  6 催收次數
    if (isset($arrayData[$wip['contractno']])) {
        // 整理合約資料
        // $arrayData[$wip['contractno']][0] = $wip['department_id'];;
        // $arrayData[$wip['contractno']][1] = $wip['depart_name'];
        // $arrayData[$wip['contractno']][2] = $wip['manager_name'];
        // $arrayData[$wip['contractno']][3] = $wip['salesid'];
        // $arrayData[$wip['contractno']][4] = $wip['name'];
        $arrayData[$wip['contractno']][10] = $wip['contractno'];
        $arrayData[$wip['contractno']]['A40002'] = (isset($contract_budget[$wip['contractno']]['A40002']) && !is_null($contract_budget[$wip['contractno']]['A40002'])) ? $contract_budget[$wip['contractno']]['A40002'] : 0;
        $arrayData[$wip['contractno']]['A40009'] = (isset($contract_budget[$wip['contractno']]['A40009']) && !is_null($contract_budget[$wip['contractno']]['A40009'])) ? $contract_budget[$wip['contractno']]['A40009'] : 0;
        
        // [合約號]['facility'][作番號]['no']
        $arrayData[$wip['contractno']]['facility'][$wip['facilityno']]['no'] = $wip['facilityno'];
        $arrayData[$wip['contractno']]['facility'][$wip['facilityno']]['status'] = "";
        $arrayData[$wip['contractno']]['facility'][$wip['facilityno']]['total_budget'] = 0;
        $arrayData[$wip['contractno']]['facility'][$wip['facilityno']]['receivable_budget'] = 0;
        $arrayData[$wip['contractno']]['facility'][$wip['facilityno']]['collect_budget'] = 0;
        $arrayData[$wip['contractno']]['facility'][$wip['facilityno']]['A40002'] = (isset($contract_budget[$wip['contractno']][$wip['facilityno']]['A40002']) && !is_null($contract_budget[$wip['contractno']][$wip['facilityno']]['A40002'])) ? $contract_budget[$wip['contractno']][$wip['facilityno']]['A40002'] : 0;
        $arrayData[$wip['contractno']]['facility'][$wip['facilityno']]['A40009'] = (isset($contract_budget[$wip['contractno']][$wip['facilityno']]['A40009']) && !is_null($contract_budget[$wip['contractno']][$wip['facilityno']]['A40009'])) ? $contract_budget[$wip['contractno']][$wip['facilityno']]['A40009'] : 0;
        $arrayData[$wip['contractno']]['facility'][$wip['facilityno']]['A40010'] = (isset($contract_budget[$wip['contractno']][$wip['facilityno']]['A40010']) && !is_null($contract_budget[$wip['contractno']][$wip['facilityno']]['A40010'])) ? $contract_budget[$wip['contractno']][$wip['facilityno']]['A40010'] : 0;
    }
    $wipwhole_status[$wip['facilityno']] = $wip;
}

// 整理arrayData[合約]['PayStage']進 (新增) arrayData[合約]['facility'][作番號][PayStage]
$sign_array = ['訂金', '簽約', '合約'];
// 將所有facility的paystage放入一個array以便計算順序
$allPayStages = [];
foreach ($arrayData as &$value) {
    if (isset($value['facility']) && is_iterable($value['facility'])) {
        foreach ($value['facility'] as $fakey => &$facility) {
            if (isset($contract_budget[$value[10]])) {
                $ratio = get_contract_ratio($fakey, $contract_budget[$value[10]]);
                for ($stage = 1; $stage < (count($value['PayStage']) + 1); $stage++) {
                    $facility['PayStage'][$stage] = $value['PayStage'][$stage];
                    $facility['PayStage'][$stage]['facilityno'] = $fakey;
                    //算每個作番的金額
                    $facility['PayStage'][$stage]['PayAmount'] = $value['PayStage'][$stage]['PayAmount'] * $ratio;
                    //算每個作番每個收款階段的收款時間
                    switch ($facility['PayStage'][$stage]['type']) {
                        case "A":
                            switch ($facility['PayStage'][$stage]['name']) {
                                case (stristr($facility['PayStage'][$stage]['name'], '試車')):
                                    if (isset($wipwhole_status[$fakey]['tryrun_end_date']) && $wipwhole_status[$fakey]['tryrun_end_date'] !== NULL && !empty($wipwhole_status[$fakey]['tryrun_end_date'])) {
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime($wipwhole_status[$fakey]['tryrun_end_date']));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '完工')):
                                    if (isset($wipwhole_status[$fakey]['delivery_date']) && $wipwhole_status[$fakey]['delivery_date'] !== NULL && !empty($wipwhole_status[$fakey]['delivery_date'])) {
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime($wipwhole_status[$fakey]['delivery_date']));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '尾款')):
                                    if (isset($wipwhole_status[$fakey]['delivery_date']) && $wipwhole_status[$fakey]['delivery_date'] !== NULL && !empty($wipwhole_status[$fakey]['delivery_date'])) {
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime($wipwhole_status[$fakey]['delivery_date']));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '交車')):
                                    if (isset($wipwhole_status[$fakey]['delivery_date']) && $wipwhole_status[$fakey]['delivery_date'] !== NULL && !empty($wipwhole_status[$fakey]['delivery_date'])) {
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime($wipwhole_status[$fakey]['delivery_date']));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '驗收')):
                                    if (isset($wipwhole_status[$fakey]['delivery_date']) && $wipwhole_status[$fakey]['delivery_date'] !== NULL && !empty($wipwhole_status[$fakey]['delivery_date'])) {
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime($wipwhole_status[$fakey]['delivery_date']));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '安裝')):
                                    if (isset($wipwhole_status[$fakey]['install_end_date']) && $wipwhole_status[$fakey]['install_end_date'] !== NULL && !empty($wipwhole_status[$fakey]['install_end_date'])) {
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime($wipwhole_status[$fakey]['install_end_date']));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '貨抵工地')):
                                    if (isset($wipwhole_status[$fakey]['real_arrival_date']) && $wipwhole_status[$fakey]['real_arrival_date'] !== NULL && !empty($wipwhole_status[$fakey]['real_arrival_date'])) {
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime($wipwhole_status[$fakey]['real_arrival_date']));
                                    } elseif (isset($wipwhole_status[$fakey]['real_contract_arrival_date']) && $wipwhole_status[$fakey]['real_contract_arrival_date'] !== NULL && !empty($wipwhole_status[$fakey]['real_contract_arrival_date'])) {
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime($wipwhole_status[$fakey]['real_contract_arrival_date']));
                                    }
                                    break;
                                default:
                                    break;
                            };

                        case "B":

                            break;
                        case "C":
                            switch ($facility['PayStage'][$stage]['name']) {
                                case (stristr($facility['PayStage'][$stage]['name'], '試車')):
                                    if (isset($wipwhole_status[$fakey]['tryrun_end_date']) && $wipwhole_status[$fakey]['tryrun_end_date'] !== NULL && !empty($wipwhole_status[$fakey]['tryrun_end_date'])) {
                                        $haveNumber = ((int) filter_var(str_replace('-', '', $facility['PayStage'][$stage]['name']), FILTER_SANITIZE_NUMBER_INT)) - 1;
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime("+ $haveNumber months", strtotime($wipwhole_status[$fakey]['tryrun_end_date'])));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '完工')):
                                    if (isset($wipwhole_status[$fakey]['delivery_date']) && $wipwhole_status[$fakey]['delivery_date'] !== NULL && !empty($wipwhole_status[$fakey]['delivery_date'])) {
                                        $haveNumber = ((int) filter_var(str_replace('-', '', $facility['PayStage'][$stage]['name']), FILTER_SANITIZE_NUMBER_INT)) - 1;
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime("+ $haveNumber months", strtotime($wipwhole_status[$fakey]['delivery_date'])));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '尾款')):
                                    if (isset($wipwhole_status[$fakey]['delivery_date']) && $wipwhole_status[$fakey]['delivery_date'] !== NULL && !empty($wipwhole_status[$fakey]['delivery_date'])) {
                                        $haveNumber = ((int) filter_var(str_replace('-', '', $facility['PayStage'][$stage]['name']), FILTER_SANITIZE_NUMBER_INT)) - 1;
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime("+ $haveNumber months", strtotime($wipwhole_status[$fakey]['delivery_date'])));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '交車')):
                                    if (isset($wipwhole_status[$fakey]['delivery_date']) && $wipwhole_status[$fakey]['delivery_date'] !== NULL && !empty($wipwhole_status[$fakey]['delivery_date'])) {
                                        $haveNumber = ((int) filter_var(str_replace('-', '', $facility['PayStage'][$stage]['name']), FILTER_SANITIZE_NUMBER_INT)) - 1;
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime("+ $haveNumber months", strtotime($wipwhole_status[$fakey]['delivery_date'])));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '驗收')):
                                    if (isset($wipwhole_status[$fakey]['delivery_date']) && $wipwhole_status[$fakey]['delivery_date'] !== NULL && !empty($wipwhole_status[$fakey]['delivery_date'])) {
                                        $haveNumber = ((int) filter_var(str_replace('-', '', $facility['PayStage'][$stage]['name']), FILTER_SANITIZE_NUMBER_INT)) - 1;
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime("+ $haveNumber months", strtotime($wipwhole_status[$fakey]['delivery_date'])));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '安裝')):
                                    if (isset($wipwhole_status[$fakey]['install_end_date']) && $wipwhole_status[$fakey]['install_end_date'] !== NULL && !empty($wipwhole_status[$fakey]['install_end_date'])) {
                                        $haveNumber = ((int) filter_var(str_replace('-', '', $facility['PayStage'][$stage]['name']), FILTER_SANITIZE_NUMBER_INT)) - 1;
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime("+ $haveNumber months", strtotime($wipwhole_status[$fakey]['install_end_date'])));
                                    }
                                    break;
                                case (stristr($facility['PayStage'][$stage]['name'], '貨抵工地')):
                                    if (isset($wipwhole_status[$fakey]['real_arrival_date']) && $wipwhole_status[$fakey]['real_arrival_date'] !== NULL && !empty($wipwhole_status[$fakey]['real_arrival_date'])) {
                                        $haveNumber = ((int) filter_var(str_replace('-', '', $facility['PayStage'][$stage]['name']), FILTER_SANITIZE_NUMBER_INT)) - 1;
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime("+ $haveNumber months", strtotime($wipwhole_status[$fakey]['real_arrival_date'])));
                                    } elseif (isset($wipwhole_status[$fakey]['real_contract_arrival_date']) && $wipwhole_status[$fakey]['real_contract_arrival_date'] !== NULL && !empty($wipwhole_status[$fakey]['real_contract_arrival_date'])) {
                                        $haveNumber = ((int) filter_var(str_replace('-', '', $facility['PayStage'][$stage]['name']), FILTER_SANITIZE_NUMBER_INT)) - 1;
                                        $facility['PayStage'][$stage]['PlanPayDate'] = date("Ymd", strtotime("+ $haveNumber months", strtotime($wipwhole_status[$fakey]['real_contract_arrival_date'])));
                                    }
                                    break;
                                default:
                                    break;
                            };
                        default:
                            break;
                    }
                }
                if (isset($allPayStages[$value[10]])) {
                    $allPayStages[$value[10]] = array_merge($allPayStages[$value[10]], $facility['PayStage']);
                } else {
                    $allPayStages[$value[10]] = $facility['PayStage'];
                }
            }
        }
    }
}
foreach ($allPayStages as $paykey => $pay) {
    uasort($allPayStages[$paykey], 'comparePlanPayDate');
}

foreach ($allPayStages as &$payStage) {
    $sequenceNumber = 1;
    foreach ($payStage as $tk => &$pstage) {
        $pstage['sequenceNumber'] = $sequenceNumber;
        $sequenceNumber += 1;
    }
}
// 整理好的收款階段
$final_paystage = array();
foreach ($allPayStages as $key => &$payStage) {
    if (is_iterable($payStage)) {
        foreach ($payStage as $key2 => $pstage) {
            $final_paystage[$key][$pstage['sequenceNumber']] = $pstage;
        }
    }
}

// [invoice][發票號碼][0 發票日期, 1 發票金額, 2 發票狀態, 3 收款日期, 4 收款金額, 5 手續費, 6 核銷單號] 
foreach ($invoice_data as $invoice) {
    if (isset($arrayData[$invoice['FromSalSalesOrder']])) {
        $arrayData[$invoice['FromSalSalesOrder']]['invoice_budget'] += intval($invoice['OAmountWithTax']);
        $arrayData[$invoice['FromSalSalesOrder']]['invoice'][$invoice['InvoiceNo']] = [date("Ymd", strtotime($invoice['InvoiceTime'])), $invoice['OAmountWithTax'], $invoice['InvoiceState']];
    }
}

// 把核銷單的內容補到發票資料中
foreach ($received_array as $received) {
    $BillNo = $received['OrderBillNo'];
    if (isset($arrayData[$BillNo])) {
        if (isset($arrayData[$BillNo]['invoice'][$received['InvoiceNo']])) {
            $arrayData[$BillNo]['received_budget'] += $received['PayWriteOffOAmount'];
            array_push($arrayData[$BillNo]['invoice'][$received['InvoiceNo']], date($received['ReceivedDate']), $received['PayWriteOffOAmount'], $received['Fee'], $received['BillNo']);
        }
    }
}
// $final_paystage ['type'] ['PlanPayDate'] ['PayAmount'] ['facilityno'] ['sequenceNumber'] ['name'] ['InvoiceAmount']  ['receivedAmount']
foreach ($final_paystage as $key => &$payStage) {
    if (isset($arrayData[$key]['invoice_budget']) && isset($arrayData[$key]['received_budget'])) {
        $tmpInvoice = $arrayData[$key]['invoice_budget'];
        $tmpReceived = $arrayData[$key]['received_budget'];
        for ($sequence = 1; $sequence <= count($payStage); $sequence++) {
            $collect_month = collect_month(strtotime($payStage[$sequence]['PlanPayDate']));
            if ($tmpInvoice > $payStage[$sequence]['PayAmount']) {
                $payStage[$sequence]['InvoiceAmount'] = $payStage[$sequence]['PayAmount'];
                $tmpInvoice -= $payStage[$sequence]['PayAmount'];
            } else {
                $payStage[$sequence]['InvoiceAmount'] = $tmpInvoice;
                $tmpInvoice = 0;
            }

            if ($tmpReceived > $payStage[$sequence]['PayAmount']) {

                $payStage[$sequence]['receivedAmount'] = $payStage[$sequence]['PayAmount'];
                $tmpReceived -= $payStage[$sequence]['PayAmount'];
            } else {
                $payStage[$sequence]['receivedAmount'] = $tmpReceived;
                $tmpReceived = 0;
            }
            if ($collect_month > 0) {
                // $arrayData[$key]['facility'][$payStage[$sequence]['facilityno']]['receivable_budget']+=$payStage[$sequence]['PayAmount'];
            }
            if ($collect_month > 0 && $payStage[$sequence]['PayAmount'] > $payStage[$sequence]['receivedAmount']) {
                $payStage[$sequence]['collect_month'] = $collect_month;
            } else {
                $payStage[$sequence]['collect_month'] = 0;
            }
        }
    }
}

// 0 部門id  1 部門名稱   2 經理名稱  3 營業員id  4 營業員名稱 5 客戶名稱 6 抬頭 7 統編 8 通訊地址 9 T8單據日期 10 合約號
// 0 合約號 1 作番號 2 部門id 3 部門名稱 4 經理名稱 5 營業員id 6 營業員名稱 7 客戶名稱 8 抬頭 9 統編 10 通訊地址 
// 11 收款階段名稱 12 應收日期 13 收款金額 14 已開金額 15 已收金額 16 催收次數
$excel_aray = array();
$today = strtotime(date('Ymt'));
foreach ($arrayData as $key => $value) {
    if (isset($final_paystage[$key])) {
        for ($sequence = 1; $sequence <= count($final_paystage[$key]); $sequence++) {
            $value[4] = (isset($value[4]) && !empty($value[4])) ? $value[4] : '--';
            $value[5] = (isset($value[5]) && !empty($value[5])) ? $value[5] : '--';
            $value[6] = (isset($value[6]) && !empty($value[6])) ? $value[6] : '--';
            $value[7] = (isset($value[7]) && !empty($value[7])) ? $value[7] : '--';
            $value[8] = (isset($value[8]) && !empty($value[8])) ? $value[8] : '--';
            $value[11] = (isset($value[11]) && !empty($value[11])) ? $value[11] : '--';
            $value[12] = (isset($value[12]) && !empty($value[12])) ? date('Y/m/d', strtotime($value[12])) : '--';
            $thisPayStage = $final_paystage[$key][$sequence];
            $keyname = $key . "/" . $final_paystage[$key][$sequence]['facilityno'] . "/" . $value[5];
            $havetopay = ($today >= strtotime($thisPayStage['PlanPayDate'])) ? "V" : "--";
            switch ($havetopay) {
                case "V":
                    $unInvoiceAmount = $thisPayStage['PayAmount'] - $thisPayStage['InvoiceAmount'];
                    $unReceivedAmount = $thisPayStage['PayAmount'] - $thisPayStage['receivedAmount'];
                    break;
                default:
                    $unInvoiceAmount = 0;
                    $unReceivedAmount = 0;
                    break;
            }
            $excel_aray[$keyname] = [
                $value[10], $thisPayStage['facilityno'], $value[0], $value[1], $value[11], $value[2], $value[3], $value[4], $value[5], $value[6], $value[7], $value[8],
                $thisPayStage['name'], $thisPayStage['PlanPayDate'], $havetopay,
                number_format(round($thisPayStage['PayAmount'])),
                number_format(round($thisPayStage['InvoiceAmount'])), number_format(round($unInvoiceAmount)),
                number_format(round($thisPayStage['receivedAmount'])), number_format(round($unReceivedAmount)),
                number_format(round($thisPayStage['collect_month']))

            ];
        }
    }
}
$total_data = json_encode($excel_aray);
//計算總作番數、金額、A40002、A40009、A40010金額與平均
$average_budget = round($sum_total_budget / $sum_facility);
$average_A40002 = round($sum_A40002 / $sum_facility);
$average_A40009 = round($sum_A40009 / $sum_facility);
$average_A40010 = round($sum_A40010 / $sum_facility);

foreach($arrayData as $key=> $data){
    echo $key."<br>";
    print_r($data);
    echo "<br>-----------------------------<br>";
}
exit();
?>
<style>
    table {
        table-layout: fixed;
        width: 100%;
    }

    td {
        word-wrap: break-word;
    }

    img {
        width: 125px;
    }

    .width_style_1 {
        width: 125px;
    }

    table {
        width: 100%;
    }

    #table_index_filter {
        float: right;
    }

    #table_index_paginate {
        float: right;
    }

    label {
        display: inline-flex;
        margin-bottom: .5rem;
        margin-top: .5rem;

    }

    .hiddenTable {
        display: none;
    }

    .hidden {
        display: none;
    }
</style>

<div style="width: 98%;margin: 1%;">
    <div class="btn-group btn-group-md " style="padding:10 px; width: 100%;">
        <button type="button" style="width: 12%;" onclick="downloadData()" class="btn btn-success">下載 <span class="glyphicon glyphicon-download-alt"></span></button>
    </div>
</div>
<div class="container">
    <div class="text-center" style="margin-bottom: 20px;">
        <h3><strong>合約 (汰改)統整資訊</strong></h3>
    </div>
    <form class="form-horizontal">
        <div class="form-group">
            <div class="col-md-3">
                <label for="sum_total_budget">合約總金額</label>
                <input type="text" class="form-control" id="sum_total_budget" name="sum_total_budget" value="<?= number_format($sum_total_budget) ?>" disabled>
            </div>
            <div class="col-md-3">
                <label for="sum_A40002">M1總金額</label>
                <input type="text" class="form-control" id="sum_A40002" name="sum_A40002" value="<?= number_format($sum_A40002) ?>" disabled>
            </div>
            <div class="col-md-3">
                <label for="sum_A40009">設備總金額</label>
                <input type="text" class="form-control" id="sum_A40009" name="sum_A40009" value="<?= number_format($sum_A40009) ?>" disabled>
            </div>
            <div class="col-md-3">
                <label for="sum_A40010">安裝總金額</label>
                <input type="text" class="form-control" id="sum_A40010" name="sum_A40010" value="<?= number_format($sum_A40010) ?>" disabled>
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-6">
                <label for="sum_facility">總台數</label>
                <input type="text" class="form-control" id="sum_facility" name="sum_facility" value="<?= number_format($sum_facility) ?>" disabled>
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-3">
                <label for="average_budget">平均金額</label>
                <input type="text" class="form-control" id="average_budget" name="average_budget" value="<?= number_format($average_budget) ?>" disabled>
            </div>
            <div class="col-md-3">
                <label for="average_A40002">M1平均金額</label>
                <input type="text" class="form-control" id="average_A40002" name="average_A40002" value="<?= number_format($average_A40002) ?>" disabled>
            </div>
            <div class="col-md-3">
                <label for="average_A40009">設備平均金額</label>
                <input type="text" class="form-control" id="average_A40009" name="average_A40009" value="<?= number_format($average_A40009) ?>" disabled>
            </div>
            <div class="col-md-3">
                <label for="average_A40010">安裝平均金額</label>
                <input type="text" class="form-control" id="average_A40010" name="average_A40010" value="<?= number_format($average_A40010) ?>" disabled>
            </div>
        </div>
    </form>
</div>
<?php
include "./footer.php";
?>
<div style="width: 20%;float:right;margin-right:5%; ">
    <label>查詢條件</label>
    <input type="text" id="search" name="search" class="form-control" placeholder="請輸入合約號/作番號/客戶名稱" oninput="searchData()">
</div>
<div style="width: 98%; margin:1%;" style="overflow-x:auto;">
    <table class="table table-striped table-bordered" style="width:100%">
        <thead>
            <tr>
                <th style='text-align:center;vertical-align: middle;width:120px'>合約號</th>
                <th style='text-align:center;vertical-align: middle;width:120px'>作番號</th>
                <th style='text-align:center;vertical-align: middle;width:110px'>部門</th>
                <th style='text-align:center;vertical-align: middle;'>主管</th>
                <th style='text-align:center;vertical-align: middle;'>契約員</th>
                <th style='text-align:center;vertical-align: middle;width:130px'>客戶名稱</th>
                <th style='text-align:center;vertical-align: middle;'>收款階段</th>
                <th style='text-align:center;vertical-align: middle;'>應收日期</th>
                <th style='text-align:center;vertical-align: middle;'>是否應收</th>
                <th style='text-align:end;vertical-align: middle;'>應收金額</th>
                <th style='text-align:end;vertical-align: middle;'>已開金額</th>
                <th style='text-align:end;vertical-align: middle;'>未開金額</th>
                <th style='text-align:end;vertical-align: middle;'>已收金額</th>
                <th style='text-align:end;vertical-align: middle;'>催收金額</th>
                <th style='text-align:center;vertical-align: middle;'>催收次數</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($excel_aray as $key => $row) { ?>
                <tr class="data-row" style="display: '';" id="<?= $key; ?>">
                    <td><?= $row[0]; ?></td>
                    <td><?= $row[1]; ?></td>
                    <td><?= $row[2] . "<br>" . $row[3]; ?></td>
                    <td><?= $row[4] . "<br>" . $row[5]; ?></td>
                    <td><?= $row[6] . "<br>" . $row[7]; ?></td>
                    <td><?= $row[8]; ?></td>
                    <td><?= $row[12]; ?></td>
                    <td><?= $row[13]; ?></td>
                    <td><?= $row[14]; ?></td>
                    <td><?= $row[15]; ?></td>
                    <td><?= $row[16]; ?></td>
                    <td><?= $row[17]; ?></td>
                    <td><?= $row[18]; ?></td>
                    <td><?= $row[19]; ?></td>
                    <td><?= $row[20]; ?></td>
                </tr>
            <?php } ?>
        </tbody>
    </table>
</div>

<script>
    function searchData() {
        var searchTerm = document.getElementById('search').value.toLowerCase();
        var rows = document.getElementsByClassName('data-row');
        for (var i = 0; i < rows.length; i++) {
            var rowText = rows[i].textContent.toLowerCase();
            // var rowText = rows[i].id.toLowerCase();
            if (rowText.includes(searchTerm)) {
                console.log(rowText);
                rows[i].style.display = '';
            } else {
                rows[i].style.display = 'none';
            }
        }
    }

    function downloadData() {
        var xhr = new XMLHttpRequest();
        var url = window.location.origin + "/wms/account-receivable-renovate-excel.php?type=all&<?= $token_link ?>";
        xhr.open('POST', url, true);
        xhr.setRequestHeader('Content-Type', 'application/json');
        xhr.onreadystatechange = function() {
            if (xhr.readyState === 4 && xhr.status === 200) {
                var file_path = xhr.responseText;
                var link = document.createElement('a');
                var name = "汰改應收帳款" + "<?= date('Y-m-d-Hm') ?>" + ".xlsx";
                link.setAttribute('href', window.location.origin + "/wms/account-receivable-renovate.xlsx");
                link.setAttribute('download', name);
                link.style.display = 'none';
                document.body.appendChild(link);
                link.click();
                document.body.removeChild(link);
            }
        }
        xhr.send(JSON.stringify({
            Bill: <?= $total_data ?>
        }));

    }

    function check(searchname) {
        console.log(searchname);

    }
</script>