You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1743 lines
106 KiB
1743 lines
106 KiB
<?php
|
|
include "./header.php";
|
|
$start_date = empty($_POST['date_start']) ? null : $_POST['date_start'];
|
|
$end_date = empty($_POST['date_end']) ? null : $_POST['date_end'];
|
|
$sum_facility = 0;
|
|
$sum_contract = 0;
|
|
$sum_A40001 = 0;
|
|
$sum_A40008 = 0;
|
|
$sum_total_budget = 0;
|
|
$average_facility_budget = 0;
|
|
$average_A40001 = 0;
|
|
$average_A40008 = 0;
|
|
$sum_invoice_budget = 0;
|
|
$sum_received_budget = 0;
|
|
$sum_receivable_budget = 0;
|
|
$sum_collect_budget = 0;
|
|
|
|
// 主要array
|
|
$arrayData = array();
|
|
// 收款階段另存array整理收款順序
|
|
$PayStage_array = array();
|
|
// 所有的新梯合約號，方便後續SQL查詢
|
|
$contractNumbers = array();
|
|
|
|
/* 比較應收日期
|
|
@param $a array
|
|
@param $b array
|
|
return int */
|
|
function comparePlanPayDate($a, $b)
|
|
{
|
|
// return strtotime($a['PlanPayDate']) - strtotime($b['PlanPayDate']);
|
|
$aDate = strtotime($a['PlanPayDate']);
|
|
$bDate = strtotime($b['PlanPayDate']);
|
|
|
|
if ($aDate === false && $bDate === false) {
|
|
return 0; // 兩個日期都無效，視為相等
|
|
} elseif ($aDate === false) {
|
|
return 1; // $aDate 無效，認為 $aDate 較大
|
|
} elseif ($bDate === false) {
|
|
return -1; // $bDate 無效，認為 $bDate 較大
|
|
}
|
|
return $aDate - $bDate;
|
|
}
|
|
|
|
/* 計算比例
|
|
@param $facilityno string
|
|
@param $contractno string
|
|
@param $arrayData array
|
|
return float */
|
|
function get_ratio($facilityno, $contractno, $arrayData)
|
|
{
|
|
$ratio = 0;
|
|
if (isset($arrayData[$contractno])) {
|
|
$this_total = $arrayData[$contractno]['total_budget'];
|
|
if (isset($arrayData[$contractno]['facility'][$facilityno]['total_budget'])) {
|
|
$this_facility = $arrayData[$contractno]['facility'][$facilityno]['total_budget'];
|
|
$ratio = $this_facility / $this_total;
|
|
}
|
|
}
|
|
return $ratio;
|
|
}
|
|
|
|
// 查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('員工姓名', '主管工號', '主管姓名');
|
|
// $find_manager = array();
|
|
// if (is_iterable($managers)) {
|
|
// foreach ($managers as $manager) {
|
|
// $find_manager[$manager['accountid']] = $manager;
|
|
// }
|
|
// }
|
|
$follower = find_follow($user_id);
|
|
|
|
// T8所有合約號
|
|
$sql_all_contract = "SELECT
|
|
MainAll.*,
|
|
Person.PersonName,
|
|
Dept.DeptName,
|
|
Dept.LeaderId,
|
|
Leader.PersonName AS LeaderName
|
|
FROM
|
|
(
|
|
SELECT
|
|
Main.BillNo,
|
|
Main.BillDate,
|
|
Main.BizPartnerId,
|
|
Biz.ShortName,
|
|
Biz.BizPartnerName,
|
|
Biz.ContactAddress,
|
|
Main.OAmountWithTax,
|
|
Main.PersonId,
|
|
Main.DeptId,
|
|
Main.ModeId,
|
|
Main.CurrentState
|
|
FROM
|
|
salSalesOrder AS Main
|
|
LEFT JOIN comBusinessPartner AS Biz ON Biz.BizPartnerId = Main.BizPartnerId
|
|
) AS MainAll
|
|
LEFT JOIN comGroupPerson AS Person ON MainAll.PersonId = Person.PersonId
|
|
LEFT JOIN comDepartment AS Dept ON MainAll.DeptId = Dept.DeptId
|
|
LEFT JOIN comGroupPerson AS Leader ON Dept.LeaderId = Leader.PersonId
|
|
WHERE
|
|
MainAll.ModeId = 'M' AND (MainAll.CurrentState = 2 OR MainAll.CurrentState = 4) ";
|
|
if (!is_null($start_date)) {
|
|
$start_date = (int)date('Ymd', strtotime($start_date));
|
|
$sql_all_contract .= " AND MainAll.BillDate >= $start_date ";
|
|
}
|
|
if (!is_null($end_date)) {
|
|
$end_date = (int)date('Ymd', strtotime($end_date));
|
|
$sql_all_contract .= " AND MainAll.BillDate <= $end_date ";
|
|
}
|
|
if ((in_array($user_id, array('M0060', 'M0175','M0168'))) || (in_array(accountidToDepartId($user_id), array('220', '210', '321')))) {
|
|
} else {
|
|
$sql_all_contract .= " AND (MainAll.PersonId = '$user_id'";
|
|
if (count($follower) > 0) {
|
|
$column_str = "('$user_id'" . ",'";
|
|
$column_str .= implode("','", $follower);
|
|
$column_str .= "')";
|
|
$sql_all_contract .= " OR MainAll.PersonId IN $column_str)";
|
|
} else {
|
|
$sql_all_contract .= ")";
|
|
};
|
|
}
|
|
$sql_all_contract .= " ORDER BY MainAll.PersonId ASC ";
|
|
$str_numbers = "";
|
|
$query_all_contract = $conn->query($sql_all_contract);
|
|
if (is_iterable($query_all_contract)) {
|
|
foreach ($query_all_contract as $contract_numbers) {
|
|
if (!in_array($contract_numbers['BillNo'], $contractNumbers)) {
|
|
array_push($contractNumbers, $contract_numbers['BillNo']);
|
|
$arrayData[$contract_numbers['BillNo']]['BillNo'] = isset($contract_numbers['BillNo']) ? $contract_numbers['BillNo'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['BillDate'] = isset($contract_numbers['BillDate']) ? $contract_numbers['BillDate'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['CustomerId'] = isset($contract_numbers['BizPartnerId']) ? $contract_numbers['BizPartnerId'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['CustomerName'] = isset($contract_numbers['BizPartnerName']) ? $contract_numbers['BizPartnerName'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['CustomerAddress'] = isset($contract_numbers['ContactAddress']) ? $contract_numbers['ContactAddress'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['OAmountWithTax'] = isset($contract_numbers['OAmountWithTax']) ? $contract_numbers['OAmountWithTax'] : 0;
|
|
$arrayData[$contract_numbers['BillNo']]['PersonId'] = isset($contract_numbers['PersonId']) ? $contract_numbers['PersonId'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['DeptId'] = isset($contract_numbers['DeptId']) ? $contract_numbers['DeptId'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['PersonName'] = isset($contract_numbers['PersonName']) ? $contract_numbers['PersonName'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['DeptName'] = isset($contract_numbers['DeptName']) ? $contract_numbers['DeptName'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['ShortName'] = isset($contract_numbers['ShortName']) ? $contract_numbers['ShortName'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['ManagerId'] = isset($contract_numbers['LeaderId']) ? $contract_numbers['LeaderId'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['ManagerName'] = isset($contract_numbers['LeaderName']) ? $contract_numbers['LeaderName'] : '--';
|
|
$arrayData[$contract_numbers['BillNo']]['invoice_budget'] = 0;
|
|
$arrayData[$contract_numbers['BillNo']]['received_budget'] = 0;
|
|
$arrayData[$contract_numbers['BillNo']]['receivable_budget'] = 0;
|
|
$arrayData[$contract_numbers['BillNo']]['collect_budget'] = 0;
|
|
$arrayData[$contract_numbers['BillNo']]['A40001'] = 0;
|
|
$arrayData[$contract_numbers['BillNo']]['A40008'] = 0;
|
|
$arrayData[$contract_numbers['BillNo']]['collect_month'] = 0;
|
|
$arrayData[$contract_numbers['BillNo']]['facility_status'] = "";
|
|
$arrayData[$contract_numbers['BillNo']]['facility'] = array();
|
|
$arrayData[$contract_numbers['BillNo']]['facility_num'] = 0;
|
|
$arrayData[$contract_numbers['BillNo']]['total_budget'] = $contract_numbers['OAmountWithTax'];
|
|
$sum_total_budget += $contract_numbers['OAmountWithTax'];
|
|
$sum_contract += 1;
|
|
}
|
|
}
|
|
$str_numbers .= "('" . implode("','", $contractNumbers) . "')";
|
|
}
|
|
|
|
// T8 銷售訂單 作番總金額、作番A40001金額、作番A40008金額、合約A40001金額、合約A40008金額
|
|
$sql_contract_budget = "SELECT A.BillNo, A.OAmountWithTax,A.CU_MaterialId, A.MaterialId FROM salSalesOrderDetail AS A
|
|
LEFT JOIN salSalesOrder AS B ON A.BillNo=B.BillNo
|
|
WHERE B.ModeId='M' AND A.ItemType=1 ";
|
|
$query_contract_budget = $conn->query($sql_contract_budget);
|
|
foreach ($query_contract_budget as $contract_budget) {
|
|
if (isset($arrayData[$contract_budget['BillNo']])) {
|
|
$arrayData[$contract_budget['BillNo']][$contract_budget['MaterialId']] += $contract_budget['OAmountWithTax'];
|
|
// if($contract_budget['BillNo']=='SO20230801002'){
|
|
// echo $contract_budget['OAmountWithTax'];
|
|
// echo "<br>";
|
|
// echo $contract_budget['MaterialId'];
|
|
// echo "<br>";
|
|
// echo $contract_budget['CU_MaterialId'];
|
|
// echo "<br>";
|
|
// }
|
|
if (isset($arrayData[$contract_budget['BillNo']]['facility']) && isset($arrayData[$contract_budget['BillNo']]['facility'][$contract_budget['CU_MaterialId']])) {
|
|
$arrayData[$contract_budget['BillNo']]['facility'][$contract_budget['CU_MaterialId']]['total_budget'] += $contract_budget['OAmountWithTax'];
|
|
if (isset($arrayData[$contract_budget['BillNo']]['facility'][$contract_budget['CU_MaterialId']][$contract_budget['MaterialId']])) {
|
|
$arrayData[$contract_budget['BillNo']]['facility'][$contract_budget['CU_MaterialId']][$contract_budget['MaterialId']] += $contract_budget['OAmountWithTax'];
|
|
} else {
|
|
$arrayData[$contract_budget['BillNo']]['facility'][$contract_budget['CU_MaterialId']][$contract_budget['MaterialId']] = $contract_budget['OAmountWithTax'];
|
|
}
|
|
} else {
|
|
$arrayData[$contract_budget['BillNo']]['facility'][$contract_budget['CU_MaterialId']]['total_budget'] = $contract_budget['OAmountWithTax'];
|
|
$arrayData[$contract_budget['BillNo']]['facility'][$contract_budget['CU_MaterialId']][$contract_budget['MaterialId']] = $contract_budget['OAmountWithTax'];
|
|
$arrayData[$contract_budget['BillNo']]['facility'][$contract_budget['CU_MaterialId']]['status'] = "";
|
|
$sum_facility += 1;
|
|
$arrayData[$contract_budget['BillNo']]['facility_num'] += 1;
|
|
}
|
|
switch ($contract_budget['MaterialId']) {
|
|
case "A40001":
|
|
$sum_A40001 += $contract_budget['OAmountWithTax'];
|
|
break;
|
|
case "A40008":
|
|
$sum_A40008 += $contract_budget['OAmountWithTax'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
//T8 銷售訂單 階段收款計畫
|
|
$sql_PayStage = "SELECT
|
|
a.RowNo,
|
|
a.BillNo,
|
|
s.BillDate,
|
|
a.PayStage,
|
|
a.PlanPayAmt,
|
|
a.PlanPayDate,
|
|
s.BizPartnerId,
|
|
s.PersonId
|
|
FROM salOrderStagePay AS a
|
|
LEFT JOIN salSalesOrder AS s on a.BillNo = s.BillNo
|
|
WHERE s.ModeId = 'M' AND (s.CurrentState=2 OR s.CurrentState=4) ";
|
|
if (!empty($str_numbers)) {
|
|
$sql_PayStage .= " AND s.BillNo IN " . $str_numbers;
|
|
}
|
|
|
|
$query_PayStage = $conn->query($sql_PayStage);
|
|
// 把階段收款計畫放進arrayData內
|
|
foreach ($query_PayStage as $PayStage) {
|
|
if (isset($arrayData[$PayStage['BillNo']])) {
|
|
$arrayData[$PayStage['BillNo']]['PayStage'][$PayStage['RowNo']]['PayStage'] = $PayStage['PayStage'];
|
|
$arrayData[$PayStage['BillNo']]['PayStage'][$PayStage['RowNo']]['PlanPayAmt'] = $PayStage['PlanPayAmt'];
|
|
$arrayData[$PayStage['BillNo']]['PayStage'][$PayStage['RowNo']]['PlanPayDate'] = $PayStage['PlanPayDate'];
|
|
$arrayData[$PayStage['BillNo']]['PayStage'][$PayStage['RowNo']]['stage'] = '';
|
|
if (isset($arrayData[$PayStage['BillNo']]['facility']) && is_iterable($arrayData[$PayStage['BillNo']]['facility'])) {
|
|
foreach ($arrayData[$PayStage['BillNo']]['facility'] as $key => &$value) {
|
|
$tmpkey = $key . $PayStage['RowNo'];
|
|
$arrayData[$PayStage['BillNo']]['facility'][$key]['PayStage'][$tmpkey] = $arrayData[$PayStage['BillNo']]['PayStage'][$PayStage['RowNo']];
|
|
$arrayData[$PayStage['BillNo']]['facility'][$key]['PayStage'][$tmpkey]['PlanPayAmt'] = $arrayData[$PayStage['BillNo']]['facility'][$key]['PayStage'][$tmpkey]['PlanPayAmt'] * get_ratio($key, $PayStage['BillNo'], $arrayData);
|
|
$arrayData[$PayStage['BillNo']]['facility'][$key]['PayStage'][$tmpkey]['facilityno'] = $key;
|
|
if (isset($PayStage_array[$PayStage['BillNo']])) {
|
|
$PayStage_array[$PayStage['BillNo']] = array_merge($PayStage_array[$PayStage['BillNo']], $arrayData[$PayStage['BillNo']]['facility'][$key]['PayStage']);
|
|
} else {
|
|
$PayStage_array[$PayStage['BillNo']] = $arrayData[$PayStage['BillNo']]['facility'][$key]['PayStage'];
|
|
}
|
|
$arrayData[$PayStage['BillNo']]['facility'][$key]['PayStage'] = array();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
//作番大日程
|
|
$sql_wip = "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='A'
|
|
)AS tmp2
|
|
left join (
|
|
SELECT DISTINCT
|
|
department_id,
|
|
name
|
|
FROM department
|
|
|
|
) AS d
|
|
ON d.department_id = tmp2.department_id ";
|
|
|
|
// 將作番大日程新梯資料存入array中方便查詢
|
|
// 作番號 -> estimated_shipping_date 預計出港日; real_contract_arrival_date 預計出貨日; real_arrival_date 實際出貨日;
|
|
// install_end_date 安裝完畢日; tryrun_end_date 試車完畢日; official_check_date 官檢日; delivery_date 移交完畢日
|
|
$wip_array = array();
|
|
$query_wip = mysqli_query($link, $sql_wip);
|
|
foreach ($query_wip as $wip) {
|
|
$contractno = $wip['contractno'];
|
|
switch ($wip['contractno']) {
|
|
case 'M231067':
|
|
$contractno = 'SO20230801002';
|
|
break;
|
|
case 'M230947':
|
|
$contractno = 'SO20230801001';
|
|
break;
|
|
}
|
|
$wip_array[$wip['facilityno']]['estimated_shipping_date'] = $wip['estimated_shipping_date'];
|
|
$wip_array[$wip['facilityno']]['real_contract_arrival_date'] = $wip['real_contract_arrival_date'];
|
|
$wip_array[$wip['facilityno']]['real_arrival_date'] = $wip['real_arrival_date'];
|
|
$wip_array[$wip['facilityno']]['install_end_date'] = $wip['install_end_date'];
|
|
$wip_array[$wip['facilityno']]['tryrun_end_date'] = $wip['tryrun_end_date'];
|
|
$wip_array[$wip['facilityno']]['official_check_date'] = $wip['official_check_date'];
|
|
$wip_array[$wip['facilityno']]['delivery_date'] = $wip['delivery_date'];
|
|
if (isset($arrayData[$contractno]) && isset($arrayData[$contractno]['facility'][$wip['facilityno']])) {
|
|
if ($wip['delivery_date'] != NULL && $wip['delivery_date'] !== '1970-01-01' && $wip['delivery_date'] !== '1970-01-11' && !empty($wip['delivery_date'])) {
|
|
$arrayData[$contractno]['facility'][$wip['facilityno']]['status'] = $wip['delivery_date'] . "已移交";
|
|
$arrayData[$contractno]['facility_status'] .= $wip['facilityno'] . " (" . $wip['delivery_date'] . "已移交) ; ";
|
|
} elseif ($wip['official_check_date'] != NULL && $wip['official_check_date'] != '1970-01-01' && $wip['delivery_date'] !== '1970-01-11' && !empty($wip['official_check_date'])) {
|
|
$arrayData[$contractno]['facility'][$wip['facilityno']]['status'] = $wip['official_check_date'] . "官檢完畢";
|
|
$arrayData[$contractno]['facility_status'] .= $wip['facilityno'] . " (" . $wip['official_check_date'] . "官檢完畢) ; ";
|
|
} elseif ($wip['tryrun_end_date'] != NULL && $wip['tryrun_end_date'] !== '1970-01-01' && $wip['delivery_date'] !== '1970-01-11' && !empty($wip['tryrun_end_date'])) {
|
|
$arrayData[$contractno]['facility'][$wip['facilityno']]['status'] = $wip['tryrun_end_date'] . "試車完畢";
|
|
$arrayData[$contractno]['facility_status'] .= $wip['facilityno'] . " (" . $wip['tryrun_end_date'] . "試車完畢) ; ";
|
|
} elseif ($wip['install_end_date'] != NULL && $wip['install_end_date'] !== '1970-01-01' && $wip['delivery_date'] !== '1970-01-11' && !empty($wip['install_end_date'])) {
|
|
$arrayData[$contractno]['facility'][$wip['facilityno']]['status'] = $wip['install_end_date'] . "安裝完畢";
|
|
$arrayData[$contractno]['facility_status'] .= $wip['facilityno'] . " (" . $wip['install_end_date'] . "安裝完畢) ; ";
|
|
} elseif ($wip['real_arrival_date'] != NULL && $wip['real_arrival_date'] !== '1970-01-01' && $wip['delivery_date'] !== '1970-01-11' && !empty($wip['real_arrival_date'])) {
|
|
$arrayData[$contractno]['facility'][$wip['facilityno']]['status'] = $wip['real_arrival_date'] . "貨抵工地";
|
|
$arrayData[$contractno]['facility_status'] .= $wip['facilityno'] . " (" . $wip['real_arrival_date'] . "貨抵工地) ; ";
|
|
} elseif ($wip['real_contract_arrival_date'] != NULL && $wip['real_contract_arrival_date'] !== '1970-01-01' && $wip['delivery_date'] !== '1970-01-11' && !empty($wip['real_contract_arrival_date'])) {
|
|
$arrayData[$contractno]['facility'][$wip['facilityno']]['status'] = $wip['real_contract_arrival_date'] . "預計出貨";
|
|
$arrayData[$contractno]['facility_status'] .= $wip['facilityno'] . " (" . date('Y-m-d', strtotime($wip['real_contract_arrival_date'])) . "預計出貨) ; ";
|
|
} elseif ($wip['estimated_shipping_date'] != NULL && $wip['estimated_shipping_date'] !== '1970-01-01' && $wip['delivery_date'] !== '1970-01-11' && !empty($wip['estimated_shipping_date'])) {
|
|
$arrayData[$contractno]['facility'][$wip['facilityno']]['status'] = $wip['estimated_shipping_date'] . "預計出港";
|
|
$arrayData[$contractno]['facility_status'] .= $wip['facilityno'] . " (" . $wip['estimated_shipping_date'] . "預計出港) ; ";
|
|
}
|
|
}
|
|
}
|
|
|
|
// 合約收款階段內容分類
|
|
$sign60 = array('簽訂後60天', '簽約60日', '簽約後60天', '簽訂後60日內', '訂金支付後60天');
|
|
$sign90 = array('簽約後90天', '簽約後90日', '簽訂後90天');
|
|
$sign120 = array('簽約後120天', '簽約後120日', '簽訂後120天', '合約簽訂後120天');
|
|
$sign0 = array('簽約', '簽訂', '合約簽訂', '訂金');
|
|
$arrival = array('貨抵工地', '貨底工地', '工地動工');
|
|
$final6 = array('核可函取得後6個月', '得合格函後6個月');
|
|
|
|
|
|
|
|
|
|
|
|
foreach ($PayStage_array as $key => &$value) {
|
|
foreach ($value as $paykey => &$payvalue) {
|
|
if (isset($wip_array[$payvalue['facilityno']])) {
|
|
switch ($payvalue['PayStage']) {
|
|
case '寶佳出貨前30天':
|
|
$payvalue['stage'] = '訂金';
|
|
if ($wip_array[$payvalue['facilityno']]['real_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']);
|
|
$signtime = $estimate_time - (30 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} elseif ($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']);
|
|
$signtime = $estimate_time - (30 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case '寶佳出貨後10天':
|
|
$payvalue['stage'] = '二次款';
|
|
if ($wip_array[$payvalue['facilityno']]['real_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']);
|
|
$signtime = $estimate_time + (10 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case '貨抵工地後90天':
|
|
$payvalue['stage'] = '貨抵工地款';
|
|
if ($wip_array[$payvalue['facilityno']]['real_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']);
|
|
$signtime = $estimate_time + (90 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case '安裝試車後90天':
|
|
$payvalue['stage'] = '試車款';
|
|
if ($wip_array[$payvalue['facilityno']]['tryrun_end_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['tryrun_end_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['tryrun_end_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['tryrun_end_date']);
|
|
$signtime = $estimate_time + (90 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case '交車後270天':
|
|
$payvalue['stage'] = '尾款';
|
|
if ($wip_array[$payvalue['facilityno']]['delivery_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['delivery_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['delivery_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['delivery_date']);
|
|
$signtime = $estimate_time + (270 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case '交車後180天':
|
|
$payvalue['stage'] = '尾款';
|
|
if ($wip_array[$payvalue['facilityno']]['delivery_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['delivery_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['delivery_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['delivery_date']);
|
|
$signtime = $estimate_time + (180 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case (in_array($payvalue['PayStage'], $arrival)):
|
|
$payvalue['stage'] = '貨抵工地款';
|
|
if ($wip_array[$payvalue['facilityno']]['real_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']);
|
|
$signtime = $estimate_time;
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = 'NULL';
|
|
}
|
|
break;
|
|
case '二次款':
|
|
$payvalue['stage'] = '二次款';
|
|
if ($wip_array[$payvalue['facilityno']]['real_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $estimate_time);
|
|
} elseif ($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $estimate_time);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case (stristr($payvalue['PayStage'], '出貨前30') || stristr($payvalue['PayStage'], '簽約(出貨前30天)') || stristr($payvalue['PayStage'], '建照核發時')):
|
|
$payvalue['stage'] = '二次款';
|
|
if ($wip_array[$payvalue['facilityno']]['real_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']);
|
|
$signtime = $estimate_time - (30 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} elseif ($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']);
|
|
$signtime = $estimate_time - (30 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case (stristr($payvalue['PayStage'], '出貨前60')):
|
|
$payvalue['stage'] = '二次款';
|
|
if ($wip_array[$payvalue['facilityno']]['real_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']);
|
|
$signtime = $estimate_time - (60 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} elseif ($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']);
|
|
$signtime = $estimate_time - (60 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case (stristr($payvalue['PayStage'], '出貨前90')):
|
|
$payvalue['stage'] = '二次款';
|
|
if ($wip_array[$payvalue['facilityno']]['real_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']);
|
|
$signtime = $estimate_time - (90 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} elseif ($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']);
|
|
$signtime = $estimate_time - (90 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case (stristr($payvalue['PayStage'], '出貨前120')):
|
|
$payvalue['stage'] = '二次款';
|
|
if ($wip_array[$payvalue['facilityno']]['real_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_arrival_date']);
|
|
$signtime = $estimate_time - (120 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} elseif ($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['real_contract_arrival_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['real_contract_arrival_date']);
|
|
$signtime = $estimate_time - (120 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case "簽定30天內":
|
|
$payvalue['stage'] = '二次款';
|
|
$estimate_time = strtotime($arrayData[$key]['PayStage'][1]['PlanPayDate']);
|
|
$signtime = $estimate_time + (30 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
break;
|
|
case (in_array($payvalue['PayStage'], $sign60)):
|
|
$payvalue['stage'] = '二次款';
|
|
$estimate_time = strtotime($arrayData[$key]['PayStage'][1]['PlanPayDate']);
|
|
$signtime = $estimate_time + (60 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
break;
|
|
case (in_array($payvalue['PayStage'], $sign90)):
|
|
$payvalue['stage'] = '二次款';
|
|
$estimate_time = strtotime($arrayData[$key]['PayStage'][1]['PlanPayDate']);
|
|
$signtime = $estimate_time + (90 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
break;
|
|
case (in_array($payvalue['PayStage'], $sign120)):
|
|
$payvalue['stage'] = '二次款';
|
|
$estimate_time = strtotime($arrayData[$key]['PayStage'][1]['PlanPayDate']);
|
|
$signtime = $estimate_time + (120 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
break;
|
|
case (stristr($payvalue['PayStage'], '簽訂後30')):
|
|
$payvalue['stage'] = '二次款';
|
|
$estimate_time = strtotime($arrayData[$key]['PayStage'][1]['PlanPayDate']);
|
|
$signtime = $estimate_time + (30 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $signtime);
|
|
break;
|
|
case (in_array($payvalue['PayStage'], $sign0)):
|
|
$payvalue['stage'] = '訂金';
|
|
$estimate_time = strtotime($arrayData[$key]['PayStage'][1]['PlanPayDate']);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $estimate_time);
|
|
break;
|
|
case (stristr($payvalue['PayStage'], '試車') || $payvalue['PayStage'] == '安裝試車'):
|
|
$payvalue['stage'] = '試車款';
|
|
if ($wip_array[$payvalue['facilityno']]['tryrun_end_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['tryrun_end_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['tryrun_end_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['tryrun_end_date']);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $estimate_time);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case (stristr($payvalue['PayStage'], '合格') || stristr($payvalue['PayStage'], '驗收') || stristr($payvalue['PayStage'], '驗收')):
|
|
$payvalue['stage'] = '官檢驗收款';
|
|
if ($wip_array[$payvalue['facilityno']]['official_check_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['official_check_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['official_check_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['official_check_date']);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $estimate_time);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case (stristr($payvalue['PayStage'], '安裝') || stristr($payvalue['PayStage'], '貨抵工地且完工')):
|
|
$payvalue['stage'] = '安裝款';
|
|
if ($wip_array[$payvalue['facilityno']]['install_end_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['install_end_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['install_end_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['install_end_date']);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $estimate_time);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
case (in_array($payvalue['PayStage'], $final6)):
|
|
$payvalue['stage'] = '尾款';
|
|
if ($wip_array[$payvalue['facilityno']]['delivery_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['delivery_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['delivery_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['delivery_date']);
|
|
$estimate_time = $estimate_time + (180 * 86400);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $estimate_time);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
|
|
case (stristr($payvalue['PayStage'], '交車') || stristr($payvalue['PayStage'], '移交') || stristr($payvalue['PayStage'], '完工') || stristr($payvalue['PayStage'], '許可') || stristr($payvalue['PayStage'], '核可')):
|
|
$payvalue['stage'] = '交車款';
|
|
if ($wip_array[$payvalue['facilityno']]['delivery_date'] !== null && strtotime($wip_array[$payvalue['facilityno']]['delivery_date']) > 975686400 && !empty($wip_array[$payvalue['facilityno']]['delivery_date'])) {
|
|
$estimate_time = strtotime($wip_array[$payvalue['facilityno']]['delivery_date']);
|
|
$payvalue['PlanPayDate'] = date('Y-m-d', $estimate_time);
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
break;
|
|
default:
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
break;
|
|
}
|
|
if (strtotime($payvalue['PlanPayDate']) < 975686400) {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
}
|
|
} else {
|
|
$payvalue['PlanPayDate'] = NULL;
|
|
$payvalue['stage'] = "大日程無作番";
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
// 在$PayStage_array中排序
|
|
foreach ($PayStage_array as $key => $value) {
|
|
uasort($PayStage_array[$key], 'comparePlanPayDate');
|
|
}
|
|
// 加入序號
|
|
foreach ($PayStage_array as &$payStage) {
|
|
$sequenceNumber = 1;
|
|
foreach ($payStage as $tk => &$pstage) {
|
|
$pstage['sequenceNumber'] = $sequenceNumber;
|
|
$sequenceNumber += 1;
|
|
}
|
|
}
|
|
|
|
// 整理好的收款階段放入 $final_paystage
|
|
$final_paystage = array();
|
|
foreach ($PayStage_array as $key => &$payStage) {
|
|
if (is_iterable($payStage)) {
|
|
foreach ($payStage as $key2 => $pstage) {
|
|
$final_paystage[$key][$pstage['sequenceNumber']] = $pstage;
|
|
}
|
|
}
|
|
}
|
|
|
|
// 發票資訊
|
|
$sql_invoice = "SELECT
|
|
Detail.BillNo AS InvoiceBillNo,
|
|
Detail.MaterialId,
|
|
Detail.LAmountWithTax,
|
|
Main.InvoiceNo,
|
|
Main.InvoiceDate,
|
|
Main.InvoiceState,
|
|
CheckAll.FromSalSalesOrder AS BillNo
|
|
FROM arSellInvoiceMaterial AS Detail
|
|
LEFT JOIN arSellInvoice AS Main ON Detail.BillNo=Main.BillNo
|
|
LEFT JOIN (
|
|
SELECT CheckDetail.BillNo, CheckDetail.FromSalSalesOrder, CheckDetail.RowNo
|
|
FROM arCheckBillDetail AS CheckDetail )
|
|
AS CheckAll ON CheckAll.BillNo= Detail.FromBillNo AND Detail.FromRowCode = CheckAll.RowNo
|
|
WHERE Main.InvoiceState!=2 AND LEN(CheckAll.FromSalSalesOrder)>0 AND Main.InvoiceDate > 20240229";
|
|
$query_invoice = $conn->query($sql_invoice);
|
|
|
|
//發票資訊回填到arrayData
|
|
foreach ($query_invoice as $invoice) {
|
|
if (isset($arrayData[$invoice['BillNo']])) {
|
|
$arrayData[$invoice['BillNo']]['invoice_budget'] += $invoice['LAmountWithTax'];
|
|
}
|
|
}
|
|
|
|
//未在T8的收款資料
|
|
// $sql_wms = "SELECT * FROM account_received WHERE type='M' AND BillNo IN $str_numbers";
|
|
// $query_wms = mysqli_query($link, $sql_wms);
|
|
// foreach ($query_wms as $wms) {
|
|
// if (isset($arrayData[$wms['BillNo']]) && isset($arrayData[$wms['BillNo']]['invoice_budget'])) {
|
|
// $arrayData[$wms['BillNo']]['invoice_budget'] += $wms['invoice_budget'];
|
|
// }
|
|
// if (isset($arrayData[$wms['BillNo']]) && isset($arrayData[$wms['BillNo']]['received_budget'])) {
|
|
// $arrayData[$wms['BillNo']]['received_budget'] += $wms['received_budget'];
|
|
// }
|
|
// }
|
|
|
|
// 核銷資訊
|
|
// $sql_received = "SELECT
|
|
// CheckDetail.OrderBillNo,
|
|
// arWriteOffBillDetail.FromBillNo AS CheckBillNo,
|
|
// arWriteOffBillDetail.FromRowCode AS CheckRowCode,
|
|
// arWriteOffBillDetail.CurrStandOffOAmount,
|
|
// CheckDetail.TypeId
|
|
// FROM arWriteOffBillDetail
|
|
// LEFT JOIN
|
|
// (SELECT
|
|
// arCheckBillDetail.BillNo,
|
|
// arCheckBillDetail.RowCode,
|
|
// arCheckBillDetail.FromSalSalesOrder AS OrderBillNo,
|
|
// salSalesOrder.TypeId,
|
|
// salSalesOrder.ModeId
|
|
// FROM arCheckBillDetail
|
|
// LEFT JOIN salSalesOrder
|
|
// ON salSalesOrder.BillNo =arCheckBillDetail.FromSalSalesOrder )
|
|
// AS CheckDetail
|
|
// ON CheckDetail.BillNo=arWriteOffBillDetail.FromBillNo AND arWriteOffBillDetail.FromRowCode = CheckDetail.RowCode
|
|
// WHERE CheckDetail.ModeId = 'M'";
|
|
$sql_received = "
|
|
SELECT Detail.OrderBillNo,
|
|
Detail.CurrWriteOffLAmount,
|
|
Main.BillDate
|
|
FROM arWriteOffBillDetail AS Detail
|
|
LEFT JOIN arWriteOffBill AS Main
|
|
ON Detail.BillNo=Main.BillNo
|
|
WHERE Main.BillDate >20240229 AND LEN(Detail.OrderBillNo)>0";
|
|
$query_received = $conn->query($sql_received);
|
|
|
|
// 核銷資訊回填到arrayData
|
|
if(is_iterable($query_received)){
|
|
foreach ($query_received as $received) {
|
|
if (isset($arrayData[$received['OrderBillNo']])) {
|
|
$arrayData[$received['OrderBillNo']]['received_budget'] += $received['CurrWriteOffLAmount'];
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
// 取WMS期初資料並依作番款別填入 $wms_initial
|
|
$wms_initial = array();
|
|
$sql_initial = "SELECT * FROM account_receivable";
|
|
$initail_data = mysqli_query($link, $sql_initial);
|
|
foreach ($initail_data as $initial) {
|
|
if ($initial['invoice_budget'] > 0) {
|
|
if (isset($wms_initial[$initial['facility_no']][$initial['stage']]['invoice_budget'])) {
|
|
$wms_initial[$initial['facility_no']][$initial['stage']]['invoice_budget'] += $initial['invoice_budget'];
|
|
} else {
|
|
$wms_initial[$initial['facility_no']][$initial['stage']]['invoice_budget'] = $initial['invoice_budget'];
|
|
}
|
|
}
|
|
if ($initial['received_budget'] > 0) {
|
|
if (isset($wms_initial[$initial['facility_no']][$initial['stage']]['received_budget'])) {
|
|
$wms_initial[$initial['facility_no']][$initial['stage']]['received_budget'] += $initial['received_budget'];
|
|
} else {
|
|
$wms_initial[$initial['facility_no']][$initial['stage']]['received_budget'] = $initial['received_budget'];
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
// 將發票與核銷資訊 分別計算作番收款階段金額 與 催收次數
|
|
$today = strtotime(date('Y-m-t'));
|
|
foreach ($final_paystage as $key => &$value) {
|
|
$tmpinvoice = $arrayData[$key]['invoice_budget'];
|
|
$tmpreceived = $arrayData[$key]['received_budget'];
|
|
for ($sequenceNumber = 1; $sequenceNumber <= count($value); $sequenceNumber++) {
|
|
if (isset($value[$sequenceNumber]['stage'])) {
|
|
switch ($value[$sequenceNumber]['stage']) {
|
|
case '訂金':
|
|
$istage = 100;
|
|
break;
|
|
case '二次款':
|
|
$istage = 200;
|
|
break;
|
|
case '貨抵工地款':
|
|
$istage = 300;
|
|
break;
|
|
case '安裝款':
|
|
$istage = 400;
|
|
break;
|
|
case '試車款':
|
|
$istage = 500;
|
|
break;
|
|
case '官檢驗收款':
|
|
$istage = 600;
|
|
break;
|
|
case '交車款':
|
|
$istage = 700;
|
|
break;
|
|
case '尾款':
|
|
$istage = 800;
|
|
break;
|
|
}
|
|
} else {
|
|
$istage = 0;
|
|
}
|
|
if (isset($wms_initial[$value[$sequenceNumber]['facilityno']][$istage]['invoice_budget'])) {
|
|
$value[$sequenceNumber]['invoice_budget'] = $wms_initial[$value[$sequenceNumber]['facilityno']][$istage]['invoice_budget'];
|
|
} else {
|
|
if ($tmpinvoice > $value[$sequenceNumber]['PlanPayAmt']) {
|
|
$value[$sequenceNumber]['invoice_budget'] = $value[$sequenceNumber]['PlanPayAmt'];
|
|
$tmpinvoice = $tmpinvoice - $value[$sequenceNumber]['invoice_budget'];
|
|
} elseif ($tmpinvoice > 0) {
|
|
$value[$sequenceNumber]['invoice_budget'] = $tmpinvoice;
|
|
$tmpinvoice = 0;
|
|
} else {
|
|
$value[$sequenceNumber]['invoice_budget'] = 0;
|
|
}
|
|
}
|
|
|
|
if (isset($wms_initial[$value[$sequenceNumber]['facilityno']][$istage]['received_budget'])) {
|
|
$value[$sequenceNumber]['received_budget'] = $wms_initial[$value[$sequenceNumber]['facilityno']][$istage]['received_budget'];
|
|
} else {
|
|
if ($tmpreceived > $value[$sequenceNumber]['PlanPayAmt']) {
|
|
$value[$sequenceNumber]['received_budget'] = $value[$sequenceNumber]['PlanPayAmt'];
|
|
$tmpreceived -= $value[$sequenceNumber]['received_budget'];
|
|
} elseif ($tmpreceived > 0) {
|
|
$value[$sequenceNumber]['received_budget'] = $tmpreceived;
|
|
$tmpreceived = 0;
|
|
} else {
|
|
$value[$sequenceNumber]['received_budget'] = 0;
|
|
}
|
|
}
|
|
|
|
|
|
if (isset($value[$sequenceNumber]['PlanPayDate'])) {
|
|
$collect_month = collect_month(strtotime($value[$sequenceNumber]['PlanPayDate']));
|
|
if ($today > strtotime($value[$sequenceNumber]['PlanPayDate'])) {
|
|
$value[$sequenceNumber]['hadtopay'] = 'V';
|
|
} else {
|
|
$value[$sequenceNumber]['hadtopay'] = '--';
|
|
}
|
|
} else {
|
|
$collect_month = 0;
|
|
$value[$sequenceNumber]['hadtopay'] = '--';
|
|
}
|
|
|
|
|
|
if ($collect_month > 0 && $value[$sequenceNumber]['PlanPayAmt'] > $value[$sequenceNumber]['received_budget']) {
|
|
$value[$sequenceNumber]['collect_month'] = $collect_month;
|
|
} else {
|
|
$value[$sequenceNumber]['collect_month'] = 0;
|
|
}
|
|
}
|
|
}
|
|
|
|
//合約資料填回arrayData
|
|
foreach ($final_paystage as $key => $value) {
|
|
for ($sequence = 1; $sequence <= count($value); $sequence++) {
|
|
if (isset($arrayData[$key]) && isset($arrayData[$key]['facility']) && isset($arrayData[$key]['facility'][$value[$sequence]['facilityno']]) && isset($arrayData[$key]['facility'][$value[$sequence]['facilityno']]['PayStage'])) {
|
|
array_push($arrayData[$key]['facility'][$value[$sequence]['facilityno']]['PayStage'], $value[$sequence]);
|
|
foreach ($arrayData[$key]['PayStage'] as $arkey => &$arvalue) {
|
|
if ($arvalue['PayStage'] == $value[$sequence]['PayStage']) {
|
|
if (isset($arvalue['receivable_budget']) && isset($arvalue['received_budget']) && isset($arvalue['invoice_budget'])) {
|
|
$arvalue['received_budget'] += $value[$sequence]['received_budget'];
|
|
$arrayData[$key]['received_budget'] += $value[$sequence]['received_budget'];
|
|
$arvalue['invoice_budget'] += $value[$sequence]['invoice_budget'];
|
|
$arrayData[$key]['invoice_budget'] += $value[$sequence]['invoice_budget'];
|
|
if ($value[$sequence]['hadtopay'] == 'V') {
|
|
$arvalue['receivable_budget'] += $value[$sequence]['PlanPayAmt'];
|
|
$arrayData[$key]['receivable_budget'] += $value[$sequence]['PlanPayAmt'];
|
|
}
|
|
if ($arvalue['collect_month'] < $value[$sequence]['collect_month']) {
|
|
$arvalue['collect_month'] = $value[$sequence]['collect_month'];
|
|
}
|
|
$sum_receivable_budget += $value[$sequence]['PlanPayAmt'];
|
|
$sum_received_budget += $value[$sequence]['received_budget'];
|
|
$sum_invoice_budget += $value[$sequence]['invoice_budget'];
|
|
} else {
|
|
$arvalue['stage'] = $value[$sequence]['stage'];
|
|
if ($value[$sequence]['hadtopay'] == 'V') {
|
|
$arvalue['receivable_budget'] = $value[$sequence]['PlanPayAmt'];
|
|
$arrayData[$key]['receivable_budget'] += $value[$sequence]['PlanPayAmt'];
|
|
} else {
|
|
$arvalue['receivable_budget'] = 0;
|
|
}
|
|
$arvalue['received_budget'] = $value[$sequence]['received_budget'];
|
|
$arrayData[$key]['received_budget'] += $value[$sequence]['received_budget'];
|
|
$arvalue['invoice_budget'] = $value[$sequence]['invoice_budget'];
|
|
$arrayData[$key]['invoice_budget'] += $value[$sequence]['invoice_budget'];
|
|
$arvalue['collect_month'] = $value[$sequence]['collect_month'];
|
|
$sum_receivable_budget += $value[$sequence]['PlanPayAmt'];
|
|
$sum_received_budget += $value[$sequence]['received_budget'];
|
|
$sum_invoice_budget += $value[$sequence]['invoice_budget'];
|
|
}
|
|
if ((round($arvalue['receivable_budget'] - $arvalue['received_budget']) >2)&&($value[$sequence]['collect_month'] > $arrayData[$key]['collect_month'])) {
|
|
$arrayData[$key]['collect_month'] = $value[$sequence]['collect_month'];
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
if($sum_facility>0){
|
|
$average_A40001 = $sum_A40001 / $sum_facility;
|
|
$average_A40008 = $sum_A40008 / $sum_facility;
|
|
$average_facility_budget = $sum_total_budget / $sum_facility;
|
|
}
|
|
if($sum_contract>0){
|
|
$average_contract_budget = $sum_total_budget / $sum_contract;
|
|
$average_contract_facility = $sum_facility / $sum_contract;
|
|
}
|
|
|
|
|
|
|
|
|
|
// excel 結果
|
|
// 0 合約號; 1 單據日期; 2 客戶編號; 3 客戶名稱; 4 客戶地址; 5 部門ID; 6 部門名稱; 7 營業員工號; 8 營業員名稱; 9 部門主管工號; 10 部門主管名稱;
|
|
// 11 合約總金額; 12 A40001總金額; 13 A40008總金額; 14 目前應收金額; 15 已開發票金額; 16 已收金額; 17 催收金額; 18 催收次數; 19 作番總數; 20 作番狀態
|
|
// 21 訂金名稱; 22 訂金金額; 23 應收金額; 24 已開發票金額; 25 已收金額; 26 催收金額; 27 催收次數;
|
|
// 28 二次款名稱; 29 二次款金額; 30 應收金額; 31 已開發票金額; 32 已收金額; 33 催收金額; 34 催收次數;
|
|
$excel_contract_array = array();
|
|
$excel_contract_all_array = array();
|
|
$excel_contract_boga_array = array();
|
|
$excel_contract_noboga_array = array();
|
|
|
|
$excel_facility_array = array();
|
|
$total_collect_budget = 0;
|
|
$total_collect_facility = 0;
|
|
$boga_collect_budget = 0;
|
|
$boga_collect_facility = 0;
|
|
$noboga_collect_budget = 0;
|
|
$noboga_collect_facility = 0;
|
|
$total_sign_collect_budget = $boga_sign_collect_budget = $noboga_sign_collect_budget = 0;
|
|
$total_second_collect_budget = $boga_second_collect_budget = $noboga_second_collect_budget = 0;
|
|
$total_arrival_collect_budget = $boga_arrival_collect_budget = $noboga_arrival_collect_budget = 0;
|
|
$total_install_collect_budget = $boga_install_collect_budget = $noboga_install_collect_budget = 0;
|
|
$total_tryrun_collect_budget = $boga_tryrun_collect_budget = $noboga_tryrun_collect_budget = 0;
|
|
$total_check_collect_budget = $boga_check_collect_budget = $noboga_check_collect_budget = 0;
|
|
$total_delivery_collect_budget = $boga_delivery_collect_budget = $noboga_delivery_collect_budget = 0;
|
|
$total_final_collect_budget = $boga_final_collect_budget = $noboga_final_collect_budget = 0;
|
|
foreach ($arrayData as $key => &$value) {
|
|
|
|
$excel_contract_array[$key] = array_fill(0, 76, '');
|
|
$excel_contract_array[$key][0] = (isset($value['BillNo']) && !is_null($value['BillNo'])) ? $value['BillNo'] : '';
|
|
$excel_contract_array[$key][1] = (isset($value['BillDate']) && !is_null($value['BillDate'])) ? $value['BillDate'] : '';
|
|
$excel_contract_array[$key][2] = (isset($value['CustomerId']) && !is_null($value['CustomerId'])) ? $value['CustomerId'] : '';
|
|
$excel_contract_array[$key][3] = (isset($value['CustomerName']) && !is_null($value['CustomerName'])) ? $value['CustomerName'] : '';
|
|
$excel_contract_array[$key][4] = (isset($value['CustomerAddress']) && !is_null($value['CustomerAddress'])) ? $value['CustomerAddress'] : '';
|
|
$excel_contract_array[$key][5] = (isset($value['DeptId']) && !is_null($value['DeptId'])) ? $value['DeptId'] : '';
|
|
$excel_contract_array[$key][6] = (isset($value['DeptName']) && !is_null($value['DeptName'])) ? $value['DeptName'] : '';
|
|
$excel_contract_array[$key][7] = (isset($value['PersonId']) && !is_null($value['PersonId'])) ? $value['PersonId'] : '';
|
|
$excel_contract_array[$key][8] = (isset($value['PersonName']) && !is_null($value['PersonName'])) ? $value['PersonName'] : '';
|
|
$excel_contract_array[$key][9] = (isset($value['ManagerId']) && !is_null($value['ManagerId'])) ? $value['ManagerId'] : '';
|
|
$excel_contract_array[$key][10] = (isset($value['ManagerName']) && !is_null($value['ManagerName'])) ? $value['ManagerName'] : '';
|
|
$excel_contract_array[$key][11] = (isset($value['OAmountWithTax']) && !is_null($value['OAmountWithTax'])) ? round($value['OAmountWithTax']) : 0;
|
|
$excel_contract_array[$key][12] = (isset($value['A40001']) && !is_null($value['A40001'])) ? round($value['A40001']) : 0;
|
|
$excel_contract_array[$key][13] = (isset($value['A40008']) && !is_null($value['A40008'])) ? round($value['A40008']) : 0;
|
|
$excel_contract_array[$key][14] = (isset($value['receivable_budget']) && !is_null($value['receivable_budget'])) ? round($value['receivable_budget']) : 0;
|
|
$excel_contract_array[$key][15] = (isset($value['invoice_budget']) && !is_null($value['invoice_budget'])) ? round($value['invoice_budget']) : 0;
|
|
$excel_contract_array[$key][16] = (isset($value['received_budget']) && !is_null($value['received_budget'])) ? round($value['received_budget']) : 0;
|
|
$value['collect_budget'] = (abs($excel_contract_array[$key][14] - $excel_contract_array[$key][16]) > 10) ? round($excel_contract_array[$key][14] - $excel_contract_array[$key][16]) : 0;
|
|
$excel_contract_array[$key][17] = $value['collect_budget'];
|
|
$excel_contract_array[$key][18] = (isset($value['collect_month']) && !is_null($value['collect_month'])) ? round($value['collect_month']) : 0;
|
|
$excel_contract_array[$key][19] = (isset($value['facility_num']) && !is_null($value['facility_num'])) ? round($value['facility_num']) : 0;
|
|
$excel_contract_array[$key][20] = (isset($value['facility_status']) && !is_null($value['facility_status'])) ? $value['facility_status'] : '--';
|
|
if (isset($value['PayStage']) && is_iterable($value['PayStage'])) {
|
|
if ($excel_contract_array[$key][18] >0 && abs($value['collect_budget']) > 10) {
|
|
$excel_contract_all_array[$key] = array_fill(0, 24, 0);
|
|
$excel_contract_all_array[$key][0] = $excel_contract_array[$key][0];
|
|
$excel_contract_all_array[$key][1] = $excel_contract_array[$key][3];
|
|
$excel_contract_all_array[$key][2] = $excel_contract_array[$key][6];
|
|
$excel_contract_all_array[$key][3] = $excel_contract_array[$key][8];
|
|
$excel_contract_all_array[$key][4] = $excel_contract_array[$key][11];
|
|
$excel_contract_all_array[$key][5] = $excel_contract_array[$key][19];
|
|
$excel_contract_all_array[$key][6] = $excel_contract_array[$key][20];
|
|
$excel_contract_all_array[$key][7] = $excel_contract_array[$key][17];
|
|
$total_collect_budget += $excel_contract_all_array[$key][7];
|
|
$total_collect_facility += $excel_contract_all_array[$key][5];
|
|
$isBoga = 0;
|
|
if (stristr($value['PayStage'][1]['PayStage'], '寶佳')) {
|
|
$isBoga = 1;
|
|
$excel_contract_boga_array[$key] = array_fill(0, 24, '');
|
|
$excel_contract_boga_array[$key][0] = $excel_contract_array[$key][0];
|
|
$excel_contract_boga_array[$key][1] = $excel_contract_array[$key][3];
|
|
$excel_contract_boga_array[$key][2] = $excel_contract_array[$key][6];
|
|
$excel_contract_boga_array[$key][3] = $excel_contract_array[$key][8];
|
|
$excel_contract_boga_array[$key][4] = $excel_contract_array[$key][11];
|
|
$excel_contract_boga_array[$key][5] = $excel_contract_array[$key][19];
|
|
$excel_contract_boga_array[$key][6] = $excel_contract_array[$key][20];
|
|
$excel_contract_boga_array[$key][7] = $excel_contract_array[$key][17];
|
|
$boga_collect_budget += $excel_contract_boga_array[$key][7];
|
|
$boga_collect_facility += $excel_contract_boga_array[$key][5];
|
|
} else {
|
|
$excel_contract_noboga_array[$key] = array_fill(0, 24, '');
|
|
$excel_contract_noboga_array[$key][0] = $excel_contract_array[$key][0];
|
|
$excel_contract_noboga_array[$key][1] = $excel_contract_array[$key][3];
|
|
$excel_contract_noboga_array[$key][2] = $excel_contract_array[$key][6];
|
|
$excel_contract_noboga_array[$key][3] = $excel_contract_array[$key][8];
|
|
$excel_contract_noboga_array[$key][4] = $excel_contract_array[$key][11];
|
|
$excel_contract_noboga_array[$key][5] = $excel_contract_array[$key][19];
|
|
$excel_contract_noboga_array[$key][6] = $excel_contract_array[$key][20];
|
|
$excel_contract_noboga_array[$key][7] = $excel_contract_array[$key][17];
|
|
$noboga_collect_budget += $excel_contract_noboga_array[$key][7];
|
|
$noboga_collect_facility += $excel_contract_noboga_array[$key][5];
|
|
}
|
|
}
|
|
|
|
foreach ($value['PayStage'] as $paykey => $payvalue) {
|
|
switch ($payvalue['stage']) {
|
|
case "訂金":
|
|
$excel_contract_array[$key][21] = (isset($payvalue['PayStage']) && !is_null($payvalue['PayStage'])) ? $payvalue['PayStage'] : '--';
|
|
$excel_contract_array[$key][22] = (isset($payvalue['PlanPayAmt']) && !is_null($payvalue['PlanPayAmt'])) ? round($payvalue['PlanPayAmt']) : 0;
|
|
$excel_contract_array[$key][23] = (isset($payvalue['receivable_budget']) && !is_null($payvalue['receivable_budget'])) ? round($payvalue['receivable_budget']) : 0;
|
|
$excel_contract_array[$key][24] = (isset($payvalue['invoice_budget']) && !is_null($payvalue['invoice_budget'])) ? round($payvalue['invoice_budget']) : 0;
|
|
$excel_contract_array[$key][25] = (isset($payvalue['received_budget']) && !is_null($payvalue['received_budget'])) ? round($payvalue['received_budget']) : 0;
|
|
$excel_contract_array[$key][26] = (abs($excel_contract_array[$key][23] - $excel_contract_array[$key][25]) >10)?round($excel_contract_array[$key][23] - $excel_contract_array[$key][25]):0;
|
|
$excel_contract_array[$key][27] = (isset($payvalue['collect_month']) && !is_null($payvalue['collect_month']) && abs($excel_contract_array[$key][26]) > 10) ? round($payvalue['collect_month']) : 0;
|
|
$total_sign_collect_budget += $excel_contract_array[$key][26];
|
|
if (isset($excel_contract_all_array[$key])) {
|
|
$excel_contract_all_array[$key][8] = $excel_contract_array[$key][26];
|
|
$excel_contract_all_array[$key][9] = $excel_contract_array[$key][27];
|
|
$total_sign_collect_budget += $excel_contract_all_array[$key][8];
|
|
if ($isBoga == 1) {
|
|
$excel_contract_boga_array[$key][8] = $excel_contract_all_array[$key][8];
|
|
$excel_contract_boga_array[$key][9] = $excel_contract_all_array[$key][9];
|
|
$boga_sign_collect_budget += $excel_contract_boga_array[$key][8];
|
|
} else {
|
|
$excel_contract_noboga_array[$key][8] = $excel_contract_all_array[$key][8];
|
|
$excel_contract_noboga_array[$key][9] = $excel_contract_all_array[$key][9];
|
|
$noboga_sign_collect_budget += $excel_contract_noboga_array[$key][8];
|
|
}
|
|
}
|
|
break;
|
|
case "二次款":
|
|
$excel_contract_array[$key][28] = (isset($payvalue['PayStage']) && !is_null($payvalue['PayStage'])) ? $payvalue['PayStage'] : '--';
|
|
$excel_contract_array[$key][29] = (isset($payvalue['PlanPayAmt']) && !is_null($payvalue['PlanPayAmt'])) ? round($payvalue['PlanPayAmt']) : 0;
|
|
$excel_contract_array[$key][30] = (isset($payvalue['receivable_budget']) && !is_null($payvalue['receivable_budget'])) ? round($payvalue['receivable_budget']) : 0;
|
|
$excel_contract_array[$key][31] = (isset($payvalue['invoice_budget']) && !is_null($payvalue['invoice_budget'])) ? round($payvalue['invoice_budget']) : 0;
|
|
$excel_contract_array[$key][32] = (isset($payvalue['received_budget']) && !is_null($payvalue['received_budget'])) ? round($payvalue['received_budget']) : 0;
|
|
$excel_contract_array[$key][33] = (abs($excel_contract_array[$key][30] - $excel_contract_array[$key][32]) >10)?round($excel_contract_array[$key][30] - $excel_contract_array[$key][32]):0;
|
|
$excel_contract_array[$key][34] = (isset($payvalue['collect_month']) && !is_null($payvalue['collect_month']) && abs($excel_contract_array[$key][33]) > 10) ? round($payvalue['collect_month']) : 0;
|
|
if (isset($excel_contract_all_array[$key])) {
|
|
$excel_contract_all_array[$key][10] = $excel_contract_array[$key][33];
|
|
$excel_contract_all_array[$key][11] = $excel_contract_array[$key][34];
|
|
$total_second_collect_budget += $excel_contract_all_array[$key][10];
|
|
if ($isBoga == 1) {
|
|
$excel_contract_boga_array[$key][10] = $excel_contract_all_array[$key][10];
|
|
$excel_contract_boga_array[$key][11] = $excel_contract_all_array[$key][11];
|
|
$boga_second_collect_budget += $excel_contract_boga_array[$key][10];
|
|
} else {
|
|
$excel_contract_noboga_array[$key][10] = $excel_contract_all_array[$key][10];
|
|
$excel_contract_noboga_array[$key][11] = $excel_contract_all_array[$key][11];
|
|
$noboga_second_collect_budget += $excel_contract_noboga_array[$key][10];
|
|
}
|
|
}
|
|
|
|
|
|
break;
|
|
case "貨抵工地款":
|
|
$excel_contract_array[$key][35] = (isset($payvalue['PayStage']) && !is_null($payvalue['PayStage'])) ? $payvalue['PayStage'] : '--';
|
|
$excel_contract_array[$key][36] = (isset($payvalue['PlanPayAmt']) && !is_null($payvalue['PlanPayAmt'])) ? round($payvalue['PlanPayAmt']) : 0;
|
|
$excel_contract_array[$key][37] = (isset($payvalue['receivable_budget']) && !is_null($payvalue['receivable_budget'])) ? round($payvalue['receivable_budget']) : 0;
|
|
$excel_contract_array[$key][38] = (isset($payvalue['invoice_budget']) && !is_null($payvalue['invoice_budget'])) ? round($payvalue['invoice_budget']) : 0;
|
|
$excel_contract_array[$key][39] = (isset($payvalue['received_budget']) && !is_null($payvalue['received_budget'])) ? round($payvalue['received_budget']) : 0;
|
|
$excel_contract_array[$key][40] = (abs($excel_contract_array[$key][37] - $excel_contract_array[$key][39])>10)?round($excel_contract_array[$key][37] - $excel_contract_array[$key][39]):0;
|
|
$excel_contract_array[$key][41] = (isset($payvalue['collect_month']) && !is_null($payvalue['collect_month']) && abs($excel_contract_array[$key][40]) > 10) ? round($payvalue['collect_month']) : 0;
|
|
if (isset($excel_contract_all_array[$key])) {
|
|
$excel_contract_all_array[$key][12] = $excel_contract_array[$key][40];
|
|
$excel_contract_all_array[$key][13] = $excel_contract_array[$key][41];
|
|
$total_arrival_collect_budget += $excel_contract_all_array[$key][12];
|
|
if ($isBoga == 1) {
|
|
$excel_contract_boga_array[$key][12] = $excel_contract_all_array[$key][12];
|
|
$excel_contract_boga_array[$key][13] = $excel_contract_all_array[$key][13];
|
|
$boga_arrival_collect_budget += $excel_contract_boga_array[$key][12];
|
|
} else {
|
|
$excel_contract_noboga_array[$key][12] = $excel_contract_all_array[$key][12];
|
|
$excel_contract_noboga_array[$key][13] = $excel_contract_all_array[$key][13];
|
|
$noboga_arrival_collect_budget += $excel_contract_noboga_array[$key][12];
|
|
}
|
|
}
|
|
|
|
break;
|
|
case "安裝款":
|
|
$excel_contract_array[$key][42] = (isset($payvalue['PayStage']) && !is_null($payvalue['PayStage'])) ? $payvalue['PayStage'] : '--';
|
|
$excel_contract_array[$key][43] = (isset($payvalue['PlanPayAmt']) && !is_null($payvalue['PlanPayAmt'])) ? round($payvalue['PlanPayAmt']) : 0;
|
|
$excel_contract_array[$key][44] = (isset($payvalue['receivable_budget']) && !is_null($payvalue['receivable_budget'])) ? round($payvalue['receivable_budget']) : 0;
|
|
$excel_contract_array[$key][45] = (isset($payvalue['invoice_budget']) && !is_null($payvalue['invoice_budget'])) ? round($payvalue['invoice_budget']) : 0;
|
|
$excel_contract_array[$key][46] = (isset($payvalue['received_budget']) && !is_null($payvalue['received_budget'])) ? round($payvalue['received_budget']) : 0;
|
|
$excel_contract_array[$key][47] = (abs($excel_contract_array[$key][44] - $excel_contract_array[$key][46])>10)?round($excel_contract_array[$key][44] - $excel_contract_array[$key][46]):0;
|
|
$excel_contract_array[$key][48] = (isset($payvalue['collect_month']) && !is_null($payvalue['collect_month']) && abs($excel_contract_array[$key][47]) > 10) ? round($payvalue['collect_month']) : 0;
|
|
if (isset($excel_contract_all_array[$key])) {
|
|
$excel_contract_all_array[$key][14] = $excel_contract_array[$key][47];
|
|
$excel_contract_all_array[$key][15] = $excel_contract_array[$key][48];
|
|
$excel_contract_noboga_array[$key][14] = $excel_contract_all_array[$key][14];
|
|
$excel_contract_noboga_array[$key][15] = $excel_contract_all_array[$key][15];
|
|
$total_install_collect_budget += $excel_contract_all_array[$key][14];
|
|
$noboga_install_collect_budget += $excel_contract_noboga_array[$key][14];
|
|
}
|
|
break;
|
|
case "試車款":
|
|
$excel_contract_array[$key][49] = (isset($payvalue['PayStage']) && !is_null($payvalue['PayStage'])) ? $payvalue['PayStage'] : '--';
|
|
$excel_contract_array[$key][50] = (isset($payvalue['PlanPayAmt']) && !is_null($payvalue['PlanPayAmt'])) ? round($payvalue['PlanPayAmt']) : 0;
|
|
$excel_contract_array[$key][51] = (isset($payvalue['receivable_budget']) && !is_null($payvalue['receivable_budget'])) ? round($payvalue['receivable_budget']) : 0;
|
|
$excel_contract_array[$key][52] = (isset($payvalue['invoice_budget']) && !is_null($payvalue['invoice_budget'])) ? round($payvalue['invoice_budget']) : 0;
|
|
$excel_contract_array[$key][53] = (isset($payvalue['received_budget']) && !is_null($payvalue['received_budget'])) ? round($payvalue['received_budget']) : 0;
|
|
$excel_contract_array[$key][54] = (abs($excel_contract_array[$key][51] - $excel_contract_array[$key][53])>10)?round($excel_contract_array[$key][51] - $excel_contract_array[$key][53]):0;
|
|
$excel_contract_array[$key][55] = (isset($payvalue['collect_month']) && !is_null($payvalue['collect_month']) && abs($excel_contract_array[$key][54]) > 10) ? round($payvalue['collect_month']) : 0;
|
|
if (isset($excel_contract_all_array[$key])) {
|
|
$excel_contract_all_array[$key][16] = $excel_contract_array[$key][54];
|
|
$excel_contract_all_array[$key][17] = $excel_contract_array[$key][55];
|
|
$total_tryrun_collect_budget += $excel_contract_all_array[$key][16];
|
|
if ($isBoga == 1) {
|
|
$excel_contract_boga_array[$key][16] = $excel_contract_all_array[$key][16];
|
|
$excel_contract_boga_array[$key][17] = $excel_contract_all_array[$key][17];
|
|
$boga_tryrun_collect_budget += $excel_contract_boga_array[$key][16];
|
|
} else {
|
|
$excel_contract_noboga_array[$key][16] = $excel_contract_all_array[$key][16];
|
|
$excel_contract_noboga_array[$key][17] = $excel_contract_all_array[$key][17];
|
|
$noboga_tryrun_collect_budget += $excel_contract_noboga_array[$key][16];
|
|
}
|
|
}
|
|
break;
|
|
case "官檢驗收款":
|
|
$excel_contract_array[$key][56] = (isset($payvalue['PayStage']) && !is_null($payvalue['PayStage'])) ? $payvalue['PayStage'] : '--';
|
|
$excel_contract_array[$key][57] = (isset($payvalue['PlanPayAmt']) && !is_null($payvalue['PlanPayAmt'])) ? round($payvalue['PlanPayAmt']) : 0;
|
|
$excel_contract_array[$key][58] = (isset($payvalue['receivable_budget']) && !is_null($payvalue['receivable_budget'])) ? round($payvalue['receivable_budget']) : 0;
|
|
$excel_contract_array[$key][59] = (isset($payvalue['invoice_budget']) && !is_null($payvalue['invoice_budget'])) ? round($payvalue['invoice_budget']) : 0;
|
|
$excel_contract_array[$key][60] = (isset($payvalue['received_budget']) && !is_null($payvalue['received_budget'])) ? round($payvalue['received_budget']) : 0;
|
|
$excel_contract_array[$key][61] = (abs($excel_contract_array[$key][58] - $excel_contract_array[$key][60])>10)?round($excel_contract_array[$key][58] - $excel_contract_array[$key][60]):0;
|
|
$excel_contract_array[$key][62] = (isset($payvalue['collect_month']) && !is_null($payvalue['collect_month']) && abs($excel_contract_array[$key][61]) > 10) ? round($payvalue['collect_month']) : 0;
|
|
if (isset($excel_contract_all_array[$key])) {
|
|
$excel_contract_all_array[$key][18] = $excel_contract_array[$key][61];
|
|
$excel_contract_all_array[$key][19] = $excel_contract_array[$key][62];
|
|
$total_check_collect_budget += $excel_contract_all_array[$key][18];
|
|
|
|
$excel_contract_noboga_array[$key][18] = $excel_contract_all_array[$key][18];
|
|
$excel_contract_noboga_array[$key][19] = $excel_contract_all_array[$key][19];
|
|
$noboga_check_collect_budget += $excel_contract_noboga_array[$key][18];
|
|
}
|
|
break;
|
|
case "交車款":
|
|
$excel_contract_array[$key][63] = (isset($payvalue['PayStage']) && !is_null($payvalue['PayStage'])) ? $payvalue['PayStage'] : '--';
|
|
$excel_contract_array[$key][64] = (isset($payvalue['PlanPayAmt']) && !is_null($payvalue['PlanPayAmt'])) ? round($payvalue['PlanPayAmt']) : 0;
|
|
$excel_contract_array[$key][65] = (isset($payvalue['receivable_budget']) && !is_null($payvalue['receivable_budget'])) ? round($payvalue['receivable_budget']) : 0;
|
|
$excel_contract_array[$key][66] = (isset($payvalue['invoice_budget']) && !is_null($payvalue['invoice_budget'])) ? round($payvalue['invoice_budget']) : 0;
|
|
$excel_contract_array[$key][67] = (isset($payvalue['received_budget']) && !is_null($payvalue['received_budget'])) ? round($payvalue['received_budget']) : 0;
|
|
$excel_contract_array[$key][68] = (abs($excel_contract_array[$key][65] - $excel_contract_array[$key][67])>10)?round($excel_contract_array[$key][65] - $excel_contract_array[$key][67]):0;
|
|
$excel_contract_array[$key][69] = (isset($payvalue['collect_month']) && !is_null($payvalue['collect_month']) && abs($excel_contract_array[$key][68]) > 10) ? round($payvalue['collect_month']) : 0;
|
|
if (isset($excel_contract_all_array[$key])) {
|
|
$excel_contract_all_array[$key][20] = $excel_contract_array[$key][68];
|
|
$excel_contract_all_array[$key][21] = $excel_contract_array[$key][69];
|
|
$total_delivery_collect_budget += $excel_contract_all_array[$key][20];
|
|
$excel_contract_noboga_array[$key][20] = $excel_contract_all_array[$key][20];
|
|
$excel_contract_noboga_array[$key][21] = $excel_contract_all_array[$key][21];
|
|
$noboga_delivery_collect_budget += $excel_contract_noboga_array[$key][20];
|
|
}
|
|
break;
|
|
case "尾款":
|
|
$excel_contract_array[$key][70] = (isset($payvalue['PayStage']) && !is_null($payvalue['PayStage'])) ? $payvalue['PayStage'] : '--';
|
|
$excel_contract_array[$key][71] = (isset($payvalue['PlanPayAmt']) && !is_null($payvalue['PlanPayAmt'])) ? round($payvalue['PlanPayAmt']) : 0;
|
|
$excel_contract_array[$key][72] = (isset($payvalue['receivable_budget']) && !is_null($payvalue['receivable_budget'])) ? round($payvalue['receivable_budget']) : 0;
|
|
$excel_contract_array[$key][73] = (isset($payvalue['invoice_budget']) && !is_null($payvalue['invoice_budget'])) ? round($payvalue['invoice_budget']) : 0;
|
|
$excel_contract_array[$key][74] = (isset($payvalue['received_budget']) && !is_null($payvalue['received_budget'])) ? round($payvalue['received_budget']) : 0;
|
|
$excel_contract_array[$key][75] = (abs($excel_contract_array[$key][72] - $excel_contract_array[$key][74])>10)?round($excel_contract_array[$key][72] - $excel_contract_array[$key][74]):0;
|
|
$excel_contract_array[$key][76] = (isset($payvalue['collect_month']) && !is_null($payvalue['collect_month']) && abs($excel_contract_array[$key][75]) > 10) ? round($payvalue['collect_month']) : 0;
|
|
if (isset($excel_contract_all_array[$key])) {
|
|
$excel_contract_all_array[$key][22] = $excel_contract_array[$key][75];
|
|
$excel_contract_all_array[$key][23] = $excel_contract_array[$key][76];
|
|
$total_final_collect_budget += $excel_contract_all_array[$key][22];
|
|
if ($isBoga == 1) {
|
|
$excel_contract_boga_array[$key][22] = $excel_contract_all_array[$key][22];
|
|
$excel_contract_boga_array[$key][23] = $excel_contract_all_array[$key][23];
|
|
$boga_final_collect_budget += $excel_contract_boga_array[$key][22];
|
|
} else {
|
|
$excel_contract_noboga_array[$key][22] = $excel_contract_all_array[$key][22];
|
|
$excel_contract_noboga_array[$key][23] = $excel_contract_all_array[$key][23];
|
|
$noboga_final_collect_budget += $excel_contract_noboga_array[$key][22];
|
|
}
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
// 0 作番號; 1 合約號; 2 合約單據日期; 3 客戶編號; 4 客戶名稱; 5 客戶地址; 6 部門ID; 7 部門名稱; 8 營業員工號; 9 營業員名稱; 10 部門主管工號; 11 部門主管名稱;
|
|
// 12 作番總金額; 13 A40001 金額; 14 A40008 金額; 15 目前應收金額; 16 已開發票金額; 17 已收金額; 18 催收金額; 19 催收次數; 20 作番狀態
|
|
if (isset($value['facility']) && is_iterable($value['facility'])) {
|
|
foreach ($value['facility'] as $fkey => $fvalue) {
|
|
$excel_facility_array[$fkey] = [$fkey, $key, $value['BillDate']];
|
|
$excel_facility_array[$key][2] = (isset($value['CustomerId']) && !is_null($value['CustomerId'])) ? $value['CustomerId'] : '';
|
|
$excel_facility_array[$key][3] = (isset($value['CustomerName']) && !is_null($value['CustomerName'])) ? $value['CustomerName'] : '';
|
|
$excel_facility_array[$key][4] = (isset($value['CustomerAddress']) && !is_null($value['CustomerAddress'])) ? $value['CustomerAddress'] : '';
|
|
$excel_facility_array[$key][5] = (isset($value['DeptId']) && !is_null($value['DeptId'])) ? $value['DeptId'] : '';
|
|
$excel_facility_array[$key][6] = (isset($value['DeptName']) && !is_null($value['DeptName'])) ? $value['DeptName'] : '';
|
|
$excel_facility_array[$key][7] = (isset($value['PersonId']) && !is_null($value['PersonId'])) ? $value['PersonId'] : '';
|
|
$excel_facility_array[$key][8] = (isset($value['PersonName']) && !is_null($value['PersonName'])) ? $value['PersonName'] : '';
|
|
$excel_facility_array[$key][9] = (isset($value['ManagerId']) && !is_null($value['ManagerId'])) ? $value['ManagerId'] : '';
|
|
}
|
|
}
|
|
}
|
|
// $excel_contract_all_array['last']= $excel_contract_boga_array['last'] = $excel_contract_noboga_array['last'] = array_fill(0, 23, '');
|
|
$excel_contract_all_array['last']= ['合計', '', '', '','', $total_collect_facility, '', $total_collect_budget, $total_sign_collect_budget, '', $total_second_collect_budget, '', $total_arrival_collect_budget, '', $total_install_collect_budget, '', $total_tryrun_collect_budget, '', $total_check_collect_budget, '', $total_delivery_collect_budget, '', $total_final_collect_budget, ''];
|
|
$excel_contract_boga_array['last']= ['合計', '', '', '','', $boga_collect_facility, '', $boga_collect_budget, $boga_sign_collect_budget, '', $boga_second_collect_budget, '', $boga_arrival_collect_budget, '', $boga_install_collect_budget, '', $boga_tryrun_collect_budget, '', $boga_check_collect_budget, '', $boga_delivery_collect_budget, '', $boga_final_collect_budget, ''];
|
|
$excel_contract_noboga_array['last']= ['合計', '', '', '','', $noboga_collect_facility, '', $noboga_collect_budget, $noboga_sign_collect_budget, '', $noboga_second_collect_budget, '', $noboga_arrival_collect_budget, '', $noboga_install_collect_budget, '', $noboga_tryrun_collect_budget, '', $noboga_check_collect_budget, '', $noboga_delivery_collect_budget, '', $noboga_final_collect_budget, ''];
|
|
|
|
// print_r($excel_contract_array['M230064']);
|
|
// echo "<br>---------------------------------------------------------<br>";
|
|
// foreach($arrayData['M230064'] as $key => $value){
|
|
// if($key == 'PayStage'){
|
|
// echo "是PayStage!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! <br>";
|
|
// foreach($value as $k => $v){
|
|
|
|
// echo $k.' => ';
|
|
// print_r($v);
|
|
// echo '<br>';
|
|
// }
|
|
// echo "-----------------------<br>";
|
|
// }else{
|
|
// echo $key.' => ';
|
|
// print_r($value);
|
|
// echo '<br>';
|
|
// }
|
|
// }
|
|
// print_r($arrayData['M220003']);
|
|
// exit();
|
|
$total_data = json_encode($excel_contract_all_array);
|
|
$boga_data = json_encode($excel_contract_boga_array);
|
|
$noboga_data = json_encode($excel_contract_noboga_array);
|
|
$table = "";
|
|
$testtotal = 0;
|
|
// foreach ($arrayData as $key => $value) {
|
|
// if (isset($value['facility']) && is_iterable($value['facility'])) {
|
|
// foreach ($value['facility'] as $fkey => $fvalue) {
|
|
// // echo $fkey . "<br>";
|
|
// foreach ($fvalue['PayStage'] as $pkey => $pvalue) {
|
|
// // echo $pkey . "<br>";
|
|
// // print_r($pvalue);
|
|
// // echo "<br>";
|
|
// $invoice_budget = round($pvalue['invoice_budget']);
|
|
// $received_budget = round($pvalue['received_budget']);
|
|
// $budget = round($pvalue['PlanPayAmt']);
|
|
// $paydate = $pvalue['PlanPayDate'];
|
|
// switch ($pvalue['stage']) {
|
|
// case '訂金':
|
|
// $stageno = 100;
|
|
// break;
|
|
// case '二次款':
|
|
// $stageno = 200;
|
|
// break;
|
|
// case '貨抵工地款':
|
|
// $stageno = 300;
|
|
// break;
|
|
// case '安裝款':
|
|
// $stageno = 400;
|
|
// break;
|
|
// case '試車款':
|
|
// $stageno = 500;
|
|
// break;
|
|
// case '官檢驗收款':
|
|
// $stageno = 600;
|
|
// break;
|
|
// case '交車款':
|
|
// $stageno = 700;
|
|
// break;
|
|
// case '尾款':
|
|
// $stageno = 800;
|
|
// break;
|
|
// default:
|
|
// $stageno = 0;
|
|
// break;
|
|
// }
|
|
// switch ($pvalue['hadtopay']) {
|
|
// case "V":
|
|
// $hadtopay = 1;
|
|
// break;
|
|
// default:
|
|
// $hadtopay = 0;
|
|
// break;
|
|
// }
|
|
// $invoice = 0;
|
|
// if ($pvalue['invoice_budget'] > 1) {
|
|
// $invoice = 1;
|
|
// $invoice_state = 1;
|
|
// }
|
|
// $table1 = "INSERT INTO account_receivable(
|
|
// `contract_no`,
|
|
// `facility_no`,
|
|
// `stage`,
|
|
// `budget`,
|
|
// `receivable`,
|
|
// `invoice`,
|
|
// `invoice_budget`,
|
|
// `received_budget`";
|
|
|
|
|
|
// $table2 = ") VALUES ('$key','$fkey',$stageno,$budget,$hadtopay,$invoice,$invoice_budget,$received_budget";
|
|
// if (!is_null($paydate) && !empty($paydate)) {
|
|
// $table1 .= ",`receivable_date`";
|
|
// $table2 .= ",'$paydate'";
|
|
// }
|
|
// if (!is_null($invoice_state) && !empty($invoice_state)) {
|
|
// $table1 .= ",`invoice_state`";
|
|
// $table2 .= ",$invoice_state";
|
|
// }
|
|
// $table .= $table1 . $table2 . "); \n";
|
|
// }
|
|
// }
|
|
// }
|
|
// }
|
|
// $file_path = 'account_receivable_sql.sql';
|
|
// if (file_put_contents($file_path, $table) !== false) {
|
|
// echo "SQL文件已成功生成：{$file_path}";
|
|
// } else {
|
|
// echo "生成SQL文件时出现错误。";
|
|
// }
|
|
|
|
?>
|
|
<style>
|
|
table {
|
|
table-layout: fixed;
|
|
width: 100%;
|
|
}
|
|
|
|
td {
|
|
word-wrap: break-word;
|
|
}
|
|
|
|
th {
|
|
text-align: center;
|
|
}
|
|
|
|
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;
|
|
|
|
}
|
|
</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('all')" class="btn btn-success">全部<span class="glyphicon glyphicon-download-alt"></span></button>
|
|
<button type="button" style="width: 12%;" onclick="downloadData('noboga')" class="btn btn-info">不含寶佳<span class="glyphicon glyphicon-download-alt"></span></button>
|
|
<button type="button" style="width: 12%;" onclick="downloadData('boga')" class="btn btn-warning">寶佳<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_A40001">合約設備總金額</label>
|
|
<input type="text" class="form-control" id="sum_A40001" name="sum_A40001" value="<?= number_format($sum_A40001) ?>" disabled>
|
|
</div>
|
|
<div class="col-md-3">
|
|
<label for="sum_A40008">合約安裝總金額</label>
|
|
<input type="text" class="form-control" id="sum_A40008" name="sum_A40008" value="<?= number_format($sum_A40008) ?>" disabled>
|
|
</div>
|
|
<div class="col-md-3">
|
|
<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_facility_budget">作番平均金額 (元/台)</label>
|
|
<input type="text" class="form-control" id="average_facility_budget" name="average_facility_budget" value="<?= number_format($average_facility_budget) ?>" disabled>
|
|
</div>
|
|
<div class="col-md-3">
|
|
<label for="average_A40001">設備平均金額 (元/台)</label>
|
|
<input type="text" class="form-control" id="average_A40001" name="average_A40001" value="<?= number_format($average_A40001) ?>" disabled>
|
|
</div>
|
|
<div class="col-md-3">
|
|
<label for="average_A40008">安裝平均金額 (元/台)</label>
|
|
<input type="text" class="form-control" id="average_A40008" name="average_A40008" value="<?= number_format($average_A40008) ?>" disabled>
|
|
</div>
|
|
<div class="col-md-3">
|
|
<label for="average_contract_facility">平均台數 (台/合約)</label>
|
|
<input type="text" class="form-control" id="average_contract_facility" name="average_contract_facility" value="<?= round($average_contract_facility,2) ?>" disabled>
|
|
</div>
|
|
</div>
|
|
</form>
|
|
<hr>
|
|
<form class="form-horizontal" id='myForm' method='post' action='account-receivable-new-index.php?<?= $token_link ?>'>
|
|
<table class="table table-striped table-bordered" style='width:98%;text-align:center;margin:0 auto'>
|
|
<tbody>
|
|
<tr>
|
|
<th class="text-center" style="vertical-align: middle;">合約日期</th>
|
|
<td colspan="2">
|
|
<input type="date" class='form-control' id='date_start' name='date_start' style='width:40%;display:inline;'>
|
|
～
|
|
<input type="date" class='form-control' id='date_end' name='date_end' style='width:40%;display:inline;'>
|
|
<button type="submit" class="btn btn-primary btn-sm">搜尋</button>
|
|
</td>
|
|
</tr>
|
|
<tr>
|
|
<th class="text-center" style="vertical-align: middle;">合約催收次數</th>
|
|
<td colspan="2">
|
|
<input type="text" class='form-control' id='search_collectstart' name='collect_time_start' style='width:40%;display:inline;' oninput="searchFront('collectstart')">
|
|
≤ 催收次數 ≤
|
|
<input type="text" class='form-control' id='search_collectend' name='collect_time_end' style='width:40%;display:inline;' oninput="searchFront('collectend')">
|
|
<!-- <button type="submit" class="btn btn-primary btn-sm">搜尋</button> -->
|
|
</td>
|
|
</tr>
|
|
<tr>
|
|
<th class="text-center" style="vertical-align: middle;">合約催收金額</th>
|
|
<td colspan="2">
|
|
<input type="text" class='form-control' id='search_CBudgetstart' name='collect_budget_start' style='width:40%;display:inline;' onblur="searchFront('CBudgetstart')">
|
|
≤ 催收金額 ≤
|
|
<input type="text" class='form-control' id='search_CBudgetend' name='collect_budget_end' style='width:40%;display:inline;' onblur="searchFront('CBudgetend')">
|
|
<!-- <button type="submit" class="btn btn-primary btn-sm">搜尋</button> -->
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</form>
|
|
<hr>
|
|
<div class="form-group">
|
|
<div class="col-md-4">
|
|
<label for="search_contract">查詢合約號</label>
|
|
<input type="text" id="search_contract" name="search_contract" class="form-control" placeholder="請輸入合約號" onblur="searchFront('contract')">
|
|
</div>
|
|
<div class="col-md-4">
|
|
<label for="search_dept">查詢部門</label>
|
|
<input type="text" id="search_dept" name="search_dept" class="form-control" placeholder="請輸入部門" onblur="searchFront('dept')">
|
|
</div>
|
|
<div class="col-md-4">
|
|
<label for="search_manager">查詢主管</label>
|
|
<input type="text" id="search_manager" name="search_manager" class="form-control" placeholder="請輸入主管工號/姓名" onblur="searchFront('manager')">
|
|
</div>
|
|
|
|
</div>
|
|
<div class="form-group">
|
|
|
|
<div class="col-md-4">
|
|
<label for="search_personId">查詢營業員</label>
|
|
<input type="text" id="search_personId" name="search_personId" class="form-control" placeholder="請輸入契約員工號/姓名" onblur="searchFront('personId')">
|
|
</div>
|
|
<div class="col-md-4">
|
|
<label for="search_customerid">查詢客戶編號</label>
|
|
<input type="text" id="search_customerid" name="search_customerid" class="form-control" placeholder="請輸入客戶編號" onblur="searchFront('customerid')">
|
|
</div>
|
|
<div class="col-md-4">
|
|
<label for="search_facility">查詢作番號</label>
|
|
<input type="text" id="search_facility" name="search_facility" class="form-control" placeholder="請輸入作番號" onblur="searchFront('facility')">
|
|
</div>
|
|
</div>
|
|
</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% ;overflow-x: auto;">
|
|
<table class="table table-striped table-bordered" style="width:100%;">
|
|
<thead>
|
|
<tr>
|
|
<th style="width: 100px;">合約號</th>
|
|
<th style="width: 100px;">部門</th>
|
|
<th style="width: 100px;">主管</th>
|
|
<th style="width: 100px;">營業員</th>
|
|
<th style="width: 150px;">客戶</th>
|
|
|
|
<th style="width: 280px;">作番狀態</th>
|
|
<th style="width: 110px;">合約<br>設備金額</th>
|
|
<th style="width: 110px;">合約<br>安裝金額</th>
|
|
<th style="width: 110px;">合約<br>總金額</th>
|
|
<th style="width: 110px;">合約<br>目前應收</th>
|
|
<th style="width: 110px;">合約<br>已開票金額</th>
|
|
<th style="width: 110px;">合約<br>已收金額</th>
|
|
<th style="width: 110px;">合約<br>催收金額</th>
|
|
<th style="width: 90px;">合約<br>催收次數</th>
|
|
<th style="width: 90px;">作番數量</th>
|
|
|
|
<th style="width: 120px;">訂金 <br>名稱</th>
|
|
<th style="width: 120px;">訂金<br>合約金額</th>
|
|
<th style="width: 120px;">訂金<br>應收金額</th>
|
|
<th style="width: 120px;">訂金<br>已開金額</th>
|
|
<th style="width: 120px;">訂金<br>已收金額</th>
|
|
<th style="width: 120px;">訂金<br>催收金額</th>
|
|
<th style="width: 100px;">訂金<br>催收次數</th>
|
|
|
|
<th style="width: 120px;">二次款 <br>名稱</th>
|
|
<th style="width: 120px;">二次款<br>合約金額</th>
|
|
<th style="width: 120px;">二次款<br>應收金額</th>
|
|
<th style="width: 120px;">二次款<br>已開金額</th>
|
|
<th style="width: 120px;">二次款<br>已收金額</th>
|
|
<th style="width: 120px;">二次款<br>催收金額</th>
|
|
<th style="width: 100px;">二次款<br>催收次數</th>
|
|
|
|
<th style="width: 120px;">貨抵工地款 <br>名稱</th>
|
|
<th style="width: 120px;">貨抵工地款<br>合約金額</th>
|
|
<th style="width: 120px;">貨抵工地款<br>應收金額</th>
|
|
<th style="width: 120px;">貨抵工地款<br>已開金額</th>
|
|
<th style="width: 120px;">貨抵工地款<br>已收金額</th>
|
|
<th style="width: 120px;">貨抵工地款<br>催收金額</th>
|
|
<th style="width: 100px;">貨抵工地款<br>催收次數</th>
|
|
|
|
<th style="width: 120px;">安裝款 <br>名稱</th>
|
|
<th style="width: 120px;">安裝款<br>合約金額</th>
|
|
<th style="width: 120px;">安裝款<br>應收金額</th>
|
|
<th style="width: 120px;">安裝款<br>已開金額</th>
|
|
<th style="width: 120px;">安裝款<br>已收金額</th>
|
|
<th style="width: 120px;">安裝款<br>催收金額</th>
|
|
<th style="width: 100px;">安裝款<br>催收次數</th>
|
|
|
|
<th style="width: 120px;">試車款 <br>名稱</th>
|
|
<th style="width: 120px;">試車款<br>合約金額</th>
|
|
<th style="width: 120px;">試車款<br>應收金額</th>
|
|
<th style="width: 120px;">試車款<br>已開金額</th>
|
|
<th style="width: 120px;">試車款<br>已收金額</th>
|
|
<th style="width: 120px;">試車款<br>催收金額</th>
|
|
<th style="width: 100px;">試車款<br>催收次數</th>
|
|
|
|
<th style="width: 120px;">官檢驗收款 <br>名稱</th>
|
|
<th style="width: 120px;">官檢驗收款<br>合約金額</th>
|
|
<th style="width: 120px;">官檢驗收款<br>應收金額</th>
|
|
<th style="width: 120px;">官檢驗收款<br>已開金額</th>
|
|
<th style="width: 120px;">官檢驗收款<br>已收金額</th>
|
|
<th style="width: 120px;">官檢驗收款<br>催收金額</th>
|
|
<th style="width: 100px;">官檢驗收款<br>催收次數</th>
|
|
|
|
<th style="width: 120px;">交車款 <br>名稱</th>
|
|
<th style="width: 120px;">交車款<br>合約金額</th>
|
|
<th style="width: 120px;">交車款<br>應收金額</th>
|
|
<th style="width: 120px;">交車款<br>已開金額</th>
|
|
<th style="width: 120px;">交車款<br>已收金額</th>
|
|
<th style="width: 120px;">交車款<br>催收金額</th>
|
|
<th style="width: 100px;">交車款<br>催收次數</th>
|
|
|
|
<th style="width: 120px;">尾款 <br>名稱</th>
|
|
<th style="width: 120px;">尾款<br>合約金額</th>
|
|
<th style="width: 120px;">尾款<br>應收金額</th>
|
|
<th style="width: 120px;">尾款<br>已開金額</th>
|
|
<th style="width: 120px;">尾款<br>已收金額</th>
|
|
<th style="width: 120px;">尾款<br>催收金額</th>
|
|
<th style="width: 100px;">尾款<br>催收次數</th>
|
|
|
|
|
|
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php
|
|
foreach ($arrayData as $key => $value) {
|
|
?>
|
|
<tr class="data-row" style="display: '';" id="<?= $key ?>">
|
|
<th class="contract"><?= $key ?></th>
|
|
<td style="text-align: center;" class="dept"><?= $value['DeptId'] . "<br>" . $value['DeptName'] ?></td>
|
|
<td style="text-align: center;" class="manager"><?= $value['ManagerId'] . "<br>" . $value['ManagerName'] ?></td>
|
|
<td style="text-align: center;" class="personId"><?= $value['PersonId'] . "<br>" . $value['PersonName'] ?></td>
|
|
<td style="text-align: center;" class="customerid"><?= $value['CustomerId'] . "<br>" . $value['CustomerName'] ?></td>
|
|
|
|
<td style="text-align: end;" class="facility"><?= $value['facility_status'] ?></td>
|
|
<td style="text-align: end;"><?= number_format(round($value['A40001'])) ?></td>
|
|
<td style="text-align: end;"><?= number_format(round($value['A40008'])) ?></td>
|
|
<td style="text-align: end;"><?= number_format(round($value['total_budget'])) ?></td>
|
|
<td style="text-align: end;"><?= number_format(round($value['receivable_budget'])) ?></td>
|
|
<td style="text-align: end;"><?= number_format(round($value['invoice_budget'])) ?></td>
|
|
<td style="text-align: end;"><?= number_format(round($value['received_budget'])) ?></td>
|
|
<td style="text-align: end;" class="CBudget"><?= number_format(round($value['collect_budget'])) ?></td>
|
|
<td style="text-align: end;" class="collect"><?= $value['collect_month'] ?></td>
|
|
<td style="text-align: end;"><?= $value['facility_num'] ?></td>
|
|
|
|
<td style="text-align: center;"><?= $excel_contract_array[$key][21] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][22] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][23] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][24] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][25] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][26] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][27] ?></td>
|
|
|
|
<td style="text-align: center;"><?= $excel_contract_array[$key][28] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][29] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][30] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][31] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][32] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][33] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][34] ?></td>
|
|
|
|
<td style="text-align: center;"><?= $excel_contract_array[$key][35] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][36] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][37] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][38] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][39] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][40] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][41] ?></td>
|
|
|
|
<td style="text-align: center;"><?= $excel_contract_array[$key][42] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][43] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][44] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][45] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][46] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][47] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][48] ?></td>
|
|
|
|
<td style="text-align: center;"><?= $excel_contract_array[$key][49] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][50] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][51] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][52] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][53] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][54] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][55] ?></td>
|
|
|
|
<td style="text-align: center;"><?= $excel_contract_array[$key][56] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][57] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][58] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][59] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][60] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][61] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][62] ?></td>
|
|
|
|
<td style="text-align: center;"><?= $excel_contract_array[$key][63] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][64] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][65] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][66] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][67] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][68] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][69] ?></td>
|
|
|
|
<td style="text-align: center;"><?= $excel_contract_array[$key][70] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][71] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][72] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][73] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][74] ?></td>
|
|
<td style="text-align: end;"><?= $excel_contract_array[$key][75] ?></td>
|
|
<td style="text-align: end;"><?= isset($excel_contract_array[$key][76]) ? $excel_contract_array[$key][76] : 0 ?></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 searchFront(area) {
|
|
var term = 'search_' + area;
|
|
|
|
if (area == 'collectstart') {
|
|
var searchTerm = parseInt(document.getElementById(term).value);
|
|
var searchArea = document.getElementsByClassName('collect');
|
|
var rows = document.getElementsByClassName('data-row');
|
|
var criterion = parseInt(document.getElementById('search_collectend').value);
|
|
for (var i = 0; i < rows.length; i++) {
|
|
var rowText = parseInt(searchArea[i].textContent);
|
|
if (rowText >= searchTerm) {
|
|
rows[i].style.display = '';
|
|
} else {
|
|
rows[i].style.display = 'none';
|
|
}
|
|
if (criterion !== '' && rowText > criterion) {
|
|
rows[i].style.display = 'none';
|
|
}
|
|
}
|
|
} else if (area == 'collectend') {
|
|
var searchTerm = parseInt(document.getElementById(term).value);
|
|
var searchArea = document.getElementsByClassName('collect');
|
|
var rows = document.getElementsByClassName('data-row');
|
|
var criterion = parseInt(document.getElementById('search_collectstart').value);
|
|
for (var i = 0; i < rows.length; i++) {
|
|
var rowText = parseInt(searchArea[i].textContent);
|
|
if (rowText <= searchTerm) {
|
|
rows[i].style.display = '';
|
|
} else {
|
|
rows[i].style.display = 'none';
|
|
}
|
|
if (criterion !== '' && rowText < criterion) {
|
|
rows[i].style.display = 'none';
|
|
}
|
|
}
|
|
} else if (area == 'CBudgetstart') {
|
|
var searchTerm = parseInt(document.getElementById(term).value);
|
|
var searchArea = document.getElementsByClassName('CBudget');
|
|
var rows = document.getElementsByClassName('data-row');
|
|
var criterion = parseInt(document.getElementById('search_CBudgetend').value);
|
|
for (var i = 0; i < rows.length; i++) {
|
|
var rowText = parseInt(searchArea[i].textContent.replace(/,/g, ''));
|
|
if (isNaN(searchTerm) || searchTerm == 0) {
|
|
rows[i].style.display = '';
|
|
} else if (rowText >= searchTerm) {
|
|
rows[i].style.display = '';
|
|
} else {
|
|
rows[i].style.display = 'none';
|
|
}
|
|
if (criterion !== '' && rowText > criterion) {
|
|
rows[i].style.display = 'none';
|
|
}
|
|
}
|
|
} else if (area == 'CBudgetend') {
|
|
var searchTerm = parseInt(document.getElementById(term).value);
|
|
var searchArea = document.getElementsByClassName('CBudget');
|
|
var rows = document.getElementsByClassName('data-row');
|
|
var criterion = parseInt(document.getElementById('search_CBudgetstart').value);
|
|
for (var i = 0; i < rows.length; i++) {
|
|
var rowText = parseInt(searchArea[i].textContent.replace(/,/g, ''));
|
|
if (isNaN(searchTerm) || searchTerm == 0) {
|
|
rows[i].style.display = '';
|
|
} else if (rowText <= searchTerm) {
|
|
rows[i].style.display = '';
|
|
} else {
|
|
rows[i].style.display = 'none';
|
|
}
|
|
if (!isNaN(criterion) && rowText < criterion) {
|
|
rows[i].style.display = 'none';
|
|
}
|
|
}
|
|
} else {
|
|
var searchTerm = document.getElementById(term).value.toLowerCase();
|
|
var searchArea = document.getElementsByClassName(area);
|
|
var rows = document.getElementsByClassName('data-row');
|
|
for (var i = 0; i < rows.length; i++) {
|
|
var rowText = searchArea[i].textContent.toLowerCase();
|
|
if (rowText.includes(searchTerm)) {
|
|
rows[i].style.display = '';
|
|
} else {
|
|
rows[i].style.display = 'none';
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
function downloadData(content) {
|
|
if (content == 'all') {
|
|
var BillData = <?= $total_data ?>;
|
|
var filename = "全部應收帳款" + "<?= date('Ymd-Hm') ?>" + ".xlsx";
|
|
} else if (content == 'noboga') {
|
|
var BillData = <?= $noboga_data ?>;
|
|
var filename = "不含寶佳應收帳款" + "<?= date('Ymd-Hm') ?>" + ".xlsx";
|
|
} else if (content == 'boga') {
|
|
var BillData = <?= $boga_data ?>;
|
|
var filename = "寶佳應收帳款" + "<?= date('Ymd-Hm') ?>" + ".xlsx";
|
|
}
|
|
var xhr = new XMLHttpRequest();
|
|
var url = window.location.origin + "/wms/account-receivable-excel.php?type=newContract&<?= $token_link ?>";
|
|
xhr.open('POST', url, true);
|
|
xhr.setRequestHeader('Content-Type', 'application/json');
|
|
xhr.onreadystatechange = function() {
|
|
if (xhr.readyState === 4 && xhr.status === 200) {
|
|
var link = document.createElement('a');
|
|
link.setAttribute('href', window.location.origin + "/wms/account-receivable-contract.xlsx");
|
|
link.setAttribute('download', filename);
|
|
link.style.display = 'none';
|
|
document.body.appendChild(link);
|
|
link.click();
|
|
document.body.removeChild(link);
|
|
}
|
|
}
|
|
xhr.send(JSON.stringify({
|
|
Bill: BillData
|
|
}));
|
|
|
|
|
|
}
|
|
</script>
|