点击表格单元格查询对应来源及状态CRM线索全量数据的实现方案

步骤1：修改模型类，补充查询所需字段 #

首先调整get_statusreport方法把来源ID查出来，同时新增查询明细数据的专用方法：

function get_statusreport($fdate='',$tdate='')
{
    // 补充查询crm_sources.id作为source_id
    $this->db->select("l.lead_status,crm_sources.id as source_id, crm_sources.title,count(*) as leadnum,l.enquiry_date,l.sub_status");
    $this->db->from($this->table_name." as l");
    if($fdate !='')
        $this->db->where("date(l.added_date) >=",date('Y-m-d',strtotime($fdate)));
    if($tdate !='')
        $this->db->where("date(l.added_date) <=",date('Y-m-d',strtotime($tdate)));
    $this->db->where("lead_status <>",10);
    $this->db->join("crm_sources ","crm_sources.id= l.lead_source","left");
    $this->db->group_by("l.lead_status,crm_sources.title");
    $this->db->order_by("leadnum DESC, crm_sources.title ASC,l.lead_status ASC");
    $query = $this->db->get();
    $results = $query->result_array();
    return $results;
}

// 新增方法：获取指定来源和状态的全量线索数据
function get_leads_by_source_status($source_id, $status_id, $fdate='', $tdate='')
{
    $this->db->select("l.id, crm_sources.title as source, l.enquiry_date");
    $this->db->from($this->table_name." as l");
    $this->db->join("crm_sources ","crm_sources.id= l.lead_source","left");
    $this->db->where("l.lead_source", $source_id);
    $this->db->where("l.lead_status", $status_id);
    if($fdate !='')
        $this->db->where("date(l.added_date) >=",date('Y-m-d',strtotime($fdate)));
    if($tdate !='')
        $this->db->where("date(l.added_date) <=",date('Y-m-d',strtotime($tdate)));
    $query = $this->db->get();
    return $query->result_array();
}

步骤2：修改统计页视图，给跳转链接携带参数 #

不需要用form提交，直接在a标签拼接查询参数即可：

<?php
    $ls_arr = array(1=>'Open',8=>'Hot',2=>'Closed',3=>'Transacted',4=>'Dead'); 
    // 新增反向映射：状态文本对应状态ID
    $status_id_map = array_flip($ls_arr);
    // 新增存储来源名称对应来源ID的数组
    $source_id_map = [];
     foreach($groupedleads as $grplead){
        $statuses[] = $status = $ls_arr[$grplead["lead_status"]];
        if($grplead["title"] == NULL || $grplead["title"] == '')
            $grplead["title"] = "Unknown";
        if(isset($grplead["title"])){
            $titles[] = $title = $grplead["title"];
            // 存入来源ID映射
            $source_id_map[$title] = $grplead['source_id'];
        }
        $leaddata[$status][$title] = $grplead["leadnum"];
    }

    if(count($titles) > 0)
      $titles = array_unique($titles);
    if(count($statuses) > 0)
      $statuses = array_unique($statuses);
    
?>
<table>
<tr>
            <th id="status">Source</th>
             <?php
              if(count($statuses) > 0)
              foreach($statuses as $status){
            ?><th id="<?php echo $status; ?>"><?php echo $status; ?></th>
            <?php
              }
            ?>
            <th>Total</th>
          </tr>
<?php
          if(is_array($titles))
            foreach($titles as $title){ 
          ?>
              <tr>
                  <?php 
                    $total = 0;
                    echo "<td>".$title."</td>";
                    foreach ($statuses as $status) {
                        $num = $leaddata[$status][$title] ?? 0;
                        if($num > 0){
                            // 拼接来源ID和状态ID到跳转链接，如果有日期筛选也可以一并拼接
                            $link = site_url('reports/viewall?source_id='.$source_id_map[$title].'&status_id='.$status_id_map[$status]);
                            echo "<td><a target='_blank' href='".$link."'>".$num."</a></td>";
                        }else{
                            echo "<td>0</td>";
                        }
                        $total += $num; 
                        $sum[$status] += $num;
                    }
                    echo "<td>".$total."</td>";
                    $grandtotal += $total; 
                   ?>
              </tr>
          <?php } ?>
</table>

步骤3：修改控制器viewall方法，接收参数查询数据 #

public function viewall($slug='')
{
    $content='';
    // 过滤获取GET参数
    $source_id = $this->input->get('source_id', true);
    $status_id = $this->input->get('status_id', true);
    // 可补充获取和统计页一致的日期筛选参数
    $fdate = $this->input->get('fdate', true);
    $tdate = $this->input->get('tdate', true);
    
    // 非法参数直接跳404
    if(!$source_id || !$status_id){
        show_404();
    }
    // 调用模型查询明细数据
    $content['leads'] = $this->leads_model->get_leads_by_source_status($source_id, $status_id, $fdate, $tdate);
    $this->load->view('crm/main',$main);    
    $this->load->view('crm/reports/viewall',$content);
}

步骤4：编写明细页视图，渲染数据表格 #

在crm/reports/viewall.php中添加如下代码：

<table border="1" cellpadding="8" cellspacing="0">
  <thead>
    <tr>
      <th>线索ID</th>
      <th>来源</th>
      <th>咨询日期</th>
    </tr>
  </thead>
  <tbody>
    <?php if(empty($leads)): ?>
    <tr>
      <td colspan="3">暂无数据</td>
    </tr>
    <?php else: ?>
    <?php foreach($leads as $lead): ?>
    <tr>
      <td><?php echo $lead['id']; ?></td>
      <td><?php echo $lead['source']; ?></td>
      <td><?php echo $lead['enquiry_date']; ?></td>
    </tr>
    <?php endforeach; ?>
    <?php endif; ?>
  </tbody>
</table>

内容的提问来源于stack exchange，提问作者JJM50