123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573 |
- <?php
- namespace JinDouYun\Model\Commission;
- use JinDouYun\Controller\Common\Logger;
- use JinDouYun\Dao\Commission\DCommissionOrderIndex;
- use JinDouYun\Dao\Customer\DCustomer;
- use JinDouYun\Dao\Order\DOrderGoods;
- use JinDouYun\Dao\Order\DOrderIndex;
- use Mall\Framework\Core\StatusCode;
- use JinDouYun\Dao\Commission\DCommissionOrder;
- use Mall\Framework\Core\ErrorCode;
- use Mall\Framework\Core\ResultWrapper;
- /**
- * Description:
- * Class MCommissionOrder
- * @package JinDouYun\Model\Commission
- */
- class MCommissionOrder
- {
- /**
- * @var DCommissionOrder
- */
- private $objDCommissionOrder;
- /**
- * @var int
- */
- private $onlineEnterpriseId;
- /**
- * @var int
- */
- private $onlineUserId;
- private static $retLevel = [
- 0 => '',
- 1 => '一级返佣',
- 2 => '二级返佣',
- 3 => '三级返佣'
- ];
- /**
- * @var array
- */
- private static $isAccount = [
- 3 => '已取消',
- 4 => '等待入账',
- 5 => '已入账'
- ];
- /**
- * @var DCustomer
- */
- private $objDCustomer;
- /**
- * @var DCommissionOrderIndex
- */
- private $objDCommissionOrderIndex;
- /**
- * @var DOrderGoods
- */
- private $objDOrderGoods;
- /**
- * @var int 单表数据量
- */
- private $cutTable = 200000;
- /**
- * @var DOrderIndex
- */
- private $objDOrderIndex;
- /**
- * MCommissionOrder constructor.
- * @param $onlineEnterpriseId
- * @param $onlineUserId
- * @throws \Exception
- */
- public function __construct($onlineEnterpriseId, $onlineUserId)
- {
- $this->onlineEnterpriseId = $onlineEnterpriseId;
- $this->onlineUserId = $onlineUserId;
- $this->objDCommissionOrder = new DCommissionOrder();
- $this->objDCommissionOrder->setTable('qianniao_commission_order_' . $this->onlineEnterpriseId . '_' . date('Y') . '_' . ceil(date('m') / 3));
- $this->objDCustomer = new DCustomer();
- $this->objDCustomer->setTable('qianniao_customer_'.$this->onlineEnterpriseId);
- $this->objDCommissionOrderIndex = new DCommissionOrderIndex();
- $this->objDCommissionOrderIndex->setTable('qianniao_commission_order_index_'.$this->onlineEnterpriseId);
- $this->objDOrderIndex = new DOrderIndex();
- $this->objDOrderIndex->setTable('qianniao_order_index_'.$this->onlineEnterpriseId);
- $this->objDOrderGoods = new DOrderGoods();
- }
- public function __destruct()
- {
- // TODO: Implement __destruct() method.
- }
- /**
- * Doc: (des="分销商品记录")
- * User: XMing
- * Date: 2020/7/24
- * Time: 4:23 下午
- * @param array $selectParams
- * @return ResultWrapper
- */
- public function getAll(array $selectParams)
- {
- $fields = 'c.*,o.auditStatus,o.orderStatus,i.isAccount';
- $commissionOrderTableName = 'qianniao_commission_order_' . $this->onlineEnterpriseId . '_' . date('Y') . '_' . ceil(date('m') / 3);
- $sql = 'SELECT '.$fields.' FROM '.$commissionOrderTableName.' as c
- LEFT JOIN qianniao_order_index_'.$this->onlineEnterpriseId. ' as o
- ON o.id = c.orderId
- LEFT JOIN qianniao_commission_order_index_'.$this->onlineEnterpriseId.' as i
- ON i.orderId = c.orderId
- WHERE c.id IS NOT NULL';
- if(isset($selectParams['state']) && !empty($selectParams['state'])){
- $stateSql = self::stateSql($selectParams['state']);
- $sql .= empty($stateSql) ? '' : ' and '.$stateSql;
- }
- if (isset($selectParams['customerId']) && !empty($selectParams['customerId'])){
- $sql .= ' AND c.customerId = '.$selectParams['customerId'];
- }
- if (isset($selectParams['no']) && !empty($selectParams['no'])){
- $sql .= ' AND c.orderNo = '.$selectParams['no'];
- }
- if (isset($selectParams['goodsName']) && !empty($selectParams['goodsName'])){
- $sql .= ' AND c.goodsName = "'.$selectParams['goodsName'].'"';
- }
- if (isset($selectParams['orderStatus']) && !empty($selectParams['orderStatus'])){
- $sql .= ' AND o.orderStatus = '.$selectParams['orderStatus'];
- }
- if (isset($selectParams['isAccount']) && !empty($selectParams['isAccount'])){
- $sql .= ' AND i.isAccount = '.$selectParams['isAccount'];
- }
- if (isset($selectParams['startTime']) && !empty($selectParams['startTime']) && isset($selectParams['endTime']) && !empty($selectParams['endTime'])){
- $sql .= ' AND c.createTime BETWEEN '.$selectParams['startTime'].' and '.$selectParams['endTime'];
- }
- $total = count((array) $this->objDCommissionOrder->query($sql));
- //排序规则
- if (isset($selectParams['orderBy']) && empty($selectParams['orderBy'])){
- $sql .= ' GROUP BY c.id order by '.$selectParams['orderBy'];
- }else{
- $sql .= ' GROUP BY c.id order by c.createTime DESC';
- }
- //导出不需要分页
- if( isset($selectParams['export']) && !empty($selectParams['export']) ){ //导出
- $commissionList = $this->objDCommissionOrder->query($sql);
- if ($commissionList === false){
- return ResultWrapper::fail($this->objDCommissionOrder->error(),ErrorCode::$dberror);
- }
- if(empty($commissionList)){
- return ResultWrapper::success('导出数据为空');
- }
- self::exportCommissionOrder($commissionList);
- }
- $sql .= ' limit '.$selectParams['offset'].','.$selectParams['limit'];//分页
- $commissionList = $this->objDCommissionOrder->query($sql);
- if ($commissionList === false){
- return ResultWrapper::fail($this->objDCommissionOrder->error(),ErrorCode::$dberror);
- }
- $formatResult = self::formatList((array)$commissionList);
- if (!$formatResult->isSuccess()){
- return ResultWrapper::fail($formatResult->getData(),$formatResult->getErrorCode());
- }
- $return = [
- 'total' => $total,
- 'data' => $formatResult->getData()
- ];
- return ResultWrapper::success($return);
- }
- /**
- * Doc: (des="state标示对应sql")
- * User: XMing
- * Date: 2020/7/13
- * Time: 9:43 上午
- * @param string $state
- * @return string
- */
- private static function stateSql(string $state)
- {
- $sql = '';
- switch ($state) {
- case 'waitAudit'://待审核
- $sql .= ' (o.auditStatus = ' . StatusCode::$auditStatus['auditing'] . ' and o.orderStatus != ' . StatusCode::$orderStatus['close'] . ')';
- break;
- case 'waitOutStock'://待出库
- $sql .= ' (o.auditStatus = ' . StatusCode::$auditStatus['auditPass'] . ' and (o.orderStatus = ' . StatusCode::$orderStatus['waitPay'] . ' or o.orderStatus = ' . StatusCode::$orderStatus['waitDelivery'] . ')) ';
- break;
- case 'hasOutStock'://已出库
- $sql .= ' (o.auditStatus = ' . StatusCode::$auditStatus['auditPass'] . ' and o.orderStatus = ' . StatusCode::$orderStatus['waitReceive'] . ')';
- break;
- case 'finish'://已完成
- $sql .= ' (o.auditStatus = ' . StatusCode::$auditStatus['auditPass'] . ' and o.orderStatus = ' . StatusCode::$orderStatus['finish'] . ')';
- break;
- case 'close'://已关闭
- $sql .= ' (o.orderStatus = ' . StatusCode::$orderStatus['close'] . ')';
- break;
- case 'all'://全部订单
- break;
- }
- return $sql;
- }
- /**
- * Doc: (des="格式化")
- * User: XMing
- * Date: 2020/7/29
- * Time: 4:06 下午
- * @param array $data
- * @return ResultWrapper
- */
- public function formatList(array $data)
- {
- if (empty($data)){
- return ResultWrapper::success([]);
- }
- $customerIds = [];
- $retCustomerIds = [];
- foreach ($data as $datum){
- $customerIds[] = $datum['customerId'];
- $retCustomerIds[] = $datum['retCustomerId'];
- }
- $allCustomerIds = array_merge($customerIds,$retCustomerIds);//全部客户id
- //获取客户的名称
- $customerLists = $this->objDCustomer->select(['id'=>$allCustomerIds],'id,name');
- if ($customerIds === false){
- return ResultWrapper::fail($this->objDCustomer->error(),ErrorCode::$dberror);
- }
- $customerRelNameMap = [];
- foreach ($customerLists as $list){
- $customerRelNameMap[$list['id']] = $list['name'];
- }
- $data = self::formatOrderStatus($data);
- foreach ($data as $key => &$value){
- is_string($value['specGroup']) && $data[$key]['specGroup'] = json_decode($value['specGroup'],true);
- $value['selfMsg'] = $value['isSelf'] == StatusCode::$standard ? '自购返佣' : '';
- $value['retLevelMsg'] = self::$retLevel[$value['retLevel']];
- $value['customerName'] = isset($customerRelNameMap[$value['customerId']]) ? $customerRelNameMap[$value['customerId']] : '';
- $value['retCustomerName'] = isset($customerRelNameMap[$value['retCustomerId']]) ? $customerRelNameMap[$value['retCustomerId']] : '';
- $value['retMsg'] = isset(self::$isAccount[$value['isAccount']]) ? self::$isAccount[$value['isAccount']] : '';
- }
- return ResultWrapper::success($data);
- }
- /**
- * 格式化订单状态
- * @param $data
- * @return mixed
- * @return ResultWrapper
- */
- private static function formatOrderStatus($data)
- {
- if (empty($data)) return $data;
- foreach ($data as $key => $value) {
- if ($value['auditStatus'] == StatusCode::$auditStatus['auditing']) {
- $data[$key]['orderMsg'] = '待审核';
- }
- if ($value['auditStatus'] == StatusCode::$auditStatus['auditPass']) {
- $data[$key]['orderMsg'] = '待出库';
- if ($value['orderStatus'] == StatusCode::$orderStatus['waitReceive']) {
- $data[$key]['orderMsg'] = '已出库';
- }
- if ($value['orderStatus'] == StatusCode::$orderStatus['finish']) {
- $data[$key]['orderMsg'] = '已完成';
- }
- }
- if ($value['orderStatus'] == StatusCode::$orderStatus['close']) {
- $data[$key]['orderMsg'] = '已关闭';
- }
- }
- return $data;
- }
- /**
- * Doc: (des="分销中心订单")
- * User: XMing
- * Date: 2020/7/30
- * Time: 10:51 上午
- * @param array $selectParams
- * @return ResultWrapper
- *
- * @throws \Exception
- */
- public function getCommissionOrder(array $selectParams)
- {
- $fields = 'c.id,o.id as orderId,o.orderStatus,o.payStatus,o.createTime,o.payType,o.deliveryType,
- c.payAmount,c.orderNo,c.retMoney,c.retLevel,c.retUserCenterId,c.retCustomerId,c.userCenterId,c.customerId';
- $sql = 'SELECT '.$fields.' FROM qianniao_commission_order_index_'.$this->onlineEnterpriseId.' as c
- LEFT JOIN qianniao_order_index_'.$this->onlineEnterpriseId.' as o
- ON o.id = c.orderId
- WHERE c.retUserCenterId = '.$this->onlineUserId;
- if (isset($selectParams['orderStatus']) && !empty($selectParams['orderStatus'])){
- if (is_array($selectParams['orderStatus'])){
- $orderStatusStr = implode(',',$selectParams['orderStatus']);
- $sql .= ' AND o.orderStatus IN('.$orderStatusStr.') ';
- }else{
- $sql .= ' AND o.orderStatus = '.$selectParams['orderStatus'];
- }
- }
- $sql .= ' order by c.createTime desc';
- $count = count((array)$this->objDCommissionOrderIndex->query($sql));
- //分页
- if (isset($selectParams['limit']) && isset($selectParams['offset'])) {
- $sql .= ' limit ' . $selectParams['offset'] . ',' . $selectParams['limit'];
- }
- $list = $this->objDCommissionOrderIndex->query($sql);
- if ($list === false){
- return ResultWrapper::fail($this->objDCommissionOrderIndex->error(),ErrorCode::$dberror);
- }
- if (empty($list)){
- return ResultWrapper::success([
- 'data' => [],
- 'total' => 0
- ]);
- }
- $formatResult = self::formatApiOrderList((array)$list);
- if (!$formatResult->isSuccess()){
- return ResultWrapper::fail($formatResult->getData(),$formatResult->getErrorCode());
- }
- $ret = [
- 'data' => $formatResult->getData(),
- 'total' => $count
- ];
- return ResultWrapper::success($ret);
- }
- /**
- * Doc: (des="")
- * User: XMing
- * Date: 2020/7/30
- * Time: 3:41 下午
- * @param array $data
- * @return ResultWrapper
- * @throws \Exception
- */
- private function formatApiOrderList(array $data)
- {
- $allGroupData = [];
- $allCustomerIds = [];
- foreach ($data as $key => $val) {
- $tableNum = ceil($val['userCenterId'] / $this->cutTable);
- $allGroupData[$tableNum][] = $val['orderId'];
- $allCustomerIds[] = $val['customerId'];
- $allCustomerIds[] = $val['retCustomerId'];
- }
- //查询这些订单的商品数据
- $allOrderGoodsData = [];
- foreach ($allGroupData as $k => $orderId) {
- $this->objDOrderGoods->setTable('qianniao_order_goods_' . $this->onlineEnterpriseId . '_' . $k);
- $dbResult = $this->objDOrderGoods->select(
- ['orderId' => $orderId],
- 'orderId,goodsName,goodsId,goodsCode,skuId,shopName,price,buyNum,preferential,totalMoney,specGroup,goodsImages,unitName',
- 'createTime desc'
- );//查询订单
- if ($dbResult === false) {
- Logger::logs(E_USER_ERROR, 'sql错误', __CLASS__, __LINE__, $this->objDOrderGoods->error());
- return ResultWrapper::fail($this->objDOrderGoods->error(), ErrorCode::$dberror);
- }
- $allOrderGoodsData = array_merge($allOrderGoodsData,$dbResult);
- }
- $orderGoodsMap = [];//订单商品数据集合
- foreach ($allOrderGoodsData as $value){
- $value['specGroup'] = empty($value['specGroup']) ? [] : json_decode($value['specGroup'],true);
- $orderGoodsMap[$value['orderId']][] = $value;
- }
- $customer = $this->objDCustomer->select(['id' => $allCustomerIds],'id,name');
- if ($customer === false){
- return ResultWrapper::fail($this->objDCustomer->error(),ErrorCode::$dberror);
- }
- $customerMap = [];
- foreach ($customer as $value){
- $customerMap[$value['id']] = $value['name'];
- }
- foreach ($data as &$datum){
- $datum['selfMsg'] = '';
- if ($datum['userCenterId'] == $datum['retUserCenterId']){
- $datum['selfMsg'] = '自购返佣';
- }
- $datum['retLevelMsg'] = isset(self::$retLevel[$datum['retLevel']]) ? self::$retLevel[$datum['retLevel']] : '';
- $datum['orderGoods'] = isset($orderGoodsMap[$datum['orderId']]) ? $orderGoodsMap[$datum['orderId']] : [];
- $datum['customerName'] = isset($customerMap[$datum['customerId']]) ? $customerMap[$datum['customerId']] : '';
- $datum['retCustomerName'] = isset($customerMap[$datum['retCustomerId']]) ? $customerMap[$datum['retCustomerId']] : '';
- }
- return ResultWrapper::success($data);
- }
- /**
- * Doc: (des="分销订单详情")
- * User: XMing
- * Date: 2020/7/30
- * Time: 4:07 下午
- * @param int $id
- *
- * @throws \Exception
- * @return ResultWrapper
- */
- public function getCommissionOrderInfo(int $id)
- {
- $fields = 'c.id,o.id as orderId,o.orderStatus,o.payStatus,o.createTime,o.payType,o.deliveryType,
- c.payAmount,c.orderNo,c.retMoney,c.retLevel,c.retUserCenterId,c.retCustomerId,c.userCenterId,c.customerId';
- $sql = 'SELECT '.$fields.' FROM qianniao_commission_order_index_'.$this->onlineEnterpriseId.' as c
- LEFT JOIN qianniao_order_index_'.$this->onlineEnterpriseId.' as o
- ON o.id = c.orderId
- WHERE c.id = '.$id;
- $info = $this->objDCommissionOrderIndex->query($sql);
- if ($info === false){
- return ResultWrapper::fail($this->objDCommissionOrderIndex->error(),ErrorCode::$dberror);
- }
- $info = array_shift($info);
- if (empty($info)){
- return ResultWrapper::fail('未找到订单信息',ErrorCode::$paramError);
- }
- $formatResult = self::formatDetails($info);
- if (!$formatResult->isSuccess()){
- return ResultWrapper::fail($formatResult->getData(),$formatResult->getErrorCode());
- }
- return ResultWrapper::success($formatResult->getData());
- }
- /**
- * Doc: (des="")
- * User: XMing
- * Date: 2020/7/30
- * Time: 4:15 下午
- * @param array $data
- * @throws \Exception
- * @return ResultWrapper
- */
- private function formatDetails(array $data)
- {
- $fix = ceil($data['userCenterId'] / $this->cutTable);
- $this->objDOrderGoods->setTable('qianniao_order_goods_' . $this->onlineEnterpriseId . '_' . $fix);
- $orderGoods = $this->objDOrderGoods->select(['orderId' => $data['orderId']],'orderId,goodsName,goodsId,goodsCode,skuId,shopName,price,buyNum,preferential,totalMoney,specGroup,goodsImages,unitName');
- if ($orderGoods === false) {
- Logger::logs(E_USER_ERROR, 'sql错误', __CLASS__, __LINE__, $this->objDOrderGoods->error());
- return ResultWrapper::fail($this->objDOrderGoods->error(), ErrorCode::$dberror);
- }
- foreach ($orderGoods as &$value){
- $value['specGroup'] = empty($value['specGroup']) ? [] : json_decode($value['specGroup'],true);
- }
- unset($value);
- $data['orderGoods'] = $orderGoods;
- //获取客户名称
- $allCustomerIds = [
- $data['customerId'],
- $data['retCustomerId']
- ];
- $customer = $this->objDCustomer->select(['id' => $allCustomerIds],'id,name,avatar');
- if ($customer === false){
- return ResultWrapper::fail($this->objDCustomer->error(),ErrorCode::$dberror);
- }
- $customerMap = [];
- foreach ($customer as $value){
- $customerMap[$value['id']] = $value;
- }
- $data['customerName'] = isset($customerMap[$data['customerId']]) ? $customerMap[$data['customerId']]['name'] : '';
- $data['customerAvatar'] = isset($customerMap[$data['customerId']]) ? $customerMap[$data['customerId']]['avatar'] : '';
- $data['retCustomerName'] = isset($customerMap[$data['retCustomerId']]) ? $customerMap[$data['retCustomerId']]['name'] : '';
- $data['retCustomerAvatar'] = isset($customerMap[$data['retCustomerId']]) ? $customerMap[$data['retCustomerId']]['avatar'] : '';
- $data['deliveryMsg'] = isset(StatusCode::$deliveryType[$data['deliveryType']]) ? StatusCode::$deliveryType[$data['deliveryType']] : '';
- $data['selfMsg'] = '';
- if ($data['userCenterId'] == $data['retCustomerId']){
- $data['selfMsg'] = '自购返佣';
- }
- $data['payTypeMsg'] = StatusCode::$payType[$data['payType']];
- $data['retLevelMsg'] = self::$retLevel[$data['retLevel']];
- return ResultWrapper::success($data);
- }
- /**
- * Doc: (des="获取分销订单数据")
- * User: XMing
- * Date: 2020/8/5
- * Time: 10:38 上午
- * @param array $userCenterIds
- * @return ResultWrapper
- */
- public function getCommissionOrderData(array $userCenterIds)
- {
- $CommissionOrder = $this->objDCommissionOrderIndex->select(['userCenterId' => $userCenterIds],'userCenterId,payAmount');
- if ($CommissionOrder === false){
- return ResultWrapper::fail($this->objDCommissionOrderIndex->error(),ErrorCode::$dberror);
- }
- $retMap = [];
- foreach ($CommissionOrder as $value){
- $retMap[$value['userCenterId']][] = $value['payAmount'];
- }
- $map = [];
- foreach ($retMap as $userCenterId => $item){
- $map[$userCenterId] = [
- 'totalOrderAmount' => array_sum($item),
- 'total' => count($item),
- ];
- }
- return ResultWrapper::success($map);
- }
- public function exportCommissionOrder($date)
- {
- //导出到本地
- header("Content-type:application/vnd.ms-excel");
- header("Content-Disposition:filename=分销订单.csv");
- header('Cache-Control: max-age=0');
- $fp = fopen('php://output', 'a');
- $head = ['商品信息', '单价','数量','实付款', '订单来源', '买家','订单状态','时间', '分销商', '佣金', '佣金状态']; //定义标题
- foreach ($head as $i => $v) {
- $head[$i] = mb_convert_encoding($v, 'GBK', 'utf-8'); //将中文标题转换编码,否则乱码
- }
- fputcsv($fp, $head);
- $limit = 10000;
- $num = 0;//计数器
- foreach ($date as $value) {//循环数据
- $num++;
- if ($num == $limit) {
- ob_flush();//释放内存
- flush();
- }
- $rows['goodsName'] = isset($value['goodsName']) ? $value['goodsName'] : null; // 商品信息
- $rows['goodsPrice'] = isset($value['goodsPrice']) ? $value['goodsPrice'] : null; // 单价
- $rows['buyNum'] = isset($value['buyNum']) ? $value['createTime'] : 0; // 数量
- $rows['payAmount'] = bcmul($rows['goodsPrice'],$rows['buyNum'],2); // 实付款
- $rows['customerName'] = isset($value['customerName']) ? $value['customerName'] : null; // 订单来源
- $rows['customerMobile'] = isset($value['customerMobile']) ? $value['customerMobile'] : null; // 买家
- $rows['sourceNo'] = isset($value['sourceNo']) ? $value['sourceNo'] : null; // 订单状态
- $rows['createTime'] = isset($value['createTime']) ? date("Y-m-d H:i:s",$value['createTime']) : null; // 时间
- $rows['typeName'] = isset($value['typeName']) ? $value['typeName'] : null; // 分销商
- $rows['retMoney'] = isset($value['retMoney']) ? $value['retMoney'] : null; // 佣金
- $rows['isSettled'] = ($value['isSettled'] == StatusCode::$standard) ? '已结算': '未结算';// 佣金状态
- foreach ($rows as $kk => $vv) {
- $rs[$kk] = mb_convert_encoding($vv, 'GBK', 'utf-8'); //转译编码
- }
- fputcsv($fp, $rs);
- $rows = [];
- }
- }
- }
|