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()
	{
		$header = array('门店类型', '门店编号', '门店名称', '订单号', '商品编码', '存货编码', '商品名称', '规格型号', '税率', '商品单价', '购买数量', '商品合计金额', '代理优惠金额', '商品实付金额', '礼品卡金额', '钱包金额','商品支付金额', '礼品卡名称', '礼品卡卡号', '代理商注册时间', '下单时间', '付款时间', '发货时间', '完成时间', '订单总数量', '订单总金额', '订单实付金额', '礼品卡金额', '钱包金额', '优惠券金额', '代理优惠金额', '订单状态', '退款状态', '退款时间', '退款金额','退卡金额','退现金金额', '收件人', '收件人手机', '收件人地址');

		$body = array();
		$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');
		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'];
			}
		}
		$where['state'] = 1;
		$data = Dever::db('shop/sell_order')->getExcel($where);
		// print_R($data);die;
		if ($data) {
			foreach ($data as $k => $v) {
				$v['card'] = '';
				$v['card_code'] = '';
		        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'];
		                }
		            }
		        }
				if ($v['shop_type'] == 1) {
					$v['shop_type_name'] = '体验店';
				} elseif ($v['shop_type'] == 2) {
					$v['shop_type_name'] = '平台商城';
				}
				$shop = Dever::db('shop/info')->find(Array('id'=>$v['shop_id']));
				if (isset($shop) && $shop) {
					$data[$k]['shop_sid'] = $shop['sid'];
					$data[$k]['shop_name'] = $shop['name'];
				}
				$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']) : '-';
				$goods = Dever::db('shop/sell_order_goods')->select(Array('order_id'=>$v['id']));
				$fdate = '';
				if ($v['fdate']) {
					$fdate = date('Y-m-d H:i',$v['fdate']);
				}
				
				
				
				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 = '';
	                }
	                $goods_price = $v1['price']*$v1['num'];
	                #代理优惠金额
	                $discount_cash = round($v['discount_cash']/$goods_price,2);
	                #商品实付金额
	                $price = round(($v['price']/$goods_price),2);
	                #礼品卡金额
	                $card_code_cash = round(($v['card_code_cash']/$goods_price),2);
	                #钱包金额
	                $wallet_cash = round(($v['wallet_cash']/$goods_price),2);
	                #商品支付金额
	                if ($k1 == 0) {
	                	$total_cash = Dever::load("shop/lib/manage.getTotalCash", $v['id']);
	                } else {
	                	$total_cash = 0;
	                }
	                $ka = 0;
	                $money = 0;
	                 if ($v['status'] == 8 || $v1['status'] == 3) {
	                    $num = $v1['num'];
	                    $refund_cash = $v1['price']*$v1['num'];
	                    if ($v['card_code_cash'] >0) {
	                    	if ($v['refund_cash'] <= $v['card_code_cash']) {
	                    		$ka = $v['refund_cash'];
	                    	} else {
	                    		$ka = $v['card_code_cash'];
	                    		$money = $v['refund_cash'] - $v['card_code_cash'];
	                    	}
	                    }
	                    // $refund = Dever::db('shop/sell_order_refund')->find(array('order_id'=>$v['id']));
	                    // if ($refund)
	                    $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 = '';
	                    $num = '0';
	                    $refund_cash = '0';
	                    $refund_status = '未退款';
	                }
	                if ($v['status'] >=7) {
	                	$v['price'] = 0;
	                }
	                
	                if ($k1 == 0) {
	                    $v['num'] = $v['num'];
	                    $v['card_code_cash'] = $v['card_code_cash'];
	                    $v['price'] = $v['price'];
	                    $v['wallet_cash'] = $v['wallet_cash'];
	                    $v['coupon_cash'] = $v['coupon_cash'];
	                    $v['discount_cash'] = $v['discount_cash'];
	                    $ka = $ka;
	                    $money = $money;
	                } else {
	                    $v['num'] = 0;
	                    $v['card_code_cash'] = 0;
	                    $v['price'] = 0;
	                    $v['wallet_cash'] = 0;
	                    $v['coupon_cash'] = 0;
	                    $v['discount_cash'] = 0;
	                    $ka = 0;
	                    $money = 0;
	                }
	                // print_R($total_cash);die;
	                $d = array
                    (
                        $v['shop_type_name'],
						$data[$k]['shop_sid'],
						$data[$k]['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),
						$discount_cash,
						$price,
						$card_code_cash,
						$wallet_cash,
						$price,
						$v['card'],
						$v['card_code']."\t",
						$zhuce,
						$cdate,
						$paydate,
						$operdate,
						$fdate,
						$v['num'],
						sprintf('%01.2f',$total_cash),
	                    round($v['price'], 2),
	                    sprintf('%01.2f',$v['card_code_cash']),
	                    round($v['wallet_cash'], 2),
	                    round($v['coupon_cash'], 2),
	                    round($v['discount_cash'], 2),
	                    $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;
					if ($goods_info['price_type'] > 2 && isset($goods_info['goods']) && is_array($goods_info['goods'])) {
                    
	                    foreach ($goods_info['goods'] as $k2 => $v2) {
	                        if ($v2['code']) {
	                            //$v2['code'] = '-' . $v2['code'];
	                        }
	                        $p = $v2['price'];
	                        if (is_numeric($v2['num']) && is_numeric($v2['price'])) {
	                            $p = $v2['price']*$v2['num'];
	                        }
	                        $goods_price1 = 0;
	                        
	                        $d = array
							(
								$v['shop_type_name'],
								$data[$k]['shop_sid'],
								$data[$k]['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),
								$discount_cash,
								$price,
								$card_code_cash,
								$wallet_cash,
								$price,
								$v['card'],
								$v['card_code']."\t",
								$zhuce,
								$cdate,
								$paydate,
								$operdate,
								$fdate,
								'',
								'',
			                    '',
			                    '',
			                    '',
			                    '',
			                    '',
			                    $status[$v['status']],
			                    '',
			                    '',
			                    '',
			                    $address_contact,
			                    $address_mobile,
			                    $address_info,
							);

	                        
	                    }
	                }
	                $body[] = $d;
				}
				
			}
			
		}
		$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'));
			// $data = Dever::db('agent/member_goods')->getJoinAll($where);
			// echo Dever::sql();
			// print_R($data);die;
			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;
				}
				
			} 
		}
		$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);
		if ($data) {
			foreach ($data as $k =>$v) {
				$cdata = '';
				$leixing = '';
				$member_goods_name = '';
				if (isset($v['name']) && $v['name']) {
					$member_goods_name = $v['name'];
				}
				if ($v['cdate']) {
					$cdate = date('Y-m-d H:i',$v['cdate']);
				}
				$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'];
	            }
			            
				if ($where['types'] == 1 || $where['types'] == -1) {
					$leixing = '发放权益';
				}
				
				$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);
	            // echo Dever::sql();die;
	            if ($where['types'] == 2 || $where['types'] == -1) {
	            	if ($dh_order_ps) {
		            	$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);
		            		$leixing = '使用权益';
		            		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) {
		            			$leixing = '使用权益';
			                	$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'];
			                	}
		            		}
		                }
	            	}
	            }
                if ($leixing) {
                	$d = array
	                (
	                	$cdate,
	                	$member_goods_name,
	                	$role_name,
	                	$member_name,
	                	$member_mobile,
	                	$card,
	                	$leixing,
	                	$total_num,
	                	$price,
			            $cash,
			            $shop_type,
			            $shop_sid,
			            $shop_name,
			            $order_num,
			            $desc,
		            	'',
		            	$cdate,
		                $member_cdate,
		                $youxiao,
	                );
	               	$body[] = $d;
                }
                
			}
			
		}
		$file = '代理商权益明细';
				// Dever::input('excel_name');
        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;
	}
}