|
Server : Apache System : Linux ruga7-004.fmcity.com 3.10.0-1160.119.1.el7.x86_64 #1 SMP Tue Jun 4 14:43:51 UTC 2024 x86_64 User : tkt_travelbus ( 1137) PHP Version : 7.0.0p1 Disable Function : mysql_pconnect Directory : /tkt_travelbus/www/LibClass/ |
Upload File : |
<?php
/******************************************************************************
*
* 호텔/여행 공통 정산 및 입출금 클래스
*
******************************************************************************/
class AccountCustomer{
// Class 멤버변수
public $cf;
public $TO_CF;
public $TO_MB;
// 생성자
public function __construct() {
global $cf , $TO_CF;
global $TO_MB;
$this->cf = $cf;
$this->TO_CF = $TO_CF;
$this->TO_MB = $TO_MB;
}
// 입출금 - 고객 자동정산정보 계산
//
// @res_no : 예약번호
// @mode : 여행상품(good_res)
public function account_customer_check($res_no,$mode="")
{
global $TO_MB;
global $cf;
Log::save(sprintf("%s(%d)account_customer_check : %s %s - PG : %s ", __FILE__, __LINE__, $res_no, $mode,$cf['pg']['suply']), '', '/'.date('Ym'));
if (!$res_no) return false;
$table = "TB_ACCOUNT_CUSTOMER";
$acnt_cd = "";// 기초코드 > 자동정산숙박비
// 여행상품 예약내역
if ($mode=="good_res") {
$row = res_good_master(" and res_no='{$res_no}' and res_stat_cd='C' ");
$acnt_cd = ""; // 여행상품은 acnt_cd가 없음.
}
// 할인항공 예약내역
elseif ($mode=="air_res") {
$row = res_air_master(" and res_no='{$res_no}' and res_stat_cd='C' ");
$acnt_cd = ""; // 할인항공은 acnt_cd가 없음.
}
// 렌트카 예약내역
elseif ($mode=="rentcar_res") {
$row = res_rentcar_master(" and res_no='{$res_no}' and res_stat_cd='C' ");
$acnt_cd = ""; // 할인항공은 acnt_cd가 없음.
}
// 호텔숙박 객실예약내역
else {
$class_HotelReserv = new HotelReserv();
//$row = $class_HotelReserv->res_hotel_master(" and res_no='{$res_no}' and res_stat_cd='C' ");
$row = $class_HotelReserv->res_hotel_master(" and res_no='{$res_no}' ");
$acnt_cd = "MOCU001";// 호텔숙박 acnt_cd는 기초코드 > 자동정산숙박비
}
// 기장정보에 숙박비자동정산(MOCU001)이 존재하는지 체크
$sql = "select * from {$table} where res_no='{$res_no}' and acnt_cd='{$acnt_cd}'"; // 기초코드 > 자동정산숙박비
$rs = db_query($sql);
$cnt = db_num($rs);
Log::save(sprintf("%s(%d)TB_ACCOUNT_CUSTOMER select : %s ", __FILE__, __LINE__, $sql), '', '/'.date('Ym'));
// 예약내역이 없을 경우 정산정보도 제거
if (count($row)==0) {
$sql = " update {$table} set delflag='Y' where res_no='{$res_no}' ";
Log::save(sprintf("%s(%d)TB_ACCOUNT_CUSTOMER update : %s ", __FILE__, __LINE__, $sql), '', '/'.date('Ym'));
db_query($sql);
return false;
}
// 누계 초기화
$price_total_tot = 0;// 합계금액
$price_cost_tot = 0;// 계약금액
for ($i=0 ; $i<count($row) ; $i++) {
// 합계금액누계(=입금예정(+))
$price_total_tot += $row[$i]['price_total'];
// 계약금액누계(=계약가(+))
$price_cost_tot += $row[$i]['price_cost_total'];
}
// 숙박비(or 여행상품)자동정산 정보(자동정산숙박비)가 있으면, 업데이트(update)
if ($cnt >= 1) {
$sql = "select pgacidx from {$table} where res_no='{$res_no}' order by pgacidx ASC limit 1";
$row_t = db_fetch($sql);
$pgacidx = $row_t['pgacidx'];
$sql = "update {$table} set ";
$sql .= "price_cost_plus='{$price_cost_tot}'";// 계약가(+)
$sql .= ",input_will_plus='{$price_total_tot}'";// 입금예정(+)
$sql .= ",edit_dt=now()";
##$sql .= ",ins_mb_id='{$TO_MB[mb_id]}'";
$sql .= ",delflag='N' ";
$sql .= " where res_no='{$res_no}' and pgacidx='{$pgacidx}' ";
db_query($sql);
}
// 숙박비(or 여행상품)자동정산 정보가 없으면, 인서트(insert)
else if ( $cnt == 0 ) {
$sql = "insert into {$table} set ";
$sql .= " res_no='{$res_no}'";
$sql .= ",acc_dt=curdate()";
$sql .= ",acnt_cd='{$acnt_cd}'";
$sql .= ",price_cost_plus='{$price_total_tot}'";// 계약가(+)
$sql .= ",input_will_plus='{$price_total_tot}'";// 입금예정(+)
$sql .= ",ins_dt=now()";
//$sql .= ",ins_mb_id='{$TO_MB[mb_id]}'";
db_query($sql);
}
Log::save(sprintf("%s(%d)TB_ACCOUNT_CUSTOMER update : %s ", __FILE__, __LINE__, $sql), '', '/'.date('Ym'));
}
// 입출금 기장 정보 - 고객
//
// @res_no : 예약코드
// @pgacidx : 입출금 row seq
//
// 프로시저 : SP_RES_GOOD_SEARCH도 수정해야함.
public function res_account_info($res_no,$pgacidx="")
{
if (!$res_no) return "";
if ($pgacidx) $sql_where = " and pgacidx='{$pgacidx}' ";
/*$sql = "select * , ";
$sql .= " date_format(acc_dt,'%Y.%m.%d') as acc_dt2,";
$sql .= " (select cd_nm from TB_CODEC where TB_CODEC.code=TB_ACCOUNT_CUSTOMER.acnt_cd and cd_a='ACC' and cd_b='MOCU' limit 1 ) as acnt_cd_nm, ";
$sql .= " (select cd_nm from TB_CODEC where TB_CODEC.code=TB_ACCOUNT_CUSTOMER.pgcase_cd and cd_a='PRD' and cd_b='INST' limit 1 ) as pgcase_cd_nm ";
$sql .= " from TB_ACCOUNT_CUSTOMER ";
$sql .= " where res_no='{$res_no}' and delflag='N' {$sql_where} ";
$sql .= " order by pgacidx ";*/
$sql = "select * , ";
$sql .= " date_format(acc_dt,'%Y.%m.%d') as acc_dt2,";
$sql .= " (select cd_nm from TB_CODEC where TB_CODEC.code=TB_ACCOUNT_CUSTOMER.acnt_cd and cd_a='ACC' and cd_b='MOCU' limit 1 ) as acnt_cd_nm, ";
$sql .= " (select cd_nm from TB_CODEC where TB_CODEC.code=TB_ACCOUNT_CUSTOMER.pgcase_cd and cd_a='PRD' and cd_b='INST' limit 1 ) as pgcase_cd_nm ";
$sql .= " from TB_ACCOUNT_CUSTOMER ";
$sql .= " where res_no='{$res_no}' and viewflag='Y' {$sql_where} ";
$sql .= " order by pgacidx ";
$rs = db_query($sql);
$data = array();//리턴변수
$acc_data = array();//입출금 기장
$i=0;
while ($row = db_fetch_array($rs)) {
$acc_data[$i] = $row;
if ($row['pgcase_cd']=="INST001") {
$acc_data[$i]['banking_info']=$row['bank_nm']." [".$row['bank_number']."]";
} else if ($row['pgcase_cd']=="INST002") {
$acc_data[$i]['banking_info']=$row['bank_nm']." [".$row['bank_number']."]";
} else if ($row['pgcase_cd']=="INST006") {
$acc_data[$i]['banking_info']=$row['bank_nm']." [".$row['bank_number']."]";
} else if($row['pgcase_cd']=="INST010"){ //wechat
$acc_data[$i]['banking_info']=$row['bank_nm']." [".$row['bank_number']."]";
} else if($row['pgcase_cd']=="INST011"){ //alipay
$acc_data[$i]['banking_info']=$row['bank_nm']." [".$row['bank_number']."]";
}
else {
$acc_data[$i]['banking_info']="";
}
// 계정
if ($row['ioac_cd']){
$data_ioac = base_codec("PRD","IOAC",$row['ioac_cd']); // 계정
$acc_data[$i]['ioac_nm'] = $data_ioac[0]['cd_nm'];
}
$i++;
}
// 개별데이터 요청
if ($pgacidx) return $acc_data;
$data['acc'] = $acc_data;
// 계약가,입금예정,통장출금 합계
/*$sql = "select sum(point_divide) as tot_point_divide, ";
$sql .= " sum(ifnull(price_cost_plus,0))-sum(ifnull(price_cost_minus,0)) as tot_price_cost, ";
$sql .= " sum(ifnull(input_will_plus,0)) - sum(ifnull(input_will_minus,0)) as tot_input_will,";
$sql .= " sum(ifnull(bank_plus,0)) as tot_bank_plus, "; // 입금계
$sql .= " sum(ifnull(bank_minus,0)) as tot_bank_minus, "; // 출금계
$sql .= " sum(ifnull(bank_plus,0)) - sum(ifnull(bank_minus,0)) as tot_bank, "; // 입출금계(=입금계-출금계)
$sql .= " SUM( IF(pgcase_cd='INST001', IFNULL(bank_plus,0),0 ) ) - SUM( IF(pgcase_cd='INST001', IFNULL(bank_minus,0),0) ) AS pay_bank , "; // 통장 입금계(2014-07-28)
$sql .= " SUM( IF(pgcase_cd='INST002', IFNULL(bank_plus,0),0 ) ) - SUM( IF(pgcase_cd='INST002', IFNULL(bank_minus,0),0) ) AS pay_card "; // 카드 입금계(2014-07-28)
$sql .= " from TB_ACCOUNT_CUSTOMER where res_no='{$res_no}' and delflag='N' ";*/
$sql = "select sum(point_divide) as tot_point_divide, ";
$sql .= " sum(ifnull(price_cost_plus,0))-sum(ifnull(price_cost_minus,0)) as tot_price_cost, ";
$sql .= " sum(ifnull(input_will_plus,0)) - sum(ifnull(input_will_minus,0)) as tot_input_will,";
$sql .= " sum(ifnull(bank_plus,0)) as tot_bank_plus, "; // 입금계
$sql .= " sum(ifnull(bank_minus,0)) as tot_bank_minus, "; // 출금계
$sql .= " sum(ifnull(bank_plus,0)) - sum(ifnull(bank_minus,0)) as tot_bank, "; // 입출금계(=입금계-출금계)
$sql .= " SUM( IF(pgcase_cd='INST001', IFNULL(bank_plus,0),0 ) ) - SUM( IF(pgcase_cd='INST001', IFNULL(bank_minus,0),0) ) AS pay_bank , "; // 통장 입금계(2014-07-28)
$sql .= " SUM( IF(pgcase_cd='INST002', IFNULL(bank_plus,0),0 ) ) - SUM( IF(pgcase_cd='INST002', IFNULL(bank_minus,0),0) ) AS pay_card , "; // 카드 입금계(2014-07-28)
$sql .= " SUM( IF(pgcase_cd='INST006', IFNULL(bank_plus,0),0 ) ) - SUM( IF(pgcase_cd='INST006', IFNULL(bank_minus,0),0) ) AS pay_benepia "; // 베네피아 입금계(2022-12-20)
$sql .= " from TB_ACCOUNT_CUSTOMER where res_no='{$res_no}' and viewflag='Y' ";
/*if(devCookie()){
echo $sql;
}*/
$rs = db_query($sql);
$row = db_fetch_all($rs);
$data['tot'] = $row[0];
// 개별 합계계산
$benefit_will = $row[0]['tot_input_will'] - $row[0]['tot_price_cost']; // 예정수익금 (= 입금예정금계 - 계약금계)
$pre_money = $row[0]['tot_input_will'] - $row[0]['tot_bank']; // 미입금 (= 입금예정금계 - 통장입출금계)
$pre_money = $pre_money - $row[0]['tot_point_divide'];// 미입금 = 미입금 + 적립금 결제 (2017-01-13.허종)
$pay_money = $row[0]['tot_bank']; // 입금( = 통장출금계)
$pay_bank = $row[0]['pay_bank']; // 통장입금(2014-07-28)
$pay_card = $row[0]['pay_card']; // 카드입금(2014-07-28)
$pay_benepia = $row[0]['pay_benepia']; // 카드입금(2014-07-28)
// 실이익금 계산
$benefit = 0;
if ($pre_money<=0) { // 입금할 금액이 없거나, 과입금일때 실수익금계산
$benefit = $row[0]['tot_bank'] - $row[0]['tot_price_cost'] ; // (실)수익금 = 입금액계 - 예약금계
}
// 반올림 각 항목에 기장
$data['tot']['benefit'] = round($benefit); // (실)수익금
$data['tot']['benefit_will'] = round($benefit_will); // 예정 수익금
$data['tot']['benefit'] = round($benefit); // 수익금
$data['tot']['pre_money'] = round($pre_money); // 미입금
$data['tot']['pay_money'] = round($pay_money); // 입금
$data['tot']['pay_bank'] = round($pay_bank); // 통장 입금(2014-07-28)
$data['tot']['pay_card'] = round($pay_card); // 카드 입금(2014-07-28)
$data['tot']['pay_benepia'] = round($pay_benepia); // 카드 입금(2014-07-28)
return $data;
}
public function res_account_all_info($res_no)
{
if($res_no){
$sql = "SELECT * FROM TB_ACCOUNT_CUSTOMER WHERE res_no = '{$res_no}'";
$res = db_query($sql);
$returnData = array();
while($dataRow = db_fetch_array($res)){
array_push($returnData, $dataRow);
}
return $returnData;
}
return false;
}
/*
//
// 호텔 정산 목록(예약완료C)
//
public function account_res_complete_hotel($sql_where="",$sql_order="",$olds=0,$line=0) {
if ($sql_where) $sql .= " ".$sql_where ;
$sql ="select * from (
SELECT a.res_dt,a.idx,a.res_no,a.hseq,a.agent_id,a.checkin_dt,
SUM(a.adult_num+a.child_num) AS inwon ,
SUM(a.room_cnt) AS room_cnt,
SUM(a.lodg_day) AS lodg_cnt,
SUM(a.price_cost_total) AS price_cost_total,
SUM(a.price_sell_total) AS price_sell_total,
SUM(a.price_agent_total) AS price_agent_total,
SUM(a.price_total) AS price_total
FROM
(SELECT c.res_dt,b.idx,c.res_no,b.res_stat_cd,c.adult_num,c.child_num,c.agent_id,b.checkin_dt,
b.hseq,b.room_cnt,
b.lodg_day,b.price_cost_total,b.price_sell_total,b.price_agent_total,b.price_total
FROM TB_RES_HOTEL_ROOM b , TB_RES_HOTEL c
WHERE b.res_no=c.res_no AND b.res_stat_cd='C' ) a
WHERE 0=0 {$sql_where}
GROUP BY a.res_no,a.hseq
) tb_acc where 0=0 ";
if ($sql_order) $sql .= " order by ".$sql_order;
else $sql .= " order by tb_acc.res_dt desc ";
if ($olds>=0 and $line>0) $sql .= " limit $olds , $line";
$data = array();
$rs = db_query($sql);
$i=0;
while ($rows = db_fetch_array($rs)) {
$data[$i] = $rows;
// 호텔,호텔코드,룸타입등은 한 예약코드안에 복수개일수 있으므로,
// 위 자료를 다시 fetch
$data_hotel = toad_res_hotel_roomdate(" and a.res_no='$rows[res_no]' ");
for( $j=0 ; $j<count($data_hotel) ; $j++){
$arr_hotel = array(
'hotel_nm'=>$data_hotel[$j]['hotel_nm'],
'hotel_code'=>$data_hotel[$j]['hotel_code'],
'room_type'=>$data_hotel[$j]['room_type']
);
$data[$i]['hotel_info'][] = $arr_hotel;
}
// 자동정산숙박비 계정생성
//account_hotel_check($rows['res_no'],$rows['hseq']);
// 정산정보 가져오기
//$row_t = account_hotel($rows['res_no'],$rows['hseq']);
//$data[$i][]=$row_t['total'];
$i++;
}
return $data;
}
*/
//
// 호텔 정산 정보
//
// params res_no : 예약번호
// params seq :
public function acc_hotel_date($res_no,$seq="")
{
$table = "TB_ACCOUNT_HOTEL";
$class_HotelReserv = new HotelReserv();
$data['list'] = array(); // 정산 항목 목록
$data['calu'] = array(); // 정산 누적금액 및 이익율 계산
$revn_tot = 0; // 수익계
$exes_tot = 0; // 비용계
$exes_common = 0; // 출금지출(2014-07-29)
$exes_vat = 0; // 부가세 지출(2014-07-29)
if (!$res_no) return $data;
$sql = "select * from {$table} where res_no='{$res_no}' ";
if ($seq) $sql .= " and seq='{$seq}' ";
$sql .= " order by acc_dt , ins_dt ";
$rs = db_query($sql);
// 각 항목 조회
for ($i=0 ; $row=db_fetch_array($rs) ; $i++) {
$revn_tot += $row['revn_price']; // 수익계
$exes_tot += $row['exes_price']; // 비용계,총지출 = 출금지출 + 부가세 지출(2014-07-29)
if($row['exes_cd']=="LGXE003") // 부가세 지출(2014-07-29)
{
$exes_vat += $row['exes_price'];
}
else // 출금 지출(2014-07-29)
{
$exes_common += $row['exes_price'];
}
$data['list'][] = $row;
}
if (!$seq) {
$arr = array();
$comm_tot = 0; // 수수료 수익
$incom_tot =0; // 총수익
$price_total = 0; // 판매액
if ( count($data['list']) > 0 ) // 정산정보가 있음
{
// 판매(입금)액
$arr_t = $class_HotelReserv->res_hotel_master(" and res_no='{$res_no}' ");
$price_total = $arr_t[0]['price_total'];
// 알선수수료 = 판매액 - 비용계(항공,지상비등등)
$comm_tot = $price_total - $exes_tot;
// 총수익 = 알선수수료 + 수익계(COM,수수료 등)
$incom_tot = $comm_tot + $revn_tot;
}
$arr['revn_tot'] = $revn_tot;
$arr['exes_tot'] = $exes_tot; // 총지출 = 출금지출 + 부가세 지출
$arr['exes_common'] = $exes_common; // 출금 지출(2014-07-29)
$arr['exes_vat'] = $exes_vat; // 부가세 지출(2014-07-29)
$arr['comm_tot'] = $comm_tot; // 알선수수료
$arr['incom_tot'] = $incom_tot; // 총수익
$data['calu'] = $arr;
}
// return
return $data;
}
public function getBenepiaAccData($res_no){
$sql = "SELECT * FROM TB_ACCOUNT_CUSTOMER WHERE res_no = '{$res_no}' ";
// p($sql);
$res = db_query($sql);
$returnData = array();
while($row = db_fetch_array($res)){
array_push($returnData, $row);
}
return $returnData;
}
}