<?php
/**
 * @copyright ONE/DEPO Project
 * @license   PHP License 3.01 <http://www.php.net/license/3_01.txt>
 */


/**
 * キャストマスタテーブル関連
 *
 * @package     Onedepo.Db.Cast
 * @author      netnee T.Otogawa<tomokazu.otogawa@netnee.jp>
 * @access      public
 */
class Onedepo_Db_Delivery
{
    /**
     * DBアクセスオブジェクト
     *
     * @var     object
     * @access  private
     */
    var $dbaccess;
    
    /**
     * 条件取得
     * 
     * @param --
     * @return object 取得レコード
     */   
    function selectNonDelivery($lim, $off)
    {
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();
        
        $sql  = "SELECT stk.orderno, sup.suppliernm, str.makeritemcd, str.variationnm, bas.itemcd, bas.itemnm, ";
        $sql .= "stk.orderdate, sum(ex.exhibitcount) as excnt, sum(od.count) as ndcnt ";
        $sql .= "FROM t_ex_stock_detail ex ";
        $sql .= "INNER JOIN t_stock_title stk ";
        $sql .= "        ON stk.stocktitlecd = ex.stocktitlecd ";
        $sql .= "       AND stk.delflg = false ";
        $sql .= "INNER JOIN t_supplier sup ";
        $sql .= "        ON sup.suppliercd = stk.suppliercd ";
        $sql .= "       AND sup.delflg = false ";
        $sql .= "INNER JOIN t_item_basic_attr bas ";
        $sql .= "        ON bas.itemcd = ex.itemcd ";
        $sql .= "       AND bas.delflg = false ";
        $sql .= "INNER JOIN t_item_struct str ";
        $sql .= "        ON str.itemcd = ex.itemcd ";
        $sql .= "       AND str.structcd = ex.structcd ";
        $sql .= "       AND str.delflg = false ";
        $sql .= "LEFT  JOIN t_order_detail od ";
        $sql .= "        ON od.itemcd = ex.itemcd ";
        $sql .= "       AND od.structcd = ex.structcd ";
        $sql .= "       AND od.stockcd = ex.stockcd ";
        $sql .= "       AND od.delflg = false ";
        
        $sql .= "WHERE ex.stockstate = 4 ";
        
        $sql .= "GROUP BY stk.orderno, sup.suppliernm, str.makeritemcd, str.variationnm, bas.itemcd, bas.itemnm, ";
        $sql .= "stk.orderdate ";
        
        $sql .= "LIMIT ". $lim ." OFFSET ". $off . " ";

        $log->debug($sql, "Onedepo_Db_Delivery#selectNonDelivery");
        $data = $db->getAll($sql);
        
        $this->dbaccess->doClose($db);

        return $data;
    }
    
    function countNonDelivery()
    {
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();
        
        $sql  = "SELECT count(*) FROM (";
        $sql .= "SELECT stk.orderno, sup.suppliernm, stk.orderno, bas.itemnm, ";
        $sql .= "stk.orderdate, sum(ex.exhibitcount) as excnt, sum(od.count) as ndcnt ";
        $sql .= "FROM t_ex_stock_detail ex ";
        $sql .= "INNER JOIN t_stock_title stk ";
        $sql .= "        ON stk.stocktitlecd = ex.stocktitlecd ";
        $sql .= "       AND stk.delflg = false ";
        $sql .= "INNER JOIN t_supplier sup ";
        $sql .= "        ON sup.suppliercd = stk.suppliercd ";
        $sql .= "       AND sup.delflg = false ";
        $sql .= "INNER JOIN t_item_basic_attr bas ";
        $sql .= "        ON bas.itemcd = ex.itemcd ";
        $sql .= "       AND bas.delflg = false ";
        $sql .= "INNER JOIN t_item_struct str ";
        $sql .= "        ON str.itemcd = ex.itemcd ";
        $sql .= "       AND str.structcd = ex.structcd ";
        $sql .= "       AND str.delflg = false ";
        $sql .= "LEFT  JOIN t_order_detail od ";
        $sql .= "        ON od.itemcd = ex.itemcd ";
        $sql .= "       AND od.structcd = ex.structcd ";
        $sql .= "       AND od.stockcd = ex.stockcd ";
        $sql .= "       AND od.delflg = false ";

        $sql .= "WHERE ex.stockstate = 4 ";

        $sql .= "GROUP BY stk.orderno, sup.suppliernm, str.makeritemcd, bas.itemcd, bas.itemnm, ";
        $sql .= "stk.orderdate ";
        
        $sql .= ") tmp ";
              
        $log->debug($sql, "Onedepo_Db_Delivery#countNonDelivery");
        $cnt = $db->getOne($sql);
        
        $this->dbaccess->doClose($db);
        
        return $cnt;
    }

