SaleReport.php 181 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279328032813282328332843285328632873288328932903291329232933294329532963297329832993300330133023303330433053306330733083309331033113312331333143315331633173318331933203321332233233324332533263327332833293330333133323333333433353336333733383339334033413342334333443345334633473348334933503351335233533354335533563357335833593360336133623363336433653366336733683369337033713372337333743375337633773378337933803381338233833384338533863387338833893390339133923393339433953396339733983399340034013402340334043405340634073408340934103411341234133414341534163417341834193420342134223423342434253426342734283429343034313432343334343435343634373438343934403441344234433444344534463447344834493450345134523453345434553456345734583459346034613462346334643465346634673468346934703471347234733474347534763477347834793480348134823483348434853486348734883489349034913492349334943495349634973498349935003501350235033504350535063507350835093510351135123513351435153516351735183519352035213522352335243525352635273528352935303531353235333534353535363537353835393540354135423543354435453546354735483549355035513552355335543555355635573558355935603561356235633564356535663567356835693570357135723573357435753576357735783579358035813582358335843585358635873588358935903591359235933594359535963597359835993600360136023603360436053606
  1. <?php
  2. namespace app\admin\controller;
  3. //use app\abutment\model\SupplierRelationUser;
  4. //use app\abutment\model\SupplierUser;
  5. use think\facade\Db;
  6. //前端报表处理类
  7. class SaleReport extends Base
  8. {
  9. //订单来源:1直接下单,2咨询,3项目,4平台,5有赞,6售后补换货,7报备转单,8支付渠道9采销录单10结算录单
  10. private $all_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '报备转单'];//订单类型
  11. private $all_stock = [0 => '非库存品', 1 => '库存品'];//是否库存品
  12. private $all_sale_status = [0 => '待发货', 1 => '待发货完成', 2 => '发货已完成', 3 => '订单已取消'];//订单状态(sale表里的status)
  13. private $is_addrs = [0 => '是', 1 => '否'];//是否一地(数据库中存的是'是否多地',逻辑相反)
  14. private $is_custom = [0 => '否', 1 => '是'];//是否定制
  15. private $consult_info_status = [1 => '招标进行中', 2 => '招标已结束', 3 => '等待议价结果', 4 => '确认商品', 5 => '成功转单', 6 => '取消转单', 7 => '招标已暂停'];//状态
  16. private $noble_metal = [1 => '18K', 2 => '24K', 3 => '白银'];//贵金属种类
  17. private $all_order_return_status = [1 => '待业务审核', 2 => '待采购审核', 3 => '待设置仓库', 4 => '待客户退货', 5 => '售后已完成', 6 => '业务已驳回', 7 => '采购已驳回', 8 => '申请已取消'];
  18. private $is_activity = [0 => '不参与活动', 1 => '参与活动'];
  19. private $good_type = [1 => '正常商品', 2 => '赠品', 3 => '样品'];
  20. private $sale_diff_status = [1 => '修改销售额', 2 => '不修改销售额'];
  21. private $is_receive = [0 => '未收到', 1 => '收到'];
  22. private $bargain_order_status = [1 => '待主管审批', 2 => '待财务专员审批', 3 => '待财务主管审批', 4 => '审批通过', 5 => '主管驳回', 6 => '财务主管驳回', 7 => '已取消议价'];
  23. private $order_out_status = [0 => '待采购备货', 1 => '待库管发货', 2 => '已发货待收货', 3 => '已收货', 4 => '已全部退货'];
  24. private $except_code = [0 => [1 => '退货', 2 => '补发'], 1 => [1 => '退货', 2 => '换货'],];//期望意愿
  25. private $order_back_status = [0 => '待申请', 1 => '待验收', 2 => '待验收审核', 3 => '待业务审核', 4 => '完成退货'];
  26. private $order_source = [0 => '备库下单', 1 => '直接下单', 2 => '咨询下单', 3 => '项目下单', 4 => '平台导入',5=>'有赞转单',6=>"售后补换货",7=>'报备转单',8=>'支付渠道',9=>'采销录单',10=>'结算录单'];
  27. private $plat_type = [0=>"无",1=>"ToB",2=>"ToC"];
  28. //【一、管理报表】1.日报_列表
  29. public function consultDay()
  30. {
  31. $param = $this->request->only(['token', 'date' => date('Y-m-d')], 'post', 'trim');
  32. $rs_1 = Db::name('consult_info')
  33. ->alias('ci')
  34. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  35. ->where([
  36. ['ci.is_del', '=', 0],
  37. ['ci.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
  38. ])
  39. ->group('co.createrid,co.creater')
  40. ->column('COUNT(ci.id) 当日咨询单数量,SUM(ci.budget_price*ci.num) 金额,co.creater 创建人', 'co.createrid');
  41. $rs_2 = Db::name('sale')
  42. ->where([
  43. ['is_del', '=', 0],
  44. ['addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
  45. ])
  46. ->group('apply_id,apply_name')
  47. ->column('COUNT(id) 当日下单数量,SUM(total_price) 金额,apply_name 创建人', 'apply_id');
  48. $rs_3 = Db::name('order_out')
  49. ->alias('oo')
  50. ->leftJoin('sale s', 's.orderCode=oo.orderCode')
  51. ->where([
  52. ['oo.status', '=', 3],
  53. ['oo.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
  54. ])
  55. ->group('oo.apply_id,oo.apply_name')
  56. ->column('COUNT(oo.id) 当日签收数量,SUM(s.total_price) 金额,oo.apply_name 创建人', 'oo.apply_id');
  57. $rs_4 = Db::name('order_return')
  58. ->alias('or')
  59. ->leftJoin('sale s', 's.orderCode=or.orderCode')
  60. ->where([
  61. ['or.is_del', '=', 0],
  62. ['or.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
  63. ])
  64. ->group('or.apply_id,or.apply_name')
  65. ->column('COUNT(or.id) 单日售后单数,SUM(s.total_price) 金额,or.apply_name 创建人', 'or.apply_id');
  66. $all_ids = array_unique(array_merge(array_keys($rs_1), array_keys($rs_2), array_keys($rs_3), array_keys($rs_4)));
  67. $all_company = get_company_name_by_uid($all_ids);//获取创建人的所属部门
  68. $list = [];
  69. foreach ($all_ids as $id) {
  70. $val = [];
  71. if (isset($rs_1[$id]['创建人'])) $val['creater'] = $rs_1[$id]['创建人'];
  72. elseif (isset($rs_2[$id]['创建人'])) $val['creater'] = $rs_2[$id]['创建人'];
  73. elseif (isset($rs_3[$id]['创建人'])) $val['creater'] = $rs_3[$id]['创建人'];
  74. elseif (isset($rs_4[$id]['创建人'])) $val['creater'] = $rs_4[$id]['创建人'];
  75. else $val['creater'] = '';
  76. $val['creater_company'] = $all_company[$id] ?? '';//get_company_name_by_uid($id);//获取创建人的所属部门
  77. $val['consult_num'] = isset($rs_1[$id]['当日咨询单数量']) ? $rs_1[$id]['当日咨询单数量'] : '';
  78. $val['consult_money'] = isset($rs_1[$id]['金额']) ? $rs_1[$id]['金额'] : '';
  79. $val['sale_num'] = isset($rs_2[$id]['当日下单数量']) ? $rs_2[$id]['当日下单数量'] : '';
  80. $val['sale_money'] = isset($rs_2[$id]['金额']) ? $rs_2[$id]['金额'] : '';
  81. $val['receipt_num'] = isset($rs_3[$id]['当日签收数量']) ? $rs_3[$id]['当日签收数量'] : '';
  82. $val['receipt_money'] = isset($rs_3[$id]['金额']) ? $rs_3[$id]['金额'] : '';
  83. $val['invoice_num'] = '';
  84. $val['invoice_money'] = '';
  85. $val['after_sales_num'] = isset($rs_4[$id]['单日售后单数']) ? $rs_4[$id]['单日售后单数'] : '';
  86. $val['after_sales_money'] = isset($rs_4[$id]['金额']) ? $rs_4[$id]['金额'] : '';
  87. $val['payment_num'] = '';
  88. $val['payment_money'] = '';
  89. $list[] = $val;
  90. }
  91. return app_show(0, '请求成功', ['list' => $list]);
  92. }
  93. //【一、管理报表】1.日报_导出
  94. public function consultDayExport()
  95. {
  96. $param = $this->request->only(['token', 'date' => date('Y-m-d')], 'post', 'trim');
  97. $rs_1 = Db::name('consult_info')
  98. ->alias('ci')
  99. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  100. ->where([
  101. ['ci.is_del', '=', 0],
  102. ['ci.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
  103. ])
  104. ->group('co.createrid,co.creater')
  105. ->column('COUNT(ci.id) 当日咨询单数量,SUM(ci.budget_price*ci.num) 金额,co.creater 创建人', 'co.createrid');
  106. $rs_2 = Db::name('sale')
  107. ->where([
  108. ['is_del', '=', 0],
  109. ['addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
  110. ])
  111. ->group('apply_id,apply_name')
  112. ->column('COUNT(id) 当日下单数量,SUM(total_price) 金额,apply_name 创建人', 'apply_id');
  113. $rs_3 = Db::name('order_out')
  114. ->alias('oo')
  115. ->leftJoin('sale s', 's.orderCode=oo.orderCode')
  116. ->where([
  117. ['oo.status', '=', 3],
  118. ['oo.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
  119. ])
  120. ->group('oo.apply_id,oo.apply_name')
  121. ->column('COUNT(oo.id) 当日签收数量,SUM(s.total_price) 金额,oo.apply_name 创建人', 'oo.apply_id');
  122. $rs_4 = Db::name('order_return')
  123. ->alias('or')
  124. ->leftJoin('sale s', 's.orderCode=or.orderCode')
  125. ->where([
  126. ['or.is_del', '=', 0],
  127. ['or.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
  128. ])
  129. ->group('or.apply_id,or.apply_name')
  130. ->column('COUNT(or.id) 单日售后单数,SUM(s.total_price) 金额,or.apply_name 创建人', 'or.apply_id');
  131. $all_ids = array_unique(array_merge(array_keys($rs_1), array_keys($rs_2), array_keys($rs_3), array_keys($rs_4)));
  132. $all_company = get_company_name_by_uid($all_ids);//获取创建人的所属部门
  133. $list = [];
  134. foreach ($all_ids as $id) {
  135. $val = [];
  136. if (isset($rs_1[$id]['创建人'])) $val['创建人'] = $rs_1[$id]['创建人'];
  137. elseif (isset($rs_2[$id]['创建人'])) $val['创建人'] = $rs_2[$id]['创建人'];
  138. elseif (isset($rs_3[$id]['创建人'])) $val['创建人'] = $rs_3[$id]['创建人'];
  139. elseif (isset($rs_4[$id]['创建人'])) $val['创建人'] = $rs_4[$id]['创建人'];
  140. else $val['创建人'] = '';
  141. $val['创建人部门'] = $all_company[$id] ?? '';
  142. $val['当日咨询单数量'] = isset($rs_1[$id]['当日咨询单数量']) ? $rs_1[$id]['当日咨询单数量'] : '';
  143. $val['当日咨询单金额'] = isset($rs_1[$id]['金额']) ? $rs_1[$id]['金额'] : '';
  144. $val['当日下单数量'] = isset($rs_2[$id]['当日下单数量']) ? $rs_2[$id]['当日下单数量'] : '';
  145. $val['当日下单金额'] = isset($rs_2[$id]['金额']) ? $rs_2[$id]['金额'] : '';
  146. $val['当日签收数量'] = isset($rs_3[$id]['当日签收数量']) ? $rs_3[$id]['当日签收数量'] : '';
  147. $val['当日签收金额'] = isset($rs_3[$id]['金额']) ? $rs_3[$id]['金额'] : '';
  148. $val['当日申报发票数量'] = '';
  149. $val['当日申报发票金额'] = '';
  150. $val['单日售后单数'] = isset($rs_4[$id]['单日售后单数']) ? $rs_4[$id]['单日售后单数'] : '';
  151. $val['单日售后金额'] = isset($rs_4[$id]['金额']) ? $rs_4[$id]['金额'] : '';
  152. $val['当日回款单数'] = '';
  153. $val['当日回款金额'] = '';
  154. $list[] = $val;
  155. }
  156. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  157. excelSave('咨询单报表' . date('YmdHis'), array_keys($list[0]), $list);
  158. }
  159. //【一、管理报表】2.竞价反馈报表
  160. public function consultInfo()
  161. {
  162. $param = $this->request->only([
  163. 'token',
  164. 'start_date' => '',
  165. 'end_date' => '',
  166. 'page' => 1,
  167. 'size' => 15,
  168. ], 'post', 'trim');
  169. $where = [['ci.is_del', '=', 0]];
  170. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  171. $count = Db::name('consult_info')
  172. ->alias('ci')
  173. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  174. ->leftJoin('unit u', 'u.id=ci.unit')
  175. // ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
  176. ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo')
  177. ->where($where)
  178. ->order('ci.addtime', 'desc')
  179. ->count('ci.id');
  180. $data = Db::name('consult_info')
  181. ->alias('ci')
  182. ->field('co.creater,ci.addtime,ci.infoNo,cb.bidNo,"" companyName,ci.good_name,ci.num,u.unit,ci.budget_price,cb.id bid_id,ci.status,0 itemid,co.createrid,co.khNo')
  183. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  184. ->leftJoin('unit u', 'u.id=ci.unit')
  185. // ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
  186. ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo')
  187. ->where($where)
  188. ->page($param['page'], $param['size'])
  189. ->order('ci.addtime', 'desc')
  190. ->cursor();
  191. $userCommon = \app\admin\common\User::getIns();
  192. $list = $customer_tmp = $company_tmp = $customer2_tmp = [];
  193. foreach ($data as $value) {
  194. $value['total_fee'] = round($value['budget_price'] * $value['num'], 2);
  195. $value['is_bids'] = is_null($value['bid_id']) ? '否' : '是';
  196. $value['is_zhuandan'] = $value['status'] == 5 ? '是' : '否';//5成功转单
  197. if (!isset($customer2_tmp[$value['khNo']])) {
  198. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
  199. $customer2_tmp[$value['khNo']] = [
  200. 'itemid' => $temp['data']['itemid'],
  201. 'companyName' => $temp['data']['companyName'],
  202. ];
  203. }
  204. $value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
  205. $value['companyName'] = $customer2_tmp[$value['khNo']]['companyName'];
  206. if (!empty($value['itemid'])) {
  207. if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  208. $value['first_customer_org'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
  209. $value['second_customer_org'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
  210. }
  211. if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
  212. // $value['creater_company'] = $company_tmp[$value['createrid']] ?? '';
  213. $list[] = $value;
  214. }
  215. $all_company = get_company_name_by_uid(array_values($company_tmp));
  216. foreach ($list as &$val) {
  217. $val['creater_company'] = $all_company[$val['createrid']] ?? '';
  218. }
  219. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  220. }
  221. //【一、管理报表】2.竞价反馈报表_导出
  222. public function consultInfoExport()
  223. {
  224. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
  225. $where = [['ci.is_del', '=', 0]];
  226. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  227. $data = Db::name('consult_info')
  228. ->alias('ci')
  229. ->field('co.creater 创建人,"" 创建人部门,ci.addtime 竞价时间,ci.infoNo 竞价单号,cb.bidNo 反馈编号,"" 一级企业,"" 二级企业,"" 客户名称,ci.good_name 产品名称,ci.num 数量,u.unit 单位,ci.budget_price 单价, "" 金额,cb.id 是否采反,ci.status 是否转单,co.createrid,co.khNo')
  230. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  231. ->leftJoin('unit u', 'u.id=ci.unit')
  232. ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo')
  233. ->where($where)
  234. ->order('ci.addtime', 'desc')
  235. ->cursor();
  236. $userCommon = \app\admin\common\User::getIns();
  237. $list = $customer_tmp = $company_tmp = $customer2_tmp = [];
  238. foreach ($data as $value) {
  239. if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
  240. // $value['创建人部门'] = $company_tmp[$value['createrid']] ?? '';
  241. $value['金额'] = round($value['单价'] * $value['数量'], 2);
  242. $value['是否采反'] = is_null($value['是否采反']) ? '否' : '是';
  243. $value['是否转单'] = $value['是否转单'] == 5 ? '是' : '否';//5成功转单
  244. if (!isset($customer2_tmp[$value['khNo']])) {
  245. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
  246. $customer2_tmp[$value['khNo']] = [
  247. 'itemid' => $temp['data']['itemid'],
  248. 'companyName' => $temp['data']['companyName'],
  249. ];
  250. }
  251. $value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
  252. $value['客户名称'] = $customer2_tmp[$value['khNo']]['companyName'];
  253. if (!empty($value['itemid'])) {
  254. if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  255. $value['一级企业'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
  256. $value['二级企业'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
  257. }
  258. // unset($value['createrid']);
  259. unset($value['itemid']);
  260. unset($value['khNo']);
  261. $list[] = $value;
  262. }
  263. $all_company = get_company_name_by_uid(array_values($company_tmp));
  264. foreach ($list as &$val) {
  265. $val['创建人部门'] = $all_company[$val['createrid']] ?? '';
  266. unset($val['createrid']);
  267. }
  268. unset($customer_tmp);
  269. unset($company_tmp);
  270. unset($customer2_tmp);
  271. unset($all_company);
  272. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  273. excelSave('竞价反馈报表' . date('YmdHis'), array_keys($list[0]), $list);
  274. }
  275. //【一、管理报表】2.1.竞价单报表
  276. public function consultInfoNew()
  277. {
  278. $param = $this->request->only([
  279. 'token',
  280. 'start_date' => '',
  281. 'end_date' => '',
  282. 'page' => 1,
  283. 'size' => 15,
  284. ], 'post', 'trim');
  285. $where = [['ci.is_del', '=', 0]];
  286. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  287. $count = Db::name('consult_info')
  288. ->alias('ci')
  289. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  290. ->leftJoin('unit u', 'u.id=ci.unit')
  291. // ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
  292. ->where($where)
  293. ->order('ci.addtime', 'desc')
  294. ->count('ci.id');
  295. $data = Db::name('consult_info')
  296. ->alias('ci')
  297. ->field('co.creater,ci.addtime,ci.infoNo,"" companyName,ci.good_name,ci.num,u.unit,ci.budget_price,ci.status,0 itemid,co.createrid,co.khNo')
  298. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  299. ->leftJoin('unit u', 'u.id=ci.unit')
  300. // ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
  301. ->where($where)
  302. ->page($param['page'], $param['size'])
  303. ->order('ci.addtime', 'desc')
  304. ->cursor();
  305. $userCommon = \app\admin\common\User::getIns();
  306. $list = $customer_tmp = $company_tmp = $customer2_tmp = [];
  307. foreach ($data as $value) {
  308. $value['total_fee'] = round($value['budget_price'] * $value['num'], 2);
  309. $value['is_zhuandan'] = $value['status'] == 5 ? '是' : '否';//5成功转单
  310. if (!isset($customer2_tmp[$value['khNo']])) {
  311. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
  312. $customer2_tmp[$value['khNo']] = [
  313. 'itemid' => $temp['data']['itemid'],
  314. 'companyName' => $temp['data']['companyName'],
  315. ];
  316. }
  317. $value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
  318. $value['companyName'] = $customer2_tmp[$value['khNo']]['companyName'];
  319. if (!empty($value['itemid'])) {
  320. if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  321. $value['first_customer_org'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
  322. $value['second_customer_org'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
  323. }
  324. if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
  325. // $value['creater_company'] = $company_tmp[$value['createrid']] ?? '';
  326. $list[] = $value;
  327. }
  328. $all_company = get_company_name_by_uid(array_values($company_tmp));
  329. foreach ($list as &$val) {
  330. $val['creater_company'] = $all_company[$val['createrid']] ?? '';
  331. // unset($val['createrid']);
  332. }
  333. unset($customer_tmp);
  334. unset($company_tmp);
  335. unset($customer2_tmp);
  336. unset($all_company);
  337. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  338. }
  339. //【一、管理报表】2.1.竞价单报表_导出
  340. public function consultInfoNewExport()
  341. {
  342. $param = $this->request->only([
  343. 'token',
  344. 'start_date' => '',
  345. 'end_date' => '',
  346. ], 'post', 'trim');
  347. $where = [['ci.is_del', '=', 0]];
  348. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  349. $data = Db::name('consult_info')
  350. ->alias('ci')
  351. ->field('co.creater 创建人,"" 创建人部门,ci.addtime 竞价时间,ci.infoNo 竞价单号,"" 一级企业,"" 二级企业,"" 客户名称,ci.good_name 产品名称,ci.num 数量,u.unit 单位,ci.budget_price 单价, "" 金额,ci.status 是否转单,co.createrid,co.khNo')
  352. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  353. ->leftJoin('unit u', 'u.id=ci.unit')
  354. // ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
  355. ->where($where)
  356. ->order('ci.addtime', 'desc')
  357. ->cursor();
  358. $userCommon = \app\admin\common\User::getIns();
  359. $list = $customer_tmp = $company_tmp = $customer2_tmp = [];
  360. foreach ($data as $value) {
  361. if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
  362. // $value['创建人部门'] = $company_tmp[$value['createrid']] ?? '';
  363. $value['金额'] = round($value['单价'] * $value['数量'], 2);
  364. $value['是否转单'] = $value['是否转单'] == 5 ? '是' : '否';//5成功转单
  365. if (!isset($customer2_tmp[$value['khNo']])) {
  366. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
  367. $customer2_tmp[$value['khNo']] = [
  368. 'itemid' => $temp['data']['itemid'],
  369. 'companyName' => $temp['data']['companyName'],
  370. ];
  371. }
  372. $value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
  373. $value['客户名称'] = $customer2_tmp[$value['khNo']]['companyName'];
  374. if (!empty($value['itemid'])) {
  375. if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  376. $value['一级企业'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
  377. $value['二级企业'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
  378. }
  379. // unset($value['createrid']);
  380. unset($value['itemid']);
  381. unset($value['khNo']);
  382. $list[] = $value;
  383. }
  384. $all_company = get_company_name_by_uid(array_values($company_tmp));
  385. foreach ($list as &$val) {
  386. $val['创建人部门'] = $all_company[$val['createrid']] ?? '';
  387. unset($val['createrid']);
  388. }
  389. unset($customer_tmp);
  390. unset($company_tmp);
  391. unset($customer2_tmp);
  392. unset($all_company);
  393. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  394. excelSave('竞价单报表' . date('YmdHis'), array_keys($list[0]), $list);
  395. }
  396. //【一、管理报表】3.订单报表
  397. public function saleList()
  398. {
  399. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  400. $where = [['s.is_del', '=', 0]];
  401. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  402. $count = Db::name('sale')
  403. ->alias('s')
  404. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  405. ->where($where)
  406. ->count('s.id');
  407. $data = Db::name('sale')
  408. ->alias('s')
  409. ->field('s.apply_name,s.addtime,s.orderCode,"" companyName,s.good_name,s.good_num,s.sale_price,s.total_price,s.is_stock,s.order_type,s.good_code,s.apply_id,s.cat_id,s.customer_code')
  410. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  411. ->where($where)
  412. ->page($param['page'], $param['size'])
  413. ->order('s.addtime', 'desc')
  414. ->cursor();
  415. $userCommon = \app\admin\common\User::getIns();
  416. $list = $cat_tmp = $customer2_tmp = $company_tmp = [];
  417. foreach ($data as $value) {
  418. if (!isset($customer2_tmp[$value['customer_code']])) {
  419. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  420. $customer2_tmp[$value['customer_code']] = [
  421. 'itemid' => $temp['data']['itemid'],
  422. 'companyName' => $temp['data']['companyName'],
  423. ];
  424. }
  425. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  426. $value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
  427. if (!empty($value['itemid'])) {
  428. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  429. $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  430. $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  431. }
  432. if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['unit'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
  433. else $value['unit'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
  434. $value['is_stock'] = isset($this->all_stock[$value['is_stock']]) ? $this->all_stock[$value['is_stock']] : '';
  435. $value['order_type'] = isset($this->all_order_type[$value['order_type']]) ? $this->all_order_type[$value['order_type']] : '';
  436. if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
  437. // $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
  438. if (!isset($cat_tmp[$value['cat_id']])) $cat_tmp[$value['cat_id']] = implode('/', array_column(made($value['cat_id']), 'name'));
  439. $value['cat_name'] = $cat_tmp[$value['cat_id']] ?? '';
  440. $list[] = $value;
  441. }
  442. $all_company = get_company_name_by_uid(array_values($company_tmp));
  443. foreach ($list as &$val) {
  444. $val['creater_company'] = $all_company[$val['apply_id']] ?? '';
  445. }
  446. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  447. }
  448. //【一、管理报表】3.订单报表_导出
  449. public function saleExport()
  450. {
  451. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
  452. $where = [['s.is_del', '=', 0]];
  453. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  454. $data = Db::name('sale')
  455. ->alias('s')
  456. ->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,"" 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,s.is_stock 是否库存,s.order_type 订单类型,s.good_code,s.apply_id,s.cat_id,s.customer_code')
  457. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  458. ->where($where)
  459. ->order('s.addtime', 'desc')
  460. ->cursor();
  461. $userCommon = \app\admin\common\User::getIns();
  462. $list = $tmp_customer = $tmp_item = $cat_tmp = $customer2_tmp = [];
  463. foreach ($data as $value) {
  464. if (!isset($customer2_tmp[$value['customer_code']])) {
  465. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  466. $customer2_tmp[$value['customer_code']] = [
  467. 'itemid' => $temp['data']['itemid'],
  468. 'companyName' => $temp['data']['companyName'],
  469. ];
  470. }
  471. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  472. $value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
  473. if (!empty($value['itemid'])) {
  474. if (!isset($tmp_customer[$value['itemid']])) $tmp_customer[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  475. // $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  476. $value['一级企业'] = isset($tmp_customer[$value['itemid']][1]) ? $tmp_customer[$value['itemid']][1] : '';
  477. $value['二级企业'] = isset($tmp_customer[$value['itemid']][2]) ? $tmp_customer[$value['itemid']][2] : '';
  478. }
  479. if ($value['订单类型'] == 3 || $value['订单类型'] == 4) {
  480. $value['单位'] = Db::name("good_zixun")
  481. ->alias('gz')
  482. ->leftJoin('unit u', 'u.id=gz.good_unit')
  483. ->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])
  484. ->value('u.unit');
  485. } else {
  486. $value['单位'] = Db::name('good_basic')
  487. ->alias('gb')
  488. ->leftJoin('unit u', 'u.id=gb.good_unit')
  489. ->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])
  490. ->value('u.unit');
  491. }
  492. $value['是否库存'] = isset($this->all_stock[$value['是否库存']]) ? $this->all_stock[$value['是否库存']] : '';
  493. $value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';
  494. if (!isset($tmp_item[$value['apply_id']])) $tmp_item[$value['apply_id']] = $value['apply_id'];//get_company_name_by_uid($value['apply_id']);
  495. // $value['创建人所属部门'] = $tmp_item[$value['apply_id']];//获取创建人的所属部门
  496. if (!isset($cat_tmp[$value['cat_id']])) $cat_tmp[$value['cat_id']] = implode('/', array_column(made($value['cat_id']), 'name'));
  497. $value['分类'] = $cat_tmp[$value['cat_id']] ?? '';
  498. unset($value['itemid']);
  499. // unset($value['apply_id']);
  500. unset($value['good_code']);
  501. unset($value['cat_id']);
  502. unset($value['customer_code']);
  503. $list[] = $value;
  504. }
  505. $all_company = get_company_name_by_uid(array_values($tmp_item));
  506. foreach ($list as &$val) {
  507. $val['创建人所属部门'] = $all_company[$val['apply_id']] ?? '';
  508. unset($val['apply_id']);
  509. }
  510. unset($tmp_customer);
  511. unset($tmp_item);
  512. unset($cat_tmp);
  513. unset($customer2_tmp);
  514. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  515. excelSave('订单报表' . date('YmdHis'), array_keys($list[0]), $list);
  516. }
  517. //【一、管理报表】4.未发货报表_列表
  518. public function saleNotOut()
  519. {
  520. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_arrive_date' => '', 'end_arrive_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  521. $where = [['s.is_del', '=', 0], ['out.status', '=', 1]];
  522. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  523. if ($param['start_arrive_date'] != '' && $param['end_arrive_date'] != '') $where[] = ['s.arrive_time', 'between', [$param['start_arrive_date'] . ' 00:00:00', $param['end_arrive_date'] . ' 23:59:59']];
  524. $count = Db::name('sale')
  525. ->alias('s')
  526. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  527. ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
  528. ->where($where)
  529. ->order('s.addtime', 'desc')
  530. ->count('s.id');
  531. $data = Db::name('sale')
  532. ->alias('s')
  533. ->field('s.apply_name,s.addtime,s.orderCode,"" companyName,s.good_name,s.good_num,s.sale_price,s.total_price,s.arrive_time,s.good_code,s.skuCode,s.is_stock,s.order_type,s.apply_id,s.customer_code')
  534. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  535. ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
  536. ->where($where)
  537. ->page($param['page'], $param['size'])
  538. ->order('s.addtime', 'desc')
  539. ->cursor();
  540. $userCommon = \app\admin\common\User::getIns();
  541. $list = $customer2_tmp = $company_tmp = [];
  542. foreach ($data as $value) {
  543. if (!isset($customer2_tmp[$value['customer_code']])) {
  544. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  545. $customer2_tmp[$value['customer_code']] = [
  546. 'itemid' => $temp['data']['itemid']??"",
  547. 'companyName' => $temp['data']['companyName'],
  548. ];
  549. }
  550. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  551. $value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
  552. if (!empty($value['itemid'])) {
  553. $customer_org1 = array_column(get_top_customer_org($value['itemid'])??[], 'name', 'level');
  554. $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  555. $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  556. }
  557. if ($value['order_type'] == 3 || $value['order_type'] == 4) {
  558. $value['unit'] = Db::name("good_zixun")
  559. ->alias('gz')
  560. ->leftJoin('unit u', 'u.id=gz.good_unit')
  561. ->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])
  562. ->value('u.unit');
  563. } else {
  564. $value['unit'] = Db::name('good_basic')
  565. ->alias('gb')
  566. ->leftJoin('unit u', 'u.id=gb.good_unit')
  567. ->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])
  568. ->value('u.unit');
  569. }
  570. if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
  571. // $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
  572. $list[] = $value;
  573. }
  574. $all_company = get_company_name_by_uid(array_values($company_tmp));
  575. foreach ($list as &$val) {
  576. $val['creater_company'] = $all_company[$val['apply_id']] ?? '';
  577. // unset($val['createrid']);
  578. }
  579. unset($customer2_tmp);
  580. unset($company_tmp);
  581. return app_show(0, '请求成功', ['count' => $count, 'list' => $list]);
  582. }
  583. //【一、管理报表】4.未发货报表_导出
  584. public function saleNotOutExport()
  585. {
  586. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_arrive_date' => '', 'end_arrive_date' => ''], 'post', 'trim');
  587. $where = [['s.is_del', '=', 0], ['out.status', '=', 1]];
  588. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  589. if ($param['start_arrive_date'] != '' && $param['end_arrive_date'] != '') $where[] = ['s.arrive_time', 'between', [$param['start_arrive_date'] . ' 00:00:00', $param['end_arrive_date'] . ' 23:59:59']];
  590. $data = Db::name('sale')
  591. ->alias('s')
  592. ->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,"" 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,s.arrive_time 承诺发货时间,s.good_code,s.skuCode,s.is_stock,s.order_type,s.apply_id,s.customer_code')
  593. ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
  594. ->where($where)
  595. ->order('s.addtime', 'desc')
  596. ->cursor();
  597. $userCommon = \app\admin\common\User::getIns();
  598. $list = $customer2_tmp = $company_tmp =$depart= [];
  599. foreach ($data as $value) {
  600. if (!isset($customer2_tmp[$value['customer_code']])) {
  601. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  602. $customer2_tmp[$value['customer_code']] = [
  603. 'itemid' => $temp['data']['itemid']??"",
  604. 'companyName' => $temp['data']['companyName'],
  605. ];
  606. }
  607. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  608. $value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
  609. if (!empty($value['itemid'])) {
  610. $customer_org1 = array_column(get_top_customer_org($value['itemid'])??[], 'name', 'level');
  611. $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  612. $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  613. }
  614. if (!isset($depart[$value['apply_id']])) {
  615. $depart[$value['apply_id']] =get_company_name_by_uid($value['apply_id']);
  616. }
  617. $value['创建人所属部门'] = $depart[$value['apply_id']] ?? '';
  618. if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')
  619. ->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
  620. else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
  621. if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
  622. $value['创建人所属部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
  623. unset($value['itemid']);
  624. unset($value['good_code']);
  625. unset($value['order_type']);
  626. unset($value['apply_id']);
  627. unset($value['customer_code']);
  628. $list[] = $value;
  629. }
  630. unset($customer2_tmp);
  631. unset($company_tmp);
  632. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  633. excelSave('未发货报表' . date('YmdHis'), array_keys($list[0]), $list);
  634. }
  635. //【一、管理报表】5.未签收报表
  636. public function saleNotSigned()
  637. {
  638. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  639. $where = [['oo.is_del', '=', 0], ['oo.status', '=', 2], ['s.is_del', '=', 0]];
  640. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  641. $count = Db::name('order_out')
  642. ->alias('oo')
  643. ->leftJoin('sale s', 's.orderCode=oo.orderCode')
  644. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  645. // ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
  646. ->where($where)
  647. ->count('oo.id');
  648. $data = Db::name('order_out')
  649. ->alias('oo')
  650. ->field('oo.id,s.apply_name,s.addtime,s.orderCode,"" companyName,s.good_name,s.good_num,s.sale_price,s.total_price,oo.sendtime,s.good_code,s.order_type,s.apply_id,s.customer_code')
  651. ->leftJoin('sale s', 's.orderCode=oo.orderCode')
  652. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  653. // ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
  654. ->where($where)
  655. ->page($param['page'], $param['size'])
  656. ->order('s.addtime', 'desc')
  657. ->cursor();
  658. $userCommon = \app\admin\common\User::getIns();
  659. $list = $customer2_tmp = $uid_tmp = [];
  660. foreach ($data as $value) {
  661. if (!isset($customer2_tmp[$value['customer_code']])) {
  662. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  663. $customer2_tmp[$value['customer_code']] = [
  664. 'itemid' => $temp['data']['itemid'],
  665. 'companyName' => $temp['data']['companyName'],
  666. ];
  667. }
  668. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  669. $value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
  670. if (!empty($value['itemid'])) {
  671. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  672. $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  673. $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  674. }
  675. if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['unit'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u',
  676. 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
  677. else $value['unit'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
  678. if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];//get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
  679. // $value['creater_company'] = $uid_tmp[$value['apply_id']];
  680. $list[] = $value;
  681. }
  682. $all_company = get_company_name_by_uid(array_values($uid_tmp));
  683. foreach ($list as &$val) {
  684. $val['creater_company'] = $all_company[$val['apply_id']] ?? '';
  685. // unset($val['apply_id']);
  686. }
  687. unset($customer2_tmp);
  688. unset($uid_tmp);
  689. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  690. }
  691. //【一、管理报表】5.未签收报表_导出
  692. public function saleNotSignedExport()
  693. {
  694. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
  695. $where = [['s.is_del', '=', 0], ['oo.status', '=', 2]];
  696. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  697. // $data = Db::name('order_out')
  698. // ->alias('oo')
  699. // ->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,csi.companyName 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,oo.sendtime 发货时间,csi.itemid,s.good_code,s.order_type,s.apply_id')
  700. // ->leftJoin('sale s', 's.orderCode=oo.orderCode')
  701. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  702. // ->leftJoin('order_out out', 'out.orderCode=s.orderCode')
  703. // ->where($where)
  704. // ->order('s.addtime', 'desc')
  705. // ->cursor('oo.id');
  706. // $list = [];
  707. // foreach ($data as $value) {
  708. //
  709. // if (!empty($value['itemid'])) {
  710. // $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  711. // $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  712. // $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  713. // }
  714. //
  715. // if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
  716. // else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
  717. //
  718. // $value['创建人所属部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
  719. //
  720. // unset($value['itemid']);
  721. // unset($value['good_code']);
  722. // unset($value['order_type']);
  723. // unset($value['apply_id']);
  724. //
  725. // $list[] = $value;
  726. // }
  727. $list = $this->saleNotSignedExportTemp($where);
  728. excelSave('未签收报表' . date('YmdHis'), ['创建人', '创建人所属部门', '下单时间', '订单号', '一级企业', '二级企业', '客户名称', '商品名称', '数量', '单位', '单价', '金额', '发货时间'], $list);
  729. }
  730. //针对"【一、管理报表】5.未签收报表_导出"数据量太大
  731. private function saleNotSignedExportTemp(array $where = [])
  732. {
  733. $data = Db::name('order_out')
  734. ->alias('oo')
  735. ->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,"" 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,oo.sendtime 发货时间,s.good_code,s.order_type,s.apply_id,s.customer_code')
  736. ->leftJoin('sale s', 's.orderCode=oo.orderCode')
  737. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  738. ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
  739. ->where($where)
  740. ->order('s.addtime', 'desc')
  741. ->cursor();
  742. $userCommon = \app\admin\common\User::getIns();
  743. $customer2_tmp = $uid_tmp = [];
  744. foreach ($data as $value) {
  745. if (!isset($customer2_tmp[$value['customer_code']])) {
  746. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  747. $customer2_tmp[$value['customer_code']] = [
  748. 'itemid' => $temp['data']['itemid'],
  749. 'companyName' => $temp['data']['companyName'],
  750. ];
  751. }
  752. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  753. $value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
  754. if (!empty($value['itemid'])) {
  755. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  756. $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  757. $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  758. }
  759. if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')
  760. ->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
  761. else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
  762. if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
  763. $value['创建人所属部门'] = $uid_tmp[$value['apply_id']];
  764. unset($value['itemid']);
  765. unset($value['good_code']);
  766. unset($value['order_type']);
  767. unset($value['apply_id']);
  768. unset($value['customer_code']);
  769. yield $value;
  770. }
  771. }
  772. //【一、管理报表】6.售后报表
  773. public function orderReturn()
  774. {
  775. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  776. $where = [['or.is_del', '=', 0]];
  777. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  778. if ($param['status'] != '') $where[] = ['or.status', '=', $param['status']];
  779. $count = Db::name('order_return')
  780. ->alias('or')
  781. ->leftJoin('sale s', 's.orderCode=or.orderCode')
  782. // ->leftJoin('customer_info csi', 'csi.companyNo=or.customer_code')
  783. ->leftJoin('order_out out', 'out.orderCode=s.orderCode')
  784. ->leftJoin('result_info ri', 'ri.id=or.error_code')
  785. ->where($where)
  786. ->count('or.id');
  787. $data = Db::name('order_return')
  788. ->alias('or')
  789. ->field('or.addtime,or.apply_name,or.orderCode,"" companyName,or.good_name,or.total_num,s.sale_price,s.total_price,or.updatetime,ri.result,or.status,or.good_code,or.order_type,or.apply_id,or.customer_code')
  790. ->leftJoin('sale s', 's.orderCode=or.orderCode')
  791. // ->leftJoin('customer_info csi', 'csi.companyNo=or.customer_code')
  792. ->leftJoin('order_out out', 'out.orderCode=s.orderCode')
  793. ->leftJoin('result_info ri', 'ri.id=or.error_code')
  794. ->where($where)
  795. ->page($param['page'], $param['size'])
  796. ->order('s.addtime', 'desc')
  797. ->cursor();
  798. $userCommon = \app\admin\common\User::getIns();
  799. $list = $customer2_tmp = $company_tmp = [];
  800. foreach ($data as $value) {
  801. if (!isset($customer2_tmp[$value['customer_code']])) {
  802. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  803. $customer2_tmp[$value['customer_code']] = [
  804. 'itemid' => $temp['data']['itemid'],
  805. 'companyName' => $temp['data']['companyName'],
  806. ];
  807. }
  808. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  809. $value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
  810. if (!empty($value['itemid'])) {
  811. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  812. $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  813. $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  814. }
  815. if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
  816. // $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
  817. if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['unit'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
  818. else $value['unit'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
  819. $value['is_finish'] = $value['status'] == 5 ? '已完结' : '未完结';
  820. $value['status'] = isset($this->all_order_return_status[$value['status']]) ? $this->all_order_return_status[$value['status']] : '';
  821. $list[] = $value;
  822. }
  823. $all_company = get_company_name_by_uid(array_values($company_tmp));
  824. foreach ($list as &$val) {
  825. $val['creater_company'] = $all_company[$val['apply_id']] ?? '';
  826. unset($val['apply_id']);
  827. }
  828. unset($customer2_tmp);
  829. unset($company_tmp);
  830. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  831. }
  832. //【一、管理报表】6.售后报表_导出
  833. public function orderReturnExport()
  834. {
  835. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => ''], 'post', 'trim');
  836. $where = [['or.is_del', '=', 0]];
  837. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  838. if ($param['status'] != '') $where[] = ['or.status', '=', $param['status']];
  839. $data = Db::name('order_return')
  840. ->alias('or')
  841. ->field('or.addtime 售后时间,or.apply_name 创建人,"" 创建人部门,or.orderCode 订单号,"" 一级企业,"" 二级企业,"" 客户名称,or.good_name 商品名称,or.total_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,or.updatetime 签收时间,ri.result 异常原因,or.status 异常状态,"" 是否完结,or.good_code,or.order_type,or.apply_id,or.customer_code')
  842. ->leftJoin('sale s', 's.orderCode=or.orderCode')
  843. // ->leftJoin('customer_info csi', 'csi.companyNo=or.customer_code')
  844. ->leftJoin('order_out out', 'out.orderCode=s.orderCode')
  845. ->leftJoin('result_info ri', 'ri.id=or.error_code')
  846. ->where($where)
  847. ->order('s.addtime', 'desc')
  848. ->cursor();
  849. $userCommon = \app\admin\common\User::getIns();
  850. $list = $customer2_tmp = $company_tmp = [];
  851. foreach ($data as $value) {
  852. if (!isset($customer2_tmp[$value['customer_code']])) {
  853. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  854. $customer2_tmp[$value['customer_code']] = [
  855. 'itemid' => $temp['data']['itemid'],
  856. 'companyName' => $temp['data']['companyName'],
  857. ];
  858. }
  859. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  860. $value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
  861. if (!empty($value['itemid'])) {
  862. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  863. $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  864. $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  865. }
  866. if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
  867. else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
  868. if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
  869. // $value['创建人部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
  870. $value['是否完结'] = $value['异常状态'] == 5 ? '已完结' : '未完结';
  871. $value['异常状态'] = isset($this->all_order_return_status[$value['异常状态']]) ? $this->all_order_return_status[$value['异常状态']] : '';
  872. unset($value['itemid']);
  873. unset($value['good_code']);
  874. unset($value['order_type']);
  875. // unset($value['apply_id']);
  876. unset($value['customer_code']);
  877. $list[] = $value;
  878. }
  879. $all_company = get_company_name_by_uid(array_values($company_tmp));
  880. foreach ($list as &$val) {
  881. $val['创建人部门'] = $all_company[$val['apply_id']] ?? '';
  882. unset($val['apply_id']);
  883. }
  884. unset($customer2_tmp);
  885. unset($company_tmp);
  886. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  887. excelSave('售后报表' . date('YmdHis'), array_keys($list[0]), $list);
  888. }
  889. //【一、管理报表】7.产品热销排行
  890. public function goodRankingList()
  891. {
  892. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  893. $where = [['s.is_del', '=', 0]];
  894. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  895. $count = Db::name('sale')
  896. ->alias('s')
  897. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  898. ->where($where)
  899. ->group('s.good_code')
  900. ->count('s.id');
  901. $data = Db::name('sale')
  902. ->alias('s')
  903. // ->field('s.good_code,s.good_name,SUM(s.good_num) total_num,SUM(s.total_price) total_price,s.cat_id,s.customer_code')
  904. ->field('s.good_code,SUM(s.good_num) total_num,SUM(s.total_price) total_price')
  905. // ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
  906. ->where($where)
  907. ->group('s.good_code')//存疑,此处原本是根据csi.itemid分组的
  908. ->page($param['page'], $param['size'])
  909. ->order('total_num', 'desc')
  910. ->select()
  911. ->toArray();
  912. $replenish = Db::name('sale')
  913. ->where(['is_del' => 0, 'good_code' => array_unique(array_column($data, 'good_code'))])
  914. ->column('good_name,cat_id,customer_code', 'good_code');
  915. $userCommon = \app\admin\common\User::getIns();
  916. $list = $customer2_tmp = [];
  917. foreach ($data as $value) {
  918. $value['good_name'] = $replenish[$value['good_code']]['good_name'] ?? '';
  919. $value['cat_id'] = $replenish[$value['good_code']]['cat_id'] ?? '';
  920. $value['customer_code'] = $replenish[$value['good_code']]['customer_code'] ?? '';
  921. if (!isset($customer2_tmp[$value['customer_code']])) {
  922. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  923. $customer2_tmp[$value['customer_code']] = [
  924. 'itemid' => $temp['data']['itemid'],
  925. 'companyName' => $temp['data']['companyName'],
  926. ];
  927. }
  928. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  929. // $value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
  930. if (!empty($value['itemid'])) {
  931. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  932. $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  933. $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  934. }
  935. $value['cat'] = implode('/', array_column(made($value['cat_id']), 'name'));
  936. $list[] = $value;
  937. }
  938. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  939. }
  940. //【一、管理报表】7.产品热销排行_导出
  941. public function goodRankingListExport()
  942. {
  943. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
  944. $where = [['s.is_del', '=', 0]];
  945. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  946. $data = Db::name('sale')
  947. ->alias('s')
  948. ->field('s.good_code,"" 产品品类,"" 产品名称,SUM(s.good_num) total_num,"" 数量,SUM(s.total_price) 金额,0 cat_id,"" customer_code,"" 一级企业,"" 二级企业')
  949. ->where($where)
  950. ->group('s.good_code')//存疑,此处原本是根据csi.itemid分组的
  951. ->order('total_num', 'desc')
  952. ->cursor();
  953. $replenish = Db::name('sale')
  954. ->alias('s')
  955. ->where($where)
  956. ->column('good_name 产品名称,cat_id,customer_code', 'good_code');
  957. $userCommon = \app\admin\common\User::getIns();
  958. $list = $customer2_tmp = [];
  959. foreach ($data as $value) {
  960. // $tmp=Db::name('sale')
  961. // ->field('good_name 产品名称,cat_id,customer_code')
  962. // ->where(['is_del'=>0,'good_code'=>$value['good_code']])
  963. // ->findOrEmpty();
  964. $value = array_merge($value, $replenish[$value['good_code']]);
  965. if (!isset($customer2_tmp[$value['customer_code']])) {
  966. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  967. $customer2_tmp[$value['customer_code']] = [
  968. 'itemid' => $temp['data']['itemid'],
  969. 'companyName' => $temp['data']['companyName'],
  970. ];
  971. }
  972. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  973. // $value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
  974. if (!empty($value['itemid'])) {
  975. $customer_org1 = array_column(get_top_customer_org($value['itemid']) ?? [], 'name', 'level');
  976. $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  977. $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  978. }
  979. $value['产品品类'] = implode('/', array_column(made($value['cat_id']), 'name'));
  980. $value['数量'] = $value['total_num'];
  981. unset($value['good_code']);
  982. unset($value['itemid']);
  983. unset($value['cat_id']);
  984. unset($value['total_num']);
  985. unset($value['customer_code']);
  986. $list[] = $value;
  987. }
  988. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  989. excelSave('产品热销排行' . date('YmdHis'), array_keys($list[0]), $list);
  990. }
  991. //【三、咨询单导出明细】
  992. public function consultDetailed()
  993. {
  994. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'use_desc' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  995. $where = [['ci.is_del', '=', 0]];
  996. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  997. if ($param['use_desc'] != '') $where[] = ['ci.use_desc', 'like', '%' . $param['use_desc'] . '%'];
  998. $count = Db::name('consult_info')
  999. ->alias('ci')
  1000. ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo AND cb.is_del=0')
  1001. ->leftJoin('sale s', 's.zxNo=cb.bidNo AND s.is_del=0')
  1002. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  1003. ->leftJoin('brand b', 'b.id=ci.brand_id')
  1004. ->leftJoin('unit u', 'u.id=ci.unit')
  1005. ->where($where)
  1006. ->order('ci.addtime', 'desc')
  1007. ->count('ci.id');
  1008. $data = Db::name('consult_info')
  1009. ->alias('ci')
  1010. ->field('ci.addtime,co.salesman,"" name,ci.infoNo,"" companyName,ci.use_desc,ci.good_name,b.brand_name,ci.model,s.good_code,ci.cat_id,ci.budget_price,u.unit,ci.num,s.sale_price,ci.arrival_time,ci.is_addrs,ci.is_custom,ci.remark,ci.status,ci.bargain_code,s.orderCode,co.khNo,co.saleid')
  1011. ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo AND cb.is_del=0')
  1012. ->leftJoin('sale s', 's.zxNo=cb.bidNo AND s.is_del=0')
  1013. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  1014. ->leftJoin('brand b', 'b.id=ci.brand_id')
  1015. ->leftJoin('unit u', 'u.id=ci.unit')
  1016. ->where($where)
  1017. ->page($param['page'], $param['size'])
  1018. ->order('ci.addtime', 'desc')
  1019. ->cursor();
  1020. $userCommon = \app\admin\common\User::getIns();
  1021. $list = $customer2_tmp = $tmp=[];
  1022. foreach ($data as $value) {
  1023. if (!isset($tmp[$value['saleid']])) $tmp[$value['saleid']] = get_company_name_by_uid($value['saleid']);
  1024. $value['name']=$tmp[$value['saleid']] ?? '';
  1025. if (!isset($customer2_tmp[$value['khNo']])) {
  1026. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
  1027. $customer2_tmp[$value['khNo']] = [
  1028. 'itemid' => $temp['data']['itemid'] ?? 0,
  1029. 'companyName' => $temp['data']['companyName'] ?? '',
  1030. ];
  1031. }
  1032. $value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
  1033. $value['companyName'] = $customer2_tmp[$value['khNo']]['companyName'];
  1034. if (!empty($value['itemid'])) {
  1035. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  1036. $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  1037. $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  1038. }
  1039. $cat = made($value['cat_id']);
  1040. $value['first_cat_name'] = isset($cat[0]['name']) ? $cat[0]['name'] : '';
  1041. $value['budget_total_price'] = round($value['budget_price'] * $value['num'], 2);
  1042. $value['is_addrs'] = isset($this->is_addrs[$value['is_addrs']]) ? $this->is_addrs[$value['is_addrs']] : '';
  1043. $value['is_custom'] = isset($this->is_custom[$value['is_custom']]) ? $this->is_custom[$value['is_custom']] : '';
  1044. $value['status'] = isset($this->consult_info_status[$value['status']]) ? $this->consult_info_status[$value['status']] : '';
  1045. $list[] = $value;
  1046. }
  1047. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  1048. }
  1049. //【三、咨询单导出明细】_导出
  1050. public function consultDetailedExport()
  1051. {
  1052. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'use_desc' => ''], 'post', 'trim');
  1053. $where = [['ci.is_del', '=', 0]];
  1054. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1055. if ($param['use_desc'] != '') $where[] = ['ci.use_desc', 'like', '%' . $param['use_desc'] . '%'];
  1056. $data = Db::name('consult_info')
  1057. ->alias('ci')
  1058. ->field('ci.addtime 咨询时间,co.salesman 业务员,"" 业务部门,cb.infoNo 咨询单号,"" 一级组织,"" 二级组织,"" 客户名称,ci.use_desc 产品用途,ci.good_name 产品名称,b.brand_name 品牌,ci.model 型号,s.good_code 产品编码,"" 一级分类,ci.cat_id,ci.budget_price 预算单价,u.unit 单位,ci.num 需求数量,"" 预算总价,s.sale_price 单价,ci.arrival_time 要求到货时间,ci.is_addrs 是否一地,ci.is_custom 定制,ci.remark 咨询备注,ci.status 竞单状态,ci.bargain_code 上次咨询单单号,s.orderCode 销售订单编号,co.khNo,co.saleid')
  1059. ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo AND cb.is_del=0')
  1060. ->leftJoin('sale s', 's.zxNo=cb.bidNo AND s.is_del=0')
  1061. ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo')
  1062. // ->leftJoin('depart_user du', 'du.uid=co.saleid AND du.is_del=0')
  1063. // ->leftJoin('company_item item', 'item.id=du.itemid')
  1064. // ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
  1065. ->leftJoin('brand b', 'b.id=ci.brand_id')
  1066. ->leftJoin('unit u', 'u.id=ci.unit')
  1067. ->where($where)
  1068. ->order('ci.addtime', 'desc')
  1069. ->cursor();
  1070. $userCommon = \app\admin\common\User::getIns();
  1071. $list = $customer2_tmp = [];
  1072. foreach ($data as $value) {
  1073. if (!isset($tmp[$value['saleid']])) $tmp[$value['saleid']] = get_company_name_by_uid($value['saleid']);
  1074. $value['业务部门']=$tmp[$value['saleid']] ?? '';
  1075. if (!isset($customer2_tmp[$value['khNo']])) {
  1076. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
  1077. $customer2_tmp[$value['khNo']] = [
  1078. 'itemid' => $temp['data']['itemid'] ?? 0,
  1079. 'companyName' => $temp['data']['companyName'] ?? '',
  1080. ];
  1081. }
  1082. $value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
  1083. $value['客户名称'] = $customer2_tmp[$value['khNo']]['companyName'];
  1084. if (!empty($value['itemid'])) {
  1085. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  1086. $value['一级组织'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  1087. $value['二级组织'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  1088. }
  1089. $cat = made($value['cat_id']);
  1090. $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : '';
  1091. $value['预算总价'] = round($value['预算单价'] * $value['需求数量'], 2);
  1092. $value['是否一地'] = isset($this->is_addrs[$value['是否一地']]) ? $this->is_addrs[$value['是否一地']] : '';
  1093. $value['定制'] = isset($this->is_custom[$value['定制']]) ? $this->is_custom[$value['定制']] : '';
  1094. $value['竞单状态'] = isset($this->consult_info_status[$value['竞单状态']]) ? $this->consult_info_status[$value['竞单状态']] : '';
  1095. unset($value['itemid']);
  1096. unset($value['cat_id']);
  1097. unset($value['khNo']);
  1098. unset($value['saleid']);
  1099. $list[] = $value;
  1100. }
  1101. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  1102. excelSave('咨询单导出明细' . date('YmdHis'), array_keys($list[0]), $list);
  1103. }
  1104. //【四、工差明细表】
  1105. public function saleDiff()
  1106. {
  1107. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  1108. $where = [['pd.is_del', '=', 0]];
  1109. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['pd.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1110. $count = Db::name('purchease_diff')
  1111. ->alias('pd')
  1112. ->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
  1113. ->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
  1114. ->leftJoin('order_num on', 'on.cgdNo=pd.cgdNo')
  1115. ->leftJoin('sale s', 's.orderCode=on.orderCode')
  1116. // ->leftJoin('customer_info ci', 'ci.companyNo=s.customer_code')
  1117. ->where($where)
  1118. ->order('pd.addtime', 'desc')
  1119. ->count('pd.id');
  1120. $data = Db::name('purchease_diff')
  1121. ->alias('pd')
  1122. ->field('pd.addtime,sd.diffCode,sd.orderCode,pd.apply_name,pd.apply_id,po.addtime poaddtime,pd.good_name,"" companyName,pd.good_num,pd.sale_price,pd.good_weight,pd.diff_weight,pd.diff_price,sd.status,po.order_type,po.spuCode,s.apply_name sale_apply_name,s.apply_id sale_apply_id,s.customer_code')
  1123. ->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
  1124. ->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
  1125. ->leftJoin('order_num on', 'on.cgdNo=pd.cgdNo')
  1126. ->leftJoin('sale s', 's.orderCode=on.orderCode')
  1127. // ->leftJoin('customer_info ci', 'ci.companyNo=s.customer_code')
  1128. ->where($where)
  1129. ->page($param['page'], $param['size'])
  1130. ->order('pd.addtime', 'desc')
  1131. ->cursor();
  1132. $list = $codes = $uid_tmp = [];
  1133. foreach ($data as $value) {
  1134. if (!isset($codes[$value['customer_code']])) $codes[$value['customer_code']] = $value['customer_code'];
  1135. if ($value['order_type'] == 3 || $value['order_type'] == 4) $noble_metal = Db::name("good_zixun")->where(["spuCode" => $value['spuCode'], "is_del" => 0])->value('noble_metal');
  1136. else $noble_metal = Db::name('good_basic')->where(['spuCode' => $value['spuCode'], 'is_del' => 0])->value('noble_metal');
  1137. if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];
  1138. if (!isset($uid_tmp[$value['sale_apply_id']])) $uid_tmp[$value['sale_apply_id']] = $value['sale_apply_id'];
  1139. // $value['apply_company'] = $uid_tmp[$value['apply_id']];
  1140. // $value['sale_apply_company'] = $uid_tmp[$value['sale_apply_id']];
  1141. $value['noble_metal'] = isset($this->noble_metal[$noble_metal]) ? $this->noble_metal[$noble_metal] : '';
  1142. $value['total_price'] = round($value['sale_price'], $value['good_num'], 2);
  1143. $value['is_update_sale'] = isset($this->sale_diff_status[$value['status']]) ? $this->sale_diff_status[$value['status']] : '';
  1144. $list[] = $value;
  1145. }
  1146. $userCommon = \app\admin\common\User::getIns();
  1147. $names = $userCommon->handle('getCodeAndName', ['code' => array_values($codes)]);
  1148. $all_company = get_company_name_by_uid(array_values($uid_tmp));
  1149. foreach ($list as &$val) {
  1150. $val['companyName'] = $names['data'][$val['customer_code']] ?? '';
  1151. $val['apply_company'] = $all_company[$val['apply_id']] ?? '';
  1152. $val['sale_apply_company'] = $all_company[$val['sale_apply_id']] ?? '';
  1153. }
  1154. unset($codes);
  1155. unset($uid_tmp);
  1156. unset($all_company);
  1157. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  1158. }
  1159. //【四、工差明细表】_导出
  1160. public function saleDiffExport()
  1161. {
  1162. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
  1163. $where = [['pd.is_del', '=', 0]];
  1164. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['pd.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1165. $data = Db::name('purchease_diff')
  1166. ->alias('pd')
  1167. ->field('pd.addtime 发起时间,sd.diffCode 工差单号,sd.orderCode 关联确认单,pd.apply_name 创建人,"" 部门,pd.apply_id,po.addtime 确认单下单日期,pd.good_name 产品名称,"" 客户名称,"" 贵金属种类,pd.good_num 下单数量,pd.sale_price 单价,"" 总额,pd.good_weight 商品总重量,pd.diff_weight 工差重量,pd.diff_price 销售工差总费用,sd.status 是否修改销售额,s.apply_name 销售人员,s.apply_id sale_apply_id,"" 销售部门,po.order_type,po.spuCode,s.customer_code')
  1168. ->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
  1169. ->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
  1170. ->leftJoin('order_num on', 'on.cgdNo=pd.cgdNo')
  1171. ->leftJoin('sale s', 's.orderCode=on.orderCode')
  1172. // ->leftJoin('customer_info ci', 'ci.companyNo=s.customer_code')
  1173. ->where($where)
  1174. ->order('pd.addtime', 'desc')
  1175. ->cursor();
  1176. $list = $codes = $uid_tmp = [];
  1177. foreach ($data as $value) {
  1178. if (!isset($codes[$value['customer_code']])) $codes[$value['customer_code']] = $value['customer_code'];
  1179. if ($value['order_type'] == 3 || $value['order_type'] == 4) $noble_metal = Db::name("good_zixun")->where(["spuCode" => $value['spuCode'], "is_del" => 0])->value('noble_metal');
  1180. else $noble_metal = Db::name('good_basic')->where(['spuCode' => $value['spuCode'], 'is_del' => 0])->value('noble_metal');
  1181. if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];
  1182. if (!isset($uid_tmp[$value['sale_apply_id']])) $uid_tmp[$value['sale_apply_id']] = $value['sale_apply_id'];
  1183. // $value['部门'] = $uid_tmp[$value['apply_id']];
  1184. // $value['销售部门'] = $uid_tmp[$value['sale_apply_id']];
  1185. $value['贵金属种类'] = isset($this->noble_metal[$noble_metal]) ? $this->noble_metal[$noble_metal] : '';
  1186. $value['总额'] = round($value['单价'], $value['下单数量'], 2);
  1187. $value['是否修改销售额'] = isset($this->sale_diff_status[$value['是否修改销售额']]) ? $this->sale_diff_status[$value['是否修改销售额']] : '';
  1188. // unset($value['apply_id']);
  1189. unset($value['order_type']);
  1190. unset($value['spuCode']);
  1191. // unset($value['sale_apply_id']);
  1192. $list[] = $value;
  1193. }
  1194. $userCommon = \app\admin\common\User::getIns();
  1195. $names = $userCommon->handle('getCodeAndName', ['code' => array_values($codes)]);
  1196. $all_company = get_company_name_by_uid(array_values($uid_tmp));
  1197. foreach ($list as &$val) {
  1198. $val['客户名称'] = $names['data'][$val['customer_code']] ?? '';
  1199. $val['部门'] = $all_company[$val['apply_id']] ?? '';
  1200. $val['销售部门'] = $all_company[$val['sale_apply_id']] ?? '';
  1201. unset($val['customer_code']);
  1202. unset($val['apply_id']);
  1203. unset($val['sale_apply_id']);
  1204. }
  1205. unset($codes);
  1206. unset($uid_tmp);
  1207. unset($all_company);
  1208. unset($names);
  1209. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  1210. excelSave('工差明细表' . date('YmdHis'), array_keys($list[0]), $list);
  1211. }
  1212. //【五、退货明细表】
  1213. public function thData()
  1214. {
  1215. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  1216. $where = [['thd.is_del', '=', 0]];
  1217. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['thd.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1218. $count = Db::name('th_data')
  1219. ->alias('thd')
  1220. ->leftJoin('sale s', 's.orderCode=thd.orderCode')
  1221. ->where($where)
  1222. ->order('thd.addtime', 'desc')
  1223. ->count('thd.id');
  1224. $data = Db::name('th_data')
  1225. ->alias('thd')
  1226. ->field('thd.id,thd.th_type,
  1227. thd.addtime return_time,thd.thCode,"" outCode,"" status,s.apply_name,s.apply_id apply_company,thd.orderCode,s.addtime,s.customerName companyName,s.good_name,s.sale_price,s.wsend_num,thd.th_num,thd.th_fee,"" contactor,"" mobile,"" addr,"" result')
  1228. ->leftJoin('sale s', 's.orderCode=thd.orderCode')
  1229. ->where($where)
  1230. ->limit($param['page'],$param['size'])
  1231. ->order('thd.addtime', 'desc')
  1232. ->select()
  1233. ->toArray();
  1234. $th_types = ["1"=>[],"2"=>[],"3"=>[],"4"=>[]];
  1235. foreach ($data as $value) {
  1236. $th_types[$value['th_type']][] = $value['thCode'];
  1237. }
  1238. $all_order_return = Db::name('order_return')
  1239. ->alias('or')
  1240. ->withAttr('status', function ($val) {
  1241. return isset($this->all_order_return_status[$val]) ? $this->all_order_return_status[$val] : '';
  1242. })
  1243. ->leftJoin('order_out oo', 'oo.outCode=or.outCode')
  1244. ->leftJoin('order_addr oa', 'oa.id=oo.addrid')
  1245. ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
  1246. ->where('or.is_del', 0)
  1247. ->whereIn('or.returnCode', $th_types[2])
  1248. ->column('or.id,or.outCode,or.status,or.apply_id,or.apply_name,oa.contactor,oa.mobile,oa.addr,ri.result,oa.addr_code', 'or.returnCode');
  1249. $all_order_back = Db::name('order_back')
  1250. ->alias('ob')
  1251. ->where('ob.is_del', 0)
  1252. ->whereIn('ob.thNo', $th_types[3])
  1253. ->withAttr('status', function ($val) {
  1254. return isset($this->order_back_status[$val]) ? $this->order_back_status[$val] : '';
  1255. })
  1256. ->leftJoin('order_out oo', 'oo.outCode=ob.outCode')
  1257. ->leftJoin('order_addr oa', 'oa.id=oo.addrid')
  1258. ->leftJoin('result_info ri', 'ri.result_code=ob.return_code')
  1259. ->column('ob.id,ob.outCode,ob.status,ob.apply_id,ob.apply_name,oa.contactor,oa.mobile,oa.addr,ri.result,oa.addr_code', 'thNo');
  1260. $list = [];
  1261. $all_uids = array_unique(array_merge(array_column($all_order_return, 'apply_id'), array_column
  1262. ($all_order_back, 'apply_id'), array_column($data, 'apply_company')));
  1263. $all_company = get_company_name_by_uid($all_uids);
  1264. // $userCommon = \app\admin\common\User::getIns();
  1265. // $names = $userCommon->handle('getCodeAndName', ['code' => array_unique(array_column($data, 'customer_code'))]);
  1266. foreach ($data as $value) {
  1267. if ($value['th_type'] == 2) {
  1268. $value['outCode'] = isset($all_order_return[$value['thCode']]['outCode']) ? $all_order_return[$value['thCode']]['outCode'] : '';
  1269. $value['status'] = isset($all_order_return[$value['thCode']]['status']) ? $all_order_return[$value['thCode']]['status'] : '';
  1270. $value['apply_name'] = isset($all_order_return[$value['thCode']]['apply_name']) ? $all_order_return[$value['thCode']]['apply_name'] : '';
  1271. $value['apply_company'] = $all_company[$all_order_return[$value['thCode']]['apply_id'] ?? 0] ?? '';//isset($all_order_return[$value['thCode']]['apply_company']) ? $all_order_return[$value['thCode']]['apply_company'] : '';
  1272. $value['contactor'] = isset($all_order_return[$value['thCode']]['contactor']) ? $all_order_return[$value['thCode']]['contactor'] : '';
  1273. $value['mobile'] = isset($all_order_return[$value['thCode']]['mobile']) ? $all_order_return[$value['thCode']]['mobile'] : '';
  1274. $value['addr'] = isset($all_order_return[$value['thCode']]['addr']) ? $all_order_return[$value['thCode']]['addr'] : '';
  1275. $value['result'] = isset($all_order_return[$value['thCode']]['result']) ? $all_order_return[$value['thCode']]['result'] : '';
  1276. } elseif ($value['th_type'] == 3) {
  1277. $value['outCode'] = isset($all_order_back[$value['thCode']]['outCode']) ? $all_order_back[$value['thCode']]['outCode'] : '';
  1278. $value['status'] = isset($all_order_back[$value['thCode']]['status']) ? $all_order_back[$value['thCode']]['status'] : '';
  1279. $value['apply_name'] = isset($all_order_back[$value['thCode']]['apply_name']) ? $all_order_back[$value['thCode']]['apply_name'] : '';
  1280. $value['apply_company'] = $all_company[$all_order_back[$value['thCode']]['apply_id'] ?? 0] ?? '';//isset($all_order_back[$value['thCode']]['apply_company']) ? $all_order_back[$value['thCode']]['apply_company'] : '';
  1281. $value['contactor'] = isset($all_order_back[$value['thCode']]['contactor']) ? $all_order_back[$value['thCode']]['contactor'] : '';
  1282. $value['mobile'] = isset($all_order_back[$value['thCode']]['mobile']) ? $all_order_back[$value['thCode']]['mobile'] : '';
  1283. $value['addr'] = isset($all_order_back[$value['thCode']]['addr']) ? $all_order_back[$value['thCode']]['addr'] : '';
  1284. $value['result'] = isset($all_order_back[$value['thCode']]['result']) ? $all_order_back[$value['thCode']]['result'] : '';
  1285. }else{
  1286. $value['apply_company'] = $all_company[$value['apply_company'] ?? 0] ?? '';
  1287. $value['status'] = $value['th_type']==1?"销售退货完成":"取消订单完成";
  1288. $value['result'] = $value['th_type']==1?"销售退货":"取消订单";
  1289. }
  1290. $list[] = $value;
  1291. }
  1292. unset($names);
  1293. unset($all_uids);
  1294. unset($all_company);
  1295. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  1296. }
  1297. //【五、退货明细表】
  1298. public function thDataExport()
  1299. {
  1300. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
  1301. $where = [['thd.is_del', '=', 0]];
  1302. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['thd.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1303. $data = Db::name('th_data')
  1304. ->alias('thd')
  1305. ->field('thd.id,thd.th_type,thd.addtime 退货时间,thd.thCode 退货单号,"" 发货申请单编码,"" 退货单状态,s.apply_name 创建人,s.apply_id 部门,thd.orderCode 确认单编号,s.addtime 下单日期,s.customerName 客户名称,s.good_name 产品名称,s.sale_price 单价,s.wsend_num 未发货数量,thd.th_num 退货数量,thd.th_fee 退货销售货款,"" 发货单联系人,"" 发货单联系人电话,"" 发货单地址,"" 退货原因')
  1306. ->leftJoin('sale s', 's.orderCode=thd.orderCode')
  1307. ->where($where)
  1308. ->order('thd.addtime', 'desc')
  1309. ->select()
  1310. ->toArray();
  1311. $th_types = ["1"=>[],"2"=>[],"3"=>[],"4"=>[]];
  1312. foreach ($data as $value) {
  1313. $th_types[$value['th_type']][] = $value['退货单号'];
  1314. }
  1315. $all_order_return = Db::name('order_return')
  1316. ->alias('or')
  1317. ->withAttr('退货单状态', function ($val) {
  1318. return isset($this->all_order_return_status[$val]) ? $this->all_order_return_status[$val] : '';
  1319. })
  1320. ->leftJoin('order_out oo', 'oo.outCode=or.outCode')
  1321. ->leftJoin('order_addr oa', 'oa.id=oo.addrid')
  1322. ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
  1323. ->where('or.is_del', 0)
  1324. ->whereIn('or.returnCode', $th_types[2])
  1325. ->column('or.id,or.outCode 发货申请单编码,or.status 退货单状态,or.apply_id,or.apply_name 创建人,"" 部门,oa.contactor 发货单联系人,oa.mobile 发货单联系人电话,oa.addr 发货单地址,ri.result 退货原因,oa.addr_code', 'or.returnCode');
  1326. $all_order_back = Db::name('order_back')
  1327. ->alias('ob')
  1328. ->where('ob.is_del', 0)
  1329. ->whereIn('ob.thNo', $th_types[3])
  1330. ->withAttr('退货单状态', function ($val) {
  1331. return isset($this->order_back_status[$val]) ? $this->order_back_status[$val] : '';
  1332. })
  1333. ->leftJoin('order_out oo', 'oo.outCode=ob.outCode')
  1334. ->leftJoin('order_addr oa', 'oa.id=oo.addrid')
  1335. ->leftJoin('result_info ri', 'ri.result_code=ob.return_code')
  1336. ->column('ob.id,ob.outCode 发货申请单编码,ob.status 退货单状态,ob.apply_id,ob.apply_name 创建人,"" 部门,oa.contactor 发货单联系人,oa.mobile 发货单联系人电话,oa.addr 发货单地址,ri.result 退货原因,oa.addr_code', 'thNo');
  1337. $list = [];
  1338. // $userCommon = \app\admin\common\User::getIns();
  1339. // $names = $userCommon->handle('getCodeAndName', ['code' => array_column($data, 'customer_code')]);
  1340. $all_uids = array_unique(array_merge(array_column($all_order_return, 'apply_id'), array_column
  1341. ($all_order_back, 'apply_id'), array_column($all_order_back, '部门')));
  1342. $all_company = get_company_name_by_uid($all_uids);
  1343. foreach ($data as $value) {
  1344. if ($value['th_type'] == 2) {
  1345. $value['发货申请单编码'] = isset($all_order_return[$value['退货单号']]['发货申请单编码']) ? $all_order_return[$value['退货单号']]['发货申请单编码'] : '';
  1346. $value['退货单状态'] = isset($all_order_return[$value['退货单号']]['退货单状态']) ? $all_order_return[$value['退货单号']]['退货单状态'] : '';
  1347. $value['创建人'] = isset($all_order_return[$value['退货单号']]['创建人']) ? $all_order_return[$value['退货单号']]['创建人'] : '';
  1348. $value['部门'] = $all_company[$all_order_return[$value['退货单号']]['apply_id'] ?? 0] ?? '';//isset($all_order_return[$value['退货单号']]['部门']) ? $all_order_return[$value['退货单号']]['部门'] : '';
  1349. $value['发货单联系人'] = isset($all_order_return[$value['退货单号']]['发货单联系人']) ? $all_order_return[$value['退货单号']]['发货单联系人'] : '';
  1350. $value['发货单联系人电话'] = isset($all_order_return[$value['退货单号']]['发货单联系人电话']) ? $all_order_return[$value['退货单号']]['发货单联系人电话'] : '';
  1351. $value['发货单地址'] = isset($all_order_return[$value['退货单号']]['发货单地址']) ? $all_order_return[$value['退货单号']]['发货单地址'] : '';
  1352. $value['退货原因'] = isset($all_order_return[$value['退货单号']]['退货原因']) ? $all_order_return[$value['退货单号']]['退货原因'] : '';
  1353. } elseif ($value['th_type'] == 3) {
  1354. $value['发货申请单编码'] = isset($all_order_back[$value['退货单号']]['发货申请单编码']) ? $all_order_back[$value['退货单号']]['发货申请单编码'] : '';
  1355. $value['退货单状态'] = isset($all_order_back[$value['退货单号']]['退货单状态']) ? $all_order_back[$value['退货单号']]['退货单状态'] : '';
  1356. $value['创建人'] = isset($all_order_back[$value['退货单号']]['创建人']) ? $all_order_back[$value['退货单号']]['创建人'] : '';
  1357. $value['部门'] = $all_company[$all_order_back[$value['退货单号']]['apply_id'] ?? 0] ?? '';//isset($all_order_back[$value['退货单号']]['部门']) ? $all_order_back[$value['退货单号']]['部门'] : '';
  1358. $value['发货单联系人'] = isset($all_order_back[$value['退货单号']]['发货单联系人']) ? $all_order_back[$value['退货单号']]['发货单联系人'] : '';
  1359. $value['发货单联系人电话'] = isset($all_order_back[$value['退货单号']]['发货单联系人电话']) ? $all_order_back[$value['退货单号']]['发货单联系人电话'] : '';
  1360. $value['发货单地址'] = isset($all_order_back[$value['退货单号']]['发货单地址']) ? $all_order_back[$value['退货单号']]['发货单地址'] : '';
  1361. $value['退货原因'] = isset($all_order_back[$value['退货单号']]['退货原因']) ? $all_order_back[$value['退货单号']]['退货原因'] : '';
  1362. }else{
  1363. $value['部门'] = $all_company[$value['部门'] ?? 0] ?? '';
  1364. $value['退货单状态'] = $value['th_type']==1?"销售退货完成":"取消订单完成";
  1365. $value['退货原因'] = $value['th_type']==1?"销售退货":"取消订单";
  1366. }
  1367. // $value['客户名称'] = $names['data'][$value['customer_code']];
  1368. unset($value['id']);
  1369. unset($value['th_type']);
  1370. unset($value['customer_code']);
  1371. $list[] = $value;
  1372. }
  1373. unset($all_uids);
  1374. unset($all_company);
  1375. unset($names);
  1376. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  1377. excelSave('退货明细表' . date('YmdHis'), array_keys($list[0]), $list);
  1378. }
  1379. //【六、售后明细表】
  1380. public function orderReturnDetailed()
  1381. {
  1382. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  1383. $where = [['or.is_del', '=', 0]];
  1384. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1385. if ($param['status'] != '') $where[] = ['or.status', '=', $param['status']];
  1386. $count = Db::name('order_return')
  1387. ->alias('or')
  1388. // ->leftJoin('customer_info c', 'c.companyNo=or.customer_code')
  1389. ->leftJoin('sale s', 's.orderCode=or.orderCode')
  1390. ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
  1391. ->where($where)
  1392. ->order('or.addtime', 'desc')
  1393. ->count('or.id');
  1394. $data = Db::name('order_return')
  1395. ->alias('or')
  1396. ->field('or.addtime,or.returnCode,or.apply_name,or.apply_id,or.outCode,or.total_num,or.status,or.orderCode,"" companyName,or.good_name,s.sale_price,or.is_receive,or.error_num,ri.result result_desc,or.error_remark,or.except_code,or.customer_code')
  1397. // ->leftJoin('customer_info c', 'c.companyNo=or.customer_code')
  1398. ->leftJoin('sale s', 's.orderCode=or.orderCode')
  1399. ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
  1400. ->where($where)
  1401. ->page($param['page'], $param['size'])
  1402. ->order('or.addtime', 'desc')
  1403. ->cursor();
  1404. $userCommon = \app\admin\common\User::getIns();
  1405. $list = $customer2_tmp = $uid_tmp = [];
  1406. foreach ($data as $value) {
  1407. $value['status'] = isset($this->all_order_return_status[$value['status']]) ? $this->all_order_return_status[$value['status']] : '';
  1408. $value['except_code'] = isset($this->except_code[$value['is_receive']][$value['except_code']]) ? $this->except_code[$value['is_receive']][$value['except_code']] : '';
  1409. $value['is_receive'] = isset($this->is_receive[$value['is_receive']]) ? $this->is_receive[$value['is_receive']] : '';
  1410. if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];;//get_company_name_by_uid($value['apply_id']);
  1411. // $value['apply_company'] = $uid_tmp[$value['apply_id']];
  1412. $value['error_total_price'] = round($value['error_num'] * $value['sale_price'], 2);
  1413. if (!isset($customer2_tmp[$value['customer_code']])) {
  1414. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  1415. $customer2_tmp[$value['customer_code']] = [
  1416. 'itemid' => $temp['data']['itemid'],
  1417. 'companyName' => $temp['data']['companyName'],
  1418. ];
  1419. }
  1420. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  1421. $value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
  1422. if (!empty($value['itemid'])) {
  1423. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  1424. $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  1425. $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  1426. }
  1427. $list[] = $value;
  1428. }
  1429. $all_company = get_company_name_by_uid(array_values($uid_tmp));
  1430. foreach ($list as &$val) {
  1431. $val['apply_company'] = $all_company[$val['apply_id']] ?? '';
  1432. // unset($val['apply_id']);
  1433. }
  1434. unset($customer2_tmp);
  1435. unset($uid_tmp);
  1436. unset($all_company);
  1437. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  1438. }
  1439. //【六、售后明细表】
  1440. public function orderReturnDetailedExport()
  1441. {
  1442. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => ''], 'post', 'trim');
  1443. $where = [['or.is_del', '=', 0]];
  1444. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1445. if ($param['status'] != '') $where[] = ['or.status', '=', $param['status']];
  1446. $data = Db::name('order_return')
  1447. ->alias('or')
  1448. ->field('or.addtime 售后时间,or.returnCode 售后申请编号,or.apply_name 申请人,or.apply_id,"" 部门,or.outCode 发货申请编号,or.total_num 发货数量,or.status 处理状态,or.orderCode 订单号,"" 一级组织,"" 二级组织,"" 客户名称,or.good_name 商品名称,s.sale_price 销售单价,"" 异常金额,or.is_receive 收货情况,or.error_num 异常数量,ri.result 异常原因,or.error_remark 异常备注,or.except_code 期望意愿,or.customer_code')
  1449. // ->leftJoin('customer_info c', 'c.companyNo=or.customer_code')
  1450. ->leftJoin('sale s', 's.orderCode=or.orderCode')
  1451. ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
  1452. ->where($where)
  1453. ->order('or.addtime', 'desc')
  1454. ->cursor();
  1455. $userCommon = \app\admin\common\User::getIns();
  1456. $list = $customer2_tmp = $uid_tmp = [];
  1457. foreach ($data as $value) {
  1458. $value['处理状态'] = isset($this->all_order_return_status[$value['处理状态']]) ? $this->all_order_return_status[$value['处理状态']] : '';
  1459. if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];//get_company_name_by_uid($value['apply_id']);
  1460. // $value['部门'] = $uid_tmp[$value['apply_id']];
  1461. $value['异常金额'] = round($value['异常数量'] * $value['销售单价'], 2);
  1462. if (!isset($customer2_tmp[$value['customer_code']])) {
  1463. $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
  1464. $customer2_tmp[$value['customer_code']] = [
  1465. 'itemid' => $temp['data']['itemid'],
  1466. 'companyName' => $temp['data']['companyName'],
  1467. ];
  1468. }
  1469. $value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
  1470. $value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
  1471. if (!empty($value['itemid'])) {
  1472. $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
  1473. $value['一级组织'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
  1474. $value['二级组织'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
  1475. }
  1476. $value['期望意愿'] = isset($this->except_code[$value['收货情况']][$value['期望意愿']]) ? $this->except_code[$value['收货情况']][$value['期望意愿']] : '';
  1477. $value['收货情况'] = isset($this->is_receive[$value['收货情况']]) ? $this->is_receive[$value['收货情况']] : '';
  1478. // unset($value['apply_id']);
  1479. unset($value['itemid']);
  1480. unset($value['customer_code']);
  1481. $list[] = $value;
  1482. }
  1483. $all_company = get_company_name_by_uid(array_values($uid_tmp));
  1484. foreach ($list as &$val) {
  1485. $val['部门'] = $all_company[$val['apply_id']] ?? '';
  1486. unset($val['apply_id']);
  1487. }
  1488. unset($customer2_tmp);
  1489. unset($uid_tmp);
  1490. unset($all_company);
  1491. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  1492. excelSave('售后明细表' . date('YmdHis'), array_keys($list[0]), $list);
  1493. }
  1494. //【七、议价明细表】
  1495. public function bargainOrder()
  1496. {
  1497. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
  1498. $where = [['bo.is_del', '=', 0]];
  1499. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['bo.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1500. $count = Db::name('bargain_order')
  1501. ->alias('bo')
  1502. ->leftJoin('consult_info ci', 'ci.infoNo=bo.infoNo')
  1503. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  1504. // ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
  1505. ->leftJoin('result_info ri', 'ri.result_code=bo.result_info_id')
  1506. ->where($where)
  1507. ->order('bo.addtime', 'desc')
  1508. ->count('bo.id');
  1509. $data = Db::name('bargain_order')
  1510. ->alias('bo')
  1511. ->field('bo.addtime,bo.bargainNo,bo.infoNo,bo.status,bo.bidsNo,"" companyName,ci.good_name,bo.creater,bo.createrid,bo.sale_price,bo.after_price,ri.result result_desc,bo.bargain_remark,co.khNo')
  1512. ->leftJoin('consult_info ci', 'ci.infoNo=bo.infoNo')
  1513. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  1514. // ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
  1515. ->leftJoin('result_info ri', 'ri.result_code=bo.result_info_id')
  1516. ->where($where)
  1517. ->page($param['page'], $param['size'])
  1518. ->order('bo.addtime', 'desc')
  1519. ->cursor();
  1520. $list = $codes = $uid_tmp = [];
  1521. foreach ($data as $value) {
  1522. $value['status'] = isset($this->bargain_order_status[$value['status']]) ? $this->bargain_order_status[$value['status']] : '';
  1523. if (!isset($uid_tmp[$value['createrid']])) $uid_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);
  1524. // $value['creater_company'] = $uid_tmp[$value['createrid']];
  1525. if (!isset($codes[$value['khNo']])) $codes[$value['khNo']] = $value['khNo'];
  1526. $list[] = $value;
  1527. }
  1528. //补充客户名称
  1529. $userCommon = \app\admin\common\User::getIns();
  1530. $names = $userCommon->handle('getCodeAndName', ['code' => $codes]);
  1531. $all_company = get_company_name_by_uid(array_values($uid_tmp));
  1532. foreach ($list as &$val) {
  1533. $val['companyName'] = $names['data'][$val['khNo']];
  1534. $val['creater_company'] = $all_company[$val['createrid']] ?? '';
  1535. }
  1536. unset($codes);
  1537. unset($uid_tmp);
  1538. unset($userCommon);
  1539. unset($names);
  1540. unset($all_company);
  1541. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  1542. }
  1543. //【七、议价明细表】
  1544. public function bargainOrderExport()
  1545. {
  1546. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => ''], 'post', 'trim');
  1547. $where = [['bo.is_del', '=', 0]];
  1548. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['bo.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1549. $data = Db::name('bargain_order')
  1550. ->alias('bo')
  1551. ->field('bo.addtime 议价时间,bo.bargainNo 议价编号,bo.infoNo 咨询单编号,bo.status 议价单状态,bo.bidsNo 采返商品编号,"" 客户名称,ci.good_name 商品名称,bo.creater 申请人,"" 部门,bo.createrid,bo.sale_price 议价前售价,bo.after_price 期望售价,ri.result 议价原因,bo.bargain_remark 议价备注,co.khNo')
  1552. ->leftJoin('consult_info ci', 'ci.infoNo=bo.infoNo')
  1553. ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
  1554. // ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
  1555. ->leftJoin('result_info ri', 'ri.result_code=bo.result_info_id')
  1556. ->where($where)
  1557. ->order('bo.addtime', 'desc')
  1558. ->cursor();
  1559. $list = $codes = $uid_tmp = [];
  1560. foreach ($data as $value) {
  1561. if (!isset($codes[$value['khNo']])) $codes[$value['khNo']] = $value['khNo'];
  1562. if (!isset($uid_tmp[$value['createrid']])) $uid_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);
  1563. $value['议价单状态'] = isset($this->bargain_order_status[$value['议价单状态']]) ? $this->bargain_order_status[$value['议价单状态']] : '';
  1564. // $value['部门'] = $uid_tmp[$value['createrid']];
  1565. // unset($value['createrid']);
  1566. $list[] = $value;
  1567. }
  1568. //补充客户名称
  1569. $userCommon = \app\admin\common\User::getIns();
  1570. $names = $userCommon->handle('getCodeAndName', ['code' => $codes]);
  1571. $all_company = get_company_name_by_uid(array_values($uid_tmp));
  1572. foreach ($list as &$val) {
  1573. $val['客户名称'] = $names['data'][$val['khNo']];
  1574. $val['部门'] = $all_company[$val['createrid']] ?? '';
  1575. unset($val['createrid']);
  1576. unset($val['khNo']);
  1577. }
  1578. unset($codes);
  1579. unset($uid_tmp);
  1580. unset($userCommon);
  1581. unset($names);
  1582. unset($all_company);
  1583. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  1584. excelSave('议价明细表' . date('YmdHis'), array_keys($list[0]), $list);
  1585. }
  1586. //【八、延时发货申请单统计】
  1587. public function orderOutNotAddr()
  1588. {
  1589. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');
  1590. $where = [['a.is_del', '=', 0], ['b.send_type', '=', 2], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==2 延迟发货,a.status==4 已全部退货
  1591. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1592. if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
  1593. if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
  1594. // if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
  1595. if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];
  1596. $count = Db::name('order_out')
  1597. ->alias('a')
  1598. ->leftJoin("sale b", "b.orderCode=a.orderCode")
  1599. ->leftJoin("order_send os", "os.outCode=a.outCode")
  1600. ->leftJoin("purchease_order d", "d.cgdNo=os.cgdNo")
  1601. ->order("a.addtime desc")
  1602. ->where($where)
  1603. ->count('a.id');
  1604. $data = Db::name('order_out')
  1605. ->alias('a')
  1606. ->field('a.addtime,a.orderCode,os.cgdNo,"" company,a.apply_name,a.apply_id,a.status,a.send_num,b.good_code,b.good_name,"" companyName,b.wsend_num,b.skuCode,b.order_type,""person,b.customer_code,b.supplierNo companyNo,b.supNo as supplierNo')
  1607. ->leftJoin("sale b", "b.orderCode=a.orderCode")
  1608. ->leftJoin("order_send os", "os.outCode=a.outCode")
  1609. ->leftJoin("purchease_order d", "d.cgdNo=os.cgdNo")
  1610. ->page($param['page'], $param['size'])
  1611. ->where($where)
  1612. ->order("a.addtime desc")
  1613. ->cursor();
  1614. $list = $tmp_apply = $tmp_cat = $tmp_person = $tmp_codes = [];
  1615. $userCommon = \app\admin\common\User::getIns();
  1616. foreach ($data as $value) {
  1617. if (!isset($tmp_apply[$value['apply_id']])) $tmp_apply[$value['apply_id']] = $value['apply_id'];
  1618. if (!isset($tmp_codes[$value['customer_code']])) $tmp_codes[$value['customer_code']] = $value['customer_code'];
  1619. if (!isset($tmp_codes[$value['companyNo']])) $tmp_codes[$value['companyNo']] = $value['companyNo'];
  1620. if (!isset($tmp_person[$value['supplierNo']])) {
  1621. $tmp = $userCommon->handle('sInfo', ['code' => $value['supplierNo']]);
  1622. $tmp_person[$value['supplierNo']] = $tmp['data']['person']??"";
  1623. }
  1624. $value['person'] = $tmp_person[$value['supplierNo']];
  1625. if ($value['order_type'] == 3 || $value['order_type'] == 4) {
  1626. $goon = Db::name("good_zixun")
  1627. ->field('id,cat_id,customized')
  1628. ->where(["spuCode" => $value['good_code'], "is_del" => 0])
  1629. ->find();
  1630. } else {
  1631. $goon = Db::name('good_platform')
  1632. ->alias('a')
  1633. ->field('a.id,b.cat_id,b.customized')
  1634. ->join('good b', 'b.spuCode=a.spuCode', 'left')
  1635. ->where(['a.is_del' => 0, 'a.skuCode' => $value['skuCode']])
  1636. ->find();
  1637. }
  1638. if (!isset($tmp_cat[$goon['cat_id']])) $tmp_cat[$goon['cat_id']] = implode('/', array_column(made($goon['cat_id']), 'name'));
  1639. $value['cat'] = $tmp_cat[$goon['cat_id']];
  1640. $value['send_date'] = date('Y-m-d H:i:s', strtotime($value['addtime']) + $goon['customized'] * 24 * 3600);
  1641. $value['status'] = isset($this->order_out_status[$value['status']]) ? $this->order_out_status[$value['status']] : '';
  1642. $list[] = $value;
  1643. }
  1644. $names = $userCommon->handle('getCodeAndName', ['code' => $tmp_codes]);
  1645. $apply_company = get_company_name_by_uid($tmp_apply);
  1646. foreach ($list as &$val) {
  1647. $val['companyName'] = $names['data'][$val['customer_code']] ?? '';
  1648. $val['company'] = $names['data'][$val['companyNo']] ?? '';
  1649. $val['apply_company'] = $apply_company[$val['apply_id']] ?? '';
  1650. }
  1651. unset($tmp_apply);
  1652. unset($tmp_cat);
  1653. unset($tmp_person);
  1654. unset($tmp_codes);
  1655. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  1656. }
  1657. //【八、延时发货申请单统计】
  1658. public function orderOutNotAddrExport()
  1659. {
  1660. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');
  1661. $where = [['a.is_del', '=', 0], ['b.send_type', '=', 2], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==2 延迟发货,a.status==4 已全部退货
  1662. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1663. if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
  1664. if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
  1665. // if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
  1666. if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];
  1667. $data = Db::name('order_out')
  1668. ->alias('a')
  1669. ->field('a.addtime 下单时间,a.orderCode 订单编号,os.cgdNo 采购单编号,"" 公司名称,a.apply_name 申请人,"" 部门,a.apply_id,a.status 状态,a.send_num 出库总数,"" 商品分类,b.good_code 商品编码,b.good_name 商品名称,"" 客户名称,b.wsend_num 未发货数量,"" 应发货日期,b.skuCode,b.order_type 订单类型,"" 采购员,b.customer_code,b.supplierNo companyNo,b.supNo as supplierNo')
  1670. ->leftJoin("sale b", "b.orderCode=a.orderCode")
  1671. ->leftJoin("order_send os", "os.outCode=a.outCode")
  1672. ->leftJoin("purchease_order d", "d.cgdNo=os.cgdNo")
  1673. ->where($where)
  1674. ->order("a.addtime desc")
  1675. ->cursor();
  1676. $userCommon = \app\admin\common\User::getIns();
  1677. $list = $tmp_apply = $tmp_cat = $tmp_person = $tmp_codes = [];
  1678. foreach ($data as $value) {
  1679. if (!isset($tmp_codes[$value['customer_code']])) $tmp_codes[$value['customer_code']] = $value['customer_code'];
  1680. if (!isset($tmp_codes[$value['companyNo']])) $tmp_codes[$value['companyNo']] = $value['companyNo'];
  1681. if (!isset($tmp_apply[$value['apply_id']])) $tmp_apply[$value['apply_id']] = $value['apply_id'];// get_company_name_by_uid($value['apply_id']);
  1682. if (!isset($tmp_person[$value['supplierNo']])) {
  1683. $tmp = $userCommon->handle('sInfo', ['code' => $value['supplierNo']]);
  1684. $tmp_person[$value['supplierNo']] = $tmp['data']['person']??"";
  1685. }
  1686. $value['采购员'] = $tmp_person[$value['supplierNo']];
  1687. if ($value['订单类型'] == 3 || $value['订单类型'] == 4) {
  1688. $goon = Db::name("good_zixun")
  1689. ->field('id,cat_id,customized')
  1690. ->where(["spuCode" => $value['商品编码'], "is_del" => 0])
  1691. ->find();
  1692. } else {
  1693. $goon = Db::name('good_platform')
  1694. ->alias('a')
  1695. ->field('a.id,b.cat_id,b.customized')
  1696. ->join('good b', 'b.spuCode=a.spuCode', 'left')
  1697. ->where(['a.is_del' => 0, 'a.skuCode' => $value['skuCode']])
  1698. ->find();
  1699. }
  1700. if (!isset($tmp_cat[$goon['cat_id']])) $tmp_cat[$goon['cat_id']] = implode('/', array_column(made($goon['cat_id']), 'name'));
  1701. $value['商品分类'] = $tmp_cat[$goon['cat_id']];
  1702. $value['应发货日期'] = date('Y-m-d H:i:s', strtotime($value['下单时间']) + $goon['customized'] * 24 * 3600);
  1703. $value['状态'] = isset($this->order_out_status[$value['状态']]) ? $this->order_out_status[$value['状态']] : '';
  1704. $value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';
  1705. // unset($value['apply_id']);
  1706. unset($value['skuCode']);
  1707. $list[] = $value;
  1708. }
  1709. $names = $userCommon->handle('getCodeAndName', ['code' => $tmp_codes]);
  1710. $apply_company = get_company_name_by_uid(array_values($tmp_apply));
  1711. foreach ($list as &$val) {
  1712. $val['客户名称'] = $names['data'][$val['customer_code']] ?? '';
  1713. $val['公司名称'] = $names['data'][$val['companyNo']] ?? '';
  1714. $value['部门'] = $apply_company[$val['apply_id']] ?? '';
  1715. unset($val['customer_code']);
  1716. unset($val['companyNo']);
  1717. unset($val['apply_id']);
  1718. }
  1719. unset($tmp_apply);
  1720. unset($tmp_cat);
  1721. unset($tmp_person);
  1722. unset($tmp_codes);
  1723. unset($names);
  1724. unset($apply_company);
  1725. unset($userCommon);
  1726. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  1727. excelSave('延时发货申请单' . date('YmdHis'), array_keys($list[0]), $list);
  1728. }
  1729. //【九、直接发货申请单统计 】
  1730. public function orderOutAddr()
  1731. {
  1732. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');
  1733. $where = [['a.is_del', '=', 0], ['b.send_type', '=', 1], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==1 直接发货
  1734. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1735. if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
  1736. if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
  1737. // if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
  1738. if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];
  1739. $count = Db::name('order_out')
  1740. ->alias('a')
  1741. ->leftJoin("sale b", "b.orderCode=a.orderCode")
  1742. ->leftJoin("order_send os", "os.outCode=a.outCode")
  1743. ->leftJoin("purchease_order po", "po.cgdNo=os.cgdNo")
  1744. ->leftJoin("order_addr oa", "oa.id=a.addrid")
  1745. // ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
  1746. // ->leftJoin("company_item ci", "ci.id=u.itemid")
  1747. // ->leftJoin("supplier c", "c.code=po.supplierNo")
  1748. ->where($where)
  1749. ->order("a.addtime desc")
  1750. ->count('a.id');
  1751. $data = Db::name('order_out')
  1752. ->alias('a')
  1753. ->field('a.addtime,a.orderCode,a.sendtime,a.outCode,a.status,b.good_name,b.sale_price,a.send_num,a.apply_name,"" apply_company,"" cgder,a.post_name,a.post_code,oa.contactor,oa.mobile,oa.addr,oa.addr_code,a.remark,b.order_type,po.supplierNo,a.apply_id')
  1754. ->leftJoin("sale b", "b.orderCode=a.orderCode")
  1755. ->leftJoin("order_send os", "os.outCode=a.outCode")
  1756. ->leftJoin("purchease_order po", "po.cgdNo=os.cgdNo")
  1757. ->leftJoin("order_addr oa", "oa.id=a.addrid")
  1758. // ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
  1759. // ->leftJoin("company_item ci", "ci.id=u.itemid")
  1760. // ->leftJoin("supplier c", "c.code=po.supplierNo")
  1761. ->where($where)
  1762. ->page($param['page'], $param['size'])
  1763. ->order("a.addtime desc")
  1764. ->cursor();
  1765. $list = $tmp = [];
  1766. foreach ($data as $value) {
  1767. if (!isset($tmp[$value['apply_id']])) $tmp[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
  1768. $value['apply_company']=$tmp[$value['apply_id']] ?? '';
  1769. $value['send_total_price'] = round($value['send_num'] * $value['sale_price'], 2);
  1770. $value['status'] = isset($this->order_out_status[$value['status']]) ? $this->order_out_status[$value['status']] : '';
  1771. //拼接省市区
  1772. if (!json_decode($value['addr_code'])) {
  1773. $temp = explode(',', $value['addr_code']);
  1774. $value['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
  1775. }
  1776. $value['addr'] = GetAddr($value['addr_code']) . $value['addr'];
  1777. if (!isset($tmp[$value['supplierNo']])) $tmp[$value['supplierNo']] = $value['supplierNo'];
  1778. $list[] = $value;
  1779. }
  1780. $person = \app\admin\common\User::getIns()->handle('sGetList', ['more_code' => array_values($tmp), 'size' => count($tmp)]);
  1781. $person = array_column($person['data']['list'], 'person', 'code');
  1782. foreach ($list as &$value) {
  1783. $value['cgder'] = $person[$value['supplierNo']] ?? '';
  1784. }
  1785. unset($tmp);
  1786. unset($person);
  1787. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  1788. }
  1789. //【九、直接发货申请单统计-导出】
  1790. public function orderOutAddrExport()
  1791. {
  1792. $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'order_type' => ''], 'post', 'trim');
  1793. $where = [['a.is_del', '=', 0], ['b.send_type', '=', 1], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==1 直接发货
  1794. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1795. if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
  1796. if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
  1797. if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
  1798. if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];
  1799. $data = Db::name('order_out')
  1800. ->alias('a')
  1801. ->field('a.addtime 下单日期,a.orderCode 订单编号,a.sendtime 发货时间,a.outCode 发货申请单号,a.status 发货申请单状态,b.good_name 商品名称,b.sale_price 产品单价,a.send_num 发货数量,"" 发货申请单总价,a.apply_name 申请人,"" 部门,"" 采购员,a.post_name 快递公司,a.post_code 快递单号,oa.contactor 收货人,oa.mobile 收货电话,oa.addr 收货地址,oa.addr_code,a.remark 备注,b.order_type 订单类型,po.supplierNo,a.apply_id')
  1802. ->leftJoin("sale b", "b.orderCode=a.orderCode")
  1803. ->leftJoin("order_send os", "os.outCode=a.outCode")
  1804. ->leftJoin("purchease_order po", "po.cgdNo=os.cgdNo")
  1805. ->leftJoin("order_addr oa", "oa.id=a.addrid")
  1806. // ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
  1807. // ->leftJoin("company_item ci", "ci.id=u.itemid")
  1808. // ->leftJoin("supplier c", "c.code=po.supplierNo")
  1809. ->where($where)
  1810. ->order("a.addtime desc")
  1811. ->cursor();
  1812. $list = $tmp = [];
  1813. foreach ($data as $value) {
  1814. if (!isset($tmp[$value['apply_id']])) $tmp[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
  1815. $value['部门']=$tmp[$value['apply_id']] ?? '';
  1816. $value['发货申请单总价'] = round(bcmul($value['发货数量'], $value['产品单价'], 3), 2);
  1817. $value['发货申请单状态'] = isset($this->order_out_status[$value['发货申请单状态']]) ? $this->order_out_status[$value['发货申请单状态']] : '';
  1818. $value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';
  1819. //拼接省市区
  1820. if (!json_decode($value['addr_code'])) {
  1821. $temp = explode(',', $value['addr_code']);
  1822. $value['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
  1823. }
  1824. $value['收货地址'] = GetAddr($value['addr_code']) . $value['收货地址'];
  1825. if (!isset($tmp[$value['supplierNo']])) $tmp[$value['supplierNo']] = $value['supplierNo'];
  1826. unset($value['addr_code']);
  1827. unset($value['apply_id']);
  1828. $list[] = $value;
  1829. }
  1830. $person = \app\admin\common\User::getIns()->handle('sGetList', ['more_code' => array_values($tmp), 'size' => count($tmp)]);
  1831. $person = array_column($person['data']['list'], 'person', 'code');
  1832. foreach ($list as &$value) {
  1833. $value['采购员'] = $person[$value['supplierNo']] ?? '';
  1834. }
  1835. unset($tmp);
  1836. unset($person);
  1837. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  1838. excelSave('直接发货申请单统计' . date('YmdHis'), array_keys($list[0]), $list);
  1839. }
  1840. //【十、无地址销售订单】
  1841. //@todo 待处理,2023-02-08 此处用户及所属部门应该修改,但是困难之处在于数据是依据u.nickname分组的,是否可以按照a.apply_id分组,并在查询之后补充用户昵称和所属部门?
  1842. public function saleNotAddr()
  1843. {
  1844. $param = $this->request->only(['token',
  1845. 'start_date' => '',
  1846. 'end_date' => '',
  1847. 'status' => '',
  1848. 'apply_company' => '',
  1849. "supplierNo"=>"",
  1850. "depart_id"=>"",
  1851. "companyNo"=>"",
  1852. 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');
  1853. $where = [['a.send_type', '=', 2], ['a.is_del', '=', 0], ['a.status', '<>', 3], ['a.send_status', '<>', 3]];
  1854. //send_type==2 延迟发货
  1855. if ($param['depart_id'] != ''){
  1856. $uidArr = get_company_item_user_by_name("",$param['depart_id']);
  1857. $where[] = ['a.apply_id', 'in',$uidArr];
  1858. }
  1859. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1860. if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
  1861. // if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
  1862. if ($param['order_type'] != '') $where[] = ['a.order_type', '=', $param['order_type']];
  1863. if ($param['supplierNo'] != '') $where[] = ['a.supNo', 'like', "%{$param['supplierNo']}%"];
  1864. if ($param['companyNo'] != '') $where[] = ['a.supplierNo', 'like',"%{$param['companyNo']}%"];
  1865. $count_row = Db::name('sale')
  1866. ->alias('a')
  1867. ->where($where)
  1868. ->field(" a.good_num-th_num - IFNULL((select sum(receipt_quantity) from wsm_order_addr where orderCode=a.orderCode and is_del=0),0) not_addr_address_good_num,count(a.id) as count")
  1869. ->having("not_addr_address_good_num>0")
  1870. ->find();
  1871. $data = Db::name('sale')
  1872. ->alias('a')
  1873. ->field("a.id,
  1874. a.orderCode,
  1875. a.order_type,
  1876. a.good_code,
  1877. a.skuCode,
  1878. good_name,
  1879. a.good_num,
  1880. a.th_num,
  1881. a.send_num,
  1882. a.wsend_num,
  1883. a.status,
  1884. a.good_num-a.th_num - IFNULL((select sum(receipt_quantity) from wsm_order_addr where orderCode=a.orderCode and is_del=0),0) not_addr_address_good_num,
  1885. a.apply_name nickname,
  1886. a.apply_id,
  1887. a.addtime")
  1888. ->where($where)
  1889. ->page($param['page'], $param['size'])
  1890. ->order("a.addtime desc")
  1891. ->having("not_addr_address_good_num>0")
  1892. ->select()
  1893. ->toArray();
  1894. $applyAr = array_unique(array_column($data,"apply_id")) ;
  1895. $depart =get_company_name_by_uid($applyAr);
  1896. foreach ($data as &$value){
  1897. $value['name']=$depart[$value['apply_id']]??"";
  1898. $value['month']=date("m",strtotime($value['addtime']));
  1899. }
  1900. return app_show(0, '请求成功', ['list' => $data, 'count' => $count_row['count']]);
  1901. }
  1902. //【十、无地址销售订单-导出】
  1903. public function saleNotAddrExport()
  1904. {
  1905. $param = $this->request->only(['token',
  1906. 'start_date' => '',
  1907. 'end_date' => '',
  1908. "supplierNo"=>"",
  1909. "companyNo"=>"",
  1910. "depart_id"=>"",
  1911. 'status' => '',
  1912. 'apply_company' => '',
  1913. 'order_type' => ''], 'post', 'trim');
  1914. $where = [['a.send_type', '=', 2], ['a.is_del', '=', 0], ['a.status', '<>', 3],['a.send_status', '<>', 3]];//send_type==2 延迟发货
  1915. if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
  1916. if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
  1917. if ($param['depart_id'] != ''){
  1918. $uidArr = get_company_item_user_by_name("",$param['depart_id']);
  1919. $where[] = ['a.apply_id', 'in',$uidArr];
  1920. }
  1921. // if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
  1922. if ($param['order_type'] != '') $where[] = ['a.order_type', '=', $param['order_type']];
  1923. if ($param['supplierNo'] != '') $where[] = ['a.supNo', 'like', "%{$param['supplierNo']}%"];
  1924. if ($param['companyNo'] != '') $where[] = ['a.supplierNo', 'like',"%{$param['companyNo']}%"];
  1925. $data = Db::name('sale')
  1926. ->alias('a')
  1927. ->field("a.orderCode 订单编号,a.order_type 订单类型,
  1928. a.good_code 商品成本编码,a.skuCode 商品上线编码,a.good_name 商品名称,
  1929. a.good_num 购买数量,a.send_num 已发货数量,a.wsend_num 未发货数量,a.status 订单状态,
  1930. a.apply_name 申请人名称,a.apply_id 申请人所属部门,
  1931. a.good_num-a.th_num - IFNULL((select sum(receipt_quantity) from wsm_order_addr where orderCode=a.orderCode and is_del=0),0) 无地址数量,
  1932. '' 下单月份,
  1933. a.addtime")
  1934. ->where($where)
  1935. ->order("a.addtime desc")
  1936. ->cursor();
  1937. $list = [];
  1938. $depart=[];
  1939. foreach ($data as $value) {
  1940. if(!isset($depart[$value['申请人所属部门']])){
  1941. $departArr =get_company_name_by_uid($value['申请人所属部门']);
  1942. $depart[$value['申请人所属部门']] = $departArr??"";
  1943. }
  1944. $value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';
  1945. $value['订单状态'] = isset($this->all_sale_status[$value['订单状态']]) ? $this->all_sale_status[$value['订单状态']] : '';
  1946. $value['申请人所属部门']=$depart[$value['申请人所属部门']]??"";
  1947. $value['下单月份']=date("m",strtotime($value['addtime']));
  1948. unset($value['addtime']);
  1949. $list[] = $value;
  1950. }
  1951. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  1952. excelSave('无地址销售订单列表' . date('YmdHis'), array_keys($list[0]), $list);
  1953. }
  1954. //【十一、销售订单商品类型】
  1955. public function saleMonth()
  1956. {
  1957. $param = $this->request->only(['token', 'apply_company' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  1958. $where = [['a.is_del', '=', 0]];
  1959. // if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
  1960. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  1961. $data = Db::name('sale')
  1962. ->alias('a')
  1963. ->field('a.id,a.order_type,a.apply_id')
  1964. // ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
  1965. // ->leftJoin("company_item ci", "ci.id=u.itemid")
  1966. ->where($where)
  1967. ->cursor();
  1968. $list = $tmp=[];
  1969. $total = ['name' => '总计', 'tag_1' => 0, 'tag_2' => 0, 'tag_3' => 0, 'tag_total' => 0];
  1970. foreach ($data as $val) {
  1971. if (!isset($tmp[$val['apply_id']])) $tmp[$val['apply_id']] = get_company_name_by_uid($val['apply_id']);
  1972. $val['company_name']=$tmp[$val['apply_id']] ?? '';
  1973. if (!isset($list[$val['company_name']])) $list[$val['company_name']] = [
  1974. 'name' => $val['company_name'],
  1975. 'tag_1' => 0,
  1976. 'tag_2' => 0,
  1977. 'tag_3' => 0,
  1978. 'tag_total' => 0,
  1979. ];
  1980. $list[$val['company_name']]['tag_' . $val['order_type']]++;
  1981. $list[$val['company_name']]['tag_total']++;
  1982. $total['tag_' . $val['order_type']]++;
  1983. $total['tag_total']++;
  1984. }
  1985. $list = array_merge($list);//重置下标
  1986. $list[] = $total;
  1987. return app_show(0, '请求成功', $list);
  1988. }
  1989. //【十一、销售订单商品类型-导出】
  1990. public function saleMonthExport()
  1991. {
  1992. $param = $this->request->only(['token', 'apply_company' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  1993. $where = [['a.is_del', '=', 0]];
  1994. if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
  1995. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  1996. $data = Db::name('sale')
  1997. ->alias('a')
  1998. ->field('a.id,a.order_type,a.apply_id')
  1999. // ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
  2000. // ->leftJoin("company_item ci", "ci.id=u.itemid")
  2001. ->where($where)
  2002. ->cursor();
  2003. $list =$tmp= [];
  2004. $total = ['部门' => '总计', '库存品' => 0, '非库存品' => 0, '咨询采反' => 0, '总计' => 0];
  2005. foreach ($data as $k => $val) {
  2006. if (!isset($tmp[$val['apply_id']])) $tmp[$val['apply_id']] = get_company_name_by_uid($val['apply_id']);
  2007. $val['company_name']=$tmp[$val['apply_id']] ?? '';
  2008. if (!isset($list[$val['company_name']])) $list[$val['company_name']] = ['部门' => $val['company_name'], '库存品' => 0, '非库存品' => 0, '咨询采反' => 0, '总计' => 0];
  2009. switch ($val['order_type']) {
  2010. case 1:
  2011. $list[$val['company_name']]['库存品']++;
  2012. $total['库存品']++;
  2013. break;
  2014. case 2:
  2015. $list[$val['company_name']]['非库存品']++;
  2016. $total['非库存品']++;
  2017. break;
  2018. case 3:
  2019. $list[$val['company_name']]['咨询采反']++;
  2020. $total['咨询采反']++;
  2021. break;
  2022. }
  2023. $list[$val['company_name']]['总计']++;
  2024. $total['总计']++;
  2025. }
  2026. $list = array_merge($list);//重置下标
  2027. $list[] = $total;
  2028. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  2029. excelSave('销售订单商品类型' . date('YmdHis'), array_keys($list[0]), $list);
  2030. }
  2031. //【十二、采购订单发货维度表】
  2032. public function orderOutMonth()
  2033. {
  2034. $param = $this->request->only(['token', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  2035. $where = [['a.is_del', '=', 0], ['b.is_del', '=', 0]];
  2036. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2037. $data = Db::name('order_out')
  2038. ->alias('a')
  2039. ->field('a.id,b.order_type,b.cat_id,d.supplierNo')
  2040. ->leftJoin('sale b', 'b.orderCode=a.orderCode')
  2041. ->leftJoin('order_send c', 'c.outCode=a.outCode')
  2042. ->leftJoin('purchease_order d', 'd.cgdNo=c.cgdNo')
  2043. ->where($where)
  2044. ->cursor();
  2045. $list = [];
  2046. // $total = ['cat_1' => '总计', 'cat_2' => '', 'cat_3' => '', 'tag_1' => 0, 'tag_2' => 0, 'tag_3' => 0, 'tag_supplier' => 0, 'tag_person' => 0, 'tag_logistics' => 0, 'tag_total' => 0];
  2047. // $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
  2048. // ->group('supplierNo')
  2049. // ->column('supplierNo');
  2050. $has_sup_acc = checkHasAccountBySupplierNos();
  2051. foreach ($data as $val) {
  2052. if (!isset($list[$val['cat_id']])) {
  2053. $cat = made($val['cat_id']);
  2054. $list[$val['cat_id']] = [
  2055. 'cat_1' => $cat[0]['name'] ?? '',
  2056. 'cat_2' => $cat[1]['name'] ?? '',
  2057. 'cat_3' => $cat[2]['name'] ?? '',
  2058. 'tag_1' => 0,
  2059. 'tag_2' => 0,
  2060. 'tag_3' => 0,
  2061. 'tag_supplier' => 0,
  2062. 'tag_person' => 0,
  2063. 'tag_logistics' => 0,
  2064. ];
  2065. }
  2066. switch ($val['order_type']) {
  2067. case 1:
  2068. //库存品,物流岗发
  2069. $list[$val['cat_id']]['tag_logistics']++;
  2070. break;
  2071. case 2: //非库存品
  2072. case 3: //咨询采反
  2073. if (in_array($val['supplierNo'], $has_sup_acc)) $list[$val['cat_id']]['tag_supplier']++;
  2074. else $list[$val['cat_id']]['tag_person']++;
  2075. break;
  2076. }
  2077. $list[$val['cat_id']]['tag_' . $val['order_type']]++;
  2078. // $list[$val['cat_id']]['tag_total']++;
  2079. // $total['tag_' . $val['order_type']]++;
  2080. // $total['tag_total']++;
  2081. }
  2082. $list = array_merge($list);//重置下标
  2083. // $list[] = $total;
  2084. return app_show(0, '请求成功', $list);
  2085. }
  2086. //【十二、采购订单发货维度表-导出】
  2087. public function orderOutMonthExport()
  2088. {
  2089. $param = $this->request->only(['token', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  2090. $where = [['a.is_del', '=', 0], ['b.is_del', '=', 0]];
  2091. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2092. $data = Db::name('order_out')
  2093. ->alias('a')
  2094. ->field('a.id,b.order_type,b.cat_id,d.supplierNo')
  2095. ->leftJoin("sale b", "b.orderCode=a.orderCode")
  2096. ->leftJoin('order_send c', 'c.outCode=a.outCode')
  2097. ->leftJoin('purchease_order d', 'd.cgdNo=c.cgdNo')
  2098. ->where($where)
  2099. ->cursor();
  2100. $has_sup_acc = checkHasAccountBySupplierNos();
  2101. // $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
  2102. // ->group('supplierNo')
  2103. // ->column('supplierNo');
  2104. $list = [];
  2105. // $total = ['一级分类' => '总计', '二级分类' => '', '三级分类' => '', '库存品' => 0, '非库存品' => 0, '咨询采反' => 0, '总计' => 0];
  2106. foreach ($data as $val) {
  2107. if (!isset($list[$val['cat_id']])) {
  2108. $cat = made($val['cat_id']);
  2109. $list[$val['cat_id']] = [
  2110. '一级分类' => $cat[0]['name'] ?? '',
  2111. '二级分类' => $cat[1]['name'] ?? '',
  2112. '三级分类' => $cat[2]['name'] ?? '',
  2113. '库存品' => 0,
  2114. '非库存品' => 0,
  2115. '咨询采反' => 0,
  2116. '供应商发货' => 0,
  2117. '负责人发货' => 0,
  2118. '物流岗发货' => 0,
  2119. // '总计' => 0,
  2120. ];
  2121. }
  2122. switch ($val['order_type']) {
  2123. case 1:
  2124. $list[$val['cat_id']]['库存品']++;
  2125. // $total['库存品']++;
  2126. //物流岗发
  2127. $list[$val['cat_id']]['物流岗发货']++;
  2128. break;
  2129. case 2:
  2130. $list[$val['cat_id']]['非库存品']++;
  2131. // $total['非库存品']++;
  2132. if (in_array($val['supplierNo'], $has_sup_acc)) $list[$val['cat_id']]['供应商发货']++;
  2133. else $list[$val['cat_id']]['负责人发货']++;
  2134. break;
  2135. case 3:
  2136. $list[$val['cat_id']]['咨询采反']++;
  2137. if (in_array($val['supplierNo'], $has_sup_acc)) $list[$val['cat_id']]['供应商发货']++;
  2138. else $list[$val['cat_id']]['负责人发货']++;
  2139. // $total['咨询采反']++;
  2140. break;
  2141. }
  2142. // $list[$val['cat_id']]['总计']++;
  2143. // $total['总计']++;
  2144. }
  2145. $list = array_merge($list);//重置下标
  2146. // $list[] = $total;
  2147. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  2148. excelSave('采购订单发货维度表' . date('YmdHis'), array_keys($list[0]), $list);
  2149. }
  2150. //【十三、供应商采购订单情况】
  2151. public function purcheaseOrderMonth()
  2152. {
  2153. $param = $this->request->only(['token',
  2154. 'supplierNo' => '',
  2155. 'companyNo' => '',
  2156. 'supplier_name' => '',
  2157. 'status' => '',
  2158. 'person_id' => '',
  2159. 'start_day' => date('Y-m-01'),
  2160. 'end_day' => date('Y-m-t')], 'post', 'trim');
  2161. $where = [['a.is_del', '=', 0],['a.status',"<>",4]];
  2162. if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
  2163. if ($param['companyNo'] != '') $where[] = ['a.companyNo', 'like', '%' . $param['companyNo'] . '%'];
  2164. if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
  2165. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2166. if($param['status']!==''){
  2167. $where[]=["a.status","=",$param['status']];
  2168. }
  2169. if($param['person_id']!==''){
  2170. $where[]=["a.cgder_id","=",$param['person_id']];
  2171. }
  2172. $data = Db::name('purchease_order')
  2173. ->alias('a')
  2174. ->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
  2175. ->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
  2176. ->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
  2177. ->where($where)
  2178. ->group("a.supplierNo,a.supplier_name,a.order_type,p.use_type,a.order_source,a.status")
  2179. ->field("a.supplierNo,a.supplier_name,a.order_type,p.use_type,a.order_source,a.status,count(a.id) as total_cgd,sum(total_fee) as total_fee,sum(a.wsend_num*a.good_price) as wtotal_fee")
  2180. ->select()
  2181. ->toArray();
  2182. $list = [];
  2183. $total = ['supplier_name' => '总计',
  2184. "is_supplier"=>'',//是否开通供应商端
  2185. "person"=>'', //供应商负责人
  2186. 'total_cgd' => 0,//订单总量
  2187. 'total_fee' => 0,//订单总金额
  2188. 'dtotal_fee' => 0,//待确认订单总金额
  2189. 'dtotal_cgd' => 0,//待确认订单总量
  2190. 'wtotal_fee' => 0,//待入库订单总金额
  2191. 'wtotal_cgd' => 0,//待入库订单总量
  2192. 'tag_1' => 0,//库存品
  2193. 'tag_2' => 0,//非库存品
  2194. 'tag_c' => 0,//to C
  2195. 'tag_b' => 0,//to B
  2196. 'tag_zx' => 0,//咨询采反总数
  2197. ];
  2198. $supArr=array_unique(array_column($data,'supplierNo'));
  2199. $supplier =checkHasAccountBySupplierNos($supArr);
  2200. $pers =get_personid_by_supplierNo($supArr,"person");
  2201. foreach ($data as $val) {
  2202. if (!isset($list[$val['supplierNo']])) {
  2203. $list[$val['supplierNo']] = [
  2204. 'supplier_name' => $val['supplier_name'],
  2205. "is_supplier"=>isset($supplier[$val['supplierNo']]) ?'是' :'否',//是否开通供应商端
  2206. "person"=>$pers[$val['supplierNo']]??"", //供应商负责人
  2207. 'total_cgd' => 0,//订单总量
  2208. 'total_fee' => 0,//订单总金额
  2209. 'dtotal_fee' => 0,//待确认订单总金额
  2210. 'dtotal_cgd' => 0,//待确认订单总量
  2211. 'wtotal_fee' => 0,//待入库订单总金额
  2212. 'wtotal_cgd' => 0,//待入库订单总量
  2213. 'tag_1' => 0,//库存品
  2214. 'tag_2' => 0,//非库存品
  2215. 'tag_c' => 0,//to C
  2216. 'tag_b' => 0,//to B
  2217. 'tag_zx' => 0,//咨询采反总数
  2218. ];
  2219. }
  2220. switch ($val['order_type']) {
  2221. case 1:
  2222. //库存品
  2223. if ($val['order_source'] == 0) {
  2224. $list[$val['supplierNo']]['tag_1']+=$val['total_cgd'];
  2225. $total['tag_1']+=$val['total_cgd'];
  2226. }
  2227. break;
  2228. case 2:
  2229. //非库存品
  2230. $list[$val['supplierNo']]['tag_2']+=$val['total_cgd'];
  2231. $total['tag_2']+=$val['total_cgd'];
  2232. break;
  2233. case 3:
  2234. //咨询采反
  2235. // if ($val['order_source'] == 2) {
  2236. $list[$val['supplierNo']]['tag_zx']+=$val['total_cgd'];
  2237. $total['tag_zx']+=$val['total_cgd'];
  2238. // }
  2239. break;
  2240. }
  2241. switch ($val['use_type']) {
  2242. case 1:
  2243. //to B
  2244. $list[$val['supplierNo']]['tag_b']+=$val['total_cgd'];
  2245. $total['tag_b']+=$val['total_cgd'];
  2246. break;
  2247. case 2:
  2248. //to C
  2249. $list[$val['supplierNo']]['tag_c']+=$val['total_cgd'];
  2250. $total['tag_c']+=$val['total_cgd'];
  2251. break;
  2252. }
  2253. switch ($val['status']) {
  2254. case 0:
  2255. //to B
  2256. $list[$val['supplierNo']]['dtotal_cgd']+=$val['total_cgd'];
  2257. $list[$val['supplierNo']]['dtotal_fee']=bcadd($list[$val['supplierNo']]['dtotal_fee'] , $val['total_fee'],2);
  2258. $total['dtotal_cgd']+=$val['total_cgd'];
  2259. $total['dtotal_fee']=bcadd( $total['dtotal_fee'], $val['total_fee'],2);
  2260. break;
  2261. case 1:
  2262. case 2:
  2263. //to C
  2264. $list[$val['supplierNo']]['wtotal_cgd']+=$val['total_cgd'];
  2265. $list[$val['supplierNo']]['wtotal_fee']=bcadd( $list[$val['supplierNo']]['wtotal_fee'], $val['wtotal_fee'],2);
  2266. $total['wtotal_cgd']+=$val['total_cgd'];
  2267. $total['wtotal_fee']=bcadd( $total['wtotal_fee'], $val['wtotal_fee'],2);
  2268. break;
  2269. }
  2270. $list[$val['supplierNo']]['total_cgd']+=$val['total_cgd'];
  2271. $list[$val['supplierNo']]['total_fee']=bcadd( $list[$val['supplierNo']]['total_fee'], $val['total_fee'],2);
  2272. $total['total_cgd']+=$val['total_cgd'];
  2273. $total['total_fee']=bcadd( $total['total_fee'], $val['total_fee'],2);
  2274. }
  2275. // $list = array_merge($list);//重置下标,关联数组,下标是字符,无法通过array_merge方法重置下标
  2276. $list[] =$total;
  2277. return app_show(0, '请求成功', array_values($list));
  2278. }
  2279. //【十三、供应商采购订单情况-导出】
  2280. public function purcheaseOrderMonthExport()
  2281. {
  2282. $param = $this->request->only(['token', 'supplierNo' => '', 'companyNo' => '', 'supplier_name' => '','status' => '',
  2283. 'person_id' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  2284. $where = [['a.is_del', '=', 0],['a.status','<>',4]];
  2285. if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
  2286. if ($param['companyNo'] != '') $where[] = ['a.companyNo', 'like', '%' . $param['companyNo'] . '%'];
  2287. if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
  2288. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2289. if($param['status']!==''){
  2290. $where[]=["a.status","=",$param['status']];
  2291. }
  2292. if($param['person_id']!==''){
  2293. $where[]=["a.cgder_id","=",$param['person_id']];
  2294. }
  2295. $data = Db::name('purchease_order')
  2296. ->alias('a')
  2297. ->field('a.id,a.supplierNo,a.supplier_name,a.order_type,a.order_source,p.use_type')
  2298. ->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
  2299. ->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
  2300. ->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
  2301. ->where($where)
  2302. ->group("a.supplierNo,a.supplier_name,a.order_type,p.use_type,a.order_source,a.status")
  2303. ->field("a.supplierNo,a.supplier_name,a.order_type,p.use_type,a.order_source,a.status,count(a.id) as total_cgd,sum(total_fee) as total_fee,sum(a.wsend_num*a.good_price) as wtotal_fee")
  2304. ->select()
  2305. ->toArray();
  2306. $list = $tmp = [];
  2307. $total = [
  2308. '供应商名称' => '总计',
  2309. '是否开通供应商端'=>'',
  2310. '供应商负责人'=>'',
  2311. '采购订单总金额' => 0,
  2312. '采购订单总量' => 0,
  2313. '待确认采购订单总金额' => 0,
  2314. '待确认采购订单总数量' => 0,
  2315. '待入库采购订单总金额' => 0,
  2316. '待入库采购订单总数量' => 0,
  2317. '库存品' => 0,
  2318. '非库存品' => 0,
  2319. '采返商品' =>0,
  2320. 'ToC平台' => 0,
  2321. 'ToB平台' => 0,
  2322. ];
  2323. $supArr=array_unique(array_column($data,'supplierNo'));
  2324. $supplier =checkHasAccountBySupplierNos($supArr);
  2325. $pers =get_personid_by_supplierNo($supArr,"person");
  2326. foreach ($data as $val) {
  2327. if (!isset($list[$val['supplierNo']])) {
  2328. $list[$val['supplierNo']] = [
  2329. '采购方' => $val['supplier_name'],
  2330. '是否开通供应商端'=>isset($supplier[$val['supplierNo']]) ?'是' :'否',
  2331. '供应商负责人'=>$pers[$val['supplierNo']]??"",
  2332. '采购订单总金额' => 0,
  2333. '采购订单总量' => 0,
  2334. '待确认采购订单总金额' => 0,
  2335. '待确认采购订单总数量' => 0,
  2336. '待入库采购订单总金额' => 0,
  2337. '待入库采购订单总数量' => 0,
  2338. '库存品' => 0,
  2339. '非库存品' => 0,
  2340. '采返商品' =>0,
  2341. 'ToC平台' => 0,
  2342. 'ToB平台' => 0,
  2343. ];
  2344. }
  2345. switch ($val['order_type']) {
  2346. case 1:
  2347. //库存品
  2348. if ($val['order_source'] == 0) {
  2349. $list[$val['supplierNo']]['库存品']+=$val['total_cgd'];
  2350. $total['库存品']+=$val['total_cgd'];
  2351. }
  2352. break;
  2353. case 2:
  2354. //非库存品
  2355. $list[$val['supplierNo']]['非库存品']+=$val['total_cgd'];
  2356. $total['非库存品']+=$val['total_cgd'];
  2357. break;
  2358. case 3:
  2359. //咨询采反
  2360. // if ($val['order_source'] == 2) {
  2361. $list[$val['supplierNo']]['采返商品']+=$val['total_cgd'];
  2362. $total['采返商品']+=$val['total_cgd'];
  2363. // }
  2364. break;
  2365. }
  2366. switch ($val['use_type']) {
  2367. case 1:
  2368. //to B
  2369. $list[$val['supplierNo']]['ToB平台']+=$val['total_cgd'];
  2370. $total['ToB平台']+=$val['total_cgd'];
  2371. break;
  2372. case 2:
  2373. //to C
  2374. $list[$val['supplierNo']]['ToC平台']+=$val['total_cgd'];
  2375. $total['ToC平台']+=$val['total_cgd'];
  2376. break;
  2377. }
  2378. switch ($val['status']) {
  2379. case 0:
  2380. //to B
  2381. $list[$val['supplierNo']]['待确认采购订单总数量']+=$val['total_cgd'];
  2382. $list[$val['supplierNo']]['待确认采购订单总金额']+=$val['total_fee'];
  2383. $total['待确认采购订单总数量']+=$val['total_cgd'];
  2384. $total['待确认采购订单总金额']+=$val['total_fee'];
  2385. break;
  2386. case 1:
  2387. case 2:
  2388. //to C
  2389. $list[$val['supplierNo']]['待入库采购订单总数量']+=$val['total_cgd'];
  2390. $list[$val['supplierNo']]['待入库采购订单总金额']+=$val['wtotal_fee'];
  2391. $total['待入库采购订单总数量']+=$val['total_cgd'];
  2392. $total['待入库采购订单总金额']+=$val['wtotal_fee'];
  2393. break;
  2394. }
  2395. $list[$val['supplierNo']]['采购订单总量']+=$val['total_cgd'];
  2396. $list[$val['supplierNo']]['采购订单总金额']+=$val['total_fee'];
  2397. $total['采购订单总量']+=$val['total_cgd'];
  2398. $total['采购订单总金额']+=$val['total_fee'];
  2399. }
  2400. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  2401. excelSave('供应商采购订单情况' . date('YmdHis'), array_keys($total), $list);
  2402. }
  2403. //【十四、供应商负责人采购订单情况】
  2404. public function purcheaseOrderPersonMonth()
  2405. {
  2406. $param = $this->request->only(['token', 'supplierNo' => '', 'supplier_name' => '', 'flag' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  2407. $where = [['a.is_del', '=', 0]];
  2408. if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
  2409. if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
  2410. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2411. if ($param['flag'] != '') {
  2412. // $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
  2413. // ->group('supplierNo')
  2414. // ->column('supplierNo');
  2415. $has_sup_acc = checkHasAccountBySupplierNos();
  2416. if ($param['flag'] == 1) $where[] = ['a.supplierNo', 'in', array_keys($has_sup_acc)];//1.开通账号的
  2417. else $where[] = ['a.supplierNo', 'not in', array_keys($has_sup_acc)];//2.未开通账号的
  2418. }
  2419. $data = Db::name('purchease_order')
  2420. ->alias('a')
  2421. ->where($where)
  2422. ->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
  2423. ->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
  2424. ->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
  2425. ->group("a.cgder_id,a.cgder,a.order_type,p.use_type,a.order_source")
  2426. ->field("a.cgder_id,a.cgder,a.order_type,p.use_type,a.order_source,count(a.id) as total_cgd")
  2427. ->select()
  2428. ->toArray();
  2429. // var_dump($data);
  2430. $list = [];
  2431. $total = ['person' => '总计', 'total_cgd' => 0, 'tag_1' => 0, 'tag_2' => 0, 'tag_c' => 0, 'tag_b' => 0, 'tag_zx' => 0, 'tag_else' => 0];
  2432. $list[]=&$total;
  2433. foreach ($data as $val) {
  2434. if(!isset($list[$val['cgder_id']]))$list[$val['cgder_id']]=['person' => '', 'total_cgd' => 0, 'tag_1' => 0, 'tag_2' => 0, 'tag_c' => 0, 'tag_b' => 0, 'tag_zx' => 0, 'tag_else' => 0];
  2435. $list[$val['cgder_id']]['person']=$val['cgder'];
  2436. $list[$val['cgder_id']]['total_cgd']+=$val['total_cgd'];
  2437. switch ($val['order_type']) {
  2438. case 1:
  2439. //库存品
  2440. $list[$val['cgder_id']]['tag_1']+=$val['total_cgd'];
  2441. $total['tag_1']+=$val['total_cgd'];
  2442. break;
  2443. case 2:
  2444. //非库存品
  2445. $list[$val['cgder_id']]['tag_2']+=$val['total_cgd'];
  2446. $total['tag_2']+=$val['total_cgd'];
  2447. break;
  2448. case 3:
  2449. //咨询采反
  2450. if ($val['order_source'] == 2) {
  2451. $list[$val['cgder_id']]['tag_zx']+=$val['total_cgd'];
  2452. $total['tag_zx']+=$val['total_cgd'];
  2453. } else {
  2454. $list[$val['cgder_id']]['tag_else']+=$val['total_cgd'];
  2455. $total['tag_else']+=$val['total_cgd'];
  2456. }
  2457. break;
  2458. }
  2459. switch ($val['use_type']) {
  2460. case 1:
  2461. //to B
  2462. $list[$val['cgder_id']]['tag_b']+=$val['total_cgd'];
  2463. $total['tag_b']+=$val['total_cgd'];
  2464. break;
  2465. case 2:
  2466. //to C
  2467. $list[$val['cgder_id']]['tag_c']+=$val['total_cgd'];
  2468. $total['tag_c']+=$val['total_cgd'];
  2469. break;
  2470. }
  2471. $total['total_cgd'] +=$val['total_cgd'];
  2472. }
  2473. $list=array_values($list);
  2474. return app_show(0, '请求成功', $list);
  2475. }
  2476. //【十四、供应商负责人采购订单情况-导出】
  2477. public function purcheaseOrderPersonMonthExport()
  2478. {
  2479. $param = $this->request->only(['token', 'supplierNo' => '', 'supplier_name' => '', 'flag' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  2480. $where = [['a.is_del', '=', 0]];
  2481. if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
  2482. if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
  2483. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2484. if ($param['flag'] != '') {
  2485. $has_sup_acc = checkHasAccountBySupplierNos();
  2486. if ($param['flag'] == 1) $where[] = ['a.supplierNo', 'in', array_keys($has_sup_acc)];//1.开通账号的
  2487. else $where[] = ['a.supplierNo', 'not in', array_keys($has_sup_acc)];//2.未开通账号的
  2488. }
  2489. $data = Db::name('purchease_order')
  2490. ->alias('a')
  2491. ->where($where)
  2492. ->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
  2493. ->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
  2494. ->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
  2495. ->group("a.cgder_id,a.cgder,a.order_type,p.use_type,a.order_source")
  2496. ->field("a.cgder_id,a.cgder,a.order_type,p.use_type,a.order_source,count(a.id) as total_cgd")
  2497. ->select()
  2498. ->toArray();
  2499. $list = [];
  2500. $total = ['采购方' => '总计', '订单总量' => 0, '库存品' => 0, '非库存品' => 0, 'toC' => 0, 'toB' => 0, '反馈单数合计' => 0, '采销录单采反' => 0];
  2501. foreach ($data as $val) {
  2502. if (!isset($list[$val['personid']])) {
  2503. $list[$val['cgder_id']] = [
  2504. '采购方' => $val['cgder'],
  2505. '订单总量' => 0,//订单总量
  2506. '库存品' => 0,//库存品
  2507. '非库存品' => 0,//非库存品
  2508. 'toC' => 0,//to C
  2509. 'toB' => 0,//to B
  2510. '反馈单数合计' => 0,//咨询采反总数
  2511. '采销录单采反' => 0,//采销录单采反
  2512. ];
  2513. }
  2514. switch ($val['order_type']) {
  2515. case 1:
  2516. //库存品
  2517. if ($val['order_source'] == 0) {
  2518. $list[$val['cgder_id']]['库存品']+=$val['total_cgd'];
  2519. $total['库存品']+=$val['total_cgd'];
  2520. }
  2521. break;
  2522. case 2:
  2523. //非库存品
  2524. $list[$val['cgder_id']]['非库存品']+=$val['total_cgd'];
  2525. $total['非库存品']+=$val['total_cgd'];
  2526. break;
  2527. case 3:
  2528. //咨询采反
  2529. if($val['order_source'] == 2){
  2530. $list[$val['supplierNo']]['反馈单数合计']+=$val['total_cgd'];
  2531. $total['反馈单数合计']+=$val['total_cgd'];
  2532. } else {
  2533. $list[$val['cgder_id']]['采销录单采反']+=$val['total_cgd'];
  2534. $total['采销录单采反']+=$val['total_cgd'];
  2535. }
  2536. break;
  2537. }
  2538. switch ($val['use_type']) {
  2539. case 1:
  2540. //to B
  2541. $list[$val['cgder_id']]['toB']+=$val['total_cgd'];
  2542. $total['toB']+=$val['total_cgd'];
  2543. break;
  2544. case 2:
  2545. //to C
  2546. $list[$val['cgder_id']]['toC']+=$val['total_cgd'];
  2547. $total['toC']+=$val['total_cgd'];
  2548. break;
  2549. }
  2550. $list[$val['cgder_id']]['订单总量']+=$val['total_cgd'];
  2551. $total['订单总量']+=$val['total_cgd'];
  2552. }
  2553. $list[] = $total;
  2554. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  2555. excelSave('供应商负责人采购订单情况' . date('YmdHis'), array_keys($total), $list);
  2556. }
  2557. //竞价单反馈情况
  2558. public function FeedBackBySupplier(){
  2559. $param = $this->request->only([
  2560. 'supplierNo' => '',
  2561. 'companyNo' => '',
  2562. 'companyName' => '',
  2563. 'supplier_name' => '',
  2564. 'start_day' => date('Y-m-01'),
  2565. 'end_day' => date('Y-m-t')], 'post', 'trim');
  2566. $where = [['a.is_del', '=', 0]];
  2567. if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
  2568. if ($param['supplier_name'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['supplier_name'] . '%'];
  2569. if ($param['companyNo'] != '') $where[] = ['b.companyNo', 'like', '%' . $param['companyNo'] . '%'];
  2570. if ($param['companyName'] != '') $where[] = ['b.companyName', 'like', '%' . $param['companyName'] . '%'];
  2571. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2572. $list =Db::name("consult_bids")->alias("a")
  2573. ->leftJoin("consult_order b","a.zxNo=b.zxNo")
  2574. ->leftJoin("consult_info c","a.infoNo=c.infoNo")
  2575. ->leftJoin("platform d","b.platform_code=d.id")
  2576. ->where($where)
  2577. ->group("a.supplierNo,d.use_type")
  2578. ->field("a.supplierNo,a.supplierName,d.use_type,count(a.id) feed_num,sum(c.num) good_num,sum(c.num*a.total_fee) total_price,a.cgder")->select()->toArray();
  2579. $data=[];
  2580. $supArr =array_column($list,"supplierNo");
  2581. $supp=checkHasAccountBySupplierNos($supArr);
  2582. $nmer =["supplierName"=>'合计',"is_supplier"=>"","person"=>"","feed_num"=>0,"good_num"=>0,"total_price"=>0,"toc"=>0,"tob"=>0];
  2583. $data[]=&$nmer;
  2584. foreach ($list as $value){
  2585. if(!isset($data[$value['supplierNo']])){
  2586. $data[$value['supplierNo']]=[
  2587. "supplierName"=>$value['supplierName'],
  2588. "is_supplier"=>isset($supp[$value['supplierNo']])?'是':'否',
  2589. "person"=>$value['cgder'],
  2590. "feed_num"=>0,
  2591. "good_num"=>0,
  2592. "total_price"=>0,
  2593. "toc"=>0,
  2594. "tob"=>0
  2595. ];
  2596. }
  2597. switch ($value['use_type']){
  2598. case 1:
  2599. $data[$value['supplierNo']]['tob']= bcadd($data[$value['supplierNo']]['tob'],$value['feed_num'],2);
  2600. $nmer['tob']=bcadd($nmer['tob'],$value['feed_num'],2);
  2601. break;
  2602. case 2:
  2603. $data[$value['supplierNo']]['toc']=bcadd($data[$value['supplierNo']]['toc'],$value['feed_num'],2);
  2604. $nmer['toc']=bcadd($nmer['toc'],$value['feed_num'],2);
  2605. break;
  2606. }
  2607. $data[$value['supplierNo']]['feed_num']=bcadd($data[$value['supplierNo']]['feed_num'],$value['feed_num'],2);
  2608. $data[$value['supplierNo']]['good_num']=bcadd($data[$value['supplierNo']]['good_num'],$value['good_num'],2);
  2609. $data[$value['supplierNo']]['total_price']=bcadd($data[$value['supplierNo']]['total_price'],$value['total_price'],2);
  2610. $nmer['feed_num']=bcadd($value['feed_num'],$nmer['feed_num'],2);
  2611. $nmer['good_num']=bcadd($value['good_num'],$nmer['good_num'],2);
  2612. $nmer['total_price']=bcadd($value['total_price'],$nmer['total_price'],2);
  2613. }
  2614. return app_show(0, '请求成功', array_values($data));
  2615. }
  2616. //当日库存销售情况
  2617. public function saleStock(){
  2618. $param = $this->request->only([
  2619. 'supplierNo' => '',
  2620. 'companyNo' => '',
  2621. 'companyName' => '',
  2622. 'supplier_name' => '',
  2623. 'good_name' => '',
  2624. 'page' => 1,
  2625. 'size' => 15,
  2626. 'start_day' => date('Y-m-01'),
  2627. 'end_day' => date('Y-m-t')
  2628. ], 'post', 'trim');
  2629. $where = [['a.is_del', '=', 0],["a.is_stock","=","1"]];
  2630. if ($param['supplierNo'] != '') $where[] = ['a.supNo', 'like', '%' . $param['supplierNo'] . '%'];
  2631. if ($param['supplier_name'] != '') $where[] = ['a.supName', 'like', '%' . $param['supplier_name'] . '%'];
  2632. if ($param['companyNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['companyNo'] . '%'];
  2633. if ($param['companyName'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['companyName'] . '%'];
  2634. if ($param['good_name'] != '') $where[] = ['a.good_name', 'like', '%' . $param['good_name'] . '%'];
  2635. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2636. $count = Db::name('sale')
  2637. ->alias('a')
  2638. ->where($where)->group("a.good_code")
  2639. ->count('a.id');
  2640. $list=Db::name("sale")->alias("a")
  2641. ->leftJoin('order_num on', 'a.orderCode=on.orderCode')
  2642. ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
  2643. ->where($where)
  2644. ->page($param['page'], $param['size'])
  2645. ->order('a.addtime', 'desc')
  2646. ->field("a.good_code,a.good_name,sum(a.good_num) as good_num,a.sale_price,a.is_stock,sum(a.send_num) as send_num,0 stock_num,a.supplierName,po.good_price,sum(po.total_fee) total_fee")
  2647. ->group("a.good_code")
  2648. ->cursor();
  2649. $data=[];
  2650. foreach ($list as $value){
  2651. $value['stock_num'] = Db::name("good_stock")->where("spuCode",$value['good_code'])->sum("usable_stock");
  2652. if($value['is_stock']==1){
  2653. $value['good_price'] = Db::name("purchease_order")->where(["spuCode"=>$value['good_code'],"order_source"=>0])->value("good_price",0);
  2654. $value['total_fee'] = bcmul($value['good_price'],$value['good_num'],2);
  2655. }
  2656. $data[]= $value;
  2657. }
  2658. return app_show(0, '请求成功', ['list' => $data, 'count' => $count]);
  2659. }
  2660. //当日库存销售情况导出
  2661. public function saleStockExport(){
  2662. $param = $this->request->only([
  2663. 'supplierNo' => '',
  2664. 'companyNo' => '',
  2665. 'companyName' => '',
  2666. 'supplier_name' => '',
  2667. 'good_name' => '',
  2668. 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  2669. $where = [['a.is_del', '=', 0],["a.is_stock","=","1"]];
  2670. if ($param['supplierNo'] != '') $where[] = ['a.supNo', 'like', '%' . $param['supplierNo'] . '%'];
  2671. if ($param['supplier_name'] != '') $where[] = ['a.supName', 'like', '%' . $param['supplier_name'] . '%'];
  2672. if ($param['companyNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['companyNo'] . '%'];
  2673. if ($param['companyName'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['companyName'] . '%'];
  2674. if ($param['good_name'] != '') $where[] = ['a.good_name', 'like', '%' . $param['good_name'] . '%'];
  2675. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2676. $list=Db::name("sale")->alias("a")
  2677. ->leftJoin('order_num on', 'a.orderCode=on.orderCode')
  2678. ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
  2679. ->where($where)
  2680. ->order('a.addtime', 'desc')
  2681. ->group("a.good_code")
  2682. ->field("a.good_code 商品编号,a.good_name 产品名称,a.good_num 当日库存销售,a.is_stock,a.send_num 当日库存发货,0 当日剩余可用库存,a.supplierName 业务公司,po.good_price 库存采购单价,po.total_fee 当日库存消耗金额")
  2683. ->cursor();
  2684. $data=[];
  2685. foreach ($list as $value){
  2686. $value['当日剩余可用库存'] = Db::name("good_stock")->where("spuCode",$value['商品编号'])->sum("usable_stock");
  2687. if($value['is_stock']==1){
  2688. $value['库存采购单价'] = Db::name("purchease_order")->where(["spuCode"=>$value['商品编号'],"order_source"=>0])->value("good_price",0);
  2689. $value['当日库存消耗金额'] = bcmul($value['库存采购单价'],$value['当日库存销售'],2);
  2690. }
  2691. unset($value['is_stock']);
  2692. $data[]= $value;
  2693. }
  2694. if (empty($data)) $data[] = ['没有相关可导出的数据'];
  2695. excelSave('订单报表' . date('YmdHis'), array_keys($data[0]), $data);
  2696. }
  2697. //待库管发货
  2698. public function OutChildList(){
  2699. $param = $this->request->only([
  2700. 'supplierNo' => '',
  2701. 'companyNo' => '',
  2702. 'companyName' => '',
  2703. 'supplier_name' => '',
  2704. 'depart_id' => '',
  2705. 'person_id' => '',
  2706. 'page' => 1,
  2707. 'size' => 15,
  2708. 'start_day' => date('Y-m-01'),
  2709. 'end_day' => date('Y-m-t'),
  2710. 'order_start_day' => date('Y-m-01'),
  2711. 'order_end_day' => date('Y-m-t')],
  2712. 'post', 'trim');
  2713. $where = [['a.is_del', '=', 0],["a.status","in",[0,1]]];
  2714. if ($param['depart_id'] != ''){
  2715. $uidArr = get_company_item_user_by_name("",$param['depart_id']);
  2716. $where[] = ['a.apply_id', 'in',$uidArr];
  2717. }
  2718. if($param['person_id'] != '') {
  2719. $where[] = ['c.cgderid', '=',$param['person_id']];
  2720. }
  2721. if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
  2722. if ($param['supplier_name'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['supplier_name'] . '%'];
  2723. if ($param['companyNo'] != '') $where[] = ['a.companyNo', 'like', '%' . $param['companyNo'] . '%'];
  2724. if ($param['companyName'] != '') $where[] = ['a.companyName', 'like', '%' . $param['companyName'] . '%'];
  2725. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2726. if ($param['order_start_day'] != '' && $param['order_end_day'] != '') $where[] = ['c.addtime', 'between',
  2727. [$param['order_start_day'] . ' 00:00:00', $param['order_end_day'] . ' 23:59:59']];
  2728. $count = Db::name("order_out_child")->alias("a")->leftJoin('sale c','a.orderCode=c.orderCode')->where($where)->count();
  2729. $list = Db::name("order_out_child")
  2730. ->alias("a")
  2731. ->leftJoin("warehouse_info b","a.wsm_code=b.wsm_code")
  2732. ->leftJoin("sale c","a.orderCode=c.orderCode")
  2733. ->leftJoin("order_send d","a.outCode=d.outCode")
  2734. ->page($param['page'],$param['size'])
  2735. ->where($where)
  2736. ->field("a.outChildCode,a.outCode,a.orderCode,a.supplierNo,a.supplierName,a.companyNo,a.companyName,a.customer_code,a.customer_name,
  2737. a.status,a.order_type,a.order_source,a.num,a.wsm_code,b.name,a.spuCode,a.skuCode,a.good_name,c.addtime,a.apply_name,a.apply_id,c.platform_id,a.post_name,
  2738. a.post_code,a.post_fee,a.addrid,d.cgdNo,c.remark,datediff(now(),a.addtime) sx,b.contactor_name")
  2739. ->select()
  2740. ->toArray();
  2741. $supArr =array_unique( array_column($list,"supplierNo"));
  2742. $platArr =array_unique(array_column($list,"platform_id"));
  2743. $AddrArr =array_unique(array_column($list,"addrid"));
  2744. $applyArr =array_unique(array_column($list,"apply_id"));
  2745. $supp=checkHasAccountBySupplierNos($supArr);
  2746. $preson = get_personid_by_supplierNo($supArr,"person");
  2747. $plafrom = Db::name("platform")->where("id","in",$platArr)->column("use_type","id");
  2748. $addr = Db::name("order_addr")->where("id","in",$AddrArr)->column("addr,addr_code,contactor,mobile","id");
  2749. $depart = get_company_name_by_uid($applyArr);
  2750. // $depart = $applyRe['data']??[];
  2751. foreach ($list as &$value){
  2752. $value['is_supplier'] = isset($supp[$value['supplierNo']])?'已开通':'未开通';
  2753. $value['person'] = $preson[$value['supplierNo']]??"";
  2754. $value['platform_type'] = $plafrom[$value['platform_id']]??"0";
  2755. $value['addr'] = "";
  2756. $value['addr_contactor'] = "";
  2757. $value['addr_mobile'] ="";
  2758. $value['report_sx'] =report_sx($value['sx']);
  2759. if(isset($addr[$value['addrid']])){
  2760. if($addr[$value['addrid']]['addr_code']!=''){
  2761. if (!json_decode($addr[$value['addrid']]['addr_code'])) {
  2762. $temp = explode(',', $addr[$value['addrid']]['addr_code']);
  2763. $addr[$value['addrid']]['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
  2764. }
  2765. $value['addr'] = GetAddr($addr[$value['addrid']]['addr_code']) . $addr[$value['addrid']]['addr'];
  2766. }else{
  2767. $value['addr'] = $addr[$value['addrid']]['addr'];
  2768. }
  2769. $value['addr_contactor'] = $addr[$value['addrid']]['contactor'];
  2770. $value['addr_mobile'] =$addr[$value['addrid']]['mobile'];
  2771. }
  2772. $value['depart']=$depart[$value['apply_id']]??"";
  2773. }
  2774. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  2775. }
  2776. //待库管发货
  2777. public function OutChildExport(){
  2778. $param = $this->request->only([
  2779. 'supplierNo' => '',
  2780. 'companyNo' => '',
  2781. 'companyName' => '',
  2782. 'depart_id' => '',
  2783. 'person_id' => '',
  2784. 'supplier_name' => '',
  2785. 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t'),
  2786. 'order_start_day' => date('Y-m-01'), 'order_end_day' => date('Y-m-t'),
  2787. ], 'post', 'trim');
  2788. $where = [['a.is_del', '=', 0],["a.status","in",[0,1]]];
  2789. if ($param['depart_id'] != ''){
  2790. $uidArr = get_company_item_user_by_name("",$param['depart_id']);
  2791. $where[] = ['a.apply_id', 'in',$uidArr];
  2792. }
  2793. if($param['person_id'] != '') {
  2794. $where[] = ['c.cgderid', '=',$param['person_id']];
  2795. }
  2796. if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
  2797. if ($param['supplier_name'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['supplier_name'] . '%'];
  2798. if ($param['companyNo'] != '') $where[] = ['a.companyNo', 'like', '%' . $param['companyNo'] . '%'];
  2799. if ($param['companyName'] != '') $where[] = ['a.companyName', 'like', '%' . $param['companyName'] . '%'];
  2800. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2801. if ($param['order_start_day'] != '' && $param['order_end_day'] != '') $where[] = ['c.addtime', 'between',
  2802. [$param['order_start_day'] . ' 00:00:00', $param['order_end_day'] . ' 23:59:59']];
  2803. $list = Db::name("order_out_child")
  2804. ->alias("a")
  2805. ->leftJoin("warehouse_info b","a.wsm_code=b.wsm_code")
  2806. ->leftJoin("sale c","a.orderCode=c.orderCode")
  2807. ->leftJoin("order_send d","a.outCode=d.outCode")
  2808. ->where($where)
  2809. ->field("a.outChildCode 发货工单号,a.outCode 发货单编号,a.orderCode 销售订单号,'' 是否开通供应商端,a.status 状态,a.order_type 商品类型,a.order_source 订单来源,
  2810. a.num 总数量,a.wsm_code 仓库编号,b.name 仓库名称,a.spuCode 商品成本编号,a.skuCode 商品上线编号,a.good_name 商品名称,
  2811. a.companyNo 业务公司编号,a.companyName 业务公司名称,a.customer_code 客户编号,a.customer_name 客户名称,a.supplierNo 供应商编号,a.supplierName 供应商名称,
  2812. c.addtime 下单时间,a.apply_name 申请人,a.apply_id 申请人部门,c.platform_id 对接类型,a.post_name 物流公司,a.post_code 物流编号,a.post_fee 物流费用,
  2813. a.addrid,'' 收货地址,'' 收货联系人,'' 联系方式, d.cgdNo 采购单号,c.remark 备注,datediff(now(),a.addtime) 时效,b.contactor_name 仓库负责人,'' 供应商负责人")
  2814. ->select()
  2815. ->toArray();
  2816. $supArr =array_unique( array_column($list,'supplierNo'));
  2817. $platArr =array_unique(array_column($list,'platform_id'));
  2818. $AddrArr =array_unique(array_column($list,'addrid'));
  2819. $applyArr =array_unique(array_column($list,'apply_id'));
  2820. $supp=checkHasAccountBySupplierNos($supArr);
  2821. $preson = get_personid_by_supplierNo($supArr,"person");
  2822. $plafrom = Db::name("platform")->where("id","in",$platArr)->column("use_type","id");
  2823. $addr = Db::name("order_addr")->where("id","in",$AddrArr)->column("addr,addr_code,contactor,mobile","id");
  2824. $depart = get_company_name_by_uid($applyArr);
  2825. $childstatus = [0=>"待确认",1=>"待发货"];
  2826. foreach ($list as &$value){
  2827. $value['是否开通供应商端'] = isset($supp[$value['供应商编号']])?'已开通':'未开通';
  2828. $value['供应商负责人'] = $preson[$value['供应商编号']]??"";
  2829. $value['对接类型'] =$this->plat_type[$plafrom[$value['对接类型']]??"0"];
  2830. $value['商品类型'] = $this->all_order_type[$value['商品类型']]??"";
  2831. $value['订单来源'] = $this->order_source[$value['订单来源']]??"";
  2832. $value['收货地址'] = "";
  2833. $value['收货联系人'] = "";
  2834. $value['联系方式'] ="";
  2835. $value['时效'] =report_sx($value['时效']);
  2836. $value['状态'] = $childstatus[$value['状态']]??"";
  2837. if(isset($addr[$value['addrid']])){
  2838. if($addr[$value['addrid']]['addr_code']!=''){
  2839. if (!json_decode($addr[$value['addrid']]['addr_code'])) {
  2840. $temp = explode(',', $addr[$value['addrid']]['addr_code']);
  2841. $addr[$value['addrid']]['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
  2842. }
  2843. $value['收货地址'] = GetAddr($addr[$value['addrid']]['addr_code']) . $addr[$value['addrid']]['addr'];
  2844. }else{
  2845. $value['收货地址'] = $addr[$value['addrid']]['addr'];
  2846. }
  2847. $value['收货联系人'] = $addr[$value['addrid']]['contactor'];
  2848. $value['联系方式'] =$addr[$value['addrid']]['mobile'];
  2849. }
  2850. $value['申请人部门']=$depart[$value['申请人部门']]??"";
  2851. }
  2852. if (empty($list)) $list[] = ['没有相关可导出的数据'];
  2853. excelSave('订单报表' . date('YmdHis'), array_keys($list[0]), $list);
  2854. }
  2855. //待采购备货
  2856. public function OrderOutBk(){
  2857. $param = $this->request->only([
  2858. 'supplierNo' => '',
  2859. 'companyNo' => '',
  2860. 'companyName' => '',
  2861. 'supplier_name' => '',
  2862. 'depart_id' => '',
  2863. 'person_id' => '',
  2864. 'page' => 1,
  2865. 'size' => 15,
  2866. 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  2867. $where = [['a.is_del', '=', 0],['a.status', '=', 0]];
  2868. if ($param['supplierNo'] != '') $where[] = ['b.supNo', 'like', '%' . $param['supplierNo'] . '%'];
  2869. if ($param['supplier_name'] != '') $where[] = ['b.supName', 'like', '%' . $param['supplier_name'] . '%'];
  2870. if ($param['companyNo'] != '') $where[] = ['b.supplierNo', 'like', '%' . $param['companyNo'] . '%'];
  2871. if ($param['companyName'] != '') $where[] = ['b.supplierName', 'like', '%' . $param['companyName'] . '%'];
  2872. if ($param['depart_id'] != ''){
  2873. $uidArr = get_company_item_user_by_name("",$param['depart_id']);
  2874. $where[] = ['a.apply_id', 'in',$uidArr];
  2875. }
  2876. if($param['person_id'] != '') {
  2877. $where[] = ['b.cgderid', '=',$param['person_id']];
  2878. }
  2879. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2880. $count= Db::name("order_out")->alias("a")
  2881. ->leftJoin("sale b","a.orderCode=b.orderCode")
  2882. ->leftJoin("order_num c","a.orderCode = c.orderCode")
  2883. ->leftJoin("order_addr d","a.addrid = d.id")->where($where)->count("a.id");
  2884. $list =Db::name("order_out")->alias("a")
  2885. ->leftJoin("sale b","a.orderCode=b.orderCode")
  2886. ->leftJoin("order_num c","a.orderCode = c.orderCode")
  2887. ->leftJoin("order_addr d","a.addrid = d.id")
  2888. ->field("a.outCode,a.orderCode,c.cgdNo,a.send_num,b.platform_id,a.status,b.order_type,b.order_source,supName,b.cat_id,b.good_code,b.good_name
  2889. ,b.customerName,b.customer_code,b.supplierName,b.supplierNo,d.contactor,d.mobile,a.post_name,a.post_code,a.post_fee,'' send_time,
  2890. a.apply_id,a.apply_name,a.addtime,b.addtime createdTime,datediff(now(),a.addtime) sx,b.cgder")
  2891. ->page($param['page'],$param['size'])
  2892. ->where($where)
  2893. ->select()->toArray();
  2894. $data=[];
  2895. $platArr =array_unique(array_column($list,"platform_id"));
  2896. $applyArr = array_unique(array_column($list,"apply_id")) ;
  2897. $catArr =array_column($list,"cat_id");
  2898. $plafrom = Db::name("platform")->where("id","in",$platArr)->column("use_type","id");
  2899. $cat = Db::name("cat")->where("id","in",$catArr)->column("search","id");
  2900. $depart= get_company_name_by_uid($applyArr);
  2901. // $depart = $applyRe['data']??[];
  2902. foreach ($list as $item) {
  2903. $item['platform_type'] = $this->plat_type[$plafrom[$item['platform_id']]??0];
  2904. $item['depart'] = $depart[$item['apply_id']]??"";
  2905. $item['order_source'] = $this->order_source[$item['order_source']]??"";
  2906. $item['order_type'] = $this->all_order_type[$item['order_type']]??"";
  2907. $item['cat_name'] = $cat[$item['cat_id']]??"";
  2908. $item['report_sx'] =report_sx($item['sx']);
  2909. $data[]=$item;
  2910. }
  2911. return app_show(0, '请求成功', ['list' => $data, 'count' => $count]);
  2912. }
  2913. //待采购备货
  2914. public function OrderOutExport(){
  2915. $param = $this->request->only([
  2916. 'supplierNo' => '',
  2917. 'companyNo' => '',
  2918. 'companyName' => '',
  2919. "depart_id"=>"",
  2920. "person_id"=>"",
  2921. 'supplier_name' => '',
  2922. 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
  2923. $where = [['a.is_del', '=', 0],['a.status', '=', 0]];
  2924. if ($param['supplierNo'] != '') $where[] = ['b.supNo', 'like', '%' . $param['supplierNo'] . '%'];
  2925. if ($param['supplier_name'] != '') $where[] = ['b.supName', 'like', '%' . $param['supplier_name'] . '%'];
  2926. if ($param['companyNo'] != '') $where[] = ['b.supplierNo', 'like', '%' . $param['companyNo'] . '%'];
  2927. if ($param['companyName'] != '') $where[] = ['b.supplierName', 'like', '%' . $param['companyName'] . '%'];
  2928. if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
  2929. if ($param['depart_id'] != ''){
  2930. $uidArr = get_company_item_user_by_name("",$param['depart_id']);
  2931. $where[] = ['a.apply_id', 'in',$uidArr];
  2932. }
  2933. if($param['person_id'] != '') {
  2934. $where[] = ['b.cgderid', '=',$param['person_id']];
  2935. }
  2936. $list =Db::name("order_out")->alias("a")
  2937. ->leftJoin("sale b","a.orderCode=b.orderCode")
  2938. ->leftJoin("order_num c","a.orderCode = c.orderCode")
  2939. ->leftJoin("order_addr d","a.addrid = d.id")
  2940. ->field("
  2941. a.outCode 发货单编号,
  2942. a.orderCode 订单编号,
  2943. c.cgdNo 采购单编号,
  2944. a.send_num 发货数量,
  2945. b.platform_id 平台类型,
  2946. a.status 状态,
  2947. b.order_type 商品类型,
  2948. b.order_source 订单来源,
  2949. supName 供应商,
  2950. b.cat_id 商品分类,
  2951. b.good_code 商品编号,
  2952. b.good_name 商品名称,
  2953. b.customerName 客户名称,
  2954. b.customer_code 客户编号,
  2955. b.supplierName 业务公司名称,
  2956. b.supplierNo 业务公司编号,
  2957. d.contactor 收货联系人,
  2958. d.mobile 联系方式,
  2959. a.post_name 快递公司,
  2960. a.post_code 快递编号,
  2961. a.post_fee 快递费用,
  2962. '' 发货时间,
  2963. a.apply_id 申请人部门,
  2964. a.apply_name 申请人,
  2965. a.addtime 添加时间,
  2966. b.addtime 下单日期,
  2967. datediff(now(),a.addtime) 时效,
  2968. b.cgder 供应商负责人")
  2969. ->where($where)
  2970. ->cursor();
  2971. $data=[];
  2972. // $platArr =array_column($list,"平台类型");
  2973. // $applyArr =array_column($list,"申请人部门");
  2974. // $catArr =array_column($list,"商品分类");
  2975. // $plafrom = Db::name("platform")->where("id",'in',$platArr)->column("use_type","id");
  2976. // $cat = Db::name("cat")->where("id",'in',$catArr)->column("search","id");
  2977. // $depart = get_company_name_by_uid($applyArr);
  2978. $depart=[];
  2979. $cat=[];
  2980. $plafrom=[];
  2981. foreach ($list as $item) {
  2982. if(!isset($depart[$item['申请人部门']])){
  2983. $temp=get_company_name_by_uid($item['申请人部门']);
  2984. $depart[$item['申请人部门']]=$temp;
  2985. }
  2986. if(!isset($cat[$item['商品分类']])){
  2987. $cat[$item['商品分类']]= Db::name("cat")->where("id",$item['商品分类'])->value("search","");
  2988. }
  2989. if(!isset($plafrom[$item['平台类型']])){
  2990. $plafrom[$item['平台类型']]= Db::name("platform")->where("id",$item['平台类型'])->value("use_type","0");
  2991. }
  2992. $item['平台类型'] = $this->plat_type[$plafrom[$item['平台类型']]];
  2993. $item['申请人部门'] = $depart[$item['申请人部门']]??"";
  2994. $item['状态'] = $this->order_out_status[$item['状态']]??"";
  2995. $item['订单来源'] = $this->order_source[$item['订单来源']]??"";
  2996. $item['商品类型'] = $this->all_order_type[$item['商品类型']]??"";
  2997. $item['商品分类'] = $cat[$item['商品分类']]??"";
  2998. $item['时效'] =report_sx($item['时效']);
  2999. $data[]=$item;
  3000. }
  3001. if (empty($data)) $data[] = ['没有相关可导出的数据'];
  3002. excelSave('订单报表' . date('YmdHis'), array_keys($data[0]), $data);
  3003. }
  3004. }