<?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\DB;
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(['receipt_id', 'account_no', 'supplier_id', 'bank_adderss'])->get()->toArray();
foreach ($accounts as $account) {
if (!SupplierReceiptService::checkAccountNo($account['account_no'])) {
dump($account['account_no']);
dump('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
$accountNo = SupplierReceiptService::transferAccountNo($account['account_no']);
if ($accountNo != $account['account_no']) {
dump('-----------------------------------');
dump($accountNo);
SupplierReceiptModel::where('receipt_id', $account['receipt_id'])->update([
'account_no' => $accountNo,
]);
}
}
}
}
public static function exportInvalidSupplierName()
{
}
public static function repairAccount()
{
SupplierAccountModel::where('a_status', 0)->where('id', '!=', 1284)
->update([
'a_status' => 1
]);
}
public static function exportNameInvalid()
{
$suppliers = DB::connection('web')->select('SELECT *, count(*) FROM lie_supplier_channel WHERE is_type=0 and group_code != "" GROUP BY supplier_name having count(*) > 1');
$supplierNameList = array_column($suppliers, 'supplier_name');
$suppliers = SupplierChannelModel::whereIn('supplier_name', $supplierNameList)->orderBy('supplier_name', 'desc')->get()->toArray();
$transformer = new SupplierTransformer();
$suppliers = $transformer->transformList($suppliers);
Excel::create('重名供应商导出', function ($excel) use ($suppliers) {
$header = [
'供应商编码',
'供应商名字',
'状态',
'供应商性质',
'采购员',
'线上采购员',
'最新上传SKU时间',
];
$excelData = [];
foreach ($suppliers as $supplier) {
$excelData[] = [
$supplier['supplier_name'],
$supplier['supplier_code'],
$supplier['status_name'],
$supplier['supplier_group'],
$supplier['channel_username'],
$supplier['yunxin_channel_username'],
$supplier['last_upload_sku_time'],
];
}
array_unshift($excelData, $header);
$excel->sheet('sheet1', function ($sheet) use ($excelData) {
$sheet->fromArray($excelData);
});
})->export('csv');
}
public static function getSpuAttrs()
{
$spuId = request()->input('spu_id');
$mongo = DB::connection('mongo')->table('spu_attrs2')->where('spu_id', (int)$spuId)->first();
dd($mongo);
}
public static function updateIedgeSkuName($isUpdate = false)
{
ini_set('memory_limit', '2048M');
$filePath = public_path('data') . DIRECTORY_SEPARATOR . '爱智平台SKU汇总表-2024.09.19.xlsx';
Excel::selectSheetsByIndex(0)->load($filePath, function ($reader) use ($isUpdate) {
$reader->sheet('细分商品分类', function () use ($reader, $isUpdate) {
foreach ($reader->all()->toArray() as $key => $item) {
if ($key == 0 || $key > 100) {
continue;
}
$skuId = trim(trim($item[1]), "\t");
$oldSkuName = trim(trim($item[2], "\t"));
$newSkuName = trim(trim($item[3], "\t"));
dump($skuId, $oldSkuName, $newSkuName);
//直接去修改sku的sku_name
$dbInfo = getSpuSkuDb($skuId);
$connection = DB::connection($dbInfo["db"]);
$table = $dbInfo['table'];
$skuName = $connection->table($table)->where('goods_id', $skuId)->value('goods_name');
if (empty($skuName)) {
dump('sku_id找不到');
}
dump($skuName);
}
});
});
}
public static function initSignCom()
{
//读取excel
$filePath = public_path('data') . DIRECTORY_SEPARATOR . 'sign_com.xlsx';
$excel = Excel::load($filePath, function ($reader) {
$reader->sheet('sheet1', function ($sheet) {
$sheet->fromArray($sheet->toArray());
});
});
}
}