<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Order_model extends CI_Model {

    function __construct() {
        parent::__construct();
        //$this->DEST = $this->load->database('DEST', TRUE);
        $this->HT = $this->load->database('HT', TRUE);
    }

    //获取订单列表
    public function get_order_list($o_sn = false, $o_orderno = false) {
        $mapsql = '';
        if ($o_sn && is_numeric($o_sn)) {
            $mapsql = " AND o_sn=$o_sn ";
        }
        $mapsql.=$o_orderno == false ? '' : " AND o_orderno like '%$o_orderno%' ";

        $sql = "SELECT top 50 
                        o_sn,
                        o_orderno,
                        o_ordertype,
                        o_status,
                        o_source,
                        o_contractors,
                        o_delete,
                        o_date,
                        o_memo,
                        o_device,
                        occ_sn,
                        occ_o_sn,
                        occ_circuitcode,
                        occ_adultcount,
                        occ_childrencount,
                        occ_babycount,
                        occ_days,
                        occ_startdate,
                        occ_detailtext,
                        occ_price,
                        occ_edited_price,
                        occ_date,
                        cus_firstname,
                        cus_middlename,
                        cus_lastname
                 FROM   orders 
                        LEFT JOIN order_circuit ON o_sn=occ_o_sn 
                        LEFT JOIN ordercus ON o_sn=oc_subordersn AND oc_type='orders' LEFT JOIN customer ON oc_cus_sn=cus_sn
               WHERE  o_delete!=1 $mapsql order by o_date desc";
        $query = $this->DEST->query($sql, array());
        $result = $query->result();
        return $result;
    }
	
	//插入导游信息进入翰特 lzq
	public function update_confirmInfo($update_info,$coli_id){
		$sql = "UPDATE vendorArrangeState
										SET    VAS_ConfirmInfo = ISNULL( VAS_ConfirmInfo,'' ) + N?
										WHERE  EXISTS (
												   SELECT TOP 1 1
												   FROM   BIZ_ConfirmLineInfo bcli
												   WHERE  bcli.COLI_ID = ?
														  AND VAS_GRI_SN = bcli.COLI_GRI_SN
											   )";
		$query = $this->HT->query($sql, array($update_info, $coli_id));
        return $query;
	}
	
    // 根据订单号获产品信息 汉特表 lzq
    public function get_product_by_coli_id($coli_id) {
        $sql = "SELECT 
                       bcli.COLI_SN,
					   bcli.COLI_ID,
					   bcli.COLI_GUT_SN,
					   bcli.COLI_Memo,
					   bcli.COLI_OrderDetailText,
					   bcli.COLI_ApplyDate,
					   bcli.COLI_Price,
					   bcli.COLI_sourcetype,
					   bcli.COLI_servicetype,
					   ISNULL(bcli.COLI_OrderSource,'') COLI_OrderSource,
					   bcld.COLD_SN,
					   bcld.COLD_TotalPrice,
					   bcld.COLD_Count,
					   bcld.COLD_PersonNum,
					   bcld.COLD_ChildNum,
					   bcld.COLD_BabyNum,
					   bcld.COLD_StartDate,
					   bcld.COLD_EndDate,
					   bcld.COLD_MemoText,
					   bcld.COLD_ServiceSN,
					   bpi.PAG_Code,
					   bpi.PAG_NeedTime
                FROM   BIZ_ConfirmLineInfo bcli
					   LEFT JOIN BIZ_ConfirmLineDetail bcld
							ON  bcld.COLD_COLI_SN = bcli.COLI_SN
					   LEFT JOIN BIZ_PackageInfo bpi
							ON  bpi.PAG_SN = bcld.COLD_ServiceSN
               WHERE bcld.COLD_SN is not null AND bcli.COLI_ID=? ";
        $query = $this->HT->query($sql, array($coli_id));
        $result = $query->result();
        return $result;
    }

    // 根据产品号获取订单及联系人信息  汉特表lzq
    public function get_order_by_coli_id($coli_id) {
        $sql = "
					SELECT bcli.COLI_SN,
						   bcli.COLI_ID,
						   bcli.COLI_GUT_SN,
						   bcli.COLI_State,
						   bcli.COLI_sourcetype,
						   bcli.COLI_OrderSource,
						   bcld.COLD_SN,
						   bg.GUT_SN,
						   ISNULL(bg.GUT_FirstName, '') GUT_FirstName,
						   ISNULL(bg.GUT_LastName, '') GUT_LastName,
						   bg.GUT_NationalityID,
						   bg.GUT_Passport,
						   bg.GUT_TEL,
						   bg.GUT_Email,
						   bg.GUT_Email2,
						   bg.GUT_SEX,
						   bbp.BPE_PassExpdate,
						   bbp.BPE_Passport
					FROM   BIZ_GUEST bg
						   LEFT JOIN BIZ_ConfirmLineInfo bcli
								ON  bcli.COLI_GUT_SN = bg.GUT_SN
						   LEFT JOIN BIZ_ConfirmLineDetail bcld
								ON  bcld.COLD_COLI_SN = bcli.COLI_SN
						   LEFT JOIN BIZ_BookPeopleList bbpl
								ON  bbpl.BPL_COLD_SN = bcld.COLD_SN
						   LEFT JOIN BIZ_BookPeople bbp
								ON  bbpl.BPL_BPE_SN = bbp.BPE_SN
					WHERE  EXISTS (
							   SELECT TOP 1 1
							   FROM   BIZ_BookPeopleList bbpl
							   WHERE  bbpl.BPL_COLD_SN = bcld.COLD_SN
									  AND bbpl.BPL_BPE_SN = bbp.BPE_SN
						   )
						   AND bcli.COLI_ID is not null AND bcli.COLI_ID = ?
                ";
        $query = $this->HT->query($sql, array($coli_id));
        $result = $query->result();
        if (!empty($result)) {
            $result = $result[0];
        }
        return $result;
    }

    //根据订单订单号 获取付款记录   汉特表lzq
    public function get_pay_by_coli_id($coli_id) {
        $sql = "
			SELECT bgai.GAI_SN,
				   bgai.GAI_COLI_SN,
				   bgai.GAI_Type,
				   bgai.GAI_SQJE,
				   bgai.GAI_SQDate,
				   bgai.GAI_SSDate,
				   bgai.GAI_SSJE,
				   bgai.GAI_SQJECurrency,
				   ISNULL(bgai.GAI_CusName,'') GAI_CusName,
				   bgai.GAI_CusEmail,
				   bgai.DeleteFlag,
				   bgai.LastEditTime,
				   ISNULL(bgai.GAI_Memo,'') GAI_Memo,
				   bcli.COLI_SN
			FROM   BIZ_GroupAccountInfo bgai
				   LEFT JOIN BIZ_ConfirmLineInfo bcli
						ON bcli.COLI_SN = bgai.GAI_COLI_SN
			WHERE bgai.GAI_SN is not null AND bcli.COLI_ID = ?
			ORDER BY
				   bgai.GAI_SN ASC    
			";
        $query = $this->HT->query($sql, array($coli_id));
        $result = $query->result();
        return $result;
    }

    //根据日期查询订单信息 汉特表 lzq
    public function get_date_info($start_date, $end_date) {

        $sql = "SELECT 
                        bcli.COLI_ID,
						bcli.COLI_State,
                        bcli.COLI_ApplyDate,
                        bcld.COLD_MemoText,
                        bcld.COLD_StartDate,
                        bcld.COLD_PersonNum,
                        bcld.COLD_ChildNum,
                        bcld.COLD_BabyNum,
                        bcld.COLD_ServiceSN,
                        bpi.PAG_Code,
                        bpi.PAG_NeedTime,
                        bpoi.POI_Hotel
             FROM   BIZ_ConfirmLineInfo bcli
                        LEFT JOIN BIZ_ConfirmLineDetail bcld
                                     ON  bcli.COLI_SN = bcld.COLD_COLI_SN
                        LEFT JOIN BIZ_PackageInfo bpi
                                     ON  bpi.PAG_SN = bcld.COLD_ServiceSN
                        LEFT JOIN BIZ_PackageOrderInfo bpoi
                                     ON  bpoi.POI_COLD_SN = bcld.COLD_SN
             WHERE  bcld.COLD_StartDate BETWEEN ? AND
                        ?
                        AND bcli.COLI_sourcetype = 32090
                        AND bcli.DeleteFlag = 0
                        AND bcli.COLI_WebCode IN ('CHT')
                        AND EXISTS (
                                        SELECT TOP 1 1
                                        FROM   BIZ_GroupAccountInfo 
                                                       bgai
                                        WHERE  bgai.GAI_COLI_SN = bcli.COLI_SN
                                )
             ORDER BY
                        bcld.COLD_StartDate ASC
                        ";

        $query = $this->HT->query($sql, array($start_date, $end_date));
        $result = $query->result();
        return $result;
    }

    //获取首页订单列表ID
    public function get_order_list_ids($o_orderno = false, $status = false, $ordertype = false, $ordercolno = false, $startdate = false, $startdate2 = false, $applydate = false, $applydate2 = false, $source = false) {
        $mapsql = $o_orderno == false ? '' : " AND o_orderno like '%$o_orderno%' ";
        $mapsql.=$status == false ? '' : " AND o_status='$status' ";
        $mapsql.=$ordertype == false ? '' : " AND o_ordertype='$ordertype' ";
        $mapsql.=$applydate == false ? '' : " AND (o_date BETWEEN '$applydate' AND '$applydate2') ";
        $mapsql.=$source == false ? '' : " AND o_source like '%$source%' ";

        $mapsql2 = $ordercolno == false ? '' : " AND occ_circuitcode like '%$ordercolno%' ";
        $mapsql2.=$startdate == false ? '' : " AND (occ_startdate BETWEEN '$startdate' AND '$startdate2') ";
        $joinsql = $mapsql3 = '';
        if ($ordercolno != false || $startdate != false) {
            $mapsql3 = " and occ_sn in (select min(occ_sn) from order_circuit WHERE 1=1 $mapsql2 group by occ_o_sn) ";
            $joinsql = " LEFT JOIN order_circuit on o_sn=occ_o_sn ";
        }

        $sql = "SELECT TOP 200 o_sn from orders LEFT JOIN ordercus ON o_sn=oc_subordersn AND oc_type='orders' inner JOIN customer ON oc_cus_sn=cus_sn $joinsql WHERE o_delete!=1 $mapsql $mapsql3 order by o_date desc";
        $query = $this->DEST->query($sql);
        $result = $query->result();
        return $result;
    }

    //订单首页列表数据
    public function get_order_list_by_idstr($order_id_str) {
        $sql = "SELECT    o_sn,
                        o_orderno,
                        o_ordertype,
                        o_status,
                        o_source,
                        o_contractors,
                        o_delete,
                        o_date,
                        o_memo,
                        occ_sn,
                        occ_o_sn,
                        occ_circuitcode,
                        occ_adultcount,
                        occ_childrencount,
                        occ_babycount,
                        occ_days,
                        occ_startdate,
                        occ_detailtext,
                        (select SUM(occ_price) from order_circuit where occ_o_sn=o_sn) as occ_price,
                        occ_date ,
                        cus_firstname,
                        cus_middlename,
                        cus_lastname
                   from orders 
                        LEFT JOIN order_circuit on o_sn=occ_o_sn 
                        LEFT JOIN ordercus ON o_sn=oc_subordersn AND oc_type='orders'
                        inner JOIN customer ON oc_cus_sn=cus_sn
                  where occ_sn in (select min(occ_sn) from order_circuit group by occ_o_sn) 
                        and o_sn in($order_id_str)
                  order by o_date desc";
        $query = $this->DEST->query($sql);
        $result = $query->result();
        return $result;
    }

    //根据订单主表ID获取客人列表
    public function get_guest_list($o_sn) {
        $sql = "SELECT    cus_sn,
                        cus_firstname,
                        cus_middlename,
                        cus_lastname,
                        cus_nationality,
                        cus_passport,
                        cus_passport_validdate,
                        cus_phone,
                        cus_email,
                        cus_backup_email,
                        cus_sex,
                        cus_delete,
                        cus_date,
                        occ_sn
                FROM    order_circuit 
                        LEFT JOIN ordercus ON occ_sn=oc_subordersn AND oc_type='order_circuit'
                        LEFT JOIN customer ON oc_cus_sn=cus_sn
               WHERE cus_delete!=1 AND occ_o_sn=?";
        $query = $this->DEST->query($sql, array($o_sn));
        return $query->result();
    }

    public function get_link_user($o_sn) {
        $sql = "SELECT top 1   cus_sn,
                        cus_firstname,
                        cus_middlename,
                        cus_lastname,
                        cus_nationality,
                        cus_passport,
                        cus_passport_validdate,
                        cus_phone,
                        cus_email,
                        cus_backup_email,
                        cus_sex,
                        cus_delete,
                        cus_date,
                        cus_islink,
                        o_sn
                FROM    orders 
                        LEFT JOIN ordercus ON o_sn=oc_subordersn AND oc_type='orders'
                        LEFT JOIN customer ON oc_cus_sn=cus_sn
               WHERE cus_delete!=1 AND o_sn=?";
        $query = $this->DEST->query($sql, array($o_sn));
        $result = $query->result();
        if (!empty($result)) {
            $result = $result[0];
        }
        return $result;
    }

    public function get_detail_by_orderno($o_orderno) {
        $sql = "SELECT 
                        o_sn,
                        o_orderno,
                        o_ordertype,
                        o_status,
                        o_source,
                        o_contractors,
                        o_delete,
                        o_date,
                        occ_sn,
                        occ_o_sn,
                        occ_circuitcode,
                        occ_adultcount,
                        occ_childrencount,
                        occ_babycount,
                        occ_days,
                        occ_startdate,
                        occ_detailtext,
                        occ_price,
                        occ_edited_price,
                        occ_date
                FROM    orders 
                        LEFT JOIN order_circuit ON o_sn=occ_o_sn 
               WHERE occ_sn is not null AND o_orderno=?";
        $query = $this->DEST->query($sql, array($o_orderno));
        $result = $query->result();
        if ($result) {
            $result = $result[0];
        }
        return $result;
    }

    public function get_payments($pm_o_sn = false, $pm_sn = false) {
        $mapsql = '1=1';
        $mapsql.=$pm_o_sn ? " and pm_o_sn in($pm_o_sn) " : "";
        $mapsql.=$pm_sn ? " and pm_sn=$pm_sn " : "";
        $sql = "SELECT    pm_sn,
                        pm_o_sn,
                        pm_money,
                        pm_money_currency,
                        pm_money_type,
                        pm_money_date,
                        pm_rmb,
                        pm_rmb_date,
                        pm_customer,
                        pm_customer_email,
                        pm_company,
                        pm_memo,
                        pm_delete,
                        pm_date 
                FROM payments
                WHERE $mapsql AND pm_delete!=1";
        $query = $this->DEST->query($sql);
        $result = $query->result();
        if ($pm_sn && !empty($result)) {
            $result = $result[0];
        }
        return $result;
    }

    public function save_orders($o_orderno, $o_ordertype, $o_status, $o_source, $o_contractors, $o_deleteflag, $o_memo, $o_device = '') {
        $sql = "INSERT INTO orders (o_orderno,o_ordertype,o_status,o_source,o_contractors,o_delete,o_memo,o_device,o_date) VALUES (?,?,?,?,?,?,?,?,GETDATE())";
        $query = $this->DEST->query($sql, array($o_orderno, $o_ordertype, $o_status, $o_source, $o_contractors, $o_deleteflag, $o_memo, $o_device));
        $insertid = $this->DEST->last_id('orders');
        return $insertid;
    }

    public function save_order_circuit($occ_o_sn, $occ_circuitcode, $occ_adultcount, $occ_childrencount, $occ_babycount, $occ_days, $occ_startdate, $occ_detailtext, $occ_totalprice, $occ_edited_price) {
        $sql = "INSERT INTO order_circuit (occ_o_sn,occ_circuitcode,occ_adultcount,occ_childrencount,occ_babycount,occ_days,occ_startdate,occ_detailtext,occ_price,occ_edited_price,occ_date) VALUES (?,?,?,?,?,?,?,?,?,?,GETDATE())";
        $query = $this->DEST->query($sql, array($occ_o_sn, $occ_circuitcode, $occ_adultcount, $occ_childrencount, $occ_babycount, $occ_days, $occ_startdate, $occ_detailtext, $occ_totalprice, $occ_edited_price));
        $insertid = $this->DEST->last_id('order_circuit');
        return $insertid;
    }

    public function save_customer($cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passportvaliddate, $cus_phone, $cus_email, $cus_sex, $cus_deleteflag, $cus_backup_email = '') {
        $sql = "INSERT INTO customer (cus_firstname,cus_middlename,cus_lastname,cus_nationality,cus_passport,cus_passport_validdate,cus_phone,cus_email,cus_sex,cus_delete,cus_backup_email,cus_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,GETDATE())";
        $query = $this->DEST->query($sql, array($cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passportvaliddate, $cus_phone, $cus_email, $cus_sex, $cus_deleteflag, $cus_backup_email));
        $insertid = $this->DEST->last_id('customer');
        return $insertid;
    }

    public function save_ordercus($oc_subordersn, $cus_sn, $oc_type) {
        $sql = "INSERT INTO ordercus (oc_subordersn,oc_cus_sn,oc_type) VALUES (?,?,?)";
        $query = $this->DEST->query($sql, array($oc_subordersn, $cus_sn, $oc_type));
        $insertid = $this->DEST->last_id('ordercus');
        return $insertid;
    }

    public function save_payments($pm_o_sn, $pm_money, $pm_money_currency, $pm_money_type, $pm_money_date, $pm_rmb, $pm_rmb_date, $pm_customer, $pm_customer_email, $pm_company, $pm_memo, $pm_delete = 0) {
        $sql = "INSERT INTO payments (pm_o_sn,pm_money,pm_money_currency,pm_money_type,pm_money_date,pm_rmb,pm_rmb_date,pm_customer,pm_customer_email,pm_company,pm_memo,pm_delete,pm_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,GETDATE())";
        $query = $this->DEST->query($sql, array($pm_o_sn, $pm_money, $pm_money_currency, $pm_money_type, $pm_money_date, $pm_rmb, $pm_rmb_date, $pm_customer, $pm_customer_email, $pm_company, $pm_memo, $pm_delete));
        $insertid = $this->DEST->last_id('payments');
        return $insertid;
    }

    public function update_orders($o_sn, $o_orderno, $o_ordertype, $o_status, $o_source, $o_contractors, $o_memo, $o_device) {
        $sql = "UPDATE orders SET 
                                o_orderno=?,
                                o_ordertype=?,
                                o_status=?,
                                o_source=?,
                                o_contractors=?,
                                o_memo=?,
                                o_device=? 
                        WHERE   o_sn=?";
        $query = $this->DEST->query($sql, array($o_orderno, $o_ordertype, $o_status, $o_source, $o_contractors, $o_memo, $o_device, $o_sn));
        return $query;
    }

    public function set_orders($o_sn, $filed, $value) {
        $sql = "UPDATE orders SET $filed=? WHERE o_sn =? ";
        $query = $this->DEST->query($sql, array($value, $o_sn));
        return $query;
    }

    public function update_order_circuit($occ_sn, $occ_circuitcode, $occ_adultcount, $occ_childrencount, $occ_babycount, $occ_days, $occ_startdate, $occ_detailtext, $occ_totalprice, $occ_edited_price) {
        $sql = "UPDATE order_circuit 
                 SET occ_circuitcode=?,
                     occ_adultcount=?,
                     occ_childrencount=?,
                     occ_babycount=?,
                     occ_days=?,
                     occ_startdate=?,
                     occ_detailtext=?,
                     occ_price=?,
                     occ_edited_price=?
                WHERE occ_sn=?";
        $query = $this->DEST->query($sql, array($occ_circuitcode, $occ_adultcount, $occ_childrencount, $occ_babycount, $occ_days, $occ_startdate, $occ_detailtext, $occ_totalprice, $occ_edited_price, $occ_sn));
        return $query;
    }

    public function update_customer($cus_sn, $cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passportvaliddate, $cus_phone, $cus_email, $cus_sex, $cus_backup_email = '') {
        $sql = "UPDATE customer 
                 SET cus_firstname=?,
                     cus_middlename=?,
                     cus_lastname=?,
                     cus_nationality=?,
                     cus_passport=?,
                     cus_passport_validdate=?,
                     cus_phone=?,
                     cus_email=?,
                     cus_sex=?, 
                     cus_backup_email=?
                WHERE cus_sn=?";
        $query = $this->DEST->query($sql, array($cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passportvaliddate, $cus_phone, $cus_email, $cus_sex, $cus_backup_email, $cus_sn));
        return $query;
    }
	

    public function update_payments($pm_sn, $pm_o_sn, $pm_money, $pm_money_currency, $pm_money_type, $pm_money_date, $pm_rmb, $pm_rmb_date, $pm_customer, $pm_customer_email, $pm_company, $pm_memo, $pm_delete = 0) {
        $sql = "UPDATE payments 
                 SET pm_o_sn=?,
                    pm_money=?,
                    pm_money_currency=?,
                    pm_money_type=?,
                    pm_money_date=?,
                    pm_rmb=?,
                    pm_rmb_date=?,
                    pm_customer=?,
                    pm_customer_email=?,
                    pm_company=?,
                    pm_memo=?,
                    pm_delete=?,
                    pm_date=GETDATE()
                WHERE pm_sn=?";
        $query = $this->DEST->query($sql, array($pm_o_sn, $pm_money, $pm_money_currency, $pm_money_type, $pm_money_date, $pm_rmb, $pm_rmb_date, $pm_customer, $pm_customer_email, $pm_company, $pm_memo, $pm_delete, $pm_sn));
        return $query;
    }

    public function delete_orders($o_sn) {
        $sql = "UPDATE orders SET o_delete=1 WHERE o_sn=?";
        $query = $this->DEST->query($sql, array($o_sn));
        return $query;
    }

    public function delete_guest($cus_sn) {
        $sql = "UPDATE customer SET cus_delete=1 WHERE cus_sn=?";
        $query = $this->DEST->query($sql, array($cus_sn));
        return $query;
    }

    public function delete_payments($pm_sn) {
        $sql = "UPDATE payments SET pm_delete=1 WHERE pm_sn=?";
        $query = $this->DEST->query($sql, array($pm_sn));
        return $query;
    }

}