<?php

namespace App\Http\Services;

//后台用户相关信息服务
use App\Http\Transformers\SupplierTransformer;
use App\Model\BigData\DataManageModel;
use App\Model\DepartmentModel;
use App\Model\IntracodeModel;
use App\Model\LogModel;
use App\Model\Purchase\PurchaseOrderModel;
use App\Model\RedisModel;
use App\Model\SkuUploadItem;
use App\Model\SkuUploadLogModel;
use App\Model\SpuBrandModel;
use App\Model\StandardBrandMappingModel;
use App\Model\StandardBrandModel;
use App\Model\SupplierAccountModel;
use App\Model\SupplierAddressModel;
use App\Model\SupplierAttachmentsModel;
use App\Model\SupplierChannelModel;
use App\Model\SupplierContactModel;
use App\Model\SupplierReceiptModel;
use App\Model\UserInfoModel;
use Carbon\Carbon;
use GuzzleHttp\Client;
use GuzzleHttp\RequestOptions;
use Illuminate\Support\Facades\Redis;
use Maatwebsite\Excel\Facades\Excel;

//这个服务是处理数据的,比如导出信息,或者临时修复数据,所以代码会比较多
class DataService
{

    public function initSystemTag()
    {

        $tagList = [
//            1 => '临时供应商',
//            2 => '优质供应商',
//            3 => '客户指定供应商',
//            4 => '开票不及时',
//            5 => '跳票',
            '战略供应商',

        ];

        $client = new Client([
            'base_uri' => config('website.TagUrl'),
        ]);

        foreach ($tagList as $tag) {
            //构建请求参数
            $params = [
                $tag => [
                    'tag_use' => 14,
                    'tag_type' => 2,
                    'remark' => '',
                    'creator' => 1000,
                    'creator_name' => 'admin',
                    'status' => 1,
                ]
            ];
            $response = $client->post('/create', [
                RequestOptions::JSON => $params,
            ]);
            var_dump($response->getBody()->getContents());
        }
    }


    const TYPE_YC = 4;
    const TYPE_DLS = 1;
    const TYPE_MYS = 2;


    //导出供应商
    public function exportSupplierForUnionData()
    {
        ini_set('memory_limit', -1);
        $suppliers = SupplierChannelModel::where('is_type', 0)->whereRaw('supplier_name NOT LIKE "%-1"')->get();
        $excelData = [];
        $header = [
            '供应商名称',
            '主营品牌',
            '性质',
            '等级',
            '品质协议是否签署',
            '是否平台供应商',
            '是否芯链商家',
            '是否SKU上架中',
            '现有线上采购员或者意向分配采购员',
            '采购所在采购部门',
        ];
        $intraCodeModel = new IntracodeModel();
        $users = $intraCodeModel->getSampleName(true);
        foreach ($suppliers as $supplier) {
            $mainBrands = (new SupplierTransformer())->getMainBrandNames($supplier['main_brands']);
            $attachmentsField = SupplierAttachmentsModel::where('supplier_id',
                $supplier['supplier_id'])->pluck('field_name')->toArray();
            $hasPinzhi = $hasHezuo = '否';
            if (in_array('quality_assurance_agreement', $attachmentsField)) {
                $hasPinzhi = '是';
            }
            if (in_array('cooperation_agreement', $attachmentsField)) {
                $hasHezuo = '是';
            }
            $isYunxin = '否';
            if ($supplier['stockup_type']) {
                if (strpos($supplier['stockup_type'], "5") !== false) {
                    dump("是芯链");
                    $isYunxin = '是';
                }
            }

            $skuUserName = $skuUserNameRaw = '';
            if ($supplier['yunxin_channel_uid']) {
                $skuUserNameRaw = array_get($users, $supplier['yunxin_channel_uid']);
                $skuUserName = $skuUserNameRaw . '(供应商系统设置)';
            } else {
                //去采购系统找最多单的用户id
                $mostPurchaseOrderUser = PurchaseOrderModel::selectRaw('purchase_uid,supplier_id,COUNT(*) AS purchase_order_num')
                    ->where('supplier_id', $supplier['supplier_id'])
                    ->groupBy('purchase_uid')->orderBy('purchase_order_num', 'DESC')->first();
                if (!empty($mostPurchaseOrderUser)) {
                    $mostPurchaseOrderUser = $mostPurchaseOrderUser->toArray();
                    $codeId = IntracodeModel::where('admin_id',
                        $mostPurchaseOrderUser['purchase_uid'])->value('code_id');
                    if ($codeId) {
                        $skuUserNameRaw = array_get($users, $codeId);
                        $skuUserName = $skuUserNameRaw . '(采购系统下单最多)';
                    }
                }
            }
            $skuUserDepartment = '';
            if ($skuUserNameRaw) {
                $skuUserDepartment = (new DepartmentService())->getDepartmentNameByUserName($skuUserNameRaw);
            }
            $itemData = [
                $supplier['supplier_name'],
                $mainBrands,
                array_get(config('fixed.SupplierGroup'), $supplier['supplier_group'], '未设置'),
                $supplier['level'],
                $hasPinzhi,
                $hasHezuo,
                $isYunxin,
                $supplier['sku_num'] > 0 ? "是" : "否",
                $skuUserName,
                $skuUserDepartment
            ];
            $excelData[] = $itemData;
        }
        array_unshift($excelData, $header);
        Excel::create('供应商导出', function ($excel) use ($excelData) {
            $excel->sheet('sheet1', function ($sheet) use ($excelData) {
                $sheet->fromArray($excelData);
            });
        })->store('csv');
    }


