php后台execl日期筛选导出功能程序源码
php后台execl数据日期筛选导出功能程序源码
- public function export_excel(){
-
- set_time_limit(3600);
- vendor('Excel.PHPExcel');
- //$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
- //PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
- $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
- $cacheSettings = array('memoryCacheSize' => '16MB');
- PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
- $objPHPExcel = new PHPExcel();
- $file_name = 'product'.date('Ymd');
-
- $g_name = I('g_name', '', 'htmlspecialchars,trim');
- $g_id = I('g_id', 0, 'intval');
- $g_common = I('g_common', '', 'htmlspecialchars,trim');
- $g_sn = I('g_sn', '', 'htmlspecialchars,trim');
- $g_cate = I('g_cate', 0, 'intval');
- $g_brand = I('g_brand', '', 'htmlspecialchars,trim');
- $g_brand_id = I('g_brand_id', 0, 'intval');
- $g_is_drug = I('is_drug', 0, 'intval');
- $g_spec = I('g_spec', '', 'htmlspecialchars,trim');
- $g_spec_id = I('g_spec_id', 0, 'intval');
- $g_manufacturer = I('g_manufacturer', '', 'htmlspecialchars,trim');
- $g_manufacturer_id = I('g_manufacturer_id', 0, 'intval');
- $g_license = I('g_license', 0, 'htmlspecialchars,trim');
- $g_license_id = I('g_license_id', 0, 'intval');
- $g_is_on_sale = I('g_is_on_sale', 0, 'intval');
- $g_department = I('g_department', 0, 'intval');
- $g_stock = I('g_stock', 0, 'intval');
- $g_is_code = I('g_is_code', 0, 'intval');
- $g_code = I('g_code', 0, 'htmlspecialchars,trim');
- $g_purchase_cate = I('g_purchase_cate', 0, 'intval');
- $add_time1 = I('add_time1', '', 'htmlspecialchars,trim');
- $add_time2 = I('add_time2', '', 'htmlspecialchars,trim');
-
- $field = array('g.new_goods_sn, g.goods_name, g.keywords, g.effect, g.common_name, g.license_number, gs.spec_name, g.is_drug, g.drug_description, g.form, g.goods_name as new_goods_name, g.material, g.leading_effect, g.crowd, gs.spec_name as new_spec_name, g.instructions, m.manufacturer_name, b.brand_name, gt.type_name, g.is_import, c.cate_name, g.haipin_id, g.is_on_sale, g.stock, g.code');
- $table = 'pro_goods g';
- $where = array('g.status'=>1, 'g.cate_id'=>array('neq', 9));
- if($g_id){
- $kwhere['g.goods_id'] = array('eq', $g_id);
- }else{
- if($g_name){
- $kwhere['g.goods_name'] = array('like',array('%'.$g_name.'%'));
- }
- }
- if($g_common){
- $kwhere['g.common_name'] = array('like',array('%'.$g_common.'%'));
- }
- if($g_sn){
- $kwhere['g.new_goods_sn'] = array('like',array('%'.$g_sn.'%'));
- }
- if($g_cate){
- $kwhere['g.cate_id'] = array('eq', $g_cate);
- }
- if($g_brand_id){
- $kwhere['g.brand_id'] = array('eq', $g_brand_id);
- }else{
- if($g_brand){
- $kwhere['g.brand_id'] = array('in', $this->getList('Brand', 'brand_name', $g_brand, 'b_id'));
- }
- }
- if($g_is_drug){
- $kwhere['g.is_drug'] = array('eq', $g_is_drug);
- }
- if($g_spec_id){
- $kwhere['g.spec_id'] = array('eq', $g_spec_id);
- }else{
- if($g_spec){
- $kwhere['g.spec_id'] = array('in', $this->getList('Goods_spec', 'spec_name', $g_spec, 's_id'));
- }
- }
- if($g_manufacturer_id){
- $kwhere['g.manufacturer_id'] = array('eq', $g_manufacturer_id);
- }else{
- if($g_manufacturer){
- $kwhere['g.manufacturer_id'] = array('in', $this->getList('Manufacturer', 'manufacturer_name', $g_manufacturer, 'm_id'));
- }
- }
- if($g_license_id){
- $kwhere['g.goods_id'] = array('eq', $g_license_id);
- }else{
- if($g_license){
- $kwhere['g.license_number'] = array('like',array('%'.$g_license.'%'));
- }
- }
- if($g_is_on_sale){
- $kwhere['g.is_on_sale'] = array('eq', $g_is_on_sale-1);
- }
- if($g_department){
- $kwhere['g.department_id'] = array('eq', $g_department);
- }
- if($g_stock){
- $g_stock = $g_stock-1;
- if(empty($g_stock)){
- $kwhere['g.stock'] = array('eq', $g_stock);
- }else{
- $kwhere['g.stock'] = array('gt', 0);
- }
- }
- if($g_is_code){
- $g_is_code = $g_is_code-1;
- if(empty($g_is_code)){
- $kwhere['g.code'] = array('eq', '');
- }else{
- $kwhere['g.code'] = array('neq', '');
- }
- }
- if($g_code){
- $kwhere['g.code'] = array('like', array('%'.$g_code.'%'));
- }
- if($g_purchase_cate){
- $kwhere['g.purchase_cate'] = array('eq', $g_purchase_cate);
- }
- //日期索引
- if($add_time1){
- if($add_time2){
- $add_time1 = strtotime($add_time1.' 00:00:00');
- $add_time2 = strtotime($add_time2.' 23:59:59');
- if($add_time2 < $add_time1){
- $this->error('申请时间到期时间不能小于开始时间!');
- }else{
- $kwhere['g.add_time'] = array('between', array($add_time1, $add_time2));
- }
- }else{
- $add_time1 = strtotime($add_time1.' 00:00:00');
- $kwhere['g.add_time'] = array('egt', $add_time1);
- }
- }else{
- if($add_time2){
- $add_time2 = strtotime($add_time2.' 23:59:59');
- $kwhere['g.add_time'] = array('elt', $add_time2);
- }
- }
-
- if(count($kwhere)){
- $kwhere['_logic'] = 'AND';
- $where['_complex'] = $kwhere;
- }
-
- $count = M('Goods')->table($table)
- ->join('LEFT JOIN pro_brand b ON g.brand_id=b.b_id')
- ->join('LEFT JOIN pro_goods_spec gs ON g.spec_id=gs.s_id')
- ->join('LEFT JOIN pro_cate c ON g.cate_id=c.cate_id')
- ->join('LEFT JOIN pro_manufacturer m ON g.manufacturer_id=m.m_id')
- ->join('LEFT JOIN pro_goods_type gt ON g.type_id=gt.t_id')
- ->where($where)->count();
-
- $page = 1;
- $page_number = 1024;
- $count += $page_number;
- $arr = array();
- $is_drug = C(IS_DRUG);
- $begin = ($page -1) * $page_number;
- while($begin < $count){
- $res = M('Goods')->table($table)
- ->join('LEFT JOIN pro_brand b ON g.brand_id=b.b_id')
- ->join('LEFT JOIN pro_goods_spec gs ON g.spec_id=gs.s_id')
- ->join('LEFT JOIN pro_cate c ON g.cate_id=c.cate_id')
- ->join('LEFT JOIN pro_manufacturer m ON g.manufacturer_id=m.m_id')
- ->join('LEFT JOIN pro_goods_type gt ON g.type_id=gt.t_id')
- ->where($where)->field($field)->limit($begin, $page_number)->select();
-
- foreach($res as $key=>$val){
- $res[$key]['is_drug'] = $is_drug[$val['is_drug']];
- $res[$key]['is_import'] = $val['is_import']? '是': '';
- $res[$key]['is_on_sale'] = empty($val['is_on_sale'])? '否': '是';
- $new_key = $begin + $key;
- $arr[] = $res[$key];
- }
- unset($res);
- $page ++;
- $begin = ($page -1) * $page_number;
- }
-
- $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
- $title = array("新编码", "商品名称", "商品关键字", "商品功效", "通用名称", "批准文号", "商品规格", "是否处方药", "处方药说明", "剂型", "产品名称", "主要原料", "主要作用", "适宜人群", "产品规格", "用法用量", "生产企业", "品牌名称", "药品类别", "是否进口", "商品分类", "海品id", "是否采购", "库存", "条形码");
-
- $objPHPExcel->setActiveSheetIndex(0);
- $j =0;
- foreach($title as $t_val){
- $index = $letter[$j];
- $objPHPExcel->getActiveSheet()->setCellValue($index."1", $t_val);
- $j++;
- }
-
- $arr_count = count($arr);
- $i = 2;
-
- foreach($arr as $key=>$val){
- if($key < $arr_count){
- $j =0;
- foreach($val as $val2){
- $index = $letter[$j];
- $objPHPExcel->getActiveSheet()->setCellValue($index.$i, $val2);
- $j++;
- }
- $i++;
- }
- }
-
- $objPHPExcel->getActiveSheet()->setTitle('商品信息表');
-
- header('Content-Type:application/vnd.ms-excel');
- header('Content-Disposition:attachment;filename="'.$file_name.'.xls"');
- header('Cache-Control:max-age=0');
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
- $objWriter->save('php://output');
- exit;
-
- }
复制代码
|
|