12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201 |
- <?php
- namespace addons\qingdongams\controller;
- use addons\qingdongams\model\Achievement;
- use addons\qingdongams\model\AreaManagement;
- use addons\qingdongams\model\ConsumeDetail;
- use addons\qingdongams\model\ContractRatio;
- use addons\qingdongams\model\Field;
- use addons\qingdongams\model\FormApproval;
- use addons\qingdongams\model\PartsStockReload;
- use addons\qingdongams\model\Receivables;
- use addons\qingdongams\model\Contract;
- use addons\qingdongams\model\Event;
- use addons\qingdongams\model\Leads;
- use addons\qingdongams\model\Customer;
- use addons\qingdongams\model\ReceivablesPlan;
- use addons\qingdongams\model\Record;
- use addons\qingdongams\model\SeaOperation;
- use addons\qingdongams\model\Staff;
- use addons\qingdongams\model\Workorder;
- use addons\qingdongams\model\StaffSignIn;
- use addons\qingdongams\model\Product;
- use addons\qingdongams\model\Business;
- use think\Exception;
- /**
- * 统计接口
- */
- class Statistics extends StaffApi {
- protected $noNeedLogin = [];
- protected $noNeedRight = [];
- public function _initialize()
- {
- parent::_initialize();
- try{
- \think\Db::execute("SET @@sql_mode='';");
- }catch (Exception $e){
- }
- }
- /**
- *成交排行
- */
- public function contractRanding() {
- $date = input('date', date('Y-m'));
- $type = input('type', 0);//0 本人及下属 1 仅本人 2 仅下属 4公司
- //月底
- $endDate = date('Y-m-d', strtotime('+1 month', strtotime(date($date . '-1'))) - 1);
- $contracts = Contract::where([
- 'order_date' => ['between', [$date . '-1', $endDate]],
- 'check_status' => 2,
- ])->group('owner_staff_id')->field('owner_staff_id,sum(money) as money')->order('money desc')->select();
- $list = [];
- foreach ($contracts as $v) {
- $list[$v['owner_staff_id']] = $v['money'];
- }
- $contracts = $list;
- $data = [];
- $staffs = Staff::getList();
- foreach ($staffs as $v) {
- if (isset($contracts[$v['id']])) {
- $data[$v['id']] = $contracts[$v['id']];
- } else {
- $data[$v['id']] = 0;
- }
- }
- arsort($data);
- $staffs = Staff::getKeyList();
- $result = [];
- $i = 1;
- $oneMoney = 0;
- if ($type == 1) {//本人
- $showStaffIds = [$this->auth->id];
- } elseif ($type == 2) {//下属
- $showStaffIds = Staff::getLowerStaffId();
- } elseif ($type == 3) {//公司
- $showStaffIds = Staff::getLowerStaffId();
- } else {//全部
- $showStaffIds = Staff::getMyStaffIds();
- }
- foreach ($data as $id => $money) {
- if ($i == 1) {
- $oneMoney = $money;
- }
- $val = $staffs[$id];
- $val['money'] = $money;
- $val['ratio'] = ($money && $oneMoney) ? sprintf("%.2f", $money / $oneMoney * 100) : 0;
- $val['rank'] = $i;
- $i++;
- if(in_array($id,$showStaffIds)){
- $result[] = $val;
- }
- }
- if (count($result) >= 10) {
- $top = array_slice($result, 0, 3);
- $bottom = array_slice($result, -3, 3);
- $middle = array_slice($result, 3, 4);
- $result = array_merge($top, $bottom, $middle);
- }
- if ($type == 1) {//本人
- $showStaffIds = [$this->auth->id];
- //业绩目标完成情况
- $achievement = Achievement::getStaffAchievement($date);
- } elseif ($type == 2) {//下属
- $showStaffIds = Staff::getLowerStaffId();
- //业绩目标完成情况
- $achievement = Achievement::getTeamAchievement($date);
- } elseif ($type == 3) {//公司
- $showStaffIds = Staff::getLowerStaffId();
- //业绩目标完成情况
- $achievement = Achievement::getCompanyAchievement($date);
- } else {//全部
- $showStaffIds = Staff::getMyStaffIds();
- //业绩目标完成情况
- $achievement = Achievement::getTeamAchievement($date);
- }
- $contractMoneys = Contract::where([
- 'order_date' => ['between', [$date . '-1', $endDate]],
- 'check_status' => 2,
- 'owner_staff_id' => ['in',$showStaffIds],
- ])->sum('money');
- $ratio = ($contractMoneys && intval($achievement)) ? round($contractMoneys / $achievement * 100, 2) : 0;
- $data = [
- 'achievement' => $achievement,
- 'contract_moneys' => $contractMoneys,
- 'completion_status' => ($ratio >= 100) ? 1 : 0,//完成状态
- 'ratio' => $ratio,
- 'ranking' => $result
- ];
- $this->success('请求成功', $data);
- }
- //合同排行 机型排行
- public function contractRanking() {
- $date = input('date', date('Y-m'));
- $type = input('type', 0);//0 本人及下属 1 仅本人 2 仅下属
- //月底
- $endDate = date('Y-m-d', strtotime('+1 month', strtotime($date . '-1')) - 1);
- $contracts = Contract::where([
- 'order_date' => ['between', [$date . '-1', $endDate]],
- 'check_status' => 2,
- ])->group('owner_staff_id')->field('owner_staff_id,count(*) as contract_number')->order('contract_number desc')->select();
- $list = [];
- foreach ($contracts as $v) {
- $list[$v['owner_staff_id']] = $v['contract_number'];
- }
- $contracts = $list;
- $data = [];
- $staffs = Staff::getList();
- foreach ($staffs as $v) {
- if (isset($contracts[$v['id']])) {
- $data[$v['id']] = $contracts[$v['id']];
- } else {
- $data[$v['id']] = 0;
- }
- }
- arsort($data);
- $staffs = Staff::getKeyList();
- $result = [];
- $i = 1;
- $oneNumber = 0;
- if ($type == 1) {//本人
- $showStaffIds = [$this->auth->id];
- } elseif ($type == 2) {//下属
- $showStaffIds = Staff::getLowerStaffId();
- } else {//全部
- $showStaffIds = Staff::getMyStaffIds();
- }
- foreach ($data as $id => $number) {
- if ($i == 1) {
- $oneNumber = $number;
- }
- $val = $staffs[$id];
- $val['number'] = $number;
- $val['ratio'] = $oneNumber ? sprintf("%.2f", $number / $oneNumber * 100) : 0;
- $val['rank'] = $i;
- $i++;
- if(in_array($id,$showStaffIds)){
- $result[] = $val;
- }
- }
- if (count($result) >= 10) {
- $top = array_slice($result, 0, 3);
- $bottom = array_slice($result, -3, 3);
- $middle = array_slice($result, 3, 4);
- $result = array_merge($top, $bottom, $middle);
- }
- $this->success('请求成功', $result);
- }
- //回款统计
- public function receivablesStatistics() {
- $contract_moneys = Contract::where(['owner_staff_id' => $this->auth->id, 'check_status' => 2])->sum('money');
- $receivables_moneys = Receivables::where([
- 'owner_staff_id' => $this->auth->id,
- 'check_status' => 2,
- ])->sum('money');
- $plan_moneys = ReceivablesPlan::where([
- 'owner_staff_id' => $this->auth->id,
- 'status' => 0,
- ])->sum('money');
- $no_moneys = $contract_moneys - $receivables_moneys;
- $startDate = date('Y-m', strtotime($this->auth->createtime));
- $endDate = date('Y-m');
- $plans = ReceivablesPlan::where([
- 'owner_staff_id' => $this->auth->id,
- ])->group('dates')->field('id,sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_date),"%Y-%m") as dates')->select();
- $plans_list = [];
- foreach ($plans as $item) {
- $plans_list[$item['dates']] = $item['money'];
- }
- $receivables = Receivables::where([
- 'owner_staff_id' => $this->auth->id,
- ])->group('dates')->field('id,sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_time),"%Y-%m") as dates')->select();
- $receivables_list = [];
- foreach ($receivables as $item) {
- $receivables_list[$item['dates']] = $item['money'];
- }
- $data = [];
- for ($startDate; strtotime($startDate) <= strtotime($endDate); $startDate = date('Y-m', strtotime($startDate . ' +1 month'))) {
- $row = [
- 'date' => $startDate,
- 'plan' => $plans_list[$startDate] ?? 0,
- 'receivables' => $receivables_list[$startDate] ?? 0,
- ];
- $row['no'] = ($row['plan'] - $row['receivables']) > 0 ? $row['plan'] - $row['receivables'] : 0;
- $data[] = $row;
- }
- $this->success('请求成功', [
- 'contract_moneys' => $contract_moneys,
- 'receivables_moneys' => $receivables_moneys,
- 'plan_moneys' => $plan_moneys,
- 'no_moneys' => $no_moneys,
- 'data' => $data
- ]);
- }
- //所属员工回款统计
- public function staffReceivablesStatistics() {
- $ids = Staff::getMyStaffIds();
- $contract_moneys = Contract::where([
- 'owner_staff_id' => ['in', $ids],
- 'check_status' => 2,
- ])->sum('money');
- $receivables_moneys = Receivables::where([
- 'owner_staff_id' => ['in', $ids],
- 'check_status' => 2,
- ])->sum('money');
- $plan_moneys = ReceivablesPlan::where([
- 'owner_staff_id' => ['in', $ids],
- 'status' => 0,
- ])->sum('money');
- $no_moneys = $contract_moneys - $receivables_moneys;
- //$startDate = date('Y-m', $this->auth->createtime);
- //$endDate = date('Y-m');
- $contracts = Contract::where([
- 'owner_staff_id' => [
- 'in',
- $ids
- ],
- 'check_status' => 2,
- ])->group('owner_staff_id')->field('id,sum(money) as money,owner_staff_id')->select();
- $contracts_list = [];
- foreach ($contracts as $item) {
- $contracts_list[$item['owner_staff_id']] = $item['money'];
- }
- $plans = ReceivablesPlan::where([
- 'owner_staff_id' => [
- 'in',
- $ids
- ],
- 'status' => 0,
- ])->group('owner_staff_id')->field('id,sum(money) as money,owner_staff_id')->select();
- $plans_list = [];
- foreach ($plans as $item) {
- $plans_list[$item['owner_staff_id']] = $item['money'];
- }
- $receivables = Receivables::where([
- 'owner_staff_id' => [
- 'in',
- $ids
- ],
- 'check_status' => 2,
- ])->group('owner_staff_id')->field('id,sum(money) as money,owner_staff_id')->select();
- $receivables_list = [];
- foreach ($receivables as $item) {
- $receivables_list[$item['owner_staff_id']] = $item['money'];
- }
- $data = [];
- $ids = Staff::getMyStaffIds();
- $staffs = Staff::where(['id' => ['in', $ids],'status'=>1])->field('id,name')->select();
- foreach ($staffs as $v) {
- $row = [
- 'id' => $v['id'],
- 'name' => $v['name'],
- 'contracts' => $contracts_list[$v['id']] ?? 0,
- 'plan' => $plans_list[$v['id']] ?? 0,
- 'receivables' => $receivables_list[$v['id']] ?? 0,
- ];
- $row['no'] = ($row['contracts'] - $row['receivables']) > 0 ? $row['contracts'] - $row['receivables'] : 0;
- $data[] = $row;
- }
- $this->success('请求成功', [
- 'contract_moneys' => $contract_moneys,
- 'receivables_moneys' => $receivables_moneys,
- 'plan_moneys' => $plan_moneys,
- 'no_moneys' => $no_moneys,
- 'data' => $data
- ]);
- }
- //我的业绩目标完成度
- public function staffAchievementStatistics() {
- $year = input('year', date('Y'));
- $type = input('type', 0);
- if ($type == 1) {//本人
- $showStaffIds = [$this->auth->id];
- $yearAchievement = Achievement::getStaffYearAchievement($year);
- } elseif ($type == 2) {//下属
- $showStaffIds = Staff::getLowerStaffId();
- $yearAchievement = Achievement::getTeamYearAchievement($year);
- } elseif ($type == 3) {//公司
- $showStaffIds = Staff::getLowerStaffId();
- $yearAchievement = Achievement::getCompanyYearAchievement($year);
- } else {//全部
- $showStaffIds = Staff::getMyStaffIds();
- $yearAchievement = Achievement::getTeamYearAchievement($year);
- }
- $contracts = Contract::where([
- 'owner_staff_id' => ['in',$showStaffIds],
- 'check_status' => 2,
- 'order_date' => ['like', $year . '%'],
- ])->group('month')->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(order_date),"%Y-%m") as month,count(*) as count')->select();
- $contracts_list = [];
- foreach ($contracts as $v) {
- $contracts_list[$v['month']] = $v;
- }
- $data = [];
- foreach ($yearAchievement as $k => $v) {
- if ($month = Achievement::getFieldMonth($k)) {
- $month = $year . '-' . $month;
- $row = [
- 'month' => $month,
- 'achievement' => $v,
- 'money' => isset($contracts_list[$month]) ? $contracts_list[$month]['money'] : 0,
- 'count' => isset($contracts_list[$month]) ? $contracts_list[$month]['count'] : 0,
- ];
- $row['ratio'] = ($row['money'] && intval($row['achievement'])) ? sprintf("%.2f", $row['money'] / $row['achievement'] * 100) : 0;
- $row['unit_price'] = ($row['money'] && $row['count']) ? sprintf("%.2f", $row['money'] / $row['count']) : 0;
- $data[] = $row;
- }
- }
- $this->success('请求成功', $data);
- }
- //回款数据排行
- public function receivablesRanking() {
- $year = input('year', date('Y'));
- $type = input('type', 0);
- if ($type == 1) {//本人
- $showStaffIds = [$this->auth->id];
- } elseif ($type == 2) {//下属
- $showStaffIds = Staff::getLowerStaffId();
- } else {//全部
- $showStaffIds = Staff::getMyStaffIds();
- }
- $receivables = Receivables::where([
- 'check_status' => 2,
- 'return_time' => ['like', $year . '%']
- ])->group('owner_staff_id')->with(['staff'])->order('money desc')->field('owner_staff_id,sum(money) as money,count(*) as count')->select();
- $receivablesData = [];
- $list = [];
- foreach ($receivables as $k => $v) {
- $v['ranking'] = $k + 1;
- if(in_array($v['owner_staff_id'],$showStaffIds)){
- $receivablesData[] = $v;
- }
- $list[$v['owner_staff_id']] = $v['money'];
- }
- $contracts = $list;
- $data = [];
- $staffs = Staff::getList();
- foreach ($staffs as $v) {
- if (isset($contracts[$v['id']])) {
- $data[$v['id']] = $contracts[$v['id']];
- } else {
- $data[$v['id']] = 0;
- }
- }
- arsort($data);
- $staffs = Staff::getKeyList();
- $result = [];
- $i = 1;
- $oneNumber = 0;
- foreach ($data as $id => $number) {
- if ($i == 1) {
- $oneNumber = $number;
- }
- $val = $staffs[$id];
- $val['number'] = $number;
- $val['ratio'] = $oneNumber ? sprintf("%.2f", $number / $oneNumber * 100) : 0;
- $val['rank'] = $i;
- $i++;
- if(in_array($id,$showStaffIds)){
- $result[] = $val;
- }
- }
- if (count($result) >= 10) {
- $top = array_slice($result, 0, 3);
- $bottom = array_slice($result, -3, 3);
- $middle = array_slice($result, 3, 4);
- $result = array_merge($top, $bottom, $middle);
- }
- $this->success('请求成功', ['ranking' => $result, 'data' => $receivablesData]);
- }
- //成交数据排行
- public function contractMoneyRanking() {
- $date = input('year', date('Y'));
- $type = input('type', 0);
- if ($type == 1) {//本人
- $showStaffIds = [$this->auth->id];
- } elseif ($type == 2) {//下属
- $showStaffIds = Staff::getLowerStaffId();
- } else {//全部
- $showStaffIds = Staff::getMyStaffIds();
- }
- $receivables = Contract::where([
- 'check_status' => 2,
- 'order_date' => ['like', $date . '%']
- ])->group('owner_staff_id')->with(['staff'])->order('money desc')->field('owner_staff_id,sum(money) as money,count(*) as count')->select();
- $receivablesData = [];
- $list = [];
- foreach ($receivables as $k => $v) {
- $v['ranking'] = $k + 1;
- $v['unit_price'] = ($v['money'] && $v['count']) ? sprintf("%.2f", $v['money'] / $v['count']) : 0;
- if(in_array($v['owner_staff_id'],$showStaffIds)){
- $receivablesData[] = $v;
- }
- $list[$v['owner_staff_id']] = $v['money'];
- }
- $contracts = $list;
- $data = [];
- $staffs = Staff::getList();
- foreach ($staffs as $v) {
- if (isset($contracts[$v['id']])) {
- $data[$v['id']] = $contracts[$v['id']];
- } else {
- $data[$v['id']] = 0;
- }
- }
- arsort($data);
- $staffs = Staff::getKeyList();
- $result = [];
- $i = 1;
- $oneNumber = 0;
- foreach ($data as $id => $number) {
- if ($i == 1) {
- $oneNumber = $number;
- }
- $val = $staffs[$id];
- $val['number'] = $number;
- $val['ratio'] = $oneNumber ? sprintf("%.2f", $number / $oneNumber * 100) : 0;
- $val['rank'] = $i;
- $i++;
- if(in_array($id,$showStaffIds)){
- $result[] = $val;
- }
- }
- if (count($result) >= 10) {
- $top = array_slice($result, 0, 3);
- $bottom = array_slice($result, -3, 3);
- $middle = array_slice($result, 3, 4);
- $result = array_merge($top, $bottom, $middle);
- }
- $this->success('请求成功', ['ranking' => $result, 'data' => $receivablesData]);
- }
- //新增排行
- public function newRanking() {
- $year = input('date', date('Y'));
- $between = [strtotime($year . '-01-01'), strtotime($year.'-1-1 +1 year')];
- $betweenC = [date('Y-m-d 00:00:00',strtotime($year . '-01-01')), date('Y-m-d 23:59:59',strtotime($year.'-1-1 +1 year'))];
- $customers = Customer::where([
- 'createtime' => [
- 'between',
- $between
- ],
- 'create_staff_id' => $this->auth->id
- ])->group('month')->field("id,FROM_UNIXTIME(createtime,'%Y-%m') as month,count(*) as count")->select();
- $customers_list = [];
- foreach ($customers as $v) {
- $customers_list[$v['month']] = $v['count'];
- }
- $contracts = Contract::where([
- 'order_date' => [
- 'between',
- $betweenC
- ],
- 'create_staff_id' => $this->auth->id
- ])->group('month')->field('id,FROM_UNIXTIME(createtime,"%Y-%m") as month,count(*) as count')->select();
- $contracts_list = [];
- foreach ($contracts as $v) {
- $contracts_list[$v['month']] = $v['count'];
- }
- $leads = Leads::where([
- 'createtime' => [
- 'between',
- $between
- ],
- 'create_staff_id' => $this->auth->id
- ])->group('month')->field('id,FROM_UNIXTIME(createtime,"%Y-%m") as month,count(*) as count')->select();
- $leads_list = [];
- foreach ($leads as $v) {
- $leads_list[$v['month']] = $v['count'];
- }
- $data = [];
- for ($i = 1; $i <= 12; $i++) {
- $month = date('Y-m', strtotime($year . '-' . $i));
- $data[] = [
- 'month' => $month,
- 'customers' => $customers_list[$month] ?? 0,
- 'contracts' => $contracts_list[$month] ?? 0,
- 'leads' => $leads_list[$month] ?? 0,
- ];
- }
- $this->success('请求成功', $data);
- }
- //获取团队新增统计
- public function addCustomerStatistics() {
- $date = input('date', date('Y-m'));
- if(strlen($date) == 4){
- $between = [strtotime($date . '-01-01'), strtotime($date.'-1-1 +1 year') - 1];
- $betweenC = [date('Y-m-d 00:00:00',strtotime($date . '-01-01')), date('Y-m-d 23:59:59',strtotime($date.'-1-1 +1 year') - 1)];
- }else{
- $between = [strtotime($date), strtotime('+1 month', strtotime($date)) - 1];
- $betweenC = [date('Y-m-d 00:00:00',strtotime($date)), date('Y-m-d 23:59:59',strtotime('+1 month', strtotime($date)) - 1)];
- }
- $ids = Staff::getMyStaffIds();
- $staffs = Staff::where(['id' => ['in', $ids]])->field('id,name,post,img')->select();
- $c = Customer::where([
- 'createtime' => [
- 'between',
- $between
- ],
- 'create_staff_id' => ['in', $ids]
- ])->group('create_staff_id')->order('count desc')->field("id,create_staff_id,count(*) as count")->select();
- $customers = [];
- foreach ($c as $v) {
- $customers[$v['create_staff_id']] = $v['count'];
- }
- $l = Leads::where([
- 'createtime' => [
- 'between',
- $between
- ],
- 'create_staff_id' => ['in', $ids]
- ])->group('create_staff_id')->field("id,create_staff_id,count(*) as count")->select();
- $leads = [];
- foreach ($l as $v) {
- $leads[$v['create_staff_id']] = $v['count'];
- }
- $t = Contract::where([
- 'order_date' => [
- 'between',
- $betweenC
- ],
- 'create_staff_id' => ['in', $ids]
- ])->group('create_staff_id')->field("id,create_staff_id,count(*) as count")->select();
- $contracts = [];
- foreach ($t as $v) {
- $contracts[$v['create_staff_id']] = $v['count'];
- }
- $total=[];
- $total[0]=[
- 'name'=>'数据汇总',
- 'id'=>0,
- 'leads'=>0,
- 'customers'=>0,
- 'contracts'=>0,
- ];
- $sort=[];
- foreach ($staffs as $k => $v) {
- $v['leads'] = $leads[$v['id']] ?? 0;
- $v['customers'] = $customers[$v['id']] ?? 0;
- $v['contracts'] = $contracts[$v['id']] ?? 0;
- $sort[$v['id']] = $v['leads'] + $v['customers'] + $v['contracts'];
- $total[0]['leads'] += $v['leads'];
- $total[0]['customers'] += $v['customers'];
- $total[0]['contracts'] += $v['contracts'];
- $staffs[$v['id']] = $v;
- }
- arsort($sort);
- $result=[];
- foreach ($sort as $id=>$v){
- $result[]=$staffs[$id];
- }
- $staffs=array_merge($total,$result);
- $this->success('请求成功',$staffs);
- }
- //客户分析-客户数分析
- public function getCustomerCount()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'time');
- $where = [];
- $where['createtime'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result = Customer::where($where)
- ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
- ->group('time')->select();
- $trade_result = Customer::where($where)->where(['contract_status'=>1])
- ->field('count(*) as count,trade_date')
- ->group('trade_date')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['time']] = $v['count'];
- }
- $tradeCount = [];
- foreach ($trade_result as $v) {
- $tradeCount[$v['trade_date']] = $v['count'];
- }
- $dateList = getDateList($times[0], $times[1]);
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['add_count'][]=$addCount[$date] ?? 0;
- $result['trade_count'][]=$tradeCount[$date] ?? 0;
- }
- $this->success('请求成功', $result);
- }
- //客户分析-老客跟进分析
- public function getCustomerSignIn()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'time');
- $where = [];
- $where['createtime'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getDepartmentStaffIds($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result = StaffSignIn::where($where)
- ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
- ->group('time,customer_id')->select();
- $trade_result = StaffSignIn::where($where)->where(['customer_status'=>1])//成交
- ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
- ->group('time,customer_id')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['time']] = $v['count'];
- }
- $tradeCount = [];
- foreach ($trade_result as $v) {
- $tradeCount[$v['time']] = $v['count'];
- }
- $dateList = getDateList($times[0], $times[1]);
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['count'][]=$addCount[$date] ?? 0;
- $result['trade_count'][]=$tradeCount[$date] ?? 0;
- }
- $this->success('请求成功', $result);
- }
- //客户分析-客户转化率分析
- public function getCustomerTradeRate()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'time');
- $where = [];
- $where['createtime'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getDepartmentStaffIds($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result = Customer::where($where)->count();
- //成交
- $trade_result = Customer::where($where)->where(['contract_status' => 1])->count();
- $rate = getRatio($trade_result, $result);
- $this->success('请求成功', ['addCount' => $result, 'tradeCount' => $trade_result, 'rate' => $rate]);
- }
- //客户分析-客户售后满意度分析
- public function getWorkorderSatisfied()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'time');
- $where = [];
- $where['createtime'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getDepartmentStaffIds($department_id);
- $where['owner_staff_id'] = ['in', $l_ids];//完成人
- }
- if ($staff_id) {
- $where['owner_staff_id'] = $staff_id;//完成人
- }
- $result = Workorder::where($where)->where(['status'=>3])->count();
- //成交
- $satisfied_result = Workorder::where($where)->where(['status' => 3,'comment_score'=>['in',[3,4,5]]])->count();
- $rate = getRatio($satisfied_result, $result);
- $this->success('请求成功', ['addCount' => $result, 'satisfiedCount' => $satisfied_result, 'rate' => $rate]);
- }
- //客户分析-客户成交周期分析
- public function getCustomerTrade()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'date');
- $where = [];
- $where['trade_date'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getDepartmentStaffIds($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result = Customer::where($where)->where(['contract_status'=>1])//成交
- ->field('count(*) as count,trade_date')
- ->group('trade_date')->select();
- $date_result = Customer::where($where)->where(['contract_status' => 1])//成交
- ->field('trade_date,createtime')->select();
- $data=[];
- foreach ($date_result as $v){
- $data[$v['trade_date']][]=intval((strtotime($v['trade_date'])-strtotime($v['createtime']))/86400);
- }
- foreach ($data as $k=>$v) {
- $data[$k] = $v ? intval(array_sum($v) / count($v)) : [];
- }
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['trade_date']] = $v['count'];
- }
- $dateList = getDateList(strtotime($times[0]), strtotime($times[1])+86400-1);
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['count'][]=$addCount[$date] ?? 0;
- $result['number'][]=$data[$date] ?? 0;
- }
- $this->success('请求成功', $result);
- }
- //售后分析-工单数分析
- public function getWorkorderCount(){
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- $workorder_type = input('workorder_type');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'time');
- $where = [];
- $where['createtime'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- if ($workorder_type) {
- $where['workorder_type'] = $workorder_type;
- }
- $result = Workorder::where($where)//新增工单
- ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
- ->group('time')->select();
- unset($where['createtime']);
- $where['end_time'] = ['between', $times];
- $satisfied_result = Workorder::where($where)->where(['status' => 3])//完成工单
- ->field('count(*) as count,FROM_UNIXTIME(end_time,"%Y-%m-%d") as time')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['time']] = $v['count'];
- }
- $satisfiedCount = [];
- foreach ($satisfied_result as $v) {
- $satisfiedCount[$v['time']] = $v['count'];
- }
- $dateList = getDateList($times[0], $times[1]);
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['count'][]=$addCount[$date] ?? 0;
- $result['satisfied'][]=$satisfiedCount[$date] ?? 0;
- }
- $this->success('请求成功', $result);
- }
- //售后分析-工单金额分析
- public function getWorkorderMoney(){
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- $workorder_type = input('workorder_type');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'time');
- $where = [];
- $where['end_time'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getDepartmentStaffIds($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- if ($workorder_type) {
- $where['workorder_type'] = $workorder_type;
- }
- $result = Workorder::where($where)->where(['status' => 3])//完成工单
- ->field('count(*) as count,FROM_UNIXTIME(end_time,"%Y-%m-%d") as time')->select();
- $money_result = Workorder::where($where)->where(['status' => 3])//工单金额
- ->field('sum(money) as money,FROM_UNIXTIME(end_time,"%Y-%m-%d") as time')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['time']] = $v['count'];
- }
- $moneyCount = [];
- foreach ($money_result as $v) {
- $moneyCount[$v['time']] = $v['money'];
- }
- $dateList = getDateList($times[0], $times[1]);
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['count'][]=$addCount[$date] ?? 0;
- $result['money'][]=$moneyCount[$date] ?? 0;
- }
- $this->success('请求成功', $result);
- }
- //销售分析-销售漏斗分析
- public function getStaffCustomer()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'time');
- $where = [];
- $where['createtime'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneGroupStaffIds($department_id);
- $where['owner_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['owner_staff_id'] = $staff_id;
- }
- $status=Field::where(['type'=>'跟进状态'])->column('field');
- $result = Customer::where($where)//成交
- ->field('count(*) as count,follow')
- ->group('follow')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['follow']] = $v['count'];
- }
- $customerList = [];
- foreach ($status as $follow) {
- $customerList[] = [
- 'name' => $follow,
- 'value' => $addCount[$follow] ?? 0,
- ];
- }
- $this->success('请求成功', $customerList);
- }
- //产品分析-产品销售分析
- public function getProductCount(){
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'date');
- $where = [];
- $where['start_time'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['owner_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['owner_staff_id'] = $staff_id;
- }
- $result=Contract::where($where)->field('count(*) as count,start_time')->group('start_time')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['start_time']] = $v['count'];
- }
- $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['count'][]=$addCount[$date] ?? 0;
- }
- $this->success('请求成功', $result);
- }
- //产品分析-部件问题分析
- public function getProductParts()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'time');
- $where = [];
- $where['w.createtime'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result = Workorder::where($where)->alias('w')->where(['workorder_type'=>'上门维修'])
- ->join('qingdongams_customer_product c', 'c.id=w.customer_product_id')
- ->field('count(*) as count,product_id')->select();
- $result=collection($result)->toArray();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['product_id']] = $v['count'];
- }
- $sum=array_sum($addCount);
- $product = Product::where([])->select();
- $result=[];
- foreach ($product as $item) {
- $result['name'][]=$item['name'];
- $result['count'][]=$addCount[$item['id']] ?? 0;
- $result['ratio'][]=getRatio($addCount[$item['id']] ?? 0,$sum);
- }
- $this->success('请求成功', $result?:null);
- }
- //员工业绩分析-合同数分析
- public function getContractCount()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'date');
- $where = [];
- $where['order_date'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result=Contract::where($where)->field('count(*) as count,order_date')->group('order_date')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['order_date']] = $v['count'];
- }
- $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['count'][]=$addCount[$date] ?? 0;
- }
- $this->success('请求成功', $result);
- }
- //员工业绩分析-合同金额分析
- public function getContractMoney()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'date');
- $where = [];
- $where['order_date'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result=Contract::where($where)->field('sum(money) as count,order_date')->group('order_date')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['order_date']] = $v['count'];
- }
- $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['count'][]=$addCount[$date] ?? 0;
- }
- $this->success('请求成功', $result);
- }
- //员工业绩分析-回款金额分析
- public function getReceivablesMoney()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'date');
- $where = [];
- $where['return_time'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result=Receivables::where($where)->field('sum(money) as count,return_time')
- ->group('return_time')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['return_time']] = $v['count'];
- }
- $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['money'][]=intval($addCount[$date] ?? 0);
- }
- $this->success('请求成功', $result);
- }
- //员工业绩分析-合同汇总表
- public function getContractSummary()
- {
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'date');
- $where = [];
- $where['order_date'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result = Contract::where($where)->field('count(*) as count,order_date')
- ->group('order_date')->select();
- $contract_result = Contract::where($where)->field('sum(money) as count,order_date')
- ->group('order_date')->select();
- unset($where['order_date']);
- $where['return_time'] = ['between', $times];
- $receivables_result=Receivables::where($where)->field('sum(money) as count,return_time')
- ->group('return_time')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['order_date']] = $v['count'];
- }
- $contractCount = [];
- foreach ($contract_result as $v) {
- $contractCount[$v['order_date']] = $v['count'];
- }
- $receivablesCount = [];
- foreach ($receivables_result as $v) {
- $receivablesCount[$v['return_time']] = $v['count'];
- }
- $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));
- $contractList = [];
- foreach ($dateList as $date) {
- $contractList[] = [
- 'date' => $date,
- 'count' => $addCount[$date] ?? 0,
- 'contract_money' => $contractCount[$date] ?? 0,
- 'receivables_money' => $receivablesCount[$date] ?? 0,
- ];
- }
- $this->success('请求成功', $contractList);
- }
- //业绩目标完成分析-合同金额分析
- public function getContractTarget(){
- $year = input('year', date('Y'));
- $team_id = input('team_id');
- $staff_id = input('staff_id');
- if (empty($year)) {
- $this->error('日期不能为空');
- }
- $where = ['check_status'=>2];
- $where['order_date'] = ['like', $year . '%'];
- //默认公司
- $yearAchievement = Achievement::getCompanyYearAchievement($year,Achievement::CONTRACT_TYPE);
- if ($team_id) {//获取团队下成员
- $staff=Staff::where([])->find();
- $l_ids = Staff::getStaffLowerIds($staff);
- $where['owner_staff_id'] = ['in', $l_ids];
- $yearAchievement = Achievement::getTeamYearAchievement($year,Achievement::CONTRACT_TYPE,$team_id);
- }
- if ($staff_id) {
- $where['owner_staff_id'] = $staff_id;
- $yearAchievement = Achievement::getStaffYearAchievement($year,Achievement::CONTRACT_TYPE,$staff_id);
- }
- $contracts = Contract::where($where)->group('month')
- ->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(order_date),"%Y-%m") as month,count(*) as count')->select();
- $contracts_list = [];
- foreach ($contracts as $v) {
- $contracts_list[$v['month']] = $v;
- }
- $data = [];
- foreach ($yearAchievement as $k => $v) {
- if ($month = Achievement::getFieldMonth($k)) {
- $month = $year . '-' . $month;
- $row = [
- 'month' => $month,
- 'achievement' => $v,
- 'money' => isset($contracts_list[$month]) ? $contracts_list[$month]['money'] : 0,
- 'count' => isset($contracts_list[$month]) ? $contracts_list[$month]['count'] : 0,
- ];
- $row['ratio'] = ($row['money'] && intval($row['achievement'])) ? sprintf("%.2f", $row['money'] / $row['achievement'] * 100) : 0;
- $row['unit_price'] = ($row['money'] && $row['count']) ? sprintf("%.2f", $row['money'] / $row['count']) : 0;
- $data['month'][] = $row['month'];
- $data['achievement'][] = $row['achievement'];
- $data['money'][] = $row['money'];
- $data['ratio'][] = $row['ratio'];
- }
- }
- $this->success('请求成功', $data);
- }
- //业绩目标完成分析-回款金额分析
- public function getReceivablesTarget(){
- $year = input('year', date('Y'));
- $team_id = input('team_id');
- $staff_id = input('staff_id');
- if (empty($year)) {
- $this->error('日期不能为空');
- }
- $where = ['check_status'=>2];
- $where['return_time'] = ['like', $year . '%'];
- //默认公司
- $yearAchievement = Achievement::getCompanyYearAchievement($year,Achievement::RECEIVABLES_TYPE);
- if ($team_id) {//获取团队下成员
- $staff=Staff::where([])->find();
- $l_ids = Staff::getStaffLowerIds($staff);
- $where['owner_staff_id'] = ['in', $l_ids];
- $yearAchievement = Achievement::getTeamYearAchievement($year,Achievement::RECEIVABLES_TYPE,$team_id);
- }
- if ($staff_id) {
- $where['owner_staff_id'] = $staff_id;
- $yearAchievement = Achievement::getStaffYearAchievement($year,Achievement::RECEIVABLES_TYPE,$staff_id);
- }
- $receivables = Receivables::where($where)->group('month')
- ->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_time),"%Y-%m") as month,count(*) as count')->select();
- $receivables_list = [];
- foreach ($receivables as $v) {
- $receivables_list[$v['month']] = $v;
- }
- $data = [];
- foreach ($yearAchievement as $k => $v) {
- if ($month = Achievement::getFieldMonth($k)) {
- $month = $year . '-' . $month;
- $row = [
- 'month' => $month,
- 'achievement' => $v,
- 'money' => isset($receivables_list[$month]) ? $receivables_list[$month]['money'] : 0,
- 'count' => isset($receivables_list[$month]) ? $receivables_list[$month]['count'] : 0,
- ];
- $row['ratio'] = ($row['money'] && intval($row['achievement'])) ? sprintf("%.2f", $row['money'] / $row['achievement'] * 100) : 0;
- $row['unit_price'] = ($row['money'] && $row['count']) ? sprintf("%.2f", $row['money'] / $row['count']) : 0;
- $data['month'][] = $row['month'];
- $data['achievement'][] = $row['achievement'];
- $data['money'][] = $row['money'];
- $data['ratio'][] = $row['ratio'];
- }
- }
- $this->success('请求成功', $data);
- }
- //进销存销售分析-销售汇总分析
- public function getSalesSummary(){
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'date');
- $where = [];
- $where['order_date'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result = Contract::where($where)->field('count(*) as count,order_date')
- ->group('order_date')->select();
- $contract_result = Contract::where($where)->field('sum(money) as count,order_date')
- ->group('order_date')->select();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['order_date']] = $v['count'];
- }
- $contractCount = [];
- foreach ($contract_result as $v) {
- $contractCount[$v['order_date']] = $v['count'];
- }
- $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));
- $result=[];
- foreach ($dateList as $date) {
- $result['date'][]=$date;
- $result['count'][]=intval($addCount[$date] ?? 0);
- $result['contract_money'][]=$contractCount[$date] ?? 0;
- }
- $this->success('请求成功', $result);
- }
- //进销存销售分析-产品销售分析
- public function getProductSummary(){
- $times = input('times');
- $department_id = input('department_id');
- $staff_id = input('staff_id');
- if (empty($times)) {
- $this->error('日期不能为空');
- }
- $times = setTimes($times, 'date');
- $where = [];
- // $where['createtime'] = ['between', $times];
- if ($department_id) {
- $l_ids = Staff::getOneDepartmentStaffList($department_id);
- $where['create_staff_id'] = ['in', $l_ids];
- }
- if ($staff_id) {
- $where['create_staff_id'] = $staff_id;
- }
- $result = Contract::where($where) ->alias('w')
- ->join('qingdongams_contract_product c', 'w.id=c.contract_id')
- ->field('count(*) as count,sum(number) as number,product_id,sum(money) as money')->group('product_id')->where(['check_status'=>2])->select();
- $result=collection($result)->toArray();
- $addCount = [];
- foreach ($result as $v) {
- $addCount[$v['product_id']] = [
- 'count'=>$v['count'],
- 'number'=>$v['number'],
- 'money'=>$v['money'],
- ];
- }
- $sum=array_sum($addCount);
- $product = Product::where([])->select();
- $data = [];
- foreach ($product as $item) {
- $data[] = [
- 'name' => $item['name'],
- 'count' => $addCount[$item['id']]['count'] ?? 0,
- 'number' => $addCount[$item['id']]['number'] ?? 0,
- 'money' => $addCount[$item['id']]['money'] ?? 0,
- ];
- }
- $this->success('请求成功', $data);
- }
- //获取任务统计
- public function getEventStatistics(){
- $khfwsm= Event::where(['event_type'=>['like',"客户服务_上门%"]])->group('status')->column('count(*) as number','status');
- $khfwdh= Event::where(['event_type'=>"客户服务_电话"])->group('status')->column('count(*) as number','status');
- $khfwwx= Event::where(['event_type'=>"客户服务_微信"])->group('status')->column('count(*) as number','status');
- $shfwsm= Event::where(['event_type'=>['like',"售后服务_上门%"]])->group('status')->column('count(*) as number','status');
- $shfwdh= Event::where(['event_type'=>"售后服务_电话"])->group('status')->column('count(*) as number','status');
- $shfwfcwx= Event::where(['event_type'=>"售后服务_返厂维修"])->group('status')->column('count(*) as number','status');
- $data = [
- [
- 'name' => '客户服务-上门',
- 'data' => [
- [
- 'key' => '未开始',
- 'value' => ($khfwsm[0] ?? 0) . '',
- ],
- [
- 'key' => '进行中',
- 'value' => ($khfwsm[1] ?? 0) . '',
- ],
- [
- 'key' => '已结束',
- 'value' => ($khfwsm[2] ?? 0) . '',
- ],
- [
- 'key' => '任务结束',
- 'value' => ($khfwsm[3] ?? 0) . '',
- ],
- ]
- ],
- [
- 'name' => '客户服务-电话',
- 'data' => [
- [
- 'key' => '未开始',
- 'value' => ($khfwdh[0] ?? 0) . '',
- ],
- [
- 'key' => '进行中',
- 'value' => ($khfwdh[1] ?? 0) . '',
- ],
- [
- 'key' => '已结束',
- 'value' => ($khfwdh[2] ?? 0) . '',
- ],
- [
- 'key' => '任务结束',
- 'value' => ($khfwdh[3] ?? 0) . '',
- ],
- ]
- ],
- [
- 'name' => '客户服务-微信',
- 'data' => [
- [
- 'key' => '未开始',
- 'value' => ($khfwwx[0] ?? 0) . '',
- ],
- [
- 'key' => '进行中',
- 'value' => ($khfwwx[1] ?? 0) . '',
- ],
- [
- 'key' => '已结束',
- 'value' => ($khfwwx[2] ?? 0) . '',
- ],
- [
- 'key' => '任务结束',
- 'value' => ($khfwwx[3] ?? 0) . '',
- ],
- ]
- ],
- [
- 'name' => '售后服务-上门',
- 'data' => [
- [
- 'key' => '未开始',
- 'value' => ($shfwsm[0] ?? 0) . '',
- ],
- [
- 'key' => '进行中',
- 'value' => ($shfwsm[1] ?? 0) . '',
- ],
- [
- 'key' => '已结束',
- 'value' => ($shfwsm[2] ?? 0) . '',
- ],
- [
- 'key' => '任务结束',
- 'value' => ($shfwsm[3] ?? 0) . '',
- ],
- ]
- ],
- [
- 'name' => '售后服务-电话',
- 'data' => [
- [
- 'key' => '未开始',
- 'value' => ($shfwdh[0] ?? 0) . '',
- ],
- [
- 'key' => '进行中',
- 'value' => ($shfwdh[1] ?? 0) . '',
- ],
- [
- 'key' => '已结束',
- 'value' => ($shfwdh[2] ?? 0) . '',
- ],
- [
- 'key' => '任务结束',
- 'value' => ($shfwdh[3] ?? 0) . '',
- ],
- ]
- ],
- [
- 'name' => '售后服务-返厂维修',
- 'data' => [
- [
- 'key' => '未开始',
- 'value' => ($shfwfcwx[0] ?? 0) . '',
- ],
- [
- 'key' => '进行中',
- 'value' => ($shfwfcwx[1] ?? 0) . '',
- ],
- [
- 'key' => '已结束',
- 'value' => ($shfwfcwx[2] ?? 0) . '',
- ],
- [
- 'key' => '任务结束',
- 'value' => ($shfwfcwx[3] ?? 0) . '',
- ],
- ]
- ],
- ];
- $this->success('请求成功',$data);
- }
- //获取客户统计
- public function getCustomerStatistics(){
- $data= Customer::where([])->group('management_id,type')
- ->field('count(*) as number,type,management_id')->select();
- $managements=AreaManagement::where([])->column('name','id');
- $result=[];
- $customer=[];
- foreach ($data as $v){
- $customer[$v['management_id']][$v['type']]=$v['number'];
- }
- foreach ($customer as $mid=>$v){
- if(!isset($managements[$mid])){
- continue;
- }
- $result[]=[
- 'name'=>$managements[$mid],
- 'data' => [
- [
- 'key' => 'A类客户',
- 'value' => ($v['A'] ?? 0) . '',
- ],
- [
- 'key' => 'B类客户',
- 'value' => ($v['B'] ?? 0) . '',
- ],
- [
- 'key' => 'C类客户',
- 'value' => ($v['C'] ?? 0) . '',
- ],
- [
- 'key' => 'D类客户',
- 'value' => ($v['D'] ?? 0) . '',
- ],
- ]
- ];
- }
- $this->success('请求成功',$result);
- }
- //获取审批统计
- public function getApprovalStatistics(){
- $form= FormApproval::where([])->column('name','id');
- $data=[];
- foreach ($form as $id=>$name) {
- $value=[];
- $value['name']=$name;
- $approval=\addons\qingdongams\model\Approval::where(['formapproval_id'=>$id])->group('check_status')->column('count(*) as number','check_status');
- $value['data']=[
- ['key'=>'待审核','value'=>($approval[1]??0).''],
- ['key'=>'审核通过','value'=>($approval[2]??0).''],
- ['key'=>'审核拒绝','value'=>($approval[3]??0).''],
- ['key'=>'撤销审核','value'=>($approval[4]??0).''],
- ['key'=>'终止审批','value'=>($approval[9]??0).''],
- ];
- $data[]=$value;
- }
- $this->success('请求成功',$data);
- }
- //获取费用统计
- public function getConsumeStatistics()
- {
- $consume_types=ConsumeDetail::where([])->group('consume_type')->column('consume_type');
- $data=[];
- foreach ($consume_types as $name){
- $value=[];
- $value['name']=$name;
- $consume_ids=ConsumeDetail::where(['consume_type'=>$name])->column('consume_id');
- $consume=\addons\qingdongams\model\Consume::where(['id'=>['in',$consume_ids]])->group('check_status')->column('count(*) as number','check_status');
- $value['data']=[
- ['key'=>'待审核','value'=>($consume[0]??0).''],
- ['key'=>'审核通过','value'=>($consume[2]??0).''],
- ['key'=>'审核拒绝','value'=>($consume[3]??0).''],
- ['key'=>'撤销审核','value'=>($consume[4]??0).''],
- ['key'=>'终止审批','value'=>($consume[9]??0).''],
- ];
- $data[]=$value;
- }
- $this->success('请求成功',$data);
- }
- //获取出纳统计
- public function getCashierStatistics()
- {
- $consume_types=ConsumeDetail::where([])->group('consume_type')->column('consume_type');
- $data=[];
- foreach ($consume_types as $name){
- $value=[];
- $value['name']=$name;
- $consume_ids=ConsumeDetail::where(['consume_type'=>$name])->column('consume_id');
- $consume=\addons\qingdongams\model\Consume::where(['id'=>['in',$consume_ids],'check_status'=>2])->group('is_cashier')->column('count(*) as number','is_cashier');
- $value['data']=[
- ['key'=>'未打款','value'=>($consume[0]??0).''],
- ['key'=>'已打款','value'=>($consume[1]??0).''],
- ];
- $data[]=$value;
- }
- $this->success('请求成功',$data);
- }
- //获取合同统计
- public function getContractStatistics()
- {
- $contract= Contract::where([])->group('check_status')->column('count(*) as number','check_status');
- $data = [
- [
- 'name' => '合同',
- 'data' => [
- ['key'=>'待审核','value'=>($contract[0]??0).''],
- ['key'=>'审核通过','value'=>($contract[2]??0).''],
- ['key'=>'审核拒绝','value'=>($contract[3]??0).''],
- ['key'=>'撤销审核','value'=>($contract[4]??0).''],
- ['key'=>'终止审批','value'=>($contract[9]??0).''],
- ]
- ],
- ];
- $this->success('请求成功',$data);
- }
- //获取报价单拿统计
- public function getQuoteStatistics()
- {
- $quote= \addons\qingdongams\model\Quote::where([])->group('check_status')->column('count(*) as number','check_status');
- $data = [
- [
- 'name' => '报价单',
- 'data' => [
- ['key'=>'待审核','value'=>($quote[0]??0).''],
- ['key'=>'审核通过','value'=>($quote[2]??0).''],
- ['key'=>'审核拒绝','value'=>($quote[3]??0).''],
- ['key'=>'撤销审核','value'=>($quote[4]??0).''],
- ['key'=>'终止审批','value'=>($quote[9]??0).''],
- ]
- ],
- ];
- $this->success('请求成功',$data);
- }
- //获取备件统计
- public function getPartsStatistics()
- {
- $ru= PartsStockReload::where(['type'=>1])->group('check_status')->column('count(*) as number','check_status');
- $chu= PartsStockReload::where(['type'=>2])->group('check_status')->column('count(*) as number','check_status');
- $data = [
- [
- 'name' => '入库审核',
- 'data' => [
- ['key'=>'待审核','value'=>($ru[0]??0).''],
- ['key'=>'审核通过','value'=>($ru[2]??0).''],
- ['key'=>'审核拒绝','value'=>($ru[3]??0).''],
- ['key'=>'撤销审核','value'=>($ru[4]??0).''],
- ['key'=>'终止审批','value'=>($ru[9]??0).''],
- ]
- ],
- [
- 'name' => '出库审核',
- 'data' => [
- ['key'=>'待审核','value'=>($chu[0]??0).''],
- ['key'=>'审核通过','value'=>($chu[2]??0).''],
- ['key'=>'审核拒绝','value'=>($chu[3]??0).''],
- ['key'=>'撤销审核','value'=>($chu[4]??0).''],
- ['key'=>'终止审批','value'=>($chu[9]??0).''],
- ]
- ],
- ];
- $this->success('请求成功',$data);
- }
- //获取工单统计
- public function getWorkorderStatistics()
- {
- $workorder_types= Workorder::where([])->group('workorder_type')->column('workorder_type');
- $data=[];
- foreach ($workorder_types as $name) {
- if(empty($name)){
- continue;
- }
- $value=[];
- $value['name']=$name;
- $workorder=Workorder::where(['workorder_type'=>$name])->group('check_status')->column('count(*) as number','check_status');
- $value['data']=[
- ['key'=>'待审核','value'=>($workorder[0]??0).''],
- ['key'=>'审核通过','value'=>($workorder[2]??0).''],
- ['key'=>'审核拒绝','value'=>($workorder[3]??0).''],
- ['key'=>'撤销审核','value'=>($workorder[4]??0).''],
- ['key'=>'终止审批','value'=>($workorder[9]??0).''],
- ];
- $data[]=$value;
- }
- $this->success('请求成功',$data);
- }
- /**
- * 客户新增排行
- */
- public function newCustomer() {
- $date = input('date', date('Y-m'));
- $type = input('type', 0);
- if(strlen($date) == 4){
- $between = [strtotime($date . '-01-01'), strtotime($date.'-1-1 +1 year') - 1];
- }else{
- $between = [strtotime($date), strtotime('+1 month', strtotime($date)) - 1];
- }
- if ($type == 1) {//本人
- $ids = [$this->auth->id];
- } elseif ($type == 2) {//下属
- $ids = Staff::getLowerStaffId();
- } else {//全部
- $ids = Staff::getMyStaffIds();
- }
- $c = Customer::where([
- 'createtime' => [
- 'between',
- $between
- ],
- 'owner_staff_id' => ['in', $ids]
- ])->group('owner_staff_id')->order('count desc')->field("owner_staff_id,count(*) as count")->select();
- $customers = [];
- foreach ($c as $v) {
- $customers[$v['owner_staff_id']] = $v['count'];
- }
- arsort($customers);
- $staffs = Staff::getKeyList();
- $result = [];
- $i = 1;
- $data=[];
- foreach ($customers as $id => $number) {
- $val = $staffs[$id];
- $val['number'] = $number;
- $val['rank'] = $i;
- $data[]=[
- 'name'=>$val['name'],
- 'number'=>$number
- ];
- $i++;
- $result[] = $val;
- }
- $this->success('请求成功',['ranking' => $result, 'data' => $data]);
- }
- /**
- * 客户跟进排行
- */
- public function newRecord() {
- $date = input('date', date('Y-m'));
- $type = input('type', 0);
- if(strlen($date) == 4){
- $between = [strtotime($date . '-01-01'), strtotime($date.'-1-1 +1 year') - 1];
- }else{
- $between = [strtotime($date), strtotime('+1 month', strtotime($date)) - 1];
- }
- if ($type == 1) {//本人
- $ids = [$this->auth->id];
- } elseif ($type == 2) {//下属
- $ids = Staff::getLowerStaffId();
- } else {//全部
- $ids = Staff::getMyStaffIds();
- }
- //跟进次数
- $r= Record::where([
- 'create_staff_id' => ['in',$ids],
- 'relation_type' =>1,
- 'createtime' => [
- 'between',
- $between
- ]
- ])->field("create_staff_id,count(*) as count")->group('create_staff_id')->select();
- $records = [];
- foreach ($r as $v) {
- $records[$v['create_staff_id']] = $v['count'];
- }
- arsort($records);
- $staffs = Staff::getKeyList();
- $result = [];
- $data = [];
- $i = 1;
- foreach ($records as $id => $number) {
- $val = $staffs[$id];
- $val['number'] = $number;
- $val['rank'] = $i;
- $data[]=[
- 'name'=>$val['name'],
- 'number'=>$number
- ];
- $i++;
- $result[] = $val;
- }
- $this->success('请求成功',['ranking' => $result, 'data' => $data]);
- }
- /**
- * 获取业绩详情
- */
- public function getAchievementDetail()
- {
- $year = input('year', date('Y'));
- $status = input('status', 1);
- $staff_id = input('staff_id', '');
- $type = input('type', 3);//3员工 2团队
- if (empty($staff_id)) {
- $this->error('员工不能为空');
- }
- if($type == 2){
- $staff_id = $this->auth->id;
- $ids = Staff::getMyStaffIds();
- }else{
- $ids = $staff_id;
- }
- $achievements = Achievement::where(['type' => $type, 'obj_id' => ['in', $staff_id],
- 'year' => $year, 'status' => $status])
- ->field('sum(january) as january,sum(february) as february,sum(march) as march,sum(april) as april,sum(may) as may,sum(june) as june,sum(july) as july,sum(august) as august,sum(september) as september,sum(october) as october,sum(november) as november,sum(december) as december,sum(yeartarget) as yeartarget')->find()->toArray();
- if ($status == 1) {//合同金额
- $contracts = ContractRatio::where([
- 'contract.check_status' => 2, 'contract_ratio.staff_id' => ['in', $ids],
- 'contract.order_date' => ['like', $year . '%']
- ])->with(['contract'])->select();
- $contracts = collection($contracts)->toArray();
- $contractData = [];
- foreach ($contracts as $v) {
- $order_date = $v['contract']['order_date'];
- $month = date('Y-m', strtotime($order_date));
- $contractData[$month]['money'][] = $v['ratio_money'];
- $contractData[$month]['contract_id'][] = $v['contract_id'];
- }
- $list = [];
- foreach ($contractData as $month => $v) {
- $list[$month] = ['money' => array_sum($v['money']), 'count' => count($v['contract_id'])];
- }
- } else if ($status == 2) {
- $receivables = Receivables::where([
- 'owner_staff_id' => ['in', $ids],
- 'check_status' => 2,
- 'return_time' => ['like', $year . '%'],
- ])->group('month')
- ->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_time),"%Y-%m") as month,count(*) as count')->select();
- $list = [];
- foreach ($receivables as $v) {
- $list[$v['month']] = $v;
- }
- }elseif ($status == 3) {//客户
- $where['createtime'] = ['between',[strtotime($year.'-01-01'),
- strtotime($year.'-12-31')]];
- $where['create_staff_id'] = ['in',$ids];
- $moneys = Customer::where($where)
- ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m") as month')
- ->group('month')->select();
- $list = [];
- foreach ($moneys as $v) {
- $list[$v['month']] = $v;
- }
- } elseif ($status == 4) {//拜访
- $where['createtime'] = ['between',[strtotime($year.'-01-01'),
- strtotime($year.'-12-31')]];
- $where['staff_id'] = ['in',$staff_id];
- $where['type'] = 3;
- $where['status'] = 2;
- $moneys = Event::where($where)->field('count(*) as count,FROM_UNIXTIME(actual_end_time,"%Y-%m") as month')
- ->group('month')->select();
- $list = [];
- foreach ($moneys as $v) {
- $list[$v['month']] = $v;
- }
- }
- $data = [];
- $echartdata = [];
- foreach ($achievements as $k => $v) {
- if ($month = Achievement::getFieldMonth($k)) {
- $month = $year . '-' . $month;
- $row = [
- 'month' => $month,
- 'achievement' => $v ?? 0,
- 'money' => isset($list[$month]) ? $list[$month]['money'] : 0,
- 'count' => isset($list[$month]) ? $list[$month]['count'] : 0,
- ];
- $row['ratio'] = ($row['money'] && intval($row['achievement'])) ? sprintf("%.2f", $row['money'] / $row['achievement'] * 100) : 0;
- $row['unit_price'] = ($row['money'] && $row['count']) ? sprintf("%.2f", $row['money'] / $row['count']) : 0;
- $echartdata['month'][] = $row['month'];
- $echartdata['achievement'][] = $row['achievement']??0;
- $echartdata['money'][] = $row['money'];
- $data[] = $row;
- }
- }
- $money = isset($echartdata['money']) ? array_sum($echartdata['money']) : 0;
- $ratio = ($money && intval($achievements['yeartarget'])) ? sprintf("%.2f", $money / $achievements['yeartarget'] * 100) : 0;
- $yeartarget = [
- 'year' => $year,
- 'money' => $money,
- 'achievement' => $achievements['yeartarget'] ?? 0,
- 'ratio' => $ratio
- ];
- $this->success('请求成功', ['data' => $data, 'echartdata' => $echartdata, 'yeartarget' => $yeartarget]);
- }
- //客户统计
- public function customerStatistics(){
- $times = input('times');
- $type = input('type', 2);//2下属 1本人 0团队
- if (empty($times)) {
- $this->error('筛选时间不能为空');
- }
- $times = setTimes($times, 'datetime');
- $startTime = strtotime($times[0]);
- $endTime = strtotime($times[1]);
- $where = ['createtime' => ['between', [$startTime, $endTime]]];
- $whereR = ['createtime' => ['between', [$startTime, $endTime]]];
- if ($type == 1) {
- $where['owner_staff_id'] = $this->auth->id;
- $whereR['create_staff_id'] = $this->auth->id;
- } elseif ($type == 2) {
- $where['owner_staff_id'] = ['in', Staff::getLowerStaffId()];
- $whereR['create_staff_id'] = ['in', Staff::getLowerStaffId()];
- } else {
- $where['owner_staff_id'] = ['in', Staff::getMyStaffIds()];
- $whereR['create_staff_id'] = ['in', Staff::getMyStaffIds()];
- }
- //新增客户数
- $customer = Customer::where($where)->count();
- //跟进客户数
- $record = Record::where($whereR)->where(['relation_type'=>1,'follow_type' => ['neq', '其它']])->count();
- //客户成交数
- $customer_complate = Customer::where($where)->where(['contract_status' => 1])->count();
- //客户未成交数
- $customer_nocomplate = Customer::where($where)->where(['contract_status' => 0])->count();
- //进入公海客户数
- $seain = SeaOperation::where($where)->where(['type'=>1])->count();
- //公海领取客户数
- $seaout = SeaOperation::where($where)->where(['type'=>0])->count();
- $this->success('请求成功', [
- 'customer' => $customer,
- 'record' => $record,
- 'customer_complate' => $customer_complate,
- 'customer_nocomplate' => $customer_nocomplate,
- 'seain' => $seain,
- 'seaout' => $seaout,]);
- }
- //商机统计
- public function businessStatistics(){
- $times = input('times');
- $type = input('type', 2);//2下属 1本人 0团队
- if (empty($times)) {
- $this->error('筛选时间不能为空');
- }
- $times = setTimes($times, 'datetime');
- $startTime = strtotime($times[0]);
- $endTime = strtotime($times[1]);
- $where = ['createtime' => ['between', [$startTime, $endTime]]];
- if ($type == 1) {
- $where['owner_staff_id'] = $this->auth->id;
- } elseif ($type == 2) {
- $where['owner_staff_id'] = ['in', Staff::getLowerStaffId()];
- } else {
- $where['owner_staff_id'] = ['in', Staff::getMyStaffIds()];
- }
- //商机总金额
- $business_money = Business::where($where)->sum('money');
- //商机总数
- $business_num = Business::where($where)->count();
- //赢单金额
- $business_complate_money = Business::where($where)->where(['contract_status' => 1])->sum('money');
- //输单交金额
- $business_nocomplate_money = Business::where($where)->where(['contract_status' => 0])->sum('money');
- //赢单数
- $business_complate = Business::where($where)->where(['contract_status' => 1])->count();
- //输单数
- $business_nocomplate = Business::where($where)->where(['contract_status' => 0])->count();
- $this->success('请求成功', [
- 'business_money' => $business_money,
- 'business_num' => $business_num,
- 'business_complate_money' => $business_complate_money,
- 'business_nocomplate_money' => $business_nocomplate_money,
- 'business_complate' => $business_complate,
- 'business_nocomplate' => $business_nocomplate,]);
- }
- //合同统计
- public function contractStatistics(){
- $times = input('times');
- $type = input('type', 2);//2下属 1本人 0团队
- if (empty($times)) {
- $this->error('筛选时间不能为空');
- }
- if($times == 'thisyear' || $times == 'lastyear' || $times == 'nextyear'){
- $timeinfo ='%Y-%m';
- }else{
- $timeinfo ='%Y-%m-%d';
- }
- $times = setTimes($times, 'datetime');
- $startTime = strtotime($times[0]);
- $endTime = strtotime($times[1]);
- $where = ['order_date' => ['between', [date('Y-m-d 00:00:00',$startTime), date('Y-m-d 23:59:59',$endTime)]]];
- $whereR = ['return_time' => ['between', [date('Y-m-d 00:00:00',$startTime), date('Y-m-d 23:59:59',$endTime)]]];
- if ($type == 1) {
- $where['owner_staff_id'] = $this->auth->id;
- $whereR['owner_staff_id'] = $this->auth->id;
- } elseif ($type == 2) {
- $where['owner_staff_id'] = ['in', Staff::getLowerStaffId()];
- $whereR['owner_staff_id'] = ['in', Staff::getLowerStaffId()];
- } else {
- $where['owner_staff_id'] = ['in', Staff::getMyStaffIds()];
- $whereR['owner_staff_id'] = ['in', Staff::getMyStaffIds()];
- }
- $where['check_status']= 2;
- //合同总金额
- $contract_money = Contract::where($where)->sum('money');
- //签约合同数
- $contract_num = Contract::where($where)->count();
- //回款总金额
- $receivables_money = Receivables::where($whereR)->count();
- //未回款总金额
- $no_money = $contract_money -$receivables_money;
- if($no_money <= 0){
- $no_money = 0;
- }
- $receivables_nomoney = $no_money;
- //已到期的合同
- $contract_over = Contract::where($where)->where(['end_time' => array('gt',date('Y-m-d'))])->sum('money');
- //即将到期的合同
- $contract_now = Contract::where($where)->where(['end_time' => array('elt',date('Y-m-d'))])->count();
- $contract = Contract::where($where)->group('month')->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(order_date),"'.$timeinfo.'") as month')->select();
- $contracts_list = [];
- foreach ($contract as $v) {
- $contracts_list[$v['month']] = $v['money'];
- }
- $receivables = Receivables::where($whereR)->where([
- 'check_status' => 2,
- ])->group('dates')->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_time),"'.$timeinfo.'") as dates')->select();
- $receivables_list = [];
- foreach ($receivables as $v) {
- $receivables_list[$v['dates']] = $v['money'];
- }
- $data = [];
- foreach ($contracts_list as $k => $v) {
- $row = array(
- 'month'=>$k,
- 'contract'=>$v,
- 'receivables'=>isset($receivables_list[$k]) ? $receivables_list[$k] : 0,
- );
- $data[] = $row;
- }
- $this->success('请求成功', [
- 'contract_money' => $contract_money,
- 'receivables_money' => $receivables_money,
- 'receivables_nomoney' => $receivables_nomoney,
- 'contract_num' => $contract_num,
- 'contract_over' => $contract_over,
- 'contract_now' => $contract_now,
- 'contract' => $data,]);
- }
- }
|