GeneralMethod.cs 188 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182218321842185218621872188218921902191219221932194219521962197219821992200220122022203220422052206220722082209221022112212221322142215221622172218221922202221222222232224222522262227222822292230223122322233223422352236223722382239224022412242224322442245224622472248224922502251225222532254225522562257225822592260226122622263226422652266226722682269227022712272227322742275227622772278227922802281228222832284228522862287228822892290229122922293229422952296229722982299230023012302230323042305230623072308230923102311231223132314231523162317231823192320232123222323232423252326232723282329233023312332233323342335233623372338233923402341234223432344234523462347234823492350235123522353235423552356235723582359236023612362236323642365236623672368236923702371237223732374237523762377237823792380238123822383238423852386238723882389239023912392239323942395239623972398239924002401240224032404240524062407240824092410241124122413241424152416241724182419242024212422242324242425242624272428242924302431243224332434243524362437243824392440244124422443244424452446244724482449245024512452245324542455245624572458245924602461246224632464246524662467246824692470247124722473247424752476247724782479248024812482248324842485248624872488248924902491249224932494249524962497249824992500250125022503250425052506250725082509251025112512251325142515251625172518251925202521252225232524252525262527252825292530253125322533253425352536253725382539254025412542254325442545254625472548254925502551255225532554255525562557255825592560256125622563256425652566256725682569257025712572257325742575257625772578257925802581258225832584258525862587258825892590259125922593259425952596259725982599260026012602260326042605260626072608260926102611261226132614261526162617261826192620262126222623262426252626262726282629263026312632263326342635263626372638263926402641264226432644264526462647264826492650265126522653265426552656265726582659266026612662266326642665266626672668266926702671267226732674267526762677267826792680268126822683268426852686268726882689269026912692269326942695269626972698269927002701270227032704270527062707270827092710271127122713271427152716271727182719272027212722272327242725272627272728272927302731273227332734273527362737273827392740274127422743274427452746274727482749275027512752275327542755275627572758275927602761276227632764276527662767276827692770277127722773277427752776277727782779278027812782278327842785278627872788278927902791279227932794279527962797279827992800280128022803280428052806280728082809281028112812281328142815281628172818281928202821282228232824282528262827282828292830283128322833283428352836283728382839284028412842284328442845284628472848284928502851285228532854285528562857285828592860286128622863286428652866286728682869287028712872287328742875287628772878287928802881288228832884288528862887288828892890289128922893289428952896289728982899290029012902290329042905290629072908290929102911291229132914291529162917291829192920292129222923292429252926292729282929293029312932293329342935293629372938293929402941294229432944294529462947294829492950295129522953295429552956295729582959296029612962296329642965296629672968296929702971297229732974297529762977297829792980298129822983298429852986298729882989299029912992299329942995299629972998299930003001300230033004300530063007300830093010301130123013301430153016301730183019302030213022302330243025302630273028302930303031303230333034303530363037303830393040304130423043304430453046304730483049305030513052305330543055305630573058305930603061306230633064306530663067306830693070307130723073307430753076307730783079308030813082308330843085308630873088308930903091309230933094309530963097309830993100310131023103310431053106310731083109311031113112311331143115311631173118311931203121312231233124312531263127312831293130313131323133313431353136313731383139314031413142314331443145314631473148314931503151315231533154315531563157315831593160316131623163316431653166316731683169317031713172317331743175317631773178317931803181318231833184318531863187318831893190319131923193319431953196319731983199320032013202320332043205320632073208320932103211321232133214321532163217321832193220322132223223322432253226322732283229323032313232323332343235323632373238323932403241324232433244324532463247324832493250325132523253325432553256325732583259326032613262326332643265326632673268326932703271327232733274327532763277327832793280328132823283328432853286328732883289329032913292329332943295329632973298329933003301330233033304330533063307330833093310331133123313331433153316331733183319332033213322332333243325332633273328332933303331333233333334333533363337333833393340334133423343334433453346334733483349335033513352335333543355335633573358335933603361336233633364336533663367336833693370337133723373337433753376337733783379338033813382338333843385338633873388338933903391339233933394339533963397339833993400340134023403340434053406340734083409341034113412341334143415341634173418341934203421342234233424342534263427342834293430343134323433343434353436343734383439344034413442344334443445344634473448344934503451345234533454345534563457345834593460346134623463346434653466346734683469347034713472347334743475347634773478347934803481348234833484348534863487348834893490349134923493349434953496349734983499350035013502350335043505350635073508350935103511351235133514351535163517351835193520352135223523352435253526352735283529353035313532353335343535353635373538353935403541354235433544354535463547354835493550355135523553355435553556355735583559356035613562356335643565356635673568356935703571357235733574357535763577357835793580358135823583358435853586358735883589359035913592359335943595359635973598359936003601360236033604360536063607360836093610361136123613361436153616361736183619362036213622362336243625362636273628362936303631363236333634363536363637363836393640364136423643364436453646364736483649365036513652365336543655365636573658365936603661366236633664366536663667366836693670367136723673367436753676367736783679368036813682368336843685368636873688368936903691369236933694369536963697369836993700370137023703370437053706370737083709371037113712371337143715371637173718371937203721372237233724372537263727372837293730373137323733373437353736373737383739374037413742374337443745374637473748374937503751375237533754375537563757375837593760376137623763376437653766376737683769377037713772377337743775377637773778377937803781378237833784378537863787378837893790379137923793379437953796379737983799380038013802380338043805380638073808380938103811381238133814381538163817381838193820382138223823382438253826382738283829383038313832383338343835383638373838383938403841384238433844384538463847384838493850385138523853385438553856385738583859386038613862386338643865386638673868386938703871387238733874387538763877387838793880388138823883388438853886388738883889389038913892389338943895389638973898389939003901390239033904390539063907390839093910391139123913391439153916391739183919392039213922392339243925392639273928392939303931393239333934393539363937393839393940394139423943394439453946394739483949395039513952395339543955395639573958395939603961396239633964396539663967396839693970397139723973397439753976397739783979398039813982398339843985398639873988398939903991399239933994399539963997399839994000400140024003400440054006400740084009401040114012401340144015401640174018401940204021402240234024402540264027402840294030403140324033403440354036403740384039404040414042404340444045404640474048404940504051405240534054405540564057405840594060406140624063406440654066406740684069407040714072407340744075407640774078407940804081408240834084408540864087408840894090409140924093409440954096409740984099410041014102410341044105410641074108410941104111411241134114411541164117411841194120412141224123412441254126412741284129413041314132413341344135413641374138413941404141414241434144414541464147414841494150415141524153415441554156415741584159416041614162416341644165416641674168416941704171417241734174417541764177417841794180418141824183418441854186418741884189419041914192419341944195419641974198419942004201420242034204420542064207420842094210421142124213421442154216421742184219422042214222422342244225422642274228422942304231423242334234423542364237423842394240424142424243424442454246424742484249425042514252425342544255425642574258425942604261426242634264426542664267426842694270
  1. 
  2. using Aspose.Cells;
  3. using Aspose.Words;
  4. using Aspose.Words.Layout;
  5. using Aspose.Words.Saving;
  6. using Microsoft.AspNetCore.SignalR;
  7. using Microsoft.International.Converters.PinYinConverter;
  8. using Microsoft.VisualBasic;
  9. using NPOI.HSSF.Util;
  10. using OASystem.API.OAMethodLib.File;
  11. using OASystem.API.OAMethodLib.Hub.HubClients;
  12. using OASystem.API.OAMethodLib.Hub.Hubs;
  13. using OASystem.API.OAMethodLib.JuHeAPI;
  14. using OASystem.API.OAMethodLib.SignalR.Hubs;
  15. using OASystem.Domain.Dtos.Groups;
  16. using OASystem.Domain.Entities.Customer;
  17. using OASystem.Domain.Entities.District;
  18. using OASystem.Domain.Entities.Financial;
  19. using OASystem.Domain.Entities.Groups;
  20. using OASystem.Domain.ViewModels.Financial;
  21. using OASystem.Domain.ViewModels.Groups;
  22. using OASystem.Domain.ViewModels.JuHeExchangeRate;
  23. using OASystem.Infrastructure.Repositories.CRM;
  24. using OASystem.Infrastructure.Repositories.Groups;
  25. using System.Data;
  26. using System.IdentityModel.Tokens.Jwt;
  27. using System.IO.Compression;
  28. using System.Security.Claims;
  29. using System.Web;
  30. using static OpenAI.GPT3.ObjectModels.SharedModels.IOpenAiModels;
  31. namespace OASystem.API.OAMethodLib
  32. {
  33. public static class GeneralMethod
  34. {
  35. //团组信息
  36. private readonly static SqlSugarClient _sqlSugar = AutofacIocManager.Instance.GetService<SqlSugarClient>();
  37. private readonly static DelegationInfoRepository _dirRep = AutofacIocManager.Instance.GetService<DelegationInfoRepository>();
  38. private readonly static TeamRateRepository _teamRateRep = AutofacIocManager.Instance.GetService<TeamRateRepository>();
  39. private readonly static IJuHeApiService _juHeApi = AutofacIocManager.Instance.GetService<IJuHeApiService>();
  40. private readonly static SetDataRepository _setDataRep = AutofacIocManager.Instance.GetService<SetDataRepository>();
  41. private readonly static TableOperationRecordRepository _tableOperationRecordRep = AutofacIocManager.Instance.GetService<TableOperationRecordRepository>();
  42. private readonly static MessageRepository _messageRep = AutofacIocManager.Instance.GetService<MessageRepository>();
  43. private readonly static IHubContext<ChatHub, IChatClient> _hubContext = AutofacIocManager.Instance.GetService<IHubContext<ChatHub, IChatClient>>();
  44. private readonly static string[] weekdays = new string[] { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };
  45. #region 员工注册默认添加基础页面
  46. /// <summary>
  47. /// 默认职位权限
  48. /// </summary>
  49. public static bool DefaultPostAuth(int depId, int postId, int userId, int createUserId)
  50. {
  51. if (depId < 1) return false;
  52. if (postId < 1) return false;
  53. if (userId < 1) return false;
  54. if (userId < 1) return false;
  55. List<DefaultPostPageAuthView> _defaultData = AppSettingsHelper.Get<DefaultPostPageAuthView>("DefaultPostPageData");
  56. if (_defaultData.Count < 1) return false;
  57. var postJobDatas = _dirRep._sqlSugar.Queryable<Sys_JobPost>().Where(it => it.IsDel == 0).ToList();
  58. List<int> pageData = new List<int>();
  59. //添加公司公共页面
  60. pageData.AddRange(_defaultData.Find(it => it.DepId == -1)?.PostPageAuths[0].PageIds ?? new List<int>());
  61. //添加部门页面
  62. var depPublicPageData = _defaultData.Find(it => it.DepId == depId);
  63. var jobPublicPageData = postJobDatas.Find(it => it.DepId == depId && it.Id == postId);
  64. if (depPublicPageData != null) //特殊部门
  65. {
  66. //公共页面
  67. pageData.AddRange(depPublicPageData.PostPageAuths.Find(it => it.PostId == -1)?.PageIds ?? new List<int>());
  68. if (depId == 7) //国交部特殊处理
  69. {
  70. List<string> postNames = new List<string>() { "主管", "经理" };
  71. //岗位页面
  72. if (jobPublicPageData != null)
  73. {
  74. if (postNames.Contains(jobPublicPageData.JobName))
  75. {
  76. pageData.AddRange(depPublicPageData.PostPageAuths.Find(it => it.PostId == 0)?.PageIds ?? new List<int>());
  77. }
  78. else
  79. {
  80. pageData.AddRange(depPublicPageData.PostPageAuths.Find(it => it.PostId == postId)?.PageIds ?? new List<int>());
  81. }
  82. }
  83. }
  84. }
  85. else //通用部门
  86. {
  87. pageData.AddRange(_defaultData.Find(it => it.DepId == 0)?.PostPageAuths[0].PageIds ?? new List<int>());
  88. }
  89. if (pageData.Count > 0)
  90. {
  91. //页面操作权限数据(添加修改等...)
  92. var pageFunctionData = _dirRep._sqlSugar.Queryable<Sys_SystemMenuAndFunction>().Where(it => pageData.Contains(it.SmId) && it.IsDel == 0).ToList();
  93. var defaultPageData = pageFunctionData.Select(it => new Sys_UserAuthority() {
  94. UId = userId,
  95. SmId = it.SmId,
  96. FId = it.FId,
  97. CreateUserId = createUserId, //管理员
  98. CreateTime = DateTime.Now,
  99. IsDel = 0,
  100. IsTemp = 1
  101. }).ToList();
  102. if (defaultPageData.Count > 0)
  103. {
  104. //移除每个页面的审核权限,审核权限需要单独开启
  105. defaultPageData = defaultPageData.Where(it => it.FId != 12).ToList();
  106. //添加页面操作权限
  107. var s = _dirRep._sqlSugar.Fastest<Sys_UserAuthority>().PageSize(100000).BulkCopy(defaultPageData);
  108. if (s > 0)
  109. {
  110. return true;
  111. }
  112. }
  113. }
  114. return false;
  115. }
  116. #endregion
  117. #region 消息
  118. /// <summary>
  119. /// 消息 发布And 通知
  120. /// </summary>
  121. /// <param name="msgTypeEnum">
  122. /// 消息类型
  123. /// 1 公告通知
  124. /// 2 团组流程管控通知
  125. /// 3 团组业务操作通知
  126. /// 4 团组费用审核消息
  127. /// 5 团组签证进度通知
  128. /// 6 团组任务进度通知
  129. /// </param>
  130. /// <param name="title">消息标题</param>
  131. /// <param name="content">消息内容</param>
  132. /// <param name="userIds"></param>
  133. /// <param name="diId">团组id</param>
  134. /// <param name="param">团组id</param>
  135. /// <returns></returns>
  136. public static async Task<bool> MessageIssueAndNotification(MessageTypeEnum msgTypeEnum, string title, string content, List<int> userIds, int diId = 0, string param = "")
  137. {
  138. MessageDto messageDto = new()
  139. {
  140. Type = msgTypeEnum,
  141. IssuerId = 4,//管理员
  142. DiId = diId,
  143. Title = title,
  144. Content = content,
  145. ReleaseTime = DateTime.Now,
  146. UIdList = userIds,
  147. Param = param
  148. };
  149. var status = await _messageRep.AddMsg(messageDto);//添加消息
  150. if (status)
  151. {
  152. //给在线在用户发送消息
  153. List<string> connIds = UserStore.OnlineUser.Where(it => userIds.Contains(it.UserId)).Select(it => it.ConnectionId).ToList();
  154. string notificationTypeStr1 = GetMsgNotificationType(msgTypeEnum);
  155. string notificationTypeStr = JsonConvert.SerializeObject(
  156. new
  157. {
  158. UserIds = userIds,
  159. Msg = $"您有一条{notificationTypeStr1}相关的消息!"
  160. }
  161. );
  162. await _hubContext.Clients.Clients(connIds).ReceiveMessage($"您有一条{notificationTypeStr1}相关的消息!");
  163. return true;
  164. }
  165. return false;
  166. }
  167. /// <summary>
  168. /// 根据消息类型 获取 消息通知类型
  169. /// </summary>
  170. /// <returns></returns>
  171. public static string GetMsgNotificationType(MessageTypeEnum msgTypeEnum)
  172. {
  173. int notificationType = 0;
  174. string notificationStr = "";
  175. List<NotificationTypeView> notificationTypeViews = AppSettingsHelper.Get<NotificationTypeView>("MessageNotificationType");
  176. notificationType = notificationTypeViews.Where(it => it.MsgTypeIds.Contains((int)msgTypeEnum)).Select(it => it.TypeId).FirstOrDefault();
  177. if (notificationType == 1021) notificationStr = "操作";
  178. else if (notificationType == 1020) notificationStr = "任务";
  179. return notificationStr;
  180. }
  181. #endregion
  182. #region md5 加密
  183. /// <summary>
  184. /// MD5加密,和动网上的16/32位MD5加密结果相同,
  185. /// 使用的UTF8编码
  186. /// </summary>
  187. /// <param name="source">待加密字串</param>
  188. /// <param name="length">16或32值之一,其它则采用.net默认MD5加密算法</param>
  189. /// <returns>加密后的字串</returns>
  190. public static string Encrypt(string source, int length = 32)
  191. {
  192. if (string.IsNullOrWhiteSpace(source))
  193. return string.Empty;
  194. HashAlgorithm hashAlgorithm = CryptoConfig.CreateFromName("MD5") as HashAlgorithm;
  195. byte[] bytes = Encoding.UTF8.GetBytes(source);
  196. byte[] hashValue = hashAlgorithm.ComputeHash(bytes);
  197. StringBuilder sb = new StringBuilder();
  198. switch (length)
  199. {
  200. case 16://16位密文是32位密文的9到24位字符
  201. for (int i = 4; i < 12; i++)
  202. {
  203. sb.Append(hashValue[i].ToString("x2"));
  204. }
  205. break;
  206. case 32:
  207. for (int i = 0; i < 16; i++)
  208. {
  209. sb.Append(hashValue[i].ToString("x2"));
  210. }
  211. break;
  212. default:
  213. for (int i = 0; i < hashValue.Length; i++)
  214. {
  215. sb.Append(hashValue[i].ToString("x2"));
  216. }
  217. break;
  218. }
  219. return sb.ToString();
  220. }
  221. #endregion
  222. #region jwt
  223. /// <summary>
  224. /// 获取token
  225. /// </summary>
  226. /// <param name="_config"></param>
  227. /// <param name="Number"></param>
  228. /// <param name="exp"></param>
  229. /// <returns></returns>
  230. public static async Task<string> GetToken(IConfiguration _config, string Number, int uId, string uName, DateTime exp)
  231. {
  232. string userId = Guid.NewGuid().ToString().Replace("-", "");
  233. var claims = new[] {
  234. new Claim(ClaimTypes.NameIdentifier, uName),
  235. new Claim(ClaimTypes.NameIdentifier, uId.ToString()),
  236. new Claim(ClaimTypes.NameIdentifier, userId),
  237. new Claim("Number",Number)
  238. };
  239. var key = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(_config["JwtSecurityKey"]));
  240. var creds = new SigningCredentials(key, SecurityAlgorithms.HmacSha256);
  241. var token = new JwtSecurityToken(
  242. issuer: "OASystem.com",
  243. audience: "OASystem.com",
  244. claims: claims,
  245. expires: exp,
  246. signingCredentials: creds);
  247. var indentity = new ClaimsIdentity(claims, "formlogin");
  248. var principal = new ClaimsPrincipal(indentity);
  249. // await _httpContext.SignInAsync (CookieAuthenticationDefaults.AuthenticationScheme, principal);
  250. return new JwtSecurityTokenHandler().WriteToken(token);
  251. }
  252. #endregion
  253. #region 数据类型转换
  254. /// <summary>
  255. /// object 转 Int
  256. /// </summary>
  257. /// <param name="obj"></param>
  258. /// <returns></returns>
  259. public static int GetInt(this object obj)
  260. {
  261. if (obj == null)
  262. return 0;
  263. int _number = 0;
  264. bool reslut = Int32.TryParse(obj.ToString(), out _number);
  265. return _number;
  266. }
  267. private static DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
  268. private static long longTime = 621355968000000000;
  269. private static int samllTime = 10000000;
  270. /// <summary>
  271. /// 时间戳 转 datetime
  272. /// </summary>
  273. /// <param name="timeStamp"></param>
  274. /// <returns></returns>
  275. public static DateTime GetTimeSpmpToDate(this object timeStamp)
  276. {
  277. if (timeStamp == null) return dateStart;
  278. DateTime dateTime = new DateTime(longTime + Convert.ToInt64(timeStamp) * samllTime, DateTimeKind.Utc).ToLocalTime();
  279. return dateTime;
  280. }
  281. #endregion
  282. #region 用户页面操作功能 权限
  283. /// <summary>
  284. /// 用户页面操作功能(可使用)
  285. /// </summary>
  286. /// <param name="userId">用户Id</param>
  287. /// <param name="PageId">页面Id</param>
  288. /// <returns></returns>
  289. public static async Task<PageFunAuthViewBase> PostUserPageFuncDatas(int userId, int PageId)
  290. {
  291. PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
  292. List<UserPageFuncView> userPageFuncDatas = new List<UserPageFuncView>();
  293. string sql = string.Format(@"Select ua.UId As UserId, u.CnName As UserName, pa.ModuleId,pa.ModuleName,pa.PageId,pa.PageName,pa.PageIsEnable,
  294. pa.PagePhoneIsEnable,pa.FuncId,pa.FuncName,pa.FuncIsEnable
  295. From Sys_UserAuthority ua
  296. Left Join Sys_Users u On ua.UId = u.Id
  297. Left Join (
  298. Select sd.Id As ModuleId,sd.Name As ModuleName, smp.Id As PageId,smp.Name As PageName,smp.IsEnable As PageIsEnable,
  299. smp.phoneIsEnable As PagePhoneIsEnable,pfp.Id As FuncId,pfp.FunctionName As FuncName,pfp.IsEnable As FuncIsEnable
  300. From Sys_SystemMenuAndFunction smaf
  301. Left Join Sys_SystemMenuPermission smp On smaf.SmId = smp.Id
  302. Left Join Sys_SetData sd On sd.STid = 5 And smp.Mid = sd.Id
  303. Left Join Sys_PageFunctionPermission pfp On smaf.FId = pfp.Id
  304. Where smaf.IsDel = 0 And smp.IsDel = 0 And pfp.IsDel = 0 And sd.IsDel = 0 And smp.IsEnable = 1
  305. ) As pa On ua.SmId = pa.PageId And ua.FId = pa.FuncId
  306. Where ua.IsDel = 0 And ua.UId = {0} And pa.PageId = {1}
  307. Order By ModuleId,PageId,FuncId Asc", userId, PageId);
  308. userPageFuncDatas = await _dirRep._sqlSugar.SqlQueryable<UserPageFuncView>(sql).ToListAsync();
  309. if (userPageFuncDatas.Count <= 0)
  310. {
  311. return pageFunAuth;
  312. }
  313. UserPageFuncView userPageFunc = new UserPageFuncView();
  314. //查询 1
  315. userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 1).FirstOrDefault();
  316. if (userPageFunc != null) pageFunAuth.CheckAuth = 1;
  317. //删除 2
  318. userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 2).FirstOrDefault();
  319. if (userPageFunc != null) pageFunAuth.DeleteAuth = 1;
  320. //编辑 3
  321. userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 3).FirstOrDefault();
  322. if (userPageFunc != null) pageFunAuth.EditAuth = 1;
  323. //下载 4
  324. userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 4).FirstOrDefault();
  325. if (userPageFunc != null) pageFunAuth.FilesDownloadAuth = 1;
  326. //上传 5
  327. userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 5).FirstOrDefault();
  328. if (userPageFunc != null) pageFunAuth.FilesUploadAuth = 1;
  329. //添加 11
  330. userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 11).FirstOrDefault();
  331. if (userPageFunc != null) pageFunAuth.AddAuth = 1;
  332. //审核 12
  333. userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 12).FirstOrDefault();
  334. if (userPageFunc != null) pageFunAuth.AuditAuth = 1;
  335. return pageFunAuth;
  336. }
  337. #endregion
  338. #region 业务模块 团组权限
  339. /// <summary>
  340. /// 业务模块 团组操作权限
  341. /// 验证 并返回可操作的团
  342. /// </summary>
  343. /// <param name="diId">团组Id</param>
  344. /// <param name="userId">用户Id</param>
  345. /// <param name="CTable">业务模块Id</param>
  346. /// <returns></returns>
  347. public static async Task<Result> PostGroupOperationAuth(int diId, int userId, int CTable)
  348. {
  349. Result _result = new Result { Code = -1, Msg = "No Operation Authorty!" };
  350. if (CTable < 1)
  351. {
  352. _result.Msg = "请填写正确的用户Id!";
  353. return _result;
  354. }
  355. var data = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(it => it.DIId == diId && it.UId == userId && it.CTId == CTable && it.IsDel == 0).First();
  356. if (data == null)
  357. {
  358. _result.Msg = "你没有本团下的该业务模块操作,请联系主管分配操作权限!";
  359. }
  360. else
  361. {
  362. _result.Code = 0;
  363. }
  364. return _result;
  365. }
  366. /// <summary>
  367. /// 业务模块 团组操作权限
  368. /// 返回可操作的团
  369. /// </summary>
  370. /// <param name="diId">团组Id</param>
  371. /// <param name="userId">用户Id</param>
  372. /// <param name="CTable">业务模块Id</param>
  373. /// <returns></returns>
  374. public static async Task<List<Web_ShareGroupInfoView>> PostOperationAuthReturnGroupInfosAsync(int userId, int CTable)
  375. {
  376. List<Web_ShareGroupInfoView> _ShareGroupInfoViews = new List<Web_ShareGroupInfoView>();
  377. var taskDatas = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(it => it.UId == userId && it.CTId == CTable && it.IsDel == 0).ToList();
  378. if (taskDatas.Count < 1) return _ShareGroupInfoViews;
  379. string diIds = string.Join(",", taskDatas.Select(it => it.DIId).ToList());
  380. string sql = string.Format(@"Select Id,TeamName,TourCode,ClientName,VisitCountry,VisitStartDate,VisitEndDate,VisitDays,VisitPNumber
  381. From Grp_DelegationInfo Where Id In({0}) And IsDel = 0 Order By Id Desc", diIds);
  382. _ShareGroupInfoViews = await _dirRep._sqlSugar.SqlQueryable<Web_ShareGroupInfoView>(sql).ToListAsync();
  383. return _ShareGroupInfoViews;
  384. }
  385. #endregion
  386. #region 团组相关
  387. #region 建团按国家默认添加汇率 / 默认权限分配
  388. /// <summary>
  389. /// 团组汇率
  390. /// 建团时 添加基础汇率 CNY
  391. /// 按国家 添加 默认币种
  392. /// </summary>
  393. /// <param name="userId"></param>
  394. /// <param name="diId"></param>
  395. /// <returns></returns>
  396. public static async Task<Result> PostGroupRateAddInit(int userId, int diId)
  397. {
  398. Result result = new() { Code = -2 };
  399. if (userId < 1)
  400. {
  401. result.Msg = string.Format(@"请传入正确的userId");
  402. return result;
  403. }
  404. if (diId < 1)
  405. {
  406. result.Msg = string.Format(@"请传入正确的DiId");
  407. return result;
  408. }
  409. //美元(USD):1.0000|欧元(EUR):1.0000|墨西哥比索(MXN):1.0000
  410. string rateInit = string.Format(@"人民币(CNY):1.0000");
  411. //var gropInfo = _dirRep._sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && it.Id == diId).First();
  412. //if (gropInfo == null)
  413. //{
  414. // result.Msg = $@"未查询到团组信息!";
  415. // return result;
  416. //}
  417. var juheRateData = await _juHeApi.GetExchangeRateAsync();
  418. if (juheRateData.Error_code != 0)
  419. {
  420. result.Msg = juheRateData.Reason;
  421. return result;
  422. }
  423. List<ExchangeRateModel> exchangeRateModels = (List<ExchangeRateModel>)juheRateData.Result;
  424. if (exchangeRateModels.Count <= 0)
  425. {
  426. result.Msg = $@"未查询到聚合接口汇率信息!";
  427. return result;
  428. }
  429. if (exchangeRateModels.Count > 0)
  430. {
  431. var codes = _dirRep._sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 66).ToList();
  432. for (int i = 0; i < exchangeRateModels.Count; i++)
  433. {
  434. string currencyName = exchangeRateModels[i].Name;
  435. string code = "";
  436. var currencyData = codes.Where(it => it.Remark == currencyName).FirstOrDefault();
  437. if (currencyData != null)
  438. {
  439. code = currencyData.Name;
  440. decimal currRate = 0.00M;
  441. string MSellPri = exchangeRateModels[i].MSellPri;
  442. if (!string.IsNullOrEmpty(MSellPri))
  443. {
  444. currRate = Convert.ToDecimal(MSellPri) / 100M;
  445. }
  446. rateInit += $@"|{currencyName}({code}):{currRate.ToString("#0.0000")}";
  447. }
  448. }
  449. }
  450. var cTableIds = _dirRep._sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 16).ToList();
  451. if (cTableIds.Count < 1)
  452. {
  453. result.Msg = $@"未查询到操作模块信息!";
  454. return result;
  455. }
  456. List<Grp_TeamRate> grp_TeamRates = new List<Grp_TeamRate>();
  457. foreach (var item in cTableIds)
  458. {
  459. grp_TeamRates.Add(
  460. new Grp_TeamRate() { DiId = diId, CTable = item.Id, Remark = rateInit, CreateUserId = userId }
  461. );
  462. }
  463. if (grp_TeamRates.Count > 0)
  464. {
  465. var addId = _teamRateRep._sqlSugar.Insertable(grp_TeamRates).ExecuteCommand();
  466. if (addId < 1)
  467. {
  468. result.Msg = string.Format(@"添加失败!");
  469. return result;
  470. }
  471. }
  472. result.Code = 0;
  473. result.Msg = string.Format(@"操作成功!");
  474. return result;
  475. }
  476. /// <summary>
  477. /// 团组任务分配
  478. /// 建团时 默认按照岗位分配权限
  479. /// </summary>
  480. /// <param name="userId"></param>
  481. /// <param name="diId"></param>
  482. /// <param name="companyId"></param>
  483. /// <returns></returns>
  484. public static async Task<Result> PostGroupAuthAddInit(int userId, int diId, int companyId = 2)
  485. {
  486. Result result = new() { Code = -2 };
  487. if (userId < 0)
  488. {
  489. result.Msg = string.Format(@"请传入正确的userId");
  490. return result;
  491. }
  492. if (companyId < 0)
  493. {
  494. result.Msg = string.Format(@"请传入正确的companyId");
  495. return result;
  496. }
  497. if (diId < 0)
  498. {
  499. result.Msg = string.Format(@"请传入正确的DiId");
  500. return result;
  501. }
  502. var userDatas = await _teamRateRep._sqlSugar
  503. .Queryable<Sys_Users>()
  504. .LeftJoin<Sys_Department>((su, sd) => su.DepId == sd.Id && sd.IsDel == 0)
  505. .LeftJoin<Sys_JobPost>((su, sd, sjp) => su.JobPostId == sjp.Id && sjp.IsDel == 0)
  506. .Where(su => su.IsDel == 0)
  507. .Select((su, sd, sjp) => new {
  508. su.DepId,
  509. sd.DepName,
  510. su.JobPostId,
  511. sjp.JobName,
  512. su.Id,
  513. su.CnName
  514. })
  515. .ToListAsync();
  516. /*
  517. * 76 酒店预订 --> 国交部门 酒店 岗位
  518. * 77 行程 --> 国交部门 经理,主管 岗位
  519. * 79 车/导游地接 --> 国交部门 OP 岗位
  520. * 80 签证 --> 国交部门 签证 岗位
  521. * 81 邀请/公务活动 --> 国交部门 商邀 岗位
  522. * 82 团组客户保险 --> 国交部 经理,主管 岗位
  523. * 85 机票预订 --> 国交部门 机票 岗位
  524. * 98 其他款项 --> 总经办部门 总经理 岗位/国交部门(ALL) 岗位/财务部门(ALL) 岗位/策划部门(ALL) 岗位/人事部门 采购 岗位
  525. * 1015 超支费用,69 收款退还 --> 财务部门(ALL)岗位
  526. * 1015 超支费用 --> 国交部(ALL)岗位
  527. */
  528. List<Grp_GroupsTaskAssignment> _GroupsTaskAssignments = new List<Grp_GroupsTaskAssignment>();
  529. // 76 酒店预订 --> 国交部门(7) 酒店(25) 岗位
  530. _GroupsTaskAssignments.AddRange(
  531. userDatas
  532. .Where(it => it.DepId == 7 && it.JobPostId == 25)
  533. .Select(it => new Grp_GroupsTaskAssignment()
  534. {
  535. DIId = diId,
  536. CTId = 76,
  537. UId = it.Id,
  538. CreateUserId = userId
  539. }).ToList()
  540. );
  541. // 77 行程 --> 国交部门(7) 经理(32),主管(22) 岗位
  542. _GroupsTaskAssignments.AddRange(
  543. userDatas
  544. .Where(it => it.DepId == 7 && (it.JobPostId == 22 || it.JobPostId == 32))
  545. .Select(it => new Grp_GroupsTaskAssignment()
  546. {
  547. DIId = diId,
  548. CTId = 77,
  549. UId = it.Id,
  550. CreateUserId = userId
  551. }).ToList()
  552. );
  553. //79 车/导游地接 --> 国交部门(7) OP(28) 岗位
  554. _GroupsTaskAssignments.AddRange(
  555. userDatas.Where(it => it.DepId == 7 && it.JobPostId == 28)
  556. .Select(it => new Grp_GroupsTaskAssignment()
  557. {
  558. DIId = diId,
  559. CTId = 79,
  560. UId = it.Id,
  561. CreateUserId = userId
  562. }).ToList()
  563. );
  564. //81 邀请/公务活动 --> 国交部门(7) 商邀(27) 岗位
  565. _GroupsTaskAssignments.AddRange(
  566. userDatas.Where(it => it.DepId == 7 && it.JobPostId == 27)
  567. .Select(it => new Grp_GroupsTaskAssignment()
  568. {
  569. DIId = diId,
  570. CTId = 81,
  571. UId = it.Id,
  572. CreateUserId = userId
  573. }).ToList()
  574. );
  575. //2024年8月26日16点27分 “邀请公务活动分配给王鸽(UserID:149)”
  576. _GroupsTaskAssignments.Add(
  577. new Grp_GroupsTaskAssignment()
  578. {
  579. DIId = diId,
  580. CTId = 81,
  581. UId = 149,
  582. CreateUserId = userId
  583. }
  584. );
  585. //80 签证 --> 国交部门(7) 签证(26) 岗位
  586. _GroupsTaskAssignments.AddRange(
  587. userDatas
  588. .Where(it => it.DepId == 7 && it.JobPostId == 26)
  589. .Select(it => new Grp_GroupsTaskAssignment()
  590. {
  591. DIId = diId,
  592. CTId = 80,
  593. UId = it.Id,
  594. CreateUserId = userId
  595. }).ToList()
  596. );
  597. //82 团组客户保险 --> 国交部(7) 经理(32),主管(22) 岗位
  598. //2024-06-25 默认将保险权限分配给签证岗。 签证(26) 岗位
  599. List<int> insurancePositions = new List<int>() { 32, 22, 26 };
  600. _GroupsTaskAssignments.AddRange(
  601. userDatas
  602. .Where(it => it.DepId == 7 && insurancePositions.Contains(it.JobPostId))
  603. .Select(it => new Grp_GroupsTaskAssignment()
  604. {
  605. DIId = diId,
  606. CTId = 82,
  607. UId = it.Id,
  608. CreateUserId = userId
  609. }).ToList()
  610. );
  611. //85 机票预订 --> 国交部门(7) 机票(24) 岗位
  612. _GroupsTaskAssignments.AddRange(
  613. userDatas
  614. .Where(it => it.DepId == 7 && it.JobPostId == 24)
  615. .Select(it => new Grp_GroupsTaskAssignment()
  616. {
  617. DIId = diId,
  618. CTId = 85,
  619. UId = it.Id,
  620. CreateUserId = userId
  621. }).ToList()
  622. );
  623. //98 其他款项 --> 总经办部门(1) 总经理(1) 岗位/国交部门(7)(ALL) 岗位/财务部门(3)(ALL) 岗位/策划部门(5)(ALL) 岗位/人事部门(4) 采购(74) 岗位
  624. List<int> depIds = new List<int>() { 7, 3, 5 };
  625. List<int> jobIds = new List<int>() { 1, 74 };
  626. _GroupsTaskAssignments.AddRange(
  627. userDatas
  628. .Where(it => depIds.Contains(it.DepId) || jobIds.Contains(it.JobPostId))
  629. .Select(it => new Grp_GroupsTaskAssignment()
  630. {
  631. DIId = diId,
  632. CTId = 98,
  633. UId = it.Id,
  634. CreateUserId = userId
  635. }).ToList()
  636. );
  637. //2024年8月26日16点27分 “其他款项分配给王鸽(UserID:149)”
  638. _GroupsTaskAssignments.Add(new Grp_GroupsTaskAssignment()
  639. {
  640. DIId = diId,
  641. CTId = 98,
  642. UId = 149,
  643. CreateUserId = userId
  644. }
  645. );
  646. //1015 超支费用,285 收款退还 --> 财务部门(ALL)岗位
  647. _GroupsTaskAssignments.AddRange(
  648. userDatas
  649. .Where(it => it.DepId == 3)
  650. .Select(it => new Grp_GroupsTaskAssignment()
  651. {
  652. DIId = diId,
  653. CTId = 1015,
  654. UId = it.Id,
  655. CreateUserId = userId
  656. }).ToList()
  657. );
  658. _GroupsTaskAssignments.AddRange(
  659. userDatas
  660. .Where(it => it.DepId == 3)
  661. .Select(it => new Grp_GroupsTaskAssignment()
  662. {
  663. DIId = diId,
  664. CTId = 285,
  665. UId = it.Id,
  666. CreateUserId = userId
  667. }).ToList()
  668. );
  669. //1015 超支费用 --> 国交部(ALL)岗位
  670. _GroupsTaskAssignments.AddRange(
  671. userDatas
  672. .Where(it => it.DepId == 7)
  673. .Select(it => new Grp_GroupsTaskAssignment()
  674. {
  675. DIId = diId,
  676. CTId = 1015,
  677. UId = it.Id,
  678. CreateUserId = userId
  679. }).ToList()
  680. );
  681. if (_GroupsTaskAssignments.Count > 0)
  682. {
  683. var addId = _teamRateRep._sqlSugar.Insertable(_GroupsTaskAssignments).ExecuteCommand();
  684. }
  685. result.Code = 0;
  686. result.Msg = string.Format(@"操作成功!");
  687. return result;
  688. }
  689. #endregion
  690. #region 员工是否是市场部人员
  691. /// <summary>
  692. /// 验证市场部客户人员
  693. /// </summary>
  694. /// <param name="userId"></param>
  695. /// <returns></returns>
  696. public static async Task<bool> IsMarketingStaff(int userId)
  697. {
  698. var userInfos = await _dirRep._sqlSugar
  699. .Queryable<Sys_Users>()
  700. .InnerJoin<Sys_Department>((u,d) => u.DepId == d.Id)
  701. .Where((u,d) => u.IsDel == 0 && u.Id == userId && d.DepName.Contains("市场部"))
  702. .ToListAsync();
  703. if (userInfos.Count > 0) return true;
  704. return false;
  705. }
  706. #endregion
  707. #endregion
  708. #region 团组汇率
  709. /// <summary>
  710. /// 团组汇率
  711. /// 获取板块 币种 及 汇率
  712. /// 76 酒店预订 77 行程 79 车/导游地接
  713. /// 80 签证 82 团组客户保险 85 机票预订
  714. /// 98 其他款项 285 收款退还
  715. /// </summary>
  716. /// <param name="teamRateModels"></param>
  717. /// <param name="cTable"></param>
  718. /// <param name="currencyCode"></param>
  719. /// <returns>
  720. /// string
  721. /// eg: CNY 1.0000
  722. /// </returns>
  723. public static async Task<string> PostGroupRateByCTableAndCurrency(List<TeamRateModelView> teamRateModels, int cTable, List<string> currencyCodes)
  724. {
  725. string str = "";
  726. List<string> currencyRates = new List<string>();
  727. TeamRateModelView hotelRateData = teamRateModels.Where(it => it.CTableId == cTable).FirstOrDefault();
  728. if (hotelRateData != null)
  729. {
  730. var hotelRates = hotelRateData.TeamRates;
  731. foreach (var item in currencyCodes)
  732. {
  733. if (!string.IsNullOrEmpty(item))
  734. {
  735. var hotelRateInfo = hotelRates.Where(it => it.CurrencyCode.Equals(item)).FirstOrDefault();
  736. if (hotelRateInfo != null)
  737. {
  738. string str1 = string.Format("{0} {1}\r\n", hotelRateInfo.CurrencyCode, hotelRateInfo.Rate.ToString("#0.0000"));
  739. currencyRates.Add(str1);
  740. }
  741. }
  742. }
  743. if (currencyRates != null || currencyRates.Count > 0)
  744. {
  745. currencyRates = currencyRates.Distinct().ToList();
  746. foreach (var item in currencyRates)
  747. {
  748. str += item;
  749. }
  750. }
  751. }
  752. return str;
  753. }
  754. /// <summary>
  755. /// 团组汇率 币种 Item (来源:团组汇率)
  756. /// 根据 团组Id And 业务类型(CTable)Id
  757. /// api处理CTable = 285,默认返回CNY
  758. /// </summary>
  759. /// <param name="portType"></param>
  760. /// <param name="diId"></param>
  761. /// <param name="cTable"></param>
  762. /// <returns></returns>
  763. public static async Task<TeamRateModelGeneralView> PostGroupTeamRateByDiIdAndCTableId(int portType, int diId, int cTable)
  764. {
  765. TeamRateModelGeneralView _view = new TeamRateModelGeneralView();
  766. _view = await _teamRateRep.PostGroupTeamRateByDiIdAndCTableId(portType, diId, cTable);
  767. return _view;
  768. }
  769. /// <summary>
  770. /// 汇率备注拆分
  771. /// (美元(USD):7.2370|日元(JPY):0.0499|欧元(EUR):8.3000|英镑(GBP):9.1996|港币(HKD):0.9291)
  772. /// </summary>
  773. /// <param name="rateRemark"></param>
  774. /// <returns></returns>
  775. public static async Task<List<TeamRateDescAddCurrencyIdView>> SplitExchangeRate(this string rateRemark)
  776. {
  777. List<TeamRateDescAddCurrencyIdView> _view = new List<TeamRateDescAddCurrencyIdView>();
  778. List<SetDataInfoView> currencyDatas = new List<SetDataInfoView>();
  779. #region 获取所有币种
  780. string sql = string.Format(@"select * from Sys_SetData where STid = {0} and isdel = 0", 66);
  781. var DBdata = _setDataRep.GetListBySqlWithNolock(sql);
  782. if (DBdata == null || DBdata.Count == 0)
  783. {
  784. return _view;
  785. }
  786. currencyDatas = DBdata.Select(x => new SetDataInfoView
  787. {
  788. Name = x.Name,
  789. Id = x.Id,
  790. Remark = x.Remark,
  791. }).ToList();
  792. #endregion
  793. #region 拆分remark里的汇率
  794. if (string.IsNullOrEmpty(rateRemark))
  795. {
  796. return _view;
  797. }
  798. if (rateRemark.Contains("|"))
  799. {
  800. string[] currencyArr = rateRemark.Split("|");
  801. foreach (string currency in currencyArr)
  802. {
  803. string[] currency1 = currency.Split(":");
  804. string[] currency2 = currency1[0].Split("(");
  805. string currencyCode = currency2[1].Replace(")", "").TrimEnd();
  806. TeamRateDescAddCurrencyIdView rateDescView = new TeamRateDescAddCurrencyIdView()
  807. {
  808. CurrencyId = currencyDatas.Find(it => it.Name == currencyCode).Id,
  809. CurrencyCode = currencyCode,
  810. CurrencyName = currency2[0],
  811. Rate = decimal.Parse(currency1[1]),
  812. };
  813. _view.Add(rateDescView);
  814. }
  815. }
  816. else
  817. {
  818. try
  819. {
  820. string[] currency1 = rateRemark.Split(":");
  821. string[] currency2 = currency1[0].Split("(");
  822. string currencyCode = currency2[1].Replace(")", "").TrimEnd();
  823. TeamRateDescAddCurrencyIdView rateDescView = new TeamRateDescAddCurrencyIdView()
  824. {
  825. CurrencyId = currencyDatas.Find(it => it.Name == currencyCode).Id,
  826. CurrencyCode = currencyCode,
  827. CurrencyName = currency2[0],
  828. Rate = decimal.Parse(currency1[1]),
  829. };
  830. _view.Add(rateDescView);
  831. }
  832. catch (Exception)
  833. {
  834. return _view;
  835. }
  836. }
  837. #endregion
  838. return _view;
  839. }
  840. #endregion
  841. #region 汉字转换拼音
  842. private static Dictionary<int, List<string>> GetTotalPingYinDictionary(string text)
  843. {
  844. var chs = text.ToCharArray();
  845. //记录每个汉字的全拼
  846. Dictionary<int, List<string>> totalPingYinList = new Dictionary<int, List<string>>();
  847. for (int i = 0; i < chs.Length; i++)
  848. {
  849. var pinyinList = new List<string>();
  850. //是否是有效的汉字
  851. if (ChineseChar.IsValidChar(chs[i]))
  852. {
  853. ChineseChar cc = new ChineseChar(chs[i]);
  854. pinyinList = cc.Pinyins.Where(p => !string.IsNullOrWhiteSpace(p)).ToList();
  855. }
  856. else
  857. {
  858. pinyinList.Add(chs[i].ToString());
  859. }
  860. //去除声调,转小写
  861. pinyinList = pinyinList.ConvertAll(p => Regex.Replace(p, @"\d", "").ToLower());
  862. //去重
  863. pinyinList = pinyinList.Where(p => !string.IsNullOrWhiteSpace(p)).Distinct().ToList();
  864. if (pinyinList.Any())
  865. {
  866. totalPingYinList[i] = pinyinList;
  867. }
  868. }
  869. return totalPingYinList;
  870. }
  871. /// <summary>
  872. /// 获取汉语拼音全拼
  873. /// </summary>
  874. /// <param name="text">The string.</param>
  875. /// <returns></returns>
  876. public static List<string> GetTotalPingYin(this string text)
  877. {
  878. var result = new List<string>();
  879. foreach (var pys in GetTotalPingYinDictionary(text))
  880. {
  881. var items = pys.Value;
  882. if (result.Count <= 0)
  883. {
  884. result = items;
  885. }
  886. else
  887. {
  888. //全拼循环匹配
  889. var newTotalPingYinList = new List<string>();
  890. foreach (var totalPingYin in result)
  891. {
  892. newTotalPingYinList.AddRange(items.Select(item => totalPingYin + item));
  893. }
  894. newTotalPingYinList = newTotalPingYinList.Distinct().ToList();
  895. result = newTotalPingYinList;
  896. }
  897. }
  898. return result;
  899. }
  900. /// <summary>
  901. /// 获取中文第一个拼音
  902. /// </summary>
  903. /// <param name="text"></param>
  904. /// <param name="isUp"></param>
  905. /// <returns></returns>
  906. public static string GetTotalPingYinFirst(this string text , bool isUp = true)
  907. {
  908. var returnstr = string.Empty;
  909. var enResult = text.GetTotalPingYin();
  910. if(enResult.Count == 0)
  911. {
  912. returnstr = text;
  913. }
  914. else if (isUp)
  915. {
  916. returnstr = enResult[0].ToUpper();
  917. }
  918. else
  919. {
  920. returnstr = enResult[0].ToLower();
  921. }
  922. return returnstr;
  923. }
  924. /// <summary>
  925. /// 获取汉语拼音首字母
  926. /// </summary>
  927. /// <param name="text"></param>
  928. /// <returns></returns>
  929. public static List<string> GetFirstPingYin(this string text)
  930. {
  931. var result = new List<string>();
  932. foreach (var pys in GetTotalPingYinDictionary(text))
  933. {
  934. var items = pys.Value;
  935. if (result.Count <= 0)
  936. {
  937. result = items.ConvertAll(p => p.Substring(0, 1)).Distinct().ToList();
  938. }
  939. else
  940. {
  941. //首字母循环匹配
  942. var newFirstPingYinList = new List<string>();
  943. foreach (var firstPingYin in result)
  944. {
  945. newFirstPingYinList.AddRange(items.Select(item => firstPingYin + item.Substring(0, 1)));
  946. }
  947. newFirstPingYinList = newFirstPingYinList.Distinct().ToList();
  948. result = newFirstPingYinList;
  949. }
  950. }
  951. return result;
  952. }
  953. #endregion
  954. #region 新客户资料表 操作记录
  955. /// <summary>
  956. /// 新客户资料表
  957. /// 操作记录添加
  958. /// </summary>
  959. /// <param name="portType"></param>
  960. /// <param name="operationEnum"></param>
  961. /// <param name="userId"></param>
  962. /// <param name="dataId"></param>
  963. /// <param name="remark"></param>
  964. /// <returns></returns>
  965. public static async Task<bool> NewClientOperationRecord(int portType, OperationEnum operationEnum, int userId, int dataId, string remark)
  966. {
  967. Crm_TableOperationRecord _TableOperationRecord = new Crm_TableOperationRecord()
  968. {
  969. TableName = "Crm_NewClientData",
  970. PortType = portType,
  971. OperationItem = operationEnum,
  972. DataId = dataId,
  973. CreateUserId = userId,
  974. CreateTime = DateTime.Now,
  975. Remark = remark
  976. };
  977. bool add = await _tableOperationRecordRep._Add(_TableOperationRecord);
  978. if (add) return false;
  979. return false;
  980. }
  981. #endregion
  982. #region 金额转大写
  983. /// <summary>
  984. /// 金额转换为大写数字
  985. /// <para>1、支持的最大数字:999999999999.99(玖仟玖佰玖拾玖亿玖仟玖佰玖拾玖万玖仟玖佰玖拾玖元玖角玖分)</para>
  986. /// <para>2、小数点后最多支持两位</para>
  987. /// </summary>
  988. /// <param name="num">数值</param>
  989. /// <returns></returns>
  990. public static string ConvertCNYUpper(this decimal num)
  991. {
  992. if (num == 0)
  993. {
  994. return "零元";
  995. }
  996. // 解决327000000转换后缺少单位万的问题
  997. return ConvertToChinese(num);
  998. }
  999. #region 实例
  1000. /// <summary>
  1001. /// 要转换的数字
  1002. /// </summary>
  1003. private static decimal j;
  1004. /// <summary>
  1005. ///
  1006. /// </summary>
  1007. private static string[] NumChineseCharacter = new string[] { "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖" };
  1008. /// <summary>
  1009. /// 判断输入的数字是否大于double类型
  1010. /// </summary>
  1011. private static bool IsNumber
  1012. {
  1013. get
  1014. {
  1015. if (j > decimal.MaxValue || j <= 0)
  1016. return false;
  1017. else
  1018. return true;
  1019. }
  1020. }
  1021. /// <summary>
  1022. /// 数字转换成大写汉字主函数
  1023. /// </summary>
  1024. /// <returns>返回转换后的大写汉字</returns>
  1025. public static string ConvertToChinese(decimal m)
  1026. {
  1027. j = m;
  1028. string bb = "";
  1029. if (IsNumber)
  1030. {
  1031. string str = j.ToString();
  1032. string[] Num = str.Split('.');
  1033. if (Num.Length == 1)
  1034. {
  1035. bb = NumberString(Num[0]) + "元整";
  1036. bb = bb.Replace("零零", "零");
  1037. }
  1038. else
  1039. {
  1040. bb = NumberString(Num[0]) + "元";
  1041. bb += FloatString(Num[1]);
  1042. bb = bb.Replace("零零", "零");
  1043. }
  1044. }
  1045. else
  1046. {
  1047. throw new FormatException("你输入的数字格式不正确或不是数字!");
  1048. }
  1049. return bb;
  1050. }
  1051. /// <summary>
  1052. /// 小数位转换只支持两位的小数
  1053. /// </summary>
  1054. /// <param name="Num">转换的小数</param>
  1055. /// <returns>小数转换成汉字</returns>
  1056. private static string FloatString(string Num)
  1057. {
  1058. string cc = "";
  1059. if (Num.Length > 2)
  1060. {
  1061. throw new FormatException("小数位数过多.");
  1062. }
  1063. else
  1064. {
  1065. string bb = ConvertString(Num);
  1066. int len = bb.IndexOf("零");
  1067. if (len != 0)
  1068. {
  1069. bb = bb.Replace("零", "");
  1070. if (bb.Length == 1)
  1071. {
  1072. cc = bb.Substring(0, 1) + "角";
  1073. }
  1074. else
  1075. {
  1076. cc = bb.Substring(0, 1) + "角";
  1077. cc += bb.Substring(1, 1) + "分";
  1078. }
  1079. }
  1080. else
  1081. cc = bb + "分";
  1082. }
  1083. return cc;
  1084. }
  1085. /// <summary>
  1086. /// 判断数字位数以进行拆分转换
  1087. /// </summary>
  1088. /// <param name="Num">要进行拆分的数字</param>
  1089. /// <returns>转换成的汉字</returns>
  1090. private static string NumberString(string Num)
  1091. {
  1092. string bb = "";
  1093. if (Num.Length <= 4)
  1094. {
  1095. bb = Convert4(Num);
  1096. }
  1097. else if (Num.Length > 4 && Num.Length <= 8)
  1098. {
  1099. bb = Convert4(Num.Substring(0, Num.Length - 4)) + "万";
  1100. bb += Convert4(Num.Substring(Num.Length - 4, 4));
  1101. }
  1102. else if (Num.Length > 8 && Num.Length <= 12)
  1103. {
  1104. bb = Convert4(Num.Substring(0, Num.Length - 8)) + "亿";
  1105. if (Convert4(Num.Substring(Num.Length - 8, 4)) == "")
  1106. if (Convert4(Num.Substring(Num.Length - 4, 4)) != "")
  1107. bb += "零";
  1108. else
  1109. bb += "";
  1110. else
  1111. bb += Convert4(Num.Substring(Num.Length - 8, 4)) + "万";
  1112. bb += Convert4(Num.Substring(Num.Length - 4, 4));
  1113. }
  1114. else
  1115. {
  1116. throw new Exception("整数部分最多支持12位");
  1117. }
  1118. return bb;
  1119. }
  1120. /// <summary>
  1121. /// 四位数字的转换
  1122. /// </summary>
  1123. /// <param name="Num">准备转换的四位数字</param>
  1124. /// <returns>转换以后的汉字</returns>
  1125. private static string Convert4(string Num)
  1126. {
  1127. string bb = "";
  1128. if (Num.Length == 1)
  1129. {
  1130. bb = ConvertString(Num);
  1131. }
  1132. else if (Num.Length == 2)
  1133. {
  1134. bb = ConvertString(Num);
  1135. bb = Convert2(bb);
  1136. }
  1137. else if (Num.Length == 3)
  1138. {
  1139. bb = ConvertString(Num);
  1140. bb = Convert3(bb);
  1141. }
  1142. else
  1143. {
  1144. bb = ConvertString(Num);
  1145. string cc = "";
  1146. string len = bb.Substring(0, 4);
  1147. if (len != "零零零零")
  1148. {
  1149. len = bb.Substring(0, 3);
  1150. if (len != "零零零")
  1151. {
  1152. bb = bb.Replace("零零零", "");
  1153. if (bb.Length == 1)
  1154. {
  1155. bb = bb.Substring(0, 1) + "仟";
  1156. }
  1157. else
  1158. {
  1159. if (bb.Substring(0, 1) != "零" && bb.Substring(0, 2) != "零")
  1160. cc = bb.Substring(0, 1) + "仟";
  1161. else
  1162. cc = bb.Substring(0, 1);
  1163. bb = cc + Convert3(bb.Substring(1, 3));
  1164. }
  1165. }
  1166. else
  1167. {
  1168. bb = bb.Replace("零零零", "零");
  1169. }
  1170. }
  1171. else
  1172. {
  1173. bb = bb.Replace("零零零零", "");
  1174. }
  1175. }
  1176. return bb;
  1177. }
  1178. /// <summary>
  1179. /// 将数字转换成汉字
  1180. /// </summary>
  1181. /// <param name="Num">需要转换的数字</param>
  1182. /// <returns>转换后的汉字</returns>
  1183. private static string ConvertString(string Num)
  1184. {
  1185. string bb = "";
  1186. for (int i = 0; i < Num.Length; i++)
  1187. {
  1188. bb += NumChineseCharacter[int.Parse(Num.Substring(i, 1))];
  1189. }
  1190. return bb;
  1191. }
  1192. /// <summary>
  1193. /// 两位数字的转换
  1194. /// </summary>
  1195. /// <param name="Num">两位数字</param>
  1196. /// <returns>转换后的汉字</returns>
  1197. private static string Convert2(string Num)
  1198. {
  1199. string bb = ""; string cc = "";
  1200. string len = Num.Substring(0, 1);
  1201. if (len != "零")
  1202. {
  1203. bb = Num.Replace("零", "");
  1204. if (bb.Length == 1)
  1205. {
  1206. cc = bb.Substring(0, 1) + "拾";
  1207. }
  1208. else
  1209. {
  1210. cc = bb.Substring(0, 1) + "拾";
  1211. cc += bb.Substring(1, 1);
  1212. }
  1213. }
  1214. else
  1215. cc = Num;
  1216. return cc;
  1217. }
  1218. /// <summary>
  1219. /// 三位数字的转换
  1220. /// </summary>
  1221. /// <param name="Num">三位数字</param>
  1222. /// <returns>转换后的汉字</returns>
  1223. private static string Convert3(string Num)
  1224. {
  1225. string bb = ""; string cc = "";
  1226. string len = Num.Substring(0, 2);
  1227. if (len != "零零")
  1228. {
  1229. bb = Num.Replace("零零", "");
  1230. if (bb.Length == 1)
  1231. {
  1232. bb = bb.Substring(0, 1) + "佰";
  1233. }
  1234. else
  1235. {
  1236. if (bb.Substring(0, 1) != "零")
  1237. cc = bb.Substring(0, 1) + "佰";
  1238. else
  1239. cc = bb.Substring(0, 1);
  1240. bb = cc + Convert2(bb.Substring(1, 2));
  1241. }
  1242. }
  1243. else
  1244. {
  1245. bb = Num.Replace("零零", "零");
  1246. }
  1247. return bb;
  1248. }
  1249. #endregion
  1250. #endregion
  1251. #region 数字验证
  1252. /// <summary>
  1253. /// 验证数字字符串
  1254. /// </summary>
  1255. /// <param name="num"></param>
  1256. /// <returns></returns>
  1257. public static bool IsNumeric(this string numStr)
  1258. {
  1259. bool isNumeric = Regex.IsMatch(numStr, @"^\d+$");
  1260. if (isNumeric)
  1261. {
  1262. return true;
  1263. }
  1264. return false;
  1265. }
  1266. #endregion
  1267. #region 日期 格式转换
  1268. /// <summary>
  1269. /// 验证数字字符串
  1270. /// </summary>
  1271. /// <param name="str"></param>
  1272. /// <returns></returns>
  1273. public static string ConvertToDatetime(this string str)
  1274. {
  1275. if (!string.IsNullOrEmpty(str))
  1276. {
  1277. DateTime currentDate = Convert.ToDateTime(str);
  1278. return $"{currentDate.Year}年{currentDate.Month}月{currentDate.Day}日";
  1279. }
  1280. return "";
  1281. }
  1282. #endregion
  1283. #region 月份/季度
  1284. /// <summary>
  1285. /// 获取月份对应的起始天数
  1286. /// </summary>
  1287. /// <param name="year"></param>
  1288. /// <returns></returns>
  1289. public static List<MonthInfo> GetMonthInfos(int year)
  1290. {
  1291. List<MonthInfo> months = new List<MonthInfo>();
  1292. if (year > 1)
  1293. {
  1294. for (int i = 1; i < 13; i++)
  1295. {
  1296. months.Add(new MonthInfo()
  1297. {
  1298. Month = i,
  1299. Days = new DaysInfo() { BeginDays = 1, EndDays = DateTime.DaysInMonth(year, i) }
  1300. });
  1301. }
  1302. }
  1303. return months;
  1304. }
  1305. /// <summary>
  1306. /// 获取季度对应的起始天数
  1307. /// </summary>
  1308. /// <param name="year"></param>
  1309. /// <returns></returns>
  1310. public static List<MonthInfo> GetQuarter(int year)
  1311. {
  1312. List<MonthInfo> months = new List<MonthInfo>();
  1313. months.Add(new MonthInfo() { Month = 1, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //1.1 - 3.31
  1314. months.Add(new MonthInfo() { Month = 2, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //4.1 - 6.30
  1315. months.Add(new MonthInfo() { Month = 3, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //7.1 - 9.31
  1316. months.Add(new MonthInfo() { Month = 4, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //10.1 - 12.31
  1317. return months;
  1318. }
  1319. public class MonthInfo
  1320. {
  1321. public int Month { get; set; }
  1322. public DaysInfo Days { get; set; }
  1323. }
  1324. public class DaysInfo
  1325. {
  1326. public int BeginDays { get; set; }
  1327. public int EndDays { get; set; }
  1328. }
  1329. #endregion
  1330. #region op行程单,黑屏幕代码
  1331. public static string GetCountryStandingTime(int diId)
  1332. {
  1333. DataTable datas = GetTableByBlackCode(diId);
  1334. string countryStr = "[黑屏代码未录入].";
  1335. if (datas.Rows.Count > 0)
  1336. {
  1337. var airDatas = from row in datas.AsEnumerable()
  1338. select new
  1339. {
  1340. Three = row.Field<string>("Three"),
  1341. Day = row.Field<string>("Day"),
  1342. ArrivedDate = row.Field<string>("ArrivedDate"),
  1343. };
  1344. //三字码信息
  1345. List<Res_ThreeCode> listcode = _dirRep._sqlSugar.Queryable<Res_ThreeCode>().Where(x => x.IsDel == 0).ToList();
  1346. //string countryStr = "";
  1347. int index = 0;
  1348. List<string> cityCodes = new List<string>();
  1349. //去掉开始和结束城市
  1350. foreach (var row in airDatas)
  1351. {
  1352. if (!string.IsNullOrEmpty(row.Three))
  1353. {
  1354. if (index == 0) cityCodes.Add(row.Three.Substring(3, 3)); //到达国家
  1355. else if (airDatas.Count() - 1 == index) cityCodes.Add(row.Three.Substring(0, 3)); //到达国家
  1356. else cityCodes.Add(row.Three.Substring(0, 3)); //到达国家
  1357. }
  1358. index++;
  1359. }
  1360. cityCodes = cityCodes.Distinct().ToList();
  1361. if (cityCodes.Count > 0) countryStr = "";
  1362. foreach (var item in cityCodes)
  1363. {
  1364. var airData = airDatas.Where(it => it.Three.Contains(item)).ToList();
  1365. string country = listcode.Find(it => it.Three.Equals(item))?.Country ?? "Unknown";
  1366. if (country.Equals("中国"))
  1367. {
  1368. country = listcode.Find(it => it.Three.Equals(item))?.City ?? "Unknown";
  1369. }
  1370. int days = 0;
  1371. if (airData.Count == 2)
  1372. {
  1373. DateTime arr_dt = Convert.ToDateTime(airData[0].ArrivedDate); //抵达时间
  1374. DateTime dep_dt = Convert.ToDateTime(airData[1].Day); //离开时间
  1375. days = (dep_dt - arr_dt).Days;
  1376. //countryStr += $@"{country}停留{days}日、";
  1377. countryStr += $@"{country}停留 日、";
  1378. }
  1379. }
  1380. if (countryStr.Length > 0) countryStr = countryStr.Substring(0, countryStr.Length - 1);
  1381. }
  1382. return countryStr;
  1383. }
  1384. /// <summary>
  1385. ///根据机票黑屏代码整理DataTable
  1386. /// </summary>
  1387. /// <param name="diid"></param>
  1388. /// <returns></returns>
  1389. public static DataTable GetTableByBlackCode1(int diid)
  1390. {
  1391. //黑屏代码信息
  1392. List<Air_TicketBlackCode> listcode = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.DiId == diid && x.IsDel == 0).ToList();
  1393. //测试数据为序号,航班号,起飞日期,三字码,起飞时刻,到达时刻,出发航站楼,到达航站楼,机型,飞行时间
  1394. //1.3U8391 TU17NOV CTUCAI 0220 0715 T1 T2 330 10H55M
  1395. DataTable dt = new DataTable();
  1396. dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
  1397. dt.Columns.Add("Date", typeof(string));//起飞日期
  1398. dt.Columns.Add("Three", typeof(string));//三字码
  1399. dt.Columns.Add("StartTime", typeof(string));//起飞时刻
  1400. dt.Columns.Add("EndTime", typeof(string));//到达时刻
  1401. dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
  1402. dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
  1403. dt.Columns.Add("AirModel", typeof(string)); //机型
  1404. dt.Columns.Add("FlightTime", typeof(string));//飞行时间
  1405. dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据
  1406. dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
  1407. dt.Columns.Add("Error", typeof(string));//整理的到达日期
  1408. dt.Columns.Add("Sign", typeof(string));//标识:0表示为原生黑屏代码、1表示“+1”新增的黑屏代码
  1409. //判断是否录入黑屏代码
  1410. if (listcode.Count() == 0 || listcode == null)
  1411. {
  1412. dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "黑屏代码未录入!", null);
  1413. }
  1414. else
  1415. {
  1416. //读取单段黑屏代码
  1417. for (int i = 0; i < listcode.Count; i++)
  1418. {
  1419. //去除序号
  1420. string[] CodeList = Regex.Split(listcode[i].BlackCode, "\\d+\\.", RegexOptions.IgnoreCase);
  1421. //去除多余空格,方法一Linq扩展方法
  1422. CodeList = CodeList.Where(str => str != "").ToArray();
  1423. CodeList = CodeList.Where(str => str != " ").ToArray();
  1424. //年
  1425. int year = Convert.ToInt32(DateTime.Now.Year.ToString());
  1426. //读取单条黑屏代码
  1427. for (int j = 0; j < CodeList.Count(); j++)
  1428. {
  1429. //去除多余空格,方法二使用Split()方法进行分割,分割有一个选项是RemoveEmptyEntries
  1430. CodeList[j] = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty).TrimStart().TrimEnd();
  1431. string[] Info = CodeList[j].Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
  1432. //string[] Info = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty)
  1433. // .TrimStart().TrimEnd().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
  1434. //去除多余空格
  1435. Info = Info.Where(str => str != "").ToArray();
  1436. Info = Info.Where(str => str != " ").ToArray();
  1437. //判断黑屏代码是否正确拆分; 理应拆成9段
  1438. if (Info.Count() < 9)
  1439. {
  1440. dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "本团组第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误,请联系机票同事核对", null);
  1441. }
  1442. else
  1443. {
  1444. try
  1445. {
  1446. //月
  1447. int month = Convert.ToInt32(GetLonger(Info[1].Substring(4, 3)));
  1448. //日
  1449. int day = Convert.ToInt32(Info[1].Substring(2, 2));
  1450. #region 逐一比较月份,判断是否翻年;逐一比较三字码顶真,判断是否跑错机场
  1451. if (j > 0)
  1452. {
  1453. string[] Temp = CodeList[j - 1].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty)
  1454. .TrimStart().TrimEnd().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
  1455. Temp = Temp.Where(str => str != "").ToArray();
  1456. Temp = Temp.Where(str => str != " ").ToArray();
  1457. int monthTemp = Convert.ToInt32(GetLonger(Temp[1].Substring(4, 3)));
  1458. // 如果相邻月份之差小于0,则证明次一条年份需+1
  1459. if (month - monthTemp < 0)
  1460. {
  1461. year = year + 1;
  1462. }
  1463. //如果相邻代码三字码不顶真,提醒
  1464. string FootThree = Temp[2].Substring(3, 3);
  1465. string HeadThree = Info[2].Substring(0, 3);
  1466. if (FootThree != HeadThree)
  1467. {
  1468. //DelegationInfoService s = new DelegationInfoService();
  1469. //UsersService us = new UsersService();
  1470. //GroupsTaskAssignmentService gts = new GroupsTaskAssignmentService();
  1471. //77 行程
  1472. List<Grp_GroupsTaskAssignment> list2 = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(x => x.CTId == 77 && x.DIId == diid && x.IsDel == 0).ToList();
  1473. foreach (var temp in list2)
  1474. {
  1475. //if (temp.UId != 21)
  1476. //SendAndReturn(us.GetUsersByID(temp.UId).Email,
  1477. // "黑屏代码提醒",
  1478. // s.GetDelegationInfoByID(diid).TeamName + "的机票黑屏代码中,相邻航段的三字码不连续,请查看!");
  1479. }
  1480. //85 机票预订
  1481. List<Grp_GroupsTaskAssignment> list6 = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(x => x.CTId == 85 && x.DIId == diid && x.IsDel == 0).ToList();
  1482. foreach (var temp in list6)
  1483. {
  1484. //if (temp.UId != 21)
  1485. //SendAndReturn(us.GetUsersByID(temp.UId).Email,
  1486. // "黑屏代码提醒",
  1487. // s.GetDelegationInfoByID(diid).TeamName + "的机票黑屏代码中,相邻航段的三字码不连续,请查看!");
  1488. }
  1489. }
  1490. }
  1491. #endregion
  1492. #region 判断到达日期是否需要加1
  1493. if (Info[4].Contains("+"))
  1494. {
  1495. //日期+1
  1496. day = day + 1;
  1497. //判断是否进入下一月
  1498. if (day > Convert.ToInt32(GetDaysByMonth(Info[1].Substring(4, 3), year)))
  1499. {
  1500. day = day - Convert.ToInt32(GetDaysByMonth(Info[1].Substring(4, 3), year));
  1501. month = month + 1;
  1502. //判断是否进入下一年
  1503. if (month > 12)
  1504. {
  1505. month = month - 12;
  1506. year = year + 1;
  1507. }
  1508. }
  1509. //月份整理格式
  1510. string monthTemp = month.ToString();
  1511. if (month < 10)
  1512. {
  1513. monthTemp = "0" + monthTemp;
  1514. }
  1515. //日期整理格式
  1516. string daytemp = day.ToString();
  1517. if (day < 10)
  1518. {
  1519. daytemp = "0" + daytemp;
  1520. }
  1521. string temp = Info[4].Split('+')[0];
  1522. //添加起飞数据
  1523. dt.Rows.Add(Info[0],
  1524. Info[1],
  1525. Info[2],
  1526. Info[3],
  1527. temp,
  1528. Info[5],
  1529. Info[6],
  1530. Info[7],
  1531. Info[8],
  1532. year + "-" + GetLonger(Info[1].Substring(4, 3)) + "-" + Info[1].Substring(2, 2),
  1533. year + "-" + monthTemp + "-" + daytemp,
  1534. "",
  1535. "0");
  1536. //加1天,添加到达数据
  1537. dt.Rows.Add(Info[0],
  1538. Info[1].Replace(Info[1].Substring(2, 2), daytemp),
  1539. Info[2],
  1540. Info[3],
  1541. temp,
  1542. Info[5],
  1543. Info[6],
  1544. Info[7],
  1545. Info[8],
  1546. year + "-" + monthTemp + "-" + daytemp,
  1547. year + "-" + monthTemp + "-" + daytemp,
  1548. "",
  1549. "1");
  1550. }
  1551. else
  1552. {
  1553. //月份整理格式
  1554. string monthTemp = month.ToString();
  1555. if (month < 10)
  1556. {
  1557. monthTemp = "0" + monthTemp;
  1558. }
  1559. //日期整理格式
  1560. string daytemp = day.ToString();
  1561. if (day < 10)
  1562. {
  1563. daytemp = "0" + daytemp;
  1564. }
  1565. dt.Rows.Add(Info[0],
  1566. Info[1],
  1567. Info[2],
  1568. Info[3],
  1569. Info[4],
  1570. Info[5],
  1571. Info[6],
  1572. Info[7],
  1573. Info[8],
  1574. year + "-" + monthTemp + "-" + daytemp,
  1575. year + "-" + monthTemp + "-" + daytemp,
  1576. "",
  1577. "0");
  1578. }
  1579. #endregion
  1580. }
  1581. catch (Exception ex)
  1582. {
  1583. string exstr = ex.Message.ToString();
  1584. }
  1585. }
  1586. }
  1587. //排序
  1588. dt.DefaultView.Sort = "Day asc";
  1589. dt = dt.DefaultView.ToTable();
  1590. }
  1591. }
  1592. return dt;
  1593. }
  1594. private readonly static string[] excludeArr = new string[] { "[中转]", "[转机]" };
  1595. /// <summary>
  1596. ///根据机票黑屏代码整理DataTable
  1597. /// </summary>
  1598. /// <param name="diid"></param>
  1599. /// <returns></returns>
  1600. public static DataTable GetTableByBlackCode(int diid)
  1601. {
  1602. //黑屏代码信息
  1603. List<Air_TicketBlackCode> listcode = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.DiId == diid && x.IsDel == 0).ToList();
  1604. //测试数据为序号,航班号,起飞日期,三字码,起飞时刻,到达时刻,出发航站楼,到达航站楼,机型,飞行时间
  1605. //1.3U8391 TU17NOV CTUCAI 0220 0715 T1 T2 330 10H55M
  1606. DataTable dt = new DataTable();
  1607. dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
  1608. dt.Columns.Add("Date", typeof(string));//起飞日期
  1609. dt.Columns.Add("Three", typeof(string));//三字码
  1610. dt.Columns.Add("StartTime", typeof(string));//起飞时刻
  1611. dt.Columns.Add("EndTime", typeof(string));//到达时刻
  1612. dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
  1613. dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
  1614. dt.Columns.Add("AirModel", typeof(string)); //机型
  1615. dt.Columns.Add("FlightTime", typeof(string));//飞行时间
  1616. dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据
  1617. dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
  1618. dt.Columns.Add("Error", typeof(string));//整理的到达日期
  1619. dt.Columns.Add("Sign", typeof(string));//标识:0表示为原生黑屏代码、1表示“+1”新增的黑屏代码
  1620. //添加转机标识
  1621. dt.Columns.Add("isTransitShipment", typeof(bool));
  1622. //判断是否录入黑屏代码
  1623. if (listcode.Count() == 0 || listcode == null)
  1624. {
  1625. dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "黑屏代码未录入!", null, false);
  1626. }
  1627. else
  1628. {
  1629. //读取单段黑屏代码
  1630. for (int i = 0; i < listcode.Count; i++)
  1631. {
  1632. //去除序号
  1633. string[] CodeList = Regex.Split(listcode[i].BlackCode, "\\d+\\.", RegexOptions.IgnoreCase);
  1634. //去除多余空格,方法一Linq扩展方法
  1635. CodeList = CodeList.Where(str => !string.IsNullOrWhiteSpace(str)).ToArray();
  1636. //读取单条黑屏代码
  1637. for (int j = 0; j < CodeList.Count(); j++)
  1638. {
  1639. //去除多余空格,方法二使用Split()方法进行分割,分割有一个选项是RemoveEmptyEntries
  1640. CodeList[j] = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty).TrimStart().TrimEnd();
  1641. string[] Info = CodeList[j].Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
  1642. //去除多余空格
  1643. Info = Info.Where(str => !string.IsNullOrWhiteSpace(str)).ToArray();
  1644. //判断黑屏代码是否正确拆分; 理应拆成9段
  1645. if (Info.TakeWhile((x) =>
  1646. {
  1647. return !excludeArr.Contains(x);
  1648. }).Count() != 9)
  1649. {
  1650. dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "本团组第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误,请联系机票同事核对", null, false);
  1651. //MessageBoxEx.Show("第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误, 请联系机票同事核对");
  1652. return dt;
  1653. }
  1654. else
  1655. {
  1656. try
  1657. {
  1658. var monthEn = Info[1].Substring(4, 3);
  1659. //月
  1660. int month = Convert.ToInt32(GetLonger(monthEn));
  1661. //日
  1662. int day = Convert.ToInt32(Info[1].Substring(2, 2));
  1663. var time = new DateTime(DateTime.Now.Year, month, day); //
  1664. var isExist = Info.Contains("[中转]");
  1665. //dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
  1666. //dt.Columns.Add("Date", typeof(string));//起飞日期
  1667. //dt.Columns.Add("Three", typeof(string));//三字码
  1668. //dt.Columns.Add("StartTime", typeof(string));//起飞时刻
  1669. //dt.Columns.Add("EndTime", typeof(string));//到达时刻
  1670. //dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
  1671. //dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
  1672. //dt.Columns.Add("AirModel", typeof(string)); //机型
  1673. //dt.Columns.Add("FlightTime", typeof(string));//飞行时间
  1674. //dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据
  1675. //dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
  1676. //dt.Columns.Add("Error", typeof(string));//整理的到达日期
  1677. //dt.Columns.Add("Sign", typeof(string));//标识:0表示为原生黑屏代码、1表示“+1”新增的黑屏代码
  1678. dt.Rows.Add(Info[0],
  1679. Info[1],
  1680. Info[2],
  1681. Info[3],
  1682. Info[4],
  1683. Info[5],
  1684. Info[6],
  1685. Info[7],
  1686. Info[8],
  1687. time.ToString("yyyy-MM-dd"),
  1688. time.ToString("yyyy-MM-dd"),
  1689. "",
  1690. "0",
  1691. isExist
  1692. );
  1693. }
  1694. catch (Exception ex)
  1695. {
  1696. string exstr = ex.Message.ToString();
  1697. }
  1698. }
  1699. }
  1700. //排序
  1701. dt.DefaultView.Sort = "Day asc";
  1702. dt = dt.DefaultView.ToTable();
  1703. }
  1704. }
  1705. return dt;
  1706. }
  1707. /// <summary>
  1708. /// 根据星期,月份的缩写,转换成数字或者全称
  1709. /// 根据币种中文名称返回币种代码
  1710. /// 根据数字返回机型型号【2、3开头的就是空客,比如空客320,7开头的就是波音,比如波音777】
  1711. /// 20210903贾文滔
  1712. /// </summary>
  1713. /// <param name="startDate"></param>
  1714. /// <param name="endDate"></param>
  1715. /// <returns></returns>
  1716. public static string GetLonger(string temp)
  1717. {
  1718. string str = "";
  1719. switch (temp.ToUpper())
  1720. {
  1721. case "美元":
  1722. str = "USD";
  1723. break;
  1724. case "日元":
  1725. str = "JPY";
  1726. break;
  1727. case "英镑":
  1728. str = "GBP";
  1729. break;
  1730. case "欧元":
  1731. str = "EUR";
  1732. break;
  1733. case "港币":
  1734. str = "HKD";
  1735. break;
  1736. case "MO":
  1737. str = "星期一";
  1738. break;
  1739. case "TU":
  1740. str = "星期二";
  1741. break;
  1742. case "WE":
  1743. str = "星期三";
  1744. break;
  1745. case "TH":
  1746. str = "星期四";
  1747. break;
  1748. case "FR":
  1749. str = "星期五";
  1750. break;
  1751. case "SA":
  1752. str = "星期六";
  1753. break;
  1754. case "SU":
  1755. str = "星期天";
  1756. break;
  1757. case "JAN":
  1758. str = "01";
  1759. break;
  1760. case "FEB":
  1761. str = "02";
  1762. break;
  1763. case "MAR":
  1764. str = "03";
  1765. break;
  1766. case "APR":
  1767. str = "04";
  1768. break;
  1769. case "MAY":
  1770. str = "05";
  1771. break;
  1772. case "JUN":
  1773. str = "06";
  1774. break;
  1775. case "JUL":
  1776. str = "07";
  1777. break;
  1778. case "AUG":
  1779. str = "08";
  1780. break;
  1781. case "SEP":
  1782. str = "09";
  1783. break;
  1784. case "OCT":
  1785. str = "10";
  1786. break;
  1787. case "NOV":
  1788. str = "11";
  1789. break;
  1790. case "DEC":
  1791. str = "12";
  1792. break;
  1793. case "MONDAY":
  1794. str = "星期一";
  1795. break;
  1796. case "TUESDAY":
  1797. str = "星期二";
  1798. break;
  1799. case "WEDNESDAY":
  1800. str = "星期三";
  1801. break;
  1802. case "THURSDAY":
  1803. str = "星期四";
  1804. break;
  1805. case "FRIDAY":
  1806. str = "星期五";
  1807. break;
  1808. case "SATURDAY":
  1809. str = "星期六";
  1810. break;
  1811. case "SUNDAY":
  1812. str = "星期日";
  1813. break;
  1814. case "01":
  1815. str = "JAN";
  1816. break;
  1817. case "02":
  1818. str = "FEB";
  1819. break;
  1820. case "03":
  1821. str = "MAR";
  1822. break;
  1823. case "04":
  1824. str = "APR";
  1825. break;
  1826. case "05":
  1827. str = "MAY";
  1828. break;
  1829. case "06":
  1830. str = "JUN";
  1831. break;
  1832. case "07":
  1833. str = "JUL";
  1834. break;
  1835. case "08":
  1836. str = "AUG";
  1837. break;
  1838. case "09":
  1839. str = "SEP";
  1840. break;
  1841. case "10":
  1842. str = "OCT";
  1843. break;
  1844. case "11":
  1845. str = "NOV";
  1846. break;
  1847. case "12":
  1848. str = "DEC";
  1849. break;
  1850. case "2":
  1851. str = "空客A";
  1852. break;
  1853. case "3":
  1854. str = "空客A";
  1855. break;
  1856. case "7":
  1857. str = "波音";
  1858. break;
  1859. }
  1860. return str;
  1861. }
  1862. /// <summary>
  1863. /// 根据月份返回天数
  1864. /// </summary>
  1865. /// <param name="temp"></param>
  1866. /// <returns></returns>
  1867. public static string GetDaysByMonth(string Month, int year)
  1868. {
  1869. string str = "";
  1870. //判断是否是闰年
  1871. if (DateTime.IsLeapYear(year) == false)
  1872. {
  1873. switch (Month.ToUpper())
  1874. {
  1875. case "JAN":
  1876. str = "31";
  1877. break;
  1878. case "FEB":
  1879. str = "28";
  1880. break;
  1881. case "MAR":
  1882. str = "31";
  1883. break;
  1884. case "APR":
  1885. str = "30";
  1886. break;
  1887. case "MAY":
  1888. str = "31";
  1889. break;
  1890. case "JUN":
  1891. str = "30";
  1892. break;
  1893. case "JUL":
  1894. str = "31";
  1895. break;
  1896. case "AUG":
  1897. str = "31";
  1898. break;
  1899. case "SEP":
  1900. str = "30";
  1901. break;
  1902. case "OCT":
  1903. str = "31";
  1904. break;
  1905. case "NOV":
  1906. str = "30";
  1907. break;
  1908. case "DEC":
  1909. str = "31";
  1910. break;
  1911. case "01":
  1912. str = "31";
  1913. break;
  1914. case "02":
  1915. str = "28";
  1916. break;
  1917. case "03":
  1918. str = "31";
  1919. break;
  1920. case "04":
  1921. str = "30";
  1922. break;
  1923. case "05":
  1924. str = "31";
  1925. break;
  1926. case "06":
  1927. str = "30";
  1928. break;
  1929. case "07":
  1930. str = "31";
  1931. break;
  1932. case "08":
  1933. str = "31";
  1934. break;
  1935. case "09":
  1936. str = "30";
  1937. break;
  1938. case "10":
  1939. str = "31";
  1940. break;
  1941. case "11":
  1942. str = "30";
  1943. break;
  1944. case "12":
  1945. str = "31";
  1946. break;
  1947. }
  1948. }
  1949. else
  1950. {
  1951. switch (Month.ToUpper())
  1952. {
  1953. case "JAN":
  1954. str = "31";
  1955. break;
  1956. case "FEB":
  1957. str = "29";
  1958. break;
  1959. case "MAR":
  1960. str = "31";
  1961. break;
  1962. case "APR":
  1963. str = "30";
  1964. break;
  1965. case "MAY":
  1966. str = "31";
  1967. break;
  1968. case "JUN":
  1969. str = "30";
  1970. break;
  1971. case "JUL":
  1972. str = "31";
  1973. break;
  1974. case "AUG":
  1975. str = "31";
  1976. break;
  1977. case "SEP":
  1978. str = "30";
  1979. break;
  1980. case "OCT":
  1981. str = "31";
  1982. break;
  1983. case "NOV":
  1984. str = "30";
  1985. break;
  1986. case "DEC":
  1987. str = "31";
  1988. break;
  1989. case "01":
  1990. str = "31";
  1991. break;
  1992. case "02":
  1993. str = "29";
  1994. break;
  1995. case "03":
  1996. str = "31";
  1997. break;
  1998. case "04":
  1999. str = "30";
  2000. break;
  2001. case "05":
  2002. str = "31";
  2003. break;
  2004. case "06":
  2005. str = "30";
  2006. break;
  2007. case "07":
  2008. str = "31";
  2009. break;
  2010. case "08":
  2011. str = "31";
  2012. break;
  2013. case "09":
  2014. str = "30";
  2015. break;
  2016. case "10":
  2017. str = "31";
  2018. break;
  2019. case "11":
  2020. str = "30";
  2021. break;
  2022. case "12":
  2023. str = "31";
  2024. break;
  2025. }
  2026. }
  2027. return str;
  2028. }
  2029. /// <summary>
  2030. /// op行程单 团组 城市路径
  2031. /// </summary>
  2032. /// <param name="diid">团组Id</param>
  2033. /// <param name="separator">分隔符</param>
  2034. /// <returns></returns>
  2035. public static string GetGroupCityLine(int diid, string separator)
  2036. {
  2037. string city = string.Empty;
  2038. var blackCode = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.IsDel == 0 && x.DiId == diid).ToList();
  2039. if (blackCode.Count > 0)
  2040. {
  2041. var black = blackCode.First();
  2042. black.BlackCode = black.BlackCode == null ? "" : black.BlackCode;
  2043. var blackSp = Regex.Split(black.BlackCode, "\\d+\\.", RegexOptions.IgnoreCase).Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
  2044. if (blackSp.Length > 0)
  2045. {
  2046. try
  2047. {
  2048. var cityArrCode = new List<string>(20);
  2049. foreach (var item in blackSp)
  2050. {
  2051. var itemSp = item.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
  2052. var IndexSelect = itemSp[2];
  2053. var cityArrCodeLength = cityArrCode.Count - 1;
  2054. var startCity = IndexSelect.Substring(0, 3);
  2055. if (cityArrCodeLength > 0)
  2056. {
  2057. var arrEndCity = cityArrCode[cityArrCodeLength];
  2058. if (arrEndCity != startCity)
  2059. {
  2060. cityArrCode.Add(startCity.ToUpper());
  2061. }
  2062. }
  2063. else
  2064. {
  2065. cityArrCode.Add(startCity.ToUpper());
  2066. }
  2067. var endCity = IndexSelect.Substring(3, 3);
  2068. cityArrCode.Add(endCity.ToUpper());
  2069. }
  2070. var cityThree = string.Empty;
  2071. cityArrCode.ForEach(x => cityThree += "'" + x + "',");
  2072. cityThree = cityThree.TrimEnd(',');
  2073. if (string.IsNullOrWhiteSpace(cityThree))
  2074. {
  2075. throw new Exception("error");
  2076. }
  2077. string sql = $"SELECT * FROM Res_ThreeCode rtc WHERE UPPER(rtc.Three) in ({cityThree}) ";
  2078. var cityArr = _dirRep._sqlSugar.SqlQueryable<Res_ThreeCode>(sql).ToList();
  2079. foreach (var item in cityArrCode)
  2080. {
  2081. var find = cityArr.Find(x => x.Three.ToUpper() == item.ToUpper());
  2082. if (find != null)
  2083. {
  2084. city += find.City + separator;
  2085. }
  2086. else
  2087. {
  2088. city += item + "[三字码未收录]" + separator;
  2089. }
  2090. }
  2091. city = city.TrimEnd(char.Parse(separator));
  2092. }
  2093. catch (Exception e)
  2094. {
  2095. city = "[黑屏代码格式不正确!]";
  2096. }
  2097. }
  2098. }
  2099. else city = "[未录入黑屏代码]";
  2100. return city;
  2101. }
  2102. /// <summary>
  2103. /// op行程单 团组 城市路径
  2104. /// </summary>
  2105. /// <param name="diid">团组Id</param>
  2106. /// <param name="separator">分隔符</param>
  2107. /// <returns></returns>
  2108. public static Dictionary<int,string> GetGroupCityLineItem(List<int> diids, string separator)
  2109. {
  2110. Dictionary<int, string> dicCitys = new Dictionary<int, string>();
  2111. var cityCodes = _dirRep._sqlSugar.Queryable<Res_ThreeCode>().Where(it => it.IsDel == 0).ToList();
  2112. var blackCodes = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.IsDel == 0 && diids.Contains(x.DiId)).ToList();
  2113. foreach (var diid in diids)
  2114. {
  2115. string city = string.Empty;
  2116. var blackCode = blackCodes.Where(it => it.DiId == diid).ToList();
  2117. if (blackCode.Count > 0)
  2118. {
  2119. var black = blackCode.First();
  2120. black.BlackCode = black.BlackCode == null ? "" : black.BlackCode;
  2121. var blackSp = Regex.Split(black.BlackCode, "\\d+\\.", RegexOptions.IgnoreCase).Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
  2122. if (blackSp.Length > 0)
  2123. {
  2124. try
  2125. {
  2126. var cityArrCode = new List<string>(20);
  2127. foreach (var item in blackSp)
  2128. {
  2129. var itemSp = item.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
  2130. var IndexSelect = itemSp[2];
  2131. var cityArrCodeLength = cityArrCode.Count - 1;
  2132. var startCity = IndexSelect.Substring(0, 3);
  2133. if (cityArrCodeLength > 0)
  2134. {
  2135. var arrEndCity = cityArrCode[cityArrCodeLength];
  2136. if (arrEndCity != startCity)
  2137. {
  2138. cityArrCode.Add(startCity.ToUpper());
  2139. }
  2140. }
  2141. else
  2142. {
  2143. cityArrCode.Add(startCity.ToUpper());
  2144. }
  2145. var endCity = IndexSelect.Substring(3, 3);
  2146. cityArrCode.Add(endCity.ToUpper());
  2147. }
  2148. var cityThree = string.Empty;
  2149. cityArrCode.ForEach(x => cityThree += "'" + x + "',");
  2150. cityThree = cityThree.TrimEnd(',');
  2151. if (string.IsNullOrWhiteSpace(cityThree))
  2152. {
  2153. throw new Exception("error");
  2154. }
  2155. var cityArr = cityCodes.Where(it => cityThree.Contains(it.Three.ToUpper())).ToList();
  2156. foreach (var item in cityArrCode)
  2157. {
  2158. var find = cityArr.Find(x => x.Three.ToUpper() == item.ToUpper());
  2159. if (find != null)
  2160. {
  2161. city += find.City + separator;
  2162. }
  2163. else
  2164. {
  2165. city += item + "[三字码未收录]" + separator;
  2166. }
  2167. }
  2168. city = city.TrimEnd(char.Parse(separator));
  2169. }
  2170. catch (Exception e)
  2171. {
  2172. city = "[黑屏代码格式不正确!]";
  2173. }
  2174. }
  2175. }
  2176. else city = "[未录入黑屏代码]";
  2177. dicCitys.Add(diid,city);
  2178. }
  2179. return dicCitys;
  2180. }
  2181. /// <summary>
  2182. /// op行程单相关团组信息 含途径城市
  2183. /// </summary>
  2184. /// <returns></returns>
  2185. public static async Task< List<MateOpGroupPageListView>> MateOpGroupPageListRedis()
  2186. {
  2187. List<MateOpGroupPageListView> _views = new List<MateOpGroupPageListView>();
  2188. string viewStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("MateOpGroupPageList");//string 取
  2189. if (!string.IsNullOrEmpty(viewStr))
  2190. {
  2191. _views = JsonConvert.DeserializeObject<List<MateOpGroupPageListView>>(viewStr);
  2192. }
  2193. else
  2194. {
  2195. string sql = string.Format($@"Select Row_Number,Id,SalesQuoteNo,TourCode,TeamTypeId, TeamType,
  2196. TeamLevId,TeamLev,TeamName,ClientName,ClientUnit,
  2197. VisitDate,VisitDays,VisitPNumber,JietuanOperatorId,
  2198. JietuanOperator,IsSure,CreateTime,VisitCountry
  2199. From (
  2200. Select row_number() over(order by gdi.CreateTime Desc) as Row_Number,
  2201. gdi.Id,SalesQuoteNo,TourCode,ssd.Id TeamTypeId, ssd.Name TeamType,
  2202. ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,ClientName,ClientUnit,
  2203. VisitDate,VisitDays,VisitPNumber,JietuanOperator JietuanOperatorId,
  2204. su.CnName JietuanOperator,IsSure,gdi.CreateTime,gdi.VisitCountry
  2205. From Grp_DelegationInfo gdi
  2206. Left Join Sys_SetData ssd On gdi.TeamDid = ssd.Id
  2207. Left Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
  2208. Left Join Sys_Users su On gdi.JietuanOperator = su.Id
  2209. Where gdi.IsDel = 0
  2210. ) temp");
  2211. _views = _dirRep._sqlSugar.SqlQueryable<MateOpGroupPageListView>(sql).ToList();
  2212. #region 处理所属部门
  2213. /*
  2214. * 1.sq 和 gyy 等显示 市场部
  2215. * 2.王鸽和主管及张总还有管理员号统一国交部
  2216. * 2-1. 4 管理员 ,21 张海麟
  2217. */
  2218. List<int> userIds = _views.Select(it => it.JietuanOperatorId).ToList();
  2219. List<int> userIds1 = new List<int>() { 4, 21 };
  2220. var UserDepDatas = _dirRep._sqlSugar.Queryable<Sys_Users>()
  2221. .LeftJoin<Sys_Department>((u, d) => u.DepId == d.Id)
  2222. .Where(u => u.IsDel == 0 && userIds.Contains(u.Id))
  2223. .Select((u, d) => new { UserId = u.Id, DepName = userIds1.Contains(u.Id) ? "国交部" : d.DepName })
  2224. .ToList();
  2225. #endregion
  2226. _views.ForEach(it => {
  2227. it.RouteCity = GetGroupCityLine(it.Id, @"/");
  2228. it.Department = UserDepDatas.Find(it1 => it.JietuanOperatorId == it1.UserId)?.DepName ?? "Unknown";
  2229. });
  2230. TimeSpan ts = DateTime.Now.AddHours(2).TimeOfDay;
  2231. await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync<string>("MateOpGroupPageList", JsonConvert.SerializeObject(_views), ts);//string 存
  2232. }
  2233. return _views;
  2234. }
  2235. /// <summary>
  2236. /// 获取黑屏代码日期列表
  2237. /// </summary>
  2238. /// <param name="dt"></param>
  2239. /// <returns></returns>
  2240. public static List<string> GetTimeListByDataTable(DataTable dt)
  2241. {
  2242. DateTime datestart = Convert.ToDateTime(dt.Rows[0]["Day"].ToString());
  2243. DateTime dateend = Convert.ToDateTime(dt.Rows[dt.Rows.Count - 1]["ArrivedDate"].ToString());
  2244. List<string> timeList = new List<string>();
  2245. while (datestart <= dateend)
  2246. {
  2247. timeList.Add(datestart.ToString("yyyy-MM-dd"));
  2248. datestart = datestart.AddDays(1);
  2249. }
  2250. return timeList;
  2251. }
  2252. const decimal conversion = 1.61M;
  2253. /// <summary>
  2254. /// 简要行程
  2255. /// </summary>
  2256. /// <param name="diid"></param>
  2257. /// <returns></returns>
  2258. public static Result GetBriefStroke (int diid)
  2259. {
  2260. DataTable resultTable = GetTableByBlackCode(diid);
  2261. if (resultTable == null)
  2262. {
  2263. return new Result { Code = -1, Msg = "黑屏代码有误或黑屏代码未录入" };
  2264. }
  2265. if (resultTable.Rows.Count == 0 || string.IsNullOrWhiteSpace(resultTable.Rows[0][1].ToString()))
  2266. {
  2267. string msg = string.Empty;
  2268. if (resultTable.Rows.Count > 0)
  2269. {
  2270. msg = resultTable.Rows[0]["Error"].ToString();
  2271. }
  2272. return new Result { Code = -1, Msg = $"黑屏代码有误;{msg}" };
  2273. }
  2274. var timeArr = GetTimeListByDataTable(resultTable);
  2275. var threeCodes = _dirRep._sqlSugar.Queryable<Res_ThreeCode>().Where(it => it.IsDel == 0).ToList(); //三字码
  2276. var airCompanys = _dirRep._sqlSugar.Queryable<Res_AirCompany>().Where(it => it.IsDel == 0).ToList(); //航司公司
  2277. var obDatas = _dirRep._sqlSugar.Queryable<Res_OfficialActivities>().Where(it => it.IsDel == 0 && it.DiId == diid).ToList(); //公务信息
  2278. var NewListTravel = new List<Grp_TravelList>();
  2279. var index = 0;
  2280. var stopCity = string.Empty;
  2281. foreach (var item in timeArr)
  2282. {
  2283. string trip = string.Empty;
  2284. string weekDay = string.Empty;
  2285. DateTime time = DateTime.Now;
  2286. if (DateTime.TryParse(item, out time))
  2287. {
  2288. weekDay = weekdays[(int)time.DayOfWeek];
  2289. }
  2290. else
  2291. {
  2292. weekDay = "日期格式不正确!";
  2293. }
  2294. var empty = "【未收入该三字码!请机票同事录入】";
  2295. var tabSelect = resultTable.Select(string.Format("Day = '{0}'", item));
  2296. var isMoreTraffic = false;
  2297. if (tabSelect.Length > 0)
  2298. {
  2299. isMoreTraffic = true;
  2300. var takeOffTime = DateTime.Parse(item);
  2301. var fallToTime = DateTime.Parse(item);
  2302. Res_ThreeCode start_Object = null;
  2303. Res_ThreeCode end_Object = null;
  2304. bool isTrade = false;
  2305. //air 处理
  2306. foreach (var tabRow in tabSelect)
  2307. {
  2308. takeOffTime = DateTime.Parse(item);
  2309. fallToTime = DateTime.Parse(item);
  2310. var takeOff = tabRow["StartTime"].ToString();
  2311. var fallTo = tabRow["EndTime"].ToString();
  2312. takeOffTime = takeOffTime.AddHours(int.Parse(takeOff.Substring(0, 2)));
  2313. takeOffTime = takeOffTime.AddMinutes(int.Parse(takeOff.Substring(2, 2)));
  2314. fallToTime = fallToTime.AddHours(int.Parse(fallTo.Substring(0, 2)));
  2315. fallToTime = fallToTime.AddMinutes(int.Parse(fallTo.Substring(2, 2)));
  2316. var threeCode = tabRow["Three"].ToString();
  2317. var start = threeCode.Substring(0, 3);
  2318. var end = threeCode.Substring(3, 3);
  2319. stopCity = end;
  2320. start_Object = threeCodes.Find(x => x.Three.ToUpper() == start.ToUpper());
  2321. end_Object = threeCodes.Find(x => x.Three.ToUpper() == end.ToUpper());
  2322. if (start_Object == null)
  2323. {
  2324. start_Object = new Res_ThreeCode()
  2325. {
  2326. AirPort = empty,
  2327. AirPort_En = empty,
  2328. City = empty,
  2329. Country = empty,
  2330. Four = empty,
  2331. Three = empty,
  2332. };
  2333. }
  2334. if (end_Object == null)
  2335. {
  2336. end_Object = new Res_ThreeCode()
  2337. {
  2338. AirPort = empty,
  2339. AirPort_En = empty,
  2340. City = empty,
  2341. Country = empty,
  2342. Four = empty,
  2343. Three = empty,
  2344. };
  2345. }
  2346. //航班号
  2347. string flightcode = tabRow["Fliagtcode"].ToString();
  2348. trip += $"{takeOffTime.ToString("HH:mm")}—{fallToTime.ToString("HH:mm")} {start_Object.City}—{end_Object.City} 航班号:{flightcode}\r\n";
  2349. }
  2350. //var airArrive = fallToTime; //航班落地时间
  2351. time = fallToTime.AddHours(1.5); //出机场一个半小时
  2352. string Time = string.Empty;
  2353. string Distance = string.Empty;
  2354. int GetGoogleResult = 0;
  2355. int GetDistResult = 0;
  2356. JObject Result = null;
  2357. try
  2358. {
  2359. Time = Result["routes"][0]["legs"][0]["duration"]["text"].ToString().Replace(" ", "").Trim();
  2360. Distance = Result["routes"][0]["legs"][0]["distance"]["text"].ToString().Replace(" ", "").Trim();
  2361. if (Time.Contains("hours"))
  2362. {
  2363. Time = Time.Replace("hours", "小时");
  2364. }
  2365. if (Time.Contains("hour"))
  2366. {
  2367. Time = Time.Replace("hour", "小时");
  2368. }
  2369. Time = Time.Replace("mins", "分钟");
  2370. if (Distance.Contains("mi"))
  2371. {
  2372. var distSp = Regex.Split(Distance, "mi");
  2373. if (distSp.Length > 0)
  2374. {
  2375. Distance = (decimal.Parse(distSp[0]) * conversion).ToString("#0.00") + " 公里";
  2376. }
  2377. }
  2378. if (Distance.Contains("km"))
  2379. {
  2380. Distance = Distance.Replace("km", "公里");
  2381. }
  2382. if (Time.Contains("小时"))
  2383. {
  2384. var xs = Regex.Split(Time, "小时");
  2385. var xsValue = int.Parse(xs[0]);
  2386. var fz = Regex.Split(xs[1], "分钟");
  2387. var fzValue = int.Parse(fz[0]);
  2388. GetGoogleResult = xsValue * 60;
  2389. GetGoogleResult += fzValue;
  2390. }
  2391. else if (Time.Contains("分钟"))
  2392. {
  2393. GetGoogleResult = int.Parse(Regex.Split(Time, "分钟")[0]);
  2394. }
  2395. if (Distance.Contains("公里"))
  2396. {
  2397. GetDistResult = (int)decimal.Parse(Regex.Split(Distance, "公里")[0]);
  2398. }
  2399. if (GetGoogleResult > 0 && GetGoogleResult % 5 != 0)
  2400. {
  2401. while (GetGoogleResult % 5 != 0)
  2402. {
  2403. GetGoogleResult++;
  2404. }
  2405. }
  2406. if (GetDistResult % 5 != 0 && GetDistResult > 0)
  2407. {
  2408. while (GetDistResult % 5 != 0)
  2409. {
  2410. GetDistResult++;
  2411. }
  2412. }
  2413. int H = (GetGoogleResult / 60);
  2414. int m = (GetGoogleResult % 60);
  2415. string TimeStr = string.Empty;
  2416. if (H != 0)
  2417. {
  2418. if (H < 10)
  2419. {
  2420. TimeStr += "0" + H + "小时";
  2421. }
  2422. else
  2423. {
  2424. TimeStr += H + "小时";
  2425. }
  2426. }
  2427. if (m < 10)
  2428. {
  2429. TimeStr += "0" + m + "分钟";
  2430. }
  2431. else
  2432. {
  2433. TimeStr += m + "分钟";
  2434. }
  2435. Time = TimeStr;
  2436. Distance = GetDistResult.ToString() + "公里";
  2437. }
  2438. catch (Exception ex)
  2439. {
  2440. Time = "未知!";
  2441. Distance = "未知!";
  2442. }
  2443. //trip += $"{time.ToString("HH:mm")} 搭乘专车前往市区({end_Object.AirPort} - {end_Object.City}市区 路程{Distance},耗时{Time})";
  2444. //trip += $"{time.ToString("HH:mm")} 搭乘专车前往市区({end_Object.AirPort} - {end_Object.City}市区)";
  2445. time = time.AddMinutes(GetGoogleResult); //到达市区时间
  2446. string tripDate = Convert.ToDateTime(item).ToString("M月d日");
  2447. if (index == timeArr.Count - 1)
  2448. {
  2449. NewListTravel.Add(new Grp_TravelList
  2450. {
  2451. CreateTime = DateTime.Now,
  2452. CreateUserId = 0,
  2453. Diid = diid,
  2454. Date = tripDate,
  2455. Trip = trip,
  2456. WeekDay = weekDay,
  2457. Days = index + 1,
  2458. Traffic_First = "飞机",
  2459. Traffic_Second = "汽车",
  2460. Diffgroup = 1,
  2461. Issel = 1,
  2462. IsDel = 0,
  2463. });
  2464. continue;
  2465. }
  2466. if (time.Day != fallToTime.Day) //超出一天
  2467. {
  2468. NewListTravel.Add(new Grp_TravelList
  2469. {
  2470. CreateTime = DateTime.Now,
  2471. CreateUserId = 0,
  2472. Diid = diid,
  2473. Date = tripDate,
  2474. Trip = trip,
  2475. WeekDay = weekDay,
  2476. Days = index + 1,
  2477. Traffic_First = "飞机",
  2478. Traffic_Second = "汽车",
  2479. Diffgroup = 1,
  2480. Issel = 1,
  2481. IsDel = 0,
  2482. });
  2483. continue;
  2484. }
  2485. var obInfo = obDatas.Find(it => Convert.ToDateTime(it.Date).ToString("M月d日").Equals(tripDate));
  2486. string obtime = "";
  2487. string obcontent = "";
  2488. if (obInfo != null)
  2489. {
  2490. obtime = obInfo.Time;
  2491. obcontent = $"拜访{obInfo.Client ?? "公务出访单位未录入"}{obInfo.Job ?? "公务出访联系人职务未录入"}{obInfo.Contact ?? "公务出访联系人职务未录入"}";
  2492. }
  2493. bool obTime_bool = DateTime.TryParse(obtime, out DateTime obTimeDt);
  2494. if (time.Hour < 9) // && (airArrive < new DateTime(airArrive.Year,airArrive.Month,airArrive.Day,6, 30, 0))
  2495. {
  2496. if (obTime_bool)
  2497. {
  2498. if (obTimeDt.Hour < 9)
  2499. {
  2500. trip += $"{obTimeDt.ToString("HH:mm")}—10:30 {obcontent};\r\n";
  2501. }
  2502. }
  2503. else
  2504. {
  2505. trip += "09:00—10:30 公务活动;\r\n";
  2506. }
  2507. }
  2508. else if (time.Hour < 10)
  2509. {
  2510. if (obTime_bool)
  2511. {
  2512. if (obTimeDt.Hour < 10)
  2513. {
  2514. trip += $"{obTimeDt.ToString("HH:mm")}—12:00 {obcontent};\r\n";
  2515. }
  2516. }
  2517. else
  2518. {
  2519. trip += "10:30—12:00 公务活动;\r\n";
  2520. }
  2521. }
  2522. if (time.Hour < 13)
  2523. {
  2524. //trip += $"\r\n{time.ToString("HH:mm")} 午餐于当地餐厅;";
  2525. }
  2526. if (time < new DateTime(time.Year, time.Month, time.Day, 14, 30, 0))
  2527. {
  2528. if (obTime_bool)
  2529. {
  2530. if (obTimeDt < new DateTime(time.Year, time.Month, time.Day, 14, 30, 0))
  2531. {
  2532. trip += $"{obTimeDt.ToString("HH:mm")}—16:00 {obcontent};\r\n";
  2533. }
  2534. }
  2535. else
  2536. {
  2537. trip += "14:00—16:00 公务活动;\r\n";
  2538. }
  2539. }
  2540. else if (time.Hour < 16)
  2541. {
  2542. if (obTime_bool)
  2543. {
  2544. if (obTimeDt.Hour < 16)
  2545. {
  2546. trip += $"{obTimeDt.ToString("HH:mm")}—17:30 {obcontent};\r\n";
  2547. }
  2548. }
  2549. else
  2550. {
  2551. trip += "16:00—17:30 公务活动;\r\n";
  2552. }
  2553. }
  2554. //if (time.Hour < 18)
  2555. //{
  2556. // trip += $"\r\n18:00 晚餐于当地餐厅;";
  2557. //}
  2558. //time = time.AddHours(1);
  2559. //trip += $"\r\n{time.ToString("HH:mm")} 搭乘专车前往酒店,抵达后办理入住;";
  2560. }
  2561. else
  2562. {
  2563. var end_Object = threeCodes.Find(x => x.Three.ToUpper() == stopCity.ToUpper());
  2564. if (end_Object == null)
  2565. {
  2566. end_Object = new Res_ThreeCode()
  2567. {
  2568. AirPort = empty,
  2569. AirPort_En = empty,
  2570. City = empty,
  2571. Country = empty,
  2572. Four = empty,
  2573. Three = empty,
  2574. };
  2575. }
  2576. //trip += $"{end_Object.City}\r\n";
  2577. // trip += @"08:00 早餐于酒店;
  2578. //09:00—10:30 公务活动;
  2579. //12:00 午餐于当地餐厅;
  2580. //14:00—16:00 公务活动;
  2581. //18:00 晚餐于当地餐厅;
  2582. //19:00 入住酒店休息;";
  2583. trip += "09:00—10:30 公务活动;\r\n14:00—16:00 公务活动;\r\n";
  2584. }
  2585. string[] traffic = new string[] { "飞机", "汽车" };
  2586. if (!isMoreTraffic)
  2587. {
  2588. traffic = new string[] { "汽车", "" };
  2589. }
  2590. NewListTravel.Add(new Grp_TravelList
  2591. {
  2592. CreateTime = DateTime.Now,
  2593. CreateUserId = 0,
  2594. Diid = diid,
  2595. Date = Convert.ToDateTime(item).ToString("M月d日"),
  2596. Trip = trip,
  2597. WeekDay = weekDay,
  2598. Days = index + 1,
  2599. Traffic_First = traffic[0],
  2600. Traffic_Second = traffic[1],
  2601. Diffgroup = 1,
  2602. Issel = 1,
  2603. IsDel = 0,
  2604. });
  2605. index++;
  2606. }
  2607. return new Result() { Code = 0, Msg = "获取成功!", Data = NewListTravel };
  2608. }
  2609. /// <summary>
  2610. /// 根据日期获取星期
  2611. /// </summary>
  2612. /// <param name="dt"></param>
  2613. /// <returns></returns>
  2614. public static string GetWeek(this string dtStr)
  2615. {
  2616. string week = "";
  2617. bool isDt = DateTime.TryParse(dtStr,out DateTime dt);
  2618. if (isDt)
  2619. {
  2620. week = weekdays[(int)dt.DayOfWeek];
  2621. }
  2622. return week;
  2623. }
  2624. #endregion
  2625. #region 三公费用明细 文件下载
  2626. /// <summary>
  2627. /// 获取三公费用标准city
  2628. /// </summary>
  2629. /// <param name="placeData"></param>
  2630. /// <param name="nationalTravelFeeId"></param>
  2631. /// <returns></returns>
  2632. private static string GetEnterExitCostExportCity(List<Grp_NationalTravelFee> placeData, int nationalTravelFeeId)
  2633. {
  2634. string _city = string.Empty;
  2635. if (placeData.Count < 1) return _city;
  2636. var data = placeData.Find(it => it.Id == nationalTravelFeeId);
  2637. if (data == null) return _city;
  2638. string country = data.Country;
  2639. string city = data.City;
  2640. if (city.Contains("其他城市") || city.Contains("所有城市")) _city = $"{country}-{city}";
  2641. else _city = city;
  2642. return _city;
  2643. }
  2644. /// <summary>
  2645. /// 三公费用明细 文件下载
  2646. /// </summary>
  2647. /// <param name="diId"></param>
  2648. /// <param name="userId"></param>
  2649. /// <param name="CTable"></param>
  2650. /// <returns></returns>
  2651. public static async Task<JsonView> EnterExitCostDownload(EnterExitCostDownloadDto dto,string fileFormat = "pdf")
  2652. {
  2653. var _view = new JsonView() { Code = StatusCodes.Status204NoContent };
  2654. if (dto.DiId < 1)
  2655. {
  2656. _view.Msg = "请传入有效的DiId参数;";
  2657. return _view;
  2658. }
  2659. if (dto.ExportType < 1)
  2660. {
  2661. _view.Msg = MsgTips.Port;
  2662. return _view;
  2663. }
  2664. if (dto.SubTypeId < 1)
  2665. {
  2666. _view.Msg = @"请传入有效的SubTypeId参数;
  2667. 1 明细表 --> 1005(默认明细表) 1006(因公出国(境)经费测算明细表) 1007(四川省商务厅出国经费财政先行审核表)
  2668. 2 表格 --> 1008(派员单位出(境)任务和预算审批意见表) 1009(省级单位出(境)经费报销单)
  2669. 3 团组成员名单 1 团组成员名单";
  2670. return _view;
  2671. }
  2672. try
  2673. {
  2674. var _EnterExitCosts = _sqlSugar.Queryable<Grp_EnterExitCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
  2675. var _DayAndCosts = _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).ToList();
  2676. if (_EnterExitCosts == null)
  2677. {
  2678. _view.Msg = @"该团组未填写出入境费用;";
  2679. return _view;
  2680. }
  2681. //数据源
  2682. List<Grp_DayAndCost> dac1 = _DayAndCosts.Where(it => it.Type == 1).ToList(); //住宿费
  2683. List<Grp_DayAndCost> dac2 = _DayAndCosts.Where(it => it.Type == 2).ToList(); //伙食费
  2684. List<Grp_DayAndCost> dac3 = _DayAndCosts.Where(it => it.Type == 3).ToList(); //公杂费
  2685. List<Grp_DayAndCost> dac4 = _DayAndCosts.Where(it => it.Type == 4).ToList(); //培训费
  2686. var _CurrDatas = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 66).ToList();
  2687. var _DelegationInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && it.Id == dto.DiId).First();
  2688. var DeleClientList = _sqlSugar.Queryable<Grp_TourClientList>()
  2689. .LeftJoin<Crm_DeleClient>((tcl, dc) => tcl.ClientId == dc.Id && dc.IsDel == 0)
  2690. .LeftJoin<Crm_CustomerCompany>((tcl, dc, cc) => dc.CrmCompanyId == cc.Id && dc.IsDel == 0)
  2691. .Where((tcl, dc, cc) => tcl.IsDel == 0 && tcl.DiId == dto.DiId)
  2692. .Select((tcl, dc, cc) => new
  2693. {
  2694. Name = dc.LastName + dc.FirstName,
  2695. Sex = dc.Sex,
  2696. Birthday = dc.BirthDay,
  2697. Company = cc.CompanyFullName,
  2698. Job = dc.Job
  2699. })
  2700. .ToList();
  2701. var blackCode = _sqlSugar.Queryable<Air_TicketBlackCode>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
  2702. var threeCodes = _sqlSugar.Queryable<Res_ThreeCode>().Where(it => it.IsDel == 0).ToList();
  2703. var placeData = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();
  2704. var rateDatas = await _EnterExitCosts.CurrencyRemark.SplitExchangeRate();
  2705. _DelegationInfo.VisitCountry = _DelegationInfo.VisitCountry.Replace("|", "、");
  2706. if (dto.ExportType == 1) //明细表
  2707. {
  2708. if (dto.SubTypeId == 1005) //1005(默认明细表)
  2709. {
  2710. if (!fileFormat.Equals("pdf")) fileFormat = "docx";
  2711. //获取模板
  2712. string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/出入境费用表模板.docx");
  2713. //载入模板
  2714. Document doc = new Document(tempPath);
  2715. DocumentBuilder builder = new DocumentBuilder(doc);
  2716. //利用键值对存放数据
  2717. Dictionary<string, string> dic = new Dictionary<string, string>();
  2718. decimal stayFeeTotal = _DayAndCosts.Where(it => it.Type == 1).Sum(it => it.SubTotal); // 住宿费
  2719. decimal mealsFeeTotal = _DayAndCosts.Where(it => it.Type == 2).Sum(it => it.SubTotal); // 伙食费费
  2720. decimal miscellaneousFeeTotal = _DayAndCosts.Where(it => it.Type == 3).Sum(it => it.SubTotal); // 公杂费
  2721. decimal tainFeeTotal = _DayAndCosts.Where(it => it.Type == 4).Sum(it => it.SubTotal); // 培训费
  2722. decimal insidePayTotal = _EnterExitCosts.InsidePay;
  2723. string row1_1 = "";
  2724. if (_EnterExitCosts.Visa > 0)
  2725. {
  2726. //insidePayTotal += _EnterExitCosts.Visa;
  2727. row1_1 = $"签证费: {_EnterExitCosts.Visa.ToString("#0.00")} 人民币/人";
  2728. if (!string.IsNullOrEmpty(_EnterExitCosts.VisaRemark))
  2729. {
  2730. row1_1 += $"\t签证费用描述: {_EnterExitCosts.VisaRemark}";
  2731. }
  2732. }
  2733. string row1_2 = "";
  2734. if (_EnterExitCosts.YiMiao > 0)
  2735. {
  2736. //insidePayTotal += _EnterExitCosts.YiMiao;
  2737. row1_2 += $"疫苗费:{_EnterExitCosts.YiMiao.ToString("#0.00")} 人民币/人";
  2738. }
  2739. if (_EnterExitCosts.HeSuan > 0)
  2740. {
  2741. //insidePayTotal += _EnterExitCosts.HeSuan;
  2742. row1_2 += $"核酸检测费:{_EnterExitCosts.HeSuan.ToString("#0.00")} 人民币/人";
  2743. }
  2744. if (_EnterExitCosts.Service > 0)
  2745. {
  2746. //insidePayTotal += _EnterExitCosts.Service;
  2747. row1_2 += $"服务费:{_EnterExitCosts.Service.ToString("#0.00")} 人民币/人";
  2748. }
  2749. string row1_3 = "";
  2750. if (_EnterExitCosts.Safe > 0)
  2751. {
  2752. //insidePayTotal += _EnterExitCosts.Safe;
  2753. row1_3 += $"保险费:{_EnterExitCosts.Safe.ToString("#0.00")} 人民币/人";
  2754. }
  2755. if (_EnterExitCosts.Ticket > 0)
  2756. {
  2757. //insidePayTotal += _EnterExitCosts.Ticket;
  2758. row1_3 += $"参展门票:{_EnterExitCosts.Ticket.ToString("#0.00")} 人民币/人";
  2759. }
  2760. string row1 = "";
  2761. if (!string.IsNullOrEmpty(row1_1)) row1 += $"{row1_1}\r\n";
  2762. if (!string.IsNullOrEmpty(row1_2)) row1 += $"{row1_2}\r\n";
  2763. if (!string.IsNullOrEmpty(row1_3)) row1 += $"{row1_3}";
  2764. dic.Add("InsidePay", insidePayTotal.ToString("#0.00"));
  2765. dic.Add("Row1Str", row1);
  2766. dic.Add("OutsideJJ", _EnterExitCosts.OutsideJJPay.ToString("#0.00"));
  2767. dic.Add("OutsaideGW", _EnterExitCosts.OutsideGWPay.ToString("#0.00"));
  2768. dic.Add("AirJJ", _EnterExitCosts.AirJJ.ToString("#0.00"));
  2769. dic.Add("AirGW", _EnterExitCosts.AirGW.ToString("#0.00"));
  2770. dic.Add("CityTranffic", _EnterExitCosts.CityTranffic.ToString("#0.00"));
  2771. dic.Add("SubZS", stayFeeTotal.ToString("#0.00"));
  2772. dic.Add("SubHS", mealsFeeTotal.ToString("#0.00"));
  2773. string miscellaneousFeeTotalStr = miscellaneousFeeTotal.ToString("#0.00");
  2774. dic.Add("SubGZF", miscellaneousFeeTotalStr);
  2775. //dic.Add("SubPX", tainFeeTotal.ToString("#0.00"));
  2776. decimal subJJC = insidePayTotal + stayFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + tainFeeTotal + _EnterExitCosts.OutsideJJPay;
  2777. decimal subGWC = insidePayTotal + stayFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + tainFeeTotal + _EnterExitCosts.OutsideGWPay;
  2778. dic.Add("SubJJC", subJJC.ToString("#0.00"));
  2779. dic.Add("SubGWC", subGWC.ToString("#0.00"));
  2780. #region 填充word模板书签内容
  2781. foreach (var key in dic.Keys)
  2782. {
  2783. builder.MoveToBookmark(key);
  2784. builder.Write(dic[key]);
  2785. }
  2786. #endregion
  2787. #region 填充word表格内容
  2788. ////获读取指定表格方法二
  2789. NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
  2790. Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
  2791. for (int i = 0; i < dac1.Count; i++)
  2792. {
  2793. Grp_DayAndCost dac = dac1[i];
  2794. if (dac == null) continue;
  2795. builder.MoveToCell(0, i, 0, 0);
  2796. builder.Write("第" + dac.Days.ToString() + "晚:");
  2797. builder.MoveToCell(0, i, 1, 0);
  2798. //builder.Write(placeData.Find(it => it.Id == dac.NationalTravelFeeId)?.Country ?? "Unknown");
  2799. //builder.Write(dac.Place == null ? "" : dac.Place);
  2800. builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
  2801. builder.MoveToCell(0, i, 2, 0);
  2802. builder.Write("费用标准:");
  2803. string curr = "";
  2804. var currData = _CurrDatas.Where(it => it.Id == dac.Currency).FirstOrDefault();
  2805. if (currData != null)
  2806. {
  2807. curr = currData.Name;
  2808. }
  2809. builder.MoveToCell(0, i, 3, 0);
  2810. builder.Write(dac.Cost.ToString("#0.00") + curr);
  2811. builder.MoveToCell(0, i, 4, 0);
  2812. builder.Write("费用小计:");
  2813. builder.MoveToCell(0, i, 5, 0);
  2814. builder.Write(dac.SubTotal.ToString("#0.00") + "CNY");
  2815. }
  2816. //删除多余行
  2817. while (table1.Rows.Count > dac1.Count)
  2818. {
  2819. table1.Rows.RemoveAt(dac1.Count);
  2820. }
  2821. Aspose.Words.Tables.Table table2 = allTables[1] as Aspose.Words.Tables.Table;
  2822. for (int i = 0; i < dac2.Count; i++)
  2823. {
  2824. Grp_DayAndCost dac = dac2[i];
  2825. if (dac == null) continue;
  2826. builder.MoveToCell(1, i, 0, 0);
  2827. builder.Write("第" + dac.Days.ToString() + "天:");
  2828. builder.MoveToCell(1, i, 1, 0);
  2829. builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
  2830. builder.MoveToCell(1, i, 2, 0);
  2831. builder.Write("费用标准:");
  2832. string curr = "";
  2833. var currData = _CurrDatas.Where(it => it.Id == dac.Currency).FirstOrDefault();
  2834. if (currData != null)
  2835. {
  2836. curr = currData.Name;
  2837. }
  2838. builder.MoveToCell(1, i, 3, 0);
  2839. builder.Write(dac.Cost.ToString("#0.00") + curr);
  2840. builder.MoveToCell(1, i, 4, 0);
  2841. builder.Write("费用小计:");
  2842. builder.MoveToCell(1, i, 5, 0);
  2843. builder.Write(dac.SubTotal.ToString("#0.00") + "CNY");
  2844. }
  2845. //删除多余行
  2846. while (table2.Rows.Count > dac2.Count)
  2847. {
  2848. table2.Rows.RemoveAt(dac2.Count);
  2849. }
  2850. Aspose.Words.Tables.Table table3 = allTables[2] as Aspose.Words.Tables.Table;
  2851. for (int i = 0; i < dac3.Count; i++)
  2852. {
  2853. Grp_DayAndCost dac = dac3[i];
  2854. if (dac == null) continue;
  2855. builder.MoveToCell(2, i, 0, 0);
  2856. builder.Write("第" + dac.Days.ToString() + "天:");
  2857. builder.MoveToCell(2, i, 1, 0);
  2858. builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
  2859. builder.MoveToCell(2, i, 2, 0);
  2860. builder.Write("费用标准:");
  2861. string curr = "";
  2862. var currData = _CurrDatas.Where(it => it.Id == dac.Currency).FirstOrDefault();
  2863. if (currData != null)
  2864. {
  2865. curr = currData.Name;
  2866. }
  2867. builder.MoveToCell(2, i, 3, 0);
  2868. builder.Write(dac.Cost.ToString("#0.00") + curr);
  2869. builder.MoveToCell(2, i, 4, 0);
  2870. builder.Write("费用小计:");
  2871. builder.MoveToCell(2, i, 5, 0);
  2872. builder.Write(dac.SubTotal.ToString("#0.00") + "CNY");
  2873. }
  2874. //删除多余行
  2875. while (table3.Rows.Count > dac3.Count)
  2876. {
  2877. table3.Rows.RemoveAt(dac3.Count);
  2878. }
  2879. #endregion
  2880. //文件名
  2881. //string strFileName = $"{_DelegationInfo.TeamName}出入境费用{Guid.NewGuid().ToString()}.{fileFormat}";
  2882. //string strFileName = $"{_DelegationInfo.TeamName}-出入境费用{Guid.NewGuid().ToString()}.{fileFormat}";
  2883. string strFileName = $"{_DelegationInfo.TeamName}{DateTime.UtcNow.ToString("yyyyMMddHHmmss")}.{fileFormat}";
  2884. AsposeHelper.removewatermark_v2180();
  2885. //doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
  2886. var saveFormat = Aspose.Words.SaveFormat.Docx;
  2887. if (fileFormat.Equals("pdf")) {
  2888. //doc.LayoutOptions.IsShowComments = false;
  2889. doc.LayoutOptions.IsShowHiddenText = false;
  2890. doc.LayoutOptions.IsShowParagraphMarks = false;
  2891. doc.LayoutOptions.RevisionOptions.DeletedTextEffect = RevisionTextEffect.None;
  2892. doc.AcceptAllRevisions();
  2893. Aspose.Words.Saving.PdfSaveOptions options = new Aspose.Words.Saving.PdfSaveOptions();
  2894. options.Compliance = PdfCompliance.PdfA1a;
  2895. options.CreateNoteHyperlinks = true;
  2896. doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName, options);
  2897. //_view.Data = new { Url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName };
  2898. _view.Data = new { Url = strFileName };
  2899. _view.Msg = "成功";
  2900. return _view;
  2901. }
  2902. doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName, saveFormat);
  2903. _view.Data = new { Url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName };
  2904. _view.Msg = "成功";
  2905. return _view;
  2906. }
  2907. else if (dto.SubTypeId == 1006)//1006(因公出国(境)经费测算明细表)
  2908. {
  2909. //获取模板
  2910. string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/因公出国(境)经费测算明细表.docx");
  2911. //载入模板
  2912. Document doc = new Document(tempPath);
  2913. DocumentBuilder builder = new DocumentBuilder(doc);
  2914. Dictionary<string, string> dic = new Dictionary<string, string>();
  2915. if (blackCode != null && !string.IsNullOrWhiteSpace(blackCode.BlackCode))
  2916. {
  2917. List<string> list = new List<string>();
  2918. try
  2919. {
  2920. var spilitArr = Regex.Split(blackCode.BlackCode, "\r\n");
  2921. foreach (var item in spilitArr)
  2922. {
  2923. var spDotandEmpty = item.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
  2924. var depCode = spDotandEmpty[2].Substring(0, 3);
  2925. var arrCode = spDotandEmpty[2].Substring(3, 3);
  2926. string depName = threeCodes.Find(it => it.Three.Equals(depCode)).City,
  2927. arrName = threeCodes.Find(it => it.Three.Equals(arrCode)).City;
  2928. list.Add(depName);
  2929. list.Add(arrName);
  2930. }
  2931. list = list.Distinct().ToList();
  2932. dic.Add("ReturnCode", string.Join("-", list).TrimEnd('-'));
  2933. }
  2934. catch (Exception)
  2935. {
  2936. dic.Add("ReturnCode", "行程录入不正确!");
  2937. }
  2938. }
  2939. else
  2940. {
  2941. dic.Add("ReturnCode", "未录入行程!");
  2942. }
  2943. dic.Add("ReturnCodeAir", dic["ReturnCode"]);
  2944. dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
  2945. dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));
  2946. if (dic.ContainsKey("VisitStartDate") && dic.ContainsKey("VisitEndDate"))
  2947. {
  2948. TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
  2949. dic.Add("Day", sp.Days.ToString());
  2950. }
  2951. dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
  2952. dic.Add("ClientUnit", _DelegationInfo.ClientUnit);
  2953. //var Names = string.Join("、", DeleClientList.Select(it => it.Name).ToList()).TrimEnd('、');
  2954. //dic.Add("Names", Names);
  2955. NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
  2956. Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
  2957. decimal dac1totalPrice = 0.00M;
  2958. int accommodationStartIndex = 6, foodandotherStartIndex = 22;
  2959. foreach (var dac in dac1)
  2960. {
  2961. if (dac.SubTotal == 0.00M)
  2962. {
  2963. continue;
  2964. }
  2965. //builder.MoveToCell(0, accommodationStartIndex, 0, 0);
  2966. //builder.Write(DeleClientList[i].LastName + DeleClientList[i].Name);
  2967. builder.MoveToCell(0, accommodationStartIndex, 1, 0);
  2968. builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
  2969. builder.MoveToCell(0, accommodationStartIndex, 2, 0);
  2970. string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknown";
  2971. builder.Write(currency);//币种
  2972. builder.MoveToCell(0, accommodationStartIndex, 3, 0);
  2973. builder.Write(dac.Cost.ToString("#0.00"));//标准
  2974. builder.MoveToCell(0, accommodationStartIndex, 4, 0);
  2975. builder.Write("");//人数
  2976. builder.MoveToCell(0, accommodationStartIndex, 5, 0);
  2977. builder.Write("");//天数
  2978. builder.MoveToCell(0, accommodationStartIndex, 6, 0);
  2979. builder.Write(dac.SubTotal.ToString("#0.00"));//小计
  2980. builder.MoveToCell(0, accommodationStartIndex, 7, 0);
  2981. decimal rate = 0.00M;
  2982. rate = rateDatas.Find(it => it.CurrencyName.Equals(currency))?.Rate ?? 0.00M;
  2983. builder.Write(rate.ToString("#0.0000"));//汇率
  2984. builder.MoveToCell(0, accommodationStartIndex, 8, 0);
  2985. decimal rbmPrice = dac.SubTotal;
  2986. builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
  2987. accommodationStartIndex++;
  2988. dac1totalPrice += Convert.ToDecimal(rbmPrice.ToString("#0.00"));
  2989. }
  2990. dic.Add("dac1totalPrice", dac1totalPrice.ToString("#0.00"));
  2991. for (int i = 21; i > (dac1.Count == 0 ? 1 : dac1.Count) + 6; i--)
  2992. {
  2993. table1.Rows.RemoveAt(i - 1);
  2994. foodandotherStartIndex--;
  2995. }
  2996. if (dac2.Count == dac3.Count)//国家 币种 金额
  2997. {
  2998. for (int i = 0; i < dac2.Count; i++)
  2999. {
  3000. dac2[i].SubTotal = dac2[i].SubTotal + dac3[i].SubTotal; //小计
  3001. dac2[i].Cost = dac3[i].Cost + dac2[i].Cost; //标准
  3002. }
  3003. }
  3004. decimal dac2totalPrice = 0.00M;
  3005. foreach (var dac in dac2)
  3006. {
  3007. if (dac.SubTotal == 0)
  3008. {
  3009. continue;
  3010. }
  3011. builder.MoveToCell(0, foodandotherStartIndex, 1, 0);
  3012. builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
  3013. builder.MoveToCell(0, foodandotherStartIndex, 2, 0);
  3014. string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknown";
  3015. builder.Write(currency);//币种
  3016. builder.MoveToCell(0, foodandotherStartIndex, 3, 0);
  3017. builder.Write(dac.Cost.ToString("#0.00"));//标准
  3018. builder.MoveToCell(0, foodandotherStartIndex, 4, 0);
  3019. builder.Write("");//人数
  3020. builder.MoveToCell(0, foodandotherStartIndex, 5, 0);
  3021. builder.Write("");//天数
  3022. builder.MoveToCell(0, foodandotherStartIndex, 6, 0);
  3023. builder.Write(dac.SubTotal.ToString("#0.00"));//小计
  3024. builder.MoveToCell(0, foodandotherStartIndex, 7, 0);
  3025. decimal rate = 0.00M;
  3026. rate = rateDatas.Find(it => it.CurrencyName.Equals(currency))?.Rate ?? 0.00M;
  3027. builder.Write(rate.ToString("#0.0000"));//汇率
  3028. builder.MoveToCell(0, foodandotherStartIndex, 8, 0);
  3029. decimal rbmPrice = dac.SubTotal;
  3030. builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
  3031. foodandotherStartIndex++;
  3032. dac2totalPrice += Convert.ToDecimal(rbmPrice.ToString("#0.00"));
  3033. }
  3034. dic.Add("dac2totalPrice", dac2totalPrice.ToString("#0.00"));
  3035. for (int i = foodandotherStartIndex + (15 - dac2.Count); i > (dac2.Count == 0 ? 1 : 0) + foodandotherStartIndex; i--)
  3036. {
  3037. table1.Rows.RemoveAt(i - 1);
  3038. }
  3039. dic.Add("CityTranffic", _EnterExitCosts.CityTranffic.ToString("#0.00"));
  3040. string otherFeeStr = "";
  3041. if (_EnterExitCosts.Visa > 0) otherFeeStr += $"签证费: {_EnterExitCosts.Visa.ToString("#0.00")} 元,";
  3042. if (_EnterExitCosts.Safe > 0) otherFeeStr += $"保险费: {_EnterExitCosts.Safe.ToString("#0.00")} 元,";
  3043. if (_EnterExitCosts.Ticket > 0) otherFeeStr += $"参展门票费: {_EnterExitCosts.Ticket.ToString("#0.00")} 元,";
  3044. if (otherFeeStr.Length > 0)
  3045. {
  3046. otherFeeStr = otherFeeStr.Substring(0, otherFeeStr.Length - 1);
  3047. otherFeeStr = $"({otherFeeStr})";
  3048. dic.Add("OtherFeeStr", otherFeeStr);
  3049. }
  3050. //总计
  3051. decimal allPrice = dac1totalPrice + dac2totalPrice + _EnterExitCosts.Visa + _EnterExitCosts.Safe + _EnterExitCosts.Ticket;
  3052. //国际旅费
  3053. string outsideJJ = "";
  3054. string allPriceJJ = "";
  3055. if (_EnterExitCosts.SumJJC == 1)
  3056. {
  3057. outsideJJ = string.Format(@"经济舱:{0} 元/人", _EnterExitCosts.AirJJ.ToString("#0.00"));
  3058. allPriceJJ = string.Format(@"经济舱:{0} 元/人", (allPrice + _EnterExitCosts.OutsideJJPay).ToString("#0.00"));
  3059. }
  3060. string outsideGW = "";
  3061. string allPriceGW = "";
  3062. if (_EnterExitCosts.SumGWC == 1)
  3063. {
  3064. outsideGW = string.Format(@"公务舱:{0} 元/人", _EnterExitCosts.AirGW.ToString("#0.00"));
  3065. allPriceGW = string.Format(@"公务舱:{0} 元/人", (allPrice + _EnterExitCosts.OutsideGWPay).ToString("#0.00"));
  3066. }
  3067. if (_EnterExitCosts.SumJJC == 1 || _EnterExitCosts.SumGWC == 1)
  3068. {
  3069. string InTravelPriceStr = string.Format(@" ({0} {1})", outsideJJ, outsideGW);
  3070. dic.Add("InTravelPrice", InTravelPriceStr);
  3071. string FinalSumPriceStr = string.Format(@" ({0} {1})", allPriceJJ, allPriceGW);
  3072. dic.Add("FinalSumPrice", FinalSumPriceStr);
  3073. }
  3074. //dic.Add("VisaPay", _EnterExitCosts.Visa.ToString("#0.00"));
  3075. //dic.Add("SafePay", _EnterExitCosts.Safe.ToString("#0.00"));
  3076. //dic.Add("YiMiao", _EnterExitCosts.YiMiao.ToString("#0.00"));
  3077. foreach (var key in dic.Keys)
  3078. {
  3079. builder.MoveToBookmark(key);
  3080. builder.Write(dic[key]);
  3081. }
  3082. //模板文件名
  3083. string strFileName = $"{_DelegationInfo.TeamName}因公出国(境)经费测算明细表.docx";
  3084. doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
  3085. string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
  3086. _view.Data = new { Url = url };
  3087. _view.Msg = "成功";
  3088. return _view;
  3089. }
  3090. else if (dto.SubTypeId == 1007) //1007(四川省商务厅出国经费财政先行审核表)
  3091. {
  3092. //获取模板
  3093. string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/四川省商务厅出国经费财政先行审核表.xls");
  3094. //载入模板
  3095. WorkbookDesigner designer = new WorkbookDesigner();
  3096. designer.Workbook = new Workbook(tempPath);
  3097. Dictionary<string, string> dic = new Dictionary<string, string>();
  3098. if (blackCode != null && !string.IsNullOrWhiteSpace(blackCode.BlackCode))
  3099. {
  3100. List<string> list = new List<string>();
  3101. try
  3102. {
  3103. var spilitArr = Regex.Split(blackCode.BlackCode, "\r\n");
  3104. foreach (var item in spilitArr)
  3105. {
  3106. var spDotandEmpty = item.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
  3107. var depCode = spDotandEmpty[2].Substring(0, 3);
  3108. var arrCode = spDotandEmpty[2].Substring(3, 3);
  3109. string depName = threeCodes.Find(it => it.Three.Equals(depCode))?.City ?? "Unknwon",
  3110. arrName = threeCodes.Find(it => it.Three.Equals(arrCode))?.City ?? "Unknown";
  3111. list.Add(depName);
  3112. list.Add(arrName);
  3113. }
  3114. list = list.Distinct().ToList();
  3115. dic.Add("ReturnCode", string.Join("-", list).TrimEnd('-'));
  3116. }
  3117. catch (Exception)
  3118. {
  3119. dic.Add("ReturnCode", "行程录入不正确!");
  3120. }
  3121. }
  3122. else
  3123. {
  3124. dic.Add("ReturnCode", "未录入行程!");
  3125. }
  3126. dic.Add("ReturnCodeAir", dic["ReturnCode"]);
  3127. dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
  3128. dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));
  3129. if (dic.ContainsKey("VisitStartDate") && dic.ContainsKey("VisitEndDate"))
  3130. {
  3131. TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
  3132. dic.Add("Day", sp.Days.ToString());
  3133. }
  3134. dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
  3135. dic.Add("ClientUnit", _DelegationInfo.ClientUnit);
  3136. var Names = string.Join("、", DeleClientList.Select(it => it.Name).ToList()).TrimEnd('、');
  3137. designer.SetDataSource("ClientUnit", _DelegationInfo.ClientUnit);
  3138. designer.SetDataSource("VisitCountry", _DelegationInfo.VisitCountry);
  3139. designer.SetDataSource("Group", _DelegationInfo.TeamName);
  3140. designer.SetDataSource("ClientUnitTitle", _DelegationInfo.TeamName);
  3141. designer.SetDataSource("Name", Names);
  3142. designer.SetDataSource("VisitStartDate", dic["VisitStartDate"] + "-" + dic["VisitEndDate"]);
  3143. designer.SetDataSource("Day", dic["Day"] + "天");
  3144. designer.SetDataSource("ReturnCode", dic["ReturnCode"]);
  3145. designer.SetDataSource("ReturnCodeAir", dic["ReturnCodeAir"]);
  3146. int startIndex = 10;
  3147. const int startIndexcopy = 10;
  3148. if (dac2.Count == dac3.Count)//国家 币种 金额
  3149. {
  3150. for (int i = 0; i < dac2.Count; i++)
  3151. {
  3152. dac2[i].SubTotal = dac2[i].SubTotal + dac3[i].SubTotal; //小计
  3153. dac2[i].Cost = dac3[i].Cost + dac2[i].Cost; //标准
  3154. }
  3155. }
  3156. DataTable dtdac1 = new DataTable();
  3157. List<string> place = new List<string>();
  3158. dtdac1.Columns.AddRange(new DataColumn[] {
  3159. new DataColumn(){ ColumnName = "city"},
  3160. new DataColumn(){ ColumnName = "curr"},
  3161. new DataColumn(){ ColumnName = "criterion"},
  3162. new DataColumn(){ ColumnName = "number",DataType = typeof(int)},
  3163. new DataColumn(){ ColumnName = "day",DataType = typeof(int)},
  3164. new DataColumn(){ ColumnName = "cost", DataType = typeof(decimal)},
  3165. new DataColumn(){ ColumnName = "rate", DataType = typeof(decimal) },
  3166. new DataColumn(){ ColumnName = "costRMB", DataType = typeof(decimal) },
  3167. });
  3168. DataTable dtdac2 = new DataTable();
  3169. dtdac2.Columns.AddRange(new DataColumn[] {
  3170. new DataColumn(){ ColumnName = "city"},
  3171. new DataColumn(){ ColumnName = "curr"},
  3172. new DataColumn(){ ColumnName = "criterion"},
  3173. new DataColumn(){ ColumnName = "number",DataType = typeof(int)},
  3174. new DataColumn(){ ColumnName = "day",DataType = typeof(int)},
  3175. new DataColumn(){ ColumnName = "cost", DataType = typeof(decimal)},
  3176. new DataColumn(){ ColumnName = "rate", DataType = typeof(decimal) },
  3177. new DataColumn(){ ColumnName = "costRMB",DataType = typeof(decimal)},
  3178. });
  3179. dtdac1.TableName = "tb1";
  3180. dtdac2.TableName = "tb2";
  3181. decimal dac1totalPrice = 0.00M, dac2totalPrice = 0.00M;
  3182. foreach (var item in dac1)
  3183. {
  3184. item.Place = GetEnterExitCostExportCity(placeData, item.NationalTravelFeeId);
  3185. if (place.Contains(item.Place))
  3186. {
  3187. continue;
  3188. }
  3189. DataRow row = dtdac1.NewRow();
  3190. row["city"] = item.Place;
  3191. string currency = _CurrDatas.Find(it => it.Id == item.Currency)?.Remark ?? "Unknwon";
  3192. decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
  3193. row["curr"] = currency;
  3194. row["rate"] = rate.ToString("#0.0000");
  3195. row["criterion"] = item.Cost.ToString("#0.00");
  3196. row["number"] = 1;
  3197. row["day"] = dac1.FindAll(x => x.NationalTravelFeeId == item.NationalTravelFeeId).Count;
  3198. //row["costRMB"] = rbmPrice;
  3199. dtdac1.Rows.Add(row);
  3200. place.Add(item.Place);
  3201. }
  3202. place = new List<string>();
  3203. foreach (var item in dac2)
  3204. {
  3205. item.Place = GetEnterExitCostExportCity(placeData, item.NationalTravelFeeId);
  3206. if (place.Contains(item.Place))
  3207. {
  3208. continue;
  3209. }
  3210. DataRow row = dtdac2.NewRow();
  3211. row["city"] = item.Place;
  3212. string currency = _CurrDatas.Find(it => it.Id == item.Currency)?.Remark ?? "Unknwon";
  3213. decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
  3214. row["curr"] = currency;
  3215. row["rate"] = rate.ToString("#0.0000");
  3216. row["criterion"] = item.Cost.ToString("#0.00");
  3217. row["number"] = 1;
  3218. row["day"] = dac2.FindAll(x => x.NationalTravelFeeId == item.NationalTravelFeeId).Count;
  3219. //row["cost"] = item.SubTotal;
  3220. //row["costRMB"] = rbmPrice;
  3221. dtdac2.Rows.Add(row);
  3222. place.Add(item.Place);
  3223. //dac2totalPrice += rbmPrice;
  3224. }
  3225. dac1totalPrice = dac1.Sum(it => it.SubTotal);
  3226. dac2totalPrice = dac2.Sum(it => it.SubTotal);
  3227. designer.SetDataSource("dac1totalPrice", dac1totalPrice.ToString("#0.00"));
  3228. designer.SetDataSource("dac2totalPrice", dac2totalPrice);
  3229. designer.SetDataSource("cityTranffic", @$"其中:国外城市间机票费: {_EnterExitCosts.CityTranffic.ToString("#0.00")} 元");
  3230. designer.SetDataSource("sumCityTranffic", @$"{_EnterExitCosts.CityTranffic.ToString("#0.00")} ");
  3231. string cell4Str = $" 4.国际旅费:经济舱:{_EnterExitCosts.AirJJ.ToString("#0.00")} 元/人,公务舱:{_EnterExitCosts.AirGW.ToString("#0.00")} 元/人";
  3232. string cellStr = $" 5.其他费用(";
  3233. if (_EnterExitCosts.Visa > 0) cellStr += $"签证费:{_EnterExitCosts.Visa.ToString("#0.00")}元,";
  3234. if (_EnterExitCosts.YiMiao > 0) cellStr += $"疫苗费:{_EnterExitCosts.YiMiao.ToString("#0.00")}元,";
  3235. if (_EnterExitCosts.HeSuan > 0) cellStr += $"核酸费:{_EnterExitCosts.HeSuan.ToString("#0.00")}元,";
  3236. if (_EnterExitCosts.Safe > 0) cellStr += $"保险费:{_EnterExitCosts.Safe.ToString("#0.00")}元,";
  3237. if (_EnterExitCosts.Ticket > 0) cellStr += $"参展门票费:{_EnterExitCosts.Ticket.ToString("#0.00")}元,";
  3238. if (_EnterExitCosts.Service > 0) cellStr += $"服务费:{_EnterExitCosts.Service.ToString("#0.00")}元,";
  3239. if (cellStr.Length > 8)
  3240. {
  3241. cellStr = cellStr.Substring(0, cellStr.Length - 1);
  3242. }
  3243. cellStr += ")";
  3244. decimal otherFee = _EnterExitCosts.Visa + _EnterExitCosts.YiMiao + _EnterExitCosts.HeSuan + _EnterExitCosts.Safe + _EnterExitCosts.Ticket + _EnterExitCosts.Service;
  3245. decimal s = dac1totalPrice + dac2totalPrice + _EnterExitCosts.OutsideJJPay + _EnterExitCosts.OutsideGWPay + otherFee;
  3246. decimal pxFee = dac4.Sum(it => it.Cost);
  3247. decimal glvFee = _EnterExitCosts.OutsideJJPay + _EnterExitCosts.OutsideGWPay;
  3248. string celllastStr1 = "";
  3249. if (dac1totalPrice > 0) celllastStr1 += $"住宿费 {dac1totalPrice.ToString("#0.00")} 元";
  3250. if (dac2totalPrice > 0) celllastStr1 += $",伙食费和公杂费 {dac2totalPrice.ToString("#0.00")} 元";
  3251. if (pxFee > 0) celllastStr1 += $",培训费 {pxFee.ToString("#0.00")} 元";
  3252. celllastStr1 += $",国际旅费 元";
  3253. if (otherFee > 0) celllastStr1 += $",其他费用 {otherFee.ToString("#0.00")} 元";
  3254. string celllastStr = $" 经审核,{celllastStr1},本次出国经费预算合计为 元。其中:市本级安排 。";
  3255. designer.SetDataSource("cell4Str", cell4Str);
  3256. designer.SetDataSource("cellStr", cellStr);
  3257. designer.SetDataSource("cellSum", (_EnterExitCosts.Visa + _EnterExitCosts.Safe).ToString("#0.00"));
  3258. designer.SetDataSource("cellSum4", (_EnterExitCosts.OutsideJJPay + _EnterExitCosts.OutsideGWPay).ToString("#0.00"));
  3259. designer.SetDataSource("celllastStr", celllastStr);
  3260. Workbook wb = designer.Workbook;
  3261. var sheet = wb.Worksheets[0];
  3262. //绑定datatable数据集
  3263. designer.SetDataSource(dtdac1);
  3264. designer.SetDataSource(dtdac2);
  3265. designer.Process();
  3266. var rowStart = dtdac1.Rows.Count;
  3267. while (rowStart > 0)
  3268. {
  3269. sheet.Cells[startIndex, 8].Formula = $"=G{startIndex + 1} * H{startIndex + 1}";
  3270. sheet.Cells[startIndex, 6].Formula = $"=E{startIndex + 1} * F{startIndex + 1} * D{startIndex + 1}";
  3271. startIndex++;
  3272. rowStart--;
  3273. }
  3274. sheet.Cells[startIndex, 8].Formula = $"=SUM(I{startIndexcopy + 1}: I{startIndex})";
  3275. startIndex += 1; //总计行
  3276. rowStart = dtdac2.Rows.Count;
  3277. while (rowStart > 0)
  3278. {
  3279. sheet.Cells[startIndex, 8].Formula = $"= G{startIndex + 1} * H{startIndex + 1}";
  3280. sheet.Cells[startIndex, 6].Formula = $"= E{startIndex + 1} * F{startIndex + 1} * D{startIndex + 1}";
  3281. startIndex++;
  3282. rowStart--;
  3283. }
  3284. sheet.Cells[startIndex, 8].Formula = $"=SUM(I{startIndexcopy + dtdac1.Rows.Count + 2}: I{startIndex})";
  3285. wb.CalculateFormula(true);
  3286. //模板文件名
  3287. string strFileName = $"{_DelegationInfo.TeamName}-四川省商务厅出国经费财政先行审核表{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls"; ;//$".xls";
  3288. designer.Workbook.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
  3289. string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
  3290. _view.Data = new { Url = url };
  3291. _view.Msg = "成功";
  3292. return _view;
  3293. }
  3294. else if (dto.SubTypeId == 1066) //成都市因公临时出国任务和预算审批意见表(外专培训团专用)
  3295. {
  3296. //获取模板
  3297. string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/成都市因公临时出国任务和预算审批意见表.docx");
  3298. //载入模板
  3299. Document doc = new Document(tempPath);
  3300. DocumentBuilder builder = new DocumentBuilder(doc);
  3301. Dictionary<string, string> dic = new Dictionary<string, string>();
  3302. dic.Add("GroupName", _DelegationInfo.TeamName);
  3303. dic.Add("ClientUnit", _DelegationInfo.ClientUnit);
  3304. string missionLeader = ""; //团负责人默认接团客户名单第一个人
  3305. string missionLeaderJob = "";//负责人job
  3306. int groupNumber = 0; //团人数
  3307. if (DeleClientList.Count > 0)
  3308. {
  3309. missionLeader = DeleClientList[0]?.Name ?? "";
  3310. missionLeaderJob = DeleClientList[0]?.Job ?? "";
  3311. }
  3312. dic.Add("MissionLeader", missionLeader); //团负责人
  3313. dic.Add("MissionLeaderJob", missionLeaderJob); //团负责人job
  3314. dic.Add("GroupNumber", _DelegationInfo.VisitPNumber.ToString()); //团人数
  3315. #region MyRegion
  3316. //if (blackCode != null && !string.IsNullOrWhiteSpace(blackCode.BlackCode))
  3317. //{
  3318. // List<string> list = new List<string>();
  3319. // try
  3320. // {
  3321. // var spilitArr = Regex.Split(blackCode.BlackCode, "\r\n");
  3322. // foreach (var item in spilitArr)
  3323. // {
  3324. // var spDotandEmpty = item.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
  3325. // var depCode = spDotandEmpty[2].Substring(0, 3);
  3326. // var arrCode = spDotandEmpty[2].Substring(3, 3);
  3327. // string depName = threeCodes.Find(it => it.Three.Equals(depCode)).City,
  3328. // arrName = threeCodes.Find(it => it.Three.Equals(arrCode)).City;
  3329. // list.Add(depName);
  3330. // list.Add(arrName);
  3331. // }
  3332. // list = list.Distinct().ToList();
  3333. // dic.Add("ReturnCode", string.Join("-", list).TrimEnd('-'));
  3334. // }
  3335. // catch (Exception)
  3336. // {
  3337. // dic.Add("ReturnCode", "行程录入不正确!");
  3338. // }
  3339. //}
  3340. //else
  3341. //{
  3342. // dic.Add("ReturnCode", "未录入行程!");
  3343. //}
  3344. List<string> countrys = _dirRep.GroupSplitCountry(_DelegationInfo.VisitCountry);
  3345. dic.Add("ReturnCode", string.Join("、", countrys));
  3346. #endregion
  3347. //dic.Add("ReturnCodeAir", dic["ReturnCode"]);
  3348. //dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
  3349. //dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));
  3350. //if (dic.ContainsKey("VisitStartDate") && dic.ContainsKey("VisitEndDate"))
  3351. //{
  3352. // TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
  3353. // dic.Add("Day", sp.Days.ToString());
  3354. //}
  3355. dic.Add("Day", _DelegationInfo.VisitDays.ToString());
  3356. dic.Add("CultivateDay", dac4.Count.ToString()); //培训天数
  3357. // dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
  3358. NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
  3359. Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
  3360. //培训人员名单
  3361. int cultivateRowIndex = 7;
  3362. foreach (var item in DeleClientList)
  3363. {
  3364. builder.MoveToCell(0, cultivateRowIndex, 0, 0);
  3365. builder.Write(item.Name);
  3366. builder.MoveToCell(0, cultivateRowIndex, 1, 0);
  3367. builder.Write(item.Sex == 0 ? "男" : item.Sex == 1 ? "女" : "");
  3368. builder.MoveToCell(0, cultivateRowIndex, 2, 0);
  3369. string birthDay = "";
  3370. if (item.Birthday != null)
  3371. {
  3372. DateTime dt = Convert.ToDateTime(item.Birthday);
  3373. birthDay = $"{dt.Year}.{dt.Month}";
  3374. }
  3375. builder.Write(birthDay);
  3376. builder.MoveToCell(0, cultivateRowIndex, 3, 0);
  3377. builder.Write(item.Company);
  3378. builder.MoveToCell(0, cultivateRowIndex, 4, 0);
  3379. builder.Write(item.Job);
  3380. cultivateRowIndex++;
  3381. }
  3382. //删除多余行
  3383. //cultivateRowIndex -= 2;
  3384. int delRows = 10 + 7 - cultivateRowIndex;
  3385. if (delRows > 0)
  3386. {
  3387. for (int i = 0; i < delRows; i++)
  3388. {
  3389. table1.Rows.RemoveAt(cultivateRowIndex);
  3390. //cultivateRowIndex++;
  3391. }
  3392. }
  3393. decimal hotelFeeTotal = dac1.Sum(it => it.SubTotal);//住宿费
  3394. dic.Add("HotelFeeTotal", hotelFeeTotal.ToString("#0.00"));
  3395. decimal mealsFeeTotal = dac2.Sum(it => it.SubTotal);//伙食费
  3396. dic.Add("MealsFeeTotal", mealsFeeTotal.ToString("#0.00"));
  3397. decimal miscellaneousFeeTotal = dac3.Sum(it => it.SubTotal);//公杂费
  3398. dic.Add("MiscellaneousFeeTotal", miscellaneousFeeTotal.ToString("#0.00"));
  3399. decimal trainingFeeTotal = dac4.Sum(it => it.SubTotal);//培训费
  3400. dic.Add("TrainingFeeTotal", trainingFeeTotal.ToString("#0.00"));
  3401. decimal cityTranfficFeeToatal = _EnterExitCosts.CityTranffic; //城市区间交通费
  3402. dic.Add("CityTranfficFeeToatal", cityTranfficFeeToatal.ToString("#0.00"));//
  3403. //其他费用
  3404. decimal otherFeeTotal = _EnterExitCosts.Visa + _EnterExitCosts.Safe + _EnterExitCosts.Ticket + _EnterExitCosts.YiMiao + _EnterExitCosts.HeSuan + _EnterExitCosts.Service;
  3405. dic.Add("OtherFeeTotal", otherFeeTotal.ToString("#0.00"));
  3406. //其他费用合计
  3407. decimal _otherFeeTotal = hotelFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + trainingFeeTotal + cityTranfficFeeToatal + otherFeeTotal;
  3408. decimal _jjcFeeToatal = _EnterExitCosts.AirJJ + _otherFeeTotal; //经济舱
  3409. decimal _gwcFeeToatal = _EnterExitCosts.AirGW + _otherFeeTotal; //公务舱
  3410. //公务舱合计
  3411. //国际旅费
  3412. string outsideJJ = "";
  3413. string allPriceJJ = "";
  3414. if (_EnterExitCosts.SumJJC == 1 && _EnterExitCosts.SumGWC == 0)
  3415. {
  3416. dic.Add("AirFeeTotal", _EnterExitCosts.AirJJ.ToString("#0.00"));
  3417. dic.Add("FeeTotal", _jjcFeeToatal.ToString("#0.00"));
  3418. }
  3419. if (_EnterExitCosts.SumGWC == 1 && _EnterExitCosts.SumJJC == 0)
  3420. {
  3421. dic.Add("AirFeeTotal", _EnterExitCosts.AirGW.ToString("#0.00"));
  3422. dic.Add("FeeTotal", _gwcFeeToatal.ToString("#0.00"));
  3423. }
  3424. if (_EnterExitCosts.SumJJC == 1 && _EnterExitCosts.SumGWC == 1)
  3425. {
  3426. string airFeeTotalStr = string.Format(@$"经济舱:{_EnterExitCosts.AirJJ.ToString("#0.00")} 公务舱:{_EnterExitCosts.AirGW.ToString("#0.00")}");
  3427. dic.Add("AirFeeTotal", airFeeTotalStr);
  3428. string feeTotalStr = string.Format(@$"经济舱:{_jjcFeeToatal.ToString("#0.00")} 公务舱:{_gwcFeeToatal.ToString("#0.00")}");
  3429. dic.Add("FeeTotal", feeTotalStr);
  3430. }
  3431. foreach (var key in dic.Keys)
  3432. {
  3433. builder.MoveToBookmark(key);
  3434. builder.Write(dic[key]);
  3435. }
  3436. //模板文件名
  3437. string strFileName = $"{_DelegationInfo.TeamName}-成都市因公临时出国任务和预算审批意见表(外专培训团专用).docx";
  3438. doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
  3439. string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
  3440. _view.Data = new { Url = url };
  3441. _view.Msg = "成功";
  3442. return _view;
  3443. }
  3444. }
  3445. else if (dto.ExportType == 2) //表格
  3446. {
  3447. //利用键值对存放数据
  3448. Dictionary<string, string> dic = new Dictionary<string, string>();
  3449. dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
  3450. dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));
  3451. TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
  3452. dic.Add("Day", sp.Days.ToString());
  3453. dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
  3454. if (dto.SubTypeId == 1008) //1008(派员单位出(境)任务和预算审批意见表)
  3455. {
  3456. //获取模板
  3457. string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/派员单位出(境)任务和预算审批意见表.docx");
  3458. //载入模板
  3459. Document doc = new Document(tempPath);
  3460. DocumentBuilder builder = new DocumentBuilder(doc);
  3461. dic.Add("TeamName", _DelegationInfo.TeamName);
  3462. dic.Add("ClientUnit", _DelegationInfo.ClientUnit);
  3463. //dic.Add("TellPhone", _DelegationInfo.TellPhone);
  3464. string missionLeaderName = "",
  3465. missionLeaderJob = "";
  3466. if (DeleClientList.Count > 0)
  3467. {
  3468. missionLeaderName = DeleClientList[0].Name;
  3469. missionLeaderJob = DeleClientList[0].Job;
  3470. }
  3471. dic.Add("MissionLeaderName", missionLeaderName);
  3472. dic.Add("MissionLeaderJob", missionLeaderJob);
  3473. dic.Add("VisitPNumber", _DelegationInfo.VisitPNumber.ToString());
  3474. dic.Add("VisitPurpose", _DelegationInfo.VisitPurpose);
  3475. NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
  3476. Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
  3477. int rowCount = 10;//总人数行
  3478. int startRowIndex = 7; //起始行
  3479. for (int i = 0; i < DeleClientList.Count; i++)
  3480. {
  3481. builder.MoveToCell(0, startRowIndex, 0, 0);
  3482. builder.Write(DeleClientList[i].Name); //出国人员姓名
  3483. builder.MoveToCell(0, startRowIndex, 1, 0);
  3484. string sex = DeleClientList[i].Sex == 0 ? "男" : DeleClientList[i].Sex == 1 ? "女" : "";
  3485. builder.Write(sex);//性别
  3486. builder.MoveToCell(0, startRowIndex, 2, 0);
  3487. builder.Write(Convert.ToDateTime(DeleClientList[i].Birthday).ToString("yyyy年MM月dd日"));//出生年月
  3488. builder.MoveToCell(0, startRowIndex, 3, 0);
  3489. builder.Write(DeleClientList[i].Company);//工作单位
  3490. builder.MoveToCell(0, startRowIndex, 4, 0);
  3491. builder.Write(DeleClientList[i].Job);//职务及级别
  3492. builder.MoveToCell(0, startRowIndex, 5, 0);
  3493. builder.Write("");//人员属性
  3494. builder.MoveToCell(0, startRowIndex, 6, 0);
  3495. builder.Write("");//上次出国时间
  3496. startRowIndex++;
  3497. }
  3498. int nullRow = rowCount - DeleClientList.Count;//空行
  3499. for (int i = 0; i < nullRow; i++)
  3500. {
  3501. table1.Rows.Remove(table1.Rows[startRowIndex]);
  3502. }
  3503. foreach (var key in dic.Keys)
  3504. {
  3505. builder.MoveToBookmark(key);
  3506. builder.Write(dic[key]);
  3507. }
  3508. //模板文件名
  3509. string strFileName = $"派员单位出(境)任务和预算审批意见表.docx";
  3510. doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
  3511. string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
  3512. _view.Data = new { Url = url };
  3513. _view.Msg = "成功";
  3514. return _view;
  3515. }
  3516. else if (dto.SubTypeId == 1009)//1009(省级单位出(境)经费报销单)
  3517. {
  3518. //获取模板
  3519. string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/省级单位出(境)经费报销单.docx");
  3520. //载入模板
  3521. Document doc = new Document(tempPath);
  3522. DocumentBuilder builder = new DocumentBuilder(doc);
  3523. dic.Add("GroupClient", _DelegationInfo.ClientUnit);
  3524. var Names = string.Join("、", DeleClientList.Select(x => x.Name)).TrimEnd('、');
  3525. dic.Add("Names", Names);
  3526. int accommodationRows = 12, foodandotherRows = 12;
  3527. var Dac1currCn = dac1.GroupBy(x => x.Currency).Select(x => x.Key).ToList();
  3528. var Dac2currCn = dac2.GroupBy(x => x.Currency).Select(x => x.Key).ToList();
  3529. NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
  3530. Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
  3531. int accommodationStartIndex = 6;
  3532. decimal dac1totalPrice = 0.00M;
  3533. foreach (var dac in dac1)
  3534. {
  3535. if (dac.SubTotal == 0)
  3536. {
  3537. continue;
  3538. }
  3539. //builder.MoveToCell(0, accommodationStartIndex, 0, 0);
  3540. //builder.Write(DeleClientList[i].LastName + DeleClientList[i].Name);
  3541. builder.MoveToCell(0, accommodationStartIndex, 1, 0);
  3542. builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));//城市
  3543. builder.MoveToCell(0, accommodationStartIndex, 2, 0);
  3544. string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknwon";
  3545. builder.Write(currency);//币种
  3546. builder.MoveToCell(0, accommodationStartIndex, 3, 0);
  3547. builder.Write(dac.Cost.ToString("#0.00"));//标准
  3548. builder.MoveToCell(0, accommodationStartIndex, 4, 0);
  3549. builder.Write("");//人数
  3550. builder.MoveToCell(0, accommodationStartIndex, 5, 0);
  3551. builder.Write("");//天数
  3552. builder.MoveToCell(0, accommodationStartIndex, 6, 0);
  3553. builder.Write(dac.SubTotal.ToString("#0.00"));//小计
  3554. builder.MoveToCell(0, accommodationStartIndex, 7, 0);
  3555. decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
  3556. builder.Write(rate.ToString("#0.0000"));//汇率
  3557. builder.MoveToCell(0, accommodationStartIndex, 8, 0);
  3558. decimal rbmPrice = rate * dac.SubTotal;
  3559. builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
  3560. accommodationStartIndex++;
  3561. dac1totalPrice += rbmPrice;
  3562. }
  3563. dic.Add("dac1totalPrice", dac1totalPrice.ToString("#0.00"));
  3564. builder.MoveToCell(0, accommodationStartIndex, 1, 0);
  3565. builder.Write("小计");
  3566. builder.MoveToCell(0, accommodationStartIndex, 8, 0);
  3567. builder.Write(dac1totalPrice.ToString("#0.00"));
  3568. accommodationStartIndex++;
  3569. int nullRow = accommodationRows - dac1.Count;
  3570. //删除空行
  3571. //if (nullRow > 0)
  3572. //{
  3573. // int rowIndex = accommodationStartIndex;
  3574. // for (int i = 0; i < nullRow; i++)
  3575. // {
  3576. // Row row = table1.Rows[rowIndex];
  3577. // row.Remove();
  3578. // rowIndex++;
  3579. // }
  3580. //}
  3581. if (dac2.Count == dac3.Count)//国家 币种 金额
  3582. {
  3583. for (int i = 0; i < dac2.Count; i++)
  3584. {
  3585. dac2[i].SubTotal = dac2[i].SubTotal + dac3[i].SubTotal; //小计
  3586. dac2[i].Cost = dac3[i].Cost + dac2[i].Cost; //标准
  3587. }
  3588. }
  3589. int foodandotherStartIndex = 19;//
  3590. decimal dac2totalPrice = 0.00M;
  3591. foreach (var dac in dac2)
  3592. {
  3593. if (dac.SubTotal == 0)
  3594. {
  3595. continue;
  3596. }
  3597. //foodandotherStartIndex = 12;
  3598. builder.MoveToCell(0, foodandotherStartIndex, 1, 0);
  3599. builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));//城市
  3600. builder.MoveToCell(0, foodandotherStartIndex, 2, 0);
  3601. string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknwon";
  3602. builder.Write(currency);//币种
  3603. builder.MoveToCell(0, foodandotherStartIndex, 3, 0);
  3604. builder.Write(dac.Cost.ToString("#0.00"));//标准
  3605. builder.MoveToCell(0, foodandotherStartIndex, 4, 0);
  3606. builder.Write("");//人数
  3607. builder.MoveToCell(0, foodandotherStartIndex, 5, 0);
  3608. builder.Write("");//天数
  3609. builder.MoveToCell(0, foodandotherStartIndex, 6, 0);
  3610. builder.Write(dac.SubTotal.ToString("#0.00"));//小计
  3611. builder.MoveToCell(0, foodandotherStartIndex, 7, 0);
  3612. decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
  3613. builder.Write(rate.ToString("#0.0000"));//汇率
  3614. builder.MoveToCell(0, foodandotherStartIndex, 8, 0);
  3615. decimal rbmPrice = rate * dac.SubTotal;
  3616. builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
  3617. foodandotherStartIndex++;
  3618. dac2totalPrice += rbmPrice;
  3619. }
  3620. dic.Add("dac2totalPrice", dac2totalPrice.ToString("#0.00"));
  3621. //删除空行
  3622. if (dac2.Count < foodandotherRows)
  3623. {
  3624. //int nullRow = accommodationRows - dac2.Count;
  3625. //while (table2.Rows.Count > dac2.Count)
  3626. //{
  3627. // table2.Rows.RemoveAt(dac2.Count);
  3628. //}
  3629. }
  3630. dic.Add("CityTranffic", _EnterExitCosts.CityTranffic.ToString("#0.00"));
  3631. string otherFeeStr = "";
  3632. if (_EnterExitCosts.Visa > 0) otherFeeStr += $"签证费: {_EnterExitCosts.Visa.ToString("#0.00")} 元,";
  3633. if (_EnterExitCosts.Safe > 0) otherFeeStr += $"保险费: {_EnterExitCosts.Safe.ToString("#0.00")} 元,";
  3634. if (_EnterExitCosts.Ticket > 0) otherFeeStr += $"参展门票费: {_EnterExitCosts.Ticket.ToString("#0.00")} 元,";
  3635. if (otherFeeStr.Length > 0)
  3636. {
  3637. otherFeeStr = otherFeeStr.Substring(0, otherFeeStr.Length - 1);
  3638. otherFeeStr = $"({otherFeeStr})";
  3639. dic.Add("OtherFeeStr", otherFeeStr);
  3640. }
  3641. foreach (var key in dic.Keys)
  3642. {
  3643. builder.MoveToBookmark(key);
  3644. builder.Write(dic[key]);
  3645. }
  3646. //模板文件名
  3647. string strFileName = $"省级单位出(境)经费报销单.docx";
  3648. doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
  3649. string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
  3650. _view.Data = new { Url = url };
  3651. _view.Msg = "成功";
  3652. return _view;
  3653. }
  3654. }
  3655. else if (dto.ExportType == 3)
  3656. {
  3657. if (dto.SubTypeId == 1) //团组成员名单
  3658. {
  3659. if (DeleClientList.Count < 1)
  3660. {
  3661. _view.Msg = "团组成员暂未录入!!!";
  3662. return _view;
  3663. }
  3664. //获取模板
  3665. string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/dwon_团组人员列表模板.doc");
  3666. //载入模板
  3667. Document doc = new Document(tempPath);
  3668. DocumentBuilder builder = new DocumentBuilder(doc);
  3669. //获取word里所有表格
  3670. NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
  3671. //获取所填表格的序数
  3672. Aspose.Words.Tables.Table tableOne = allTables[0] as Aspose.Words.Tables.Table;
  3673. var rowStart = tableOne.Rows[0]; //获取第1行
  3674. //循环赋值
  3675. for (int i = 0; i < DeleClientList.Count; i++)
  3676. {
  3677. builder.MoveToCell(0, i + 1, 0, 0);
  3678. builder.Write(DeleClientList[i].Name);
  3679. builder.MoveToCell(0, i + 1, 1, 0);
  3680. string sex = DeleClientList[i].Sex == 0 ? "男" : DeleClientList[i].Sex == 1 ? "女" : "";
  3681. builder.Write(sex);
  3682. builder.MoveToCell(0, i + 1, 2, 0);
  3683. builder.Write(Convert.ToDateTime(DeleClientList[i].Birthday).ToString("yyyy年MM月dd日"));
  3684. builder.MoveToCell(0, i + 1, 3, 0);
  3685. builder.Write(DeleClientList[i].Company);
  3686. builder.MoveToCell(0, i + 1, 4, 0);
  3687. builder.Write(DeleClientList[i].Job);
  3688. }
  3689. //删除多余行
  3690. while (tableOne.Rows.Count > DeleClientList.Count + 1)
  3691. {
  3692. tableOne.Rows.RemoveAt(DeleClientList.Count + 1);
  3693. }
  3694. string strFileName = $"{_DelegationInfo.TeamName}组团人员名单({DateTime.Now.ToString("yyyyMMddHHmmss")}).doc";
  3695. //C:/Server/File/OA2023/Office/Word/EnterExitCost/File/
  3696. //C:\Server\File\OA2023\Office\Word\EnterExitCost\File\
  3697. doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
  3698. string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
  3699. _view.Data = new { Url = url };
  3700. _view.Msg = "成功";
  3701. return _view;
  3702. }
  3703. }
  3704. }
  3705. catch (Exception ex)
  3706. {
  3707. _view.Code = StatusCodes.Status500InternalServerError;
  3708. _view.Msg = ex.Message;
  3709. return _view;
  3710. }
  3711. return _view;
  3712. }
  3713. #endregion
  3714. }
  3715. }