<?php
/**
 * 予約モデル
 */
// Qdmailのエラー(E_DEPRECATED)がでるので...
error_reporting(E_ALL & ~E_DEPRECATED);
include(dirname(__FILE__) . "/../libraries/qdmail.php");

class Res_model extends Model {
    
    // 予約エラーコード
    const ERR_ROOM_GUEST_MIN = -1;      // 部屋あたりの人数が下限より小
    const ERR_ROOM_GUEST_MAX = -2;      // 部屋あたりの人数が上限より大
    const ERR_PLAN_GUEST_MIN = -3;      // 
    const ERR_PLAN_GUEST_MAX = -4;      // 
    const ERR_VACANT_PLAN = -5;         
    const ERR_VACANT_ROOM = -6;         
    const ERR_VACANT_OPTION = -7;       
    const ERR_HOLIDAY = -8;             // 休館日を含む
    const ERR_PLAN_PERIOD = -9;         // プラン提供期間外
    const ERR_ROOMTYPE_PERIOD = -10;    // 部屋タイプ提供期間外
    const ERR_PARAM = -99;              // パラメータエラー
    
    // 予約モード
    const RESMODE_CHECK = 1;        // チェックのみ
    const RESMODE_TENTATIVE = 2;    // 仮予約
    const RESMODE_REGULAR = 3;      // 本予約
    
    /**
     * 予約エラーコードから文字列を返す
     */
    function getErrorString($i)
    {
        $ret = '';
        switch ($i)
        {
            case Res_model::ERR_ROOM_GUEST_MIN:
                $ret = "１部屋の人数が部屋タイプの受容人数の下限より少ないです。";
                break;
            case Res_model::ERR_ROOM_GUEST_MAX:
                $ret = "１部屋の人数が部屋タイプの受容人数の上限より多いです。";
                break;
            case Res_model::ERR_PLAN_GUEST_MIN:
                $ret = "人数がプランの最小人数より少ないです。";
                break;
            case Res_model::ERR_PLAN_GUEST_MAX:
                $ret = "人数がプランの最大人数より多いです。";
                break;
            case Res_model::ERR_VACANT_PLAN:
                $ret = "プランの空き人数がありません。";
                break;
            case Res_model::ERR_VACANT_ROOM:
                $ret = "空き部屋がありません。";
                break;
            case Res_model::ERR_VACANT_OPTION:
                $ret = "オプションの空きがありません。";
                break;
            case Res_model::ERR_HOLIDAY:
                $ret = "休館日が含まれています。";
                break;
            case Res_model::ERR_PLAN_PERIOD:
                $ret = "プラン提供期間外です。";
                break;
            case Res_model::ERR_ROOMTYPE_PERIOD:
                $ret = "部屋タイプ提供期間外です。";
                break;
            case Res_model::ERR_PARAM:
                $ret = "パラメータエラー";
                break;
        }
        return $ret;
    }
    
    function Res_model()
    {
        parent::Model();
    }
    
