gary_wms/wms/maintainance_contract_payment_status_modal.php

<?php
include "database.php";
include "fun_global.php";

function getContractnoDetails($link, $user_id, $department_id, $contractno = null)
{
    $sql = "
        SELECT
            c.contractno
        FROM contract AS c
        LEFT JOIN con_maintance_examine_apply AS cmea
        ON c.contractno = cmea.vol_no
        WHERE 1 = 1
    ";

    if (!in_array($department_id, ['210', '220', '240'])) {
        $sql .= " AND cmea.salesman IN (" . getAccountids($link, $user_id) . ")";
    }

    if (!empty($contractno)) {
        $sql .= "
            AND c.contractno = '$contractno'
        ";
    }
    $result = mysqli_query($link, $sql);
    $data = [];
    foreach ($result as $row) {
        array_push($data, $row['contractno']);
    }
    return "'" . implode("','", $data) . "'";
}

function getSalesmanNo($link, $contractno)
{
    $sql = "
        SELECT
            cmea.salesman
        FROM contract AS c
        LEFT JOIN con_maintance_examine_apply AS cmea
        ON c.contractno = cmea.vol_no
        WHERE 1 = 1
        AND c.contractno IN ('$contractno')
    ";
    $result = mysqli_query($link, $sql);
    $data = [];
    foreach ($result as $row)
        return $row['salesman'];
    return "";
}
function getSalesmanName($link, $contractno)
{
    $sql = "
        SELECT
            a.name
        FROM contract AS c
        LEFT JOIN con_maintance_examine_apply AS cmea
        ON c.contractno = cmea.vol_no
        LEFT JOIN account AS a
        ON cmea.salesman = a.accountid
        WHERE 1 = 1
        AND c.contractno IN ('$contractno')
    ";
    $result = mysqli_query($link, $sql);
    $data = [];
    foreach ($result as $row)
        return $row['name'];
    return "";
}

function getAccountids($link, $user_id)
{
    $sql = "
        SELECT
            accountid
        FROM account
        WHERE 1 = 1
        AND (accountid = '$user_id'
            OR accountid IN (
                SELECT
                    accountid
                FROM account
                WHERE 1 = 1
                AND manager = '$user_id'
                AND accounttype IN ('B','E','M','W')
            )
        )
    ";
    $result = mysqli_query($link, $sql);
    $data = [];
    foreach ($result as $row) {
        array_push($data, $row['accountid']);
    }
    return "'" . implode("','", $data) . "'";
}

function checkArCheckBillStatus($row)
{
    $CU_EstPayDate = $row['CU_EstPayDate'];
    $BillNo2 = $row['BillNo2'];
    if (substr($CU_EstPayDate, 0, 6) <= date("Ym")) {
        if (empty($BillNo2)) {
            return "<span class='text-danger'>未轉應收確認單</span>";
        } else {
            return "<span class='text-primary'>已轉應收確認單</span>";
        }
    } else {
        return "時間未到";
    }
}

function arSellInvoiceMaterial($row)
{

    $CU_EstPayDate = $row['CU_EstPayDate'];
    $InvoiceName = $row['InvoiceName'];

    if (substr($CU_EstPayDate, 0, 6) <= date("Ym")) {
        if (empty($InvoiceName)) {
            return "<span class='text-danger'>未開發票</span>";
        } else {
            return "<span class='text-primary'>已開發票</span>";
        }
    } else {
        return "時間未到";
    }
}

function checkCollectMonth($row)
{
    $CU_EstPayDate = $row['CU_EstPayDate'];
    if (collect_month(strtotime($CU_EstPayDate)) >= 3) {
        return "<span class='text-danger'>" . collect_month(strtotime($CU_EstPayDate)) . "</span>";
    } else {
        return "<span class=''>" . collect_month(strtotime($CU_EstPayDate)) . "</span>";
    }
}

