1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182218321842185218621872188218921902191219221932194219521962197219821992200220122022203220422052206220722082209221022112212221322142215221622172218221922202221222222232224222522262227222822292230223122322233223422352236223722382239224022412242224322442245224622472248224922502251225222532254225522562257225822592260226122622263226422652266226722682269227022712272227322742275227622772278227922802281228222832284228522862287228822892290229122922293229422952296229722982299230023012302230323042305230623072308230923102311231223132314231523162317231823192320232123222323232423252326232723282329233023312332233323342335233623372338233923402341234223432344234523462347234823492350235123522353235423552356235723582359236023612362236323642365236623672368236923702371237223732374237523762377237823792380238123822383238423852386238723882389239023912392239323942395239623972398239924002401240224032404240524062407240824092410241124122413241424152416241724182419242024212422242324242425242624272428242924302431243224332434243524362437243824392440244124422443244424452446244724482449245024512452245324542455245624572458245924602461246224632464246524662467246824692470247124722473247424752476247724782479248024812482248324842485248624872488248924902491249224932494249524962497249824992500250125022503250425052506250725082509251025112512251325142515251625172518251925202521252225232524252525262527252825292530253125322533253425352536253725382539254025412542254325442545254625472548254925502551255225532554255525562557255825592560256125622563256425652566256725682569257025712572257325742575257625772578257925802581258225832584258525862587258825892590259125922593259425952596259725982599260026012602260326042605260626072608260926102611261226132614261526162617261826192620262126222623262426252626262726282629263026312632263326342635263626372638263926402641264226432644264526462647264826492650265126522653265426552656265726582659266026612662266326642665266626672668266926702671267226732674267526762677267826792680268126822683268426852686268726882689269026912692269326942695269626972698269927002701270227032704270527062707270827092710271127122713271427152716271727182719272027212722272327242725272627272728272927302731273227332734273527362737273827392740274127422743274427452746274727482749275027512752275327542755275627572758275927602761276227632764276527662767276827692770277127722773277427752776277727782779278027812782278327842785278627872788278927902791279227932794279527962797279827992800280128022803280428052806280728082809281028112812281328142815281628172818281928202821282228232824282528262827282828292830283128322833283428352836283728382839284028412842284328442845284628472848284928502851285228532854285528562857285828592860286128622863286428652866286728682869287028712872287328742875287628772878287928802881288228832884288528862887288828892890289128922893289428952896289728982899290029012902290329042905290629072908290929102911291229132914291529162917291829192920292129222923292429252926292729282929293029312932293329342935293629372938293929402941294229432944294529462947294829492950295129522953295429552956295729582959296029612962296329642965296629672968296929702971297229732974297529762977297829792980298129822983298429852986298729882989299029912992299329942995299629972998299930003001300230033004300530063007300830093010301130123013301430153016301730183019302030213022302330243025302630273028302930303031303230333034303530363037303830393040304130423043304430453046304730483049305030513052305330543055305630573058305930603061306230633064306530663067306830693070307130723073307430753076307730783079308030813082308330843085308630873088308930903091309230933094309530963097309830993100310131023103310431053106310731083109311031113112311331143115311631173118311931203121312231233124312531263127312831293130313131323133313431353136313731383139314031413142314331443145314631473148314931503151315231533154315531563157315831593160316131623163316431653166316731683169317031713172317331743175317631773178317931803181318231833184318531863187318831893190319131923193319431953196319731983199320032013202320332043205320632073208320932103211321232133214321532163217321832193220322132223223322432253226322732283229323032313232323332343235323632373238323932403241324232433244324532463247324832493250325132523253325432553256325732583259326032613262326332643265326632673268326932703271327232733274327532763277327832793280328132823283328432853286328732883289329032913292329332943295329632973298329933003301330233033304330533063307330833093310331133123313331433153316331733183319332033213322332333243325332633273328332933303331333233333334333533363337333833393340334133423343334433453346334733483349335033513352335333543355335633573358335933603361336233633364336533663367336833693370337133723373337433753376337733783379338033813382338333843385338633873388338933903391339233933394339533963397339833993400340134023403340434053406340734083409341034113412341334143415341634173418341934203421342234233424342534263427342834293430343134323433343434353436343734383439344034413442344334443445344634473448344934503451345234533454345534563457345834593460346134623463346434653466346734683469347034713472347334743475347634773478347934803481348234833484348534863487348834893490349134923493349434953496349734983499350035013502350335043505350635073508350935103511351235133514351535163517351835193520352135223523352435253526352735283529353035313532353335343535353635373538353935403541354235433544354535463547354835493550355135523553355435553556355735583559356035613562356335643565356635673568356935703571357235733574357535763577357835793580358135823583358435853586358735883589359035913592359335943595359635973598359936003601360236033604360536063607360836093610361136123613361436153616361736183619362036213622362336243625362636273628362936303631363236333634363536363637363836393640364136423643364436453646364736483649365036513652365336543655365636573658365936603661366236633664366536663667366836693670367136723673367436753676367736783679368036813682368336843685368636873688368936903691369236933694369536963697369836993700370137023703370437053706370737083709371037113712371337143715371637173718371937203721372237233724372537263727372837293730373137323733373437353736373737383739374037413742374337443745374637473748374937503751375237533754375537563757375837593760376137623763376437653766376737683769377037713772377337743775377637773778377937803781378237833784378537863787378837893790379137923793379437953796379737983799380038013802380338043805380638073808380938103811381238133814381538163817381838193820382138223823382438253826382738283829383038313832383338343835383638373838383938403841384238433844384538463847384838493850385138523853385438553856385738583859386038613862386338643865386638673868386938703871387238733874387538763877387838793880388138823883388438853886388738883889389038913892389338943895389638973898389939003901390239033904390539063907390839093910391139123913391439153916391739183919392039213922392339243925392639273928392939303931393239333934393539363937393839393940394139423943394439453946394739483949395039513952395339543955395639573958395939603961396239633964396539663967396839693970397139723973397439753976397739783979398039813982398339843985398639873988398939903991399239933994399539963997399839994000400140024003400440054006400740084009401040114012401340144015401640174018401940204021402240234024402540264027402840294030403140324033403440354036403740384039404040414042404340444045404640474048404940504051405240534054405540564057405840594060406140624063406440654066406740684069407040714072407340744075407640774078407940804081408240834084408540864087408840894090409140924093409440954096409740984099410041014102410341044105410641074108410941104111411241134114411541164117411841194120412141224123412441254126412741284129413041314132413341344135413641374138413941404141414241434144414541464147414841494150415141524153415441554156415741584159416041614162416341644165416641674168416941704171417241734174417541764177417841794180418141824183418441854186418741884189419041914192419341944195419641974198419942004201420242034204420542064207420842094210421142124213421442154216421742184219422042214222422342244225422642274228422942304231423242334234423542364237423842394240424142424243424442454246424742484249425042514252425342544255425642574258425942604261426242634264426542664267426842694270427142724273427442754276427742784279428042814282428342844285428642874288428942904291429242934294429542964297429842994300430143024303430443054306430743084309431043114312431343144315431643174318431943204321432243234324432543264327432843294330433143324333433443354336433743384339434043414342434343444345434643474348434943504351435243534354435543564357435843594360436143624363436443654366436743684369437043714372437343744375437643774378437943804381438243834384438543864387438843894390439143924393439443954396439743984399440044014402440344044405440644074408440944104411441244134414441544164417441844194420442144224423442444254426442744284429443044314432443344344435443644374438443944404441444244434444444544464447444844494450445144524453445444554456445744584459446044614462446344644465446644674468446944704471447244734474447544764477447844794480448144824483448444854486448744884489449044914492449344944495449644974498449945004501450245034504450545064507450845094510451145124513451445154516451745184519452045214522452345244525452645274528 |
- <?php
- /**
- * Created by PhpStorm.
- * User: Administrator
- * Date: 2020/1/7 0007
- * Time: 13:19
- */
- namespace App\Http\Controllers\Admin\Analyze;
- use App\Exports\BasicExport;
- use App\Http\Constant\CacheConstant;
- use App\Http\Controllers\Controller;
- use App\Http\Models\Article;
- use App\Http\Models\ArticleStatistic;
- use App\Http\Models\Business;
- use App\Http\Models\LinkStatistical;
- use App\Http\Models\LinkTaskDetail;
- use App\Http\Models\Role;
- use App\Http\Models\Site;
- use App\Http\Models\SitePayment;
- use App\Http\Models\User;
- use GuzzleHttp\Client;
- use Illuminate\Http\Request;
- use Illuminate\Database\Eloquent\Builder;
- use Illuminate\Support\Facades\Cache;
- use Illuminate\Support\Facades\DB;
- /**
- * 菜单栏 的 "数据"
- * Class IndexController
- * @package App\Http\Controllers\Admin\Analyze
- */
- class IndexController extends Controller
- {
- const NOT_RENEWED_MEMO_LIST = [
- 0 => '',
- 1 => '效果不好',
- 2 => '疫情影响',
- 3 => '业务转型',
- 4 => '价格高',
- 5 => '服务器不在引擎力',
- ];
- const REFUND_REASON_LIST = [
- 0 => '',
- 1 => '建站期太长',
- 2 => '客户原因',
- 3 => '客户原因/未启动',
- 4 => '实施期中止',
- 5 => '主动终止',
- ];
- //汇总
- public function overview(Request $request)
- {
- $rateFormat = function ($data) {
- return number_format($data, 2) * 100 . '%';
- };
- $thisMonth = [date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59')];
- $lastMonth = [date('Y-m-01 00:00:00', strtotime('last month')), date('Y-m-d 23:59:59', strtotime(date('Y-m-01') . '-1 day'))];
- //销售签单
- $nowMonthOrder = Site::query()->whereBetween('order_at', [$thisMonth, $lastMonth])->count();
- //分配
- $nowMonthAssign = Site::query()->whereBetween('assign_at', [$thisMonth, $lastMonth])->count();
- //当月上线
- $nowMonthOnline = Site::query()->whereBetween('online_at', [$thisMonth, $lastMonth])->count();
- //当月达标
- $nowMonthReach = Site::query()->whereBetween('reach_at', [$thisMonth, $lastMonth])->count();
- //上线消耗
- $onlineConsume = $nowMonthOrder > 0 ? $rateFormat($nowMonthOnline / $nowMonthOrder) : '无';
- //达标消耗
- $reachConsume = $nowMonthOrder > 0 ? $rateFormat($nowMonthReach / $nowMonthOrder) : "无";
- //项目总数
- $totalSite = Site::query()->count();
- //未下单项目数
- $noOrderCount = Site::query()->whereNull('assign_at')->count();
- $statusMap = Site::STATUS_MAP;
- $statusGroup = Site::query()->selectRaw('COUNT(id) AS count,status')->whereIn('status', array_keys($statusMap))->groupBy('status')->get()->pluck('count', 'status');
- $statusResult = [];
- foreach ($statusMap as $inx => $item) {
- $statusResult[$inx] = [
- 'count' => $statusGroup[$inx] ?? 0,
- 'title' => $item
- ];
- }
- //上线消耗:(总项目数-建站期-建站期暂停)/ 总项目数
- //达标消耗: 服务期总数/ 总项目数
- $totalOnlineConsume = $rateFormat(($totalSite - ($statusGroup[1] ?? 0) - ($statusGroup[4] ?? 0)) / $totalSite);
- $totalReachConsume = $rateFormat(($statusGroup[3] ?? 0) / $totalSite);
- $startYear = sprintf('%s-01-01 00:00:00', date('Y'));
- $endYear = sprintf('%s-12-31 11:59:59', date('Y'));
- $yearRenewal = Site::query()->where([
- ['renewal_at', '>=', $startYear],
- ['renewal_at', '<=', $endYear],
- ])->count();
- $yearExpired = Site::query()->where([
- ['expired_at', '>=', $startYear],
- ['expired_at', '<=', $endYear],
- ])->count();
- $renewalConsume = $yearExpired ? $rateFormat($yearRenewal / $yearExpired) : "无";
- $threeHistoryOnline = [];
- $threeHistoryReach = [];
- for ($i = 0; $i < 3; $i++) {
- $startMonth = date('Y-m-d 00:00:00', strtotime('first day of -' . $i . ' month'));
- $endMonth = date('Y-m-d 23:59:59', strtotime('last day of -' . $i . ' month'));
- $threeHistoryOnline[] = Site::query()->where([
- ['online_at', '>=', $startMonth],
- ['online_at', '<=', $endMonth]
- ])->count();
- $threeHistoryReach[] = Site::query()->where([
- ['reach_at', '>=', $startMonth],
- ['reach_at', '<=', $endMonth]
- ])->count();
- }
- return view('admin/analyze/overview',
- compact('nowMonthOrder',
- 'nowMonthAssign',
- 'nowMonthOnline',
- 'nowMonthReach',
- 'onlineConsume',
- 'reachConsume',
- 'totalSite',
- 'noOrderCount',
- 'yearRenewal',
- 'yearExpired',
- 'renewalConsume',
- 'threeHistoryOnline',
- 'threeHistoryReach',
- 'statusResult',
- 'totalOnlineConsume',
- 'totalReachConsume'
- ));
- }
- public function overviewAjax(Request $request)
- {
- $monthRange = $request->input('monthRange');
- $mr = explode(" - ", $monthRange);
- $start = $mr[0] ?? '';
- $end = $mr[1] ?? '';
- $rateFormat = function ($data) {
- return number_format($data, 2) * 100 . '%';
- };
- $nowMonthOrderWhere = [['order_at', '>=', date('Y-m-01 00:00:00')]];
- $nowMonthAssignWhere = [['assign_at', '>=', date('Y-m-01 00:00:00')]];
- $nowMonthOnlineWhere = [['online_at', '>=', date('Y-m-01 00:00:00')]];
- $nowMonthReachWhere = [['reach_at', '>=', date('Y-m-01 00:00:00')]];
- if ($start && $end) {
- $start = sprintf('%s-01 00:00:00', $start);
- $end = date('Y-m-t 23:59:59', strtotime(sprintf('%s-01 00:00:00', $end)));
- $nowMonthOrderWhere = [
- ['order_at', '>=', $start],
- ['order_at', '<=', $end],
- ];
- $nowMonthAssignWhere = [
- ['assign_at', '>=', $start],
- ['assign_at', '<=', $end],
- ];
- $nowMonthOnlineWhere = [
- ['online_at', '>=', $start],
- ['online_at', '<=', $end],
- ];
- $nowMonthReachWhere = [
- ['reach_at', '>=', $start],
- ['reach_at', '<=', $end],
- ];
- }
- //销售签单
- $nowMonthOrder = Site::query()->where($nowMonthOrderWhere)->count();
- $nowMonthAssign = Site::query()->where($nowMonthAssignWhere)->count();
- //当月上线
- $nowMonthOnline = Site::query()->where($nowMonthOnlineWhere)->count();
- //当月达标
- $nowMonthReach = Site::query()->where($nowMonthReachWhere)->count();
- //上线消耗
- $onlineConsume = $nowMonthOrder > 0 ? $rateFormat($nowMonthOnline / $nowMonthOrder) : '无';
- //达标消耗
- $reachConsume = $nowMonthOrder > 0 ? $rateFormat($nowMonthReach / $nowMonthOrder) : "无";
- return response()->json(['data' => compact(
- 'nowMonthOrder',
- 'nowMonthAssign',
- 'nowMonthOnline',
- 'nowMonthReach',
- 'onlineConsume',
- 'reachConsume'
- )]);
- }
- //下单
- public function placeOrder(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/place_order', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'editors' => Role::getUsers(Role::TYPE_AE),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- ]);
- }
- $builder = $this->placeOrderBuilder();
- $sites = $builder->orderByDesc('order_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- array_walk($items, function ($item) {
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->sign_at = substr($item->sign_at, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- $item->task_at = substr($item->task_at, 0, 10);
- });
- $result = [];
- foreach ($items as $item) {
- if (empty($item->order_at)) {
- $result[] = $item;
- }
- }
- foreach ($items as $item) {
- if (!empty($item->order_at)) {
- $result[] = $item;
- }
- }
- return response()->json([
- 'rows' => $result,
- 'total' => $sites->total()
- ]);
- }
- public function placeOrderBuilder()
- {
- $request = \request();
- $filterUserIds = [];
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $startDate = $request->input('startDate');
- $endDate = $request->input('endDate');
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- if ($startDate) {
- $start = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('order_at', '>=', $start);
- }
- if ($endDate) {
- $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
- $builder->where('order_at', '<=', $end);
- }
- if ($startDate && $endDate) {
- $startDate = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('order_at', '>=', $startDate)
- ->where('order_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
- }
- if (!$startDate && !$endDate) {
- $builder->where(function (Builder $q) {
- $q->whereNull('order_at')->orWhere('order_at', '>=', date('Y-m-01 00:00:00'));
- });
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- return $builder;
- }
- public function placeOrderExport()
- {
- $builder = $this->placeOrderBuilder();
- $sites = $builder->orderByDesc('order_at')->get()->toArray();
- $list = [];
- foreach ($sites as $key => $site) {
- $sellerTitle = [];//销售
- $serverTitle = [];//客服
- $managersTitle = [];//项目经理1
- $manageHelperTitle = [];//项目经理
- $aeTitle = [];//采编
- $optimizerTitle = [];//优化师
- $bidTitle = [];//竞价
- $articleTitle = [];//软文
- $designerTitle = [];//设计
- $webTitle = [];//前端
- $userList = $site['users'];
- foreach ($userList as $kk => $value) {
- if ($value['role_id'] == Role::TYPE_SELLER) {
- $sellerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_SERVER) {
- $serverTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGER) {
- $managersTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
- $manageHelperTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_AE) {
- $aeTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
- $optimizerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_BID) {
- $bidTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
- $articleTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_DESIGNER) {
- $designerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_WEB) {
- $webTitle [] = $value['nickname'];
- }
- }
- $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
- $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
- $list[$key]['task_at'] = substr($site['task_at'], 0, 10);//首款
- $list[$key]['cn_title'] = $site['cn_title'];//公司
- $list[$key]['domain'] = $site['domain'];//网站
- $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词达标
- $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
- $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
- $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理
- $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理1
- $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
- $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
- $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
- $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
- $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
- $list[$key]['webTitle'] = implode('-', $webTitle);//前端
- //$list[$key]['order_at'] = substr($site['order_at'], 0, 10);//分配时间
- $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
- $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
- $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
- $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
- $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
- $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
- }
- $result = [];
- foreach ($list as $item) {
- if (empty($item['order_at'])) {
- $result[] = $item;
- }
- }
- foreach ($list as $item) {
- if (!empty($item['order_at'])) {
- $result[] = $item;
- }
- }
- array_unshift($result, ['下单时间', '合同签订时间', '首款时间', '公司', '网站', '关键词指标', '项目销售', '客服经理', '项目经理1', '项目经理', '项目采编', '优化师', '竞价', '软文', '设计', '前端', '上线时间', '达标时间', '过期时间', '百千约访时间', '续费次数', '续费时间']);
- return (new BasicExport($result))->download(sprintf('下单%s.xls', date('YmdHis')));
- }
- //当月达标
- public function reach(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/reach', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'optimizersAe' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'editors' => Role::getUsers(Role::TYPE_AE),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- ]);
- }
- $builder = $this->reachBuilder();
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($items as $item) {
- $item->status = Site::STATUS_MAP[$item->status];
- $item->optimizerAe = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';
- $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联';
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->sign_at = substr($item->sign_at, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- $item->reach_mail_at = substr($item->reach_mail_at, 0, 10);
- $item->reach_pay_at = substr($item->reach_pay_at, 0, 10);
- if ($item->reach_at && $item->online_at) {
- $reachAt = date_create($item->reach_at);
- $onlineAt = date_create($item->online_at);
- $diff = date_diff($reachAt, $onlineAt);
- $item->reach_days = $diff->days;
- } else {
- $item->reach_days = null;
- }
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- protected function reachBuilder()
- {
- $request = \request();
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $optimizerAeId = $request->input('optimizerAeId');
- $optimizerAeId && $filterUserIds[] = $optimizerAeId;
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $keyword = $request->input('keyword');
- $status = $request->input('status');
- $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword, $status) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- if ($status) {
- $q->where('status', $status);
- }
- });
- $startDate = $request->input('startDate');
- $endDate = $request->input('endDate');
- if ($startDate) {
- $start = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('reach_at', '>=', $start);
- }
- if ($endDate) {
- $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
- $builder->where('reach_at', '<=', $end);
- }
- if ($startDate && $endDate) {
- $startDate = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('reach_at', '>=', $startDate)
- ->where('reach_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
- }
- if (!$startDate && !$endDate) {
- $startDate = date('Y-m-01 00:00:00');
- $builder->whereRaw('reach_at >= ? AND reach_at <= ?', [
- date('Y-m-01 00:00:00'),
- sprintf("%s-%s 23:59:59", date('Y-m'), date('t', strtotime($startDate)))
- ]
- );
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- return $builder;
- }
- //导出达标项目
- public function reachExport()
- {
- $builder = $this->reachBuilder();
- $items = $builder->orderByDesc('id')->get();
- $oldIds = array_filter(array_column($items->toArray(), 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- $results = [];
- foreach ($items as $item) {
- if ($item->reach_at && $item->online_at) {
- $reachAt = date_create($item->reach_at);
- $onlineAt = date_create($item->online_at);
- $diff = date_diff($reachAt, $onlineAt);
- $reach_days = $diff->days;
- } else {
- $reach_days = null;
- }
- $results[] = [
- 'reach_at' => substr($item->reach_at, 0, 10),
- 'expired_at' => substr($item->expired_at, 0, 10),
- 'reach_mail_at' => substr($item->reach_mail_at, 0, 10),
- 'reach_pay_at' => substr($item->reach_pay_at, 0, 10),
- 'cn_title' => $item->cn_title,
- 'status' => Site::STATUS_MAP[$item->status],
- 'domain' => $item->domain,
- 'keyword_goal' => $item->keyword_goal,
- 'top10' => $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联',
- 'reach_days' => $reach_days,
- 'inquire' => $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联',
- 'seller_title' => implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray()),
- 'server_title' => implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray()),
- 'optimizerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray()),
- 'optimizerAe' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray()),
- 'bidTitle' => implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()),
- 'articleTitle' => implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray()),
- 'designerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray()),
- 'webTitle' => implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray()),
- 'sign_at' => substr($item->sign_at, 0, 10),
- 'order_at' => substr($item->order_at, 0, 10),
- 'assign_at' => substr($item->assign_at, 0, 10),
- 'online_at' => substr($item->online_at, 0, 10),
- 'bq_at' => substr($item->bq_at, 0, 10),
- 'renewal_times' => $item->renewal_times,
- 'renewal_at' => substr($item->renewal_at, 0, 10),
- ];
- };
- array_unshift($results, ['达标时间', '过期时间', '达标邮件确认时间', '达标款支付时间', '公司', '项目状态', '网站', '关键词达标', '实际达标关键词', '达标时长', '询盘', '项目销售', '客服经理', '优化师', '优化采编', '竞价', '软文', '设计', '前端', '合同签订时间', '下单时间', '分配时间', '上线时间', '百千约访时间', '续费次数', '续费时间']);
- return (new BasicExport($results))->download(sprintf('当月达标%s.xls', date('YmdHis')));
- }
- //历史达标
- public function historyReach(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/history_reach', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'editors' => Role::getUsers(Role::TYPE_AE),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- ]);
- }
- $builder = $this->historyReachBuilder();
- $sites = $builder->orderByDesc('reach_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($items as $item) {
- if ($item->reach_at && $item->online_at) {
- $reachAt = date_create($item->reach_at);
- $onlineAt = date_create($item->online_at);
- $diff = date_diff($reachAt, $onlineAt);
- $item->reach_days = $diff->days;
- } else {
- $item->reach_days = null;
- }
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联';
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->sign_at = substr($item->sign_at, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- protected function historyReachBuilder()
- {
- $request = \request();
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- })->whereIn('status', [1, 2, 3, 4, 5, 6, 8, 9]);
- $startDate = $request->input('startDate');
- $endDate = $request->input('endDate');
- if ($startDate) {
- $start = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('reach_at', '>=', $start);
- }
- if ($endDate) {
- $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
- $builder->where('reach_at', '<=', $end);
- }
- if ($startDate && $endDate) {
- $startDate = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('reach_at', '>=', $startDate)
- ->where('reach_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
- }
- if (!$startDate && !$endDate) {
- $date = date('Y-m-01 00:00:00');
- $builder->whereRaw('expired_at is null and reach_at < ?', [$date]);
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- return $builder;
- }
- //历史达标导出
- public function historyReachExport()
- {
- $builder = $this->historyReachBuilder();
- $items = $builder->orderByDesc('reach_at')->get();
- $oldIds = array_filter(array_column($items->toArray(), 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- $results = [];
- foreach ($items as $item) {
- if ($item->reach_at && $item->online_at) {
- $reachAt = date_create($item->reach_at);
- $onlineAt = date_create($item->online_at);
- $diff = date_diff($reachAt, $onlineAt);
- $reach_days = $diff->days;
- } else {
- $reach_days = null;
- }
- $results[] = [
- 'reach_at' => substr($item->reach_at, 0, 10),
- 'expired_at' => substr($item->expired_at, 0, 10),
- 'cn_title' => $item->cn_title,
- 'status' => Site::STATUS_MAP[$item->status],
- 'domain' => $item->domain,
- 'keyword_goal' => $item->keyword_goal,
- 'top10' => $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联',
- 'reach_days' => $reach_days,
- 'seller_title' => implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray()),
- 'server_title' => implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray()),
- 'managers_title' => implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray()),
- 'manage_helper_title' => implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray()),
- 'optimizerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray()),
- 'ae_title' => implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray()),
- 'bidTitle' => implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()),
- 'articleTitle' => implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray()),
- 'designerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray()),
- 'webTitle' => implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray()),
- 'sign_at' => substr($item->sign_at, 0, 10),
- 'order_at' => substr($item->order_at, 0, 10),
- 'assign_at' => substr($item->assign_at, 0, 10),
- 'online_at' => substr($item->online_at, 0, 10),
- 'bq_at' => substr($item->bq_at, 0, 10),
- 'renewal_times' => $item->renewal_times,
- 'renewal_at' => substr($item->renewal_at, 0, 10),
- ];
- };
- array_unshift($results, ['达标时间', '过期时间', '公司', '项目状态', '网站', '关键词达标',
- '实际达标关键词', '达标时长', '项目销售', '客服经理', '项目经理1', '项目经理', '优化师', '项目采编', '竞价', '软文', '设计', '前端', '合同签订时间', '下单时间', '分配时间', '上线时间', '百千约访时间', '续费次数', '续费时间']);
- return (new BasicExport($results))->download(sprintf('历史达标%s.xls', date('YmdHis')));
- }
- //上线
- public function online(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/online', [
- 'webs' => Role::getUsers(Role::TYPE_WEB),
- 'design' => Role::getUsers(Role::TYPE_DESIGNER),
- 'services' => Role::getUsers(Role::TYPE_SERVER),
- 'editors' => Role::getUsers(Role::TYPE_AE),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'optimizerAes' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
- ]);
- }
- $builder = $this->onlineBuilder();
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- foreach ($items as $item) {
- $item->identify = base64_encode($item->id);
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->optimizerAe = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray());
- $item->ae = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->web = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->sign_at = substr($item->sign_at, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- if ($item->online_at && $item->assign_at) {
- $orderAt = date_create($item->assign_at);
- $onlineAt = date_create($item->online_at);
- $diff = date_diff($orderAt, $onlineAt);
- $item->online_days = $diff->days;
- } else {
- $item->online_days = null;
- }
- $item->online_at = substr($item->online_at, 0, 10);
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- public function onlineBuilder()
- {
- $request = \request();
- $filterUserIds = [];
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $optimizerAeId = $request->input('optimizerAeId');
- $optimizerAeId && $filterUserIds[] = $optimizerAeId;
- $designId = $request->input('designId');
- $designId && $filterUserIds[] = $designId;
- $webId = $request->input('webId');
- $webId && $filterUserIds[] = $webId;
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- $sortName = $request->input('sortName');
- $sortOrder = $request->input('sortOrder');
- if (!empty($sortName) || !empty($sortOrder)) {
- $builder->orderBy($sortName, $sortOrder);
- }
- $startDate = $request->input('startDate');
- $endDate = $request->input('endDate');
- if ($startDate) {
- $start = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('online_at', '>=', $start);
- }
- if ($endDate) {
- $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
- $builder->where('online_at', '<=', $end);
- }
- if ($startDate && $endDate) {
- $startDate = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('online_at', '>=', $startDate)
- ->where('online_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
- }
- if (!$startDate && !$endDate) {
- $startDate = date('Y-m-01 00:00:00');
- $builder->Where('online_at', '>=', date('Y-m-01 00:00:00'))
- ->where('online_at', '<=', sprintf("%s-%s 23:59:59", date('Y-m'), date('t', strtotime($startDate))));
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- return $builder;
- }
- public function onlineExport()
- {
- $builder = $this->onlineBuilder();
- $sites = $builder->orderByDesc('id')->get()->toArray();
- $list = [];
- foreach ($sites as $key => $site) {
- $onlineDays = 0;
- if ($site['online_at'] && $site['assign_at']) {
- $orderAt = date_create($site['assign_at']);
- $onlineAt = date_create($site['online_at']);
- $diff = date_diff($orderAt, $onlineAt);
- $onlineDays = $diff->days;
- }
- $sellerTitle = [];//销售
- $serverTitle = [];//客服
- $managersTitle = [];//项目经理1
- $manageHelperTitle = [];//项目经理
- $optimizerAe = [];//优化采编
- $ae = [];//采编
- $optimizerTitle = [];//优化师
- $bidTitle = [];//竞价
- $articleTitle = [];//软文
- $designerTitle = [];//设计
- $webTitle = [];//前端
- $userList = $site['users'];
- foreach ($userList as $kk => $value) {
- if ($value['role_id'] == Role::TYPE_SELLER) {
- $sellerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_SERVER) {
- $serverTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGER) {
- $managersTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
- $manageHelperTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_OPTIMIZATION_EDITING) {
- $optimizerAe [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_AE) {
- $ae [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
- $optimizerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_BID) {
- $bidTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
- $articleTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_DESIGNER) {
- $designerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_WEB) {
- $webTitle [] = $value['nickname'];
- }
- }
- $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
- $list[$key]['cn_title'] = $site['cn_title'];//公司
- $list[$key]['domain'] = $site['domain'];//网站
- $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词指标
- $list[$key]['online_days'] = $onlineDays;//建站时长
- $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
- $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
- $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理
- $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理1
- $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
- $list[$key]['optimizerAe'] = implode('-', $optimizerAe);//优化采编
- $list[$key]['ae'] = implode('-', $ae);//采编
- $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
- $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
- $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
- $list[$key]['webTitle'] = implode('-', $webTitle);//前端
- $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
- $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
- $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
- //$list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
- $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
- $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
- $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
- $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
- $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
- }
- array_unshift($list, ['上线时间', '公司', '网站', '关键词指标', '建站时长', '项目销售', '客服经理', '项目经理1', '项目经理', '优化师', '优化采编', '采编', '竞价', '软文', '设计', '前端', '合同签订时间', '下单时间', '分配时间', '达标时间', '过期时间', '百千约访时间', '续费次数', '续费时间']);
- return (new BasicExport($list))->download(sprintf('上线%s.xls', date('YmdHis')));
- }
- //待续费
- public function renewal(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/renewal', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'editors' => Role::getUsers(Role::TYPE_AE),
- ]);
- }
- $builder = $this->renewalBuilder();
- $sites = $builder->orderBy('expired_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($items as $item) {
- $item->status = Site::STATUS_MAP[$item->status];
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
- $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
- $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->sign_at = substr($item->sign_at, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- public function renewalBuilder()
- {
- $request = \request();
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $keyword = $request->input('keyword');
- $status = $request->input('status');
- $builder = Site::query()->with(['users'])->whereIn('status', [2, 3, 5])->where(function (Builder $q) use ($keyword, $status) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- if ($status) {
- $q->where('status', $status);
- }
- });
- $startDate = $request->input('startDate');
- $endDate = $request->input('endDate');
- if ($startDate) {
- $start = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('expired_at', '>=', $start);
- }
- if ($endDate) {
- $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
- $builder->where('expired_at', '<=', $end);
- }
- if ($startDate && $endDate) {
- $startDate = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('expired_at', '>=', $startDate)
- ->where('expired_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
- }
- if (!$startDate && !$endDate) {
- $builder->where([
- ['expired_at', '<', date('Y-m-d H:i:s', strtotime('+3 month'))]
- ]);
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- return $builder;
- }
- public function renewalExport()
- {
- $builder = $this->renewalBuilder();
- $sites = $builder->orderBy('expired_at')->get()->toArray();
- $list = [];
- $oldIds = array_filter(array_column($sites, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($sites as $key => $site) {
- $isRenewal = '否';//是否续费
- $sellerTitle = [];//销售
- $serverTitle = [];//客服
- $managersTitle = [];//项目经理1
- $manageHelperTitle = [];//项目经理
- $aeTitle = [];//采编
- $optimizerTitle = [];//优化师
- $bidTitle = [];//竞价
- $articleTitle = [];//软文
- $designerTitle = [];//设计
- $webTitle = [];//前端
- $userList = $site['users'];
- foreach ($userList as $kk => $value) {
- if ($value['role_id'] == Role::TYPE_SELLER) {
- $sellerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_SERVER) {
- $serverTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGER) {
- $managersTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
- $manageHelperTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_AE) {
- $aeTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
- $optimizerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_BID) {
- $bidTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
- $articleTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_DESIGNER) {
- $designerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_WEB) {
- $webTitle [] = $value['nickname'];
- }
- }
- if (!empty($site['renewal_at'])) {
- $isRenewal = '是';
- }
- $list[$key]['cn_title'] = $site['cn_title'];
- $list[$key]['status'] = Site::STATUS_MAP[$site['status']];
- $list[$key]['domain'] = $site['domain'];
- $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
- $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
- $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
- $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
- $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词
- $list[$key]['top10'] = $top10ListMap[$site['old_id']]->top10 ?? '';
- $list[$key]['traffic'] = $listReportMap[$site['old_id']]->traffic ?? '';
- $list[$key]['inquire'] = $listReportMap[$site['old_id']]->inquire ?? '';
- $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
- $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
- $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理1
- $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理
- $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
- $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
- $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
- $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
- $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
- $list[$key]['webTitle'] = implode('-', $webTitle);//前端
- $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
- $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
- $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
- $list[$key]['is_renewal'] = $isRenewal;
- $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
- }
- array_unshift($list, ['公司', '项目状态', '网站', '下单时间', '上线时间', '过期时间', '达标时间', '关键词指标', '实际达标关键词',
- '月流量', '询盘总数', '销售', '客服', '项目总监', '项目经理', '优化组长', '建站采编',
- '竞价', '软文', '设计', '前端', '合同签订时间', '分配时间', '百千约访时间', '是否续费', '续费次数']);
- return (new BasicExport($list))->download(sprintf('待续费%s.xls', date('YmdHis')));
- }
- //未续费
- public function notRenewed(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/not_renewed', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'editors' => Role::getUsers(Role::TYPE_AE),
- ]);
- }
- $builder = $this->notRenewedBuilder();
- $sites = $builder->orderBy('expired_at', 'desc')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($items as $item) {
- $item->status = Site::STATUS_MAP[$item->status];
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
- $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
- $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->sign_at = substr($item->sign_at, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
- $item->notRenewedReasonAnalyze = self::NOT_RENEWED_MEMO_LIST[$item->sitePayment->not_renewed_reason_analyze];
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- public function notRenewedBuilder()
- {
- $request = \request();
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $keyword = $request->input('keyword');
- $status = $request->input('status');
- $build = Site::query()->with(['users', 'sitePayment']);
- $notRenewedReasonAnalyze = $request->input('not_renewed_reason_analyze');
- if (!empty($notRenewedReasonAnalyze)) {
- $siteIds = SitePayment::query()
- ->where('not_renewed_reason_analyze', $notRenewedReasonAnalyze)
- ->pluck('site_id');
- $build->whereIn('id', $siteIds);
- }
- $builder = $build->where('status', 10)->where(function (Builder $q) use ($keyword, $status) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- if ($status) {
- $q->where('status', $status);
- }
- });
- $startDate = $request->input('startDate');
- $endDate = $request->input('endDate');
- if ($startDate) {
- $start = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('expired_at', '>=', $start);
- }
- if ($endDate) {
- $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
- $builder->where('expired_at', '<=', $end);
- }
- if ($startDate && $endDate) {
- $startDate = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('expired_at', '>=', $startDate)
- ->where('expired_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
- }
- if (!$startDate && !$endDate) {
- $builder->where([
- ['expired_at', '<', date('Y-m-d H:i:s', strtotime('+3 month'))]
- ]);
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- return $builder;
- }
- public function notRenewedExport()
- {
- $builder = $this->notRenewedBuilder();
- $sites = $builder->orderBy('expired_at', 'desc')->get()->toArray();
- $list = [];
- $oldIds = array_filter(array_column($sites, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($sites as $key => $site) {
- $sellerTitle = [];//销售
- $serverTitle = [];//客服
- $managersTitle = [];//项目经理1
- $manageHelperTitle = [];//项目经理
- $aeTitle = [];//采编
- $optimizerTitle = [];//优化师
- $bidTitle = [];//竞价
- $articleTitle = [];//软文
- $designerTitle = [];//设计
- $webTitle = [];//前端
- $userList = $site['users'];
- foreach ($userList as $kk => $value) {
- if ($value['role_id'] == Role::TYPE_SELLER) {
- $sellerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_SERVER) {
- $serverTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGER) {
- $managersTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
- $manageHelperTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_AE) {
- $aeTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
- $optimizerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_BID) {
- $bidTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
- $articleTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_DESIGNER) {
- $designerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_WEB) {
- $webTitle [] = $value['nickname'];
- }
- }
- $list[$key]['cn_title'] = $site['cn_title'];
- $list[$key]['status'] = Site::STATUS_MAP[$site['status']];
- $list[$key]['domain'] = $site['domain'];
- $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
- $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
- $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
- $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
- $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词
- $list[$key]['top10'] = $top10ListMap[$site['old_id']]->top10 ?? '';
- $list[$key]['traffic'] = $listReportMap[$site['old_id']]->traffic ?? '';
- $list[$key]['inquire'] = $listReportMap[$site['old_id']]->inquire ?? '';
- $list[$key]['notRenewedReasonAnalyze'] = self::NOT_RENEWED_MEMO_LIST[$site['site_payment']['not_renewed_reason_analyze']];
- $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
- $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
- $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理1
- $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理
- $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
- $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
- $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
- $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
- $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
- $list[$key]['webTitle'] = implode('-', $webTitle);//前端
- $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
- //$list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
- $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
- $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
- $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
- }
- array_unshift($list, ['公司', '项目状态', '网站', '过期时间', '下单时间', '上线时间', '达标时间',
- '关键词指标', '实际达标关键词', '月流量', '询盘总数', '未续费原因', '销售', '客服',
- '项目总监', '项目经理', '优化组长', '建站采编', '竞价', '软文', '设计', '前端', '合同签订时间', '百千约访时间', '续费次数', '续费时间']);
- return (new BasicExport($list))->download(sprintf('未续费%s.xls', date('YmdHis')));
- }
- //退款
- public function refund(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/refund', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'editors' => Role::getUsers(Role::TYPE_AE),
- ]);
- }
- $builder = $this->refundBuilder();
- $sites = $builder->orderBy('expired_at', 'desc')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($items as $item) {
- $item->status = Site::STATUS_MAP[$item->status];
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->refund = $item->sitePayment->refund ?? 0;
- $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
- $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
- $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->refund_date = substr($item->refund_date, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
- $item->refundReason = self::REFUND_REASON_LIST[$item->sitePayment->refund_reason_analyze];
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- public function refundBuilder()
- {
- $request = \request();
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $keyword = $request->input('keyword');
- $status = $request->input('status');
- $siteIds = SitePayment::query()->whereNotNull('refund')->pluck('site_id');
- $build = Site::query()->with(['users', 'sitePayment']);
- $notRenewedReasonAnalyze = $request->input('not_renewed_reason_analyze');
- if (!empty($notRenewedReasonAnalyze)) {
- $siteIds = SitePayment::query()
- ->where('not_renewed_reason_analyze', $notRenewedReasonAnalyze)
- ->pluck('site_id');
- $build->whereIn('id', $siteIds);
- }
- $refundReasonAnalyze = $request->input('refund_reason_analyze');
- if (!empty($refundReasonAnalyze)) {
- $siteIds = SitePayment::query()
- ->where('refund_reason_analyze', $refundReasonAnalyze)
- ->pluck('site_id');
- $build->whereIn('id', $siteIds);
- }
- $builder = $build
- ->whereIn('id', $siteIds)
- ->with(['users', 'sitePayment'])
- ->where(function (Builder $q) use ($keyword, $status) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- if ($status) {
- $q->where('status', $status);
- }
- });
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- return $builder;
- }
- public function refundExport()
- {
- $builder = $this->refundBuilder();
- $sites = $builder->orderBy('expired_at', 'desc')->get()->toArray();
- $list = [];
- $oldIds = array_filter(array_column($sites, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($sites as $key => $site) {
- $sellerTitle = [];//销售
- $serverTitle = [];//客服
- $managersTitle = [];//项目经理1
- $manageHelperTitle = [];//项目经理
- $aeTitle = [];//采编
- $optimizerTitle = [];//优化师
- $bidTitle = [];//竞价
- $articleTitle = [];//软文
- $designerTitle = [];//设计
- $webTitle = [];//前端
- $userList = $site['users'];
- foreach ($userList as $kk => $value) {
- if ($value['role_id'] == Role::TYPE_SELLER) {
- $sellerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_SERVER) {
- $serverTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGER) {
- $managersTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
- $manageHelperTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_AE) {
- $aeTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
- $optimizerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_BID) {
- $bidTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
- $articleTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_DESIGNER) {
- $designerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_WEB) {
- $webTitle [] = $value['nickname'];
- }
- }
- $list[$key]['cn_title'] = $site['cn_title'];
- $list[$key]['status'] = Site::STATUS_MAP[$site['status']];
- $list[$key]['domain'] = $site['domain'];
- $list[$key]['refund'] = $site['site_payment']['reach'] ?? '';
- $list[$key]['refund_date'] = substr($site['refund_date'], 0, 10);//过期时间
- $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
- $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
- $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
- $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词
- $list[$key]['top10'] = $top10ListMap[$site['old_id']]->top10 ?? '';
- $list[$key]['traffic'] = $listReportMap[$site['old_id']]->traffic ?? '';
- $list[$key]['inquire'] = $listReportMap[$site['old_id']]->inquire ?? '';
- $list[$key]['refundReason'] = self::REFUND_REASON_LIST[$site['site_payment']['refund_reason_analyze']];
- $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
- $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
- $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理1
- $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理
- $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
- $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
- $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
- $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
- $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
- $list[$key]['webTitle'] = implode('-', $webTitle);//前端
- $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
- $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
- $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
- $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
- $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
- }
- array_unshift($list, ['公司', '项目状态', '网站', '退款金额', '退款时间', '下单时间', '上线时间',
- '达标时间', '关键词指标', '实际达标关键词', '月流量', '询盘总数', '退款原因', '销售', '客服',
- '项目总监', '项目经理', '优化组长', '建站采编', '竞价', '软文', '设计', '前端', '合同签订时间', '分配时间', '百千约访时间', '续费次数', '续费时间']);
- return (new BasicExport($list))->download(sprintf('退款%s.xls', date('YmdHis')));
- }
- //已续费
- public function hasRenewal(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/has_renewal', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'editors' => Role::getUsers(Role::TYPE_AE),
- ]);
- }
- $builder = $this->hasRenewalRenewalBuilder();
- $sites = $builder->orderBy('renewal_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($items as $item) {
- $item->status = Site::STATUS_MAP[$item->status];
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
- $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
- $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->sign_at = substr($item->sign_at, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
- //$item->renew_type = empty($item->is_renew) ? '优化续费' : '网站续费';
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- private function getInquiryAndFlowList($oldIds)
- {
- $rankConnection = DB::connection('rank');
- $top10ListMap = $rankConnection->table('project_keyword')
- ->whereIn('project_id', $oldIds)
- ->selectRaw('SUM(CASE WHEN google_rank <= 10 THEN 1 ELSE 0 END) as top10,project_id')
- ->groupBy('project_id')->get()->keyBy('project_id')->toArray();
- $listReportMap = $rankConnection->table('project_listreport')
- ->whereIn('project_id', $oldIds)
- ->where(['ym' => date('Ym', strtotime('first day of -1 month'))
- ])->get()->keyBy('project_id')->toArray();
- return [$top10ListMap, $listReportMap];
- }
- public function hasRenewalRenewalBuilder()
- {
- $request = \request();
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $keyword = $request->input('keyword');
- $status = $request->input('status');
- $builder = Site::query()
- ->where('status', 3)
- ->with(['users'])->where($condition ?? [])->where(function (Builder $q) use ($keyword, $status) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- if ($status) {
- $q->where('status', $status);
- }
- });
- $startDate = $request->input('startDate');
- $endDate = $request->input('endDate');
- if ($startDate) {
- $start = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('renewal_at', '>=', $start);
- }
- if ($endDate) {
- $end = sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate)));
- $builder->where('renewal_at', '<=', $end);
- }
- if ($startDate && $endDate) {
- $startDate = sprintf("%s-01 00:00:00", $startDate);
- $builder->where('renewal_at', '>=', $startDate)
- ->where('renewal_at', '<=', sprintf("%s-%s 23:59:59", $endDate, date('t', strtotime($endDate))));
- }
- if (!$startDate && !$endDate) {
- $startDate = date('Y-m-01 00:00:00');
- $builder->Where('renewal_at', '>=', $startDate)
- ->where('renewal_at', '<=', sprintf("%s-%s 23:59:59", date('Y-m'), date('t', strtotime($startDate))));
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- return $builder;
- }
- public function hasRenewalExport()
- {
- $builder = $this->hasRenewalRenewalBuilder();
- $sites = $builder->orderBy('renewal_at')->get()->toArray();
- $list = [];
- $oldIds = array_filter(array_column($sites, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($sites as $key => $site) {
- $sellerTitle = [];//销售
- $serverTitle = [];//客服
- $managersTitle = [];//项目经理1
- $manageHelperTitle = [];//项目经理
- $aeTitle = [];//采编
- $optimizerTitle = [];//优化师
- $bidTitle = [];//竞价
- $articleTitle = [];//软文
- $designerTitle = [];//设计
- $webTitle = [];//前端
- $userList = $site['users'];
- foreach ($userList as $kk => $value) {
- if ($value['role_id'] == Role::TYPE_SELLER) {
- $sellerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_SERVER) {
- $serverTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGER) {
- $managersTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
- $manageHelperTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_AE) {
- $aeTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
- $optimizerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_BID) {
- $bidTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
- $articleTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_DESIGNER) {
- $designerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_WEB) {
- $webTitle [] = $value['nickname'];
- }
- }
- $list[$key]['cn_title'] = $site['cn_title'];
- $list[$key]['status'] = Site::STATUS_MAP[$site['status']];
- $list[$key]['domain'] = $site['domain'];
- $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
- $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
- $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
- $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
- $list[$key]['renew_years'] = $site['renew_years'] ?? 0;//最近一次续费年限
- $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
- $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词
- $list[$key]['top10'] = $top10ListMap[$site['old_id']]->top10 ?? '';
- $list[$key]['traffic'] = $listReportMap[$site['old_id']]->traffic ?? '';
- $list[$key]['inquire'] = $listReportMap[$site['old_id']]->inquire ?? '';
- $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
- $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
- $list[$key]['managers_title'] = implode('-', $managersTitle);//项目总监
- $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理
- $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化组长
- $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
- $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
- $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
- $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
- $list[$key]['webTitle'] = implode('-', $webTitle);//前端
- $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
- $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
- $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
- }
- array_unshift($list, ['公司', '项目状态', '网站', '下单时间', '上线时间', '续费时间', '续费次数', '最近一次续费年限', '达标时间',
- '关键词指标', '实际达标关键词', '月流量', '询盘总数', '销售', '客服', '项目总监', '项目经理', '优化组长',
- '建站采编', '竞价', '软文', '设计', '前端', '合同签订时间', '过期时间', '百千约访时间']);
- return (new BasicExport($list))->download(sprintf('已续费%s.xls', date('YmdHis')));
- }
- //百千计划
- public function plan(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/plan', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- ]);
- }
- list($items, $nowtMonthKeywords, $nowListReportMap, $listReportMapResult, $listReportMapResult1, $keywordDropResults, $keywordDropResults1, $clicksDropResults, $clicksDropResults1, $sites, $sortName, $sortOrder) = $this->planBuilder();
- $result = [];
- foreach ($items as $item) {
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->top10 = $item->old_id ? ($nowtMonthKeywords[$item->old_id] ?? '-') : '未关联';
- $item->traffic = $item->old_id ? ($nowListReportMap[$item->old_id]->traffic ?? '-') : '未关联';
- $item->inquire = $item->old_id ? ($nowListReportMap[$item->old_id]->inquire ?? '-') : '未关联';
- $item->listReportMapResult = $listReportMapResult[$item->old_id] ?? '';
- $item->listReportMapResult1 = $listReportMapResult1[$item->old_id] ?? '';
- $item->keywordDropResults = $keywordDropResults[$item->old_id] ?? '';
- $item->keywordDropResults1 = $keywordDropResults1[$item->old_id] ?? '';
- $item->clicksDropResults = $clicksDropResults[$item->old_id] ?? '';
- $item->clicksDropResults1 = $clicksDropResults1[$item->old_id] ?? '';
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->bq_time = strtotime($item->bq_at);
- $item->sign_at = substr($item->sign_at, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- $result[] = $item;
- };
- $resList = collect($result);
- if ($sortName == 'bq_at') {
- $sortName = 'bq_time';
- }
- if (in_array($sortName, ['bq_time', 'traffic', 'inquire', 'top10'])) {
- $descending = strtolower($sortOrder) == 'asc' ? false : true;
- $resList = $resList->sortBy(function ($item, $key) use ($sortName, $descending) {
- return intval($item[$sortName]);
- }, SORT_REGULAR, $descending)->values();
- }
- return response()->json([
- 'rows' => $resList,
- 'total' => $sites->total()
- ]);
- }
- public function planBuilder($type = false)
- {
- $user = auth()->user();
- if (empty($user->is_super) && !in_array($user->id, [240, 554, 836])) {
- $siteList = DB::table('user_has_sites')->where('user_id', $user->id)->pluck('site_id')->toArray();
- $condition[] = [function ($query) use ($siteList) {
- $query->whereIn('id', $siteList);
- }];
- }
- $condition['is_bq'] = 1;
- $request = \request();
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $sortName = $request->input('sortName');
- $sortOrder = $request->input('sortOrder');
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users'])
- ->where('status', '!=', '7')
- ->where($condition)
- ->whereIn('status', [2, 3])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- if ($type == false) {
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- } else {
- $sites = $builder->orderByDesc('id')->get();
- $items = $sites->toArray();
- }
- //所有百千项目
- $siteBqList = Site::query()
- ->where('status', '!=', '7')
- ->where('is_bq', 1)
- ->whereIn('status', [2, 3])
- ->get();
- $projectCondition = [];
- foreach ($siteBqList as $item) {
- $data = [
- 'old_id' => $item->old_id,
- 'bq_at' => date('Y-m', strtotime($item->bq_at)),
- ];
- $projectCondition[] = $data;
- }
- //上个月的关键词排名
- if (!Cache::has(CacheConstant::NOW_MONTH_KEY_WORDS_CACHE_KEY)) {
- $maxIdList = DB::connection('rank')
- ->table('project_history')
- ->selectRaw('max(id) as max_id')->where([
- ['create_time', '>', strtotime(date('Y-m-01', strtotime('-1 month')))],
- ['create_time', '<', strtotime(date('Y-m-t 23:59:59', strtotime('-1 month')))],
- ])->groupBy('project_id')->pluck('max_id')->toArray();
- $nowtMonthKeywords = DB::connection('rank')->table('project_history')
- ->select('top10', 'project_id')->whereIn('id', $maxIdList)
- ->pluck('top10', 'project_id')->toArray();
- Cache::put(CacheConstant::NOW_MONTH_KEY_WORDS_CACHE_KEY, $nowtMonthKeywords, mt_rand(54000, 57600));
- }
- $nowtMonthKeywords = Cache::get(CacheConstant::NOW_MONTH_KEY_WORDS_CACHE_KEY) ?? [];
- //百千约访时的关键词排名
- if (!Cache::has(CacheConstant::LAST_MONTH_BQ_KEY_WORDS_CACHE_KEY)) {
- $bqLastKeywordsList = [];
- foreach ($projectCondition as $value) {
- $maxIdList = DB::connection('rank')
- ->table('project_history')
- ->selectRaw('max(id) as max_id')->where([
- ['create_time', '>', strtotime($value['bq_at'])],
- ['create_time', '<', strtotime('+1 month', strtotime($value['bq_at']))],
- ['project_id', '=', $value['old_id']],
- ])->groupBy('project_id')->pluck('max_id')->toArray();
- $keywordsInfo = DB::connection('rank')->table('project_history')
- ->select('top10', 'project_id')->whereIn('id', $maxIdList)
- ->pluck('top10', 'project_id')->toArray();
- foreach ($keywordsInfo as $key => $item) {
- $bqLastKeywordsList[$key] = $item;
- }
- }
- Cache::put(CacheConstant::LAST_MONTH_BQ_KEY_WORDS_CACHE_KEY, $bqLastKeywordsList, mt_rand(54000, 57600));
- }
- $bqLastKeywordsList = Cache::get(CacheConstant::LAST_MONTH_BQ_KEY_WORDS_CACHE_KEY);
- $keywordDropResults = [];
- $keywordDropResults1 = [];
- foreach ($nowtMonthKeywords as $inx => $val) {
- $nowTop10 = $bqLastKeywordsList[$inx] ?? 0;
- if ($nowTop10 > 0) {
- $lessVal = $val - $nowTop10;
- if ($lessVal > 0) {
- $keywordDropResults1[$inx] = $lessVal;
- } else {
- $keywordDropResults[$inx] = abs($lessVal);
- }
- }
- }
- //百千时间的询盘
- if (!Cache::has(CacheConstant::LAST_MONTH_BQ_INQUIRE_CACHE_KEY)) {
- $bqLastListReportList = [];
- foreach ($projectCondition as $value) {
- $listReportList = DB::connection('rank')
- ->table('project_listreport')
- ->where('ym', date('Ym', strtotime($value['bq_at'])))
- ->where('project_id', $value['old_id'])
- ->get()->keyBy('project_id')->toArray();
- foreach ($listReportList as $key => $item) {
- $bqLastListReportList[$key] = $item;
- }
- }
- Cache::put(CacheConstant::LAST_MONTH_BQ_INQUIRE_CACHE_KEY, $bqLastListReportList, mt_rand(54000, 57600));
- }
- $bqLastListReportList = Cache::get(CacheConstant::LAST_MONTH_BQ_INQUIRE_CACHE_KEY);
- //上月询盘
- if (!Cache::has(CacheConstant::NOW_MONTH_INQUIRE_CACHE_KEY)) {
- $lastListReportList = DB::connection('rank')
- ->table('project_listreport')
- ->where('ym', date('Ym', strtotime('first day of -1 month')))
- ->get()->keyBy('project_id')->toArray();
- Cache::put(CacheConstant::NOW_MONTH_INQUIRE_CACHE_KEY, $lastListReportList, mt_rand(54000, 57600));
- }
- $bqLastMonthListReportList = Cache::get(CacheConstant::NOW_MONTH_INQUIRE_CACHE_KEY);
- $listReportMapResult = [];
- $listReportMapResult1 = [];
- foreach ($bqLastListReportList as $inx => $val) {
- $lastOne = $bqLastMonthListReportList[$inx] ?? null;
- if (empty($lastOne)) {
- continue;
- }
- $lessVal = $val->inquire - $lastOne->inquire;
- if ($lessVal > 0) {
- $listReportMapResult[$inx] = $lessVal;
- } else {
- $listReportMapResult1[$inx] = abs($lessVal);
- }
- }
- if (!Cache::has(CacheConstant::NOW_MONTH_FLOW_CACHE_KEY)) {
- $nowtMonthFlow = DB::connection('rank')->table('webmaster_effect')->selectRaw('SUM(clicks) as clicks,project_id')
- ->where(['ym' => date('Ym', strtotime('first day of -1 month'))])
- ->groupBy('project_id')->get()->keyBy('project_id')->toArray();
- Cache::put(CacheConstant::NOW_MONTH_FLOW_CACHE_KEY, $nowtMonthFlow, mt_rand(54000, 57600));
- }
- $nowtMonthFlow = Cache::get(CacheConstant::NOW_MONTH_FLOW_CACHE_KEY);
- if (!Cache::has(CacheConstant::LAST_MONTH_BQ_FLOW_CACHE_KEY)) {
- $bqLastClicksList = [];
- foreach ($projectCondition as $value) {
- $lastClicksList = DB::connection('rank')
- ->table('webmaster_effect')
- ->selectRaw('SUM(clicks) as clicks,project_id')
- ->where(['ym' => date('Ym', strtotime($value['bq_at'])), 'project_id' => $value['old_id']])
- ->groupBy('project_id')
- ->get()->keyBy('project_id')->toArray();
- foreach ($lastClicksList as $key => $item) {
- $bqLastClicksList[$key] = $item;
- }
- }
- Cache::put(CacheConstant::LAST_MONTH_BQ_FLOW_CACHE_KEY, $bqLastClicksList, mt_rand(54000, 57600));
- }
- $bqLastClicksList = Cache::get(CacheConstant::LAST_MONTH_BQ_FLOW_CACHE_KEY);
- $clicksDropResults = [];
- $clicksDropResults1 = [];
- foreach ($bqLastClicksList as $inx => $val) {
- $lastOne = $nowtMonthFlow[$inx] ?? null;
- if (empty($lastOne)) {
- continue;
- }
- $lessVal = $val->clicks - $lastOne->clicks;
- if ($lessVal > 0) {
- $clicksDropResults[$inx] = $lessVal;
- } else {
- $clicksDropResults1[$inx] = abs($lessVal);
- }
- }
- return [$items, $nowtMonthKeywords, $bqLastMonthListReportList, $listReportMapResult, $listReportMapResult1, $keywordDropResults, $keywordDropResults1, $clicksDropResults, $clicksDropResults1, $sites, $sortName, $sortOrder];
- }
- public function planExport()
- {
- $list = [];
- list($items, $nowtMonthKeywords, $nowListReportMap, $listReportMapResult, $listReportMapResult1, $keywordDropResults, $keywordDropResults1, $clicksDropResults, $clicksDropResults1, $sites, $sortName, $sortOrder) = $this->planBuilder(true);
- foreach ($items as $key => $site) {
- $sellerTitle = [];//销售
- $serverTitle = [];//客服
- $managersTitle = [];//项目经理1
- $manageHelperTitle = [];//项目经理
- $aeTitle = [];//采编
- $optimizerTitle = [];//优化师
- $bidTitle = [];//竞价
- $articleTitle = [];//软文
- $designerTitle = [];//设计
- $webTitle = [];//前端
- $userList = $site['users'];
- foreach ($userList as $kk => $value) {
- if ($value['role_id'] == Role::TYPE_SELLER) {
- $sellerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_SERVER) {
- $serverTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGER) {
- $managersTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
- $manageHelperTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_AE) {
- $aeTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
- $optimizerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_BID) {
- $bidTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
- $articleTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_DESIGNER) {
- $designerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_WEB) {
- $webTitle [] = $value['nickname'];
- }
- }
- $list[$key]['cn_title'] = $site['cn_title'];
- $list[$key]['domain'] = $site['domain'];
- $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
- $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
- $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
- $list[$key]['bq_score'] = $site['bq_score'];//百千客户评分
- $list[$key]['traffic'] = $nowListReportMap[$site['old_id']]->traffic ?? '';
- $list[$key]['top10'] = $nowtMonthKeywords[$site['old_id']] ?? '';
- $list[$key]['inquire'] = $nowListReportMap[$site['old_id']]->inquire ?? '';
- $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
- $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
- $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理1
- $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理
- $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
- $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
- $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
- $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
- $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
- $list[$key]['webTitle'] = implode('-', $webTitle);//前端
- $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
- $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
- $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
- $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
- $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
- $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
- }
- array_unshift($list, ['公司', '网站', '下单时间', '上线时间', '百千约访时间', '百千客户评分',
- '月流量', '月排名', '询盘', '项目销售', '客服经理', '项目总监', '项目经理', '优化师', '建站采编',
- '竞价', '软文', '设计', '前端', '合同签订时间', '分配时间', '达标时间', '过期时间', '续费次数', '续费时间']);
- return (new BasicExport($list))->download(sprintf('百千计划%s.xls', date('YmdHis')));
- }
- //引擎力案例
- public function example(Request $request)
- {
- if (!$request->ajax()) {
- $business = Business::query()->pluck('title', 'id');
- return view('admin/analyze/example', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'business' => $business
- ]);
- }
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $keyword = $request->input('keyword');
- $ids = [];
- $caseIds1 = $request->input('caseIds1');
- if ($caseIds1) {
- $ids[] = $caseIds1;
- }
- $caseIds2 = $request->input('caseIds2');
- if ($caseIds2) {
- $ids[] = $caseIds2;
- }
- $caseIds3 = $request->input('caseIds3');
- if ($caseIds3) {
- $ids[] = $caseIds3;
- }
- $caseIds4 = $request->input('caseIds4');
- if ($caseIds4) {
- $ids[] = $caseIds4;
- }
- $caseIds5 = $request->input('caseIds5');
- if ($caseIds5) {
- $ids[] = $caseIds5;
- }
- $caseIds6 = $request->input('caseIds6');
- if ($caseIds6) {
- $ids[] = $caseIds6;
- }
- $caseIds7 = $request->input('caseIds7');
- if ($caseIds7) {
- $ids[] = $caseIds7;
- }
- $businessId = $request->input('businessId');
- $builder = Site::query()->with(['users', 'business'])
- ->where('status', '!=', '7')
- ->where(['is_case' => 1])->where(function (Builder $q) use ($keyword, $ids, $businessId) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- if ($ids) {
- $q->where('case_ids', 'like', '%' . implode(',', $ids) . '%');
- }
- if ($businessId) {
- $q->where('business_id', $businessId);
- }
- });
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- array_walk($items, function ($item) use (&$top10ListMap, &$listReportMap) {
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->businessTitle = $item->business->title ?? "";
- $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联';
- $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
- $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';
- $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;
- $item->order_at = substr($item->order_at, 0, 10);
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
- $item->case = $this->getCaseList($item->case_ids);
- $item->example_url = $item->example_url ?? '';
- });
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- public function getCaseList($caseIds)
- {
- $result = '';
- if (!empty($caseIds)) {
- $list = [
- '21' => '结构化数据呈现',
- '22' => '关键词分析',
- '23' => '软文优化',
- '24' => '维基百科',
- '25' => '知识图谱',
- '26' => '竞价',
- '27' => '付费外链',
- ];
- $caseIds = explode(',', $caseIds);
- $result = [];
- foreach ($caseIds as $id) {
- $result[] = $list[$id];
- }
- $result = implode('<br>', $result);
- }
- return $result;
- }
- //上市
- public function listed(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/listed', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- ]);
- }
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users', 'business'])
- ->where('status', '!=', '7')
- ->whereNotNull('enterprise_nature')->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- array_walk($items, function ($item) use (&$top10ListMap, &$listReportMap) {
- if (!empty($item->enterprise_nature)) {
- $list = explode(',', $item->enterprise_nature);
- foreach ($list as $value) {
- if ($value == 1) {
- $item->the500Strongest = 1;
- }
- if ($value == 2) {
- $item->listedCompany = 1;
- }
- if ($value == 3) {
- $item->stateEnterprises = 1;
- }
- if ($value == 4) {
- $item->leadingEnterprise = 1;
- }
- }
- }
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->businessTitle = $item->business->title ?? "";
- $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联';
- $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
- $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';
- $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
- });
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- //流量异常项目
- public function abnormal(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/abnormal', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- ]);
- }
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users'])->whereIn('status', [2, 3, 5])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- // $condition='select *,SUM(CASE WHEN user_id in (3,5) then 1 ELSE 0 end) as total from user_has_sites where site_id=4 HAVING total=2';
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- $rankConnection = DB::connection('rank');
- $lastOneClicks = $rankConnection->table('webmaster_effect')->selectRaw('SUM(clicks) as clicks,project_id')
- ->whereIn('project_id', $oldIds)->where(['ym' => date('Ym', strtotime('first day of -1 month'))])
- ->groupBy('project_id')->get()->keyBy('project_id')->toArray();
- $lastTwoClicks = $rankConnection->table('webmaster_effect')->selectRaw('SUM(clicks) as clicks,project_id')
- ->whereIn('project_id', $oldIds)->where(['ym' => date('Ym', strtotime('first day of -2 month'))])
- ->groupBy('project_id')->get()->keyBy('project_id')->toArray();
- $clicksDropResults = [];
- foreach ($lastTwoClicks as $inx => $val) {
- $lastOne = $lastOneClicks[$inx] ?? null;
- if (empty($lastOne)) {
- continue;
- }
- $lessVal = $val->clicks - $lastOne->clicks;
- if ($lessVal >= 50) {
- $clicksDropResults[$inx] = $lessVal;
- }
- }
- $result = [];
- foreach ($items as $item) {
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
- $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';
- $item->clicksDropResults = $clicksDropResults[$item->old_id] ?? '';
- if (empty($item->clicksDropResults)) {
- continue;
- }
- $result[] = $item;
- }
- return response()->json([
- 'rows' => $result,
- 'total' => $sites->total()
- ]);
- }
- //关键词骤降项目
- public function abnormal2(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/abnormal2', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- ]);
- }
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users'])->whereIn('status', [2, 3, 5])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- // $condition='select *,SUM(CASE WHEN user_id in (3,5) then 1 ELSE 0 end) as total from user_has_sites where site_id=4 HAVING total=2';
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $rankConnection = DB::connection('rank');
- //关键词排名急剧下降
- $nowMonthKeywords = $rankConnection->table('project_history')->selectRaw('max(create_time),top10,project_id')->where([
- ['create_time', '>', strtotime(date('Y-m-01'))],
- ['create_time', '<', strtotime(date('Y-m-t 23:59:59'))],
- ])->groupBy('project_id')->pluck('top10', 'project_id')->toArray();
- $lastMonthKeywords = $rankConnection->table('project_history')->selectRaw('max(create_time),top10,project_id')->where([
- ['create_time', '>', strtotime(date('Y-m-01', strtotime('-1 month')))],
- ['create_time', '<', strtotime(date('Y-m-t 23:59:59', strtotime('-1 month')))],
- ])->groupBy('project_id')->pluck('top10', 'project_id')->toArray();
- $keywordDropResults = [];
- foreach ($lastMonthKeywords as $inx => $val) {
- $nowTop10 = $nowMonthKeywords[$inx] ?? 0;
- $lessVal = $val - $nowTop10;
- if ($lessVal >= 10) {
- // $dropResults[$inx] = ['project_id' => $inx, 'nowTop10' => $nowTop10, 'lastTop10' => $val];
- $keywordDropResults[$inx] = $lessVal;
- }
- }
- $result = [];
- foreach ($items as $item) {
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
- $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';
- $item->keywordDropResults = $keywordDropResults[$item->old_id] ?? '';
- if (empty($item->keywordDropResults)) {
- continue;
- }
- $result[] = $item;
- }
- return response()->json([
- 'rows' => $result,
- 'total' => $sites->total()
- ]);
- }
- //关键词异常项目
- public function abnormal3(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/abnormal3', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- ]);
- }
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users'])->whereIn('status', [2, 3, 5])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- // $condition='select *,SUM(CASE WHEN user_id in (3,5) then 1 ELSE 0 end) as total from user_has_sites where site_id=4 HAVING total=2';
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- $result = [];
- foreach ($items as $item) {
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->renewal_at = $item->expired_at ? date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at))) : null;
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->is_renewal = empty($item->renewal_at) ? '否' : '是';
- $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';
- if ($item->status == 3 && $item->reach_at && !empty($top10ListMap[$item->old_id])) {
- if ($top10ListMap[$item->old_id]->top10 < $item->keyword_goal) {
- $item->top10NoReach = $item->keyword_goal - $top10ListMap[$item->old_id]->top10;
- }
- }
- if (empty($item->top10NoReach)) {
- continue;
- }
- $result[] = $item;
- }
- return response()->json([
- 'rows' => $result,
- 'total' => $sites->total()
- ]);
- }
- //总监控
- public function monitor(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/monitor', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'sitesStatus' => Site::STATUS_MAP
- ]);
- }
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- DB::connection()->enableQueryLog();
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users' => function (\Illuminate\Database\Eloquent\Relations\BelongsToMany $q) {
- $q->select(['id', 'username', 'nickname', 'role_id']);
- }, 'linkDetails' => function (\Illuminate\Database\Eloquent\Relations\HasMany $q) {
- // $q->selectRaw('COUNT(DISTINCT(link_id)) as linkPactCount')->where(['enable' => 1, 'link_tasks_detail.status' => 5]);
- $q->select(['id', 'redundant_site_id', 'link_id'])->where(['enable' => 1, 'link_tasks_detail.status' => 5]);
- }])->withCount([
- 'articles as noSyncArticle' => function (Builder $b) {
- $b->whereNull('sync_at');
- }, 'articles as syncArticle' => function (Builder $b) {
- $b->whereNotNull('sync_at')->whereNull('publish_at');
- }, 'articles as publishArticle' => function (Builder $b) {
- $b->whereNotNull('publish_at');
- },
- 'linkUrls as linkUrls' => function (Builder $query) {
- $query->where('link_tasks_detail.status', 5)
- // ->where('link_tasks_detail.enable', 1)
- ->where('link_tasks_url.status', 5);
- }])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- $siteStatus = $request->input('siteStatus');
- if ($siteStatus) {
- $builder->where('status', $siteStatus);
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- // $condition='select *,SUM(CASE WHEN user_id in (3,5) then 1 ELSE 0 end) as total from user_has_sites where site_id=4 HAVING total=2';
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $articleFinishStatus = $request->input('articleFinishStatus');
- $linkFinishStatus = $request->input('linkFinishStatus');
- $result = collect([]);
- foreach ($items as $item) {
- //发布完成:软文指标小于软文发布量
- if ($articleFinishStatus == 1) {
- if ($item->article_goal > $item->publishArticle || !$item->article_goal) {
- continue;
- }
- }
- //查找完成:软文指标小于待更新软文数
- if ($articleFinishStatus == 2) {
- if ($item->article_goal > $item->noSyncArticle || !$item->article_goal) {
- continue;
- }
- }
- //未完成:软文指标大于软文发布量
- if ($articleFinishStatus == 3) {
- if ($item->article_goal <= $item->publishArticle) {
- continue;
- }
- }
- $item->linkPactCount = $item->linkDetails->groupBy('link_id')->count();
- //未完成:外链指标大于外联完成量
- if ($linkFinishStatus == 1) {
- if ($item->link_goal <= $item->linkPactCount || !$item->link_goal) {
- continue;
- }
- }
- //已完成:外链指标小于外联完成量
- if ($linkFinishStatus == 2) {
- if ($item->link_goal > $item->linkPactCount || !$item->link_goal) {
- continue;
- }
- }
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->expect_renewal_at = substr(($item->expired_at ?
- date('Y-m-d', strtotime('-3 month', strtotime($item->expired_at)))
- : null), 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';
- $result->prepend($item);
- }
- return response()->json([
- 'rows' => array_values($result->toArray()),
- 'total' => count($items)
- ]);
- }
- //软文监控
- public function monitorArticle(Request $request)
- {
- if (!$request->ajax()) {
- $list = Site::query()
- ->whereIn('status', [2, 3, 5])
- ->where('article_pause', 0)//软文没有暂停的
- ->where('article_goal', '!=', 0)//软文指标不等于0
- ->select('id', 'domain', 'articles_updates_per_month', 'article_renewal_quantity')
- ->withCount([
- 'articles as publishThisMonth' => function (Builder $b) {
- $b->whereBetween('publish_at', [date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59')]);
- }
- ])->get();
- $articlesUpdatesPerMonth = 0;
- $publishThisMonth = 0;
- foreach ($list as $item) {
- $articlesUpdatesPerMonth += $item->articles_updates_per_month ?? 0;
- $publishThisMonth += $item->publishThisMonth ?? 0;
- $notPublish = Article::query()->where('site_id', $item->id)
- ->whereNull('publish_at')
- ->where('be_applicable', 1)
- ->count() ?? 0;
- //中文文章 数据公式:【未发布数(即无发布时间的文章数量)-(每月待更新软文数-当月发布量)】/每月待更新软文数
- $a = ($notPublish - ($item->articles_updates_per_month - $item->publishThisMonth));
- $b = 0;
- if (!empty($item->articles_updates_per_month)) {
- $b = round($a / $item->articles_updates_per_month, 2);
- }
- $item->chineseArticles = $b;
- }
- $sites = 0;
- foreach ($list as $item) {
- if ($item->chineseArticles <= 2) {
- $sites++;
- }
- }
- return view('admin/analyze/monitor_article', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'optimizerEditing' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
- 'articleEditing' => Role::getUsers(Role::TYPE_ARTICLE),
- 'sitesStatus' => Site::STATUS_MAP,
- 'notUpdated' => $articlesUpdatesPerMonth - $publishThisMonth,
- 'sites' => $sites,
- ]);
- }
- $filterUserIds = [];
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $optimizerEditingId = $request->input('optimizerEditingId');
- $optimizerEditingId && $filterUserIds[] = $optimizerEditingId;
- $articleEditingId = $request->input('articleEditingId');
- $articleEditingId && $filterUserIds[] = $articleEditingId;
- $keyword = $request->input('keyword');
- $builder = Site::query()
- ->whereIn('status', [2, 3, 5])
- ->where('article_pause', 0)//软文没有暂停的
- ->where('article_goal', '!=', 0)//软文指标不等于0
- ->select('id', 'domain', 'cn_title', 'status', 'expired_at', 'article_goal', 'articles_updates_per_month', 'article_pause', 'article_renewal_quantity')
- ->with(['users' => function (\Illuminate\Database\Eloquent\Relations\BelongsToMany $q) {
- $q->select(['id', 'username', 'nickname', 'role_id']);
- }])->withCount([
- 'articles as publishThisMonth' => function (Builder $b) {
- $b->whereBetween('publish_at', [date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59')]);
- },
- 'articles as fit' => function (Builder $b) {
- //适用
- $b->where('be_applicable', 1);
- },
- 'articles as undetermined' => function (Builder $b) {
- //待定
- $b->where('be_applicable', 0);
- },
- 'articles as pendingUpgrade' => function (Builder $b) {
- //待更新
- $b->where('be_applicable', 1)
- ->whereNull('publish_at');
- }
- ])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- $siteStatus = $request->input('siteStatus');
- if ($siteStatus) {
- $builder->where('status', $siteStatus);
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- $sites = $builder->orderByDesc('id')->get();
- foreach ($sites as $item) {
- $updateMonth = Article::query()->where('site_id', $item->id)
- ->whereNotNull('publish_at')
- ->orderBy('publish_at', 'desc')
- ->value('publish_at');
- if (!empty($updateMonth)) {
- $updateMonth = date('Y-m-d', strtotime($updateMonth));
- $updateMonthTime = strtotime($updateMonth);
- } else {
- $updateMonth = '';
- $updateMonthTime = 0;
- }
- $item->updateMonth = $updateMonth;
- $item->updateMonthTime = $updateMonthTime;
- if ($item->spec_article == 1) {
- $item->spec_article = '是';
- }
- /*$item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());*/
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->articleEditing = implode('-', $item->users->where('role_id', Role::TYPE_ARTICLE)->pluck('nickname')->toArray());
- $item->optimizerEditing = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray());
- $item->order_at = substr($item->order_at, 0, 10);
- $item->statusTitle = Site::STATUS_MAP[$item->status] ?? '';
- $notPublish = Article::query()->where('site_id', $item->id)
- ->whereNull('publish_at')
- ->where('be_applicable', 1)
- ->count() ?? 0;
- //中文文章 数据公式:【未发布数(即无发布时间的文章数量)-(每月待更新软文数-当月发布量)】/每月待更新软文数
- $a = ($notPublish - ($item->articles_updates_per_month - $item->publishThisMonth));
- $b = 0;
- if (!empty($item->articles_updates_per_month)) {
- $b = round($a / $item->articles_updates_per_month, 2);
- }
- $item->chineseArticles = $b;
- }
- //中文完成情况 中文文章下数字≥2
- $chineseCompletion = $request->input('chinese_completion');
- if ($chineseCompletion == 1) {
- foreach ($sites as $key => $item) {
- if ($item->chineseArticles < 2) {
- unset($sites[$key]);
- }
- }
- }
- //不足2月库存: 2>中文文章下数字≥1
- if ($chineseCompletion == 2) {
- foreach ($sites as $key => $item) {
- if ($item->chineseArticles < 1 || $item->chineseArticles >= 2) {
- unset($sites[$key]);
- }
- }
- }
- //不足1月库存:1>中文文章下数字≥0
- if ($chineseCompletion == 3) {
- foreach ($sites as $key => $item) {
- if ($item->chineseArticles < 0 || $item->chineseArticles >= 1) {
- unset($sites[$key]);
- }
- }
- }
- //无库存:适用文章为0
- if ($chineseCompletion == 4) {
- foreach ($sites as $key => $item) {
- if ($item->fit != 0) {
- unset($sites[$key]);
- }
- }
- }
- //中文未反馈:待定 >0,本月更新<每月更新数,且待更新=0
- $array = [];
- if ($chineseCompletion == 5) {
- foreach ($sites as $key => $item) {
- if ($item->undetermined > 0 && $item->publishThisMonth < $item->articles_updates_per_month && $item->pendingUpgrade == 0) {
- $array[] = $item;
- }
- }
- $sites = $array;
- }
- //当月完成情况、完成
- $isComplete = $request->input('is_complete');
- if ($isComplete == 1) {
- foreach ($sites as $key => $item) {
- if ($item->publishThisMonth < $item->articles_updates_per_month) {
- unset($sites[$key]);
- }
- }
- }
- //当月完成情况、未完成
- if ($isComplete == 2) {
- foreach ($sites as $key => $item) {
- if ($item->publishThisMonth >= $item->articles_updates_per_month) {
- unset($sites[$key]);
- }
- }
- }
- //当月完成情况、当月发布量0
- if ($isComplete == 3) {
- foreach ($sites as $key => $item) {
- if ($item->publishThisMonth != 0) {
- unset($sites[$key]);
- }
- }
- }
- $sortName = $request->input('sortName') ?? 'id';
- $sortOrder = $request->input('sortOrder');
- if ($sortName == 'updateMonth') {
- $sortName = 'updateMonthTime';
- }
- if ($sortOrder == 'asc') {
- $res = collect($sites)->sortBy($sortName);
- } else {
- $res = collect($sites)->sortByDesc($sortName);
- }
- $result = $this->paginateCollection($res, $request->input('pageSize'));
- return response()->json([
- 'rows' => $result->items(),
- 'total' => $result->total(),
- ]);
- }
- //外链监控
- public function monitorLink(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/monitor_link', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- 'optimizerEditing' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
- 'chongqingLink' => Role::getUsers(Role::TYPE_LINK_PART_CHONGQING),
- 'sitesStatus' => Site::STATUS_MAP
- ]);
- }
- $builder = LinkStatistical::query();
- $optimizerId = $request->input('optimizerId');
- if (!empty($optimizerId)) {
- $builder->whereRaw('FIND_IN_SET(?,optimizer_people_id)', [$optimizerId]);
- }
- $optimizerEditingId = $request->input('optimizerEditingId');
- if (!empty($optimizerEditingId)) {
- $builder->whereRaw('FIND_IN_SET(?,optimizer_editing_people_id)', [$optimizerEditingId]);
- }
- $serverId = $request->input('serverId');
- if (!empty($serverId)) {
- $builder->whereRaw('FIND_IN_SET(?,server_people_id)', [$serverId]);
- }
- $chongqingLinkId = $request->input('chongqingLinkId');
- if (!empty($chongqingLinkId)) {
- $builder->whereRaw('FIND_IN_SET(?,chongqing_link_people_id)', [$chongqingLinkId]);
- }
- $keyword = $request->input('keyword');
- if (!empty($keyword)) {
- $builder->where('cn_title', 'like', '%' . $keyword . '%');
- }
- $siteStatus = $request->input('siteStatus');
- if (!empty($siteStatus)) {
- $builder->where('site_status', $siteStatus);
- }
- $completionLastMonth = $request->input('completionLastMonth');
- if ($completionLastMonth == 1) {
- $builder->where('publish_last_month', '=', 0);
- }
- if ($completionLastMonth == 2) {
- $builder->whereBetween('publish_last_month', [1, 49]);
- }
- if ($completionLastMonth == 3) {
- $builder->whereBetween('publish_last_month', [50, 69]);
- }
- if ($completionLastMonth == 4) {
- $builder->where('publish_last_month', '>', 70);
- }
- $completionThisMonth = $request->input('completionThisMonth');
- if ($completionThisMonth == 1) {
- $builder->where('publish_this_month', '=', 0);
- }
- if ($completionThisMonth == 2) {
- $builder->whereBetween('publish_this_month', [1, 49]);
- }
- if ($completionThisMonth == 3) {
- $builder->whereBetween('publish_this_month', [50, 69]);
- }
- if ($completionThisMonth == 4) {
- $builder->where('publish_this_month', '>', 70);
- }
- $sortName = $request->input('sortName') ?? 'id';
- $sortOrder = $request->input('sortOrder') ?? 'desc';
- $sites = $builder->orderBy($sortName, $sortOrder)->paginate($request->input('pageSize'));
- $userList = User::query()->where('status', 1)->pluck('nickname', 'id');
- foreach ($sites as $key => $item) {
- $optimizerEditingList = '';
- if (!empty($item->optimizer_editing_people_id)) {
- $optimizerEditing = explode(',', $item->optimizer_editing_people_id);
- foreach ($optimizerEditing as $value) {
- $optimizerEditingList .= $userList[$value] ?? '';
- }
- }
- $item->optimizerEditing = $optimizerEditingList;
- $chongqingLinkList = '';
- if (!empty($item->chongqing_link_people_id)) {
- $chongqingLinks = explode(',', $item->chongqing_link_people_id);
- foreach ($chongqingLinks as $value) {
- $chongqingLinkList .= $userList[$value] ?? '';
- }
- }
- $item->chongqingLink = $chongqingLinkList;
- $serverList = '';
- if (!empty($item->server_people_id)) {
- $servers = explode(',', $item->server_people_id);
- foreach ($servers as $value) {
- $serverList .= $userList[$value] ?? '';
- }
- }
- $item->server = $serverList;
- $optimizerList = '';
- if (!empty($item->optimizer_people_id)) {
- $optimizers = explode(',', $item->optimizer_people_id);
- foreach ($optimizers as $value) {
- $optimizerList .= $userList[$value] ?? '';
- }
- }
- $item->optimizer = $optimizerList;
- $item->site_status = Site::STATUS_MAP[$item->site_status] ?? '';
- }
- return response()->json([
- 'rows' => $sites->items(),
- 'total' => $sites->total(),
- ]);
- }
- //合同档案
- public function archive(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/archive');
- }
- $builder = $this->archiveBuilder();
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- foreach ($items as $item) {
- $item->areaText = implode(',', $item->area_text);
- $item->status = Site::STATUS_MAP[$item->status];
- $item->contract_total_fee = $item->sitePayment->contract_total_fee ?? '';
- $item->head = $item->sitePayment->head ?? '';
- $item->done = $item->sitePayment->done ?? '';
- $item->reach = $item->sitePayment->reach ?? '';
- $item->service_final_payment = $item->sitePayment->service_final_payment ?? '';
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->optimizerAe = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->webTitle = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- protected function archiveBuilder()
- {
- $request = \request();
- $keyword = $request->input('keyword');
- $address = $request->input('address');
- $status = $request->input('status');
- $builder = Site::query()->with(['users', 'sitePayment'])->where(function (Builder $q) use ($keyword, $status, $address) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- if ($status) {
- $q->where('status', $status);
- }
- if ($address) {
- $address = json_encode($address);
- $address = (trim($address, '"'));
- $address = str_replace('\\', '%' . '\\', $address);
- $q->where('area_text', 'like', '%' . $address . '%');
- }
- });
- return $builder;
- }
- //合同档案导出
- public function archiveExport(Request $request)
- {
- $builder = $this->archiveBuilder();
- $items = $builder->orderByDesc('id')->get();
- $list = [];
- foreach ($items as $item) {
- $data = [
- 'areaText' => implode(',', $item->area_text),
- 'cn_title' => $item->cn_title,
- 'status' => Site::STATUS_MAP[$item->status],
- 'domain' => $item->domain,
- 'keyword_goal' => $item->keyword_goal,
- 'article_goal' => $item->article_goal,
- 'link_goal' => $item->link_goal,
- 'sign_at' => $item->sign_at,
- 'contract_total_fee' => $item->sitePayment->contract_total_fee ?? '',
- 'head' => $item->sitePayment->head ?? '',
- 'done' => $item->sitePayment->done ?? '',
- 'reach' => $item->sitePayment->reach ?? '',
- 'service_final_payment' => $item->sitePayment->service_final_payment ?? '',
- 'renewal_times' => $item->renewal_times ?? '',
- 'renewal_amount' => $item->renewal_amount ?? '',
- 'remark' => $item->remark ?? '',
- 'seller_title' => implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray()),
- 'server_title' => implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray()),
- 'managers_title' => implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray()),
- 'manage_helper_title' => implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray()),
- 'optimizerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray()),
- 'optimizerAe' => implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->pluck('nickname')->toArray()),
- 'ae_title' => implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray()),
- 'bidTitle' => implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray()),
- 'articleTitle' => implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray()),
- 'designerTitle' => implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray()),
- 'webTitle' => implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray()),
- ];
- $list[] = $data;
- };
- array_unshift($list, ['地区', '公司', '项目状态', '网站', '关键词指标', '文章指标', '外链指标', '合同日期', '合同金额', '首付款', '建站金额', '达标金额', '服务期尾款', '续费次数', '续费金额', '备注', '项目销售', '客服经理', '项目经理1', '项目经理', '优化师', '优化采编', '项目采编', '竞价', '软文', '设计', '前端']);
- return (new BasicExport($list))->download(sprintf('合同档案%s.xls', date('YmdHis')));
- }
- //已终止
- public function abort(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/abort', [
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- ]);
- }
- $builder = $this->abortBuilder();
- $sites = $builder->orderByDesc('expired_at')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $oldIds = array_filter(array_column($items, 'old_id'));
- [$top10ListMap, $listReportMap] = $this->getInquiryAndFlowList($oldIds);
- foreach ($items as $item) {
- $item->status = Site::STATUS_MAP[$item->status];
- $item->top10 = $item->old_id ? ($top10ListMap[$item->old_id]->top10 ?? '-') : '未关联'; //实际达标关键词
- $item->traffic = $item->old_id ? ($listReportMap[$item->old_id]->traffic ?? '-') : '未关联';
- $item->inquire = $item->old_id ? ($listReportMap[$item->old_id]->inquire ?? '-') : '未关联';
- $item->order_at = substr($item->order_at, 0, 10);
- $item->online_at = substr($item->online_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- $item->refundReasonAnalyze = self::REFUND_REASON_LIST[$item->sitePayment->refund_reason_analyze];
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- protected function abortBuilder()
- {
- $request = \request();
- $monthRange = $request->input('monthRange');
- $keyword = $request->input('keyword');
- $status = $request->input('status');
- $build = Site::query();
- $refundReasonAnalyze = $request->input('refund_reason_analyze');
- if (!empty($refundReasonAnalyze)) {
- $siteIds = SitePayment::query()
- ->where('refund_reason_analyze', $refundReasonAnalyze)
- ->pluck('site_id');
- $build->whereIn('id', $siteIds);
- }
- $filterUserIds = [];
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- if ($filterUserIds) {
- $build->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- $builder = $build->with(['sitePayment'])->where(function (Builder $q) use ($keyword, $status) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- if ($status) {
- $q->where('status', $status);
- }
- })->where(['status' => 7]);
- if ($monthRange) {
- $startDate = sprintf("%s-01 00:00:00", $monthRange);
- $builder->where('expired_at', '>=', $startDate)
- ->where('expired_at', '<=', sprintf("%s-%s 23:59:59", $monthRange, date('t', strtotime($startDate))));
- }
- return $builder;
- }
- //客服任务
- public function customerServiceTask(Request $request)
- {
- if (!$request->ajax()) {
- $projectSteward = User::query()
- ->where('status', 1)
- ->where('role_id', 7)
- ->pluck('nickname', 'id');
- return view('admin/analyze/customer_service_task', [
- 'projectSteward' => $projectSteward
- ]);
- }
- $task = DB::table('tasks');
- $builder = Site::query();
- $status = $request->input('status');
- if (!empty($status)) {
- $builder->where('status', $status);
- }
- $keyword = $request->input('keyword');
- if (!empty($keyword)) {
- $siteIds = $builder->where('cn_title', 'like', '%' . $keyword . '%')->pluck('id');
- $task->whereIn('site_id', $siteIds);
- } else {
- $siteIds = $builder->pluck('id')->toArray();
- $task->whereIn('site_id', $siteIds);
- }
- $projectStewardId = $request->input('projectStewardId');
- if (!empty($projectStewardId)) {
- $siteIds = DB::table('user_has_sites')->where('user_id', $projectStewardId)->pluck('site_id');
- $task->whereIn('site_id', $siteIds);
- }
- $page = $request->input('page') ?? 1;
- $size = $request->input('size') ?? 10;
- $taskList = $task->get();
- foreach ($taskList as $key => $value) {
- $taskList[$key]->dataList = \GuzzleHttp\json_decode($value->data, true);
- }
- $list = [];
- foreach ($taskList as $key => $value) {
- foreach ($value->dataList['dataList'] as $kk => $vv) {
- $date = '';
- if (!empty($vv['flow_except_date'])) {
- $date = date('Y-m-d', strtotime($vv['flow_except_date']));
- }
- $date1 = '';
- if (!empty($vv['initial_date'])) {
- $date1 = date('Y-m-d', strtotime($vv['initial_date']));
- }
- $info = [
- 'siteId' => $value->site_id ?? 0,
- 'taskId' => $vv['task_id'] ?? '',
- 'statusId' => $value->status ?? 0,
- 'status' => $value->status ?? 0,
- 'title' => $vv['title'] ?? '',
- 'data' => $vv['data'] ?? '',
- 'fulfil' => $vv['fulfil'] ?? '',
- 'flow_except_date' => $date,
- 'initial_date' => $date1,
- 'personnel' => $vv['personnel'] ?? 0,
- ];
- $list[] = $info;
- }
- }
- $fulfil = $request->input('fulfil');
- if (!empty($fulfil)) {
- foreach ($list as $key => $value) {
- if ($fulfil != $value['fulfil']) {
- unset($list[$key]);
- }
- }
- }
- $list = $this->array_sort($list, 'fulfil', 'asc');
- $listList = DB::table('sites')->whereNull('deleted_at')->pluck('cn_title', 'id')->toArray() ?? [];
- $userList = DB::table('users')->whereNull('deleted_at')->pluck('nickname', 'id')->toArray() ?? [];
- foreach ($list as $key => $value) {
- $list[$key]['siteName'] = $listList[$value['siteId']] ?? '';
- $list[$key]['user'] = $userList[$value['personnel']] ?? '';
- $list[$key]['fulfil'] = '未完成';
- if ($value['fulfil'] == 2) {
- $list[$key]['fulfil'] = '完成';
- }
- $list[$key]['status'] = Site::STATUS_MAP[$value['status']];
- }
- $count = count($list);
- $list = array_splice($list, $size * ($page - 1), $size);
- return response()->json([
- 'rows' => $list,
- 'total' => $count
- ]);
- }
- /**
- * 二维数组排序
- * @param array $arr 需要排序的二维数组
- * @param string $keys 所根据排序的key
- * @param string $type 排序类型,desc、asc
- * @return array $new_array 排好序的结果
- */
- function array_sort($arr, $keys, $type = 'desc')
- {
- $key_value = $new_array = array();
- foreach ($arr as $k => $v) {
- $key_value[$k] = $v[$keys];
- }
- if ($type == 'asc') {
- asort($key_value);
- } else {
- arsort($key_value);
- }
- reset($key_value);
- foreach ($key_value as $k => $v) {
- $new_array[$k] = $arr[$k];
- }
- return $new_array;
- }
- //客服任务保存\删除
- public function customerServiceTaskCarryOut($type, $siteId, $status, $taskId)
- {
- $taskInfo = DB::table('tasks')->where([['site_id', $siteId], ['status', $status]])->value('data');
- if (empty($taskInfo)) {
- return response()->json(['message' => '项目不存在'], 400);
- }
- $taskInfo = \GuzzleHttp\json_decode($taskInfo, true);
- if ($type == 1) {
- foreach ($taskInfo['dataList'] as $key => $value) {
- if ($taskId == $value['task_id']) {
- $taskInfo['dataList'][$key]['fulfil'] = 2;
- $taskInfo['dataList'][$key]['flow_except_date'] = date('Y-m-d');
- }
- }
- $updateData = [
- 'data' => \GuzzleHttp\json_encode($taskInfo),
- 'site_id' => $siteId,
- 'status' => $status
- ];
- } else {
- foreach ($taskInfo['dataList'] as $key => $value) {
- if ($taskId == $value['task_id']) {
- unset($taskInfo['dataList'][$key]);
- }
- }
- $updateData = [
- 'data' => \GuzzleHttp\json_encode($taskInfo),
- 'site_id' => $siteId,
- 'status' => $status,
- ];
- }
- $result = DB::table('tasks')->where([['site_id', $siteId], ['status', $status]])->update($updateData);
- if (empty($result)) {
- return response()->json(['message' => '操作失败'], 400);
- } else {
- return response()->json(['message' => '操作成功']);
- }
- }
- //测速
- public function speedMeasurement(Request $request)
- {
- if (!$request->ajax()) {
- return view('admin/analyze/speed_measurement', [
- 'webs' => Role::getUsers(Role::TYPE_WEB),
- 'services' => Role::getUsers(Role::TYPE_SERVER), //服务人员
- 'editors' => Role::getUsers(Role::TYPE_AE),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZER),
- ]);
- }
- $builder = $this->speedMeasurementBuilder();
- $sites = $builder->orderByDesc('id')->paginate($request->input('pageSize') ?? TABLE_PAGE_SIZE);
- $items = $sites->items();
- $pcSpeedMeasurement = [];
- $mobileSpeedMeasurement = [];
- $speedMeasurementList = DB::table('app_speed_measurement_cache')->get();
- foreach ($speedMeasurementList as $key => $value) {
- $array = json_decode($value->cache, true);
- if (!empty($array)) {
- $pcSpeedMeasurementResult = $array['pc'];
- $mobileSpeedMeasurementResult = $array['mobile'];
- $oldId = str_replace('cache:app/Http/Controllers/SpeedMeasurementController/index:', '', $value->key);
- $pcSpeedMeasurement[$oldId] = $pcSpeedMeasurementResult;
- $mobileSpeedMeasurement[$oldId] = $mobileSpeedMeasurementResult;
- }
- }
- foreach ($items as $item) {
- $item->identify = base64_encode($item->id);
- $item->managers_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGER)->pluck('nickname')->toArray());
- $item->server_title = implode('-', $item->users->where('role_id', Role::TYPE_SERVER)->pluck('nickname')->toArray());
- $item->seller_title = implode('-', $item->users->where('role_id', Role::TYPE_SELLER)->pluck('nickname')->toArray());
- $item->optimizerTitle = implode('-', $item->users->where('role_id', Role::TYPE_OPTIMIZER)->pluck('nickname')->toArray());
- $item->manage_helper_title = implode('-', $item->users->where('role_id', Role::TYPE_MANAGE_HELPER)->pluck('nickname')->toArray());
- $item->ae_title = implode('-', $item->users->where('role_id', Role::TYPE_AE)->pluck('nickname')->toArray());
- $item->web = implode('-', $item->users->where('role_id', Role::TYPE_WEB)->pluck('nickname')->toArray());
- $item->bidTitle = implode('-', $item->users->where('role_id', Role::TYPE_BID)->pluck('nickname')->toArray());
- $item->articleTitle = implode('-', $item->users->where('role_id', Role::TYPE_PROPAGANDA)->pluck('nickname')->toArray());
- $item->designerTitle = implode('-', $item->users->where('role_id', Role::TYPE_DESIGNER)->pluck('nickname')->toArray());
- $item->pcSpeedMeasurement = $pcSpeedMeasurement[$item->old_id] ?? '-';
- $item->mobileSpeedMeasurement = $mobileSpeedMeasurement[$item->old_id] ?? '-';
- $item->sign_at = substr($item->sign_at, 0, 10);
- $item->order_at = substr($item->order_at, 0, 10);
- $item->assign_at = substr($item->assign_at, 0, 10);
- $item->reach_at = substr($item->reach_at, 0, 10);
- $item->expired_at = substr($item->expired_at, 0, 10);
- $item->bq_at = substr($item->bq_at, 0, 10);
- $item->renewal_at = substr($item->renewal_at, 0, 10);
- if ($item->online_at && $item->assign_at) {
- $orderAt = date_create($item->assign_at);
- $onlineAt = date_create($item->online_at);
- $diff = date_diff($orderAt, $onlineAt);
- $item->online_days = $diff->days;
- } else {
- $item->online_days = null;
- }
- $item->online_at = substr($item->online_at, 0, 10);
- };
- return response()->json([
- 'rows' => $items,
- 'total' => $sites->total()
- ]);
- }
- public function speedMeasurementBuilder()
- {
- $request = \request();
- $filterUserIds = [];
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $managerId = $request->input('managerId');
- $managerId && $filterUserIds[] = $managerId;
- $serverId = $request->input('serverId');
- $serverId && $filterUserIds[] = $serverId;
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $optimizerId = $request->input('optimizerId');
- $optimizerId && $filterUserIds[] = $optimizerId;
- $keyword = $request->input('keyword');
- $builder = Site::query()->with(['users'])->where(function (Builder $q) use ($keyword) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- });
- $sortName = $request->input('sortName');
- $sortOrder = $request->input('sortOrder');
- if (!empty($sortName) || !empty($sortOrder)) {
- $builder->orderBy($sortName, $sortOrder);
- }
- if ($filterUserIds) {
- $builder->whereExists(function (\Illuminate\Database\Query\Builder $b) use ($filterUserIds) {
- $raw = sprintf('SUM(CASE WHEN user_id in (%s) then 1 ELSE 0 end) as total', implode(',', $filterUserIds));
- $b->select(DB::raw($raw))->from('user_has_sites')
- ->whereRaw(sprintf('user_has_sites.site_id=sites.id HAVING total=%s', count($filterUserIds)));
- });
- }
- return $builder;
- }
- public function speedMeasurementExport()
- {
- $builder = $this->speedMeasurementBuilder();
- $sites = $builder->orderByDesc('id')->get()->toArray();
- $list = [];
- $pcSpeedMeasurement = [];
- $mobileSpeedMeasurement = [];
- $speedMeasurementList = DB::table('app_speed_measurement_cache')->get();
- foreach ($speedMeasurementList as $key => $value) {
- $array = json_decode($value->cache, true);
- if (!empty($array)) {
- $pcSpeedMeasurementResult = $array['pc'];
- $mobileSpeedMeasurementResult = $array['mobile'];
- $oldId = str_replace('cache:app/Http/Controllers/SpeedMeasurementController/index:', '', $value->key);
- $pcSpeedMeasurement[$oldId] = $pcSpeedMeasurementResult;
- $mobileSpeedMeasurement[$oldId] = $mobileSpeedMeasurementResult;
- }
- }
- foreach ($sites as $key => $site) {
- $onlineDays = 0;
- if ($site['online_at'] && $site['assign_at']) {
- $orderAt = date_create($site['assign_at']);
- $onlineAt = date_create($site['online_at']);
- $diff = date_diff($orderAt, $onlineAt);
- $onlineDays = $diff->days;
- }
- $sellerTitle = [];//销售
- $serverTitle = [];//客服
- $managersTitle = [];//项目经理1
- $manageHelperTitle = [];//项目经理
- $aeTitle = [];//采编
- $optimizerTitle = [];//优化师
- $bidTitle = [];//竞价
- $articleTitle = [];//软文
- $designerTitle = [];//设计
- $webTitle = [];//前端
- $userList = $site['users'];
- foreach ($userList as $kk => $value) {
- if ($value['role_id'] == Role::TYPE_SELLER) {
- $sellerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_SERVER) {
- $serverTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGER) {
- $managersTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_MANAGE_HELPER) {
- $manageHelperTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_AE) {
- $aeTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_OPTIMIZER) {
- $optimizerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_BID) {
- $bidTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_PROPAGANDA) {
- $articleTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_DESIGNER) {
- $designerTitle [] = $value['nickname'];
- }
- if ($value['role_id'] == Role::TYPE_WEB) {
- $webTitle [] = $value['nickname'];
- }
- }
- $list[$key]['online_at'] = substr($site['online_at'], 0, 10);//上线时间
- $list[$key]['cn_title'] = $site['cn_title'];//公司
- $list[$key]['domain'] = $site['domain'];//网站
- $list[$key]['keyword_goal'] = $site['keyword_goal'];//关键词指标
- $list[$key]['online_days'] = $onlineDays;//建站时长
- $list[$key]['pcSpeedMeasurement'] = $pcSpeedMeasurement[$site['old_id']] ?? '';
- $list[$key]['mobileSpeedMeasurement'] = $mobileSpeedMeasurement[$site['old_id']] ?? '';
- $list[$key]['seller_title'] = implode('-', $sellerTitle);//销售
- $list[$key]['server_title'] = implode('-', $serverTitle);//客服经理
- $list[$key]['managers_title'] = implode('-', $managersTitle);//项目经理
- $list[$key]['manage_helper_title'] = implode('-', $manageHelperTitle);//项目经理1
- $list[$key]['optimizerTitle'] = implode('-', $optimizerTitle);//优化
- $list[$key]['ae_title'] = implode('-', $aeTitle);//采编
- $list[$key]['bidTitle'] = implode('-', $bidTitle);//竞价
- $list[$key]['articleTitle'] = implode('-', $articleTitle);//软文
- $list[$key]['designerTitle'] = implode('-', $designerTitle);//设计
- $list[$key]['webTitle'] = implode('-', $webTitle);//前端
- $list[$key]['sign_at'] = substr($site['sign_at'], 0, 10);//合同签订时间
- $list[$key]['order_at'] = substr($site['order_at'], 0, 10);//下单时间
- $list[$key]['assign_at'] = substr($site['assign_at'], 0, 10);//分配时间
- $list[$key]['reach_at'] = substr($site['reach_at'], 0, 10);//达标时间
- $list[$key]['expired_at'] = substr($site['expired_at'], 0, 10);//过期时间
- $list[$key]['bq_at'] = substr($site['bq_at'], 0, 10);//百千约访时间
- $list[$key]['renewal_times'] = $site['renewal_times'];//续费次数
- $list[$key]['renewal_at'] = substr($site['renewal_at'], 0, 10);//续费时间
- }
- array_unshift($list, ['上线时间', '公司', '网站', '关键词指标', '建站时长', 'pc端测速得分', '移动端测速得分', '项目销售', '客服经理', '项目经理1', '项目经理', '优化组长', '项目采编', '竞价', '软文', '设计', '前端', '合同签订时间', '下单时间', '分配时间', '达标时间', '过期时间', '百千约访时间', '续费次数', '续费时间']);
- return (new BasicExport($list))->download(sprintf('测速得分%s.xls', date('YmdHis')));
- }
- //查找任务管理
- public function findTaskManagement(Request $request)
- {
- if (!$request->ajax()) {
- $userList = User::query()->where(['status' => 1])->whereIn('role_id', [15, 7, 6, 25, 26, 2, 1])->select(['id', 'nickname', 'role_id'])->get();
- $businessList = Business::query()->pluck('title', 'id');
- return view('admin/site/index', [
- 'userList' => $userList,
- 'businessList' => $businessList,
- 'role_id' => 15,
- 'is_propaganda' => 1,//品宣只显示建站期和服务期
- ]);
- }
- }
- //项目进度
- public function projectProgress(Request $request)
- {
- if (!$request->ajax()) {
- $this->statisticsTask();
- return view('admin/analyze/project_progress');
- }
- $projectProgressStatistics = DB::table('project_progress_statistics');
- $count = DB::table('project_progress_statistics')->count();
- $keyword = $request->input('keyword');
- if (!empty($keyword)) {
- $siteIds = Db::table('sites')->where('cn_title', 'like', $keyword . '%')->pluck('id')->toArray();
- $projectProgressStatistics->whereIn('sites_id', [$siteIds]);
- }
- $sortName = $request->input('sortName');
- $sortOrder = $request->input('sortOrder');
- if (!empty($sortName) || !empty($sortOrder)) {
- $projectProgressStatistics->orderBy($sortName, $sortOrder);
- }
- $pageSize = $request->input('pageSize');
- $projectProgressStatisticsList = $projectProgressStatistics->paginate($pageSize);
- $list = $projectProgressStatisticsList->items();
- foreach ($list as $key => $value) {
- if ($value->is_project_data_collection == 1) {
- $list[$key]->is_project_data_collection = '完成';
- } else {
- $list[$key]->is_project_data_collection = '-';
- }
- if (!empty($value->project_data_collection_date)) {
- $list[$key]->project_data_collection_date = date('Y-m-d', strtotime($value->project_data_collection_date));
- }
- if ($value->is_site_architecture == 1) {
- $list[$key]->is_site_architecture = '完成';
- } else {
- $list[$key]->is_site_architecture = '-';
- }
- if (!empty($value->site_architecture_date)) {
- $list[$key]->site_architecture_date = date('Y-m-d', strtotime($value->site_architecture_date));
- }
- if ($value->is_homepage_design == 1) {
- $list[$key]->is_homepage_design = '完成';
- } else {
- $list[$key]->is_homepage_design = '-';
- }
- if (!empty($value->homepage_design_date)) {
- $list[$key]->homepage_design_date = date('Y-m-d', strtotime($value->homepage_design_date));
- }
- if ($value->is_inside_page_design == 1) {
- $list[$key]->is_inside_page_design = '完成';
- } else {
- $list[$key]->is_inside_page_design = '-';
- }
- if (!empty($value->inside_page_design_date)) {
- $list[$key]->inside_page_design_date = date('Y-m-d', strtotime($value->inside_page_design_date));
- }
- if ($value->is_test_station == 1) {
- $list[$key]->is_test_station = '完成';
- } else {
- $list[$key]->is_test_station = '-';
- }
- if (!empty($value->test_station_date)) {
- $list[$key]->test_station_date = date('Y-m-d', strtotime($value->test_station_date));
- }
- if ($value->is_key_words == 1) {
- $list[$key]->is_key_words = '完成';
- } else {
- $list[$key]->is_key_words = '-';
- }
- if (!empty($value->key_words_date)) {
- $list[$key]->key_words_date = date('Y-m-d', strtotime($value->key_words_date));
- }
- if ($value->is_seo_perfect == 1) {
- $list[$key]->is_seo_perfect = '完成';
- } else {
- $list[$key]->is_seo_perfect = '-';
- }
- if (!empty($value->seo_perfect_date)) {
- $list[$key]->seo_perfect_date = date('Y-m-d', strtotime($value->seo_perfect_date));
- }
- if ($value->is_online == 1) {
- $list[$key]->is_online = '完成';
- } else {
- $list[$key]->is_online = '-';
- }
- if (!empty($value->online_date)) {
- $list[$key]->online_date = date('Y-m-d', strtotime($value->online_date));
- }
- if ($value->is_entire_site_information_provision == 1) {
- $list[$key]->is_entire_site_information_provision = '完成';
- } else {
- $list[$key]->is_entire_site_information_provision = '-';
- }
- if (!empty($value->entire_site_information_provision_date)) {
- $list[$key]->entire_site_information_provision_date = date('Y-m-d', strtotime($value->entire_site_information_provision_date));
- }
- }
- return response()->json([
- 'rows' => $list,
- 'total' => count($list) ?? 0
- ]);
- }
- public function statisticsTask()
- {
- $siteList = Site::query()->with(['users' => function ($query) {
- $query->where('role_id', 25);
- }]
- )->where('status', 1)->select('id', 'cn_title')->get()->toArray();
- $flowStageList = DB::table('flow_stage as a')
- ->join('flow_info as b', 'a.id', '=', 'b.stage_id')
- ->select('a.id', 'a.title', 'a.site_id', 'b.detail_list')
- ->get()->toJson();
- $flowStageList = \GuzzleHttp\json_decode($flowStageList, true);
- foreach ($flowStageList as $key => $value) {
- $result = \GuzzleHttp\json_decode($value['detail_list'], true);
- krsort($result);
- $result = array_merge($result);
- $flowStageList[$key]['info'] = $result;
- }
- foreach ($siteList as $key => $v) {
- $siteList[$key]['list'] = [];
- foreach ($flowStageList as $kk => $vv) {
- if ($v['id'] == $vv['site_id']) {
- $siteList[$key]['list'][] = $vv;
- }
- }
- }
- $insertData = [];
- foreach ($siteList as $key => $value) {
- $updateData = [
- 'sites_id' => $value['id'] ?? '',//项目id
- 'project' => $value['cn_title'] ?? '',//项目名称
- 'project_manager' => $value['users'][0]['nickname'] ?? '',//项目经理
- 'project_data_collection_date' => null,
- 'is_project_data_collection' => 0,
- 'site_architecture_date' => null,
- 'is_site_architecture' => 0,
- 'homepage_design_date' => null,
- 'is_homepage_design' => 0,
- 'inside_page_design_date' => null,
- 'is_inside_page_design' => 0,
- 'test_station_date' => null,
- 'is_test_station' => 0,
- 'key_words_date' => null,
- 'is_key_words' => 0,
- 'seo_perfect_date' => null,
- 'is_seo_perfect' => 0,
- 'online_date' => null,
- 'is_online' => 0,
- 'entire_site_information_provision_date' => null,
- 'is_entire_site_information_provision' => 0,
- ];
- if (!empty($value['list'][0]['info'][0]['flow_done_date'])) {
- $projectDataCollectionDate = $value['list'][0]['info'][0]['flow_done_date'];
- $updateData['project_data_collection_date'] = $projectDataCollectionDate;
- $updateData['is_project_data_collection'] = 1;
- }
- if (!empty($value['list'][1]['info'][0]['flow_done_date'])) {
- $siteArchitectureDate = $value['list'][1]['info'][0]['flow_done_date'];
- $updateData['site_architecture_date'] = $siteArchitectureDate;
- $updateData['is_site_architecture'] = 1;
- }
- if (!empty($value['list'][2]['info'][0]['flow_done_date'])) {
- $homepageDesignDate = $value['list'][2]['info'][0]['flow_done_date'];
- $updateData['homepage_design_date'] = $homepageDesignDate;
- $updateData['is_homepage_design'] = 1;
- }
- if (!empty($value['list'][3]['info'][0]['flow_done_date'])) {
- $insidePageDesignDate = $value['list'][3]['info'][0]['flow_done_date'];
- $updateData['inside_page_design_date'] = $insidePageDesignDate;
- $updateData['is_inside_page_design'] = 1;
- }
- if (!empty($value['list'][4]['info'][0]['flow_done_date'])) {
- $testStationDate = $value['list'][4]['info'][0]['flow_done_date'];
- $updateData['test_station_date'] = $testStationDate;
- $updateData['is_test_station'] = 1;
- }
- if (!empty($value['list'][5]['info'][0]['flow_done_date'])) {
- $keyWordsDate = $value['list'][5]['info'][0]['flow_done_date'];
- $updateData['key_words_date'] = $keyWordsDate;
- $updateData['is_key_words'] = 1;
- }
- if (!empty($value['list'][6]['info'][0]['flow_done_date'])) {
- $seoPerfectDate = $value['list'][6]['info'][0]['flow_done_date'];
- $updateData['seo_perfect_date'] = $seoPerfectDate;
- $updateData['is_seo_perfect'] = 1;
- }
- if (!empty($value['list'][7]['info'][0]['flow_done_date'])) {
- $onlineDate = $value['list'][7]['info'][0]['flow_done_date'];
- $updateData['online_date'] = $onlineDate;
- $updateData['is_online'] = 1;
- }
- if (!empty($value['list'][8]['info'][0]['flow_done_date'])) {
- $entireSiteInformationProvisionDate = $value['list'][8]['info'][0]['flow_done_date'];
- $updateData['entire_site_information_provision_date'] = $entireSiteInformationProvisionDate;
- $updateData['is_entire_site_information_provision'] = 1;
- }
- $insertData[] = $updateData;
- }
- DB::table('project_progress_statistics')->delete();
- DB::table('project_progress_statistics')->insert($insertData);
- }
- //项目进度导出
- public function projectProgressExport()
- {
- $dataList[] = ['项目名称', '项目经理', '项目资料搜集-进度', '完成时间', '网站架构-进度', '完成时间', '首页设计-进度', '完成时间', '内页设计-进度', '完成时间', '测试站-进度', '完成时间', '关键词-进度', '完成时间', 'SEO完善-进度', '完成时间', '上线-进度', '完成时间', '整站资料提供-进度', '完成时间'];
- $list = DB::table('project_progress_statistics')
- ->select(
- 'project',
- 'project_manager',
- 'is_project_data_collection',
- 'project_data_collection_date',
- 'is_site_architecture',
- 'site_architecture_date',
- 'is_homepage_design',
- 'homepage_design_date',
- 'is_inside_page_design',
- 'inside_page_design_date',
- 'is_test_station',
- 'test_station_date',
- 'is_key_words',
- 'key_words_date',
- 'is_seo_perfect',
- 'seo_perfect_date',
- 'is_online',
- 'online_date',
- 'is_entire_site_information_provision',
- 'entire_site_information_provision_date'
- )->get()->toJson();
- $result = \GuzzleHttp\json_decode($list, true);
- foreach ($result as $key => $value) {
- if ($value['is_project_data_collection'] == 1) {
- $result[$key]['is_project_data_collection'] = '完成';
- } else {
- $result[$key]['is_project_data_collection'] = '-';
- }
- if ($value['is_site_architecture'] == 1) {
- $result[$key]['is_site_architecture'] = '完成';
- } else {
- $result[$key]['is_site_architecture'] = '-';
- }
- if ($value['is_homepage_design'] == 1) {
- $result[$key]['is_homepage_design'] = '完成';
- } else {
- $result[$key]['is_homepage_design'] = '-';
- }
- if ($value['is_inside_page_design'] == 1) {
- $result[$key]['is_inside_page_design'] = '完成';
- } else {
- $result[$key]['is_inside_page_design'] = '-';
- }
- if ($value['is_test_station'] == 1) {
- $result[$key]['is_test_station'] = '完成';
- } else {
- $result[$key]['is_test_station'] = '-';
- }
- if ($value['is_key_words'] == 1) {
- $result[$key]['is_key_words'] = '完成';
- } else {
- $result[$key]['is_key_words'] = '-';
- }
- if ($value['is_seo_perfect'] == 1) {
- $result[$key]['is_seo_perfect'] = '完成';
- } else {
- $result[$key]['is_seo_perfect'] = '-';
- }
- if ($value['is_online'] == 1) {
- $result[$key]['is_online'] = '完成';
- } else {
- $result[$key]['is_online'] = '-';
- }
- if ($value['is_entire_site_information_provision'] == 1) {
- $result[$key]['is_entire_site_information_provision'] = '完成';
- } else {
- $result[$key]['is_entire_site_information_provision'] = '-';
- }
- }
- foreach ($result as $key => $value) {
- $dataList[] = $value;
- }
- return (new BasicExport($dataList))->download(sprintf('项目进度统计%s.xls', date('YmdHis')));
- }
- public function statisticsInquiry()
- {
- set_time_limit(0);
- $siteList = Site::query()->whereIn('status', [2, 3])->get();
- DB::table('statistics_inquiry')->delete();
- foreach ($siteList as $siteInfo) {
- try {
- $config = [
- 'connection_name' => sprintf('connection_name_%s', $siteInfo->id),
- 'host' => $siteInfo->server->server_ip,
- 'port' => '3306',
- 'database' => $siteInfo->database,
- 'username' => $siteInfo->server->mysql_user_name,
- 'password' => $siteInfo->server->mysql_passwd,
- ];
- config_connection($config);
- $list = DB::connection($config['connection_name'])->table('user_msg')
- ->selectRaw('FROM_UNIXTIME(create_time, "%Y-%m") as date, count(*) as count')
- ->groupBy('date')
- ->get()->toArray() ?? [];
- if (!empty($list)) {
- $createTime = DB::connection($config['connection_name'])->table('user_msg')
- ->orderBy('id', 'asc')
- ->value('create_time');
- $lastTime = DB::connection($config['connection_name'])->table('user_msg')
- ->orderBy('id', 'desc')
- ->value('create_time');
- if (!empty($createTime) && !empty($lastTime)) {
- $dateList = $this->getDateList($createTime, $lastTime);
- foreach ($dateList as $key => $value) {
- $dateList[$key]['count'] = 0;
- foreach ($list as $kkk => $vvv) {
- if (strtotime($value['date']) == strtotime($vvv->date)) {
- $dateList[$key]['count'] = $vvv->count;
- }
- }
- $num = 0;
- foreach ($list as $kk => $vv) {
- if (strtotime($value['date']) >= strtotime($vv->date)) {
- $num += $vv->count;
- }
- }
- $dateList[$key]['sum'] = $num;
- }
- //重置数组中的key
- $dateList = array_merge($dateList);
- $data = [
- 'site_id' => $siteInfo->id,
- 'cn_title' => $siteInfo->cn_title,
- 'old_id' => $siteInfo->old_id,
- 'data' => \GuzzleHttp\json_encode($dateList),
- 'last_time' => date('Y-m-d H:i:s', $lastTime),
- ];
- DB::table('statistics_inquiry')->insert($data);
- }
- }
- } catch (\Throwable $exception) {
- pre_dump($exception->getMessage());
- }
- }
- }
- public function getDateList($startTime, $lastTime)
- {
- $startTime = strtotime(date("Y-m", strtotime("-1 months", $startTime)));
- $endTime = strtotime(date('Y-m', $lastTime));
- $dateList = [];
- $i = 0;
- while ($startTime < $endTime) {
- $startTime = strtotime('+1 Month', $startTime);
- $i++;
- $dateList[$i]['date'] = date('Y-m', $startTime);
- }
- return $dateList;
- }
- public function sendMessage(Request $request)
- {
- $siteId = $request->input('siteId');
- $mobile = $request->input('mobile');
- if (empty($mobile)) {
- return response()->json(['message' => '请填写手机号'], 400);
- }
- if (!preg_match("/^1[3456789]\d{9}$/", $mobile)) {
- return response()->json(['message' => '手机号输入有误'], 400);
- }
- $siteInfo = Site::query()->where('id', $siteId)->first();
- if (empty($siteInfo)) {
- return response()->json(['message' => '项目不存在'], 400);
- }
- //5分钟以内发重复的code
- $time = strtotime(date('Y-m-d H:i:s')) - strtotime($siteInfo->create_time_code);
- if (!empty($siteInfo->create_time_code) && $time > 300) {
- $code = mt_rand(1000, 9999);
- } else {
- $code = $siteInfo->code;
- }
- if (empty($siteInfo->code)) {
- $code = mt_rand(1000, 9999);
- }
- try {
- $url = 'http://translate.api.yinqingli.net/openapi/Msg/Msg';
- $data = [
- 'mobile' => $mobile,
- 'tpl' => 'SMS_223585128',
- 'TemplateParam' => [
- 'code' => $code,
- ],
- ];
- $client = new Client();
- $response = $client->post($url, [
- 'form_params' => $data,
- ]);
- $result = $response->getBody()->getContents();//没返回
- $update = [
- 'code' => $data['TemplateParam']['code'],
- 'create_time_code' => date('Y-m-d H:i:s'),
- 'code_num' => ++$siteInfo->code_num
- ];
- Site::query()->where('id', $siteId)->update($update);
- return response()->json(['message' => '操作成功']);
- } catch (\Throwable $exception) {
- return response()->json(['message' => '服务器未知错误'], 400);
- }
- }
- }
|