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

/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

/**
 * [[機能説明]]
 *
 * @package     Onedepo.db.itemorder
 * @author      T.Shimano <shimano@fuco.jp>
 * @access      public
 */
class Onedepo_Db_Itemorder
{
    var $dbaccess;
    
    // 一覧取得
    function getItemorderList($to = 10, $from = 0, $where = array(), $params = array(), $sortkey = 'makernm', $state = 0)
    {     
        $log =& LogFactory::getLog();
    
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql = "FROM t_item_struct st
                JOIN (
              SELECT itemcd, structcd
                FROM t_ex_stock_detail
            ". (($state==0)?"":"WHERE stockstate=$state") ."
            GROUP BY itemcd, structcd
                     ) AS te
                  ON st.itemcd=te.itemcd AND st.structcd=te.structcd
           LEFT JOIN t_item_basic_attr bs
                  ON st.itemcd = bs.itemcd
           LEFT JOIN
        (SELECT itemcd, structcd, count(stockcd) as advancenum
                FROM t_ex_stock_detail s
               WHERE stockstate = 1 AND exhibitcount != 0
                AND   stockcd NOT IN ( 
                      SELECT stockcd FROM t_order_detail t_o 
                        LEFT JOIN t_forward_detail t_f ON t_f.orderno = t_o.orderno AND t_f.orderrowno = t_o.orderrowno 
                       WHERE t_o.itemcd = s.itemcd AND t_o.structcd = s.structcd 
                         AND (t_f.forwardstate = 9) 
                      ) 
            GROUP BY itemcd , structcd ) AS ad 
                  ON st.itemcd = ad.itemcd AND st.structcd = ad.structcd
           LEFT JOIN
        (SELECT count(stockcd) as orderednum, itemcd, structcd
                FROM t_ex_stock_detail s
               WHERE stockstate = 2
                AND   stockcd NOT IN ( 
                      SELECT stockcd FROM t_order_detail t_o 
                        LEFT JOIN t_forward_detail t_f ON t_f.orderno = t_o.orderno AND t_f.orderrowno = t_o.orderrowno 
                       WHERE t_o.itemcd = s.itemcd AND t_o.structcd = s.structcd 
                         AND (t_f.forwardstate = 9) 
                      ) 
            GROUP BY itemcd , structcd
                   ) AS sd
                  ON st.itemcd = sd.itemcd AND st.structcd = sd.structcd ";

        $limitquery = "SELECT
       st.itemcd,
       st.structcd,
       makeritemcd,
       itemnm,
       variationnm,
       salestartdate,
       orderclosedate,
       makerprice,
       advancenum,
       orderednum ".$sql;


        $countquery = "SELECT count(*) AS cnt ".$sql;
        
        $qry = "";
        foreach ($where as $key => $val) {
            if (is_array($val)) {
                if ($key==0)    $qry .= " WHERE";
                else            $qry .= " OR";

                foreach($val as $k => $v) {
                    if ($k > 0) $qry .= " AND";
                    $qry .= " $v";
                }
            }
        }

        $limitquery .= $qry;
        //$limitquery .= " ORDER BY ". $sortkey;
        if($to > 0) {
            $limitquery .=  " LIMIT ". $to ." OFFSET ". $from;
        }
        
        $log->debug($limitquery, "Onedepo_Db_Itemorder#getItemorderList");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemorder#getItemorderList");
        
        $data = $db->getAll($limitquery, $params);
        if (DB::isError($data)) {
                        die($data->getMessage());
        }
        $countquery .= $qry;

        $cnt = $db->getOne($countquery, $params);
        
        if (DB::isError($data)) {
                        die($data->getMessage());
        }
        $data += array("recordcount" => $cnt);
        
        $this->dbaccess->doClose($db);

        return $data;
    }

    /**
     *　発注の更新
     *
     */
    function update($itemcd, $structcd, $suppliercd, $supplidiv, $ordercd, $orderunitprice, $ordernum, $upduser="")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itemorder#insert";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit(false);
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $params = array();
        //$params = array( $itemcd, $startdate, $closedate, $priority, $updusr, $_SERVER['REMOTE_ADDR'], $updcnt);
        $updpc = $_SERVER['REMOTE_ADDR'];


        // 仕入れ見出し
        $sql  = "INSERT INTO t_stock_title ( orderno, "
                                           ."orderdate, "
                                           ."suppliercd, "
                                           ."supplidiv, "
                                           ."delflg, "
                                           ."adddate, "
                                           ."upddate, "
                                           ."updusrnm, "
                                           ."updpcnm ,"
                                           ."updcnt) "
                                  ."VALUES ( '$ordercd', "
                                           ."CURRENT_DATE, "
                                           ."$suppliercd, "
                                           ."$supplidiv, "
                                           ."'false', "
                                           ."CURRENT_DATE, "
                                           ."CURRENT_DATE, "
                                           ."'$upduser', "
                                           ."'$updpc', "
                                           ."0 ) "; // 新しい行の仕入れ見出しCを取得する

