StatisticsController.cs 236 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906290729082909291029112912291329142915291629172918291929202921292229232924292529262927292829292930293129322933293429352936293729382939294029412942294329442945294629472948294929502951295229532954295529562957295829592960296129622963296429652966296729682969297029712972297329742975297629772978297929802981298229832984298529862987298829892990299129922993299429952996299729982999300030013002300330043005300630073008300930103011301230133014301530163017301830193020302130223023302430253026302730283029303030313032303330343035303630373038303930403041304230433044304530463047304830493050305130523053305430553056305730583059306030613062306330643065306630673068306930703071307230733074307530763077307830793080308130823083308430853086308730883089309030913092309330943095309630973098309931003101310231033104310531063107310831093110311131123113311431153116311731183119312031213122312331243125312631273128312931303131313231333134313531363137313831393140314131423143314431453146314731483149315031513152315331543155315631573158315931603161316231633164316531663167316831693170317131723173317431753176317731783179318031813182318331843185318631873188318931903191319231933194319531963197319831993200320132023203320432053206320732083209321032113212321332143215321632173218321932203221322232233224322532263227322832293230323132323233323432353236323732383239324032413242324332443245324632473248324932503251325232533254325532563257325832593260326132623263326432653266326732683269327032713272327332743275327632773278327932803281328232833284328532863287328832893290329132923293329432953296329732983299330033013302330333043305330633073308330933103311331233133314331533163317331833193320332133223323332433253326332733283329333033313332333333343335333633373338333933403341334233433344334533463347334833493350335133523353335433553356335733583359336033613362336333643365336633673368336933703371337233733374337533763377337833793380338133823383338433853386338733883389339033913392339333943395339633973398339934003401340234033404340534063407340834093410341134123413341434153416341734183419342034213422342334243425342634273428342934303431343234333434343534363437343834393440344134423443344434453446344734483449345034513452345334543455345634573458345934603461346234633464346534663467346834693470347134723473347434753476347734783479348034813482348334843485348634873488348934903491349234933494349534963497349834993500350135023503350435053506350735083509351035113512351335143515351635173518351935203521352235233524352535263527352835293530353135323533353435353536353735383539354035413542354335443545354635473548354935503551355235533554355535563557355835593560356135623563356435653566356735683569357035713572357335743575357635773578357935803581358235833584358535863587358835893590359135923593359435953596359735983599360036013602360336043605360636073608360936103611361236133614361536163617361836193620362136223623362436253626362736283629363036313632363336343635363636373638363936403641364236433644364536463647364836493650365136523653365436553656365736583659366036613662366336643665366636673668366936703671367236733674367536763677367836793680368136823683368436853686368736883689369036913692369336943695369636973698369937003701370237033704370537063707370837093710371137123713371437153716371737183719372037213722372337243725372637273728372937303731373237333734373537363737373837393740374137423743374437453746374737483749375037513752375337543755375637573758375937603761376237633764376537663767376837693770377137723773377437753776377737783779378037813782378337843785378637873788378937903791379237933794379537963797379837993800380138023803380438053806380738083809381038113812381338143815381638173818381938203821382238233824382538263827382838293830383138323833383438353836383738383839384038413842384338443845384638473848384938503851385238533854385538563857385838593860386138623863386438653866386738683869387038713872387338743875387638773878387938803881388238833884388538863887388838893890389138923893389438953896389738983899390039013902390339043905390639073908390939103911391239133914391539163917391839193920392139223923392439253926392739283929393039313932393339343935393639373938393939403941394239433944394539463947394839493950395139523953395439553956395739583959396039613962396339643965396639673968396939703971397239733974397539763977397839793980398139823983398439853986398739883989399039913992399339943995399639973998399940004001400240034004400540064007400840094010401140124013401440154016401740184019402040214022402340244025402640274028402940304031403240334034403540364037403840394040404140424043404440454046404740484049405040514052405340544055405640574058405940604061406240634064406540664067406840694070407140724073407440754076407740784079408040814082408340844085408640874088408940904091409240934094409540964097409840994100410141024103410441054106410741084109411041114112411341144115411641174118411941204121412241234124412541264127412841294130413141324133413441354136413741384139414041414142414341444145414641474148414941504151415241534154415541564157415841594160416141624163416441654166416741684169417041714172417341744175417641774178417941804181418241834184418541864187418841894190419141924193419441954196419741984199420042014202420342044205420642074208420942104211421242134214421542164217421842194220422142224223422442254226422742284229423042314232423342344235423642374238423942404241424242434244424542464247424842494250425142524253425442554256425742584259426042614262426342644265426642674268426942704271427242734274427542764277427842794280428142824283428442854286428742884289429042914292429342944295429642974298429943004301430243034304430543064307430843094310431143124313431443154316431743184319432043214322432343244325432643274328432943304331433243334334433543364337433843394340434143424343434443454346434743484349435043514352435343544355435643574358435943604361436243634364436543664367436843694370437143724373437443754376437743784379438043814382438343844385438643874388438943904391439243934394439543964397439843994400440144024403440444054406440744084409441044114412441344144415441644174418441944204421442244234424442544264427442844294430443144324433443444354436443744384439444044414442444344444445444644474448444944504451445244534454445544564457445844594460446144624463446444654466446744684469447044714472447344744475447644774478447944804481448244834484448544864487448844894490449144924493449444954496449744984499450045014502450345044505450645074508450945104511451245134514451545164517451845194520452145224523452445254526452745284529453045314532453345344535453645374538453945404541454245434544454545464547454845494550455145524553455445554556455745584559456045614562456345644565456645674568456945704571457245734574457545764577457845794580458145824583458445854586458745884589459045914592459345944595459645974598459946004601460246034604460546064607460846094610461146124613461446154616461746184619462046214622462346244625462646274628462946304631463246334634463546364637463846394640464146424643464446454646464746484649465046514652465346544655465646574658465946604661466246634664466546664667466846694670467146724673467446754676467746784679468046814682468346844685468646874688468946904691469246934694469546964697469846994700470147024703470447054706470747084709471047114712471347144715471647174718471947204721472247234724472547264727472847294730473147324733473447354736473747384739474047414742474347444745474647474748474947504751475247534754475547564757475847594760476147624763476447654766476747684769477047714772477347744775477647774778477947804781478247834784478547864787478847894790479147924793479447954796479747984799480048014802480348044805480648074808480948104811481248134814481548164817481848194820482148224823482448254826482748284829483048314832483348344835483648374838483948404841484248434844484548464847484848494850485148524853485448554856485748584859486048614862486348644865486648674868486948704871487248734874487548764877487848794880488148824883488448854886488748884889489048914892489348944895489648974898489949004901490249034904490549064907490849094910491149124913491449154916491749184919492049214922492349244925492649274928492949304931493249334934493549364937493849394940494149424943494449454946494749484949495049514952495349544955495649574958495949604961496249634964496549664967496849694970497149724973497449754976497749784979498049814982498349844985498649874988498949904991499249934994499549964997499849995000500150025003500450055006500750085009501050115012501350145015501650175018501950205021502250235024502550265027502850295030503150325033503450355036503750385039504050415042504350445045504650475048504950505051505250535054505550565057505850595060506150625063506450655066506750685069507050715072507350745075507650775078507950805081508250835084508550865087508850895090509150925093509450955096509750985099510051015102510351045105510651075108510951105111511251135114511551165117511851195120512151225123512451255126512751285129513051315132513351345135513651375138513951405141514251435144514551465147514851495150515151525153515451555156515751585159516051615162516351645165516651675168516951705171517251735174
  1. using Aspose.Cells;
  2. using NPOI.SS.Formula.Functions;
  3. using OASystem.API.OAMethodLib;
  4. using OASystem.Domain.Dtos.Statistics;
  5. using OASystem.Domain.Entities.Customer;
  6. using OASystem.Domain.Entities.Groups;
  7. using OASystem.Domain.ViewModels.Financial;
  8. using OASystem.Domain.ViewModels.QiYeWeChat;
  9. using OASystem.Domain.ViewModels.Statistics;
  10. using OASystem.Infrastructure.Repositories.Groups;
  11. using System;
  12. using System.Data;
  13. using static OASystem.API.OAMethodLib.GeneralMethod;
  14. using TypeInfo = OASystem.Domain.ViewModels.Statistics.TypeInfo;
  15. namespace OASystem.API.Controllers
  16. {
  17. /// <summary>
  18. /// 统计模块
  19. /// </summary>
  20. [Route("api/[controller]")]
  21. [ApiController]
  22. public class StatisticsController : ControllerBase
  23. {
  24. private readonly int _decimalPlaces;
  25. private readonly IConfiguration _config;
  26. private readonly IMapper _mapper;
  27. private readonly SqlSugarClient _sqlSugar;
  28. private readonly DelegationInfoRepository _groupRep;
  29. private readonly SetDataRepository _setDataRep;
  30. private readonly TeamRateRepository _teamRateRep;
  31. private readonly VisitingClientsRepository _visitingClientsRep;
  32. /// <summary>
  33. /// Init
  34. /// </summary>
  35. /// <param name="mapper"></param>
  36. /// <param name="sqlSugar"></param>
  37. /// <param name="groupRep"></param>
  38. /// <param name="setDataRep"></param>
  39. public StatisticsController(
  40. IMapper mapper,
  41. IConfiguration config,
  42. SqlSugarClient sqlSugar,
  43. DelegationInfoRepository groupRep,
  44. SetDataRepository setDataRep,
  45. TeamRateRepository teamRate,
  46. VisitingClientsRepository visitingClientsRep
  47. )
  48. {
  49. _mapper = mapper;
  50. _config = config;
  51. _groupRep = groupRep;
  52. _setDataRep = setDataRep;
  53. _sqlSugar = sqlSugar;
  54. _teamRateRep = teamRate;
  55. _visitingClientsRep = visitingClientsRep;
  56. }
  57. #region 团组报表
  58. /// <summary>
  59. /// 团组报表
  60. /// Items
  61. /// </summary>
  62. /// <param name="_dto">团组列表请求dto</param>
  63. /// <returns></returns>
  64. [HttpPost("PostGroupStatementItems")]
  65. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  66. public async Task<IActionResult> PostGroupStatementItems(GroupStatementItemsDto _dto)
  67. {
  68. #region 参数验证
  69. if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
  70. if (_dto.PageId < 1) return Ok(JsonView(false, "页面Id为空"));
  71. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  72. #region 页面操作权限验证
  73. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  74. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
  75. #endregion
  76. #endregion
  77. if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
  78. {
  79. string sqlWhere = string.Empty;
  80. if (_dto.IsSure == 0) //未完成
  81. {
  82. sqlWhere += string.Format(@" And IsSure = 0");
  83. }
  84. else if (_dto.IsSure == 1) //已完成
  85. {
  86. sqlWhere += string.Format(@" And IsSure = 1");
  87. }
  88. if (!string.IsNullOrEmpty(_dto.SearchCriteria))
  89. {
  90. string tj = _dto.SearchCriteria;
  91. sqlWhere += string.Format(@"And (ssd.Name Like '%{0}%' Or TeamName Like '%{1}%' Or ClientName Like '%{2}%' Or ClientName Like '%{3}%' Or su.CnName Like '%{4}%')",
  92. tj, tj, tj, tj, tj);
  93. }
  94. string sql = string.Format(@"Select row_number() over(order by gdi.VisitDate Desc) as Row_Number,
  95. gdi.Id,TourCode,ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,
  96. ClientName,ClientUnit,VisitDate,ssd.Id TeamTypeId, ssd.Name TeamType,
  97. VisitDays,VisitPNumber,su.CnName JietuanOperator,IsSure,gdi.CreateTime,
  98. pr.LastCollectionTime
  99. From Grp_DelegationInfo gdi
  100. Left Join Sys_SetData ssd On gdi.TeamDid = ssd.Id
  101. Left Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
  102. Left Join Sys_Users su On gdi.JietuanOperator = su.Id
  103. Left Join (
  104. SELECT Diid, MAX(CreateTime) LastCollectionTime
  105. FROM Fin_ProceedsReceived
  106. Where IsDel = 0
  107. GROUP BY Diid
  108. ) pr On gdi.Id = pr.Diid
  109. Where gdi.IsDel = 0 {0} ", sqlWhere);
  110. RefAsync<int> total = 0;//REF和OUT不支持异步,想要真的异步这是最优解
  111. var _DelegationList = await _sqlSugar.SqlQueryable<GroupStatementItemView>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);//ToPageAsync
  112. var _view = new
  113. {
  114. PageFuncAuth = pageFunAuthView,
  115. Data = _DelegationList
  116. };
  117. return Ok(JsonView(true, "查询成功!", _view, total));
  118. }
  119. else
  120. {
  121. return Ok(JsonView(false, "查询失败"));
  122. }
  123. }
  124. /// <summary>
  125. /// 团组报表
  126. /// Details
  127. /// </summary>
  128. /// <param name="_dto">团组列表请求dto</param>
  129. /// <returns></returns>
  130. [HttpPost("PostGroupStatementDetails")]
  131. //[JsonConverter(typeof(DecimalConverter), 2)]
  132. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  133. public async Task<IActionResult> PostGroupStatementDetails(GroupStatementDetailsDto _dto)
  134. {
  135. /*
  136. * 团组报表计算方式
  137. * 当前总支出 = 团组支出.Sum() + 超支费用.Sum()
  138. * 应收金额 = 应收表.Sum()
  139. * 已收金额 = 已收表.Sum()
  140. * 应收利润(应收-支出) = 应收金额 - 收款退还 - 当前总支出
  141. * 已收利润(已收-支出) = 已收金额 - 收款退还 - 当前总支出
  142. *
  143. */
  144. #region 参数验证
  145. if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
  146. if (_dto.PageId < 1) _dto.PageId = 38; //团组报表页面Id
  147. if (_dto.DiId < 1) return Ok(JsonView(false, "团组Id为空"));
  148. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  149. #region 页面操作权限验证
  150. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  151. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
  152. #endregion
  153. #endregion
  154. if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
  155. {
  156. GroupStatementDetailsView _view = new GroupStatementDetailsView();
  157. #region 费用类型 币种,转账,客户信息
  158. List<Sys_SetData> _setDatas = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToListAsync();
  159. var _clientDatas = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0).ToListAsync();
  160. #endregion
  161. #region 团组收入
  162. GroupIncomeView _giView = new GroupIncomeView();
  163. /*
  164. * 应收报表
  165. * 增加方式=实际报价时 费用必须审核才能进入团组报表
  166. */
  167. decimal frTotalAmount = 0.00M;//应收总金额
  168. string _frSql = string.Format(@" Select fr.Id,fr.AddingWay,fr.Status,u.CnName As Auditor,fr.AuditTime,fr.Diid,fr.PriceName,fr.Price,fr.Count,fr.Unit,fr.Currency,
  169. sd.Name As CurrencyCode,sd.Remark As CurrencyName,fr.Rate,fr.ItemSumPrice,fr.CreateTime
  170. From Fin_ForeignReceivables fr
  171. Left Join Sys_SetData sd On fr.Currency = sd.Id
  172. Left Join Sys_Users u On fr.Auditor = u.Id
  173. Where fr.IsDel = 0 And fr.Diid = {0} Order By CreateTime", _dto.DiId);
  174. List<Gsd_ForeignReceivablesView> _frViews = await _sqlSugar.SqlQueryable<Gsd_ForeignReceivablesView>(_frSql).ToListAsync();
  175. //List<Gsd_ForeignReceivablesView> _frViews = new List<Gsd_ForeignReceivablesView>();
  176. //if (_frViews1.Count > 0)
  177. //{
  178. // _frViews.AddRange(_frViews1.Where(x => x.AddingWay != 2).ToList());
  179. // _frViews.AddRange(_frViews1.Where(x => x.AddingWay == 2 && x.Status == 1).ToList());
  180. //}
  181. _frViews.ForEach(x =>
  182. {
  183. string namePrefix = string.Empty;
  184. if (x.AddingWay == 0) namePrefix = $"账单模块-";
  185. else if (x.AddingWay == 1) namePrefix = $"成本预算模块-";
  186. else if (x.AddingWay == 2) namePrefix = $"实际报价-";
  187. x.PriceName = $"{namePrefix}{x.PriceName}";
  188. });
  189. frTotalAmount = _frViews.Sum(it => it.ItemSumPrice);
  190. _giView.Receivables = _frViews;
  191. _giView.ReceivableStr = string.Format(@"应收款合计:{0} CNY(人民币)", frTotalAmount.ConvertToDecimal1().ToString("#0.00"));
  192. /*
  193. * 已收报表
  194. */
  195. decimal prTotalAmount = 0.00M;//已收总金额
  196. string _prSql = string.Format(@"Select pr.Id,pr.Diid,pr.SectionTime As SectionTimeDt,pr.Price,pr.Currency,
  197. sd1.Name As CurrencyCode,sd1.Remark As CurrencyName,pr.Client,
  198. pr.ReceivablesType,sd2.Name As ReceivablesTypeName,pr.Remark,pr.CreateTime
  199. From Fin_ProceedsReceived pr
  200. Left Join Sys_SetData sd1 On pr.Currency = sd1.Id
  201. Left Join Sys_SetData sd2 On pr.ReceivablesType = sd2.Id
  202. Where pr.IsDel = 0 and pr.Diid = {0} Order By CreateTime", _dto.DiId);
  203. List<Gsd_ProceedsReceivedView> _prViews = await _sqlSugar.SqlQueryable<Gsd_ProceedsReceivedView>(_prSql).ToListAsync();
  204. prTotalAmount = _prViews.Sum(it => it.Price);
  205. _giView.ProceedsReceivedViews = _prViews;
  206. _giView.ProceedsReceivedStr = string.Format(@$"应收合计:{frTotalAmount:#0.00} CNY 已收款合计:{prTotalAmount.ConvertToDecimal1():#0.00} CNY");
  207. /*
  208. * 超支费用
  209. */
  210. decimal exTotalAmount = 0.00M;
  211. // string ecSql = string.Format(@"Select gec.Id As GECId,gec.DiId As GECDiId,gec.PriceName,(gec.PriceSum * gec.Coefficient) As PayMoney,sd1.Name As PaymentCurrency,
  212. // (gec.PriceSum * gec.Coefficient * ccp.DayRate) As CNYPrice,ccp.DayRate,ccp.Payee,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
  213. // sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,gec.CreateTime
  214. //From OA2023DB.dbo.Fin_GroupExtraCost gec
  215. //Left Join Grp_CreditCardPayment ccp On gec.Id = ccp.CId
  216. // Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  217. // Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  218. // Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
  219. // Left Join Sys_Users u On ccp.CreateUserId = u.Id
  220. //Where ccp.IsDel = 0 And ccp.CTable = 1015 {1} And ccp.DiId = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1 And ccp.IsPay = 1 " : " ");
  221. // List<Gsd_ExtraCostsView> _ExtraCostsViews = await _sqlSugar.SqlQueryable<Gsd_ExtraCostsView>(ecSql).ToListAsync();
  222. // #region 超支费用 - 模拟数据
  223. // //if (_ExtraCostsViews.Count < 1)
  224. // //{
  225. // // _ExtraCostsViews.Add(new Gsd_ExtraCostsView()
  226. // // {
  227. // // GECId = 0,
  228. // // GECDiId = 2334,
  229. // // PriceName = "模拟数据-超支费用名称",
  230. // // PayMoney = 1000.00M,
  231. // // PaymentCurrency = "CNY",
  232. // // DayRate = 1.0000M,
  233. // // CNYPrice = 1000.00M,
  234. // // Payee = "模拟数据-超支费用收款方",
  235. // // OrbitalPrivateTransfer = 1,
  236. // // PayWay = "刷卡",
  237. // // CardType = "招行卡",
  238. // // IsPay = 1,
  239. // // Applicant = "刘华举"
  240. // // });
  241. // // _ExtraCostsViews.Add(new Gsd_ExtraCostsView()
  242. // // {
  243. // // GECId = 0,
  244. // // GECDiId = 2334,
  245. // // PriceName = "模拟数据-超支费用名称",
  246. // // PayMoney = 1000.00M,
  247. // // PaymentCurrency = "CNY",
  248. // // DayRate = 1.0000M,
  249. // // CNYPrice = 1000.00M,
  250. // // Payee = "模拟数据-超支费用收款方",
  251. // // OrbitalPrivateTransfer = 1,
  252. // // PayWay = "刷卡",
  253. // // CardType = "招行卡",
  254. // // IsPay = 1,
  255. // // Applicant = "刘华举"
  256. // // });
  257. // //}
  258. // #endregion
  259. // exTotalAmount = _ExtraCostsViews.Sum(it => it.CNYPrice);
  260. // _giView.ExtraCostsViews = _ExtraCostsViews;
  261. // _giView.ExtraCostsStr = string.Format(@"人民币总费用:{0} CNY", exTotalAmount.ConvertToDecimal1().ToString("#0.00"));
  262. /*
  263. * 收款退还
  264. */
  265. decimal promTotalAmount = 0.00M;// 收款退还总金额
  266. List<Gsd_PaymentRefundAndOtherMoneyView> _promView = new List<Gsd_PaymentRefundAndOtherMoneyView>();
  267. //删除了 And prom.PriceType = 1
  268. string _ropSql = string.Format(@"Select u.CnName As Appliction,prom.Id As PrId,prom.DiId As PrDiId,prom.Price As PrPrice,
  269. prom.PriceName AS PrPriceName,prom.CurrencyId As PrCurrencyId,
  270. prom.PayType As PrPayType,prom.PriceType As PrPriceType,
  271. ccp.RMBPrice * ccp.DayRate As RMBPrice,ccp.*,prom.CreateTime As PrCreateTime
  272. From Fin_PaymentRefundAndOtherMoney prom
  273. Left Join Grp_CreditCardPayment ccp On prom.DiId = ccp.DIId And prom.Id = ccp.CId
  274. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  275. Where prom.IsDel = 0
  276. And prom.PayType = 1
  277. And ccp.CTable = 285
  278. {1}
  279. And prom.DiId = {0} Order By PrCreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
  280. var _promDatas = await _sqlSugar.SqlQueryable<Gsd_PaymentRefundAndOtherMoneyDataSource1View>(_ropSql).ToListAsync();
  281. foreach (var ropItem in _promDatas)
  282. {
  283. string thisCueencyCode = "Unknown";
  284. string thisCueencyName = "Unknown";
  285. var currency = _setDatas.Where(it => it.Id == ropItem.PaymentCurrency).FirstOrDefault();
  286. if (currency != null)
  287. {
  288. thisCueencyCode = currency.Name;
  289. thisCueencyName = currency.Remark;
  290. }
  291. string orbitalPrivateTransferStr = "Unknown";
  292. var orbitalPrivateTransfer = _setDatas.Where(it => it.Id == ropItem.OrbitalPrivateTransfer).FirstOrDefault();
  293. if (orbitalPrivateTransfer != null)
  294. {
  295. orbitalPrivateTransferStr = orbitalPrivateTransfer.Name;
  296. }
  297. string payStr = "Unknown";
  298. var pay = _setDatas.Where(it => it.Id == ropItem.PayDId).FirstOrDefault();
  299. if (pay != null)
  300. {
  301. payStr = pay.Name;
  302. }
  303. Gsd_PaymentRefundAndOtherMoneyView gsd_PaymentRefund = new Gsd_PaymentRefundAndOtherMoneyView()
  304. {
  305. Id = ropItem.Id,
  306. DiId = ropItem.DIId,
  307. PriceName = ropItem.PrPriceName,
  308. PayCurrencyCode = thisCueencyCode,
  309. PayCurrencyName = thisCueencyName,
  310. Price = ropItem.PrPrice,
  311. CNYPrice = ropItem.PayMoney * ropItem.DayRate,
  312. ThisRate = ropItem.DayRate,
  313. Payee = ropItem.Payee,
  314. PayTime = ropItem.AuditGMDate,
  315. OrbitalPrivateTransfer = ropItem.OrbitalPrivateTransfer,
  316. PayType = payStr,
  317. IsPay = ropItem.IsPay,
  318. Applicant = ropItem.Appliction
  319. };
  320. _promView.Add(gsd_PaymentRefund);
  321. }
  322. #region 收款退还 - 模拟数据
  323. //if (_promView.Count < 1)
  324. //{
  325. // _promView.Add(new Gsd_PaymentRefundAndOtherMoneyView()
  326. // {
  327. // Id = 0,
  328. // DiId = 2334,
  329. // PriceName = "模拟数据-费用名称",
  330. // PayCurrencyCode = "CNY",
  331. // PayCurrencyName = "人民币",
  332. // Price = 1000.00M,
  333. // CNYPrice = 1000.00M,
  334. // ThisRate = 1.00M,
  335. // Payee = "模拟数据-收款方",
  336. // PayTime = "2023-01-01 15:20:01",
  337. // OrbitalPrivateTransfer = 1,
  338. // PayType = "刷卡",
  339. // IsPay = 1,
  340. // Applicant = "刘华举"
  341. // });
  342. // _promView.Add(new Gsd_PaymentRefundAndOtherMoneyView()
  343. // {
  344. // Id = 0,
  345. // DiId = 2334,
  346. // PriceName = "模拟数据-费用名称",
  347. // PayCurrencyCode = "CNY",
  348. // PayCurrencyName = "人民币",
  349. // Price = 1000.00M,
  350. // CNYPrice = 1000.00M,
  351. // ThisRate = 1.00M,
  352. // Payee = "模拟数据-收款方",
  353. // PayTime = "2023-01-01 15:20:01",
  354. // OrbitalPrivateTransfer = 1,
  355. // PayType = "刷卡",
  356. // IsPay = 1,
  357. // Applicant = "刘华举"
  358. // });
  359. //}
  360. #endregion
  361. promTotalAmount = _promView.Sum(it => it.CNYPrice);
  362. _giView.PaymentRefundAndOtherMoneyViews = _promView;
  363. _giView.PaymentRefundAndOtherMoneyStr = string.Format(@"人民币总费用:{0} CNY", promTotalAmount.ConvertToDecimal1().ToString("#0.00"));
  364. decimal BalancePayment = frTotalAmount - prTotalAmount + promTotalAmount;
  365. _view.GroupIncome = _giView;
  366. _view.GroupIncomeStr = string.Format(@"<span style='color:red;'>剩余尾款:{0} CNY(包含了收款退还费用数据)</span>", BalancePayment.ConvertToDecimal1().ToString("#0.00"));
  367. #endregion
  368. #region 团组支出
  369. GroupExpenditureView _geView = new GroupExpenditureView();
  370. #region 酒店预定费用
  371. List<GroupHotelFeeView> groupHotelFeeViews = new List<GroupHotelFeeView>();
  372. //ccp.RMBPrice As CNYPrice
  373. //(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice
  374. string hotelFeeSql = string.Format(@"
  375. SELECT
  376. hr.Id AS HrId,
  377. hr.DiId AS HrDiId,
  378. hr.City,
  379. hr.HotelName,
  380. hr.CheckInDate,
  381. hr.CheckOutDate,
  382. hr.CardPrice AS RoomPrice,
  383. sd1.Name AS PaymentCurrency,
  384. hr.SingleRoomPrice,
  385. hr.SingleRoomCount,
  386. hr.DoubleRoomPrice,
  387. hr.DoubleRoomCount,
  388. hr.SuiteRoomPrice,
  389. hr.SuiteRoomCount,
  390. hr.OtherRoomPrice,
  391. hr.OtherRoomCount,
  392. hr.BreakfastPrice,
  393. sd4.Name AS BreakfastCurrency,
  394. hr.Isoppay,
  395. hr.GovernmentRent,
  396. sd5.Name AS GovernmentRentCurrency,
  397. hr.CityTax,
  398. sd6.Name AS CityTaxCurrency,
  399. ccp.PayMoney,
  400. (
  401. ((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
  402. ) AS CNYPrice,
  403. ccp.PayPercentage,
  404. ccp.DayRate,
  405. ccp.Payee,
  406. ccp.OrbitalPrivateTransfer,
  407. sd2.Name AS PayWay,
  408. sd3.Name AS CardType,
  409. ccp.IsPay,
  410. u.CnName AS Applicant
  411. FROM
  412. Grp_HotelReservations hr
  413. INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
  414. LEFT JOIN Sys_SetData sd1 ON ccp.PaymentCurrency = sd1.Id
  415. LEFT JOIN Sys_SetData sd2 ON ccp.PayDId = sd2.Id
  416. LEFT JOIN Sys_SetData sd3 ON ccp.CTDId = sd3.Id
  417. LEFT JOIN Sys_Users u ON ccp.CreateUserId = u.Id
  418. LEFT JOIN Sys_SetData sd4 ON hr.BreakfastCurrency = sd4.Id
  419. LEFT JOIN Sys_SetData sd5 ON hr.GovernmentRentCurrency = sd5.Id
  420. LEFT JOIN Sys_SetData sd6 ON hr.CityTaxCurrency = sd6.Id
  421. WHERE
  422. hr.IsDel = 0
  423. AND ccp.IsDel = 0
  424. AND ccp.CTable = 76 {1}
  425. AND ccp.PayMoney <> 0
  426. AND hr.DiId = {0}
  427. ORDER BY
  428. CheckInDate Asc", _dto.DiId, _dto.isAudit ? "AND (ccp.IsAuditGM = 1 Or ccp.IsAuditGM = 3)" : " ");
  429. groupHotelFeeViews = await _sqlSugar.SqlQueryable<GroupHotelFeeView>(hotelFeeSql).ToListAsync();
  430. List<int> hotelSubIds = groupHotelFeeViews.Select(it => it.HrId).ToList();
  431. List<Grp_HotelReservationsContent> groupHotelContentFeeViews = new List<Grp_HotelReservationsContent>();
  432. groupHotelContentFeeViews = await _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => hotelSubIds.Contains(it.HrId)).ToListAsync();
  433. decimal HotelCNYTotalPrice = 0.00M;
  434. var teamRateData = await _teamRateRep.PostGroupRateInfoByDiId(_dto.DiId);
  435. foreach (var item in groupHotelFeeViews)
  436. {
  437. if (groupHotelContentFeeViews.Count > 0)
  438. {
  439. string paymentStr = string.Empty;
  440. var roomData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 1); //房费
  441. item.RoomPrice = roomData?.Price ?? 0.00M;
  442. if (item.RoomPrice != 0)
  443. {
  444. if (roomData.IsPay == 0) paymentStr += $"房费:未付款<br/>";
  445. item.RoomPriceCurrency = _setDatas.Find(it => it.Id == roomData?.Currency)?.Name;
  446. string feeMark1 = roomData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
  447. string isFeeMark1 = roomData?.IsOppay == 1 ? "是" : "否";
  448. item.RoomInfoTips = @$"当时汇率:{roomData?.Rate.ToString("#0.0000")} <br/>
  449. 收款方:{roomData?.Payee}<br/>
  450. 费用标识:{feeMark1} <br/>
  451. 支付方式:{_setDatas.Find(it => it.Id == roomData?.PayDId)?.Name} <br/>
  452. 卡类型:{_setDatas.Find(it => it.Id == roomData?.CTDId)?.Name}<br/>
  453. 是否由地接支付:{isFeeMark1} <br/>";
  454. }
  455. var breakfastData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 2); //早餐
  456. item.BreakfastPrice = breakfastData?.Price ?? 0.00M;
  457. if (item.BreakfastPrice != 0)
  458. {
  459. if (breakfastData.IsPay == 0) paymentStr += $"早餐:未付款<br/>";
  460. item.BreakfastCurrency = _setDatas.Find(it => it.Id == breakfastData?.Currency)?.Name;
  461. string feeMark2 = breakfastData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
  462. string isFeeMark2 = breakfastData?.IsOppay == 1 ? "是" : "否";
  463. item.BreakfastInfoTips = @$"当时汇率:{breakfastData?.Rate.ToString("#0.0000")} <br/>
  464. 收款方:{breakfastData?.Payee}<br/>
  465. 费用标识:{feeMark2} <br/>
  466. 支付方式:{_setDatas.Find(it => it.Id == roomData?.PayDId)?.Name} <br/>
  467. 卡类型:{_setDatas.Find(it => it.Id == roomData?.CTDId)?.Name} <br/>
  468. 是否由地接支付:{isFeeMark2} <br/>";
  469. }
  470. var landTaxData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 3); //地税
  471. item.GovernmentRent = landTaxData?.Price ?? 0.00M;
  472. if (item.GovernmentRent != 0)
  473. {
  474. if (landTaxData.IsPay == 0) paymentStr += $"地税:未付款<br/>";
  475. item.GovernmentRentCurrency = _setDatas.Find(it => it.Id == landTaxData?.Currency)?.Name;
  476. string feeMark3 = landTaxData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
  477. string isFeeMark3 = landTaxData?.IsOppay == 1 ? "是" : "否";
  478. item.GovernmentRentTips = @$"当时汇率:{landTaxData?.Rate.ToString("#0.0000")} <br/>
  479. 收款方:{landTaxData?.Payee}<br/>
  480. 费用标识:{feeMark3} <br/>
  481. 支付方式:{_setDatas.Find(it => it.Id == landTaxData?.PayDId)?.Name} <br/>
  482. 卡类型:{_setDatas.Find(it => it.Id == landTaxData?.CTDId)?.Name} <br/>
  483. 是否由地接支付:{isFeeMark3} <br/>";
  484. }
  485. var cityTaxData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 4); //城市税
  486. item.CityTax = cityTaxData?.Price ?? 0.00M;
  487. if (item.CityTax != 0)
  488. {
  489. if (cityTaxData.IsPay == 0) paymentStr += $"城市税:未付款<br/>";
  490. item.CityTaxCurrency = _setDatas.Find(it => it.Id == cityTaxData?.Currency)?.Name;
  491. string feeMark4 = cityTaxData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
  492. string isFeeMark4 = landTaxData?.IsOppay == 1 ? "是" : "否";
  493. item.CityTaxTips = @$"当时汇率:{cityTaxData?.Rate.ToString("#0.0000")} <br/>
  494. 收款方:{cityTaxData?.Payee}<br/>
  495. 费用标识:{feeMark4} <br/>
  496. 支付方式:{_setDatas.Find(it => it.Id == cityTaxData?.PayDId)?.Name} <br/>
  497. 卡类型:{_setDatas.Find(it => it.Id == cityTaxData?.CTDId)?.Name} <br/>
  498. 是否由地接支付:{isFeeMark4} <br/>";
  499. }
  500. if (!string.IsNullOrEmpty(paymentStr))
  501. {
  502. item.IsPay = 2;
  503. item.PayTips = paymentStr;
  504. }
  505. }
  506. else
  507. {
  508. decimal roomPrice = (item.SingleRoomCount * item.SingleRoomPrice) +
  509. (item.DoubleRoomCount * item.DoubleRoomPrice) +
  510. (item.SuiteRoomCount * item.SuiteRoomPrice) +
  511. (item.OtherRoomCount * item.OtherRoomPrice);
  512. //item.RoomPrice = item.CardPrice;
  513. item.RoomPriceCurrency = item.PaymentCurrency;
  514. }
  515. HotelCNYTotalPrice += item.CNYPrice;
  516. item.PayMoney = item.PayMoney.ConvertToDecimal1();
  517. item.CNYPrice = item.CNYPrice.ConvertToDecimal1();
  518. }
  519. _geView.GroupHotelFeeViews = groupHotelFeeViews;
  520. _geView.GroupHotelFeeStr = string.Format(@"人民币总费用:{0} CNY", HotelCNYTotalPrice.ToString("#0.00"));
  521. #endregion
  522. #region 地接费用
  523. List<GroupCTGGRFeeView> groupCTGGRFeeViews = new List<GroupCTGGRFeeView>();
  524. string CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.PriceName As Area,ctggrc.*,ctggrc.Price As PayMoney,
  525. sd2.name As PaymentCurrency,ccp.PayPercentage,
  526. (ctggrc.Price * (ccp.PayPercentage / 100)) As AmountPaid,
  527. (ctggrc.Price - ctggrc.Price * (ccp.PayPercentage / 100)) As BalancePayment,
  528. ccp.DayRate,(ctggrc.Price * (ccp.PayPercentage / 100) * ccp.DayRate) As CNYPrice,ccp.Payee,ccp.AuditGMDate,
  529. ccp.OrbitalPrivateTransfer,sd1.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ctggr.CreateTime
  530. From Grp_CarTouristGuideGroundReservations ctggr
  531. Left Join ( Select cggrc.CTGGRId,sd1.Name As PriceName,cggrc.Price*cggrc.Count As Price,sd2.Name As PriceCurrency,
  532. cggrc.DatePrice,cggrc.PriceContent
  533. From Grp_CarTouristGuideGroundReservationsContent cggrc
  534. Left Join Sys_SetData sd1 On cggrc.SId = sd1.Id
  535. Left Join Sys_SetData sd2 On cggrc.Currency = sd2.Id
  536. Where cggrc.ISdel = 0 And cggrc.Price != 0.00
  537. ) ctggrc On ctggr.Id = ctggrc.CTGGRId
  538. Left Join Grp_CreditCardPayment ccp On ccp.IsDel = 0 And ccp.CTable = 79 And ctggr.Id = ccp.CId
  539. Left Join Sys_SetData sd1 On ccp.PayDId = sd1.Id
  540. Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
  541. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  542. Where ctggr.IsDel = 0 {1} And ctggr.DiId = {0}
  543. Order By CreateTime", _dto.DiId, _dto.isAudit ? "And (ccp.IsAuditGM = 1 OR ccp.IsAuditGM = 3)" : " ");
  544. groupCTGGRFeeViews = await _sqlSugar.SqlQueryable<GroupCTGGRFeeView>(CTGGRFeeSql).ToListAsync();
  545. string CTGGRFeeStr = "";
  546. decimal CTGGRCNYTotalPrice = 0.00M;
  547. //按1 地区,2 币种,3 汇率 分组计算
  548. var groupCTGGRFeeDatas = groupCTGGRFeeViews.GroupBy(it => it.Area);
  549. foreach (var ctggfr in groupCTGGRFeeDatas)
  550. {
  551. var ctggfr_curr = ctggfr.GroupBy(it => it.PaymentCurrency);
  552. if (ctggfr_curr.Count() > 0)
  553. {
  554. foreach (var curr in ctggfr_curr)
  555. {
  556. var ctggfr_rate = curr.GroupBy(it => it.DayRate);
  557. if (ctggfr_rate.Count() > 0)
  558. {
  559. foreach (var rate in ctggfr_rate)
  560. {
  561. CTGGRFeeStr += string.Format(@$"{ctggfr.Key} 总费用:{rate.Sum(it => it.AmountPaid).ToString("#0.00")}
  562. {rate.FirstOrDefault()?.PaymentCurrency}(人民币:
  563. {rate.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00"))).ToString("#0.00")} CNY 当时支付汇率:
  564. {rate.FirstOrDefault()?.DayRate.ToString("#0.0000")})\r\n");
  565. CTGGRCNYTotalPrice += rate.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
  566. }
  567. }
  568. else
  569. {
  570. CTGGRFeeStr += string.Format(@$"{ctggfr.Key} 总费用:{curr.Sum(it => it.AmountPaid).ToString("#0.00")}
  571. {curr.FirstOrDefault()?.PaymentCurrency}(人民币:
  572. {curr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00"))).ToString("#0.00")} CNY 当时支付汇率:
  573. {curr.FirstOrDefault()?.DayRate.ToString("#0.0000")})\r\n");
  574. CTGGRCNYTotalPrice += curr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
  575. }
  576. }
  577. }
  578. else
  579. {
  580. CTGGRFeeStr += string.Format(@$"{ctggfr.Key} 总费用:{ctggfr.Sum(it => it.AmountPaid).ToString("#0.00")}
  581. {ctggfr.FirstOrDefault()?.PaymentCurrency}(人民币:
  582. {ctggfr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00"))).ToString("#0.00")} CNY 当时支付汇率:
  583. {ctggfr.FirstOrDefault()?.DayRate.ToString("#0.0000")})\r\n");
  584. CTGGRCNYTotalPrice += ctggfr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
  585. }
  586. }
  587. foreach (var item in groupCTGGRFeeViews)
  588. {
  589. if (!string.IsNullOrEmpty(item.AuditGMDate))
  590. {
  591. item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
  592. }
  593. if (item.DatePrice != null)
  594. {
  595. item.PriceName = $"{item.PriceName}({Convert.ToDateTime(item.DatePrice).ToString("yyyy-MM-dd")})";
  596. }
  597. //CTGGRFeeStr += string.Format(@"{0} 总费用:{1} {2}(人民币:{3} CNY 当时支付汇率:{4})\r\n",
  598. // item.Area, item.AmountPaid.ConvertToDecimal1().ToString("#0.00"), item.PaymentCurrency, item.CNYPrice.ToString("#0.0000"), item.DayRate.ToString("#0.0000"));
  599. //CTGGRCNYTotalPrice += item.CNYPrice;
  600. }
  601. _geView.GroupCTGGRFeeViews = groupCTGGRFeeViews;
  602. _geView.GroupCTGGRFeeStr = string.Format(@"{0}人民币总费用:{1} CNY", CTGGRFeeStr, CTGGRCNYTotalPrice.ToString("#0.00"));
  603. #endregion
  604. #region 机票预订费用
  605. //(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice
  606. //ccp.RMBPrice As CNYPrice
  607. List<GroupAirFeeView> groupAirFeeViews = new List<GroupAirFeeView>();
  608. string groupAirFeeSql = string.Format(@"Select atr.Id As AirId,atr.DIId As AirDiId,atr.FlightsCode,atr.FlightsCity,sd4.Name As AirTypeName,
  609. atr.FlightsDate,atr.FlightsTime,atr.ClientName,atr.ClientNum,ccp.PayMoney,
  610. sd1.Name As PayMoneyCurrency,(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,ccp.DayRate,ccp.Payee,ccp.AuditGMDate,
  611. ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,atr.CreateTime,
  612. atr.CType
  613. From Grp_AirTicketReservations atr
  614. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 85 And atr.Id = ccp.CId
  615. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  616. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  617. Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
  618. Left Join Sys_SetData sd4 On atr.CType = sd4.Id
  619. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  620. Where atr.IsDel = 0 {1} And atr.DiId = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
  621. groupAirFeeViews = await _sqlSugar.SqlQueryable<GroupAirFeeView>(groupAirFeeSql).ToListAsync();
  622. string str = "";
  623. List<dynamic> airClientPris = new List<dynamic>();
  624. decimal AirCNYTotalPrice = 0.00M;
  625. decimal JJCCNYTotalPrice = 0.00M, JJCPeopleNum = 0.00M, JJCAveragePrice = 0.00M;
  626. decimal GWCCNYTotalPrice = 0.00M, GWCPeopleNum = 0.00M, GWCAveragePrice = 0.00M;
  627. //if (groupAirFeeViews.Count > 0)
  628. //{
  629. // JJCCNYTotalPrice = groupAirFeeViews.Where(it => it.AirTypeName.Equals("经济舱")).Sum(it => it.CNYPrice);
  630. // JJCPeopleNum = groupAirFeeViews.Where(it => it.AirTypeName.Equals("经济舱")).Sum(it => it.ClientNum);
  631. // JJCAveragePrice = (JJCCNYTotalPrice / JJCPeopleNum).ConvertToDecimal1();
  632. // GWCCNYTotalPrice = groupAirFeeViews.Where(it => it.AirTypeName.Equals("公务舱")).Sum(it => it.CNYPrice);
  633. // GWCPeopleNum = groupAirFeeViews.Where(it => it.AirTypeName.Equals("公务舱")).Sum(it => it.ClientNum);
  634. // GWCAveragePrice = (GWCCNYTotalPrice / GWCPeopleNum).ConvertToDecimal1();
  635. //}
  636. int Index = 0;
  637. foreach (var item in groupAirFeeViews)
  638. {
  639. if (item.AirId > 2924)
  640. {
  641. string itemClientName = "";
  642. if (!string.IsNullOrEmpty(item.ClientName))
  643. {
  644. System.Text.RegularExpressions.Match m_EnName = Regex.Match(item.ClientName, @"[A-Za-z]+");
  645. System.Text.RegularExpressions.Match m_ZHName = Regex.Match(item.ClientName, @"[\u4e00-\u9fa5]");
  646. if (m_EnName.Success || m_ZHName.Success)
  647. {
  648. itemClientName = item.ClientName;
  649. decimal unitCost = 0.00M;
  650. AirCNYTotalPrice += item.CNYPrice;
  651. continue;
  652. }
  653. string[] clientIds = new string[] { };
  654. if (item.ClientName.Contains(','))
  655. {
  656. clientIds = item.ClientName.Split(',');
  657. }
  658. else
  659. {
  660. clientIds = new string[] { item.ClientName };
  661. }
  662. if (clientIds.Length > 0)
  663. {
  664. int[] output = Array.ConvertAll<string, int>(clientIds, delegate (string s) { return int.Parse(s); });
  665. if (output.Contains(-1))
  666. {
  667. itemClientName += $@"行程单";
  668. output = output.Where(val => val != -1).ToArray();
  669. }
  670. var clients = _clientDatas.Where(it => output.Contains(it.Id)).ToList();
  671. decimal unitCost = 0.00M;
  672. unitCost = (item.PayMoney / item.ClientNum).ConvertToDecimal1();
  673. int clienIndex = 1;
  674. foreach (var client in clients)
  675. {
  676. airClientPris.Add(new
  677. {
  678. CnName = client.LastName + client.FirstName,
  679. EnName = client.Pinyin,
  680. Price = unitCost,
  681. AirType = item.AirTypeName
  682. });
  683. string six = "";
  684. if (client.Sex == 0) six = "Mr";
  685. else if (client.Sex == 1) six = "Ms";
  686. itemClientName += string.Format(@"{0}.{1} {2};", clienIndex, client.LastName + client.FirstName, six);
  687. clienIndex++;
  688. }
  689. }
  690. }
  691. item.ClientName = itemClientName;
  692. }
  693. else
  694. {
  695. string clientPinYinName = "";
  696. decimal unitCost = 0.00M;
  697. int cNum = item.ClientNum == 0 ? 1 : item.ClientNum;
  698. unitCost = (item.PayMoney / cNum).ConvertToDecimal1();
  699. Regex r = new Regex("[0-9]");
  700. string name1 = item.ClientName;
  701. name1 = r.Replace(name1, "");
  702. string[] clientNames = name1.Split('.');
  703. for (int i = 0; i < item.ClientNum; i++)
  704. {
  705. string name = "";
  706. if (clientNames.Length > 0)
  707. {
  708. int index = i + 1;
  709. if (index < clientNames.Length)
  710. {
  711. name = clientNames[index].Replace("MR", "").Replace("MS", "").Trim();
  712. if (!string.IsNullOrEmpty(name))
  713. {
  714. airClientPris.Add(new
  715. {
  716. CnName = name,
  717. EnName = name,
  718. Price = unitCost,
  719. AirType = item.AirTypeName
  720. });
  721. }
  722. //if (name.Length > 0)
  723. //{
  724. // string nameLastStr = name[name.Length - 1].ToString();
  725. // if (nameLastStr.IsNumeric())
  726. // {
  727. // name = name.Substring(0, name.Length - 1).Trim();
  728. // }
  729. //}
  730. }
  731. }
  732. clientPinYinName += string.Format(@"{0}.{1}出票价为:{2} CNY;", Index + 1, name, unitCost.ToString("#0.00"));
  733. }
  734. }
  735. if (!string.IsNullOrEmpty(item.AuditGMDate))
  736. {
  737. item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
  738. }
  739. AirCNYTotalPrice += item.CNYPrice;
  740. }
  741. _geView.GroupAirFeeViews = groupAirFeeViews;
  742. if (airClientPris.Count > 0)
  743. {
  744. var peoplePriStr = "";
  745. var airClientPris1 = airClientPris.GroupBy(item => item.CnName)
  746. .Select(group => group.First())
  747. .ToList();
  748. int airClientPrisIndex = 1;
  749. foreach (var item in airClientPris1)
  750. {
  751. decimal price = 0.00M;
  752. var prices = airClientPris.Where(it => it.CnName == item.CnName).ToList();
  753. foreach (var pri in prices)
  754. {
  755. price += pri.Price;
  756. }
  757. peoplePriStr += $@"{airClientPrisIndex}.{item.EnName}出票价为: {price.ToString("#0.00")} CNY;";
  758. airClientPrisIndex++;
  759. }
  760. if (!string.IsNullOrEmpty(peoplePriStr))
  761. {
  762. str = $@"其中:{peoplePriStr}";
  763. }
  764. //经济舱均价
  765. var airJJCPris = airClientPris.Where(it => it.AirType == "经济舱").ToList();
  766. if (airJJCPris.Count > 0)
  767. {
  768. decimal jjcTotalPrice = 0.00M;
  769. foreach (var item in airJJCPris)
  770. {
  771. jjcTotalPrice += item.Price;
  772. }
  773. decimal jjcPeopleNum = airJJCPris.GroupBy(item => item.CnName)
  774. .Select(group => group.First())
  775. .ToList().Count;
  776. JJCAveragePrice = jjcTotalPrice / jjcPeopleNum;
  777. }
  778. //公务舱均价
  779. var airGWCPris = airClientPris.Where(it => it.AirType == "公务舱").ToList();
  780. if (airGWCPris.Count > 0)
  781. {
  782. decimal gwcTotalPrice = 0.00M;
  783. foreach (var item in airGWCPris)
  784. {
  785. gwcTotalPrice += item.Price;
  786. }
  787. decimal gwcPeopleNum = airGWCPris.GroupBy(item => item.CnName)
  788. .Select(group => group.First())
  789. .ToList().Count;
  790. GWCAveragePrice = gwcTotalPrice / gwcPeopleNum;
  791. }
  792. }
  793. _geView.GroupAirFeeStr = $@"人民币总费用:{AirCNYTotalPrice.ToString("#0.00")} CNY\r\n{str}\r\n经济舱均价为:{JJCAveragePrice.ToString("#0.00")}CNY/人;公务舱均价为:{GWCAveragePrice.ToString("#0.00")}CNY/人;";
  794. #endregion
  795. #region 签证费用
  796. List<GroupVisaFeeView> groupVisaFeeViews = new List<GroupVisaFeeView>();
  797. string groupVisaFeeSql = string.Format(@"Select vi.Id As VisaId,vi.DIId As VisaDiId,vi.VisaClient,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
  798. ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
  799. sd3.Name As CardTypeName,ccp.IsPay,u.CnName As Applicant,vi.CreateTime,
  800. (((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice
  801. From Grp_VisaInfo vi
  802. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 80 And vi.Id = ccp.CId
  803. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  804. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  805. Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
  806. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  807. Where vi.IsDel = 0 {1} And vi.DIId = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
  808. groupVisaFeeViews = await _sqlSugar.SqlQueryable<GroupVisaFeeView>(groupVisaFeeSql).ToListAsync();
  809. decimal VisaCNYTotalPirce = 0.00M;
  810. foreach (var item in groupVisaFeeViews)
  811. {
  812. string itemClientName = "";
  813. string visaClients = item.VisaClient;
  814. if (!string.IsNullOrEmpty(visaClients))
  815. {
  816. string[] clientIds = new string[] { };
  817. if (visaClients.Contains(','))
  818. {
  819. clientIds = visaClients.Split(',');
  820. }
  821. else
  822. {
  823. clientIds = new string[] { visaClients };
  824. }
  825. if (clientIds.Length > 0)
  826. {
  827. List<int> clientIds1 = new List<int>() { };
  828. foreach (var clientIdStr in clientIds)
  829. {
  830. if (clientIdStr.IsNumeric())
  831. {
  832. clientIds1.Add(int.Parse(clientIdStr));
  833. }
  834. }
  835. if (clientIds1.Count > 0)
  836. {
  837. var clients = _clientDatas.Where(it => clientIds1.Contains(it.Id)).ToList();
  838. foreach (var client in clients)
  839. {
  840. itemClientName += $"{client.LastName + client.FirstName},";
  841. }
  842. }
  843. else
  844. {
  845. itemClientName = visaClients;
  846. }
  847. }
  848. }
  849. if (itemClientName.Length > 0)
  850. {
  851. itemClientName = itemClientName.Substring(0, itemClientName.Length - 1);
  852. }
  853. item.VisaClient = itemClientName;
  854. VisaCNYTotalPirce += item.CNYPrice;
  855. if (!string.IsNullOrEmpty(item.AuditGMDate))
  856. {
  857. item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
  858. }
  859. }
  860. _geView.GroupVisaFeeViews = groupVisaFeeViews;
  861. _geView.GroupVisaFeeStr = string.Format(@"人民币总费用:{0} CNY", VisaCNYTotalPirce.ConvertToDecimal1().ToString("#.00"));
  862. #endregion
  863. #region 邀请/公务活动 CTable = 81
  864. List<GroupInvitationalFeeView> groupInvitationalFeeViews = new List<GroupInvitationalFeeView>();
  865. string groupInvitationalFeeSql = string.Format(@"Select ioa.Id As IOAId,ioa.DiId As IOADiId,ioa.InviterArea,ioa.Inviter,ioa.InviteTime,
  866. ioa.InviteCost,sd3.Name As InviteCurrency,ioa.SendCost,sd4.Name As SendCurrency,ioa.EventsCost,
  867. sd5.Name As EventsCurrency,ioa.TranslateCost,sd6.Name As TranslateCurrency,ccp.PayMoney,
  868. sd7.Name As PaymentCurrency,ccp.RMBPrice As CNYPrice,
  869. (((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice2,ccp.Payee,ccp.AuditGMDate,
  870. ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ioa.CreateTime
  871. From Grp_InvitationOfficialActivities ioa
  872. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 81 And ioa.Id = ccp.CId
  873. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  874. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  875. Left Join Sys_SetData sd3 On ioa.InviteCurrency = sd3.Id
  876. Left Join Sys_SetData sd4 On ioa.SendCurrency = sd4.Id
  877. Left Join Sys_SetData sd5 On ioa.EventsCurrency = sd5.Id
  878. Left Join Sys_SetData sd6 On ioa.TranslateCurrency = sd6.Id
  879. Left Join Sys_SetData sd7 On ccp.PaymentCurrency = sd7.Id
  880. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  881. Where ioa.IsDel = 0 {1} And ioa.Diid = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
  882. groupInvitationalFeeViews = await _sqlSugar.SqlQueryable<GroupInvitationalFeeView>(groupInvitationalFeeSql).ToListAsync();
  883. #region 邀请/公务活动 - 模拟数据
  884. //if (groupInvitationalFeeViews.Count < 1)
  885. //{
  886. // groupInvitationalFeeViews.Add(new GroupInvitationalFeeView()
  887. // {
  888. // IOAId = 0,
  889. // IOADiId = 2334,
  890. // InviterArea = "模拟数据-邀请方地区",
  891. // Inviter = "模拟数据-邀请方",
  892. // InviteTime = "2023-10-10",
  893. // InviteCost = 100.00M,
  894. // InviteCurrency = "EUR",
  895. // SendCost = 100.00M,
  896. // SendCurrency = "EUR",
  897. // EventsCost = 10000.00M,
  898. // EventsCurrency = "EUR",
  899. // TranslateCost = 300.00M,
  900. // TranslateCurrency = "EUR",
  901. // PayMoney = 10500.00M,
  902. // PaymentCurrency = "EUR",
  903. // CNYPrice = 76765.50M,
  904. // Payee = "模拟数据-收款方",
  905. // AuditGMDate = "2023-12-05",
  906. // OrbitalPrivateTransfer = 1,
  907. // PayWay = "刷卡",
  908. // IsPay = 1,
  909. // Applicant = "刘华举"
  910. // });
  911. // groupInvitationalFeeViews.Add(new GroupInvitationalFeeView()
  912. // {
  913. // IOAId = 0,
  914. // IOADiId = 2334,
  915. // InviterArea = "模拟数据-邀请方地区",
  916. // Inviter = "模拟数据-邀请方",
  917. // InviteTime = "2023-10-10",
  918. // InviteCost = 100.00M,
  919. // InviteCurrency = "EUR",
  920. // SendCost = 100.00M,
  921. // SendCurrency = "EUR",
  922. // EventsCost = 10000.00M,
  923. // EventsCurrency = "EUR",
  924. // TranslateCost = 300.00M,
  925. // TranslateCurrency = "EUR",
  926. // PayMoney = 10500.00M,
  927. // PaymentCurrency = "EUR",
  928. // CNYPrice = 76765.50M,
  929. // Payee = "模拟数据-收款方",
  930. // AuditGMDate = "2023-12-05",
  931. // OrbitalPrivateTransfer = 1,
  932. // PayWay = "刷卡",
  933. // IsPay = 1,
  934. // Applicant = "刘华举"
  935. // });
  936. //}
  937. #endregion
  938. decimal InvitationalCNYTotalPrice = 0.00M;
  939. foreach (var item in groupInvitationalFeeViews)
  940. {
  941. InvitationalCNYTotalPrice += item.CNYPrice2;
  942. if (!string.IsNullOrEmpty(item.AuditGMDate))
  943. {
  944. item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
  945. }
  946. string currencyRateStr = "";
  947. List<string> currencys = new List<string>();
  948. if (!string.IsNullOrEmpty(item.InviteCurrency)) currencys.Add(item.InviteCurrency);
  949. if (!string.IsNullOrEmpty(item.SendCurrency)) currencys.Add(item.SendCurrency);
  950. if (!string.IsNullOrEmpty(item.EventsCurrency)) currencys.Add(item.EventsCurrency);
  951. if (!string.IsNullOrEmpty(item.TranslateCurrency)) currencys.Add(item.TranslateCurrency);
  952. if (!string.IsNullOrEmpty(item.PaymentCurrency)) currencys.Add(item.PaymentCurrency);
  953. currencyRateStr = await GeneralMethod.PostGroupRateByCTableAndCurrency(teamRateData, 81, currencys);
  954. item.CurrencyRateStr = currencyRateStr;
  955. }
  956. _geView.GroupInvitationalFeeViews = groupInvitationalFeeViews;
  957. _geView.GroupInvitationalFeeStr = string.Format(@"人民币总费用:{0} CNY", InvitationalCNYTotalPrice.ToString("#.00"));
  958. #endregion
  959. #region 保险费用
  960. List<GroupInsuranceFeeView> groupInsuranceFeeViews = new List<GroupInsuranceFeeView>();
  961. string groupInsuranceFeeSql = string.Format(@"Select ic.Id As InsuranceId,ic.Diid As InsuranceDiId,ClientName,ccp.PayMoney,ccp.PayMoney * ccp.DayRate As CNYPrice,
  962. sd1.Name As PayMoneyCurrency,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,
  963. sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ic.CreateTime
  964. From Grp_Customers ic
  965. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 82 And ic.Id = ccp.CId
  966. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  967. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  968. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  969. Where ic.IsDel = 0 {1} And ic.DiId = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
  970. groupInsuranceFeeViews = await _sqlSugar.SqlQueryable<GroupInsuranceFeeView>(groupInsuranceFeeSql).ToListAsync();
  971. decimal InsuranceCNYTotalPrice = 0.00M;
  972. foreach (var item in groupInsuranceFeeViews)
  973. {
  974. InsuranceCNYTotalPrice += item.CNYPrice;
  975. string itemClientName = "";
  976. string insClients = item.ClientName;
  977. if (!string.IsNullOrEmpty(insClients))
  978. {
  979. //System.Text.RegularExpressions.Match m_EnName = Regex.Match(item.ClientName, @"[A-Za-z]+");
  980. //System.Text.RegularExpressions.Match m_ZHName = Regex.Match(item.ClientName, @"[\u4e00-\u9fa5]");
  981. //if (m_EnName.Success || m_ZHName.Success)
  982. //{
  983. // itemClientName = insClients;
  984. // continue;
  985. //}
  986. string[] clientIds = new string[] { };
  987. if (insClients.Contains(','))
  988. {
  989. clientIds = insClients.Split(',');
  990. }
  991. else
  992. {
  993. clientIds = new string[] { insClients };
  994. }
  995. if (clientIds.Length > 0)
  996. {
  997. List<int> output = new List<int>();
  998. foreach (var clientId in clientIds)
  999. {
  1000. if (clientId.IsNumeric())
  1001. {
  1002. output.Add(int.Parse(clientId));
  1003. }
  1004. }
  1005. if (output.Count > 0)
  1006. {
  1007. var clients = _clientDatas.Where(it => output.Contains(it.Id)).ToList();
  1008. foreach (var client in clients)
  1009. {
  1010. itemClientName += $"{client.LastName + client.FirstName},";
  1011. }
  1012. if (itemClientName.Length > 0)
  1013. {
  1014. itemClientName = itemClientName.Substring(0, itemClientName.Length - 1);
  1015. }
  1016. }
  1017. else
  1018. {
  1019. itemClientName = insClients;
  1020. }
  1021. }
  1022. }
  1023. item.ClientName = itemClientName;
  1024. if (!string.IsNullOrEmpty(item.AuditGMDate))
  1025. {
  1026. item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
  1027. }
  1028. }
  1029. _geView.GroupInsuranceFeeViews = groupInsuranceFeeViews;
  1030. _geView.GroupInsuranceFeeStr = string.Format(@"人民币总费用:{0} CNY", InsuranceCNYTotalPrice.ToString("#0.00"));
  1031. #endregion
  1032. #region 其他款项费用 98
  1033. List<GroupDecreaseFeeView> groupDecreaseFeeViews = new List<GroupDecreaseFeeView>();
  1034. string groupDecreaseFeeSql = string.Format(@"Select dp.Id As DPId,dp.DiId As DPDiId,dp.PriceName,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
  1035. (((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,
  1036. ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,
  1037. sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,dp.CreateTime
  1038. From Grp_DecreasePayments dp
  1039. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 98 And dp.Id = ccp.CId
  1040. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  1041. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  1042. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1043. Where dp.IsDel = 0 And ccp.Ctable = 98 {1} And dp.Diid = {0}
  1044. Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
  1045. groupDecreaseFeeViews = await _sqlSugar.SqlQueryable<GroupDecreaseFeeView>(groupDecreaseFeeSql).ToListAsync();
  1046. #region 保险费用 - 模拟数据
  1047. //if (groupDecreaseFeeViews.Count < 1)
  1048. //{
  1049. // groupDecreaseFeeViews.Add(new GroupDecreaseFeeView()
  1050. // {
  1051. // DPId = 0,
  1052. // DPDiId = 2334,
  1053. // PriceName = "模拟数据-费用名称",
  1054. // PayMoney = 1000.00M,
  1055. // PayMoneyCurrency = "CNY",
  1056. // DayRate = 1.0000M,
  1057. // CNYPrice = 1.0000M,
  1058. // AuditGMDate = "2023-12-10 12:13:00",
  1059. // Payee = "模拟数据-付款方",
  1060. // OrbitalPrivateTransfer = 1,
  1061. // PayWay = "现金",
  1062. // IsPay = 1,
  1063. // Applicant = "刘华举"
  1064. // });
  1065. // groupDecreaseFeeViews.Add(new GroupDecreaseFeeView()
  1066. // {
  1067. // DPId = 0,
  1068. // DPDiId = 2334,
  1069. // PriceName = "模拟数据-费用名称",
  1070. // PayMoney = 1000.00M,
  1071. // PayMoneyCurrency = "CNY",
  1072. // DayRate = 1.0000M,
  1073. // CNYPrice = 1.0000M,
  1074. // AuditGMDate = "2023-12-10 12:13:00",
  1075. // Payee = "模拟数据-付款方",
  1076. // OrbitalPrivateTransfer = 1,
  1077. // PayWay = "现金",
  1078. // IsPay = 1,
  1079. // Applicant = "刘华举"
  1080. // });
  1081. //}
  1082. #endregion
  1083. decimal DecreaseCNYTotalPrice = 0.00M;
  1084. foreach (var item in groupDecreaseFeeViews)
  1085. {
  1086. item.CNYPrice = Convert.ToDecimal(item.CNYPrice.ToString("#0.00"));
  1087. DecreaseCNYTotalPrice += item.CNYPrice;
  1088. if (!string.IsNullOrEmpty(item.AuditGMDate))
  1089. {
  1090. item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
  1091. }
  1092. }
  1093. _geView.GroupDecreaseFeeViews = groupDecreaseFeeViews;
  1094. _geView.GroupDecreaseFeeStr = string.Format(@"人民币总费用:{0} CNY", DecreaseCNYTotalPrice.ToString("#0.00"));
  1095. #endregion
  1096. _view.GroupExpenditure = _geView;
  1097. #endregion
  1098. /*
  1099. * 团组报表计算方式
  1100. * 当前总支出 = 团组支出.Sum() + 超支费用.Sum()
  1101. * 应收金额 = 应收表.Sum()
  1102. * 已收金额 = 已收表.Sum()
  1103. * 应收利润(应收-支出) = 应收金额 - 收款退还 - 当前总支出
  1104. * 已收利润(已收-支出) = 已收金额 - 收款退还 - 当前总支出
  1105. *
  1106. */
  1107. decimal _totalExpenditure = 0.00M; //总支出
  1108. decimal _amountReceivable = 0.00M; //应收金额
  1109. decimal _amountReceived = 0.00M; //已收金额
  1110. decimal _receivableProfit = 0.00M; //应收利润
  1111. decimal _receivedProfit = 0.00M; //已收利润
  1112. _totalExpenditure = HotelCNYTotalPrice + CTGGRCNYTotalPrice + AirCNYTotalPrice + VisaCNYTotalPirce + InvitationalCNYTotalPrice +
  1113. InsuranceCNYTotalPrice + DecreaseCNYTotalPrice + exTotalAmount;
  1114. _amountReceivable = frTotalAmount;
  1115. _amountReceived = prTotalAmount;
  1116. _receivableProfit = _amountReceivable - promTotalAmount - _totalExpenditure;
  1117. _receivedProfit = _amountReceived - promTotalAmount - _totalExpenditure;
  1118. _view.FeeTotalStr = string.Format(@$"<span>
  1119. <span>当前总支出:{_totalExpenditure.ToString("#0.00")} CNY</span>
  1120. <span style='padding-left:10px;color: Green;'>应收金额:{_amountReceivable.ToString("#0.00")} CNY</span>
  1121. <span style='padding-left:10px;color: Green;'>已收金额:{_amountReceived.ToString("#0.00")} CNY</span>
  1122. <span style='padding-left:10px;color: Green;'>应收利润(应收-支出):{_receivableProfit.ToString("#0.00")} CNY</span>
  1123. <span style='padding-left:10px;color: Green;'>已收利润(已收-支出):{_receivedProfit.ToString("#0.00")} CNY</span>
  1124. </span>");
  1125. return Ok(JsonView(true, "查询成功!", _view));
  1126. }
  1127. else
  1128. {
  1129. return Ok(JsonView(false, "查询成功"));
  1130. }
  1131. }
  1132. #endregion
  1133. #region 报表/折线图统计
  1134. //企业利润-团组利润
  1135. //企业利润-会务利润
  1136. /// <summary>
  1137. /// 企业利润
  1138. /// Details
  1139. /// 待添加权限验证
  1140. /// </summary>
  1141. /// <param name="_dto">团组列表请求dto</param>
  1142. /// <returns></returns>
  1143. [HttpPost("PostCorporateProfit")]
  1144. //[JsonConverter(typeof(DecimalConverter), 2)]
  1145. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1146. public async Task<IActionResult> PostCorporateProfit(PostCorporateProfitDto _dto)
  1147. {
  1148. #region 参数验证
  1149. if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
  1150. if (_dto.PageId < 1) _dto.PageId = 38; //团组报表页面Id
  1151. if (_dto.Year < 1) return Ok(JsonView(false, "请输入有效的Year参数!"));
  1152. if (_dto.StatisticsType > 2 && _dto.StatisticsType < 1) return Ok(JsonView(false, "请输入有效的StatisticsType参数,1 月份 2 季度"));
  1153. if (_dto.BusinessType > 3 && _dto.BusinessType < 1) return Ok(JsonView(false, "请输入有效的BusinessType参数,1 所有 2 团组 3 会务"));
  1154. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  1155. #region 页面操作权限验证
  1156. //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  1157. //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
  1158. #endregion
  1159. string sqlWhere = string.Empty;
  1160. //起止时间
  1161. DateTime beginDt = Convert.ToDateTime($"{_dto.Year}-01-01 00:00:00");
  1162. DateTime endDt = Convert.ToDateTime($"{_dto.Year}-12-31 23:59:59");
  1163. sqlWhere = string.Format(@$" Where Isdel = 0 ");
  1164. //业务类型
  1165. List<int> groupTypeId = new List<int>();
  1166. if (_dto.BusinessType == 2) //团组
  1167. {
  1168. groupTypeId.AddRange(new List<int>() {
  1169. 38, // 政府团
  1170. 39, // 企业团
  1171. 40, // 散客团
  1172. 1048 // 高校团
  1173. });
  1174. }
  1175. else if (_dto.BusinessType == 3) //会务
  1176. {
  1177. groupTypeId.AddRange(new List<int>() {
  1178. 102, // 未知
  1179. 248, // 非团组
  1180. 302, // 成都-会务活动
  1181. 691, // 四川-会务活动
  1182. 762, // 四川-赛事项目收入
  1183. 1047 // 成都-赛事项目收入
  1184. });
  1185. }
  1186. if (groupTypeId.Count > 0)
  1187. {
  1188. sqlWhere += string.Format(@$" And TeamDid In ({string.Join(',', groupTypeId)})");
  1189. }
  1190. string sql = string.Format(@$"Select * From Grp_DelegationInfo {sqlWhere}");
  1191. var groupInfos = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).Where(it => it.CreateTime >= beginDt && it.CreateTime <= endDt).ToListAsync();
  1192. if (groupInfos.Count < 1) return Ok(JsonView(false, "暂无相关团组!"));
  1193. List<int> diIds = groupInfos.Select(it => it.Id).ToList();
  1194. List<CorporateProfit> corporateProfits = await CorporateProfit(diIds);
  1195. List<MonthInfo> months = new List<MonthInfo>();
  1196. if (_dto.StatisticsType == 1) //月份
  1197. {
  1198. months = GeneralMethod.GetMonthInfos(Convert.ToInt32(_dto.Year));
  1199. }
  1200. else if (_dto.StatisticsType == 1) //季度
  1201. {
  1202. months = GeneralMethod.GetQuarter(Convert.ToInt32(_dto.Year));
  1203. }
  1204. List<CorporateProfitMonthView> _view = new List<CorporateProfitMonthView>();
  1205. foreach (var item in months)
  1206. {
  1207. DateTime monthBeginDt = Convert.ToDateTime($"{_dto.Year}-{item.Month}-{item.Days.BeginDays} 00:00:00");
  1208. DateTime monthEndDt = Convert.ToDateTime($"{_dto.Year}-{item.Month}-{item.Days.EndDays} 23:59:59");
  1209. var corporateProfit = corporateProfits.Where(it => it.CreateDt >= monthBeginDt && it.CreateDt <= monthEndDt).ToList();
  1210. _view.Add(new CorporateProfitMonthView()
  1211. {
  1212. Month = item.Month,
  1213. Profit = corporateProfit.Sum(it => it.ReceivedProfit),
  1214. GroupInfos = corporateProfit.OrderBy(it => it.CreateDt).ToList()
  1215. });
  1216. }
  1217. return Ok(JsonView(true, "操作成功!", _view));
  1218. #endregion
  1219. }
  1220. /// <summary>
  1221. /// 计算团组利润
  1222. /// </summary>
  1223. /// <param name="diIds"></param>
  1224. /// <returns></returns>
  1225. private async Task<List<CorporateProfit>> CorporateProfit(List<int> diIds)
  1226. {
  1227. List<CorporateProfit> corporateProfits = new List<CorporateProfit>();
  1228. if (diIds.Count < 1)
  1229. {
  1230. return corporateProfits;
  1231. }
  1232. #region 计算团组利润
  1233. /*
  1234. * 团组报表计算方式
  1235. * 当前总支出 = 团组支出.Sum() + 超支费用.Sum()
  1236. * 应收金额 = 应收表.Sum()
  1237. * 已收金额 = 已收表.Sum()
  1238. * 应收利润(应收-支出) = 应收金额 - 收款退还 - 当前总支出
  1239. * 已收利润(已收-支出) = 已收金额 - 收款退还 - 当前总支出
  1240. *
  1241. */
  1242. string diIdStr = string.Join(",", diIds);
  1243. string sql = string.Format(@$"Select * From Grp_DelegationInfo Where Isdel = 0 And Id In ({diIdStr})");
  1244. var groupInfos = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).ToListAsync();
  1245. #region 费用类型 币种,转账,客户信息
  1246. List<Sys_SetData> _setDatas = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToListAsync();
  1247. var _clientDatas = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0).ToListAsync();
  1248. #endregion
  1249. foreach (var _diId in diIds)
  1250. {
  1251. List<ExpenditureInfo> expenditureInfos = new List<ExpenditureInfo>();
  1252. #region 团组收入
  1253. /*
  1254. * 应收报表
  1255. */
  1256. decimal frTotalAmount = 0.00M;//应收总金额
  1257. string _frSql = string.Format(@"Select fr.Id,fr.Diid,fr.PriceName,fr.Price,fr.Count,fr.Unit,fr.Currency,
  1258. sd.Name As CurrencyCode,sd.Remark As CurrencyName,fr.Rate,fr.ItemSumPrice,fr.CreateTime
  1259. From Fin_ForeignReceivables fr
  1260. Left Join Sys_SetData sd On fr.Currency = sd.Id
  1261. Where fr.IsDel = 0 And fr.Diid = {0} Order By CreateTime", _diId);
  1262. List<Gsd_ForeignReceivablesView> _frViews = await _sqlSugar.SqlQueryable<Gsd_ForeignReceivablesView>(_frSql).ToListAsync();
  1263. frTotalAmount = _frViews.Sum(it => it.ItemSumPrice);
  1264. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "应收项", Amount = frTotalAmount });
  1265. /*
  1266. * 已收报表
  1267. */
  1268. decimal prTotalAmount = 0.00M;//已收总金额
  1269. string _prSql = string.Format(@"Select pr.Id,pr.Diid,pr.SectionTime As SectionTimeDt,pr.Price,pr.Currency,
  1270. sd1.Name As CurrencyCode,sd1.Remark As CurrencyName,pr.Client,
  1271. pr.ReceivablesType,sd2.Name As ReceivablesTypeName,pr.Remark,pr.CreateTime
  1272. From Fin_ProceedsReceived pr
  1273. Left Join Sys_SetData sd1 On pr.Currency = sd1.Id
  1274. Left Join Sys_SetData sd2 On pr.ReceivablesType = sd2.Id
  1275. Where pr.IsDel = 0 and pr.Diid = {0} Order By CreateTime", _diId);
  1276. List<Gsd_ProceedsReceivedView> _prViews = await _sqlSugar.SqlQueryable<Gsd_ProceedsReceivedView>(_prSql).ToListAsync();
  1277. prTotalAmount = _prViews.Sum(it => it.Price);
  1278. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "已收项", Amount = prTotalAmount });
  1279. /*
  1280. * 超支费用
  1281. */
  1282. decimal exTotalAmount = 0.00M;
  1283. string _ecSql = string.Format(@"Select gec.Id As GECId,gec.DiId As GECDiId,gec.PriceName,ccp.PayMoney,sd1.Name As PaymentCurrency,
  1284. ccp.RMBPrice As CNYPrice,ccp.DayRate,ccp.Payee,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
  1285. sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,gec.CreateTime
  1286. From OA2023DB.dbo.Fin_GroupExtraCost gec
  1287. Left Join Grp_CreditCardPayment ccp On gec.Id = ccp.CId
  1288. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  1289. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  1290. Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
  1291. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1292. Where ccp.IsDel = 0 And ccp.CTable = 1015 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And ccp.DiId = {0} Order By CreateTime", _diId);
  1293. List<Gsd_ExtraCostsView> _ExtraCostsViews = await _sqlSugar.SqlQueryable<Gsd_ExtraCostsView>(_ecSql).ToListAsync();
  1294. exTotalAmount = _ExtraCostsViews.Sum(it => it.CNYPrice);
  1295. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "超支费用", Amount = exTotalAmount });
  1296. /*
  1297. * 收款退还
  1298. */
  1299. decimal promTotalAmount = 0.00M;// 收款退还总金额
  1300. List<Gsd_PaymentRefundAndOtherMoneyView> _promView = new List<Gsd_PaymentRefundAndOtherMoneyView>();
  1301. //删除了 And prom.PriceType = 1
  1302. string _ropSql = string.Format(@"Select u.CnName As Appliction,prom.Id As PrId,prom.DiId As PrDiId,prom.Price As PrPrice,
  1303. prom.PriceName AS PrPriceName,prom.CurrencyId As PrCurrencyId,
  1304. prom.PayType As PrPayType,prom.PriceType As PrPriceType,ccp.*,prom.CreateTime As PrCreateTime
  1305. From Fin_PaymentRefundAndOtherMoney prom
  1306. Left Join Grp_CreditCardPayment ccp On prom.DiId = ccp.DIId And prom.Id = ccp.CId
  1307. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1308. Where prom.IsDel = 0 And prom.PayType = 1 And ccp.CTable = 285
  1309. And ccp.IsAuditGM = 1 And ccp.IsPay = 1
  1310. And prom.DiId = {0} Order By PrCreateTime", _diId);
  1311. var _promDatas = await _sqlSugar.SqlQueryable<Gsd_PaymentRefundAndOtherMoneyDataSource1View>(_ropSql).ToListAsync();
  1312. foreach (var ropItem in _promDatas)
  1313. {
  1314. string thisCueencyCode = "Unknown";
  1315. string thisCueencyName = "Unknown";
  1316. var currency = _setDatas.Where(it => it.Id == ropItem.PaymentCurrency).FirstOrDefault();
  1317. if (currency != null)
  1318. {
  1319. thisCueencyCode = currency.Name;
  1320. thisCueencyName = currency.Remark;
  1321. }
  1322. string orbitalPrivateTransferStr = "Unknown";
  1323. var orbitalPrivateTransfer = _setDatas.Where(it => it.Id == ropItem.OrbitalPrivateTransfer).FirstOrDefault();
  1324. if (orbitalPrivateTransfer != null)
  1325. {
  1326. orbitalPrivateTransferStr = orbitalPrivateTransfer.Name;
  1327. }
  1328. string payStr = "Unknown";
  1329. var pay = _setDatas.Where(it => it.Id == ropItem.PayDId).FirstOrDefault();
  1330. if (pay != null)
  1331. {
  1332. payStr = pay.Name;
  1333. }
  1334. Gsd_PaymentRefundAndOtherMoneyView gsd_PaymentRefund = new Gsd_PaymentRefundAndOtherMoneyView()
  1335. {
  1336. Id = ropItem.Id,
  1337. DiId = ropItem.DIId,
  1338. PriceName = ropItem.PrPriceName,
  1339. PayCurrencyCode = thisCueencyCode,
  1340. PayCurrencyName = thisCueencyName,
  1341. Price = ropItem.PrPrice,
  1342. CNYPrice = ropItem.RMBPrice,
  1343. ThisRate = ropItem.DayRate,
  1344. Payee = ropItem.Payee,
  1345. PayTime = ropItem.AuditGMDate,
  1346. OrbitalPrivateTransfer = ropItem.OrbitalPrivateTransfer,
  1347. PayType = payStr,
  1348. IsPay = ropItem.IsPay,
  1349. Applicant = ropItem.Appliction
  1350. };
  1351. _promView.Add(gsd_PaymentRefund);
  1352. }
  1353. promTotalAmount = _promView.Sum(it => it.CNYPrice);
  1354. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "收款退还", Amount = promTotalAmount });
  1355. #endregion
  1356. #region 团组支出
  1357. GroupExpenditureView _geView = new GroupExpenditureView();
  1358. #region 酒店预定费用
  1359. List<GroupHotelFeeView> groupHotelFeeViews = new List<GroupHotelFeeView>();
  1360. string hotelFeeSql = string.Format(@"Select hr.Id As HrId,hr.DiId As HrDiId,hr.City,hr.HotelName,hr.CheckInDate,hr.CheckOutDate,
  1361. sd1.Name As PaymentCurrency,hr.SingleRoomPrice,hr.SingleRoomCount,hr.DoubleRoomPrice,
  1362. hr.DoubleRoomCount,hr.SuiteRoomPrice,hr.SuiteRoomCount,hr.OtherRoomPrice,hr.OtherRoomCount,
  1363. hr.BreakfastPrice,sd4.Name As BreakfastCurrency,hr.Isoppay,hr.GovernmentRent,
  1364. sd5.Name As GovernmentRentCurrency,hr.CityTax,sd6.Name As CityTaxCurrency,
  1365. ccp.PayMoney,ccp.RMBPrice As CNYPrice,ccp.DayRate,ccp.Payee,ccp.OrbitalPrivateTransfer,
  1366. sd2.Name As PayWay,sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant
  1367. From Grp_HotelReservations hr
  1368. Left Join Grp_CreditCardPayment ccp On hr.Id = ccp.CId
  1369. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  1370. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  1371. Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
  1372. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1373. Left Join Sys_SetData sd4 On hr.BreakfastCurrency = sd4.Id
  1374. Left Join Sys_SetData sd5 On hr.GovernmentRentCurrency = sd5.Id
  1375. Left Join Sys_SetData sd6 On hr.CityTaxCurrency = sd6.Id
  1376. Where hr.IsDel = 0 And ccp.IsDel = 0 And ccp.CTable = 76 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And hr.DiId = {0}
  1377. Order By CheckInDate Asc", _diId);
  1378. groupHotelFeeViews = await _sqlSugar.SqlQueryable<GroupHotelFeeView>(hotelFeeSql).ToListAsync();
  1379. decimal HotelCNYTotalPrice = groupHotelFeeViews.Sum(it => it.CNYPrice);
  1380. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "酒店预定", Amount = HotelCNYTotalPrice });
  1381. #endregion
  1382. #region 地接费用
  1383. List<GroupCTGGRFeeView> groupCTGGRFeeViews = new List<GroupCTGGRFeeView>();
  1384. string CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.Area,ctggrc.*,ctggrc.Price As PayMoney,
  1385. sd2.name As PaymentCurrency,ccp.PayPercentage,
  1386. (ctggrc.Price / (ccp.PayPercentage / 100)) As AmountPaid,
  1387. (ctggrc.Price / (ccp.PayPercentage / 100) - ctggrc.Price) As BalancePayment,
  1388. ccp.DayRate,(ctggrc.Price * ccp.DayRate) As CNYPrice,ccp.Payee,ccp.AuditGMDate,
  1389. ccp.OrbitalPrivateTransfer,sd1.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ctggr.CreateTime
  1390. From Grp_CarTouristGuideGroundReservations ctggr
  1391. Left Join ( Select cggrc.CTGGRId,sd1.Name As PriceName,cggrc.Price,sd2.Name As PriceCurrency,
  1392. cggrc.PriceContent
  1393. From Grp_CarTouristGuideGroundReservationsContent cggrc
  1394. Left Join Sys_SetData sd1 On cggrc.SId = sd1.Id
  1395. Left Join Sys_SetData sd2 On cggrc.Currency = sd2.Id
  1396. Where cggrc.ISdel = 0 And cggrc.Price != 0.00
  1397. ) ctggrc On ctggr.Id = ctggrc.CTGGRId
  1398. Left Join Grp_CreditCardPayment ccp On ccp.IsDel = 0 And ccp.CTable = 79 And ctggr.Id = ccp.CId
  1399. Left Join Sys_SetData sd1 On ccp.PayDId = sd1.Id
  1400. Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
  1401. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1402. Where ctggr.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And ctggr.DiId = {0}
  1403. Order By CreateTime", _diId);
  1404. groupCTGGRFeeViews = await _sqlSugar.SqlQueryable<GroupCTGGRFeeView>(CTGGRFeeSql).ToListAsync();
  1405. decimal CTGGRCNYTotalPrice = groupCTGGRFeeViews.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
  1406. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "地接", Amount = CTGGRCNYTotalPrice });
  1407. #endregion
  1408. #region 机票预订费用
  1409. List<GroupAirFeeView> groupAirFeeViews = new List<GroupAirFeeView>();
  1410. string groupAirFeeSql = string.Format(@"Select atr.Id As AirId,atr.DIId As AirDiId,atr.FlightsCode,atr.FlightsCity,sd4.Name As AirTypeName,
  1411. atr.FlightsDate,atr.FlightsTime,atr.ClientName,atr.ClientNum,ccp.PayMoney,
  1412. sd1.Name As PayMoneyCurrency,ccp.RMBPrice As CNYPrice,ccp.DayRate,ccp.Payee,ccp.AuditGMDate,
  1413. ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,atr.CreateTime
  1414. From Grp_AirTicketReservations atr
  1415. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 85 And atr.Id = ccp.CId
  1416. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  1417. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  1418. Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
  1419. Left Join Sys_SetData sd4 On atr.CType = sd4.Id
  1420. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1421. Where atr.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And atr.DiId = {0} Order By CreateTime", _diId);
  1422. groupAirFeeViews = await _sqlSugar.SqlQueryable<GroupAirFeeView>(groupAirFeeSql).ToListAsync();
  1423. decimal AirCNYTotalPrice = groupAirFeeViews.Sum(it => it.CNYPrice);
  1424. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "机票预订", Amount = AirCNYTotalPrice });
  1425. #endregion
  1426. #region 签证费用
  1427. List<GroupVisaFeeView> groupVisaFeeViews = new List<GroupVisaFeeView>();
  1428. string groupVisaFeeSql = string.Format(@"Select vi.Id As VisaId,vi.DIId As VisaDiId,vi.VisaClient,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
  1429. ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
  1430. sd3.Name As CardTypeName,ccp.IsPay,u.CnName As Applicant,vi.CreateTime
  1431. From Grp_VisaInfo vi
  1432. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 80 And vi.Id = ccp.CId
  1433. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  1434. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  1435. Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
  1436. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1437. Where vi.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And vi.DIId = {0} Order By CreateTime", _diId);
  1438. groupVisaFeeViews = await _sqlSugar.SqlQueryable<GroupVisaFeeView>(groupVisaFeeSql).ToListAsync();
  1439. decimal VisaCNYTotalPirce = groupVisaFeeViews.Sum(it => it.PayMoney);
  1440. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "签证", Amount = VisaCNYTotalPirce });
  1441. #endregion
  1442. #region 邀请/公务活动 CTable = 81
  1443. List<GroupInvitationalFeeView> groupInvitationalFeeViews = new List<GroupInvitationalFeeView>();
  1444. string groupInvitationalFeeSql = string.Format(@"Select ioa.Id As IOAId,ioa.DiId As IOADiId,ioa.InviterArea,ioa.Inviter,ioa.InviteTime,
  1445. ioa.InviteCost,sd3.Name As InviteCurrency,ioa.SendCost,sd4.Name As SendCurrency,ioa.EventsCost,
  1446. sd5.Name As EventsCurrency,ioa.TranslateCost,sd6.Name As TranslateCurrency,ccp.PayMoney,
  1447. sd7.Name As PaymentCurrency,ccp.RMBPrice As CNYPrice,ccp.Payee,ccp.AuditGMDate,
  1448. ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ioa.CreateTime
  1449. From Grp_InvitationOfficialActivities ioa
  1450. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 81 And ioa.Id = ccp.CId
  1451. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  1452. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  1453. Left Join Sys_SetData sd3 On ioa.InviteCurrency = sd3.Id
  1454. Left Join Sys_SetData sd4 On ioa.SendCurrency = sd4.Id
  1455. Left Join Sys_SetData sd5 On ioa.EventsCurrency = sd5.Id
  1456. Left Join Sys_SetData sd6 On ioa.TranslateCurrency = sd6.Id
  1457. Left Join Sys_SetData sd7 On ccp.PaymentCurrency = sd7.Id
  1458. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1459. Where ioa.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And ioa.Diid = {0} Order By CreateTime", _diId);
  1460. groupInvitationalFeeViews = await _sqlSugar.SqlQueryable<GroupInvitationalFeeView>(groupInvitationalFeeSql).ToListAsync();
  1461. decimal InvitationalCNYTotalPrice = groupInvitationalFeeViews.Sum(it => it.CNYPrice);
  1462. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "邀请/公务活动", Amount = InvitationalCNYTotalPrice });
  1463. #endregion
  1464. #region 保险费用
  1465. List<GroupInsuranceFeeView> groupInsuranceFeeViews = new List<GroupInsuranceFeeView>();
  1466. string groupInsuranceFeeSql = string.Format(@"Select ic.Id As InsuranceId,ic.Diid As InsuranceDiId,ClientName,ccp.PayMoney,ccp.RMBPrice As CNYPrice,
  1467. sd1.Name As PayMoneyCurrency,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,
  1468. sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ic.CreateTime
  1469. From Grp_Customers ic
  1470. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 82 And ic.Id = ccp.CId
  1471. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  1472. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  1473. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1474. Where ic.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And ic.DiId = {0} Order By CreateTime", _diId);
  1475. groupInsuranceFeeViews = await _sqlSugar.SqlQueryable<GroupInsuranceFeeView>(groupInsuranceFeeSql).ToListAsync();
  1476. decimal InsuranceCNYTotalPrice = groupInsuranceFeeViews.Sum(it => it.CNYPrice);
  1477. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "保险费用", Amount = InsuranceCNYTotalPrice });
  1478. #endregion
  1479. #region 其他款项费用 98
  1480. List<GroupDecreaseFeeView> groupDecreaseFeeViews = new List<GroupDecreaseFeeView>();
  1481. string groupDecreaseFeeSql = string.Format(@"Select dp.Id As DPId,dp.DiId As DPDiId,dp.PriceName,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
  1482. (((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,
  1483. ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,
  1484. sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,dp.CreateTime
  1485. From Grp_DecreasePayments dp
  1486. Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 98 And dp.Id = ccp.CId
  1487. Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
  1488. Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
  1489. Left Join Sys_Users u On ccp.CreateUserId = u.Id
  1490. Where dp.IsDel = 0 And ccp.Ctable = 98 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And dp.Diid = {0}
  1491. Order By CreateTime", _diId);
  1492. groupDecreaseFeeViews = await _sqlSugar.SqlQueryable<GroupDecreaseFeeView>(groupDecreaseFeeSql).ToListAsync();
  1493. decimal DecreaseCNYTotalPrice = groupDecreaseFeeViews.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
  1494. expenditureInfos.Add(new ExpenditureInfo() { ItemName = "其他款项", Amount = DecreaseCNYTotalPrice });
  1495. #endregion
  1496. #endregion
  1497. /*
  1498. * 团组报表计算方式
  1499. * 当前总支出 = 团组支出.Sum() + 超支费用.Sum()
  1500. * 应收金额 = 应收表.Sum()
  1501. * 已收金额 = 已收表.Sum()
  1502. * 应收利润(应收-支出) = 应收金额 - 收款退还 - 当前总支出
  1503. * 已收利润(已收-支出) = 已收金额 - 收款退还 - 当前总支出
  1504. *
  1505. */
  1506. decimal _totalExpenditure = 0.00M; //总支出
  1507. decimal _amountReceivable = 0.00M; //应收金额
  1508. decimal _amountReceived = 0.00M; //已收金额
  1509. decimal _receivableProfit = 0.00M; //应收利润
  1510. decimal _receivedProfit = 0.00M; //已收利润
  1511. _totalExpenditure = HotelCNYTotalPrice + CTGGRCNYTotalPrice + AirCNYTotalPrice + VisaCNYTotalPirce + InvitationalCNYTotalPrice +
  1512. InsuranceCNYTotalPrice + DecreaseCNYTotalPrice + exTotalAmount;
  1513. _amountReceivable = frTotalAmount;
  1514. _amountReceived = prTotalAmount;
  1515. _receivableProfit = _amountReceivable - promTotalAmount - _totalExpenditure;
  1516. _receivedProfit = _amountReceived - promTotalAmount - _totalExpenditure;
  1517. var groupInfo = groupInfos.Find(it => it.Id == _diId);
  1518. corporateProfits.Add(new CorporateProfit()
  1519. {
  1520. DiId = _diId,
  1521. TeamName = groupInfo?.TeamName ?? "Unkwnon",
  1522. CreateDt = Convert.ToDateTime(groupInfo?.CreateTime),
  1523. TotalExpenditure = _totalExpenditure,
  1524. ExpenditureItem = expenditureInfos,
  1525. AmountReceivable = _amountReceivable,
  1526. AmountReceived = _amountReceived,
  1527. ReceivableProfit = _receivableProfit,
  1528. ReceivedProfit = _receivedProfit,
  1529. });
  1530. }
  1531. #endregion
  1532. return corporateProfits;
  1533. }
  1534. //未来预测-地区接团/出团量
  1535. //未来预测-地区酒店预订量
  1536. //未来预测-地区机票预订量
  1537. //未来预测-地区车辆预订量
  1538. /// <summary>
  1539. /// (国家/城市)地区预订数量(团,酒店,机票,车辆)
  1540. /// Details
  1541. /// 待添加权限验证
  1542. /// </summary>
  1543. /// <param name="_dto">团组列表请求dto</param>
  1544. /// <returns></returns>
  1545. [HttpPost("PostRegionalBookingsNumber")]
  1546. //[JsonConverter(typeof(DecimalConverter), 2)]
  1547. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1548. public async Task<IActionResult> PostRegionalBookingsNumber(PostRegionalBookingsNumberDto _dto)
  1549. {
  1550. #region 参数验证
  1551. if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
  1552. //if (_dto.PageId < 1) _dto.PageId = 38; //团组报表页面Id
  1553. if (_dto.Type > 1 && _dto.Type > 5) return Ok(JsonView(false, "请输入有效的Type参数,1 团 2 酒店 3 机票 4 车辆"));
  1554. if (_dto.Year < 1) return Ok(JsonView(false, "请输入有效的Year参数!"));
  1555. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  1556. #region 页面操作权限验证
  1557. //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  1558. //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
  1559. #endregion
  1560. string sqlWhere = string.Empty;
  1561. //起止时间
  1562. DateTime beginDt = Convert.ToDateTime($"{_dto.Year}-01-01 00:00:00");
  1563. DateTime endDt = Convert.ToDateTime($"{_dto.Year}-12-31 23:59:59");
  1564. sqlWhere = string.Format(@$" Where Isdel = 0 ");
  1565. string sql = string.Format(@$"Select * From Grp_DelegationInfo {sqlWhere}");
  1566. var groupInfos = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).Where(it => it.CreateTime >= beginDt && it.CreateTime <= endDt).ToListAsync();
  1567. if (groupInfos.Count < 1) return Ok(JsonView(false, "暂无相关团组!"));
  1568. List<GroupTypeNumberInfo> datas = new List<GroupTypeNumberInfo>();
  1569. foreach (var item in groupInfos)
  1570. {
  1571. var data = await GroupBookingsNumber(_dto.Type, item);
  1572. if (data.TypeItem.Count > 0)
  1573. {
  1574. datas.Add(data);
  1575. }
  1576. }
  1577. //类型处理
  1578. if (_dto.Type == 1)//接团
  1579. {
  1580. List<GroupBookingNumberView> views = new List<GroupBookingNumberView>();
  1581. dynamic groupData = null;
  1582. foreach (var item in datas)
  1583. {
  1584. if (item.TypeItem.Count > 0)
  1585. {
  1586. foreach (var item1 in item.TypeItem)
  1587. {
  1588. if (item1.RegionItem.Count > 0)
  1589. {
  1590. GroupInfo groupInfo = new GroupInfo()
  1591. {
  1592. DiId = item.DiId,
  1593. TeamName = item.GroupName,
  1594. CreateTime = groupInfos.Find(it => it.Id == item.DiId)?.CreateTime.ToString("yyyy-MM-dd HH:mm:ss") ?? "Unknown",
  1595. ClientUnit = groupInfos.Find(it => it.Id == item.DiId)?.ClientUnit ?? "Unknown",
  1596. Principal = groupInfos.Find(it => it.Id == item.DiId)?.ClientName ?? "Unknown",
  1597. };
  1598. views.Add(new GroupBookingNumberView() { Name = item1.RegionItem[0].Name, Number = item1.RegionItem[0].Number, GroupItem = new List<GroupInfo>() { groupInfo } });
  1599. }
  1600. }
  1601. }
  1602. }
  1603. var viewsGroup = views.GroupBy(it => it.Name);
  1604. List<GroupBookingNumberView> _view = new List<GroupBookingNumberView>();
  1605. foreach (var item in viewsGroup)
  1606. {
  1607. List<GroupInfo> infos = new List<GroupInfo>();
  1608. foreach (var item1 in item)
  1609. {
  1610. infos.AddRange(item1.GroupItem);
  1611. }
  1612. infos = infos.OrderByDescending(it => it.CreateTime).ToList(); //
  1613. _view.Add(new GroupBookingNumberView() { Name = item.Key, Number = item.Count(), GroupItem = infos });
  1614. }
  1615. _view = _view.OrderByDescending(it => it.Number).Take(10).ToList();
  1616. return Ok(JsonView(true, "操作成功!", _view, _view.Count));
  1617. }
  1618. else if (_dto.Type == 2)
  1619. {
  1620. List<HotelBookingNumberView> views = new List<HotelBookingNumberView>();
  1621. foreach (var item in datas)
  1622. {
  1623. if (item.TypeItem.Count > 0)
  1624. {
  1625. foreach (var item1 in item.TypeItem)
  1626. {
  1627. if (item1.RegionItem.Count > 0)
  1628. {
  1629. foreach (var item2 in item1.RegionItem)
  1630. {
  1631. GroupInfo groupInfo = new GroupInfo()
  1632. {
  1633. DiId = item.DiId,
  1634. TeamName = item.GroupName,
  1635. CreateTime = groupInfos.Find(it => it.Id == item.DiId)?.CreateTime.ToString("yyyy-MM-dd HH:mm:ss") ?? "Unknown",
  1636. ClientUnit = groupInfos.Find(it => it.Id == item.DiId)?.ClientUnit ?? "Unknown",
  1637. Principal = groupInfos.Find(it => it.Id == item.DiId)?.ClientName ?? "Unknown",
  1638. };
  1639. List<HotelInfo> hotels = new List<HotelInfo>();
  1640. foreach (var item3 in item2.Data)
  1641. {
  1642. StatisticsHotelInfo statisticsHotelInfos = JsonConvert.DeserializeObject<StatisticsHotelInfo>(JsonConvert.SerializeObject(item3));
  1643. HotelInfo hotelInfo = new HotelInfo()
  1644. {
  1645. HotelName = statisticsHotelInfos.HotelName,
  1646. SingleRoomNum = statisticsHotelInfos.SingleRoomNum,
  1647. DoubleRoomNum = statisticsHotelInfos.DoubleRoomNum,
  1648. SuiteRoomNum = statisticsHotelInfos.SuiteRoomNum,
  1649. OtherRoomNum = statisticsHotelInfos.OtherRoomNum,
  1650. GroupInfo = groupInfo
  1651. };
  1652. hotels.Add(hotelInfo);
  1653. }
  1654. views.Add(new HotelBookingNumberView() { Name = item1.RegionItem[0].Name, Number = item1.RegionItem[0].Number, HotelItem = hotels });
  1655. }
  1656. }
  1657. }
  1658. }
  1659. }
  1660. var viewsGroup = views.GroupBy(it => it.Name);
  1661. List<HotelBookingNumberView> _view = new List<HotelBookingNumberView>();
  1662. foreach (var item in viewsGroup)
  1663. {
  1664. List<HotelInfo> infos = new List<HotelInfo>();
  1665. foreach (var item1 in item)
  1666. {
  1667. infos.AddRange(item1.HotelItem);
  1668. }
  1669. _view.Add(new HotelBookingNumberView() { Name = item.Key, Number = item.Count(), HotelItem = infos });
  1670. }
  1671. _view = _view.OrderByDescending(it => it.Number).Take(10).ToList();
  1672. return Ok(JsonView(true, "操作成功!", views, views.Count));
  1673. }
  1674. return Ok(JsonView(false, "操作失败!"));
  1675. #endregion
  1676. }
  1677. /// <summary>
  1678. /// 计算团组ALLType预订数量
  1679. /// </summary>
  1680. /// <param name="diIds"></param>
  1681. /// <returns></returns>
  1682. private async Task<GroupTypeNumberInfo> GroupBookingsNumber(int type, Grp_DelegationInfo info)
  1683. {
  1684. GroupTypeNumberInfo _view = new GroupTypeNumberInfo();
  1685. if (info == null)
  1686. {
  1687. return _view;
  1688. }
  1689. _view.DiId = info.Id;
  1690. _view.GroupName = info.TeamName;
  1691. List<TypeInfo> _types = new List<TypeInfo>();
  1692. #region 计算团组ALLType预订数量
  1693. if (type == 1)
  1694. {
  1695. //接团 客户集团所在地区
  1696. string group_region = string.Empty;
  1697. int group_number = 0;
  1698. if (!string.IsNullOrEmpty(info.ClientUnit))
  1699. {
  1700. var _NewClientData = await _sqlSugar.Queryable<Crm_NewClientData>().Where(it => it.IsDel == 0 && it.Client.Equals(info.ClientUnit)).FirstAsync();
  1701. if (_NewClientData != null)
  1702. {
  1703. var regionInfo = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == _NewClientData.Lvlid).FirstAsync();
  1704. if (regionInfo != null)
  1705. {
  1706. group_region = regionInfo.Name.Replace("级", "");
  1707. group_number++;
  1708. }
  1709. }
  1710. }
  1711. if (group_number > 0)
  1712. {
  1713. _types.Add(new TypeInfo() { Id = 1, RegionItem = new List<RegionInfo>() { new RegionInfo() { Name = group_region, Number = group_number } } });
  1714. }
  1715. }
  1716. else if (type == 2)
  1717. {
  1718. //酒店
  1719. var hotelInfos = await _sqlSugar.Queryable<Grp_HotelReservations>().Where(it => it.IsDel == 0 && it.DiId == info.Id).ToListAsync();
  1720. if (hotelInfos.Count > 0)
  1721. {
  1722. List<RegionInfo> hotelRegions = new List<RegionInfo>();
  1723. foreach (var item in hotelInfos)
  1724. {
  1725. var hotelNumberInfo = new StatisticsHotelInfo()
  1726. {
  1727. HotelName = item.HotelName,
  1728. SingleRoomNum = item.SingleRoomCount,
  1729. DoubleRoomNum = item.DoubleRoomCount,
  1730. SuiteRoomNum = item.SuiteRoomCount,
  1731. OtherRoomNum = item.OtherRoomCount,
  1732. };
  1733. int hotelRoomTotal = item.SingleRoomCount + item.DoubleRoomCount + item.SuiteRoomCount + item.OtherRoomCount;
  1734. if (hotelRegions.Select(it => it.Name).ToList().Contains(item.City))
  1735. {
  1736. RegionInfo hotelRegion = hotelRegions.Find(it => it.Name.Equals(item.City));
  1737. if (hotelRegion != null)
  1738. {
  1739. hotelRegions.Remove(hotelRegion);
  1740. if (hotelRegion.Data.Count > 0)
  1741. {
  1742. hotelRegion.Data.Add(hotelNumberInfo);
  1743. }
  1744. hotelRegion.Number += hotelRoomTotal;
  1745. hotelRegions.Add(hotelRegion);
  1746. }
  1747. }
  1748. else
  1749. {
  1750. hotelRegions.Add(new RegionInfo() { Name = item.City, Number = hotelRoomTotal, Data = new List<dynamic>() { hotelNumberInfo } });
  1751. }
  1752. }
  1753. _types.Add(new TypeInfo() { Id = 2, RegionItem = hotelRegions });
  1754. }
  1755. }
  1756. else if (type == 3)
  1757. {
  1758. //机票
  1759. var airTicketInfos = await _sqlSugar.Queryable<Grp_AirTicketReservations>()
  1760. .LeftJoin<Grp_CreditCardPayment>((atr, ccp) => atr.Id == ccp.CId && ccp.IsPay == 1)
  1761. .LeftJoin<Sys_SetData>((atr, ccp, sd) => atr.CType == sd.Id)
  1762. .Where((atr, ccp, sd) => atr.IsDel == 0 && atr.DIId == info.Id)
  1763. .Select((atr, ccp, sd) => new { atr.ClientNum, atr.CType, ccp.Payee, AirType = sd.Name })
  1764. .ToListAsync();
  1765. if (airTicketInfos.Count > 0)
  1766. {
  1767. List<RegionInfo> airTicketRegions = new List<RegionInfo>();
  1768. foreach (var item in airTicketInfos)
  1769. {
  1770. var ticketClass = new
  1771. {
  1772. TiketClass = item.AirType,
  1773. Number = item.ClientNum
  1774. };
  1775. if (airTicketRegions.Select(it => it.Name).ToList().Contains(item.Payee))
  1776. {
  1777. RegionInfo airTicketRegion = airTicketRegions.Find(it => it.Name.Equals(item.Payee));
  1778. if (airTicketRegion != null)
  1779. {
  1780. airTicketRegions.Remove(airTicketRegion);
  1781. if (airTicketRegion.Data.Count > 0)
  1782. {
  1783. airTicketRegion.Data.Add(ticketClass);
  1784. }
  1785. airTicketRegion.Number += item.ClientNum;
  1786. airTicketRegions.Add(airTicketRegion);
  1787. }
  1788. }
  1789. else
  1790. {
  1791. airTicketRegions.Add(new RegionInfo() { Name = item.Payee, Number = item.ClientNum, Data = new List<dynamic>() { ticketClass } });
  1792. }
  1793. }
  1794. _types.Add(new TypeInfo() { Id = 3, RegionItem = airTicketRegions });
  1795. }
  1796. }
  1797. else if (type == 4)
  1798. {
  1799. //车辆
  1800. var opInfos = await _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>().Where(it => it.IsDel == 0 && it.DiId == info.Id).ToListAsync();
  1801. var opContentInfos = await _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservationsContent>().Where(it => it.IsDel == 0 && it.DiId == info.Id).ToListAsync();
  1802. if (opInfos.Count > 0)
  1803. {
  1804. List<RegionInfo> opRegions = new List<RegionInfo>();
  1805. foreach (var item in opInfos)
  1806. {
  1807. int carNum = 0;
  1808. var opContentInfo = opContentInfos.Where(it => it.CTGGRId == item.Id && it.SId == 91).ToList();
  1809. if (opContentInfo.Count > 0)
  1810. {
  1811. foreach (var item1 in opContentInfo)
  1812. {
  1813. if (item1.Price > 0 && item1.Count > 0)
  1814. {
  1815. carNum += item1.Count;
  1816. }
  1817. }
  1818. }
  1819. if (carNum > 0)
  1820. {
  1821. var opData = new
  1822. {
  1823. ServiceCompany = item.ServiceCompany,
  1824. BusName = item.BusName,
  1825. Numbuer = carNum
  1826. };
  1827. if (opRegions.Select(it => it.Name).ToList().Contains(item.Area))
  1828. {
  1829. RegionInfo opRegion = opRegions.Find(it => it.Name.Equals(item.Area));
  1830. if (opRegion != null)
  1831. {
  1832. opRegions.Remove(opRegion);
  1833. if (opRegion.Data.Count > 0)
  1834. {
  1835. opRegion.Data.Add(opData);
  1836. }
  1837. opRegion.Number += carNum;
  1838. opRegions.Add(opRegion);
  1839. }
  1840. }
  1841. else
  1842. {
  1843. opRegions.Add(new RegionInfo() { Name = item.Area, Number = carNum, Data = new List<dynamic>() { opData } });
  1844. }
  1845. }
  1846. }
  1847. _types.Add(new TypeInfo() { Id = 4, RegionItem = opRegions });
  1848. }
  1849. }
  1850. #endregion
  1851. _view.TypeItem = _types;
  1852. return _view;
  1853. }
  1854. #endregion
  1855. #region 市场部销售额
  1856. /// <summary>
  1857. /// 市场部销售额
  1858. /// Init 基础数据(公司/人员/年份/季度/月份)
  1859. /// </summary>
  1860. /// <param name="_dto">市场部销售额请求dto</param>
  1861. /// <returns></returns>
  1862. [HttpPost("PostMarketingSalesInitData")]
  1863. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1864. public async Task<IActionResult> PostMarketingSalesInitData(MarketingSalesInitDataDto _dto)
  1865. {
  1866. #region 参数验证
  1867. MarketingSalesInitDataDtoFoalidator validationRules = new MarketingSalesInitDataDtoFoalidator();
  1868. var validResult = await validationRules.ValidateAsync(_dto);
  1869. if (!validResult.IsValid)
  1870. {
  1871. var errors = new StringBuilder();
  1872. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  1873. return Ok(JsonView(false, errors.ToString()));
  1874. }
  1875. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  1876. #region 页面操作权限验证
  1877. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  1878. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  1879. #endregion
  1880. var companyData = _sqlSugar.Queryable<Sys_Company>().Where(it => it.IsDel == 0)
  1881. .Select(it => new { id = it.Id, name = it.CompanyName })
  1882. .ToList();
  1883. List<int> companyIds = companyData.Select(it => it.id).ToList();
  1884. List<int> pickGroupUserIds = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0)
  1885. .Select(it => it.JietuanOperator)
  1886. .ToList();
  1887. var userData = _sqlSugar.Queryable<Sys_Users>().Where(it => it.IsDel == 0 && (pickGroupUserIds.Contains(it.Id) || it.Id == 21))
  1888. .Select(it => new { id = it.Id, companyId = it.CompanyId, name = it.CnName })
  1889. .ToList();
  1890. companyData.Insert(0, new { id = -1, name = "全部" });
  1891. userData.Insert(0, new { id = -1, companyId = -1, name = "全部" });
  1892. #region 年份
  1893. var dtData = new List<dynamic>();
  1894. int dt = DateTime.Now.Year;
  1895. for (int y = dt; y >= dt - 4; y--)
  1896. {
  1897. //季度
  1898. var quarterDatas = new List<dynamic>();
  1899. quarterDatas.Add(new { name = "全部", beginDt = $"{y}-01-01", endDt = $"{y}-12-31" });
  1900. for (int q = 0; q < 4; q++)
  1901. {
  1902. dynamic quarterData = null;
  1903. if (q == 0)
  1904. {
  1905. var monthDatas = new List<dynamic>();
  1906. monthDatas.Add(new { name = $"全部", beginDt = $"{y}-01-01", endDt = $" {y}-03-31" });
  1907. for (int m = 1; m < 4; m++)
  1908. {
  1909. MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
  1910. monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
  1911. }
  1912. //quarterData = new { name = "第一季度", beginDt = $"{y}-01-01", endDt = $" {y}-03-31", monthData = monthDatas };
  1913. quarterData = new { name = "第一季度", monthData = monthDatas };
  1914. }
  1915. else if (q == 1)
  1916. {
  1917. var monthDatas = new List<dynamic>();
  1918. monthDatas.Add(new { name = $"全部", beginDt = $"{y}-04-01", endDt = $"{y}-06-30" });
  1919. for (int m = 4; m < 7; m++)
  1920. {
  1921. MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
  1922. monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
  1923. }
  1924. //quarterData = new { name = "第二季度", beginDt = $"{y}-04-01", endDt = $"{y}-06-30", monthData = monthDatas };
  1925. quarterData = new { name = "第二季度", monthData = monthDatas };
  1926. }
  1927. else if (q == 2)
  1928. {
  1929. var monthDatas = new List<dynamic>();
  1930. monthDatas.Add(new { name = $"全部", beginDt = $"{y}-07-01", endDt = $"{y}-09-30" });
  1931. for (int m = 7; m < 10; m++)
  1932. {
  1933. MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
  1934. monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
  1935. }
  1936. //quarterData = new { name = "第三季度", beginDt = $"{y}-07-01", endDt = $"{y}-09-30", monthData = monthDatas };
  1937. quarterData = new { name = "第三季度", monthData = monthDatas };
  1938. }
  1939. else if (q == 3)
  1940. {
  1941. var monthDatas = new List<dynamic>();
  1942. monthDatas.Add(new { name = $"全部", beginDt = $"{y}-10-01", endDt = $"{y}-12-31" });
  1943. for (int m = 10; m < 13; m++)
  1944. {
  1945. MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
  1946. monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
  1947. }
  1948. //quarterData = new { name = "第四季度", beginDt = $"{y}-10-01", endDt = $"{y}-12-31", monthData = monthDatas };
  1949. quarterData = new { name = "第四季度", monthData = monthDatas };
  1950. }
  1951. quarterDatas.Add(quarterData);
  1952. }
  1953. dtData.Add(new
  1954. {
  1955. year = y,
  1956. //yearData = new { beginDt = $"{y}-01-01", endDt = $"{y}-12-31" },
  1957. quarterData = quarterDatas,
  1958. });
  1959. }
  1960. #endregion
  1961. return Ok(JsonView(true, "操作成功!", new { companyData = companyData, userData = userData, dtData = dtData }));
  1962. #endregion
  1963. }
  1964. private static string ConvertToChinese(int month)
  1965. {
  1966. if (month < 1 || month > 12)
  1967. throw new ArgumentOutOfRangeException(nameof(month), "月份必须在1到12之间。");
  1968. var cultureInfo = new System.Globalization.CultureInfo("zh-CN");
  1969. var dateTimeFormat = cultureInfo.DateTimeFormat;
  1970. return dateTimeFormat.GetMonthName(month);
  1971. }
  1972. /// <summary>
  1973. /// 市场部销售额
  1974. /// 年度/季度/月度 报表(同比)
  1975. /// </summary>
  1976. /// <param name="_dto">市场部销售额请求dto</param>
  1977. /// <returns></returns>
  1978. [HttpPost("PostMarketingSalesStatistics")]
  1979. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1980. public async Task<IActionResult> PostMarketingSalesStatistics_Year(MarketingSalesStatisticsDto _dto)
  1981. {
  1982. #region 参数验证
  1983. MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
  1984. var validResult = await validationRules.ValidateAsync(_dto);
  1985. if (!validResult.IsValid)
  1986. {
  1987. var errors = new StringBuilder();
  1988. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  1989. return Ok(JsonView(false, errors.ToString()));
  1990. }
  1991. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  1992. #region 页面操作权限验证
  1993. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  1994. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  1995. #endregion
  1996. #endregion
  1997. string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
  1998. return Ok(JsonView(true, "操作成功!", await GroupSales(_dto.CompanyId, _dto.GroupPickupUserId, beginDt, endDt)));
  1999. }
  2000. private async Task<List<int>> GetUserIds(int companyId, int groupPickupUserId)
  2001. {
  2002. //全部人员Id
  2003. //查询所有公司的市场部
  2004. var pickGroupIds = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0).Select(it => it.JietuanOperator).Distinct().ToList();
  2005. var userIds = new List<int>();
  2006. var userDatas = await _sqlSugar.Queryable<Sys_Users>()
  2007. .InnerJoin<Sys_Company>((u, c) => u.CompanyId == c.Id)
  2008. .Where((u, c) => u.IsDel == 0 && pickGroupIds.Contains(u.Id))
  2009. .Select((u, c) => new { u.Id, u.CompanyId, u.CnName })
  2010. .ToListAsync();
  2011. userIds = userDatas.Select(it => it.Id).ToList();
  2012. if (companyId > 0)
  2013. {
  2014. userIds = userDatas.Where(it => it.CompanyId == companyId).Select(it => it.Id).ToList();
  2015. }
  2016. if (groupPickupUserId > 0)
  2017. {
  2018. userIds = userDatas.Where(it => it.Id == groupPickupUserId).Select(it => it.Id).ToList();
  2019. }
  2020. return userIds;
  2021. }
  2022. /// <summary>
  2023. /// 计算团组销售额
  2024. /// </summary>
  2025. /// <param name="companyId"></param>
  2026. /// <param name="groupPickupUserId"></param>
  2027. /// <param name="beginDt"></param>
  2028. /// <param name="endDt"></param>
  2029. /// <returns></returns>
  2030. private async Task<SalesYOYView> GroupSales(int companyId, int groupPickupUserId, string beginDt, string endDt)
  2031. {
  2032. decimal thisSales = 0.00M, lastSales = 0.00M, yoy = 1.00M;
  2033. var _view = new SalesYOYView();
  2034. List<int> userIds = new List<int>();
  2035. userIds = await GetUserIds(companyId, groupPickupUserId);
  2036. string userSqlWhere = "";
  2037. if (userIds.Count > 0)
  2038. {
  2039. userSqlWhere = string.Format($" And Id IN ({string.Join(',', userIds)})");
  2040. }
  2041. else
  2042. {
  2043. _view = new SalesYOYView
  2044. {
  2045. thisYearSales = "0.00",
  2046. lastYearSales = "0.00",
  2047. };
  2048. return _view;
  2049. }
  2050. string lastBeginDt = Convert.ToDateTime(beginDt).AddYears(-1).ToString("yyyy-MM-dd HH:mm:ss"),
  2051. lastEndDt = Convert.ToDateTime(endDt).AddYears(-1).ToString("yyyy-MM-dd HH:mm:ss");
  2052. // string salesSql = string.Format(@"
  2053. //SELECT
  2054. // 'ThisSales' As [Name],
  2055. // CAST(SUM(Sales) AS decimal(12,2)) As Sales
  2056. //FROM
  2057. // (
  2058. // SELECT di.VisitDate,
  2059. // (SELECT CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
  2060. // WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
  2061. // ) AS Sales
  2062. // FROM
  2063. // Grp_DelegationInfo di
  2064. // WHERE di.IsDel = 0 AND di.IsSure = 1
  2065. // AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0 {0})
  2066. // AND di.VisitDate BETWEEN '{1}' AND '{2}'
  2067. // ) temp
  2068. //Union ALL
  2069. //SELECT
  2070. // 'LastSales' As [Name],
  2071. // CAST(SUM(Sales) AS decimal(12,2)) As Sales
  2072. //FROM
  2073. // (
  2074. // SELECT di.VisitDate,
  2075. // (SELECT CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
  2076. // WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
  2077. // ) AS Sales
  2078. // FROM
  2079. // Grp_DelegationInfo di
  2080. // WHERE di.IsDel = 0 AND di.IsSure = 1
  2081. // AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0 {3})
  2082. // AND di.VisitDate BETWEEN '{4}' AND '{5}'
  2083. // ) temp", userSqlWhere, beginDt, endDt, userSqlWhere, lastBeginDt, lastEndDt);
  2084. string salesSql = string.Format(@"
  2085. SELECT
  2086. 'ThisSales' As [Name],
  2087. CAST(SUM(Sales) AS decimal(12,2)) As Sales
  2088. FROM
  2089. (
  2090. SELECT di.VisitDate,
  2091. (SELECT CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
  2092. WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
  2093. ) AS Sales
  2094. FROM
  2095. Grp_DelegationInfo di
  2096. WHERE di.IsDel = 0
  2097. AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0 {0})
  2098. AND di.VisitDate BETWEEN '{1}' AND '{2}'
  2099. ) temp
  2100. Union ALL
  2101. SELECT
  2102. 'LastSales' As [Name],
  2103. CAST(SUM(Sales) AS decimal(12,2)) As Sales
  2104. FROM
  2105. (
  2106. SELECT di.VisitDate,
  2107. (SELECT CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
  2108. WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
  2109. ) AS Sales
  2110. FROM
  2111. Grp_DelegationInfo di
  2112. WHERE di.IsDel = 0
  2113. AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0 {3})
  2114. AND di.VisitDate BETWEEN '{4}' AND '{5}'
  2115. ) temp", userSqlWhere, beginDt, endDt, userSqlWhere, lastBeginDt, lastEndDt);
  2116. var salesData = await _sqlSugar.SqlQueryable<SalesView>(salesSql).ToListAsync();
  2117. thisSales = salesData.Where(x => x.Name.Equals("ThisSales")).First()?.Sales ?? 0;
  2118. lastSales = salesData.Where(x => x.Name.Equals("LastSales")).First()?.Sales ?? 0;
  2119. if (lastSales != 0 && thisSales != 0) yoy = (thisSales - lastSales) / lastSales;
  2120. return new SalesYOYView()
  2121. {
  2122. thisYearSales = thisSales.ToString("#0.00"),
  2123. lastYearSales = lastSales.ToString("#0.00"),
  2124. yoy = yoy.ToString("#0.00")
  2125. };
  2126. }
  2127. private class SalesView
  2128. {
  2129. public string Name { get; set; }
  2130. public decimal Sales { get; set; }
  2131. }
  2132. private class SalesYOYView
  2133. {
  2134. public string thisYearSales { get; set; }
  2135. public string lastYearSales { get; set; }
  2136. public string yoy { get; set; } = "1.00";
  2137. }
  2138. /// <summary>
  2139. /// 市场部销售额
  2140. /// 团组列表
  2141. /// </summary>
  2142. /// <param name="_dto">市场部销售额请求dto</param>
  2143. /// <returns></returns>
  2144. [HttpPost("PostMarketingSalesGroupList")]
  2145. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2146. public async Task<IActionResult> PostMarketingSalesGroupList(MarketingSalesGroupListDto _dto)
  2147. {
  2148. #region 参数验证
  2149. MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
  2150. var validResult = await validationRules.ValidateAsync(_dto);
  2151. if (!validResult.IsValid)
  2152. {
  2153. var errors = new StringBuilder();
  2154. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  2155. return Ok(JsonView(false, errors.ToString()));
  2156. }
  2157. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2158. #region 页面操作权限验证
  2159. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2160. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  2161. #endregion
  2162. #endregion
  2163. string userSql = "";
  2164. List<int> userIds = new List<int>();
  2165. userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);
  2166. if (userIds.Count <= 0)
  2167. {
  2168. return Ok(JsonView(true, "操作成功!", new List<object> { }, 0));
  2169. }
  2170. else userSql = @$" And di.JietuanOperator In ({string.Join(",", userIds)})";
  2171. if (!string.IsNullOrEmpty(_dto.SearchCriteria))
  2172. {
  2173. userSql += string.Format(@$" AND di.TeamName Like '%{_dto.SearchCriteria}%'");
  2174. }
  2175. string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
  2176. // string sql = string.Format(@$"SELECT
  2177. // ROW_NUMBER() OVER (
  2178. // ORDER BY
  2179. // CollectionDays
  2180. // ) AS RowNumber,
  2181. // *
  2182. //FROM
  2183. // (
  2184. // SELECT
  2185. // di.Id,
  2186. // di.TeamName,
  2187. // di.ClientUnit,
  2188. // di.ClientName,
  2189. // di.VisitDate,
  2190. // di.VisitPNumber,
  2191. // di.JietuanOperator,
  2192. // di.VisitEndDate,
  2193. // (
  2194. // SELECT
  2195. // CAST(
  2196. // COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2197. // ) AS GroupSales
  2198. // FROM
  2199. // Fin_ForeignReceivables
  2200. // WHERE
  2201. // IsDel = 0
  2202. // AND di.Id = Diid
  2203. // AND AddingWay IN (0, 1, 2)
  2204. // ) AS GroupSales,
  2205. // u.CnName AS GroupPickupUser,
  2206. // DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
  2207. // FROM
  2208. // Grp_DelegationInfo di
  2209. // WITH
  2210. // (NoLock)
  2211. // LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
  2212. // WHERE
  2213. // di.Isdel = 0
  2214. // AND di.IsSure = 1 {userSql}
  2215. // AND VisitDate Between '{beginDt}' And '{endDt}'
  2216. // ) Temp ");
  2217. string sql = string.Format(@$"SELECT
  2218. ROW_NUMBER() OVER (
  2219. ORDER BY
  2220. CollectionDays
  2221. ) AS RowNumber,
  2222. *
  2223. FROM
  2224. (
  2225. SELECT
  2226. di.Id,
  2227. di.TeamName,
  2228. di.ClientUnit,
  2229. di.ClientName,
  2230. di.VisitDate,
  2231. di.VisitPNumber,
  2232. di.JietuanOperator,
  2233. di.VisitEndDate,
  2234. (
  2235. SELECT
  2236. CAST(
  2237. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2238. ) AS GroupSales
  2239. FROM
  2240. Fin_ForeignReceivables
  2241. WHERE
  2242. IsDel = 0
  2243. AND di.Id = Diid
  2244. AND AddingWay IN (0, 1, 2)
  2245. ) AS GroupSales,
  2246. u.CnName AS GroupPickupUser,
  2247. DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
  2248. FROM
  2249. Grp_DelegationInfo di
  2250. WITH
  2251. (NoLock)
  2252. LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
  2253. WHERE
  2254. di.Isdel = 0
  2255. AND (
  2256. SELECT
  2257. CAST(
  2258. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2259. ) AS GroupSales
  2260. FROM
  2261. Fin_ForeignReceivables
  2262. WHERE
  2263. IsDel = 0
  2264. AND di.Id = Diid
  2265. AND AddingWay IN (0, 1, 2)
  2266. ) > 0
  2267. {userSql}
  2268. AND VisitDate Between '{beginDt}' And '{endDt}'
  2269. ) Temp ");
  2270. RefAsync<int> total = 0;
  2271. var groupData = await _sqlSugar.SqlQueryable<MarketingSalesGroupList>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);
  2272. return Ok(JsonView(true, "操作成功!", groupData, total));
  2273. }
  2274. /// <summary>
  2275. /// 市场部销售额
  2276. /// 团组列表Excel下载
  2277. /// </summary>
  2278. /// <param name="_dto">市场部销售额请求dto</param>
  2279. /// <returns></returns>
  2280. [HttpPost("PostMarketingSalesGroupExcel")]
  2281. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2282. public async Task<IActionResult> PostMarketingSalesGroupExcel(MarketingSalesGroupListDto _dto)
  2283. {
  2284. #region 参数验证
  2285. MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
  2286. var validResult = await validationRules.ValidateAsync(_dto);
  2287. if (!validResult.IsValid)
  2288. {
  2289. var errors = new StringBuilder();
  2290. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  2291. return Ok(JsonView(false, errors.ToString()));
  2292. }
  2293. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2294. #region 页面操作权限验证
  2295. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2296. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  2297. #endregion
  2298. #endregion
  2299. string userSql = "";
  2300. var userIds = new List<int>();
  2301. userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);
  2302. if (userIds.Count <= 0)
  2303. {
  2304. return Ok(JsonView(false));
  2305. }
  2306. else userSql = @$" And di.JietuanOperator In ({string.Join(",", userIds)})";
  2307. if (!string.IsNullOrEmpty(_dto.SearchCriteria))
  2308. {
  2309. userSql += string.Format(@$" AND di.TeamName Like '%{_dto.SearchCriteria}%'");
  2310. }
  2311. string beginDt = $"{_dto.BeginDt} 00:00:00",
  2312. endDt = $"{_dto.EndDt} 23:59:59";
  2313. string sql = string.Format(@$"SELECT
  2314. ROW_NUMBER() OVER (
  2315. ORDER BY
  2316. CollectionDays
  2317. ) AS RowNumber,
  2318. *
  2319. FROM
  2320. (
  2321. SELECT
  2322. di.Id,
  2323. di.TeamName,
  2324. di.ClientUnit,
  2325. di.ClientName,
  2326. di.VisitDate,
  2327. di.VisitPNumber,
  2328. di.JietuanOperator,
  2329. di.VisitEndDate,
  2330. (
  2331. SELECT
  2332. CAST(
  2333. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2334. ) AS GroupSales
  2335. FROM
  2336. Fin_ForeignReceivables
  2337. WHERE
  2338. IsDel = 0
  2339. AND di.Id = Diid
  2340. AND AddingWay IN (0, 1, 2)
  2341. ) AS GroupSales,
  2342. u.CnName AS GroupPickupUser,
  2343. DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
  2344. FROM
  2345. Grp_DelegationInfo di
  2346. WITH
  2347. (NoLock)
  2348. LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
  2349. WHERE
  2350. di.Isdel = 0
  2351. AND (
  2352. SELECT
  2353. CAST(
  2354. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2355. ) AS GroupSales
  2356. FROM
  2357. Fin_ForeignReceivables
  2358. WHERE
  2359. IsDel = 0
  2360. AND di.Id = Diid
  2361. AND AddingWay IN (0, 1, 2)
  2362. ) > 0
  2363. {userSql}
  2364. AND VisitDate Between '{beginDt}' And '{endDt}'
  2365. ) Temp ");
  2366. var view = await _sqlSugar.SqlQueryable<MarketingSalesGroupList>(sql).ToListAsync();
  2367. if (view.Count < 1 ) return Ok(JsonView(false, "暂无数据!"));
  2368. //DataTable dt = GeneralMethod.
  2369. DataTable dt = CommonFun.GetDataTableFromIList(view);
  2370. dt.TableName = $"_view";
  2371. WorkbookDesigner designer = new WorkbookDesigner();
  2372. designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/营业额团组报表模板.xls");
  2373. designer.SetDataSource(dt);
  2374. designer.SetDataSource("Total", view.Sum(x => x.GroupSales));
  2375. designer.Workbook.Worksheets[0].Name = "营业额团组报表";
  2376. designer.Process();
  2377. string fileName = $"MarketingSales/{_dto.BeginDt}~{_dto.EndDt}营业额团组报表{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
  2378. designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
  2379. string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
  2380. return Ok(JsonView(true, "操作成功", new { url = rst }));
  2381. }
  2382. /// <summary>
  2383. /// 市场部销售额
  2384. /// 客户类型、客户等级 统计
  2385. /// </summary>
  2386. /// <param name="_dto">市场部销售额请求dto</param>
  2387. /// <returns></returns>
  2388. [HttpPost("PostMarketingSalesGroupStatistics")]
  2389. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2390. public async Task<IActionResult> PostMarketingSalesGroupStatistics(MarketingSalesGroupStatisticsDto _dto)
  2391. {
  2392. #region 参数验证
  2393. MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
  2394. var validResult = await validationRules.ValidateAsync(_dto);
  2395. if (!validResult.IsValid)
  2396. {
  2397. var errors = new StringBuilder();
  2398. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  2399. return Ok(JsonView(false, errors.ToString()));
  2400. }
  2401. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2402. #region 页面操作权限验证
  2403. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2404. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  2405. #endregion
  2406. #endregion
  2407. string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
  2408. List<int> userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);
  2409. string userSql = "";
  2410. if (userIds.Count > 0)
  2411. {
  2412. userSql = string.Format(@$" AND JietuanOperator IN ({string.Join(",", userIds)})");
  2413. }
  2414. else
  2415. {
  2416. if (_dto.PortType == 2 || _dto.PortType == 3)
  2417. {
  2418. return Ok(JsonView(true, "操作成功!", new List<object> { }));
  2419. }
  2420. else
  2421. {
  2422. return Ok(JsonView(true, "操作成功!", new
  2423. {
  2424. customerTypeData = new List<object> { },
  2425. clientGradeData = new List<object> { }
  2426. }));
  2427. }
  2428. }
  2429. string sql = "";
  2430. if (_dto.StatisticsType == 1)
  2431. {
  2432. sql = string.Format(@$"Select
  2433. sd.[Name],
  2434. Count(*) As [Count]
  2435. From Grp_DelegationInfo di
  2436. Left Join Sys_SetData sd On di.TeamDid = sd.Id
  2437. Where di.Isdel = 0
  2438. And (
  2439. SELECT
  2440. CAST(
  2441. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2442. ) AS GroupSales
  2443. FROM
  2444. Fin_ForeignReceivables fr
  2445. WHERE
  2446. fr.IsDel = 0
  2447. AND di.Id = fr.Diid
  2448. AND AddingWay IN (0, 1, 2)
  2449. ) > 0
  2450. {userSql}
  2451. And VisitDate Between '{beginDt}' And '{endDt}'
  2452. Group By [Name]
  2453. Order By Count Desc");
  2454. }
  2455. else if (_dto.StatisticsType == 2)
  2456. {
  2457. sql = string.Format(@$"Select
  2458. sd.[Name],
  2459. Count(*) As [Count]
  2460. From Grp_DelegationInfo di
  2461. Left Join Sys_SetData sd On di.TeamLevSId = sd.Id
  2462. Where di.Isdel = 0
  2463. And (
  2464. SELECT
  2465. CAST(
  2466. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2467. ) AS GroupSales
  2468. FROM
  2469. Fin_ForeignReceivables fr
  2470. WHERE
  2471. fr.IsDel = 0
  2472. AND di.Id = fr.Diid
  2473. AND AddingWay IN (0, 1, 2)
  2474. ) > 0
  2475. {userSql}
  2476. And VisitDate Between '{beginDt}' And '{endDt}'
  2477. Group By [Name]
  2478. Order By Count Desc");
  2479. }
  2480. else return Ok(JsonView(false, "StatisticsType不在可取范围!"));
  2481. if (_dto.PortType == 1)
  2482. {
  2483. string sql1 = string.Format(@$"Select
  2484. sd.[Name],
  2485. Count(*) As [Count]
  2486. From Grp_DelegationInfo di
  2487. Left Join Sys_SetData sd On di.TeamDid = sd.Id
  2488. Where di.Isdel = 0
  2489. And (
  2490. SELECT
  2491. CAST(
  2492. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2493. ) AS GroupSales
  2494. FROM
  2495. Fin_ForeignReceivables fr
  2496. WHERE
  2497. fr.IsDel = 0
  2498. AND di.Id = fr.Diid
  2499. AND AddingWay IN (0, 1, 2)
  2500. ) > 0
  2501. {userSql}
  2502. And VisitDate Between '{beginDt}' And '{endDt}'
  2503. Group By [Name]
  2504. Order By Count Desc");
  2505. var customerTypeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql1).ToListAsync();
  2506. string sql2 = string.Format(@$"Select
  2507. sd.[Name],
  2508. Count(*) As [Count]
  2509. From Grp_DelegationInfo di
  2510. Left Join Sys_SetData sd On di.TeamLevSId = sd.Id
  2511. Where di.Isdel = 0
  2512. And (
  2513. SELECT
  2514. CAST(
  2515. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2516. ) AS GroupSales
  2517. FROM
  2518. Fin_ForeignReceivables fr
  2519. WHERE
  2520. fr.IsDel = 0
  2521. AND di.Id = fr.Diid
  2522. AND AddingWay IN (0, 1, 2)
  2523. ) > 0
  2524. {userSql}
  2525. And VisitDate Between '{beginDt}' And '{endDt}'
  2526. Group By [Name]
  2527. Order By Count Desc");
  2528. var clientGradeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql2).ToListAsync();
  2529. return Ok(JsonView(true, "操作成功!", new
  2530. {
  2531. customerTypeData = customerTypeData,
  2532. clientGradeData = clientGradeData
  2533. }));
  2534. }
  2535. else if (_dto.PortType == 2 || _dto.PortType == 3)
  2536. {
  2537. var data = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql).ToListAsync();
  2538. return Ok(JsonView(true, "操作成功!", data, data.Count));
  2539. }
  2540. else return Ok(JsonView(false, MsgTips.Port));
  2541. }
  2542. /// <summary>
  2543. /// 市场部销售额
  2544. /// 接单排名
  2545. /// </summary>
  2546. /// <param name="_dto">市场部销售额请求dto</param>
  2547. /// <returns></returns>
  2548. [HttpPost("PostMarketingSalesOrderRanking")]
  2549. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2550. public async Task<IActionResult> PostMarketingSalesOrderRanking(MarketingSalesOrderRankingDto _dto)
  2551. {
  2552. #region 参数验证
  2553. MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
  2554. var validResult = await validationRules.ValidateAsync(_dto);
  2555. if (!validResult.IsValid)
  2556. {
  2557. var errors = new StringBuilder();
  2558. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  2559. return Ok(JsonView(false, errors.ToString()));
  2560. }
  2561. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2562. #region 页面操作权限验证
  2563. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2564. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  2565. #endregion
  2566. #endregion
  2567. string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
  2568. List<int> userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);
  2569. string userSql = "";
  2570. if (userIds.Count > 0)
  2571. {
  2572. userSql = string.Format(@$" AND JietuanOperator IN ({string.Join(",", userIds)})");
  2573. }
  2574. else return Ok(JsonView(true, "操作成功!", new List<object> { }, 0));
  2575. string sql = string.Format(@$"Select
  2576. ROW_NUMBER() Over(Order By Count(*) Desc) As RowNumber,
  2577. u.CnName As UserName,
  2578. Count(*) As [Count]
  2579. From Grp_DelegationInfo di
  2580. Left Join Sys_Users u On di.JietuanOperator = u.Id
  2581. Where di.Isdel = 0
  2582. And (
  2583. SELECT
  2584. CAST(
  2585. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2586. ) AS GroupSales
  2587. FROM
  2588. Fin_ForeignReceivables fr
  2589. WHERE
  2590. fr.IsDel = 0
  2591. AND di.Id = fr.Diid
  2592. AND AddingWay IN (0, 1, 2)
  2593. ) > 0
  2594. {userSql}
  2595. And VisitDate Between '{beginDt}' And '{endDt}'
  2596. Group By CnName");
  2597. RefAsync<int> total = 0;
  2598. var rankingData = await _sqlSugar.SqlQueryable<MarketingSalesOrderRankingView>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);
  2599. return Ok(JsonView(true, "操作成功!", rankingData, total));
  2600. }
  2601. /// <summary>
  2602. /// 市场部销售额
  2603. /// 团组列表、客户类型、客户等级、接单排名
  2604. /// </summary>
  2605. /// <param name="_dto">市场部销售额请求dto</param>
  2606. /// <returns></returns>
  2607. [HttpPost("PostMarketingSalesGroupItem")]
  2608. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2609. public async Task<IActionResult> PostMarketingSalesGroupItem(MarketingSalesGroupListDto _dto)
  2610. {
  2611. #region 参数验证
  2612. MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
  2613. var validResult = await validationRules.ValidateAsync(_dto);
  2614. if (!validResult.IsValid)
  2615. {
  2616. var errors = new StringBuilder();
  2617. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  2618. return Ok(JsonView(false, errors.ToString()));
  2619. }
  2620. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2621. #region 页面操作权限验证
  2622. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2623. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  2624. #endregion
  2625. #endregion
  2626. string userSql = "";
  2627. List<int> userIds = new List<int>();
  2628. userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);
  2629. if (userIds.Count <= 0)
  2630. {
  2631. return Ok(JsonView(true, "操作成功!", new List<object> { }, 0));
  2632. }
  2633. else userSql = @$" And di.JietuanOperator In ({string.Join(",", userIds)})";
  2634. if (!string.IsNullOrEmpty(_dto.SearchCriteria))
  2635. {
  2636. userSql += string.Format(@$" AND di.TeamName Like '%{_dto.SearchCriteria}%'");
  2637. }
  2638. string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
  2639. #region 团组List
  2640. string sql = string.Format(@$"SELECT
  2641. ROW_NUMBER() OVER (
  2642. ORDER BY
  2643. CollectionDays
  2644. ) AS RowNumber,
  2645. *
  2646. FROM
  2647. (
  2648. SELECT
  2649. di.Id,
  2650. di.TeamName,
  2651. di.ClientUnit,
  2652. di.ClientName,
  2653. di.VisitDate,
  2654. di.VisitPNumber,
  2655. di.JietuanOperator,
  2656. di.VisitEndDate,
  2657. (
  2658. SELECT
  2659. CAST(
  2660. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2661. ) AS GroupSales
  2662. FROM
  2663. Fin_ForeignReceivables
  2664. WHERE
  2665. IsDel = 0
  2666. AND di.Id = Diid
  2667. AND AddingWay IN (0, 1, 2)
  2668. ) AS GroupSales,
  2669. u.CnName AS GroupPickupUser,
  2670. DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
  2671. FROM
  2672. Grp_DelegationInfo di
  2673. WITH
  2674. (NoLock)
  2675. LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
  2676. WHERE
  2677. di.Isdel = 0
  2678. AND (
  2679. SELECT
  2680. CAST(
  2681. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2682. ) AS GroupSales
  2683. FROM
  2684. Fin_ForeignReceivables fr
  2685. WHERE
  2686. fr.IsDel = 0
  2687. AND di.Id = fr.Diid
  2688. AND AddingWay IN (0, 1, 2)
  2689. ) > 0
  2690. {userSql}
  2691. AND VisitDate Between '{beginDt}' And '{endDt}'
  2692. ) Temp ");
  2693. RefAsync<int> total = 0;
  2694. var groupData = await _sqlSugar.SqlQueryable<MarketingSalesGroupList>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);
  2695. #endregion
  2696. #region 客户类型、客户等级 统计
  2697. string sql1 = string.Format(@$"Select
  2698. sd.[Name],
  2699. Count(*) As [Count]
  2700. From Grp_DelegationInfo di
  2701. Left Join Sys_SetData sd On di.TeamDid = sd.Id
  2702. Where di.Isdel = 0
  2703. And (
  2704. SELECT
  2705. CAST(
  2706. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2707. ) AS GroupSales
  2708. FROM
  2709. Fin_ForeignReceivables fr
  2710. WHERE
  2711. fr.IsDel = 0
  2712. AND di.Id = fr.Diid
  2713. AND AddingWay IN (0, 1, 2)
  2714. ) > 0
  2715. {userSql}
  2716. And VisitDate Between '{beginDt}' And '{endDt}'
  2717. Group By [Name]
  2718. Order By Count Desc");
  2719. var customerTypeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql1).ToListAsync();
  2720. string sql2 = string.Format(@$"Select
  2721. sd.[Name],
  2722. Count(*) As [Count]
  2723. From Grp_DelegationInfo di
  2724. Left Join Sys_SetData sd On di.TeamLevSId = sd.Id
  2725. Where di.Isdel = 0
  2726. And (
  2727. SELECT
  2728. CAST(
  2729. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2730. ) AS GroupSales
  2731. FROM
  2732. Fin_ForeignReceivables fr
  2733. WHERE
  2734. fr.IsDel = 0
  2735. AND di.Id = fr.Diid
  2736. AND AddingWay IN (0, 1, 2)
  2737. ) > 0
  2738. {userSql}
  2739. And VisitDate Between '{beginDt}' And '{endDt}'
  2740. Group By [Name]
  2741. Order By Count Desc");
  2742. var clientGradeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql2).ToListAsync();
  2743. #endregion
  2744. #region 接单排名
  2745. string sql4 = string.Format(@$"Select
  2746. ROW_NUMBER() Over(Order By Count(*) Desc) As RowNumber,
  2747. u.CnName As UserName,
  2748. Count(*) As [Count]
  2749. From Grp_DelegationInfo di
  2750. Left Join Sys_Users u On di.JietuanOperator = u.Id
  2751. Where di.Isdel = 0
  2752. And (
  2753. SELECT
  2754. CAST(
  2755. COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
  2756. ) AS GroupSales
  2757. FROM
  2758. Fin_ForeignReceivables fr
  2759. WHERE
  2760. fr.IsDel = 0
  2761. AND di.Id = fr.Diid
  2762. AND AddingWay IN (0, 1, 2)
  2763. ) > 0
  2764. {userSql}
  2765. And VisitDate Between '{beginDt}' And '{endDt}'
  2766. Group By CnName");
  2767. var rankingData = await _sqlSugar.SqlQueryable<MarketingSalesOrderRankingView>(sql4).ToListAsync();
  2768. #endregion
  2769. var viewData = new
  2770. {
  2771. groupData = groupData,
  2772. groupTotal = total,
  2773. customerTypeData = customerTypeData,
  2774. clientGradeData = clientGradeData,
  2775. rankingData = rankingData
  2776. };
  2777. return Ok(JsonView(true, "操作成功!", viewData, total));
  2778. }
  2779. /// <summary>
  2780. /// 市场部销售额
  2781. /// 客户拜访列表
  2782. /// </summary>
  2783. /// <param name="_dto">市场部销售额请求dto</param>
  2784. /// <returns></returns>
  2785. [HttpPost("PostMarketingSalesVCList")]
  2786. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2787. public async Task<IActionResult> PostMarketingSalesVCList(MarketingSalesVCListDto _dto)
  2788. {
  2789. #region 参数验证
  2790. MarketingSalesUserPageFuncDtoBaseFoalidator validationRules = new MarketingSalesUserPageFuncDtoBaseFoalidator();
  2791. var validResult = await validationRules.ValidateAsync(_dto);
  2792. if (!validResult.IsValid)
  2793. {
  2794. var errors = new StringBuilder();
  2795. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  2796. return Ok(JsonView(false, errors.ToString()));
  2797. }
  2798. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2799. #region 页面操作权限验证
  2800. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2801. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  2802. #endregion
  2803. #endregion
  2804. return Ok(await _visitingClientsRep._List(_dto.PortType, _dto.PageIndex, _dto.PageSize, _dto.DiId, _dto.Search));
  2805. }
  2806. /// <summary>
  2807. /// 市场部销售额
  2808. /// 客户拜访 操作(添加 Or 编辑)
  2809. /// </summary>
  2810. /// <param name="_dto">市场部销售额请求dto</param>
  2811. /// <returns></returns>
  2812. [HttpPost("PostMarketingSalesVCOperate")]
  2813. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2814. public async Task<IActionResult> PostMarketingSalesVCOperate(MarketingSalesVCOperrateDto _dto)
  2815. {
  2816. #region 参数验证
  2817. MarketingSalesUserPageFuncDtoBaseFoalidator validationRules = new MarketingSalesUserPageFuncDtoBaseFoalidator();
  2818. var validResult = await validationRules.ValidateAsync(_dto);
  2819. if (!validResult.IsValid)
  2820. {
  2821. var errors = new StringBuilder();
  2822. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  2823. return Ok(JsonView(false, errors.ToString()));
  2824. }
  2825. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2826. #region 页面操作权限验证
  2827. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2828. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  2829. #endregion
  2830. #endregion
  2831. return Ok(await _visitingClientsRep._AddOrEdit(_dto));
  2832. }
  2833. /// <summary>
  2834. /// 市场部销售额
  2835. /// 客户拜访 Del
  2836. /// </summary>
  2837. /// <param name="_dto">市场部销售额请求dto</param>
  2838. /// <returns></returns>
  2839. [HttpPost("PostMarketingSalesVCDel")]
  2840. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2841. public async Task<IActionResult> PostMarketingSalesVCDel(MarketingSalesVCDelDto _dto)
  2842. {
  2843. return Ok(await _visitingClientsRep._Del(_dto.Id, _dto.UserId));
  2844. }
  2845. /// <summary>
  2846. /// 市场部销售额
  2847. /// 客户拜访 操作 save
  2848. /// </summary>
  2849. /// <param name="_dto">市场部销售额请求dto</param>
  2850. /// <returns></returns>
  2851. [HttpPost("PostMarketingSalesVCSave")]
  2852. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2853. public async Task<IActionResult> PostMarketingSalesVCSave(MarketingSalesVCSaveDto _dto)
  2854. {
  2855. #region 参数验证
  2856. MarketingSalesUserPageFuncDtoBaseFoalidator validationRules = new MarketingSalesUserPageFuncDtoBaseFoalidator();
  2857. var validResult = await validationRules.ValidateAsync(_dto);
  2858. if (!validResult.IsValid)
  2859. {
  2860. var errors = new StringBuilder();
  2861. foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
  2862. return Ok(JsonView(false, errors.ToString()));
  2863. }
  2864. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2865. #region 页面操作权限验证
  2866. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2867. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
  2868. #endregion
  2869. #endregion
  2870. return Ok(await _visitingClientsRep._Save(_dto));
  2871. }
  2872. #endregion
  2873. #region 日付报表
  2874. /// <summary>
  2875. /// 日付类型数据
  2876. /// </summary>
  2877. /// <param name="_dto"></param>
  2878. /// <returns></returns>
  2879. [HttpPost("DailypaymentTypeInit")]
  2880. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2881. public async Task<IActionResult> DailypaymentTypeInit(DailypaymentTypeInitDto _dto)
  2882. {
  2883. #region 参数验证
  2884. if (_dto.PortType < 1 || _dto.PortType > 3) return Ok(JsonView(false, msg: MsgTips.Port));
  2885. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2886. #region 页面操作权限验证
  2887. //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2888. //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
  2889. #endregion
  2890. #endregion
  2891. if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
  2892. {
  2893. var defaultParentIds = new List<int>() {
  2894. 48,// 人员费用
  2895. 49,// 办公费用
  2896. 50,// 销售费用
  2897. 51,// 其他费用
  2898. 55,// 大运会
  2899. };
  2900. var dailypaymentTypeData = await RedisRepository.RedisFactory
  2901. .CreateRedisRepository()
  2902. .StringGetAsync<List<int>>("DailypaymentTypeData") ?? new List<int>();
  2903. var dailyTypeData = await _sqlSugar.Queryable<DailypaymentParentTypeView>()
  2904. .Includes(x => x.SubData)
  2905. .Where(x => defaultParentIds.Contains(x.Id))
  2906. .ToListAsync();
  2907. dailyTypeData.ForEach(x =>
  2908. {
  2909. x.SubData.ForEach(y =>
  2910. {
  2911. int currId = dailypaymentTypeData.Find(z => z == y.Id);
  2912. y.IsChecked = currId == 0 ? false : true;
  2913. });
  2914. });
  2915. var companyData = await _sqlSugar.Queryable<Sys_Company>()
  2916. .Where(x => x.IsDel == 0)
  2917. .Select(x => new { id = x.Id, name = x.CompanyName })
  2918. .ToListAsync();
  2919. return Ok(JsonView(true, "查询成功!", new { dailyTypeData = dailyTypeData, companyData = companyData }));
  2920. }
  2921. else
  2922. {
  2923. return Ok(JsonView(false, "查询失败"));
  2924. }
  2925. }
  2926. /// <summary>
  2927. /// 日付类型数据 Save
  2928. /// </summary>
  2929. /// <param name="_dto"></param>
  2930. /// <returns></returns>
  2931. [HttpPost("DailypaymentTypeDataSave")]
  2932. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2933. public async Task<IActionResult> DailypaymentTypeDataSave(DailypaymentTypeDataSaveDto _dto)
  2934. {
  2935. #region 参数验证
  2936. if (_dto.PortType < 1 || _dto.PortType > 3) return Ok(JsonView(false, msg: MsgTips.Port));
  2937. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2938. #region 页面操作权限验证
  2939. //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2940. //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
  2941. #endregion
  2942. #endregion
  2943. if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
  2944. {
  2945. if (_dto.TypeIds.Count < 1) return Ok(JsonView(false, "请传入需要保存的TypeIds"));
  2946. var res = await RedisRepository.RedisFactory
  2947. .CreateRedisRepository()
  2948. .StringSetAsync(
  2949. key: "DailypaymentTypeData",
  2950. _dto.TypeIds,
  2951. timeout: null);
  2952. if (!res) return Ok(JsonView(false, "操作失败"));
  2953. return Ok(JsonView(true, "操作成功!"));
  2954. }
  2955. else return Ok(JsonView(false, "操作失败"));
  2956. }
  2957. /// <summary>
  2958. /// 日付数据列表
  2959. /// </summary>
  2960. /// <param name="_dto"></param>
  2961. /// <returns></returns>
  2962. [HttpPost("DailypaymentRange")]
  2963. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2964. public async Task<IActionResult> DailypaymentRange(DailypaymentRangeDto _dto)
  2965. {
  2966. #region 参数验证
  2967. var validator = new DailypaymentRangeDtoValidator();
  2968. var validationRes = validator.Validate(_dto);
  2969. if (!validationRes.IsValid)
  2970. {
  2971. StringBuilder sb = new StringBuilder();
  2972. foreach (var item in validationRes.Errors)
  2973. {
  2974. sb.AppendLine(item.ErrorMessage);
  2975. }
  2976. return Ok(JsonView(false, sb.ToString()));
  2977. }
  2978. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2979. #region 页面操作权限验证
  2980. //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
  2981. //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
  2982. #endregion
  2983. #endregion
  2984. if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
  2985. {
  2986. DateTime _beginDt = Convert.ToDateTime($"{_dto.BeginDt} 00:00:00"),
  2987. _endDt = Convert.ToDateTime($"{_dto.EndDt} 23:59:59");
  2988. var sqlWhere = string.Format(@" And dfp.CreateTime Between '{0}' And '{1}'", _beginDt, _endDt);
  2989. var dailypaymentTypeData = await RedisRepository.RedisFactory
  2990. .CreateRedisRepository()
  2991. .StringGetAsync<List<int>>("DailypaymentTypeData") ?? new List<int>();
  2992. var sql = string.Format(@"
  2993. Select
  2994. dfp.Id,
  2995. dfp.Instructions,
  2996. dfp.PriceTypeId,
  2997. sd1.Name As FeeType,
  2998. dfp.TransferTypeId,
  2999. sd2.Name As TransferType,
  3000. dfp.SumPrice,
  3001. dfp.FAuditDate,
  3002. dfp.MAuditDate,
  3003. dfp.CompanyId,
  3004. c.CompanyName,
  3005. dfp.CreateUserId As ApplicantId,
  3006. u.CnName As Applicant,
  3007. dfp.CreateTime As ApplicantDt,
  3008. dfp.Instructions+u.CnName As ViewStr
  3009. From Fin_DailyFeePayment dfp
  3010. Left Join Sys_Users u On dfp.CreateUserId = u.Id
  3011. Left Join Sys_SetData sd1 On dfp.TransferTypeId = sd1.Id
  3012. Left Join Sys_Company c On dfp.CompanyId = c.Id
  3013. Left Join Sys_SetData sd2 On dfp.PriceTypeId = sd2.Id
  3014. Where dfp.IsDel = 0 And dfp.IsPay = 1
  3015. ");//--Order By dfp.CreateTime Desc
  3016. if (_dto.Type == 1) //data
  3017. {
  3018. RefAsync<int> total = 0;
  3019. var _view = await _sqlSugar.SqlQueryable<DailyFeePaymentRangeView>(sql)
  3020. .Where(x => x.ApplicantDt >= _beginDt && x.ApplicantDt <= _endDt)
  3021. .WhereIF(dailypaymentTypeData.Count > 0,
  3022. x => dailypaymentTypeData.Contains(x.PriceTypeId))
  3023. .WhereIF(_dto.CompanyIds.Count > 0, x => _dto.CompanyIds.Contains(x.CompanyId))
  3024. .WhereIF(!string.IsNullOrEmpty(_dto.Filter), x => x.ViewStr.Contains(_dto.Filter))
  3025. .OrderByDescending(x => x.ApplicantDt)
  3026. .ToPageListAsync(
  3027. pageNumber: _dto.PageIndex,
  3028. pageSize: _dto.PageSize,
  3029. totalNumber: total
  3030. );
  3031. _sqlSugar.ThenMapper(_view, x =>
  3032. {
  3033. x.Contents = _sqlSugar.Queryable<DailyFeePaymentContentView>()
  3034. .SetContext(x1 => x1.DFPId, () => x.Id, x)
  3035. .ToList();
  3036. });
  3037. //单独处理
  3038. return Ok(JsonView(true, "操作成功!", new { data = _view, total = _view.Sum(x => x.SumPrice) }, total));
  3039. }
  3040. else if (_dto.Type == 2) //view
  3041. {
  3042. var _view = await _sqlSugar.SqlQueryable<ExcelView>(sql)
  3043. .Where(x => x.ApplicantDt >= _beginDt && x.ApplicantDt <= _endDt)
  3044. .WhereIF(dailypaymentTypeData.Count > 0,
  3045. x => dailypaymentTypeData.Contains(x.PriceTypeId))
  3046. .WhereIF(_dto.CompanyIds.Count > 0, x => _dto.CompanyIds.Contains(x.CompanyId))
  3047. .WhereIF(!string.IsNullOrEmpty(_dto.Filter), x => x.ViewStr.Contains(_dto.Filter))
  3048. .OrderByDescending(x => x.ApplicantDt)
  3049. .ToPageListAsync(
  3050. pageNumber: 1,
  3051. pageSize: 99999
  3052. );
  3053. _sqlSugar.ThenMapper(_view, x =>
  3054. {
  3055. x.Contents = _sqlSugar.Queryable<DailyFeePaymentContentView>()
  3056. .SetContext(x1 => x1.DFPId, () => x.Id, x)
  3057. .ToList();
  3058. string str = "";
  3059. int index = 1;
  3060. foreach (var item in x.Contents)
  3061. {
  3062. string str1 = $"{index}、费用名称:[{item.PriceName}] 单价:[{item.Price:#0.00}] 数量:[{item.Quantity:#0.00}] 小计:[{item.ItemTotal:#0.00}] 备注:[{item.Remark}]";
  3063. if (index == x.Contents.Count) str += str1;
  3064. else str += str1 + "\r\n";
  3065. index++;
  3066. }
  3067. x.ContentStr = str;
  3068. });
  3069. if (_view.Count > 0)
  3070. {
  3071. //DataTable dt = GeneralMethod.
  3072. DataTable dt = CommonFun.GetDataTableFromIList(_view);
  3073. dt.TableName = $"_view";
  3074. decimal total = _view.Sum(x => x.SumPrice);
  3075. WorkbookDesigner designer = new WorkbookDesigner();
  3076. designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/日付报表模板.xls");
  3077. designer.SetDataSource(dt);
  3078. designer.SetDataSource("Total", total);
  3079. designer.Workbook.Worksheets[0].Name = "日付报表";
  3080. designer.Process();
  3081. string fileName = $"DailyPayment/日付报表{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
  3082. designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
  3083. string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
  3084. return Ok(JsonView(true, "操作成功", new { url = rst }));
  3085. }
  3086. return Ok(JsonView(true, ",暂无数据"));
  3087. }
  3088. return Ok(JsonView(false, "操作失败"));
  3089. }
  3090. else return Ok(JsonView(false, "操作失败"));
  3091. }
  3092. #endregion
  3093. #region 统计模块
  3094. /// <summary>
  3095. /// 同比
  3096. /// 营业额(今年和去年的)、成本支出(今年和去年的)、毛利润(今年和去年的)
  3097. /// </summary>
  3098. /// <param name="_dto">市场部销售额请求dto</param>
  3099. /// <returns></returns>
  3100. [HttpPost("StatisticsYOY")]
  3101. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3102. public async Task<IActionResult> StatisticsYOY(YOYDto _dto)
  3103. {
  3104. //同比增长率 = (本期 - 同期) / 同期 * 100%;
  3105. if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));
  3106. string beginDt = $"{_dto.Year - 1}-01-01 00:00:00",
  3107. endDt = $"{_dto.Year}-12-31 23:59:59";
  3108. string sql = string.Format(@"
  3109. SELECT
  3110. di.Id,
  3111. di.TeamName,
  3112. di.VisitDate,
  3113. YEAR(di.VisitDate) AS YEAR,
  3114. MONTH(di.VisitDate) AS MONTH,
  3115. (
  3116. SELECT
  3117. CAST(SUM(ItemSumPrice * Rate) AS DECIMAL(12, 2))
  3118. FROM
  3119. Fin_ForeignReceivables
  3120. WHERE
  3121. IsDel = 0
  3122. AND AddingWay IN (0, 1, 2)
  3123. AND di.Id = Diid
  3124. ) AS SaleAmount,
  3125. (
  3126. SELECT
  3127. CAST(
  3128. SUM(
  3129. (
  3130. ((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
  3131. )
  3132. ) AS DECIMAL(12, 2)
  3133. )
  3134. FROM
  3135. Grp_AirTicketReservations atr
  3136. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  3137. AND ccp.CTable = 85
  3138. AND atr.Id = ccp.CId
  3139. WHERE
  3140. atr.IsDel = 0
  3141. AND ccp.IsAuditGM = 1
  3142. AND ccp.IsPay = 1
  3143. AND di.Id = atr.DiId
  3144. ) AirTicketAmount,
  3145. (
  3146. SELECT
  3147. CAST(
  3148. SUM(
  3149. (
  3150. ctggr.ServiceQuotedPrice * (ccp.PayPercentage / 100) * ccp.DayRate
  3151. )
  3152. ) AS DECIMAL(12, 2)
  3153. )
  3154. FROM
  3155. Grp_CarTouristGuideGroundReservations ctggr
  3156. INNER JOIN Grp_CreditCardPayment ccp ON ccp.IsDel = 0
  3157. AND ccp.CTable = 79
  3158. AND ctggr.Id = ccp.CId
  3159. WHERE
  3160. ctggr.IsDel = 0
  3161. AND ccp.IsAuditGM = 1
  3162. AND ccp.IsPay = 1
  3163. AND di.Id = ctggr.DiId
  3164. ) AS OPAmount,
  3165. (
  3166. SELECT
  3167. CAST(SUM((ccp.PayMoney * ccp.DayRate)) AS DECIMAL(12, 2))
  3168. FROM
  3169. Grp_DecreasePayments dp
  3170. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  3171. AND ccp.CTable = 98
  3172. AND dp.Id = ccp.CId
  3173. WHERE
  3174. dp.IsDel = 0
  3175. AND ccp.Ctable = 98
  3176. AND ccp.IsAuditGM = 1
  3177. AND ccp.IsPay = 1
  3178. AND di.Id = dp.Diid
  3179. ) AS OtherCostAmount,
  3180. (
  3181. SELECT
  3182. CAST(
  3183. SUM(
  3184. (
  3185. (ccp.PayMoney * ccp.DayRate)
  3186. )
  3187. ) AS DECIMAL(12, 2)
  3188. )
  3189. FROM
  3190. Grp_VisaInfo vi
  3191. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  3192. AND ccp.CTable = 80
  3193. AND vi.Id = ccp.CId
  3194. WHERE
  3195. vi.IsDel = 0
  3196. AND ccp.IsAuditGM = 1
  3197. AND ccp.IsPay = 1
  3198. AND di.Id = vi.DIId
  3199. ) AS VisaAmount,
  3200. (
  3201. SELECT
  3202. CAST(
  3203. SUM(
  3204. (ccp.PayMoney * ccp.DayRate)
  3205. ) AS DECIMAL(12, 2)
  3206. )
  3207. FROM
  3208. Grp_InvitationOfficialActivities ioa
  3209. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  3210. AND ccp.CTable = 81
  3211. AND ioa.Id = ccp.CId
  3212. WHERE
  3213. ioa.IsDel = 0
  3214. AND ccp.IsAuditGM = 1
  3215. AND ccp.IsPay = 1
  3216. AND di.Id = ioa.Diid
  3217. ) AS OAAmount,
  3218. (
  3219. SELECT
  3220. CAST(
  3221. SUM(
  3222. (ccp.PayMoney * ccp.DayRate)
  3223. ) AS DECIMAL(12, 2)
  3224. )
  3225. FROM
  3226. Grp_Customers ic
  3227. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  3228. AND ccp.CTable = 82
  3229. AND ic.Id = ccp.CId
  3230. WHERE
  3231. ic.IsDel = 0
  3232. AND ccp.IsAuditGM = 1
  3233. AND ccp.IsPay = 1
  3234. AND di.Id = ic.DiId
  3235. ) AS InsureAmount,
  3236. (
  3237. SELECT
  3238. CAST(
  3239. SUM(
  3240. (ccp.PayMoney * ccp.DayRate)
  3241. ) AS DECIMAL(12, 2)
  3242. )
  3243. FROM
  3244. Grp_HotelReservations hr
  3245. INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
  3246. WHERE
  3247. hr.IsDel = 0
  3248. AND ccp.IsDel = 0
  3249. AND ccp.CTable = 76
  3250. AND ccp.IsAuditGM = 1
  3251. AND ccp.PayMoney <> 0
  3252. AND ccp.IsPay = 1
  3253. AND di.Id = hr.DiId
  3254. ) AS HotelAmount,
  3255. (
  3256. SELECT
  3257. CAST(
  3258. SUM((gec.PriceSum * gec.Coefficient * ccp.DayRate)) AS DECIMAL(12, 2)
  3259. )
  3260. FROM
  3261. OA2023DB.dbo.Fin_GroupExtraCost gec
  3262. INNER JOIN Grp_CreditCardPayment ccp ON gec.Id = ccp.CId
  3263. WHERE
  3264. ccp.IsDel = 0
  3265. AND ccp.CTable = 1015
  3266. AND ccp.IsAuditGM = 1
  3267. AND ccp.IsPay = 1
  3268. AND di.Id = gec.DiId
  3269. ) AS ExcessAmount,
  3270. (
  3271. SELECT
  3272. CAST(
  3273. SUM(
  3274. (ccp.PayMoney * ccp.DayRate)
  3275. ) AS DECIMAL(12, 2)
  3276. )
  3277. FROM
  3278. Fin_PaymentRefundAndOtherMoney prom
  3279. INNER JOIN Grp_CreditCardPayment ccp ON prom.DiId = ccp.DIId
  3280. AND prom.Id = ccp.CId
  3281. WHERE
  3282. prom.IsDel = 0
  3283. AND prom.PayType = 1
  3284. AND ccp.CTable = 285
  3285. AND ccp.IsAuditGM = 1
  3286. AND ccp.IsPay = 1
  3287. AND di.Id = prom.DiId
  3288. ) AS SKTHAmount
  3289. FROM
  3290. Grp_DelegationInfo di
  3291. WHERE
  3292. di.IsDel = 0
  3293. AND di.TeamDid IN (38,39,40,1048)
  3294. AND di.VisitDate BETWEEN '{0}' AND '{1}'
  3295. ORDER BY
  3296. di.VisitDate
  3297. ", beginDt, endDt);
  3298. var data = await _sqlSugar.SqlQueryable<StatisticsYOYView>(sql).ToListAsync();
  3299. var thisYearData = data.Where(x => x.Year == _dto.Year).ToList();
  3300. var lastYearData = data.Where(x => x.Year == (_dto.Year - 1)).ToList();
  3301. List<YOYReturnView> salesYOYData = new List<YOYReturnView>();
  3302. List<YOYReturnView> costYOYData = new List<YOYReturnView>();
  3303. List<YOYReturnView> grossProfitYOYData = new List<YOYReturnView>();
  3304. int monthIndex = 1;
  3305. while (monthIndex < 13)
  3306. {
  3307. var thisMonthData = thisYearData.Where(x => x.Month == monthIndex).ToList();
  3308. var lastMonthData = lastYearData.Where(x => x.Month == monthIndex).ToList();
  3309. var thisGroupIds = thisMonthData.Select(x => x.Id).ToList();
  3310. var lastGroupIds = lastMonthData.Select(x => x.Id).ToList();
  3311. decimal thisSalesAmount = thisMonthData.Sum(x => x?.SaleAmount ?? 0.00M),
  3312. lastSalesAmount = lastMonthData.Sum(x => x?.SaleAmount ?? 0.00M),
  3313. thisCostAmount = thisMonthData.Sum(x => x?.CostAmount ?? 0.00M),
  3314. lastCostAmount = lastMonthData.Sum(x => x?.CostAmount ?? 0.00M),
  3315. thisgrossProfitAmount = thisMonthData.Sum(x => x?.GrossProfitAmount ?? 0.00M),
  3316. lastgrossProfitAmount = lastMonthData.Sum(x => x?.GrossProfitAmount ?? 0.00M);
  3317. salesYOYData.Add(new YOYReturnView(_dto.Year, monthIndex, thisSalesAmount, lastSalesAmount, thisGroupIds, lastGroupIds));
  3318. costYOYData.Add(new YOYReturnView(_dto.Year, monthIndex, thisCostAmount, lastCostAmount, thisGroupIds, lastGroupIds));
  3319. grossProfitYOYData.Add(new YOYReturnView(_dto.Year, monthIndex, thisgrossProfitAmount, lastgrossProfitAmount, thisGroupIds, lastGroupIds));
  3320. monthIndex++;
  3321. }
  3322. salesYOYData = salesYOYData.OrderBy(x => x.Month).ToList();
  3323. costYOYData = costYOYData.OrderBy(x => x.Month).ToList();
  3324. grossProfitYOYData = grossProfitYOYData.OrderBy(x => x.Month).ToList();
  3325. return Ok(JsonView(true, "操作成功!", new { salesYOYData = salesYOYData, costYOYData = costYOYData, grossProfitYOYData = grossProfitYOYData }));
  3326. }
  3327. /// <summary>
  3328. /// 国交数据统计-机票相关
  3329. /// 机票票数、机票到达地、预订平台
  3330. /// </summary>
  3331. /// <param name="_dto"></param>
  3332. /// <returns></returns>
  3333. [HttpPost("StatisticsAirTicket")]
  3334. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3335. public async Task<IActionResult> StatisticsAirTicket(YOYDto _dto)
  3336. {
  3337. if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));
  3338. string beginDt = $"{_dto.Year}-01-01 00:00:00",
  3339. endDt = $"{_dto.Year}-12-31 23:59:59";
  3340. string sql = string.Format(@"
  3341. SELECT
  3342. atr.DIId,
  3343. atr.FlightsDate,
  3344. YEAR(atr.FlightsDate) AS YEAR,
  3345. MONTH(atr.FlightsDate) AS MONTH,
  3346. atr.CType,
  3347. sd.Name As CTypeName,
  3348. atr.FlightsCity,
  3349. atr.ClientNum,
  3350. atr.ClientName,
  3351. di.TeamName As 'GroupName',
  3352. CASE
  3353. WHEN ccp.Payee = '' OR ccp.Payee IS NULL THEN '未知'
  3354. ELSE ccp.Payee
  3355. END AS 'Platform'
  3356. FROM
  3357. Grp_AirTicketReservations atr
  3358. INNER JOIN Grp_CreditCardPayment ccp ON atr.Id = ccp.CId
  3359. AND ccp.IsDel = 0
  3360. AND CTable = 85
  3361. INNER JOIN Grp_DelegationInfo di ON atr.DIId = di.Id
  3362. LEFT JOIN Sys_SetData sd On atr.CType = sd.Id
  3363. WHERE
  3364. atr.IsDel = 0
  3365. AND di.TeamDid IN (38,39,40,1048)
  3366. AND atr.CType IN(457,458,459,460,574,575)
  3367. AND CHARINDEX('行程单',atr.ClientName) = 0
  3368. AND CHARINDEX('返点',atr.ClientName) = 0
  3369. AND CHARINDEX('-1',atr.ClientName) = 0
  3370. AND ccp.IsPay = 1
  3371. AND atr.FlightsDate BETWEEN '{0}' AND '{1}'
  3372. ORDER BY atr.FlightsDate
  3373. ", beginDt, endDt);
  3374. var data = await _sqlSugar.SqlQueryable<StatisticsAirTicketView>(sql).ToListAsync();
  3375. var groupIds = data.Select(x => x.DIId).ToList();
  3376. //处理城市数据
  3377. var threeCodeData = await _sqlSugar.Queryable<Res_ThreeCode>().Where(x => x.IsDel == 0).ToListAsync();
  3378. //处理团组名称
  3379. var groupNames = await _sqlSugar.Queryable<Grp_DelegationInfo>().Where(x => groupIds.Contains(x.Id)).Select(x => new { Id = x.Id, GroupName = x.TeamName }).ToListAsync();
  3380. data.ForEach(x =>
  3381. {
  3382. string cityName = "";
  3383. if (!string.IsNullOrEmpty(x.FlightsCity))
  3384. {
  3385. string cityCode = x.FlightsCity.Replace("-", "").Replace("/", "");
  3386. int cityNum = cityCode.Length / 3;
  3387. if (cityCode.Length % 3 == 0)
  3388. {
  3389. for (int i = 0; i < cityNum; i++)
  3390. {
  3391. string code = "";
  3392. if (i == 0) code = cityCode.Substring(0, 3);
  3393. else code = cityCode.Substring(i * 3, 3);
  3394. if (!string.IsNullOrEmpty(code))
  3395. {
  3396. if (i == cityNum - 1)
  3397. {
  3398. cityName += threeCodeData.Find(x1 => x1.Three.Equals(code))?.City ?? "";
  3399. }
  3400. else
  3401. {
  3402. var str1 = threeCodeData.Find(x1 => x1.Three.Equals(code))?.City ?? "";
  3403. cityName += str1 + "、";
  3404. }
  3405. }
  3406. }
  3407. }
  3408. }
  3409. x.FlightsCityName = cityName;
  3410. });
  3411. var airTicketNumData = new List<AirTicketReturnView>();
  3412. var airTicketAreaData = new List<AirTicketCityReturnView>();
  3413. int monthIndex = 1;
  3414. while (monthIndex < 13)
  3415. {
  3416. var monthData = data.Where(x => x.Month == monthIndex).ToList();
  3417. //机票票数处理
  3418. int airticket_quantity = monthData.Sum(x => x.ClientNum);
  3419. var linkGroupIds = monthData.Select(x => x.DIId).ToList();
  3420. List<AitTicketInfo> aitTicketInfos = new List<AitTicketInfo>();
  3421. foreach (var item in monthData)
  3422. {
  3423. string groupName = groupNames.Find(x => item.DIId == x.Id)?.GroupName ?? "";
  3424. aitTicketInfos.Add(new AitTicketInfo(item.DIId, groupName, item.ClientNum));
  3425. }
  3426. var aitTicketInfosGroupBy = aitTicketInfos.GroupBy(x => x.Id);
  3427. List<AitTicketInfo> aitTicketInfos1 = new List<AitTicketInfo>();
  3428. foreach (var item in aitTicketInfosGroupBy)
  3429. {
  3430. aitTicketInfos1.Add(new AitTicketInfo(item.Key, item.First().GroupName, item.Sum(x => x.Quantity)));
  3431. }
  3432. aitTicketInfos1 = aitTicketInfos1.OrderBy(x => x.Quantity).ToList();
  3433. linkGroupIds = linkGroupIds.Distinct().ToList();
  3434. airTicketNumData.Add(new AirTicketReturnView(_dto.Year, monthIndex, airticket_quantity, aitTicketInfos1, linkGroupIds));
  3435. //机票城市处理
  3436. //城市处理
  3437. var airTicketCityInfos = new List<string>();
  3438. foreach (var item in monthData)
  3439. {
  3440. if (!string.IsNullOrEmpty(item.FlightsCityName))
  3441. {
  3442. if (item.FlightsCityName.Contains("、"))
  3443. {
  3444. var cityArray = item.FlightsCityName.Split("、").ToList();
  3445. foreach (var item1 in cityArray)
  3446. {
  3447. if (!string.IsNullOrEmpty(item1))
  3448. {
  3449. airTicketCityInfos.Add(item1);
  3450. }
  3451. }
  3452. }
  3453. else
  3454. {
  3455. airTicketCityInfos.Add(item.FlightsCityName);
  3456. }
  3457. }
  3458. }
  3459. //int quantity1 = airTicketCityInfos.Count;
  3460. int city_totalquantity = 0;
  3461. var airTicketCityInfosGroupby = airTicketCityInfos.GroupBy(x => x);
  3462. var airTicketCityInfos1 = new List<AirTicketCityInfo>();
  3463. foreach (var item in airTicketCityInfosGroupby)
  3464. {
  3465. var city_quantity = item.ToList().Count;
  3466. city_totalquantity += city_quantity;
  3467. airTicketCityInfos1.Add(new AirTicketCityInfo(item.Key, city_quantity));
  3468. }
  3469. airTicketCityInfos1 = airTicketCityInfos1.OrderByDescending(x => x.Quantity).ToList();
  3470. airTicketAreaData.Add(new AirTicketCityReturnView(_dto.Year, monthIndex, city_totalquantity, airTicketCityInfos1, linkGroupIds));
  3471. monthIndex++;
  3472. }
  3473. var airTicketPlatformData = data.GroupBy(g => g.Platform)
  3474. .Select(g => new
  3475. {
  3476. platform = g.Key,
  3477. total = g.Sum(x => x.ClientNum),
  3478. children = g.GroupBy(g1 => g1.DIId)
  3479. .Select(g1 => new {
  3480. diId = g1.Key,
  3481. groupName = g1.FirstOrDefault()?.GroupName ?? "-",
  3482. total = g1.Sum(x1 => x1.ClientNum),
  3483. children = g1.Select(x1 => new {
  3484. //diId = x1.DIId,
  3485. //groupName = x1.GroupName,
  3486. flightsCityName=x1.FlightsCityName,
  3487. total = x1.ClientNum
  3488. })
  3489. .ToList()
  3490. })
  3491. .OrderByDescending(x1 => x1.total)
  3492. .ToList()
  3493. })
  3494. .OrderByDescending(x => x.total)
  3495. .ToList();
  3496. return Ok(JsonView(true, "操作成功!", new
  3497. {
  3498. airTicketNumData = airTicketNumData,
  3499. airTicketAreaData = airTicketAreaData,
  3500. airTicketPlatformData = airTicketPlatformData
  3501. }));
  3502. }
  3503. /// <summary>
  3504. /// 国交数据统计-酒店
  3505. /// 地区TOP10、预订平台
  3506. /// </summary>
  3507. /// <param name="_dto"></param>
  3508. /// <returns></returns>
  3509. [HttpPost("StatisticsHotel")]
  3510. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3511. public async Task<IActionResult> StatisticsHotel(YOYDto _dto)
  3512. {
  3513. if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));
  3514. string beginDt = $"{_dto.Year}-01-01 00:00:00",
  3515. endDt = $"{_dto.Year}-12-31 23:59:59";
  3516. string sql = string.Format(@"
  3517. SELECT
  3518. hr.DIId,
  3519. hr.City,
  3520. hr.HotelName,
  3521. sd.Name AS BookinSite,
  3522. hr.CheckInDate,
  3523. YEAR(hr.CheckInDate) AS YEAR,
  3524. MONTH(hr.CheckInDate) AS MONTH,
  3525. hr.CheckOutDate,
  3526. hr.SingleRoomCount,
  3527. hr.DoubleRoomCount,
  3528. hr.SuiteRoomCount,
  3529. hr.OtherRoomCount
  3530. FROM
  3531. Grp_HotelReservations hr
  3532. INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
  3533. AND ccp.IsDel = 0
  3534. AND CTable = 76
  3535. INNER JOIN Grp_DelegationInfo di ON hr.DiId = di.Id
  3536. LEFT JOIN Sys_SetData sd ON hr.ReservationsWebsite = sd.Id
  3537. WHERE
  3538. hr.IsDel = 0
  3539. AND di.TeamDid IN (38,39,40,1048)
  3540. AND ccp.IsPay = 1
  3541. AND (
  3542. hr.SingleRoomCount > 0
  3543. OR hr.DoubleRoomCount > 0
  3544. OR hr.SuiteRoomCount > 0
  3545. OR hr.OtherRoomCount > 0
  3546. )
  3547. AND CHARINDEX('早餐',hr.HotelName) = 0
  3548. AND CHARINDEX('晚餐',hr.HotelName) = 0
  3549. AND CHARINDEX('升级',hr.HotelName) = 0
  3550. AND CHARINDEX('饮料茶水费',hr.HotelName) = 0
  3551. AND CHARINDEX('城市税',hr.HotelName) = 0
  3552. AND CHARINDEX('退款',hr.HotelName) = 0
  3553. AND CHARINDEX('返现',hr.HotelName) = 0
  3554. AND CHARINDEX('会议室',hr.HotelName) = 0
  3555. AND CHARINDEX('迷你吧消费',hr.HotelName) = 0
  3556. AND CHARINDEX('运费',hr.HotelName) = 0
  3557. AND CHARINDEX('地接',hr.HotelName) = 0
  3558. AND CHARINDEX('损失',hr.HotelName) = 0
  3559. AND CHARINDEX('补差',hr.HotelName) = 0
  3560. AND hr.CheckInDate BETWEEN CONVERT(datetime,'{0}') AND CONVERT(datetime,'{1}')
  3561. ORDER BY
  3562. hr.CheckInDate
  3563. ", beginDt, endDt);
  3564. var data = await _sqlSugar.SqlQueryable<StatisticsHotelView>(sql).ToListAsync();
  3565. var hotelData = data.OrderBy(x => x.CityStr).ToList();
  3566. var hotelCityGroupByData = data.GroupBy(x => x.CityStr)
  3567. .Select(g => new
  3568. {
  3569. city = g.Key,
  3570. BookingRoomNum = g.Sum(x => x.RoomTotal),
  3571. hotelData = g.GroupBy(x => x.HotelName)
  3572. .Select(g1 => new
  3573. {
  3574. hotelName = g1.Key,
  3575. roomNights = g1.Sum(x => x.RoomNights),
  3576. roomTotal = g1.Sum(x => x.RoomTotal),
  3577. singleRoomCount = g1.Sum(x => x.SingleRoomCount),
  3578. doubleRoomCount = g1.Sum(x => x.DoubleRoomCount),
  3579. suiteRoomCount = g1.Sum(x => x.SuiteRoomCount),
  3580. otherRoomCount = g1.Sum(x => x.OtherRoomCount),
  3581. })
  3582. .ToList(),
  3583. linkGroupIds = g.Select(x => x.DIId).Distinct().ToList()
  3584. })
  3585. .OrderByDescending(x => x.BookingRoomNum)
  3586. .Take(10)
  3587. .ToList();
  3588. var hotelPlatformGroupByData = data.GroupBy(x => x.BookinSite)
  3589. .Select(g => new
  3590. {
  3591. Platform = g.Key,
  3592. BookingRoomNum = g.Sum(x => x.RoomTotal),
  3593. hotelData = g.GroupBy(x => x.HotelName)
  3594. .Select(g1 => new
  3595. {
  3596. hotelName = g1.Key,
  3597. roomNights = g1.Sum(x => x.RoomNights),
  3598. roomTotal = g1.Sum(x => x.RoomTotal),
  3599. singleRoomCount = g1.Sum(x => x.SingleRoomCount),
  3600. doubleRoomCount = g1.Sum(x => x.DoubleRoomCount),
  3601. suiteRoomCount = g1.Sum(x => x.SuiteRoomCount),
  3602. otherRoomCount = g1.Sum(x => x.OtherRoomCount),
  3603. })
  3604. .ToList(),
  3605. linkGroupIds = g.Select(x => x.DIId).Distinct().ToList()
  3606. })
  3607. .OrderByDescending(x => x.BookingRoomNum)
  3608. .ToList();
  3609. return Ok(JsonView(true, "操作成功!", new { hotelCityData = hotelCityGroupByData , hotelPlatformData = hotelPlatformGroupByData }));
  3610. }
  3611. /// <summary>
  3612. /// 国交数据统计
  3613. /// 商邀邀请国家数量TOP10
  3614. /// </summary>
  3615. /// <param name="_dto"></param>
  3616. /// <returns></returns>
  3617. [HttpPost("StatisticsInvitation")]
  3618. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3619. public async Task<IActionResult> StatisticsInvitation(YOYDto _dto)
  3620. {
  3621. if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));
  3622. string beginDt = $"{_dto.Year}-01-01 00:00:00",
  3623. endDt = $"{_dto.Year}-12-31 23:59:59";
  3624. string sql = string.Format(@"
  3625. SELECT
  3626. ioa.DiId,
  3627. ioa.InviterArea AS Country
  3628. FROM
  3629. Grp_InvitationOfficialActivities ioa
  3630. INNER JOIN Grp_CreditCardPayment ccp ON ioa.Id = ccp.CId
  3631. AND ccp.IsDel = 0
  3632. AND ccp.CTable = 81
  3633. INNER JOIN Grp_DelegationInfo di ON ioa.DiId = di.Id
  3634. WHERE
  3635. ioa.IsDel = 0
  3636. AND di.TeamDid IN (38,39,40,1048)
  3637. AND ccp.IsPay = 1
  3638. AND ioa.CreateTime BETWEEN '{0}' AND '{1}'
  3639. ", beginDt, endDt);
  3640. var data = await _sqlSugar.SqlQueryable<StatisticsInvitation>(sql).ToListAsync();
  3641. var groupByData = data.GroupBy(x => x.Country)
  3642. .Select(g => new
  3643. {
  3644. Country = g.Key,
  3645. TimeNum = g.Count(),
  3646. LinkGroupIds = g.Select(x => x.DIId).ToList()
  3647. })
  3648. .OrderByDescending(x => x.TimeNum)
  3649. .Take(10)
  3650. .ToList();
  3651. return Ok(JsonView(true, "操作成功!", groupByData));
  3652. }
  3653. /// <summary>
  3654. /// 会务数据统计
  3655. /// 会务城市TOP10排序
  3656. /// </summary>
  3657. /// <param name="_dto"></param>
  3658. /// <returns></returns>
  3659. [HttpPost("StatisticsConferenceCity")]
  3660. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3661. public async Task<IActionResult> StatisticsConferenceCity(YOYDto _dto)
  3662. {
  3663. if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));
  3664. string beginDt = $"{_dto.Year}-01-01 00:00:00",
  3665. endDt = $"{_dto.Year}-12-31 23:59:59";
  3666. string sql = string.Format(@"
  3667. SELECT
  3668. ioa.DiId,
  3669. ioa.InviterArea AS Country
  3670. FROM
  3671. Grp_InvitationOfficialActivities ioa
  3672. INNER JOIN Grp_CreditCardPayment ccp ON ioa.Id = ccp.CId
  3673. AND ccp.IsDel = 0
  3674. AND ccp.CTable = 81
  3675. INNER JOIN Grp_DelegationInfo di ON ioa.DiId = di.Id
  3676. WHERE
  3677. ioa.IsDel = 0
  3678. AND di.TeamDid IN (102,248,302,691,762,1047)
  3679. AND ccp.IsPay = 1
  3680. AND ioa.CreateTime BETWEEN '{0}' AND '{1}'
  3681. ", beginDt, endDt);
  3682. var data = await _sqlSugar.SqlQueryable<StatisticsInvitation>(sql).ToListAsync();
  3683. var groupByData = data.GroupBy(x => x.Country)
  3684. .Select(g => new
  3685. {
  3686. Country = g.Key,
  3687. TimeNum = g.Count(),
  3688. LinkGroupIds = g.Select(x => x.DIId).ToList()
  3689. })
  3690. .OrderByDescending(x => x.TimeNum)
  3691. .Take(10)
  3692. .ToList();
  3693. return Ok(JsonView(true, "操作成功!", groupByData));
  3694. }
  3695. /// <summary>
  3696. /// 团组数据统计
  3697. /// 团组合作前十的客户TOP10排序
  3698. /// </summary>
  3699. /// <param name="_dto"></param>
  3700. /// <returns></returns>
  3701. [HttpPost("StatisticsCooperativeCustomer")]
  3702. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3703. public async Task<IActionResult> StatisticsCooperativeCustomer(YOYDto _dto)
  3704. {
  3705. if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));
  3706. string beginDt = $"{_dto.Year}-01-01 00:00:00",
  3707. endDt = $"{_dto.Year}-12-31 23:59:59";
  3708. DateTime _beginDt = Convert.ToDateTime(beginDt),
  3709. _endDt = Convert.ToDateTime(endDt);
  3710. var groupInfos = await _sqlSugar.Queryable<Grp_DelegationInfo>()
  3711. .Where(x => x.IsDel == 0)
  3712. //.Where(x => x.IsSure == 1)
  3713. .Where(x => x.VisitDate >= _beginDt && x.VisitDate <= _endDt)
  3714. .ToListAsync();
  3715. var groupInfos1 = groupInfos.GroupBy(x => x.ClientName);
  3716. var view = groupInfos1.Select(x =>
  3717. new
  3718. {
  3719. clienName = x.Key,
  3720. clientUnit = x.FirstOrDefault()?.ClientUnit ?? "",
  3721. visitsNum = x.Count()
  3722. }
  3723. )
  3724. .OrderByDescending(x => x.visitsNum)
  3725. .Take(10)
  3726. .ToList();
  3727. return Ok(JsonView(true, "操作成功!", view));
  3728. }
  3729. /// <summary>
  3730. /// 团组数据统计
  3731. /// 已出团客户单位的类型比例图(饼状图-政府团、企业团等)
  3732. /// </summary>
  3733. /// <param name="_dto"></param>
  3734. /// <returns></returns>
  3735. [HttpPost("StatisticsCooperativeCustomerType")]
  3736. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3737. public async Task<IActionResult> StatisticsCooperativeCustomerType(YOYDto _dto)
  3738. {
  3739. if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));
  3740. string beginDt = $"{_dto.Year}-01-01 00:00:00",
  3741. endDt = $"{_dto.Year}-12-31 23:59:59";
  3742. DateTime _beginDt = Convert.ToDateTime(beginDt),
  3743. _endDt = Convert.ToDateTime(endDt);
  3744. var groupInfos = await _sqlSugar.Queryable<Grp_DelegationInfo>()
  3745. .LeftJoin<Sys_SetData>((di, sd) => di.TeamDid == sd.Id)
  3746. .Where((di, sd) => di.IsDel == 0)
  3747. .Where((di, sd) => di.VisitDate >= _beginDt && di.VisitDate <= _endDt)
  3748. .Select((di, sd) => new
  3749. {
  3750. Id = di.Id,
  3751. GroupTypeId = di.TeamDid,
  3752. GroupTypeName = sd.Name,
  3753. })
  3754. .ToListAsync();
  3755. var groupInfos1 = groupInfos.GroupBy(x => x.GroupTypeName);
  3756. var view = groupInfos1.Select(x =>
  3757. new
  3758. {
  3759. groupTypeName = x.Key,
  3760. groupNum = x.Count(),
  3761. linkGroupId = x.Select(x1 => x1.Id).ToList()
  3762. }
  3763. )
  3764. .OrderByDescending(x => x.groupNum)
  3765. .ToList();
  3766. return Ok(JsonView(true, "操作成功!", view));
  3767. }
  3768. /// <summary>
  3769. /// 团组数据统计
  3770. /// 团组数量
  3771. /// </summary>
  3772. /// <param name="_dto"></param>
  3773. /// <returns></returns>
  3774. [HttpPost("StatisticsGroupNum")]
  3775. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3776. public async Task<IActionResult> StatisticsGroupNum(YOYDto _dto)
  3777. {
  3778. if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));
  3779. string beginDt = $"{_dto.Year}-01-01 00:00:00",
  3780. endDt = $"{_dto.Year}-12-31 23:59:59";
  3781. DateTime _beginDt = Convert.ToDateTime(beginDt),
  3782. _endDt = Convert.ToDateTime(endDt);
  3783. var groupInfos = await _sqlSugar.Queryable<Grp_DelegationInfo>()
  3784. .Where(x => x.IsDel == 0)
  3785. .Where(x => x.VisitDate >= _beginDt && x.VisitDate <= _endDt)
  3786. .Select(x => new StatisticsGroupInfoEntity
  3787. {
  3788. Id = x.Id,
  3789. VisitDate = x.VisitDate,
  3790. Month = x.VisitDate.Month,
  3791. //MonthName = x.VisitDate.ToString("MMM", CultureInfo.GetCultureInfo("zh-CN")),
  3792. VisitPNumber = x.VisitPNumber
  3793. })
  3794. .MergeTable()
  3795. .OrderBy(x => new { x.Month })
  3796. .ToListAsync();
  3797. string groupNumStr = $"年度出访量:{groupInfos.Count}\r\n";
  3798. var groupNumYearData = new
  3799. {
  3800. year = _dto.Year,
  3801. yearName = _dto.Year + "年",
  3802. num = groupInfos.Count,
  3803. linkGroupIds = groupInfos.Select(x => x.Id).ToList()
  3804. };
  3805. var groupNumData = groupInfos.GroupBy(x => x.Quarter)
  3806. .Select(x => new
  3807. {
  3808. Quarter = x.Key,
  3809. QuarterName = x.FirstOrDefault()?.QuarterName ?? "-",
  3810. Num = x.Count(),
  3811. LinkGroupIds = x.Select(x1 => x1.Id).ToList()
  3812. })
  3813. .OrderBy(x => x.Quarter)
  3814. .ToList();
  3815. groupNumData.ForEach(x =>
  3816. {
  3817. groupNumStr += $"{x.QuarterName}出访量:{x.Num};";
  3818. });
  3819. var groupNumMonthData = groupInfos.GroupBy(x => x.Month)
  3820. .Select(x => new
  3821. {
  3822. Month = x.Key,
  3823. MonthName = x.FirstOrDefault()?.MonthName ?? "-",
  3824. Num = x.Count(),
  3825. LinkGroupIds = x.Select(x1 => x1.Id).ToList()
  3826. })
  3827. .OrderBy(x => x.Month)
  3828. .ToList();
  3829. return Ok(JsonView(true, "操作成功!", new
  3830. {
  3831. yearData = groupNumYearData,
  3832. quarterData = groupNumData,
  3833. monthData = groupNumMonthData,
  3834. remark = groupNumStr
  3835. }));
  3836. }
  3837. /// <summary>
  3838. /// 团组数据统计
  3839. /// 团组人数
  3840. /// </summary>
  3841. /// <param name="_dto"></param>
  3842. /// <returns></returns>
  3843. [HttpPost("StatisticsGroupPeopleNum")]
  3844. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3845. public async Task<IActionResult> StatisticsGroupPeopleNum(YOYDto _dto)
  3846. {
  3847. if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));
  3848. string beginDt = $"{_dto.Year}-01-01 00:00:00",
  3849. endDt = $"{_dto.Year}-12-31 23:59:59";
  3850. DateTime _beginDt = Convert.ToDateTime(beginDt),
  3851. _endDt = Convert.ToDateTime(endDt);
  3852. var groupInfos = await _sqlSugar.Queryable<Grp_DelegationInfo>()
  3853. .Where(x => x.IsDel == 0)
  3854. .Where(x => x.VisitDate >= _beginDt && x.VisitDate <= _endDt)
  3855. .Select(x => new StatisticsGroupInfoEntity
  3856. {
  3857. Id = x.Id,
  3858. VisitDate = x.VisitDate,
  3859. Month = x.VisitDate.Month,
  3860. VisitPNumber = x.VisitPNumber
  3861. })
  3862. .MergeTable()
  3863. .OrderBy(x => new { x.Month })
  3864. .ToListAsync();
  3865. string groupPeopleNumStr = $"年度出访人数:{groupInfos.Sum(x => x.VisitPNumber)}\r\n";
  3866. var groupPeopleNumYearData = new
  3867. {
  3868. year = _dto.Year,
  3869. yearName = _dto.Year + "年",
  3870. num = groupInfos.Sum(x => x.VisitPNumber),
  3871. linkGroupIds = groupInfos.Select(x => x.Id).ToList()
  3872. };
  3873. var groupPeopleNumData = groupInfos.GroupBy(x => x.Quarter)
  3874. .Select(x => new
  3875. {
  3876. Quarter = x.Key,
  3877. QuarterName = x.FirstOrDefault()?.QuarterName ?? "-",
  3878. Num = x.Sum(x1 => x1.VisitPNumber),
  3879. LinkGroupIds = x.Select(x1 => x1.Id).ToList()
  3880. })
  3881. .OrderBy(x => x.Quarter)
  3882. .ToList();
  3883. groupPeopleNumData.ForEach(x =>
  3884. {
  3885. groupPeopleNumStr += $"{x.QuarterName}出访人数:{x.Num};";
  3886. });
  3887. var groupPeopleNumMonthData = groupInfos.GroupBy(x => x.Month)
  3888. .Select(x => new
  3889. {
  3890. Month = x.Key,
  3891. MonthName = x.FirstOrDefault()?.MonthName ?? "-",
  3892. Num = x.Sum(x1 => x1.VisitPNumber),
  3893. LinkGroupIds = x.Select(x1 => x1.Id).ToList()
  3894. })
  3895. .OrderBy(x => x.Month)
  3896. .ToList();
  3897. return Ok(JsonView(true, "操作成功!", new
  3898. {
  3899. yearData = groupPeopleNumYearData,
  3900. quarterData = groupPeopleNumData,
  3901. monthData = groupPeopleNumMonthData,
  3902. remark = groupPeopleNumStr
  3903. }));
  3904. }
  3905. /// <summary>
  3906. /// 团组数据统计
  3907. /// OP 成本
  3908. /// </summary>
  3909. /// <param name="_dto"></param>
  3910. /// <returns></returns>
  3911. [HttpPost("StatisticsOP")]
  3912. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3913. public async Task<IActionResult> StatisticsOP(StatisticsOPDto _dto)
  3914. {
  3915. if (_dto.Year < 1) return Ok(JsonView(false, "Year参数错误!"));
  3916. if (_dto.Type < 1 || _dto.Type > 2) return Ok(JsonView(false, "Type参数错误!"));
  3917. int thisYear = _dto.Year, lastYear = _dto.Year - 1;
  3918. string beginDt = $"{lastYear}-01-01 00:00:00",
  3919. endDt = $"{thisYear}-12-31 23:59:59";
  3920. string sql = string.Format(@"
  3921. SELECT
  3922. temp.Id,
  3923. DiId,
  3924. PaymentTime,
  3925. [Year],
  3926. [Quarter],
  3927. [Month],
  3928. Area,
  3929. FeeType,
  3930. sd1.Name AS FeeTypeName,
  3931. FeeSubType,
  3932. sd2.Name AS FeeSubTypeName,
  3933. CONVERT(DECIMAL(12, 2), Price * Qauntity * ExchangeRate) AS Price
  3934. FROM
  3935. (
  3936. SELECT
  3937. ctggr.Id,
  3938. ctggr.DiId,
  3939. ctggr.Area,
  3940. CASE
  3941. WHEN ctggr.PriceType IS NULL THEN 1069
  3942. ELSE ctggr.PriceType
  3943. END AS FeeType,
  3944. ctggrc.SId AS FeeSubType,
  3945. ctggrc.Price,
  3946. CASE
  3947. WHEN ctggrc.Count IS NULL THEN 1
  3948. ELSE ctggrc.Count
  3949. END AS Qauntity,
  3950. ccp.DayRate AS ExchangeRate,
  3951. ctggrc.CreateTime AS PaymentTime,
  3952. YEAR(ctggrc.CreateTime) AS [Year],
  3953. CONVERT(INT,DATENAME(QUARTER, ctggrc.CreateTime)) AS [Quarter],
  3954. MONTH(ctggrc.CreateTime) AS [Month]
  3955. FROM
  3956. Grp_CarTouristGuideGroundReservations ctggr
  3957. INNER JOIN Grp_CarTouristGuideGroundReservationsContent ctggrc ON ctggr.Id = ctggrc.CTGGRId
  3958. AND ctggrc.IsDel = 0
  3959. INNER JOIN Grp_CreditCardPayment ccp ON ctggr.Id = ccp.CId
  3960. AND ccp.CTable = 79
  3961. AND ccp.IsDel = 0
  3962. WHERE
  3963. ctggr.IsDel = 0
  3964. AND ctggrc.SId != 1070 --费用子项筛掉尾款
  3965. AND ctggrc.Price > 0 --筛选真实存在的数据
  3966. AND ccp.IsPay = 1
  3967. ) temp
  3968. LEFT JOIN Sys_SetData sd1 ON FeeType = sd1.Id
  3969. LEFT JOIN Sys_SetData sd2 ON FeeSubType = sd2.Id
  3970. WHERE
  3971. temp.Area NOT LIKE '%尾款%' -- 筛选掉尾款相关信息
  3972. AND temp.FeeType != 1062 --费用类型筛选掉全款和首付款数据
  3973. AND temp.PaymentTime BETWEEN CONVERT(datetime,'{0}') AND CONVERT(datetime,'{1}')
  3974. ORDER BY
  3975. temp.[Year],
  3976. temp.[Quarter],
  3977. temp.[Month]
  3978. ", beginDt, endDt);
  3979. var opInfos = await _sqlSugar.SqlQueryable<StatisticsOP>(sql).ToListAsync();
  3980. //处理地区相关信息
  3981. var countrys = await _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(x => x.IsDel == 0).ToListAsync();
  3982. for (int i = 0; i < opInfos.Count; i++)
  3983. {
  3984. string country = string.Empty, city = string.Empty;
  3985. int dataId = 0;
  3986. bool isCountryId = int.TryParse(opInfos[i].Area, out dataId);
  3987. string[] citySelecter = new string[] { "其他城市", "所有城市" };
  3988. if (isCountryId)
  3989. {
  3990. var countryInfo = countrys.Find(x => x.Id == dataId);
  3991. country = countryInfo?.Country ?? "";
  3992. if (citySelecter.Contains(countryInfo?.City)) city = country;
  3993. else city = countryInfo?.City ?? "";
  3994. }
  3995. else
  3996. {
  3997. var countryInfo1 = countrys.Find(x => opInfos[i].Area.Contains(x.Country));
  3998. if (countryInfo1 != null)
  3999. {
  4000. country = countryInfo1?.Country ?? "";
  4001. city = countryInfo1?.City ?? country;
  4002. var countryInfo2 = countrys.Find(x => opInfos[i].Area.Contains(x.City));
  4003. if (citySelecter.Contains(countryInfo2?.City)) city = country;
  4004. else city = countryInfo2?.City ?? country;
  4005. }
  4006. else
  4007. {
  4008. var countryInfo2 = countrys.Find(x => opInfos[i].Area.Contains(x.City));
  4009. if (countryInfo2 != null)
  4010. {
  4011. country = countryInfo2?.Country ?? "";
  4012. city = countryInfo2?.City ?? "";
  4013. if (citySelecter.Contains(countryInfo2?.City)) city = country;
  4014. }
  4015. }
  4016. }
  4017. opInfos[i].Country = country;
  4018. opInfos[i].City = city;
  4019. }
  4020. var opParentFeeData = new StatisticsOP()._OPParentFeeInfo;
  4021. if (_dto.Type == 1)
  4022. {
  4023. //同比增长率=(本期数-同期数)/ 同期数
  4024. var yearData = opInfos.GroupBy(x => x.ParentFeeName)
  4025. .Select(x => new StatisticsOPYOY(
  4026. feeId: x.FirstOrDefault()?.ParentFeeId ?? 0,
  4027. feeName: x.Key,
  4028. currPeriodFee: x.Where(x => x.Year == thisYear).Sum(x => x.Price),
  4029. samePeriodFee: x.Where(x => x.Year == lastYear).Sum(x => x.Price),
  4030. currPeriodGroupTotal: x.Where(x => x.Year == thisYear).Select(x => x.DiId).Distinct().Count(),
  4031. samePeriodGroupTotal: x.Where(x => x.Year == lastYear).Select(x => x.DiId).Distinct().Count(),
  4032. subFeeData: x.GroupBy(x1 => x1.FeeSubType)
  4033. .Select(x1 => new StatisticsOPSubFeeYOY(
  4034. feeId: x1.Key,
  4035. feeName: x1.FirstOrDefault()?.FeeSubTypeName ?? "",
  4036. currPeriodFee: x1.Where(x1 => x1.Year == thisYear).Sum(x1 => x1.Price),
  4037. samePeriodFee: x1.Where(x1 => x1.Year == lastYear).Sum(x1 => x1.Price),
  4038. currPeriodGroupTotal: x1.Where(x1 => x1.Year == thisYear).Select(x1 => x1.DiId).Distinct().Count(),
  4039. samePeriodGroupTotal: x1.Where(x1 => x1.Year == lastYear).Select(x1 => x1.DiId).Distinct().Count(),
  4040. cityData: x1.GroupBy(x2 => x2.City)
  4041. .Select(x2 => new StatisticsOPCityYOY(
  4042. cityName: x2.Key,
  4043. currPeriodGroupTotal: x2.Where(x2 => x2.Year == thisYear).Select(x2 => x2.DiId).Distinct().Count(),
  4044. samePeriodGroupTotal: x2.Where(x2 => x2.Year == lastYear).Select(x2 => x2.DiId).Distinct().Count(),
  4045. currPeriodFee: x2.Where(x2 => x2.Year == thisYear).Sum(x2 => x2.Price),
  4046. samePeriodFee: x2.Where(x2 => x2.Year == lastYear).Sum(x2 => x2.Price)
  4047. ))
  4048. .ToArray()
  4049. ))
  4050. .ToArray()
  4051. ))
  4052. .OrderBy(x => x.FeeId)
  4053. .ToList();
  4054. var opParentYearIds = yearData.Select(x => x.FeeId).ToList();
  4055. var opParentYearDataSelector = opParentFeeData.Where(x => !opParentYearIds.Contains(x.Id)).ToList();
  4056. if (opParentYearDataSelector.Count > 0)
  4057. {
  4058. foreach (var item in opParentYearDataSelector)
  4059. {
  4060. yearData.Add(
  4061. new StatisticsOPYOY(feeId: item.Id,
  4062. feeName: item.Name,
  4063. currPeriodFee: 0.00M,
  4064. samePeriodFee: 0.00M,
  4065. currPeriodGroupTotal: 0,
  4066. samePeriodGroupTotal: 0,
  4067. subFeeData: new StatisticsOPSubFeeYOY[] { }
  4068. ));
  4069. }
  4070. yearData = yearData.OrderBy(x => x.FeeId).ToList();
  4071. }
  4072. return Ok(JsonView(true, "操作成功!", yearData));
  4073. }
  4074. else if (_dto.Type == 2)
  4075. {
  4076. var monthData = opInfos.GroupBy(x => x.Month)
  4077. .Select(x => new StatisticsOPMonthYOY(
  4078. monthId: x.Key,
  4079. monthName: Enum.IsDefined(typeof(MonthEnum), (int)x.Key) ? ((MonthEnum)(int)x.Key).GetEnumDescription() : "",
  4080. feeDatas: x.GroupBy(x1 => x1.ParentFeeId)
  4081. .Select(x1 => new StatisticsOPYOY(
  4082. feeId: x1.Key,
  4083. feeName: x1.FirstOrDefault()?.ParentFeeName ?? "",
  4084. currPeriodFee: x1.Where(x1 => x1.Year == thisYear).Sum(x1 => x1.Price),
  4085. samePeriodFee: x1.Where(x1 => x1.Year == lastYear).Sum(x1 => x1.Price),
  4086. currPeriodGroupTotal: x1.Where(x1 => x1.Year == thisYear).Select(x1 => x1.DiId).Distinct().Count(),
  4087. samePeriodGroupTotal: x1.Where(x1 => x1.Year == lastYear).Select(x1 => x1.DiId).Distinct().Count(),
  4088. subFeeData: x1.GroupBy(x2 => x2.FeeSubType)
  4089. .Select(x2 => new StatisticsOPSubFeeYOY(
  4090. feeId: x2.Key,
  4091. feeName: x2.FirstOrDefault()?.FeeSubTypeName ?? "",
  4092. currPeriodFee: x2.Where(x2 => x2.Year == thisYear).Sum(x2 => x2.Price),
  4093. samePeriodFee: x2.Where(x2 => x2.Year == lastYear).Sum(x2 => x2.Price),
  4094. currPeriodGroupTotal: x2.Where(x2 => x2.Year == thisYear).Select(x2 => x2.DiId).Distinct().Count(),
  4095. samePeriodGroupTotal: x2.Where(x2 => x2.Year == lastYear).Select(x2 => x2.DiId).Distinct().Count(),
  4096. cityData: x2.GroupBy(x3 => x3.City)
  4097. .Select(x3 => new StatisticsOPCityYOY(
  4098. cityName: x3.Key,
  4099. currPeriodFee: x3.Where(x3 => x3.Year == thisYear).Sum(x3 => x3.Price),
  4100. samePeriodFee: x3.Where(x3 => x3.Year == lastYear).Sum(x3 => x3.Price),
  4101. currPeriodGroupTotal: x3.Where(x3 => x3.Year == thisYear).Select(x3 => x3.DiId).Distinct().Count(),
  4102. samePeriodGroupTotal: x3.Where(x3 => x3.Year == lastYear).Select(x3 => x3.DiId).Distinct().Count()
  4103. ))
  4104. .ToArray()
  4105. ))
  4106. .ToArray()
  4107. ))
  4108. .ToArray()
  4109. ))
  4110. //.OrderBy(x => x.MonthId)
  4111. .ToList();
  4112. //1、月份是否足够十二月
  4113. var monthIds = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
  4114. var monthDataIds = monthData.Select(x => x.MonthId).ToArray();
  4115. var newMonthIds = monthIds.Where(x => !monthDataIds.Contains(x)).ToList();
  4116. if (newMonthIds.Count > 0)
  4117. {
  4118. foreach (var item in newMonthIds)
  4119. {
  4120. monthData.Add(new StatisticsOPMonthYOY(
  4121. monthId: item,
  4122. monthName: Enum.IsDefined(typeof(MonthEnum), (int)item) ? ((MonthEnum)(int)item).GetEnumDescription() : "",
  4123. feeDatas: Array.Empty<StatisticsOPYOY>()
  4124. ));
  4125. }
  4126. monthData = monthData.OrderBy(x => x.MonthId).ToList();
  4127. }
  4128. //2、费用父类是否足够
  4129. for (int i = 0; i < monthData.Count; i++)
  4130. {
  4131. var month = monthData[i];
  4132. var opParentFeeIds = month.FeeDatas.Select(x => x.FeeId).ToList();
  4133. var opParentFeeDataSelector = opParentFeeData.Where(x => !opParentFeeIds.Contains(x.Id)).ToList();
  4134. if (opParentFeeDataSelector.Count > 0)
  4135. {
  4136. var feeDatas = month.FeeDatas.ToList();
  4137. foreach (var item in opParentFeeDataSelector)
  4138. {
  4139. feeDatas.Add(new StatisticsOPYOY(
  4140. feeId: item.Id,
  4141. feeName: item.Name,
  4142. currPeriodFee: 0.00M,
  4143. samePeriodFee: 0.00M,
  4144. currPeriodGroupTotal: 0,
  4145. samePeriodGroupTotal: 0,
  4146. subFeeData: Array.Empty<StatisticsOPSubFeeYOY>()
  4147. ));
  4148. }
  4149. feeDatas = feeDatas.OrderBy(x => x.FeeId).ToList();
  4150. monthData[i].FeeDatas = feeDatas.ToArray();
  4151. }
  4152. }
  4153. //3、费用子类是否足够
  4154. return Ok(JsonView(true, "操作成功!", monthData));
  4155. }
  4156. return Ok(JsonView(false, "操作失败!"));
  4157. }
  4158. /// <summary>
  4159. /// 团组数据统计
  4160. /// 团组预算/实际成本
  4161. /// </summary>
  4162. /// <param name="_dto"></param>
  4163. /// <returns></returns>
  4164. [HttpPost("StatisticsGroupCost")]
  4165. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  4166. public async Task<IActionResult> StatisticsBRCost(StatisticsBRCostDto _dto)
  4167. {
  4168. if (_dto.Year < 1) return Ok(JsonView(false, "Year参数错误!"));
  4169. string beginDt = $"{_dto.Year}-01-01 00:00:00",
  4170. endDt = $"{_dto.Year}-12-31 23:59:59";
  4171. var sql = string.Format(@"
  4172. SELECT
  4173. ROW_NUMBER() OVER(
  4174. ORDER BY
  4175. di.VisitDate DESC
  4176. ) AS 'RowNumber',
  4177. di.Id,
  4178. di.TeamName AS 'GroupName',
  4179. di.ClientUnit,
  4180. di.VisitDate,
  4181. di.VisitDays,
  4182. di.VisitPNumber,
  4183. (
  4184. SELECT
  4185. TOP 1 CASE
  4186. ISNUMERIC (gcp.Currency)
  4187. WHEN 1 THEN (
  4188. SELECT
  4189. Name
  4190. FROM
  4191. Sys_SetData sd
  4192. WHERE
  4193. CAST(gcp.Currency AS INT) = sd.Id
  4194. )
  4195. ELSE gcp.Currency
  4196. END AS 'CurrencyCode'
  4197. FROM
  4198. Grp_GroupCostParameter gcp
  4199. WHERE
  4200. gcp.IsDel = 0
  4201. AND gcp.DiId = di.Id
  4202. ) Budget_CurrencyCode,
  4203. --预算币种
  4204. (
  4205. SELECT
  4206. TOP 1 CAST(COALESCE(gcp.Rate, 0.0000) AS DECIMAL(12, 4))
  4207. FROM
  4208. Grp_GroupCostParameter gcp
  4209. WHERE
  4210. gcp.IsDel = 0
  4211. AND gcp.DiId = di.Id
  4212. ) Budget_Rate,
  4213. --预算汇率
  4214. (
  4215. SELECT
  4216. CAST(
  4217. SUM(COALESCE(gcp.VisaCB, 0) * COALESCE(gcp.VisaRS, 0)) AS DECIMAL(12, 2)
  4218. )
  4219. FROM
  4220. Grp_GroupCostParameter gcp
  4221. WHERE
  4222. gcp.IsDel = 0
  4223. AND gcp.DiId = di.id
  4224. ) Budget_VisaAmount,
  4225. -- 预算签证成本
  4226. (
  4227. SELECT
  4228. CAST(
  4229. SUM(
  4230. (
  4231. (ccp.PayMoney * ccp.DayRate)
  4232. )
  4233. ) AS DECIMAL(12, 2)
  4234. )
  4235. FROM
  4236. Grp_VisaInfo vi
  4237. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  4238. AND ccp.CTable = 80
  4239. AND vi.Id = ccp.CId
  4240. WHERE
  4241. vi.IsDel = 0
  4242. AND ccp.IsAuditGM = 1
  4243. AND ccp.IsPay = 1
  4244. AND di.Id = vi.DIId
  4245. ) AS Reality_VisaAmount,
  4246. -- 实际签证成本
  4247. (
  4248. SELECT
  4249. CAST(
  4250. SUM(
  4251. COALESCE(gcp.JJCCB, 0) * COALESCE(gcp.JJCRS, 0) + COALESCE(gcp.GWCCB, 0) * COALESCE(gcp.GWCRS, 0) + COALESCE(gcp.TDCCB, 0) * COALESCE(gcp.TDCRS, 0)
  4252. ) AS DECIMAL(12, 2)
  4253. )
  4254. FROM
  4255. Grp_GroupCostParameter gcp
  4256. WHERE
  4257. gcp.IsDel = 0
  4258. AND gcp.DiId = di.id
  4259. ) Budget_AirTicketAmount,
  4260. -- 预算机票成本
  4261. (
  4262. SELECT
  4263. CAST(
  4264. SUM(
  4265. (
  4266. (ccp.PayMoney * ccp.DayRate)
  4267. )
  4268. ) AS DECIMAL(12, 2)
  4269. )
  4270. FROM
  4271. Grp_AirTicketReservations atr
  4272. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  4273. AND ccp.CTable = 85
  4274. AND atr.Id = ccp.CId
  4275. WHERE
  4276. atr.IsDel = 0
  4277. AND ccp.IsAuditGM = 1
  4278. AND ccp.IsPay = 1
  4279. AND di.Id = atr.DiId
  4280. ) Reality_AirTicketAmount,
  4281. --实际机票
  4282. (
  4283. SELECT
  4284. CAST(
  4285. SUM(
  4286. COALESCE(gcp.SGRCB, 0) * COALESCE(gcp.SGRNumber, 0) + COALESCE(gcp.TBRCB, 0) * COALESCE(gcp.TBRNumber, 0) + COALESCE(gcp.SUITECB, 0) * COALESCE(gcp.SUITENumber, 0) + COALESCE(gcp.SUITENumber, 0) * COALESCE(gcp.JSESXS, 0)
  4287. ) AS DECIMAL(12, 2)
  4288. )
  4289. FROM
  4290. Grp_GroupCostParameter gcp
  4291. WHERE
  4292. gcp.IsDel = 0
  4293. AND gcp.DiId = di.id
  4294. ) Budget_HotelAmount,
  4295. -- 预算酒店成本
  4296. (
  4297. SELECT
  4298. CAST(
  4299. SUM(
  4300. (ccp.PayMoney * ccp.DayRate)
  4301. ) AS DECIMAL(12, 2)
  4302. )
  4303. FROM
  4304. Grp_HotelReservations hr
  4305. INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
  4306. WHERE
  4307. hr.IsDel = 0
  4308. AND ccp.IsDel = 0
  4309. AND ccp.CTable = 76
  4310. AND ccp.IsAuditGM = 1
  4311. AND ccp.PayMoney <> 0
  4312. AND ccp.IsPay = 1
  4313. AND di.Id = hr.DiId
  4314. ) AS Reality_HotelAmount,
  4315. -- 实际酒店成本
  4316. (
  4317. SELECT
  4318. CAST(
  4319. SUM(COALESCE(gcp.DJCB, 0) * COALESCE(gcp.DJRS, 0)) AS DECIMAL(12, 2)
  4320. )
  4321. FROM
  4322. Grp_GroupCostParameter gcp
  4323. WHERE
  4324. gcp.IsDel = 0
  4325. AND gcp.DiId = di.id
  4326. ) Budget_OPAmount,
  4327. -- 预算 OP
  4328. (
  4329. SELECT
  4330. CAST(
  4331. SUM(
  4332. (
  4333. ctggr.ServiceQuotedPrice * (ccp.PayPercentage / 100) * ccp.DayRate
  4334. )
  4335. ) AS DECIMAL(12, 2)
  4336. )
  4337. FROM
  4338. Grp_CarTouristGuideGroundReservations ctggr
  4339. INNER JOIN Grp_CreditCardPayment ccp ON ccp.IsDel = 0
  4340. AND ccp.CTable = 79
  4341. AND ctggr.Id = ccp.CId
  4342. WHERE
  4343. ctggr.IsDel = 0
  4344. AND ccp.IsAuditGM = 1
  4345. AND ccp.IsPay = 1
  4346. AND di.Id = ctggr.DiId
  4347. ) AS Reality_OPAmount,
  4348. -- 实际 OP
  4349. (
  4350. SELECT
  4351. CAST(
  4352. SUM(COALESCE(gcp.GWCB, 0) * COALESCE(gcp.GWRS, 0)) AS DECIMAL(12, 2)
  4353. )
  4354. FROM
  4355. Grp_GroupCostParameter gcp
  4356. WHERE
  4357. gcp.IsDel = 0
  4358. AND gcp.DiId = di.id
  4359. ) Budget_OAAmount,
  4360. -- 预算 商邀
  4361. (
  4362. SELECT
  4363. CAST(
  4364. SUM(
  4365. (ccp.PayMoney * ccp.DayRate)
  4366. ) AS DECIMAL(12, 2)
  4367. )
  4368. FROM
  4369. Grp_InvitationOfficialActivities ioa
  4370. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  4371. AND ccp.CTable = 81
  4372. AND ioa.Id = ccp.CId
  4373. WHERE
  4374. ioa.IsDel = 0
  4375. AND ccp.IsAuditGM = 1
  4376. AND ccp.IsPay = 1
  4377. AND di.Id = ioa.Diid
  4378. ) AS Reality_OAAmount,
  4379. -- 实际 商邀
  4380. (
  4381. SELECT
  4382. CAST(
  4383. SUM(
  4384. COALESCE(gcp.BXCB, 0) * COALESCE(gcp.BXRS, 0) + COALESCE(gcp.HCPCB, 0) * COALESCE(gcp.HCPRS, 0) + COALESCE(gcp.CPCB, 0) * COALESCE(gcp.CPRS, 0) + COALESCE(gcp.HSCB, 0) * COALESCE(gcp.HSRS, 0) + COALESCE(gcp.LYJCB, 0) * COALESCE(gcp.LYJRS, 0)
  4385. ) AS DECIMAL(12, 2)
  4386. )
  4387. FROM
  4388. Grp_GroupCostParameter gcp
  4389. WHERE
  4390. gcp.IsDel = 0
  4391. AND gcp.DiId = di.id
  4392. ) Budget_OtherAmount,
  4393. -- 预算其他成本
  4394. (
  4395. SELECT
  4396. CAST(
  4397. SUM(
  4398. (ccp.PayMoney * ccp.DayRate)
  4399. ) AS DECIMAL(12, 2)
  4400. )
  4401. FROM
  4402. Grp_Customers ic
  4403. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  4404. AND ccp.CTable = 82
  4405. AND ic.Id = ccp.CId
  4406. WHERE
  4407. ic.IsDel = 0
  4408. AND ccp.IsAuditGM = 1And ccp.IsPay = 1
  4409. AND di.Id = ic.DiId
  4410. ) AS Reality_InsureAmount,
  4411. -- 实际保险成本
  4412. (
  4413. SELECT
  4414. CAST(SUM((ccp.PayMoney * ccp.DayRate)) AS DECIMAL(12, 2))
  4415. FROM
  4416. Grp_DecreasePayments dp
  4417. INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
  4418. AND ccp.CTable = 98
  4419. AND dp.Id = ccp.CId
  4420. WHERE
  4421. dp.IsDel = 0
  4422. AND ccp.Ctable = 98
  4423. AND ccp.IsAuditGM = 1
  4424. AND ccp.IsPay = 1
  4425. AND di.Id = dp.Diid
  4426. ) AS Reality_OtherCostAmount -- 实际其他款项
  4427. FROM
  4428. Grp_DelegationInfo di
  4429. WHERE
  4430. di.IsDel = 0
  4431. AND di.TeamDid IN (38, 39, 40, 1048)
  4432. AND di.VisitDate BETWEEN '{0}' AND '{1}'
  4433. ", beginDt, endDt);
  4434. if (!string.IsNullOrEmpty(_dto.Search))
  4435. {
  4436. sql += $"AND di.TeamName Like '%{_dto.Search}%'";
  4437. }
  4438. var view = await _sqlSugar.SqlQueryable<StatisticsBRCostView>(sql).ToListAsync();
  4439. view = view.OrderByDescending(x => x.Id).ToList();
  4440. var total = view.Count;
  4441. var toSkip = (_dto.PageIndex - 1) * _dto.PageSize;
  4442. var data = view.Skip(toSkip).Take(_dto.PageSize).ToList();
  4443. if (data.Count > 0)
  4444. {
  4445. var diIds = data.Select(x => x.Id).ToList();
  4446. var currencyData = await _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && x.STid == 66).ToListAsync();
  4447. var ccpData = await _sqlSugar.Queryable<Grp_CreditCardPayment>()
  4448. .Where(x => x.IsDel == 0 &&
  4449. diIds.Contains(x.DIId) &&
  4450. x.IsPay == 1 &&
  4451. x.IsAuditGM == 1
  4452. )
  4453. .ToListAsync();
  4454. for (int i = 0; i < data.Count; i++)
  4455. {
  4456. var info = data[i];
  4457. info.RowNumber = i + 1;
  4458. #region 1:签证 80
  4459. var visaDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 80).ToList();
  4460. var visaDesc = "-";
  4461. if (visaDatas.Count > 0)
  4462. {
  4463. visaDesc = $"实际币种金额:";
  4464. foreach (var visa in visaDatas)
  4465. {
  4466. var currencyCode = currencyData.Find(x => x.Id == visa.PaymentCurrency)?.Name;
  4467. visaDesc += $"{visa.PayMoney} {currencyCode} 汇率:{visa.DayRate.ToString("#0.0000")};";
  4468. }
  4469. }
  4470. info.Reality_VisaAmountDesc = visaDesc;
  4471. #endregion
  4472. #region 2:机票 85
  4473. var airDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 85).ToList();
  4474. var airDesc = "-";
  4475. if (airDatas.Count > 0)
  4476. {
  4477. airDesc = $"实际币种金额:";
  4478. foreach (var air in airDatas)
  4479. {
  4480. var currencyCode = currencyData.Find(x => x.Id == air.PaymentCurrency)?.Name;
  4481. airDesc += $"{air.PayMoney} {currencyCode} 汇率:{air.DayRate.ToString("#0.0000")};";
  4482. }
  4483. }
  4484. info.Reality_AirTicketAmountDesc = airDesc;
  4485. #endregion
  4486. #region 3:酒店 76
  4487. var hotelDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 76 && x.PayMoney != 0 ).ToList();
  4488. var hotelDesc = "-";
  4489. if (hotelDatas.Count > 0)
  4490. {
  4491. hotelDesc = $"实际币种金额:";
  4492. foreach (var hotel in hotelDatas)
  4493. {
  4494. var currencyCode = currencyData.Find(x => x.Id == hotel.PaymentCurrency)?.Name;
  4495. hotelDesc += $"{hotel.PayMoney} {currencyCode} 汇率:{hotel.DayRate.ToString("#0.0000")};";
  4496. }
  4497. }
  4498. info.Reality_HotelAmountDesc = hotelDesc;
  4499. #endregion
  4500. #region 4:OP 79
  4501. var opDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 79 && x.PayMoney != 0).ToList();
  4502. var opDesc = "-";
  4503. if (opDatas.Count > 0)
  4504. {
  4505. opDesc = $"实际币种金额;";
  4506. foreach (var op in opDatas)
  4507. {
  4508. var currencyCode = currencyData.Find(x => x.Id == op.PaymentCurrency)?.Name;
  4509. opDesc += $"{op.PayMoney} {currencyCode} 汇率:{op.DayRate.ToString("#0.0000")};";
  4510. }
  4511. }
  4512. info.Reality_OPAmountDesc = opDesc;
  4513. #endregion
  4514. #region 5:商邀 81
  4515. var oaDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 81 && x.PayMoney != 0).ToList();
  4516. var oaDesc = "-";
  4517. if (oaDatas.Count > 0)
  4518. {
  4519. oaDesc = $"实际币种金额:";
  4520. foreach (var oa in oaDatas)
  4521. {
  4522. var currencyCode = currencyData.Find(x => x.Id == oa.PaymentCurrency)?.Name;
  4523. oaDesc += $"{oa.PayMoney} {currencyCode} 汇率:{oa.DayRate.ToString("#0.0000")};";
  4524. }
  4525. }
  4526. info.Reality_OAAmountDesc = oaDesc;
  4527. #endregion
  4528. #region 6:其他款项(保险 82、其他款项 98)
  4529. var cTables = new List<int>() { 82, 98 };
  4530. var otherDatas = ccpData.Where(x => x.DIId == info.Id && cTables.Contains(x.CTable) && x.PayMoney != 0).ToList();
  4531. var otherDesc = "-";
  4532. if (otherDatas.Count > 0)
  4533. {
  4534. otherDesc = $"实际币种金额:";
  4535. foreach (var other in otherDatas)
  4536. {
  4537. var currencyCode = currencyData.Find(x => x.Id == other.PaymentCurrency)?.Name;
  4538. otherDesc += $"{other.PayMoney} {currencyCode} 汇率:{other.DayRate.ToString("#0.0000")};";
  4539. }
  4540. }
  4541. info.Reality_OtherCostAmountDesc = otherDesc;
  4542. #endregion
  4543. }
  4544. }
  4545. var briefData = _mapper.Map<List<StatisticsBRCostBriefView>>(data);
  4546. return Ok(JsonView(briefData, total));
  4547. }
  4548. #endregion
  4549. }
  4550. }