|
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 : |
<?
/******************************************************************************
*
* 행사,여행예약,정산 클래스
*
******************************************************************************/
class GoodResEvCal{
// 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;
}
// 여행상품 정산 정보(예약기준)
//
// params res_no : 예약번호
// params seq :
public function acc_good_date_res($res_no,$seq="")
{
$table = "TB_ACCOUNT_GOOD";
$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 *,
IF(tosu_price>0 OR toas_price>0,CONCAT(tosu_nm,toas_nm),'') AS revn_nm,
IF(tosu_price>0 OR toas_price>0,tosu_price+toas_price,0) AS revn_price,
IF(toln_price>0 OR toai_price>0 OR toet_price>0 ,CONCAT(toln_nm,toai_nm,toet_nm),'') AS exes_nm,
IF(toln_price>0 OR toai_price>0 OR toet_price>0,toln_price+toai_price+toet_price,0) AS exes_price,
if(toln_price>0,'TOLN',if(toai_price>0,'TOAI',if(toet_price>0,'TOET',if(tosu_price>0,'TOSU',if(toas_price>0,'TOAS',''))))) as type
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']; // 기타수익
switch($row['type']) {
case "TOLN":
$row['type_str'] = "지상비";
break;
case "TOAI":
$row['type_str'] = "항공비";
break;
case "TOET":
$row['type_str'] = "기타경비";
break;
case "TOSU":
$row['type_str'] = "기타수익";
break;
case "TOAS":
$row['type_str'] = "항공수익";
break;
}
$data['list'][] = $row;
}
if (!$seq)
{
$arr = array();
$comm_tot = 0; // 수수료 수익
$incom_tot =0; // 총수익
if ( count($data['list']) > 0 ) // 정산정보가 있음
{
// 판매(입금)액
$arr_t = res_good_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;
}
// 여행상품 정산 정보(행사기준)
//
// params d_uid : 행사 id
// params seq :
// params dpidx : 행사계획서 id(TB_GOOD_DATE_PLAN)
public function acc_good_date($d_uid="",$seq="",$dpidx="")
{
$table = "TB_ACCOUNT_GOOD";
$data['list'] = array(); // 정산 항목 목록
$data['calu'] = array(); // 정산 누적금액 및 이익율 계산
$revn_tot = 0; // 수익계
$exes_tot = 0; // 비용계
$sql = "select *,
IF(tosu_price>0 OR toas_price>0,CONCAT(tosu_nm,toas_nm),'') AS revn_nm,
IF(tosu_price>0 OR toas_price>0,tosu_price+toas_price,0) AS revn_price,
IF(toln_price>0 OR toai_price>0 OR toet_price>0 ,CONCAT(toln_nm,toai_nm,toet_nm),'') AS exes_nm,
IF(toln_price>0 OR toai_price>0 OR toet_price>0,toln_price+toai_price+toet_price,0) AS exes_price,
if(toln_price>0,'TOLN',if(toai_price>0,'TOAI',if(toet_price>0,'TOET',if(tosu_price>0,'TOSU',if(toas_price>0,'TOAS',''))))) as type
from {$table} where 0=0 ";
if ($d_uid) $sql .= " and d_uid='{$d_uid}' ";
if ($seq) $sql .= " and seq='{$seq}' ";
if ($dpidx) $sql .= " and dpidx='{$dpidx}' ";
$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']; // 기타수익
if($row['artx_cd']) $data_artx = base_codec("ACC","ARTX",$row['artx_cd']);// 요금구분
$row['artx_nm'] = $data_artx[0]['cd_nm'];
switch($row['type']) {
case "TOLN":
$row['type_str'] = "지상비";
break;
case "TOAI":
$row['type_str'] = "항공비";
break;
case "TOET":
$row['type_str'] = "기타경비";
break;
case "TOSU":
$row['type_str'] = "기타수익";
break;
case "TOAS":
$row['type_str'] = "항공수익";
break;
}
$data['list'][] = $row;
}
if (!$seq and !$dpidx)
{
$arr = array();
$comm_tot = 0; // 수수료 수익
$incom_tot =0; // 총수익
$tot_pay = 0; // 총입금액
$price_total = 0; // 판매금액
if ( count($data['list']) > 0 ) // 정산정보가 있음
{
// 판매(입금)액
$arr_t = res_good_master(" and ev_uid='{$d_uid}' and res_stat_cd='C' ");
for($i=0 ; $i<count($arr_t) ; $i++)
{
$price_total += $arr_t[$i]['price_total'];
}
// 알선수수료 = 판매금액 - 비용계(2014-07-29)
$comm_tot = $price_total - $exes_tot;
// 총수익 = 알선수수료 + 수익계(COM,수수료 등)
$incom_tot = $comm_tot + $revn_tot;
}
$arr['revn_tot'] = $revn_tot; // 비용계
$arr['exes_tot'] = $exes_tot; // 수익계
$arr['comm_tot'] = $comm_tot; // 알선수수료(수수료수익)
$arr['incom_tot'] = $incom_tot; // 총수익
$data['calu'] = $arr;
}
// return
return $data;
}
// 정산서 수지계산 정보
//
// @params : ev_uid 행사번호
// @params : res_no 예약번호
public function to_paper_acc_report($ev_uid="",$res_no="",$tab=""){
$data = array();
// 정산정보 where
$sql_where_account="";
if($ev_uid) $sql_where_account = "acct2.d_uid = res.ev_uid";
if($res_no) $sql_where_account = "acct2.res_no = res.res_no";
// 입금정보 where
$sql_where_customer="";
if($ev_uid) $sql_where_customer = "AND TAC.res_no IN(SELECT res_no FROM TB_RES_GOOD WHERE 1=1 AND ev_uid='{$ev_uid}' AND res_stat_cd<>'D')";
if($res_no) $sql_where_customer = "AND TAC.res_no=res.res_no";
if(!$sql_where_account) return $data;
if($tab == 'L') {
// 행사에 관련된 예약정보
$sql= "select * , (total_sales_mny-total_exes_mny) as acct_mny,(total_bank_mny-total_exes_mny) as acct_bank_mny
from (
select hnm, rnm, room_date, room_date_out, admin_nm, SUM(sales_total) as total_sales_mny /*총판매액*/,
(select SUM(bank_plus)-SUM(bank_minus) from TB_ACCOUNT_CUSTOMER TAC where TAC.delflag='N' {$sql_where_customer}) as total_bank_mny /*총입금액*/,
(select SUM(toln_price+toai_price) from TB_ACCOUNT_GOOD acct2 where {$sql_where_account} ) as total_exes_mny /*총지출금=지상비+항공비*/,
(select SUM(tosu_price) from TB_ACCOUNT_GOOD acct2 where {$sql_where_account} ) as total_su_mny /*기타수익*/,
(select SUM(toet_price) from TB_ACCOUNT_GOOD acct2 where {$sql_where_account} ) as total_etc_mny /*기타경비*/,
(select SUM(toas_price) from TB_ACCOUNT_GOOD acct2 where {$sql_where_account} ) as total_air_mny /*항공수익*/,
(select count(1) from TB_RES_HOTEL_PERSON person where person.res_no = res.res_no) as total_member /*총인원*/
from TB_RES_HOTEL res where 1=1 %s
) t ";
} else {
// 행사에 관련된 예약정보
$sql= "select * , (total_sales_mny-total_exes_mny) as acct_mny,(total_bank_mny-total_exes_mny) as acct_bank_mny
from (
select g_title, ev_title, ev_start, ev_end, adult_price, child_price, baby_price, admin_nm, SUM(sales_total+agent_total) as total_sales_mny /*총판매액*/,
(select SUM(bank_plus)-SUM(bank_minus) from TB_ACCOUNT_CUSTOMER TAC where TAC.delflag='N' {$sql_where_customer}) as total_bank_mny /*총입금액*/,
(select SUM(toln_price+toai_price) from TB_ACCOUNT_GOOD acct2 where {$sql_where_account} ) as total_exes_mny /*총지출금=지상비+항공비*/,
(select SUM(tosu_price) from TB_ACCOUNT_GOOD acct2 where {$sql_where_account} ) as total_su_mny /*기타수익*/,
(select SUM(toet_price) from TB_ACCOUNT_GOOD acct2 where {$sql_where_account} ) as total_etc_mny /*기타경비*/,
(select SUM(toas_price) from TB_ACCOUNT_GOOD acct2 where {$sql_where_account} ) as total_air_mny /*항공수익*/,
(select count(1) from TB_RES_GOOD_PERSON person where person.ev_uid = res.ev_uid) as total_member /*총인원*/
from TB_RES_GOOD res where 1=1 %s /* and order_state='F' and res_stat_cd='C' */
) t ";
}
$sql_where="";
if($ev_uid) $sql_where .= " and ev_uid='{$ev_uid}' and res_stat_cd<>'D' ";
if($res_no) $sql_where .= " and res_no='{$res_no}' ";
$sql = sprintf($sql,$sql_where);
$db_total = db_fetch($sql);
$data['(총)인원'] = $db_total['total_member'];
//$data['총판매액'] = $db_total['total_sales_mny'];
$data['총판매액'] = $db_total['total_sales_mny'];
$data['총입금액'] = $db_total['total_bank_mny'];
//$data['알선수익'] = $db_total['acct_bank_mny']; // =총판매액-총지출액
$data['알선수익'] = $db_total['acct_mny']; // =총판매액-총지출액
$data['총지출액'] = $db_total['total_exes_mny'];// =지상비내역+항공비내역
$data['알선수수료'] = round($data['알선수익']/1.1); // =알선수익/1.1
$data['부가가치세'] = $data['알선수익']-$data['알선수수료'];
$data['항공수익'] = $db_total['total_air_mny']; // 항공수익
$data['기타수익'] = $db_total['total_su_mny'];
$data['기타경비'] = $db_total['total_etc_mny'];
$data['총비용'] = $db_total['total_exes_mny']+$db_total['total_etc_mny'];//=지상비+항공비+기타경비(New)
$data['수수료수익'] = $db_total['total_sales_mny']-($db_total['total_exes_mny']+$db_total['total_etc_mny']);//=판매금액-비용계(지상비+항공비+기타경비)(New)
$data['총수익'] = $data['알선수수료']+$data['항공수익']+$data['기타수익']-$data['기타경비']; // 알선수수료+항공수익+기타수익+기타경비
$data['총수익/1인수익'] = @round($data['총수익']/$data['(총)인원'],2);
$data['알선수익/1인수익'] = @round($data['알선수익']/$data['(총)인원'],2);
//$data['행사수익율'] = ($data['알선수익']/$data['총수익/1인수익'])/$data['총판매액'];
//$data['행사수익율'] = @round((($data['알선수수료']+$data['항공수익'])/$data['총판매액'])*100,2);
$data['행사수익율'] = @round((($data['알선수수료']+$data['항공수익'])/$data['총판매액'])*100,2);
//$data['총수익율'] = round($data['총수익']/$data['총판매액'],2);
$data['총수익율'] = @round(($data['총수익']/$data['총판매액'])*100,2);
/***********************************
* 판매내역서
************************************/
$data_good_res = res_good_master(" /*and order_state='F' and res_stat_cd='C' */ ".$sql_where);
if(is_array($data_good_res)){ foreach($data_good_res as $v){
$row_good_res = res_good_master_replace($v);
if ($row_good_res['adult_price']>0 and $row_good_res['adult_inwon']>0) $data['판매내역서'][] = array('generation_str'=>'성인','price'=>$row_good_res['adult_price'],'inwon'=>$row_good_res['adult_inwon'],'agent_corp_nm'=>$row_good_res['agent_corp_nm']);
if ($row_good_res['child_price']>0 and $row_good_res['child_inwon']>0) $data['판매내역서'][] = array('generation_str'=>'소인','price'=>$row_good_res['child_price'],'inwon'=>$row_good_res['child_inwon'],'agent_corp_nm'=>$row_good_res['agent_corp_nm']);
if ($row_good_res['baby_price']>0 and $row_good_res['baby_inwon']>0) $data['판매내역서'][] = array('generation_str'=>'유아','price'=>$row_good_res['baby_price'],'inwon'=>$row_good_res['baby_inwon'],'agent_corp_nm'=>$row_good_res['agent_corp_nm']);
if ($row_good_res['infant_price']>0 and $row_good_res['infant_inwon']>0) $data['판매내역서'][] = array('generation_str'=>'영아','price'=>$row_good_res['infant_price'],'inwon'=>$row_good_res['infant_inwon'],'agent_corp_nm'=>$row_good_res['agent_corp_nm']);
if(is_array($row_good_res['person_arr'])){
foreach($row_good_res['person_arr'] as $v){
if ($v['person_price']>0 and $v['person_inwon']>0) $data['판매내역서'][] = array('generation_str'=>$v['ptitle'],'price'=>$v['person_price'],'inwon'=>$v['person_inwon'],'agent_corp_nm'=>$v['agent_corp_nm']);
}}
}}
/***********************************
* 입금내역서
************************************/
$sql ="
select * , date_format(acc_dt,'%Y.%m.%d') as ins_dt2, (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
from TB_ACCOUNT_CUSTOMER
where res_no in (select res_no from TB_RES_GOOD where 1=1 {$sql_where}) and delflag='N' and (bank_plus>0 OR bank_minus>0) ";
$res = db_query($sql);
$data['입금내역서'] = db_fetch_all($res);
// 계약가,입금예정,통장출금 합계 (to_hotel_res.lib.php>res_account_info 참조)
$sql = "select res_no,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)) - 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 in (select res_no from TB_RES_GOOD where 1=1 {$sql_where}) and delflag='N' ";
$row = db_fetch($sql);
// 개별 합계계산
$benefit_will = $row['tot_input_will'] - $row['tot_price_cost']; // 예정수익금 (= 입금예정금계 - 계약금계)
$pre_money = $row['tot_input_will'] - $row['tot_bank']; // 미입금 (= 입금예정금계 - 통장입출금계)
$pre_money = $pre_money - $row['tot_point_divide'];// 미입금 = 미입금 + 적립금 결제 (2017-01-13.허종)
$pay_money = $row['tot_bank']; // 입금( = 통장출금계)
$pay_bank = $row['pay_bank']; // 통장입금(2014-07-28)
$pay_card = $row['pay_card']; // 카드입금(2014-07-28)
// 전자결제 수수료 계산(2017-10-25.박지정차장)
$r = db_fetch("select sum(toet_price) as card_pay_tax from TB_ACCOUNT_GOOD where res_no='{$row[res_no]}' and toet_cd='TOET003' ");
$pre_money = $pre_money - intval($r['card_pay_tax']);
if($pre_money>=0) { // 미수금
$data['입금내역서.미수금'] = $pre_money;
$data['입금내역서.과입금'] = 0;
}else{
$data['입금내역서.미수금'] = 0;
$data['입금내역서.과입금'] = abs($pre_money);
}
if($ev_uid) $data_acc = $this->acc_good_date($ev_uid); // 행사정산내역
/***********************************
* 지상비내역서
************************************/
$data['지상비내역'] = array();
for($i=0 ; $i<count($data_acc['list']) ; $i++){
$r = $data_acc['list'][$i];
// 지상비
if ($r['toln_price']>0) {
$data['지상비내역'][] = array("toln_nm"=>$r['toln_nm'],
"agent_nm"=>$r['agent_nm'],
"toln_1price"=>$r['toln_1price'],
"person_num"=>$r['person_num'],
"toln_price"=>$r['toln_price'],
"cont"=>$r['cont']
);
}
}
/***********************************
* 항공비내역서
************************************/
$data['항공비내역'] = array();
for($i=0 ; $i<count($data_acc['list']) ; $i++){
$r = $data_acc['list'][$i];
// 지상비
if ($r['toai_price']>0) {
$r['plane_cd2'] = ($r['plane_cd']) ? "[".$r['plane_cd']."]":"";
$data['항공비내역'][] = array("toai_nm"=>$r['toai_nm'],
"agent_nm"=>$r['agent_nm'],
"toai_1price_net"=>$r['toai_1price_net'],
"toai_1price_tax"=>$r['toai_1price_tax'],
"person_num"=>$r['person_num'],
"toai_price"=>$r['toai_price'],
"plane_cd"=>$r['plane_cd'],
"plane_cd2"=>$r['plane_cd2'],
"artx_cd"=>$r['artx_cd'],
"artx_nm"=>$r['artx_nm'],
"cont"=>$r['cont']
);
}
}
/***********************************
* 기타경비내역서
************************************/
$data['기타경비내역'] = array();
for($i=0 ; $i<count($data_acc['list']) ; $i++){
$r = $data_acc['list'][$i];
// 지상비
if ($r['toet_price']>0) {
$data['기타경비내역'][] = array("toet_nm"=>$r['toet_nm'],
"agent_nm"=>$r['agent_nm'],
"toet_1price"=>$r['toet_1price'],
"person_num"=>$r['person_num'],
"toet_price"=>$r['toet_price'],
"cont"=>$r['cont']
);
}
}
/***********************************
* 기타수익내역서
************************************/
$data['기타수익내역'] = array();
for($i=0 ; $i<count($data_acc['list']) ; $i++){
$r = $data_acc['list'][$i];
// 지상비
if ($r['tosu_price']>0) {
$data['기타수익내역'][] = array("tosu_nm"=>$r['tosu_nm'],
"agent_nm"=>$r['agent_nm'],
"tosu_1price"=>$r['tosu_1price'],
"person_num"=>$r['person_num'],
"tosu_price"=>$r['tosu_price'],
"cont"=>$r['cont']
);
}
}
/***********************************
* 항공수익내역서
************************************/
$data['항공수익내역'] = array();
for($i=0 ; $i<count($data_acc['list']) ; $i++){
$r = $data_acc['list'][$i];
// 지상비
if ($r['toas_price']>0) {
$data['항공수익내역'][] = array("toas_nm"=>$r['toas_nm'],
"agent_nm"=>$r['agent_nm'],
"toas_1price"=>$r['toas_1price'],
"person_num"=>$r['person_num'],
"toas_price"=>$r['toas_price'],
"cont"=>$r['cont']
);
}
}
return $data;
}
}
?>