FinancialController.cs 229 KB

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