<?php

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

class Import_model extends CI_Model {

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

    public function business() {
        //1.检查最后的商务订单SN，用来判断新的订单SN开始查询位置
        $sql = " 
            
            SELECT TOP 1 
                   o.o_source_sn
            FROM   orders o
            WHERE  o.o_source = N'CH网前'
            ORDER BY
                   o.o_sn DESC
    ";
        $query = $this->DEST->query($sql);
        $last_coli_sn = 0;
        if ($query->result()) {
            $row = $query->row();
            $last_coli_sn = $row->o_source_sn;
        }
        //2.查询新的商务订单，条件是分配给图兰朵的线路
        $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
                    FROM   BIZ_ConfirmLineInfo bcli
                    WHERE  bcli.COLI_sourcetype = 32090
                           AND bcli.DeleteFlag = 0
                           AND bcli.COLI_WebCode IN ('CHT')
                           AND bcli.COLI_SN > ?
                    ORDER BY
                           bcli.COLI_SN ASC
    ";
        $query_newtour = $this->HT->query($sql, array($last_coli_sn));
        if ($query_newtour) {
            $tour_list = $query_newtour->result();
            //3.循环把新订单插入目的地系统的订单表
            foreach ($tour_list as $item_tour) {
                //添加主订单表
                $o_orderno = $item_tour->COLI_ID; //生成订单号
                $o_ordertype = '一地线路';
                $o_status = '新订单';
                $o_source = $this->get_source_type($item_tour->COLI_sourcetype);  //今后可能有TA、北京目的地等来源类型,根据COLI_sourcetype来判断
                $o_source_sn = $item_tour->COLI_SN;
                $o_contractors = '1'; //操作者ID，用来识别地接社或者个人
                $o_memo = $item_tour->COLI_Memo;
                $o_date = $item_tour->COLI_ApplyDate;
                $o_device = $this->get_source_device($item_tour->COLI_OrderSource);
                $insert_order_id = $this->add_orders($o_orderno, $o_ordertype, $o_status, $o_source, $o_source_sn, $o_contractors, $o_memo, $o_device, $o_date);
                //添加主订单表 end

                if ($insert_order_id) {

                    //添加付款信息到支付表
                    $sql = "
                        SELECT bgai.GAI_SN,
                               bgai.GAI_COLI_SN,
                               bgai.GAI_Type,
                               bgai.GAI_SQJE,
                               bgai.GAI_SQDate,
                               bgai.GAI_SSDate,
                               bgai.GAI_Money,
                               bgai.GAI_SQJECurrency,
                               ISNULL(bgai.GAI_CusName,'') GAI_CusName,
                               bgai.GAI_CusEmail,
                               bgai.DeleteFlag,
                               bgai.LastEditTime,
                               ISNULL(bgai.GAI_Memo,'') GAI_Memo
                        FROM   BIZ_GroupAccountInfo bgai
                        WHERE  bgai.GAI_COLI_SN = ?
                        ORDER BY
                               bgai.GAI_SN ASC    
                        ";
                    $query_bgai = $this->HT->query($sql, array($item_tour->COLI_SN));
                    if ($query_bgai) {
                        foreach ($query_bgai->result() as $item_bgai) {
                            $pm_money = $item_bgai->GAI_SQJE;
                            $pm_money_currency = $item_bgai->GAI_SQJECurrency;
                            $pm_money_type = $this->get_payment_type($item_bgai->GAI_Type);
                            $pm_money_date = $item_bgai->GAI_SQDate;
                            $pm_rmb = $item_bgai->GAI_Money;
                            $pm_rmb_date = $item_bgai->GAI_SSDate;
                            $pm_customer = $item_bgai->GAI_CusName;
                            $pm_customer_email = $item_bgai->GAI_CusEmail;
                            $pm_company = 'cht';
                            $pm_memo = $item_bgai->GAI_Memo;
                            $pm_delete = $item_bgai->DeleteFlag;
                            $pm_date = $item_bgai->LastEditTime;
                            $this->add_payments($insert_order_id, $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);
                        }
                    }
                    //添加付款信息到支付表 end
                    //增加订单子表
                    $sql = "
                            SELECT 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,
                                   ISNULL(bcld.COLD_Describe,'') COLD_Describe
                            FROM   BIZ_ConfirmLineDetail bcld
                            WHERE  bcld.COLD_COLI_SN = ?
                                   AND bcld.DeleteFlag = 0
                            ORDER BY
                                   bcld.COLD_SN ASC
                    ";
                    $query_bcld = $this->HT->query($sql, array($item_tour->COLI_SN));
                    if ($query_bcld) {
                        foreach ($query_bcld->result() as $item_bcld) {
                            $occ_o_sn = $insert_order_id;
                            $occ_circuitcode = $item_bcld->COLD_MemoText; //商务订单线路code  lzq
                            $occ_adultcount = $item_bcld->COLD_PersonNum;
                            $occ_childrencount = $item_bcld->COLD_ChildNum;
                            $occ_babycount = $item_bcld->COLD_BabyNum;
                            $package_info = $this->package_info($occ_circuitcode);
                            $occ_days = empty($package_info) ? '-1' : $package_info->PAG_NeedTime;
                            $occ_startdate = $item_bcld->COLD_StartDate;
                            $occ_detailtext = $item_tour->COLI_OrderDetailText . '------------------------' . $item_bcld->COLD_Describe; //添加主表的客人其他需求
                            $occ_price = $item_bcld->COLD_TotalPrice;
                            $order_circuit_id = $this->add_order_circuit($occ_o_sn, $occ_circuitcode, $occ_adultcount, $occ_childrencount, $occ_babycount, $occ_days, $occ_startdate, $occ_detailtext, $occ_price);

                            if ($order_circuit_id) {
// 插入客人表
                                $sql = "
                                        SELECT bbp.BPE_SN,
                                        ISNULL(bbp.BPE_FirstName, '')      BPE_FirstName,
                                        ISNULL(bbp.BPE_MiddleName, '')     BPE_MiddleName,
                                        ISNULL(bbp.BPE_LastName, '')       BPE_LastName,
                                        bbp.BPE_Passport,
                                        bbp.BPE_Nationality,
                                        bbp.BPE_SEX,
                                        bbp.BPE_PassExpdate
                                        FROM   BIZ_BookPeople                     bbp
                                        WHERE  EXISTS (
                                        SELECT TOP 1 1
                                        FROM   BIZ_BookPeopleList bbpl
                                        WHERE  bbpl.BPL_COLD_SN = ?
                                        AND bbpl.BPL_BPE_SN = bbp.BPE_SN
                                        )
                                        ";
                                $query_member = $this->HT->query($sql, array($item_bcld->COLD_SN));
                                if ($query_member) {
                                    foreach ($query_member->result() as $item_member) {
                                        $cus_firstname = $item_member->BPE_FirstName;
                                        $cus_middlename = $item_member->BPE_MiddleName;
                                        $cus_lastname = $item_member->BPE_LastName;
                                        $cus_nationality = $item_member->BPE_Nationality;
                                        $cus_passport = $item_member->BPE_Passport;
                                        $cus_passport_validdate = $item_member->BPE_PassExpdate;
                                        $cus_phone = '';
                                        $cus_email = '';
                                        $cus_backup_email='';
                                        $cus_sex = ($item_member->BPE_SEX == 1) ? '男' : '女';
                                        $this->add_customer($order_circuit_id, 'order_circuit', $cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passport_validdate, $cus_phone, $cus_email,$cus_backup_email, $cus_sex);
                                    }
                                }
                            }
                        }
                    }
                    //4.1增加订单子表end
//4.4插入联系人详细信息表
                    $sql = "
                                    SELECT 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
                                    FROM   BIZ_GUEST  bg
                                    WHERE  bg.GUT_SN = ?
                            ";
                    $query_guest = $this->HT->query($sql, array($item_tour->COLI_GUT_SN));
                    if ($query_guest) {
                        foreach ($query_guest->result() as $item_guest) {
                            $cus_firstname = $item_guest->GUT_FirstName;
                            $cus_middlename = '';
                            $cus_lastname = $item_guest->GUT_LastName;
                            $cus_nationality = $item_guest->GUT_NationalityID;
                            $cus_passport = $item_guest->GUT_Passport;
                            $cus_passport_validdate = '';
                            $cus_phone = $item_guest->GUT_TEL;
                            $cus_email = $item_guest->GUT_Email;
                            $cus_backup_email = $item_guest->GUT_Email2;
                            $cus_sex = ($item_guest->GUT_SEX == 1) ? '男' : '女';
                            $this->add_customer($insert_order_id, 'orders', $cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passport_validdate, $cus_phone, $cus_email, $cus_backup_email, $cus_sex);
                        }
                    }
                }
            }
        }
    }

