churen/tool/stats/lib/Manage.php

<?php
namespace Stats\Lib;
use Dever;
Class Manage
{
	public function index_api()
	{
		$data = array();
		$start = Dever::input('start');
		$end = Dever::input('end');
		if (!$start && !$end) {
			$start=Dever::maketime(date('Y-m-01') . ' 00:00:00');
            $end = strtotime(date('Y-m-d 23:59:59',
                strtotime(date('Y-m-01')."+1 month - 1 day")));
			$data['start'] = date('Y-m-d',$start);
			$data['end'] = date('Y-m-d',$end);
		} else {
			$data['start'] = date('Y-m-d',$start);
			$data['end'] = date('Y-m-d',$end);
		}
		$fastart = Dever::input('fastart');
		$faend = Dever::input('faend');
		if (!$fastart && !$faend) {
			$fastart=Dever::maketime(date('Y-m-01') . ' 00:00:00');
            $faend = strtotime(date('Y-m-d 23:59:59',
                strtotime(date('Y-m-01')."+1 month - 1 day")));
			$data['fastart'] = date('Y-m-d',$fastart);
			$data['faend'] = date('Y-m-d',$faend);
		} else {
			$data['fastart'] = date('Y-m-d',$fastart);
			$data['faend'] = date('Y-m-d',$faend);
		}
		$fstart = Dever::input('fstart');
		$fend = Dever::input('fend');
		if (!$fstart && !$fend) {
			$fstart=Dever::maketime(date('Y-m-01') . ' 00:00:00');
            $fend = strtotime(date('Y-m-d 23:59:59',
                strtotime(date('Y-m-01')."+1 month - 1 day")));
			$data['fstart'] = date('Y-m-d',$fstart);
			$data['fend'] = date('Y-m-d',$fend);
		} else {
			$data['fstart'] = date('Y-m-d',$fstart);
			$data['fend'] = date('Y-m-d',$fend);
		}
		$data['type'] = array(1 => '体验店',2 => '零售店',10 => '平台商城');
		return Dever::render('out', $data);
	}
	public function list_api()
	{
		$status = Dever::db('shop/sell_order')->config['status_manage'];
		$start = Dever::input('start');
		$end = Dever::input('end');
		$fastart = Dever::input('fastart');
		$faend = Dever::input('faend');
		$fstart = Dever::input('fstart');
		$fend = Dever::input('fend');
		$cate = Dever::input('cate');
		$name = Dever::input('goods_name');
		$shop_name = Dever::input('shop_name');
		$order_num = Dever::input('order_num');
		if ($start) {
			$where['start'] = strtotime($start);
		}
		if ($end) {
			$where['end'] = strtotime($end);
		}
		if ($fastart) {
			$where['fastart'] = strtotime($fastart);
		}
		if ($faend) {
			$where['faend'] = strtotime($faend);
		}
		if ($fstart) {
			$where['fstart'] = strtotime($fstart);
		}
		if ($fend) {
			$where['fend'] = strtotime($fend);
		}
		if ($cate && $cate>0) {
			$where['shop_type'] = $cate;
		}
		if ($name) {
			$where['name'] = $name;
		}
		if ($shop_name) {
			$shop = Dever::db('shop/info')->find(array('name'=>$name));
			if ($shop) {
				$where['shop_id'] = $shop['id'];
			}
		}
		if ($order_num) {
			$where['order_num'] = $order_num;
		}
		$where['state'] = 1;
		$data = Dever::db('shop/sell_order')->getExcel($where);
		$header = array('门店类型', '门店编号', '门店名称', '订单号', '商品编码', '存货编码', '商品名称', '规格型号', '税率', '商品单价', '购买数量', '商品合计金额', '代理优惠金额', '商品实付金额', '礼品卡金额', '钱包金额','商品支付金额', '礼品卡名称', '礼品卡卡号', '代理商注册时间', '下单时间', '付款时间', '发货时间', '完成时间', '订单总数量', '订单总金额', '订单实付金额', '礼品卡金额', '钱包金额', '优惠券金额', '代理优惠金额', '订单状态', '退款状态', '退款时间', '退款金额','退卡金额','退现金金额', '收件人', '收件人手机', '收件人地址');

		$body = array();
		if ($data) {
			foreach ($data as $k => $v) {
				$shop_type = '';
				if ($v['shop_type'] == 1) {
					$shop_type = '体验店';
				} elseif ($v['shop_type'] == 2) {
					$shop_type = '平台商城';
				}
				$shop = Dever::db('shop/info')->find($v['shop_id']);
				$sid = '';
				$shop_name = '';
				if ($shop) {
					if (isset($shop['sid']) && $shop['sid'] ) {
						$sid = $shop['sid'];
					}
					if (isset($shop['name']) && $shop['name']) {
						$shop_name = $shop['name'];
					}
				}
				$v['card'] = '';
				if ($v['card_code_id']) {
			            $code = Dever::db('card/code')->find($v['card_code_id']);
			            if ($code) {
			                $card_info = Dever::db('card/info')->find($code['card_id']);
			                if ($card_info) {
			                    if (!$code['total_cash']) {
			                        $code['total_cash'] = $card_info['value'];
			                    }
			                    $v['card'] = $card_info['name'];
			                    $v['card_code'] = $code['card'];
			                }
			            }
			        }
			        $zhuce = '';
					$address_contact = '';
					$address_mobile = '';
					$address_info = '';
					if ($v['address_id'] > 0) {
	        			$address = Dever::load('passport/address')->getOne($v['uid'], $v['address_id']);
	        			if($address) {
	        				$address_contact = $address['contact'];
	        				$address_mobile = $address['mobile'];
	        				$address_info = $address['address_info'];
	        				$member = Dever::db('agent/member')->find(array('mobile'=>$address['mobile']));
	        				if ($member) {
	        					$zhuce = date('Y-m-d H:i',$member['cdate']);
	        				}
	        			}
	        		}
				$ps = Dever::db('shop/sell_order_ps')->find(array('order_id' => $v['id']));
				$cdate = strstr($v['cdate'], '-') ? $v['cdate'] : date('Y-m-d H:i', $v['cdate']);
	            $paydate = isset($v['paydate']) && $v['paydate'] ? date('Y-m-d H:i', $v['paydate']) : '-';
	            $operdate = ($ps && $ps['cdate']) ? date('Y-m-d H:i', $ps['cdate']) : '-';
	            $fdate = '-';
				if ($v['fdate']) {
					$fdate = date('Y-m-d H:i',$v['fdate']);
				}
				$refund_status = '';
		        $refund_cdate = '';
		        $refund_cash = '';
		        $ka = 0;
	            $money = 0;
	            
				$goods = Dever::db('shop/sell_order_goods')->select(Array('order_id'=>$v['id']));
				foreach($goods as $k1 => $v1) {
					$goods_info = Dever::load('goods/lib/info')->getInfoBySku($v1['goods_id'], $v1['sku_id']);
					if (isset($goods_info['sku'])) {
	                    $sku = $goods_info['sku']['string'];
	                } else {
	                    $sku = '';
	                }
	                if ($k1 == 0) {
	                	$total_cash = Dever::load("shop/lib/manage.getTotalCash", $v['id']);
	                } else {
	                	$total_cash = 0;
	                }
	                if ($v['status'] == 8 || $v['status'] == 6) { 
	                	$refund_status = '已退款';
		            	$refund_cash = $v1['price']*$v1['num'];
	                    if ($v['status'] == 8) {
	                    	if ($v['refund_cash'] && $v['refund_cash']>0) {
		                    	$money = $v['refund_cash']+$v['wallet_cash'];
		                    }
		                    if ($v['card_code_cash'] && $v['card_code_cash'] >0) {
		                    	$ka = $v['card_code_cash'];
		                    }
		                    if ($k1 == 0) {
		                    	$ka = $ka;
		                    	$money = $money;
		                    } else {
		                    	$ka = 0;
		                    	$money = 0;
		                    }
	                    } elseif ($v['status'] == 6) {
	                    	if ($v1['status'] == 3) {
	                    		if ($v['card_code_id'] && $v['card_code_id'] >0) {
	                    			
		                    		$ka = $v['refund_cash'];
		                    	} else {
		                    		$money = $v['refund_cash'];
		                    	}
		                    	$refund_status = '已退款';
	                    	} else {
	                    		$refund_status = '正常';
	                    		$refund_cash = 0;
	                    		$ka = 0;
	                    		$money = 0;
	                    	}
	                    	
	                    }
	                    
	                    // print_R($ka);die;
	                    
	                    $refund_cdate = '';
	                    if ($v['shop_type'] == 1) {
	                    	$refund = Dever::db('shop/sell_order_refund')->find(array('order_id'=>$v['id']));
							if ($refund) {
								$refund_cdate = date('Y-m-d H:i',$refund['cdate']);
							}
	                    }
		            } else {
		            	if ($v['refund_status'] == 3 && $v1['status'] == 3) {
		            		$refund_cash = $v1['num']*$v1['price'];
		                	if ($v['card_code_id'] && $v['card_code_id'] >0) {
	                    		$ka = $v['refund_cash'];
	                    	} else {
	                    		$money = $v['refund_cash'];
	                    	}
		                    	// $refund_status = '已退款';
		                	$refund_status = '有退款';
		                    $refund_cdate = '';
		                    if ($v['shop_type'] == 1) {
		                    	$refund = Dever::db('shop/sell_order_refund')->find(array('order_id'=>$v['id']));
								if ($refund) {
									$refund_cdate = date('Y-m-d H:i',$refund['cdate']);
								}
		                    }
		            	} else {
		            		$refund_cdate = '';
		                    $refund_cash = '0';
		                    $refund_status = '未退款';
		            	}
		            }
		            // print_R($goods);die;
		            // print_R($goods_info);die;
	                if ($goods_info['price_type'] > 2 && isset($goods_info['goods']) && is_array($goods_info['goods'])) {
	                	$heji = 0;
	                	$heji = $this->total($goods,$v1['num']);
	                	// print_R($v);die;
						foreach ($goods_info['goods'] as $key => $val) {

							$dnum = round($val['num']*$v1['num'],2);
							$bili = round($val['price']*$dnum /$heji,2);#比例
							// print_R($bili);die;
							$price_money = $val['price']*$dnum*$v['discount_cash'];#支付
							
							if ($v['card_code_cash'] && $v['card_code_cash']>0){
								$card_price = round(($v['card_code_cash']/$v['oprice']*($val['price']*$dnum)),2);
							} else {
								$card_price = 0;
							}
							$wallet_price = round(($v['wallet_cash']/$v['oprice']*($val['price']*$dnum)),2);
							$goodsfu = 0;
							if ($v1['discount_id']<=0) {
								$youhui = 0;
								if ($card_price + $wallet_price >=$val['price']*$dnum){
									$goodsfu = 0;
								} elseif ($card_price + $wallet_price ==0) {
									$goodsfu = round($val['price']*$dnum,2);
								} else {
									$goodsfu = round($val['price']*$dnum-($card_price + $wallet_price),2);
								}
								// print_R($card_price);die;
								
							} else {
								$act = Dever::db('act/discount')->find($v['discount_id']);
								if ($act && $act['num']) {
									$act_num = $act['num']/10;
								}
								if ($card_price + $wallet_price == 0) {
									$goodsfu = round($val['price']*$dnum*$act_num,2);
									$youhui = round($val['price']*$dnum-$goodsfu);
								} elseif ($card_price + $wallet_price >=$val['price']*$dnum) {
									$goodsfu = 0;
									$youhui = round($val['price']*$dnum-$goodsfu-($card_price + $wallet_price),2);
								} else {
									$goodsfu = round($val['price']*$dnum-($card_price + $wallet_price),2);
									$youhui = round($val['price']*$dnum-$goodsfu,2);
								}
								

								// $youhui = ($val['price']*$dnum-($val['price']*$dnum/$heji)*$v1['price'])*$v1['num'];
							}
							
							// $goodsfu = $val['price']*$dnum-$youhui-$card_price-$wallet_price;
							if ($key == 0 && $k1 ==0) {
								$price = $v['price'];//round($v1['price']*$v1['num']-$v1['discount_cash'],2);
								$shifu = round($v['price'],2);//实付金额
								$order_num = $v1['num'];#订单总数量
								$oprice = $v['oprice'];//round($v1['price']*$v1['num'],2);#订单总金额
								$coupon_cash = round($v1['coupon_cash'],2);
								$discount_cash = round($v1['discount_cash'],2);
								$card_code_cash = round($v['card_code_cash'],2);
								$wallet_cash = round($v['wallet_cash'],2);
								if ($v['status'] == 8) {
									$refund_cash = $refund_cash;
									$ka = $ka;
									$money = 0;
								}

							} else {
								$price = 0;
								$shifu = 0;
								$order_num = 0;
								$oprice = 0;#订单总金额
								$coupon_cash = 0;
								$discount_cash = 0;
								$card_code_cash = 0;
								$wallet_cash = 0;
								if ($v['status'] == 8) {
									$refund_cash = 0;
									$ka = 0;
									$money = 0;
								}
							}
							

							$d = array
							(
								$shop_type,
								$sid,
								$shop_name,
								$v['order_num'],
								$val['code']."\t",
								$goods_info['cunhuo_code']."\t",
								$val['name'].'('.$goods_info['name'].')',
								$sku,
								$goods_info['tax'],

								round($val['price'],2),
								$dnum,
								round(($val['price']*$dnum),2),
								round($youhui,2),
								$goodsfu,
								$card_price,
								$wallet_price,
								$price,//$price,//$price_money,

								$v['card'],
								$v['card_code_card']."\t",
								$zhuce,
								$cdate,
								$paydate,
								$operdate,
								$fdate,

								$order_num,
								$oprice,
								$price,
								$card_code_cash,
								$wallet_cash,
								$coupon_cash,
								$discount_cash,

								$status[$v['status']],
			                    $refund_status,
			                    $refund_cdate,

			                    round($refund_cash, 2),
			                    round($ka,2),
			                    round($money,2),

			                    $address_contact,
			                    $address_mobile,
			                    $address_info,
							);
							$body[] = $d;
						}
					} else {
						if ($ka > 0 || $money > 0) {
							$st = Dever::db('shop/sell_order_goods')->find(Array('order_id'=>$v['id'],'status'=>3));
							if ($v1['goods_id'] == $st['goods_id']) {
								$ka = round($ka,2);
								$money = round($money,2);
							} else {
								$ka = 0;
								$money = 0;
							}
						} 
						if ($k1 == 0 ) {
							$discount_cash = round($v['discount_cash'],2);
							$card_code_cash = round($v['card_code_cash'],2);
							$wallet_cash = round($v['wallet_cash'],2);
							$zhifu = $v['price'];
							$total = $v['oprice']-($card_code_cash + $wallet_cash + $discount_cash+$zhifu);
							$order_num = $v['num'];//订单总数量
							$oprice = round($v['oprice'],2);#订单总金额
							$coupon_cash = round($v['coupon_cash'],2);
							// $ka = round($ka,2);
							// $money = round($money,2);
						} else {
							$discount_cash = 0;
							$card_code_cash = 0;
							$wallet_cash = 0;
							$total = 0;
							$zhifu = 0;
							$order_num = 0;
							$oprice = 0;
							$coupon_cash = 0;
							// $ka = 0;
							// $money = 0;
						}
						$dyouhui = round($v1['discount_cash'],2);
						$dcard_price = $v['card_code_cash']/$v['oprice']*($v1['price']*$v1['num']);
						$dwallet_price = $v['wallet_cash']/$v['oprice']*($v1['price']*$v1['num']);
						$dgoodsfu = $v1['price']*$v1['num']-$dyouhui-$dcard_price-$dwallet_price;
						// print_R($v1);die;
						// $dpaymoney = round($v1['price']/$v['oprice']*$v1['price']*$v1['num'],2);
						if ($dgoodsfu<0){
							$dgoodsfu = 0;
						}
						// print_R($ka);
						$d = array
						(
							$shop_type,
							$sid,
							$shop_name,
							$v['order_num'],
							$goods_info['code']."\t",
							$goods_info['cunhuo_code']."\t",
							$goods_info['name'],
							$sku,
							$goods_info['tax'],

							round($v1['price'],2),
							round($v1['num'],2),
							round(($v1['price']*$v1['num']),2),

							round($dyouhui,2),
							$dgoodsfu,
							$dcard_price,
							$dwallet_price,
							$zhifu,//$dgoodsfu,//

							$v['card'],
							$v['card_code_card']."\t",
							$zhuce,
							$cdate,
							$paydate,
							$operdate,
							$fdate,

							$order_num,
							sprintf('%01.2f',$total_cash),
							$zhifu,
							$card_code_cash,
							$wallet_cash,
							$coupon_cash,//优惠券
		                    $discount_cash,//代理优惠

		                    $status[$v['status']],
		                    $refund_status,
		                    $refund_cdate,

		                    round($refund_cash, 2),
		                    $ka,
		                    $money,

		                    $address_contact,
		                    $address_mobile,
		                    $address_info,

						);
						$body[] = $d;

					}					
				}
				
			}

		}
		if (!$body) {
			Dever::alert('此时段没有数据，请重新选择条件');
		}
		$file = '商品销售相关数据';
        return Dever::excelExport($body, $header, $file);
	}
	
	public function total($goods,$num){
		if ($goods) {
			$price = 0;
			foreach ($goods as $k => $v) {
				$price += $v['price'] * $v['num']*$num;
			}
			return $price;
		}
	}
	public function duihuan_api()
	{
		$start = Dever::input('start');
		$end = Dever::input('end');
		$fastart = Dever::input('fastart');
		$faend = Dever::input('faend');
		$fstart = Dever::input('fstart');
		$fend = Dever::input('fend');
		$name = Dever::input('goods_name');
		$shop_name = Dever::input('shop_name');
		$order_num = Dever::input('order_num');
		$parent_type = Dever::input('cate');
		if ($start) {
			$where['start'] = strtotime($start);
		}
		if ($end) {
			$where['end'] = strtotime($end);
		}
		if ($fastart) {
			$where['fastart'] = strtotime($fastart);
		}
		if ($faend) {
			$where['faend'] = strtotime($faend);
		}
		if ($fstart) {
			$where['fstart'] = strtotime($fstart);
		}
		if ($fend) {
			$where['fend'] = strtotime($fend);
		}
		if ($parent_type && $parent_type>0) {
			$where['parent_type'] = $parent_type;
		} else {
			$where['parent_type'] = '1,3';
		}
		
		if ($name) {
			$where['name'] = $name;
		}
		if ($shop_name) {
			$where['type'] = 1;
			$shop = Dever::db('shop/info')->find(array('name'=>$name));
			if ($shop) {
				$where['type_id'] = $shop['id'];
			}
		}
		if ($order_num) {
			$where['order_num'] = $order_num;
		}
		$where['state'] = 1;
		$data = Dever::db('shop/buy_order')->getDui($where);
		$header = array('门店类型', '门店编号', '门店名称', '订单号', '商品编码', '存货编码', '商品名称', '规格型号', '税率', '商品单价', '购买数量', '商品合计金额',  '代理商注册时间', '下单时间', '付款时间', '发货时间', '完成时间', '订单总数量', '订单总金额', '订单实付金额', '资金账户抵扣金额', '订单状态',  '退款状态', '退款时间', '退款金额',  '收件人', '收件人手机', '收件人地址');
		$body = array();
		if ($data) {
			foreach($data as $k => $v){
				$shop = Dever::db('shop/info')->find($v['type_id']);
				if ($shop['type'] ==1) {
					$shop_type = '体验店';
				} elseif ($shop['type'] ==10) {
					$shop_type = '平台商城';
				}
				$zhuce = '';
				$address_contact = '';
				$address_mobile = '';
				$address_info = '';
				$shifu = 0;
				if ($v['parent_type'] == 3) {
					$parent_order = Dever::db('agent/dh_order')->find($v['parent_order_id']);
					$shifu = 0;
					$record_cash = 0;
				} else {
					$shifu = round($v['price'],2);
					$record_cash = round($v['record_cash'],2);
				}
				if ($v['type'] == 1) {
		            $type_info = Dever::db('shop/info')->find($v['type_id']);
		        } elseif ($info['type'] == 2) {
		            $type_info = Dever::db('store/info')->find($v['type_id']);
		        } elseif ($info['type'] == 3) {
		            $type_info = Dever::db('store/info')->find($v['type_id']);
		        }
				if (isset($parent_order) && $parent_order && isset($parent_order['uid']) && $parent_order['uid']) {
            		if ($parent_order['address_id']) {
                		$address = Dever::load('passport/address')->getOne($parent_order['uid'], $parent_order['address_id']);
                		if($address) {
	        				$address_contact = $address['contact'];
	        				$address_mobile = $address['mobile'];
	        				$address_info = $address['address_info'];
	        				$member = Dever::db('agent/member')->find(array('mobile'=>$address['mobile']));
	        				if ($member) {
	        					$zhuce = date('Y-m-d H:i',$member['cdate']);
	        				}
	        			}
                	}
                } elseif (isset($type_info) && $type_info) {
                	$address_contact = $type_info['truename'];
                	$address_mobile = $type_info['mobile'];
	        		$address_info = $type_info['address'];
                }
				$goods = Dever::db('shop/buy_order_goods')->select(array('order_id' => $v['id']));
        		$goods_status = Dever::db('shop/buy_order_goods')->config['status'];
        		foreach ($goods as $k1 => $v1) {
        			$goods_info = Dever::load('goods/lib/info')->getInfoBySku($v1['goods_id'], $v1['sku_id']);
        			if (isset($goods_info['sku'])) {
	                    $sku = $goods_info['sku']['string'];
	                } else {
	                    $sku = '';
	                }
	                if ($v['cdate']) {
	                	$cdate = date('Y-m-d H:i',$v['cdate']);
						
	                }
	                $fadate = '';
	                if ($v['fadate']) {
	                	$fadate = date('Y-m-d H:i',$v['fadate']);
	                }
	                $fdate = '';
	                if ($v['fdate']) {
	                	$fdate = date('Y-m-d H:i',$v['fdate']);
	                }
	                
	                if ($v1['status'] == 1) {
	                	$refund_status = '正常';
	                } elseif ($v1['status'] == 2) {
	                	$refund_status = '已申报';
	                } elseif ($v1['status'] == 3) {
	                	$refund_status = '已退款';
	                }
	                $tk = Dever::db('shop/buy_order_refund')->find(array('order_id'=>$v['id'],'order_goods_id'=>$v1['id']));
	                $refund_cdate = '-';
	                if ($tk) {
	                	$refund_cdate = date('Y-m-d H:i',$tk['cdate']);
	                }
	                if ($k1 == 0) {
	                	$num = $v['num'];
	                	$order_price = round($v['price'],2);
	                	$shifu = $shifu;
	                	$record_cash = $record_cash;
	                	$refund_cash = round($v['refund_cash'],2);
	                } else {
	                	$num = 0;
	                	$order_price = 0;
	                	$shifu = 0;
	                	$record_cash = 0;
	                	$refund_cash = 0;
	                }

        			$d = array
					(
						$shop_type,
						$shop['sid'],
						$shop['name'],
						$v['order_num'],
						$goods_info['code']."\t",
						$goods_info['cunhuo_code']."\t",
						$goods_info['name'],
						$sku,
						$goods_info['tax'],

						round($v1['price'],2),
						round($v1['num'],2),
						round($v1['price']*$v1['num'],2),

						$zhuce,
						$cdate,
						$cdate,
						$fadate,
						$fdate,

						$num,
						$order_price,
						$shifu,
						$record_cash,
						$goods_status[$v1['status']],
						$refund_status,
						$refund_cdate,
						$refund_cash,



						$address_contact,
						$address_mobile,
						$address_info,
					);
					$body[] = $d;
        		}
				
			}
		}
		// print_R($body);die;
		if (!$body) {
			Dever::alert('此时段没有数据，请重新选择条件');
		}
		$file = '代理商兑换订单';
        return Dever::excelExport($body, $header, $file);
	}
	public function memberGoods_api()
	{
		$name = Dever::input('name');
		$member_name = Dever::input('member_name');
		$card_num = Dever::input('card_num');
		$mobile = Dever::input('mobile');
		$start = Dever::input('start');
		$end = Dever::input('end');
		$rstart = Dever::input('rstart');
		$rend = Dever::input('end');
		$cstart = Dever::input('cstart');
		$cend = Dever::input('cend');
		if ($card_num) {
			$code = Dever::db('card/code')->find(array('card'=>$card_num));
			if ($code){
				$where['sku_id'] = "t_1.sku_id=".$code['id'];
			}
		}

		if ($name) {
			$where['name'] = "t_1.name like '%$name%'";
		} 
		if ($cstart) {
			$where['cstart'] = 't_1.cdate+(360*86400)>='. strtotime($cstart);
		} 
		if ($cend) {
			$where['cend'] = 't_1.cdate+(360*86400)<='.strtotime($cend);
		} 
		if ($start) {
			$where['start'] = 't_1.cdate>='.strtotime($start);
		}
		if ($end) {
			$where['end'] = 't_1.cdate<='.strtotime($end);
		} 
		
		if ($member_name) {
			$where['member_name'] = "t_2.name like '%$member_name%'";
		} 
		if ($mobile) {
			$where['mobile'] = 't_2.mobile='.$mobile;
		} 
		if ($rstart) {
			$where['rstart'] = 't_2.cdate>='.strtotime($rstart);
		} 
		if ($rend) {
			$where['rend'] = 't_2.cdate<='.strtotime($rend);
		} 
		$where['t_1.state'] = 't_1.state=1';
		$where['t_1.type'] = "t_1.type in (1,2)";
		$header = array('权益名称','会员等级','姓名','手机号','卡号','类型','数量','单件价值','总价值','已兑换价值','已使用价值','未兑换价值','未使用价值','备注','获取方式','获取时间','代理商注册时间','礼品卡有效期');
		$body = array();
		if ($where) {
			$where = implode(' and ', $where);
			$data = Dever::db('agent/member_goods')->fetchAll("select t_1.*,t_2.id as aid from churen_agent_member_goods as t_1 left join churen_agent_member as t_2 on t_1.mid = t_2.id where " . $where,array(),array(20, 'list'));
			
			if ($data) {
				foreach ($data as $k => $v) {
					$role_name = '';
					$member_name = '';
					$member_mobile = '';
					$member = Dever::db('agent/member')->find($v['mid']);
					if ($member) {
						$member_cdate = '-';
			            if($member['cdate']){
			                $member_cdate = date('Y-m-d H:i',$member['cdate']);
			            }
						$member_name = $member['name'];
						$member_mobile = $member['mobile'];
						$role = Dever::db('setting/role')->find($member['role']);
						if ($role) {
							$role_name = $role['name'];
						}
					}
					$wd = ($v['total_num'] - $v['sell_num'])*$v['price'];
            		$yd = $v['sell_num']*$v['price'];
					$card = '-';
		            $mianzhi = '0';
		            $money = '-';
		            $youxiao = '';
		            $leitype = '发放权益';
		            if($v['type'] == 2){
		                $code = Dever::db('card/code')->find($v['sku_id']);
		                if($code){
		                    $card_info = Dever::db('card/info')->find($code['card_id']);
		                    if ($card_info && $card_info['end_dh_day']) {
		                        $youxiao = date('Y-m-d H:i',strtotime(+ $card_info['end_dh_day'].'day',$code['bdate']));
		                    }
		                    $card = $code['card']."\t";
		                    $mianzhi = $code['use_cash']; 
		                }                  
		            }
		            $wy = $v['cash'] - $mianzhi;
		            $desc = '';
		            $member_goods_log = Dever::db('agent/member_goods_log')->getOne(array('type'=>$v['type'],'type_id'=>$v['type_id'],'sku_id'=>$v['sku_id'],'state'=>1));
		            if($member_goods_log && $member_goods_log['desc']){
		                $desc = $member_goods_log['desc'];
		            }
		            $d = array
		            (
		            	$v['name'],
		            	$role_name,
		            	$member_name,
		            	$member_mobile,
		            	$card,
		            	$leitype,
		            	$v['total_num'],
		            	$v['price'],
		            	$v['cash'],
		            	$yd,
		            	$mianzhi,
		            	$wd,
		            	$wy,
		            	$desc,
		            	'',
		            	date('Y-m-d H:i',$v['cdate']),
		                $member_cdate,
		                $youxiao,
		            );
		            $body[] = $d;
				}
				
			} 
		}
		if (!$body) {
			Dever::alert('此时段没有数据，请重新选择条件');
		}
		$file = '代理商权益';
        return Dever::excelExport($body, $header, $file);
	}
	#代理商权益发放使用兑换明细
	public function mingxi_api()
	{
		$name = Dever::input('name');
		$mobile = Dever::input('mobile');
		$card_num = Dever::input('card_num');
		$shop_name = Dever::input('shop_name');
		$shop_num = Dever::input('shop_num');
		$order_num = Dever::input('order_num');
		$start = Dever::input('start');
		$end = Dever::input('end');
		$type = Dever::input('type');
		if ($name) {
			$where['name'] = $name;
		}
		if ($mobile) {
			$where['mobile'] = $mobile;
		}
		if ($card_num) {
			$code = Dever::db('card/code')->find(array('card'=>$card_num));
			if ($code){
				$where['sku_id'] = $code['id'];
			}
		}
		if ($shop_name) {
			$shop = Dever::db('shop/info')->find(array('name'=>$shop_name));
			$where['shop_id'] = $shop['id'];
		}

		if ($shop_num) {
			$shop = Dever::db('shop/info')->fetch('select * from churen_shop_info where sid = '.$shop_num.' and state = 1');
			$where['shop_id'] = $shop['id'];
		}
		// print_R($where['shop_id']);die;
		if ($order_num) {
			$sell_order = Dever::db('shop/sell_order')->find(array('order_num'=>$order_num));
			$where['sku_id'] = $sell_order['card_code_id'];

		}
		if ($start) {
			$where['start'] = strtotime($start);
		}
		if ($end) {
			$where['end'] = strtotime($end);
		}
		if ($type) {
			$where['types'] = $type;
		}
		$header = array('日期','权益名称','会员等级','姓名','手机号','卡号','类型','数量','单件价值','总价值','门店类型','门店编号','门店名称','关联平台商城订单号','备注','获取方式','获取时间','代理商注册时间','礼品卡有效期');
		$body = array();
		$where['type'] = '1,2';
		$data = Dever::db('agent/member_goods')->getMingxi($where);
		$res = array('发放权益','兑换权益','使用权益');
		// print_R($data);die;
		$rest = array();
		if ($where['types'] == -1) {
			foreach ($res as $a => $b ){
				foreach ($data as $k => $v) {
					$v['leixing'] = $b;
					$rest[] = $v;
				}
			}
		} elseif ($where['types'] == 1) {
			foreach ($data as $k => $v) {
				$v['leixing'] = '发放权益';
				$rest[] = $v;
			}
		} elseif ($where['types'] == 2) {
			foreach ($data as $k => $v) {
				$v['leixing'] = '兑换权益';
				$rest[] = $v;
			}
		} elseif ($where['types'] == 3) {
			foreach ($data as $k => $v) {
				$v['leixing'] = '使用权益';
				$rest[] = $v;
			}
		}
		
		foreach ($rest as $k => $v) {
			$data[$k]['cdate'] = date('Y-m-d H:i',$v['cdate']);
				// foreach ($res as $a => $b) {
					// $data[$k]['leixing'] = $b;
					$member_goods_name = '';
					if (isset($v['name']) && $v['name']) {
						$member_goods_name = $v['name'];
					}
					$total_num = '';
					if(isset($v['total_num']) && $v['total_num']) {
						$total_num = $v['total_num'];
					}
					$price = '';
					if (isset($v['price']) && $v['price']) {
						$price = $v['price'];
					}
		            $cash = '';
		            if (isset($v['cash']) && $v['cash']) {
		            	$cash = $v['cash'];
		            }
		            $role_name = '';
					$member_name = '';
					$member_mobile = '';
					$member_cdate = '-';
					$member = Dever::db('agent/member')->find($v['mid']);
					if ($member) {
			            if($member['cdate']){
			                $member_cdate = date('Y-m-d H:i',$member['cdate']);
			            }
						$member_name = $member['name'];
						$member_mobile = $member['mobile'];
						$role = Dever::db('setting/role')->find($member['role']);
						if ($role) {
							$role_name = $role['name'];
						}
					}
					$card = '';
					$youxiao = '';
					if ($v['type'] == 2){
		                $code = Dever::db('card/code')->find($v['sku_id']);
		                if($code){
		                    $card_info = Dever::db('card/info')->find($code['card_id']);
		                    if ($card_info && $card_info['end_dh_day']) {
		                        $youxiao = date('Y-m-d H:i',strtotime(+ $card_info['end_dh_day'].'day',$code['bdate']));
		                    }
		                    $card = $code['card']."\t";
		                }                  
		            }
		            $desc = '';
		            $member_goods_log = Dever::db('agent/member_goods_log')->getOne(array('type'=>$v['type'],'type_id'=>$v['type_id'],'sku_id'=>$v['sku_id'],'state'=>1));
		            if($member_goods_log && $member_goods_log['desc']){
		                $desc = $member_goods_log['desc'];
		            }
		            $dui['mid'] = $v['mid'];

		            if (isset($where['start']) && $where['start']) {
		            	$dui['start'] = $where['start'];
		            }
		            if (isset($where['end']) && $where['end']) {
		            	$dui['end'] = $where['end'];
		            }
		            $dh_order_ps = Dever::db('agent/dh_order_ps')->getDui($dui);
	            if ($where['types'] == 2 || $where['types'] == -1) {
	            	// $v['leixing'] = ''
	            	if ($dh_order_ps) {
		            	// $data[$k]['leixing'] = '兑换权益';
		            	foreach ($dh_order_ps as $k =>$v) {
		            		$dh_order = Dever::db('agent/dh_order')->find($v['order_id']);
		            		if ($dh_order && isset($dh_order['info']) && $dh_order['info']){
		            			$desc = $dh_order['info'];
		            		}
		            	}
		            } 
	            }
	           
	            $shop_name = '';
	            $shop_type = '';
	            $shop_sid = '';
	            $order_num = '';
	            if (isset($where['shop_id']) && $where['shop_id']) {
	            	$where['types'] = 3;
	            	$w2['shop_id'] = $where['shop_id'];
            		if (isset($where['start']) && $where['start']) {
		            	$w2['start'] = $where['start'];
		            }
		            if (isset($where['end']) && $where['end']) {
		            	$w2['end'] = $where['end'];
		            }
	            	$sell_order = Dever::db('shop/sell_order')->getOutOne($w2);
	            	if ($sell_order) {
	            		if ($where['types'] == 3 || $where['types'] == -1){
	            			$w = $this->common($sell_order);
		            		if ($w) {
		            			$shop_name = $w['shop_name'];
						        $shop_type = $w['shop_type'];
						        $shop_sid = $w['shop_sid'];
						        $order_num = $w['order_num'];
		            			
		            		}
	            		}
	            		
	            	}
	            } else {
	            	if (isset($v['sku_id']) && $v['sku_id']){
	            		$w1['card_code_id'] = $v['sku_id'];
	            		if (isset($where['start']) && $where['start']) {
			            	$w1['start'] = $where['start'];
			            }
			            if (isset($where['end']) && $where['end']) {
			            	$w1['end'] = $where['end'];
			            }
	            		$sell_order = Dever::db('shop/sell_order')->getOutOne($w1);
		            	if ($sell_order) {
		            		if ($where['types'] == 3 || $where['types'] == -1) {
			                	$w = $this->common($sell_order);
			                	if ($w) {
			                		$shop_name = $w['shop_name'];
							        $shop_type = $w['shop_type'];
							        $shop_sid = $w['shop_sid'];
							        $order_num = $w['order_num'];
			                	}
		            		}
		                }
	            	}
	            }
	            $d = array
                (
                	$data[$k]['cdate'],
                	$member_goods_name,
                	$role_name,
                	$member_name,
                	$member_mobile,
                	$card,
                	$v['leixing'],
                	$total_num,
                	$price,
		            $cash,
		            $shop_type,
		            $shop_sid,
		            $shop_name,
		            $order_num,
		            $desc,
	            	'',
	            	$data[$k]['cdate'],
	                $member_cdate,
	                $youxiao,
                );
       			$body[] = $d;
		}
		if (!$body) {
			Dever::alert('此时段没有数据，请重新选择条件');
		}
		$file = '代理商权益明细';
        return Dever::excelExport($body, $header, $file);
	}
	public function common($sell_order)
	{
		$w = array();
		$w['order_num'] = $sell_order['order_num'];
    	$shop = Dever::db('shop/info')->find($sell_order['shop_id']);
    	if ($shop) {
    		$w['shop_name'] = $shop['name'];
    		$w['shop_sid'] = $shop['sid'];
    		if ($shop['type'] = 1) {
    			$w['shop_type'] = '体验店';
    		} elseif ($shop['type'] = 2) {
    			$w['shop_type'] = '零售店';
    		} elseif ($shop['type'] = 10) {
    			$w['shop_type'] = '平台商城';
    		}
    	}
    	return $w;
	}
}