PHPExcel导入excel数据到数据库中

绝地灬酷狼 2022-08-05 08:23 317阅读 0赞

以下内容是基于ThinkPHP框架。。

简述：导入的excel表格文件由四个工作表组成，其中前面三个工作表根据客户名称关联起来，它们共同构成数据库中12个表的每条数据，第四个工作表是独立的产品信息表。12个表都是关联表，含有其他表的外键。

数据库字段结构设计是全部不能为空，主键id都是自增。

1、总体实现思路

为每个数据库表创建一个数组来保存Excel表格中读取的数据，数组的键等于数据库字段名，数组的值等于excel对应的列的内容。先读取第一个工作表的数据，根据工作表行数增加来读取每条数据保存到数组中，然后使用

M()->add($data)方法添加数组到数据库中。通过PHPExcel类库中的$phpexcel->getSheet(0)方法可以读取每个工作表。

2、如何关联数据表的外键

TP中调用模型的add()方法添加数据成功后会返回插入的记录的主键id，保存该id。

一个客户名称存在于3个工作表的数据通过客户名称关联起来，也就是3个工作表中都有客户名称。所以插入其他工作表的数据到数据库时需要根据客户名称获得关联的表的主键。

$condition\[‘name’\] = $customer\_name;

$res = M(‘customer\_info’)->where($condition)->find();

$data\_procurement\_plan\[‘customer\_id’\] = $res\[‘id’\];//关联customer\_info表的id

3、添加相同的客户名称的记录

客户名称在客户信息表中是唯一存在的所以该字段设置唯一索引。Excel中含有数据库中已经存在的客户名称时更新记录，调用删除方法删除存在的12个表中的记录，再添加新记录。

4、excel有的列为空，但是数据库不能有空字段，设置默认值

定义一个函数对数组进行检查，有空的键给设默认值