    public function exportHalfYearSupplier()
    {
        $starTime = Carbon::now()->startOfYear()->timestamp;
        $suppliers = SupplierChannelModel::where('create_time', '>=', $starTime)
            ->select([
                'supplier_name',
                'supplier_code',
                'create_uid',
            ])->get();

        $excelData = [];
        $header = [
            '供应商名字',
            '编码',
            '建档采购员',
            '采购主管',
        ];
        foreach ($suppliers as $supplier) {
            $supervisor = '';
            $user = UserInfoModel::where('userId', $supplier['create_uid'])->first();
            $user = !empty($user) ? $user->toArray() : [];
            $supervisorDepartmentId = $user['department_id'];
            $positionId = \DB::table('user_position')->where('department_id', $supervisorDepartmentId)
                ->where('position_name', '采购总监')->value('position_id');
            if (empty($positionId)) {
                $positionId = \DB::table('user_position')
                    ->where('position_name', '运营总监')->value('position_id');
            }
            $supervisor = UserInfoModel::where('position_id', $positionId)->value('name');
            $excelData[] = [
                $supplier['supplier_name'],
                $supplier['supplier_code'],
                array_get($user, 'name'),
                $supervisor,
            ];
        }
        array_unshift($excelData, $header);
        dd($excelData);
        Excel::create('2022年新建供应商导出', function ($excel) use ($excelData) {
            $excel->sheet('sheet1', function ($sheet) use ($excelData) {
                $sheet->fromArray($excelData);
            });
        })->export('csv');
    }

    //初始化品牌数据
    public function initBrandData()
    {
        $prefixKey = '';
        $redis = new RedisModel();
        ini_set('memory_limit', -1);
        //先初始化品牌
        $brands = SpuBrandModel::get()->toArray();
        $redis->del($prefixKey . 'brand');
        $redis->del($prefixKey . 'brand_info');
        $redis->del($prefixKey . 'brand_name_all');
        $redis->del($prefixKey . 'standard_brand');
        $redis->del($prefixKey . 'standard_brand_mapping');
        foreach ($brands as $brand) {
            $redis->hset($prefixKey . 'brand', $brand['brand_id'], $brand['brand_name']);
            $redis->hset($prefixKey . 'brand_info', $brand['brand_id'], json_encode($brand));
            $redis->hset($prefixKey . 'brand_name_all', md5(strtolower($brand['brand_name'])), $brand['brand_id']);
        }

        //再去初始化标准品牌
        $standardBrands = StandardBrandModel::get()->toArray();
        foreach ($standardBrands as $brand) {
            $redis->hset($prefixKey . 'standard_brand', $brand['standard_brand_id'], json_encode($brand));
        }

        //最后初始化标品映射
        $mappings = StandardBrandMappingModel::get()->toArray();
        foreach ($mappings as $mapping) {
            $redis->hset($prefixKey . 'standard_brand_mapping', $mapping['brand_id'], $mapping['standard_brand_id']);
        }
    }