function checkArWriteOffBill($conn, $row)
{
    $BillNo = $row['BillNo2'];

    $sql = "
        SELECT 
            *
        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 c.checkBillNo = '$BillNo'
    ";

    $del = $conn->query($sql);
    $i = 0;
    foreach ($del as $row)
        $i++;
    if (empty($i)) {
        return "<span class='text-danger'>未收款</span>";
    } else {
        return "<span class='text-primary'>已收款</span>";
    }
}

function getAllCount($conn, $sql)
{
    $result = $conn->query($sql);
    $count = 0;
    foreach ($result as $row) {
        $count++;
    }
    return $count;
}

// 設置一個空陣列來放資料
$data = array();

$columns = array(
    'PersonId',
    'DeptName',
    'BillNo',
    'RowNo',
    'CU_MaterialId',
    'BizPartnerName',
    'CU_EstPayDate',
    // 'checkCollectMonth',
    // 'checkArCheckBillStatus',
    'UnTransCheckBLAmtWTax',
    'HadTransCheckBLAmtWTax',
    'BillNo2',
    'arSellInvoiceMaterial',
    'RowCode2',
    'BillNo3',
    'InvoiceName',
    'OAmountWithTax',
    'InvoiceNo',
    'checkArWriteOffBill'
);

$user_id = empty($_POST['user_id']) ? null : $_POST['user_id'];
$department_id = accountidToDepartId($user_id);
$contractno = empty($_POST['contractno']) ? '' : getContractnoDetails($link, $user_id, $department_id, $_POST['contractno']);
$draw = $_POST['draw'];
$start = $_POST['start'];
$length = $_POST['length'];
$order_column = $_POST['order'][0]['column'];
$order_dir = $_POST['order'][0]['dir'];

$sql = "
    SELECT 
        siam.PersonId,
        cd.DeptName,
        cbp.BizPartnerName,
        siamd.RowNo,
        siamd.RowCode,
        siam.BillNo,
        siamd.CU_EstPayDate,
        siamd.UnTransCheckBLAmtWTax ,
        siamd.HadTransCheckBLAmtWTax,
        siamd.CU_MaterialId,
        acb_tmp.BillNo2,
        acb_tmp.RowCode2,
        asim.BillNo AS BillNo3,
        asim.InvoiceName,
        asim.OAmountWithTax,
        asi.InvoiceNo
    FROM salIncomeApplyMaster AS siam -- 收入申請單
    LEFT JOIN salIncomeApplyDetail AS siamd -- 收入申請單明細
    ON siam.BillNo = siamd.BillNo
    LEFT JOIN comPerson AS cp   -- 員工主檔
    ON siam.PersonId = cp.PersonId
    LEFT JOIN  comDepartment AS cd -- 部門主檔
    ON cp.DeptId = cd.DeptId
    LEFT JOIN (
        SELECT
            acbd.FromRowCode,
            acb.FromBillNo,
            acbd.BillNo AS BillNo2,
            acbd.RowCode AS RowCode2
        FROM arCheckBill AS acb -- 應收確認單
        LEFT JOIN arCheckBillDetail AS acbd -- 應收確認單明細
        ON acb.BillNo = acbd.BillNo
        WHERE 1 = 1
        AND acb.TypeId = 'RVS'
";
$sql .= !empty($contractno) ? " AND acb.FromBillNo IN ($contractno) " : "";
$sql .= "
    ) AS acb_tmp 
    ON siamd.RowNo = acb_tmp.FromRowCode
    AND siamd.BillNo = acb_tmp.FromBillNo
    LEFT JOIN arSellInvoiceMaterial AS asim -- 買賣發票明細
    ON asim.FromBillNo = acb_tmp.BillNo2
    AND asim.RowCode = acb_tmp.RowCode2
    LEFT JOIN arSellInvoice AS asi
    ON asim.BillNo = asi.BillNo
    LEFT JOIN comBusinessPartner AS cbp
    ON siam.BizPartnerId = cbp.BizPartnerId
    WHERE 1 = 1
