PHP Excel::addWorksheet方法代码示例

//.........这里部分代码省略.........
             $curr_arr[$i] = 0;
             //统计表数据
             $uplist_arr[$i]['timetext'] = $i;
             $currlist_arr[$i]['timetext'] = $i;
             $uplist_arr[$i]['val'] = 0;
             $currlist_arr[$i]['val'] = 0;
             //横轴
             $stat_arr['xAxis']['categories'][] = $i;
         }
         $where['add_time'] = array('between', array($stime, $etime));
         $field .= ',MONTH(FROM_UNIXTIME(add_time)) as monthval,day(FROM_UNIXTIME(add_time)) as dayval ';
         $memberlist = $model->getStoreSaleStatList($where, $field, 0, '', 0, 'monthval,dayval');
         if ($memberlist) {
             foreach ($memberlist as $k => $v) {
                 if ($up_month == $v['monthval']) {
                     $up_arr[$v['dayval']] = intval($v['allnum']);
                     $uplist_arr[$v['dayval']]['val'] = intval($v['allnum']);
                 }
                 if ($curr_month == $v['monthval']) {
                     $curr_arr[$v['dayval']] = intval($v['allnum']);
                     $currlist_arr[$v['dayval']]['val'] = intval($v['allnum']);
                 }
             }
         } elseif (trim($_GET['store_name']) != '') {
             Tpl::output('data_null', 'yes');
         }
         $stat_arr['series'][0]['name'] = '上月';
         $stat_arr['series'][0]['data'] = array_values($up_arr);
         $stat_arr['series'][1]['name'] = '本月';
         $stat_arr['series'][1]['data'] = array_values($curr_arr);
         Tpl::output('actionurl', 'index.php?act=stat_trade&op=sale&search_type=month&search_time_year=' . $current_year . '&search_time_month=' . $current_month);
     }
     //统计数据标题
     $statlist['headertitle'] = array('订单号', '买家', '店铺名称', '下单时间', '订单总额', '订单状态');
     //导出Excel
     if ($_GET['exporttype'] == 'excel') {
         //导出Excel
         import('libraries.excel');
         $excel_obj = new Excel();
         $excel_data = array();
         //设置样式
         $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
         //header
         foreach ($statlist['headertitle'] as $v) {
             $excel_data[0][] = array('styleid' => 's_title', 'data' => $v);
         }
         $order_all_list = $model->getStoreOrderList($where, false);
         //data
         foreach ($order_all_list as $k => $v) {
             $excel_data[$k + 1][] = array('data' => $v['order_sn']);
             $excel_data[$k + 1][] = array('data' => $v['buyer_name']);
             $excel_data[$k + 1][] = array('data' => $v['store_name']);
             $excel_data[$k + 1][] = array('data' => date('Y-m-d H:i:s', $v['add_time']));
             $excel_data[$k + 1][] = array('data' => number_format($v['order_amount'], 2));
             switch ($v['order_state']) {
                 case ORDER_STATE_CANCEL:
                     $excel_data[$k + 1][] = array('data' => '已取消');
                     break;
                 case ORDER_STATE_NEW:
                     $excel_data[$k + 1][] = array('data' => '待付款');
                     break;
                 case ORDER_STATE_PAY:
                     $excel_data[$k + 1][] = array('data' => '待发货');
                     break;
                 case ORDER_STATE_SEND:
                     $excel_data[$k + 1][] = array('data' => '待收货');
                     break;
                 case ORDER_STATE_SUCCESS:
                     $excel_data[$k + 1][] = array('data' => '交易完成');
                     break;
             }
         }
         $excel_data = $excel_obj->charset($excel_data, CHARSET);
         $excel_obj->addArray($excel_data);
         $excel_obj->addWorksheet($excel_obj->charset('订单统计', CHARSET));
         $excel_obj->generateXML($excel_obj->charset('订单统计', CHARSET) . date('Y-m-d-H', time()));
         exit;
     } else {
         $order_list = $model->getStoreOrderList($where);
         //得到统计图数据
         if (trim($_GET['stat_type']) == 'sale') {
             $stat_arr['title'] = '订单销售额统计';
             $stat_arr['yAxis'] = '订单销售额';
         } else {
             $stat_arr['title'] = '订单量统计';
             $stat_arr['yAxis'] = '订单量';
         }
         $stat_json = getStatData_LineLabels($stat_arr);
         //总数统计
         $amount = $model->getStoreSaleStatList($where, ' count(*) as allnum ');
         $sale = $model->getStoreSaleStatList($where, ' sum(order_amount) as allnum ');
         Tpl::output('sum_data', array($amount[0]['allnum'], $sale[0]['allnum']));
         Tpl::output('stat_json', $stat_json);
         Tpl::output('statlist', $statlist);
         Tpl::output('order_list', $order_list);
         Tpl::output('show_page', $model->showpage());
         Tpl::output('top_link', $this->sublink($this->links, 'sale'));
         Tpl::showpage('stat.sale');
     }
 }

 /**
  * 区域分析之详细列表
  */
 public function area_listOp()
 {
     $model = Model('stat');
     $where = array();
     $searchtime_arr = explode('|', $this->search_arr['t']);
     $where['add_time'] = array('between', $searchtime_arr);
     //$where['order_state'] = array(array('neq',ORDER_STATE_CANCEL),array('neq',ORDER_STATE_NEW),'and');
     $where['order_state'] = array('neq', ORDER_STATE_NEW);
     //去除未支付订单
     $where['refund_state'] = array('exp', "!(order_state = '" . ORDER_STATE_CANCEL . "' and refund_state = 0)");
     //没有参与退款的取消订单，不记录到统计中
     $where['payment_code'] = array('exp', "!([order].payment_code='offline' and order_state <> '" . ORDER_STATE_SUCCESS . "')");
     //货到付款订单，订单成功之后才计入统计
     $field = ' order_common.reciver_province_id, count(*) as ordernum,sum([order].order_amount) as orderamount, count(DISTINCT [order].buyer_id) as membernum ';
     if (!trim($this->search_arr['orderby'])) {
         $this->search_arr['orderby'] = 'membernum desc';
     }
     $orderby = trim($this->search_arr['orderby']) . ',order_common.reciver_province_id';
     $count_arr = $model->statByOrderCommon($where, 'count(DISTINCT order_common.reciver_province_id) as countnum');
     $countnum = intval($count_arr[0]['countnum']);
     if ($this->search_arr['exporttype'] == 'excel') {
         $statlist_tmp = $model->statByOrderCommon($where, $field, 0, 0, $orderby, 'order_common.reciver_province_id');
     } else {
         $statlist_tmp = $model->statByOrderCommon($where, $field, array(10, $countnum), 0, $orderby, 'order_common.reciver_province_id');
     }
     // 地区
     require_once BASE_DATA_PATH . '/area/area.php';
     $statheader = array();
     $statheader[] = array('text' => '省份', 'key' => 'provincename');
     $statheader[] = array('text' => '下单会员数', 'key' => 'membernum', 'isorder' => 1);
     $statheader[] = array('text' => '下单金额', 'key' => 'orderamount', 'isorder' => 1);
     $statheader[] = array('text' => '下单量', 'key' => 'ordernum', 'isorder' => 1);
     $statlist = array();
     foreach ((array) $statlist_tmp as $k => $v) {
         $province_id = intval($v['reciver_province_id']);
         $tmp = array();
         $tmp['provincename'] = ($t = $area_array[$province_id]['area_name']) ? $t : '其他';
         $tmp['membernum'] = $v['membernum'];
         $tmp['orderamount'] = $v['orderamount'];
         $tmp['ordernum'] = $v['ordernum'];
         $statlist[] = $tmp;
     }
     //导出Excel
     if ($this->search_arr['exporttype'] == 'excel') {
         //导出Excel
         import('libraries.excel');
         $excel_obj = new Excel();
         $excel_data = array();
         //设置样式
         $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
         //header
         foreach ($statheader as $k => $v) {
             $excel_data[0][] = array('styleid' => 's_title', 'data' => $v['text']);
         }
         //data
         foreach ($statlist as $k => $v) {
             foreach ($statheader as $h_k => $h_v) {
                 $excel_data[$k + 1][] = array('data' => $v[$h_v['key']]);
             }
         }
         $excel_data = $excel_obj->charset($excel_data, CHARSET);
         $excel_obj->addArray($excel_data);
         $excel_obj->addWorksheet($excel_obj->charset('区域分析', CHARSET));
         $excel_obj->generateXML($excel_obj->charset('区域分析', CHARSET) . date('Y-m-d-H', time()));
         exit;
     }
     Tpl::output('statlist', $statlist);
     Tpl::output('statheader', $statheader);
     Tpl::output('orderby', $this->search_arr['orderby']);
     Tpl::output('actionurl', "index.php?act={$this->search_arr['act']}&op={$this->search_arr['op']}&t={$this->search_arr['t']}");
     Tpl::output('show_page', $model->showpage(2));
     Tpl::output('top_link', $this->sublink($this->links, 'area'));
     Tpl::showpage('stat.listandorder', 'null_layout');
 }

 /**
  * 生成excel
  *
  * @param array $data
  */
 private function createExcel($data = array())
 {
     Language::read('export');
     import('libraries.excel');
     $excel_obj = new Excel();
     $excel_data = array();
     //设置样式
     $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
     //header
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_no'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_store'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_buyer'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_xtimd'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_count'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_yfei'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_paytype'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_state'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_storeid'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_buyerid'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_od_bemail'));
     //data
     foreach ((array) $data as $k => $v) {
         $tmp = array();
         $tmp[] = array('data' => 'NC' . $v['order_sn']);
         $tmp[] = array('data' => $v['store_name']);
         $tmp[] = array('data' => $v['buyer_name']);
         $tmp[] = array('data' => date('Y-m-d H:i:s', $v['add_time']));
         $tmp[] = array('format' => 'Number', 'data' => ncPriceFormat($v['order_amount']));
         $tmp[] = array('format' => 'Number', 'data' => ncPriceFormat($v['shipping_fee']));
         $tmp[] = array('data' => orderPaymentName($v['payment_code']));
         $tmp[] = array('data' => orderState($v));
         $tmp[] = array('data' => $v['store_id']);
         $tmp[] = array('data' => $v['buyer_id']);
         $tmp[] = array('data' => $v['buyer_email']);
         $excel_data[] = $tmp;
     }
     $excel_data = $excel_obj->charset($excel_data, CHARSET);
     $excel_obj->addArray($excel_data);
     $excel_obj->addWorksheet($excel_obj->charset(L('exp_od_order'), CHARSET));
     $excel_obj->generateXML($excel_obj->charset(L('exp_od_order'), CHARSET) . $_GET['curpage'] . '-' . date('Y-m-d-H', time()));
 }

 /**
  * 生成excel
  *
  * @param array $data
  */
 private function createExcel($data = array())
 {
     Language::read('export');
     import('libraries.excel');
     $excel_obj = new Excel();
     $excel_data = array();
     //设置样式
     $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
     //header
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_member'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_name'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_jf'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_yck'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_jbs'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => 'Email');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_sex'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => 'QQ');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_ww'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_dcs'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_rtime'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_ltime'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mb_storeid'));
     $state_cn = array(Language::get('admin_points_stage_regist'), Language::get('admin_points_stage_login'), Language::get('admin_points_stage_comments'), Language::get('admin_points_stage_order'), Language::get('admin_points_stage_system'), Language::get('admin_points_stage_pointorder'), Language::get('admin_points_stage_app'));
     foreach ((array) $data as $k => $v) {
         $tmp = array();
         $tmp[] = array('data' => $v['member_name']);
         $tmp[] = array('data' => $v['member_truename']);
         $tmp[] = array('format' => 'Number', 'data' => ncPriceFormat($v['pl_points']));
         $tmp[] = array('data' => Language::get('member_index_available') . ':' . $v['available_predeposit'] . Language::get('member_index_frozen') . ':' . $v['freeze_predeposit']);
         $tmp[] = array('data' => $v['member_goldnum']);
         $tmp[] = array('data' => $v['member_email']);
         $tmp[] = array('data' => str_replace(array(1, 2, 0, 3), array(L('exp_mb_nan'), L('exp_mb_nv'), '', ''), $v['member_sex']));
         $tmp[] = array('data' => $v['member_qq']);
         $tmp[] = array('data' => $v['member_ww']);
         $tmp[] = array('data' => $v['member_login_num']);
         $tmp[] = array('data' => $v['member_time']);
         $tmp[] = array('data' => $v['member_login_time']);
         $tmp[] = array('data' => $v['store_id']);
         $excel_data[] = $tmp;
     }
     $excel_data = $excel_obj->charset($excel_data, CHARSET);
     $excel_obj->addArray($excel_data);
     $excel_obj->addWorksheet($excel_obj->charset(L('exp_member'), CHARSET));
     $excel_obj->generateXML($excel_obj->charset(L('exp_member'), CHARSET) . $_GET['curpage'] . '-' . date('Y-m-d-H', time()));
 }

 /**
  * 商品销售明细
  */
 public function goods_saleOp()
 {
     if (!$this->search_arr['search_type']) {
         $this->search_arr['search_type'] = 'day';
     }
     $model = Model('stat');
     //获得搜索的开始时间和结束时间
     $searchtime_arr = $model->getStarttimeAndEndtime($this->search_arr);
     //获取相关数据
     $where = array();
     $where['order_isvalid'] = 1;
     //计入统计的有效订单
     $where['order_add_time'] = array('between', $searchtime_arr);
     //品牌
     $brand_id = intval($_REQUEST['b_id']);
     if ($brand_id > 0) {
         $where['brand_id'] = $brand_id;
     }
     //商品分类
     if ($this->choose_gcid > 0) {
         //获得分类深度
         $depth = $this->gc_arr[$this->choose_gcid]['depth'];
         $where['gc_parentid_' . $depth] = $this->choose_gcid;
     }
     if (trim($_GET['goods_name'])) {
         $where['goods_name'] = array('like', '%' . trim($_GET['goods_name']) . '%');
     }
     if (trim($_GET['store_name'])) {
         $where['store_name'] = array('like', '%' . trim($_GET['store_name']) . '%');
     }
     $field = 'goods_id,goods_name,store_id,store_name,goods_commonid,SUM(goods_num) as goodsnum,COUNT(DISTINCT order_id) as ordernum,SUM(goods_pay_price) as goodsamount';
     //排序
     $orderby_arr = array('goodsnum asc', 'goodsnum desc', 'ordernum asc', 'ordernum desc', 'goodsamount asc', 'goodsamount desc');
     if (!in_array(trim($this->search_arr['orderby']), $orderby_arr)) {
         $this->search_arr['orderby'] = 'goodsnum desc';
     }
     $orderby = trim($this->search_arr['orderby']) . ',goods_id asc';
     //查询记录总条数
     $count_arr = $model->getoneByStatordergoods($where, 'COUNT(DISTINCT goods_id) as countnum');
     $countnum = intval($count_arr['countnum']);
     if ($_GET['exporttype'] == 'excel') {
         $goods_list = $model->statByStatordergoods($where, $field, 0, 0, $orderby, 'goods_id');
     } else {
         $goods_list = $model->statByStatordergoods($where, $field, array(10, $countnum), 0, $orderby, 'goods_id');
     }
     //导出Excel
     if ($_GET['exporttype'] == 'excel') {
         //导出Excel
         import('libraries.excel');
         $excel_obj = new Excel();
         $excel_data = array();
         //设置样式
         $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
         //header
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '商品名称');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '平台货号');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '店铺名称');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '下单商品件数');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '下单单量');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '下单金额');
         //data
         foreach ($goods_list as $k => $v) {
             $excel_data[$k + 1][] = array('data' => $v['goods_name']);
             $excel_data[$k + 1][] = array('data' => $v['goods_commonid']);
             $excel_data[$k + 1][] = array('data' => $v['store_name']);
             $excel_data[$k + 1][] = array('data' => $v['goodsnum']);
             $excel_data[$k + 1][] = array('data' => $v['ordernum']);
             $excel_data[$k + 1][] = array('data' => $v['goodsamount']);
         }
         $excel_data = $excel_obj->charset($excel_data, CHARSET);
         $excel_obj->addArray($excel_data);
         $excel_obj->addWorksheet($excel_obj->charset('商品销售明细', CHARSET));
         $excel_obj->generateXML($excel_obj->charset('商品销售明细', CHARSET) . date('Y-m-d-H', time()));
         exit;
     } else {
         //查询品牌
         $brand_list = Model('brand')->getBrandList(array('brand_apply' => 1));
         Tpl::output('brand_list', $brand_list);
         Tpl::output('goods_list', $goods_list);
         Tpl::output('show_page', $model->showpage(2));
         Tpl::output('orderby', $this->search_arr['orderby']);
         Tpl::output('top_link', $this->sublink($this->links, 'goods_sale'));
         Tpl::showpage('stat.goodssale');
     }
 }

 /**
  * 生成excel
  *
  * @param array $data
  */
 private function createExcel($data = array())
 {
     Language::read('export');
     import('libraries.excel');
     $excel_obj = new Excel();
     $excel_data = array();
     //设置样式
     $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
     //header
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_spec'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_sp_content'));
     foreach ((array) $data as $k => $v) {
         $tmp = array();
         $tmp[] = array('data' => $v['sp_name']);
         $tmp[] = array('data' => $v['sp_value']);
         $excel_data[] = $tmp;
     }
     $excel_data = $excel_obj->charset($excel_data, CHARSET);
     $excel_obj->addArray($excel_data);
     $excel_obj->addWorksheet($excel_obj->charset(L('exp_spec'), CHARSET));
     $excel_obj->generateXML($excel_obj->charset(L('exp_spec'), CHARSET) . $_GET['curpage'] . '-' . date('Y-m-d-H', time()));
 }

 /**
  * 生成excel
  *
  * @param array $data
  */
 private function createExcel($data = array())
 {
     Language::read('export');
     import('libraries.excel');
     $excel_obj = new Excel();
     $excel_data = array();
     //设置样式
     $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
     //header
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_pi_member'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_pi_system'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_pi_point'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_pi_time'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_pi_jd'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_pi_ms'));
     $state_cn = array(Language::get('admin_points_stage_regist'), Language::get('admin_points_stage_login'), Language::get('admin_points_stage_comments'), Language::get('admin_points_stage_order'), Language::get('admin_points_stage_system'), Language::get('admin_points_stage_pointorder'), Language::get('admin_points_stage_app'));
     foreach ((array) $data as $k => $v) {
         $tmp = array();
         $tmp[] = array('data' => $v['pl_membername']);
         $tmp[] = array('data' => $v['pl_adminname']);
         $tmp[] = array('format' => 'Number', 'data' => ncPriceFormat($v['pl_points']));
         $tmp[] = array('data' => date('Y-m-d H:i:s', $v['pl_addtime']));
         $tmp[] = array('data' => str_replace(array('regist', 'login', 'comments', 'order', 'system', 'pointorder', 'app'), $state_cn, $v['pl_stage']));
         $tmp[] = array('data' => $v['pl_desc']);
         $excel_data[] = $tmp;
     }
     $excel_data = $excel_obj->charset($excel_data, CHARSET);
     $excel_obj->addArray($excel_data);
     $excel_obj->addWorksheet($excel_obj->charset(L('exp_pi_jfmx'), CHARSET));
     $excel_obj->generateXML($excel_obj->charset(L('exp_pi_jfmx'), CHARSET) . $_GET['curpage'] . '-' . date('Y-m-d-H', time()));
 }

 /**
  * 团购商品统计
  */
 public function groupgoodsOp()
 {
     $model = Model('stat');
     $where = array();
     $searchtime_arr = explode('|', $_GET['t']);
     $where['add_time'] = array('between', $searchtime_arr);
     $where['goods_type'] = 2;
     $goodsname = trim($_GET['goodsname']);
     if ($goodsname) {
         $where['goods_name'] = array('like', "%{$goodsname}%");
     }
     $field = " goods_id,goods_name";
     $field .= " ,SUM(order_goods.goods_num) as goodsnum";
     $field .= " ,SUM(order_goods.goods_pay_price) as goodsamount";
     $field .= " ,SUM(IF(order.order_state='" . ORDER_STATE_CANCEL . "',goods_num,0)) as cancelgoodsnum";
     $field .= " ,SUM(IF(order.order_state='" . ORDER_STATE_CANCEL . "',goods_pay_price,0)) as cancelgoodsamount";
     $field .= " ,SUM(IF(order.order_state<>'" . ORDER_STATE_CANCEL . "' and order.order_state<>'" . ORDER_STATE_NEW . "',goods_num,0)) as finishgoodsnum";
     $field .= " ,SUM(IF(order.order_state<>'" . ORDER_STATE_CANCEL . "' and order.order_state<>'" . ORDER_STATE_NEW . "',goods_pay_price,0)) as finishgoodsamount";
     if (!trim($this->search_arr['orderby'])) {
         $this->search_arr['orderby'] = 'goodsnum desc';
     }
     $orderby = trim($this->search_arr['orderby']) . ',goods_id desc';
     //统计记录总条数
     $count_arr = $model->statByOrderGoods($where, 'count(DISTINCT goods_id) as countnum');
     $countnum = intval($count_arr[0]['countnum']);
     if ($this->search_arr['exporttype'] == 'excel') {
         $statlist_tmp = $model->statByOrderGoods($where, $field, 0, 0, $orderby, 'goods_id');
     } else {
         $statlist_tmp = $model->statByOrderGoods($where, $field, array(10, $countnum), 0, $orderby, 'goods_id');
     }
     $statheader = array();
     $statheader[] = array('text' => '商品名称', 'key' => 'goods_name', 'class' => 'alignleft');
     $statheader[] = array('text' => '下单商品数', 'key' => 'goodsnum', 'isorder' => 1);
     $statheader[] = array('text' => '下单金额', 'key' => 'goodsamount', 'isorder' => 1);
     $statheader[] = array('text' => '取消商品数', 'key' => 'cancelgoodsnum', 'isorder' => 1);
     $statheader[] = array('text' => '取消金额', 'key' => 'cancelgoodsamount', 'isorder' => 1);
     $statheader[] = array('text' => '完成商品数', 'key' => 'finishgoodsnum', 'isorder' => 1);
     $statheader[] = array('text' => '完成金额', 'key' => 'finishgoodsamount', 'isorder' => 1);
     foreach ((array) $statlist_tmp as $k => $v) {
         $tmp = $v;
         foreach ($statheader as $h_k => $h_v) {
             $tmp[$h_v['key']] = $v[$h_v['key']];
             if ($h_v['key'] == 'goods_name') {
                 $tmp[$h_v['key']] = '<a href="' . urlShop('goods', 'index', array('goods_id' => $v['goods_id'])) . '" target="_blank">' . $v['goods_name'] . '</a>';
             }
         }
         $statlist[] = $tmp;
     }
     if ($this->search_arr['exporttype'] == 'excel') {
         //导出Excel
         import('libraries.excel');
         $excel_obj = new Excel();
         $excel_data = array();
         //设置样式
         $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
         //header
         foreach ($statheader as $k => $v) {
             $excel_data[0][] = array('styleid' => 's_title', 'data' => $v['text']);
         }
         //data
         foreach ($statlist as $k => $v) {
             foreach ($statheader as $h_k => $h_v) {
                 $excel_data[$k + 1][] = array('data' => $v[$h_v['key']]);
             }
         }
         $excel_data = $excel_obj->charset($excel_data, CHARSET);
         $excel_obj->addArray($excel_data);
         $excel_obj->addWorksheet($excel_obj->charset('团购商品统计', CHARSET));
         $excel_obj->generateXML($excel_obj->charset('团购商品统计', CHARSET) . date('Y-m-d-H', time()));
         exit;
     } else {
         Tpl::output('statheader', $statheader);
         Tpl::output('statlist', $statlist);
         Tpl::output('show_page', $model->showpage(2));
         Tpl::output('searchtime', $_GET['t']);
         Tpl::output('orderby', $this->search_arr['orderby']);
         Tpl::output('actionurl', "index.php?act={$this->search_arr['act']}&op={$this->search_arr['op']}&t={$this->search_arr['t']}");
         Tpl::showpage('stat.listandorder', 'null_layout');
     }
 }

 /**
  * 查看店铺列表
  */
 public function showstoreOp()
 {
     $model = Model('stat');
     $where = array();
     if (in_array($_GET['type'], array('newbyday', 'newbyweek', 'newbymonth'))) {
         $actionurl = 'index.php?act=stat_store&op=showstore&type=newbyday&t=' . $_GET['t'];
         $searchtime_arr = explode('|', $_GET['t']);
         $where['store_time'] = array('between', $searchtime_arr);
     }
     if ($_GET['exporttype'] == 'excel') {
         $store_list = $model->getNewStoreStatList($where);
     } else {
         $store_list = $model->getNewStoreStatList($where, '', 10);
     }
     //店铺等级
     $model_grade = Model('store_grade');
     $grade_list = $model_grade->getGradeList();
     if (!empty($grade_list)) {
         $search_grade_list = array();
         foreach ($grade_list as $k => $v) {
             $search_grade_list[$v['sg_id']] = $v['sg_name'];
         }
     }
     //导出Excel
     if ($_GET['exporttype'] == 'excel') {
         //导出Excel
         import('libraries.excel');
         $excel_obj = new Excel();
         $excel_data = array();
         //设置样式
         $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
         //header
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '店铺名称');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '店主账号');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '店主卖家账号');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '所属等级');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '有效期至');
         $excel_data[0][] = array('styleid' => 's_title', 'data' => '开店时间');
         //data
         foreach ($store_list as $k => $v) {
             $excel_data[$k + 1][] = array('data' => $v['store_name']);
             $excel_data[$k + 1][] = array('data' => $v['member_name']);
             $excel_data[$k + 1][] = array('data' => $v['seller_name']);
             $excel_data[$k + 1][] = array('data' => $search_grade_list[$v['grade_id']]);
             $excel_data[$k + 1][] = array('data' => $v['store_end_time'] ? date('Y-m-d', $v['store_end_time']) : '无限制');
             $excel_data[$k + 1][] = array('data' => date('Y-m-d', $v['store_time']));
         }
         $excel_data = $excel_obj->charset($excel_data, CHARSET);
         $excel_obj->addArray($excel_data);
         $excel_obj->addWorksheet($excel_obj->charset('新增店铺', CHARSET));
         $excel_obj->generateXML($excel_obj->charset('新增店铺', CHARSET) . date('Y-m-d-H', time()));
         exit;
     }
     Tpl::output('search_grade_list', $search_grade_list);
     Tpl::output('actionurl', $actionurl);
     Tpl::output('store_list', $store_list);
     Tpl::output('show_page', $model->showpage(2));
     $this->links[] = array('url' => 'act=stat_store&op=showstore', 'lang' => 'stat_storelist');
     Tpl::output('top_link', $this->sublink($this->links, 'showstore'));
     Tpl::showpage('stat.info.storelist');
 }