    //导出供应商评级数据
    public function exportSupplierDataForLevel()
    {
        ini_set('memory_limit', -1);
        $redis = new RedisModel();
        $startTime = Carbon::create(2023, 5, 1, 0, 0, 0)->timestamp;
        $endTime = Carbon::create(2024, 1, 1, 0, 0, 0)->timestamp;
        $suppliers = SupplierChannelModel::select([
            'supplier_id',
            'supplier_code',
            'supplier_group',
            'supplier_name',
            'yunxin_channel_uid',
            'uploaded_sku',
            'sku_num',
            'sku_update_time',
        ])->where('is_type', 0)
            ->whereBetween('sku_update_time', [$startTime, $endTime])
            //->where('sku_num', '>', 0)->get()->toArray();
            ->get()->toArray();
        $excelData = [];
        $header = [
            '供应商名称',
            '供应商类型',
            'SKU采购部门',
            '最近更新SKU时间',
            '是否开通芯链',
            '芯链报价次数',
            '芯链报价单成单数量',
            '采购成单总额(含税)',
            '当前上架SKU数',
            'SKU数最多的品牌1',
            'SKU数最多的品牌2',
            'SKU数最多的品牌3',
            'SKU数最多的品牌4',
            'SKU数最多的品牌5',
        ];
        $supplierCodes = array_column($suppliers, 'supplier_code');
        $supplierAccountMap = SupplierAccountModel::whereIn('supplier_code', $supplierCodes)->pluck('mobile', 'supplier_id')->toArray();
        $intraCodeModel = new IntracodeModel();
        $users = $intraCodeModel->getSampleName(true);
        foreach ($suppliers as $supplier) {
            $supplierId = $supplier['supplier_id'];
            $frqData = $redis->hget('frq_supplier_data_20230501_20231231', $supplier['supplier_name']);
            $frqData = !empty($frqData) ? json_decode($frqData, true) : [];
            $skuUserNameRaw = '';
            if ($supplier['yunxin_channel_uid']) {
                $skuUserNameRaw = array_get($users, $supplier['yunxin_channel_uid']);
            }
            $skuUserDepartment = '';
            if ($skuUserNameRaw) {
                $skuUserDepartment = (new DepartmentService())->getDepartmentNameByUserName($skuUserNameRaw);
            }

            $skuCountByStandardBrandName = StatisticsSkuUploadService::getSortedBrandNameSkuCount($supplier['supplier_code']);
            if (empty($skuCountByStandardBrandName)) {
                continue;
            }
            $skuCountByStandardBrandName = array_keys($skuCountByStandardBrandName);
            $itemData = [
                $supplier['supplier_name'],
                array_get(config('fixed.SupplierGroup'), $supplier['supplier_group'], '未设置'),
                $skuUserDepartment,
                $supplier['sku_update_time'] ? date('Y-m-d H:i:s', $supplier['sku_update_time']) : '',
                !empty($supplierAccountMap[$supplierId]) ? '是' : '否',
                array_get($frqData, 'quote_num', 0),
                array_get($frqData, 'pur_num', 0),
                array_get($frqData, 'pur_amount', 0),
                $supplier['sku_num'],
                array_get($skuCountByStandardBrandName, 0, ''),
                array_get($skuCountByStandardBrandName, 1, ''),
                array_get($skuCountByStandardBrandName, 2, ''),
                array_get($skuCountByStandardBrandName, 3, ''),
                array_get($skuCountByStandardBrandName, 4, ''),
            ];

            $excelData[] = $itemData;
        }

        //近半年内，查出更新过的品牌对应SKU数量最多排名（前100名）；需要字段“品牌名称，SKU数量，型号数量”
        $excelData2 = [];
        $countByBrandName = [];
        $allBrandCountSort = StatisticsSkuUploadService::getAllBrandSkuCountSort();
        foreach ($allBrandCountSort as $brandName => $skuCount) {

            //然后获取每个品牌的标准品牌
            $brandNameAllKey = md5(strtolower($brandName));
            $brandId = $redis->hget('brand_name_all', $brandNameAllKey);
            if (empty($brandId)) {
                $value['standard_brand_id'] = 0;
                $countByBrandName[] = [
                    'standard_brand_id' => 0,
                ];
                continue;
            }
            //然后找到标准品牌id
            $standardBrandId = $redis->hget('standard_brand_mapping', $brandId);
            $value['standard_brand_id'] = $standardBrandId;
            $standardBrand = $redis->hget('standard_brand', $standardBrandId);
            $standardBrand = json_decode($standardBrand, true);
            $value['standard_brand_name'] = $standardBrand['brand_name'];
            $countByBrandName[] = [
                'standard_brand_id' => $standardBrandId,
                'standard_brand_name' => $standardBrand['brand_name'],
                'sku_count' => $skuCount,
            ];
        }
        //统计标品的sku数量
        $dataByStandardBrandName = collect($countByBrandName)->groupBy('standard_brand_name')->toArray();
        $countByStandardBrandName = [];
        foreach ($dataByStandardBrandName as $brandName => $standardBrandItem) {
            if (empty($brandName)) {
                continue;
            }
            $skuCount = 0;
            foreach ($standardBrandItem as $value) {
                $skuCount = $value['sku_count'];
            }
            $countByStandardBrandName[$brandName] = [
                'sku_count' => $skuCount,
            ];
        }

        uasort($countByStandardBrandName, function ($a, $b) {
            return $b['sku_count'] > $a['sku_count'];
        });

        $excel2Header = [
            '品牌名称',
            'SKU数量',
            //'型号数量',
        ];

        foreach ($countByStandardBrandName as $brandName => $value) {

            //去搜索服务获取型号数量
            $excelData2[] = [
                $brandName,
                $value['sku_count'],
            ];
        }
        array_unshift($excelData, $header);
        array_unshift($excelData2, $excel2Header);
        Excel::create('供应商导出评级', function ($excel) use ($excelData, $excelData2) {
            $excelData = array_map('array_values', $excelData);
            $excelData2 = array_map('array_values', $excelData2);
            $excel->sheet('sheet1', function ($sheet) use ($excelData) {
                $sheet->fromArray($excelData);
            });
            $excel->sheet('sheet2', function ($sheet) use ($excelData2) {
                $sheet->fromArray($excelData2);
            });
        })->save('xlsx');
    }