";
$sql .= !empty($contractno) ? " AND siam.BillNo IN ($contractno)" : "";
if (!empty($_POST['search']['value'])) {

    $sql .= " 
        AND (
            siam.PersonId LIKE '%" . $_POST['search']['value'] . "%' 
            OR cd.DeptName LIKE '%" . $_POST['search']['value'] . "%'
            OR cbp.BizPartnerName LIKE '%" . $_POST['search']['value'] . "%'
            OR siam.BillNo LIKE '%" . $_POST['search']['value'] . "%'
            OR siamd.CU_MaterialId LIKE '%" . $_POST['search']['value'] . "%'
            OR asi.InvoiceNo LIKE '%" . $_POST['search']['value'] . "%'
            OR asim.InvoiceName LIKE '%" . $_POST['search']['value'] . "%'
            OR siamd.CU_EstPayDate LIKE '%" . $_POST['search']['value'] . "%'
            OR siamd.UnTransCheckBLAmtWTax LIKE '%" . $_POST['search']['value'] . "%'
            OR siamd.HadTransCheckBLAmtWTax LIKE '%" . $_POST['search']['value'] . "%'
            OR acb_tmp.BillNo2 LIKE '%" . $_POST['search']['value'] . "%'
            OR asim.OAmountWithTax LIKE '%" . $_POST['search']['value'] . "%'
            OR siamd.RowNo LIKE '%" . $_POST['search']['value'] . "%'
            OR siamd.RowCode LIKE '%" . $_POST['search']['value'] . "%'
            OR acb_tmp.RowCode2 LIKE '%" . $_POST['search']['value'] . "%'
        )
    ";
}
$count = getAllCount($conn, $sql);
$sql .= " ORDER BY " . $columns[$order_column] . " " . $order_dir;
$sql .= " OFFSET $start ROWS FETCH NEXT $length ROWS ONLY ";

$result = $conn->query($sql);
$data = [];

foreach ($result as $row) {
    $tmp = [];
    $tmp['PersonId'] = $row['PersonId'] . "<br/>" . accountidToName($row['PersonId']);
    $tmp['DeptName'] = $row['DeptName'];
    $tmp['BillNo'] = $row['BillNo'];
    $tmp['RowNo'] = $row['RowNo'];
    $tmp['CU_MaterialId'] = $row['CU_MaterialId'];
    $tmp['BizPartnerName'] = $row['BizPartnerName'];
    $tmp['CU_EstPayDate'] = date('Y/m/d', strtotime($row['CU_EstPayDate']));
    $tmp['checkCollectMonth'] = checkCollectMonth($row);
    $tmp['checkArCheckBillStatus'] = checkArCheckBillStatus($row);
    $tmp['UnTransCheckBLAmtWTax'] = number_format(intval($row['UnTransCheckBLAmtWTax']), 0, '', ',');
    $tmp['HadTransCheckBLAmtWTax'] = number_format(intval($row['HadTransCheckBLAmtWTax']), 0, '', ',');
    $tmp['BillNo2'] = $row['BillNo2'];
    $tmp['arSellInvoiceMaterial'] = arSellInvoiceMaterial($row);
    $tmp['RowCode2'] = $row['RowCode2'];
    $tmp['BillNo3'] = $row['BillNo3'];
    $tmp['InvoiceName'] = $row['InvoiceName'];
    $tmp['OAmountWithTax'] = number_format(intval($row['OAmountWithTax']), 0, '', ',');
    $tmp['InvoiceNo'] = $row['InvoiceNo'];
    $tmp['checkArWriteOffBill'] = checkArWriteOffBill($conn, $row);
    array_push($data, $tmp);
}

echo json_encode(array(
    "draw" => intval($draw),
    "recordsTotal" => $count,
    "recordsFiltered" => $count,
    "data" => $data
), JSON_UNESCAPED_UNICODE);