    /**
     * 予約を実行する
     *
     * セッションの予約データを参照する。
     * @return 0:正常,整数:予約id,負数:エラー
     */
    public function doRes($resmode, $hotel_model)
    {
        $this->load->library('phpsession');
        
        $reservation = $this->phpsession->get('reservation');
        $hotelid = $reservation['hotelid'];
        $planid = $reservation['planid'];
        $cin = $reservation['cin'];
        $cout = $reservation['cout'];
        $room_num = $reservation['room_num'];
        
        $reservation_detail = $this->phpsession->get('reservation_detail');
        //var_dump($reservation_detail);
        $reservation_options = $this->phpsession->get('reservation_options');
        //var_dump($reservation_options);
        
        $resid = 0;
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('planid', $planid);
        $query = $this->db->get('plans', 1);
        if ($query->num_rows() <= 0)
        {
            return Res_model::ERR_PARAM;
        }
        $plan = $query->row_array();
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('roomtypeid', $plan['roomtypeid']);
        $query = $this->db->get('roomtypes', 1);
        if ($query->num_rows() <= 0)
        {
            return Res_model::ERR_PARAM;
        }
        $roomtype = $query->row_array();
        
        $this->db->where('hotelid', $hotelid);
        $query = $this->db->get('guesttypes');
        if ($query->num_rows() <= 0)
        {
            return Res_model::ERR_PARAM;
        }
        $guesttypes = array();
        foreach ($query->result() as $row)
        {
            $guesttypes[$row->guesttypeid] = $row;
        }
        
        // プラン提供期間内であるかをチェックする
        $int_start_date = intval(substr($plan['start_date'], 0, 4) . substr($plan['start_date'], 5, 2) . substr($plan['start_date'], 8, 2));
        $int_end_date = intval(substr($plan['end_date'], 0, 4) . substr($plan['end_date'], 5, 2) . substr($plan['end_date'], 8, 2));
        $int_cin = intval(substr($cin, 0, 4) . substr($cin, 5, 2) . substr($cin, 8, 2));
        $int_cout = intval(substr($cout, 0, 4) . substr($cout, 5, 2) . substr($cout, 8, 2));
        if ($int_cin < $int_start_date || $int_cout > $int_end_date)
        {
            return Res_model::ERR_PLAN_PERIOD;
        }
        
        // 部屋タイプ提供期間内であるかをチェックする
        $int_start_date = intval(substr($roomtype['start_date'], 0, 4) . substr($roomtype['start_date'], 5, 2) . substr($roomtype['start_date'], 8, 2));
        $int_end_date = intval(substr($roomtype['end_date'], 0, 4) . substr($roomtype['end_date'], 5, 2) . substr($roomtype['end_date'], 8, 2));
        if ($int_cin < $int_start_date || $int_cout > $int_end_date)
        {
            return Res_model::ERR_ROOMTYPE_PERIOD;
        }
        
        $vplan = 0;     // 予約プラン人数
        $plan_n_per = 0;    // プラン人数(%)
        
        foreach ($reservation_detail as $tmp)
        {
            $reservation_detail_1 = $tmp;
            break;
        }
        
        foreach ($reservation_detail_1 as $i)
        {
            $room_n_per = 0;
            foreach ($i as $j)
            {
                $guesttypeid = $j['guesttypeid'];
                $room_n_per += $j['num'] * $guesttypes[$guesttypeid]->roomtype_rate;
                $plan_n_per += $j['num'] * $guesttypes[$guesttypeid]->plan_rate;
            }
            // 一部屋あたりの人数が部屋の許容人数以内であることをチェックする
            if ($room_n_per < $roomtype['guest_min'] * 100)
            {
                return Res_model::ERR_ROOM_GUEST_MIN;
            }
            else if ($room_n_per > $roomtype['guest_max'] * 100)
            {
                return Res_model::ERR_ROOM_GUEST_MAX;
            }
        }
        
        $vroom = $room_num;     // 予約部屋数
        $vplan = floor($plan_n_per / 100);
        
        var_dump($plan_n_per);
        
        // プラン人数がプランの許容最小値から最大値の範囲内である
        // ことをチェックする。
        if ($plan_n_per < $plan['guest_min'] * 100)
        {
            return Res_model::ERR_PLAN_GUEST_MIN;
        }
        else if ($plan_n_per > $plan['guest_max'] * 100)
        {
            return Res_model::ERR_PLAN_GUEST_MAX;
        }
        
        /// ↑
        // ここまでのチェックは、Controller/res/indexでするほうが良い。
        
        //die("stop<br />\n");
        
        // TABLEをlockする。
        $this->db->query("LOCK TABLES hotel_seq WRITE, vacant_plans WRITE, vacant_rooms WRITE, vacant_options WRITE;");
        
        // トランザクション開始
        $this->db->trans_start();
        
        try
        {
            $tmp_y = substr($cin, 0, 4);
            $tmp_m = substr($cin, 5, 2);
            $tmp_d = substr($cin, 8, 2);
            for ($i = 0;;$i++)
            {
                $tmp_date = date('Y-m-d', mktime(0, 0, 0, $tmp_m, $tmp_d + $i, $tmp_y));
                if ($tmp_date >= $cout)
                {
                    break;
                }
                
                // プラン人数空きをチェックし、空きがあれば空き数を更新する。
                $this->db->where('hotelid', $hotelid);
                $this->db->where('planid', $planid);
                $this->db->where('day', $tmp_date);
                $query = $this->db->get('vacant_plans', 1);
                
                $record_flag = false;   // DB:vacantplansにデータがあるか？
                $vp_num = 0;
                if ($query->num_rows() > 0)
                {
                    $record_flag = true;
                    $row = $query->row_array();
                    $vp_num = $row['vp_num'];
                    $vp_vacant = $row['vp_num'] - $row['vp_inuse'];
                }
                else
                {
                    $vp_num = $plan['default_num'];
                    $vp_vacant = $plan['default_num'];
                }
                
                if ($vp_vacant >= $vplan)
                {
                    if ($resmode == Res_model::RESMODE_TENTATIVE || $resmode == Res_model::RESMODE_REGULAR)
                    {
                        if ($record_flag == true)
                        {
                            $this->db->where('hotelid', $hotelid);
                            $this->db->where('planid', $planid);
                            $this->db->where('day', $tmp_date);
                            $data = array('vp_inuse' => $row['vp_inuse'] + $vplan);
                            $this->db->update('vacant_plans', $data);
                        }
                        else
                        {
                            $data = array(
                                'hotelid' => $hotelid,
                                'planid' => $planid,
                                'day' => $tmp_date,
                                'vp_inuse' => $vplan,
                                'vp_num' => $vp_num);
                            $this->db->insert('vacant_plans', $data);
                        }
                    }
                }
                else
                {
                    throw new Exception('', Res_model::ERR_VACANT_PLAN);
                }
                
                // 部屋数の空きをチェックをし、空きがあれば空き数を更新する。
                $this->db->where('hotelid', $hotelid);
                $this->db->where('roomtypeid', $roomtype['roomtypeid']);
                $this->db->where('day', $tmp_date);
                $query = $this->db->get('vacant_rooms', 1);
                $record_flag = false;   // DB:vacant_roomsにデータがあるか？
                $vr_num = 0;
                
                if ($query->num_rows() > 0)
                {
                    $record_flag = true;
                    $row = $query->row_array();
                    $vr_num = $row['vr_num'];
                    $vr_vacant = $row['vr_num'] - $row['vr_inuse'];
                }
                else
                {
                    $vr_num = $roomtype['default_num'];
                    $vr_vacant = $roomtype['default_num'];
                }
                
                if ($vr_vacant >= $vroom)
                {
                    if ($resmode == Res_model::RESMODE_TENTATIVE || $resmode == Res_model::RESMODE_REGULAR)
                    {
                        if ($record_flag == true)
                        {
                            $this->db->where('hotelid', $hotelid);
                            $this->db->where('roomtypeid', $roomtype['roomtypeid']);
                            $this->db->where('day', $tmp_date);
                            $data = array('vr_inuse' => $row['vr_inuse'] + $vroom);
                            $this->db->update('vacant_rooms', $data);
                        }
                        else
                        {
                            $data = array(
                                'hotelid' => $hotelid,
                                'roomtypeid' => $roomtype['roomtypeid'],
                                'day' => $tmp_date,
                                'vr_inuse' => $vroom,
                                'vr_num' => $vr_num);
                            $this->db->insert('vacant_rooms', $data);
                        }
                    }
                }
                else
                {
                    log_message('info', 'Res::ERR_VACANT_ROOM');
                    throw new Exception('', Res::ERR_VACANT_ROOM);
                }
            }   // 日付loop-end
            
            // オプションの空きをチェックをし、空きがあれば空き数を更新する。
            foreach ($reservation_options as $key => $value)
            {
                $day = $key;
                foreach ($value as $key2 => $value2)
                {
                    if ($value2['num'] > 0)
                    {
                        $optionid = $key2;
                        $num = $value2['num'];
                        
                        $this->db->where('hotelid', $hotelid);
                        $this->db->where('optionid', $optionid);
                        $this->db->where('day', $day);
                        $query = $this->db->get('vacant_options', 1);
                        
                        $record_flag = false;   // DB:vacantoptionsにデータがあるか？
                        $vo_num = 0;
                        if ($query->num_rows() > 0)
                        {
                            $record_flag = true;
                            $row = $query->row_array();
                            $vo_num = $row['vo_num'];
                            $vo_vacant = $row['vo_num'] - $row['vo_inuse'];
                        }
                        else
                        {
                            $this->db->where('hotelid', $hotelid);
                            $this->db->where('optionid', $planid);
                            $query2 = $this->db->get('options', 1);
                            $option = $query2->row_array();
                            $vo_num = $option['default_num'];
                            $vo_vacant = $option['default_num'];
                        }
                        
                        if ($vo_vacant >= $num)
                        {
                            if ($resmode == Res_model::RESMODE_TENTATIVE || $resmode == Res_model::RESMODE_REGULAR)
                            {
                                if ($record_flag == true)
                                {
                                    $this->db->where('hotelid', $hotelid);
                                    $this->db->where('optionid', $planid);
                                    $this->db->where('day', $day);
                                    $data = array('vo_inuse' => $row['vo_inuse'] + $num);
                                    $this->db->update('vacant_options', $data);
                                }
                                else
                                {
                                    $data = array(
                                        'hotelid' => $hotelid,
                                        'optionid' => $optionid,
                                        'day' => $day,
                                        'vo_inuse' => $num,
                                        'vo_num' => $vo_num);
                                    $this->db->insert('vacant_options', $data);
                                }
                            }
                        }
                        else
                        {
                            throw new Exception('', Res_model::ERR_VACANT_OPTION);
                        }
                    }
                }
            }
            
            if ($resmode == Res_model::RESMODE_TENTATIVE || $resmode == Res_model::RESMODE_REGULAR)
            {
                $status = 1;
                if ($resmode == Res_model::RESMODE_REGULAR)
                {
                    $status = 2;
                }
                
                $memberid = 0;
                $member = $this->phpsession->get('member');
                if ($member != null)
                {
                    $memberid = $member['memberid'];
                }
                
                $reservation_member = $this->phpsession->get('reservation_member');
                
                // メール送信で「○○様」の表示に使用する。
                $reservation_member_name = $reservation_member['name'];
                
                // $residの取得。
                $this->db->query("UPDATE hotel_seq SET seq_resid = seq_resid + 1 WHERE hotelid = " . $hotelid);
                $this->db->where('hotelid', $hotelid);
                $query = $this->db->get('hotel_seq', 1);
                $resid = $query->row()->seq_resid;
                $rescode = uniqid();
                
                $data = array(
                               'resid' => $resid,
                               'hotelid' => $hotelid,
                               'cin' => $reservation['cin'],
                               'cout' => $reservation['cout'],
                               'stay_days' => $reservation['stay_days'],
                               'memberid' => $memberid,
                               'planid' => $reservation['planid'],
                               'roomtypeid' => $reservation['roomtypeid'],
                               'plan_num' => $vplan,
                               'room_num' => $vroom,
                               'status' => $status,
                               'total_price' => $reservation['total_price'],
                               'created' => date("Y-m-d H:i:s"),
                               'memo' => $reservation_member['memo'],
                               'rescode' => $rescode
                            );
                if ($reservation_member['cinhour'] != '')
                {
                    $data['cin_hour'] = $reservation_member['cinhour'];
                }
                $this->db->insert('reservations', $data);
                log_message('info', 'reservation insert ok');
                
                $data = array(
                                'hotelid' => $hotelid,
                                'resid' => $resid,
                                'email' => $reservation_member['email'],
                                'name' => $reservation_member['name'],
                                'name_kana' => $reservation_member['name_kana'],
                                'postcode' => $reservation_member['postcode'],
                                'prefecture' => $reservation_member['prefecture'],
                                'city' => $reservation_member['city'],
                                'street' => $reservation_member['street'],
                                'phone' => $reservation_member['phone']
                            );
                $this->db->insert('reservation_member', $data);
                log_message('info', 'reservation_member insert ok');

                foreach ($reservation_detail as $i)
                {
                    foreach ($i as $j)
                    {
                        foreach ($j as $tmp)
                        {
                            $data = array(
                                'hotelid' => $hotelid,
                                'resid' => $resid,
                                'day' => $tmp['day'],
                                'room_no'  => $tmp['room_no'],
                                'guesttypeid' => $tmp['guesttypeid'],
                                'daytypeid' => $tmp['daytypeid'],
                                'num' => $tmp['num'],
                                'price' => $tmp['price'],
                                'subtotal' => $tmp['subtotal']
                            );
                            $this->db->insert('reservation_detail', $data);
                        }
                    }
                }
                log_message('info', 'reservation_detail insert ok');
                
                $reservation_options = $this->phpsession->get('reservation_options');
                foreach ($reservation_options as $key => $value)
                {
                    foreach ($value as $key2 => $value2)
                    {
                        $data = array(
                            'hotelid' => $hotelid,
                            'resid' => $resid,
                            'optionid' => $key2,
                            'day' => $key,
                            'option_name' => $value2['option_name'],
                            'price' => $value2['price'],
                            'subtotal' => $value2['price'] * $value2['num'],
                            'unitname' => $value2['unitname'],
                            'num' => $value2['num']);
                        $this->db->insert('reservation_options', $data);
                    }
                }
                log_message('info', 'reservation_options insert ok');
                
                /**
                 * 完了画面に表示する情報はセッションに保存する。
                 * resid, rescode
                 */
                $this->phpsession->save('resid', $resid);
                $this->phpsession->save('rescode', $rescode);
            }
            
            // トランザクション終了
            $this->db->trans_complete();
            log_message('info', 'trans_complete');
            
            // Tableをアンロックする。
            $this->db->query("UNLOCK TABLES;");
            
            if ($resmode == Res_model::RESMODE_TENTATIVE)
            {
                // ユーザ宛仮予約Eメール送信
                if ($memberid == 0)
                {
                    $cancel_url = site_url('cancel/index/' . $hotelid . '/'. $resid . '/' . $rescode);
                }
                else
                {
                    $cancel_url = site_url('my/index');
                }
                
                $mail_subject = $hotel_model->hotel_name . ": 仮予約メール";
                $mail_body = 
                    $reservation_member_name . " 様\n" . 
                    "この度は、" . $hotel_model->hotel_name . "をご予約いただき、ありがとうございます。\n" . 
                    "以下のご予約を承りました。\n" .
                    "後ほど、本予約メールをお送りいたします。\n" .
                    "(場合によってキャンセルしていただくことがあります。\n" .
                    "その際にはキャンセルのメールをお送りします。ご了承ください。)\n";
                log_message('info', 'resid = ' . $resid);
                $mail_body .= $this->makeMailRes($hotelid, $resid);
                $mail_body .= "ご予約の確認、キャンセルはこちらから\n";
                $mail_body .= $cancel_url . "\n";
                log_message('info', $mail_body);
                
                qd_send_mail(
                    'text', 
                    $resmember['email'],
                    $mail_subject,
                    $mail_body,
                    $hotel_model->email);
                
                // 管理者宛仮予約Eメール送信
                // 件名,本文はユーザ宛と同じ。宛先だけ違う。
                qd_send_mail(
                    'text', 
                    $hotel_model->email,
                    $mail_subject,
                    $mail_body,
                    $hotel_model->email);
            }
            else if ($resmode == Res_model::RESMODE_REGULAR)
            {
                // ユーザ宛本予約Eメール送信
                if ($memberid == 0)
                {
                    $cancel_url = site_url('cancel/index/' . $hotelid . '/'. $resid . '/' . $rescode);
                }
                else
                {
                    $cancel_url = site_url('my/index');
                }
                
                $mail_subject = $hotel_model->hotel_name . ": 予約確認メール";
                $mail_body = 
                    $reservation_member_name . " 様\n" . 
                    "この度は、" . $hotel_model->hotel_name . "をご予約いただき、ありがとうございます。\n" . 
                    "以下のご予約を承りました。\n";
                $mail_body .= $this->makeMailRes($hotelid, $resid);
                $mail_body .= "ご予約の確認、キャンセルはこちらから\n" .
                $mail_body .= $cancel_url . "\n";
                log_message('info', $mail_body);
                
                qd_send_mail(
                    'text', 
                    $resmember['email'],
                    $mail_subject,
                    $mail_body,
                    $hotel_model->email);
                
                // 管理者宛本予約Eメール送信
                // 件名,本文はユーザ宛と同じ。宛先だけ違う。
                qd_send_mail(
                    'text', 
                    $hotel_model->email,
                    $mail_subject,
                    $mail_body,
                    $hotel_model->email);
            }
            
            return $resid;
        }
        catch (Exception $e)
        {
            $this->db->trans_rollback();
            
            // Tableをアンロックする。
            $this->db->query("UNLOCK TABLES;");
            
            log_message('info', 'doRes ' . $e->getCode());
            return $e->getCode();
        }
    }
    
