5) {
sleep(1);
header("Location:" . $WebSite . "login/login_form.php");
exit;
}
include $_SERVER['DOCUMENT_ROOT'] . '/load_header.php';
// 첫 화면 표시 문구
$title_message = '거래처 원장(VAT 포함)';
?>
<?=$title_message?>
';
// echo '이월잔액 기간 lastMonthEnd: ';
// print_r($lastMonthEnd);
// echo '';
$searchsecondordnum = '';
if(!empty($search))
{
$sql = "SELECT secondordnum FROM ".$DB.".".$tablenamephonebook."
WHERE (is_deleted IS NULL OR is_deleted = 0 or is_deleted ='' )
AND represent='대표코드' AND (vendor_name LIKE '%$search%')";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$searchsecondordnum = $row['secondordnum'];
}
// 거래처별 매출 및 수금 데이터 계산 (이월잔액)
// 주일 work 테이블의 컬럼을 추가해야 함.
$salesBeforeSql = "
SELECT o.secondordnum, SUM(COALESCE(e.ET_total, 0)) AS total_sales
FROM {$DB}.work o
LEFT JOIN {$DB}.output_extra e ON o.num = e.parent_num
WHERE o.outdate <= :lastMonthEnd AND (o.is_deleted IS NULL or o.is_deleted=0 or o.is_deleted ='')
GROUP BY o.secondordnum
";
$paymentBeforeSql = "
SELECT secondordnum, SUM(CAST(REPLACE(amount, ',', '') AS SIGNED)) AS total_payment
FROM {$DB}.account_juil
WHERE registDate <= :lastMonthEnd AND (is_deleted IS NULL or is_deleted=0 or is_deleted ='' ) AND content = '거래처 수금'
AND (dueDate = '0000-00-00' OR dueDate IS NULL OR dueDate = '')
GROUP BY secondordnum
";
$salesBeforeStmt = $pdo->prepare($salesBeforeSql);
$salesBeforeStmt->execute([':lastMonthEnd' => $lastMonthEnd]);
$salesBeforeData = $salesBeforeStmt->fetchAll(PDO::FETCH_ASSOC);
$paymentBeforeStmt = $pdo->prepare($paymentBeforeSql);
$paymentBeforeStmt->execute([':lastMonthEnd' => $lastMonthEnd]);
$paymentBeforeData = $paymentBeforeStmt->fetchAll(PDO::FETCH_ASSOC);
// 거래처별로 매출과 수금을 비교해 이월 잔액을 계산
foreach ($salesBeforeData as $row) {
$secondordnum = $row['secondordnum'];
$total_sales = (float)$row['total_sales'];
if (!isset($initialBalances[$secondordnum])) {
$initialBalances[$secondordnum] = 0;
}
$initialBalances[$secondordnum] += $total_sales;
}
// echo '
';
// print_r($initialBalances);
// echo '
';
// 수금 데이터를 이용해 이월 잔액에서 수금을 차감
foreach ($paymentBeforeData as $row) {
$secondordnum = $row['secondordnum'];
$total_payment = (float)$row['total_payment'];
if (!isset($initialBalances[$secondordnum])) {
$initialBalances[$secondordnum] = 0;
}
$initialBalances[$secondordnum] -= $total_payment;
}
// echo '
';
// print_r($initialBalances);
// echo '
';
// 매출이 발생한 거래처 필터링 및 매출액 계산
$salesSql = "
SELECT o.secondordnum, COALESCE(e.ET_total, 0) as ET_total
FROM {$DB}.work o
LEFT JOIN {$DB}.output_extra e ON o.num = e.parent_num
WHERE (o.outdate BETWEEN date('$fromdate') AND date('$Transtodate'))
AND (o.is_deleted IS NULL OR o.is_deleted = 0 or o.is_deleted ='')
";
$salesStmt = $pdo->prepare($salesSql);
$salesStmt->execute();
$salesData = $salesStmt->fetchAll(PDO::FETCH_ASSOC);
$salesResults = [];
foreach ($salesData as $row) {
$secondordnum = $row['secondordnum'];
$total_sales = (float)$row['ET_total'];
if (!isset($salesResults[$secondordnum])) {
$salesResults[$secondordnum] = 0;
}
$salesResults[$secondordnum] += $total_sales;
}
// echo '