FinancialController.cs 221 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279328032813282328332843285328632873288328932903291329232933294329532963297329832993300330133023303330433053306330733083309331033113312331333143315331633173318331933203321332233233324332533263327332833293330333133323333333433353336333733383339334033413342334333443345334633473348334933503351335233533354335533563357335833593360336133623363336433653366336733683369337033713372337333743375337633773378337933803381338233833384338533863387338833893390339133923393339433953396339733983399340034013402340334043405340634073408340934103411341234133414341534163417341834193420342134223423342434253426342734283429343034313432343334343435343634373438343934403441344234433444344534463447344834493450345134523453345434553456345734583459346034613462346334643465346634673468346934703471347234733474347534763477347834793480348134823483348434853486348734883489349034913492349334943495349634973498349935003501350235033504350535063507350835093510351135123513351435153516351735183519352035213522352335243525352635273528352935303531353235333534353535363537353835393540354135423543354435453546354735483549355035513552355335543555355635573558355935603561356235633564356535663567356835693570357135723573357435753576357735783579358035813582358335843585358635873588358935903591359235933594359535963597359835993600360136023603360436053606360736083609361036113612361336143615361636173618361936203621362236233624362536263627362836293630363136323633363436353636363736383639364036413642364336443645364636473648364936503651365236533654365536563657365836593660366136623663366436653666366736683669367036713672367336743675367636773678367936803681368236833684368536863687368836893690369136923693369436953696369736983699370037013702370337043705370637073708370937103711371237133714371537163717371837193720372137223723372437253726372737283729373037313732373337343735373637373738373937403741374237433744374537463747374837493750375137523753375437553756375737583759376037613762376337643765376637673768376937703771377237733774377537763777377837793780378137823783378437853786378737883789379037913792379337943795379637973798379938003801380238033804380538063807380838093810381138123813381438153816381738183819382038213822382338243825382638273828382938303831383238333834383538363837383838393840384138423843384438453846384738483849385038513852385338543855385638573858385938603861386238633864386538663867386838693870387138723873387438753876387738783879388038813882388338843885388638873888388938903891389238933894389538963897389838993900390139023903390439053906390739083909391039113912391339143915391639173918391939203921392239233924392539263927392839293930393139323933393439353936393739383939394039413942394339443945394639473948394939503951395239533954395539563957395839593960396139623963396439653966396739683969397039713972397339743975397639773978397939803981398239833984398539863987398839893990399139923993399439953996399739983999400040014002400340044005400640074008400940104011401240134014401540164017401840194020402140224023402440254026402740284029403040314032403340344035403640374038403940404041404240434044404540464047404840494050405140524053405440554056405740584059406040614062406340644065406640674068406940704071407240734074407540764077407840794080408140824083408440854086408740884089409040914092409340944095409640974098409941004101410241034104410541064107410841094110411141124113411441154116411741184119412041214122412341244125412641274128412941304131413241334134413541364137413841394140414141424143414441454146414741484149415041514152415341544155415641574158415941604161416241634164416541664167416841694170417141724173417441754176417741784179418041814182418341844185418641874188418941904191419241934194419541964197419841994200420142024203420442054206420742084209421042114212421342144215421642174218421942204221422242234224422542264227422842294230423142324233423442354236423742384239424042414242424342444245424642474248424942504251425242534254425542564257425842594260426142624263426442654266426742684269427042714272427342744275427642774278427942804281428242834284428542864287428842894290429142924293429442954296429742984299430043014302430343044305430643074308430943104311431243134314431543164317431843194320432143224323432443254326432743284329433043314332433343344335433643374338433943404341434243434344434543464347434843494350435143524353435443554356435743584359436043614362436343644365436643674368436943704371437243734374437543764377437843794380438143824383438443854386438743884389439043914392439343944395439643974398439944004401440244034404440544064407440844094410441144124413441444154416441744184419442044214422442344244425442644274428442944304431443244334434443544364437443844394440444144424443444444454446444744484449445044514452445344544455445644574458445944604461446244634464446544664467446844694470447144724473447444754476447744784479448044814482448344844485448644874488448944904491449244934494449544964497449844994500450145024503450445054506450745084509451045114512451345144515451645174518451945204521452245234524452545264527452845294530453145324533453445354536453745384539454045414542454345444545454645474548454945504551455245534554455545564557455845594560456145624563456445654566456745684569457045714572457345744575457645774578457945804581458245834584458545864587458845894590459145924593459445954596459745984599460046014602460346044605460646074608460946104611461246134614461546164617461846194620462146224623462446254626462746284629463046314632463346344635463646374638463946404641464246434644464546464647464846494650465146524653465446554656465746584659466046614662466346644665466646674668466946704671467246734674467546764677467846794680468146824683468446854686468746884689469046914692469346944695469646974698469947004701470247034704470547064707470847094710471147124713471447154716471747184719472047214722472347244725472647274728472947304731473247334734473547364737473847394740474147424743474447454746474747484749475047514752475347544755475647574758475947604761476247634764476547664767476847694770477147724773477447754776477747784779478047814782478347844785478647874788478947904791479247934794479547964797479847994800480148024803
  1. using Aspose.Cells;
  2. using Microsoft.AspNetCore.Http;
  3. using Microsoft.AspNetCore.Mvc;
  4. using OASystem.API.OAMethodLib;
  5. using OASystem.API.OAMethodLib.File;
  6. using OASystem.Domain;
  7. using OASystem.Domain.Dtos.Financial;
  8. using OASystem.Domain.Dtos.Groups;
  9. using OASystem.Domain.Entities.Financial;
  10. using OASystem.Domain.Entities.Groups;
  11. using OASystem.Domain.ViewModels.Financial;
  12. using OASystem.Domain.ViewModels.Groups;
  13. using OASystem.Domain.ViewModels.SmallFun;
  14. using OASystem.Infrastructure.Repositories.Financial;
  15. using OASystem.Infrastructure.Repositories.Groups;
  16. using SqlSugar;
  17. using StackExchange.Redis;
  18. using System.Data;
  19. using static OpenAI.GPT3.ObjectModels.SharedModels.IOpenAiModels;
  20. using System.Web;
  21. using Aspose.Words;
  22. using NPOI.HSSF.Util;
  23. using OASystem.Domain.Entities.Customer;
  24. using System.IO.Compression;
  25. using NPOI.SS.UserModel;
  26. using System.Net.Http;
  27. using Google.Protobuf;
  28. using NPOI.SS.Formula.Functions;
  29. using System.Globalization;
  30. using NPOI.POIFS.Properties;
  31. using SixLabors.ImageSharp.ColorSpaces;
  32. using OASystem.Domain.ViewModels.QiYeWeChat;
  33. using System.Diagnostics;
  34. using System.Security.Cryptography;
  35. using NPOI.POIFS.Crypt.Dsig;
  36. using OASystem.API.OAMethodLib.QiYeWeChatAPI.AppNotice;
  37. using Org.BouncyCastle.Asn1.Ocsp;
  38. using FluentValidation;
  39. using AutoMapper;
  40. using NetTaste;
  41. using EyeSoft.Runtime.InteropServices;
  42. using NPOI.HPSF;
  43. using System.Data.OleDb;
  44. using Org.BouncyCastle.Crypto;
  45. using OfficeOpenXml;
  46. using Org.BouncyCastle.Asn1.Crmf;
  47. using EyeSoft.Extensions;
  48. using System.Collections;
  49. using SqlSugar.Extensions;
  50. namespace OASystem.API.Controllers
  51. {
  52. /// <summary>
  53. /// 财务模块
  54. /// </summary>
  55. [Route("api/[controller]/[action]")]
  56. [ApiController]
  57. public class FinancialController : ControllerBase
  58. {
  59. private readonly IMapper _mapper;
  60. private readonly IConfiguration _config;
  61. private readonly SqlSugarClient _sqlSugar;
  62. private readonly HttpClient _httpClient;
  63. private readonly SetDataTypeRepository _setDataTypeRep;
  64. private readonly SetDataRepository _setDataRep;
  65. private readonly DailyFeePaymentRepository _daiRep; //日付申请仓库
  66. private readonly TeamRateRepository _teamRateRep; //团组汇率仓库
  67. private readonly ForeignReceivablesRepository _ForForeignReceivablesRep; //对外收款账单仓库
  68. private readonly ProceedsReceivedRepository _proceedsReceivedRep; //已收款项仓库
  69. private readonly PaymentRefundAndOtherMoneyRepository _paymentRefundAndOtherMoneyRep; //收款退还与其他款项 仓库
  70. private readonly DelegationInfoRepository _delegationInfoRep; //团组信息 仓库
  71. /// <summary>
  72. /// 初始化
  73. /// </summary>
  74. public FinancialController(IMapper mapper, IConfiguration configuration, DailyFeePaymentRepository daiRep, SqlSugarClient sqlSugar, SetDataTypeRepository setDataTypeRep,
  75. TeamRateRepository teamRateRep, ForeignReceivablesRepository ForForeignReceivablesRep, ProceedsReceivedRepository proceedsReceivedRep,
  76. PaymentRefundAndOtherMoneyRepository paymentRefundAndOtherMoneyRep, HttpClient httpClient, DelegationInfoRepository delegationInfoRep)
  77. {
  78. _mapper = mapper;
  79. _config = configuration;
  80. _daiRep = daiRep;
  81. _sqlSugar = sqlSugar;
  82. _setDataTypeRep = setDataTypeRep;
  83. _teamRateRep = teamRateRep;
  84. _ForForeignReceivablesRep = ForForeignReceivablesRep;
  85. _proceedsReceivedRep = proceedsReceivedRep;
  86. _paymentRefundAndOtherMoneyRep = paymentRefundAndOtherMoneyRep;
  87. _httpClient = httpClient;
  88. _delegationInfoRep = delegationInfoRep;
  89. }
  90. #region 日付申请
  91. /// <summary>
  92. /// 获取日付申请 基础数据源
  93. /// </summary>
  94. /// <param name="dto"> 日付申请 分页 dto</param>
  95. /// <returns></returns>
  96. [HttpPost]
  97. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  98. public async Task<IActionResult> PostPageSearchDailyPaymentPriceTypeData(PortDtoBase dto)
  99. {
  100. var result = await _daiRep.GetPagePriceTypeData(dto);
  101. if (result == null || result.Code != 0)
  102. {
  103. return Ok(JsonView(false, result.Msg));
  104. }
  105. var data = result.Data;
  106. return Ok(JsonView(data));
  107. }
  108. /// <summary>
  109. /// 获取日付申请 基础数据源 - 转账表识
  110. /// </summary>
  111. /// <param name="dto"> 日付申请 分页 dto</param>
  112. /// <returns></returns>
  113. [HttpPost]
  114. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  115. public async Task<IActionResult> GetSearchDailyPaymentPriceTypeAddData(PortDtoBase dto)
  116. {
  117. var result = await _daiRep.GetPriceTypeAddData(dto);
  118. if (result == null || result.Code != 0)
  119. {
  120. return Ok(JsonView(false, result.Msg));
  121. }
  122. var data = result.Data;
  123. return Ok(JsonView(data));
  124. }
  125. /// <summary>
  126. /// 日付申请 Page Search
  127. /// </summary>
  128. /// <param name="dto"> 日付申请 分页 dto</param>
  129. /// <returns></returns>
  130. [HttpPost]
  131. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  132. public async Task<IActionResult> PostPageSearchDailyPaymentList(PageDailyFeePaymentDto dto)
  133. {
  134. var result = await _daiRep.GetPageSearchAll(dto);
  135. if (result == null || result.Code != 0)
  136. {
  137. return Ok(JsonView(false, result.Msg));
  138. }
  139. var data = result.Data;
  140. if (data == null)
  141. {
  142. return Ok(JsonView(false, result.Msg));
  143. }
  144. return Ok(JsonView(data));
  145. }
  146. /// <summary>
  147. /// 日付申请 Single Search By Id
  148. /// </summary>
  149. /// <param name="dto"></param>
  150. /// <returns></returns>
  151. [HttpPost]
  152. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  153. public async Task<IActionResult> PostSearchDailyPaymentInfo(SearchDailyFeePaymentDto dto)
  154. {
  155. var result = await _daiRep.GetSearchById(dto);
  156. if (result == null || result.Code != 0)
  157. {
  158. return Ok(JsonView(false, result.Msg));
  159. }
  160. return Ok(JsonView(result.Data));
  161. }
  162. /// <summary>
  163. /// 日付申请 添加
  164. /// </summary>
  165. /// <param name="dto"> 日付申请 添加 dto</param>
  166. /// <returns></returns>
  167. [HttpPost]
  168. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  169. public async Task<IActionResult> PostAddDailyPayment(AddDailyFeePaymentDto dto)
  170. {
  171. var result = await _daiRep.Add(dto);
  172. if (result == null || result.Code != 0)
  173. {
  174. return Ok(JsonView(false, result.Msg));
  175. }
  176. #region 应用推送
  177. try
  178. {
  179. int dailyId = result.Data.GetType().GetProperty("dailyId").GetValue(result.Data, null);
  180. int sign = result.Data.GetType().GetProperty("sign").GetValue(result.Data, null);
  181. await AppNoticeLibrary.DailyPayReminders_Create_ToCaiwuChat(dailyId, sign, QiyeWeChatEnum.CaiWuChat);
  182. }
  183. catch (Exception ex)
  184. {
  185. }
  186. #endregion
  187. return Ok(JsonView(true));
  188. }
  189. /// <summary>
  190. /// 日付申请 Update
  191. /// </summary>
  192. /// <param name="dto"> 日付申请 修改 dto</param>
  193. /// <returns></returns>
  194. [HttpPost]
  195. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  196. public async Task<IActionResult> PostEditDailyPayment(EditDailyFeePaymentDto dto)
  197. {
  198. var result = await _daiRep.Edit(dto);
  199. if (result == null || result.Code != 0)
  200. {
  201. return Ok(JsonView(false, result.Msg));
  202. }
  203. #region 应用推送
  204. try
  205. {
  206. int dailyId = result.Data.GetType().GetProperty("dailyId").GetValue(result.Data, null);
  207. int sign = result.Data.GetType().GetProperty("sign").GetValue(result.Data, null);
  208. await AppNoticeLibrary.DailyPayReminders_Create_ToCaiwuChat(dailyId, sign, QiyeWeChatEnum.CaiWuChat);
  209. }
  210. catch (Exception ex)
  211. {
  212. }
  213. #endregion
  214. return Ok(JsonView(true));
  215. }
  216. /// <summary>
  217. /// 日付申请 Del
  218. /// </summary>
  219. /// <param name="dto"> 日付申请 删除 dto</param>
  220. /// <returns></returns>
  221. [HttpPost]
  222. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  223. public async Task<IActionResult> PostDelDailyPayment(DelDailyFeePaymentDto dto)
  224. {
  225. var result = await _daiRep.Del(dto);
  226. if (result == null || result.Code != 0)
  227. {
  228. return Ok(JsonView(false, result.Msg));
  229. }
  230. return Ok(JsonView(true));
  231. }
  232. /// <summary>
  233. /// 日付申请 财务审核
  234. /// </summary>
  235. /// <param name="dto"> dto </param>
  236. /// <returns></returns>
  237. [HttpPost]
  238. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  239. public async Task<IActionResult> PostDelDailyPaymentAudit(DP_AuditStatusDto dto)
  240. {
  241. var result = await _daiRep.DailyPaymentAudit(dto);
  242. if (result == null || result.Code != 0)
  243. {
  244. return Ok(JsonView(false, result.Msg));
  245. }
  246. return Ok(JsonView(true));
  247. }
  248. /// <summary>
  249. /// 日付申请 Single Excel Download
  250. /// </summary>
  251. /// <param name="dto"> dto </param>
  252. /// <returns></returns>
  253. [HttpPost]
  254. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  255. public async Task<IActionResult> PostExcelDailyPaymentDownload(SearchDailyFeePaymentDto dto)
  256. {
  257. if (dto.PortType == 1 || dto.PortType == 2)
  258. {
  259. Fin_DailyFeePaymentInfolView feeData = new Fin_DailyFeePaymentInfolView();
  260. string feeSql = string.Format(@"Select * From Fin_DailyFeePayment
  261. Where IsDel=0 And Id = {0} ", dto.Id);
  262. feeData = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentInfolView>(feeSql).FirstAsync();
  263. if (feeData == null)
  264. {
  265. return Ok(JsonView(false, "暂无数据!"));
  266. }
  267. string feeContentSql = string.Format(@"Select * From Fin_DailyFeePaymentContent
  268. Where IsDel=0 And DFPId = {0} ", dto.Id);
  269. feeData.FeeContents = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentContentInfolView>(feeContentSql).ToListAsync();
  270. if (feeData != null)
  271. {
  272. string userName = string.Empty;
  273. string userSql = string.Format("Select * From Sys_Users Where Id={0} And Isdel = {1}", feeData.CreateUserId, 0);
  274. Sys_Users user = await _sqlSugar.SqlQueryable<Sys_Users>(userSql).FirstAsync();
  275. if (user != null) { userName = user.CnName; }
  276. var setData = _setDataTypeRep.QueryDto<Sys_SetData, Fin_DailyFeePaymentPagePriceSubTypeView>().ToList();
  277. //48人员费用 49办公费用 50 销售费用 51 其他费用 55 大运会
  278. var priceSubTypeData = setData.Where(s => s.STid == 55).ToList();
  279. Dictionary<string, object> pairs = new Dictionary<string, object>();
  280. List<DataTable> datas = new List<DataTable>();
  281. //if (priceSubTypeData.Where(s => s.Id == feeData.PriceTypeId).ToList().Count() > 0)//大运会专属模板
  282. //{
  283. // //AsposeHelper.ExpertExcelToModel("日常费用付款申请模板-大运会数据.xls", "DailyPayment", "大运会所有日常费用付款申请.xls",
  284. // // pairs, datas);
  285. //}
  286. //else //日付常规模板
  287. //{
  288. pairs.Clear();
  289. pairs.Add("Opertor", userName);
  290. pairs.Add("DateTime", feeData.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
  291. pairs.Add("FAuditStatus", feeData.FAuditDesc);
  292. pairs.Add("MAuditStatus", feeData.MAuditDesc);
  293. pairs.Add("SumPrice", feeData.SumPrice);
  294. DataTable data = AsposeHelper.ListToDataTable("DailyFeePayment", feeData.FeeContents);
  295. datas.Clear();
  296. datas.Add(data);
  297. string fileName = string.Format("{0}-日常费用付款申请.xlsx", feeData.Instructions);
  298. string msg = AsposeHelper.ExpertExcelToModel("日常费用付款申请模板.xlsx", "DailyPayment", fileName, pairs, datas);
  299. return Ok(JsonView(true, msg));
  300. //}
  301. }
  302. else
  303. {
  304. return Ok(JsonView(false, "暂无数据!"));
  305. }
  306. }
  307. return Ok(JsonView(true));
  308. }
  309. #endregion
  310. #region 团组提成
  311. /// <summary>
  312. /// 提成 Page Search
  313. /// </summary>
  314. /// <param name="dto"> 提成 分页 dto</param>
  315. /// <returns></returns>
  316. [HttpPost]
  317. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  318. public async Task<IActionResult> PostPageSearchCommissionList(GroupCommissionDto dto)
  319. {
  320. var data = await GroupCommission.GetCommissionPageList(dto);
  321. return Ok(JsonView(data.Data));
  322. }
  323. #endregion
  324. #region 团组汇率
  325. /// <summary>
  326. /// 团组汇率 Select数据源(团组列,汇率列)
  327. /// </summary>
  328. /// <param name="dto"></param>
  329. /// <returns></returns>
  330. [HttpPost]
  331. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  332. public async Task<IActionResult> GetGroupRateDataSources(TeamRateDto dto)
  333. {
  334. try
  335. {
  336. //迁移数据更新团组汇率
  337. //Result teamRateData1 = await _teamRateRep.GetGroupRateChangeData();
  338. //var data = await _teamRateRep.PostGroupTeamRateHot();
  339. Stopwatch stopwatch = Stopwatch.StartNew();
  340. GroupNameDto groupNameDto = new GroupNameDto() { PortType = dto.PortType };
  341. var groups = await _delegationInfoRep.GetGroupNameList(groupNameDto);
  342. List<CurrencyHot> _currData = new List<CurrencyHot>();
  343. string currData = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("GroupTeamCurrencyData");//string 取
  344. if (!string.IsNullOrEmpty(currData))
  345. {
  346. _currData = JsonConvert.DeserializeObject<List<CurrencyHot>>(currData);
  347. }
  348. else
  349. {
  350. _currData = await _teamRateRep.PostGroupTeamRateHot();
  351. //过期时间 25 Hours
  352. TimeSpan ts = DateTime.Now.AddHours(25).TimeOfDay;
  353. await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync("GroupTeamCurrencyData", JsonConvert.SerializeObject(_currData), ts);
  354. }
  355. var _data = new { GroupData = groups.Data, TeamRateData = _currData };
  356. stopwatch.Stop();
  357. return Ok(JsonView(true, $"查询成功!耗时:{stopwatch.ElapsedMilliseconds / 1000}s", _data));
  358. }
  359. catch (Exception ex)
  360. {
  361. return Ok(JsonView(false, ex.Message));
  362. }
  363. }
  364. ///// <summary>
  365. ///// 团组汇率 changge
  366. ///// </summary>
  367. ///// <returns></returns>
  368. //[HttpPost]
  369. //[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  370. //public async Task<IActionResult> ChangeGroupRateInfo()
  371. //{
  372. // try
  373. // {
  374. // Result teamRateData = await _teamRateRep.GetGroupRateChangeData();
  375. // if (teamRateData.Code != 0)
  376. // {
  377. // return Ok(JsonView(false, teamRateData.Msg));
  378. // }
  379. // return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
  380. // }
  381. // catch (Exception ex)
  382. // {
  383. // return Ok(JsonView(false, ex.Message));
  384. // throw;
  385. // }
  386. //}
  387. /// <summary>
  388. /// 团组汇率 Select汇率详情
  389. /// </summary>
  390. /// <param name="dto"></param>
  391. /// <returns></returns>
  392. [HttpPost]
  393. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  394. public async Task<IActionResult> GetGroupRateInfo(TeamRateInfoDto dto)
  395. {
  396. try
  397. {
  398. Result teamRateData = await _teamRateRep.GetGroupRateInfoByDiid(dto);
  399. if (teamRateData.Code != 0)
  400. {
  401. return Ok(JsonView(false, teamRateData.Msg));
  402. }
  403. return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
  404. }
  405. catch (Exception ex)
  406. {
  407. return Ok(JsonView(false, ex.Message));
  408. throw;
  409. }
  410. }
  411. /// <summary>
  412. /// 团组汇率 添加 or 更新
  413. /// </summary>
  414. /// <param name="dto"></param>
  415. /// <returns></returns>
  416. [HttpPost]
  417. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  418. public async Task<IActionResult> PostGroupRateUpdate(TeamRateUpdateDto dto)
  419. {
  420. try
  421. {
  422. Result teamRateData = await _teamRateRep.PostGroupRateUpdate(dto);
  423. if (teamRateData.Code != 0)
  424. {
  425. return Ok(JsonView(false, teamRateData.Msg));
  426. }
  427. return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
  428. }
  429. catch (Exception ex)
  430. {
  431. return Ok(JsonView(false, ex.Message));
  432. throw;
  433. }
  434. }
  435. #endregion
  436. #region 对外收款账单 关联已收款项
  437. /// <summary>
  438. /// 对外收款账单 Select数据源(团组名,币种,汇款方式)
  439. /// 关联已收款项
  440. /// </summary>
  441. /// <param name="dto"></param>
  442. /// <returns></returns>
  443. [HttpPost]
  444. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  445. public async Task<IActionResult> GetForeignReceivablesDataSources(ForeignReceivablesDataSourcesDto dto)
  446. {
  447. return Ok(await _ForForeignReceivablesRep.GetDataSource(dto));
  448. }
  449. /// <summary>
  450. /// 对外收款账单
  451. /// 账单详情
  452. /// 关联已收款项
  453. /// </summary>
  454. /// <param name="dto"></param>
  455. /// <returns></returns>
  456. [HttpPost]
  457. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  458. public async Task<IActionResult> GetGroupReceivablesInfoByDiId(ForForeignReceivablesInfoDto dto)
  459. {
  460. try
  461. {
  462. Result ffrData = await _ForForeignReceivablesRep.GetGroupReceivablesInfoByDiId(dto);
  463. if (ffrData.Code != 0)
  464. {
  465. return Ok(JsonView(false, ffrData.Msg));
  466. }
  467. return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
  468. }
  469. catch (Exception ex)
  470. {
  471. return Ok(JsonView(false, ex.Message));
  472. throw;
  473. }
  474. }
  475. /// <summary>
  476. /// 对外收款账单
  477. /// 账单 删除
  478. /// 关联已收款项
  479. /// </summary>
  480. /// <param name="dto"></param>
  481. /// <returns></returns>
  482. [HttpPost]
  483. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  484. public async Task<IActionResult> PostGroupReceivablesDel(DelForForeignReceivablesInfoDto dto)
  485. {
  486. try
  487. {
  488. Result ffrData = await _ForForeignReceivablesRep._Del(dto);
  489. if (ffrData.Code != 0)
  490. {
  491. return Ok(JsonView(false, ffrData.Msg));
  492. }
  493. return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
  494. }
  495. catch (Exception ex)
  496. {
  497. return Ok(JsonView(false, ex.Message));
  498. throw;
  499. }
  500. }
  501. /// <summary>
  502. /// 对外收款账单
  503. /// 添加 And 更新
  504. /// 关联已收款项
  505. /// </summary>
  506. /// <param name="dto"></param>
  507. /// <returns></returns>
  508. [HttpPost]
  509. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  510. public async Task<IActionResult> PostReceivablesOperate(ForeignReceivablesAddAndUpdateDto dto)
  511. {
  512. try
  513. {
  514. Result ffrData = await _ForForeignReceivablesRep.PostReceivablesOperate(dto);
  515. if (ffrData.Code != 0)
  516. {
  517. return Ok(JsonView(false, ffrData.Msg));
  518. }
  519. return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
  520. }
  521. catch (Exception ex)
  522. {
  523. return Ok(JsonView(false, ex.Message));
  524. throw;
  525. }
  526. }
  527. /// <summary>
  528. /// 已收款项
  529. /// 账单 删除
  530. /// 关联已收款项
  531. /// </summary>
  532. /// <param name="dto"></param>
  533. /// <returns></returns>
  534. [HttpPost]
  535. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  536. public async Task<IActionResult> PostAmountReceivedDel(ProceedsReceivedDelDto dto)
  537. {
  538. try
  539. {
  540. Result ffrData = await _proceedsReceivedRep._Del(dto);
  541. if (ffrData.Code != 0)
  542. {
  543. return Ok(JsonView(false, ffrData.Msg));
  544. }
  545. return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
  546. }
  547. catch (Exception ex)
  548. {
  549. return Ok(JsonView(false, ex.Message));
  550. throw;
  551. }
  552. }
  553. /// <summary>
  554. /// 已收款项
  555. /// 添加 And 更新
  556. /// 关联已收款项
  557. /// </summary>
  558. /// <param name="dto"></param>
  559. /// <returns></returns>
  560. [HttpPost]
  561. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  562. public async Task<IActionResult> PostAmountReceivedOperate(ProceedsReceivedDto dto)
  563. {
  564. try
  565. {
  566. Result ffrData = await _proceedsReceivedRep.PostAmountReceivedOperate(dto);
  567. if (ffrData.Code != 0)
  568. {
  569. return Ok(JsonView(false, ffrData.Msg));
  570. }
  571. return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
  572. }
  573. catch (Exception ex)
  574. {
  575. return Ok(JsonView(false, ex.Message));
  576. throw;
  577. }
  578. }
  579. /// <summary>
  580. /// 财务 已收款项
  581. /// 分配已收款项至 应收项下
  582. /// 关联已收款项
  583. /// </summary>
  584. /// <param name="dto"></param>
  585. /// <returns></returns>
  586. [HttpPost]
  587. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  588. public async Task<IActionResult> PostAllocateAmountReceived(AllocateAmountReceivedDto dto)
  589. {
  590. try
  591. {
  592. Result ffrData = await _proceedsReceivedRep.PostAllocateAmountReceived(dto);
  593. if (ffrData.Code != 0)
  594. {
  595. return Ok(JsonView(false, ffrData.Msg));
  596. }
  597. return Ok(JsonView(true));
  598. }
  599. catch (Exception ex)
  600. {
  601. return Ok(JsonView(false, ex.Message));
  602. throw;
  603. }
  604. }
  605. ///// <summary>
  606. ///// 财务 收款账单
  607. ///// 导出Word(北京,四川)
  608. ///// </summary>
  609. ///// <param name="dto"></param>
  610. ///// <returns></returns>
  611. //[HttpPost]
  612. //[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  613. //public async Task<IActionResult> PostAccountReceivableWordExport(AccountReceivableWordExportDto dto)
  614. //{
  615. // try
  616. // {
  617. // //模板处理
  618. // string typeName = string.Empty;
  619. // if (dto.TemplateType == 1) //四川
  620. // {
  621. // typeName = "四川";
  622. // }
  623. // else if (dto.TemplateType == 2) //北京
  624. // {
  625. // typeName = "北京";
  626. // }
  627. // else return Ok(JsonView(false,"请选择正确的模板类型!"));
  628. // string wordTempName = string.Format("收款账单({0})模板.doc", typeName);
  629. // }
  630. // catch (Exception ex)
  631. // {
  632. // return Ok(JsonView(false, ex.Message));
  633. // }
  634. //}
  635. #endregion
  636. #region 对外收款账单
  637. /// <summary>
  638. /// 对外收款账单
  639. /// 数据源
  640. /// </summary>
  641. /// <param name="dto"></param>
  642. /// <returns></returns>
  643. [HttpPost]
  644. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  645. public async Task<IActionResult> PostGroupReceivablesDataSource(ForeignReceivablesDataSourcesDto dto)
  646. {
  647. return Ok(await _ForForeignReceivablesRep.PostDataSource(dto));
  648. }
  649. /// <summary>
  650. /// 对外收款账单
  651. /// 账单详情
  652. /// </summary>
  653. /// <param name="dto"></param>
  654. /// <returns></returns>
  655. [HttpPost]
  656. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  657. public async Task<IActionResult> PostGroupReceivablesInfoByDiId(ForForeignReceivablesNewDto dto)
  658. {
  659. try
  660. {
  661. Result ffrData = await _ForForeignReceivablesRep.PostGroupReceivablesInfoByDiId(dto);
  662. if (ffrData.Code != 0)
  663. {
  664. return Ok(JsonView(false, ffrData.Msg));
  665. }
  666. return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
  667. }
  668. catch (Exception ex)
  669. {
  670. return Ok(JsonView(false, ex.Message));
  671. }
  672. }
  673. /// <summary>
  674. /// 对外收款账单
  675. /// 添加 And 更新
  676. /// </summary>
  677. /// <param name="dto"></param>
  678. /// <returns></returns>
  679. [HttpPost]
  680. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  681. public async Task<IActionResult> PostReceivablesSave(ForeignReceivablesSaveDto dto)
  682. {
  683. try
  684. {
  685. Result ffrData = await _ForForeignReceivablesRep.PostReceivablesSave(dto);
  686. if (ffrData.Code != 0)
  687. {
  688. return Ok(JsonView(false, ffrData.Msg));
  689. }
  690. return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
  691. }
  692. catch (Exception ex)
  693. {
  694. return Ok(JsonView(false, ex.Message));
  695. throw;
  696. }
  697. }
  698. /// <summary>
  699. /// 已收账单
  700. /// 删除
  701. /// </summary>
  702. /// <param name="dto"></param>
  703. /// <returns></returns>
  704. [HttpPost]
  705. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  706. public async Task<IActionResult> PostReceivablesDel(ForeignReceivablesDelDto dto)
  707. {
  708. try
  709. {
  710. Result ffrData = await _ForForeignReceivablesRep.PostReceivablesDel(dto);
  711. if (ffrData.Code != 0)
  712. {
  713. return Ok(JsonView(false, ffrData.Msg));
  714. }
  715. return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
  716. }
  717. catch (Exception ex)
  718. {
  719. return Ok(JsonView(false, ex.Message));
  720. throw;
  721. }
  722. }
  723. /// <summary>
  724. /// 已收账单
  725. /// File Downloasd
  726. /// Init
  727. /// </summary>
  728. /// <param name="dto"></param>
  729. /// <returns></returns>
  730. [HttpPost]
  731. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  732. public async Task<IActionResult> PostReceivablesFeilDownloadInit()
  733. {
  734. return Ok(JsonView(true, "操作成功!", new List<dynamic> {
  735. new { Id = 1, Name = "生成收款单(四川)" },
  736. new { Id = 2, Name = "生成收款单(北京)" },
  737. new { Id = 3, Name = "汇款账单" }
  738. }
  739. ));
  740. }
  741. /// <summary>
  742. /// 已收账单
  743. /// File Downloasd
  744. /// </summary>
  745. /// <param name="dto"></param>
  746. /// <returns></returns>
  747. [HttpPost]
  748. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  749. public async Task<IActionResult> PostReceivablesFeilDownload(ForeignReceivablesFeilDownloadDto dto)
  750. {
  751. try
  752. {
  753. if (dto.DiId < 1)
  754. {
  755. return Ok(JsonView(false, "请传入有效DiId参数!"));
  756. }
  757. if (dto.FileType < 1 || dto.FileType > 3)
  758. {
  759. return Ok(JsonView(false, "请传入有效FileType参数! 1 生成收款单(四川) 2 生成收款单(北京) 3 汇款账单"));
  760. }
  761. var _DelegationInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && it.Id == dto.DiId).First();
  762. if (_DelegationInfo == null)
  763. {
  764. return Ok(JsonView(false, "暂无团组信息!!"));
  765. }
  766. if (dto.FileType == 1 || dto.FileType == 2)
  767. {
  768. var _ForeignReceivables = _sqlSugar.Queryable<Fin_ForeignReceivables>().Where(it => it.IsDel == 0 && it.Diid == dto.DiId).ToList();
  769. if (_ForeignReceivables.Count < 1)
  770. {
  771. return Ok(JsonView(false, "暂无收款信息!!"));
  772. }
  773. string tempName = "";
  774. if (dto.FileType == 1) tempName = $"收款账单(四川)模板.docx";
  775. else if (dto.FileType == 2) tempName = $"收款账单(北京)模板.docx";
  776. string tempPath = AppSettingsHelper.Get("WordBasePath") + $"ForeignReceivables/Temp/{tempName}";
  777. //载入模板
  778. Document doc = new Document(tempPath);
  779. DocumentBuilder builder = new DocumentBuilder(doc);
  780. #region 替换Word模板书签内容
  781. //这里可以创建个DataTable循环添加书签的值,这里提示一下就不多做修改了
  782. //付款方
  783. if (doc.Range.Bookmarks["To"] != null)
  784. {
  785. Bookmark mark = doc.Range.Bookmarks["To"];
  786. mark.Text = _ForeignReceivables[0].To == null ? "" : _ForeignReceivables[0].To.ToString();
  787. }
  788. //付款方电话
  789. if (doc.Range.Bookmarks["ToTel"] != null)
  790. {
  791. Bookmark mark = doc.Range.Bookmarks["ToTel"];
  792. mark.Text = _ForeignReceivables[0].ToTel == null ? "" : _ForeignReceivables[0].ToTel.ToString();
  793. }
  794. //导出时间
  795. if (doc.Range.Bookmarks["Date"] != null)
  796. {
  797. Bookmark mark = doc.Range.Bookmarks["Date"];
  798. mark.Text = DateTime.Now.ToString("yyyy-MM-dd");
  799. }
  800. //注
  801. if (doc.Range.Bookmarks["Attention"] != null)
  802. {
  803. Bookmark mark = doc.Range.Bookmarks["Attention"];
  804. mark.Text = _ForeignReceivables[0].Attention == null ? "" : _ForeignReceivables[0].Attention.ToString();
  805. }
  806. //团队名称
  807. if (doc.Range.Bookmarks["Team"] != null)
  808. {
  809. Bookmark mark = doc.Range.Bookmarks["Team"];
  810. mark.Text = _DelegationInfo.VisitCountry == null ? "" : _DelegationInfo.VisitCountry.Replace("|", "、").ToString();
  811. }
  812. //付款日期
  813. if (doc.Range.Bookmarks["PayDate"] != null)
  814. {
  815. Bookmark mark = doc.Range.Bookmarks["PayDate"];
  816. mark.Text = _ForeignReceivables[0].PayDate == null ? "" : Convert.ToDateTime(_ForeignReceivables[0].PayDate).ToString("yyyy年MM月dd日");
  817. }
  818. decimal sumPrice = 0;
  819. //各项费用
  820. if (doc.Range.Bookmarks["PayItemContent"] != null)
  821. {
  822. string items = "";
  823. foreach (var fr in _ForeignReceivables)
  824. {
  825. var currInfo = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == fr.Currency).First();
  826. items += $"{fr.PriceName} {currInfo?.Name} {fr.Price.ToString("#0.00")} * {fr.Count} {fr.Unit} * {fr.Rate}.................. RMB {fr.ItemSumPrice.ToString("#0.00")}\n";
  827. sumPrice += fr.ItemSumPrice;
  828. }
  829. Bookmark mark = doc.Range.Bookmarks["PayItemContent"];
  830. mark.Text = items;
  831. }
  832. //合计
  833. if (doc.Range.Bookmarks["Total"] != null)
  834. {
  835. Bookmark mark = doc.Range.Bookmarks["Total"];
  836. mark.Text = sumPrice.ToString("#0.00");
  837. }
  838. #endregion
  839. //文件名
  840. string strFileName = _DelegationInfo.TeamName + "-收款账单.docx";
  841. //去水印
  842. new Aspose.Words.License().SetLicense(new MemoryStream(Convert.FromBase64String(AsposeHelper.asposeKey)));
  843. doc.Save(AppSettingsHelper.Get("WordBasePath") + "ForeignReceivables/File/" + strFileName);
  844. string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/ForeignReceivables/File/" + strFileName;
  845. return Ok(JsonView(true, "成功", new { Url = url }));
  846. }
  847. else if (dto.FileType == 3) //汇款通知
  848. {
  849. var _EnterExitCosts = _sqlSugar.Queryable<Grp_EnterExitCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
  850. var _DayAndCosts = _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId && it.NationalTravelFeeId > 0).ToList();
  851. if (_EnterExitCosts == null)
  852. {
  853. return Ok(JsonView(false, "该团组未填写出入境费用;"));
  854. }
  855. var _cityFee = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();
  856. foreach (var item in _DayAndCosts)
  857. {
  858. var cityInfo = _cityFee.Where(it => it.Id == item.NationalTravelFeeId).FirstOrDefault();
  859. if (cityInfo != null)
  860. {
  861. if (cityInfo.City.Contains("全部城市") || cityInfo.City.Contains("其他城市"))
  862. {
  863. item.Place = cityInfo.Country;
  864. }
  865. else item.Place = cityInfo.City;
  866. }
  867. }
  868. //数据源
  869. List<Grp_DayAndCost> dac1 = _DayAndCosts.Where(it => it.Type == 1).ToList(); //住宿费
  870. List<Grp_DayAndCost> dac2 = _DayAndCosts.Where(it => it.Type == 2).ToList(); //伙食费
  871. List<Grp_DayAndCost> dac3 = _DayAndCosts.Where(it => it.Type == 3).ToList(); //公杂费
  872. List<Grp_DayAndCost> dac4 = _DayAndCosts.Where(it => it.Type == 4).ToList(); //培训费
  873. //币种Data
  874. var currData = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 66).ToList();
  875. var DeleClientList = _sqlSugar.Queryable<Grp_TourClientList>()
  876. .LeftJoin<Crm_DeleClient>((tcl, dc) => tcl.ClientId == dc.Id && dc.IsDel == 0)
  877. .LeftJoin<Crm_CustomerCompany>((tcl, dc, cc) => dc.CrmCompanyId == cc.Id && dc.IsDel == 0)
  878. .Where((tcl, dc, cc) => tcl.IsDel == 0 && tcl.DiId == dto.DiId)
  879. .Select((tcl, dc, cc) => new
  880. {
  881. Name = dc.LastName + dc.FirstName,
  882. Sex = dc.Sex,
  883. Birthday = dc.BirthDay,
  884. Company = cc.CompanyFullName,
  885. Job = dc.Job,
  886. AirType = tcl.ShippingSpaceTypeId
  887. })
  888. .ToList();
  889. if (DeleClientList.Count < 1)
  890. {
  891. return Ok(JsonView(false, "暂无团组成员,请先填写团组成员!!!"));
  892. }
  893. var _ClientNames = DeleClientList.Select(x => x.Name).ToList();
  894. var _GroupClient = DeleClientList.GroupBy(x => x.Company).ToList();
  895. Dictionary<string, string> bookmarkArr = null;
  896. string tempPath = AppSettingsHelper.Get("WordBasePath") + $"ForeignReceivables/Temp/汇款通知.docx";
  897. //载入模板
  898. Document doc = new Document(tempPath);
  899. DocumentBuilder builder = null;
  900. List<string> filesToZip = new List<string>();
  901. foreach (var ClientItem in _GroupClient) //遍历单位
  902. {
  903. doc = new Document(tempPath);
  904. builder = new DocumentBuilder(doc);
  905. Paragraph paragraph = new Paragraph(doc);
  906. bookmarkArr = new Dictionary<string, string>();
  907. bookmarkArr.Add("titleClientUnit", ClientItem.Key); //title单位
  908. bookmarkArr.Add("ClientUnit", ClientItem.Key); //单位
  909. bookmarkArr.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));//出发日期
  910. bookmarkArr.Add("name", string.Join(",", ClientItem.Select(x => x.Name).ToArray()).TrimEnd(','));//全部人员信息
  911. bookmarkArr.Add("VisitCountry", _DelegationInfo.VisitCountry.Replace("|", "、"));//出访国家
  912. bookmarkArr.Add("dayTime", DateTime.Now.ToString("yyyy年MM月dd日"));//今天日期
  913. var ClientItemList = ClientItem.ToList();
  914. string UsersTop = string.Empty;//word中人员以及金额
  915. decimal WordAllPrice = 0.00M;
  916. //int[] infoColumn = new int[5] {dac1.Select(x=>x.Place.Length).ToList().Max(),2, dac1.Select(x => x.Cost.Length).Max() + dac1.Select(x => x.Currency.Length).Max(),
  917. // 6, 3 + dac1.Where(x=>!string.IsNullOrWhiteSpace(x.Place)).Select(x=>(Convert.ToDecimal(x.SubTotal) / Convert.ToDecimal(x.Cost)).ToString().Length).Max() };
  918. //遍历人员
  919. for (int i = 0; i < ClientItemList.Count(); i++)
  920. {
  921. Dictionary<string, string> TeableBookmarkArr = new Dictionary<string, string>();
  922. var client = ClientItemList[i]; //每个人员
  923. var firstName = ClientItemList[i].Name;
  924. //计算费用总和
  925. decimal airPrice = client.AirType == 460 ? _EnterExitCosts.AirJJ : _EnterExitCosts.AirGW;
  926. decimal AllPrice = airPrice + _EnterExitCosts.CityTranffic + dac1.Sum(x => x.SubTotal) + dac2.Sum(x => x.SubTotal) +
  927. dac3.Sum(x => x.SubTotal) + dac4.Sum(x => x.SubTotal) + _EnterExitCosts.Visa + _EnterExitCosts.Safe +
  928. +_EnterExitCosts.YiMiao + _EnterExitCosts.YiMiao + _EnterExitCosts.Ticket + _EnterExitCosts.Service;
  929. WordAllPrice += AllPrice;
  930. UsersTop += firstName + "出访费用为¥" + AllPrice.ToString("#0.00") + "元、";
  931. TeableBookmarkArr.Add("jp", (client.AirType == 460 ? _EnterExitCosts.AirJJ : _EnterExitCosts.AirGW).ToString("#0.00") + " 元"); //机票金额
  932. TeableBookmarkArr.Add("cs", _EnterExitCosts.CityTranffic.ToString("#0.00") + " 元"); //城市交通费用
  933. TeableBookmarkArr.Add("zs", dac1.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //住宿费
  934. string zsinfo = string.Empty;
  935. string hsinfo = string.Empty;
  936. string gzinfo = string.Empty;
  937. List<string> placeArr = new List<string>();
  938. Aspose.Words.Tables.Table FirstTable = (Aspose.Words.Tables.Table)doc.GetChild(NodeType.Table, 0, true);
  939. Aspose.Words.Tables.Table ChildTable = (Aspose.Words.Tables.Table)FirstTable.GetChild(NodeType.Table, 0, true);
  940. Aspose.Words.Tables.Table ChildTable1 = (Aspose.Words.Tables.Table)FirstTable.GetChild(NodeType.Table, 1, true);
  941. Aspose.Words.Tables.Table ChildTable2 = (Aspose.Words.Tables.Table)FirstTable.GetChild(NodeType.Table, 2, true);
  942. int rowIndex = 0;
  943. foreach (var item in dac1)
  944. {
  945. if (string.IsNullOrWhiteSpace(item.Place))
  946. {
  947. continue;
  948. }
  949. if (placeArr.Contains(item.Place))
  950. {
  951. continue;
  952. }
  953. else
  954. {
  955. placeArr.Add(item.Place);
  956. }
  957. if (!string.IsNullOrWhiteSpace(item.Place))
  958. {
  959. if (rowIndex > ChildTable.Rows.Count - 1)
  960. {
  961. var chitableRow = ChildTable.LastRow.Clone(true);
  962. ChildTable.AppendChild(chitableRow);
  963. }
  964. int days = dac1.FindAll(x => x.Place == item.Place).Count();
  965. SetCells(ChildTable, doc, rowIndex, 0, item.Place);
  966. SetCells(ChildTable, doc, rowIndex, 1, days + "晚");
  967. string currencyCode = currData.Find(it => it.Id == item.Currency)?.Remark ?? "Unknown";
  968. SetCells(ChildTable, doc, rowIndex, 2, item.Cost.ToString("#0.00") + currencyCode + "/晚");
  969. SetCells(ChildTable, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
  970. SetCells(ChildTable, doc, rowIndex, 4, "CNY" + item.SubTotal + "\r\n");
  971. rowIndex++;
  972. zsinfo += item.Place + " " + days + "晚 " + item.Cost.ToString("#0.00") + currencyCode + "/晚" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.0000") + " CNY" + item.SubTotal * days + "\r\n";
  973. }
  974. }
  975. placeArr.Clear();
  976. rowIndex = 0;
  977. foreach (var item in dac2)
  978. {
  979. if (string.IsNullOrWhiteSpace(item.Place))
  980. {
  981. continue;
  982. }
  983. if (placeArr.Contains(item.Place))
  984. {
  985. continue;
  986. }
  987. else
  988. {
  989. placeArr.Add(item.Place);
  990. }
  991. if (!string.IsNullOrWhiteSpace(item.Place))
  992. {
  993. if (rowIndex > ChildTable1.Rows.Count - 1)
  994. {
  995. var chitableRow = ChildTable1.LastRow.Clone(true);
  996. ChildTable1.AppendChild(chitableRow);
  997. }
  998. int days = dac2.FindAll(x => x.Place == item.Place).Count();
  999. SetCells(ChildTable1, doc, rowIndex, 0, item.Place);
  1000. SetCells(ChildTable1, doc, rowIndex, 1, days + "天");
  1001. string currencyCode = currData.Find(it => it.Id == item.Currency)?.Remark ?? "Unknown";
  1002. SetCells(ChildTable1, doc, rowIndex, 2, item.Cost.ToString("#0.00") + currencyCode + "/天");
  1003. SetCells(ChildTable1, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
  1004. SetCells(ChildTable1, doc, rowIndex, 4, "CNY" + item.SubTotal);
  1005. rowIndex++;
  1006. hsinfo += item.Place + " " + days + "天 " + item.Cost.ToString("#0.00") + currencyCode + "/天" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.0000") + " CNY" + item.SubTotal * days + "\r\n";
  1007. }
  1008. }
  1009. placeArr.Clear();
  1010. rowIndex = 0;
  1011. foreach (var item in dac3)
  1012. {
  1013. if (string.IsNullOrWhiteSpace(item.Place))
  1014. {
  1015. continue;
  1016. }
  1017. if (placeArr.Contains(item.Place))
  1018. {
  1019. continue;
  1020. }
  1021. else
  1022. {
  1023. placeArr.Add(item.Place);
  1024. }
  1025. if (!string.IsNullOrWhiteSpace(item.Place))
  1026. {
  1027. if (rowIndex > ChildTable2.Rows.Count - 1)
  1028. {
  1029. var chitableRow = ChildTable2.LastRow.Clone(true);
  1030. ChildTable2.AppendChild(chitableRow);
  1031. }
  1032. int days = dac3.FindAll(x => x.Place == item.Place).Count();
  1033. SetCells(ChildTable2, doc, rowIndex, 0, item.Place);
  1034. SetCells(ChildTable2, doc, rowIndex, 1, days + "天");
  1035. string currencyCode = currData.Find(it => it.Id == item.Currency)?.Remark ?? "Unknown";
  1036. SetCells(ChildTable2, doc, rowIndex, 2, item.Cost.ToString("#0.00") + currencyCode + "/天");
  1037. SetCells(ChildTable2, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
  1038. SetCells(ChildTable2, doc, rowIndex, 4, "CNY" + item.SubTotal + "\r\n");
  1039. rowIndex++;
  1040. gzinfo += item.Place + " " + days + "天 " + item.Cost.ToString("#0.00") + currencyCode + "/天" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00") + " CNY" + item.SubTotal * days + "\r\n";
  1041. }
  1042. }
  1043. placeArr.Clear();
  1044. TeableBookmarkArr.Add("zsinfo", zsinfo); //住宿费详情
  1045. TeableBookmarkArr.Add("hs", dac2.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //伙食费
  1046. TeableBookmarkArr.Add("hsinfo", hsinfo); //伙食费详情
  1047. TeableBookmarkArr.Add("gz", dac3.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //公杂费
  1048. TeableBookmarkArr.Add("gzinfo", gzinfo); //公杂费详情
  1049. string otherFeestr = "";
  1050. decimal otherFee = 0.00M;
  1051. if (_EnterExitCosts.Visa > 0.00M)
  1052. {
  1053. otherFee += _EnterExitCosts.Visa;
  1054. otherFeestr += $"签证费{_EnterExitCosts.Visa.ToString("#0.00")}元、";
  1055. }
  1056. if (_EnterExitCosts.YiMiao > 0.00M)
  1057. {
  1058. otherFee += _EnterExitCosts.YiMiao;
  1059. otherFeestr += $"疫苗费{_EnterExitCosts.YiMiao.ToString("#0.00")}元、";
  1060. }
  1061. if (_EnterExitCosts.HeSuan > 0.00M)
  1062. {
  1063. otherFee += _EnterExitCosts.HeSuan;
  1064. otherFeestr += $"核酸检测费{_EnterExitCosts.HeSuan.ToString("#0.00")}元、";
  1065. }
  1066. if (_EnterExitCosts.Safe > 0.00M)
  1067. {
  1068. otherFee += _EnterExitCosts.Safe;
  1069. otherFeestr += $"保险费{_EnterExitCosts.Safe.ToString("#0.00")}元、";
  1070. }
  1071. if (_EnterExitCosts.Ticket > 0.00M)
  1072. {
  1073. otherFee += _EnterExitCosts.Ticket;
  1074. otherFeestr += $"参展门票费{_EnterExitCosts.Ticket.ToString("#0.00")}元、";
  1075. }
  1076. if (otherFeestr.Length > 0)
  1077. {
  1078. otherFeestr = otherFeestr.Substring(0, otherFeestr.Length - 1);
  1079. otherFeestr += "等费用";
  1080. }
  1081. TeableBookmarkArr.Add("qt", otherFee.ToString("#0.00") + " 元");//其他费用
  1082. TeableBookmarkArr.Add("qtinfo", otherFeestr);//其他费用第二列
  1083. TeableBookmarkArr.Add("fw", _EnterExitCosts.Service.ToString("#0.00") + "元/人");//服务费
  1084. TeableBookmarkArr.Add("AllPrice", AllPrice.ToString("#0.00") + "元/人");//表格合计费用
  1085. string airStr = client.AirType == 460 ? "经济舱" : client.AirType == 458 ? "公务舱" : "";
  1086. TeableBookmarkArr.Add("title", $"费用清单-{airStr}{firstName})");
  1087. foreach (var book in TeableBookmarkArr.Keys)
  1088. {
  1089. if (doc.Range.Bookmarks[book] != null)
  1090. {
  1091. Bookmark mark = doc.Range.Bookmarks[book];
  1092. mark.Text = TeableBookmarkArr[book];
  1093. }
  1094. }
  1095. if (i != ClientItemList.Count - 1)
  1096. {
  1097. builder.PageSetup.Orientation = Aspose.Words.Orientation.Portrait;
  1098. Aspose.Words.Tables.Table table = (Aspose.Words.Tables.Table)doc.GetChild(NodeType.Table, 0, true);
  1099. table.ParentNode.InsertAfter(paragraph, table);
  1100. var CloneTable = (Aspose.Words.Tables.Table)table.Clone(true);
  1101. table.ParentNode.InsertAfter(CloneTable, paragraph);
  1102. }
  1103. TeableBookmarkArr.Clear();
  1104. }
  1105. bookmarkArr.Add("VisitPrice", WordAllPrice.ToString());//出访费用总额
  1106. bookmarkArr.Add("CnAllPrice", WordAllPrice.ConvertCNYUpper());//出访费用总额中文
  1107. bookmarkArr.Add("namesPrice", UsersTop.TrimEnd('、'));//各人员出访费用 付辰同志出访费用为¥73,604.8元
  1108. foreach (var book in bookmarkArr.Keys)
  1109. {
  1110. if (doc.Range.Bookmarks[book] != null)
  1111. {
  1112. Bookmark mark = doc.Range.Bookmarks[book];
  1113. mark.Text = bookmarkArr[book];
  1114. }
  1115. }
  1116. //MemoryStream outSteam = new MemoryStream();
  1117. string filsPath = AppSettingsHelper.Get("WordBasePath") + $"ForeignReceivables/File/{ClientItem.Key.Replace("\n", "")}.docx";
  1118. //去水印
  1119. new Aspose.Words.License().SetLicense(new MemoryStream(Convert.FromBase64String(AsposeHelper.asposeKey)));
  1120. doc.Save(filsPath);
  1121. filesToZip.Add(filsPath);
  1122. //streams.Add(ClientItem.Key + ".docx", outSteam.ToArray());
  1123. }
  1124. //文件名
  1125. string zipFileName = _DelegationInfo.TeamName + "-收款账单.zip";
  1126. string zipPath = $"ForeignReceivables/File/{_DelegationInfo.TeamName}-收款账单{DateTime.Now.ToString("yyyyMMddHHmmss")}.zip";
  1127. try
  1128. {
  1129. using (var zip = ZipFile.Open(AppSettingsHelper.Get("WordBasePath") + zipPath, ZipArchiveMode.Create))
  1130. {
  1131. foreach (var file in filesToZip)
  1132. {
  1133. zip.CreateEntryFromFile(file, Path.GetFileName(file));
  1134. }
  1135. }
  1136. }
  1137. catch (Exception ex)
  1138. {
  1139. return Ok(JsonView(false, ex.Message));
  1140. }
  1141. string url = AppSettingsHelper.Get("WordBaseUrl") + $"Office/Word/{zipPath}";
  1142. return Ok(JsonView(true, "成功", new { Url = url }));
  1143. }
  1144. return Ok(JsonView(false, "操作失败!"));
  1145. }
  1146. catch (Exception ex)
  1147. {
  1148. return Ok(JsonView(false, ex.Message));
  1149. }
  1150. }
  1151. private void SetCells(Aspose.Words.Tables.Table table, Document doc, int rows, int cells, string val)
  1152. {
  1153. //获取table中的某个单元格,从0开始
  1154. Aspose.Words.Tables.Cell lshCell = table.Rows[rows].Cells[cells];
  1155. //将单元格中的第一个段落移除
  1156. lshCell.FirstParagraph.Remove();
  1157. //新建一个段落
  1158. Paragraph p = new Paragraph(doc);
  1159. var r = new Run(doc, val);
  1160. r.Font.Size = 8;
  1161. //把设置的值赋给之前新建的段落
  1162. p.AppendChild(r);
  1163. //将此段落加到单元格内
  1164. lshCell.AppendChild(p);
  1165. }
  1166. #endregion
  1167. #region 已收款项
  1168. /// <summary>
  1169. /// 已收款项
  1170. /// 查询
  1171. /// </summary>
  1172. /// <param name="dto"></param>
  1173. /// <returns></returns>
  1174. [HttpPost]
  1175. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1176. public async Task<IActionResult> PostAmountReceived(AmountReceivedDto dto)
  1177. {
  1178. try
  1179. {
  1180. if (dto == null)
  1181. {
  1182. return Ok(JsonView(false, "参数不能为空!"));
  1183. }
  1184. Result ffrData = await _proceedsReceivedRep.PostAmountReceived(dto.DiId);
  1185. dynamic data = null;
  1186. if (dto.PortType == 1)
  1187. {
  1188. if (ffrData.Code != 0)
  1189. {
  1190. return Ok(JsonView(false, ffrData.Msg));
  1191. }
  1192. data = ffrData.Data;
  1193. }
  1194. else if (dto.PortType == 2)
  1195. {
  1196. if (ffrData.Code != 0)
  1197. {
  1198. return Ok(JsonView(false, ffrData.Msg));
  1199. }
  1200. data = ffrData.Data;
  1201. }
  1202. else if (dto.PortType == 2)
  1203. {
  1204. if (ffrData.Code != 0)
  1205. {
  1206. return Ok(JsonView(false, ffrData.Msg));
  1207. }
  1208. data = ffrData.Data;
  1209. }
  1210. else
  1211. {
  1212. return Ok(JsonView(false, "请选择正确的端口号!"));
  1213. }
  1214. return Ok(JsonView(true, "操作成功!", data));
  1215. }
  1216. catch (Exception ex)
  1217. {
  1218. return Ok(JsonView(false, ex.Message));
  1219. }
  1220. }
  1221. /// <summary>
  1222. /// 已收款项
  1223. /// Add Or Edit
  1224. /// </summary>
  1225. /// <param name="dto"></param>
  1226. /// <returns></returns>
  1227. [HttpPost]
  1228. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1229. public async Task<IActionResult> PostAmountReceivedAddOrEdit(AmountReceivedAddOrEditDto dto)
  1230. {
  1231. try
  1232. {
  1233. if (dto == null)
  1234. {
  1235. return Ok(JsonView(false, "参数不能为空!"));
  1236. }
  1237. Result ffrData = await _proceedsReceivedRep.PostAmountReceivedAddOrEditDto(dto);
  1238. if (dto.PortType == 1)
  1239. {
  1240. if (ffrData.Code != 0)
  1241. {
  1242. return Ok(JsonView(false, ffrData.Msg));
  1243. }
  1244. }
  1245. else if (dto.PortType == 2)
  1246. {
  1247. if (ffrData.Code != 0)
  1248. {
  1249. return Ok(JsonView(false, ffrData.Msg));
  1250. }
  1251. }
  1252. else if (dto.PortType == 2)
  1253. {
  1254. if (ffrData.Code != 0)
  1255. {
  1256. return Ok(JsonView(false, ffrData.Msg));
  1257. }
  1258. }
  1259. else
  1260. {
  1261. return Ok(JsonView(false, "请选择正确的端口号!"));
  1262. }
  1263. return Ok(JsonView(true, "操作成功!"));
  1264. }
  1265. catch (Exception ex)
  1266. {
  1267. return Ok(JsonView(false, ex.Message));
  1268. }
  1269. }
  1270. /// <summary>
  1271. /// 已收款项
  1272. /// Del
  1273. /// </summary>
  1274. /// <param name="dto"></param>
  1275. /// <returns></returns>
  1276. [HttpPost]
  1277. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1278. public async Task<IActionResult> PostAmountReceived_Del(AmountReceivedDelDto dto)
  1279. {
  1280. try
  1281. {
  1282. if (dto == null)
  1283. {
  1284. return Ok(JsonView(false, "参数不能为空!"));
  1285. }
  1286. Result ffrData = await _proceedsReceivedRep.PostAmountReceivedDel(dto);
  1287. if (ffrData.Code != 0)
  1288. {
  1289. return Ok(JsonView(false, ffrData.Msg));
  1290. }
  1291. return Ok(JsonView(true, "操作成功!"));
  1292. }
  1293. catch (Exception ex)
  1294. {
  1295. return Ok(JsonView(false, ex.Message));
  1296. }
  1297. }
  1298. #endregion
  1299. #region 收款退还与其他款项 --> 收款退还
  1300. /// <summary>
  1301. /// 收款退还与其他款项
  1302. /// 查询 根据团组Id
  1303. /// </summary>
  1304. /// <param name="dto"></param>
  1305. /// <returns></returns>
  1306. [HttpPost]
  1307. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1308. public async Task<IActionResult> PostPaymentRefundAndOtherMoneyItemByDiId(PaymentRefundAndOtherMoneyItemByDiIdDto dto)
  1309. {
  1310. try
  1311. {
  1312. if (dto == null) return Ok(JsonView(false, "参数不能为空!"));
  1313. if (dto.PageId <= 0) return Ok(JsonView(false, "请传入正确的的页面Id!"));
  1314. if (dto.UserId <= 0) return Ok(JsonView(false, "请传入正确的的员工Id!"));
  1315. #region 页面功能权限处理
  1316. PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
  1317. pageFunAuth = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
  1318. if (pageFunAuth.CheckAuth == 0)
  1319. {
  1320. return Ok(JsonView(false, "您没有当前页面查询权限!"));
  1321. }
  1322. #endregion
  1323. Result _result = await _paymentRefundAndOtherMoneyRep._ItemByDiId(dto.DiId);
  1324. if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //1 Web 2 Android 3 Ios
  1325. {
  1326. if (_result.Code != 0)
  1327. {
  1328. return Ok(JsonView(false, _result.Msg));
  1329. }
  1330. var data = new
  1331. {
  1332. PageFuncAuth = pageFunAuth,
  1333. Data = _result.Data
  1334. };
  1335. return Ok(JsonView(true, "操作成功!", data));
  1336. }
  1337. else
  1338. {
  1339. return Ok(JsonView(false, "请输入正确的端口号! 1 Web 2 Android 3 Ios;"));
  1340. }
  1341. }
  1342. catch (Exception ex)
  1343. {
  1344. return Ok(JsonView(false, ex.Message));
  1345. }
  1346. }
  1347. /// <summary>
  1348. /// 收款退还与其他款项
  1349. /// 删除
  1350. /// </summary>
  1351. /// <param name="dto"></param>
  1352. /// <returns></returns>
  1353. [HttpPost]
  1354. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1355. public async Task<IActionResult> PostPaymentRefundAndOtherMoneyDel(PaymentRefundAndOtherMoneyDelDto dto)
  1356. {
  1357. try
  1358. {
  1359. if (dto == null) return Ok(JsonView(false, "参数不能为空!"));
  1360. if (dto.PageId <= 0) return Ok(JsonView(false, "请传入正确的的页面Id!"));
  1361. if (dto.UserId <= 0) return Ok(JsonView(false, "请传入正确的的员工Id!"));
  1362. PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
  1363. #region 页面功能权限处理
  1364. pageFunAuth = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
  1365. #endregion
  1366. if (pageFunAuth.DeleteAuth == 0)
  1367. {
  1368. return Ok(JsonView(false, "您没有当前页面删除权限!"));
  1369. }
  1370. Result _result = await _paymentRefundAndOtherMoneyRep._Del(dto);
  1371. if (_result.Code != 0)
  1372. {
  1373. return Ok(JsonView(false, _result.Msg));
  1374. }
  1375. return Ok(JsonView(true, "操作成功!"));
  1376. }
  1377. catch (Exception ex)
  1378. {
  1379. return Ok(JsonView(false, ex.Message));
  1380. }
  1381. }
  1382. /// <summary>
  1383. /// 收款退还与其他款项
  1384. /// Info Data Source
  1385. /// </summary>
  1386. /// <param name="dto"></param>
  1387. /// <returns></returns>
  1388. [HttpPost]
  1389. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1390. public async Task<IActionResult> PostPaymentRefundAndOtherMoneyInfoDataSource(PortDtoBase dto)
  1391. {
  1392. try
  1393. {
  1394. Result _result = await _paymentRefundAndOtherMoneyRep._InfoDataSource(dto);
  1395. if (_result.Code != 0)
  1396. {
  1397. return Ok(JsonView(false, _result.Msg));
  1398. }
  1399. return Ok(JsonView(true, "查询成功!", _result.Data));
  1400. }
  1401. catch (Exception ex)
  1402. {
  1403. return Ok(JsonView(false, ex.Message));
  1404. }
  1405. }
  1406. /// <summary>
  1407. /// 收款退还与其他款项
  1408. /// Info
  1409. /// </summary>
  1410. /// <param name="dto"></param>
  1411. /// <returns></returns>
  1412. [HttpPost]
  1413. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1414. public async Task<IActionResult> PostPaymentRefundAndOtherMoneyInfo(PaymentRefundAndOtherMoneyInfoDto dto)
  1415. {
  1416. try
  1417. {
  1418. if (dto == null)
  1419. {
  1420. return Ok(JsonView(false, "参数不能为空!"));
  1421. }
  1422. Result _result = await _paymentRefundAndOtherMoneyRep._Info(dto);
  1423. if (_result.Code != 0)
  1424. {
  1425. return Ok(JsonView(false, _result.Msg));
  1426. }
  1427. return Ok(JsonView(true, "查询成功!", _result.Data));
  1428. }
  1429. catch (Exception ex)
  1430. {
  1431. return Ok(JsonView(false, ex.Message));
  1432. }
  1433. }
  1434. /// <summary>
  1435. /// 收款退还与其他款项 --> 收款退还(只保留人名币)
  1436. /// 操作(Add Or Edit)
  1437. /// </summary>
  1438. /// <param name="dto"></param>
  1439. /// <returns></returns>
  1440. [HttpPost]
  1441. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1442. public async Task<IActionResult> PostPaymentRefundAndOtherMoneyAddOrEdit(PaymentRefundAndOtherMoneyAddOrEditDto dto)
  1443. {
  1444. try
  1445. {
  1446. if (dto == null) return Ok(JsonView(false, "参数不能为空!"));
  1447. if (dto.PageId <= 0) return Ok(JsonView(false, "请传入正确的的页面Id!"));
  1448. if (dto.UserId <= 0) return Ok(JsonView(false, "请传入正确的的员工Id!"));
  1449. #region 页面功能权限处理
  1450. PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
  1451. pageFunAuth = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
  1452. #endregion
  1453. if (dto.Status == 1) //add
  1454. {
  1455. if (pageFunAuth.AddAuth == 0)
  1456. {
  1457. return Ok(JsonView(false, "您没有当前页面添加权限!"));
  1458. }
  1459. }
  1460. else if (dto.Status == 2) //edit
  1461. {
  1462. if (pageFunAuth.EditAuth == 0)
  1463. {
  1464. return Ok(JsonView(false, "您没有当前页面编辑权限!"));
  1465. }
  1466. }
  1467. else
  1468. {
  1469. return Ok(JsonView(false, "请输入正确的操作状态! 1 添加 2 修改!"));
  1470. }
  1471. Result _result = await _paymentRefundAndOtherMoneyRep._AddOrEdit(dto);
  1472. if (_result.Code != 0)
  1473. {
  1474. return Ok(JsonView(false, _result.Msg));
  1475. }
  1476. return Ok(JsonView(true, "操作成功!"));
  1477. }
  1478. catch (Exception ex)
  1479. {
  1480. return Ok(JsonView(false, ex.Message));
  1481. }
  1482. }
  1483. #endregion
  1484. #region 应收报表
  1485. /// <summary>
  1486. /// 应收报表
  1487. /// 查询 根据日期范围
  1488. /// </summary>
  1489. /// <param name="dto"></param>
  1490. /// <returns></returns>
  1491. [HttpPost]
  1492. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1493. public async Task<IActionResult> PostSyntheticalReceivableByDateRange(PostSyntheticalReceivableByDateRangeDto dto)
  1494. {
  1495. string sqlWhere = " Where di.IsDel=0 ";
  1496. #region 验证
  1497. if (string.IsNullOrEmpty(dto.beginDt) && string.IsNullOrEmpty(dto.endDt))
  1498. {
  1499. return Ok(JsonView(false, "日期参数至少填写一个!"));
  1500. }
  1501. if (!string.IsNullOrEmpty(dto.beginDt))
  1502. {
  1503. if (Regex.Match(dto.beginDt, @"^\d{4}-\d{2}-\d{2}$").Value.Length < 1)
  1504. {
  1505. return Ok(JsonView(false, "日期参数格式错误,应为yyyy-MM-dd!"));
  1506. }
  1507. else
  1508. {
  1509. sqlWhere += string.Format(@" And di.VisitDate >= '{0} 00:00:00' ", dto.beginDt);
  1510. }
  1511. }
  1512. if (!string.IsNullOrEmpty(dto.endDt))
  1513. {
  1514. if (Regex.Match(dto.endDt, @"^\d{4}-\d{2}-\d{2}$").Value.Length < 1)
  1515. {
  1516. return Ok(JsonView(false, "日期参数格式错误,应为yyyy-MM-dd!"));
  1517. }
  1518. else
  1519. {
  1520. sqlWhere += string.Format(@" And di.VisitDate <= '{0} 23:59:59' ", dto.endDt);
  1521. }
  1522. }
  1523. #endregion
  1524. //已收款项 判断如果是市场部的人员进来的话 只显示自己的 其他的都显示全部的
  1525. string userSqlWhere = "";
  1526. var userInfos = await _sqlSugar.Queryable<Sys_Users>()
  1527. .InnerJoin<Sys_Department>((u, d) => u.DepId == d.Id)
  1528. .Where((u, d) => u.IsDel == 0 && d.DepName.Contains("市场部") && u.Id == dto.CurrUserId)
  1529. .ToListAsync();
  1530. if (userInfos.Count > 0) userSqlWhere = string.Format(@$" And JietuanOperator={dto.CurrUserId} ");
  1531. //排序倒序
  1532. string sql = string.Format(@$"select distinct fr.diid,di.TeamName,di.ClientUnit,di.VisitDate,di.CreateTime from Fin_ForeignReceivables fr join Grp_DelegationInfo di on fr.DIID = di.id {sqlWhere} {userSqlWhere} Order By di.VisitDate Desc");
  1533. List<PostSyntheticalReceivableByDateRangeView> list_rst = _sqlSugar.SqlQueryable<PostSyntheticalReceivableByDateRangeView>(sql).ToList();
  1534. var setData = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
  1535. decimal sumAll_fr = 0M; //应收
  1536. decimal sumAll_pr = 0M; //已收
  1537. decimal sumAll_balance = 0M; //尾款
  1538. if (list_rst.Count > 0)
  1539. {
  1540. int rowNumber = 1;
  1541. foreach (var item_rst in list_rst)
  1542. {
  1543. DateTime dtTemp;
  1544. bool b = DateTime.TryParse(item_rst.visitDate, out dtTemp);
  1545. if (b)
  1546. {
  1547. item_rst.visitDate = dtTemp.ToString("yyyy-MM-dd");
  1548. }
  1549. item_rst.No = rowNumber;
  1550. rowNumber++;
  1551. int diId = item_rst.diid;
  1552. decimal sum_fr = 0M;
  1553. decimal sum_pr = 0M;
  1554. string str_client = string.Empty;
  1555. decimal sum_refund = 0M; //收款退还
  1556. decimal sum_extra = 0M; //超支费用
  1557. decimal balance = 0M;
  1558. string str_schedule = string.Empty;
  1559. //1.应收
  1560. string sql_fr = string.Format(@" Select * From Fin_ForeignReceivables Where IsDel=0 And Diid={0} ", diId);
  1561. List<Fin_ForeignReceivables> list_fr = _sqlSugar.SqlQueryable<Fin_ForeignReceivables>(sql_fr).ToList();
  1562. sum_fr = list_fr.Sum(s => s.ItemSumPrice);
  1563. //2.已收
  1564. string sql_pr = string.Format(@" Select * From Fin_ProceedsReceived Where IsDel=0 And Diid={0} ", diId);
  1565. List<Fin_ProceedsReceived> list_pr = _sqlSugar.SqlQueryable<Fin_ProceedsReceived>(sql_pr).ToList();
  1566. foreach (var item_pr in list_pr)
  1567. {
  1568. sum_pr += item_pr.Price;
  1569. str_client += string.Format(@"{0};", item_pr.Client);
  1570. str_schedule += string.Format(@"{0};", item_pr.Remark);
  1571. }
  1572. if (str_schedule.Length > 0)
  1573. {
  1574. str_schedule = str_schedule.TrimEnd(';');
  1575. }
  1576. if (str_client.Length > 0)
  1577. {
  1578. str_client = str_client.TrimEnd(';');
  1579. }
  1580. //3.收款退还
  1581. string sql_other = string.Format(@"Select * From Fin_PaymentRefundAndOtherMoney prao
  1582. Inner Join Grp_CreditCardPayment ccp On prao.Id = ccp.CId
  1583. Where ccp.CTable = 285 And ccp.IsPay = 1 And prao.IsDel = 0 And ccp.IsDel = 0 And prao.DiId = {0}", diId);
  1584. List<Grp_CreditCardPayment> list_other = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_other).ToList();
  1585. sum_refund = list_other.Sum(s => s.RMBPrice);
  1586. //4.超支
  1587. string sql_extra = string.Format(@" Select c.* From Fin_GroupExtraCost f
  1588. Inner join Grp_CreditCardPayment c On f.Id = c.CId
  1589. Where c.CTable = 1015 And c.IsPay = 1 And f.IsDel = 0 And c.IsDel = 0 And f.DiId = {0} ", diId);
  1590. List<Grp_CreditCardPayment> list_extra = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_extra).ToList();
  1591. sum_extra = list_extra.Sum(s => s.RMBPrice);
  1592. item_rst.frPrice = sum_fr.ToString("#0.00");
  1593. item_rst.extraPrice = sum_extra.ToString("#0.00");
  1594. item_rst.receivableTotal = (sum_fr + sum_extra).ToString("#0.00");
  1595. item_rst.prPrice = sum_pr.ToString("#0.00");
  1596. item_rst.refundAmount = sum_refund.ToString("#0.00");
  1597. item_rst.receivedTotal = (sum_pr - sum_refund).ToString("#0.00");
  1598. item_rst.balPrice = ((sum_fr + sum_extra) - (sum_pr - sum_refund)).ToString("#0.00");
  1599. item_rst.prClient = str_client;
  1600. item_rst.schedule = str_schedule;
  1601. string tempVisitDate = Convert.ToDateTime(item_rst.visitDate).ToString("yyyy-MM-dd");
  1602. sumAll_fr += (sum_fr + sum_extra);
  1603. sumAll_pr += (sum_pr - sum_refund);
  1604. sumAll_balance += ((sum_fr + sum_extra) - (sum_pr - sum_refund));
  1605. #region 单位应收已收细项(以应收费用名称为主去(已收费用)匹配) 新增 雷怡 2024-35-08 16:35:28
  1606. List<ClientFeeInfoView> feeDatas = new List<ClientFeeInfoView>();
  1607. //匹配上的数据
  1608. foreach (var item in list_fr)
  1609. {
  1610. var prInfo = list_pr.Find(it => item.PriceName.Contains(it.Client) || item.PriceName.Equals(it.Client));
  1611. decimal _balancePayment = 0.00M;
  1612. if (item.Currency == prInfo?.Currency)
  1613. {
  1614. _balancePayment = item.ItemSumPrice - prInfo?.Price ?? 0.00M;
  1615. }
  1616. feeDatas.Add(new ClientFeeInfoView
  1617. {
  1618. client = item.PriceName,
  1619. frMoney = item.ItemSumPrice.ToString("#0.00"),
  1620. frCurrency = setData.Find(it => it.Id == item.Currency)?.Name ?? "-",
  1621. frRate = item.Rate.ToString("#0.0000"),
  1622. prReceivablesType = setData.Find(it => it.Id == prInfo?.ReceivablesType)?.Name ?? "",
  1623. prTime = prInfo?.SectionTime ?? "-",
  1624. prMoney = prInfo?.Price.ToString("#0.00") ?? "-",
  1625. prCurrency = setData.Find(it => it.Id == prInfo?.Currency)?.Name ?? "",
  1626. balPayment = _balancePayment.ToString("#0.00")
  1627. });
  1628. }
  1629. //未匹配上的数据
  1630. foreach (var item in list_pr)
  1631. {
  1632. var frInfo = list_fr.Find(it => it.PriceName.Contains(item.Client) || it.PriceName.Equals(item.Client));
  1633. if (frInfo == null)
  1634. {
  1635. feeDatas.Add(new ClientFeeInfoView
  1636. {
  1637. client = item.Client + "[未匹配上的已收数据(应收已收公司名称不一致)]",
  1638. frMoney = "0.00",
  1639. frCurrency = "-",
  1640. frRate = "0.0000",
  1641. prReceivablesType = setData.Find(it => it.Id == item?.ReceivablesType)?.Name ?? "",
  1642. prTime = item?.SectionTime ?? "-",
  1643. prMoney = item?.Price.ToString("#0.00") ?? "0.00",
  1644. prCurrency = setData.Find(it => it.Id == item?.Currency)?.Name ?? "",
  1645. balPayment = "0.00"
  1646. });
  1647. }
  1648. }
  1649. item_rst.feeItem = feeDatas;
  1650. #endregion
  1651. }
  1652. PostSyntheticalReceivableByDateRangeResultView result = new PostSyntheticalReceivableByDateRangeResultView();
  1653. result.total_fr = sumAll_fr.ToString("#0.00");
  1654. result.total_pr = sumAll_pr.ToString("#0.00");
  1655. result.total_balance = sumAll_balance.ToString("#0.00");
  1656. result.dataList = new List<PostSyntheticalReceivableByDateRangeView>(list_rst);
  1657. if (dto.requestType == 1)
  1658. {
  1659. return Ok(JsonView(true, "请求成功", result, list_rst.Count));
  1660. }
  1661. else
  1662. {
  1663. //----------------------------
  1664. List<Excel_SyntheticalReceivableByDateRange> list_Ex = new List<Excel_SyntheticalReceivableByDateRange>();
  1665. WorkbookDesigner designer = new WorkbookDesigner();
  1666. designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/应收款项模板 - 副本.xls");
  1667. int excNo = 1;
  1668. foreach (var item in list_rst)
  1669. {
  1670. Excel_SyntheticalReceivableByDateRange exc = new Excel_SyntheticalReceivableByDateRange();
  1671. exc.No = excNo.ToString();
  1672. excNo++;
  1673. exc.TeamName = item.teamName;
  1674. exc.ClientUnit = item.clientUnit;
  1675. exc.VisitDate = item.visitDate;
  1676. exc.Accounts = item.frPrice;
  1677. exc.Extra = item.extraPrice;
  1678. exc.ReceivableTotal = item.receivableTotal;
  1679. exc.Received = item.prPrice;
  1680. exc.RefundAmount = item.refundAmount;
  1681. exc.ReceivedTotal = item.receivedTotal;
  1682. exc.Balance = item.balPrice;
  1683. exc.Collection = item.schedule;
  1684. DateTime time = Convert.ToDateTime(item.visitDate);
  1685. TimeSpan ts = DateTime.Now - time;
  1686. float SY = float.Parse(item.balPrice);
  1687. if (ts.Days >= 365 && SY > 0)
  1688. {
  1689. exc.Sign = "需收款";
  1690. }
  1691. else
  1692. {
  1693. exc.Sign = "";
  1694. }
  1695. list_Ex.Add(exc);
  1696. }
  1697. var dt = CommonFun.GetDataTableFromIList(list_Ex);
  1698. dt.TableName = "Excel_SyntheticalReceivableByDateRange";
  1699. if (dt != null)
  1700. {
  1701. designer.SetDataSource("SumPrice", "应收合计:" + result.total_fr + "RMB 已收合计:" + result.total_pr + "RMB 余款合计:" + result.total_balance + "RMB");
  1702. //数据源
  1703. designer.SetDataSource(dt);
  1704. //根据数据源处理生成报表内容
  1705. designer.Process();
  1706. string fileName = ("Receivable/应收款项(" + dto.beginDt + "~" + dto.endDt + ").xlsx");
  1707. designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
  1708. string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
  1709. return Ok(JsonView(true, "成功", new { url = rst }));
  1710. }
  1711. }
  1712. }
  1713. return Ok(JsonView(true, "获取成功", "", list_rst.Count));
  1714. }
  1715. #endregion
  1716. #region 付款申请
  1717. /// <summary>
  1718. /// 付款申请
  1719. /// 基础数据
  1720. /// </summary>
  1721. /// <param name="dto"></param>
  1722. /// <returns></returns>
  1723. [HttpPost]
  1724. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1725. public async Task<IActionResult> PostPayRequestInit()
  1726. {
  1727. try
  1728. {
  1729. var conpanyDatas = _sqlSugar.Queryable<Sys_Company>()
  1730. .Where(it => it.IsDel == 0)
  1731. .Select(it => new
  1732. {
  1733. Id = it.Id,
  1734. ConpamyName = it.CompanyName
  1735. }).ToList();
  1736. return Ok(JsonView(true, "操作成功!", new { ConpanyData = conpanyDatas }));
  1737. }
  1738. catch (Exception ex)
  1739. {
  1740. return Ok(JsonView(false, ex.Message));
  1741. }
  1742. }
  1743. /// <summary>
  1744. /// 付款申请 (PageId=51)
  1745. /// 查询 根据日期范围
  1746. /// </summary>
  1747. /// <param name="dto"></param>
  1748. /// <returns></returns>
  1749. [HttpPost]
  1750. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  1751. public async Task<IActionResult> PostPayRequest_Center(PostPayRequestByDateRangeDto dto)
  1752. {
  1753. Stopwatch stopwatch = Stopwatch.StartNew();
  1754. #region 验证
  1755. DateTime beginDt, endDt;
  1756. string format = "yyyy-MM-dd";
  1757. if (!DateTime.TryParseExact(dto.beginDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out beginDt))
  1758. {
  1759. return Ok(JsonView(false, "开始日期格式不正确!正确格式:yyyy-MM-dd"));
  1760. }
  1761. if (!DateTime.TryParseExact(dto.endDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt))
  1762. {
  1763. return Ok(JsonView(false, "结束日期格式不正确!正确格式:yyyy-MM-dd"));
  1764. }
  1765. #region 页面操作权限验证
  1766. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  1767. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
  1768. if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限!"));
  1769. #endregion
  1770. #endregion
  1771. try
  1772. {
  1773. PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
  1774. var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
  1775. if (checkedStr != null)
  1776. {
  1777. checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
  1778. }
  1779. tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(dto.Status, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
  1780. tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(dto.Status, checkedView.GroupIds, checkedView.HotelSubIds, dto.beginDt, dto.endDt);
  1781. stopwatch.Stop();
  1782. return Ok(JsonView(true, $"查询成功!耗时{stopwatch.ElapsedMilliseconds / 1000}s", new { daily = dailyResult, group = groupResult }));
  1783. }
  1784. catch (Exception ex)
  1785. {
  1786. return Ok(JsonView(false, ex.Message));
  1787. }
  1788. }
  1789. /// <summary>
  1790. /// 根据团组类型类型处理团组费用所属公司
  1791. /// </summary>
  1792. /// <param name="teamId"></param>
  1793. /// <returns></returns>
  1794. private CompanyInfo ExpenseCompanyByTeamId(int teamId)
  1795. {
  1796. CompanyInfo _companyInfo = new CompanyInfo();
  1797. List<int> _SiChuan = new List<int>() {
  1798. 38 , // 政府团
  1799. 39 , // 企业团
  1800. 40 , // 散客团
  1801. 102, // 未知
  1802. 248, // 非团组
  1803. 691, // 四川-会务活动
  1804. 762, // 四川-赛事项目收入
  1805. };
  1806. List<int> _ChengDu = new List<int>() {
  1807. 302, // 成都-会务活动
  1808. 1047, // 成都-赛事项目收入
  1809. };
  1810. if (_SiChuan.Contains(teamId))
  1811. {
  1812. _companyInfo.Id = 2;
  1813. _companyInfo.ConpanyName = "四川泛美交流有限公司";
  1814. }
  1815. if (_ChengDu.Contains(teamId))
  1816. {
  1817. _companyInfo.Id = 1;
  1818. _companyInfo.ConpanyName = "成都泛美商务有限公司";
  1819. }
  1820. return _companyInfo;
  1821. }
  1822. /// <summary>
  1823. /// 付款申请(团组费用申请相关)
  1824. /// 查询 根据日期范围
  1825. /// </summary>
  1826. /// <param name="_groupIds"></param>
  1827. /// <param name="beginDt"></param>
  1828. /// <param name="endDt"></param>
  1829. /// <returns></returns>
  1830. private tree_Group_DailyFeePaymentResult PayRequest_GroupPaymentByDateRange(int status, List<int> _groupIds,List<int> _hotelSubIds, string beginDt, string endDt)
  1831. {
  1832. tree_Group_DailyFeePaymentResult _DailyFeePaymentResult = new tree_Group_DailyFeePaymentResult();
  1833. List<tree_Group_DailyFeePaymentPageListView> dataList = new List<tree_Group_DailyFeePaymentPageListView>();
  1834. #region sql条件处理
  1835. string sqlWhere = string.Format(@" And (AuditGMDate Between '{0} 00:00:00' And '{1} 23:59:59') ", beginDt, endDt);
  1836. if (status == 2)
  1837. {
  1838. if (_hotelSubIds.Count > 1)
  1839. {
  1840. var hrIds = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => _hotelSubIds.Contains(it.Id)).Select(it => it.HrId).Distinct().ToList();
  1841. _groupIds = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(it => it.CTable == 76 && hrIds.Contains(it.CId)).Select(it => it.Id).ToList();
  1842. }
  1843. if (_groupIds.Count < 1)
  1844. {
  1845. _DailyFeePaymentResult.dataList = new List<tree_Group_DailyFeePaymentPageListView>();
  1846. return _DailyFeePaymentResult;
  1847. }
  1848. sqlWhere += string.Format(@" And Id In ({0})", string.Join(",", _groupIds));
  1849. }
  1850. string sql_1 = string.Format(@"Select * From Grp_CreditCardPayment Where IsDel = 0 And IsPay = 0 And IsAuditGM = 1 {0}", sqlWhere);
  1851. #endregion
  1852. var _paymentDatas = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_1).ToList();//付款信息
  1853. _DailyFeePaymentResult.gz = _paymentDatas.Where(it => it.OrbitalPrivateTransfer == 0).Sum(it => ((it.PayMoney * it.DayRate) / 100) * it.PayPercentage); //公转
  1854. _DailyFeePaymentResult.sz = _paymentDatas.Where(it => it.OrbitalPrivateTransfer == 1).Sum(it => ((it.PayMoney * it.DayRate) / 100) * it.PayPercentage); ; //私转
  1855. List<int> groupIds = _paymentDatas.Select(it => it.DIId).Distinct().ToList();
  1856. var _groupDatas = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Id)).ToList();
  1857. //_groupDatas = (List<Grp_DelegationInfo>)_groupDatas.GroupBy(it => it.TeamDid);
  1858. #region 相关基础数据源
  1859. var userDatas = _sqlSugar.Queryable<Sys_Users>().ToList();
  1860. var setDatas = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
  1861. var countryFeeDatas = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();
  1862. var hotelDatas = _sqlSugar.Queryable<Grp_HotelReservations>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
  1863. var hotelContentDatas = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.IsDel == 0 && it.IsPay == 0 && groupIds.Contains(it.DiId)).ToList();
  1864. var opDatas = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
  1865. var visaDatas = _sqlSugar.Queryable<Grp_VisaInfo>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DIId)).ToList();
  1866. var ioaDatas = _sqlSugar.Queryable<Grp_InvitationOfficialActivities>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
  1867. var insureDatas = _sqlSugar.Queryable<Grp_Customers>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
  1868. var airDatas = _sqlSugar.Queryable<Grp_AirTicketReservations>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DIId)).ToList();
  1869. //var otherMoneyDatas = _sqlSugar.Queryable<Fin_OtherPrice>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Diid)).ToList();
  1870. var otherMoneyDatas = _sqlSugar.Queryable<Grp_DecreasePayments>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
  1871. var refundPaymentDatas = _sqlSugar.Queryable<Fin_PaymentRefundAndOtherMoney>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
  1872. var ExtraCostDatas = _sqlSugar.Queryable<Fin_GroupExtraCost>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
  1873. #endregion
  1874. //Expense company
  1875. foreach (var groupInfo in _groupDatas)
  1876. {
  1877. List<Group_DailyFeePaymentContentInfolView> childList = new List<Group_DailyFeePaymentContentInfolView>();
  1878. var groupPaymentDatas = _paymentDatas.Where(it => groupInfo.Id == it.DIId).ToList();
  1879. int rouNumber = 1;
  1880. foreach (var payInfo in groupPaymentDatas)
  1881. {
  1882. string priName = "-";
  1883. string orbitalPrivateTransfer = payInfo.OrbitalPrivateTransfer == 0 ? "公转" : payInfo.OrbitalPrivateTransfer == 1 ? "私转" : "-";
  1884. switch (payInfo.CTable)
  1885. {
  1886. case 76: //76 酒店预订
  1887. priName = $"[费用名称:{hotelDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.HotelName ?? ""}]";
  1888. break;
  1889. case 79: //79 车/导游地接
  1890. var opData = opDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id);
  1891. if (opData != null)
  1892. {
  1893. string area = "";
  1894. bool b = int.TryParse(opData.Area, out int areaId);
  1895. if (b)
  1896. {
  1897. string area1 = countryFeeDatas.Find(it => it.Id == areaId)?.Country ?? "-";
  1898. area = $"{area1}({setDatas.Find(it => it.Id == opData.PriceType)?.Name ?? "-"})";
  1899. }
  1900. else area = opData.Area;
  1901. string opPriName = "-";
  1902. if (!string.IsNullOrEmpty(opData.PriceName)) opPriName = opData.PriceName;
  1903. area += $"({opPriName})";
  1904. if (payInfo.OrbitalPrivateTransfer == 0) //公转
  1905. {
  1906. priName = $"【{orbitalPrivateTransfer}】【导游: {opData.ServiceGuide} 】[费用名称:{area}]";
  1907. }
  1908. else if (payInfo.OrbitalPrivateTransfer == 1) //私转
  1909. {
  1910. priName = $"【{orbitalPrivateTransfer}】【导游:{opData.ServiceGuide}】[费用名称:{area}]";
  1911. }
  1912. }
  1913. break;
  1914. case 80: // 80 签证
  1915. string sql = string.Format("select b.Id,b.Pinyin,b.lastName,b.firstName,b.phone from Grp_TourClientList a, Crm_DeleClient b where a.clientid = b.id and a.isdel = 0 and a.diid = {0}", groupInfo.Id);
  1916. List<SimplClientInfo> arr = _sqlSugar.SqlQueryable<SimplClientInfo>(sql).ToList();
  1917. string visaClientName = visaDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.VisaClient ?? "";
  1918. string clientName = "-";
  1919. if (Regex.Match(visaClientName, @"\d+,?").Value.Length > 0)
  1920. {
  1921. string[] temparr = visaClientName.Split(',');
  1922. string fistrStr = temparr[0];
  1923. int count = temparr.Count();
  1924. int tempId;
  1925. bool success = int.TryParse(fistrStr, out tempId);
  1926. if (success)
  1927. {
  1928. SimplClientInfo tempInfo = arr.FirstOrDefault(s => s.Id == tempId);
  1929. if (tempInfo != null)
  1930. {
  1931. if (count > 1)
  1932. {
  1933. clientName = string.Format(@"{0}{1}等{2}人", tempInfo.LastName, tempInfo.FirstName, count);
  1934. }
  1935. else
  1936. {
  1937. clientName = string.Format(@"{0}{1}", tempInfo.LastName, tempInfo.FirstName);
  1938. }
  1939. }
  1940. }
  1941. else
  1942. {
  1943. clientName = fistrStr;
  1944. }
  1945. }
  1946. priName = $"[费用名称:{clientName}]";
  1947. break;
  1948. case 81: // 81 邀请/公务活动
  1949. priName = $"[费用名称:{ioaDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.Inviter ?? " -"}]";
  1950. break;
  1951. case 82: // 82 团组客户保险
  1952. priName = $"[费用名称:{insureDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.ClientName ?? " -"}]";
  1953. break;
  1954. case 85: // 85 机票预订
  1955. string flightsCode = airDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.FlightsCode ?? "-";
  1956. string airPayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "-";
  1957. priName = $"{flightsCode}{airPayType}】";
  1958. break;
  1959. case 98: // 98 其他款项
  1960. priName = $"[费用名称:{otherMoneyDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? " -"}]";
  1961. break;
  1962. case 285: // 285 收款退还
  1963. priName = $"[费用名称:{refundPaymentDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? " -"}]";
  1964. break;
  1965. case 1015: // 1015 超支费用
  1966. priName = $"[费用名称:{ExtraCostDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? " -"}]";
  1967. break;
  1968. default:
  1969. priName = "";
  1970. break;
  1971. }
  1972. bool status1 = false;
  1973. if (_groupIds != null)
  1974. {
  1975. status1 = _groupIds.Contains(payInfo.Id);
  1976. }
  1977. if (payInfo.CTable == 76) //酒店单独处理
  1978. {
  1979. var hotelContents = hotelContentDatas.Where(it => it.HrId == payInfo.CId);
  1980. List<Group_DailyFeePaymentContentInfolView> childInfos = new List<Group_DailyFeePaymentContentInfolView>();
  1981. foreach (var hotelContent in hotelContents)
  1982. {
  1983. string subPriceName = "";
  1984. if (hotelContent.PriceType == 1) subPriceName = "房费";
  1985. else if (hotelContent.PriceType == 2) subPriceName = "早餐";
  1986. else if (hotelContent.PriceType == 3) subPriceName = "地税";
  1987. else if (hotelContent.PriceType == 4) subPriceName = "城市税";
  1988. if (string.IsNullOrEmpty(subPriceName)) subPriceName = priName;
  1989. else subPriceName = $"{priName.Replace("]","")}-{subPriceName}]";
  1990. string payeeStr1 = string.Format(@" {0},开户行:{1},银行卡号:{2} ",
  1991. string.IsNullOrEmpty(hotelContent.Payee) ? "-" : hotelContent.Payee,
  1992. string.IsNullOrEmpty(hotelContent.OtherBankName) ? "-" : hotelContent.OtherBankName,
  1993. string.IsNullOrEmpty(hotelContent.OtherSideNo) ? "-" : hotelContent.OtherSideNo);
  1994. decimal _PaymentAmount1 = hotelContent.Price;//此次付款金额
  1995. decimal _CNYSubTotalAmount1 = _PaymentAmount1 * hotelContent.Rate;//此次付款金额
  1996. _CNYSubTotalAmount1 = Convert.ToDecimal(_CNYSubTotalAmount1.ToString("#0.00"));
  1997. //酒店子项Id选中状态更改
  1998. if (_hotelSubIds != null) status1 = _hotelSubIds.Contains(hotelContent.Id);
  1999. var childInfo1 = new Group_DailyFeePaymentContentInfolView()
  2000. {
  2001. IsChecked = status1,
  2002. Id = payInfo.Id,
  2003. HotelSubId = hotelContent.Id,
  2004. Payee = payeeStr1,
  2005. RowNumber = rouNumber,
  2006. Applicant = userDatas.Find(it => it.Id == payInfo.CreateUserId)?.CnName ?? "",
  2007. ApplicantDt = payInfo.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
  2008. PayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "",
  2009. TransferMark = orbitalPrivateTransfer,
  2010. PriceName = subPriceName,
  2011. ModuleName = setDatas.Find(it => it.Id == payInfo.CTable)?.Name ?? "",
  2012. PayCurrCode = setDatas.Find(it => it.Id == hotelContent.Currency)?.Name ?? "",
  2013. PaymentAmount = _PaymentAmount1,
  2014. PayRate = hotelContent.Rate,
  2015. CNYSubTotalAmount = _CNYSubTotalAmount1
  2016. };
  2017. string remaksDescription1 = $"【{childInfo1.PayType}】【{childInfo1.ModuleName}{rouNumber}、[申请人:{childInfo1.Applicant}]{subPriceName}[收款方:{childInfo1.Payee}] {childInfo1.PayCurrCode} {_PaymentAmount1.ToString("#0.00")}、CNY:{childInfo1.CNYSubTotalAmount.ToString("#0.00")}(团组:{groupInfo.TeamName})";
  2018. childInfo1.RemaksDescription = remaksDescription1;
  2019. childInfos.Add(childInfo1);
  2020. rouNumber++;
  2021. }
  2022. childList.AddRange(childInfos);
  2023. }
  2024. else
  2025. {
  2026. string payeeStr = string.Format(@" {0},开户行:{1},银行卡号:{2} ",
  2027. string.IsNullOrEmpty(payInfo.Payee) ? "-" : payInfo.Payee,
  2028. string.IsNullOrEmpty(payInfo.OtherBankName) ? "-" : payInfo.OtherBankName,
  2029. string.IsNullOrEmpty(payInfo.OtherSideNo) ? "-" : payInfo.OtherSideNo);
  2030. decimal _PaymentAmount = (payInfo.PayMoney / 100) * payInfo.PayPercentage;//此次付款金额
  2031. decimal _CNYSubTotalAmount = _PaymentAmount * payInfo.DayRate;//此次付款金额
  2032. _CNYSubTotalAmount = Convert.ToDecimal(_CNYSubTotalAmount.ToString("#0.00"));
  2033. var childInfo = new Group_DailyFeePaymentContentInfolView()
  2034. {
  2035. IsChecked = status1,
  2036. Id = payInfo.Id,
  2037. Payee = payeeStr,
  2038. RowNumber = rouNumber,
  2039. Applicant = userDatas.Find(it => it.Id == payInfo.CreateUserId)?.CnName ?? "",
  2040. ApplicantDt = payInfo.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
  2041. PayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "",
  2042. TransferMark = orbitalPrivateTransfer,
  2043. PriceName = priName,
  2044. ModuleName = setDatas.Find(it => it.Id == payInfo.CTable)?.Name ?? "",
  2045. PayCurrCode = setDatas.Find(it => it.Id == payInfo.PaymentCurrency)?.Name ?? "",
  2046. PaymentAmount = _PaymentAmount,
  2047. PayRate = payInfo.DayRate,
  2048. CNYSubTotalAmount = _CNYSubTotalAmount
  2049. };
  2050. string remaksDescription = $"【{childInfo.PayType}】【{childInfo.ModuleName}{rouNumber}、[申请人:{childInfo.Applicant}]{priName}[收款方:{childInfo.Payee}] {childInfo.PayCurrCode} {_PaymentAmount.ToString("#0.00")}、CNY:{childInfo.CNYSubTotalAmount.ToString("#0.00")}(团组:{groupInfo.TeamName})";
  2051. childInfo.RemaksDescription = remaksDescription;
  2052. childList.Add(childInfo);
  2053. rouNumber++;
  2054. }
  2055. }
  2056. CompanyInfo companyInfo = new CompanyInfo();
  2057. companyInfo = ExpenseCompanyByTeamId(groupInfo.TeamDid);
  2058. dataList.Add(new tree_Group_DailyFeePaymentPageListView()
  2059. {
  2060. Id = Guid.NewGuid().ToString("N"),
  2061. GroupName = groupInfo.TeamName,
  2062. CompanyId = companyInfo.Id,
  2063. ConpanyName = companyInfo.ConpanyName,
  2064. CNYTotalAmount = childList.Sum(it => it.CNYSubTotalAmount),
  2065. ChildList = childList,
  2066. });
  2067. }
  2068. _DailyFeePaymentResult.dataList = dataList;
  2069. return _DailyFeePaymentResult;
  2070. }
  2071. /// <summary>
  2072. /// 付款申请(日付申请相关)
  2073. /// 查询 根据日期范围
  2074. /// </summary>
  2075. /// <param name="_dailyIds"></param>
  2076. /// <param name="beginDt"></param>
  2077. /// <param name="endDt"></param>
  2078. /// <returns></returns>
  2079. private tree_Fin_DailyFeePaymentResult PayRequest_DailyByDateRange(int status, List<int> _dailyIds, string beginDt, string endDt)
  2080. {
  2081. #region sql条件处理
  2082. string sqlWhere = string.Format(@" And dfp.CreateTime between '{0} 00:00:00' And '{1} 23:59:59' ", beginDt, endDt);
  2083. if (status == 2)
  2084. {
  2085. if (_dailyIds.Count < 1)
  2086. {
  2087. return new tree_Fin_DailyFeePaymentResult() { childList = new List<Fin_DailyFeePaymentContentInfolView>() };
  2088. }
  2089. sqlWhere += string.Format(@" And dfp.Id In({0}) ", string.Join(",", _dailyIds));
  2090. }
  2091. string sql_1 = string.Format(@"Select * From (
  2092. Select row_number() over (order by dfp.Id Desc) as RowNumber,
  2093. dfp.Id,dfp.CompanyId,c.CompanyName,dfp.Instructions,dfp.SumPrice,
  2094. dfp.CreateUserId,u.CnName CreateUser,dfp.CreateTime,dfp.FAudit,dfp.MAudit,
  2095. dfp.PriceTypeId,dfp.TransferTypeId
  2096. From Fin_DailyFeePayment dfp
  2097. Inner Join Sys_Company c On dfp.CompanyId = c.Id
  2098. Left Join Sys_Users u On dfp.CreateUserId = u.Id
  2099. Where dfp.IsDel=0 {0} And dfp.FAudit = 1 And dfp.MAudit = 1 And dfp.IsPay = 0
  2100. ) temp ", sqlWhere);
  2101. #endregion
  2102. List<tree_Fin_DailyFeePaymentPageListView> DailyFeePaymentData = _sqlSugar.SqlQueryable<tree_Fin_DailyFeePaymentPageListView>(sql_1).ToList();
  2103. Dictionary<int, string> dic_setData = new Dictionary<int, string>();
  2104. Sys_SetDataType stGZ = _daiRep.Query<Sys_SetDataType>(s => s.Name == "公转").First();
  2105. Sys_SetDataType stSZ = _daiRep.Query<Sys_SetDataType>(s => s.Name == "私转").First();
  2106. foreach (var item in DailyFeePaymentData)
  2107. {
  2108. if (_dailyIds != null)
  2109. {
  2110. item.IsChecked = _dailyIds.Contains(item.Id);
  2111. }
  2112. if (dic_setData.ContainsKey(item.PriceTypeId))
  2113. {
  2114. item.priceTypeStr = dic_setData[item.PriceTypeId];
  2115. }
  2116. else
  2117. {
  2118. Sys_SetData sd_priceType = _daiRep.Query<Sys_SetData>(s => s.Id == item.PriceTypeId).First();
  2119. if (sd_priceType != null)
  2120. {
  2121. item.priceTypeStr = sd_priceType.Name;
  2122. dic_setData.Add(item.PriceTypeId, sd_priceType.Name);
  2123. }
  2124. }
  2125. if (dic_setData.ContainsKey(item.transferTypeId))
  2126. {
  2127. item.transferTypeIdStr = dic_setData[item.transferTypeId];
  2128. Sys_SetData sd_transfer = _daiRep.Query<Sys_SetData>(s => s.Id == item.transferTypeId).First();
  2129. if (sd_transfer != null)
  2130. {
  2131. item.transferParentId = sd_transfer.STid;
  2132. item.transferParentIdStr = sd_transfer.STid == stGZ.Id ? "公转" : sd_transfer.STid == stSZ.Id ? "私转" : "";
  2133. }
  2134. }
  2135. else
  2136. {
  2137. Sys_SetData sd_transfer = _daiRep.Query<Sys_SetData>(s => s.Id == item.transferTypeId).First();
  2138. if (sd_transfer != null)
  2139. {
  2140. item.transferTypeIdStr = sd_transfer.Name;
  2141. item.transferParentId = sd_transfer.STid;
  2142. item.transferParentIdStr = sd_transfer.STid == stGZ.Id ? "公转" : sd_transfer.STid == stSZ.Id ? "私转" : "";
  2143. dic_setData.Add(item.transferTypeId, sd_transfer.Name);
  2144. }
  2145. }
  2146. string feeContentSql = string.Format(@"Select * From Fin_DailyFeePaymentContent
  2147. Where IsDel=0 And DFPId = {0} ", item.Id);
  2148. item.childList = _sqlSugar.SqlQueryable<Fin_DailyFeePaymentContentInfolView>(feeContentSql).ToList();
  2149. int rowNumber = 1;
  2150. foreach (var subItem in item.childList)
  2151. {
  2152. string remaksDescription = $"{rowNumber}、【{item.priceTypeStr}{item.Instructions}({subItem.PriceName}) CNY:{subItem.ItemTotal.ToString("#0.00")}(单价:{subItem.Price.ToString("#0.00")} * {subItem.Quantity})";
  2153. subItem.RemaksDescription = remaksDescription;
  2154. string excelRemaksDescription = $"【{item.priceTypeStr}{item.Instructions}({subItem.PriceName}) CNY:{subItem.ItemTotal.ToString("#0.00")}(单价:{subItem.Price.ToString("#0.00")} * {subItem.Quantity})【申请人:{item.CreateUser} 申请时间:{item.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")}】";
  2155. subItem.ExcelRemaksDescription = excelRemaksDescription;
  2156. rowNumber++;
  2157. }
  2158. }
  2159. decimal total_gz = DailyFeePaymentData.Where(s => s.transferParentId == stGZ.Id).Sum(d => d.SumPrice ?? 0M);
  2160. decimal total_sz = DailyFeePaymentData.Where(s => s.transferParentId == stSZ.Id).Sum(d => d.SumPrice ?? 0M);
  2161. var result = new tree_Fin_DailyFeePaymentResult() { gz = total_gz, sz = total_sz, dataList = DailyFeePaymentData };
  2162. return result;
  2163. }
  2164. /// <summary>
  2165. /// 付款申请 (PageId=51)
  2166. /// 团组,日付相关费用 选中状态变更
  2167. /// </summary>
  2168. /// <param name="dto"></param>
  2169. /// <returns></returns>
  2170. [HttpPost]
  2171. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2172. public async Task<IActionResult> PostPayRequestCheckedChange(PayRequestCheckedChangeDto dto)
  2173. {
  2174. #region 验证
  2175. if (dto.Type < 1 || dto.Type > 2)
  2176. {
  2177. return Ok(JsonView(false, "请传入有效的Type参数! 1 checked 2 清除上次勾选"));
  2178. }
  2179. #endregion
  2180. PaymentRequestCheckedView requestCheckedView = new PaymentRequestCheckedView();
  2181. List<int> groupIds = new List<int>();
  2182. List<int> dailyPaymentIds = new List<int>();
  2183. List<int> hotelIds = new List<int>();
  2184. #region 参数处理
  2185. if (!string.IsNullOrEmpty(dto.GroupIds))
  2186. {
  2187. if (dto.GroupIds.Contains(","))
  2188. {
  2189. groupIds = dto.GroupIds.Split(',').Select(int.Parse).ToList();
  2190. }
  2191. else
  2192. {
  2193. groupIds.Add(int.Parse(dto.GroupIds));
  2194. }
  2195. }
  2196. if (!string.IsNullOrEmpty(dto.HotelSubIds))
  2197. {
  2198. if (dto.HotelSubIds.Contains(","))
  2199. {
  2200. hotelIds = dto.HotelSubIds.Split(',').Select(int.Parse).ToList();
  2201. }
  2202. else
  2203. {
  2204. hotelIds.Add(int.Parse(dto.HotelSubIds));
  2205. }
  2206. }
  2207. if (!string.IsNullOrEmpty(dto.DailyPaymentIds))
  2208. {
  2209. if (dto.DailyPaymentIds.Contains(","))
  2210. {
  2211. dailyPaymentIds = dto.DailyPaymentIds.Split(',').Select(int.Parse).ToList();
  2212. }
  2213. else
  2214. {
  2215. dailyPaymentIds.Add(int.Parse(dto.DailyPaymentIds));
  2216. }
  2217. }
  2218. #endregion
  2219. requestCheckedView.GroupIds = groupIds;
  2220. requestCheckedView.HotelSubIds = hotelIds;
  2221. requestCheckedView.DailyPaymentIds = dailyPaymentIds;
  2222. if (dto.Type == 1)
  2223. {
  2224. TimeSpan ts = DateTime.Now.AddDays(180) - DateTime.Now; //设置redis 过期时间 半年(180)
  2225. var status = await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync<string>("paymentRequestCheckedData", JsonConvert.SerializeObject(requestCheckedView), ts);
  2226. if (status)
  2227. {
  2228. return Ok(JsonView(true, "操作成功!"));
  2229. }
  2230. }
  2231. else if (dto.Type == 2)
  2232. {
  2233. var status = await RedisRepository.RedisFactory.CreateRedisRepository().KeyDeleteAsync("paymentRequestCheckedData");
  2234. if (status)
  2235. {
  2236. return Ok(JsonView(true, "操作成功!"));
  2237. }
  2238. }
  2239. return Ok(JsonView(false, "操作失败!"));
  2240. }
  2241. /// <summary>
  2242. /// 付款申请 (PageId=51)
  2243. /// 团组,日付相关费用 汇率变更
  2244. /// </summary>
  2245. /// <param name="dto"></param>
  2246. /// <returns></returns>
  2247. [HttpPost]
  2248. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2249. public async Task<IActionResult> PostPayRequestRateChange(PayRequestRateChangeDto dto)
  2250. {
  2251. #region 验证
  2252. DateTime beginDt, endDt;
  2253. string format = "yyyy-MM-dd";
  2254. if (!DateTime.TryParseExact(dto.beginDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out beginDt))
  2255. {
  2256. return Ok(JsonView(false, "开始日期格式不正确!正确格式:yyyy-MM-dd"));
  2257. }
  2258. if (!DateTime.TryParseExact(dto.endDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt))
  2259. {
  2260. return Ok(JsonView(false, "结束日期格式不正确!正确格式:yyyy-MM-dd"));
  2261. }
  2262. if (dto.UserId < 1) return Ok(JsonView(false, "请传入有效的UserId参数!"));
  2263. if (dto.Id < 1) return Ok(JsonView(false, "请传入有效的Id参数!"));
  2264. if (dto.Rate <= 0) return Ok(JsonView(false, "请传入有效的Rate参数!"));
  2265. #endregion
  2266. int hotelSubStatus = 0,status = 0;
  2267. int diid = 0, cTable = 0, currId = 0;
  2268. if (dto.HotelSubId > 0)
  2269. {
  2270. //更改酒店子表汇率
  2271. hotelSubStatus = _sqlSugar.Updateable<Grp_HotelReservationsContent>()
  2272. .SetColumns(it => it.Rate == dto.Rate)
  2273. .Where(it => it.Id == dto.HotelSubId)
  2274. .ExecuteCommand();
  2275. if (hotelSubStatus > 0)
  2276. {
  2277. var hotelSubInfo = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.Id == dto.HotelSubId).First();
  2278. if (hotelSubInfo != null)
  2279. {
  2280. diid = hotelSubInfo.DiId;
  2281. currId = hotelSubInfo.Currency;
  2282. }
  2283. //付款申请汇率更改成功;更改团组汇率对应币种
  2284. string currCode = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == currId).First()?.Name ?? "";
  2285. await _teamRateRep.UpdateGroupRateByDiIdAndCTableId(diid, 76, currCode, dto.Rate);
  2286. }
  2287. }
  2288. if (dto.Id > 0)
  2289. {
  2290. var ccpInfo = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(it => it.Id == dto.Id).First();
  2291. decimal cnyMoney = 0.00M;
  2292. if (ccpInfo != null) {
  2293. cnyMoney = ccpInfo.PayMoney * dto.Rate;
  2294. diid = ccpInfo.DIId;
  2295. cTable = ccpInfo.CTable;
  2296. currId = ccpInfo.PaymentCurrency;
  2297. }
  2298. status = _sqlSugar.Updateable<Grp_CreditCardPayment>()
  2299. .SetColumns(it => it.DayRate == dto.Rate)
  2300. .SetColumns(it => it.RMBPrice == cnyMoney)
  2301. .Where(it => it.Id == dto.Id)
  2302. .ExecuteCommand();
  2303. if (status > 0)
  2304. {
  2305. //付款申请汇率更改成功;更改团组汇率对应币种
  2306. if (cTable != 76)
  2307. {
  2308. string currCode = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == currId).First()?.Name ?? "";
  2309. await _teamRateRep.UpdateGroupRateByDiIdAndCTableId(diid, cTable, currCode, dto.Rate);
  2310. }
  2311. }
  2312. }
  2313. if (hotelSubStatus > 0 || status > 0)
  2314. {
  2315. PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
  2316. var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
  2317. if (checkedStr != null)
  2318. {
  2319. checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
  2320. }
  2321. tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(1, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
  2322. tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(1, checkedView.GroupIds, checkedView.HotelSubIds, dto.beginDt, dto.endDt);
  2323. decimal _gz = dailyResult.gz + groupResult.gz;
  2324. decimal _sz = dailyResult.sz + groupResult.sz;
  2325. return Ok(JsonView(true, "操作成功!", new { gz = dailyResult, sz = groupResult }));
  2326. }
  2327. return Ok(JsonView(false, "该项汇率修改失败!"));
  2328. }
  2329. /// <summary>
  2330. /// 付款申请 (PageId=51)
  2331. /// 团组,日付相关费用 付款状态变更
  2332. /// </summary>
  2333. /// <param name="dto"></param>
  2334. /// <returns></returns>
  2335. [HttpPost]
  2336. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2337. public async Task<IActionResult> PostPayRequestPayChange(PayRequestPayChangeDto dto)
  2338. {
  2339. if (dto.UserId < 1) return Ok(JsonView(false, "请传入有效的UserId参数!"));
  2340. //if (string.IsNullOrEmpty(dto.GroupIds))
  2341. //{
  2342. // return Ok(JsonView(false, "请传入有效的GroupIds参数!"));
  2343. //}
  2344. //if (string.IsNullOrEmpty(dto.DailyPaymentIds))
  2345. //{
  2346. // return Ok(JsonView(false, "请传入有效的DailyPaymentIds参数!"));
  2347. //}
  2348. List<int> groupIds = new List<int>();
  2349. List<int> dailyPaymentIds = new List<int>();
  2350. List<int> hotelSubIds = new List<int>();
  2351. #region 参数处理
  2352. if (!string.IsNullOrEmpty(dto.GroupIds))
  2353. {
  2354. if (dto.GroupIds.Contains(",")) groupIds = dto.GroupIds.Split(',').Select(int.Parse).ToList();
  2355. else groupIds.Add(int.Parse(dto.GroupIds));
  2356. }
  2357. if (!string.IsNullOrEmpty(dto.HotelSubIds))
  2358. {
  2359. if (dto.HotelSubIds.Contains(",")) hotelSubIds = dto.HotelSubIds.Split(',').Select(int.Parse).ToList();
  2360. else hotelSubIds.Add(int.Parse(dto.HotelSubIds));
  2361. if (hotelSubIds.Count > 0)
  2362. {
  2363. foreach (var item in hotelSubIds)
  2364. {
  2365. if (item < 1)
  2366. {
  2367. hotelSubIds.Remove(item);
  2368. }
  2369. }
  2370. }
  2371. }
  2372. if (!string.IsNullOrEmpty(dto.DailyPaymentIds))
  2373. {
  2374. if (dto.DailyPaymentIds.Contains(",")) dailyPaymentIds = dto.DailyPaymentIds.Split(',').Select(int.Parse).ToList();
  2375. else dailyPaymentIds.Add(int.Parse(dto.DailyPaymentIds));
  2376. }
  2377. #endregion
  2378. bool changeStatus = false;
  2379. _sqlSugar.BeginTran();
  2380. if (groupIds.Count > 0)
  2381. {
  2382. var ccpInfos = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Id)).ToList();
  2383. var otherTypeCcpIds = ccpInfos.Where(it => it.CTable != 76).Select(it => it.Id).ToList();
  2384. var hotelTyprCcpIds = ccpInfos.Where(it => it.CTable == 76).Select(it => it.Id).ToList();
  2385. int groupStatus = 0;
  2386. if (otherTypeCcpIds.Count > 0) //其他费用类型 正常付款
  2387. {
  2388. groupStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>()
  2389. .SetColumns(it => it.IsPay == 1)
  2390. .Where(it => otherTypeCcpIds.Contains(it.Id))
  2391. .ExecuteCommand();
  2392. changeStatus = true;
  2393. }
  2394. if (hotelTyprCcpIds.Count > 0) //酒店费用子项逻辑付款,酒店子项费用全部付完款,c表ispay=1
  2395. {
  2396. if (hotelSubIds.Count > 0)
  2397. {
  2398. List<int> hrPayIds = new List<int>();
  2399. var hrIspayStatus = _sqlSugar.Updateable<Grp_HotelReservationsContent>()
  2400. .SetColumns(it => it.IsPay == 1)
  2401. .Where(it => hotelSubIds.Contains(it.Id))
  2402. .ExecuteCommand();
  2403. changeStatus = true;
  2404. //酒店子项是否全部付完款
  2405. List<int> hrIds = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.IsDel == 0 && hotelSubIds.Contains(it.Id)).Select(it => it.HrId).Distinct().ToList();
  2406. if (hrIds.Count > 0 )
  2407. {
  2408. var hotelSubFeeData = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.IsDel == 0 && hrIds.Contains(it.HrId)).ToList();
  2409. var hotelSubFeeGroupData = hotelSubFeeData.GroupBy(it => it.HrId).ToList();
  2410. foreach (var item in hotelSubFeeGroupData)
  2411. {
  2412. var allTotal = item.Where(it => it.Price > 0).ToList().Count;
  2413. var paymentTotal = item.Where(it => it.Price > 0 && it.IsPay == 1).ToList().Count;
  2414. if (allTotal == paymentTotal)
  2415. {
  2416. hrPayIds.Add(item.Key);
  2417. }
  2418. }
  2419. }
  2420. if (hrPayIds.Count > 0)
  2421. {
  2422. //c表更改全部付款的酒店费用
  2423. groupStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>()
  2424. .SetColumns(it => it.IsPay == 1)
  2425. .Where(it => it.CTable == 76 && hrPayIds.Contains(it.CId))
  2426. .ExecuteCommand();
  2427. }
  2428. }
  2429. }
  2430. }
  2431. //if (hotelSubIds.Count > 0)
  2432. //{
  2433. // var groupStatus = _sqlSugar.Updateable<Grp_HotelReservationsContent>()
  2434. // .SetColumns(it => it.IsPay == 1)
  2435. // .Where(it => hotelSubIds.Contains(it.Id))
  2436. // .ExecuteCommand();
  2437. // if (groupStatus > 0)
  2438. // {
  2439. // changeStatus = true;
  2440. // }
  2441. //}
  2442. if (dailyPaymentIds.Count > 0)
  2443. {
  2444. var dailyPaymentStatus = _sqlSugar.Updateable<Fin_DailyFeePayment>()
  2445. .SetColumns(it => it.IsPay == 1)
  2446. .Where(it => dailyPaymentIds.Contains(it.Id))
  2447. .ExecuteCommand();
  2448. if (dailyPaymentStatus > 0)
  2449. {
  2450. changeStatus = true;
  2451. }
  2452. }
  2453. if (changeStatus)
  2454. {
  2455. _sqlSugar.CommitTran();
  2456. #region 应用推送
  2457. try
  2458. {
  2459. foreach (int ccpId in groupIds)
  2460. {
  2461. List<string> tempList = new List<string>() { ccpId.ToString() };
  2462. await AppNoticeLibrary.SendUserMsg_GroupStatus_PayResult(ccpId, tempList);
  2463. }
  2464. foreach (int dailyId in dailyPaymentIds)
  2465. {
  2466. List<string> tempList = new List<string>() { dailyId.ToString() };
  2467. await AppNoticeLibrary.DailyPayReminder_Pay_ToUser(dailyId, tempList);
  2468. }
  2469. }
  2470. catch (Exception ex)
  2471. {
  2472. }
  2473. #endregion
  2474. return Ok(JsonView(true, "操作成功!"));
  2475. }
  2476. _sqlSugar.RollbackTran();
  2477. return Ok(JsonView(false, "付款状态修改失败!"));
  2478. }
  2479. /// <summary>
  2480. /// 付款申请 (PageId=51)
  2481. /// File Download
  2482. /// </summary>
  2483. /// <param name="dto"></param>
  2484. /// <returns></returns>
  2485. [HttpPost]
  2486. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2487. public async Task<IActionResult> PostPayRequestFileDownload(PayRequestFileDownloadDto dto)
  2488. {
  2489. Stopwatch stopwatch = Stopwatch.StartNew();
  2490. #region 参数,权限 验证
  2491. if (dto.PortType < 1)
  2492. {
  2493. return Ok(JsonView(false, "请传入有效的PortType参数!"));
  2494. }
  2495. if (dto.UserId < 1)
  2496. {
  2497. return Ok(JsonView(false, "请传入有效的UserId参数!"));
  2498. }
  2499. if (dto.PageId < 1)
  2500. {
  2501. dto.PageId = 51;
  2502. return Ok(JsonView(false, "请传入有效的PageId参数!"));
  2503. }
  2504. if (dto.ConpanyId < 1 || dto.ConpanyId > 4)
  2505. {
  2506. return Ok(JsonView(false, "请传入有效的ConpanyId参数!"));
  2507. }
  2508. PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
  2509. #region 页面操作权限验证
  2510. pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
  2511. if (pageFunAuthView.FilesDownloadAuth == 0) return Ok(JsonView(false, "您没有文件下载权限!"));
  2512. #endregion
  2513. #endregion
  2514. try
  2515. {
  2516. PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
  2517. var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
  2518. if (checkedStr != null)
  2519. {
  2520. checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
  2521. }
  2522. if (checkedView == null)
  2523. {
  2524. return Ok(JsonView(false, "没有选中的数据!"));
  2525. }
  2526. if (checkedView.GroupIds == null && checkedView.DailyPaymentIds == null)
  2527. {
  2528. return Ok(JsonView(false, "没有选中的数据!"));
  2529. }
  2530. tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(2, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
  2531. tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(2, checkedView.GroupIds, checkedView.HotelSubIds, dto.beginDt, dto.endDt);
  2532. if (dailyResult.childList == null)
  2533. {
  2534. dailyResult.childList = new List<Fin_DailyFeePaymentContentInfolView>();
  2535. }
  2536. List<tree_Group_DailyFeePaymentPageListView> _GroupData = new List<tree_Group_DailyFeePaymentPageListView>();
  2537. List<tree_Fin_DailyFeePaymentPageListView> _DailyData = new List<tree_Fin_DailyFeePaymentPageListView>();
  2538. //1 成都泛美商务有限公司
  2539. if (dto.ConpanyId == 1)
  2540. {
  2541. if (groupResult.dataList != null && groupResult.dataList.Count > 0) _GroupData = groupResult.dataList.Where(it => it.CompanyId == 1).ToList();
  2542. if (dailyResult.dataList != null && dailyResult.dataList.Count > 0) _DailyData = dailyResult.dataList.Where(it => it.CompanyId == 1).ToList();
  2543. }
  2544. //2 四川泛美交流有限公司
  2545. else if (dto.ConpanyId == 2)
  2546. {
  2547. if (groupResult.dataList != null && groupResult.dataList.Count > 0) _GroupData = groupResult.dataList.Where(it => it.CompanyId == 2).ToList();
  2548. if (dailyResult.dataList != null && dailyResult.dataList.Count > 0) _DailyData = dailyResult.dataList.Where(it => it.CompanyId == 2).ToList();
  2549. }
  2550. //3 成都纽茵教育科技有限公司
  2551. else if (dto.ConpanyId == 3)
  2552. {
  2553. if (groupResult.dataList != null && groupResult.dataList.Count > 0) _GroupData = groupResult.dataList.Where(it => it.CompanyId == 3).ToList();
  2554. if (dailyResult.dataList != null && dailyResult.dataList.Count > 0) _DailyData = dailyResult.dataList.Where(it => it.CompanyId == 3).ToList();
  2555. }
  2556. //4 成都鸿企中元科技有限公司
  2557. else if (dto.ConpanyId == 4)
  2558. {
  2559. return Ok(JsonView(false, "暂未开放该类型!"));
  2560. }
  2561. else
  2562. {
  2563. return Ok(JsonView(false, "参数ConpanyId不可使用!"));
  2564. }
  2565. string _requestPaymentDt = DateTime.Now.ToString("yyyy-MM-dd"),//申请付款日期
  2566. _appliedAmount = "", //申请付款金额
  2567. _GZStr = "", //公转价格描述
  2568. _SZStr = ""; //私转价格描述
  2569. decimal groupGZAmout = 0.00M, groupSZAmout = 0.00M;
  2570. decimal dailyGZAmout = 0.00M, dailySZAmout = 0.00M;
  2571. string dailyGZStr = "", dailySZStr = "", groupGZStr = "", groupSZStr = "";
  2572. #region 数据处理
  2573. //团组费用相关
  2574. foreach (var item in _GroupData)
  2575. {
  2576. string groupGZSubStr = "";
  2577. string groupSZSubStr = "";
  2578. foreach (var subItem in item.ChildList)
  2579. {
  2580. if (subItem.TransferMark.Equals("公转"))
  2581. {
  2582. groupGZAmout += subItem.CNYSubTotalAmount;
  2583. groupGZSubStr += $"{subItem.RemaksDescription}\r\n";
  2584. }
  2585. else if (subItem.TransferMark.Equals("私转"))
  2586. {
  2587. groupSZAmout += subItem.CNYSubTotalAmount;
  2588. groupSZSubStr += $"{subItem.RemaksDescription}\r\n";
  2589. }
  2590. //groupGZSubStr += $"\t";
  2591. }
  2592. //if (!string.IsNullOrEmpty(groupGZSubStr)) groupGZStr += $"团组:{item.GroupName}\r\n{groupGZSubStr}\r\n";
  2593. //if (!string.IsNullOrEmpty(groupSZSubStr)) groupSZStr += $"团组:{item.GroupName}\r\n{groupSZSubStr}\r\n";
  2594. if (!string.IsNullOrEmpty(groupGZSubStr)) groupGZStr += $"{groupGZSubStr}\r\n";
  2595. if (!string.IsNullOrEmpty(groupSZSubStr)) groupSZStr += $"{groupSZSubStr}\r\n";
  2596. }
  2597. //日常费用相关
  2598. foreach (var item in _DailyData)
  2599. {
  2600. foreach (var subItem in item.childList)
  2601. {
  2602. if (item.transferParentId == 62) //公转
  2603. {
  2604. dailyGZAmout += item.SumPrice ?? 0.00M;
  2605. dailyGZStr += $"{item.RowNumber}、【{item.CompanyName}{subItem.ExcelRemaksDescription}\r\n";
  2606. }
  2607. else if (item.transferParentId == 63) //私转
  2608. {
  2609. dailySZAmout += item.SumPrice ?? 0.00M;
  2610. dailySZStr += $"{item.RowNumber}、【{item.CompanyName}{subItem.ExcelRemaksDescription}\r\n";
  2611. }
  2612. }
  2613. }
  2614. _GZStr = $"【公转】团组相关费用(合计:CNY {groupGZAmout.ToString("#0.00")}):\r\n{groupGZStr}【公转】日常付款费用(合计:CNY {dailyGZAmout.ToString("#0.00")}):\r\n{dailyGZStr}";
  2615. _SZStr = $"【私转】团组相关费用(合计:CNY {groupSZAmout.ToString("#0.00")}):\r\n{groupSZStr}【私转】日常付款费用(合计:CNY {dailySZAmout.ToString("#0.00")}):\r\n{dailySZStr}";
  2616. _appliedAmount = $"公转:CNY {(groupGZAmout + dailyGZAmout).ToString("#0.00")}\r\n私转:CNY {(groupSZAmout + dailySZAmout).ToString("#0.00")}";
  2617. #endregion
  2618. WorkbookDesigner designer = new WorkbookDesigner();
  2619. designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/付款申请书.xls");
  2620. designer.SetDataSource("Date", _requestPaymentDt);
  2621. designer.SetDataSource("Price", _appliedAmount);
  2622. designer.SetDataSource("Content", _GZStr);
  2623. designer.SetDataSource("Content1", _SZStr);
  2624. //根据数据源处理生成报表内容
  2625. designer.Process();
  2626. string fileName = ("PayRequest/付款申请(" + dto.beginDt + "~" + dto.endDt + ").xlsx");
  2627. designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
  2628. string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
  2629. stopwatch.Stop();
  2630. return Ok(JsonView(true, $"操作成功!{stopwatch.ElapsedMilliseconds / 1000}s", new { url = rst }));
  2631. }
  2632. catch (Exception ex)
  2633. {
  2634. return Ok(JsonView(false, ex.Message));
  2635. }
  2636. }
  2637. #endregion
  2638. #region 超支费用
  2639. /// <summary>
  2640. /// 超支费用
  2641. /// 1增、2改、3删
  2642. /// </summary>
  2643. /// <param name="dto"></param>
  2644. /// <returns></returns>
  2645. [HttpPost]
  2646. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2647. public async Task<IActionResult> PostGroupExtraCost_Operator(Fin_GroupExtraCostDto_OP dto)
  2648. {
  2649. #region 验证
  2650. #endregion
  2651. Fin_GroupExtraCost _entity = new Fin_GroupExtraCost();
  2652. _entity.DiId = dto.diId;
  2653. _entity.PriceName = dto.priceName;
  2654. _entity.Price = dto.price;
  2655. _entity.PriceCurrency = dto.currency;
  2656. _entity.PriceType = dto.priceType;
  2657. _entity.Coefficient = dto.coefficient;
  2658. _entity.PriceDetailType = dto.priceDetailType;
  2659. _entity.FilePath = dto.filePath;
  2660. _entity.Remark = dto.remark;
  2661. _entity.PriceCount = dto.PriceCount;
  2662. DateTime dt_PriceDt;
  2663. bool b_PriceDt = DateTime.TryParse(dto.PriceDt, out dt_PriceDt);
  2664. if (b_PriceDt)
  2665. {
  2666. _entity.PriceDt = dt_PriceDt;
  2667. }
  2668. else
  2669. {
  2670. _entity.PriceDt = DateTime.MinValue;
  2671. }
  2672. _entity.PriceSum = dto.price * dto.PriceCount;
  2673. _daiRep.BeginTran();
  2674. if (dto.editType == 1)
  2675. {
  2676. _entity.CreateUserId = dto.createUser;
  2677. _entity.CreateTime = DateTime.Now;
  2678. _entity.IsDel = 0;
  2679. int returnId = await _daiRep.AddAsyncReturnId<Fin_GroupExtraCost>(_entity);
  2680. if (returnId > 0)
  2681. {
  2682. dto.Id = returnId;
  2683. }
  2684. }
  2685. else if (dto.editType == 2)
  2686. {
  2687. bool res = await _daiRep.UpdateAsync<Fin_GroupExtraCost>(s => s.Id == dto.Id, s => new Fin_GroupExtraCost
  2688. {
  2689. PriceName = dto.priceName,
  2690. Price = dto.price,
  2691. PriceCurrency = dto.currency,
  2692. PriceType = dto.priceType,
  2693. PriceDetailType = dto.priceDetailType,
  2694. Coefficient = dto.coefficient,
  2695. FilePath = dto.filePath,
  2696. Remark = dto.remark,
  2697. PriceCount = dto.PriceCount,
  2698. PriceDt = _entity.PriceDt,
  2699. PriceSum = _entity.PriceSum
  2700. });
  2701. if (!res)
  2702. {
  2703. _daiRep.RollbackTran();
  2704. return Ok(JsonView(false, "2操作失败!"));
  2705. }
  2706. }
  2707. else if (dto.editType == 3)
  2708. {
  2709. string delTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
  2710. bool res = await _daiRep.UpdateAsync<Fin_GroupExtraCost>(s => s.Id == dto.Id, s => new Fin_GroupExtraCost
  2711. {
  2712. IsDel = 1,
  2713. DeleteTime = delTime,
  2714. DeleteUserId = dto.createUser
  2715. });
  2716. if (!res)
  2717. {
  2718. _daiRep.RollbackTran();
  2719. return Ok(JsonView(false, "3操作失败!"));
  2720. }
  2721. }
  2722. else
  2723. {
  2724. _daiRep.RollbackTran();
  2725. return Ok(JsonView(false, "未知的editType"));
  2726. }
  2727. if (!extraCost_editCreditCardPayment(dto))
  2728. {
  2729. return Ok(JsonView(false, "ccp操作失败"));
  2730. }
  2731. _daiRep.CommitTran();
  2732. return Ok(JsonView(true, "操作成功"));
  2733. }
  2734. private Result PostCurrencyByDiid_Sync(int diId, int CId, int currencyId)
  2735. {
  2736. if (diId == 0)
  2737. {
  2738. return new Result() { Code = -1, Msg = "请传入团组Id!" };
  2739. }
  2740. if (CId == 0)
  2741. {
  2742. return new Result() { Code = -1, Msg = "请传入类型Id!" };
  2743. }
  2744. if (currencyId == 0)
  2745. {
  2746. return new Result() { Code = -1, Msg = "请传入币种Id!" };
  2747. }
  2748. Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == diId && a.IsDel == 0 && a.CTable == CId);
  2749. List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
  2750. CurrencyInfo CurrencyRate = new CurrencyInfo();
  2751. try
  2752. {
  2753. if (_TeamRate != null)
  2754. {
  2755. Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == currencyId);
  2756. if (_SetData != null)
  2757. {
  2758. currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
  2759. CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
  2760. if (CurrencyRate != null)
  2761. {
  2762. return new Result() { Code = 0, Msg = "查询成功!", Data = CurrencyRate };
  2763. }
  2764. else
  2765. {
  2766. return new Result() { Code = -1, Msg = "暂无团组汇率,请前往设置!", Data = CurrencyRate };
  2767. }
  2768. }
  2769. else
  2770. {
  2771. return new Result() { Code = -1, Msg = "暂无团组汇率,请前往设置!", Data = CurrencyRate };
  2772. }
  2773. }
  2774. else
  2775. {
  2776. return new Result() { Code = -1, Msg = "暂无团组汇率,请前往设置!", Data = CurrencyRate };
  2777. }
  2778. }
  2779. catch (Exception)
  2780. {
  2781. return new Result() { Code = -1, Msg = "查询异常!", Data = CurrencyRate };
  2782. }
  2783. }
  2784. private bool extraCost_editCreditCardPayment(Fin_GroupExtraCostDto_OP costDto)
  2785. {
  2786. //设置团组汇率
  2787. decimal dcm_dayrate = 1M;
  2788. decimal dcm_rmbPrice = costDto.price;
  2789. int ispay = costDto.payType == 72 ? 1 : 0;
  2790. if (costDto.costSign != 3)
  2791. {
  2792. //获取新汇率 int diId,int CId, int currencyId
  2793. Result rate = this.PostCurrencyByDiid_Sync(costDto.diId, 1015, costDto.currency);
  2794. if (rate.Code == 0)
  2795. {
  2796. var rateInfo = (rate.Data as CurrencyInfo);
  2797. if (rateInfo is not null)
  2798. {
  2799. dcm_dayrate = rateInfo.Rate;
  2800. dcm_rmbPrice = rateInfo.Rate * dcm_rmbPrice;
  2801. }
  2802. else
  2803. {
  2804. dcm_dayrate = 1;
  2805. }
  2806. }
  2807. }
  2808. Grp_CreditCardPayment ccp = _daiRep.Query<Grp_CreditCardPayment>(s => s.CId == costDto.Id && s.CTable == 1015).First();
  2809. if (ccp == null)
  2810. {
  2811. ccp = new Grp_CreditCardPayment();
  2812. ccp.PayDId = costDto.payType;// dto
  2813. ccp.ConsumptionPatterns = "";
  2814. ccp.ConsumptionDate = "";
  2815. ccp.CTDId = costDto.payCardId;// dto
  2816. ccp.BankNo = "";
  2817. ccp.CardholderName = "";
  2818. ccp.PayMoney = costDto.price;// dto
  2819. ccp.PaymentCurrency = costDto.currency;// dto
  2820. ccp.CompanyBankNo = "";
  2821. ccp.OtherBankName = "";
  2822. ccp.OtherSideNo = "";
  2823. ccp.OtherSideName = "";
  2824. ccp.Remark = "";
  2825. ccp.CreateUserId = costDto.createUser;
  2826. ccp.CreateTime = DateTime.Now;
  2827. ccp.MFOperator = 0;
  2828. ccp.MFOperatorDate = "";
  2829. ccp.IsAuditDM = 0;
  2830. ccp.AuditDMOperate = 0;
  2831. ccp.AuditDMDate = "";
  2832. ccp.IsAuditMF = 0;
  2833. ccp.AuditMFOperate = 0;
  2834. ccp.AuditMFDate = "";
  2835. ccp.IsAuditGM = 0;
  2836. ccp.AuditGMOperate = 0;
  2837. ccp.AuditGMDate = "";
  2838. ccp.IsPay = ispay; // upd
  2839. ccp.DIId = costDto.diId;// dto
  2840. ccp.CId = costDto.Id;// dto
  2841. ccp.CTable = 1015; //超支费用指向id
  2842. ccp.IsDel = 0;
  2843. ccp.PayPercentage = 100M;
  2844. ccp.PayThenMoney = 0M;
  2845. ccp.PayPercentageOld = 100M;
  2846. ccp.PayThenMoneyOld = 0M;
  2847. ccp.UpdateDate = "";
  2848. ccp.Payee = costDto.payee;// dto
  2849. ccp.OrbitalPrivateTransfer = costDto.costSign;// dto
  2850. ccp.ExceedBudget = 0;
  2851. ccp.DayRate = dcm_dayrate; //upd
  2852. ccp.RMBPrice = dcm_rmbPrice; //upd
  2853. int ccpInsertId = _daiRep.AddReturnId<Grp_CreditCardPayment>(ccp);
  2854. if (ccpInsertId > 0)
  2855. {
  2856. return true;
  2857. }
  2858. }
  2859. else
  2860. {
  2861. if (costDto.editType == 2)
  2862. {
  2863. bool res = _daiRep.Update<Grp_CreditCardPayment>(s => s.Id == ccp.Id, s => new Grp_CreditCardPayment
  2864. {
  2865. PayDId = costDto.payType,
  2866. CTDId = costDto.payCardId,
  2867. PayMoney = costDto.price,
  2868. PaymentCurrency = costDto.currency,
  2869. IsPay = ispay,
  2870. Payee = costDto.payee,
  2871. OrbitalPrivateTransfer = costDto.costSign,
  2872. DayRate = dcm_dayrate,
  2873. RMBPrice = dcm_rmbPrice
  2874. });
  2875. return res;
  2876. }
  2877. else if (costDto.editType == 3)
  2878. {
  2879. string delTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
  2880. bool res2 = _daiRep.Update<Grp_CreditCardPayment>(s => s.Id == ccp.Id, s => new Grp_CreditCardPayment
  2881. {
  2882. IsDel = 1,
  2883. DeleteTime = delTime,
  2884. DeleteUserId = costDto.createUser
  2885. });
  2886. return res2;
  2887. }
  2888. }
  2889. return false;
  2890. }
  2891. /// <summary>
  2892. /// 超支费用
  2893. /// 详情查询
  2894. /// </summary>
  2895. /// <param name="dto"></param>
  2896. /// <returns></returns>
  2897. [HttpPost]
  2898. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2899. public async Task<IActionResult> PostGroupExtraCost_Detail(Fin_GroupExtraCostDto_Detail dto)
  2900. {
  2901. if (dto.Id < 1)
  2902. {
  2903. return Ok(JsonView(false, "查询失败"));
  2904. }
  2905. string sql = string.Format(@" Select
  2906. f.Id,f.DiId,
  2907. f.PriceName,f.Price,f.PriceCurrency,c.Payee,c.OrbitalPrivateTransfer,c.PayDId,
  2908. c.CTDId,f.PriceType,f.PriceDetailType,f.Coefficient,f.Remark,f.PriceCount,f.PriceDt
  2909. From Fin_GroupExtraCost f
  2910. Inner Join Grp_CreditCardPayment c On f.Id = c.CId
  2911. Left Join Sys_Users u On f.CreateUserId = u.Id
  2912. Where f.IsDel=0 And c.CTable = 1015
  2913. And f.Id = {0} ", dto.Id);
  2914. Fin_GroupExtraCostDetailView detailView = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostDetailView>(sql).FirstAsync();
  2915. if (detailView == null)
  2916. {
  2917. return Ok(JsonView(false, "查询失败"));
  2918. }
  2919. return Ok(JsonView(true, "查询成功", detailView));
  2920. }
  2921. /// <summary>
  2922. /// 超支费用
  2923. /// 列表查询
  2924. /// </summary>
  2925. /// <param name="dto"></param>
  2926. /// <returns></returns>
  2927. [HttpPost]
  2928. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  2929. public async Task<IActionResult> PostGroupExtraCost_Search(Fin_GroupExtraCostDto_Search dto)
  2930. {
  2931. string sqlWhere = string.Format(@" And f.DiId = {0} ", dto.diId);
  2932. int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
  2933. int endIndex = startIndex + dto.PageSize - 1;
  2934. string sql_data = string.Format(@"Select * From (
  2935. Select row_number() over (order by f.Id Desc) as RowNumber,f.Id,f.DiId,
  2936. f.PriceName,f.PriceType,f.PriceDetailType,CAST(f.Price as varchar)+' '+s.[Name] as PriceStr,f.PriceCount,CAST(f.PriceSum as varchar)+' '+s.[Name] as PriceSumStr,
  2937. f.CreateUserId,f.PriceDt,c.IsAuditGM
  2938. From Fin_GroupExtraCost f
  2939. Inner Join Grp_CreditCardPayment c On f.Id = c.CId
  2940. Inner Join Sys_SetData s On f.PriceCurrency = s.Id
  2941. Left Join Sys_Users u On f.CreateUserId = u.Id
  2942. Where f.IsDel=0 And c.CTable = 1015 {0}
  2943. ) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);
  2944. string sql_count = string.Format(@"Select Count(1) as DataCount From (
  2945. Select row_number() over (order by f.Id Desc) as RowNumber,f.Id,f.DiId,
  2946. f.PriceName,f.PriceType,f.Price,f.FilePath,f.CreateUserId,f.CreateTime,c.IsAuditGM
  2947. From Fin_GroupExtraCost f
  2948. Inner Join Grp_CreditCardPayment c On f.Id = c.CId
  2949. Inner Join Sys_SetData s On f.PriceCurrency = s.Id
  2950. Left Join Sys_Users u On f.CreateUserId = u.Id
  2951. Where f.IsDel=0 And c.CTable = 1015 {0}
  2952. ) temp ", sqlWhere);
  2953. if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
  2954. {
  2955. //Fin_DailyFeePaymentPageCount
  2956. var count = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostViewDataCount>(sql_count).FirstAsync();
  2957. List<Fin_GroupExtraCostView> dataList = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostView>(sql_data).ToListAsync();
  2958. Dictionary<int, string> dic_setData = new Dictionary<int, string>();
  2959. Dictionary<int, string> dic_user = new Dictionary<int, string>();
  2960. foreach (var item in dataList)
  2961. {
  2962. DateTime dtTemp_PriceDt;
  2963. bool b_ct = DateTime.TryParse(item.PriceDt, out dtTemp_PriceDt);
  2964. if (b_ct)
  2965. {
  2966. item.PriceDt = dtTemp_PriceDt.ToString("yyyy-MM-dd");
  2967. }
  2968. //费用类型
  2969. if (dic_setData.ContainsKey(item.PriceType))
  2970. {
  2971. item.PriceTypeStr = dic_setData[item.PriceType];
  2972. }
  2973. else
  2974. {
  2975. Sys_SetData sd_priceTypeDetail = _daiRep.Query<Sys_SetData>(s => s.Id == item.PriceType).First();
  2976. if (sd_priceTypeDetail != null)
  2977. {
  2978. string tempName = sd_priceTypeDetail.Name.Replace("n", "");
  2979. item.PriceTypeStr = tempName;
  2980. dic_setData.Add(item.PriceType, tempName);
  2981. }
  2982. }
  2983. if (item.PriceDetailType > 0)
  2984. {
  2985. if (dic_setData.ContainsKey(item.PriceDetailType))
  2986. {
  2987. item.PriceTypeStr = item.PriceTypeStr + " - " + dic_setData[item.PriceDetailType];
  2988. }
  2989. else
  2990. {
  2991. Sys_SetData sd_priceTypeDetail = _daiRep.Query<Sys_SetData>(s => s.Id == item.PriceDetailType).First();
  2992. if (sd_priceTypeDetail != null)
  2993. {
  2994. string tempName = sd_priceTypeDetail.Name.Replace("n", "");
  2995. item.PriceTypeStr = item.PriceTypeStr + " - " + tempName;
  2996. dic_setData.Add(item.PriceDetailType, tempName);
  2997. }
  2998. }
  2999. }
  3000. //系统用户
  3001. if (dic_user.ContainsKey(item.CreateUserId))
  3002. {
  3003. item.CreateUserIdStr = dic_user[item.CreateUserId];
  3004. }
  3005. else
  3006. {
  3007. Sys_Users users = _daiRep.Query<Sys_Users>(s => s.Id == item.CreateUserId).First();
  3008. if (users != null)
  3009. {
  3010. item.CreateUserIdStr = users.CnName;
  3011. dic_user.Add(item.CreateUserId, users.CnName);
  3012. }
  3013. }
  3014. switch (item.IsAuditGM)
  3015. {
  3016. case 0: item.IsAuditGMStr = "未审核"; break;
  3017. case 1: item.IsAuditGMStr = "已通过"; break;
  3018. case 2: item.IsAuditGMStr = "未通过"; break;
  3019. default: item.IsAuditGMStr = "未知状态"; break;
  3020. }
  3021. }
  3022. var result = new ListViewBase<Fin_GroupExtraCostView>
  3023. {
  3024. CurrPageIndex = dto.PageIndex,
  3025. CurrPageSize = dto.PageSize,
  3026. DataCount = count.DataCount,
  3027. DataList = dataList
  3028. };
  3029. return Ok(JsonView(true, "查询成功", result));
  3030. }
  3031. return Ok(JsonView(false, "查询失败"));
  3032. }
  3033. /// <summary>
  3034. /// 超支费用
  3035. /// 数据集合配置
  3036. /// </summary>
  3037. /// <param name="dto"></param>
  3038. /// <returns></returns>
  3039. [HttpPost]
  3040. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3041. public async Task<IActionResult> PostGroupExtraCost_ListDataInit(Fin_GroupExtraCostDto_DataListInit dto)
  3042. {
  3043. //支付方式
  3044. List<Sys_SetData> Payment = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 14 && a.IsDel == 0).ToList();
  3045. List<SetDataInfoView> _Payment = _mapper.Map<List<SetDataInfoView>>(Payment);
  3046. //信用卡类型
  3047. List<Sys_SetData> Card = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 15 && a.IsDel == 0).ToList();
  3048. List<SetDataInfoView> _Card = _mapper.Map<List<SetDataInfoView>>(Card);
  3049. //超支费用类型
  3050. List<Sys_SetData> PriceType = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 79 && a.IsDel == 0).ToList();
  3051. List<SetDataInfoView> _PriceType = _mapper.Map<List<SetDataInfoView>>(PriceType);
  3052. //超支费用详细类型
  3053. List<Sys_SetData> PriceDetailType = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 80 && a.IsDel == 0).ToList();
  3054. PriceDetailType.ForEach(a => { a.Name = a.Name.Replace("n", ""); });
  3055. List<SetDataInfoView> _PriceDetailType = _mapper.Map<List<SetDataInfoView>>(PriceDetailType);
  3056. var data = new
  3057. {
  3058. Payment = _Payment,
  3059. Card = _Card,
  3060. PriceType = _PriceType,
  3061. PriceDetailType = _PriceDetailType
  3062. };
  3063. return Ok(JsonView(true, "", data));
  3064. }
  3065. /// <summary>
  3066. /// 超支费用
  3067. /// 导出团组超支费用Excel
  3068. /// </summary>
  3069. /// <param name="dto"></param>
  3070. /// <returns></returns>
  3071. [HttpPost]
  3072. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3073. public async Task<IActionResult> PostGroupExtraCost_OutputExcel(Fin_GroupExtraCostExcelDto dto)
  3074. {
  3075. string sqlGroup = string.Format(@" Select * From Grp_DelegationInfo WIth(Nolock) where Id = {0} ", dto.diId);
  3076. Grp_DelegationInfo grp_DelegationInfo = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sqlGroup).FirstAsync();
  3077. if (grp_DelegationInfo == null)
  3078. {
  3079. return Ok(JsonView(false, "导出失败,未查询到团组"));
  3080. }
  3081. Workbook workbook = new Workbook();
  3082. Worksheet sheet = workbook.Worksheets[0];
  3083. Cells cells = sheet.Cells;
  3084. //sheet.Protect(Aspose.Cells.ProtectionType.All, "123123", "");//保护工作表
  3085. //sheet.Protection.IsSelectingLockedCellsAllowed = true;//设置只能选择解锁单元格
  3086. //sheet.Protection.IsFormattingColumnsAllowed = true;//设置可以调整列
  3087. //sheet.Protection.IsFormattingRowsAllowed = true;//设置可以调整行
  3088. #region 数据源
  3089. string sqlData = string.Format(@" Select f.PriceType,REPLACE(s2.[Name],'超支费用','') as PriceTypeStr,'('+REPLACE(s3.[Name],'n','')+')' as PriceDetailTypeStr, PriceDt,PriceName,
  3090. Price,PriceCount,s.[Name] as Currency,PriceSum,f.Remark
  3091. From Fin_GroupExtraCost as f With(Nolock)
  3092. Inner Join Grp_CreditCardPayment as c With(Nolock) On f.Id = c.CId
  3093. Inner Join Sys_SetData as s With(Nolock) On f.PriceCurrency = s.Id
  3094. Inner Join Sys_SetData as s2 With(Nolock) On f.PriceType = s2.Id
  3095. Inner Join Sys_SetData as s3 With(Nolock) On f.PriceDetailType = s3.Id
  3096. Where f.DiId = {0} And f.IsDel=0 And c.CTable=1015
  3097. Order by PriceType ASC,PriceDt ASC ", dto.diId);
  3098. string sqlDataCount = string.Format(@" Select f.PriceType,COUNT(f.PriceType) as DataCount
  3099. From Fin_GroupExtraCost as f With(Nolock)
  3100. Inner Join Grp_CreditCardPayment as c With(Nolock) On f.Id = c.CId
  3101. Where f.DiId = {0} And f.IsDel=0 And c.CTable=1015
  3102. Group by PriceType ", dto.diId);
  3103. List<Fin_GroupExtraCost_ExcelView> dataList = await _sqlSugar.SqlQueryable<Fin_GroupExtraCost_ExcelView>(sqlData).ToListAsync();
  3104. List<Fin_GroupExtraCost_ExcelCountView> countList = await _sqlSugar.SqlQueryable<Fin_GroupExtraCost_ExcelCountView>(sqlDataCount).ToListAsync();
  3105. if (dataList.Count < 1 || countList.Count < 1)
  3106. {
  3107. return Ok(JsonView(false, "导出失败,未查询到数据"));
  3108. }
  3109. #endregion
  3110. #region 标题
  3111. string cellValue_Header = grp_DelegationInfo.TeamName;
  3112. //Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];//新增样式
  3113. Aspose.Cells.Style style_Header = workbook.CreateStyle();
  3114. style_Header.HorizontalAlignment = TextAlignmentType.Center;//文字居中
  3115. style_Header.VerticalAlignment = TextAlignmentType.Center;
  3116. style_Header.Font.Name = "微软雅黑";//文字字体
  3117. style_Header.Font.Size = 18;//文字大小
  3118. style_Header.IsLocked = false;//单元格解锁
  3119. style_Header.Font.IsBold = false;//粗体
  3120. style_Header.Font.Color = Color.FromArgb(255, 0, 0);
  3121. //style1.ForegroundColor = Color.FromArgb(0x99, 0xcc, 0xff);//设置背景色
  3122. //style1.Pattern = BackgroundType.Solid; //设置背景样式
  3123. //style1.IsTextWrapped = true;//单元格内容自动换行
  3124. style_Header.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
  3125. style_Header.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
  3126. style_Header.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
  3127. style_Header.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
  3128. cells.Merge(1, 1, 1, 10);
  3129. Aspose.Cells.Range range_header = cells.CreateRange(1, 1, 1, 10);
  3130. range_header.PutValue(cellValue_Header, false, false);
  3131. range_header.SetStyle(style_Header);
  3132. cells.SetRowHeight(1, 35);
  3133. #endregion
  3134. #region 列名
  3135. Aspose.Cells.Style style_colName = workbook.CreateStyle();
  3136. style_colName.Name = "colName";
  3137. style_colName.HorizontalAlignment = TextAlignmentType.Center;//文字居中
  3138. style_colName.VerticalAlignment = TextAlignmentType.Center;
  3139. style_colName.Font.Name = "微软雅黑";//文字字体
  3140. style_colName.Font.Size = 12;//文字大小
  3141. style_colName.IsLocked = false;//单元格解锁
  3142. style_colName.Font.IsBold = true;//粗体
  3143. style_colName.Font.Color = Color.FromArgb(0, 0, 0);
  3144. style_colName.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
  3145. style_colName.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
  3146. style_colName.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
  3147. style_colName.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
  3148. List<Fin_GroupExtraCostExcelColumnSetting> colNameSettingList = new List<Fin_GroupExtraCostExcelColumnSetting>()
  3149. {
  3150. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 1, columnName="类型", columnWidth= 25},
  3151. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 2, columnName="时间", columnWidth= 16},
  3152. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 3, columnName="内容", columnWidth= 35},
  3153. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 4, columnName="单价", columnWidth= 12},
  3154. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 5, columnName="数量", columnWidth= 12},
  3155. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 6, columnName="货币", columnWidth= 12},
  3156. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 7, columnName="费用", columnWidth= 12},
  3157. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 8, columnName="汇率", columnWidth= 12},
  3158. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 9, columnName="人民币", columnWidth= 12},
  3159. new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 10, columnName="备注信息", columnWidth= 24}
  3160. };
  3161. foreach (var col in colNameSettingList)
  3162. {
  3163. cells[2, col.columnIndex].PutValue(col.columnName);
  3164. cells[2, col.columnIndex].SetStyle(style_colName);
  3165. cells.SetColumnWidth(col.columnIndex, col.columnWidth);
  3166. }
  3167. cells.SetRowHeight(2, 25);
  3168. #endregion
  3169. #region 数据填充
  3170. Aspose.Cells.Style style_dataCol = workbook.GetNamedStyle("colName");
  3171. style_dataCol.Font.IsBold = false;
  3172. style_dataCol.Name = "dataCol";
  3173. Aspose.Cells.Style style_dataBlue = workbook.CreateStyle();
  3174. style_dataBlue.HorizontalAlignment = TextAlignmentType.Center;//文字居中
  3175. style_dataBlue.VerticalAlignment = TextAlignmentType.Center;
  3176. style_dataBlue.Font.Name = "微软雅黑";//文字字体
  3177. style_dataBlue.Font.Size = 12;//文字大小
  3178. style_dataBlue.IsLocked = false;//单元格解锁
  3179. style_dataBlue.Font.IsBold = false;//粗体
  3180. style_dataBlue.ForegroundColor = Color.FromArgb(189, 215, 238);
  3181. style_dataBlue.Pattern = BackgroundType.Solid;
  3182. style_dataBlue.Font.Color = Color.FromArgb(0, 0, 0);
  3183. style_dataBlue.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
  3184. style_dataBlue.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
  3185. style_dataBlue.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
  3186. style_dataBlue.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
  3187. Aspose.Cells.Style style_dataYellow = workbook.CreateStyle();
  3188. style_dataYellow.HorizontalAlignment = TextAlignmentType.Center;//文字居中
  3189. style_dataYellow.VerticalAlignment = TextAlignmentType.Center;
  3190. style_dataYellow.Font.Name = "微软雅黑";//文字字体
  3191. style_dataYellow.Font.Size = 12;//文字大小
  3192. style_dataYellow.IsLocked = false;//单元格解锁
  3193. style_dataYellow.Font.IsBold = false;//粗体
  3194. style_dataYellow.ForegroundColor = Color.FromArgb(255, 242, 204);
  3195. style_dataYellow.Pattern = BackgroundType.Solid;
  3196. style_dataYellow.Font.Color = Color.FromArgb(0, 0, 0);
  3197. style_dataYellow.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
  3198. style_dataYellow.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
  3199. style_dataYellow.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
  3200. style_dataYellow.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
  3201. int rowIndex = 4;
  3202. foreach (var d in dataList)
  3203. {
  3204. //内容
  3205. string typeStr = d.PriceDetailTypeStr + d.PriceName;
  3206. cells["B" + rowIndex.ToString()].PutValue(d.PriceTypeStr);
  3207. cells["C" + rowIndex.ToString()].PutValue(d.PriceDtStr);
  3208. cells["D" + rowIndex.ToString()].PutValue(typeStr);
  3209. cells["E" + rowIndex.ToString()].PutValue(d.Price.ToString("#0.00"));
  3210. cells["F" + rowIndex.ToString()].PutValue(d.PriceCount);
  3211. cells["G" + rowIndex.ToString()].PutValue(d.Currency);
  3212. cells["H" + rowIndex.ToString()].PutValue(d.PriceSum.ToString("#0.00"));
  3213. cells["K" + rowIndex.ToString()].PutValue(d.Remark);
  3214. //样式
  3215. cells["B" + rowIndex.ToString()].SetStyle(style_dataCol);
  3216. cells["C" + rowIndex.ToString()].SetStyle(style_dataCol);
  3217. cells["D" + rowIndex.ToString()].SetStyle(style_dataCol);
  3218. cells["E" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3219. cells["F" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3220. cells["G" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3221. cells["H" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3222. cells["I" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3223. cells["J" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3224. cells["K" + rowIndex.ToString()].SetStyle(style_dataYellow);
  3225. //公式
  3226. cells["H" + rowIndex.ToString()].Formula = string.Format(@"E{0}*F{0}", rowIndex);
  3227. cells["J" + rowIndex.ToString()].Formula = string.Format(@"H{0}*I{0}", rowIndex);
  3228. cells.SetRowHeight(rowIndex - 1, 25);
  3229. rowIndex++;
  3230. }
  3231. cells["B" + rowIndex.ToString()].SetStyle(style_dataCol);
  3232. cells["C" + rowIndex.ToString()].SetStyle(style_dataCol);
  3233. cells["D" + rowIndex.ToString()].SetStyle(style_dataCol);
  3234. cells["E" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3235. cells["F" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3236. cells["G" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3237. cells["H" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3238. cells["I" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3239. cells["J" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3240. cells["K" + rowIndex.ToString()].SetStyle(style_dataYellow);
  3241. rowIndex++;
  3242. cells["B" + rowIndex.ToString()].SetStyle(style_dataCol);
  3243. cells["C" + rowIndex.ToString()].SetStyle(style_dataCol);
  3244. cells["D" + rowIndex.ToString()].SetStyle(style_dataCol);
  3245. cells["E" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3246. cells["F" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3247. cells["G" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3248. cells["H" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3249. cells["I" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3250. cells["J" + rowIndex.ToString()].SetStyle(style_dataBlue);
  3251. cells["K" + rowIndex.ToString()].SetStyle(style_dataYellow);
  3252. rowIndex = 3;
  3253. int tempPriceType = 0;
  3254. foreach (var c in dataList)
  3255. {
  3256. if (tempPriceType == c.PriceType)
  3257. {
  3258. continue;
  3259. }
  3260. tempPriceType = c.PriceType;
  3261. int _rowCount = countList.First(s => s.PriceType == tempPriceType).DataCount;
  3262. cells.Merge(rowIndex, 1, _rowCount, 1);
  3263. Aspose.Cells.Range tempRange = cells.CreateRange(rowIndex, 1, _rowCount, 1);
  3264. rowIndex += _rowCount;
  3265. }
  3266. rowIndex = 4 + dataList.Count;
  3267. cells["D" + rowIndex.ToString()].PutValue("合计");
  3268. cells["J" + rowIndex.ToString()].Formula = string.Format(@"SUM(J4,J{0})", rowIndex - 1);
  3269. cells.SetRowHeight(rowIndex - 1, 25);
  3270. rowIndex++;
  3271. cells["D" + rowIndex.ToString()].PutValue("服务费10%开票税金8%");
  3272. cells["J" + rowIndex.ToString()].Formula = string.Format(@"J{0}*1.1*1.08", rowIndex - 1);
  3273. cells.SetRowHeight(rowIndex - 1, 25);
  3274. #endregion
  3275. #region IO
  3276. System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流
  3277. string fileName = ("GroupExtraCost/超支费用(" + cellValue_Header + ").xlsx");
  3278. byte[] bt = ms.ToArray();
  3279. workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
  3280. #endregion
  3281. string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
  3282. return Ok(JsonView(true, "成功", new { url = rst }));
  3283. }
  3284. #endregion
  3285. #region 信用卡对账
  3286. /// <summary>
  3287. ///将指定的Excel的文件转换成DataTable (Excel的第一个sheet)
  3288. /// </summary>
  3289. /// <param name="fullFielPath">文件的绝对路径</param>
  3290. /// <returns></returns>
  3291. private DataTable WorksheetToTable(string fullFielPath, string sheetName = null)
  3292. {
  3293. //如果是“EPPlus”,需要指定LicenseContext。
  3294. //EPPlus.Core 不需要指定。
  3295. ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
  3296. FileInfo existingFile = new FileInfo(fullFielPath);
  3297. ExcelPackage package = new ExcelPackage(existingFile);
  3298. ExcelWorksheet worksheet = null;
  3299. if (string.IsNullOrEmpty(sheetName))
  3300. {
  3301. //不传入 sheetName 默认取第1个sheet。
  3302. //EPPlus 索引是0
  3303. //EPPlus.Core 索引是1
  3304. worksheet = package.Workbook.Worksheets[0];
  3305. }
  3306. else
  3307. {
  3308. worksheet = package.Workbook.Worksheets[sheetName];
  3309. }
  3310. if (worksheet == null)
  3311. throw new Exception("指定的sheetName不存在");
  3312. return WorksheetToTable(worksheet);
  3313. }
  3314. /// <summary>
  3315. /// 将worksheet转成datatable
  3316. /// </summary>
  3317. /// <param name="worksheet">待处理的worksheet</param>
  3318. /// <returns>返回处理后的datatable</returns>
  3319. private DataTable WorksheetToTable(ExcelWorksheet worksheet)
  3320. {
  3321. //获取worksheet的行数
  3322. int rows = worksheet.Dimension.End.Row;
  3323. //获取worksheet的列数
  3324. int cols = worksheet.Dimension.End.Column;
  3325. DataTable dt = new DataTable(worksheet.Name);
  3326. DataRow dr = null;
  3327. for (int i = 1; i <= rows; i++)
  3328. {
  3329. if (i > 1)
  3330. dr = dt.Rows.Add();
  3331. for (int j = 1; j <= cols; j++)
  3332. {
  3333. //默认将第一行设置为datatable的标题
  3334. if (i == 1)
  3335. dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
  3336. //剩下的写入datatable
  3337. else
  3338. dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
  3339. }
  3340. }
  3341. return dt;
  3342. }
  3343. private string GetString(object obj)
  3344. {
  3345. if (obj == null)
  3346. return "";
  3347. return obj.ToString();
  3348. }
  3349. private DataTable ExcelFileToDataTable(IFormFile file)
  3350. {
  3351. DataTable dtTest = null;
  3352. using (var stream = new MemoryStream())
  3353. {
  3354. file.CopyTo(stream);
  3355. using (var package = new ExcelPackage(stream))
  3356. {
  3357. ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
  3358. ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
  3359. dtTest = WorksheetToTable(worksheet);
  3360. }
  3361. }
  3362. return dtTest;
  3363. }
  3364. /// <summary>
  3365. /// 信用卡对账
  3366. /// </summary>
  3367. /// <param name="file"></param>
  3368. /// <param name="cardType"></param>
  3369. /// <param name="beginDt"></param>
  3370. /// <param name="endDt"></param>
  3371. /// <returns></returns>
  3372. [HttpPost]
  3373. [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
  3374. public async Task<IActionResult> PostCreditCardBill(IFormFile file, int cardType, string beginDt, string endDt)
  3375. {
  3376. if (file == null || file.Length < 1)
  3377. {
  3378. return Ok(JsonView(false, "请上传文件!"));
  3379. }
  3380. string fileName = file.FileName;
  3381. if (!fileName.EndsWith(".xlsx") && !fileName.EndsWith(".xls"))
  3382. {
  3383. return Ok(JsonView(false, "请上传Excel文件!"));
  3384. }
  3385. if (cardType < 1) return Ok(JsonView(false, "请传入有效的卡类型!"));
  3386. if (string.IsNullOrEmpty(beginDt) || string.IsNullOrEmpty(endDt)) return Ok(JsonView(false, "请输入开始/结束日期!"));
  3387. var beginValid = DateTime.TryParse(beginDt, out _);
  3388. var endValid = DateTime.TryParse(endDt, out _);
  3389. if (!beginValid || !endValid) return Ok(JsonView(false, "请输入正确的日期格式"));
  3390. //读取ExcelFile
  3391. DataTable dt = ExcelFileToDataTable(file);
  3392. dt.TableName = "TB";
  3393. if (dt == null)
  3394. {
  3395. return Ok(JsonView(false, $"您上传的Excel工作表没有内容,请检查!!!"));
  3396. }
  3397. //信用卡信息
  3398. string sql = string.Format($"Select * From Grp_CreditCardPayment Where Isdel = 0 And CTDId = {cardType} And ConsumptionDate between '{beginDt}' and '{endDt}' ");
  3399. var List_ccp = await _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql).ToListAsync();
  3400. if (List_ccp.Count < 1)
  3401. {
  3402. return Ok(JsonView(false, $"未查询到 {beginDt}{endDt} 信用卡账单信息!!!!"));
  3403. }
  3404. //资源信息
  3405. var delegationInfos = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0).ToList();
  3406. var users = _sqlSugar.Queryable<Sys_Users>().Where(it => it.IsDel == 0).ToList();
  3407. string url = string.Empty;
  3408. try
  3409. {
  3410. /*
  3411. * 卡类型
  3412. * 74 美元卡 1
  3413. * 75 欧元卡 1
  3414. * 86 招行卡 1
  3415. * 346 中信卡 1
  3416. * 363 交行卡 1
  3417. *
  3418. */
  3419. var ids = new List<int>();
  3420. if (cardType == 74) //美元卡
  3421. {
  3422. var AirGroupReuslt = List_ccp.Where(x => x.CTable == 85).GroupBy(x => x.DIId).ToList();
  3423. if (AirGroupReuslt != null && AirGroupReuslt.Count > 0)
  3424. {
  3425. foreach (var item in AirGroupReuslt)
  3426. {
  3427. if (item.Count() > 1)
  3428. {
  3429. var obj = item.FirstOrDefault();
  3430. obj.RMBPrice = item.Sum(x => x.PayMoney);//合计币种金额
  3431. obj.Remark = "团组机票总价(虚拟)";
  3432. List_ccp.Add(obj);
  3433. }
  3434. }
  3435. }
  3436. //dt.AcceptChanges(); //提交
  3437. //修改table列名
  3438. dt.Columns[0].ColumnName = "accountType"; // 账户类型
  3439. dt.Columns[1].ColumnName = "tradeDate"; //交易日期
  3440. dt.Columns[2].ColumnName = "BillingDate"; // 记账日期
  3441. dt.Columns[3].ColumnName = "CardNo"; // 卡号
  3442. dt.Columns[4].ColumnName = "deposit"; // 存入金额
  3443. dt.Columns[5].ColumnName = "SpendingAmount"; // 支出金额
  3444. dt.Columns[6].ColumnName = "TransactionDescription"; // 交易描述
  3445. dt.Columns[7].ColumnName = "TeamRemark"; // 团组备注描述
  3446. dt.Columns[8].ColumnName = "Handlers"; // 经手人
  3447. dt.Columns[9].ColumnName = "State"; // 状态
  3448. //删除第一行数据
  3449. if (dt.Rows.Count > 0)
  3450. {
  3451. string accountType = dt.Rows[0]["accountType"].ToString();
  3452. if (dt.Rows[0]["accountType"].ToString().Equals("账户类型"))
  3453. {
  3454. dt.Rows[0].Delete();
  3455. dt.AcceptChanges(); //提交
  3456. }
  3457. }
  3458. foreach (DataRow item in dt.Rows)
  3459. {
  3460. #region 匹配的金额
  3461. decimal ExcelAmount = 0.00M;
  3462. decimal deposit = 0.00M;
  3463. if (!string.IsNullOrEmpty(item["SpendingAmount"].ToString()))
  3464. {
  3465. var isParase = decimal.TryParse(item["SpendingAmount"].ToString(), out ExcelAmount);
  3466. if (isParase)
  3467. {
  3468. var CList = List_ccp.FindAll(x => x.PayMoney == ExcelAmount);
  3469. if (CList != null && CList.Count > 0)
  3470. {
  3471. item["TeamRemark"] = delegationInfos.Find(it => it.Id == CList.First().DIId)?.TeamName;
  3472. item["Handlers"] = users.Find(it => it.Id == CList.First().CreateUserId)?.CnName;
  3473. item["State"] = 1;
  3474. }
  3475. }
  3476. }
  3477. if (!string.IsNullOrEmpty(item["deposit"].ToString()))
  3478. {
  3479. var isParse = decimal.TryParse(item["deposit"].ToString(), out deposit);
  3480. if (isParse)
  3481. {
  3482. var CList = List_ccp.FindAll(x => x.PayMoney == deposit);
  3483. if (CList != null && CList.Count > 0)
  3484. {
  3485. item["TeamRemark"] = delegationInfos.Find(it => it.Id == CList.First().DIId)?.TeamName;
  3486. item["Handlers"] = users.Find(it => it.Id == CList.First().CreateUserId)?.CnName;
  3487. item["State"] = 1;
  3488. }
  3489. }
  3490. }
  3491. #endregion
  3492. //交易描述
  3493. if (!string.IsNullOrEmpty(item["TransactionDescription"].ToString()))
  3494. {
  3495. string TransactionDescription = item["TransactionDescription"].ToString();
  3496. var startIndex = TransactionDescription.LastIndexOf("[");
  3497. var endIndex = TransactionDescription.LastIndexOf("]");
  3498. if (startIndex != -1 && endIndex != -1)
  3499. {
  3500. var moenyList = TransactionDescription.Substring(startIndex + 1, endIndex - startIndex - 1).Split(' ').
  3501. Where(x => !string.IsNullOrEmpty(x)).ToList();
  3502. decimal money = 0.00M;
  3503. foreach (var itemMoeny in moenyList)
  3504. {
  3505. if (itemMoeny.Contains('.'))
  3506. {
  3507. string itemMoenyStr = itemMoeny.Replace(",", string.Empty);
  3508. bool istrue = decimal.TryParse(itemMoenyStr, out money);
  3509. if (istrue)
  3510. {
  3511. var CList = List_ccp.FindAll(x => x.PayMoney == money);
  3512. if (CList != null && CList.Count > 0)
  3513. {
  3514. item["TeamRemark"] = delegationInfos.Find(it => it.Id == CList.First().DIId)?.TeamName;
  3515. item["Handlers"] = users.Find(it => it.Id == CList.First().CreateUserId)?.CnName;
  3516. item["State"] = 1;
  3517. }
  3518. }
  3519. }
  3520. }
  3521. }
  3522. }
  3523. }
  3524. string fileName1 = $"信用卡账单(美元卡){DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
  3525. Dictionary<string, object> pairs = new Dictionary<string, object>();
  3526. List<DataTable> datas = new List<DataTable>();
  3527. datas.Add(dt);
  3528. url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-美元卡.xls", $"CreditCardBill", fileName1, pairs, datas);
  3529. }
  3530. else if (cardType == 75) //欧元卡
  3531. {
  3532. if (dt.Rows.Count > 0)
  3533. {
  3534. string accountType = dt.Rows[0][0].ToString();
  3535. if (accountType.Equals("账户类型"))
  3536. {
  3537. dt.Rows[0].Delete();
  3538. dt.AcceptChanges(); //提交
  3539. }
  3540. }
  3541. //dt.Columns[0].ColumnName = "All";
  3542. DataTable dt1 = new DataTable();
  3543. dt1.TableName = "TB";
  3544. dt1.Columns.Add("TradingDate", Type.GetType("System.String"));//交易日期
  3545. dt1.Columns.Add("TallyDate", Type.GetType("System.String")); //记账日期
  3546. dt1.Columns.Add("TradindDesc", Type.GetType("System.String"));//交易描述
  3547. dt1.Columns.Add("TradindMoney", Type.GetType("System.String"));//交易币种/金额
  3548. dt1.Columns.Add("ClearindMoney", Type.GetType("System.String"));// 清算币种/金额
  3549. dt1.Columns.Add("TeamRemark", Type.GetType("System.String")); //团组备注描述
  3550. dt1.Columns.Add("Handlers", Type.GetType("System.String")); //经手人
  3551. dt1.Columns.Add("State", Type.GetType("System.String")); //状态
  3552. for (int i = 0; i < dt.Rows.Count; i++)
  3553. {
  3554. string[] str = dt.Rows[i]["All"].ToString().Split(' ');
  3555. //string[] str = dt.Rows[i];
  3556. DataRow dr = dt1.NewRow();
  3557. dr["TradingDate"] = str[0].ToString();
  3558. dr["TallyDate"] = str[1].ToString();
  3559. dr["TradindDesc"] = str[2].ToString() + " " + str[3].ToString();
  3560. dr["TradindMoney"] = str[4].ToString() + " " + str[5].ToString();
  3561. dr["ClearindMoney"] = str[6].ToString() + " " + str[7].ToString();
  3562. dt1.Rows.Add(dr);
  3563. }
  3564. foreach (DataRow item in dt1.Rows)
  3565. {
  3566. for (int i = 0; i < List_ccp.Count; i++)
  3567. {
  3568. DateTime dtTime1 = Convert.ToDateTime(item["TradingDate"].ToString() == "" ? DateTime.Now.ToString("MM/dd") : item["TradingDate"].ToString()); //交易日期
  3569. string USDPrice = item["TradindMoney"].ToString(); //支出金额
  3570. USDPrice = USDPrice.Split(' ')[1].Trim(); //替换掉非数字
  3571. //USDPrice = USDPrice.Split('/')[1];
  3572. // 经手人 and excel行状态
  3573. if (!string.IsNullOrEmpty(List_ccp[i].ConsumptionDate) && !string.IsNullOrEmpty(USDPrice))
  3574. {
  3575. DateTime dtTime2 = Convert.ToDateTime(List_ccp[i].ConsumptionDate.ToString() == "" ? DateTime.Now.ToString("MM/dd") : List_ccp[i].ConsumptionDate.ToString());
  3576. float price1 = float.Parse(USDPrice);
  3577. float price2 = float.Parse(List_ccp[i].PayMoney.ToString("F2"));
  3578. if (dtTime1 == dtTime2 && price1 == price2)
  3579. {
  3580. item["TeamRemark"] = delegationInfos.Find(it => it.Id == List_ccp[i].DIId)?.TeamName;
  3581. item["Handlers"] = users.Find(it => it.Id == List_ccp[i].CreateUserId)?.CnName;
  3582. item["State"] = "1";
  3583. i = List_ccp.Count - 1;
  3584. }
  3585. else item["State"] = "0";
  3586. }
  3587. else item["State"] = "0";
  3588. }
  3589. if (item["TradindDesc"].ToString().Contains("财付通"))
  3590. item["State"] = "2";
  3591. else if (item["TradindDesc"].ToString().Contains("京东"))
  3592. item["State"] = "2";
  3593. else if (item["TradindDesc"].ToString().Contains("微信支付"))
  3594. item["State"] = "2";
  3595. else if (item["TradindDesc"].ToString().Contains("淘宝"))
  3596. item["State"] = "2";
  3597. }
  3598. string fileName1 = $"信用卡账单(欧元卡){DateTime.Now.ToString("yyyy.MM.dd")}.xls";
  3599. Dictionary<string, object> pairs = new Dictionary<string, object>();
  3600. List<DataTable> datas = new List<DataTable>();
  3601. datas.Add(dt);
  3602. url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-欧元卡.xls", "TB", fileName1, pairs, datas);
  3603. }
  3604. else if (cardType == 86) //招行卡
  3605. {
  3606. var AirGroupReuslt1 = List_ccp.Where(x => x.CTable == 85).GroupBy(x => x.DIId).ToList();
  3607. if (AirGroupReuslt1 != null && AirGroupReuslt1.Count > 0)
  3608. {
  3609. foreach (var item in AirGroupReuslt1)
  3610. {
  3611. if (item.Count() > 1)
  3612. {
  3613. var di = delegationInfos.Find(it => it.Id == item.Key);
  3614. var obj = item.FirstOrDefault();
  3615. obj.Remark = "团组机票总价";
  3616. if (di != null)
  3617. {
  3618. obj.Remark = "团组机票总价(" + di.TeamName + ")";
  3619. }
  3620. obj.RMBPrice = item.Sum(x => x.RMBPrice);//合计人民币
  3621. List_ccp.Add(obj);
  3622. }
  3623. }
  3624. }
  3625. //查询所有刷卡的团组
  3626. //将刷卡团组内刷卡金额的金额存入ccp_list
  3627. //将excel作比较
  3628. //机票刷卡记录
  3629. var AirListAndPayID = List_ccp.Where(x => x.CTable == 85 && x.PayDId == 72).Select(x => x.DIId).Distinct().ToList();
  3630. var AirData = _sqlSugar.Queryable<Grp_AirTicketReservations>().Where(it => AirListAndPayID.Contains(it.DIId) && it.PrePrice != it.Price).ToList();
  3631. foreach (var item in AirData)
  3632. {
  3633. var DBwhere = List_ccp.Find(x => x.PayMoney == item.PrePrice);
  3634. if (DBwhere == null)
  3635. {
  3636. List_ccp.Add(new Grp_CreditCardPayment
  3637. {
  3638. PayMoney = item.PrePrice,
  3639. DIId = item.DIId,
  3640. CreateUserId = item.CreateUserId,
  3641. Remark = "(机票单价价格)",
  3642. });
  3643. }
  3644. }
  3645. var HotelGroupReuslt = List_ccp.Where(x => x.CTable == 76).GroupBy(x => x.DIId).ToList();
  3646. if (HotelGroupReuslt != null && HotelGroupReuslt.Count > 0)
  3647. {
  3648. foreach (var item in HotelGroupReuslt)
  3649. {
  3650. if (item.Count() > 1)
  3651. {
  3652. var di = delegationInfos.Find(it => it.Id == item.Key);
  3653. var obj = item.FirstOrDefault();
  3654. obj.Remark = "团组酒店总价";
  3655. if (di != null)
  3656. {
  3657. obj.Remark = "团组酒店总价(" + di.TeamName + ")";
  3658. }
  3659. obj.RMBPrice = item.Sum(x => x.RMBPrice);//合计人民币
  3660. List_ccp.Add(obj);
  3661. }
  3662. }
  3663. }
  3664. #region
  3665. dt.Rows[0].Delete();//删除列名行
  3666. dt.AcceptChanges();
  3667. DataTable dt1 = new DataTable();
  3668. dt1.TableName = "TB";
  3669. dt1.Columns.Add("TradingDay", Type.GetType("System.String")); // 交易日
  3670. dt1.Columns.Add("TallyDay", Type.GetType("System.String")); // 记账日
  3671. dt1.Columns.Add("TransactionDesc", Type.GetType("System.String")); // 交易描述
  3672. dt1.Columns.Add("RMBMoney", Type.GetType("System.String")); // 人名币金额
  3673. dt1.Columns.Add("CardNo", Type.GetType("System.String")); // 卡号
  3674. dt1.Columns.Add("TradingMoney", Type.GetType("System.String")); // 交易地金额
  3675. dt1.Columns.Add("Currency", Type.GetType("System.String")); // 币种
  3676. dt1.Columns.Add("TeamRemark", Type.GetType("System.String")); //团组备注描述
  3677. dt1.Columns.Add("Handlers", Type.GetType("System.String")); //经手人
  3678. dt1.Columns.Add("State", Type.GetType("System.String")); //状态
  3679. for (int j = 0; j < dt.Rows.Count; j++)//遍历行
  3680. {
  3681. DataRow dr = dt1.NewRow(); //获取每一行的数据
  3682. dr["TradingDay"] = dt.Rows[j][0].ToString();
  3683. dr["TallyDay"] = dt.Rows[j][1].ToString();
  3684. dr["TransactionDesc"] = dt.Rows[j][2].ToString();
  3685. dr["RMBMoney"] = dt.Rows[j][3].ToString();
  3686. dr["CardNo"] = dt.Rows[j][4].ToString();
  3687. string currency = "CN";
  3688. string money = dt.Rows[j][5].ToString();
  3689. if (money.Contains('('))
  3690. {
  3691. string[] strs = money.Split('(');
  3692. money = strs[0];
  3693. currency = strs[1].Replace(")", "");
  3694. }
  3695. dr["TradingMoney"] = money;
  3696. dr["Currency"] = currency;
  3697. if (string.IsNullOrEmpty(money))
  3698. {
  3699. continue;
  3700. }
  3701. dt1.Rows.Add(dr);
  3702. }
  3703. //datatable 排序
  3704. dt1.DefaultView.Sort = "TradingDay asc";
  3705. dt1 = dt1.DefaultView.ToTable();
  3706. foreach (DataRow item in dt1.Rows)
  3707. {
  3708. if (item["TradingDay"] == "" && item["RMBMoney"] == "")
  3709. {
  3710. continue;
  3711. }
  3712. //excel获取交易日期
  3713. string ExcelDt = string.Empty;
  3714. //C表交易日期
  3715. string DBDt = string.Empty;
  3716. //excel交易的金额
  3717. decimal TradingMoney = decimal.Parse(item["TradingMoney"].ToString());
  3718. if (item["TradingDay"] != null)
  3719. {
  3720. ExcelDt = DateTime.Parse(item["TradingDay"].ToString()).ToString("MMdd");
  3721. }
  3722. //加一获取金额区间
  3723. decimal MaxTradingMoney = TradingMoney + 1;
  3724. decimal MinTradingMoney = TradingMoney - 1;
  3725. //匹配C表金额相等的值
  3726. var CList = List_ccp.FindAll(x => x.PayMoney < MaxTradingMoney && x.PayMoney > MinTradingMoney);
  3727. if (CList != null && CList.Count > 0)
  3728. {
  3729. #region 日期匹配
  3730. foreach (var Citem in CList)
  3731. {
  3732. if (!string.IsNullOrWhiteSpace(Citem.ConsumptionDate))
  3733. {
  3734. DBDt = DateTime.Parse(Citem.ConsumptionDate).ToString("MMdd");
  3735. if (DBDt == ExcelDt)
  3736. {
  3737. item["TeamRemark"] = delegationInfos.Find(it => it.Id == Citem.DIId)?.TeamName;
  3738. item["Handlers"] = users.Find(it => it.Id == Citem.CreateUserId)?.CnName;
  3739. ids.Add(Citem.Id);
  3740. }
  3741. }
  3742. }
  3743. #endregion
  3744. }
  3745. if (item["TransactionDesc"].ToString().Contains("财付通"))
  3746. item["State"] = "2";
  3747. else if (item["TransactionDesc"].ToString().Contains("京东支付"))
  3748. item["State"] = "2";
  3749. else if (item["TransactionDesc"].ToString().Contains("微信支付"))
  3750. item["State"] = "2";
  3751. else if (item["TransactionDesc"].ToString().Contains("淘宝"))
  3752. item["State"] = "2";
  3753. else if (item["TransactionDesc"].ToString().Contains("支付宝"))
  3754. item["State"] = "2";
  3755. else if (item["TransactionDesc"].ToString().Contains("拼多多"))
  3756. item["State"] = "2";
  3757. else
  3758. item["State"] = "0";
  3759. if (!string.IsNullOrEmpty(item["TeamRemark"].ToString()) && !string.IsNullOrEmpty(item["Handlers"].ToString()))
  3760. item["State"] = "1";
  3761. }
  3762. //更改匹配项状态
  3763. if (ids.Count > 0)
  3764. {
  3765. var _CreditCardPayments = new List<Grp_CreditCardPayment>();
  3766. foreach (var item in ids)
  3767. {
  3768. _CreditCardPayments.Add(new Grp_CreditCardPayment() { Id = item, IsMatchCreditCard = 1.00M });
  3769. }
  3770. var updateStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>(_CreditCardPayments)
  3771. .UpdateColumns(it => new { it.IsMatchCreditCard })
  3772. .Where(it => ids.Contains(it.Id))
  3773. .ExecuteCommand();
  3774. }
  3775. string fileName1 = $"信用卡账单(招行卡){DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
  3776. Dictionary<string, object> pairs = new Dictionary<string, object>();
  3777. List<DataTable> datas = new List<DataTable>();
  3778. datas.Add(dt1);
  3779. url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-招行卡.xls", "CreditCardBill", fileName1, pairs, datas);
  3780. #endregion
  3781. }
  3782. else if (cardType == 346) //中信卡
  3783. {
  3784. dt.Columns.Add("TeamRemark", Type.GetType("System.String")); //团组备注描述
  3785. dt.Columns.Add("Handlers", Type.GetType("System.String")); //经手人
  3786. dt.Columns.Add("State", Type.GetType("System.String")); //状态
  3787. //修改table列名
  3788. dt.Columns[0].ColumnName = "TransactionDesc"; // 交易描述
  3789. dt.Columns[1].ColumnName = "TradingCurrencyAndMoney"; // 交易币种和金额
  3790. dt.Columns[2].ColumnName = "TransactionDate"; // 交易日期
  3791. dt.Columns[3].ColumnName = "SettlementCurrencyAndMoney";// 结算币种和金额
  3792. dt.Columns[4].ColumnName = "BookedDate"; // 入账日期
  3793. foreach (DataRow item in dt.Rows)
  3794. {
  3795. for (int i = 0; i < List_ccp.Count; i++)
  3796. {
  3797. if (string.IsNullOrEmpty(item["TransactionDate"].ToString()))
  3798. break;
  3799. DateTime dtTime1 = Convert.ToDateTime(item["TransactionDate"].ToString()); //交易日期
  3800. string USDPrice = item["TradingCurrencyAndMoney"].ToString(); //支出金额
  3801. USDPrice = USDPrice.Split('/')[1];
  3802. //string USDPrice1 = USDPrice.ToString("0.00");
  3803. // 经手人 and excel行状态
  3804. if (!string.IsNullOrEmpty(List_ccp[i].ConsumptionDate) && !string.IsNullOrEmpty(USDPrice))
  3805. {
  3806. DateTime dtTime2 = Convert.ToDateTime(List_ccp[i].ConsumptionDate);
  3807. float price1 = float.Parse(USDPrice);
  3808. float price2 = float.Parse(List_ccp[i].PayMoney.ToString("F2"));
  3809. if (dtTime1 == dtTime2 && price1 == price2)
  3810. {
  3811. item["TeamRemark"] = delegationInfos.Find(it => it.Id == List_ccp[i].DIId)?.TeamName;
  3812. item["Handlers"] = users.Find(it => it.Id == List_ccp[i].CreateUserId)?.CnName;
  3813. item["State"] = "1";
  3814. i = List_ccp.Count - 1;
  3815. }
  3816. else
  3817. item["State"] = "0";
  3818. }
  3819. else
  3820. item["State"] = "0";
  3821. }
  3822. //判断是否是数字
  3823. //if (IsNumber(item["TradingCurrencyAndMoney"].ToString().Split('/')[1]) == false)
  3824. // item["State"] = "0";
  3825. if (item["TransactionDesc"].ToString().Contains("财付通"))
  3826. item["State"] = "2";
  3827. else if (item["TransactionDesc"].ToString().Contains("京东"))
  3828. item["State"] = "2";
  3829. else if (item["TransactionDesc"].ToString().Contains("微信支付"))
  3830. item["State"] = "2";
  3831. else if (item["TransactionDesc"].ToString().Contains("淘宝"))
  3832. item["State"] = "2";
  3833. }
  3834. #region DownExcel
  3835. string fileName1 = $"信用卡账单(中信卡){DateTime.Now.ToString("yyyy.MM.dd")}.xls";
  3836. Dictionary<string, object> pairs = new Dictionary<string, object>();
  3837. List<DataTable> datas = new List<DataTable>();
  3838. datas.Add(dt);
  3839. url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-中信卡.xls", "TB", fileName1, pairs, datas);
  3840. #endregion
  3841. }
  3842. else if (cardType == 363) //交行卡
  3843. {
  3844. dt.Columns[0].ColumnName = "All";
  3845. DataTable dt1 = new DataTable();
  3846. dt1.Columns.Add("TradingDate", Type.GetType("System.String"));//交易日期
  3847. dt1.Columns.Add("TallyDate", Type.GetType("System.String")); //记账日期
  3848. dt1.Columns.Add("TradindDesc", Type.GetType("System.String"));//交易描述
  3849. dt1.Columns.Add("TradindMoney", Type.GetType("System.String"));//交易币种/金额
  3850. dt1.Columns.Add("ClearindMoney", Type.GetType("System.String"));// 清算币种/金额
  3851. dt1.Columns.Add("TeamRemark", Type.GetType("System.String")); //团组备注描述
  3852. dt1.Columns.Add("Handlers", Type.GetType("System.String")); //经手人
  3853. dt1.Columns.Add("State", Type.GetType("System.String")); //状态
  3854. for (int i = 0; i < dt.Rows.Count; i++)
  3855. {
  3856. string[] str = dt.Rows[i]["All"].ToString().Split(' ');
  3857. DataRow dr = dt1.NewRow();
  3858. dr["TradingDate"] = str[0].ToString();
  3859. dr["TallyDate"] = str[1].ToString();
  3860. dr["TradindDesc"] = str[2].ToString() + " " + str[3].ToString();
  3861. dr["TradindMoney"] = str[4].ToString() + " " + str[5].ToString();
  3862. dr["ClearindMoney"] = str[6].ToString() + " " + str[7].ToString();
  3863. dt1.Rows.Add(dr);
  3864. }
  3865. foreach (DataRow item in dt1.Rows)
  3866. {
  3867. for (int i = 0; i < List_ccp.Count; i++)
  3868. {
  3869. DateTime dtTime1 = Convert.ToDateTime(item["TradingDate"].ToString() == "" ? DateTime.Now.ToString("MM/dd") : item["TradingDate"].ToString()); //交易日期
  3870. string USDPrice = item["TradindMoney"].ToString(); //支出金额
  3871. USDPrice = USDPrice.Split(' ')[1].Trim(); //替换掉非数字
  3872. //USDPrice = USDPrice.Split('/')[1];
  3873. // 经手人 and excel行状态
  3874. if (!string.IsNullOrEmpty(List_ccp[i].ConsumptionDate) && !string.IsNullOrEmpty(USDPrice))
  3875. {
  3876. DateTime dtTime2 = Convert.ToDateTime(List_ccp[i].ConsumptionDate.ToString() == "" ? DateTime.Now.ToString("MM/dd") : List_ccp[i].ConsumptionDate.ToString());
  3877. float price1 = float.Parse(USDPrice);
  3878. float price2 = float.Parse(List_ccp[i].PayMoney.ToString("F2"));
  3879. if (dtTime1 == dtTime2 && price1 == price2)
  3880. {
  3881. item["TeamRemark"] = delegationInfos.Find(it => it.Id == List_ccp[i].DIId)?.TeamName;
  3882. item["Handlers"] = users.Find(it => it.Id == List_ccp[i].CreateUserId)?.CnName;
  3883. item["State"] = "1";
  3884. i = List_ccp.Count - 1;
  3885. }
  3886. else
  3887. item["State"] = "0";
  3888. }
  3889. else
  3890. item["State"] = "0";
  3891. }
  3892. if (item["TradindDesc"].ToString().Contains("财付通"))
  3893. item["State"] = "2";
  3894. else if (item["TradindDesc"].ToString().Contains("京东"))
  3895. item["State"] = "2";
  3896. else if (item["TradindDesc"].ToString().Contains("微信支付"))
  3897. item["State"] = "2";
  3898. else if (item["TradindDesc"].ToString().Contains("淘宝"))
  3899. item["State"] = "2";
  3900. }
  3901. string fileName1 = $"信用卡账单(交行卡){DateTime.Now.ToString("yyyy.MM.dd")}.xls";
  3902. Dictionary<string, object> pairs = new Dictionary<string, object>();
  3903. List<DataTable> datas = new List<DataTable>();
  3904. datas.Add(dt);
  3905. url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-交行卡.xls", "TB", fileName1, pairs, datas);
  3906. }
  3907. }
  3908. catch (Exception ex)
  3909. {
  3910. return Ok(JsonView(false, $"匹配失败,{ex.Message}!"));
  3911. }
  3912. return Ok(JsonView(true, "操作成功", new { url = url }));
  3913. }
  3914. /// <summary>
  3915. /// 把数据从Excel装载到DataTable
  3916. /// </summary>
  3917. /// <param name="pathName">带路径的Excel文件名</param>
  3918. /// <param name="sheetName">工作表名</param>
  3919. /// <param name="tbContainer">将数据存入的DataTable</param>
  3920. /// <returns></returns>
  3921. public static DataTable ExcelToDataTable(string pathName, string sheetName)
  3922. {
  3923. DataTable tbContainer = new DataTable();
  3924. string strConn = string.Empty;
  3925. if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; }
  3926. FileInfo file = new FileInfo(pathName);
  3927. if (!file.Exists) { throw new Exception("文件不存在"); }
  3928. string extension = file.Extension;
  3929. switch (extension)
  3930. {
  3931. case ".xls":
  3932. strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
  3933. break;
  3934. case ".xlsx":
  3935. strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
  3936. break;
  3937. default:
  3938. strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
  3939. break;
  3940. }
  3941. //链接Excel
  3942. OleDbConnection cnnxls = new OleDbConnection(strConn);
  3943. //读取Excel里面有 表Sheet1
  3944. System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
  3945. DataSet ds = new DataSet();
  3946. //将Excel里面有表内容装载到内存表中!
  3947. oda.Fill(tbContainer);
  3948. return tbContainer;
  3949. }
  3950. #endregion
  3951. #region 提成计算
  3952. /// <summary>
  3953. /// 提成计算
  3954. /// </summary>
  3955. /// <param name="dto"></param>
  3956. /// <returns></returns>
  3957. [HttpPost]
  3958. public IActionResult ComputeRoyalties(ComputeRoyaltiesDto dto)
  3959. {
  3960. var jw = JsonView(false);
  3961. var userId = dto.UserId;
  3962. var user = _sqlSugar.Queryable<Sys_Users>().First(x=>x.Id == userId);
  3963. if (user == null)
  3964. {
  3965. jw.Msg = "暂无该用户!";
  3966. return Ok(jw);
  3967. }
  3968. if (!DateTime.TryParse(dto.StartDate,out DateTime StartDateTime))
  3969. {
  3970. jw.Msg = "开始时间格式有误!";
  3971. return Ok(jw);
  3972. }
  3973. if (!DateTime.TryParse(dto.EndDate, out DateTime EndDateTime))
  3974. {
  3975. jw.Msg = "结束时间格式有误!";
  3976. return Ok(jw);
  3977. }
  3978. if (DateTime.Compare(StartDateTime,EndDateTime) > 0)
  3979. {
  3980. jw.Msg = "开始时间大于结束时间!";
  3981. return Ok(jw);
  3982. }
  3983. //机票 职位表id 24
  3984. int[] AirTicket = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.JobPostId == 24).Select(x => x.Id).ToArray();
  3985. //酒店 职位表id 25
  3986. int[] Hotel = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.JobPostId == 25).Select(x => x.Id).ToArray();
  3987. //OP 职位表id 28
  3988. int [] OP = _sqlSugar.Queryable<Sys_Users>().Where(x=>x.IsDel == 0 && x.JobPostId == 28).Select(x=>x.Id).ToArray();
  3989. //商邀 职位表id 27
  3990. int [] Invitation = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.JobPostId == 27).Select(x => x.Id).ToArray();
  3991. //签证 职位表id 26
  3992. int[] Visa = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.JobPostId == 26 ).Select(x => x.Id).ToArray();
  3993. //主管职位
  3994. //string[] Manager = { "149", "283" };
  3995. ArrayList resultArr = new ArrayList();
  3996. var teamLv = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.STid == 56 && x.IsDel == 0).ToList();
  3997. if (AirTicket.Contains(userId))
  3998. {
  3999. //提成方式:¥10/人×人数×段数(每个组团涉及的换机次数)
  4000. var listAir = _sqlSugar.Queryable<Grp_AirTicketReservations, Grp_DelegationInfo, Grp_CreditCardPayment>((x, a, b) => new
  4001. JoinQueryInfos(
  4002. JoinType.Left, a.Id == x.DIId ,
  4003. JoinType.Left, b.CTable == 85 && b.CId == x.Id )
  4004. ).Where((x, a, b) => x.IsDel == 0 && x.CreateUserId == userId && (b.IsAuditGM == 1 || b.IsAuditGM == 3)
  4005. && a.IsDel == 0 && a.VisitDate > StartDateTime && a.VisitDate < EndDateTime && b.IsDel == 0)
  4006. .Select((x, a, b) => new
  4007. {
  4008. x.FlightsDescription,
  4009. a.Id,
  4010. a.TeamName,
  4011. a.VisitDate,
  4012. a.TeamLevSId,
  4013. a.VisitPNumber,
  4014. x.Price,
  4015. x.ClientName,
  4016. x.ClientNum,
  4017. x.FlightsCode,
  4018. }).ToList();
  4019. var groupByid = listAir.GroupBy(x => x.Id);
  4020. foreach (var airArr in groupByid)
  4021. {
  4022. var groupAirCount = 0; //团组航段数量
  4023. var infos = new ArrayList();
  4024. foreach (var air in airArr)
  4025. {
  4026. var airCount = 0; // 单条航段数量
  4027. if (air.FlightsDescription.IsNullOrWhiteSpace())
  4028. {
  4029. continue;
  4030. }
  4031. if (air.ClientName.Contains("-1") || air.ClientName.Contains("行程单") || air.FlightsCode.Contains("行程单"))
  4032. {
  4033. continue;
  4034. }
  4035. string errMsg = string.Empty;
  4036. if (air.FlightsDescription.Contains("\r\n"))
  4037. {
  4038. var rowCode = Regex.Split(air.FlightsDescription, "\\d+\\.", RegexOptions.IgnoreCase).Where(x=>!string.IsNullOrWhiteSpace(x)).ToArray();
  4039. for (int i = 0; i < rowCode.Length; i++)
  4040. {
  4041. if (i != 0)
  4042. {
  4043. try
  4044. {
  4045. var columnValue = rowCode[i].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
  4046. string day = columnValue[2].Substring(2, 2);//日
  4047. string monthAbbreviations = columnValue[2].Substring(4, 3).ToUpper();//月份
  4048. monthAbbreviations = GeneralMethod.GetLonger(monthAbbreviations);
  4049. string EndHHmm = columnValue[5].Substring(0, 2) + ":" + columnValue[5].Substring(2, 2);
  4050. DateTime EndData = DateTime.Parse($"{DateTime.Now.Year}-{monthAbbreviations}-{day} {EndHHmm}");//当前班次
  4051. var spDotandEmpty1 = rowCode[i - 1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
  4052. string day1 = spDotandEmpty1[2].Substring(2, 2);//日
  4053. string monthAbbreviations1 = spDotandEmpty1[2].Substring(4, 3).ToUpper();//月份
  4054. monthAbbreviations1 = GeneralMethod.GetLonger(monthAbbreviations1);
  4055. string HHmm = spDotandEmpty1[6].Substring(0, 2) + ":" + spDotandEmpty1[6].Substring(2, 2);
  4056. DateTime data = DateTime.Parse($"{DateTime.Now.Year}-{monthAbbreviations1}-{day1} {HHmm}");//上个班次
  4057. TimeSpan t3 = EndData - data; //两个时间相减 。默认得到的是 两个时间之间的天数 得到:365.00:00:00
  4058. double getHour = t3.TotalHours;
  4059. if (getHour >= 6)
  4060. {
  4061. if (air.Price > 0)
  4062. {
  4063. airCount += 1;
  4064. }
  4065. else
  4066. {
  4067. airCount -= 1;
  4068. }
  4069. }
  4070. }
  4071. catch (Exception ex)
  4072. {
  4073. errMsg = "机票行程代码格式有误!解析失败!" + $"({ex.Message})";
  4074. break;
  4075. }
  4076. }
  4077. else
  4078. {
  4079. if (air.Price > 0)
  4080. {
  4081. airCount += 1;
  4082. }
  4083. else
  4084. {
  4085. airCount -= 1;
  4086. }
  4087. }
  4088. }
  4089. }
  4090. else
  4091. {
  4092. if (air.Price > 0)
  4093. {
  4094. airCount++;
  4095. }
  4096. else
  4097. {
  4098. airCount--;
  4099. }
  4100. }
  4101. string info = $"航班{air.FlightsCode} {(air.Price > 0 ? "" : "此笔为退费!")} 人数为{air.ClientNum} 有效段数为{airCount} 总段数为{airCount * air.ClientNum}";
  4102. if (!string.IsNullOrWhiteSpace(errMsg))
  4103. {
  4104. info = $"航班{air.FlightsCode} " + errMsg ;
  4105. }
  4106. infos.Add(info);
  4107. groupAirCount += airCount * air.ClientNum;
  4108. }
  4109. var groupinfo = listAir.First(x => x.Id == airArr.Key);
  4110. string temp = "本团人数" + Convert.ToInt32(groupinfo.VisitPNumber) + ",该人员" + user.CnName + "共订航段数" + groupAirCount.ToString() + ",每程航班提成为10元," + user.CnName + "提成共" + (groupAirCount * 10).ToString("#0.00");
  4111. resultArr.Add(new
  4112. {
  4113. groupinfo.TeamName,
  4114. groupinfo.Id,
  4115. chiArr = infos,
  4116. teamLvStr = teamLv.Find(x=>x.Id == groupinfo.TeamLevSId)?.Name,
  4117. groupinfo.VisitDate,
  4118. temp,
  4119. price = (groupAirCount * 10),
  4120. }) ;
  4121. }
  4122. }
  4123. else if (Hotel.Contains(userId))
  4124. {
  4125. //标准团组:¥10/间/晚 * 实际订房间数 * 实际订房晚数;
  4126. var listHotel = _sqlSugar.Queryable<Grp_HotelReservations, Grp_DelegationInfo, Grp_CreditCardPayment>((x, a, b) => new JoinQueryInfos(
  4127. JoinType.Left,x.DiId == a.Id,
  4128. JoinType.Left,b.CTable == 76 && b.CId == x.Id ))
  4129. .Where((x, a, b) => x.IsDel == 0 && x.CreateUserId == userId && a.VisitDate > StartDateTime
  4130. && a.VisitDate < EndDateTime && a.IsDel == 0 && b.IsDel == 0 && x.CardPrice > 0).Select((x,a,b)=> new
  4131. {
  4132. a.Id,
  4133. a.TeamName,
  4134. a.VisitDate,
  4135. a.TeamLevSId,
  4136. a.VisitPNumber,
  4137. x.SingleRoomCount,
  4138. x.SuiteRoomCount,
  4139. x.DoubleRoomCount,
  4140. x.OtherRoomCount,
  4141. x.CheckInDate,
  4142. x.CheckOutDate,
  4143. x.HotelName
  4144. }).ToList();
  4145. var groupByid = listHotel.GroupBy(x => x.Id);
  4146. var propertys = new string[] { "SingleRoomCount", "SuiteRoomCount", "DoubleRoomCount", "OtherRoomCount" };
  4147. foreach (var group in groupByid)
  4148. {
  4149. var infos = new ArrayList();
  4150. var totalPrice = 0.00M;
  4151. foreach (var item in group)
  4152. {
  4153. var hotelCount = 0;
  4154. foreach (var property in propertys)
  4155. {
  4156. var value = item.GetType().GetProperty(property).GetValue(item).ObjToInt();
  4157. if (value > 0)
  4158. {
  4159. hotelCount += value;
  4160. }
  4161. }
  4162. if (hotelCount > 0)
  4163. {
  4164. if (DateTime.TryParse(item.CheckInDate, out DateTime checkin) && DateTime.TryParse(item.CheckOutDate, out DateTime checkout))
  4165. {
  4166. var day = checkout - checkin;
  4167. infos.Add($"{item.HotelName} 共有 {hotelCount} 间房 , 入住时间{item.CheckInDate} - {item.CheckOutDate}{ day.TotalDays }晚 提成数量:{hotelCount * day.TotalDays} 提成金额:{hotelCount * day.TotalDays * 10}");
  4168. totalPrice += (decimal)(hotelCount * day.TotalDays * 10);
  4169. }
  4170. else
  4171. {
  4172. infos.Add(item.HotelName + " 时间有误!请检查 。");
  4173. }
  4174. }
  4175. }
  4176. var groupinfo = listHotel.First(x => x.Id == group.Key);
  4177. var temp = "本团人数" + groupinfo.VisitPNumber + ",该人员" + user.CnName + "共操作酒店晚数" + totalPrice / 10 + ",每间每晚提成10元," + user.CnName + "提成共" + totalPrice.ToString("#0.00") + "元";
  4178. resultArr.Add(new
  4179. {
  4180. groupinfo.TeamName,
  4181. groupinfo.Id,
  4182. chiArr = infos,
  4183. teamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
  4184. groupinfo.VisitDate,
  4185. temp,
  4186. price = totalPrice,
  4187. });
  4188. }
  4189. }
  4190. else if (OP.Contains(userId))
  4191. {
  4192. }
  4193. jw.Data = resultArr;
  4194. if (resultArr.Count > 0)
  4195. {
  4196. jw.Code = 200;
  4197. jw.Msg = "success!";
  4198. }
  4199. else
  4200. {
  4201. jw.Msg = "暂无数据!";
  4202. }
  4203. return Ok(jw);
  4204. }
  4205. #endregion
  4206. }
  4207. }