public function setArrayDefault(&$arr)\{

foreach($arr a4 $k => $v)\{

arr\[$k\] = isset($v)?$v:’’;

return $arr;

5、数据库表中的每个字段在excel中都为空时，不插入该记录。例如无历史合作信息。

if( trim($data\_old\_cooperation\[‘cooperatiom\_time’\]) !== ‘’)

|| trim($data\_old\_cooperation\[‘cooperatiom\_type’\]) !== ‘’

|| trim($data\_old\_cooperation\[‘cooperatiom\_rank’\]) !== ‘’ )\{

// 添加记录代码

...

三个字段只要有一个有数据就插入，否则不插入。

trim() 函数是去除字符串两端的空格，防止excel中内容为空格的记录插入。

6、excel中的日期处理

很奇怪在excel中的日期2015-09-09在php代码中会变成42256,得不到正确的日期。

处理如下：首先在excel模版的有日期的列设置单元格格式，选中列头，右键->单元格格式->日期->格式码，设置为YYYY-MM-DD。

function formatData($data)\{

$data = trim($data);// 去除空格

if(empty($data)) return “0000-00-00”;

$n = intval(($data - 25569)\*3600\*24);// 转换成1970年以来的秒数

$format\_data = gmdata(‘Y-m-d’,$n);// 格式化的时间

return $format\_data;

$data 是从excel中读取的内容;

return “0000-00-00” 是设置自定义的默认值，如果excel内容为空的话，插入到数据库中得到的结果是1899-12-30,该值是输入日期为空时的默认时间。

注意事项：

1、数据库字段名称统一用小写，代码中字段名要可数据库中的大小写对应，否则可能插入数据失败。

2、PHPExcel插件放在Public目录下，没找到已经定义的指向public文件的路径，所以在index.php中定义了PUBLIC\_PATH的路径。

完整代码如下：

控制器代码：
    <?php 
    namespace Home\Controller; 
    use Think\Controller; 
    class PhpexcelController extends Controller { 
     
        private  static  function init(){ 
            // 设置字符集编码 
            header('Content-type:text/html;charset=utf-8'); 
            // 加载类库 
            include_once PHPEXCEL_PATH.'/PHPExcel.php'; 
            include_once PHPEXCEL_PATH.'/PHPExcel/Style/Fill.php'; 
        } 
     
        /** 
         * 导入一个excel表的多个sheet的数据 
         */ 
        public function importmoreexcel(){ 
            self::init(); 
            if($_FILES["morefile"]["error"] > 0){ 
                echo 'ERROR:'.$_FILES["morefile"]["error"]."处错误<br/>请重新导入..."; 
            }else{ 
                // 注意：上传中文文件名出现乱码，暂时不能上传中文文件 
                //$filename = iconv("GB2312","UTF-8",$_FILES["morefile"]["name"]); 
                // 文件存放路径 
                $filepath =UPLOAD_PATH."/".$_FILES["morefile"]["name"]; 
                move_uploaded_file($_FILES["morefile"]["tmp_name"],$filepath); 
                echo '文件保存在：'.$filepath."<br/>"; 
     
                // 创建读文件对象 
                $objReader = new \PHPExcel_Reader_Excel2007(); 
                if(!$objReader->canRead($filepath)){ 
                    $objReader = new \PHPExcel_Reader_Excel5(); 
                    if(!$objReader->canRead($filepath)){ 
                        echo '上传的不是excel文件...'; 
                        return; 
                    } 
                } 
     
                // 读取第一张表,客户基本信息excel表 
                $phpexcel = $objReader->load($filepath); 
                $current_excel = $phpexcel->getSheet(0); 
                $allRow = $current_excel->getHighestRow();// 得到总行数 
     
                // 插入记录成功的条数 
                $count_success = 0; 
                // 插入记录失败的条数 
                $count_fail = 0; 
                // 更新的记录树木 
                $count_upload = 0; 
                // 实例化模型 
                $m_customer_info = M('customer_info'); 
                $m_customer_data = M('customer_data'); 
                $m_old_cooperation = M('old_cooperation'); 
     
                for($row = 3;$row <= $allRow; $row++){ 
                    $data_customer_data = array(); 
                    $data_customer_info = array();// 客户基本信息 
                    $data_old_cooperation = array(); 
     
                    $data_customer_info['name'] = $current_excel->getCell('B'.$row)->getValue(); 
                    $data_customer_info['user_name'] = $current_excel->getCell('C'.$row)->getValue(); 
                    $data_customer_info['type'] = $current_excel->getCell('D'.$row)->getValue(); 
                    $data_customer_info['account'] = $current_excel->getCell('E'.$row)->getValue(); 
                    $data_customer_info['con_name'] = $current_excel->getCell('F'.$row)->getValue(); 
                    $data_customer_info['phone'] = $current_excel->getCell('G'.$row)->getValue(); 
                    $data_customer_info['qq'] = $current_excel->getCell('H'.$row)->getValue(); 
                    $data_customer_info['wechat'] = $current_excel->getCell('I'.$row)->getValue(); 
                    $data_customer_info['email'] = $current_excel->getCell('J'.$row)->getValue(); 
                    $data_customer_info['company_address'] = $current_excel->getCell('K'.$row)->getValue(); 
                    $data_customer_info['company_phone'] = $current_excel->getCell('L'.$row)->getValue(); 
                    $data_customer_info['company_email'] = $current_excel->getCell('M'.$row)->getValue(); 
                    $data_customer_info['web'] = $current_excel->getCell('N'.$row)->getValue(); 
                    $data_customer_info['money'] = $current_excel->getCell('O'.$row)->getValue(); 
                    $data_customer_info['is_listed'] = $current_excel->getCell('P'.$row)->getValue(); 
                    $data_customer_info['market_value'] = $current_excel->getCell('Q'.$row)->getValue(); 
                    $data_customer_info['sales_month'] = $current_excel->getCell('R'.$row)->getValue(); 
                    $data_customer_info['storage_facilities'] = $current_excel->getCell('S'.$row)->getValue(); 
                    $data_customer_info['it_systems'] = $current_excel->getCell('T'.$row)->getValue(); 
     
                    // 客户附加信息 
                    $data_customer_data['plan'] = $current_excel->getCell('U'.$row)->getValue(); 
                    $data_customer_data['other_supplier'] = $current_excel->getCell('V'.$row)->getValue(); 
                    $data_customer_data['cooperation_proportion'] = $current_excel->getCell('W'.$row)->getValue(); 
     
                    $data_customer_info['create_time'] = date('Y-m-d h:i:s',time()); 
                    $data_customer_info['description'] = $current_excel->getCell('X'.$row)->getValue(); 
     
                    // 历史合作信息 
                    $data_old_cooperation['cooperation_time'] = formatData($current_excel->getCell('Y'.$row)->getValue()); 
     
                    $data_old_cooperation['cooperation_type'] = $current_excel->getCell('Z'.$row)->getValue(); 
                    $data_old_cooperation['cooperation_rank'] = $current_excel->getCell('AA'.$row)->getValue(); 
     
                    // 添加前判断 
                    $this->setArrayDefault($data_customer_info); 
                    $this->setArrayDefault($data_customer_data); 
     
                    // 判断客户名称是否已经存在，存在则删除以前的记录，再添加记录 
                    $condition_c_i['name'] = $data_customer_info['name']; 
                    $result = $m_customer_info->where($condition_c_i)->find(); 
                    if(!empty($result)){ 
                        $this->delete($result['id']); 
                        $count_upload ++; 
                    } 
     
     
                    // 插入数据到数据库中 
                    // 插入到客户基本资料表 
                    $customer_id = $m_customer_info->add($data_customer_info);// 保存插入的id，关联其他表 
     
                    if($customer_id == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
                    // 插入数据到客户附加信息表 
                    $data_customer_data['customer_id'] = $customer_id; 
                    if($m_customer_data->add($data_customer_data) == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
     
                    /** 
                     * 插入到历史合作信息 
                     * 如果历史合作信息字段全没设置则不插入记录到数据库 
                     */ 
                    if(trim($data_old_cooperation['cooperation_time']) !== '' 
                        || trim($data_old_cooperation['cooperation_type']) !== '' 
                        || trim($data_old_cooperation['cooperation_rank']) !== ''){ 
     
                        $this->setArrayDefault($data_old_cooperation); 
                        $data_old_cooperation['customer_id'] = $customer_id; 
                        if($m_old_cooperation->add($data_old_cooperation) == false){ 
                            $count_fail ++; 
                        }else{ 
                            $count_success ++; 
                        } 
                    } 
     
                } 
     
     
                // 关键订单excel表 
                $current_excel = $phpexcel->getSheet(1); 
                $allRow = $current_excel->getHighestRow();// 得到总行数 
     
                $m_procurement_plan = M('procurement_plan'); 
                $m_key_order = M('key_order'); 
                $m_supplier = M('supplier'); 
                $m_r_order = M('r_order_supplier'); 
                $m_r_plan = M('r_plan_product'); 
     
                for($row = 3;$row <= $allRow; $row++){ 
                    $data_procurement_plan = array(); 
                    $data_key_order = array(); 
                    $data_r_plan = array(); 
                    $data_r_order = array(); 
                    $data_supplier = array(); 
     
                    // 得到客户名称 
                    $customer_name = $current_excel->getCell('B'.$row)->getValue(); 
     
                    // 采购计划与产品关联表 
                    $data_r_plan['product_id'] = $current_excel->getCell('C'.$row)->getValue(); 
     
                    // 采购计划 
                    $data_procurement_plan['expect_price'] = $current_excel->getCell('D'.$row)->getValue(); 
                    $data_procurement_plan['order_time'] = formatData($current_excel->getCell('E'.$row)->getValue()); 
                    $data_procurement_plan['expect_delivery_date'] = formatData($current_excel->getCell('F'.$row)->getValue()); 
                    $data_procurement_plan['pay'] = $current_excel->getCell('G'.$row)->getValue(); 
                    $data_procurement_plan['delivery_address'] = $current_excel->getCell('H'.$row)->getValue(); 
                    $data_procurement_plan['transport'] = $current_excel->getCell('I'.$row)->getValue(); 
                    $data_procurement_plan['company_doc'] = $current_excel->getCell('J'.$row)->getValue(); 
                    $data_procurement_plan['product_doc'] = $current_excel->getCell('K'.$row)->getValue(); 
                    $data_procurement_plan['after_sales_agreement'] = $current_excel->getCell('L'.$row)->getValue(); 
                    $data_procurement_plan['default_agreement'] = $current_excel->getCell('M'.$row)->getValue(); 
                    $data_procurement_plan['is_cooperation'] = $current_excel->getCell('N'.$row)->getValue(); 
     
                    // 供应商信息 
                    $data_supplier['name'] = $current_excel->getCell('O'.$row)->getValue(); 
                    $data_supplier['quote'] = $current_excel->getCell('P'.$row)->getValue(); 
     
                    // 关键订单 
                    $data_key_order['our_quote'] = $current_excel->getCell('Q'.$row)->getValue(); 
                    $data_key_order['moq'] = $current_excel->getCell('R'.$row)->getValue(); 
                    $data_key_order['profit_rate'] = $current_excel->getCell('S'.$row)->getValue(); 
                    $data_key_order['type'] = $current_excel->getCell('T'.$row)->getValue(); 
                    $data_key_order['status'] = $current_excel->getCell('U'.$row)->getValue(); 
     
     
                    // 添加前判断 
                    $this->setArrayDefault($data_procurement_plan); 
                    $this->setArrayDefault($data_key_order); 
                    $this->setArrayDefault($data_supplier); 
                    $this->setArrayDefault($data_r_order); 
                    $this->setArrayDefault($data_r_plan); 
                    // 插入数据到数据库 
                    /** 
                     * 插入数据到采购计划表 
                     * 查询客户基本信息表返回客户ID作为采购计划的客户id 
                     */ 
                    $condition['name'] = $customer_name; 
                    $res = $m_customer_info->where($condition)->find(); 
                    $data_procurement_plan['customer_id'] = $res['id']; 
                    $procurement_plan_id = $m_procurement_plan->add($data_procurement_plan); 
     
                    if($procurement_plan_id == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
                    // 插入数据到采购计划与产品关联表 
     
                    // 插入数据到关键订单表 
                    $data_key_order['plan_id'] = $procurement_plan_id; 
                    $key_order_id = $m_key_order->add($data_key_order); 
                    if($key_order_id == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
                    // 插入到供应商信息表 
                    $supplier_id = $m_supplier->add($data_supplier); 
                    if($supplier_id == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
                    // 插入到关键订单与供应商关联表 
                    $data_r_order['order_id'] = $key_order_id; 
                    $data_r_order['supplier_id'] = $supplier_id; 
                    if($m_r_order->add($data_r_order) == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
                    // 插入到采购计划与产品关联表 
                    $data_r_plan['plan_id'] = $procurement_plan_id; 
                    if($m_r_plan->add($data_r_plan) == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
     
                } 
     
     
                // 促销活动excel表 
                $current_excel = $phpexcel->getSheet(2); 
                $allRow = $current_excel->getHighestRow();// 得到总行数 
     
                $m_activity = M('activity'); 
                $m_r_activity = M('r_activity_product'); 
                $m_our_prepare = M('our_prepare'); 
     
                for($row = 3;$row <= $allRow; $row++){ 
                    $data_activity = array(); 
                    $data_our_prepare = array(); 
                    $data_r_activity = array(); 
     
                    // 得到客户名称 
                    $customer_name = $current_excel->getCell('B'.$row)->getValue(); 
                    // 活动情况 
                    $data_activity['theme'] = $current_excel->getCell('C'.$row)->getValue(); 
                    $data_activity['act_date'] = $current_excel->getCell('D'.$row)->getValue(); 
                    $data_activity['act_addr'] = $current_excel->getCell('E'.$row)->getValue(); 
                    $data_activity['act_desc'] = $current_excel->getCell('F'.$row)->getValue(); 
                    $data_activity['description'] = $current_excel->getCell('G'.$row)->getValue(); 
                    $data_activity['model'] = $current_excel->getCell('H'.$row)->getValue(); 
                    $data_activity['traffic_sources'] = $current_excel->getCell('I'.$row)->getValue(); 
                    $data_activity['conversion_rate'] = $current_excel->getCell('J'.$row)->getValue(); 
     
                    // 活动与产品关联表 
                    $data_r_activity['product_id'] = $current_excel->getCell('K'.$row)->getValue(); 
     
                    $data_activity['quantity'] = $current_excel->getCell('L'.$row)->getValue(); 
                    $data_activity['cycle'] = $current_excel->getCell('M'.$row)->getValue(); 
                    $data_activity['expect_traffic'] = $current_excel->getCell('N'.$row)->getValue(); 
                    $data_activity['hr_cs'] = $current_excel->getCell('O'.$row)->getValue(); 
                    $data_activity['hr_it'] = $current_excel->getCell('P'.$row)->getValue(); 
                    $data_activity['hr_art'] = $current_excel->getCell('Q'.$row)->getValue(); 
                    $data_activity['hr_market'] = $current_excel->getCell('R'.$row)->getValue(); 
                    $data_activity['hr_pr'] = $current_excel->getCell('S'.$row)->getValue(); 
                    $data_activity['hr_other'] = $current_excel->getCell('T'.$row)->getValue(); 
                    $data_activity['money'] = $current_excel->getCell('U'.$row)->getValue(); 
                    $data_activity['goods'] = $current_excel->getCell('V'.$row)->getValue(); 
                    $data_activity['storage_facilities'] = $current_excel->getCell('W'.$row)->getValue(); 
                    $data_activity['it_systems'] = $current_excel->getCell('X'.$row)->getValue(); 
                    $data_activity['price_strategy'] = $current_excel->getCell('Y'.$row)->getValue(); 
                    $data_activity['ad_channel'] = $current_excel->getCell('Z'.$row)->getValue(); 
                    $data_activity['ad_type'] = $current_excel->getCell('AA'.$row)->getValue(); 
                    $data_activity['ad_money'] = $current_excel->getCell('AB'.$row)->getValue(); 
                    $data_activity['order_feedback'] = $current_excel->getCell('AC'.$row)->getValue(); 
                    $data_activity['after_sales_feedback'] = $current_excel->getCell('AD'.$row)->getValue(); 
                    $data_activity['faq_feedback'] = $current_excel->getCell('AE'.$row)->getValue(); 
                    $data_activity['sales_inventory_feedback'] = $current_excel->getCell('AF'.$row)->getValue(); 
     
                    // 活动的准备 
                    $data_our_prepare['inventory'] = $current_excel->getCell('AG'.$row)->getValue(); 
                    $data_our_prepare['procurement_plan'] = $current_excel->getCell('AH'.$row)->getValue(); 
                    $data_our_prepare['emergency'] = $current_excel->getCell('AI'.$row)->getValue(); 
                    $data_our_prepare['hr_cs'] = $current_excel->getCell('AJ'.$row)->getValue(); 
                    $data_our_prepare['hr_it'] = $current_excel->getCell('AK'.$row)->getValue(); 
                    $data_our_prepare['hr_purchase'] = $current_excel->getCell('AL'.$row)->getValue(); 
                    $data_our_prepare['hr_sales_manager'] = $current_excel->getCell('AM'.$row)->getValue(); 
                    $data_our_prepare['hr_art'] = $current_excel->getCell('AN'.$row)->getValue(); 
                    $data_our_prepare['hr_market'] = $current_excel->getCell('AO'.$row)->getValue(); 
                    $data_our_prepare['hr_pr'] = $current_excel->getCell('AP'.$row)->getValue(); 
                    $data_our_prepare['hr_other'] = $current_excel->getCell('AQ'.$row)->getValue(); 
                    $data_our_prepare['money'] = $current_excel->getCell('AR'.$row)->getValue(); 
                    $data_our_prepare['goods'] = $current_excel->getCell('AS'.$row)->getValue(); 
                    $data_our_prepare['storage_facilities'] = $current_excel->getCell('AT'.$row)->getValue(); 
                    $data_our_prepare['it_systems'] = $current_excel->getCell('AU'.$row)->getValue(); 
                    $data_our_prepare['expect_order'] = $current_excel->getCell('AV'.$row)->getValue(); 
                    $data_our_prepare['expect_revenue'] = $current_excel->getCell('AW'.$row)->getValue(); 
                    $data_our_prepare['expect_quantity'] = $current_excel->getCell('AX'.$row)->getValue(); 
                    $data_our_prepare['expect_popularity'] = $current_excel->getCell('AY'.$row)->getValue(); 
                    $data_our_prepare['reach_coop'] = $current_excel->getCell('AZ'.$row)->getValue(); 
                    $data_our_prepare['coop_status'] = $current_excel->getCell('BA'.$row)->getValue(); 
     
                    // 添加前判断 
                    $this->setArrayDefault($data_activity); 
                    $this->setArrayDefault($data_r_activity); 
                    $this->setArrayDefault($data_our_prepare); 
                    // 插入到数据库 
                    /** 
                     * 插入到活动情况表 
                     * 查询客户基本信息表返回客户ID作为活动情况的客户id 
                     */ 
                    $condition['name'] = $customer_name; 
                    $res = $m_customer_info->where($condition)->find(); 
                    $data_activity['customer_id'] = $res['id']; 
     
                    $data_activity['traffic'] = 1; 
                    $activity_id = $m_activity->add($data_activity); 
                    if($activity_id == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
                    // 插入到活动与产品关联表 
                    $data_r_activity['activity_id'] = $activity_id; 
                    if($m_r_activity->add($data_r_activity) == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
     
                    // 插入到活动的准备表 
                    $data_our_prepare['activity_id'] = $activity_id; 
                    if($m_our_prepare->add($data_our_prepare) == false){ 
                        $count_fail ++; 
                    }else{ 
                        $count_success ++; 
                    } 
     
                } 
     
                // 产品信息excel表 
                $current_excel = $phpexcel->getSheet(3); 
                $allRow = $current_excel->getHighestRow();// 得到总行数 
     
                $m_product = M('product'); 
                for($row = 2;$row <= $allRow; $row++) { 
                    $data_product = array(); 
     
                    $data_product['category'] = $current_excel->getCell('B' . $row )->getValue(); 
                    $data_product['brand'] = $current_excel->getCell('C' . $row)->getValue(); 
                    $data_product['country'] = $current_excel->getCell('D' . $row)->getValue(); 
                    $data_product['name'] = $current_excel->getCell('E' . $row)->getValue(); 
                    $data_product['sku'] = $current_excel->getCell('F' . $row)->getValue(); 
                    $data_product['model'] = $current_excel->getCell('G' . $row)->getValue(); 
                    $data_product['quantity'] = $current_excel->getCell('H' . $row)->getValue(); 
                    $data_product['unit'] = $current_excel->getCell('I' . $row)->getValue(); 
     
                    // 添加前判断 
                    $this->setArrayDefault($data_product); 
                    // 插入到产品信息表 
                    if($m_product->add($data_product) == false) { 
                        $count_fail++; 
                    }else{ 
                        $count_success ++; 
                    } 
     
                } 
     
                echo $count_success.'条记录插入成功'; 
                echo "<br/>".$count_fail.'条记录失败'; 
                echo "<br/>更新".$count_upload.'条记录'; 
                $this->success('添加完成',U('Index/index'),3); 
            } 
     
        } 
     
        /** 
         * 给数组中值为空的键设置默认值 
         * 
         */ 
        public function setArrayDefault(&$arr){ 
     
            foreach($arr as $k => $v){ 
                $arr[$k] = isset($v)?$v:''; 
            } 
            return $arr; 
        } 
     
        public function delete($id) 
        { 
            // 实例化模型对象 
            $m_customer_info = M('customer_info'); 
            $m_customer_data = M('customer_data'); 
            $m_old_cooperation = M('old_cooperation'); 
            $m_procurement_plan = M('procurement_plan'); 
            $m_key_order = M('key_order'); 
            $m_r_order = M('r_order_supplier'); 
            $m_r_plan = M('r_plan_product'); 
            $m_activity = M('activity'); 
            $m_r_activity = M('r_activity_product'); 
            $m_our_prepare = M('our_prepare'); 
     
            // 获取关联customer_info表的activity的id 
            $res = $m_activity->where('customer_id='.$id)->field('id')->find(); 
            $activity_id = $res['id']; 
            // 获取关联customer_info表的procurement_plan的id 
            $res = $m_procurement_plan->where('customer_id='.$id)->field('id')->find(); 
            $procurement_plan_id = $res['id']; 
            // 获取关联key_order表的r_order_supplier的id 
            $res = $m_key_order->where('plan_id='.$procurement_plan_id)->field('id')->find(); 
            $key_order_id = $res['id']; 
     
            $m_customer_info->where('id='.$id)->delete(); 
     
            /** 
             * 判断activity表中是否有关联数据 
             * 无记录则跳过其他两个关联表的删除操作 
             */ 
            if(intval($activity_id)){ 
                $m_activity->where('customer_id='.$id)->delete(); 
                $m_our_prepare->where('activity_id='.$activity_id)->delete(); 
                $m_r_activity->where('activity_id='.$activity_id)->delete(); 
            } 
     
            /** 
             * 判断procurement_plan表中是否有关联数据 
             * 无记录则跳过其他三个关联表的删除操作 
             */ 
            if(intval($procurement_plan_id)){ 
                $m_procurement_plan->where('customer_id='.$id)->delete(); 
                $m_r_plan->where('plan_id='.$procurement_plan_id)->delete(); 
                $m_key_order->where('plan_id='.$procurement_plan_id)->delete(); 
                $m_r_order->where('order_id='.$key_order_id)->delete(); 
            } 
     
            $m_customer_data->where('customer_id='.$id)->delete(); 
            $m_old_cooperation->where('customer_id='.$id)->delete(); 
     
        } 
     
     
    }
    
    
    函数代码：functions.php
    
    function formatData($data){
        $data = trim($data);
        // 为空则返回默认值
        if(empty($data))  return "0000-00-00";
        $n = intval(($data - 25569) * 3600 * 24); //转换成1970年以来的秒数
        $format_data = gmdate('Y-m-d', $n);//格式化时间
        return $format_data;
    }