    //导出2023年有更新过sku得供应商
    public function exportUpdatedSkuSupplier()
    {
        $startTime = Carbon::now()->subYear()->startOfYear()->timestamp;
        $endTime = Carbon::now()->subYear()->endOfYear()->timestamp;
        $suppliers = SupplierChannelModel::whereBetween('sku_update_time', [$startTime, $endTime])
            ->where('is_type', 0)
            ->select([
                'supplier_name',
                'supplier_group',
                'yunxin_channel_uid',
            ])->get()->toArray();
        //区分采购一部,采购二部,采购三部的人员
        $suppliersOne = $suppliersTwo = $suppliersThird = [];
        foreach ($suppliers as &$supplier) {
            $yunxinChannelUid = $supplier['yunxin_channel_uid'];
            $user = (new AdminUserService())->getAdminUserInfoByCodeId($yunxinChannelUid);
            $userId = $user['userId'];
            //获取当前部门
            $departmentName = (new DepartmentService())->getParentDepartmentNameByUserName($user['name']);
            $supplier['department_name'] = $departmentName;
            $supplier['yunxin_user_name'] = $user['name'];
        }
        unset($supplier);

        $suppliers = collect($suppliers)->groupBy('department_name')->toArray();

        Excel::create('供应商导出', function ($excel) use ($suppliers) {
            $header = [
                '供应商名称',
                '供应商性质',
                '线上采购员'
            ];
            foreach ($suppliers as $departmentName => $supplierList) {
                $excelData = [];
                if (empty($departmentName)) {
                    continue;
                }
                foreach ($supplierList as $item) {
                    $excelData[] = [
                        $item['supplier_name'],
                        array_get(config('fixed.SupplierGroup'), $item['supplier_group']),
                        $item['yunxin_user_name'],
                    ];
                }
                array_unshift($excelData, $header);
                $excel->sheet($departmentName, function ($sheet) use ($excelData) {
                    $sheet->fromArray($excelData);
                });
            }
        })->export('xlsx');

    }

    function syncDaigouSuppliersToErp()
    {
        $supplierIdList = SupplierChannelModel::whereIn('supplier_name', config('field.SkipChangeSupplierTypeNames'))->pluck('supplier_id')->toArray();

        foreach ($supplierIdList as $supplierId) {
            (new SyncSupplierService())->syncSupplierToErp($supplierId);
        }
    }

