5) {
sleep(1);
header("Location:" . $WebSite . "login/login_form.php");
exit;
}
include $_SERVER['DOCUMENT_ROOT'] . '/load_header.php';
// 첫 화면 표시 문구
$title_message = '거래처 원장(VAT 포함)';
?>
<?=$title_message?>
prepare($balanceSql);
$balanceStmt->execute([':lastMonthDate' => $lastMonthDate]);
$balanceData = $balanceStmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($balanceData as $row) {
$secondordnum = $row['secondordnum'];
$balance = (float)str_replace(',', '', $row['balance']);
if (!isset($initialBalances[$secondordnum])) {
$initialBalances[$secondordnum] = 0;
}
$initialBalances[$secondordnum] += $balance;
}
// 매출이 발생한 거래처 필터링 및 매출액 계산
$salesSql = "
SELECT secondordnum, orderlist, accessorieslist, controllerlist, fabriclist, dcadd
FROM motor
WHERE (deadline BETWEEN date('$fromdate') AND date('$Transtodate')) AND is_deleted IS NULL
";
$salesStmt = $pdo->prepare($salesSql);
$salesStmt->execute();
$salesData = $salesStmt->fetchAll(PDO::FETCH_ASSOC);
$salesResults = [];
foreach ($salesData as $row) {
$secondordnum = $row['secondordnum'];
$orderlist = json_decode($row['orderlist'], true);
$accessorieslist = json_decode($row['accessorieslist'], true);
$controllerlist = json_decode($row['controllerlist'], true);
$fabriclist = json_decode($row['fabriclist'], true);
// 추가할인부분 빼기
$dcadd = (float)str_replace(',', '', trim($row['dcadd'])) ; // 숫자 외 문자를 제거하고 float로 변환
$total_sales = 0;
if (is_array($orderlist)) {
foreach ($orderlist as $item) {
$total_sales += isset($item['col12']) ? (float)str_replace(',', '', trim($item['col12'])) : 0;
}
}
if (is_array($accessorieslist)) {
foreach ($accessorieslist as $item) {
$total_sales += isset($item['col4']) ? (float)str_replace(',', '', trim($item['col4'])) : 0;
}
}
if (is_array($controllerlist)) {
foreach ($controllerlist as $item) {
$total_sales += isset($item['col7']) ? (float)str_replace(',', '', trim($item['col7'])) : 0;
}
}
if (is_array($fabriclist)) {
foreach ($fabriclist as $item) {
$total_sales += isset($item['col9']) ? (float)str_replace(',', '', trim($item['col9'])) : 0;
}
}
if (!isset($salesResults[$secondordnum])) {
$salesResults[$secondordnum] = 0;
}
$salesResults[$secondordnum] += ($total_sales - $dcadd) ;
}
// 모든 거래처 목록을 생성 (매출, 기초채권)
$allResults = array_unique(array_merge(array_keys($salesResults), array_keys($initialBalances)));
// rsort($allResults); // 거래처 코드 기준 정렬
// // 매출 금액 기준으로 정렬
// usort($allResults, function($a, $b) use ($salesResults) {
// $salesA = isset($salesResults[$a]) ? $salesResults[$a] : 0;
// $salesB = isset($salesResults[$b]) ? $salesResults[$b] : 0;
// return $salesB - $salesA;
// });
// 매출 금액 기준으로 역순으로 정렬
usort($allResults, function($a, $b) use ($salesResults) {
$salesA = isset($salesResults[$a]) ? $salesResults[$a] : 0;
$salesB = isset($salesResults[$b]) ? $salesResults[$b] : 0;
return $salesA - $salesB;
});
// echo '';
// print_r($allResults);
// echo '
';
// 합계를 저장할 변수들
$totalInitialReceivable = 0;
$totalSalesAmount = 0;
$totalPaymentAmount = 0;
$totalBalanceDue = 0;
try {
$start_num = 1;
foreach ($allResults as $secondordnum) {
// 이월 잔액 설정
$initialReceivable = isset($initialBalances[$secondordnum]) ? $initialBalances[$secondordnum] : 0;
// 수금 내역 가져오기
$paymentSql = "SELECT SUM(CAST(REPLACE(payment, ',', '') AS UNSIGNED)) as total_payment
FROM ".$DB.".getmoney
WHERE secondordnum = '$secondordnum'
AND registedate BETWEEN date('$fromdate') AND date('$Transtodate') AND is_deleted IS NULL";
$paymentStmt = $pdo->prepare($paymentSql);
$paymentStmt->execute();
$paymentData = $paymentStmt->fetch(PDO::FETCH_ASSOC);
$total_payment = isset($paymentData['total_payment']) ? (int)str_replace(',', '', $paymentData['total_payment']) : 0;
$total_sales = isset($salesResults[$secondordnum]) ? $salesResults[$secondordnum] : 0;
// 조건: 기초채권이 있거나 매출이 있는 경우만 표시
if ($initialReceivable != 0 || $total_sales != 0 ) {
$sql = "SELECT * FROM ".$DB.".".$tablename."
WHERE secondordnum = '$secondordnum'
AND is_deleted IS NULL
AND represent='아이디부여'";
if (checkNull($search)) {
$sql .= " AND (vendor_name LIKE '%$search%' OR representative_name LIKE '%$search%' OR manager_name LIKE '%$search%')";
}
$stmh = $pdo->query($sql);
while ($row = $stmh->fetch(PDO::FETCH_ASSOC)) {
include $_SERVER['DOCUMENT_ROOT'] . '/phonebook/_row.php';
if (empty($contact_info))
$contact_info = $phone;
if (intval($secondordnum) > 0)
$savenum = $secondordnum;
else
$savenum = $num;
// VAT를 포함한 총 매출액
$vat = $total_sales * 0.1;
$total_amount = $total_sales + $vat;
// 잔액 계산
$balance_due = $initialReceivable + $total_amount - $total_payment;
if ($balance_due != 0) {
// 각 열의 합계 계산
$totalInitialReceivable += $initialReceivable;
$totalSalesAmount += $total_amount;
$totalPaymentAmount += $total_payment;
$totalBalanceDue += $balance_due;
}
}
}
}
} catch (PDOException $Exception) {
print "오류: ".$Exception->getMessage();
}
?>