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


/**
 * 新規会員テーブル関連
 *
 * @package     Onedepo.Db.Member.Member
 * @author      FUCO Tominaga
 * @access      public
 */

class Onedepo_Db_Member_Member
{
    var $dbaccess;

	function getMemberList($to, $from, $where = array(), $params = array(), $sortkey)
	{
        $log =& LogFactory::getLog();
        $log->debug("debug", "Onedepo_Db_Member_Member#getMemberList");

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $limitquery = "SELECT tba.*, tsa.membercd as sellercd, tsa.sellerstate as sellerstate, tca.membercd as consumercd FROM t_member_basic_attr as tba "
                        ."LEFT JOIN "
                        ."t_seller_attr as tsa"
                        ." ON tba.membercd = tsa.membercd AND tsa.delflg = false "
                        ."LEFT JOIN "
                        ."t_consumer_attr as tca"
                        ." ON tba.membercd = tca.membercd AND tca.delflg = false "
                        ."WHERE tba.delflg=false ";
        
        $countquery = "SELECT count(tba.membercd) as cnt FROM t_member_basic_attr as tba "
                        ."LEFT JOIN "
                        ."t_seller_attr as tsa"
                        ." ON tba.membercd = tsa.membercd AND tsa.delflg = false "
                        ."LEFT JOIN "
                        ."t_consumer_attr as tca"
                        ." ON tba.membercd = tca.membercd AND tca.delflg = false "
                        ."WHERE tba.delflg=false ";

        $qry = "";
        foreach ($where as $key => $val) {
            if (is_array($val)) {
                if ($key==0)    $qry .= " AND ( ";
                else            $qry .= " OR";

                foreach($val as $k => $v) {
                    if ($k > 0) $qry .= " AND";
                    $qry .= " $v";
                }
            }
        }
        if( $qry != "")
            $qry .= ")";
        
        $limitquery .= $qry;
        $limitquery .= " ORDER BY ". $sortkey;
        $limitquery .=  " LIMIT ". $to ." OFFSET ". $from;

        $data = $db->getAll($limitquery, $params);
        if (DB::isError($data)) {
            $log->error($data->userinfo, "Onedepo_Db_Member_Member#getMemberList");
            die($data->getMessage());
        }

        $countquery .= $qry;

        $cnt = $db->getOne($countquery, $params);
        if (DB::isError($data)) {
            $log->error($data->userinfo, "Onedepo_Db_Member_Member#getMemberList");
            die($data->getMessage());
        }
        $data += array("recordcount" => $cnt);


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

        return $data;
	}
    function selectAll($condition, $sort)
    {
        $params = array();
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#selectAll";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT * FROM t_member_basic_attr ";
        $sql .= " WHERE delflg = FALSE ";
        if(isset($condition)){
            if($condition[0]){
                $sql .= " AND membercd = ? ";
                $params[] = $condition[0];
            }
            if($condition[1]){
                $sql .= " AND lastnm || firstnm || lastnmkana || firstnmkana LIKE ? ";
                $params[] = $condition[1];
            }
            if($condition[2]){
                $sql .= " AND mailaddr = ? ";
                $params[] = $condition[2];
            }
        }
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);
        $data = $db->getAll($sql, $params);
//        $log->debug($sql, "Onedepo_Db_Member_Member#selectAll");
        $this->dbaccess->doClose($db);
        return $data;
    }

    function select_one($MemberCd)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#select_one";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($MemberCd);
        $sql = "select * from t_member_basic_attr where membercd = ? and delflg = FALSE;";
        $data = $db->getRow($sql, $params);
        if (DB::isError($data) || !$data) {
            $log->debug($data->userinfo, $func_name);
            die($data->getMessage());
        }
        $this->dbaccess->doClose($db);
        return $data;
    }
    
    function selectNoDel($MemberCd)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#select_one";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($MemberCd);
        $sql = "select * from t_member_basic_attr where membercd = ?;";
        $data = $db->getRow($sql, $params);
        if (DB::isError($data) || !$data) {
            $log->debug($data->userinfo, $func_name);
            die($data->getMessage());
        }
        $this->dbaccess->doClose($db);
        return $data;
    }
    
    // 会員のメールアドレスからパスワードなど取得
    function getUserinfoOfMail($mailaddr)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#getUserinfoOfMail";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($mailaddr);
        $sql  = "SELECT * FROM t_member_basic_attr ";
        $sql .= "WHERE mailaddr LIKE ? AND delflg = false";
        
        $data = $db->getRow($sql, $params);

        if (DB::isError($data) || !$data) {
            $log->debug($data->userinfo, $func_name);
            //die($data->getMessage());
            $this->dbaccess->doClose($db);
            return false;
        }
        $this->dbaccess->doClose($db);
        return $data;
    }

    function check_trial($ID)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#check_trial";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $params = array($ID);
        $sql = "select * from t_trial_member where sessionID = ?;";
        $data = $db->getRow($sql, $params);