    /**
     * 仮予約->本予約
     */
    public function confirmRes($hotelid, $resid, $hotel_model)
    {
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $this->db->where('status >', 0);
        $query = $this->db->get('reservation', 1);
        if ($query->num_rows() <= 0)
        {
            return;
        }
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $this->db->update('reservation', array('status' => 2));
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $query = $this->db->get('reservation_member', 1);
        if ($query->num_rows() <= 0)
        {
            return;
        }
        $reservation_member = $query->row_array();
        
        $mail_subject = $hotel_model->hotel_name . ": 本予約メール";
        $mail_body = 
            $reservation_member['name'] . " 様\n" . 
            "この度は、" . $hotel_model->hotel_name . "をご予約いただき、ありがとうございます。\n" . 
            "以下のご予約を承りました。\n" .
            "お越しをお待ちいたしております。\n";
        $mail_body .= $this->makeMailRes($hotelid, $resid);
        $mail_body .= "ご予約の確認、キャンセルはこちらから\n";
        $mail_body .= $cancel_url . "\n";
        
        log_message('info', 'mail_subjet:' . $mail_subject);
        log_message('info', $mail_body);
        /**
         * 本予約メールはユーザにのみ送信する。
         * 管理者は自身で本処理に変更していてわかっているので、
         * 送信しなくて良い。
         */
        qd_send_mail(
            'text', 
            $resmember['email'],
            $mail_subject,
            $mail_body,
            $hotel_model->email);
    }
    
