FinancialController.cs 160 KB

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