    public function tour() {
        //1.检查最后的传统订单SN，用来判断新的订单SN开始查询位置
        $sql = " 
            
            SELECT TOP 1 
                   o.o_source_sn
            FROM   orders o
            WHERE  o.o_source <> N'CH网前'
            ORDER BY
                   o.o_sn DESC
    ";
        $query = $this->DEST->query($sql);
        $last_coli_sn = 0;
        if ($query->result()) {
            $row = $query->row();
            $last_coli_sn = $row->o_source_sn;
        }
        //2.查询新的传统订单，条件是分配给图兰朵的线路
        $sql = " 
            SELECT cli.COLI_SN,
                   cli.COLI_ID,
                   cli.COLI_Memo,
                   cli.COLI_OrderDetailText,
                   cli.COLI_PersonNum,
                   cli.COLI_ChildNum,
                   cli.COLI_BabyNum,
                   cli.COLI_Days,
                   cli.COLI_OrderStartDate,
                   cli.COLI_ApplyDate,
                   cli.COLI_Price,
                   cli.COLI_sourcetype,
                   cli.COLI_servicetype,
                   ISNULL(cli.COLI_OrderSource,'') COLI_OrderSource
            FROM   ConfirmLineInfo cli
            WHERE  cli.COLI_WebCode IN ('cht')
                   AND cli.COLI_OPI_ID = 318
                   AND cli.COLI_Sended = 1
                   AND cli.DeleteFlag = 0
                   AND cli.COLI_SN > ?
            ORDER BY
                   cli.COLI_SN ASC
    ";
        $query_newtour = $this->HT->query($sql, array($last_coli_sn));
        if ($query_newtour) {
            //3.循环把新订单插入目的地系统的订单表
            foreach ($query_newtour->result() as $item_tour) {
                $o_orderno = $item_tour->COLI_ID; //生成订单号
                $o_ordertype = '一地线路';
                $o_status = '新订单';
                $o_source = $this->get_source_type($item_tour->COLI_sourcetype);
                $o_source_sn = $item_tour->COLI_SN;
                $o_contractors = '1'; //操作者ID，用来识别地接社或者个人
                $o_memo = $item_tour->COLI_Memo;
                $o_date = $item_tour->COLI_ApplyDate;
                $o_device = $this->get_source_device($item_tour->COLI_OrderSource);
                $insert_order_id = $this->add_orders($o_orderno, $o_ordertype, $o_status, $o_source, $o_source_sn, $o_contractors, $o_memo, $o_device, $o_date);
                //4.插入主表成功后，添加订单子表和客人信息表
                if ($insert_order_id) {

                    //增加订单子表
                    $occ_o_sn = $insert_order_id;
                    $occ_circuitcode = $item_tour->COLI_Memo;
                    $occ_adultcount = $item_tour->COLI_PersonNum;
                    $occ_childrencount = $item_tour->COLI_ChildNum;
                    $occ_babycount = $item_tour->COLI_BabyNum;
                    $occ_days = $item_tour->COLI_Days;
                    $occ_startdate = $item_tour->COLI_OrderStartDate;
                    $occ_detailtext = $item_tour->COLI_OrderDetailText;
                    $occ_price = $item_tour->COLI_Price;
                    $query_circuit_id = $this->add_order_circuit($occ_o_sn, $occ_circuitcode, $occ_adultcount, $occ_childrencount, $occ_babycount, $occ_days, $occ_startdate, $occ_detailtext, $occ_price);


                    //插入客人信息表
                    $sql = "
                        SELECT ISNULL(mi.MEI_FirstName,'') MEI_FirstName,
                               ISNULL(mi.MEI_MiddleName,'') MEI_MiddleName,
                               ISNULL(mi.MEI_LastName,'') MEI_LastName,
                               mi.MEI_Country,
                               mi.MEI_PassportNo,
                               mi.MEI_PassportValidDate,
                               mi.MEI_Phone,
                               mi.MEI_MailList,
                               mi.MEI_Mail,
                               mi.MEI_Gender,
                               cu.CUL_IsLinkMan
                        FROM   MEmberInfo mi
                               INNER JOIN CUstomerList cu
                                    ON  cu.CUL_CUI_SN = mi.MEI_SN
                        WHERE  EXISTS (
                                   SELECT TOP 1 *
                                   FROM   CUstomerList cl
                                   WHERE  cl.CUL_CUI_SN = mi.MEI_SN
                                          AND cl.CUL_COLI_SN = cu.CUL_COLI_SN
                                          AND cl.CUL_COLI_SN = ?
                               )
                        ";
                    $query_member = $this->HT->query($sql, array($item_tour->COLI_SN));
                    if ($query_member) {
                        foreach ($query_member->result() as $item_member) {
                            $cus_firstname = $item_member->MEI_FirstName;
                            $cus_middlename = $item_member->MEI_MiddleName;
                            $cus_lastname = $item_member->MEI_LastName;
                            $cus_nationality = $item_member->MEI_Country;
                            $cus_passport = $item_member->MEI_PassportNo;
                            $cus_passport_validdate = $item_member->MEI_PassportValidDate;
                            $cus_phone = $item_member->MEI_Phone;
                            $cus_email = $item_member->MEI_MailList;
                            $cus_backup_email = $item_member->MEI_Mail;
                            $cus_sex = ($item_member->MEI_Gender == 100001) ? '男' : '女';
                            if ($item_member->CUL_IsLinkMan) {
                                $this->add_customer($query_circuit_id, 'orders', $cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passport_validdate, $cus_phone, $cus_email, $cus_backup_email, $cus_sex);
                            } else {
                                $this->add_customer($query_circuit_id, 'order_circuit', $cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passport_validdate, $cus_phone, $cus_email, $cus_backup_email, $cus_sex);
                            }
                        }
                    }
                }
            }
        }
    }