    function exportContact()
    {
        $contacts = SupplierContactModel::where('can_check_uids', '10096')->get()->toArray();
        Excel::create('联系人导出', function ($excel) use ($contacts) {
            $header = [
                '供应商编码',
                '供应商名字',
                '联系人',
                '联系邮箱',
                '联系手机',
                '座机',
                '职位',
                '采购员',
            ];
            $excelData = [];
            foreach ($contacts as $contact) {
                $supplierId = $contact['supplier_id'];
                $supplier = SupplierChannelModel::select(['supplier_id', 'supplier_code', 'supplier_name'])->where('supplier_id', $supplierId)
                    ->first()->toArray();
                $excelData[] = [
                    $supplier['supplier_code'],
                    $supplier['supplier_name'],
                    $contact['supplier_consignee'],
                    $contact['supplier_email'],
                    $contact['supplier_mobile'],
                    $contact['supplier_telephone'],
                    $contact['supplier_position'],
                    "陈欢",
                ];
            }
            array_unshift($excelData, $header);
            $excel->sheet('sheet1', function ($sheet) use ($excelData) {
                $sheet->fromArray($excelData);
            });

        })->save('xlsx');

    }

    function exportSupplier()
    {
        $keywords = [
            'One Capital Place',
            '18 Luard Rd',
            '18 Luard Road',
            '海德中心',
            '卢押道',
            '盧押道',
            'Bright Way Tower',
            '33 Mong Kok Road',
            '33 Mong Kok Rd',
            '凯途发展大厦',
            '旺角道33号',
            '凱途發展大廈',
            'Lockhart Rd',
            'Lockhart Road',
            'Lockhart Centre',
            '骆克道',
            '駱克道',
            'Room 803',
            'Chevalier House',
            'Chatham Road South',
            '漆咸道南45',
            '漆咸道南45',
            '2-16 Fa Yuen Street',
            'King Commercial Centre',
            '花园街',
            '花園街',
            'Fayuen',
            'Fa Yuen Street',
            'Ho King Commercial',
            '好景商业中心',
            '好景商業中心',
            '610 Nathan Rd',
            'Hollywood Plaza',
            '弥敦道610号',
            '彌敦道610號',
            '610 Nathan Road',
            '荷李活商业中心',
            '荷李活商業中心',
        ];
        $suppliers = [];
        foreach ($keywords as $keyword) {
            $supplierList = SupplierChannelModel::where('supplier_address', 'like', "%{$keyword}%")->select([
                'supplier_name',
                'phone',
                'supplier_address',
            ])->get()->toArray();
            if ($supplierList) {
                $supplierList = array_map(function ($supplier) use ($keyword) {
                    $supplier['keyword'] = $keyword;
                    return $supplier;
                }, $supplierList);
                $suppliers = array_merge($suppliers, $supplierList);
            }
            //再去匹配发货地址
            $supplierIdAddr = SupplierAddressModel::where('address', 'like', "%{$keyword}%")->pluck('supplier_id')->toArray();
            if ($supplierIdAddr) {
                $supplierList = SupplierChannelModel::whereIn('supplier_id', $supplierIdAddr)->select([
                    'supplier_name',
                    'phone',
                    'supplier_address',
                ])->get()->toArray();
                if ($supplierList) {
                    $supplierList = array_map(function ($supplier) use ($keyword) {
                        $supplier['keyword'] = $keyword;
                        return $supplier;
                    }, $supplierList);
                    $suppliers = array_merge($suppliers, $supplierList);
                }
            }

        }

        $idColumn = array_column($suppliers, 'supplier_name');
        $idColumn = array_unique($idColumn);
        $suppliers = array_intersect_key($suppliers, $idColumn);

        Excel::create('实体名单供应商导出', function ($excel) use ($suppliers) {
            $header = [
                '供应商名字',
                '联系方式',
                '命中地址',
                '命中关键词',
            ];
            $excelData = [];
            foreach ($suppliers as $supplier) {
                $excelData[] = [
                    $supplier['supplier_name'],
                    $supplier['phone'],
                    $supplier['supplier_address'],
                    $supplier['keyword'],
                ];
            }
            array_unshift($excelData, $header);
            $excel->sheet('sheet1', function ($sheet) use ($excelData) {
                $sheet->fromArray($excelData);
            });

        })->save('xlsx');

    }

    public static function checkSupplierBandAccount()
    {
        ini_set('memory_limit', -1);
        $accounts = SupplierReceiptModel::select(['account_no', 'supplier_id','bank_adderss'])->get()->toArray();
        foreach ($accounts as $account) {
            if (!SupplierReceiptService::checkAccountNo($account['account_no'])) {
                dump($account['account_no']);
                $accountNo = SupplierReceiptService::transferAccountNo($account['account_no']);
                dump($accountNo,$account['bank_adderss']);
            }
        }
    }

}