//.........这里部分代码省略.........
     Tpl::output('month_arr', $month_arr);
     Tpl::output('week_arr', $week_arr);
     Tpl::output('search_arr', $this->search_arr);
     //默认统计当前数据
     if (!$this->search_arr['search_type']) {
         $this->search_arr['search_type'] = 'day';
     }
     //计算昨天和今天时间
     if ($this->search_arr['search_type'] == 'day') {
         $stime = $this->search_arr['day']['search_time'] - 86400;
         //昨天0点
         $etime = $this->search_arr['day']['search_time'] + 86400 - 1;
         //今天24点
         $curr_stime = $this->search_arr['day']['search_time'];
         //今天0点
     } elseif ($this->search_arr['search_type'] == 'week') {
         $current_weekarr = explode('|', $this->search_arr['week']['current_week']);
         $stime = strtotime($current_weekarr[0]) - 86400 * 7;
         $etime = strtotime($current_weekarr[1]) + 86400 - 1;
         $curr_stime = strtotime($current_weekarr[0]);
         //本周0点
     } elseif ($this->search_arr['search_type'] == 'month') {
         $stime = strtotime($this->search_arr['month']['current_year'] . '-' . $this->search_arr['month']['current_month'] . "-01 -1 month");
         $etime = getMonthLastDay($this->search_arr['month']['current_year'], $this->search_arr['month']['current_month']) + 86400 - 1;
         $curr_stime = strtotime($this->search_arr['month']['current_year'] . '-' . $this->search_arr['month']['current_month'] . "-01");
         //本月0点
     }
     $where = array();
     $where['order_add_time'] = array('between', array($curr_stime, $etime));
     if (trim($_GET['order_type']) != '') {
         $where['order_state'] = trim($_GET['order_type']);
     }
     if (trim($_GET['store_name']) != '') {
         $where['store_name'] = array('like', '%' . trim($_GET['store_name']) . '%');
     }
     if ($_GET['exporttype'] == 'excel') {
         $order_list = $model->statByStatorder($where, '', 0, 0, 'order_id desc', '');
     } else {
         $order_list = $model->statByStatorder($where, '', 10, 0, 'order_id desc', '');
     }
     //统计数据标题
     $statlist = array();
     $statlist['headertitle'] = array('订单号', '买家', '店铺名称', '下单时间', '订单总额', '订单状态');
     foreach ((array) $order_list as $k => $v) {
         switch ($v['order_state']) {
             case ORDER_STATE_CANCEL:
                 $v['order_statetext'] = '已取消';
                 break;
             case ORDER_STATE_NEW:
                 $v['order_statetext'] = '待付款';
                 break;
             case ORDER_STATE_PAY:
                 $v['order_statetext'] = '待发货';
                 break;
             case ORDER_STATE_SEND:
                 $v['order_statetext'] = '待收货';
                 break;
             case ORDER_STATE_SUCCESS:
                 $v['order_statetext'] = '交易完成';
                 break;
         }
         $statlist['data'][$k] = $v;
     }
     //导出Excel
     if ($_GET['exporttype'] == 'excel') {
         //导出Excel
         import('libraries.excel');
         $excel_obj = new Excel();
         $excel_data = array();
         //设置样式
         $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
         //header
         foreach ($statlist['headertitle'] as $v) {
             $excel_data[0][] = array('styleid' => 's_title', 'data' => $v);
         }
         //data
         foreach ((array) $statlist['data'] as $k => $v) {
             $excel_data[$k + 1][] = array('data' => $v['order_sn']);
             $excel_data[$k + 1][] = array('data' => $v['buyer_name']);
             $excel_data[$k + 1][] = array('data' => $v['store_name']);
             $excel_data[$k + 1][] = array('data' => date('Y-m-d H:i:s', $v['order_add_time']));
             $excel_data[$k + 1][] = array('data' => number_format($v['order_amount'], 2));
             $excel_data[$k + 1][] = array('data' => $v['order_statetext']);
         }
         $excel_data = $excel_obj->charset($excel_data, CHARSET);
         $excel_obj->addArray($excel_data);
         $excel_obj->addWorksheet($excel_obj->charset('订单统计', CHARSET));
         $excel_obj->generateXML($excel_obj->charset('订单统计', CHARSET) . date('Y-m-d-H', time()));
         exit;
     } else {
         //总数统计
         $statcount_arr = $model->getoneByStatorder($where, ' COUNT(*) as ordernum, SUM(order_amount) as orderamount');
         Tpl::output('statcount_arr', $statcount_arr);
         Tpl::output('searchtime', implode('|', array($stime, $etime)));
         Tpl::output('statlist', $statlist);
         Tpl::output('show_page', $model->showpage());
         Tpl::output('top_link', $this->sublink($this->links, 'sale'));
         Tpl::showpage('stat.sale');
     }
 }

	/**
	 * 订单列表
	 */
	public function salelistOp(){
	    $model = Model('stat');
	    $searchtime_arr_tmp = explode('|',$this->search_arr['t']);
		foreach ((array)$searchtime_arr_tmp as $k=>$v){
		    $searchtime_arr[] = intval($v);
		}
	    $where = array();
	    $where['store_id'] = $_SESSION['store_id'];
        $where['order_add_time'] = array('between',$searchtime_arr);
		if(trim($_GET['order_type']) != ''){
    		$where['order_state'] = trim($_GET['order_type']);
    	}
		if ($_GET['exporttype'] == 'excel'){
		    $order_list = $model->statByStatorder($where, '', 0, 0,'order_id desc');
		} else {
		    $order_list = $model->statByStatorder($where, '', 10, 0,'order_id desc');
		}
		//统计数据标题
		$statlist = array();
		$statheader = array();
        $statheader[] = array('text'=>'订单编号','key'=>'order_sn');
        $statheader[] = array('text'=>'买家','key'=>'buyer_name');
        $statheader[] = array('text'=>'下单时间','key'=>'order_add_time');
        $statheader[] = array('text'=>'订单总额','key'=>'order_amount');
        $statheader[] = array('text'=>'订单状态','key'=>'order_statetext');
		foreach ((array)$order_list as $k=>$v){
		    $v['order_add_time'] = @date('Y-m-d H:i:s',$v['order_add_time']);
		    switch ($v['order_state']){
	        	case ORDER_STATE_CANCEL:
	        		$v['order_statetext'] = '已取消';
	        		break;
	        	case ORDER_STATE_NEW:
	        		$v['order_statetext'] = '待付款';
	        		break;
	        	case ORDER_STATE_PAY:
	        		$v['order_statetext'] = '待发货';
	        		break;
	        	case ORDER_STATE_SEND:
	        		$v['order_statetext'] = '待收货';
	        		break;
	        	case ORDER_STATE_SUCCESS:
	        		$v['order_statetext'] = '交易完成';
	        		break;
	        }
		    $statlist[$k]= $v;
		}

	    //导出Excel
        if ($this->search_arr['exporttype'] == 'excel'){
            //导出Excel
			import('libraries.excel');
		    $excel_obj = new Excel();
		    $excel_data = array();
		    //设置样式
		    $excel_obj->setStyle(array('id'=>'s_title','Font'=>array('FontName'=>'宋体','Size'=>'12','Bold'=>'1')));
			//header
			foreach ($statheader as $k=>$v){
			    $excel_data[0][] = array('styleid'=>'s_title','data'=>$v['text']);
			}
			//data
			foreach ($statlist as $k=>$v){
    			foreach ($statheader as $h_k=>$h_v){
    			    $excel_data[$k+1][] = array('data'=>$v[$h_v['key']]);
    			}
			}
			$excel_data = $excel_obj->charset($excel_data,CHARSET);
			$excel_obj->addArray($excel_data);
		    $excel_obj->addWorksheet($excel_obj->charset('订单记录',CHARSET));
		    $excel_obj->generateXML($excel_obj->charset('订单记录',CHARSET).date('Y-m-d-H',time()));
			exit();
        }
		Tpl::output('statheader',$statheader);
		Tpl::output('statlist',$statlist);
		Tpl::output('show_page',$model->showpage(2));
		Tpl::output('actionurl',"index.php?act={$this->search_arr['act']}&op={$this->search_arr['op']}&order_type={$this->search_arr['order_type']}&t={$this->search_arr['t']}");
		Tpl::showpage('stat.listandorder','null_layout');
	}

 /**
  * 生成excel，导出金币日志
  *
  * @param array $data
  */
 private function createLogExcel($data = array())
 {
     Language::read('export');
     import('libraries.excel');
     $excel_obj = new Excel();
     $excel_data = array();
     //设置样式
     $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
     //header
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_jb_member'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_jb_store'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_jb_jbs'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_jb_type'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_jb_btime'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_jb_mshu'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_jb_storeid'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_jb_memberid'));
     $payment_array = $this->getPayment();
     $payment_array_new = array();
     if (is_array($payment_array) && count($payment_array) > 0) {
         foreach ($payment_array as $k => $v) {
             if ($v['payment_code'] != 'predeposit') {
                 $payment_array_new[$v['payment_code']] = $v;
             }
         }
     }
     foreach ((array) $data as $k => $v) {
         $tmp = array();
         $tmp[] = array('data' => $v['glog_membername']);
         $tmp[] = array('data' => $v['glog_storename']);
         $tmp[] = array('data' => $v['glog_goldnum']);
         $tmp[] = array('data' => str_replace(array(1, 2), array(L('exp_jb_add'), L('exp_jb_del')), $v['glog_method']));
         $tmp[] = array('data' => date('Y-m-d H:i:s', $v['glog_addtime']));
         $tmp[] = array('data' => $v['glog_desc']);
         $tmp[] = array('data' => $v['glog_storeid']);
         $tmp[] = array('data' => $v['glog_memberid']);
         $excel_data[] = $tmp;
     }
     $excel_data = $excel_obj->charset($excel_data, CHARSET);
     $excel_obj->addArray($excel_data);
     $excel_obj->addWorksheet($excel_obj->charset(L('exp_jb_log'), CHARSET));
     $excel_obj->generateXML($excel_obj->charset(L('exp_jb_log'), CHARSET) . $_GET['curpage'] . '-' . date('Y-m-d-H', time()));
 }

 /**
  * 生成excel
  *
  * @param array $data
  */
 private function createExcel($data = array())
 {
     Language::read('export');
     import('libraries.excel');
     $excel_obj = new Excel();
     $excel_data = array();
     //设置样式
     $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
     //header
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '订单编号');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '下单时间');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '付款时间');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '运费');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '物流单号');
     //data
     foreach ((array) $data as $k => $v) {
         $tmp = array();
         $tmp[] = array('data' => 'NC' . $v['order_sn']);
         $tmp[] = array('data' => date('Y-m-d H:i:s', $v['add_time']));
         $tmp[] = array('data' => $v['payment_time'] ? date('Y-m-d H:i:s', $v['payment_time']) : '');
         $tmp[] = array('format' => 'Number', 'data' => $v['shipping_fee']);
         $tmp[] = array('data' => $v['shipping_code']);
         $excel_data[] = $tmp;
     }
     $excel_data = $excel_obj->charset($excel_data, CHARSET);
     $excel_obj->addArray($excel_data);
     $excel_obj->addWorksheet($excel_obj->charset(L('exp_od_order'), CHARSET));
     $excel_obj->generateXML($excel_obj->charset(L('exp_od_order'), CHARSET) . $_GET['curpage'] . '-' . date('Y-m-d-H', time()));
 }

 /**
  * 导出预存款明细excel
  *
  * @param array $data
  */
 private function createmxExcel($data = array())
 {
     Language::read('export');
     import('libraries.excel');
     $excel_obj = new Excel();
     $excel_data = array();
     //设置样式
     $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
     //header
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_member'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_ctime'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_av_money'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_freeze_money'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_system'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_mshu'));
     foreach ((array) $data as $k => $v) {
         $tmp = array();
         $tmp[] = array('data' => $v['lg_member_name']);
         $tmp[] = array('data' => date('Y-m-d H:i:s', $v['lg_add_time']));
         if (floatval($v['lg_av_amount']) == 0) {
             $tmp[] = array('data' => '');
         } else {
             $tmp[] = array('format' => 'Number', 'data' => ncPriceFormat($v['lg_av_amount']));
         }
         if (floatval($v['lg_freeze_amount']) == 0) {
             $tmp[] = array('data' => '');
         } else {
             $tmp[] = array('format' => 'Number', 'data' => ncPriceFormat($v['lg_freeze_amount']));
         }
         $tmp[] = array('data' => $v['lg_admin_name']);
         $tmp[] = array('data' => $v['lg_desc']);
         $excel_data[] = $tmp;
     }
     $excel_data = $excel_obj->charset($excel_data, CHARSET);
     $excel_obj->addArray($excel_data);
     $excel_obj->addWorksheet($excel_obj->charset(L('exp_mx_rz'), CHARSET));
     $excel_obj->generateXML($excel_obj->charset(L('exp_mx_rz'), CHARSET) . $_GET['curpage'] . '-' . date('Y-m-d-H', time()));
 }

 /**
  * 导出预存款明细excel
  *
  * @param array $data
  */
 private function createmxExcel($data = array())
 {
     Language::read('export');
     import('libraries.excel');
     $excel_obj = new Excel();
     $excel_data = array();
     //设置样式
     $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
     //header
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_member'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_ctime'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_money'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_type'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_system'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_stype'));
     $excel_data[0][] = array('styleid' => 's_title', 'data' => L('exp_mx_mshu'));
     $state_cn = array(Language::get('admin_predeposit_log_stage_recharge'), Language::get('admin_predeposit_log_stage_cash'), Language::get('admin_predeposit_log_stage_order'), Language::get('admin_predeposit_log_stage_artificial'), Language::get('admin_predeposit_log_stage_system'));
     foreach ((array) $data as $k => $v) {
         $tmp = array();
         $tmp[] = array('data' => $v['pdlog_membername']);
         $tmp[] = array('data' => date('Y-m-d H:i:s', $v['pdlog_addtime']));
         $tmp[] = array('format' => 'Number', 'data' => ncPriceFormat($v['pdlog_price']));
         $tmp[] = array('data' => $v['pdlog_type'] == 1 ? Language::get('admin_predeposit_pricetype_freeze') : Language::get('admin_predeposit_pricetype_available'));
         $tmp[] = array('data' => $v['pdlog_adminname']);
         $tmp[] = array('data' => str_replace(array('recharge', 'cash', 'order', 'admin', 'system'), $state_cn, $v['pdlog_stage']));
         $tmp[] = array('data' => $v['pdlog_desc']);
         $excel_data[] = $tmp;
     }
     $excel_data = $excel_obj->charset($excel_data, CHARSET);
     $excel_obj->addArray($excel_data);
     $excel_obj->addWorksheet($excel_obj->charset(L('exp_mx_rz'), CHARSET));
     $excel_obj->generateXML($excel_obj->charset(L('exp_mx_rz'), CHARSET) . $_GET['curpage'] . '-' . date('Y-m-d-H', time()));
 }