//        $log->debug(print_r($data, true), $func_name);
        if(DB::isError($data) || !$data){
            $this->dbaccess->doClose($db);
            return false;
        } else {
            $this->dbaccess->doClose($db);
            return $data;
        }
    }

//仮会員登録
//引き数の$dataは、dataのクラスnewmember
    function trial_insert($SessionID, $data) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#trial_insert";
        $log->debug("debug", $func_name);
        
        $maildiv = $this->execMailDiv($data->member_div, $data->mail_accept);
        
        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $db->query("BEGIN;");
        
        $params = array($SessionID,
                        $data->member_div,
                        $data->lastname,
                        $data->frontname,
                        $data->lastname_kana,
                        $data->frontname_kana,
                        $data->mailaddress_user."@".$data->mailaddress_domain,
                        sha1($data->password),
                        $data->nickname,
                        $data->question,
                        $data->answer,
                        $data->select_year."-".$data->select_month."-".$data->select_day,
                        $data->sex,
                        $maildiv,
                        date("Y-m-d"),
                        $data->mailaddress_user."@".$data->mailaddress_domain,
                        $_SERVER['REMOTE_ADDR']);
        
        $sql = "select * from f_trial_member_update(1, ?, ?, ?, ?, ?, ?, ?, NULL, ?, ?, ?, ?, ?, ?, NULL, 1, ?, ?, ?, ?, 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) {
            $db->rollback();
            $db->query("ROLLBACK;");
            
            $this->dbaccess->doClose($db);
            
            trigger_error($res->userinfo, E_USER_ERROR);
            return false;
        }
        $log->debug("commit : ", $func_name);
        $db->commit();
        $db->query("COMMIT;");
        
        $this->dbaccess->doClose($db);
        
        return $res;
    }

//本会員登録
//引き数は配列
    function insert($data) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#insert";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $db->query("BEGIN;");
        
        $maildiv = $this->execMailDiv($data["memberdiv"], $data["maildiv"]);
        
        $params = array($data["memberdiv"],
                         $data["lastnm"],
                         $data["firstnm"],
                         $data["lastnmkana"],
                         $data["firstnmkana"],
                         $data["mailaddr"],
                         $data["password"],
                         $data["nicknm"],
                         $data["secquestioncd"],
                         $data["secquestionans"],
                         $data["birthdate"],
                         $data["sex"],
                         $maildiv,
                         date("Y-m-d"),
                         $data["mailaddr"],
                         $_SERVER['REMOTE_ADDR']);
        
        $sql = "select * from f_member_basic_attr_update(1, NULL, ?, ?, ?, ?, ?, ?, NULL, ?, ?, ?, ?, ?, ?, NULL, 1, ?, ?, ?, ?, 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) {
            $db->rollback();
            $db->query("ROLLBACK;");
            
            $this->dbaccess->doClose($db);
            
            trigger_error($res->userinfo, E_USER_ERROR);
            return false;
        }
        $log->debug("commit : ", $func_name);
        $db->commit();
        $db->query("COMMIT;");
        
        $this->dbaccess->doClose($db);
        
        return $res;
    }


//仮登録削除用関数
    function trial_delete($SessionID) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#trial_delete";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        
        $params = array($SessionID);
        $sql = "delete from t_trial_member where SessionID = ?;";
        
        $res = $db->query($sql, $params);
        
        if (DB::isError($res) || !$res) {
            $db->rollback();
            
            $this->dbaccess->doClose($db);
            
            trigger_error($res->userinfo, E_USER_ERROR);
            return false;
        }
        $log->debug("commit : ", $func_name);
        $db->commit();
        
        $this->dbaccess->doClose($db);
        
        return true;
    }