    /**
     * 予約キャンセル
     * @param int $resid 予約Id
     * @param int $mode  1:ユーザによるキャンセル
                         2:管理者によるキャンセル(本予約不可)
                         3:本予約取消
     */
    public function cancel($hotelid, $resid, $mode, $hotel_model)
    {
        /**
         * DBのデータは削除されるため、メールの予約内容を先に作成しておく。
         * (要検討)
         * 後の処理で予約内容作成と同様の処理をするので、
         * 高速化を考えるときは後処理と同時にしたほうが良い？
         */
        $mailRes = $this->makeMailRes($hotelid, $resid);
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $this->db->where('status >', 0);
        $query = $this->db->get('reservations', 1);
        if ($query->num_rows() <= 0)
        {
            return -1;
        }
        $data = $query->row_array();

        $row = $query->row_array();
        $planid = $row['planid'];
        $roomtypeid = $row['roomtypeid'];
        $cin = $row['cin'];
        $cout = $row['cout'];
        $room_num = $row['room_num'];
        $plan_num = $row['plan_num'];
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('planid', $planid);
        $query = $this->db->get('plans', 1);
        if ($query->num_rows() <= 0)
        {
            return - 2;
        }
        $plan = $query->row_array();

        $this->db->where('hotelid', $hotelid);
        $this->db->where('roomtypeid', $roomtypeid);
        $query = $this->db->get('roomtypes', 1);
        if ($query->num_rows() <= 0)
        {
            return -3;
        }
        $roomtype = $query->row_array();
        
        // TABLEをlockする。
        $this->db->query("LOCK TABLES vacant_plans WRITE, vacant_rooms WRITE, vacant_options WRITE;");

        $this->db->trans_start();
        try
        {
            $tmp_y = substr($cin, 0, 4);
            $tmp_m = substr($cin, 5, 2);
            $tmp_d = substr($cin, 8, 2);
            for ($i = 0;;$i++)
            {
                $tmp_date = date('Y-m-d', mktime(0, 0, 0, $tmp_m, $tmp_d + $i, $tmp_y));
                if ($tmp_date >= $cout) {
                    break;
                }
                
                // vacant_plansの空きを元に戻す。
                $this->db->where('hotelid', $hotelid);
                $this->db->where('planid', $planid);
                $this->db->where('day', $tmp_date);
                $query = $this->db->get('vacant_plans', 1);
                if ($query->num_rows() > 0)
                {
                    $row = $query->row_array();
                    $vp_inuse = $row['vp_inuse'];
                    $this->db->where('hotelid', $hotelid);
                    $this->db->where('planid', $planid);
                    $this->db->where('day', $tmp_date);
                    $a = array('vp_inuse' => $vp_inuse - $plan_num);
                    $this->db->update('vacant_plans', $a);
                }
                else
                {
                    log_message('error', 'vacant_plans');
                }
                
                // vacant_roomsの空きを元に戻す。
                $this->db->where('hotelid', $hotelid);
                $this->db->where('roomtypeid', $roomtypeid);
                $this->db->where('day', $tmp_date);
                $query = $this->db->get('vacant_rooms', 1);
                if ($query->num_rows() > 0)
                {
                    $row = $query->row_array();
                    $vr_inuse = $row['vr_inuse'];
                    $this->db->where('hotelid', $hotelid);
                    $this->db->where('roomtypeid', $roomtypeid);
                    $this->db->where('day', $tmp_date);
                    $a = array('vr_inuse' => $vr_inuse - $room_num);
                    $this->db->update('vacant_rooms', $a);
                }
                else
                {
                    log_message('error', 'vacant_rooms');
                }
            }
            
            // vacant_optionの空きを元に戻す。
            $this->db->where('hotelid', $hotelid);
            $this->db->where('resid', $resid);
            
            $query = $this->db->get('reservation_options');
            foreach ($query->result() as $row)
            {
                $day = $row->day;
                $optionid = $row->optionid;
                $num = $row->num;
                
                $this->db->where('hotelid', $hotelid);
                $this->db->where('day', $day);
                $this->db->where('optionid', $optionid);
                $query2 = $this->db->get('vacant_options', 1);
                if ($query2->num_rows() > 0)
                {
                    $tmp = $query2->row_array();
                    $vo_num = $tmp['vo_num'];
                    $vo_inuse = $tmp['vo_inuse'] - $num;
                    $data = array('vo_inuse' => $vo_inuse);
                    $this->db->where('hotelid', $hotelid);
                    $this->db->where('day', $day);
                    $this->db->where('optionid', $optionid);
                    $this->db->update('vacant_options', $data);
                }
                else
                {
                    log_message('error', 'vacant_options');
                }
            }
            
            /**
             * reservationのstatusを0にする。
             * reservation_member,reservation_detail,reservation_options
             * は変更せずに残しておく。
             */
            $this->db->where('hotelid', $hotelid);
            $this->db->where('resid', $resid);
            $a = array('status' => 0);
            $this->db->update('reservations', $a);
            
        }
        catch (Exception $ex)
        {
            $this->db->trans_rollback();
            
            // Tableをアンロックする。
            $this->db->query("UNLOCK TABLES;");
            
            return -10;
        }
        
        $this->db->trans_complete();
        
        // Tableをアンロックする。
        $this->db->query("UNLOCK TABLES;");
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $query = $this->db->get('reservation_member', 1);
        if ($query->num_rows() <= 0)
        {
            return -12;
        }
        $reservation_member = $query->row_array();
        
        // Eメール送信
        
        $mail_subject = $hotel_model->hotel_name . ": 予約キャンセルメール";
        $mail_body = 
            $reservation_member['name'] . " 様\n" . 
            "この度は、" . $hotel_model->hotel_name . "をご利用いただき、ありがとうございます。\n" . 
            "以下のご予約のキャンセルを承りました。\n" .
            "またのご利用をお待ちしております。\n";
        $mail_body .= $mailRes;
        
        log_message('info', 'mail_subject:' . $mail_subject);
        log_message('info', $mail_body);
        
        /**
         * キャンセルメールの送信
         */
        qd_send_mail(
            'text', 
            $resmember['email'],
            $mail_subject,
            $mail_body,
            $hotel_model->email);
        qd_send_mail(
            'text',
            $hotel_model->email,
            $mail_subject,
            $mail_body,
            $hotel_model->email);
        
        return 0;
    }
    