    function selectDelivery($conditions, $lim = 0, $off = 0, $sort = "", $stocktitlecd = "")
    {
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();
        
        switch($sort){
            case "date":
                $order = "str.salestartdate";
                break;
            case "code":
            default:
                $order = "str.makeritemcd";
        }
        
        //条件配列 [0]仕入先 [1]発売日(前) [2]発売日(後ろ) [3]注文番号 [4]JANコード [5]仕入区分 [6]並び替え
        $addsql = "";
        $selectflg = false;
        
        if ($stocktitlecd == ""){
            $selectflg = false;
        } else {
            $selectflg = true;
            $params = array($stocktitlecd);
        }
        

//        $sql  = " SELECT stk.stocktitlecd, exs.stockcd, stk.orderno, mak.makernm, str.makeritemcd, bas.itemnm, str.mediacd, str.salestartdate, str.makerprice, exs.costprice, count(*) as cnt ";
        $sql  = " SELECT stk.stocktitlecd, stk.orderno, str.makeritemcd, str.structcd, str.structcd, str.variationnm, bas.itemcd, bas.itemnm, str.salestartdate, str.makerprice, exs.costprice, sum(exs.exhibitcount) as cnt ";
        $sql .= " FROM t_ex_stock_detail exs, t_stock_title stk, t_item_struct str, t_item_basic_attr bas, t_supplier sup ";
        $sql .= " WHERE exs.stocktitlecd = stk.stocktitlecd ";

        if($selectflg){
            $sql .= " AND stk.stocktitlecd = ?" ;
            //$sql .= " AND exs.structcd = ? ";
        }
        $sql .= " AND stk.suppliercd = sup.suppliercd ";
        $sql .= " AND exs.itemcd = bas.itemcd ";
        $sql .= " AND exs.itemcd = str.itemcd ";
        $sql .= " AND exs.structcd = str.structcd ";
        $sql .= " AND exs.delflg = FALSE ";
        $sql .= " AND stk.delflg = FALSE ";
        $sql .= " AND str.delflg = FALSE ";
        $sql .= " AND bas.delflg = FALSE ";
        $sql .= " AND sup.delflg = FALSE ";
        $sql .= " AND exs.stockstate IN (2, 4) ";

        $sql .= $conditions;

        $sql .= " GROUP BY stk.stocktitlecd, stk.orderno, str.makeritemcd, str.structcd, str.variationnm, bas.itemcd, bas.itemnm, str.salestartdate, str.makerprice, exs.costprice ";
        
        $sql .= " ORDER BY ".$order." ASC ";
        
        if(!$selectflg){
            $sql .= "LIMIT ". $lim ." OFFSET ". $off . ";";
        }
        
        $log->debug($sql, "Onedepo_Db_Delivery#selectDelivery");

        if($selectflg){
            $data = $db->getAll($sql, $params);
        } else {
            $data = $db->getAll($sql);
        }

        $this->dbaccess->doClose($db);

        return $data;
    }
    
