hskwon
aca1767eb9
- URL 하드코딩 → .env APP_URL 기반 동적 URL로 변경 - DB 연결 하드코딩 → .env 기반으로 변경 - MySQL strict mode DATE 오류 수정
134 lines
5.4 KiB
PHP
134 lines
5.4 KiB
PHP
<?php
|
|
// fetch_balance.php
|
|
function fetch_balances($DB, $fromdate, $todate) {
|
|
|
|
return [];
|
|
// require_once($_SERVER['DOCUMENT_ROOT'] . "/lib/mydb.php");
|
|
// $pdo = db_connect();
|
|
|
|
// // 현재 날짜
|
|
// $currentDate = date("Y-m-d");
|
|
|
|
// // fromdate 또는 todate가 빈 문자열이거나 null인 경우 처리
|
|
// if ($fromdate === "" || $fromdate === null || $todate === "" || $todate === null) {
|
|
// $fromdate = date("Y-m-01");
|
|
// $todate = $currentDate;
|
|
// }
|
|
|
|
// $initialBalances = [];
|
|
|
|
// // 이월 잔액을 직접 계산하는 로직
|
|
// $lastMonthEnd = date("Y-m-t", strtotime($fromdate . " -1 month"));
|
|
|
|
// // 미수금 내역 조회 (work + output_extra, outdate, ET_total)
|
|
// $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)
|
|
// 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) 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_before = round((float)$row['total_sales'],2); // 부가세 포함(이미 ET_total은 부가세 포함)
|
|
|
|
// if (!isset($initialBalances[$secondordnum])) {
|
|
// $initialBalances[$secondordnum] = 0;
|
|
// }
|
|
|
|
// $initialBalances[$secondordnum] += $total_sales_before;
|
|
// }
|
|
|
|
// foreach ($paymentBeforeData as $row) {
|
|
// $secondordnum = $row['secondordnum'];
|
|
// $total_payment_before = (float)$row['total_payment'];
|
|
|
|
// if (!isset($initialBalances[$secondordnum])) {
|
|
// $initialBalances[$secondordnum] = 0;
|
|
// }
|
|
|
|
// $initialBalances[$secondordnum] -= $total_payment_before;
|
|
// }
|
|
|
|
// // 당월 매출 내역 가져오기 (work + output_extra, outdate, ET_total)
|
|
// $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('$todate')) AND (o.is_deleted IS NULL or o.is_deleted = 0)
|
|
// ";
|
|
|
|
// $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']; // ET_total은 부가세 포함
|
|
|
|
// if (!isset($salesResults[$secondordnum])) {
|
|
// $salesResults[$secondordnum] = 0;
|
|
// }
|
|
// $salesResults[$secondordnum] += round($total_sales,2);
|
|
// }
|
|
|
|
// // 당월 수금 내역 가져오기 (account_juil 테이블)
|
|
// $paymentSql = "
|
|
// SELECT secondordnum, SUM(CAST(REPLACE(amount, ',', '') AS SIGNED)) AS total_payment
|
|
// FROM {$DB}.account_juil
|
|
// WHERE registDate BETWEEN date('$fromdate') AND date('$todate')
|
|
// AND (is_deleted IS NULL or is_deleted = 0) AND content = '거래처 수금'
|
|
// AND (dueDate = '0000-00-00' OR dueDate IS NULL OR dueDate = '')
|
|
// GROUP BY secondordnum
|
|
// ";
|
|
|
|
// $paymentStmt = $pdo->prepare($paymentSql);
|
|
// $paymentStmt->execute();
|
|
// $paymentData = $paymentStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// $paymentResults = [];
|
|
// foreach ($paymentData as $row) {
|
|
// $secondordnum = $row['secondordnum'];
|
|
// $total_payment = (float)$row['total_payment'];
|
|
|
|
// if (!isset($paymentResults[$secondordnum])) {
|
|
// $paymentResults[$secondordnum] = 0;
|
|
// }
|
|
// $paymentResults[$secondordnum] += $total_payment;
|
|
// }
|
|
|
|
// // 모든 거래처 목록을 생성 (매출, 기초채권)
|
|
// $allResults = array_unique(array_merge(array_keys($salesResults), array_keys($initialBalances)));
|
|
|
|
// // 최종 잔액 계산
|
|
// $balances = [];
|
|
// foreach ($allResults as $secondordnum) {
|
|
// $initialReceivable = isset($initialBalances[$secondordnum]) ? $initialBalances[$secondordnum] : 0;
|
|
// $total_sales = isset($salesResults[$secondordnum]) ? $salesResults[$secondordnum] : 0; // 부가세 포함
|
|
// $total_payment = isset($paymentResults[$secondordnum]) ? $paymentResults[$secondordnum] : 0;
|
|
|
|
// // 최종 잔액
|
|
// $balances[$secondordnum] = $initialReceivable + $total_sales - $total_payment;
|
|
// }
|
|
// return $balances;
|
|
}
|
|
?>
|