";
$webserver = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 210.94.135.16)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = ORCL) (SID = ORCL)))";
$conn = oci_connect('yk', 'z8088', $webserver, 'UTF8');
if (!$conn):
echo "No Connection " . print_r(oci_error());
exit;
endif;
return $conn;
}
//----------------------------------------------------------------------------
//POS SERVER 접속
//return $conn
function posserver_connect()
{
//return;
//echo "포스 접속시도";
$posserver = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 210.94.135.29)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = ORCL) (SID = ORCL)))";
$conn = oci_connect('book', 'easy2002', $posserver, 'UTF8');
// $conn = oci_pconnect('book', 'easy2002', $posserver, 'UTF8');
if (!$conn):
echo "No Connection " . print_r(oci_error());
exit;
endif;
//oci_commit($conn); // 명시적으로 커밋
return $conn;
}
function get_delivery_price($od_id, $overseas, $zonevalue)
{//조건부 배송료
/*
* 해외배송일경우 해당 파라메터 값을 받는다
* $overseas == "overseas"
$zonevalue =="oo1"
*
* 배송비
배송비결제 : 1만원이상인 도서는 무료
방송대교재, 정기간행물인 경우에는 도서금액 상관없이 배송비 2,500원 부과
단, 일반도서 1만원이상 포함인 경우에는 무료
1만원 미만인 도서는 : 해당 도서 포함 만원이상 구매시 무료
'방통대 교재
''국정교과서: 072, 073
''방통대교재: 202, 203, 207, 208, 209, 210, 211, 212
': 201,213,214,215,216,217,218,219,220,223
''문구플러스: 077
'' 정기간행물
maejang_code = "510" or maejang_code = "511" or maejang_code = "517" or maejang_code = "518" or maejang_code = "519"
or maejang_code = "521" or maejang_code = "525" or maejang_code = "531" or maejang_code = "535"
or maejang_code = "540"
or maejang_code = "550"
or maejang_code = "555" or maejang_code = "557"
or maejang_code = "560" or maejang_code = "570" or _
*/
// $conn = webserver_connect(); //20240712 오라클 주석처리
$orderprice = 0; //도서구매합계액
$allcount = 0; //도서구매권수
$delivprice = 0; //배송료
$textbook = 0; //방통대교재 권수
$textbookprice = 0; //방통대교재 가격
$textbook1 = 0; //문구 권수
$textbookprice1 = 0; //문구 가격
//해당 주문번호의 카트정보를 조회한다
/* // and c.ct_select = '1'
$query="
select
c.od_id , c.ct_id , c.ct_price , c.ct_qty , i.it_id , i.it_name , i.order_key , i.pos_key
from g5_shop_cart c
left join g5_shop_item i on c.it_id = i.it_id
where c.od_id ='$od_id' and c.ct_select = '1'
order by c.ct_id asc;";
*/
$query = "
select
c.od_id , c.ct_id , c.ct_price , c.ct_qty , i.it_id , i.it_name , i.order_key , i.pos_key
from g5_shop_cart c
left join g5_shop_item i on c.it_id = i.it_id
where c.od_id ='$od_id' and c.ct_select = '1'
order by c.ct_id asc;";
//echo "cartQuery :: $query
";
$mysqlStmt = sql_query($query);
$mycount = 0;
while ($data = sql_fetch_array($mysqlStmt)) {
$tmpTotalprice = $data['ct_qty'] * $data['ct_price'];
$tmpAllcount = $data['ct_qty'];
/*
//20240712 오라클 주석처리
//oracle webdb 매장정보를 가져온다
$query = "SELECT SUBSTR(GWANRINO,1,3) AS MAEJANG FROM BOOK_GWANRI B WHERE POSGWANRINO='" . trim($data['pos_key']) . "' ";
//echo "maeJangQuery :: $query
";
$oracleStmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($oracleStmt);
if ($row['MAEJANG']) {
$maejang_code = $row['MAEJANG'];
}
oci_free_statement($oracleStmt);
// 오라클에서 로그아웃
//oci_close($conn);
*
*
//20240712 오라클 주석처리
*/
$maejang_code = trim(substr($data['order_key'],0,3));
/*
echo "order_key-->{$data['order_key']}
";
echo "maejang_code-->{$maejang_code}
";
*/
//echo "maejang_code::$maejang_code
";
if ($maejang_code == "072"
|| $maejang_code == "073"
|| $maejang_code == "202"
|| $maejang_code == "203"
|| $maejang_code == "207"
|| $maejang_code == "208"
|| $maejang_code == "209"
|| $maejang_code == "210"
|| $maejang_code == "213"
|| $maejang_code == "214"
|| $maejang_code == "215"
|| $maejang_code == "216"
|| $maejang_code == "217"
|| $maejang_code == "218"
|| $maejang_code == "219"
|| $maejang_code == "220"
|| $maejang_code == "211"
|| $maejang_code == "223"
|| $maejang_code == "212"
) {//해당조건에 맞는 도서가 있으면 반영
//처리
$tmptextbook = $data['ct_qty'];
$tmptextbookprice = $data['ct_qty'] * $data['ct_price'];
} else {//없으면 미반영
$tmptextbook = 0;
$tmptextbookprice = 0;
}
if ($maejang_code == "510"
|| $maejang_code == "511"
|| $maejang_code == "517"
|| $maejang_code == "518"
|| $maejang_code == "519"
|| $maejang_code == "521"
|| $maejang_code == "525"
|| $maejang_code == "531"
|| $maejang_code == "535"
|| $maejang_code == "540"
|| $maejang_code == "550"
|| $maejang_code == "555"
|| $maejang_code == "557"
|| $maejang_code == "560"
|| $maejang_code == "570"
|| $maejang_code == "077"
) {
//처리
$tmptextbook1 = $data['ct_qty'];
$tmptextbookprice1 = $data['ct_qty'] * $data['ct_price'];
} else {//없으면 미반영
$tmptextbook1 = 0;
$tmptextbookprice1 = 0;
}
$orderprice = $orderprice + $tmpTotalprice + $tmpTotalprice1;
$allcount = $allcount + $tmpAllcount;
$textbook = $textbook + $tmptextbook;
$textbook1 = $textbook1 + $tmptextbook1;
$textbookprice = $textbookprice + $tmptextbookprice;
$textbookprice1 = $textbookprice1 + $tmptextbookprice1;
$mycount++;
}
//mysql_close();
//배송비 계산
//배송비 계산
//배송비 계산
if ($overseas == "overseas") { //'해외배송비 계산 --------------------------------------
/* //20240712 오라클 주석처리
*
$booknum_value = $allcount;
//$zone_value = "oo1";
$cbaesongbi = 0;
switch ($zonevalue) {
case "oo1":
$field = "REGION1";
break;
case "oo2":
$field = "REGION2";
break;
case "oo3":
$field = "REGION3";
break;
case "oo4":
$field = "REGION4";
break;
}
$zoneQuery = "SELECT $field FROM BAESONGBI WHERE BOOKNUM='$booknum_value'";
//echo "zoneQuery :: $zoneQuery
";
$oracleStmt = oracle_query($conn, $zoneQuery);
$row = oci_fetch_assoc($oracleStmt);
if ($row[$field]) {
$delivprice = ((int)($row[$field]) * 1.035);
//echo "zoneQueryDelivPrice :: $delivprice
";
}
oci_free_statement($oracleStmt);
// 오라클에서 로그아웃
//oci_close($conn);
*
* //20240712 오라클 주석처리
*/
} else { // '국내 배송비 계산 --------------------------------------
switch ($vip) { // 회원 등급에 따른 배송비 설정 (정책설정 후 반영토록 ,,,,)
case "2":
$baseorderprice = 0; //'20100304 17시무료배송실시 0=배송비기준금액
if (($orderprice - $textbookprice) >= $baseorderprice) $delivprice = 0;
if (($orderprice - $textbookprice) < $baseorderprice) {
if ($textbook >= 1) {
//$delivprice = 2500;
$delivprice = 3000; //20210915
} else {
$delivprice = 0;
}
}
break;
case "1":
$baseorderprice = 0; //'20100304 17시무료배송실시 0=배송비기준금액
if (($orderprice - $textbookprice) >= $baseorderprice) $delivprice = 0;
if (($orderprice - $textbookprice) < $baseorderprice) {
if ($textbook >= 1) {
//$delivprice = 2500;
$delivprice = 3000; //20210915
} else {
$delivprice = 0;
}
}
break;
case "0":
//$baseorderprice = 10000; //'20100304 17시무료배송실시 0=배송비기준금액
$baseorderprice = 20000; //20210915 //'20100304 17시무료배송실시 0=배송비기준금액
if (($orderprice - $textbookprice - $textbookprice1) >= $baseorderprice) $delivprice = 1111;
if (($orderprice - $textbookprice - $textbookprice1) < $baseorderprice) {
if ($textbook >= 1 || $textbook1 >= 1) {
$delivprice = 2222; //'일만원 미만, 방통대 교재 있음
} else {
// $delivprice = 3333;
}
}
break;
default: //그외 모든 고객
//$baseorderprice = 10000; //'20100304 17시무료배송실시
$baseorderprice = 20000; //20210915 //'20100304 17시무료배송실시
if (($orderprice - $textbookprice - $textbookprice1) >= $baseorderprice) $delivprice = 0;
if (($orderprice - $textbookprice - $textbookprice1) < $baseorderprice) {
if ($textbook >= 1 || $textbook >= 1) {
//$delivprice = 2500; //'일만원 미만, 방통대 교재 있음
$delivprice = 3000; //20210915 //'일만원 미만, 방통대 교재 있음
} else {
//'$delivprice = 2000;
}
}
/*
echo "default
";
echo "baseorderprice::$baseorderprice
";
echo "orderprice::$orderprice
";
echo "textbookprice::$textbookprice
";
echo "textbookprice1::$textbookprice1
";
echo "textbook::$textbook
";
echo "textbook1::$textbook1
";
*/
break;
}
//' 방통대 교과서, 배송료 bylee 2010-08-27
/*
if (($textbook + $textbook1) < $allcount && ($orderprice - $textbookprice - $textbookprice1) < 10000) {
$delivprice = 2000;
} elseif (($textbook + $textbook1) == $allcount) {
$delivprice = 2500;
} else {
$delivprice = 0;
}
*/
//20210915
if (($textbook + $textbook1) < $allcount && ($orderprice - $textbookprice - $textbookprice1) < 20000) {
$delivprice = 3000;
} elseif (($textbook + $textbook1) == $allcount) {
$delivprice = 3000;
} else {
$delivprice = 0;
}
}
// 오라클에서 로그아웃
//oci_close($conn); //20240712 오라클 주석처리
//echo "delivprice :: $delivprice
";
//exit();
return $delivprice;
}
function pos_getSoldout($pos_key)
{//POS 쪽 품절,절판 확인
$posserver = posserver_connect(); // 포스서버 컨넥션
// OUT_GB IN ('1' , '2')
$posQuery = "SELECT OUT_GB FROM BOOKCD WHERE BK_CD='$pos_key' ";
//echo "oracleQuery :: $posQuery
";
$stmt = oracle_query($posserver, $posQuery);
while ($row = oci_fetch_assoc($stmt)) {
$OUT_GB = $row['OUT_GB'];
}
return $OUT_GB;
oci_free_statement($stmt);
}
function web_update_chongseoname($pos_key)
{//20170421 영카트 총서명 업데이트
//echo "this
";
$conn = posserver_connect();
$query = "SELECT CHONG_BOOKNAME FROM book_gwanri WHERE posgwanrino='$pos_key' ";
//echo "$query
";
$stmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
if ($row['CHONG_BOOKNAME']) {
//수량 업데이트
$query = "update g5_shop_item set chongseoname='" . $row['CHONG_BOOKNAME'] . "' where pos_key='$pos_key' ";
//echo "$query
";
if (!sql_query($query)) {
echo sql_error_info();
}
}
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
$row = null;
$stmt = null;
}
function web_update_stock($it_id)
{//영카트 주문을 위한 최종주문서에서의 수량 9999 로 업데이트
//수량 업데이트
$query = "update g5_shop_item set it_stock_qty='9999' where it_id='$it_id'; ";
//echo "web_update_stock_Query :: $query
";
if (!sql_query($query)) {
$processFlag = false;
echo sql_error_info();
//exit();
}
}
function web_item_stock($it_id)
{//영카트 재고 확인
//수량 업데이트
$query = "select it_stock_qty from g5_shop_item where it_id='$it_id'";
$stmt = sql_query($query);
$data = sql_fetch_array($stmt);
$stock_qty = $data['it_stock_qty'];
$data = null;
$stmt = null;
return $stock_qty;
}
function pos_stock($it_id)
{ //주문시 pos 재고 리턴
$conn = posserver_connect();
$processFlag = true;
$query = "select pos_key from g5_shop_item where it_id='$it_id'; ";
$stmt = sql_query($query);
$row = sql_fetch_array($stmt);
$pos_key = trim($row['pos_key']);
$row = null;
$stmt = null;
/*
$query="SELECT SUM(JG_QTY) JG_QTY_SUM FROM BKSBJG
WHERE BK_CD = '$pos_key'
AND LOC_CD < '5999' ";
*/
//20190307
$query = "SELECT SUM(JG_QTY) JG_QTY_SUM FROM BKSBJG
WHERE BK_CD = '$pos_key'
AND NOT(LOC_CD BETWEEN '6000' AND '9000') ";
//echo "oracleQuery :: $query
";
$stmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
$pos_qty = ($row['JG_QTY_SUM']) ? $row['JG_QTY_SUM'] : 0;
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
return $pos_qty;
}
function pos_update_stock($it_id)
{//주문시 mysql 재고 수량 업데이트
$conn = posserver_connect();
$processFlag = true;
$query = "select pos_key from g5_shop_item where it_id='$it_id'; ";
$stmt = sql_query($query);
$row = sql_fetch_array($stmt);
$pos_key = trim($row['pos_key']);
$row = null;
$stmt = null;
$query = "SELECT SUM(JG_QTY) JG_QTY_SUM FROM BKSBJG
WHERE BK_CD = '$pos_key'
AND LOC_CD < '5999' ";
//echo "oracleQuery :: $query
";
$stmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
if ($row['JG_QTY_SUM']) {
//수량 업데이트
$query = "update g5_shop_item set it_stock_qty='" . $row['JG_QTY_SUM'] . "' where it_id='$it_id'; ";
//echo "updateQuery :: $query
";
if (!sql_query($query)) {
$processFlag = false;
echo sql_error_info();
//exit();
}
}
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
return $processFlag;
}
function pos_maeip_price($pos_key)
{ //매당상품의 매입가 조회
//$conn = posserver_connect();
$conn = webserver_connect(); //20181226
$query = "SELECT IN_DANGA FROM BOOKCD WHERE BK_CD='$pos_key' ";
/*
echo "pos_query :: $query
";
exit();
*/
$stmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
return $price = ($row[IN_DANGA]) ? $row[IN_DANGA] : 0;
}
function web_get_isbn($isbn)
{//isbn명 검색시 데이터 없으면 조회 후 업데이트
$conn = webserver_connect();
$query = "SELECT POSGWANRINO , ISBN FROM BOOK_GWANRI WHERE ISBN='$isbn' ";
//echo "oracle_lib > web_get_isbn($isbn) > select :: $query
";
$stmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
if ($row['ISBN']) {
$query = "update g5_shop_item set isbn='" . $row['ISBN'] . "' where pos_key='" . $row['POSGWANRINO'] . "' ";
//echo "oracle_lib > web_get_isbn($isbn) update :: $query
";
if (!sql_query($query)) {
echo sql_error_info();
exit();
}
}
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
}
function web_get_chongseoname($chongseoname)
{//총서명 검색시 데이터 없으면 조회 후 업데이트
$conn = webserver_connect();
$query = "SELECT POSGWANRINO , CHONG_BOOKNAME FROM BOOK_GWANRI WHERE CHONG_BOOKNAME LIKE'%" . $chongseoname . "%' ";
//echo "oracle_lib > web_get_chongseoname($chongseoname) select :: $query
";
$stmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
if ($row['ISBN']) {
$query = "update g5_shop_item set isbn='" . $row['ISBN'] . "' where pos_key='" . $row['POSGWANRINO'] . "' ";
//echo "oracle_lib > web_get_chongseoname($chongseoname) update :: $query
";
if (!sql_query($query)) {
echo sql_error_info();
exit();
}
}
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
}
//-------------------------------
//todo : 왼쪽 문자열
//-------------------------------
function LeftStr3($str, $ACount)
{
return substr($str, 0, $ACount);
}
//-------------------------------
//todo : 중간 문자열
//-------------------------------
function MidStr3($str, $AStart, $ACount)
{
return substr($str, $AStart - 1, $ACount);
}
//-------------------------------
//todo : 오른쪽 문자열
//-------------------------------
function RightStr3($str, $ACount)
{
return substr($str, strlen($str) - $ACount, $ACount);
}
//---------------------------------------------------------------
//도서 상세내용 리턴함수 ////////////////////////////////////////
//---------------------------------------------------------------
function posoracle_book_gwanri3($bk_cd, $loc = "")
{
if ($bk_cd):
//----------------------------------------------------------------------------
// global 영역
//----------------------------------------------------------------------------
/***
* $sql = " SELECT a.gwanrino, a.control, a.bookname, a.chong_bookname, a.pubname, a.gweoncha " .
* " ,sfMarc070(a.control) authname, authname shotname, a.price, a.gweoncha, a.sale, c.tel, " .
* " (a.price) - ((a.price)*(a.sale/100)) as v_sale, " .
* " NVL(a.balilja, '' ) balilja, a.tukga, b.chorok, c.loc, a.isbn, " .
* " substr( a.posgwanrino, 1, 12 ) pos_isbn, " .
* " a.posgwanrino img_name,a.posgwanrino, a.ipgoilja, c.loc_cd, " .
* " rtrim(nvl(a.page,'')) as page, " .
* " rtrim(nvl(a.pansize,'')) as pansize, " .
* " c.loc_cd , a.bookgweoncha , NVL(a.del_flag,'N') del_flag " .
* " FROM maejang c, bkchorok b, book_gwanri a " .
* " WHERE a.posgwanrino='" . $bk_cd . "' " .
* " AND a.posgwanrino=b.bk_cd(+) " .
* " AND SUBSTR(a.gwanrino,1,3)=c.code(+) $subQuery ";
* */
$sql = "select * from g5_shop_item WHERE pos_key ='{$bk_cd}' ";
$stmt = sql_query($sql);
// $fetch = array();
$row = sql_fetch_array($stmt);
// oci_free_statement($stmt);
//--------------------------------------------
// 필요변수 정리
//--------------------------------------------
/**
*
* [pubname] => 수문사
* [htsahang] => p. A4판:29
* [maejang] => 자연과학부(B2)
* [imagepath] => /bookimg/2021/02/9788930450560.jpg
* [content] =>
* [gweoncha] =>
* [balilja] => 20210225
* [maejang_c] => 448
**/
$tmp = explode(',', $row['it_origin']);
$ret['pubname'] = trim($tmp[0]);
$ret['htsahang'] = $row['it_brand'];
$ret['maejang'] = $row['it_model'];
$ret['imagepath'] = $row['it_img1'];
$ret['content'] = '';
$ret['gweoncha'] = '';
$ret['balilja'] = $row['balilja'];
$ret['maejang_c'] = substr($row['order_key'],0,3);
// print_r2($ret);
// exit;
return $ret;
endif;
}
function weboracle_book_gwanri3($bk_cd, $loc = "")
{
$webconnect = webserver_connect();
$posconnect = posserver_connect();
if ($loc) {
$subQuery = " AND c.loc LIKE '%($loc)%' ";
}
if ($bk_cd):
//----------------------------------------------------------------------------
// global 영역
//----------------------------------------------------------------------------
$sql = " SELECT a.gwanrino, a.control, a.bookname, a.chong_bookname, a.pubname, a.gweoncha " .
" ,sfMarc070(a.control) authname, authname shotname, a.price, a.gweoncha, a.sale, c.tel, " .
" (a.price) - ((a.price)*(a.sale/100)) as v_sale, " .
" NVL(a.balilja, '' ) balilja, a.tukga, b.chorok, c.loc, a.isbn, " .
" substr( a.posgwanrino, 1, 12 ) pos_isbn, " .
" a.posgwanrino img_name,a.posgwanrino, a.ipgoilja, c.loc_cd, " .
" rtrim(nvl(a.page,'')) as page, " .
" rtrim(nvl(a.pansize,'')) as pansize, " .
" c.loc_cd , a.bookgweoncha , NVL(a.del_flag,'N') del_flag " .
" FROM maejang c, bkchorok b, book_gwanri a " .
" WHERE a.posgwanrino='" . $bk_cd . "' " .
" AND a.posgwanrino=b.bk_cd(+) " .
" AND SUBSTR(a.gwanrino,1,3)=c.code(+) $subQuery ";
// echo "webOracle1------------>".$sql."
";
if ($_SERVER['REMOTE_ADDR'] == "61.82.205.71") {
//echo "webOracle1------------>".$sql."
";
//exit();
}
$stmt = oracle_query($webconnect, $sql);
$fetch = array();
while ($row = oci_fetch_assoc($stmt)):
$fetch[] = $row;
endwhile;
oci_free_statement($stmt);
//--------------------------------------------
// 필요변수 정리
//--------------------------------------------
$maejang_c = MidStr3($fetch[0]['GWANRINO'], 1, 3);
$bookname = $fetch[0]['BOOKNAME']; //책이름
$authname = $fetch[0]['AUTHNAME']; //저자
if (!$authname): $authname = $fetch[0]['SHOTNAME']; endif;
$price = $fetch[0]['PRICE']; //정가
$strCtl = $fetch[0]['CONTROL'];
$v_control = LeftStr3($fetch[0]['CONTROL'], 3);
$bookType = $v_control; //재고에서 사용
$ipgo_date = $fetch[0]['IPGOILJA']; //이미지 출력시 사용
$img_name = $fetch[0]['IMG_NAME'];
$chorok = $fetch[0]['CHOROK'];
$maejang = $fetch[0]['LOC'];
/*
echo "ora--maejang_c ---->$maejang_c
";
echo "ora--maejang ---->$maejang
";
*/
$balilja = $fetch[0]['BALILJA'];
$gweoncha = $fetch[0]['GWEONCHA'];
$critIpgoDate = "20010619";
if (!$ipgo_date):
if ($balilja >= "20050101"):
if (date("m") < 10):
$mm = "0" & date("m");
else:
$mm = date("m");
endif;
if (date("d") < 10):
$dd = "0" . date("d");
else:
$dd = date("d");
endif;
$ipgo_date = date("Y") . $mm . $dd;
else:
$ipgo_date = "20010101";
endif;
endif;
if ($chorok):
$chorok = str_replace(chr(13) . chr(10), "
", $chorok);
$chorok = str_replace(" ", " ", $chorok);
$chorok = str_replace("font color", "font color", $chorok);
endif;
$chorokrs_value = $chorok;
$pubDate = $BalIlja; //재고에서 사용
$chbalilja = LeftStr3($fetch[0]['BALILJA'], 4);
//-----------------------------------------
// 형태사항, 출판사항, 매장위치, 전화번호
//-----------------------------------------
$Tag300Exists = false;
$sql = "SELECT * FROM marc WHERE control='" . $strCtl . "' and (tag='250' or tag='260' or tag='300')";
//echo "webOracle2------------>".$sql."
";
$stmt = oracle_query($webconnect, $sql);
while ($Rs1 = oci_fetch_assoc($stmt)):
$sData = $Rs1['MARC'];
$sData = RightStr3($sData, strlen($sData) - 2);
if ($Rs1['TAG'] == "250"): //판차
$pancha = $sData;
$pancha = str_replace("%", "", str_replace("$a", " ", $pancha));
$pancha = trim($pancha);
endif;
//--------------------
//출판사항
//--------------------
if ($Rs1['TAG'] == "260"):
$pub_sahang = $sData;
$pub_sahang = str_replace("$a", " ", $pub_sahang);
$pub_sahang = str_replace(":$b", "; ", $pub_sahang);
$pub_sahang = str_replace("$b", "", $pub_sahang);
if (MidStr3($v_control, 1, 3) != "SPE"):
$pub_sahang = str_replace(",$c", ", 발행일:", $pub_sahang);
$pub_sahang = str_replace("%", " ", $pub_sahang);
$pub_sahang = trim($pub_sahang);
if ($fetch[0]['BALILJA'] != ""):
//pub_sahang = pub_sahang & "/" & mid(Rs("balilja"),5,2) & "/" & right(Rs("balilja"),2)
//pub_sahang = mid(pub_sahang,1,len(pub_sahang) - 4) & mid(Rs("balilja"),1,4) & "/" & mid(Rs("balilja"),5,2) & "/" & right(Rs("balilja"),2)
endif;
endif;
endif;
//-------------------
// 형태사항
//-------------------
if ($Rs1['TAG'] == "300"): //형태사항
$Tag300Exists = true;
$ht_sahang = $sData;
$ht_sahang = str_replace("$a", " ", $ht_sahang);
$ht_sahang = str_replace(":$b", " ", $ht_sahang);
$pos = strpos($ht_sahang, ";$c");
if ($pos > 0):
$pos = $pos + 3;
$strSize = "";
for ($i = $pos; $i < strlen($ht_sahang); $i++):
if (MidStr3($ht_sahang, $i, 1) >= "0" && MidStr3($ht_sahang, $i, 1) <= "9"):
$strSize = $strSize + MidStr3($ht_sahang, $i, 1);
else:
$i = strlen($ht_sahang) + 1;
endif;
endfor;
if ($strSize != ''):
$size = (double)$strSize;
if ($size >= 14 && $size <= 16):
$ht_sahang = str_replace(";$c", " A6판:", $ht_sahang);
elseif ($size >= 17 && $size <= 18):
$ht_sahang = str_replace(";$c", " B6판:", $ht_sahang);
elseif ($size >= 19 && $size <= 20):
$ht_sahang = str_replace(";$c", " 46판:", $ht_sahang);
elseif ($size == 21):
$ht_sahang = str_replace(";$c", " A5판:", $ht_sahang);
elseif ($size >= 22 && $size <= 23):
$ht_sahang = str_replace(";$c", " 국판:", $ht_sahang);
elseif ($size >= 24 && $size <= 25):
$ht_sahang = str_replace(";$c", " B5판:", $ht_sahang);
elseif ($size >= 26 && $size <= 27):
$ht_sahang = str_replace(";$c", " 46배판:", $ht_sahang);
elseif ($size >= 28 && $size <= 29):
$ht_sahang = str_replace(";$c", " A4판:", $ht_sahang);
elseif ($size >= 30 && $size <= 32):
$ht_sahang = str_replace(";$c", " 국배판:", $ht_sahang);
elseif ($size >= 33 && $size <= 36):
$ht_sahang = str_replace(";$c", " B4판:", $ht_sahang);
elseif ($size >= 37 && $size <= 39):
$ht_sahang = str_replace(";$c", " 46배배판:", $ht_sahang);
else:
$ht_sahang = str_replace(";$c", " ", $ht_sahang);
endif;
endif;
endif;
endif;
$ht_sahang = str_replace("+$e", " 딸림자료:", $ht_sahang);
$ht_sahang = str_replace("%", " ", $ht_sahang);
$ht_sahang = trim($ht_sahang);
//2003.09.20 형태사항 값이 없을 경우 Display 하지 않음
if ($strSize == ""):
$ht_sahang = "";
endif;
endwhile;
oci_free_statement($stmt);
//형태사항,출판사항,매장위치등등
//위에서 값이없다면
//아래에서 출력
if (!$Tag300Exists || !$strSize):
if ($fetch[0]['PAGE'] != "p."):
if (MidStr3($fetch[0]['PAGE'], 1, 1) != "p"):
$ht_sahang = "p." . $fetch[0]['PAGE'];
else:
$ht_sahang = $fetch[0];
endif;
endif;
if ($fetch[0]['PANSIZE'] != "cm"):
$strSize = 0;
$ht_sahang_add = $fetch[0]['PANSIZE'];
$strSize = preg_replace('/[^0-9]*/s', '', $ht_sahang_add);
if ((int)$strSize):
$size = (int)($strSize);
if (14 <= $size && $size <= 16):
$strPansize = " A6판:";
elseif (17 <= $size && $size <= 18):
$strPansize = " B6판:";
elseif ($size >= 19 && $size <= 20):
$strPansize = " 46판:";
elseif ($size == 21):
$strPansize = " A5판:";
elseif (22 <= $size && $size <= 23):
$strPansize = " 국판:";
elseif (24 <= $size && $size <= 25):
$strPansize = " B5판:";
elseif (26 <= $size && $size <= 27):
$strPansize = " 46배판:";
elseif (28 <= $size && $size <= 29):
$strPansize = " A4판:";
elseif (30 <= $size && $size <= 32):
$strPansize = " 국배판:";
elseif (33 <= $size && $size <= 36):
$strPansize = " B4판:";
elseif (37 <= $size && $size <= 39):
$strPansize = " 46배배판:";
else:
$strPansize = " ";
endif;
endif;
$ht_sahang = $ht_sahang . " " . $strPansize . $fetch[0]['PANSIZE'];
endif;
endif;
/*
if ($pub_sahang==""):
$pub_sahang = $fetch[0]['PUBNAME'];
if ($fetch[0]['BALILJA'] != "" && TRIM(MidStr2($v_control,1,3)) != "SPE"):
$pub_sahang = $pub_sahang . ", 발행일:" . LeftStr2($fetch[0]['BALILJA'],4) . "/" . MidStr2($fetch[0]['BALILJA'],5,2) . "/" . RightStr2($fetch[0]['BALILJA'],2);
endif;
endif;
if (trim(MidStr2($v_control,1,3)) == "SPE"):
$pub_sahang = $pub_sahang . ", 발행일:" . LeftStr2($fetch[0]['BALILJA'],4) . "/" . MidStr2($fetch[0]['BALILJA'],5,2) . "/" . RightStr2($fetch[0]['BALILJA'],2);
endif;
*/
if ($pub_sahang == ""):
$pub_sahang = $fetch[0]['PUBNAME'];
if ($fetch[0]['BALILJA'] != "" && TRIM(MidStr3($v_control, 1, 3)) != "SPE"):
$pub_sahang = $pub_sahang;
endif;
endif;
if (trim(MidStr3($v_control, 1, 3)) == "SPE"):
$pub_sahang = $pub_sahang;
endif;
//---------------------------------------------------------------------
//책표지 이미지
//---------------------------------------------------------------------
$strIsbn = "yk";
$sql = "SELECT ISBN FROM marc_isbn WHERE control='" . $strCtl . "'";
//echo $sql."
";
$stmt = oracle_query($webconnect, $sql);
while ($Rs1 = oci_fetch_assoc($stmt)):
$strisbn = LeftStr3($Rs1['ISBN'], 9);
endwhile;
oci_free_statement($stmt);
if ($balilja >= "20010101" && $balilja != ""):
$BookImageFile = "/bookimg/" . LeftStr3($fetch[0]['BALILJA'], 4) . "/" . MidStr3($fetch[0]['BALILJA'], 5, 2) . "/" . $img_name . ".jpg";
$BookImageFile2 = "/bookimg-1/" . LeftStr3($fetch[0]['BALILJA'], 4) . "/" . MidStr3($fetch[0]['BALILJA'], 5, 2) . "/" . $img_name . "-1.jpg";
elseif ($ipgo_date >= $critIpgoDate):
$BookImageFile = "/bookimg20010611/" . $img_name . ".jpg";
$BookImageFile2 = "/bookimg20010611/" . $img_name . "-1.jpg";
else:
$BookImageFile = "";
$BookImageFile2 = "";
endif;
//--------------------------------------------------
//todo 출력할려면 print_r($fetch); 찍어보기바람
//--------------------------------------------------
$ret['pubname'] = $pub_sahang;
$ret['htsahang'] = $ht_sahang;
$ret['maejang'] = $maejang;
$ret['imagepath'] = $BookImageFile;
$ret['content'] = $chorokrs_value;
$ret['gweoncha'] = $gweoncha;
$ret['balilja'] = $balilja;
$ret['maejang_c'] = $maejang_c;
if ($_SERVER['REMOTE_ADDR'] == "61.82.205.71") {
/*
echo "return------------>
";
print_r($ret);
echo "return------------>
";
//exit();
*/
}
return $ret;
endif;
}
/*
function web_get_order_result_point_update_rtn($od_id)
{//주문변경시 카트 구매적립금 업데이트 후 리턴
$conn = webserver_connect();
//카트내 bk_cd 확인
$query="select c.ct_price , i.pos_key from g5_shop_cart c left join g5_shop_item i on c.it_id = i.it_id
where c.od_id='$od_id'
order by i.pos_key asc;
";
$stmt = sql_query($query);
while($row = sql_fetch_array($stmt))
{
//적립율 가져오기
$sale_percent = JukLib2($row['pos_key']);
$upPoint =
}
return $cartPointSum;
}
*/
//--------------------------------------------------------------------------
// 오라클 쿼리
//--------------------------------------------------------------------------
function oracle_query($conn, $query)
{
if (!$conn): echo "오라클 접속을 확인하세요";
exit; endif;
$stmt = oci_parse($conn, $query);
oci_execute($stmt);
return $stmt;
}
function get_mem_no()
{//20170315 포스쪽으로 등록할 MEM_NO 를 생성한다
$conn = posserver_connect();
$query = "SELECT MAX(MEM_NO)+1 NEXT_MEM_NO
FROM MEMBER
WHERE SUBSTR(MEM_NO , 0,9) LIKE '77196820%' ";
//echo "oracleQuery 1:: $query
";
$stmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
$returnMemNo = $row['NEXT_MEM_NO'];
//echo "returnMemNo:: $returnMemNo
";
// 오라클 접속 닫기
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
return $returnMemNo;
}
function get_cardid($char = 'RE')
{
$sql = "select max(mb_no) as fmax from g5_member";
if (!$q = sql_query($sql)):
echo sql_error_info();
exit;
endif;
$row = sql_fetch_array($q);
if ($char == 'RE'): $char = ''; endif;
$rt = $char . trim($row['fmax'] . time());
if (strlen($rt) > 16):
$rt = substr($rt, 1, 16);
endif;
//;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
// pos 서버에 중복된 cardid 가
// 있는지 조회하여 중복된 값이
// 존재한다면 재귀함수 호출한다.
//;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
if (is_pos_carid($rt)):
get_cardid('OK');
return '';
endif;
return $rt;
}
//----------------------------------------------------------
// 할인율
//----------------------------------------------------------
function getDisRate3($YK_CD)
{
$conn = posserver_connect();
if($_SERVER['REMOTE_ADDR'] == "118.38.0.137"){
//echo "YK_CD-->{$YK_CD}
";
}
if (trim($YK_CD) != ""):
$sSQL = "SELECT in_rate,bk_part,out_danga FROM bookcd WHERE bk_cd ='" . $YK_CD . "'; ";
if($_SERVER['REMOTE_ADDR'] == "118.38.0.137"){
//echo "sSQL-->{$sSQL}
";
}
$stmt = oracle_query($conn, $sSQL);
while ($row = oci_fetch_assoc($stmt)):
$in_rate = (int)$row['IN_RATE'];
$price = (int)$row['OUT_DANGA'];
$part = $row['BK_PART'];
endwhile;
endif;
if ($part = "0120" ||
$part = "0121" ||
$part = "0122" ||
$Part = "0123" ||
$part = "0124" ||
$part = "0130" ||
$part = "0131" ||
$part = "0132" ||
$part = "0133" ||
$part = "0134" ||
$part = "0110"):
if ($in_rate <= 75):
$Jrate = 10;
else:
$jrate = 0;
endif;
else:
if ($in_rate <= 70):
$Jrate = 10;
else:
$jrate = 0;
endif;
endif;
// 오라클 접속 닫기
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
return $Jrate;
}
//------------------------------------------------------------
// 적립금 % 테이지로 리턴
// 도서할인가 * (return / 100) = 실제할인금액
//------------------------------------------------------------
function JukLib3($YK_CD)
{
$conn = posserver_connect();
if (trim($YK_CD) != ""):
$sSQL = "SELECT in_rate FROM bookcd WHERE bk_cd = '" . $YK_CD . "'";
$stmt = oracle_query($conn, $sSQL);
while ($row = oci_fetch_assoc($stmt)):
$in_rate = (int)$row['IN_RATE'];
endwhile;
if ($in_rate < 80):
$Jrate = 5;
elseif ($in_rate >= 80 && $in_rate < 85):
$Jrate = 3;
elseif ($in_rate >= 85 && $in_rate < 90):
$Jrate = 2;
else:
$Jrate = 0;
endif;
endif;
return $Jrate;
}
function is_pos_carid($cardid)
{
$conn = posserver_connect();
$query = "select mem_no from member where mem_no='$cardid'";
$stmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
if (trim($row['MEM_NO'])):
return true;
endif;
//while($row = oci_fetch_assoc($stmt)):
// print_r($row);
//endwhile;
// 오라클 접속 닫기
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
return false;
}
function posserver_save_member($cardid)
{
global $_POST;
$mb_id = trim($_POST['mb_id']);
$mb_password = trim($_POST['mb_password']);
$mb_password_re = trim($_POST['mb_password_re']);
$mb_name = trim($_POST['mb_name']);
$mb_jumin = trim($_POST['jumin']);
$mb_nick = trim($_POST['mb_nick']);
$mb_email = trim($_POST['mb_email']);
$mb_sex = isset($_POST['mb_sex']) ? trim($_POST['mb_sex']) : "";
$mb_birth = isset($_POST['mb_birth']) ? trim($_POST['mb_birth']) : "";
$mb_homepage = isset($_POST['mb_homepage']) ? trim($_POST['mb_homepage']) : "";
$mb_tel = isset($_POST['mb_tel']) ? trim($_POST['mb_tel']) : "";
$mb_hp = isset($_POST['mb_hp']) ? trim($_POST['mb_hp']) : "";
$mb_zip1 = isset($_POST['mb_zip']) ? substr(trim($_POST['mb_zip']), 0, 3) : "";
$mb_zip2 = isset($_POST['mb_zip']) ? substr(trim($_POST['mb_zip']), 3) : "";
$mb_addr1 = isset($_POST['mb_addr1']) ? trim($_POST['mb_addr1']) : "";
$mb_addr2 = isset($_POST['mb_addr2']) ? trim($_POST['mb_addr2']) : "";
$mb_addr3 = isset($_POST['mb_addr3']) ? trim($_POST['mb_addr3']) : "";
$mb_addr_jibeon = isset($_POST['mb_addr_jibeon']) ? trim($_POST['mb_addr_jibeon']) : "";
$mb_signature = isset($_POST['mb_signature']) ? trim($_POST['mb_signature']) : "";
$mb_profile = isset($_POST['mb_profile']) ? trim($_POST['mb_profile']) : "";
$mb_recommend = isset($_POST['mb_recommend']) ? trim($_POST['mb_recommend']) : "";
$mb_mailling = isset($_POST['mb_mailling']) ? trim($_POST['mb_mailling']) : "";
$mb_sms = isset($_POST['mb_sms']) ? trim($_POST['mb_sms']) : "";
//;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
// POS 서버 회원가입 SQL //////////////////
//-----------------------------------------
// 중요한 필드 정보 ///////////////////////
//-----------------------------------------
///////////// GUME_MER = 누게 (누적포인트)
///////////// NU_MER = 유효 (유효포인트)
//;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
//-------------------------------------------------
//mysql서버 테스트시에만 기존회원삭제
//-------------------------------------------------
/*
$sql="delete from g5_member where mb_id='$mb_id'";
if (!sql_query($sql)):
echo sql_error_info();
exit;
endif;
*/
//-------------------------------------------------
//POS서버 테스트시에만 기존회원삭제
//-------------------------------------------------
/*
$sql="delete from member where ID='$mb_id'";
$conn = posserver_connect();
$stmt = oracle_query($conn,$sql);
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
*/
$sql = "INSERT INTO MEMBER (
MEM_NO,NAME,JUMIN_NO,SOLAR,BIRTHDAY,TEL_NO,
MAIL_NO,ADDR,ADDR2,SEX_GB,FIRST_DATE,
STT_DATE,VALID_NUM,JOB_GB,JOB_NM,
SCHOOL_GB,SCHOOL_NM,COLL_GB,NU_AMT,
NU_MER,PUM_MER,FNS_MER,GUME_MER,
LEND_MER,EXCH_MER,SAVE_AMT,PM_REMK,
EMP_NO,UPD_EMP,UPD_DATE,JAB_YN,E_NAME,
E_MAIL,ID,HANDPHONE,VIP_GB )
VALUES
(
:MEM_NO,:NAME,:JUMIN_NO,:SOLAR,:BIRTHDAY,:TEL_NO,
:MAIL_NO,:ADDR1,:ADDR2,:SEX_GB,:FIRST_DATE,
:STT_DATE,:VALID_NUM,:JOB_GB,:JOB_NM,
:SCHOOL_GB,:SCHOOL_NM,:COLL_GB,:NU_AMT1,
:NU_MER1,:PUM_MER1,:FNS_MER1,:GUME_MER1,
:LEND_MER1,:EXCH_MER1,:SAVE_AMT,:PM_REMK,
:EMP_NO,:UPD_EMP,:UPD_DATE,:JAB_YN,:E_NAME,
:E_MAIL,:ID,:HANDPHONE,:VIP_GB)";
$sql = str_replace(':MEM_NO', "'" . $cardid . "'", $sql);
$sql = str_replace(':NAME', "'" . $mb_name . "'", $sql);
$sql = str_replace(':JUMIN_NO', "'$mb_jumin'", $sql);
$sql = str_replace(':SOLAR', "''", $sql);
$sql = str_replace(':BIRTHDAY', "'" . $mb_birth . "'", $sql);
$sql = str_replace(':TEL_NO', "'" . $mb_tel . "'", $sql);
$zipcode = $mb_zip1 . $mb_zip2;
$sql = str_replace(':MAIL_NO', "'" . $zipcode . "'", $sql);
$address = $mb_addr1 . $mb_addr2;
$sql = str_replace(':ADDR1', "'" . $address . "'", $sql);
$sql = str_replace(':ADDR2', "'" . $mb_addr3 . "'", $sql);
$sql = str_replace(':ADDR2', "'" . $mb_addr3 . "'", $sql);
$sql = str_replace(':SEX_GB', "'" . $mb_sex . "'", $sql);
$register_date = date("Ymd", time());
$sql = str_replace(':FIRST_DATE', "'" . $register_date . "'", $sql);
$sql = str_replace(':STT_DATE', "'" . $register_date . "'", $sql);
$sql = str_replace(':VALID_NUM', 0, $sql);
$sql = str_replace(':JOB_GB', "''", $sql);
$sql = str_replace(':JOB_NM', "''", $sql);
$sql = str_replace(':SCHOOL_GB', "''", $sql);
$sql = str_replace(':COLL_GB', "''", $sql);
$sql = str_replace(':NU_AMT1', 0, $sql);
$sql = str_replace(':NU_MER1', 0, $sql);
$sql = str_replace(':PUM_MER1', "''", $sql);
$sql = str_replace(':FNS_MER1', "''", $sql);
$sql = str_replace(':GUME_MER1', 0, $sql);
$sql = str_replace(':LEND_MER1', "''", $sql);
$sql = str_replace(':EXCH_MER1', "''", $sql);
$sql = str_replace(':SAVE_AMT', 0, $sql);
$sql = str_replace(':PM_REMK', "''", $sql);
$sql = str_replace(':EMP_NO', "''", $sql);
//20124 인터넷회원 가입 코드인듯
$sql = str_replace(':UPD_EMP', "'20124'", $sql);
$gaip_date = date("YmdHis");
$sql = str_replace(':UPD_DATE', "'" . $gaip_date . "'", $sql);
$sql = str_replace(':JAB_YN', "''", $sql);
$sql = str_replace(':E_NAME', "''", $sql);
$sql = str_replace(':E_MAIL', "'" . $mb_email . "'", $sql);
$sql = str_replace(':ID', "'" . $mb_id . "'", $sql);
$sql = str_replace(':HANDPHONE', "'" . $mb_hp . "'", $sql);
$sql = str_replace(':VIP_GB', "''", $sql);
$sql = str_replace(':SCHOOL_NM', "''", $sql);
//echo "oraclePosQuery2 :: $sql
";
//exit();
//회원가입 Query 보냄
$conn = posserver_connect();
$stmt = oracle_query($conn, $sql);
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
//MYSQL 회원테이블에 cardid 정보저장
$sql = "update g5_member set cardid='$cardid' where mb_id='$mb_id'";
if (!sql_query($sql)):
echo sql_error_info();
exit;
endif;
}
function update_cart_status_flag($od_id, $flag)
{//주문상태가 변경 시 카트의 상태도 함께 변경한다 20170314
$query = "update g5_shop_cart set ct_status='$flag' where od_id='$od_id' ";
if (!sql_query($query)) {
return false;
}
return true;
}
function update_member_web_pos_point2($od_id)
{//WEB , POS 적립금 데이터를 업데이트 한다 20170314
$returnVal = "complete";
$conn = posserver_connect();
//해당 주문번호의 카트 정보 조회
$query = "select ct_id , mb_id , od_id , it_id , ct_price , ct_point , ct_qty from
g5_shop_cart where od_id='$od_id' order by ct_id asc ";
//echo "mysql 1 :: $query
";
$stmt = sql_query($query);
$rowcount = 0;
while ($data = sql_fetch_array($stmt)) {
if ($rowcount == 0) $mb_id = $data['mb_id'];
$sum_point = $sum_point + ((int)$data['ct_point'] * (int)$data['ct_qty']);
}
//echo "sum_point :: $sum_point
";
$data = null;
$stmt = null;
//echo "sum_point :: $sum_point
";
//exit();
//주문자 회원조회
$query = "select mb_id , mb_name from g5_member mb_id='$mb_id' ";
$stmt = sql_query($query);
$data = sql_fetch_array($stmt);
if (!$data['mb_id'] && !$data['mb_name']) {
return $returnVal = "fail";
} else {
$where = " AND NAME='" . $data['mb_name'] . "' ";
$query = "SELECT COUNT(ID) CNT FROM MEMBER WHERE ID='$mb_id' $where ";
$stmt2 = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt2);
if ($row['CNT'] >= 1) {
//포스 적립금 업데이트
$query = "UPDATE MEMBER SET NU_MER=NU_MER+$sum_point WHERE ID='$mb_id' $where ";
$stmt3 = oracle_query($conn, $query);
oci_free_statement($stmt3);
//포스쪽 업데이트 된 포인트 조회
$query = "SELECT NU_MER FROM MEMBER WHERE ID='$mb_id' $where ";
$stmt4 = oracle_query($conn, $query);
$row2 = oci_fetch_assoc($stmt4);
//MYSQL 회원포인트 업데이트
$query = "update g5_member set
mb_point2='" . $row2['NU_MER'] . "'
, mb_10='Y'
where mb_id='$mb_id' ";
if (!sql_query($query)) {
return $returnVal = "fail";
}
oci_free_statement($stmt4);
} else {
$returnVal = "fail";
}
oci_free_statement($stmt2);
}
// 오라클에서 로그아웃
oci_close($conn);
$data = null;
$stmt = null;
return $returnVal;
}
function order_accumulate_point($od_id)
{//20170424 해당주문건에 대한 포인트 리턴
$returnPoint = 0;
$query = "select
mb_id
,od_name
,od_email
,od_tel
,od_hp
,od_cart_count
,od_cart_price
, od_send_cost
, od_send_cost2
, od_receipt_price
, od_cancel_price
, od_receipt_point
, od_bank_account
, od_receipt_time
, od_status
, od_settle_case
, od_tax_mny
, od_vat_mny
, od_purchase_price
, ponit_flag
from g5_shop_order where od_id='$od_id' and ponit_flag='Y' ";
$stmt = sql_query($query);
$rowcount = 0;
while ($data = sql_fetch_array($stmt)) {
$mb_id = $data['mb_id'];
$mb_name = $data['od_name'];
$od_cart_price = $data['od_cart_price']; //주문총액
$od_receipt_price = $data['od_receipt_price']; //실결제금액
$od_receipt_point = $data['od_receipt_point']; //결제시 사용 적립금
$mb_point_flag = $data['ponit_flag'];
}
$data = null;
$stmt = null;
if (!$od_receipt_price) {
$returnPoint = 0;
} else {
//적립금 계산 :: 실결제금액 * 0.05
if ($od_receipt_price <= 0) { //실결제금액이 없는 경우 ( 미입금시 )
$returnPoint = 0;
} else {
//2022-06-08 : 수정 , 총주문금액(배송비포함)의 할인율이 아니라서 , 카트금액(도서할인반영된 금액)으로 처리 변경
//$returnPoint = (int)$od_receipt_price * 0.05;
$returnPoint = (int)$od_cart_price* 0.05;
}
}
return $returnPoint;
}
function update_order_status_point($od_id)
{ //20170421
//주문관리에서 주문상태 '완료' 시 적립금 처리(로그 포함)
$returnVal = "complete";
$conn = posserver_connect();
$query = "select
mb_id
,od_name
,od_email
,od_tel
,od_hp
,od_cart_count
,od_cart_price
, od_send_cost
, od_send_cost2
, od_receipt_price
, od_cancel_price
, od_receipt_point
, od_bank_account
, od_receipt_time
, od_status
, od_settle_case
, od_tax_mny
, od_vat_mny
, od_purchase_price
, ponit_flag
from g5_shop_order where od_id='$od_id' ";
//echo "1 :: $query \n";
$stmt = sql_query($query);
$rowcount = 0;
while ($data = sql_fetch_array($stmt)) {
$mb_id = $data['mb_id'];
$mb_name = $data['od_name'];
$od_cart_price = $data['od_cart_price']; //주문총액
$od_receipt_price = $data['od_receipt_price']; //실결제금액
$od_receipt_point = $data['od_receipt_point']; //결제시 사용 적립금
$mb_point_flag = $data['ponit_flag'];
}
$data = null;
$stmt = null;
//적립금 적립 여부 확인
/*
$query = "select mb_10 from g5_member where mb_id='$mb_id' ";
//echo "2 :: $query \n";
$stmt = sql_query($query);
$data = sql_fetch_array($stmt);
$mb_point_flag = $data['mb_10']; //적립금 적립 여부 (Y/N)
$data = null;
$stmt = null;
*/
//이미 적립이 되었다면
if ($mb_point_flag == "Y") {
return $returnVal = "fail";
}
if (!$mb_id && !$mb_name) {
return $returnVal = "fail";
} else {
//적립금 계산 :: 실결제금액 * 0.05
if ($od_receipt_price <= 0) { //실결제금액이 없는 경우 ( 미입금시 )
return $returnVal = "fail";
} else {
$save_point = (int)$od_receipt_price * 0.05;
}
//포스 쪽 회원 조회
//$query="SELECT COUNT(ID) CNT FROM MEMBER WHERE ID='$mb_id' AND NAME='$mb_name' ";
$query = "SELECT COUNT(ID) CNT FROM MEMBER WHERE ID='$mb_id' ";
//echo "3 :: $query \n";
$stmt2 = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt2);
if ($row['CNT'] >= 1) {
//포스 적립금 업데이트
//$query="UPDATE MEMBER SET NU_MER=NU_MER+$save_point WHERE ID='$mb_id' AND NAME='$mb_name' ";
$query = "UPDATE MEMBER SET NU_MER=NU_MER+$save_point WHERE ID='$mb_id' ";
//echo "4 :: $query \n";
$stmt3 = oracle_query($conn, $query);
oci_free_statement($stmt3);
//포스 쪽 업데이트 된 적립금조회
//$query="SELECT NU_MER FROM MEMBER WHERE ID='$mb_id' AND NAME='$mb_name' ";
$query = "SELECT NU_MER FROM MEMBER WHERE ID='$mb_id' ";
//echo "5 :: $query \n";
$stmt4 = oracle_query($conn, $query);
$row2 = oci_fetch_assoc($stmt4);
//웹 회원 적립금 업데이트
//$query = "update g5_member set mb_point2='".$row2['NU_MER']."' , mb_10='Y' where mb_id='$mb_id' ";
$query = "update g5_member set mb_point2='" . $row2['NU_MER'] . "' where mb_id='$mb_id' ";
//echo "6 :: $query \n";
if (!sql_query($query)) {
return $returnVal = "fail";
}
//주문 적립금 반영 상태 업데이트
$query = "update g5_shop_order set ponit_flag='Y' where od_id='$od_id' ";
//echo "7 :: $query \n";
if (!sql_query($query)) {
return $returnVal = "fail";
}
oci_free_statement($stmt4);
//이전까지의 적립금 합계 내역을 불러온다
$query = "select po_mb_point from g5_point where (1) and ( (mb_id = '$mb_id') ) order by po_id desc limit 1";
//echo "8 :: $query \n";
$stmt4 = sql_query($query);
while ($row3 = sql_fetch_array($stmt4)) {
$po_mb_point = $row3['po_mb_point'];
}
$po_mb_point = $po_mb_point + $save_point; //최종포인트 합
$po_content = "주문번호 $od_id 의 (배송완료) 적립포인트 ";
//적립금 로그 저장
$query = " insert into g5_point set
mb_id='$mb_id'
,po_datetime=now()
,po_content='$po_content'
,po_point='$save_point'
,po_use_point=0
,po_expired='9999-12-31'
,po_mb_point='$po_mb_point'
,po_rel_table='@delivery'
,po_rel_id='$mb_id'
,po_rel_action='" . date('Y-m-d') . "' ";
//echo "9 :: $query \n";
sql_query($query);
} else {
$returnVal = "fail";
}
oci_free_statement($stmt2);
}
// 오라클에서 로그아웃
oci_close($conn);
$data = null;
$stmt = null;
$row = null;
$row2 = null;
$row3 = null;
$stmt2 = null;
$stmt3 = null;
$stmt4 = null;
return $returnVal;
}
function update_member_web_pos_point($od_id)
{//WEB , POS 적립금 데이터를 업데이트 한다 20170314
$returnVal = "complete";
$conn = posserver_connect();
//해당 주문번호의 카트 정보 조회
$query = "select ct_id , mb_id , od_id , it_id , ct_price , ct_point
, ct_qty from g5_shop_cart where od_id='$od_id' order by ct_id asc ";
//echo "mysql 1 :: $query
";
$stmt = sql_query($query);
$rowcount = 0;
while ($data = sql_fetch_array($stmt)) {
if ($rowcount == 0) $mb_id = $data['mb_id'];
$sum_point = $sum_point + ((int)$data['ct_point'] * (int)$data['ct_qty']);
}
//echo "sum_point :: $sum_point
";
$data = null;
$stmt = null;
//echo "sum_point :: $sum_point
";
//exit();
//주문자 회원조회
$query = "select mb_id , mb_name from g5_member where mb_10 <> '' and mb_id='$mb_id' ";
//$query="select mb_id , mb_name , jumin from g5_member where mb_id='$mb_id' ";
//echo "mysql 2 :: $query
";
$stmt = sql_query($query);
$data = sql_fetch_array($stmt);
//if(!$data['mb_id'] && !$data['jumin']) //20170419 바꿈
if (!$data['mb_id'] && !$data['mb_name']) {
return $returnVal = "fail";
} else {
/*
$where =" AND NAME='".$data['mb_name']."'
AND JUMIN_NO='".$data['jumin']."' ";
*/
$where = " AND NAME='" . $data['mb_name'] . "' ";
$query = "SELECT COUNT(ID) CNT FROM MEMBER WHERE ID='$mb_id' $where ";
//echo "oracleSql 1 :: $query
";
$stmt2 = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt2);
if ($row['CNT'] >= 1) {
//포스 적립금 업데이트
$query = "UPDATE MEMBER SET NU_MER=NU_MER+$sum_point WHERE ID='$mb_id' $where ";
//echo "oracleSql 2 :: $query
";
$stmt3 = oracle_query($conn, $query);
oci_free_statement($stmt3);
//포스쪽 업데이트 된 포인트 조회
$query = "SELECT NU_MER FROM MEMBER WHERE ID='$mb_id' $where ";
$stmt4 = oracle_query($conn, $query);
$row2 = oci_fetch_assoc($stmt4);
//MYSQL 회원포인트 업데이트
//$query = "update g5_member set mb_point2='".$row2['NU_MER']."' where mb_id='$mb_id' ";
$query = "update g5_member set
mb_point2='" . $row2['NU_MER'] . "'
, mb_10='Y'
where mb_id='$mb_id' ";
if (!sql_query($query)) {
return $returnVal = "fail";
}
oci_free_statement($stmt4);
} else {
$returnVal = "fail";
}
oci_free_statement($stmt2);
}
// 오라클에서 로그아웃
oci_close($conn);
$data = null;
$stmt = null;
return $returnVal;
}
///////////// GUME_MER = 누게 (누적포인트)
///////////// NU_MER = 유효 (유효포인트)
function client_oracle_get_point($name, $jumin)
{
$conn = posserver_connect();
$sql = "select GUME_MER,NU_MER from member where NAME='$name' AND JUMIN_NO='$jumin'";
$stmt = oracle_query($conn, $sql);
while ($row = oci_fetch_assoc($stmt)):
$ret['GUME_MER'] = $row['GUME_MER'];
$ret['NU_MER'] = $row['NU_MER'];
endwhile;
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
return $ret;
}
///////////// GUME_MER = 누게 (누적포인트)
///////////// NU_MER = 유효 (유효포인트)
function client_oracle_get_point_extend($name, $jumin, $id)
{
$conn = posserver_connect();
$where = "AND NAME='$name'
/*AND JUMIN_NO='$jumin'*/
";
$sql = "select MEM_NO , NU_MER from member where ID='$id' $where ";
//echo "oracleQuery :: $sql
";
//exit();
$stmt = oracle_query($conn, $sql);
$row = oci_fetch_assoc($stmt);
$returnValue['POINT'] = ($row['NU_MER']) ? $row['NU_MER'] : 0;
$returnValue['MEM_NO'] = ($row['MEM_NO']) ? $row['MEM_NO'] : "";
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
return $returnValue;
}
//POS서버누적포인트 입력
function set_posserver_point($sum, $name, $jumin)
{
global $_POST;
$conn = posserver_connect();
$sql = "update $table set NU_MER='$sum' where NAME='$name' and JUMIN_NO='$jumin'";
$stmt = oracle_query($conn, $sql);
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
}
//ORACLE 포인트 입력
function oracle_point_modify($mb_id)
{
return;
$mysql_ret = client_mysql_get_point($mb_id);
$name = $mysql_ret['name'];
$jumin = $mysql_fet['jumin'];
$oracle_ret = client_oracle_get_point($name, $jumin);
//오라클 포인트 + mysql 포인트 합산 결과치 저장
$point_sum = (int)$oracle_ret['NU_MER'] + (int)$mysql_ret['NU_MER'];
set_posserver_point($point_sum, $name, $jumin);
set_mysqlserver_point($mb_id);
}
//------------------------------------------------------------------------
// POS 서버 회원가입
// 신규가입시 POS의 MEMBER 테이블에는 MEN_NO 의 값이 없기때문에
// MEM_NO 를 생성하는 절차및 , 알고리즘을 통해
// MYSQL 의 MEMBER 테이블의 CARDID 를 생성하고 ,
// 생성된 CARDID 를 POS 서버 MEM_NO 를 추가하는 방식으로 한다.
// 알고리즘및 절차사항이 아직 완벽하지 않기때문에 잠시보류하기로한다.
//------------------------------------------------------------------------
function posserver_member_register()
{
global $_POST;
//--------------------------------------------
//TODO 회원가입 정보데이터
//--------------------------------------------
$mb_id = trim($_POST['mb_id']);
$mb_password = trim($_POST['mb_password']);
$mb_password_re = trim($_POST['mb_password_re']);
$mb_name = trim($_POST['mb_name']);
$mb_jumin = trim($_POST['jumin']);
$mb_nick = trim($_POST['mb_nick']);
$mb_email = trim($_POST['mb_email']);
$mb_sex = isset($_POST['mb_sex']) ? trim($_POST['mb_sex']) : "";
$mb_birth = isset($_POST['mb_birth']) ? trim($_POST['mb_birth']) : "";
$mb_homepage = isset($_POST['mb_homepage']) ? trim($_POST['mb_homepage']) : "";
$mb_tel = isset($_POST['mb_tel']) ? trim($_POST['mb_tel']) : "";
$mb_hp = isset($_POST['mb_hp']) ? trim($_POST['mb_hp']) : "";
$mb_zip1 = isset($_POST['mb_zip']) ? substr(trim($_POST['mb_zip']), 0, 3) : "";
$mb_zip2 = isset($_POST['mb_zip']) ? substr(trim($_POST['mb_zip']), 3) : "";
$mb_addr1 = isset($_POST['mb_addr1']) ? trim($_POST['mb_addr1']) : "";
$mb_addr2 = isset($_POST['mb_addr2']) ? trim($_POST['mb_addr2']) : "";
$mb_addr3 = isset($_POST['mb_addr3']) ? trim($_POST['mb_addr3']) : "";
$mb_addr_jibeon = isset($_POST['mb_addr_jibeon']) ? trim($_POST['mb_addr_jibeon']) : "";
$mb_signature = isset($_POST['mb_signature']) ? trim($_POST['mb_signature']) : "";
$mb_profile = isset($_POST['mb_profile']) ? trim($_POST['mb_profile']) : "";
$mb_recommend = isset($_POST['mb_recommend']) ? trim($_POST['mb_recommend']) : "";
$mb_mailling = isset($_POST['mb_mailling']) ? trim($_POST['mb_mailling']) : "";
$mb_sms = isset($_POST['mb_sms']) ? trim($_POST['mb_sms']) : "";
$mb_1 = isset($_POST['mb_1']) ? trim($_POST['mb_1']) : "";
$mb_2 = isset($_POST['mb_2']) ? trim($_POST['mb_2']) : "";
$mb_3 = isset($_POST['mb_3']) ? trim($_POST['mb_3']) : "";
$mb_4 = isset($_POST['mb_4']) ? trim($_POST['mb_4']) : "";
$mb_5 = isset($_POST['mb_5']) ? trim($_POST['mb_5']) : "";
$mb_6 = isset($_POST['mb_6']) ? trim($_POST['mb_6']) : "";
$mb_7 = isset($_POST['mb_7']) ? trim($_POST['mb_7']) : "";
$mb_8 = isset($_POST['mb_8']) ? trim($_POST['mb_8']) : "";
$mb_9 = isset($_POST['mb_9']) ? trim($_POST['mb_9']) : "";
$mb_10 = isset($_POST['mb_10']) ? trim($_POST['mb_10']) : "";
//--------------------------------
// mysql 테이블의 member 테이블의
// cardid 를 가져온다.
//--------------------------------
$sql = "select cardid from g5_member where mb_id='$mb_id'";
//echo "oraclePosQuery1 :: $sql
";
if (!$q = sql_query($sql)):
echo sql_error_info();
exit;
endif;
$row = sql_fetch_array($q);
$cardid = trim($row['cardid']);
//;;;;;;;;;;;;;;;;;;;;;;;;;
//todo POS 서버 신규가입
//;;;;;;;;;;;;;;;;;;;;;;;;;
if (!$cardid):
//;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
// 신규카드 아이디 만듬
//;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
//$cardid=get_cardid();
//20170315
$cardid = get_mem_no();
//;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
// POS 서버 회원정보 저장
//;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
posserver_save_member($cardid);
//exit();
//---------------------------
//pos 서버로 접속 함
//---------------------------
//$query = 'select * from member where rownum<=10';
//$stmt = oracle_query($query);
//while($row = oci_fetch_assoc($stmt)):
// print_r($row);
//endwhile;
return $cardid;
endif;
return '';
}
//---------------------------------------------------------------
// 회원정보 수정
//---------------------------------------------------------------
function oracle_member_modify()
{
global $_POST;
$mb_id = trim($_POST['mb_id']);
$mb_password = trim($_POST['mb_password']);
$mb_password_re = trim($_POST['mb_password_re']);
$mb_name = trim($_POST['mb_name']);
$mb_nick = trim($_POST['mb_nick']);
$mb_email = trim($_POST['mb_email']);
$mb_sex = isset($_POST['mb_sex']) ? trim($_POST['mb_sex']) : "";
$mb_birth = isset($_POST['mb_birth']) ? trim($_POST['mb_birth']) : "";
$mb_homepage = isset($_POST['mb_homepage']) ? trim($_POST['mb_homepage']) : "";
$mb_tel = isset($_POST['mb_tel']) ? trim($_POST['mb_tel']) : "";
$mb_hp = isset($_POST['mb_hp']) ? trim($_POST['mb_hp']) : "";
$mb_zip1 = isset($_POST['mb_zip']) ? substr(trim($_POST['mb_zip']), 0, 3) : "";
$mb_zip2 = isset($_POST['mb_zip']) ? substr(trim($_POST['mb_zip']), 3) : "";
$mb_addr1 = isset($_POST['mb_addr1']) ? trim($_POST['mb_addr1']) : "";
$mb_addr2 = isset($_POST['mb_addr2']) ? trim($_POST['mb_addr2']) : "";
$mb_addr3 = isset($_POST['mb_addr3']) ? trim($_POST['mb_addr3']) : "";
$mb_addr_jibeon = isset($_POST['mb_addr_jibeon']) ? trim($_POST['mb_addr_jibeon']) : "";
$mb_signature = isset($_POST['mb_signature']) ? trim($_POST['mb_signature']) : "";
$mb_profile = isset($_POST['mb_profile']) ? trim($_POST['mb_profile']) : "";
$mb_recommend = isset($_POST['mb_recommend']) ? trim($_POST['mb_recommend']) : "";
$mb_mailling = isset($_POST['mb_mailling']) ? trim($_POST['mb_mailling']) : "";
$mb_sms = isset($_POST['mb_sms']) ? trim($_POST['mb_sms']) : "";
$mb_1 = isset($_POST['mb_1']) ? trim($_POST['mb_1']) : "";
$mb_2 = isset($_POST['mb_2']) ? trim($_POST['mb_2']) : "";
$mb_3 = isset($_POST['mb_3']) ? trim($_POST['mb_3']) : "";
$mb_4 = isset($_POST['mb_4']) ? trim($_POST['mb_4']) : "";
$mb_5 = isset($_POST['mb_5']) ? trim($_POST['mb_5']) : "";
$mb_6 = isset($_POST['mb_6']) ? trim($_POST['mb_6']) : "";
$mb_7 = isset($_POST['mb_7']) ? trim($_POST['mb_7']) : "";
$mb_8 = isset($_POST['mb_8']) ? trim($_POST['mb_8']) : "";
$mb_9 = isset($_POST['mb_9']) ? trim($_POST['mb_9']) : "";
$mb_10 = isset($_POST['mb_10']) ? trim($_POST['mb_10']) : "";
/*---------------------------------------------------------------------
JUMIN_NO VARCHAR2(14 BYTE) NULL, = 생년월일
MAIL_NO VARCHAR2(7 BYTE) NULL, = 우편번호
ADDR VARCHAR2(60 BYTE) NULL, = 주소1
ADDR2 VARCHAR2(100 BYTE) NULL, = 주소2
E_MAIL VARCHAR2(30 BYTE) NULL, = 이메일
ID VARCHAR2(10 BYTE) NULL, = 아이디
HANDPHONE VARCHAR2(20 BYTE) NULL, = 핸드폰
-----------------------------------------------------------------------*/
//20170419 :: cardid 업데이트 되면 안됨.
$sql = "select mb_name,jumin from g5_member where mb_id='$mb_id'";
$fetch = sql_fetch($sql);
$mb_name = $fetch['mb_name'];
$jumin = $fetch['jumin'];
$zipcode = $mb_zip1 . "-" . $mb_zip2;
$addr1 = $mb_addr1;
$addr2 = $mb_addr2;
$conn = posserver_connect();
if ($jumin):
$sql = "update member set
MAIL_NO='$zipcode',
ADDR='$addr1',
ADDR2='$addr2',
E_MAIL='$mb_email',
ID='$mb_id',
HANDPHONE='$mb_hp',
UPD_DATE='" . date("YmdHis") . "'
where NAME='$mb_name' and JUMIN_NO='$jumin'";
else:
$sql = "update member set
MAIL_NO='$zipcode',
ADDR='$addr1',
ADDR2='$addr2',
E_MAIL='$mb_email',
ID='$mb_id',
HANDPHONE='$mb_hp',
UPD_DATE='" . date("YmdHis") . "'
where ID='$mb_id'";
endif;
$stmt = oracle_query($conn, $sql);
oci_free_statement($stmt);
oci_close($conn);
}
//----------------------------------------------------------
// 오라클 회원로그인 정보 체크
// 웹에없는 회원정보를 체크하기위하여
// 웹base 에서 오라클 쪽으로 쿼리하여 정보를 조회한다.
//----------------------------------------------------------
function oracle_login_check($mb_id, $mb_passwd)
{
$conn = posserver_connect();
$sql = "select * from member where (ID='$mb_id') or (NAME='$mb_id' AND JUMIN_NO='$mb_passwd')";
//echo "oracleQuery :: $sql
";
//exit();
$stmt = oracle_query($conn, $sql);
$ret['row'] = '';
$c = 0;
while ($row = oci_fetch_assoc($stmt)):
$NAME = $row['NAME'];
$JUMIN = $row['JUMIN_NO'];
$ret['row'] = $row;
$c++;
endwhile;
oci_free_statement($stmt);
oci_close($conn);
$ret['mb_id'] = '';
$ret['mb_passwd'] = '';
if (trim($NAME)):
$ret['mb_id'] = $ret['row']['ID'];
$ret['mb_passwd'] = $JUMIN;
$ret['count'] = $c;
endif;
return $ret;
}
function is_check_pos_member($name, $jumin, $regid)
{
//20170316
//포스쪽의 회원도 확인을 한다
//조건 : 인터넷회원,성명,생년,웹아이디
$conn = posserver_connect();
$where = "AND NAME='$name'
AND JUMIN_NO='$jumin'
";
$query = "SELECT COUNT(*) REG_COUNT FROM MEMBER WHERE ID='$regid'
$where ";
//echo "oracleQuery :: $query
";
$stmt = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
$flag = ($row['REG_COUNT'] == 0) ? false : true;
oci_free_statement($stmt);
return $flag;
}
function leave_pos_member($id)
{//20170317 포스쪽에서도 탈퇴처리를 하여 준다
//그누보드 회원 정보 조회
$query = "select mb_id , mb_name , jumin from g5_member where mb_id='' ";
$stmt = sql_query($query);
$row = sql_fetch_array($stmt);
if ($row['mb_id']) {
//포스쪽에서도 탈퇴처리를 해준다
//아이디,이메일 NULL 값으로 변경
$where = "AND NAME='" . $row['mb_name'] . "'
AND JUMIN_NO='" . $row['jumin'] . "'
";
$query = "SELECT COUNT(*) REG_COUNT FROM MEMBER WHERE ID='" . $row['mb_id'] . "'
$where ";
//echo "oracleQuery :: $query
";
$stmt2 = oracle_query($conn, $query);
$row = oci_fetch_assoc($stmt);
if ($row['REG_COUNT'] >= 1) {//포스테이터가 존재하면 탈퇴처리
$query = "UPDATE MEMBER SET ID=NULL , HANDPHONE=NULL , PM_REMK = NULL
WHERE ID='" . $row['mb_id'] . "' $where ";
//echo "oracleQuery :: $query
";
$stmt3 = oracle_query($conn, $query);
oci_free_statement($stmt3);
}
oci_free_statement($stmt2);
}
$row = null;
$stmt = null;
return;
}
function oracle_get_point_back_20241218($name, $jumin, $id = ""){ //20241211
// Oracle 데이터베이스 연결 정보
$host = "210.94.135.29"; // Oracle DB 호스트
$port = "1521"; // Oracle DB 포트 (기본값: 1521)
$sid = "ORCL"; // Oracle SID
$username = "book"; // Oracle 사용자 이름
$password = "easy2002"; // Oracle 비밀번호
// Oracle 연결 문자열 생성
$connection_string = "(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port)))
(CONNECT_DATA=(SID=$sid))
)";
// 데이터베이스 연결
$conn = oci_connect($username, $password, $connection_string);
if (!$conn) {
$e = oci_error(); // 에러 정보 가져오기
die("Oracle 연결 실패: " . $e['message']);
}
//$sql = "SELECT * FROM member WHERE ID='{$id}'; ";
$where = " and NU_MER is not null ";
$fields = "*";
$fields = "NU_MER";
$fields = "SUM(NU_MER) NU_MER_SUM";
$query= "select {$fields} from member where id='{$id}' {$where} ";
if($_SERVER['REMOTE_ADDR'] == G5_ADMIN_DEBUG_IP){
/*
echo "Query: $query
"; // SQL 확인
echo "point: $point
"; // SQL 확인
*/
}
$stmt = oci_parse($conn, $query);
if (!$stmt) {
$e = oci_error($conn); // 연결 오류 확인
die("쿼리 준비 실패: " . $e['message']);
}
// 쿼리 실행
if (!oci_execute($stmt)) {
$e = oci_error($stmt); // 실행 오류 확인
die("쿼리 실행 실패: " . $e['message']);
}
$point = 0; // 기본값
while ($row = oci_fetch_assoc($stmt)) {
foreach ($row as $column => $value) {
$point += isset($value) ? (int)$value: 0;
}
}
// 리소스 해제
oci_free_statement($stmt);
oci_close($conn);
/*
if($_SERVER['REMOTE_ADDR'] == G5_ADMIN_DEBUG_IP){
echo "Query: $query
"; // SQL 확인
echo "point: $point
"; // SQL 확인
}
*/
if (!$point) {
$point = 0;
}
return $point;
}
//20241218
//20241218
//20241218
function oracle_get_point($name, $jumin, $id = "" , $cardid = ""){ //20241211
if($_SERVER['REMOTE_ADDR'] == G5_ADMIN_DEBUG_IP){
//echo "oracle_get_point($name, $jumin, $id, $cardid)
";
}
/*
$id = "shin8904";
$cardid ="7719681000253361";
*/
// Oracle 데이터베이스 연결 정보
$host = "210.94.135.29"; // Oracle DB 호스트
$port = "1521"; // Oracle DB 포트 (기본값: 1521)
$sid = "ORCL"; // Oracle SID
$username = "book"; // Oracle 사용자 이름
$password = "easy2002"; // Oracle 비밀번호
// Oracle 연결 문자열 생성
$connection_string = "(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port)))
(CONNECT_DATA=(SID=$sid))
)";
// 데이터베이스 연결
$conn = oci_connect($username, $password, $connection_string);
if (!$conn) {
$e = oci_error(); // 에러 정보 가져오기
die("Oracle 연결 실패: " . $e['message']);
}
//1.mysql 포인트 조회
/*
$fields = "mb_id , cardid , jumin , mb_point2";
$where = " and mb_id='{$id}' ";
$query = "SELECT mb_id , cardid , jumin , mb_point2 FROM g5_member WHERE 1=1 {$where} ; ";
//echo "query-->{$query}
";
$msql_data = sql_fetch($query);
*/
//2.오라클(pos) 포인트 조회
$where = " and NU_MER is not null ";
$where .= " and mem_no = '{$cardid}' ";
$fields = "*";
$fields = "NU_MER";
$fields = "ROUND(NU_MER, 0)";
$query= "select {$fields} from member where id='{$id}' {$where} ";
if($_SERVER['REMOTE_ADDR'] == G5_ADMIN_DEBUG_IP){
// echo "query-->{$query}
";
}
$stmt = oci_parse($conn, $query);
if (!$stmt) {
$e = oci_error($conn); // 연결 오류 확인
die("쿼리 준비 실패: " . $e['message']);
}
// 쿼리 실행
if (!oci_execute($stmt)) {
$e = oci_error($stmt); // 실행 오류 확인
die("쿼리 실행 실패: " . $e['message']);
}
$point = 0; // 기본값
while ($row = oci_fetch_assoc($stmt)) {
foreach ($row as $column => $value) {
$point += isset($value) ? (int)$value: 0;
}
}
/*
echo "mb_point-->".ceil($msql_data['mb_point2'])."
";
echo "point-->".ceil($point)."
";
*/
//20241217 (1.2비교하여 같으면 pos의 nu_mer 가져오기)
/*
if (ceil($msql_data['mb_point2']) == ceil($point)){
}else{
$point = 0;
}
*/
//echo "return_point-->".ceil($point)."
";
// 리소스 해제
oci_free_statement($stmt);
oci_close($conn);
/*
if($_SERVER['REMOTE_ADDR'] == G5_ADMIN_DEBUG_IP){
echo "Query: $query
"; // SQL 확인
echo "point: $point
"; // SQL 확인
}
*/
if (!$point) {
$point = 0;
}
return $point;
}
//20241218
//20241218
//20241218
function oracle_get_point_noused($name, $jumin) //20241211 부터 사용안함
{
global $conn;
$sql = "select * from member where NAME='$name' and JUMIN_NO='$jumin' and rownum<=1;";
/* 사용포인트관련 테스트 쿼리
$sql="select * from member where NAME='여동진' and JUMIN_NO='710911' and rownum<=1";
echo "psql :: $sql
";
*/
$stmt = oracle_query($conn, $sql);
$ret['row'] = '';
while ($row = oci_fetch_assoc($stmt)):
$point = (int)$row['NU_MER'];
endwhile;
oci_free_statement($stmt);
if (!$point) {
$point = 0;
}
return $point;
}
function oracle_get_solo_point($name, $jumin)
{
$conn = posserver_connect();
$sql = "select * from member where NAME='$name' and JUMIN_NO='$jumin'";
//echo "ajaxOracleQuery :: $sql \n";
$stmt = oracle_query($conn, $sql);
$ret['row'] = '';
$c = 0;
while ($row = oci_fetch_assoc($stmt)):
$ret[$c]['zipcode'] = $row['MAIL_NO'];
$ret[$c]['telno'] = $row['HANDPHONE'];
$ret[$c]['addr1'] = $row['ADDR'];
$ret[$c]['addr2'] = $row['addr2'];
$ret[$c]['email'] = $row['E_MAIL'];
$ret[$c]['POINT'] = $row['NU_MER'];
$ret[$c]['id'] = $row['id'];
$c++;
endwhile;
oci_free_statement($stmt);
oci_close($conn);
if (count($ret) == 1):
$set['zipcode'] = $ret[0]['zipcode'];
$set['telno'] = $ret[0]['tleno'];
$set['addr1'] = $ret[0]['addr1'];
$set['addr2'] = $ret[0]['addr2'];
$set['email'] = $ret[0]['email'];
$set['point'] = $ret[0]['POINT'];
$set['id'] = $ret[0]['id'];
$set['count'] = 1;
else:
$set['zipcode'] = $ret[0]['zipcode'];
$set['telno'] = $ret[0]['tleno'];
$set['addr1'] = $ret[0]['addr1'];
$set['addr2'] = $ret[0]['addr2'];
$set['email'] = $ret[0]['email'];
$set['point'] = $ret[0]['POINT'];
$set['id'] = $ret[0]['id'];
$set['count'] = $c;
endif;
return $set;
}
function is_pos_member($name, $jumin)
{
$conn = posserver_connect();
$sql = "select NAME from member where NAME='$name' AND JUMIN_NO='$jumin' and rownum<=1";
$stmt = oracle_query($conn, $sql);
while ($row = oci_fetch_assoc($stmt)):
$rx = $row['NAME'];
endwhile;
oci_free_statement($stmt);
oci_close($conn);
if (trim($rx)):
return true;
endif;
return false;
}
//todo: pos 로 가입한 사용자 아이디인지
//검출하는 펑션
function is_pospatten_id($mb_id)
{
$first_2 = substr($mb_id, 0, 2);
$last_6 = substr($mb_id, 2, strlen($mb_id));
if (trim($first_2) != 'yk' || strlen($last_6) != 6):
return false;
else:
return true;
endif;
}
//-----------------------------------------------------------
//오라클 포인트를 날짜별로 소트하여 신규포인트 정보 갱신함
// data 형식 : 20161031
//-----------------------------------------------------------
function oracle_getmember_to_refresh_point($findDate)
{
$conn = posserver_connect();
$query = "select * from member where SUBSTR(UPD_DATE,1,8)>='$findDate'";
$stmt = oracle_query($conn, $query);
while ($row = oci_fetch_assoc($stmt)):
$fetch_list[] = $row;
endwhile;
// 오라클 접속 닫기
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
$cc = 0;
for ($i = 0; $i < count($fetch_list); $i++):
/*
$row = $fetch_list[$i];
$oracle_id = $row['id'];
$oracle_name = $row['name'];
$oracle_jumin = $row[JUMIN_NO];
$nujuk_point = $row['GUME_MER'];
$youho_point = $row['NU_MER'];
$sql="select mb_id,mb_point,point_init from g5_member where jumin='$oracle_jumin' and mb_name='$oracle_name'";
$fetch = sql_fetch($sql);
//포인트 변경시점이 최초일때
if (trim($fetch[point_init])=='0'):
$point_sum = $row['NU_MER'] + $fetch['mb_point'];
else:
continue;
endif;
*/
//------------------------------------------------------
//오늘짜로 매장또는 포스에서 작업한 회원의 포인트갱신
//포스회원이랑 webbase 회원의 정보가 동일할때
//아래루틴 실행
//------------------------------------------------------
/*
if ($fetch['mb_id']):
$cc++;
$check_key = $oracle_jumin;
//--------------------------
//유효포인트만 갱신한다.
//--------------------------
$mb_id = $fetch['mb_id'];
$sql="update g5_member set mb_point='$point_sum',point_init='1' where mb_id='$mb_id'";
$log.=$oracle_name."=>".$oracle_jumin."=>".$youho_point."\r\n";
sql_query($sql);
else:
//$log.=$oracle_name."/".$oracle_jumin."\r\n";
endif;
*/
endfor;
return $log . '총 ' . count($fetch_list) . " of " . $cc . " 개의 회원정보를 업데이트 했습니다";
}
//-----------------------------------------
/* ----- 쿼리 Referance 기본골격
//-----------------------------------------
$conn = posserver_connect();
$query = 'select * from member where rownum<=10';
$stmt = oracle_query($query);
while($row = oci_fetch_assoc($stmt)):
print_r($row);
endwhile;
// 오라클 접속 닫기
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($conn);
*/
?>".$row['OUT_GB']."
";
print_r($conn);
/*
while($row = oci_fetch_assoc($stmt)):
endwhile;
*/
//echo "query-->{$sql}
";
//echo "func_out_gb-->{$out_gb}
";
oci_free_statement($stmt);
// 오라클에서 로그아웃
oci_close($posserver);
return $out_gb;
}
function posoracle_book_jaego($posgwanrino)
{
// global $webserver;
global $posserver;
//품절절판 , 없으면 0 리턴
//OUT_GB
$sql = "SELECT nvl(out_gb, '0') out_gb FROM bookcd WHERE bk_cd = '" . $posgwanrino . "'";
$stmt = oracle_query($posserver, $sql);
$fetch = array();
while ($row = oci_fetch_assoc($stmt)):
$pumjul = $row['OUT_GB'];
endwhile;
//품절 절판이라면
if ($pumjul == 1):
return 0;
endif;
//-------------------
//BK_QTY 재고
//-------------------
$sql = "SELECT bk_qty FROM bookcd WHERE bk_cd = '" . $posgwanrino . "'";
$stmt = oracle_query($posserver, $sql);
$fetch = array();
while ($row = oci_fetch_assoc($stmt)):
$jaego = $row['BK_QTY'];
endwhile;
oci_free_statement($stmt);
return (int)$jaego;
}
//------------------------------------------------------------
// 적립금 % 테이지로 리턴
// 도서할인가 * (return / 100) = 실제할인금액
//------------------------------------------------------------
function JukLib($YK_CD)
{
global $posserver;
// global $webserver;
//20170426
// $webserver = webserver_connect();
$posserver = posserver_connect();
if (trim($YK_CD) != ""):
$sSQL = "SELECT in_rate FROM bookcd WHERE bk_cd = '" . $YK_CD . "'";
$stmt = oracle_query($posserver, $sSQL);
while ($row = oci_fetch_assoc($stmt)):
$in_rate = (int)$row['IN_RATE'];
endwhile;
if ($in_rate < 80):
$Jrate = 5;
elseif ($in_rate >= 80 && $in_rate < 85):
$Jrate = 3;
elseif ($in_rate >= 85 && $in_rate < 90):
$Jrate = 2;
else:
$Jrate = 0;
endif;
endif;
return $Jrate;
}
//----------------------------------------------------------
// 할인율
//----------------------------------------------------------
function getDisRate($YK_CD)
{
global $posserver;
// global $webserver;
//20170425
// $webserver = webserver_connect();
$posserver = posserver_connect();
if (trim($YK_CD) != ""):
$sSQL = "SELECT in_rate,bk_part,out_danga FROM bookcd WHERE bk_cd ='" . $YK_CD . "'";
$stmt = oracle_query($posserver, $sSQL);
while ($row = oci_fetch_assoc($stmt)):
$in_rate = (int)$row['IN_RATE'];
$price = (int)$row['OUT_DANGA'];
$part = $row['BK_PART'];
endwhile;
endif;
if ($part = "0120" || $part = "0121" || $part = "0122" || $Part = "0123" || $part = "0124" || $part = "0130" || $part = "0131" || $part = "0132" || $part = "0133" || $part = "0134" || $part = "0110"):
if ($in_rate <= 75):
$Jrate = 10;
else:
$jrate = 0;
endif;
else:
if ($in_rate <= 70):
$Jrate = 10;
else:
$jrate = 0;
endif;
endif;
return $Jrate;
}
function ora_getprice($YK_CD)
{
global $posserver;
// global $webserver;
//20170425
// $webserver = webserver_connect();
$posserver = posserver_connect();
if (trim($YK_CD) != ""):
$sSQL = "SELECT in_rate,bk_part,out_danga FROM bookcd WHERE bk_cd ='" . $YK_CD . "'";
$stmt = oracle_query($posserver, $sSQL);
while ($row = oci_fetch_assoc($stmt)):
$in_rate = (int)$row['IN_RATE'];
$price = (int)$row['OUT_DANGA'];
$part = $row['BK_PART'];
endwhile;
endif;
return $price;
}
//-------------------------------------------------------------------------
// todo : 웹오라클의 할인과 적립
// 테이블명: sale sale2 sale3
// param : yk_cd = posgwanri
//-------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------
// todo : 할인율 과 할인율이적용된 금액
// web sale2,sale3 -> 임의로 지정해서 때리는곳
// pos bookcd -> in_rate 70% 이하는 일괄적으로 10% 그 이상은 할인안됨 80=3 , 85=2 적립 70=5적립
// param : yk_cd = posgwanrina , $price = 정가
// 리턴체크 $ret[protage] 가 고
//-------------------------------------------------------------------------------------------------------
function sale_price($yk_cd, $price)
{
$sale_protage = getDisRate($yk_cd);
$sale_price = $price - ($price * ($sale_protage / 100));
$ret['sale_protage'] = (int)$sale_protage;
$ret['sale_price'] = (int)$sale_price;
return $ret;
}
//--------------------------------------------------------------------------
// todo : 적립금
// param : yk_cd = posgwanrina , $sale_price = 인터넷가
//--------------------------------------------------------------------------
function save_price($yk_cd, $sale_price)
{
//$juklip_price = $sale_price /*인터넷할인가*/ * (JukLib($yk_cd) / 100);
$save_protage = JukLib($yk_cd);
$save_price = $sale_price * ($save_protage / 100);
$ret['save_protage'] = $save_protage;
$ret['save_price'] = $save_price;
return $ret;
}
//-------------------------------------------------------------------------
// todo : 웹오라클의 할인과 적립
// 테이블명: sale sale2 sale3
// param : yk_cd = posgwanri
//-------------------------------------------------------------------------
function get_web_sale_get_protage($yk_cd)
{
global $posserver;
// global $webserver;
/*
$sql = "SELECT gwanrino, sale_rt , save_rt, gubun FROM sale WHERE bk_cd='".$yk_cd."' UNION ALL " .
"SELECT gwanrino, sale_rt , save_rt, gubun FROM sale2 WHERE bk_cd='" . $yk_cd . "' UNION ALL " .
"SELECT gwanrino, sale_rt , save_rt, gubun FROM sale3 WHERE bk_cd='" . $yk_cd . "'";
$stmt = oracle_query($webserver,$sql);
while($row = oci_fetch_assoc($stmt)):
$gwanrino = $row['GWANRINO'];
$sale_rt = $row['sale_rt'];
$save_rt = $row['save_rt'];
endwhile;
*/
//---------------------------------------------
//기존 오라클에서 MYSQL 기존테이블로 교체
//---------------------------------------------
//MYSQL it_sael_price
$sql = "select it_sael_price,it_point,order_key from g5_shop_item where pos_key='$yk_cd'";
$row = sql_fetch($sql);
$gwanrino = $row['order_key'];
$sale_rt = $row['it_sael_price'];
$save_rt = $row['it_point'];
//------------------------------------------------------
//todo 모든정보가 있을경우
//------------------------------------------------------
if (trim($sale_rt)):
$ret['gwanrino'] = $gwanrino;
$ret['sale_rt'] = $sale_rt;
$ret['save_rt'] = $save_rt;
else:
$ret['gwanrino'] = '';
$ret['sale_rt'] = '';
$ret['save_rt'] = '';
endif;
return $ret;
}
//----------------------------------------------------------------------------
// todo : 토탈 할인율 적립금 모든정보 리턴
//----------------------------------------------------------------------------
function get_sale_get_tot($yk_cd, $price)
{
$ret0 = get_web_sale_get_protage($yk_cd);
/*-----------------------
$ret['GWANRINO'] = '';
$ret['sale_rt'] = '';
$ret['save_rt'] = '';
-------------------------*/
if (trim($ret0['sale_rt'])): //todo web 의 임의할인정보가 있다면
$ret['sale_protage'] = $ret0['sale_rt'];
$ret['sale_price'] = $price - ($price * ($ret0['sale_rt'] / 100));
$ret['save_protage'] = $ret0['save_rt'];
$ret['save_price'] = $ret['sale_price'] * ($ret0['save_rt'] / 100);
//print_r($ret);
else: //포스에 할인정보가 있다면
$ret1 = sale_price($yk_cd, $price);
$ret2 = save_price($yk_cd, $ret1['sale_price']);
$ret['sale_protage'] = $ret1['sale_protage'];
$ret['sale_price'] = $ret1['sale_price'];
$ret['save_protage'] = $ret2['save_protage'];
$ret['save_price'] = $ret2['save_price'];
endif;
return $ret;
}
//-------------------------------------------------------
//todo 재고 읽어와서 mysql에 업데이트
//정확한 통계치는 아니고
//데이타 걸려내기위한 도구로 활용할것임
//-------------------------------------------------------
function set_oracle_jaego_push_to_mysql($it_id, $pos_key)
{
$jaego = posoracle_book_jaego($pos_key);
if ($jaego == 0):
$sql = "update g5_shop_item set it_stock_qty='$jaego',it_use='0' where it_id='$it_id'";
//sql_query($sql);
else:
$sql = "update g5_shop_item set it_stock_qty='$jaego' where it_id='$it_id'";
endif;
//echo $sql."";
return $jaego;
}
//todo 포스키 업데이트
function get_book_infomation_update($it_id)
{
// global $webserver;
global $posserver;
//20170425
// $webserver = webserver_connect();
$posserver = posserver_connect();
$sql = "select pos_key,order_key from g5_shop_item where it_id='$it_id'";
$fetch = sql_fetch($sql);
$order_key = $fetch['order_key'];
$pkey = trim($fetch['pos_key']);
$pos_key = $pkey;
if (!$pkey):
$query = "select POSGWANRINO from BOOK_GWANRI where GWANRINO='$order_key'";
// $stmt = oracle_query($webserver, $query);
while ($row = oci_fetch_assoc($stmt)):
$pos_key = $row['POSGWANRINO'];
endwhile;
if ($pos_key):
$sql = "update g5_shop_item set pos_key='$pos_key' where it_id='$it_id'";
//echo $sql."";
sql_query($sql);
endif;
endif;
return $pos_key;
}
//$ret = get_sale_get_tot($yk_cd,$price);
?>
Warning: session_save_path(): Session save path cannot be changed after headers have already been sent in /var/www/html/common.php on line 193
Warning: ini_set(): Session ini settings cannot be changed after headers have already been sent in /var/www/html/common.php on line 200
Warning: ini_set(): Session ini settings cannot be changed after headers have already been sent in /var/www/html/common.php on line 201
Warning: ini_set(): Session ini settings cannot be changed after headers have already been sent in /var/www/html/common.php on line 202
Warning: ini_set(): Session ini settings cannot be changed after headers have already been sent in /var/www/html/common.php on line 203
Warning: session_set_cookie_params(): Session cookie parameters cannot be changed after headers have already been sent in /var/www/html/common.php on line 205
Warning: ini_set(): Session ini settings cannot be changed after headers have already been sent in /var/www/html/common.php on line 206
Warning: Undefined variable $_SESSION in /var/www/html/common.php on line 459
Warning: Trying to access array offset on value of type null in /var/www/html/common.php on line 459
Warning: Trying to access array offset on value of type null in /var/www/html/common.php on line 510
Warning: Undefined array key "mb_id" in /var/www/html/common.php on line 528
Warning: Undefined array key "device" in /var/www/html/common.php on line 662
Warning: Undefined array key "device" in /var/www/html/common.php on line 664
Warning: Trying to access array offset on value of type null in /var/www/html/common.php on line 700
Warning: Trying to access array offset on value of type null in /var/www/html/common.php on line 701
Warning: Undefined variable $aslang in /var/www/html/lib/common.lib.php on line 3833
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/lib/oracle_lib.php:1) in /var/www/html/lib/common.lib.php on line 266
Parse error: Unclosed '{' on line 24 in /var/www/html/lib/apms.lib.php on line 7136