    function countDelivery($conditions)
    {
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();
        
        $sql  = "SELECT count(*) as cnt FROM (";
        
        $sql .= " SELECT stk.stocktitlecd, stk.orderno, str.makeritemcd, bas.itemnm, str.salestartdate, str.makerprice, exs.costprice, sum(exs.exhibitcount) as cnt ";
        $sql .= " FROM t_ex_stock_detail exs, t_stock_title stk, t_item_struct str, t_item_basic_attr bas, t_supplier sup ";
        $sql .= " WHERE exs.stocktitlecd = stk.stocktitlecd ";

        $sql .= " AND stk.suppliercd = sup.suppliercd ";
        $sql .= " AND exs.itemcd = bas.itemcd ";
        $sql .= " AND exs.itemcd = str.itemcd ";
        $sql .= " AND exs.structcd = str.structcd ";
        $sql .= " AND exs.delflg = FALSE ";
        $sql .= " AND stk.delflg = FALSE ";
        $sql .= " AND str.delflg = FALSE ";
        $sql .= " AND bas.delflg = FALSE ";
        $sql .= " AND sup.delflg = FALSE ";
        $sql .= " AND exs.stockstate IN (2, 4) ";

        $sql .= $conditions;

        $sql .= " GROUP BY stk.stocktitlecd, stk.orderno, str.makeritemcd, bas.itemnm, str.salestartdate, str.makerprice, exs.costprice ";
        
        $sql .= " ) AS tmp";
        
        $log->debug($sql, "Onedepo_Db_Delivery#countDelivery");

        $cnt = $db->getOne($sql);

        $this->dbaccess->doClose($db);

        return $cnt;
    }

    function getItemcdAndStructcd(){

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();

        $sql  = "SELECT itemcd, structcd ";
        $sql .= "FROM t_ex_stock_detail ";
        $sql .= "WHERE delflg = FALSE ";
        $sql .= "AND stockstate IN (2, 4) ";
        $sql .= "GROUP BY itemcd, structcd; ";
        
        $log->debug($sql, "Onedepo_Db_Delivery#getItemcdAndStructcd");
        $data = $db->getAll($sql);
        $this->dbaccess->doClose($db);
        return $data;
    }

    function getOrderNumber($stocktitlecd)
    {
        $params = array($stocktitlecd);

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();

        $sql  = "SELECT count(*) as ordernumber ";
        $sql .= "FROM t_ex_stock_detail ";
        $sql .= "WHERE stocktitlecd   = ? ";
        $sql .= "AND delflg     = FALSE ";
        $sql .= "AND stockstate IN (2, 4) ";
        
        $log->debug($sql, "Onedepo_Db_Delivery#getOrderNumber");
        
        $data = $db->getOne($sql,$params);

        $this->dbaccess->doClose($db);

        return $data;
    }