        $res = $db->getAll($sql);


        if (DB::isError($res) || $res < 0) {
            $log->error("rollback ".$res->userinfo, $func_name);
            $db->rollback();
            return false;
        }
         
        $sql = "SELECT lastval()";
        $res = $db->getAll($sql);

        if (DB::isError($res) || $res < 0) {
            $log->error("rollback ".$res->userinfo, $func_name);
            $db->rollback();
            return false;
        }
        $stocktitlecd = $res[0]["lastval"];
        
        
        
        // ダミーレコードの各値を取得
        $sql  = "SELECT * FROM t_ex_stock_detail ";
        $sql .= "WHERE itemcd = ? ";
        $sql .= "AND   structcd = ? ";
        $sql .= "ORDER BY stockcd DESC ";

        $row = $db->getRow($sql,array($itemcd, $structcd));

        if (DB::isError($row) || $row < 0) {
            $log->error("rollback ".$row->userinfo, $func_name);
            $db->rollback();
            return false;
        }
        //必要な値をNULLクリア
        $condition    = ($row["condition"])    ? $row["condition"]    : NULL;
        $sellingprice = ($row["sellingprice"]) ? $row["sellingprice"] : NULL;
        $souryouprc   = ($row["souryouprc"])   ? $row["souryouprc"]   : NULL;
        $scdstartdate = ($row["scdstartdate"]) ? $row["scdstartdate"] : NULL;
        $scdcompdate  = ($row["scdcompdate"])  ? $row["scdcompdate"]  : NULL;
        $itemexp      = ($row["itemexp"])      ? $row["itemexp"]      : NULL;
        $rackid       = ($row["rackid"])       ? $row["rackid"]       : NULL;
        $managecd     = ($row["managecd"])     ? $row["managecd"]     : NULL;
        $consignment  = ($row["consignment"])  ? $row["consignment"]  : NULL;
        $arrivaldate  = ($row["arrivaldate"])  ? $row["arrivaldate"]  : NULL;
        
        
        
        //品在庫明細から予約状態の商品数と在庫コードを調べる
        $sql = "SELECT itemcd, structcd, stockcd "
               ."FROM t_ex_stock_detail s "
               ."WHERE itemcd = $itemcd "
                 ."AND structcd = $structcd "
                 ."AND stockstate = 1 "
                 ."AND exhibitcount = 1 "
                 ."AND delflg = false
                   AND   stockcd NOT IN ( 
                      SELECT stockcd FROM t_order_detail t_o 
                        LEFT JOIN t_forward_detail t_f ON t_f.orderno = t_o.orderno AND t_f.orderrowno = t_o.orderrowno 
                       WHERE t_o.itemcd = s.itemcd AND t_o.structcd = s.structcd 
                         AND (t_f.forwardstate = 9) 
                      )";

        $res = $db->getAll($sql);

        if (DB::isError($res) || $res < 0) {
               $log->error("rollback ".$res->userinfo, $func_name);
               $db->rollback();
               return false;
        }
        
        //予約状態の商品数と発注数から発注フラグを立てる在庫数を算出
        $rsvnum = count($res);
        if($ordernum < $rsvnum){
            $orderFlagNum = $ordernum;
        } else {
            $orderFlagNum = $rsvnum;
        }
        
        // 発注する数だけフラグを予約から発注に変更
        $sql  = "SELECT * FROM f_ex_stock_detail_update( ";
        $sql .= "2, ?, ?, ?, ?, ";
        $sql .= "?, ?, ?, ?, ?, ";
        $sql .= "?, ?, ?, ?, ?, ";
        $sql .= "?, ?, ?, ?, ?, ";
        $sql .= "?, ?, 0)";
        
        $params = array(); $idx = 0;
        $params[$idx++] = $itemcd;                      //商品コード
        $params[$idx++] = $structcd;                    //構成コード
        $params[$idx++] = NULL;                         //在庫コード
        $params[$idx++] = NULL;                         //出品者コード

        $params[$idx++] = $stocktitlecd;                //仕入見出しコード
        $params[$idx++] = 1;                            //出品数
        $params[$idx++] = $condition;                   //コンディション
        $params[$idx++] = $sellingprice;                //販売価格
        $params[$idx++] = $souryouprc;                  //送料

