FinancialController.cs 199 KB

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