123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279328032813282328332843285328632873288328932903291329232933294329532963297329832993300330133023303330433053306330733083309331033113312331333143315331633173318331933203321332233233324332533263327332833293330333133323333333433353336333733383339334033413342334333443345334633473348334933503351335233533354335533563357335833593360336133623363336433653366336733683369337033713372337333743375337633773378337933803381338233833384338533863387338833893390339133923393339433953396339733983399340034013402340334043405340634073408340934103411341234133414341534163417341834193420342134223423342434253426342734283429343034313432343334343435343634373438343934403441344234433444344534463447344834493450345134523453345434553456345734583459346034613462346334643465346634673468346934703471347234733474347534763477347834793480348134823483348434853486348734883489349034913492349334943495349634973498349935003501350235033504350535063507350835093510351135123513351435153516351735183519352035213522352335243525352635273528352935303531353235333534353535363537353835393540354135423543354435453546354735483549355035513552355335543555355635573558355935603561356235633564356535663567356835693570357135723573357435753576357735783579358035813582358335843585358635873588358935903591359235933594359535963597359835993600360136023603360436053606360736083609361036113612361336143615361636173618361936203621362236233624362536263627362836293630363136323633363436353636363736383639364036413642364336443645364636473648364936503651365236533654365536563657365836593660366136623663366436653666366736683669367036713672367336743675367636773678367936803681368236833684368536863687368836893690369136923693369436953696369736983699370037013702370337043705370637073708370937103711371237133714371537163717371837193720372137223723372437253726372737283729373037313732373337343735373637373738373937403741374237433744374537463747374837493750375137523753375437553756375737583759376037613762376337643765376637673768376937703771377237733774377537763777377837793780378137823783378437853786378737883789379037913792379337943795379637973798379938003801380238033804380538063807380838093810381138123813381438153816381738183819382038213822382338243825382638273828382938303831383238333834383538363837383838393840384138423843384438453846384738483849385038513852385338543855385638573858385938603861386238633864386538663867386838693870387138723873387438753876387738783879388038813882388338843885388638873888388938903891389238933894389538963897389838993900390139023903390439053906390739083909391039113912391339143915391639173918391939203921392239233924392539263927392839293930393139323933393439353936393739383939394039413942394339443945394639473948394939503951395239533954395539563957395839593960396139623963396439653966396739683969397039713972397339743975397639773978397939803981398239833984398539863987398839893990399139923993399439953996399739983999400040014002400340044005400640074008400940104011401240134014401540164017401840194020402140224023402440254026402740284029403040314032403340344035403640374038403940404041404240434044404540464047404840494050405140524053405440554056405740584059406040614062406340644065406640674068406940704071407240734074407540764077407840794080408140824083408440854086408740884089409040914092409340944095409640974098409941004101410241034104410541064107410841094110411141124113411441154116411741184119412041214122412341244125412641274128412941304131413241334134413541364137413841394140414141424143414441454146414741484149415041514152415341544155415641574158415941604161416241634164416541664167416841694170417141724173417441754176417741784179418041814182418341844185418641874188418941904191419241934194419541964197419841994200420142024203420442054206420742084209421042114212421342144215421642174218421942204221422242234224422542264227422842294230423142324233423442354236423742384239424042414242424342444245424642474248424942504251425242534254425542564257425842594260426142624263426442654266426742684269427042714272427342744275427642774278427942804281428242834284428542864287428842894290429142924293429442954296429742984299430043014302430343044305430643074308430943104311431243134314431543164317431843194320432143224323432443254326432743284329433043314332433343344335433643374338433943404341434243434344434543464347434843494350435143524353435443554356435743584359436043614362436343644365436643674368436943704371437243734374437543764377437843794380438143824383438443854386438743884389439043914392439343944395439643974398439944004401440244034404440544064407440844094410441144124413441444154416441744184419442044214422442344244425442644274428442944304431443244334434443544364437443844394440444144424443444444454446444744484449445044514452445344544455445644574458445944604461446244634464446544664467446844694470447144724473447444754476447744784479448044814482448344844485448644874488448944904491449244934494449544964497449844994500450145024503450445054506450745084509451045114512451345144515451645174518451945204521452245234524452545264527452845294530453145324533453445354536453745384539454045414542454345444545454645474548454945504551455245534554455545564557 |
- <?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',[
- 'optimizers' => Role::getUsers(Role::TYPE_OPTIMIZATION_EDITING),
- 'managers' => Role::getUsers(Role::TYPE_MANAGER),
- 'managerHelpers' => Role::getUsers(Role::TYPE_MANAGE_HELPER),
- 'sellerUsers' => Role::getUsers(Role::TYPE_SELLER),
- ]);
- }
- $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();
- $filterUserIds = [];
- $keyword = $request->input('keyword');
- $pay_way = $request->input('pay_way');
- $address = $request->input('address');
- $status = $request->input('status');
- $sellerId = $request->input('sellerId');
- $sellerId && $filterUserIds[] = $sellerId;
- $editorId = $request->input('editorId');
- $editorId && $filterUserIds[] = $editorId;
- $manageHelperId = $request->input('manageHelperId');
- $manageHelperId && $filterUserIds[] = $manageHelperId;
- $signAt = $request->input('sign_at');
- $builder = Site::query()->with(['users', 'sitePayment'])->where(function (Builder $q) use ($keyword, $status, $address,$pay_way) {
- if ($keyword) {
- $q->where('domain', 'like', '%' . $keyword . '%')
- ->orWhere('cn_title', 'like', '%' . $keyword . '%');
- }
- if ($status) {
- $q->where('status', $status);
- }
- if ($pay_way) {
- $q->where('pay_way', $pay_way);
- }
- if ($address) {
- $address = json_encode($address);
- $address = (trim($address, '"'));
- $address = str_replace('\\', '%' . '\\', $address);
- $q->where('area_text', 'like', '%' . $address . '%');
- }
- });
- if ($signAt) {
- $begin = $signAt.' 00:00:00';
- $end_time =$signAt.' 23:59:59';
- $builder->whereBetween('sign_at', [$begin, $end_time]);
- }
- 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 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);
- }
- }
- }
|