    function getOrderNoFromJancd($jancode)
    {
        $params = array($jancode);

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();

        $sql  = "SELECT stk.orderno ";
        $sql .= "FROM  t_item_struct str, t_ex_stock_detail exs, t_stock_title stk ";
        $sql .= "WHERE str.jancd      = ? ";
        $sql .= "AND str.itemcd       = exs.itemcd ";
        $sql .= "AND str.structcd     = exs.structcd ";
        $sql .= "AND exs.stockstate   IN (2, 4) ";
        $sql .= "AND exs.stocktitlecd = stk.stocktitlecd ";
        $sql .= "AND str.delflg       = FALSE ";
        $sql .= "AND exs.delflg       = FALSE ";
        $sql .= "AND stk.delflg       = FALSE ";
        $sql .= "GROUP BY exs.itemcd, exs.structcd, stk.orderno ; ";

        $log->debug($sql, "Onedepo_Db_Delivery#getOrdernumberFromJancd");
        
        $data = $db->getAll($sql,$params);

        $this->dbaccess->doClose($db);

        return $data;
    }
/*
    function getMinStockcd($itemcd, $structcd)
    {
        $params = array($itemcd, $structcd);

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();

        $sql  = "SELECT MIN(stockcd) ";
        $sql .= "FROM t_ex_stock_detail ";
        $sql .= "WHERE itemcd   = ? ";
        $sql .= "AND structcd   = ? ";
        $sql .= "AND delflg     = FALSE ";
        $sql .= "AND stockstate IN (2, 4) ";

        $log->debug($sql, "Onedepo_Db_Delivery#getMinStockcd");
        
        $data = $db->getOne($sql,$params);

        $this->dbaccess->doClose($db);

        return $data;
    }
    
    function updateDelivery($itemcd, $structcd, $stockcd, $orderunitprice)
    {
        $params = array($orderunitprice, $itemcd, $structcd, $stockcd);

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();
        
        $sql  = "UPDATE t_ex_stock_detail ";
        $sql .= "SET stockstate = 3, costprice = ? ";
        $sql .= "WHERE itemcd   = ? ";
        $sql .= "AND structcd   = ? ";
        $sql .= "AND stockcd    = ? ";
        $sql .= "AND delflg     = FALSE ";
        $sql .= "AND stockstate IN (2, 4) ";

        $log->debug($sql, "Onedepo_Db_Delivery#updateDelivery");

        $data = $db->query($sql,$params);

        $this->dbaccess->doClose($db);
    }
    
    function updateNonDelivery($itemcd, $structcd, $stockcd)
    {
        $params = array($itemcd, $structcd, $stockcd);
echo $stockcd . ":";
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();
        
        $sql  = "UPDATE t_ex_stock_detail ";
        $sql .= "SET stockstate = 4 ";
        $sql .= "WHERE itemcd   = ? ";
        $sql .= "AND structcd   = ? ";
        $sql .= "AND stockcd    = ? ";
        $sql .= "AND delflg     = FALSE ";
        $sql .= "AND stockstate IN (2, 4) ";

        $log->debug($sql, "Onedepo_Db_Delivery#updateNonDelivery");
        
        $data = $db->query($sql,$params);
        $this->dbaccess->doClose($db);
    }
    
    function createStockTitle($oldStockTitleCd, $itemcd, $structcd)
    {
        $func_name="createStockTitle";
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();
        $db->autoCommit(false);
        
        $sql  = "SELECT * FROM t_stock_title ";
        $sql .= "WHERE stocktitlecd = ?";
        
        $params = array($oldStockTitleCd);
        
        $data = $db->getRow($sql,$params);
        //var_dump($data);
        if (DB::isError($data)) {
            $log->error("rollback ".$data->userinfo, $func_name);
            $db->rollback();
            return false;
        }
        
        $sql  = "INSERT INTO t_stock_title (orderno, orderdate, suppliercd, supplidiv, updusrnm, updpcnm) ";
        $sql .= "VALUES ( ?, ?, ?, ?, ?, ?) ";
        
        $params = array($data["orderno"], $data["orderdate"], $data["suppliercd"], $data["supplidiv"], $data["updusrnm"], $data["updpcnm"]);
        
        $data = $db->query($sql, $params);
        
        if (DB::isError($data)) {
            $log->error("rollback ".$data->userinfo, $func_name);
            $db->rollback();
            return false;
        }
        $sql  = "select lastval();";
        
        $data = $db->getRow($sql);

        if (DB::isError($data)) {
            $log->error("rollback ".$data->userinfo, $func_name);
            $db->rollback();
            return false;
        }        
        
        
        $sql  = "UPDATE t_ex_stock_detail ";
        $sql .= "SET stocktitlecd = ? ";
        $sql .= "WHERE itemcd = ? ";
        $sql .= "AND   structcd = ? ";
        $sql .= "AND   stockstate = 4";
        $params = array($data["lastval"], $itemcd, $structcd);
        
        $data = $db->query($sql, $params);

        if (DB::isError($data)) {
            $log->error("rollback ".$data->userinfo, $func_name);
            $db->rollback();
            return false;
        }
        
        $db->commit();
        return true; 


    }
*/    
    function updateDelivery($stocktitlecd, $ordernum, $orderunitprice)
    {
        $func_name="updateDelivery";
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();
        $db->autoCommit(false);

        // 更新ユーザ
        $adminauth =& AdminAuthext::getInstance();
        $upduser = $adminauth->getAuthData("membernm");
        $log->debug("membernm", $upduser);
        //--


        foreach($stocktitlecd as $key=>$stc){
            $orderNumber = $this->getOrderNumber($stc);

            //1つの仕入見出しコードに対応する納品を更新
            $sql  = "UPDATE t_ex_stock_detail ";
            $sql .= "SET stockstate = 3, ";
            $sql .= "    costprice = ?, ";
            $sql .= "    updusrnm = ? ";
            $sql .= "WHERE stocktitlecd = ? ";
            $sql .= "AND   stockcd IN (";
            $sql .= "SELECT stockcd FROM t_ex_stock_detail ";
            $sql .= "WHERE stocktitlecd = ? ";
            $sql .= "AND   stockstate IN (2, 4) ";
            $sql .= "ORDER BY stockcd ";
            $sql .= "LIMIT ".$ordernum[$key]." OFFSET 0)";
            
            $params = array($orderunitprice[$key], $upduser, $stc, $stc);
            
            $res = $db -> query($sql, $params);
            
            if(DB::isError($res)){
                $log->error("rollback ".$res->userinfo, $func_name);
                $db -> rollback();
                return false;
            }
            
            $difference = ($ordernum[$key] - $orderNumber > 0)? $ordernum[$key] - $orderNumber : 0;

            if($difference > 0){
                //発注数以上の納品された場合
                $sql  = "SELECT * ";
                $sql .= "FROM t_ex_stock_detail ";
                $sql .= "WHERE stocktitlecd = ? ";
                $sql .= "AND stockstate = 3 ";
                $sql .= "ORDER BY stockcd DESC ";
                $params = array($stc);
                $res = $db -> getRow($sql, $params);

                $params = array(); $idx = 0;

                $params[$idx++] = ($res["itemcd"])?       $res["itemcd"]       : NULL;//商品コード
                $params[$idx++] = ($res["structcd"])?     $res["structcd"]     : NULL;//構成コード
                $params[$idx++] = ($res["stockcd"])?      $res["stockcd"]      : NULL;//在庫コード
                $params[$idx++] = ($res["sellercd"])?     $res["sellercd"]     : NULL;//出品者コード

                $params[$idx++] = ($res["stocktitlecd"])? $res["stocktitlecd"] : NULL;//仕入見出しコード
                $params[$idx++] = 1;                                                   //出品数
                $params[$idx++] = ($res["condition"])?    $res["condition"]    : NULL;//コンディション
                $params[$idx++] = ($res["sellingprice"])? $res["sellingprice"] : NULL;//販売価格
                $params[$idx++] = ($res["souryouprc"])?   $res["souryouprc"]   : NULL;//送料
    
                $params[$idx++] = 3;                                                   //在庫状態
                $params[$idx++] = ($res["scdstartdate"])? $res["scdstartdate"] : NULL;//開始予定日時
                $params[$idx++] = ($res["scdcompdate"])?  $res["scdcompdate"]  : NULL;//完了予定日時
                $params[$idx++] = ($res["itemexp"])?      $res["itemexp"]      : NULL;//商品説明
                $params[$idx++] = ($res["rackid"])?       $res["rackid"]       : NULL;//棚番ID

                $params[$idx++] = ($res["managecd"])?     $res["managecd"]     : NULL;//管理コード
                $params[$idx++] = ($res["costprice"])?    $res["costprice"]    : NULL;//仕入価格
                $params[$idx++] = ($res["ntcostprice"])?  $res["ntcostprice"]  : NULL;//仕入価格税抜き
                $params[$idx++] = ($res["consignment"])?  $res["consignment"]  : NULL;//委託
                $params[$idx++] = ($res["arrivaldate"])?  $res["arrivaldate"]  : NULL;//入荷日

                $params[$idx++] = $upduser;                  //更新ユーザ名
                $params[$idx++] = $_SERVER['REMOTE_ADDR'];   //更新マシン名

                for($i = 1; $i <= $difference; $i++){
                    $params[2]++;
                    $sql  = "SELECT * FROM f_ex_stock_detail_update( ";
                    $sql .= "1, ?, ?, ?, ?, ";
                    $sql .= "?, ?, ?, ?, ?, ";
                    $sql .= "?, ?, ?, ?, ?, ";
                    $sql .= "?, ?, ?, ?, ?, ";
                    $sql .= "?, ?, 0)";
                    
                    $res = $db->getAll($sql,$params);
                    if (DB::isError($res) || $res < 0) {
                        $log->error("rollback ".$res->userinfo, $func_name);
                        $db->rollback();
                        return false;
                    }
                }
            } else {
                //残りの同一仕入見出しコードに関しては未納とする
                $sql  = "UPDATE t_ex_stock_detail ";
                $sql .= "SET stockstate = 4";
                $sql .= "WHERE stocktitlecd = ? ";
                $sql .= "AND   stockstate IN (2, 4) ";

                $params = array($stc);

                $res = $db -> query($sql, $params);

                if(DB::isError($res)){
                    $log->error("rollback ".$res->userinfo, $func_name);
                    $db -> rollback();
                    return false;
                }
            }
/*            
            //残りの同一仕入見出しコードに関しては未納とする
            $sql  = "UPDATE t_ex_stock_detail ";
            $sql .= "SET stockstate = 4";
            $sql .= "WHERE stocktitlecd = ? ";
            $sql .= "AND   stockstate IN (2, 4) ";
            
            $params = array($stc);
            
            $res = $db -> query($sql, $params);

            if(DB::isError($res)){
                $log->error("rollback ".$res->userinfo, $func_name);
                $db -> rollback();
                return false;
            }
*/
            //仕入見出しを新規に作成する
            $sql  = "SELECT * FROM t_stock_title ";
            $sql .= "WHERE stocktitlecd = ?";

            $params = array($stc);
            $data = $db -> getRow($sql, $params);

            if (DB::isError($data)) {
                $log->error("rollback ".$data->userinfo, $func_name);
                $db->rollback();
                return false;
            }

            $sql  = "INSERT INTO t_stock_title (orderno, orderdate, suppliercd, supplidiv, updusrnm, updpcnm) ";
            $sql .= "VALUES ( ?, ?, ?, ?, ?, ?) ";

            $params = array($data["orderno"], $data["orderdate"], $data["suppliercd"], $data["supplidiv"], $data["updusrnm"], $data["updpcnm"]);

            $res = $db->query($sql, $params);

            if (DB::isError($res)) {
                $log->error("rollback ".$res->userinfo, $func_name);
                $db->rollback();
                return false;
            }
            
            //新規に作成したStockTitleCdを取得
            $sql  = "SELECT lastval();";

            $data = $db->getRow($sql);

            if (DB::isError($data)) {
                $log->error("rollback ".$data->userinfo, $func_name);
                $db->rollback();
                return false;
            }
            $newStc = $data["lastval"];
            
            //古いStockTitleCdを持つレコードの内、
            //ステータスが4のものは新しいStockTitleCdを付加
            $sql  = "UPDATE t_ex_stock_detail ";
            $sql .= "SET stocktitlecd = ? ";
            $sql .= "WHERE stocktitlecd = ? ";
            $sql .= "AND   stockstate = 4 ";
            
            $params = array($newStc, $stc);
            
            $res = $db->query($sql, $params);

            if (DB::isError($res)) {
                $log->error("rollback ".$res->userinfo, $func_name);
                $db->rollback();
                return false;
            }

        }
        
        $db -> commit();
        
        return true;
    }
}
?>