    /**
     * 予約メールの予約内容作成
     * @param
     * @return string メール本文, エラー時は''を返す。
     */
    private function makeMailRes($hotelid, $resid)
    {
        $this->load->model('Common_model');
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $this->db->where('status >', 0);
        $query = $this->db->get('reservations', 1);
        if ($query->num_rows() <= 0)
        {
            return '';
        }
        $reservation = $query->row_array();
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $query = $this->db->get('reservation_member', 1);
        if ($query->num_rows() <= 0)
        {
            return '';
        }
        $reservation_member = $query->row_array();
        
        $planid = $reservation['planid'];
        $roomtypeid = $reservation['roomtypeid'];
        $this->db->where('hotelid', $hotelid);
        $this->db->where('planid', $planid);
        $query = $this->db->get('plans', 1);
        if ($query->num_rows() <= 0)
        {
            return '';
        }
        $plan = $query->row_array();
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('roomtypeid', $roomtypeid);
        $query = $this->db->get('roomtypes', 1);
        if ($query->num_rows() <= 0)
        {
            return '';
        }
        $roomtype = $query->row_array();
        
        $this->db->where('resid', $resid);
        $this->db->order_by('day DESC');
        $query = $this->db->get('reservation_detail');
        if ($query->num_rows() <= 0)
        {
            return '';
        }
        $reservation_detail = $query->result_array();
        
        // $reservation_detail を以下の配列に変換する。
        // $tmp_reservation_detail[$day][$room_no][$guesttypeid] = array()
        $tmp_reservation_detail = array();
        foreach ($reservation_detail as $tmp)
        {
            $day = $tmp['day'];
            $room_no = $tmp['room_no'];
            $guesttypeid = $tmp['guesttypeid'];
            $tmp_reservation_detail[$day][$room_no][$guesttypeid] = $tmp;
        }
        
        $this->db->where('resid', $resid);
        $this->db->order_by('day DESC');
        $query = $this->db->get('reservation_options');
        if ($query->num_rows() <= 0)
        {
            $reservation_options = array();
        }
        $reservation_options = $query->result_array();
        
        // $reservation_options を以下の配列に変換する。
        // $tmp_reservation_options[$day][$optionid] = array()
        $tmp_reservation_options = array();
        foreach ($reservation_options as $tmp)
        {
            $day = $tmp['day'];
            $optionid = $tmp['optionid'];
            $tmp_reservation_options[$day][$optionid] = $tmp;
        }
        
        $this->db->where('hotelid', $hotelid);
        $query = $this->db->get('guesttypes');
        $guesttypes = array();
        foreach ($query->result_array() as $row)
        {
            $guesttypes[$row['guesttypeid']] = $row;
        }
        
        $res_body = 
            "ご予約内容\n" .
            "---------------------\n" .
            "予約ID         : " . $reservation['resid'] . "\n" .
            "予約コード     : " . $reservation['rescode'] . "\n" .
            "予約受付日     : " . $reservation['created'] . "\n" .
            "チェックイン   : " . $reservation['cin'] . ' (' . $this->Common_model->week($reservation['cin']) . ')' . "\n" .
            "チェックアウト : " . $reservation['cout'] . ' (' . $this->Common_model->week($reservation['cout']) . ')' . "\n" .
            "宿泊日数       : " . $reservation['stay_days'] . ' 日' . "\n" .
            "プラン         : " . $plan['plan_name'] . "\n" .
            "部屋タイプ     : " . $roomtype['roomtype_name'] . "\n" .
            "部屋数         : " . $reservation['room_num'] . " 部屋" . "\n" .
            "人数           : " . "\n";
            
        $str = '';
        foreach ($tmp_reservation_detail as $tmp)
        {
            $tmp_reservation_detail_1 = $tmp;
            break;
        }
        $m = 0;
        foreach ($tmp_reservation_detail_1 as $i)
        {
            $str .= ($m + 1) . '部屋' . "\n";
            foreach ($i as $j)
            {
                $guesttypeid = $j['guesttypeid'];
                $guesttype_name = $guesttypes[$guesttypeid]['guesttype_name'];
                $num = $j['num'];
                $str .= $guesttype_name . " x " . $num . "人";
                $str .= "\n";
            }
            $m++;
        }
        $res_body .= $str;
        
        $res_body .= "料金 : " . "\n";
        
        $str = '';
        $n = 0;
        foreach ($tmp_reservation_detail as $i)
        {
            $str .= ($n + 1) . '日目' . "\n";
            $m = 0;
            foreach ($i as $j)
            {
                $str .= ($m + 1) . '部屋' . "\n";
                foreach ($j as $k)
                {
                    $guesttypeid = $k['guesttypeid'];
                    $guesttype_name = $guesttypes[$guesttypeid]['guesttype_name'];
                    $num = $k['num'];
                    $subtotal = $k['subtotal'];
                    $str .= $guesttype_name . " x " . $num . "人 = " . number_format($subtotal) . "円";
                    $str .= "\n";
                }
                $m++;
            }
            $n++;
        }
        $res_body .= $str;
        
        if (count($reservation_options) > 0)
        {
            $res_body .= "オプション : " . "\n";
            
            $str = '';
            foreach ($tmp_reservation_options as $i)
            {
                $str .= ($n + 1) . '日目' . "\n";
                foreach ($i as $option)
                {
                    $str .= $option['option_name'] . ' : ';
                    $str .= number_format($option['price']) . '円 x ';
                    $str .=  $option['num'] . ' ' . $option['unitname'] . ' = ';
                    $option_subtotal = $option['price'] * $option['num'];
                    $str .=  number_format($option_subtotal) . ' 円' . "\n";
                }
                $n++;
            }
            $res_body .= $str;
        }
        
        $res_body .= "合計 : " . number_format($reservation['total_price']) . "円" . "\n";
        $res_body .= "\n";
        
        $res_body .= "申込者氏名: " . $reservation_member['name'] . "\n";
        $res_body .= "---------------------\n";
        $res_body .= "\n";
        
        log_message('info', "res_body = \n" . $res_body);
        
        return $res_body;
    }
    
