<?php
namespace App\Http\Services;
//后台用户相关信息服务
use App\Http\Transformers\SupplierContactTransformer;
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\Purchase\StockInItemModel;
use App\Model\Purchase\StockInModel;
use App\Model\RedisModel;
use App\Model\SkuUploadLogModel;
use App\Model\StandardBrandModel;
use App\Model\SupplierAccountModel;
use App\Model\SupplierAttachmentModel;
use App\Model\SupplierAttachmentsModel;
use App\Model\SupplierChannelModel;
use App\Model\SupplierContactModel;
use App\Model\SupplierPayTypeModel;
use App\Model\UserInfoModel;
use Carbon\Carbon;
use GuzzleHttp\Client;
use GuzzleHttp\RequestOptions;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;
use Maatwebsite\Excel\Facades\Excel;
use PhpAmqpLib\Connection\AMQPStreamConnection;
use PhpAmqpLib\Message\AMQPMessage;
//这个服务是处理数据的,比如导出信息,或者临时修复数据,所以代码会比较多
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 changeSupplierType()
{
ini_set('memory_limit', -1);
//统一修改掉状态先
(new SupplierChannelModel)->where('supplier_id', '>', 0)->update([
'outside_contact_type' => 0
]);
//先将真实对接的供应商修改状态
$realApiSupplierCodes = [
'L0000266',
'L0000198',
'L0000273',
'L0000135',
'L0000135',
'L0005966',
'L0000004',
'L0006149',
'L0006150',
'L0006155',
'L0007637',
'L0006148',
'L0006151',
'L0006147',
'L0007639',
'L0007640',
'L0007646',
'L0007648',
'L0007650',
'L0007653',
'L0000089',
'L0007633',
'L0007663',
'L0010826',
'L0010856',
'L0010937',
'L0007595',
'L0011054',
'L0004938',
'L0011040',
'L0001175',
'L0007243',
'L0011042',
];
SupplierChannelModel::whereIn('supplier_code', $realApiSupplierCodes)
->update([
'outside_contact_type' => 2
]);
$suppliers = $this->getNeedDealSuppliers();
if (empty($suppliers)) {
return '供应商上传类型已经全部处理';
}
//api对接类型的供应商数量(706)
$ycApiTypeCount = 172;
$dlsApiTypeCount = 207;
$mysApiTypeCount = 327;
//要先找出原厂类型的API对接类型的供应商数量,然后要从库里面随机找出原厂的供应商打上类型
$this->dealApiSupplier(self::TYPE_YC, $ycApiTypeCount);
//要先找出代理商类型的API对接类型的供应商数量,然后要从库里面随机找出代理商的供应商打上类型
$this->dealApiSupplier(self::TYPE_DLS, $dlsApiTypeCount);
//要先找出贸易类型的API对接类型的供应商数量,然后要从库里面随机找出贸易的供应商打上类型
$this->dealApiSupplier(self::TYPE_MYS, $mysApiTypeCount);
echo "-------------------------" . PHP_EOL;
//然后随机取出云芯的供应商(1139)
//先剔除api对接的供应商
$ycYunxinTypeCount = 113;
$dlsYunxinTypeCount = 302;
$mysYunxinTypeCount = 724;
$this->dealYunxinSupplier(self::TYPE_YC, $ycYunxinTypeCount);
$this->dealYunxinSupplier(self::TYPE_DLS, $dlsYunxinTypeCount);
$this->dealYunxinSupplier(self::TYPE_MYS, $mysYunxinTypeCount);
echo "-------------------------" . PHP_EOL;
//剩下的都是人工上传的,也有一定占比(2658)
$ycManualTypeCount = 20;
$dlsManualTypeCount = 146;
$mysManualTypeCount = 2492;
$this->dealManualSupplier(self::TYPE_YC, $ycManualTypeCount);
$this->dealManualSupplier(self::TYPE_DLS, $dlsManualTypeCount);
$this->dealManualSupplier(self::TYPE_MYS, $mysManualTypeCount);
}
public function getNeedDealSuppliers($supplierGroup = 0, $otherSupplierGroup = [])
{
$query = SupplierChannelModel::where('outside_contact_type', 0)
->where('channel_uid', '!=', '')
->where('status', '!=', -3)
->whereRaw('supplier_name NOT LIKE "%-1"')
->where('is_type', 0);
if (!empty($otherSupplierGroup)) {
$query->whereIn('supplier_group', $otherSupplierGroup);
} else {
if (!empty($supplierGroup)) {
$query->where('supplier_group', $supplierGroup);
}
}
$data = $query->get()->toArray();
return $data;
}
//处理API接入的不同类型的数据
public function dealApiSupplier($supplierGroup, $totalApiTypeCount)
{
//先找出已经存在的api对接类型的供应商数量,方便下面算出补全数量
$existApiTypeCount = SupplierChannelModel::where('outside_contact_type', 2)
->where('supplier_group', $supplierGroup)->count();
echo "API对接类型的 $supplierGroup 供应商数量一共要有数量 : " . $totalApiTypeCount . PHP_EOL;
echo "已经存在的API对接类型的 $supplierGroup 供应商数量 : " . $existApiTypeCount . PHP_EOL;
//剩下就要补全
$needAddApiTypeCount = $totalApiTypeCount - $existApiTypeCount;
echo "需要补全API对接类型的 $supplierGroup 供应商数量 :" . $needAddApiTypeCount . PHP_EOL;
if ($needAddApiTypeCount <= 0) {
return;
}
$suppliers = $this->getNeedDealSuppliers($supplierGroup);
echo "供应商性质 $supplierGroup 可操作供应商数量 :" . count($suppliers) . PHP_EOL;
if (!count($suppliers)) {
return;
}
$rand = array_rand($suppliers, $needAddApiTypeCount);
$apiSuppliers = array_intersect_key($suppliers,
array_flip(is_array($rand) ? $rand : [$rand]));
$apiSupplierIds = array_column($apiSuppliers, 'supplier_id');
echo "供应商性质为 : $supplierGroup 的供应商修改API上传类型数量 : " . count($apiSupplierIds) . PHP_EOL . PHP_EOL;
SupplierChannelModel::whereIn('supplier_id', $apiSupplierIds)
->where('outside_contact_type', 0)->update([
'outside_contact_type' => 2
]);
}
//处理云芯接入的不同类型的数据
public function dealYunxinSupplier($supplierGroup, $totalYunxinCount)
{
$suppliers = $this->getNeedDealSuppliers($supplierGroup);
echo "供应商性质 $supplierGroup 需要修改数量 :" . $totalYunxinCount . PHP_EOL;
echo "供应商性质 $supplierGroup 可操作供应商数量 :" . count($suppliers) . PHP_EOL;
if (!count($suppliers)) {
return;
}
if (count($suppliers) < $totalYunxinCount) {
$totalYunxinCount = count($suppliers);
}
$rand = array_rand($suppliers, $totalYunxinCount);
$yunxinSuppliers = array_intersect_key($suppliers,
array_flip(is_array($rand) ? $rand : [$rand]));
$yunxinSupplierIds = array_column($yunxinSuppliers, 'supplier_id');
echo "供应商性质为 : $supplierGroup 的供应商修改云芯上传类型数量 : " . count(array_unique($yunxinSupplierIds)) . PHP_EOL;
// foreach ($yunxinSupplierIds as $supplierId) {
// SupplierChannelModel::where('supplier_id', $supplierId)->update([
// 'outside_contact_type' => 3
// ]);
// }
SupplierChannelModel::whereIn('supplier_id', $yunxinSupplierIds)
->where('outside_contact_type', 0)->update([
'outside_contact_type' => 3
]);
}
//处理人工上传
public function dealManualSupplier($supplierGroup, $totalManualCount)
{
$otherSupplierGroup = [];
if ($supplierGroup == 2) {
$otherSupplierGroup = [0, 2, 3, 5, 6];
// $otherSupplierGroup = [2];
}
//人工上传这个类别,供应商性质如果是贸易商,那就要加上方案商和分销平台和代工厂
$suppliers = $this->getNeedDealSuppliers($supplierGroup, $otherSupplierGroup);
echo "供应商性质 $supplierGroup 需要修改数量 :" . $totalManualCount . PHP_EOL;
echo "供应商性质 $supplierGroup 可操作供应商数量 :" . count($suppliers) . PHP_EOL;
if (!count($suppliers)) {
return;
}
if (count($suppliers) <= $totalManualCount) {
$totalManualCount = count($suppliers);
}
$rand = array_rand($suppliers, $totalManualCount);
$manualSuppliers = array_intersect_key($suppliers,
array_flip(is_array($rand) ? $rand : [$rand]));
$manualSupplierIds = array_column($manualSuppliers, 'supplier_id');
echo "供应商性质为 : $supplierGroup 的供应商修改人工上传类型数量 : " . count($manualSupplierIds) . PHP_EOL;
// foreach ($manualSupplierIds as $supplierId) {
// SupplierChannelModel::where('supplier_id', $supplierId)->update([
// 'outside_contact_type' => 1
// ]);
// }
foreach (collect($manualSupplierIds)->chunk(500) as $key => $ids) {
SupplierChannelModel::whereIn('supplier_id', $ids)
->where('outside_contact_type', 0)->update([
'outside_contact_type' => 1
]);
}
}
//导入公司性质
public function importSupplierGroup($isUpdate = false)
{
ini_set('memory_limit', -1);
$filePath = public_path('data') . DIRECTORY_SEPARATOR . 'supplier_group.xlsx';
try {
Excel::selectSheetsByIndex(0)->load($filePath, function ($reader) use ($isUpdate) {
$reader->sheet('Sheet2', function () use ($reader, $isUpdate) {
$num = $ycNum = 0;
$redis = new RedisModel();
foreach ($reader->all()->toArray() as $key => $item) {
$supplierCode = trim($item[0]);
$purchaseName = trim($item[3]);
$channelUid = (new AdminUserService())->getCodeIdByUserName($purchaseName);
//如果采购员id不为空,那么就要写进去
$supplierId = SupplierChannelModel::where('supplier_code', $supplierCode)
->value('supplier_id');
if (!$supplierId) {
echo "供应商不存在 : " . $supplierCode . PHP_EOL;
}
if ($channelUid) {
echo "添加采购员${purchaseName}到 : " . $supplierCode . PHP_EOL;
if ($isUpdate) {
(new SupplierService())->allocateChannelUser($supplierId, $channelUid, false);
}
}
$isYc = trim($item[4]);
if ($isYc == '原厂') {
//判断是否是原厂,如果是原厂,就跳过
$supplierGroup = SupplierChannelModel::where('supplier_code', $supplierCode)
->value('supplier_group');
if ($supplierGroup == self::TYPE_YC) {
echo "该供应商已经是原厂,跳过 : $supplierCode" . PHP_EOL;
continue;
}
echo "修改供应商性质为原厂 : " . $supplierCode . PHP_EOL;
if ($isUpdate) {
//写入redis,方便恢复
$redis->hset('lie_supplier_group_change', $supplierGroup, self::TYPE_YC);
SupplierChannelModel::where('supplier_code', $supplierCode)
->update([
'supplier_group' => self::TYPE_YC,
]);
}
$ycNum++;
}
$num++;
}
echo "一共处理 $num 家供应商采购员,处理 $ycNum 家供应商性质为原厂" . PHP_EOL;
});
});
} catch (\Exception $exception) {
dd($exception);
}
}
//生成云芯账号
public function generateYunxinAccount($isUpdate = false)
{
ini_set('memory_limit', -1);
$suppliers = SupplierChannelModel::where('outside_contact_type', 3)->get()->toArray();
// $suppliers = SupplierChannelModel::where('is_type', 0)->limit(100)->get()->toArray();
foreach ($suppliers as $supplier) {
if (SupplierAccountModel::where('supplier_id', $supplier['supplier_id'])->exists()) {
continue;
}
$mobile = generateMobile(1)[0];
$password = makePassword(mt_rand(8, 12));
$account = [
'supplier_id' => $supplier['supplier_id'],
'supplier_code' => $supplier['supplier_code'],
'mobile' => $mobile,
'password_raw' => $password,
'password' => Hash::make($password),
'create_uid' => 1000,
'create_time' => time(),
];
if ($isUpdate) {
SupplierAccountModel::insert($account);
}
}
}
//处理云芯账号的创建时间
public function dealYunxinAccountCreateTime()
{
$preYearTimestamp = time() - 24 * 3600 * 252 * 1;
$accounts = SupplierAccountModel::where('id', '>', 4)->get();
$randNum = 0;
$lastTimestamp = 0;
foreach ($accounts as $account) {
$randNum += 3600 * 5.2;
$startTime = Carbon::createFromTimestamp($preYearTimestamp + $randNum)->startOfDay()->addHour(9)->timestamp;
$endTime = Carbon::createFromTimestamp($preYearTimestamp + $randNum)->endOfDay()->addHour(19)->timestamp;
$createTimestamp = rand($startTime, $endTime);
if (Carbon::createFromTimestamp($createTimestamp)->isWeekend()) {
$createTimestamp = $createTimestamp + (2 * 24 * 3600);
}
if (Carbon::createFromTimestamp($createTimestamp)->hour < 9) {
$createTimestamp = $createTimestamp + (9 * 3600);
}
if ($createTimestamp < $lastTimestamp) {
$createTimestamp = $lastTimestamp + rand(60, 3600);
}
$lastTimestamp = $createTimestamp;
dump(date('Y-m-d H:i:s', $createTimestamp));
SupplierAccountModel::where('id', $account['id'])->update([
'create_time' => $createTimestamp
]);
}
}
//修复采购员有问题的数据
public function fixHasProblemChannelUid($isUpdate = false)
{
//1743
//1753
//1527
$channelUidsMap = [
1743 => 10201,
1753 => 10207,
1527 => 10177,
];
$supplierModel = \DB::connection('web');
foreach ($channelUidsMap as $channelUid => $codeId) {
$suppliers = $supplierModel->table('supplier_channel')
->where('channel_uid', 'like', "$channelUid%")
->where('is_type', 0)->get();
foreach ($suppliers as $supplier) {
$channelUidNew = str_replace($channelUid, $codeId, $supplier['channel_uid']);
dump("旧的采购员是 : " . $supplier['channel_uid']);
dump("新的采购员是 : " . $channelUidNew);
if ($isUpdate) {
$supplierModel->table('supplier_channel')
->where('supplier_id', $supplier['supplier_id'])
->update([
'channel_uid' => $channelUidNew,
]);
SupplierContactModel::where('supplier_id', $supplier['supplier_id'])
->where('can_check_uids', $channelUid)->update([
'can_check_uids' => $codeId
]);
}
}
}
}
//处理供应商类型的数据
public function dealSupplierTypeData($isUpdate = false)
{
//1.1 当前供应商系统中，没有品质保证协议的供应商，类型统一修正为临时供应商，注意：之前的代理商跟原厂性质的供应商类别保持不变。
$suppliers = SupplierChannelModel::where('is_type', 0)->whereNotIn('supplier_group', [1, 4])
->get()->toArray();
$skipSupplierNames = config('field.SkipChangeSupplierTypeNames');
foreach ($suppliers as $supplier) {
if (in_array($supplier['supplier_name'], $skipSupplierNames)) {
echo "代购供应商 ${supplier['supplier_name']},不能修改为临时,跳过";
}
//判断是否有品质保证协议,没有的话修改为临时类型
$hasQualityAssuranceAgreement = SupplierAttachmentsModel::where('supplier_id', $supplier['supplier_id'])
->where('field_name', 'quality_assurance_agreement')->exists();
if (!$hasQualityAssuranceAgreement) {
echo "供应商 ${supplier['supplier_code']} 没有品质保证协议,转换类型为临时;" . PHP_EOL;
if ($isUpdate) {
SupplierChannelModel::where('supplier_id', $supplier['supplier_id'])->update([
'supplier_type' => 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上架中',
'现有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 exportSupplierJsonForUnited()
{
$suppliers = SupplierChannelModel::where('is_type', 0)
->pluck('supplier_group', 'supplier_id')->where('create_time', '>', 1655778780)
->where('update_time', '>', 1655778780)->where('group_code', '!=', '')->toArray();
$exportJson = [];
$items = array_map(function ($value) {
$value = array_get(config('fixed.SupplierGroup'), $value);
return $value;
}, $suppliers);
$jsonItems = [];
foreach ($items as $key => $item) {
$jsonItems[] = [
'i' => $key,
'n' => $item
];
}
$exportJson = [
'sysId' => 1,
'cType' => 2,
'list' => $jsonItems,
];
echo json_encode($exportJson);
}
//禁用(没有集团编码)供应商
public function disableNoUnitedSupplier()
{
SupplierChannelModel::where('is_type', 0)->where('group_code', '')
->update([
'is_type' => 2,
'update_time' => time(),
]);
}
public function checkCompanyName()
{
$suppliers = SupplierChannelModel::where('group_code', '')->where('is_type', 0)
->where('create_time', '>', 1655778780)->where('update_time', '>', 1655778780)->get();
foreach ($suppliers as $supplier) {
$company = (new CompanyService())->getCompanyInfo($supplier['supplier_name'], '', 1);
if ($company) {
echo $supplier['supplier_name'] . PHP_EOL;
sleep(0.1);
}
}
}
//导入公司性质
public function disableSupplier($isUpdate = false)
{
ini_set('memory_limit', -1);
$filePath = public_path('data') . DIRECTORY_SEPARATOR . 'supplier_disable.xlsx';
try {
Excel::selectSheetsByIndex(1)->load($filePath, function ($reader) use ($isUpdate) {
$reader->sheet('禁用', function () use ($reader, $isUpdate) {
$num = 0;
foreach ($reader->all()->toArray() as $key => $item) {
$supplierId = trim($item[0]);
$supplierName = trim($item[1]);
$supplier = SupplierChannelModel::where('supplier_id', $supplierId)->first();
if (empty($supplier)) {
echo '供应商不存在 : ' . $supplierId . '__' . $supplierName . PHP_EOL;
continue;
}
$supplier = $supplier->toArray();
if ($supplier['group_code']) {
echo '已经有集团编码,跳过 : ' . $supplier['supplier_name'] . PHP_EOL;
continue;
}
if ($supplierName != $supplier['supplier_name']) {
echo '供应商名称遭到更改 : ' . $supplierName . '___' . $supplier['supplier_name'] . PHP_EOL;
continue;
}
$num++;
if ($isUpdate) {
SupplierChannelModel::where('supplier_id', $supplierId)->update([
'is_type' => 0,
'status' => SupplierChannelModel::STATUS_DISABLE,
]);
}
}
echo "一共处理 $num 家供应商" . PHP_EOL;
});
});
} catch (\Exception $exception) {
dd($exception);
}
}
//导出供应商基础信息
public function exportSuppliers()
{
$suppliers = SupplierChannelModel::where('is_type', 0)->select([
'supplier_name',
'supplier_group',
'status',
])->get()->toArray();
$excelData = [];
$header = [
'供应商名称',
'供应商性质',
'供应商状态',
];
foreach ($suppliers as $supplier) {
$itemData = [
$supplier['supplier_name'],
array_get(config('fixed.SupplierGroup'), $supplier['supplier_group']),
array_get(config('fixed.SupplierStatus'), $supplier['status']),
];
$excelData[] = $itemData;
}
array_unshift($excelData, $header);
Excel::create('供应商导出', function ($excel) use ($excelData) {
$excel->sheet('sheet1', function ($sheet) use ($excelData) {
$sheet->fromArray($excelData);
});
})->export('xlsx');
}
public function repairSupplierName()
{
$map = [
8581 => 'Holdwell Electronics(HK) Limited',
10245 => 'Micropower Communication Co.,Limited',
12021 => '深圳市卓美信电子科技有限公司',
12430 => '華创进出口有限公司',
12562 => 'Martec Unipersonale S.r.l.',
12577 => 'INELTEK Industrieelektronik und Technologien Ges.m.b.H.',
];
foreach ($map as $key => $value) {
SupplierChannelModel::where('supplier_id', $key)->update(['supplier_name' => $value]);
(new SyncSupplierService())->syncSupplierToUnited($key);
}
}
//导出重复的供应商
public function findDuplicateSupplier()
{
$suppliers = \DB::connection('web')->select('SELECT a.* FROM lie_supplier_channel a INNER JOIN (SELECT supplier_name FROM lie_supplier_channel GROUP BY supplier_name HAVING COUNT(supplier_id) > 1) b ON a.supplier_name = b.supplier_name ORDER BY supplier_name DESC');
$header = [
'供应商编码',
'供应商名称',
'采购员列表',
];
$excelData = [];
foreach ($suppliers as $supplier) {
$allChannelUserName = '';
$channelUids = $supplier['channel_uid'] ? explode(',', $supplier['channel_uid']) : [];
if ($channelUids) {
$channelUsers = (new AdminUserService())->getAdminUserListByCodeIds($channelUids);
if (!empty($channelUsers)) {
$allChannelUserName = implode(',', array_column($channelUsers, 'name'));
}
}
$excelData[] = [
$supplier['supplier_code'],
$supplier['supplier_name'],
$allChannelUserName,
];
}
array_unshift($excelData, $header);
Excel::create('重复供应商导出', function ($excel) use ($excelData) {
$excel->sheet('sheet1', function ($sheet) use ($excelData) {
$sheet->fromArray($excelData);
});
})->export('xlsx');
}
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');
}
//修复等级E的问题
public function repairLevelESupplierData($isUpdate = false)
{
$suppliers = SupplierChannelModel::where('level', 'E')->select([
'supplier_id',
'supplier_name',
'supplier_type',
])->get()->toArray();
foreach ($suppliers as $supplier) {
//去除供应商E等级，之前为E等级并且无品质保证协议的正式供应商，统一改为临时供应商。有品质保证协议的正式供应商继续保持为正式。
$hasQualityAssuranceAgreement = SupplierAttachmentsModel::where('supplier_id', $supplier['supplier_id'])
->where('field_name', 'quality_assurance_agreement')->exists();
if (!$hasQualityAssuranceAgreement && !in_array($supplier['supplier_name'],
config('field.SkipChangeSupplierTypeNames'))) {
dump("没有品质保证协议,修改为临时供应商 : " . $supplier['supplier_name']);
if ($isUpdate) {
SupplierChannelModel::where('supplier_id', $supplier['supplier_id'])->update([
'level' => '',
'supplier_type' => SupplierChannelModel::SUPPLIER_TYPE_TEMPORARY
]);
}
} else {
if ($isUpdate) {
SupplierChannelModel::where('supplier_id', $supplier['supplier_id'])->update([
'level' => '',
]);
}
}
}
}
//同步基石的sku上传内部编码到供应商的SKU采购员
public function syncFootstoneSkuUploadEncoded($isUpdate = false)
{
$suppliers = SupplierChannelModel::select(['supplier_code', 'yunxin_channel_uid'])->where('is_type',
0)->get()->toArray();
foreach ($suppliers as $supplier) {
//去基石的上传表找
$lastEncoded = SkuUploadLogModel::where('supplier_code', $supplier['supplier_code'])
->orderBy('create_time', 'desc')->value('encoded');
if (!$lastEncoded) {
continue;
}
//判断是否已经有SKU采购员了,有的话跳过
$hasSkuChannelUid = SupplierChannelModel::where('supplier_code', $supplier['supplier_code'])
->where('yunxin_channel_uid', '!=', 0)->exists();
if ($hasSkuChannelUid) {
echo "已经有SKU采购员,供应商编码为 : ${supplier['supplier_code']} , 跳过" . PHP_EOL;
continue;
}
echo "修改SKU采购员 : ${supplier['supplier_code']} => ${lastEncoded}" . PHP_EOL;
if ($isUpdate) {
SupplierChannelModel::where('supplier_code', $supplier['supplier_code'])
->update(['yunxin_channel_uid' => $lastEncoded]);
}
}
}
//导入公司性质
public function importSupplierLevel($isUpdate = false)
{
ini_set('memory_limit', -1);
$filePath = public_path('data') . DIRECTORY_SEPARATOR . 'supplier_level_20220816.csv';
try {
Excel::selectSheetsByIndex(0)->load($filePath, function ($reader) use ($isUpdate) {
$reader->sheet('supplier_level_20220816', function () use ($reader, $isUpdate) {
$suppliers = $reader->all()->toArray();
foreach ($suppliers as $supplier) {
$supplierCode = trim($supplier[1]);
$level = trim($supplier[16]);
echo "修改供应商 $supplierCode 等级为 $level" . PHP_EOL;
if ($isUpdate) {
SupplierChannelModel::where('supplier_code', $supplierCode)->update([
'level' => $level,
]);
}
}
});
});
} catch (\Exception $exception) {
dd($exception);
}
}
public static function exportSupplierByDepartment($topDepartmentId)
{
if ($topDepartmentId == 47) {
$append = '联营一部';
}
if ($topDepartmentId == 51) {
$append = '自营采购部';
}
if ($topDepartmentId == 72) {
$append = '联营二部';
}
Excel::create('采购供应商数据导出_' . $append, function ($excel) use ($topDepartmentId) {
$departmentIds = DepartmentModel::where('parent_id', $topDepartmentId)->pluck('department_id')->toArray();
$userIds = UserInfoModel::whereIn('department_id', $departmentIds)->pluck('userId')->toArray();
$suppliers2022 = $suppliersZhangQi2022 = $suppliersAll = $suppliersZhangQi = [];
//找出相关用户创建的所有供应商
$suppliersAll = SupplierChannelModel::whereIn('create_uid', $userIds)->where('is_type',
0)->get()->toArray();
foreach ($suppliersAll as $supplier) {
$createTime = date('Y-m-d H:i:s', $supplier['create_time']);
$creatName = UserInfoModel::where('userId', $supplier['create_uid'])->value('name');
$supplierGroup = array_get(config('fixed.SupplierGroup'), $supplier['supplier_group']);
$data = [
$creatName,
$supplier['supplier_name'],
$supplierGroup,
$createTime,
];
if ($supplier['create_time'] >= 1640966400) {
$suppliers2022[] = $data;
if ($supplier['pay_type'] == 1) {
$suppliersZhangQi2022[] = $data;
}
}
if ($supplier['pay_type'] == 1) {
$suppliersZhangQi[] = $data;
}
$suppliersAllList[] = $data;
}
$header = ['创建人', '供应商名称', '供应商性质', '创建时间'];
// dd($suppliersZhangQi,$header);
// $excel->sheet('2022年创建的供应商', function ($sheet) use ($header, $suppliers2022) {
// array_unshift($suppliers2022, $header);
// $sheet->fromArray($suppliers2022);
// });
// $excel->sheet('2022年创建的账期供应商', function ($sheet) use ($header, $suppliersZhangQi2022) {
// array_unshift($suppliersZhangQi2022, $header);
// $sheet->fromArray($suppliersZhangQi2022);
// });
// $excel->sheet('全部账期供应商', function ($sheet) use ($header, $suppliersZhangQi) {
// array_unshift($suppliersZhangQi, $header);
// $sheet->fromArray($suppliersZhangQi);
// });
$excel->sheet('全部供应商', function ($sheet) use ($header, $suppliersAllList) {
array_unshift($suppliersAllList, $header);
$sheet->fromArray($suppliersAllList);
});
})->store('xlsx');
}
//导出供应商
public function exportSupplier()
{
ini_set('memory_limit', -1);
$excelData = [];
$header = [
'供应商编码',
'供应商名称',
'供应商性质',
'采购员',
'sku采购员',
'最新修改人',
'创建人',
'创建时间',
'首次上传sku时间',
];
$users = (new IntracodeModel())->getSampleEncode(true);
$map = [
'source_type' => 'all',
'uploaded_sku' => 1,
'is_export' => 1,
'is_type' => 0,
];
$suppliers = SupplierChannelModel::where('uploaded_sku', 1)->where('is_type',
0)->whereRaw('supplier_name NOT LIKE "%-1"')->get()->toArray();
//获取最近修改信息
$logModel = new LogModel();
foreach ($suppliers as $supplier) {
$log = $logModel->where('supplier_id', $supplier['supplier_id'])
->where('type', LogModel::UPDATE_OPERATE)->orderBy('id', 'desc')->first();
$supplier['last_update_name'] = $log['admin_name'] ?: '无';
if (empty($supplier['create_name'])) {
$userInfo = (new AdminUserService())->getAdminUserInfo($supplier['create_uid']);
$supplier['create_name'] = array_get($userInfo, 'name');
}
$supplier['create_time'] = $supplier['create_time'] ? date('Y-m-d H:i:s', $supplier['create_time']) : '';
$firstUploadSkuTime = SkuService::getUploadTimeBySupplierCode($supplier['supplier_code'], 'first');
$supplier['first_upload_sku_time'] = $firstUploadSkuTime ? date('Y-m-d H:i:s', $firstUploadSkuTime) : '';
$supplier['supplier_group'] = array_get(config('fixed.SupplierGroup'), $supplier['supplier_group'], '未设置');
$itemData = [
$supplier['supplier_code'],
$supplier['supplier_name'],
$supplier['supplier_group'],
(new SupplierTransformer())->getChannelUserNames($supplier['channel_uid']),
array_get($users, $supplier['yunxin_channel_uid']),
$supplier['last_update_name'],
$supplier['create_name'],
$supplier['create_time'],
$supplier['first_upload_sku_time'],
];
$excelData[] = $itemData;
}
array_unshift($excelData, $header);
Excel::create('供应商导出_2023423', function ($excel) use ($excelData) {
$excel->sheet('sheet1', function ($sheet) use ($excelData) {
$sheet->fromArray($excelData);
});
})->store('csv');
}
public function exportNotUploadSkuSupplier()
{
ini_set('memory_limit', -1);
// $time = strtotime('2021-1-1');
// $suppliers = SupplierChannelModel::where('is_type', 0)->where('uploaded_sku', 1)
$time = strtotime('2021-1-1');
$suppliers = SupplierChannelModel::where('is_type', 0)->where('uploaded_sku', '!=', 1)
->whereRaw('supplier_name NOT LIKE "%-1"')
->where('create_time', '>', $time)->get();
$excelData = [];
$header = [
'供应商编码',
'供应商名称',
'采购员',
'部门',
];
$intraCodeModel = new IntracodeModel();
$users = $intraCodeModel->getSampleName(true);
foreach ($suppliers as $supplier) {
//去采购系统找采购单金额最多的用户
$purchaseAmountMap = [];
$channelUids = PurchaseOrderModel::where('supplier_id', $supplier['supplier_id'])->pluck('purchase_uid')->unique()->toArray();
if (empty($channelUids)) {
continue;
}
//去获取对应的uid的采购金额
foreach ($channelUids as $channelUid) {
//去采购系统取采购单金额
$purchaseOrders = PurchaseOrderModel::where('purchase_uid', $channelUid)->select([
'purchase_amount',
'exchange_rate',
'currency'
])->where('status', '!=', -3)->get()->toArray();
//计算出采购金额
$totalAmount = 0;
foreach ($purchaseOrders as $purchaseOrder) {
if ($purchaseOrder['currency'] == 1) {
$totalAmount += $purchaseOrder['purchase_amount'] * $purchaseOrder['exchange_rate'] * 1.13;
} else {
$totalAmount += $purchaseOrder['purchase_amount'] * $purchaseOrder['exchange_rate'];
}
}
$purchaseAmountMap[$channelUid] = $totalAmount;
}
arsort($purchaseAmountMap);
dump($purchaseAmountMap);
$maxAmountPurchaseAdminId = array_keys($purchaseAmountMap)[0];
dump($maxAmountPurchaseAdminId);
$skuUserNameRaw = UserInfoModel::where('userId', $maxAmountPurchaseAdminId)->value('name');
$skuUserDepartment = (new DepartmentService())->getDepartmentNameByUserName($skuUserNameRaw);
$itemData = [
$supplier['supplier_code'],
$supplier['supplier_name'],
$skuUserNameRaw,
$skuUserDepartment
];
$excelData[] = $itemData;
}
array_unshift($excelData, $header);
Excel::create('供应商无sku做单导出', function ($excel) use ($excelData) {
$excel->sheet('sheet1', function ($sheet) use ($excelData) {
$sheet->fromArray($excelData);
});
})->store('csv');
}
public function exportSupplierBySourceType()
{
// //找出所有大数据的供应商
$bigDataSuppliers = DataManageModel::where('is_type', 0)->get()->toArray();
// //如果platform_type不等于6,都是API采集
// //剩下的是普通采集的
// $apiSourceSupplierCodeList = $paSourceSupplierCodeList = [];
// foreach ($bigDataSuppliers as $supplier) {
// if ($supplier['platform_type'] != 6) {
// $apiSourceSupplierCodeList[] = $supplier['canal'];
// } else {
// $paSourceSupplierCodeList[] = $supplier['canal'];
// }
// }
$intraCodeModel = new IntracodeModel();
$users = $intraCodeModel->getSampleName(true);
$header = ['供应商编码', '供应商名称', 'sku采购员', '采购组别'];
//
// //导出sku接入方式为专营API
// $excelData = [];
// //导出sku接入方式为专营API
// foreach ($apiSourceSupplierCodeList as $code) {
// $supplier = SupplierChannelModel::where('supplier_code', $code)->first();
// if (empty($supplier)) {
// continue;
// }
// $skuUserNameRaw = array_get($users, $supplier['yunxin_channel_uid']);
// $supplier = $supplier->toArray();
// $departmentName = (new DepartmentService())->getDepartmentNameByUserName($skuUserNameRaw);
// $excelData[] = [
// $supplier['supplier_code'],
// $supplier['supplier_name'],
// $skuUserNameRaw,
// $departmentName,
// ];
// }
// array_unshift($excelData, $header);
// Excel::create('sku接入方式为专营API', function ($excel) use ($excelData) {
// $excel->sheet('sheet1', function ($sheet) use ($excelData) {
// $sheet->fromArray($excelData);
// });
// })->store('csv');
//
// //专营采集的供应商
// $excelData = [];
// foreach ($paSourceSupplierCodeList as $code) {
// $supplier = SupplierChannelModel::where('supplier_code', $code)->first();
// if (empty($supplier)) {
// continue;
// }
// $skuUserNameRaw = array_get($users, $supplier['yunxin_channel_uid']);
// $supplier = $supplier->toArray();
// $departmentName = (new DepartmentService())->getDepartmentNameByUserName($skuUserNameRaw);
// $excelData[] = [
// $supplier['supplier_code'],
// $supplier['supplier_name'],
// $skuUserNameRaw,
// $departmentName,
// ];
// }
// array_unshift($excelData, $header);
// Excel::create('专营采集的供应商', function ($excel) use ($excelData) {
// $excel->sheet('sheet1', function ($sheet) use ($excelData) {
// $sheet->fromArray($excelData);
// });
// })->store('csv');
//供应商性质为“分销平台”的有上传过sku的供应商
$excelData = [];
$uploadedSkuSuppliers = SupplierChannelModel::where('supplier_group', 5)
->where('is_type', 0)->where('uploaded_sku', 1)->get()->toArray();
foreach ($uploadedSkuSuppliers as $supplier) {
$supplier = SupplierChannelModel::where('supplier_code', $supplier['supplier_code'])->first();
if (empty($supplier)) {
continue;
}
$skuUserNameRaw = array_get($users, $supplier['yunxin_channel_uid']);
$supplier = $supplier->toArray();
$departmentName = (new DepartmentService())->getDepartmentNameByUserName($skuUserNameRaw);
$excelData[] = [
$supplier['supplier_code'],
$supplier['supplier_name'],
$skuUserNameRaw,
$departmentName,
];
}
array_unshift($excelData, $header);
Excel::create('供应商性质为“分销平台”的有上传过sku的供应商', function ($excel) use ($excelData) {
$excel->sheet('sheet1', function ($sheet) use ($excelData) {
$sheet->fromArray($excelData);
});
})->store('csv');
}
//获取非原厂,非api对接的,有sku的正式供应商列表
public function getOffShelfSupplierList()
{
$suppliers = SupplierChannelModel::where('sku_num', '>', 0)->where('supplier_group', '!=', 4)
->where('is_type', 0)->get()->toArray();
$supplierList = [];
foreach ($suppliers as $supplier) {
$isApiSupplier = DataManageModel::where('canal', $supplier['supplier_code'])->where('is_type', 0)->exists();
if ($isApiSupplier) {
continue;
}
$supplierList[] = $supplier['supplier_code'];
}
echo json_encode($supplierList);
}
//获取上传了平台合作协议的供应商
public function exportHasCooperationAgreementSupplierList()
{
$header = [
'供应商编码',
'供应商名称',
'供应商性质',
'sku采购员',
'组别',
];
$intraCodeModel = new IntracodeModel();
$users = $intraCodeModel->getSampleName(true);
$excelData = [];
$attachments = SupplierAttachmentsModel::where('field_name', 'cooperation_agreement')->groupBy('supplier_id')->get()->toArray();
foreach ($attachments as $attachment) {
$supplier = SupplierChannelModel::where('supplier_id', $attachment['supplier_id'])->first()->toArray();
if ($supplier['is_type'] == 1) {
continue;
}
$skuUserNameRaw = array_get($users, $supplier['yunxin_channel_uid']);
$departmentName = (new DepartmentService())->getDepartmentNameByUserName($skuUserNameRaw);
$excelData[] = [
$supplier['supplier_code'],
$supplier['supplier_name'],
array_get(config('fixed.SupplierGroup'), $supplier['supplier_group'], '未设置'),
$skuUserNameRaw,
$departmentName
];
}
array_unshift($excelData, $header);
Excel::create('上传了“平台合作协议”的供应商', function ($excel) use ($excelData) {
$excel->sheet('sheet1', function ($sheet) use ($excelData) {
$sheet->fromArray($excelData);
});
})->export('csv');
}
public function statisticsSkuNumber()
{
$dataManagerSupplierCodeList = DataManageModel::where('is_type', 0)->where('canal', '!=', '')->pluck('canal')->toArray();
//sku上架总数含api接口
$skuNum1 = SupplierChannelModel::where('is_type', 0)->where('supplier_group','!=',4)->whereNotIn('supplier_name', config('field.SkipChangeSupplierTypeNames'))->sum('sku_num');
//sku上架总数,不含api接口
$skuNum2 = SupplierChannelModel::where('is_type', 0)->where('supplier_group','!=',4)->whereNotIn('supplier_name', config('field.SkipChangeSupplierTypeNames'))->whereNotIn('supplier_code', $dataManagerSupplierCodeList)->sum('sku_num');
//有上架sku的供应商
$supplierCount = SupplierChannelModel::where('is_type', 0)->where('supplier_group','!=',4)->whereNotIn('supplier_name', config('field.SkipChangeSupplierTypeNames'))->where('sku_num','>',0)->count();
dump("上架供应商数（不含代购，原厂）：".$supplierCount);
dump("SKU 上架总条数（含API接口）：".$skuNum1);
dump("SKU 上架条数（不含API接口）：".$skuNum2);
}
//获取采购系统24号作废的深圳入库明细
public function getDeletedSZStockInItemList()
{
$todayTime = Carbon::now()->startOfDay()->timestamp;
//->where('item_status',-3)
$stockInItems = StockInItemModel::where('create_time','>',$todayTime)->where('item_status',-3)->whereHas('stock_in', function ($q) {
$q->whereIn('stock_in_type', [3]);
})->get()->toArray();
dd(count($stockInItems));
foreach ($stockInItems as $stockInItem) {
$first = $stockInItem['stock_in_item_id'];
dd($first,$stockInItem['stock_in_id']);
StockInItemModel::where('stock_in_item_id',$first)->update([
'item_status' => 1
]);
StockInModel::where('stock_in_id',$stockInItem['stock_in_id'])->update([
'erp_stock_in_sn'=> '',
'status' => 1
]);
}
dd(count((array_column($stockInItems,'stock_in_item_id'))));
}
public function deleteSupplierAccount()
{
SupplierAccountModel::where('id', 54)->delete();
}
}