    function add_customer($oc_subordersn, $oc_type, $cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passport_validdate, $cus_phone, $cus_email, $cus_backup_email, $cus_sex) {
        $sql = "
                INSERT INTO customer
                (
                        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
                )
                VALUES
                (N?,N?,N?,?,?,?,?,?,?,N?,0,getdate())
                        ";
        $query_member_insert = $this->DEST->query($sql, array($cus_firstname, $cus_middlename, $cus_lastname, $cus_nationality, $cus_passport, $cus_passport_validdate, $cus_phone, $cus_email, $cus_backup_email, $cus_sex));
        if ($query_member_insert) {
            $query_member_id = $this->DEST->last_id('customer');
            $sql = "
                    INSERT INTO ordercus
                    ( oc_subordersn,oc_cus_sn,oc_type)
                    VALUES
                    (?,?,?)
                            ";
            $this->DEST->query($sql, array($oc_subordersn, $query_member_id, $oc_type));
            return true;
        }
        return false;
    }

    function add_orders($o_orderno, $o_ordertype, $o_status, $o_source, $o_source_sn, $o_contractors, $o_memo, $o_device, $o_date) {
        $sql = "
            IF NOT EXISTS(SELECT TOP 1 1 FROM orders WHERE o_source=N'$o_source' AND o_source_sn=$o_source_sn)
            INSERT INTO orders
              (
                o_orderno,o_ordertype,o_status,o_source, o_source_sn,o_contractors,o_delete,o_memo,o_device, o_date
              )
            VALUES
              (?,N?,N?,N?,?,?,0,?,N?,? )
            ";
        $query = $this->DEST->query($sql, array($o_orderno, $o_ordertype, $o_status, $o_source, $o_source_sn, $o_contractors, $o_memo, $o_device, $o_date));
        if ($query) {
            return $this->DEST->last_id('orders');
        }
        return false;
    }