    /**
     * 
     * $mode = 0:ユーザ, 1:管理者
     */
    public function contentHtml($hotelid, $resid, $mode = 0)
    {
        $this->db->where('hotelid', $hotelid);
        $query = $this->db->get('guesttypes');
        $guesttypes = array();
        foreach ($query->result_array() as $row)
        {
            $guesttypes[$row['guesttypeid']] = $row;
        }
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $query = $this->db->get('reservations', 1);
        if ($query->num_rows() <= 0)
        {
            return;
        }
        $reservation = $query->row_array();
        
        $planid = $reservation['planid'];
        $this->db->where('hotelid', $hotelid);
        $this->db->where('planid', $planid);
        $query = $this->db->get('plans', 1);
        if ($query->num_rows() <= 0)
        {
            return;
        }
        $plan = $query->row_array();
        
        $roomtypeid = $reservation['roomtypeid'];
        $this->db->where('hotelid', $hotelid);
        $this->db->where('roomtypeid', $roomtypeid);
        $query = $this->db->get('roomtypes', 1);
        if ($query->num_rows() <= 0)
        {
            return;
        }
        $roomtype = $query->row_array();
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $query = $this->db->get('reservation_member', 1);
        if ($query->num_rows() <= 0)
        {
            return;
        }
        $reservation_member = $query->row_array();
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $this->db->order_by('day DESC');
        $this->db->order_by('room_no DESC');
        $query = $this->db->get('reservation_detail');
        $reservation_detail = $query->result_array();
        
        $this->db->where('hotelid', $hotelid);
        $this->db->where('resid', $resid);
        $this->db->order_by('day DESC');
        $query = $this->db->get('reservation_options');
        $reservation_options = $query->result_array();
        
        // $reservation_detail を以下の配列に変換する。
        // $tmp_reservation_detail[$day][$room_no][$guesttypeid] = array()
        $tmp_reservation_detail = array();
        foreach ($reservation_detail as $tmp)
        {
            $day = $tmp['day'];
            $room_no = $tmp['room_no'];
            $guesttypeid = $tmp['guesttypeid'];
            $tmp_reservation_detail[$day][$room_no][$guesttypeid] = $tmp;
        }

        // $reservation_options を以下の配列に変換する。
        // $tmp_reservation_options[$day][$optionid] = array()
        $tmp_reservation_options = array();
        foreach ($reservation_options as $tmp)
        {
            $day = $tmp['day'];
            $optionid = $tmp['optionid'];
            $tmp_reservation_options[$day][$optionid] = $tmp;
        }
        
        return $this->_contentHtml($reservation, $plan, $roomtype, $guesttypes, $tmp_reservation_detail, $reservation_member, $tmp_reservation_options, $mode);
    }
    
    
    /**
     *
     */
    private function _contentHtml($reservation, $plan, $roomtype, $guesttypes, $tmp_reservation_detail, $reservation_member, $tmp_reservation_options, $mode)
    {
        ob_start();
?>




<table class="inp">
<tr><th width="240px">予約id / 予約コード :</th>
<td><?php echo $reservation['resid'] . ' / ' . $reservation['rescode'] ?></td></tr>
<tr><th>予約日 :</th>
<td><?php echo $reservation['created'] ?></td></tr>
<?php if ($mode == 1) { ?>
<tr><th>ステータス :</th>
<td>
<?php
if ($reservation['status'] == 1) { echo '仮予約'; }
else if ($reservation['status'] == 2) { echo '本予約'; }
else if ($reservation['status'] == 0) { echo 'キャンセル'; }
if ($reservation['status'] == 1 || $reservation['status'] == 2)
{
    echo '&nbsp;(';
    if ($reservation['status'] == 1)
    {
        // 仮予約 -> 本予約
        echo '<a href="' . site_url('admin/res/changeStatus/' . $hotelid . '/' . $resid . '/2') . '">→本予約<a/>';
        echo '&nbsp;';
    }
    echo '&nbsp;';
    if ($reservation['status'] == 1 || $reservation['status'] == 2)
    {
        // -> キャンセル
        echo '<a href="' . site_url('admin/res/changeStatus/' . $hotelid . '/' . $resid . '/0') . '">→キャンセル<a/>';
    }
    echo ')';
}
?>
</td></tr>
<?php } ?>
<tr><th>チェックイン～チェックアウト :</th>
<td>
<?php
echo $reservation['cin'];
echo '(' . $this->Common_model->week($reservation['cin']) . ')';
echo '～';
echo $reservation['cout'];
echo '(' . $this->Common_model->week($reservation['cout']) . ')';
?>
</td></tr>
<tr><th>プラン :</th>
<td><?php echo $plan['plan_name'] ?></td></tr>
<tr><th>部屋タイプ :</th>
<td><?php echo $roomtype['roomtype_name'] ?></td></tr>
<tr><th>部屋数 :</th>
<td><?php echo $reservation['room_num'] . '部屋'; ?></td></tr>
<tr><th>人数 :</th>
<td>
<?php 
foreach ($tmp_reservation_detail as $tmp)
{
    $tmp_reservation_detail_1 = $tmp;
    break;
}
$m = 0;
foreach ($tmp_reservation_detail_1 as $i)
{
    echo ($m + 1) . '部屋' . "<br />\n";
    foreach ($i as $j)
    {
        $guesttypeid = $j['guesttypeid'];
        $guesttype_name = $guesttypes[$guesttypeid]['guesttype_name'];
        $num = $j['num'];
        echo $guesttype_name . " x " . $num . "人";
        echo "<br />\n";
    }
    $m++;
}
?></td></tr>
<tr><th>料金 :</th>
<td>
<?php
$n = 0;
foreach ($tmp_reservation_detail as $i)
{
    echo ($n + 1) . '日目' . "<br />\n";
    $m = 0;
    foreach ($i as $j)
    {
        echo ($m + 1) . '部屋' . "<br />\n";
        foreach ($j as $k)
        {
            $guesttypeid = $k['guesttypeid'];
            $guesttype_name = $guesttypes[$guesttypeid]['guesttype_name'];
            $num = $k['num'];
            $subtotal = $k['subtotal'];
            echo $guesttype_name . " x " . $num . "人 = " . number_format($subtotal) . "円";
            echo "<br />\n";
        }
        $m++;
    }
    $n++;
}
?>
</td>
</tr>

<tr><th>オプション :</th>
<td>
<?php
$n = 0;
foreach ($tmp_reservation_options as $i)
{
    echo ($n + 1) . '日目' . "<br />\n";
    foreach ($i as $option)
    {
        echo $option['option_name'] . ' : ';
        echo number_format($option['price']) . '円 x ';
        echo $option['num'] . ' ' . $option['unitname'] . ' = ';
        $option_subtotal = $option['price'] * $option['num'];
        echo number_format($option_subtotal) . ' 円' . "<br />\n";
    }
    $n++;
}
?>
</td>
</tr>

<tr><th>合計 :</th>
<td><?php echo number_format($reservation['total_price']) . '円' ?></td></tr>
<tr><th>チェックイン時刻 :</th>
<td><?php echo substr($reservation['cin_hour'], 0, 5); ?></td></tr>
<tr><th>備考 :</th>
<td><?php echo $reservation['memo']; ?></td></tr>

</table>
<br />
<table class="inp">
<tr><th width="240px">氏名（かな） :</th>
<td><?php echo $reservation_member['name'] . '（' . $reservation_member['name_kana'] . '）' ?></td></tr>
<tr><th>住所 :</th>
<td>
<?php
echo '〒' . $reservation_member['postcode'] . "<br />";
echo $reservation_member['prefecture'] . ' ' . $reservation_member['city'];
?></td></tr>
<tr><th>電話 :</th>
<td>
<?php echo $reservation_member['phone']; ?></td></tr>
<tr><th>Eメールアドレス :</th>
<td>
<?php echo $reservation_member['email']; ?></td></tr>

</table>
        
        
<?php
        $contents = ob_get_contents();
        ob_end_clean();
        return $contents;
    }
    
    
}