        $params[$idx++] = 2;                            //在庫状態
        $params[$idx++] = $scdstartdate;                //開始予定日時
        $params[$idx++] = $scdcompdate;                 //完了予定日時
        $params[$idx++] = $itemexp;                     //商品説明
        $params[$idx++] = $rackid;                      //棚番ID

        $params[$idx++] = $managecd;                    //管理コード
        $params[$idx++] = $orderunitprice;              //仕入価格
        $params[$idx++] = floor($orderunitprice/1.05);  //仕入価格税抜き
        $params[$idx++] = $consignment;                 //委託
        $params[$idx++] = $arrivaldate;                 //入荷日

        $params[$idx++] = $upduser;                     //更新ユーザ名
        $params[$idx++] = $updpc;                       //更新マシン名
        
        $cnt = 1;
        foreach($res as $row){
            if($orderFlagNum < $cnt) break;
            
            $params[2] = $row["stockcd"];               //在庫コード
            $res = $db->getAll($sql,$params);
            if (DB::isError($res) || $res < 0) {
                $log->error("rollback ".$res->userinfo, $func_name);
                $db->rollback();
                return false;
            }
            $cnt++;

        }
        
        if($ordernum > $rsvnum){  //予約数以上の発注を受けた場合
            // 余分に発注した分は発注扱いに
            $num = $ordernum - $rsvnum;
            
            $sql  = "SELECT * FROM f_ex_stock_detail_update( ";
            $sql .= "1, ?, ?, ?, ?, ";
            $sql .= "?, ?, ?, ?, ?, ";
            $sql .= "?, ?, ?, ?, ?, ";
            $sql .= "?, ?, ?, ?, ?, ";
            $sql .= "?, ?, 0)";
            
            $params = array(); $idx = 0;
            
            $params[$idx++] = $itemcd;                      //商品コード
            $params[$idx++] = $structcd;                    //構成コード
            $params[$idx++] = NULL;                         //在庫コード
            $params[$idx++] = NULL;                         //出品者コード
    
            $params[$idx++] = $stocktitlecd;                //仕入見出しコード
            $params[$idx++] = $num;                         //出品数
            $params[$idx++] = $condition;                   //コンディション
            $params[$idx++] = $sellingprice;                //販売価格
            $params[$idx++] = $souryouprc;                  //送料
    
            $params[$idx++] = 2;                            //在庫状態
            $params[$idx++] = $scdstartdate;                //開始予定日時
            $params[$idx++] = $scdcompdate;                 //完了予定日時
            $params[$idx++] = $itemexp;                     //商品説明
            $params[$idx++] = $rackid;                      //棚番ID
    
            $params[$idx++] = $managecd;                    //管理コード
            $params[$idx++] = $orderunitprice;              //仕入価格
            $params[$idx++] = floor($orderunitprice/1.05);  //仕入価格税抜き
            $params[$idx++] = $consignment;                 //委託
            $params[$idx++] = $arrivaldate;                 //入荷日
    
            $params[$idx++] = $upduser;                     //更新ユーザ名
            $params[$idx++] = $updpc;                       //更新マシン名
    
    
            $res = $db->getAll($sql,$params);
            if (DB::isError($res) || $res < 0) {
                $log->error("rollback ".$res->userinfo, $func_name);
                $db->rollback();
                return false;
            }
        }
        
        // ダミーデータの削除 
        $sql = "DELETE FROM t_ex_stock_detail "
                    ."WHERE itemcd = $itemcd "
        ."AND structcd = $structcd "
        ."AND exhibitcount = 0 ";
        $res = $db->getAll($sql);

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

        $db->commit();
        $this->dbaccess->doClose($db);
        return true;
        
    }

    /**
     * 削除
     *
     */
    /*
    function delete($itemcd, $structcd, $stockcd, $upduser, $updcnt)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itemexhibit#delete";

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $params = array($itemcd, $structcd, $stockcd, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt);

        $sql  = "SELECT f_ex_stock_detail_update(3, ?, ?, ?, NULL, NULL, NULL, NULL, NULL,".
        "NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ?, ?, ?);";
        $log->debug($sql, $func_name);
        $log->debug(print_r($params, true), $func_name);
        $res = $db->getOne($sql, $params);
        $log->debug(print_r($res, true), $func_name);
        if (DB::isError($res) || $res < 0) {
                        $log->debug("rollback", $func_name);
                        $db->rollback();
                        $this->dbaccess->doClose($db);
                        return false;
        }
        $log->debug("commit", $func_name);

        $db->commit();
        $this->dbaccess->doClose($db);
        return true;
    }
    */
}
?>