//会員テーブル削除
    function delete($Membercd) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#delete";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        
        $params = array($Membercd);
        $sql = "update t_member_basic_attr SET memberstate = 9 where membercd = ?;";
        
        $res = $db->query($sql, $params);
        
        if (DB::isError($res) || !$res) {
            $db->rollback();
            
            $this->dbaccess->doClose($db);
            
            trigger_error($res->userinfo, E_USER_ERROR);
            return false;
        }
        $log->debug("commit : ", $func_name);
        $db->commit();
        
        $this->dbaccess->doClose($db);
        
        return true;
    }
    
//会員テーブル 停止解除
    function undelete($Membercd) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#undelete";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        
        $params = array($Membercd);
        $sql = "update t_member_basic_attr SET memberstate = 1 where membercd = ?;";
        
        $res = $db->query($sql, $params);
        
        if (DB::isError($res) || !$res) {
            $db->rollback();
            
            $this->dbaccess->doClose($db);
            
            trigger_error($res->userinfo, E_USER_ERROR);
            return false;
        }
        $log->debug("commit : ", $func_name);
        $db->commit();
        
        $this->dbaccess->doClose($db);
        
        return true;
    }

    //本登録TBメールアドレス検索
    //登録しようとしているメールアドレスがすでに登録されてしまっているか否か
    function checkMailAddr($mail) {
        $ret = true;
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#checkMailAddr";
        $log->debug("debug", $func_name);
        
        $db  = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        //COUNTして1件以上であればfalse（エラー）を返す
        $params = array($mail);
        $data   = $db->getRow("select count(mailaddr) from t_member_basic_attr where mailaddr=? and delflg=FALSE;",$params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }
        
        $mailcnt = $data['count'];
        if ($mailcnt > 0) $ret=false;
        
        $this->dbaccess->doClose($db);
        return $ret;
    }
    
    //仮登録TBメールアドレス検索
    //登録しようとしているメールアドレスがすでにあった場合updateを行う
    //引数はnewmemberクラスである
    function checkTrialMailAddr($member) {
        $ret = true;
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#checkTrialMailAddr";
        $log->debug("debug", $func_name);
        
        $db  = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $mail = $member->mailaddress_user."@".$member->mailaddress_domain;
        
        //COUNTして1件であればupdateする
        $params = array($mail);
        $data   = $db->getRow("select count(mailaddr) from t_trial_member where mailaddr=? and delflg=FALSE;",$params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }
        
        $mailcnt = $data['count'];
        if ($mailcnt == 1) {
            //既に登録しようとしているメールアドレスがTB内に登録されていた、
            //つまりupdateすることになる場合、falseを返す
            $ret = false;
        }
        
        $this->dbaccess->doClose($db);
        return $ret;
    }
    
    //仮登録TBのupdate
    //引数はnewmemberクラスである
    function updateTrial($id,$member){
        $ret = true;
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#updateTrial";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        
        $maildiv = $this->execMailDiv($member->member_div, $member->mail_accept);
        
        $mail     = $member->mailaddress_user."@".$member->mailaddress_domain;
        $pass     = sha1($member->password);
        $birth    = $member->select_year."-".$member->select_month."-".$member->select_day;
        $regdate  = date("Y-m-d");
        
        $mailparams = array($mail);
        $cntarray = $db->getRow("select updcnt from t_trial_member where MailAddr = ? AND delflg = FALSE; ", $mailparams);
        $cnt      = $cntarray[0];
        
        
        //更新回数の加算
        $updcnt = $cnt + 1;
        
        $params = array($id, 
                        $member->member_div,
                        $member->lastname, 
                        $member->frontname, 
                        $member->lastname_kana,
                        $member->frontname_kana, 
                        $mail, 
                        $pass, 
                        $member->nickname,
                        $member->question, 
                        $member->answer,
                        $birth,
                        $member->sex,
                        $maildiv, 
                        $regdate, 
                        $mail,
                        $_SERVER['REMOTE_ADDR'],
                        $updcnt,
                        $mail);
        
        $sql  = "UPDATE t_trial_member ";
        $sql .= "   SET sessionid = ?, ";
        $sql .= "       memberdiv = ?, ";
        $sql .= "       lastnm = ?, ";
        $sql .= "       firstnm = ?, ";
        $sql .= "       lastnmkana = ?, ";
        $sql .= "       firstnmkana = ?, ";
        $sql .= "       mailaddr = ?, ";
        $sql .= "       password = ?, ";
        $sql .= "       nicknm = ?, ";
        $sql .= "       secquestioncd = ?, ";
        $sql .= "       secquestionans = ?, ";
        $sql .= "       birthdate = ?, ";
        $sql .= "       sex = ?, ";
        $sql .= "       maildiv = ?, ";
        $sql .= "       registdate = ?, ";
        $sql .= "       UpdUsrNm = ?, ";
        $sql .= "       UpdPcNm = ?, ";
        $sql .= "       UpdCnt = ? ";
        $sql .= " WHERE mailaddr = ?;";
        
        $res = $db->query($sql, $params);
        if (DB::isError($res) || !$res) {
            $log->trace("update rollback", $func_name);
            $db->rollback();
            
            $this->dbaccess->doClose($db);
            
            return false;
        }
        $log->trace("commit", $func_name);
        $db->commit();
        
        $this->dbaccess->doClose($db);
        
        return $ret;
    }
    
    //会員情報の更新
    function update(&$newmember)
    {
        $res = true;
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#update";
        $log->debug("debug", $func_name);
        
        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        
        $maildiv = $this->execMailDiv($newmember->member_div, $newmember->mail_accept);
        
        $params = array($newmember->memberCd,
                        $newmember->member_div,
                        $newmember->lastname,
                        $newmember->frontname,
                        $newmember->lastname_kana,
                        $newmember->frontname_kana,
                        $newmember->mailaddress_user."@".$newmember->mailaddress_domain,
                        $newmember->password,
                        $newmember->nickname,
                        $newmember->question,
                        $newmember->answer,
                        $newmember->select_year."-".$newmember->select_month."-".$newmember->select_day,
                        $newmember->sex,
                        $maildiv,
                        $newmember->memberCd,
                        $_SERVER['REMOTE_ADDR'],
                        $newmember->updCnt);
        
        $sql = "select * from f_member_basic_attr_update(2, ?, ?, ?, ?, ?, ?, ?, NULL, ?, ?, ?, ?, ?, ?, NULL, 1, ?, NULL, ?, ?, ?);";
        
//        $log->debug($sql, $func_name);
//        $log->debug(print_r($params, true), $func_name);
        
        $res = $db->query($sql,$params);
        if (DB::isError($res)) {
            $db->rollback();
            
            $this->dbaccess->doClose($db);
            
            trigger_error($res->userinfo, E_USER_ERROR);
            return false;
        }
        $log->trace("commit", $func_name);
        $db->commit();
        
        $this->dbaccess->doClose($db);
        
        return $res;
    }
    
    //会員情報のパスワード更新
    function updatePassword($membercd, $password)
    {
        $res = true;
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Member_Member#updatePassword";
        $log->debug("debug", $func_name);

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();

        $params = array(sha1($password), $membercd);

        $sql  = "UPDATE t_member_basic_attr SET ";
        $sql .= "password = ? ";

        $sql .= "WHERE membercd = ?";

        //$log->debug($sql, $func_name);
        //$log->debug(print_r($params, true), $func_name);

        $res = $db->query($sql,$params);
        if (DB::isError($res)) {
            $db->rollback();
            $this->dbaccess->doClose($db);
            $res = false;
            trigger_error($res->userinfo, E_USER_ERROR);
        } else {
            $log->trace("commit", $func_name);

            $db->commit();
        }

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

    }


    // メール区分加工関数
    function execMailDiv($div, $mail){
        switch ($div){
            // PC会員
            case 1 :
                if ($mail){
                    return 1;
                } else {
                    return 0;
                }
            
            // 携帯会員
            case 2 :
                if ($mail){
                    return 3;
                } else {
                    return 2;
                }
            
            // 他
            default : 
                return 0;
        }
        
    }

}
?>