    function add_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) {
        $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
                (?,?,?,?,?,?,?,N?,?,N?,N?,?,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, $pm_date));
        if ($query) {
            return $this->DEST->last_id('payments');
        }
        return false;
    }

    function add_order_circuit($occ_o_sn, $occ_circuitcode, $occ_adultcount, $occ_childrencount, $occ_babycount, $occ_days, $occ_startdate, $occ_detailtext, $occ_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_date
              )
              VALUES
              ( ?, ?, ?,?,?, ?,?, N?,?,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_price));
        if ($query) {
            return $this->DEST->last_id('order_circuit');
        }
        return false;
    }

    //获取支付方式文字，用编号转换
    function get_payment_type($type_code) {
        $payment_types = $this->lang->line('payment_types');
        if (isset($payment_types[$type_code])) {
            return $payment_types[$type_code];
        }
        return '其他';
    }

    //获取订单来源方式，用编号转换
    function get_source_type($type_code) {
        $source_types = $this->lang->line('order_sourcetypes');
        if (isset($source_types[$type_code])) {
            return $source_types[$type_code];
        }
        return $type_code . '';
    }

    function package_info($pag_code) {
        if(empty($pag_code)){
            return false;
        }
        $sql = "
            SELECT TOP 1 bpi.PAG_SN,
                    bpi.PAG_Code,
                    bpi.PAG_Type,
                    bpi.PAG_NeedTime,
                    bpi.PAG_URL
             FROM   BIZ_PackageInfo bpi
             WHERE  bpi.PAG_Code = ?
            ";
        $query = $this->HT->query($sql, array($pag_code));
        if ($query->num_rows() > 0) {
            $row = $query->row();
            return $row;
        } else {
            return FALSE;
        }
    }

    //获取客人使用的设备
    function get_source_device($type_code) {
        $source_devices = $this->lang->line('order_devices');
        if (isset($source_devices[$type_code])) {
            return $source_devices[$type_code];
        }
        return $type_code . '';
    }

}