<?php
define('USER','SYSTEM');
define('PASS','toybox');
define("DB","");

$iset = array("select"=>1,"insert"=>2,"update"=>3,"delete"=>4);
$show = FALSE;

function start_connect() {
     $con  = oci_connect(USER,PASS,DB);
     return $con;
}

function end_connect($con) {
     oci_close($con);
}

function make_sql($cate,$highlows,$item,$maker,$serial,$price) {
     $category = array("",
		       "item_deji",  "item_kaden","item_pc",
		       "item_ofice", "item_cdvd");
     $hg = array("<=",">="); 
     $fr = FALSE;
     if ($cate == 0) {
	  $table_all = TRUE;
	  $table = "TB";
     }else{
	  $table = $category[$cate];
     }
    
     $sql = "select * from ".$table;
     if ($item != NULL){
	  $fr   = TRUE;
	  $item = str_replace('　',' ',$item);
	  $buf  = ereg_replace('[[:space:]]',"%' OR ITEM_NAME LIKE '%",$item);
	  $sql  = $sql." where "."ITEM_NAME LIKE '%".$buf."%' ";
     }
     if ($maker != NULL) {
	  if ($fr) {
	       $sql = $sql." AND ";
	  } else {
	       $fr  = TRUE;
	       $sql = $sql." where ";
	  }
	  $maker = str_replace('　',' ',$maker);
	  $buf   = ereg_replace('[[:space:]]',"%' OR ITEM_MAKER LIKE '%",$maker);
	  $sql   = $sql." ITEM_MAKER LIKE '%".$buf."%' ";
     }
     if ($serial != NULL) {
	  if ($fr) {
	       $sql = $sql." AND ";
	  } else {
	       $fr  = TRUE;
	       $sql = $sql." where ";
	  }
	  $serial = str_replace('　',' ',$serial);
	  $buf    = ereg_replace('[[:space:]]',"' OR ITEM_NUMBER = '",$serial);
	  $sql    = $sql."ITEM_NUMBER = '".$buf."' ";
     }
     if ($price != NULL) {
	  if ($fr) {
	       $sql = $sql." AND ";
	  } else {
	       $fr  = TRUE;
	       $sql = $sql." where ";
	  }
	  $price = str_replace('　',' ',$price);
	  $buf   = ereg_replace('[[:space:]]'," OR ITEM_PRICE $hg[$highlows] ",$price);
	  $sql   = $sql."ITEM_PRICE ".$hg[$highlows]." ".$buf." ";
     }

     if ($table_all) {
	  for ($i=1;$i<count($category);$i++) {
	       $tmp = $tmp.str_replace('TB',$category[$i],$sql);
	       if($i%(count($category)-1)!=0){
		    $tmp = $tmp." UNION ALL ";
	       }
	  }
	  $sql = $tmp;
     }
     return $sql;
}

//------------------------------
// $sqlによって抽出された行を
// 仮想テーブルとして生成する
//
// arg
//   $con      : 接続id
//   $sql      : SQL文
//   $view_tbl : 仮想テーブル名
//
// return 
//   $view_tblの行数
//------------------------------
function create_view($con, $sql, $view_tbl) {
  $sql  = "create or replace view $view_tbl as ".$sql;
  $stmt = oci_parse($con,$sql);

  if (!oci_execute($stmt,OCI_DEFAULT)) {
       echo "error create_view\n";
       oci_free_statement($stmt);
       return NULL;
  }
  oci_free_statement($stmt);

  /* $view_tblの行数を計算する */
  $stmt = oci_parse($con, "select count(*) from $view_tbl");
  if(!oci_execute($stmt,OCI_DEFAULT)){
       echo "error create_view_2\n";
       oci_free_statement($stmt);
       return NULL;
  }
  if (!($all_ros = oci_fetch_row($stmt))) {
       echo "error create_view3\n";
       oci_free_statement($stmt);
       return NULL;
  }
  oci_free_statement($stmt);
  return $all_ros[0];
}

/* 指定したテーブルから指定した件数のデータを返す */
function select_range_data($con, $tbl, $start, $end)
{
     $sql  = "select * from (";
     $sql .= "select rownum as seq_no,$tbl.* from $tbl) ";
     $sql .= "where seq_no between $start and $end";

     $stmt = oci_parse($con,$sql);
     if(!oci_execute($stmt,OCI_DEFAULT)){
	  echo "error select_range_data\n";
	  oci_free_statement($stmt);
	  return array(NULL, NULL);
     }

     $ros = oci_fetch_all($stmt,$res);

     oci_free_statement($stmt);
     return array($ros,$res);
}

function get_zaiko($con, $id)
{
     $sql = "select item_stock from stock where item_id = '".$id."'";
     $stmt = oci_parse($con,$sql);

     if (!oci_execute($stmt,OCI_DEFAULT)) {
	  echo "error get_zaiko\n";
	  oci_free_statement($stmt);
	  return NULL;
     }

     if (!($zaiko = oci_fetch_row($stmt))) {
	  echo "error get_zaiko2\n";
	  oci_free_statement($stmt);
	  return NULL;
     }

     oci_free_statement($stmt);
     return $zaiko[0];
}

function zaiko_updata($ID)
{
     $sql  = "lock table stock in exclusive mode nowait";
     $con  = start_connect();
     $stmt = oci_parse($con,$sql);

     $sql  = "update stock set ITEM_STOCK = ITEM_STOCK -1 where ITEM_ID = $ID";

     $stmt=oci_parse($con,$sql);
     if (!oci_execute($stmt,OCI_DEFAULT)) {
	  oci_rollback($con);
	  return NULL;
     } else {
	  oci_commit($con);
     }

     oci_free_statement($stmt);
     oci_close($con);
}
?>