1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182218321842185218621872188218921902191219221932194219521962197219821992200220122022203220422052206220722082209221022112212221322142215221622172218221922202221222222232224222522262227222822292230223122322233223422352236223722382239224022412242224322442245224622472248224922502251225222532254225522562257225822592260226122622263226422652266226722682269227022712272227322742275227622772278227922802281228222832284228522862287228822892290229122922293229422952296229722982299230023012302230323042305230623072308230923102311231223132314231523162317231823192320232123222323232423252326232723282329233023312332233323342335233623372338233923402341234223432344234523462347234823492350235123522353235423552356235723582359236023612362236323642365236623672368236923702371237223732374237523762377237823792380238123822383238423852386238723882389239023912392239323942395239623972398239924002401240224032404240524062407240824092410241124122413241424152416241724182419242024212422242324242425242624272428242924302431243224332434243524362437243824392440244124422443244424452446244724482449245024512452245324542455245624572458245924602461246224632464246524662467246824692470247124722473247424752476247724782479248024812482248324842485248624872488248924902491249224932494249524962497249824992500250125022503250425052506250725082509251025112512251325142515251625172518251925202521252225232524252525262527252825292530253125322533253425352536253725382539254025412542254325442545254625472548254925502551255225532554255525562557255825592560256125622563256425652566256725682569257025712572257325742575257625772578257925802581258225832584258525862587258825892590259125922593259425952596259725982599260026012602260326042605260626072608260926102611261226132614261526162617261826192620262126222623262426252626262726282629263026312632263326342635263626372638263926402641264226432644264526462647264826492650265126522653265426552656265726582659266026612662266326642665266626672668266926702671267226732674267526762677267826792680268126822683268426852686268726882689269026912692269326942695269626972698269927002701270227032704270527062707270827092710271127122713271427152716271727182719272027212722272327242725272627272728272927302731273227332734273527362737273827392740274127422743274427452746274727482749275027512752275327542755275627572758275927602761276227632764276527662767276827692770277127722773277427752776277727782779278027812782278327842785278627872788278927902791279227932794279527962797279827992800280128022803280428052806280728082809281028112812281328142815281628172818281928202821282228232824282528262827282828292830283128322833283428352836283728382839284028412842284328442845284628472848284928502851285228532854285528562857285828592860286128622863286428652866286728682869287028712872287328742875287628772878287928802881288228832884288528862887288828892890289128922893289428952896289728982899290029012902290329042905290629072908290929102911291229132914291529162917291829192920292129222923292429252926292729282929293029312932293329342935293629372938293929402941294229432944294529462947294829492950295129522953295429552956295729582959296029612962296329642965296629672968296929702971297229732974297529762977297829792980298129822983298429852986298729882989299029912992299329942995299629972998299930003001300230033004300530063007300830093010301130123013301430153016301730183019302030213022302330243025302630273028302930303031303230333034303530363037303830393040304130423043304430453046304730483049305030513052305330543055305630573058305930603061306230633064306530663067306830693070307130723073307430753076307730783079308030813082308330843085308630873088308930903091309230933094309530963097309830993100310131023103310431053106310731083109311031113112311331143115311631173118311931203121312231233124312531263127312831293130313131323133313431353136313731383139314031413142314331443145314631473148314931503151315231533154315531563157315831593160316131623163316431653166316731683169317031713172317331743175317631773178317931803181318231833184318531863187318831893190319131923193319431953196319731983199320032013202320332043205320632073208320932103211321232133214321532163217321832193220322132223223322432253226322732283229323032313232323332343235323632373238323932403241324232433244324532463247324832493250325132523253325432553256325732583259326032613262326332643265326632673268326932703271327232733274327532763277327832793280328132823283328432853286328732883289329032913292329332943295329632973298329933003301330233033304330533063307330833093310331133123313331433153316331733183319332033213322332333243325332633273328332933303331333233333334333533363337333833393340334133423343334433453346334733483349335033513352335333543355335633573358335933603361336233633364336533663367336833693370337133723373337433753376337733783379338033813382338333843385338633873388338933903391339233933394339533963397339833993400340134023403340434053406340734083409341034113412341334143415341634173418341934203421342234233424342534263427342834293430343134323433343434353436343734383439344034413442344334443445344634473448344934503451345234533454345534563457345834593460346134623463346434653466346734683469347034713472347334743475347634773478347934803481348234833484348534863487348834893490349134923493349434953496349734983499350035013502350335043505350635073508350935103511351235133514351535163517351835193520352135223523352435253526352735283529353035313532353335343535353635373538353935403541354235433544354535463547354835493550355135523553355435553556355735583559356035613562356335643565356635673568356935703571357235733574357535763577357835793580358135823583358435853586358735883589359035913592359335943595359635973598359936003601360236033604360536063607360836093610361136123613361436153616361736183619362036213622362336243625362636273628362936303631363236333634363536363637363836393640364136423643364436453646364736483649365036513652365336543655365636573658365936603661366236633664366536663667366836693670367136723673367436753676367736783679368036813682368336843685368636873688368936903691369236933694369536963697369836993700370137023703370437053706370737083709371037113712371337143715371637173718371937203721372237233724372537263727372837293730373137323733373437353736373737383739374037413742374337443745374637473748374937503751375237533754375537563757375837593760376137623763376437653766376737683769377037713772377337743775377637773778377937803781378237833784378537863787378837893790379137923793379437953796379737983799380038013802380338043805380638073808380938103811381238133814381538163817381838193820382138223823382438253826382738283829383038313832383338343835383638373838383938403841384238433844384538463847384838493850385138523853385438553856385738583859386038613862386338643865386638673868386938703871387238733874387538763877387838793880388138823883388438853886388738883889389038913892389338943895389638973898389939003901390239033904390539063907390839093910391139123913391439153916391739183919392039213922392339243925392639273928392939303931393239333934393539363937393839393940394139423943394439453946394739483949395039513952395339543955395639573958395939603961396239633964396539663967396839693970397139723973397439753976397739783979398039813982398339843985398639873988398939903991399239933994399539963997399839994000400140024003400440054006400740084009401040114012401340144015401640174018401940204021402240234024402540264027402840294030403140324033403440354036403740384039404040414042404340444045404640474048404940504051405240534054405540564057405840594060406140624063406440654066406740684069407040714072407340744075407640774078407940804081408240834084408540864087408840894090409140924093409440954096409740984099410041014102410341044105410641074108410941104111411241134114411541164117411841194120412141224123412441254126412741284129413041314132413341344135413641374138413941404141414241434144414541464147414841494150415141524153415441554156415741584159416041614162416341644165416641674168416941704171417241734174417541764177417841794180418141824183418441854186418741884189419041914192419341944195419641974198419942004201420242034204420542064207420842094210421142124213421442154216421742184219422042214222422342244225422642274228422942304231423242334234423542364237423842394240424142424243424442454246424742484249425042514252425342544255425642574258425942604261426242634264426542664267426842694270427142724273427442754276427742784279428042814282428342844285428642874288428942904291429242934294429542964297429842994300430143024303430443054306430743084309431043114312431343144315431643174318431943204321432243234324432543264327432843294330433143324333433443354336433743384339434043414342434343444345434643474348434943504351435243534354435543564357435843594360436143624363436443654366436743684369437043714372437343744375437643774378437943804381438243834384438543864387438843894390439143924393439443954396439743984399440044014402440344044405440644074408440944104411441244134414441544164417441844194420442144224423442444254426442744284429443044314432443344344435443644374438443944404441444244434444444544464447444844494450445144524453445444554456445744584459446044614462446344644465446644674468446944704471447244734474447544764477447844794480448144824483448444854486448744884489449044914492449344944495449644974498449945004501450245034504450545064507450845094510451145124513451445154516451745184519452045214522452345244525452645274528452945304531453245334534453545364537453845394540454145424543454445454546454745484549455045514552455345544555455645574558455945604561456245634564456545664567456845694570457145724573457445754576457745784579458045814582458345844585458645874588458945904591459245934594459545964597459845994600460146024603460446054606460746084609461046114612461346144615461646174618461946204621462246234624462546264627462846294630463146324633463446354636463746384639464046414642464346444645464646474648464946504651465246534654465546564657465846594660466146624663466446654666466746684669467046714672467346744675467646774678467946804681468246834684468546864687468846894690469146924693469446954696469746984699470047014702470347044705470647074708470947104711471247134714471547164717471847194720472147224723472447254726472747284729473047314732473347344735473647374738473947404741474247434744474547464747474847494750475147524753475447554756475747584759476047614762476347644765476647674768476947704771477247734774477547764777477847794780478147824783478447854786478747884789479047914792479347944795479647974798479948004801480248034804480548064807480848094810481148124813481448154816481748184819482048214822482348244825482648274828482948304831483248334834483548364837483848394840484148424843484448454846484748484849485048514852485348544855485648574858485948604861486248634864486548664867486848694870487148724873487448754876487748784879488048814882488348844885488648874888488948904891489248934894489548964897489848994900490149024903490449054906490749084909491049114912491349144915491649174918491949204921492249234924492549264927492849294930493149324933493449354936493749384939494049414942494349444945494649474948494949504951495249534954495549564957495849594960496149624963496449654966496749684969497049714972497349744975497649774978497949804981498249834984498549864987498849894990499149924993499449954996499749984999500050015002500350045005500650075008500950105011501250135014501550165017501850195020502150225023502450255026502750285029503050315032503350345035503650375038503950405041504250435044504550465047504850495050505150525053505450555056505750585059506050615062506350645065506650675068506950705071507250735074507550765077507850795080508150825083508450855086508750885089509050915092509350945095509650975098509951005101510251035104510551065107510851095110511151125113511451155116511751185119512051215122512351245125512651275128512951305131513251335134513551365137513851395140514151425143514451455146514751485149515051515152515351545155515651575158515951605161516251635164516551665167516851695170517151725173517451755176517751785179518051815182518351845185518651875188518951905191519251935194519551965197519851995200520152025203520452055206520752085209521052115212521352145215521652175218521952205221522252235224522552265227522852295230523152325233523452355236523752385239524052415242524352445245524652475248524952505251525252535254525552565257525852595260526152625263526452655266526752685269527052715272527352745275527652775278527952805281528252835284528552865287528852895290529152925293529452955296529752985299530053015302530353045305530653075308530953105311531253135314531553165317531853195320532153225323532453255326532753285329533053315332533353345335533653375338533953405341534253435344534553465347534853495350535153525353535453555356535753585359536053615362536353645365536653675368536953705371537253735374537553765377537853795380538153825383538453855386538753885389539053915392539353945395539653975398539954005401540254035404540554065407540854095410541154125413541454155416541754185419542054215422542354245425542654275428542954305431543254335434543554365437543854395440544154425443544454455446544754485449545054515452545354545455545654575458545954605461546254635464546554665467546854695470547154725473547454755476547754785479548054815482548354845485548654875488548954905491549254935494549554965497549854995500550155025503550455055506550755085509551055115512551355145515551655175518551955205521552255235524552555265527552855295530553155325533553455355536553755385539554055415542554355445545554655475548554955505551555255535554555555565557555855595560556155625563556455655566556755685569557055715572557355745575557655775578557955805581558255835584558555865587558855895590559155925593559455955596559755985599560056015602560356045605560656075608560956105611561256135614561556165617561856195620562156225623562456255626562756285629563056315632563356345635563656375638563956405641564256435644564556465647564856495650565156525653565456555656565756585659566056615662566356645665566656675668566956705671567256735674567556765677567856795680568156825683568456855686568756885689569056915692569356945695569656975698569957005701570257035704570557065707570857095710571157125713571457155716571757185719572057215722572357245725572657275728572957305731573257335734573557365737573857395740574157425743574457455746574757485749575057515752575357545755575657575758575957605761576257635764576557665767576857695770577157725773577457755776577757785779578057815782578357845785578657875788578957905791579257935794579557965797579857995800580158025803580458055806580758085809581058115812581358145815581658175818581958205821582258235824582558265827582858295830583158325833583458355836583758385839584058415842584358445845584658475848584958505851585258535854585558565857585858595860586158625863586458655866586758685869587058715872587358745875587658775878587958805881588258835884588558865887588858895890589158925893589458955896589758985899590059015902590359045905590659075908590959105911591259135914 |
- USE [MainDB]
- GO
- /****** Object: UserDefinedFunction [dbo].[_51JobRegionToOurID] Script Date: 2018/12/13 18:28:19 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --sean 2013-6-25
- CREATE FUNCTION [dbo].[_51JobRegionToOurID]
- (
- @51JobRegin VARCHAR(50)
- )
- RETURNS VARCHAR(6)
- AS
- BEGIN
- DECLARE @Id VARCHAR(6), @Id2 VARCHAR(6), @Des VARCHAR(20)
- SELECT TOP 1 @id = ID, @Des = DESCRIPTION
- FROM dcRegion WHERE Description LIKE LEFT(@51JobRegin, 2) + '%'
- AND GRADE = 1
- ORDER BY ID
-
- IF @Id IS NULL
- SELECT TOP 1 @id = ID, @Des = DESCRIPTION
- FROM dcRegion WHERE Description LIKE LEFT(@51JobRegin, 2) + '%'
- AND GRADE = 2
- ORDER BY ID
- ELSE
- BEGIN
- SET @Des = REPLACE(@Des, '市', '')
- SET @Des = REPLACE(@Des, '省', '')
- SET @51JobRegin = REPLACE(@51JobRegin, @Des, '')
- SET @51JobRegin = REPLACE(@51JobRegin, '-', '')
- SET @51JobRegin = REPLACE(@51JobRegin, @Des, '')
- SET @51JobRegin = REPLACE(@51JobRegin, '-', '')
- SELECT TOP 1 @id2 = ID
- FROM dcRegion
- WHERE Description LIKE '%' + LEFT(@51JobRegin, 2) + '%'
- AND ID LIKE @Id + '%'
- ORDER BY Id
- END
- IF @Id2 > ''
- RETURN @ID2
- ELSE
- RETURN @ID
- RETURN ''
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[AgeToBirth] Script Date: 2018/12/13 18:28:19 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2011-2-25
- --年龄to出生年月,用于简历搜索
- --######################################3
- CREATE FUNCTION [dbo].[AgeToBirth]
- (
- @Age TINYINT
- )
- RETURNS INT
- AS
- BEGIN
- DECLARE @Year AS INT
- DECLARE @Month AS INT
- SET @Year = YEAR(GETDATE()) - @Age
- SET @Month = MONTH(GETDATE())
- RETURN @Year * 100 + @Month
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[BinToINT] Script Date: 2018/12/13 18:28:19 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[BinToINT]
- (
- @BinaryChar char(10)
- )
- RETURNS INT
- AS
- BEGIN
- DECLARE @stringLength int,@ReturnValue int,@Index int
- DECLARE @CurrentChar char(1)
- SET @Index = 0
- SET @ReturnValue = 0
- SET @stringLength = LEN(@BinaryChar)
- While @Index<@stringLength
- BEGIN
- SET @Index = @Index + 1
- SET @CurrentChar = SUBSTRING(@BinaryChar,@Index,1)
- IF(@CurrentChar='1' or @CurrentChar='0')
- BEGIN
- SET @ReturnValue = @ReturnValue + (CAST(@CurrentChar as int) * POWER(2,@stringLength - @Index))
- END
- END
- RETURN @ReturnValue
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[BirthToAge] Script Date: 2018/12/13 18:28:19 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2011-2-25
- --年龄to出生年月,用于简历搜索
- --######################################3
- CREATE FUNCTION [dbo].[BirthToAge]
- (
- @BirthDay INT
- )
- RETURNS INT
- AS
- BEGIN
- RETURN (CONVERT(VARCHAR(6), GETDATE(), 112) - @BirthDay) / 100
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[CheckCvPrivi] Script Date: 2018/12/13 18:28:19 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --修改:harry 2018-4-18
- --修改:对企业个人参加同一场校园招聘会,投递的非开放简历进行显示
- CREATE FUNCTION [dbo].[CheckCvPrivi]
- (
- @cvMainID INT,
- @cpMainID INT
- )
- RETURNS BIT
- BEGIN
- DECLARE @MemberType TINYINT
- SELECT @MemberType = MemberType
- FROM cpMain WITH(NOLOCK)
- WHERE ID = @cpMainID
-
- IF @MemberType < 2
- RETURN 0
-
- DECLARE @inActiveLog TINYINT, @inDailyGiftLog TINYINT, @inApplyPassed TINYINT, @paMainID INT
- SELECT @inActiveLog = 0, @inDailyGiftLog = 0, @inApplyPassed = 0
- IF EXISTS(SELECT TOP 1 'X'
- FROM caDailyGiftLog WITH(NOLOCK INDEX(PK_caDailyGiftLog))
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID
- )
- BEGIN
- SET @inDailyGiftLog = 1
- END
- IF @inDailyGiftLog = 0
- BEGIN
- SELECT @paMainId = paMainId FROM cvMain WITH(NOLOCK) WHERE ID = @cvMainID
- DECLARE @t AS TABLE(ID INT)
- INSERT INTO @t
- SELECT ID FROM cvMain WITH(NOLOCK) WHERE paMainId = @paMainID
- IF EXISTS(SELECT TOP 1 'X'
- FROM caActiveLog WITH(NOLOCK INDEX(IX_caActiveLog_cpMainID))
- WHERE cpMainID = @cpMainID
- AND cvMainID IN(SELECT ID FROM @t)
- )
- BEGIN
- SET @inActiveLog = 1
- END
- END
- IF @inDailyGiftLog = 0 AND @inActiveLog = 0
- BEGIN
- IF EXISTS(SELECT TOP 1 'X'
- FROM ExJobApply a WITH(NOLOCK), Job b
- WHERE a.JobID = b.ID
- AND a.IsPassed = 1
- AND a.cvMainID IN(SELECT ID FROM @t)
- AND b.cpMainID = @cpMainID
- )
- BEGIN
- SET @inApplyPassed = 1
- END
- END
- IF @inActiveLog + @inDailyGiftLog + @inApplyPassed = 0
- BEGIN
- --判断 个人给企业投递了简历,个人与企业都报名参加了同一场招聘会--harry2018-4-18
- IF EXISTS(SELECT TOP 1 'X'
- FROM ExJobApply a WITH(NOLOCK), Job b
- WHERE a.JobID = b.ID
- AND a.IsPassed = 0--非开放
- AND a.cvMainID IN(SELECT ID FROM @t)
- AND b.cpMainID = @cpMainID
- )
- BEGIN
- --是否同时参加了一场校园招聘会
- IF EXISTS( SELECT TOP 1 'x'
- FROM marketdb..rmcompany a, marketdb..rmperson b
- WHERE a.cpMainId =@cpMainID
- AND b.paMainId=@paMainID
- AND a.recruitmentId= b.recruitmentId)
- BEGIN
- RETURN 1
- END
- ELSE
- BEGIN
- RETURN 0
- END
- END
- ELSE
- BEGIN
- RETURN 0
- END
- END
-
- RETURN 1
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[CleanAddress] Script Date: 2018/12/13 18:28:19 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2011-2-25
- --年龄to出生年月,用于简历搜索
- --######################################3
- CREATE FUNCTION [dbo].[CleanAddress]
- (
- @Address NVARCHAR(100),
- @dcRegionId VARCHAR(6)
- )
- RETURNS NVARCHAR(100)
- AS
- BEGIN
- DECLARE @r VARCHAR(6), @A NVARCHAR(100), @l INT
- SELECT @a = @address
- SELECT @r = Description FROM dcRegion WHERE Id = LEFT(@dcRegionId, 2)
- SET @l = LEN(@r)
- IF(LEFT(@a, @l) = @r)
- SET @a = RIGHT(@a, LEN(@a) - @l)
- BEGIN
- IF RIGHT(LEFT(@a, @l), 1) NOT IN('路', '街', '道')
- IF(LEFT(@a, @l - 1) = LEFT(@r, @l - 1))
- SET @a = RIGHT(@a, LEN(@a) - @l + 1)
- END
- IF(LEN(@dcRegionId) > 2)
- BEGIN
- SELECT @r = Description FROM dcRegion WHERE Id = LEFT(@dcRegionId, 4)
- SET @l = LEN(@r)
- IF(LEFT(@a, @l) = @r)
- SET @a = RIGHT(@a, LEN(@a) - @l)
- ELSE IF(LEFT(@a, @l-1) = LEFT(@r, @l - 1))
- SET @a = RIGHT(@a, LEN(@a) - @l + 1)
- END
- IF(LEN(@dcRegionId) > 4)
- BEGIN
- SELECT @r = Description FROM dcRegion WHERE Id = LEFT(@dcRegionId, 6)
- SET @l = LEN(@r)
- IF(LEFT(@a, @l) = @r)
- SET @a = RIGHT(@a, LEN(@a) - @l)
- END
- RETURN @a
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ClearChinese] Script Date: 2018/12/13 18:28:19 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE function [dbo].[ClearChinese]
- (
- @str VARCHAR(MAX)
- )
- RETURNS VARCHAR(Max)
- AS
- BEGIN
- DECLARE @I AS INT, @str1 AS VARCHAR(MAX), @c AS NCHAR(1)
- SELECT @I = 1, @str1 = ''
- WHILE @I <= LEN(@str)
- BEGIN
- SELECT @c = SUBSTRING(@str, @i, 1)
- IF DATALENGTH(@c) = LEN(@c)
- SELECT @str1 = @str1 + @c
- SELECT @I = @I + 1
- END
- RETURN @str1
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[clearhtml] Script Date: 2018/12/13 18:28:19 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create function [dbo].[clearhtml]
- (
- @maco varchar(MAX)
- )
- returns varchar(MAX)
- as
- begin
- declare @randchar_one nvarchar(200)
- declare @randchar_two nvarchar(200)
- if(charindex('<<',@maco)>0)
- begin
- set @randchar_one='D4678B36-B958-4274-B81E-BBA636CFB427';
- set @randchar_two='49E374CC-9E1A-4850-897C-27074DE32E7F';
- set @maco=replace(@maco,'<<',@randchar_one)
- set @maco=replace(@maco,'>>',@randchar_two)
- end
- declare @i int
- while 1 = 1
- begin
- set @i=len(@maco)
- set @maco=replace(@maco, substring(@maco,charindex('<',@maco),
- charindex('>',@maco)-charindex('<',@maco)+1),space(0))
- if @i=len( @maco )
- break
- end
-
- set @maco=replace(@maco,' ','')
- set @maco=replace(@maco,' ','')
- set @maco=ltrim(rtrim(@maco))
- set @maco=replace(@maco,char(9),'')
- set @maco=replace(@maco,char(10),'')
- set @maco=replace(@maco,char(13),'')
- if(charindex(@randchar_one,@maco)>0)
- begin
- set @maco=replace(@maco,'D4678B36-B958-4274-B81E-BBA636CFB427','<<')
- set @maco=replace(@maco,'49E374CC-9E1A-4850-897C-27074DE32E7F','>>')
- end
- return (@maco)
- end
- GO
- /****** Object: UserDefinedFunction [dbo].[ClearHTMLTag] Script Date: 2018/12/13 18:28:20 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[ClearHTMLTag]
- (
- @Cont VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @Txt VARCHAR(MAX), @I INT, @J INT
- SET @Txt = REPLACE(ISNULL(@Cont, ''), ' ', ' ')
- SELECT @i = CHARINDEX('<', @Txt, 0), @j = CHARINDEX('>', @Txt, 1)
- WHILE @i < @j
- BEGIN
- SELECT @Txt = REPLACE(@Txt, SUBSTRING(@txt, @i, @j - @i + 1), '')
- SELECT @i = CHARINDEX('<', @Txt, 0), @j = CHARINDEX('>', @Txt, 1)
- END
- RETURN @Txt
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[CompanyNameNoCheck] Script Date: 2018/12/13 18:28:20 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --信息员推广企业是,企业名称免检
- --######################################################3
- CREATE FUNCTION [dbo].[CompanyNameNoCheck]
- (
- @CompanyName NVARCHAR(50)
- )
- RETURNS BIT
- AS
- BEGIN
- IF @CompanyName LIKE '%香港%'
- RETURN 1
- IF @CompanyName LIKE '%台湾%'
- RETURN 1
- IF @CompanyName LIKE '%澳门%'
- RETURN 1
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ComputeCvLevel] Script Date: 2018/12/13 18:28:20 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[ComputeCvLevel]
- (
- @cvLevel CHAR(10),
- @Pos TINYINT,
- @Val CHAR(1)
- )
- RETURNS CHAR(10)
- AS
- BEGIN
- DECLARE @Level AS CHAR(10)
- SET @Level = LEFT(@cvLevel, @Pos - 1) + @Val + RIGHT(@cvLevel,10 - @Pos)
- --IF LEFT(@cvLevel, 1) <> '1'
- -- SET @Level = '1' + RIGHT(@Level, 9)
- RETURN @Level
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ComputeJobValid] Script Date: 2018/12/13 18:28:20 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- -- =============================================
- -- Author: <Author,,Name>
- -- Create date: <Create Date,,>
- -- Description: <Description,,>
- -- =============================================
- CREATE FUNCTION [dbo].[ComputeJobValid]
- (
- @IsDelete BIT,
- @VerifyResult TINYINT,
- @IssueDate SMALLDATETIME,
- @IssueEnd SMALLDATETIME
- )
- RETURNS BIT
- AS
- BEGIN
- IF @IsDelete = 1
- RETURN 0
-
- IF @VerifyResult<>1
- RETURN 0
- IF NOT(@IssueDate<GETDATE() AND @IssueEnd>GETDATE())
- RETURN 0
- RETURN 1
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[CutWord] Script Date: 2018/12/13 18:28:20 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE Function [dbo].[CutWord]
- (
- @InWord NVARCHAR(20)
- )
- RETURNS NVARCHAR(20)
- AS
- BEGIN
- IF CHARINDEX('select', @InWord) > 0
- RETURN ''
- IF CHARINDEX('script', @InWord) > 0
- RETURN ''
- IF CHARINDEX('delete', @InWord) > 0
- RETURN ''
- IF CHARINDEX('update', @InWord) > 0
- RETURN ''
- IF CHARINDEX('drop', @InWord) > 0
- RETURN ''
- IF LEN(@InWord) > 4
- IF EXISTS(SELECT 'x' FROM cpMainPublish WITH(NOLOCK) WHERE Name = @InWord)
- RETURN @InWord
- DECLARE @OutWord NVARCHAR(20), @Word NVARCHAR(20), @Word1 NVARCHAR(20), @Word2 NVARCHAR(20), @Word3 NVARCHAR(20), @Id INT, @WordType INT
- SELECT @OutWord = ' ', @Id = 0
- DECLARE @t AS TABLE(Id INT IDENTITY(1, 1), Word NVARCHAR(20))
- INSERT INTO @t
- SELECT * FROM dbo.FnSplit(@InWord, ' ')
- WHILE EXISTS(SELECT 'x' FROM @t WHERE Id > @Id )
- BEGIN
- SELEcT TOP 1 @Id = Id, @Word = Word FROM @t WHERE Id > @Id ORDER BY Id
- IF EXISTS(SELECT 'x' FROM @t WHERE Id < @Id AND WOrd = @Word)
- CONTINUE
- IF LEN(@Word) < 4
- BEGIN
- SET @OutWord = @OutWord + ' ' + @Word
- CONTINUE
- END
- IF EXISTS(SELECT 'x' FROM WordList WHERE Word = @Word)
- SET @OutWord = @OutWord + ' ' + @Word
- ELSE
- BEGIN
- SET @Word1 = ''
- SELECT TOP 1 @WordType = WordType, @Word1 = Word FROM WordList WHERE CHARINDEX(Word, @Word) = 1 ORDER BY WordType, ID
- IF @Word1 > ''
- BEGIN
- SET @Word2 = RIGHT(@Word, LEN(@Word) - LEN(@Word1))
- IF @WordType = 1
- BEGIN
- SET @WordType = 0
- SELECT TOP 1 @WordType = WordType FROM WordList WHERE Word = @Word2
- IF @WordType = 1
- SET @OutWord = @OutWord + ' ' + @Word
- ELSE
- IF @WordType = 0
- BEGIN
- SELECT TOP 1 @WordType = WordType, @Word3 = Word
- FROM WordList WHERE CHARINDEX(Word, @Word2) = 1 ORDER BY WordType DESC, ID
- IF @WordType < 2
- SET @OutWord = @OutWord + ' ' + @Word
- ELSE
- SET @OutWord = @OutWord + ' ' + @Word1 + ' ' + @Word3 + ' ' + REPLACE(@Word2, @Word3, '')
- END
- ELSE
- SET @OutWord = @OutWord + ' ' + @Word1 + ' ' + @Word2
- END
- ELSE IF @WordType > 1
- BEGIN
- IF LEN(@Word) - LEN(@Word1) < 2
- SET @OutWord = @OutWord + ' ' + @Word
- ELSE
- SET @OutWord = @OutWord + ' ' + @Word1 + ' ' + RIGHT(@Word, LEN(@Word) - LEN(@Word1))
- END
- ELSE
- SET @OutWord = @OutWord + ' ' + @Word
- END
- ELSE
- SET @OutWord = @OutWord + ' ' + @Word
- END
- END
- RETURN LTRIM(@OutWord)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[cvMainValid] Script Date: 2018/12/13 18:28:20 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[cvMainValid]
- (
- @ID INT
- )
- RETURNS TINYINT
- AS
- BEGIN
- DECLARE @cvValid AS TINYINT
- SELECT @cvValid = (
- case [IsCvHidden]
- when (1) then (0)
- else
- case left([cvLevel],(6))+rtrim(CONVERT([char](2),[VerifyResult],(0)))
- when '1111111' then
- case left([cvLevelEng],(6))+rtrim(CONVERT([char](2),[VerifyResultEng],(0)))
- when '1111111' then (2)
- when '1111011' then (2)
- else (1)
- end
- when '1111011' then
- case left([cvLevelEng],(6))+rtrim(CONVERT([char](2),[VerifyResultEng],(0)))
- when '1111111' then (2)
- when '1111011' then (2)
- else (1)
- end
- else
- case left([cvLevelEng],(6))+rtrim(CONVERT([char](2),[VerifyResultEng],(0)))
- when '1111111' then (3)
- when '1111011' then (3)
- else (0)
- end
- end
- end)
- FROM cvMain WITH(NOLOCK) WHERE ID=@ID
- RETURN @cvValid
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[DateTo] Script Date: 2018/12/13 18:28:20 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Richard 2011-4-27
- --日期转换
- --######################################3
- create FUNCTION [dbo].[DateTo]
- (
- @d INT
- )
- RETURNS INT
- AS
- BEGIN
- RETURN ((@d) / 60)*100+((@d) % 60)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[DateToReportDay] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Lambo 2013-2-4
- --日期转换 将日期转换成天数 2012-1-1为1
- --######################################3
- CREATE FUNCTION [dbo].[DateToReportDay]
- (
- @d SMALLDATETIME
- )
- RETURNS INT
- AS
- BEGIN
- RETURN DATEDIFF(DAY,'2012-1-1',@d)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[DeCrypt] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --实现用户名解密的函数
- --######################################################3
- CREATE FUNCTION [dbo].[DeCrypt]
- (
- @strEncrypted VARCHAR(50)
- )
- RETURNS VARCHAR(50)
- AS
- BEGIN
- IF @strEncrypted IS NULL
- RETURN ''
- SET @strEncrypted = LTRIM(RTRIM(@strEncrypted))
- DECLARE @GKey AS CHAR(69)
- DECLARE @I AS INT
- DECLARE @iC AS INT
- DECLARE @iK AS INT
- DECLARE @iD AS INT
- DECLARE @strDecrypted AS VARCHAR(50)
- SET @strDecrypted = ''
- SET @I = 1
- SET @GKey = Char(1) + Char(3) + Char(2) + Char(4) + Char(2) + Char(4) + Char(1) + Char(3) + Char(2) + Char(4) + Char(1) + Char(3) + Char(2) + Char(4) + Char(3) + Char(1) + Char(4) + Char(2) + Char(3) + Char(1) + Char(2) + Char(2) + Char(4) + Char(1) + Char(4) + Char(1) + Char(4) + Char(3) + Char(2) + Char(3) + Char(2) + Char(4) + Char(1) + Char(4) + Char(2) + Char(3) + Char(4) + Char(1) + Char(3) + Char(1) + Char(4) + Char(2) + Char(4) + Char(4) + Char(2) + Char(3) + Char(1) + Char(3) + Char(2) + Char(4) + Char(3) + Char(1) + Char(4) + Char(2) + Char(4) + Char(1) + Char(3) + Char(2) + Char(4) + Char(1) + Char(3) + Char(2) + Char(4) + Char(3) + Char(1) + Char(4) + Char(1) + Char(3) + Char(4)
-
- WHILE @I <= LEN(@strEncrypted)
- BEGIN
- SET @iC = ASCII(SUBSTRING(@strEncrypted, @I, 1))
- SET @iK = ASCII(SUBSTRING(@GKey, @I, 1))
- SET @iD = @iK ^ @iC
- SET @strDecrypted = @strDecrypted + CHAR(@iD)
- SET @I = @I + 1
- END
- RETURN @strDecrypted
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ExistUserName] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2010-12-2
- --推广时企业用户名是否可用
- --######################################3
- CREATE FUNCTION [dbo].[ExistUserName]
- (
- @UserName VARCHAR(50)
- )
- RETURNS BIT
- AS
- BEGIN
- IF EXISTS(SELECT 'x' FROM caMain WITH(NOLOCK) WHERE UserNameLower = @UserName)
- RETURN 1
-
- IF EXISTS(SELECT 'x' FROM Company_xinxi WITH(NOLOCK) WHERE UserNameLower = @UserName)
- RETURN 1
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[f_add] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE function [dbo].[f_add](@date datetime,@i int)
- returns datetime
- as
- begin
- declare @rt datetime
- set @rt=@date
- while @i>0
- begin
- set @rt=dateadd(d,1,@rt)
- if datepart(w,@rt) in(1,7) set @rt=dateadd(d,1,@rt) --7为周六,1为周日
- if datepart(w,@rt) in(1,7) set @rt=dateadd(d,1,@rt) --7为周六,1为周日
- set @i=@i-1
- end
- return @rt
- end
- GO
- /****** Object: UserDefinedFunction [dbo].[f_Convert] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[f_Convert]
- (
- @str NVARCHAR(4000), --要转换的字符串
- @flag bit --转换标志, 0转换成半角, 1转换成全角
- )
- RETURNS nvarchar(4000)
- AS
- BEGIN
- DECLARE @pat nvarchar(8), @step int, @i int, @spc int
- IF @flag = 0
- SELECT @pat = N'%[!-~]%', @step = -65248,
- @str = REPLACE(@str, N' ', N' ')
- ELSE
- SELECT @pat = N'%[!-~]%', @step = 65248,
- @str = REPLACE(@str, N' ', N' ')
- SET @i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str)
- WHILE @i > 0
- SELECT @str = REPLACE(@str,
- SUBSTRING(@str, @i, 1),
- NCHAR(UNICODE(SUBSTRING(@str, @i, 1))+@step)),
- @i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str)
- RETURN(@str)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[FnctManageruserSuperior] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create function [dbo].[FnctManageruserSuperior](@sender int,@receiver int)
- returns int
- as
- begin
- declare @superior int,@r1 int,@r2 int,@r3 int,@s1 int,@s2 int,@s3 int
- select @s1=superior from manageruser with(nolock) where id=@sender
- select @r1=superior from manageruser with(nolock) where id=@receiver
- select @s2=superior from manageruser with(nolock) where id=@s1
- select @r2=superior from manageruser with(nolock) where id=@r1
- select @s3=superior from manageruser with(nolock) where id=@s2
- select @r3=superior from manageruser with(nolock) where id=@r2
- if @s1=@r1
- set @superior=@s1
- else if @s1=@r2
- set @superior=@s1
- else if @s2=@r1
- set @superior=@r1
- else if @s1=@r3
- set @superior=@s1
- else if @r1=@s3
- set @superior=@r1
- else if @r2=@s2
- set @superior=@s2
- else if @r2=@s3
- set @superior=@r2
- else if @s2=@r3
- set @superior=@s2
- else if @r3=@s3
- set @superior=@s3
- else
- set @superior=1
- return @superior
- end
- GO
- /****** Object: UserDefinedFunction [dbo].[fnRanString] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE function [dbo].[fnRanString]
- (
- @Length int
- )
- returns varchar(100)
- begin
- DECLARE @RandomID varchar(32), @counter smallint, @RandomNumber float, @ValidCharactersLength int,
- @RandomNumberInt tinyint, @CurrentCharacter varchar(1), @ValidCharacters varchar(255)
- SET @ValidCharacters = 'abcdefghijklmnopqrstuvwxyz0123456789'
- SET @ValidCharactersLength = len(@ValidCharacters)
- SELECT @CurrentCharacter = '', @RandomNumber = 0, @RandomNumberInt = 0, @RandomID = '', @counter = 1
- WHILE @counter < (@Length + 1)
- BEGIN
- select @RandomNumber = re from v_rand
- SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength -1) * @RandomNumber + 1));
- SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1);
- SET @counter = @counter + 1;
- SET @RandomID = @RandomID + @CurrentCharacter;
- END
- Return @RandomID;
- end
- GO
- /****** Object: UserDefinedFunction [dbo].[FormatID] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#############################333
- --格式化id,去掉所有不是数字的字符
- --#############################333
- CREATE FUNCTION [dbo].[FormatID]
- (
- @s VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- IF @s IS NULL
- RETURN ''
-
- DECLARE @i AS INT
- DECLARE @c AS CHAR(1)
- DECLARE @R AS VARCHAR(MAX)
- SET @R = ''
- SET @i = 0
- WHILE @i < LEN(@s)
- BEGIN
- IF SUBSTRING(@s, @i + 1, 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
- SET @R = @R + SUBSTRING(@s, @i + 1, 1)
- SET @i = @i +1
- END
- RETURN @R
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[FormatIDS] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#############################333
- --格式化id,去掉所有不是数字和,的字符
- --#############################333
- create FUNCTION [dbo].[FormatIDS]
- (
- @s VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- IF @s IS NULL
- RETURN ''
-
- DECLARE @i AS INT
- DECLARE @c AS CHAR(1)
- DECLARE @R AS VARCHAR(MAX)
- SET @R = ''
- SET @i = 0
- WHILE @i < LEN(@s)
- BEGIN
- IF SUBSTRING(@s, @i + 1, 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', ',')
- SET @R = @R + SUBSTRING(@s, @i + 1, 1)
- SET @i = @i +1
- END
- RETURN @R
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[FormatMobile] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[FormatMobile]
- (
- @str AS VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str, '-', '-'), '0', '0'), '1', '0'), '2', '2'), '3', '3'), '4', '4'), '5', '5'), '6', '6'), '7', '7'), '8', '8'), '9', '9'), '(', '('), ')', ')'), '——', '-'), '—', '-'),',', ','), '、', ',')
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[Full2Half] Script Date: 2018/12/13 18:28:21 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[Full2Half]
- (
- @str NVARCHAR(4000), --要转换的字符串
- @flag bit --转换标志,0转换成半角,1转换成全角
- )
- RETURNS nvarchar(4000)
- AS
- BEGIN
- DECLARE @pat nvarchar(8), @step int, @i int,@spc int
- IF @flag = 0
- SELECT @pat = N'%[!-~]%', @step = -65248,
- @str = REPLACE(@str, N' ', N' ')
- ELSE
- SELECT @pat = N'%[!-~]%', @step = 65248,
- @str = REPLACE(@str, N' ', N' ')
- SET @i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str)
- WHILE @i > 0
- SELECT @str = REPLACE(@str,
- SUBSTRING(@str, @i, 1),
- NCHAR(UNICODE(SUBSTRING(@str, @i, 1)) + @step)),
- @i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str)
- RETURN(@str)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[fullRegionName] Script Date: 2018/12/13 18:28:22 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[fullRegionName]
- (
- @Id VARCHAR(6)
- )
- RETURNS NVARCHAR(20)
- BEGIN
- RETURN (SELECT FullName FROM dcRegion WITH(NOLOCK) WHERE ID = @ID)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetBeiSenCvText] Script Date: 2018/12/13 18:28:22 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetBeiSenCvText]
- (
- @cvMainId INT
- )
- RETURNS NVARCHAR(MAX)
- AS
- BEGIN
- DECLARE @paName NVARCHAR(10), @Sex NVARCHAR(2), @BirthDay NVARCHAR(20), @LivePlace NVARCHAR(30), @Mobile VARCHAR(20),
- @Email VARCHAR(100), @Career NVARCHAR(30), @WorkYear NVARCHAR(10), @Salary VARCHAR(50), @JobType VARCHAR(100),
- @Industry VARCHAR(100), @WorkPlace VARCHAR(100), @Speciality NVARCHAR(500)
- SELECT @paName = ISNULL(a.Name, ''), @Sex = CASE WHEN Gender = 0 THEN '男' ELSE '女' END, @BirthDay = ISNULL(BirthDay, ''),
- @LivePlace = ISNULL((SELECT FullName FROM dcRegion WHERE Id = LivePlace), ''),
- @Mobile = ISNULL(Mobile, ''), @Email = Email,
- @Career = ISNULL((SELECT Description FROM dcCareerStatus WHERE Id = dcCareerStatus), 1),
- @WorkYear = LTRIM(STR(ISNULL(RelatedWorkYears, 0))) + '年',
- @Salary = ISNULL((SELECT Description FROM dcSalary WHERE id = dcSalaryId), ''),
- @Speciality = ISNULL(Speciality, '')
- FROM paMain a WITH(NOLOCK), cvMain b WITH(NOLOCK)
- WHERE b.Id = @cvMainId
- AND b.paMainId = a.Id
-
- IF LEN(@BirthDay) = 6
- SET @BirthDay = LEFT(@BirthDay, 4) + '年' + RIGHT(@BirthDay, 2) + '月'
- SELECT @Industry = ISNULL(@Industry, '') + DESCRIPTION + ' '
- FROM dcIndustry a, cvIndustry b
- WHERE a.Id = b.dcIndustryId
- AND b.cvMainId = @cvMainID
- SELECT @WorkPlace = ISNULL(@WorkPlace, '') + DESCRIPTION + ' '
- FROM dcRegion a, cvJobPlace b
- WHERE a.Id = b.dcRegionId
- AND b.cvMainId = @cvMainID
- SELECT @JobType = ISNULL(@JobType, '') + DESCRIPTION + ' '
- FROM dcJobtype a, cvJobType b
- WHERE a.Id = b.dcJobTypeId
- AND b.cvMainId = @cvMainID
- DECLARE @CvText NVARCHAR(MAX)
- SET @CvText =
- '<html xmlns="http://www.w3.org/1999/xhtml">
- <head>
- <title>个人简历</title>
- <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
- <style>
- tr{height:22px;}
- td{font-size:10.0pt;padding-left:2px;padding-right:2px}
- body{tab-interval:21.0pt}
- dd{margin:0; padding:0; float:left; clear:both;}dl{margin:0; padding:0;}
- </style>
- </head>
- <body>
- <h3>个人信息</h3>
- <table cellpadding="0" cellspacing="0" border="1" width="658" >
- <tr>
- <td><label>姓名:</label><span>' + ISNULL(@paName, '') + '</span></td>
- <td><label>性别:</label><span>' + ISNULL(@Sex, '') + '</span></td>
- </tr>
- <tr>
- <td><label>出生年月:</label><span>' + ISNULL(@BirthDay, '') + '</span></td>
- <td><label>现居住地:</label><span>' + ISNULL(@LivePlace, '') + '</span></td>
- </tr>
- <tr>
- <td><label>手机号:</label><span>' + ISNULL(@Mobile, '') + '</span></td>
- <td><label>电子邮箱:</label><span>' + ISNULL(@Email, '') + '</span></td>
- </tr>
- </table>'
- IF @Salary > '' AND @jobType > ''
- SET @cvText = @cvText + '
- <h3>求职意向</h3>
- <table cellpadding="0" cellspacing="0" border="1" width="658" >
- <tr>
- <td><label>求职状态:</label><span>' + ISNULL(@Career, '') + '</span></td>
- <td><label>相关工作经验:</label><span>' + ISNULL(@WorkYear, '') + '</span></td>
- </tr>
- <tr>
- <td><label>期望月薪:</label><span>' + ISNULL(@Salary, '') + '</span></td>
- <td><label>期望职位类别:</label><span>' + ISNULL(@jobType, '') + '</span></td>
- </tr>
- <tr>
- <td><label>期望工作地点:</label><span>' + ISNULL(@WorkPlace, '') + '</span></td>
- <td colspan="2"><label>期望从事行业:</label><span>' + ISNULL(@Industry, '') + '</span></td>
- </tr>
- </table>'
- -----------------------------------
- DECLARE @GraduateCollage AS VARCHAR(100), @EduDetail AS NVARCHAR(500), @Graduation AS VARCHAR(20),
- @Degree VARCHAR(10), @MajorName NVARCHAR(50)
- SELECT TOP 1 @GraduateCollage = ISNULL(GraduateCollage, ''),
- @EduDetail = ISNULL(LEFT(Details, 500), ''),
- @Graduation = ISNULL(Graduation, ''),
- @Degree = ISNULL((SELECT Description FROM dcEducation WHERE id = Degree), ''),
- @MajorName = ISNULL(MajorName, '')
- FROM cvEducation WHERE cvMainId = @cvMainId ORDER BY Degree DESC, Graduation DESC
- IF LEN(@Graduation) = 6
- SET @Graduation = LEFT(@Graduation, 4) + '年' + RIGHT(@Graduation, 2) + '月'
- SET @cvText = @cvText + '
- <h3>教育背景</h3>
- <table cellpadding="0" cellspacing="0" border="1" width="658" >
- <tr>
- <td><label>学校名称:</label><span>' + ISNULL(@GraduateCollage, '') + '</span></td>
- <td><label>毕业时间:</label><span>' + ISNULL(@Graduation, '') + '</span></td>
- </tr>
- <tr>
- <td><label>学历:</label><span>' + ISNULL(@Degree, '') + '</span></td>
- <td><label>专业:</label><span>' + ISNULL(@MajorName, '') + '</span></td>
- </tr>'
- IF LEN(ISNULL(@EduDetail, '')) > 0
- SET @cvText = @cvText + '
- <tr>
- <td colspan="2"><label>学习经历:</label><span>' + @EduDetail + '</span></td>
- </tr>'
- SET @cvText = @cvText + '
- </table>'
- --------------------------------------------------
- DECLARE @CompanyName AS VARCHAR(100), @companyIndustry AS VARCHAR(50), @CompanyPost AS VARCHAR(50), @BeginDate VARCHAR(10),
- @EndDate VARCHAR(10), @CompanyDesc NVARCHAR(500)
- SELECT TOP 1 @CompanyName = ISNULL(CompanyName, ''),
- @companyIndustry = ISNULL((SELECT Description FROM dcIndustry WHERE Id = dcIndustryId), ''),
- @CompanyPost = ISNULL(JobName, ''),
- @BeginDate = ISNULL(BeginDate, ''),
- @EndDate = ISNULL(EndDate, ''),
- @CompanyDesc = ISNULL(LEFT(Description, 500), '')
- FROM cvExperience WHERE cvMainId = @cvMainId ORDER BY BeginDate DESC, EndDate DESC
- IF LEN(@BeginDate) = 6 AND LEN(@EndDate) = 6
- BEGIN
- SET @cvText = @cvText + '
- <h3>工作经历</h3>
- <table cellpadding="0" cellspacing="0" border="1" width="658" >
- <tr>
- <td><label>企业名称:</label><span>' + ISNULL(@CompanyName, '') + '</span></td>
- <td><label>所属行业:</label><span>' + ISNULL(@companyIndustry, '') + '</span></td>
- </tr>
- <tr>
- <td><label>岗位名称:</label><span>' + ISNULL(@CompanyPost, '') + '</span></td>
- <td><label>工作时间:</label><span>' + LEFT(@BeginDate, 4) + '年' + RIGHT(@BeginDate, 2) + '月至' + CASE WHEN @EndDate = 999999 THEN '今' ELSE LEFT(@EndDate, 4) + '年' + RIGHT(@EndDate, 2) + '月' END + '</span></td>
- </tr>'
- IF @CompanyDesc > ''
- SET @cvText = @cvText + '
- <tr>
- <td colspan="2"><label>工作描述:</label><span>' + @CompanyDesc + '</span></td>
- </tr>'
- SET @cvText = @cvText + '
- </table>'
- END
- IF @Speciality > ''
- SET @cvTExt = @cvText + '
- <h3>工作能力</h3>
- <table cellpadding="0" cellspacing="0" border="1" width="658" >
- <tr>
- <td colspan="2"><label>工作描述:</label><span>' + @Speciality + '</span></td>
- </tr>
- </table>'
- SET @CvText = @CvText + '
- </body>
- </html>'
- RETURN @cvText
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetBit] Script Date: 2018/12/13 18:28:22 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create FUNCTION [dbo].[GetBit](@val INT, @pos INT)
- RETURNS BIT
- AS
- BEGIN
- RETURN (@Val / POWER(2, @pos - 1)) & 1
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetCaName] Script Date: 2018/12/13 18:28:22 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetCaName]
- (
- @caMainID INT
- )
- RETURNS VARCHAR(50)
- AS
- BEGIN
- DECLARE @Name VARCHAR(20)
- DECLARE @Gender BIT
- DECLARE @IsNameHide BIT
- SELECT @Name = Name, @Gender = Gender, @IsNameHide = IsNameHide
- FROM caMain WHERE ID = @caMainID
- IF @IsNameHide = 1
- BEGIN
- IF @Gender = 1
- SELECT @Name = LEFT(@Name,1) + '女士'
- ELSE
- SELECT @Name = LEFT(@Name,1) + '先生'
- END
- RETURN @Name
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetCaOnlineStatus] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetCaOnlineStatus]
- (
- @caMainID INT
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @IsOnline AS BIT
- SELECT @IsOnline = IsOnline FROM caOnline WITH(NOLOCK) WHERE caMainID = @caMainID
- -- IF EXISTS(SELECT 'X' FROM caOnline WHERE caMainID = @caMainID AND (DATEDIFF(s, RefreshDate, GETDATE()) < 120))
- -- SELECT @IsOnline = 1
- RETURN ISNULL(@IsOnline, 0)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetChatPrivi] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetChatPrivi]
- (
- @cvMainID INT,
- @cpMainID INT
- )
- RETURNS BIT
- AS
- BEGIN
- IF EXISTS(
- SELECT TOP 1 'X' FROM ChatOnline WITH(NOLOCK) WHERE caMainID IN(
- SELECT ID FROM caMain WITH(NOLOCK) WHERE cpMainID = @cpMainID
- ) AND cvMainID = @cvMainID AND Initiative = 1
- )
- BEGIN
- IF EXISTS(SELECT TOP 1 'X' FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID AND MemberType < 2)
- RETURN 0
- ELSE
- RETURN 1
- END
- RETURN dbo.CheckCvPrivi(@cvMainID, @cpMainID)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetContactPrivi] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create FUNCTION [dbo].[GetContactPrivi]
- (
- @caMainID INT,
- @cvMainID INT
- )
- RETURNS TINYINT --0无权限; 1显示手机号,email; 2显示手机号;3、显示手机号、email
- AS
- BEGIN
- DECLARE @cpMainID AS INT,
- @MemberType AS TINYINT,
- @Valid AS TINYINT,
- @VerifyResult AS TINYINT,
- @VerifyResultEng AS TINYINT,
- @IsPassed AS BIT,
- @IsCvHidden AS BIT
- SELECT @cpMainID = cpMainID FROM caMain WITH(NOLOCK) WHERE ID = @caMainId
- SELECT @MemberType = MemberType FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID
- SELECT @Valid = Valid, @VerifyResult = VerifyResult, @VerifyResultEng = VerifyResultEng, @IsCvHidden = IsCvHidden
- FROM cvMain WITH(NOLOCK) WHERE ID = @cvMainID
- ----有问题
- IF @VerifyResult = 10 OR @VerifyResultEng = 10
- RETURN 0
- ----未认证会员
- IF @MemberType IN(0, 1)
- RETURN 0
- ----普通会员
- IF @MemberType IN(2, 3)
- BEGIN
- IF EXISTS(SELECT 'x' FROM exJobApply WITH(NOLOCK)
- WHERE cvMainID = @cvMainID AND IsPassed = 1
- AND JobID IN(SELECT ID FROM Job WITH(NOLOCK) WHERE cpMainID = @cpMainID))
- RETURN 1
- IF EXISTS(SELECT 'x' FROM caActiveLog WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID)
- RETURN 1
- IF EXISTS(SELECT 'x' FROM caDailyGiftLog WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID)
- RETURN 1
- RETURN 0
- END
- ----正式会员
- IF @MemberType = 10
- BEGIN
- IF EXISTS(SELECT 'x' FROM exJobApply WITH(NOLOCK)
- WHERE cvMainID = @cvMainID AND IsPassed = 1
- AND JobID IN(SELECT ID FROM Job WITH(NOLOCK) WHERE cpMainID = @cpMainID))
- RETURN 3
- IF @IsCvHidden = 1
- RETURN 0
- RETURN 2
- END
- ----储值会员
- IF @MemberType = 11
- BEGIN
- IF EXISTS(SELECT 'x' FROM caCvQuotaLog WITH(NOLOCK)
- WHERE cvMainID = @cvMainID
- AND cpMainID = @cpMainID)
- RETURN 3
- IF @IsCvHidden = 1
- RETURN 0
- RETURN 2
- END
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetCpIndustry] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetCpIndustry]
- (
- @cpMainID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @strIndustry NVARCHAR(100)
- SET @strIndustry = ''
- SELECT @strIndustry = @strIndustry + RTRIM(LTRIM(b.Description)) + ',' FROM cpIndustry a WITH(NOLOCK),dcIndustry b WITH(NOLOCK) WHERE a.dcIndustryID=b.ID AND a.cpMainID=@cpMainID
- IF LEN(@strIndustry) > 0
- SET @strIndustry = LEFT(@strIndustry,LEN(@strIndustry)-1)
- RETURN @strIndustry
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetCpIndustryId] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetCpIndustryId]
- (
- @cpMainID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @strIndustry NVARCHAR(100)
- SET @strIndustry = ''
- SELECT @strIndustry = @strIndustry + RTRIM(LTRIM(dcIndustryID)) + ',' FROM cpIndustry WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- IF LEN(@strIndustry) > 0
- SET @strIndustry = LEFT(@strIndustry, LEN(@strIndustry)-1)
- RETURN @strIndustry
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetCpLogo] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetCpLogo]
- (
- @cpMainID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @strLogoPath NVARCHAR(1000)
- SELECT @strLogoPath = ISNULL(LogoFile, '')
- FROM cpMain WITH(NOLOCK)
- WHERE ID = @cpMainID
- RETURN @strLogoPath
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetCvJobType] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetCvJobType]
- (
- @cvMainID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @strJobType NVARCHAR(100)
- SET @strJobType = ''
- SELECT @strJobType = @strJobType + RTRIM(LTRIM(b.Description)) + ',' FROM cvJobType a,dcJobType b WHERE a.dcJobTypeID=b.ID AND a.cvMainID=@cvMainID
- IF LEN(@strJobType) > 0
- SET @strJobType = LEFT(@strJobType,LEN(@strJobType)-1)
- RETURN @strJobType
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetCvMatch] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Lambo 20120329
- --计算简历和职位的匹配度
- --######################################3
- CREATE FUNCTION [dbo].[GetCvMatch]
- (
- @cvMainID INT,
- @JobID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @JobTypeMatch AS DECIMAL(18,2) --职位类别的匹配度
- DECLARE @RegionMatch AS DECIMAL(18,2) --办公室地点的匹配度
- DECLARE @ExperienceMatch AS DECIMAL(18,2) --相关工作经验的匹配度
- DECLARE @SalaryMatch AS DECIMAL(18,2) --月薪的匹配度
- DECLARE @EducationMatch AS DECIMAL(18,2) --学历的匹配度
- DECLARE @IndustryMatch AS DECIMAL(18,2) --行业的匹配度
- DECLARE @AgeMatch AS DECIMAL(18,2) --年龄的匹配度
- SET @JobTypeMatch = 0
- SET @RegionMatch = 0
- SET @ExperienceMatch = 0
- SET @SalaryMatch = 0
- SET @EducationMatch = 0
- SET @IndustryMatch = 0
- SET @AgeMatch = 0
-
- DECLARE @JobTypeID AS INT --职位的主要职位类别
- DECLARE @dcRegionID AS INT --办公室地点
- DECLARE @cpIndustry AS VARCHAR(100) --企业所属行业
- DECLARE @MinExperience INT --相关工作经验
- DECLARE @dcSalaryID INT --最低月薪
- DECLARE @dcEducationID AS INT --学历要求
- DECLARE @MinAge AS INT --最小年龄
- DECLARE @MaxAge AS INT --最大年龄
- SELECT
- @JobTypeID=dcJobTypeID,
- @dcRegionID = dcRegionID,
- @MinExperience = MinExperience,
- @dcSalaryID = dcSalaryID,
- @dcEducationID = dcEducationID,
- @MinAge = MinAge,
- @MaxAge = MaxAge,
- @cpIndustry = ','+STUFF((SELECT ','+LTRIM(a.dcIndustryid)+',c'+LTRIM(b.CateGoryId) FROM cpIndustry a,dcIndustryCategory b WHERE a.dcIndustryid = b.dcIndustryid AND cpMainid = JOB.cpMainID FOR XML PATH('')),1,1,'')+','
- FROM JOB WITH(NOLOCK)
- WHERE ID = @JobID
- DECLARE @cvJobType AS INT --期望职位类别
- DECLARE @cvJobPlace AS VARCHAR(100) --期望工作地点
- DECLARE @cvIndustry AS VARCHAR(100) --期望从事行业
- DECLARE @cvRelatedWorkYears AS INT --个人工作经验
- DECLARE @cvDcSalaryID AS INT --期望月薪
- DECLARE @IsNegotiable AS BIT --是否面议
- DECLARE @Degree AS INT --个人学历
- DECLARE @Age AS INT --个人年龄
- DECLARE @INTTemp AS INT --临时变量
- DECLARE @INTTemp2 AS INT
- --匹配职位类别
- SELECT @JobTypeMatch = ISNULL(MAX(
- CASE
- WHEN dcJobTypeID=@JobTypeID OR @JobTypeID LIKE LTRIM(dcJobTypeID)+'%' THEN 1
- WHEN LEFT(LTRIM(@JobTypeID),2) = LEFT(dcJobTypeID,2) THEN 0.5
- END),'0')
- FROM cvJobType WITH(NOLOCK) WHERE cvMainID=@cvMainID
- /*
- IF @JobTypeMatch <> 1
- SELECT @JobTypeMatch = ISNULL(Max(a.Coefficient)/10.0,@JobTypeMatch)
- FROM dcJobTypeSimilar a WITH(NOLOCK) ,cvJobType b WITH(NOLOCK)
- WHERE b.cvMainID=@cvMainID
- AND (
- (
- a.dcJobTypeID = @JobTypeID
- AND a.SimilarId = b.dcJobTypeID
- )
- OR
- (
- SimilarId = @JobTypeID
- AND a.dcJobTypeID = b.dcJobTypeID
- )
- )
- */
- --匹配工作地点
- IF EXISTS( SELECT top 1 'x' FROM cvJobPlace WITH(NOLOCK)
- WHERE cvMainID = @cvMainID
- AND (
- dcRegionID = @dcRegionID
- OR
- (
- @dcRegionID LIKE ''+LTRIM(dcRegionID)+'%' AND (LEN(dcRegionID)=4 Or LEN(dcRegionID)=2)
- )
- )
- )
- SET @RegionMatch = 1
- --循环匹配行业
- DECLARE @iIndex AS INT
- SET @iIndex = 0
- DECLARE #Industry CURSOR FOR
- SELECT a.dcIndustryID,b.CategoryID
- FROM cvIndustry a WITH(NOLOCK) ,dcIndustryCategory b WITH(NOLOCK)
- WHERE a.dcIndustryid = b.dcIndustryid
- AND cvMainID=@cvMainID
- ORDER BY a.dcIndustryID
- OPEN #Industry
- FETCH NEXT FROM #Industry INTO @INTTemp,@INTTemp2
- WHILE @@FETCH_STATUS = 0 AND @IndustryMatch<>1
- BEGIN
- SET @iIndex = @iIndex + 1
- IF CHARINDEX(','+LTRIM(@INTTemp)+',',LTRIM(@cpIndustry))>0
- SET @IndustryMatch=1
- ELSE IF CHARINDEX(',c'+LTRIM(@INTTemp2)+',',LTRIM(@cpIndustry))>0
- SET @IndustryMatch=0.5
- FETCH NEXT FROM #Industry INTO @INTTemp,@INTTemp2
- END
- CLOSE #Industry
- DEALLOCATE #Industry
- IF @iIndex = 0
- SET @IndustryMatch = 1
- --取简历数据
- SELECT
- @Degree = Degree,
- @cvRelatedWorkYears = ISNULL(RelatedWorkYears,0),
- @cvDcSalaryID = dcSalaryID,
- @IsNegotiable = IsNegotiable,
- @Age = dbo.BirthToAge(b.Birthday)
- From cvMain a WITH(NOLOCK), paMain b WITH(NOLOCK)
- WHERE a.id=@cvMainID
- AND a.paMainID = b.ID
- --匹配工作经验
- IF @MinExperience = 0
- SET @ExperienceMatch = 1
- ELSE IF @MinExperience = 1
- BEGIN
- IF @cvRelatedWorkYears<1
- SET @ExperienceMatch = 0.5
- ELSE IF @cvRelatedWorkYears>2
- SET @ExperienceMatch = 1 + (@cvRelatedWorkYears-2) * -0.1
- ELSE
- SET @ExperienceMatch = 1
- END
- ELSE IF @MinExperience = 2
- BEGIN
- IF @cvRelatedWorkYears<3
- BEGIN
- IF @cvRelatedWorkYears >=1 AND @cvRelatedWorkYears <=2
- SET @ExperienceMatch = 0.5
- END
- ELSE IF @cvRelatedWorkYears>5
- SET @ExperienceMatch = 1 + (@cvRelatedWorkYears-2) * -0.1
- ELSE
- SET @ExperienceMatch = 1
- END
- ELSE IF @MinExperience = 3
- BEGIN
- IF @cvRelatedWorkYears<6
- BEGIN
- IF @cvRelatedWorkYears >=3 AND @cvRelatedWorkYears <=5
- SET @ExperienceMatch = 0.5
- END
- ELSE IF @cvRelatedWorkYears>10
- SET @ExperienceMatch = 1 + (@cvRelatedWorkYears-2) * -0.1
- ELSE
- SET @ExperienceMatch = 1
- END
- ELSE IF @MinExperience = 4
- BEGIN
- IF @cvRelatedWorkYears<10
- BEGIn
- IF @cvRelatedWorkYears >=6 AND @cvRelatedWorkYears <=10
- SET @ExperienceMatch = 0.5
- END
- ELSE
- SET @ExperienceMatch = 1
- END
- ELSE IF @MinExperience = 5
- SET @ExperienceMatch = 1 +(ISNULL(@cvRelatedWorkYears,0) * -0.1)
- ELSE
- SET @ExperienceMatch = 1
- --匹配月薪
- IF @dcSalaryID = @cvDcSalaryID OR @dcSalaryID = 100 OR @IsNegotiable = 1
- SET @SalaryMatch = 1
- ELSE IF @dcSalaryID - @cvDcSalaryID = 1 OR @dcSalaryID - @cvDcSalaryID = -1
- SET @SalaryMatch = 0.5
-
- --匹配学历
- IF @Degree = @dcEducationID
- SET @EducationMatch = 1
- ELSE IF @Degree - @dcEducationID = 1
- SET @EducationMatch = 0.8
- ELSE IF @Degree - @dcEducationID = 2
- SET @EducationMatch = 0.5
-
- --匹配年龄
- IF (@MinAge = 99 AND @MaxAge = 99) OR (@MinAge = 0 AND @MaxAge = 0)
- SET @AgeMatch = 1
- ELSE IF @Age < @MinAge
- BEGIN
- IF @MinAge - @Age = 1
- SET @AgeMatch = 0.8
- ELSE IF @MinAge-@Age = 2
- SET @AgeMatch = 0.5
- end
- ELSE IF @Age > @MaxAge
- BEGIN
- IF @Age - @MaxAge = 1
- SET @AgeMatch = 0.8
- ELSE IF @Age - @MaxAge = 2
- SET @AgeMatch = 0.5
- END
- ELSE
- SET @AgeMatch = 1
- RETURN CONVERT(INT,(@JobTypeMatch+@RegionMatch+@ExperienceMatch+@SalaryMatch+@EducationMatch+@IndustryMatch+@AgeMatch)/7*100)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetJobFullRegionName] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetJobFullRegionName]
- (
- @JobID INT
- )
- RETURNS NVARCHAR(100)
- AS
- BEGIN
- DECLARE @strRegionID NVARCHAR(100)
- SET @strRegionID = ''
-
- SELECT @strRegionID = @strRegionID + RTRIM(LTRIM(b.fullname)) + ','
- FROM JobRegion a WITH(NOLOCK),
- dcRegion b WITH(NOLOCK)
- WHERE a.JobID = @JobID
- AND a.dcRegionID = b.ID
- IF LEN(@strRegionID) > 0
- SET @strRegionID = LEFT(@strRegionID, LEN(@strRegionID) - 1)
-
- RETURN @strRegionID
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetJobIds] Script Date: 2018/12/13 18:28:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetJobIds]
- (
- @cpMainID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @ids AS VARCHAR(MAX)
- SET @ids = ''
- SELECT TOP 10 @ids = @ids +
- CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END +
- --ISNULL((SELECT IsFollow FROM MarketDb..PageBaiduInclude WHERE PageType = 2 AND PageId = SecondID), '') +
- LTRIM(STR(ID))
- FROM Job WITH(NOLOCK)
- WHERE cpMainId = @cpMainId
- AND Valid = 1
- ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId)
-
- RETURN @ids
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetJobIdsNew] Script Date: 2018/12/13 18:28:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetJobIdsNew]
- (
- @cpMainID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @ids AS VARCHAR(MAX)
- SET @ids = ''
- SELECT TOP 20 @ids = @ids + CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END +
- --(SELECT IsFollow FROM MarketDb..PageBaiduInclude WHERE PageType = 2 AND PageId = ID) +
- LTRIM(STR(ID))
- FROM Job WITH(NOLOCK)
- WHERE cpMainId = @cpMainId
- AND Valid = 1
- ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId)
-
- RETURN @ids
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetJobIdsOld] Script Date: 2018/12/13 18:28:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create FUNCTION [dbo].[GetJobIdsOld]
- (
- @cpMainID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @ids AS VARCHAR(MAX)
- SET @ids = ''
- SELECT TOP 20 @ids = @ids + CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END + LTRIM(STR(ID))
- FROM Job WITH(NOLOCK)
- WHERE cpMainId = @cpMainId
- AND Valid = 1
- ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId)
-
- RETURN @ids
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetJobNames] Script Date: 2018/12/13 18:28:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetJobNames]
- (
- @cpMainID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @ids AS VARCHAR(MAX)
- SET @ids = ''
- SELECT TOP 10 @ids = @ids + CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END + REPLACE(Name, ',', ',')
- FROM Job WITH(NOLOCK)
- WHERE cpMainId = @cpMainId
- AND Valid = 1
- ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId), ID
-
- RETURN @ids
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetJobSecondIds] Script Date: 2018/12/13 18:28:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetJobSecondIds]
- (
- @cpMainID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @ids AS VARCHAR(MAX)
- SET @ids = ''
- SELECT TOP 10 @ids = @ids +
- CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END +
- --ISNULL((SELECT '_' FROM MarketDb..PageBaiduInclude WITH(NOLOCK) WHERE PageType = 2 AND PageId = SecondID AND IsFollow = 'n'), '') +
- SecondId
- FROM Job WITH(NOLOCK)
- WHERE cpMainId = @cpMainId
- AND Valid = 1
- ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId), ID
-
- RETURN @ids
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetLotteryGiftType] Script Date: 2018/12/13 18:28:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetLotteryGiftType]
- (
- @IsAgent TINYINT,
- @GiftNo INT,
- @IsVip TINYINT
- )
- RETURNS TINYINT
- AS
- BEGIN
- --奖品类型:1、50职位并发 2、50份简历 3、记事本(100份简历) 4、100条短信数 5、2周六分之一图片 6、2周知名企业 7、2周职位置顶 8、200次职位刷新(2周职位自动刷新) 9、2周六分之二 10、蓝皮书(Vip会员—季度) 11、满1000-50优惠券 12、电热水壶 99、没中奖
- DECLARE @GiftType TINYINT
- IF @IsAgent = 0
- BEGIN
- IF @IsVip = 0
- BEGIN
- IF @GiftNo BETWEEN 1 AND 100 --六分之一广告 10%概率
- SET @GiftType = 5
- ELSE IF @GiftNo BETWEEN 101 AND 250 --2周知名企业 15%概率
- SET @GiftType = 6
- ELSE IF @GiftNo BETWEEN 251 AND 300 --两周职位置顶 5%概率
- SET @GiftType = 7
- ELSE IF @GiftNo BETWEEN 301 AND 450 --200次职位刷新 15%概率
- SET @GiftType = 8
- ELSE IF @GiftNo BETWEEN 451 AND 550 --六分之二广告 10%概率
- SET @GiftType = 9
- ELSE --满1000-50优惠券 45%概率
- SET @GiftType = 11
- END
- ELSE
- BEGIN
- IF @GiftNo BETWEEN 1 AND 150 --50职位并发 15%概率
- SET @GiftType = 1
- ELSE IF @GiftNo BETWEEN 151 AND 300 --50份简历 15%概率
- SET @GiftType = 2
- ELSE IF @GiftNo BETWEEN 301 AND 430 --100条短信数 13%概率
- SET @GiftType = 4
- ELSE IF @GiftNo BETWEEN 431 AND 530 --六分之一广告 10%概率
- SET @GiftType = 5
- ELSE IF @GiftNo BETWEEN 531 AND 630 --2周知名企业 10%概率
- SET @GiftType = 6
- ELSE IF @GiftNo BETWEEN 631 AND 680 --两周职位置顶 5%概率
- SET @GiftType = 7
- ELSE IF @GiftNo BETWEEN 681 AND 880 --200次职位刷新 20%概率
- SET @GiftType = 8
- ELSE IF @GiftNo BETWEEN 881 AND 980 --六分之二广告 10%概率
- SET @GiftType = 9
- ELSE --满1000-50优惠券 10%概率
- SET @GiftType = 11
- END
- END
- ELSE
- BEGIN
- IF @GiftNo BETWEEN 1 AND 300 --50职位并发 30%概率
- SET @GiftType = 1
- ELSE IF @GiftNo BETWEEN 301 AND 500 --50份简历 20%概率
- SET @GiftType = 2
- ELSE IF @GiftNo BETWEEN 501 AND 600 --100条短信数 10%概率
- SET @GiftType = 4
- ELSE --200次自动刷新 50%概率
- SET @GiftType = 8
- END
- RETURN @GiftType
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetMemberTypeWithRealName] Script Date: 2018/12/13 18:28:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2010-11-16
- --判断企业用户类型
- --######################################3
- CREATE FUNCTION [dbo].[GetMemberTypeWithRealName]
- (
- @ID INT
- )
- RETURNS TINYINT
- AS
- BEGIN
- DECLARE @HasLicence AS BIT, @BalanceDate AS SMALLDATETIME, @UnlimitedDate AS SMALLDATETIME,
- @Address AS VARCHAR(100), @Name AS NVARCHAR(6), @Gender AS BIT,
- @MemberType AS TINYINT, @IsDelete AS BIT, @Mobile VARCHAR(100), @RealName TINYINT
- SELECT @HasLicence = ISNULL(HasLicence, 0),
- @BalanceDate = ISNULL(BalanceDate, '1900-1-1'),
- @UnlimitedDate = ISNULL(UnlimitedDate, '1900-1-1'),
- @Address = ISNULL(Address, ''),
- @IsDelete = IsDelete, @RealName = RealName
- FROM cpMain WITH(NOLOCK)
- WHERE ID = @ID
-
- SELECT @Name = Name, @Gender = Gender, @Mobile = ISNULL(Mobile, ISNULL(TelePhone, '')) FROM caMain WITH(NOLOCK)
- WHERE cpMainID = @ID AND AccountType = 1
-
- ----企业资料不完整,=0
- IF ISNULL(@Address, '') = '' OR @IsDelete = 1 OR ISNULL(@Name, '') = '' OR @Gender IS NULL OR @Mobile = ''
- SET @MemberType = 0
- ----已经上传营业执照,只能是普通会员:2、无限制:10、储值:11、vip:3之一
- ELSE IF @HasLicence = 0
- SET @MemberType = 1
- ----检查订单
- ELSE IF EXISTS(SELECT 'x' FROM caOrder WITH(NOLOCK)
- WHERE cpMainID = @ID AND OrderType = 8
- AND BeginDate <= GETDATE() AND EndDate > DATEADD(MINUTE, -4, GETDATE())
- AND IsDeleted = 0
- )
- BEGIN
- IF @RealName = 1
- SET @MemberType = 31
- ELSE
- SET @MemberType = 3
- END
- ELSE
- BEGIN
- IF @RealName = 1
- SET @MemberType = 21
- ELSE
- SET @MemberType = 2
- END
- RETURN @MemberType
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetNextMSSchedule] Script Date: 2018/12/13 18:28:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create function [dbo].[GetNextMSSchedule]
- (
- @msScheduleId AS INT
- )
- RETURNS SMALLDATETIME
- AS
- BEGIN
- DECLARE @FreqType AS TINYINT,
- @FreqINTerval AS TINYINT,
- @FreqSubdayType AS TINYINT,
- @FreqSubdayInterval AS SMALLINT,
- @ActiveStartDate AS VARCHAR(8),
- @ActiveEndDate AS VARCHAR(8),
- @ActiveStartTime AS VARCHAR(4),
- @ActiveEndTime AS VARCHAR(4),
- @StartDate AS SMALLDATETIME,
- @EndDate AS SMALLDATETIME,
- @NextRunTime AS SMALLDATETIME,
- @PlanTime AS SMALLDATETIME,
- @I AS INT,
- @II AS INT,
- @Exceeding AS INT,
- @IsOk AS INT,
- @Now AS SMALLDATETIME
- --获取计划任务数据
- SELECT @FreqType = FreqType,
- @FreqInterval = FreqInterval,
- @FreqSubdayType = FreqSubdayType,
- @FreqSubdayInterval = FreqSubdayInterval,
- @ActiveStartDate = CONVERT(VARCHAR(8), ActiveStartDate),
- @ActiveEndDate = CONVERT(VARCHAR(8), ActiveEndDate),
- @ActiveStartTime = CONVERT(VARCHAR(4), ActiveStartTime),
- @ActiveEndTime = CONVERT(VARCHAR(4), ActiveEndTime),
- @IsOk = 0,
- @Now = CONVERT(VARCHAR(16), GETDATE(),120)
- FROM MainDb..msSchedule WITH(NOLOCK)
- WHERE ID = @msScheduleId
- --计算开始时间和结束时间
- SELECT @StartDate = dbo.MergeDate(@ActiveStartDate, @ActiveStartTime),
- @EndDate = dbo.MergeDate(@ActiveEndDate, @ActiveEndTime)
- IF @EndDate <= @Now --如果结束时间<当前,直接退出
- BEGIN
- SELECT @NextRunTime = NULL
- END
- ELSE IF @FreqType = 1 --执行一次
- BEGIN
- --如果开始时间>当前,则返回开始时间,否则返回null
- IF @StartDate > @Now
- SELECT @NextRunTime = @StartDate
- ELSE
- SELECT @NextRunTime = NULL
- END
- ELSE IF @FreqType = 4 --每日执行
- BEGIN
- IF @StartDate > @Now --如果开始时间>当前,下一次=开始时间
- SELECT @PlanTime = @StartDate
- ELSE
- BEGIN
- --计算从今天起再过几天(@Exceeding)是下次执行日期
- SELECT @Exceeding = DATEDIFF(DAY, @StartDate, @Now) % @FreqInterval
- IF(@Exceeding > 0)
- SELECT @Exceeding = @FreqInterval - @Exceeding
- --得出自今日-点期最近一次执行日期
- SELECT @PlanTime = dbo.MergeDate(@Now + @Exceeding, @ActiveStartTime)
- IF @PlanTime > @Now
- SELECT @NextRunTime = @PlanTime, @IsOk = 1
- ELSE
- BEGIN
- --下次执行日期<当前
- --如果间隔内循环 分钟
- IF @FreqSubdayType >= 4
- BEGIN
- IF @FreqSubdayType = 8
- SELECT @FreqSubdayInterval = @FreqSubdayInterval * 60
- --计算到下次执行,间隔多少分钟
- SELECT @Exceeding = DATEDIFF(MINUTE, @PlanTime, @Now) % @FreqSubdayInterval
- IF(@Exceeding > 0)
- SELECT @Exceeding = @FreqSubdayInterval - @Exceeding
- SELECT @NextRunTime = DATEADD(MINUTE, @Exceeding, @Now)
- IF @NextRunTime <= @Now --如果是当前一分钟,则+1分钟
- SELECT @NextRunTime = DATEADD(MINUTE, @FreqSubdayInterval, @NextRunTime)
- IF @NextRunTime < CONVERT(VARCHAR(10), @Now + 1, 120)
- SELECT @IsOk = 1
- END
- IF(@IsOk = 0) --间隔内不循环或者本日无法循环,则到下一执行日期
- SET @NextRunTime = @PlanTime + @FreqInterval
- END
- END
- END
- ELSE IF @FreqType = 8 --按周间隔,周、月都是执行一次
- BEGIN
- DECLARE @w AS INT, @TodayPower AS INT
- SELECT @w = DATEPART(W, @Now) --1周日,2 周一,。。。7周六
- SELECT @TodayPower = POWER(2, @w - 1), @i = 8, @Exceeding = 7
- IF (@FreqInterval & @TodayPower = @TodayPower) --今日是执行日期
- BEGIN
- SELECT @PlanTime = dbo.MergeDate(@Now, @ActiveStartTime)
- IF @PlanTime > @Now --执行时间
- SELECT @NextRunTime = @PlanTime, @IsOk = 1
- END
- IF(@IsOk = 0) --本日不执行,或者本日时间已过,则到下一执行日期
- BEGIN
- WHILE @I < 15
- BEGIN
- IF (@FreqInterval & POWER(2, @I - 8) = POWER(2, @I - 8))
- BEGIN
- SELECT @II = (@i - @w) % 7
- IF(@Exceeding > @II AND @II > 0)
- SET @Exceeding = @II
- END
- SET @i = @i + 1
- END
- SELECT @NextRunTime = dbo.MergeDate(@Now, @ActiveStartTime) + @Exceeding, @IsOk = 1
- END
- END
- ELSE IF @FreqType = 16 --按月间隔,周、月都是执行一次
- BEGIN
- SELECT @Exceeding = DATEDIFF(MONTH, @StartDate, @Now) / @FreqInterval, @I = 1
- SELECT @PlanTime = DATEADD(MONTH, @Exceeding * @FreqInterval, @StartDate)
- WHILE @PlanTime < @Now
- BEGIN
- SELECT @PlanTime = DATEADD(MONTH, @FreqInterval, @PlanTime)
- SELECT @I = @I + 1
- END
- SET @NextRunTime = @PlanTime
- END
- RETURN @NextRunTime
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetNextSchedule] Script Date: 2018/12/13 18:28:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create function [dbo].[GetNextSchedule]
- (
- @schScheduleId AS INT
- )
- RETURNS SMALLDATETIME
- AS
- BEGIN
- DECLARE @FreqType AS TINYINT,
- @FreqINTerval AS TINYINT,
- @FreqSubdayType AS TINYINT,
- @FreqSubdayInterval AS SMALLINT,
- @ActiveStartDate AS VARCHAR(8),
- @ActiveEndDate AS VARCHAR(8),
- @ActiveStartTime AS VARCHAR(4),
- @ActiveEndTime AS VARCHAR(4),
- @StartDate AS SMALLDATETIME,
- @EndDate AS SMALLDATETIME,
- @NextRunTime AS SMALLDATETIME,
- @PlanTime AS SMALLDATETIME,
- @I AS INT,
- @II AS INT,
- @Exceeding AS INT,
- @IsOk AS INT,
- @Now AS SMALLDATETIME
- --获取计划任务数据
- SELECT @FreqType = FreqType,
- @FreqInterval = FreqInterval,
- @FreqSubdayType = FreqSubdayType,
- @FreqSubdayInterval = FreqSubdayInterval,
- @ActiveStartDate = CONVERT(VARCHAR(8), ActiveStartDate),
- @ActiveEndDate = CONVERT(VARCHAR(8), ActiveEndDate),
- @ActiveStartTime = CONVERT(VARCHAR(4), ActiveStartTime),
- @ActiveEndTime = CONVERT(VARCHAR(4), ActiveEndTime),
- @IsOk = 0,
- @Now = CONVERT(VARCHAR(16), GETDATE(),120)
- FROM MainDb..schSchedule WITH(NOLOCK)
- WHERE ID = @schScheduleId
- --计算开始时间和结束时间
- SELECT @StartDate = dbo.MergeDate(@ActiveStartDate, @ActiveStartTime),
- @EndDate = dbo.MergeDate(@ActiveEndDate, @ActiveEndTime)
- IF @EndDate <= @Now --如果结束时间<当前,直接退出
- BEGIN
- SELECT @NextRunTime = NULL
- END
- ELSE IF @FreqType = 1 --执行一次
- BEGIN
- --如果开始时间>当前,则返回开始时间,否则返回null
- IF @StartDate > @Now
- SELECT @NextRunTime = @StartDate
- ELSE
- SELECT @NextRunTime = NULL
- END
- ELSE IF @FreqType = 4 --每日执行
- BEGIN
- IF @StartDate > @Now --如果开始时间>当前,下一次=开始时间
- SELECT @PlanTime = @StartDate
- ELSE
- BEGIN
- --计算从今天起再过几天(@Exceeding)是下次执行日期
- SELECT @Exceeding = DATEDIFF(DAY, @StartDate, @Now) % @FreqInterval
- IF(@Exceeding > 0)
- SELECT @Exceeding = @FreqInterval - @Exceeding
- --得出自今日-点期最近一次执行日期
- SELECT @PlanTime = dbo.MergeDate(@Now + @Exceeding, @ActiveStartTime)
- IF @PlanTime > @Now
- SELECT @NextRunTime = @PlanTime, @IsOk = 1
- ELSE
- BEGIN
- --下次执行日期<当前
- --如果间隔内循环 分钟
- IF @FreqSubdayType >= 4
- BEGIN
- IF @FreqSubdayType = 8
- SELECT @FreqSubdayInterval = @FreqSubdayInterval * 60
- --计算到下次执行,间隔多少分钟
- SELECT @Exceeding = DATEDIFF(MINUTE, @PlanTime, @Now) % @FreqSubdayInterval
- IF(@Exceeding > 0)
- SELECT @Exceeding = @FreqSubdayInterval - @Exceeding
- SELECT @NextRunTime = DATEADD(MINUTE, @Exceeding, @Now)
- IF @NextRunTime <= @Now --如果是当前一分钟,则+1分钟
- SELECT @NextRunTime = DATEADD(MINUTE, @FreqSubdayInterval, @NextRunTime)
- IF @NextRunTime < CONVERT(VARCHAR(10), @Now + 1, 120)
- SELECT @IsOk = 1
- END
- IF(@IsOk = 0) --间隔内不循环或者本日无法循环,则到下一执行日期
- SET @NextRunTime = @PlanTime + @FreqInterval
- END
- END
- END
- ELSE IF @FreqType = 8 --按周间隔,周、月都是执行一次
- BEGIN
- DECLARE @w AS INT, @TodayPower AS INT
- SELECT @w = DATEPART(W, @Now) --1周日,2 周一,。。。7周六
- SELECT @TodayPower = POWER(2, @w - 1), @i = 8, @Exceeding = 7
- IF (@FreqInterval & @TodayPower = @TodayPower) --今日是执行日期
- BEGIN
- SELECT @PlanTime = dbo.MergeDate(@Now, @ActiveStartTime)
- IF @PlanTime > @Now --执行时间
- SELECT @NextRunTime = @PlanTime, @IsOk = 1
- END
- IF(@IsOk = 0) --本日不执行,或者本日时间已过,则到下一执行日期
- BEGIN
- WHILE @I < 15
- BEGIN
- IF (@FreqInterval & POWER(2, @I - 8) = POWER(2, @I - 8))
- BEGIN
- SELECT @II = (@i - @w) % 7
- IF(@Exceeding > @II AND @II > 0)
- SET @Exceeding = @II
- END
- SET @i = @i + 1
- END
- SELECT @NextRunTime = dbo.MergeDate(@Now, @ActiveStartTime) + @Exceeding, @IsOk = 1
- END
- END
- ELSE IF @FreqType = 16 --按月间隔,周、月都是执行一次
- BEGIN
- SELECT @Exceeding = DATEDIFF(MONTH, @StartDate, @Now) / @FreqInterval, @I = 1
- SELECT @PlanTime = DATEADD(MONTH, @Exceeding * @FreqInterval, @StartDate)
- WHILE @PlanTime < @Now
- BEGIN
- SELECT @PlanTime = DATEADD(MONTH, @FreqInterval, @PlanTime)
- SELECT @I = @I + 1
- END
- SET @NextRunTime = @PlanTime
- END
- RETURN @NextRunTime
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetNumber] Script Date: 2018/12/13 18:28:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetNumber]
- (
- @Text NVARCHAR(100)
- )
- RETURNS INT
- AS
- BEGIN
- DECLARE @I INT, @chr NCHAR(1), @Id VARCHAR(10)
- SELECT @i = 0, @Id = ''
- WHILE @i < LEN(@Text)
- BEGIN
- SET @i = @i + 1
- SET @chr = SUBSTRING(@Text, @i, 1)
- IF @Chr IN('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
- SET @Id = @Id + @Chr
- ELSE
- IF LEN(@Id) > 0
- BREAK
- END
- IF LEN(@Id) > 0
- SET @i = CONVERT(INT, @Id)
- ELSE
- SET @i = 0
- RETURN @i
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetOldManagerUserName] Script Date: 2018/12/13 18:28:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE function [dbo].[GetOldManagerUserName]
- (
- @ManagerUserId INT,
- @Date DATETIME
- )
- RETURNS NVARCHAR(10)
- AS
- BEGIN
- DECLARE @N AS NVARCHAR(10)
- SELECT TOP 1 @n = a.Name
- FROM OaDb..WorkUser a, Oadb..Contract b
- WHERE a.ManagerUserId = @ManagerUserId
- AND a.Id = b.WorkUserId
- AND @Date BETWEEN b.BeginDate AND b.RealEndDate
- --ORDER BY ISNULL(AnnualDate, AddDate + 365) DESC
- IF ISNULL(@N, '') = ''
- SELECT @n = Name FROM MainDb..ManagerUser WHERE Id = @ManagerUSerId
- RETURN @n
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetPaName] Script Date: 2018/12/13 18:28:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#########################################################33
- --Lambo 20110221
- --Lucifer 20180425
- --#########################################################33
- CREATE FUNCTION [dbo].[GetPaName]
- (
- @cvMainID INT,
- @cpMainID INT
- )
- RETURNS NVARCHAR(20)
- AS
- BEGIN
- DECLARE @ReturnName NVARCHAR(20),
- @cpMemeberType INT,
- @Name NVARCHAR(20),
- @JobName NVARCHAR(50),
- @IsNameHiden BIT,
- @Gender BIT,
- @showType INT,
- @SecondId VARCHAR(10)
- SELECT TOP 1 @Name = b.Name,
- @Gender = b.Gender,
- @JobName = a.JobName,
- @IsNameHiden = a.IsNameHidden ,
- @SecondId = a.SecondId
- FROM cvMain a WITH(NOLOCK)
- INNER JOIN paMain b WITH(NOLOCK) ON a.paMainID = b.ID
- WHERE a.ID = @cvMainID
- SET @showType = dbo.GetChatPrivi(@cvMainID, @cpMainID)
- IF @showType = 0
- BEGIN
- SET @ReturnName = ISNULL(@JobName, @SecondId)
- END
- ELSE
- BEGIN
- IF EXISTS (SELECT 'x' FROM ExJobApply a WITH(NOLOCK),Job b
- WHERE a.JobID = b.ID
- AND a.cvMainID = @cvMainID
- AND b.cpMainID = @cpMainID
- )
- SET @ReturnName = @Name
- ELSE
- BEGIN
- IF @IsNameHiden = 1
- BEGIN
- IF @Gender = 1
- SET @ReturnName = LEFT(@Name, 1) + '女士/小姐'
- ELSE
- SET @ReturnName = LEFT(@Name, 1) + '先生'
- END
- ELSE
- SET @ReturnName = @Name
- END
- END
- RETURN @ReturnName
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetPaOnlineStatus] Script Date: 2018/12/13 18:28:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetPaOnlineStatus]
- (
- @paMainID INT
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @IsOnline AS BIT
- SELECT @IsOnline = IsOnline FROM paOnline With(NOLOCK) WHERE paMainID = @paMainID
- RETURN ISNULL(@IsOnline, 0)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetPaPhotoByCpView] Script Date: 2018/12/13 18:28:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetPaPhotoByCpView]
- (
- @cvMainID INT,
- @cpMainID INT
- )
- RETURNS VARCHAR(50)
- AS
- BEGIN
- DECLARE @PhotoPath VARCHAR(50)
-
- DECLARE @MemberType TINYINT
-
- SELECT @MemberType = MemberType
- FROM cpMain WITH(NOLOCK)
- WHERE id = @cpMainID
-
- IF @MemberType < 2
- RETURN ''
-
- DECLARE @isInActiveLog INT, @isInDailyGiftLog INT, @isApplyCount INT, @paMainID INT
- SELECT @isInActiveLog = 0, @isInDailyGiftLog = 0, @isApplyCount = 0
- IF EXISTS(SELECT TOP 1 'x'
- FROM cpCvPrivi WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID)
- SET @isInDailyGiftLog = 1
- --sean modify at 2018-3-6
- -- IF @isInDailyGiftLog = 0
- -- IF EXISTS(SELECT TOP 1 'x'
- -- FROM caDailyGiftLog WITH(NOLOCK INDEX(PK_caDailyGiftLog))
- -- WHERE cpMainID = @cpMainID
- -- AND cvMainID = @cvMainID)
- -- SET @isInDailyGiftLog = 1
- --
- -- IF @isInDailyGiftLog = 0
- -- BEGIN
- -- DECLARE @t AS TABLE(ID INT)
- -- INSERT INTO @t
- -- SELECT ID FROM cvMain WITH(NOLOCK) WHERE paMainId = @paMainID
- --
- -- IF EXISTS(SELECT TOP 1 'x'
- -- FROM caActiveLog WITH(NOLOCK INDEX(IX_caActiveLog_cpMainID))
- -- WHERE cpMainID = @cpMainID
- -- AND cvMainID IN(SELECT ID FROM @t))
- -- SET @isInActiveLog = 1
- --
- -- IF @isInActiveLog = 0
- -- IF EXISTS(SELECT TOP 1 'x'
- -- FROM ExJobApply a WITH(NOLOCK), Job b
- -- WHERE a.JobID = b.ID
- -- AND a.IsPassed = 1
- -- AND a.cvMainID IN(SELECT ID FROM @t)
- -- AND b.cpMainID = @cpMainID)
- -- SET @isApplyCount = 1
- -- END
- IF @isInActiveLog + @isInDailyGiftLog + @isApplyCount = 0
- RETURN ''
- SELECT @paMainId = paMainId FROM cvMain WITH(NOLOCK) WHERE ID = @cvMainID
- SELECT @PhotoPath = PhotoProcessed
- FROM paPhoto a WITH(NOLOCK INDEX(IX_paPhoto_paMainId))
- WHERE paMainID = @paMainId
-
- RETURN @PhotoPath
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetPersonName] Script Date: 2018/12/13 18:28:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#########################################################33
- --Lucifer 20131202
- --Lucifer 20180425
- --#########################################################33
- CREATE FUNCTION [dbo].[GetPersonName]
- (
- @cvMainID INT,
- @cpMainID INT
- )
- RETURNS VARCHAR(50)
- AS
- BEGIN
- RETURN dbo.GetPaName(@cvMainID, @cpMainID)
- --DECLARE @ReturnName NVARCHAR(20)
- --DECLARE @cpMemeberType INT
- --DECLARE @Name NVARCHAR(20)
- --DECLARE @JobName NVARCHAR(20)
- --DECLARE @IsNameHiden BIT
- --DECLARE @Gender BIT
- --DECLARE @showType INT
- --DECLARE @showDownload INT
- --SELECT TOP 1 @Name = b.Name,
- -- @Gender = b.Gender,
- -- @JobName = a.JobName,
- -- @IsNameHiden = a.IsNameHidden
- --FROM cvMain a WITH(NOLOCK)
- --INNER JOIN paMain b WITH(NOLOCK) ON a.paMainID = b.ID
- --WHERE a.ID = @cvMainID
- --SELECT @cpMemeberType = MemberType FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID
- --SET @showType = 1 --0姓名;1职位, 2不隐藏姓名
- --SET @showDownload = 0
- --IF EXISTS (
- -- SELECT 'x' FROM ExJobApply a WITH(NOLOCK),Job b
- -- WHERE a.JobID = b.ID
- -- AND a.cvMainID = @cvMainID
- -- AND b.cpMainID = @cpMainID
- --)
- -- SET @showType = 2
- --ELSE IF @cpMemeberType IN(10, 11)
- --BEGIN
- -- IF EXISTS(
- -- SELECT 'x' FROM CaCvQuotaLog WITH(NOLOCK)
- -- WHERE cpMainID = @cpMainID
- -- AND cvMainID = @cvMainID
- -- )
- -- SET @showType = 0
- -- ELSE
- -- BEGIN
- -- IF @cpMemeberType = 11
- -- IF EXISTS(
- -- SELECT 'x' FROM caBalanceLog WITH(NOLOCK)
- -- WHERE cpMainID = @cpMainID
- -- AND cvMainID = @cvMainID
- -- AND CONVERT(VARCHAR(100), AddDate, 23) = CONVERT(VARCHAR(100), GETDATE(), 23)
- -- )
- -- SET @showType = 0
- -- END
- --END
- --ELSE
- --BEGIN
- -- DECLARE @isInActiveLog INT
- -- DECLARE @isInDailyGiftLog INT
- -- ----日赠送中有下载
- -- SELECT @isInDailyGiftLog = COUNT(1) FROM caDailyGiftLog WITH(NOLOCK)
- -- WHERE cpMainID = @cpMainID
- -- AND cvMainID = @cvMainID
- -- IF @isInDailyGiftLog > 0
- -- BEGIN
- -- SET @showType = 0
- -- SET @showDownload = 1
- -- END
- -- ELSE
- -- BEGIN
- -- ----简历下载中有下载
- -- SELECT @isInActiveLog = COUNT(1) FROM caActiveLog WITH(NOLOCK)
- -- WHERE cpMainID = @cpMainID
- -- AND cvMainID = @cvMainID
- -- If @isInActiveLog>0
- -- BEGIN
- -- SET @showType = 0
- -- SET @showDownload = 1
- -- END
- -- ELSE
- -- BEGIN
- -- ----申请职位是的新状态是pass
- -- IF EXISTS (
- -- SELECT 'x' FROM ExJobApply a WITH(NOLOCK),Job b WITH(NOLOCK)
- -- WHERE a.JobID = b.ID
- -- AND a.IsPassed = 1
- -- AND a.cvMainID = @cvMainID
- -- AND b.cpMainID = @cpMainID
- -- )
- -- SET @showType = 0
- -- END
- -- END
- --END
- --IF @showType = 0 --姓名
- --BEGIN
- -- SET @ReturnName = @Name
- -- IF @IsNameHiden = 1
- -- IF @Gender = 1
- -- SET @ReturnName = LEFT(@ReturnName, 1) + '女士/小姐'
- -- ELSE
- -- SET @ReturnName = LEFT(@ReturnName, 1) + '先生'
- --END
- --ELSE IF @showType = 2 --姓名
- -- SET @ReturnName = @Name
- --ELSE
- -- SET @ReturnName = ISNULL(@JobName, LTRIM(STR(@cvMainID)))
- --IF @showDownload > 0
- -- SET @ReturnName = '$$##' + @ReturnName
- --RETURN @ReturnName
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetPrivi] Script Date: 2018/12/13 18:28:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetPrivi]
- (
- @ManagerUserID AS SMALLINT,
- @DataSourceID INT,
- @Pos INT
- )
- RETURNS CHAR(1)
- AS
- BEGIN
- DECLARE @RoleID AS INT, @Privi AS VARCHAR(20)
- SELECT @RoleID = RoleID FROM ManagerUser WITH(NOLOCK) WHERE ID = @ManagerUserID
- SELECT @Privi = Privi
- FROM Role_DataSource WITH(NOLOCK)
- WHERE RoleID = @RoleID
- AND DataSourceID = @DataSourceID
-
- IF LEN(ISNULL(@Privi, '')) = 0
- RETURN 'x'
- RETURN SUBSTRING(@Privi, @Pos, 1)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetPyFirst] Script Date: 2018/12/13 18:28:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetPyFirst](
- @str NVARCHAR(100)
- )
- RETURNS NVARCHAR(4000)
- AS
- BEGIN
- DECLARE @intLen INT
- DECLARE @strRet NVARCHAR(4000)
- DECLARE @temp NVARCHAR(100)
- SET @intLen = LEN(@str)
- SET @strRet = ''
- WHILE @intLen > 0
- BEGIN
- SET @temp = ''
- SELECT @temp = CASE
- WHEN SUBSTRING(@str,@intLen,1) >= '帀' THEN 'Z'
- WHEN SUBSTRING(@str,@intLen,1) >= '丫' THEN 'Y'
- WHEN SUBSTRING(@str,@intLen,1) >= '夕' THEN 'X'
- WHEN SUBSTRING(@str,@intLen,1) >= '屲' THEN 'W'
- WHEN SUBSTRING(@str,@intLen,1) >= '他' THEN 'T'
- WHEN SUBSTRING(@str,@intLen,1) >= '仨' THEN 'S'
- WHEN SUBSTRING(@str,@intLen,1) >= '呥' THEN 'R'
- WHEN SUBSTRING(@str,@intLen,1) >= '七' THEN 'Q'
- WHEN SUBSTRING(@str,@intLen,1) >= '妑' THEN 'P'
- WHEN SUBSTRING(@str,@intLen,1) >= '噢' THEN 'O'
- WHEN SUBSTRING(@str,@intLen,1) >= '拏' THEN 'N'
- WHEN SUBSTRING(@str,@intLen,1) >= '嘸' THEN 'M'
- WHEN SUBSTRING(@str,@intLen,1) >= '垃' THEN 'L'
- WHEN SUBSTRING(@str,@intLen,1) >= '咔' THEN 'K'
- WHEN SUBSTRING(@str,@intLen,1) >= '丌' THEN 'J'
- WHEN SUBSTRING(@str,@intLen,1) >= '铪' THEN 'H'
- WHEN SUBSTRING(@str,@intLen,1) >= '旮' THEN 'G'
- WHEN SUBSTRING(@str,@intLen,1) >= '发' THEN 'F'
- WHEN SUBSTRING(@str,@intLen,1) >= '妸' THEN 'E'
- WHEN SUBSTRING(@str,@intLen,1) >= '咑' THEN 'D'
- WHEN SUBSTRING(@str,@intLen,1) >= '嚓' THEN 'C'
- WHEN SUBSTRING(@str,@intLen,1) >= '八' THEN 'B'
- WHEN SUBSTRING(@str,@intLen,1) >= '吖' THEN 'A'
- ELSE RTRIM(LTRIM(SUBSTRING(@str,@intLen,1)))
- END
- --对于汉字特殊字符,不生成拼音码
- IF ASCII(@temp) > 127
- SET @temp = ''
- --对于英文中小括号,不生成拼音码
- IF @temp = '(' OR @temp = ')'
- SET @temp = ''
- SELECT @strRet = @temp + @strRet
- SET @intLen = @intLen - 1
- END
- RETURN LOWER(@strRet)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetRegionAll] Script Date: 2018/12/13 18:28:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE function [dbo].[GetRegionAll]
- (
- @ID VARCHAR(6)
- )
- RETURNS NVARCHAR(20)
- AS
- BEGIN
- RETURN (SELECT FullName FROM dcREgion WITH(NOLOCK) WHERE ID = @ID)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetTop5JobIDs] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create FUNCTION [dbo].[GetTop5JobIDs]
- (
- @JobIds AS VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @i INT, @Num INT, @StrSource VARCHAR(MAX), @s VARCHAR(50)
- SELECT @StrSource = RTRIM(LTRIM(@JobIds))
- SELECT @i = CHARINDEX(',', @StrSource), @Num = 0, @s = ''
- IF @i = 0
- RETURN @StrSource
- WHILE @i >= 1 AND @Num < 5
- BEGIN
- IF LEFT(@StrSource, @i - 1) > ''
- SELECT @s = @s + ',' + LEFT(@StrSource, @i - 1), @Num = @Num + 1
- SELECT @StrSource = SUBSTRING(@StrSource, @i + 1, LEN(@StrSource) - @i)
- SELECT @i = CHARINDEX(',', @StrSource)
- END
- IF @Num < 5 AND @StrSource <> '' AND @i = 0
- SELECT @s = @s + ',' + @StrSource
-
- IF LEFT(@s, 1) = ','
- SELECT @s = RIGHT(@s, LEN(@s) - 1)
- RETURN @s
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[HaveChinese] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE Function [dbo].[HaveChinese]
- (
- @Str varchar(4000)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @IsHave BIT
- IF @Str LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_AS
- BEGIN
- SET @IsHave = 1
- END
- ELSE
- BEGIN
- SET @IsHave = 0
- END
- RETURN @IsHave
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[HtmlPos] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[HtmlPos]
- (
- @s VARCHAR(MAX)
- )
- RETURNS INT
- AS
- BEGIN
- DECLARE @t AS TABLE(P INT)
-
- INSERT @t SELECT CHARINDEX('<p', @s)
- INSERT @t SELECT CHARINDEX('<strong', @s)
- INSERT @t SELECT CHARINDEX('<div', @s)
- INSERT @t SELECT CHARINDEX('<font', @s)
- INSERT @t SELECT CHARINDEX('<span', @s)
- INSERT @t SELECT CHARINDEX('<b', @s)
- INSERT @t SELECT CHARINDEX('</', @s)
- RETURN ISNULL((SELECT MIN(p) FROM @t WHERE p > 0), 0)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[Int2Bin] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create FUNCTION [dbo].[Int2Bin](@i int)
- returns varchar(31)
- as
- begin
- declare @s varchar(31);
- set @s=''
- while (@i>0)
- select @s=cast(@i%2 as varchar)+@s, @i=@i/2
-
- return(@s)
- end
- GO
- /****** Object: UserDefinedFunction [dbo].[IpPlace] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE Function [dbo].[IpPlace]
- (
- @Str varchar(31)
- )
- RETURNS NVARCHAR(100)
- AS
- BEGIN
- DECLARE @A1 VARCHAR(3), @A2 VARCHAR(3), @A3 VARCHAR(3), @A4 VARCHAR(3)
- DECLARE @b1 BIGINT, @b2 INT, @B3 INT, @B4 INT
- SELECT @A1 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
- SELECT @A2 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
- SELECT @A3 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
- SELECT @A4 = @str
- SELECT @B1 = CONVERT(INT, REPLACE(@A1, '.', '')),
- @B2 = CONVERT(INT, REPLACE(@A2, '.', '')),
- @B3 = CONVERT(INT, REPLACE(@A3, '.', '')),
- @B4 = CONVERT(INT, REPLACE(@A4, '.', ''))
- SELECT @B1 = @B1 * 256*256*256 + @B2 * 256*256 + @b3 * 256 + @b4
- RETURN (SELECT TOP 1 City + ' ' + Provider FROM dcIpPlace WITH(NOLOCK) WHERE @B1 BETWEEN IpStart AND IpEnd)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IpPlaceToInt] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE Function [dbo].[IpPlaceToInt]
- (
- @Str varchar(31)
- )
- RETURNS BIGINT
- AS
- BEGIN
- IF @Str NOT LIKE '%.%.%.%'
- RETURN 0
- DECLARE @A1 VARCHAR(3), @A2 VARCHAR(3), @A3 VARCHAR(3), @A4 VARCHAR(3)
- DECLARE @b1 BIGINT, @b2 INT, @B3 INT, @B4 INT
- SELECT @A1 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
- SELECT @A2 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
- SELECT @A3 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
- SELECT @A4 = @str
- SELECT @B1 = CONVERT(INT, REPLACE(@A1, '.', '')),
- @B2 = CONVERT(INT, REPLACE(@A2, '.', '')),
- @B3 = CONVERT(INT, REPLACE(@A3, '.', '')),
- @B4 = CONVERT(INT, REPLACE(@A4, '.', ''))
- RETURN @B1 * 256*256*256 + @B2 * 256*256 + @b3 * 256 + @b4
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsAccount] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --检查是否有效账号
- --######################################################3
- CREATE FUNCTION [dbo].[IsAccount]
- (
- @str VARCHAR(50)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @ok AS BIT
- SET @ok = 1
- IF @str IS NULL
- SET @ok = 0
-
- IF @ok = 1
- BEGIN
- SET @str = LOWER(LTRIM(RTRIM(@str)))
- IF (LEN(@str) > 50) OR (LEN(@str) < 6)
- SET @ok = 0
- END
- IF @str LIKE '% %'
- RETURN 0
- IF @ok = 1
- BEGIN
- DECLARE @I AS INT
- DECLARE @ValidChars AS VARCHAR(39)
- SET @ValidChars = 'abcdefghijklmnopqrstuvwxyz0123456789-_.'
- SET @I = 1
- WHILE @I <= LEN(@str)
- BEGIN
- IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
- SET @I = @I + 1
- ELSE
- BEGIN
- SET @ok = 0
- SET @I = 100
- END
- END
- END
- RETURN @ok
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsChinese] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --检查是否汉字
- --######################################################3
- CREATE FUNCTION [dbo].[IsChinese]
- (
- @str VARCHAR(MAX)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @ok AS BIT
- SET @ok = 1
- IF ISNULL(@str, '') = ''
- SET @ok = 0
- IF @ok = 1
- BEGIN
- DECLARE @I AS INT
- SET @I = 1
- WHILE @I <= LEN(@str)
- BEGIN
- IF dbo.HaveChinese(SUBSTRING(@str, @I, 1)) = 1
- SET @I = @I + 1
- ELSE
- BEGIN
- SET @ok = 0
- SET @I = LEN(@str) + 1
- END
- END
- END
- RETURN @ok
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsEmail] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --检查是否有效邮箱
- --######################################################3
- CREATE FUNCTION [dbo].[IsEmail]
- (
- @str VARCHAR(100)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @ok AS BIT
- SET @ok = 1
- IF @str IS NULL
- SET @ok = 0
-
- IF @ok = 1
- BEGIN
- SET @str = LOWER(LTRIM(RTRIM(@str)))
- IF LEN(@str) > 50 OR LEN(@str) < 6
- SET @ok = 0
- END
- IF @Str LIKE '% %'
- RETURN 0
- IF @ok = 1
- BEGIN
- DECLARE @t AS TABLE(ID INT IDENTITY(1, 1), S VARCHAR(50))
- INSERT INTO @t (s)
- SELECT * FROM dbo.fnSplit(@str, '@')
- IF(SELECT COUNT(1) FROM @t) <> 2
- SET @ok = 0
- ELSE
- BEGIN
- DECLARE @str1 AS VARCHAR(50)
- SELECT @str1 = S FROM @t WHERE ID = 1
- IF dbo.IsEmailText(@str1, 1) = 0
- SET @ok = 0
- ELSE
- BEGIN
- SELECT @str1 = S FROM @t WHERE ID = 2
- IF dbo.IsEmailText(@str1, 2) = 0
- SET @ok = 0
- END
- END
- END
- RETURN @ok
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsEmailText] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --检查是否有效邮箱文本,@前后的一部分
- --######################################################3
- CREATE FUNCTION [dbo].[IsEmailText]
- (
- @str VARCHAR(50),
- @Pos INT --1@前,2@后
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @ok AS BIT
- SET @ok = 1
- IF @str IS NULL
- SET @ok = 0
-
- IF @ok = 1
- BEGIN
- SET @str = LOWER(LTRIM(RTRIM(@str)))
- IF (LEN(@str) > 45) OR (LEN(@str) < 1)
- SET @ok = 0
- END
- IF @ok = 1
- BEGIN
- IF @Pos = 1 AND (LEFT(@str, 1) IN ('.', '-', '_') OR RIGHT(@str, 1) IN ('.', '-', '_'))
- SET @ok = 0
- IF @Pos = 2 AND (LEFT(@str, 1) = ('.') OR RIGHT(@str, 1) IN ('.', '-', '_'))
- SET @ok = 0
- IF CHARINDEX('..', @str) > 0
- SET @ok = 0
- END
- IF @ok = 1
- IF @pos = 2
- IF CHARINDEX('.', @str) = 0
- SET @ok = 0
- ELSE
- BEGIN
- DECLARE @t AS TABLE(ID INT IDENTITY(1, 1), S VARCHAR(50))
- INSERT INTO @t(S) SELECT * FROM dbo.fnSplit(@str, '.')
- DECLARE @s AS VARCHAR(50)
- SELECT TOP 1 @s = S FROM @t ORDER BY ID DESC
- IF LEN(@s) < 2 OR LEN(@s) > 8
- SET @ok = 0
- END
- IF @ok = 1
- BEGIN
- DECLARE @I AS INT, @ValidChars AS VARCHAR(39)
- SET @ValidChars = 'abcdefghijklmnopqrstuvwxyz0123456789-_.'
- SET @I = 1
- WHILE @I <= LEN(@str)
- BEGIN
- IF CHARINDEX(SUBSTRING(@str, @I, 1) collate Chinese_PRC_CI_AS_KS_WS, @ValidChars) > 0
- SET @I = @I + 1
- ELSE
- BEGIN
- SET @ok = 0
- SET @I = 100
- END
- END
- END
-
- RETURN @ok
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsEnglish] Script Date: 2018/12/13 18:28:26 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --检查是否English
- --######################################################3
- CREATE FUNCTION [dbo].[IsEnglish]
- (
- @str VARCHAR(MAX)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @ok AS BIT
- SET @ok = 1
- IF @str IS NULL
- SET @ok = 0
- IF @ok = 1
- BEGIN
- DECLARE @I AS INT
- DECLARE @ValidChars AS VARCHAR(39)
- SET @ValidChars = 'abcdefghijklmnopqrstuvwxyz'
- SET @I = 1
- WHILE @I <= LEN(@str)
- BEGIN
- IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
- SET @I = @I + 1
- ELSE
- BEGIN
- SET @ok = 0
- SET @I = LEN(@str) + 1
- END
- END
- END
- RETURN @ok
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsFullWidth] Script Date: 2018/12/13 18:28:27 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --检查是否全角
- --######################################################3
- CREATE FUNCTION [dbo].[IsFullWidth]
- (
- @str VARCHAR(MAX)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @ok AS BIT
- IF DATALENGTH(@str) = LEN(@str) * 2
- SET @ok = 1
- ELSE
- SET @ok = 0
- IF LEN(ISNULL(@str, '')) = 0
- SET @ok = 0
- RETURN @ok
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsINT] Script Date: 2018/12/13 18:28:27 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --检查是否INT
- --######################################################3
- CREATE FUNCTION [dbo].[IsINT]
- (
- @str VARCHAR(MAX)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @ok AS BIT
- SET @ok = 1
- IF @str IS NULL
- SET @ok = 0
- IF @ok = 1
- BEGIN
- DECLARE @I AS INT
- DECLARE @ValidChars AS VARCHAR(39)
- SET @ValidChars = '0123456789'
- SET @I = 1
- WHILE @I <= LEN(@str)
- BEGIN
- IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
- SET @I = @I + 1
- ELSE
- BEGIN
- SET @ok = 0
- SET @I = LEN(@str) + 1
- END
- END
- END
- RETURN @ok
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsJobApplyFilter] Script Date: 2018/12/13 18:28:27 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[IsJobApplyFilter]
- (
- @JobID INT,
- @cvMainID INT
- )
- RETURNS BIT
- AS
- BEGIN
- --获取职位的过滤条件
- DECLARE @FilterMaxDegree AS TINYINT
- DECLARE @FilterMinDegree AS TINYINT
- DECLARE @FilterMaxWorkYear AS TINYINT
- DECLARE @FilterMinWorkYear AS TINYINT
- DECLARE @FilterMaxAge AS TINYINT
- DECLARE @FilterMinAge AS TINYINT
- DECLARE @FilterGender AS VARCHAR(1)
- DECLARE @FilterJobRegion AS VARCHAR(6)
- DECLARE @FilterCvMainID AS VARCHAR(200)
- DECLARE @cpMainID AS INT
- SELECT
- @FilterMaxDegree = FilterMaxDegree,
- @FilterMinDegree = FilterMinDegree,
- @FilterMaxWorkYear = FilterMaxWorkYear,
- @FilterMinWorkYear = FilterMinWorkYear,
- @FilterMaxAge = FilterMaxAge,
- @FilterMinAge = FilterMinAge,
- @FilterGender = FilterGender,
- @FilterJobRegion = FilterJobRegion,
- @FilterCvMainID = FilterCvMainID,
- @cpMainID = cpMainID
- FROM Job WITH(NOLOCK)
- WHERE ID = @JobID
- --获取简历的相关条件
- DECLARE @Gender AS BIT
- DECLARE @Age AS TINYINT
- DECLARE @LivePlace AS VARCHAR(6)
- DECLARE @RelatedWorkYears AS TINYINT
- DECLARE @Degree AS TINYINT
- SELECT
- @LivePlace = b.LivePlace,
- @Gender = b.Gender,
- @Age = dbo.BirthToAge(b.birthday),
- @RelatedWorkYears = a.RelatedWorkYears,
- @Degree = a.Degree
- FROM cvMain a,paMain b WHERE a.ID=@cvMainID AND a.paMainID=b.ID
-
- --判断性别
- IF LEN(@Gender) <> 0 AND LEN(@FilterGender) <> 0
- BEGIN
- IF @Gender <> @FilterGender
- RETURN 1
- END
- --判断年龄
- IF LEN(@Age) <> 0
- BEGIN
- IF LEN(@FilterMaxAge) <> 0 AND @FilterMaxAge <> 99
- BEGIN
- IF @Age > @FilterMaxAge
- BEGIN
- RETURN 1
- END
- END
- IF LEN(@FilterMinAge) <> 0 AND @FilterMinAge <> 99
- BEGIN
- IF @Age < @FilterMinAge
- BEGIN
- RETURN 1
- END
- END
- END
- --判断工作年限
- IF LEN(@RelatedWorkYears) <> 0
- BEGIN
- IF LEN(@FilterMaxWorkYear) <> 0 AND @FilterMaxWorkYear <> 0
- IF @RelatedWorkYears > @FilterMaxWorkYear
- RETURN 1
- IF LEN(@FilterMinWorkYear) <> 0 AND @FilterMinWorkYear <> 0
- IF @RelatedWorkYears < @FilterMinWorkYear
- RETURN 1
- END
- --判断学历
- IF LEN(@Degree) <> 0
- BEGIN
- IF LEN(@FilterMaxDegree) <> 0 AND @FilterMaxDegree <> 0
- IF @Degree > @FilterMaxDegree
- RETURN 1
- IF LEN(@FilterMinDegree) <> 0 AND @FilterMinDegree <> 0
- IF @Degree < @FilterMinDegree
- RETURN 1
- END
- --判断地区
- IF LEN(@LivePlace) <> 0 AND LEN(@FilterJobRegion) <> 0 AND @FilterJobRegion <> 0
- BEGIN
- IF LEN(@LivePlace) >= LEN(@FilterJobRegion)
- BEGIN
- IF SUBSTRING(@LivePlace,1,LEN(@FilterJobRegion)) <> @FilterJobRegion
- RETURN 1
- END
- ELSE
- BEGIN
- IF SUBSTRING(@FilterJobRegion,1,LEN(@LivePlace)) <> @LivePlace
- RETURN 1
- END
- END
- --判断是否在过滤简历表中
- IF EXISTS(SELECT 'X' FROM cpCvShield WITH(NOLOCK) WHERE cpMainID = @cpMainID and cvMainID=@cvMainID)
- RETURN 1
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ISJobApplyPassed] Script Date: 2018/12/13 18:28:27 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2010-11-16
- --判断是否Vip会员
- --######################################3
- CREATE FUNCTION [dbo].[ISJobApplyPassed]
- (
- @JobID INT,
- @cvMainID INT
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @cpMainID AS INT
- DECLARE @IsOpen AS BIT
- DECLARE @MemberType AS SMALLDATETIME
- DECLARE @Valid AS INT
- SELECT @IsOpen = IsOpen FROM cvMain WITH(NOLOCK) WHERE ID = @cvMainID
- SELECT @cpMainID = a.ID,
- @MemberType = a.MemberType,
- @Valid = a.Valid
- FROM cpMain a WITH(NOLOCK)
- INNER JOIN Job b WITH(NOLOCK)
- ON a.ID = b.cpMainID
- WHERE b.ID = @JobID
- IF @Valid = 0 ------企业信息无效,返回0
- RETURN 0
- IF @MemberType >= 10 ------储值会员或无限制会员,不享受套餐vip
- RETURN 0
- IF (@IsOpen = 1 AND @MemberType = 2) OR @MemberType = 3 ------开放简历,只要申请,企业就可以查看
- RETURN 1
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsMobile] Script Date: 2018/12/13 18:28:27 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --检查是否mobile
- --######################################################3
- CREATE FUNCTION [dbo].[IsMobile]
- (
- @str VARCHAR(MAX)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @ok AS BIT
- SET @ok = 1
- IF @str IS NULL
- RETURN 0
- IF LEN(@str) <> 11
- RETURN 0
- IF LEFT(@str, 2) NOT IN ('13', '14', '15', '16', '17', '18', '19')
- RETURN 0
- --IF CHARINDEX('@', @str) > 0
- -- RETURN 0
- DECLARE @I AS INT
- DECLARE @ValidChars AS VARCHAR(39)
- SET @ValidChars = '0123456789'
- SET @I = 1
- WHILE @I <= LEN(@str)
- BEGIN
- IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
- SET @I = @I + 1
- ELSE
- RETURN 0
- END
- RETURN @ok
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsNumeric] Script Date: 2018/12/13 18:28:27 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --检查是否Numeric
- --######################################################3
- CREATE FUNCTION [dbo].[IsNumeric]
- (
- @str VARCHAR(MAX)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @ok AS BIT
- SET @ok = 1
- IF @str IS NULL
- SET @ok = 0
- DECLARE @s AS VARCHAR(MAX)
- SET @s = REPLACE(@str, '.', '')
- IF LEN(@s) < LEN(@str) - 1
- SET @ok = 0
- IF @ok = 1
- BEGIN
- DECLARE @I AS INT
- DECLARE @ValidChars AS VARCHAR(39)
- SET @ValidChars = '0123456789'
- SET @I = 1
- WHILE @I <= LEN(@s)
- BEGIN
- IF CHARINDEX(SUBSTRING(@s, @I, 1), @ValidChars) > 0
- SET @I = @I + 1
- ELSE
- BEGIN
- SET @ok = 0
- SET @I = LEN(@s) + 1
- END
- END
- END
- RETURN @ok
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsPassword] Script Date: 2018/12/13 18:28:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- ----检查是否有效密码 = 检查是否有效账号(tmp)
- --######################################################3
- CREATE FUNCTION [dbo].[IsPassword]
- (
- @str VARCHAR(60)
- )
- RETURNS BIT
- AS
- BEGIN
- IF LEN(ISNULL(@Str, '')) <> 60
- RETURN 0
- RETURN 1
- /*
- DECLARE @ok AS BIT
- SET @ok = 1
- IF @str IS NULL
- SET @ok = 0
-
- IF @ok = 1
- BEGIN
- SET @str = LOWER(LTRIM(RTRIM(@str)))
- IF (LEN(@str) > 50) OR (LEN(@str) < 6)
- SET @ok = 0
- END
- IF @ok = 1
- BEGIN
- DECLARE @I AS INT
- DECLARE @ValidChars AS VARCHAR(39)
- SET @ValidChars = 'abcdefghijklmnopqrstuvwxyz0123456789-_.'
- SET @I = 1
- WHILE @I <= LEN(@str)
- BEGIN
- IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
- SET @I = @I + 1
- ELSE
- BEGIN
- SET @ok = 0
- SET @I = 100
- END
- END
- END
- RETURN @ok
- */
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[IsSafeSQL] Script Date: 2018/12/13 18:28:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#############################333
- --安全的sql语句用于exec
- --Sean 2013-8-23
- --#############################333
- CREATE FUNCTION [dbo].[IsSafeSQL]
- (
- @inputText VARCHAR(MAX)
- )
- RETURNS INT
- AS
- BEGIN
- IF @inputText IS NULL
- RETURN 1
- DECLARE @sText VARCHAR(Max)
- SET @SText = @inputText
- SET @sText = REPLACE(@sText, '--','——')
- SET @sText = REPLACE(@sText, 'delete ','delete ')
- SET @sText = REPLACE(@sText, 'update ','update ')
- SET @sText = REPLACE(@sText, 'create ','create ')
- SET @sText = REPLACE(@sText, 'drop ','drop ')
- SET @sText = REPLACE(@sText, 'insert ','insert ')
- SET @sText = REPLACE(@sText, 'chr(','chr( ')
- SET @sText = REPLACE(@sText, 'master ','master ')
- SET @sText = REPLACE(@sText, 'net user','net user')
- SET @sText = REPLACE(@sText, 'declare ','declare ')
- SET @sText = REPLACE(@sText, 'char(','char( ')
- SET @sText = REPLACE(@sText, 'mid(','mid( ')
- SET @sText = REPLACE(@sText, 'count ','count ')
- SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell ')
- SET @sText = REPLACE(@sText, 'exec ','exec ')
- SET @sText = REPLACE(@sText, 'truncate ','truncate ')
- SET @sText = REPLACE(@sText, 'ascii(','ascii( ')
- SET @sText = REPLACE(@sText, 'substring(','substring( ')
- SET @sText = REPLACE(@sText, 'localgroup ','localgroup ')
- SET @sText = REPLACE(@sText, 'administrators ','administrators ')
- SET @sText = REPLACE(@sText, 'dbcc ','dbcc ')
- SET @sText = REPLACE(@sText, 'checkdb(','checkdb( ')
- SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember ')
- SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell ')
- IF @SText = @inputText
- RETURN 1
- ELSE
- RETURN 0
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[JobApplyName] Script Date: 2018/12/13 18:28:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[JobApplyName]
- (
- @IsNameHidden BIT,
- @paName VARCHAR(12),
- @Gender BIT
- )
- RETURNS VARCHAR(50)
- AS
- BEGIN
- IF @IsNameHidden = 0
- RETURN @paName
- SET @paName = LEFT(@paName, 1)
- IF @Gender = 0
- RETURN @paName + '先生'
- RETURN @paName + '女士/小姐'
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[JobValid] Script Date: 2018/12/13 18:28:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[JobValid]
- (
- @IsDelete BIT,
- @VerifyResult TINYINT,
- @IssueDate SMALLDATETIME,
- @IssueEnd SMALLDATETIME
- )
- RETURNS BIT
- AS
- BEGIN
- IF @IsDelete = 1
- RETURN 0
-
- IF @VerifyResult<>1
- RETURN 0
- IF @IssueDate<GETDATE() AND @IssueEnd>GETDATE()
- RETURN 1
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[MapBarRegionName] Script Date: 2018/12/13 18:28:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[MapBarRegionName]
- (
- @Id VARCHAR(6)
- )
- RETURNS NVARCHAR(20)
- BEGIN
- RETURN (SELECT MapBarName FROM dcREgion WITH(NOLOCK) WHERE ID = @ID)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[MemberType] Script Date: 2018/12/13 18:28:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2010-11-16
- --判断企业用户类型
- --######################################3
- CREATE FUNCTION [dbo].[MemberType]
- (
- @ID INT
- )
- RETURNS TINYINT
- AS
- BEGIN
- DECLARE @HasLicence AS BIT, @BalanceDate AS SMALLDATETIME, @UnlimitedDate AS SMALLDATETIME,
- @Address AS VARCHAR(100), @Name AS NVARCHAR(6), @Gender AS BIT,
- @MemberType AS TINYINT, @IsDelete AS BIT, @Mobile VARCHAR(100)
- SELECT @HasLicence = ISNULL(HasLicence, 0),
- @BalanceDate = ISNULL(BalanceDate, '1900-1-1'),
- @UnlimitedDate = ISNULL(UnlimitedDate, '1900-1-1'),
- @Address = ISNULL(Address, ''),
- @IsDelete = IsDelete
- FROM cpMain WITH(NOLOCK)
- WHERE ID = @ID
-
- SELECT @Name = Name, @Gender = Gender, @Mobile = ISNULL(Mobile, ISNULL(TelePhone, '')) FROM caMain WITH(NOLOCK)
- WHERE cpMainID = @ID AND AccountType = 1
-
- ----企业资料不完整,=0
- IF ISNULL(@Address, '') = '' OR @IsDelete = 1 OR ISNULL(@Name, '') = '' OR @Gender IS NULL OR @Mobile = ''
- SET @MemberType = 0
- ----已经上传营业执照,只能是普通会员:2、无限制:10、储值:11、vip:3之一
- ELSE IF @HasLicence = 0
- SET @MemberType = 1
- --无限制
- --ELSE IF @UnlimitedDate >= CONVERT(SMALLDATETIME, GETDATE())
- -- SET @MemberType = 10
- --储值
- --ELSE IF @BalanceDate >= CONVERT(SMALLDATETIME, GETDATE())
- -- SET @MemberType = 11
- ----检查订单
- ELSE IF EXISTS(SELECT 'x' FROM caOrder WITH(NOLOCK)
- WHERE cpMainID = @ID AND OrderType = 8
- AND BeginDate <= GETDATE() AND ENDDATE > DATEADD(MINUTE, -4, GETDATE())
- AND IsDeleted = 0
- )
- SET @MemberType = 3
- ELSE
- SET @MemberType = 2
- RETURN @MemberType
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[MemberType1] Script Date: 2018/12/13 18:28:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2010-11-16
- --判断企业用户类型
- --######################################3
- CREATE FUNCTION [dbo].[MemberType1]
- (
- @ID INT
- )
- RETURNS TINYINT
- AS
- BEGIN
- DECLARE @HasLicence AS BIT
- DECLARE @BalanceDate AS SMALLDATETIME
- DECLARE @Balance AS INT
- DECLARE @UnlimitedDate AS SMALLDATETIME
- DECLARE @Address AS VARCHAR(100)
- DECLARE @Name AS NVARCHAR(6)
- DECLARE @MemberType AS TINYINT
- DECLARE @IsDelete AS BIT
- SELECT @HasLicence = ISNULL(HasLicence, 0),
- @BalanceDate = ISNULL(BalanceDate, '1900-1-1'),
- @Balance = ISNULL(Balance, 0),
- @UnlimitedDate = ISNULL(UnlimitedDate, '1900-1-1'),
- @Address = ISNULL(Address, ''),
- @IsDelete = IsDelete
- FROM cpMain WITH(NOLOCK)
- WHERE ID = @ID
-
- SELECT @Name = Name FROM caMain WITH(NOLOCK)
- WHERE cpMainID = @ID AND AccountType = 1
- ----企业资料不完整,=0
- IF ISNULL(@Address, '') = '' OR @IsDelete = 1
- BEGIN
- SET @MemberType = 0
- GOTO ret
- END
- IF @HasLicence = 0
- BEGIN
- SET @MemberType = 1
- GOTO ret
- END
- ----已经上传营业执照,只能是普通会员:2、无限制:10、储值:11、vip:3之一
- IF @HasLicence = 1
- BEGIN
- --无限制
- IF @UnlimitedDate > GETDATE()
- BEGIN
- SET @MemberType = 10
- GOTO ret
- END
- --储值
- IF @BalanceDate > GETDATE() AND @Balance > 0
- BEGIN
- SET @MemberType = 11
- GOTO ret
- END
- ----检查订单
- IF EXISTS(SELECT 'x' FROM caOrder WITH(NOLOCK)
- WHERE cpMainID = @ID AND OrderType = 8
- AND BEGINDATE<GETDATE() AND ENDDATE > GETDATE())
- SET @MemberType = 3
- ELSE
- SET @MemberType = 2
- GOTO ret
- END
- ret:
- RETURN @MemberType
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[MergeDate] Script Date: 2018/12/13 18:28:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create function [dbo].[MergeDate]
- (
- @D SMALLDATETIME,
- @t INT
- )
- RETURNS SMALLDATETIME
- AS
- BEGIN
- DECLARE @c AS VARCHAR(30)
- SET @c = CONVERT(VARCHAR(10), @d, 120) + ' '
- IF @t < 10
- SET @c = @c + '00:0' + LTRIM(STR(@t))
- ELSE IF @t < 100
- SET @c = @c + '00:' + LTRIM(STR(@t))
- ELSE IF @t < 1000
- SET @c = @c + '0' + LEFT(@t, 1) + ':' + RIGHT(@t, 2)
- ELSE
- SET @c = @c + LEFT(@t, 2) + ':' + RIGHT(@t, 2)
- RETURN CONVERT(SMALLDATETIME, @c)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[MtNo2RtxId] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[MtNo2RtxId]
- (
- @MtNo TINYINT
- )
- RETURNS INT
- AS
- BEGIN
- IF @MtNo = 1
- RETURN 1003
- ELSE IF @MtNo = 2
- RETURN 1002
- ELSE IF @MtNo = 3
- RETURN 1004
- ELSE IF @MtNo = 8
- RETURN 1005
- ELSE IF @MtNo = 9
- RETURN 1006
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[photoPath] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[photoPath](@id INT,@fileType INT)
- RETURNS varchar(50)
- AS
- BEGIN
- DECLARE @result VARCHAR(50)
- DECLARE @path VARCHAR(50),@i INT,@photo VARCHAR(20)
- SET @i = 1
- IF @filetype <=3
- BEGIN
- SET @path = CONVERT(VARCHAR(50),((@id / 100000) + 1) * 100000)
- SET @photo = @path
- WHILE @i<=(9-LEN(@photo))
- BEGIN
- SET @path = '0' + @path
- SET @i = @i + 1
- END
- END
- ELSE
- BEGIN
- SET @path = CONVERT(VARCHAR(50),((@id / 10000) + 1) * 10000)
- SET @photo = @path
- WHILE @i<=(6-LEN(@photo))
- BEGIN
- SET @path = '0' + @path
- SET @i = @i + 1
- END
- END
- SET @path = 'L' + @path
- SET @result = @path
- RETURN @result
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[RemoveBR] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[RemoveBR]
- (
- @Str VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- RETURN REPLACE(REPLACE(@str, '<br>', ''), ' ', ' ')
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[RemoveNullChars] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[RemoveNullChars]
- (
- @string NVARCHAR(MAX)
- )
- RETURNS NVARCHAR(MAX)
- WITH RETURNS NULL ON NULL INPUT
- AS
- BEGIN
- DECLARE @Result NVARCHAR(MAX)
- SET @Result = ''
- DECLARE @counter INT
- SET @counter = 0
- WHILE (@counter <= LEN(@string))
- BEGIN
- IF UNICODE(SUBSTRING(@string, @counter,1)) <> 0
- SET @Result = @Result + SUBSTRING(@string, @counter, 1)
- SET @counter = @counter + 1
- END
- RETURN @Result
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ReplyInterval] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE function [dbo].[ReplyInterval](@adddate smalldatetime,@replydate smalldatetime)
- returns int
- as
- begin
- declare @result int
- if @replydate is null set @replydate=getdate()
- if datepart(w,@adddate)=1
- set @result=datediff(n,cast(cast(year(@adddate+1) as varchar)+'-'+cast(month(@adddate+1) as varchar)+'-'+cast(day(@adddate+1) as varchar)+' 8:30' as smalldatetime),@replydate)
- else if datepart(w,@adddate)=7
- and @adddate>cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 17:30' as smalldatetime)
- and @adddate<=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 23:59' as smalldatetime)
- set @result=datediff(n,cast(cast(year(@adddate+2) as varchar)+'-'+cast(month(@adddate+2) as varchar)+'-'+cast(day(@adddate+2) as varchar)+' 8:30' as smalldatetime),@replydate)
- else if @adddate>=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 0:0' as smalldatetime)
- and @adddate<cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 8:30' as smalldatetime)
- --没上班之前的
- set @result=datediff(n,cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 8:30' as smalldatetime),@replydate)
- else if @adddate>cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 17:30' as smalldatetime)
- and @adddate<=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 23:59' as smalldatetime) --没上班之前的
- set @result=datediff(n,cast(cast(year(@adddate+1) as varchar)+'-'+cast(month(@adddate+1) as varchar)+'-'+cast(day(@adddate+1) as varchar)+' 8:30' as smalldatetime),@replydate)
- else if @adddate>cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 10:00' as smalldatetime)
- and @adddate<=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 12:00' as smalldatetime) --10:00-12:00
- set @result=datediff(n,@adddate,@replydate)-60
- else if @adddate>cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 12:00' as smalldatetime)
- and @adddate<=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 13:00' as smalldatetime) --12:00-13:00
- set @result=datediff(n,cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 13:00' as smalldatetime),@replydate)
- else
- set @result=datediff(n,@adddate,@replydate)
- return @result
- end
- GO
- /****** Object: UserDefinedFunction [dbo].[ReportDayToDate] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Lambo 2013-2-4
- --日期转换 取多少天之前的天数 2012-1-1为1
- --######################################3
- CREATE FUNCTION [dbo].[ReportDayToDate]
- (
- @d int
- )
- RETURNS INT
- AS
- BEGIN
- RETURN DATEDIFF(DAY,'2012-1-1',GETDATE()-@d)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[SafeSQL] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#############################333
- --安全的sql语句用于exec
- --#############################333
- CREATE FUNCTION [dbo].[SafeSQL]
- (
- @sText VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- IF @sText IS NULL
- RETURN ''
- -- SET @sText = REPLACE(@sText, 'and ','and ')
- -- SET @sText = REPLACE(@sText, 'or ','or ')
- -- SET @sText = REPLACE(@sText, 'exists','exists ')
- -- SET @sText = REPLACE(@sText, 'union ','union ')
- -- SET @sText = REPLACE(@sText, '<','<')
- -- SET @sText = REPLACE(@sText, '>','>')
- -- SET @sText = REPLACE(@sText, 'from ','from ')
- -- SET @sText = REPLACE(@sText, 'select ','select ')
- SET @sText = REPLACE(@sText, '','')
- SET @sText = REPLACE(@sText, 'script','script')
- SET @sText = REPLACE(@sText, 'src','src')
- SET @sText = REPLACE(@sText, 'iframe','iframe')
- SET @sText = REPLACE(@sText, '--','——')
- SET @sText = REPLACE(@sText, 'delete ','delete ')
- SET @sText = REPLACE(@sText, 'update ','update ')
- SET @sText = REPLACE(@sText, 'delete ','delete ')
- SET @sText = REPLACE(@sText, 'update ','update ')
- SET @sText = REPLACE(@sText, 'delete
- ','delete ')
- SET @sText = REPLACE(@sText, 'update
- ','update ')
- SET @sText = REPLACE(@sText, 'create','create ')
- SET @sText = REPLACE(@sText, 'drop','drop ')
- SET @sText = REPLACE(@sText, 'insert','insert ')
- SET @sText = REPLACE(@sText, 'chr(','chr( ')
- SET @sText = REPLACE(@sText, 'master','master ')
- SET @sText = REPLACE(@sText, 'net user','net user')
- SET @sText = REPLACE(@sText, 'declare','declare ')
- SET @sText = REPLACE(@sText, 'char(','char( ')
- SET @sText = REPLACE(@sText, 'mid(','mid( ')
- SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell ')
- SET @sText = REPLACE(@sText, 'exec','exec ')
- SET @sText = REPLACE(@sText, 'truncate','truncate ')
- SET @sText = REPLACE(@sText, 'ascii(','ascii( ')
- SET @sText = REPLACE(@sText, 'substring(','substring( ')
- SET @sText = REPLACE(@sText, 'localgroup ','localgroup ')
- SET @sText = REPLACE(@sText, 'administrators ','administrators ')
- SET @sText = REPLACE(@sText, 'dbcc','dbcc ')
- SET @sText = REPLACE(@sText, 'checkdb(','checkdb( ')
- SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember ')
- SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell ')
- RETURN @sText
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[SafeSQLBack] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#############################333
- --安全的sql语句用于exec
- --#############################333
- CREATE FUNCTION [dbo].[SafeSQLBack]
- (
- @sText VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- IF @sText IS NULL
- RETURN ''
- -- SET @sText = REPLACE(@sText, 'and ','and ')
- -- SET @sText = REPLACE(@sText, 'or ','or ')
- -- SET @sText = REPLACE(@sText, 'from ','from ')
- -- SET @sText = REPLACE(@sText, 'exists','exists ')
- -- SET @sText = REPLACE(@sText, 'select ','select ')
- -- SET @sText = REPLACE(@sText, 'union ','union ')
- -- SET @sText = REPLACE(@sText, '<','<')
- -- SET @sText = REPLACE(@sText, '>','>')
- SET @sText = REPLACE(@sText, '','')
- SET @sText = REPLACE(@sText, 'script','script')
- SET @sText = REPLACE(@sText, 'src','src')
- SET @sText = REPLACE(@sText, 'iframe','iframe')
- SET @sText = REPLACE(@sText, '--','——')
- SET @sText = REPLACE(@sText, 'delete ','delete ')
- SET @sText = REPLACE(@sText, 'update ','update ')
- SET @sText = REPLACE(@sText, 'delete ','delete ')
- SET @sText = REPLACE(@sText, 'update ','update ')
- SET @sText = REPLACE(@sText, 'delete
- ','delete ')
- SET @sText = REPLACE(@sText, 'update
- ','update ')
- SET @sText = REPLACE(@sText, 'create ','create ')
- SET @sText = REPLACE(@sText, 'drop ','drop ')
- SET @sText = REPLACE(@sText, 'insert ','insert ')
- SET @sText = REPLACE(@sText, 'chr(','chr( ')
- SET @sText = REPLACE(@sText, 'master ','master ')
- SET @sText = REPLACE(@sText, 'net user','net user')
- SET @sText = REPLACE(@sText, 'declare ','declare ')
- SET @sText = REPLACE(@sText, 'char(','char( ')
- SET @sText = REPLACE(@sText, 'mid(','mid( ')
- --SET @sText = REPLACE(@sText, 'count ','count ')
- SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell ')
- SET @sText = REPLACE(@sText, 'exec ','exec ')
- SET @sText = REPLACE(@sText, 'truncate ','truncate ')
- SET @sText = REPLACE(@sText, 'ascii(','ascii( ')
- SET @sText = REPLACE(@sText, 'substring(','substring( ')
- SET @sText = REPLACE(@sText, 'localgroup ','localgroup ')
- SET @sText = REPLACE(@sText, 'administrators ','administrators ')
- SET @sText = REPLACE(@sText, 'dbcc ','dbcc ')
- SET @sText = REPLACE(@sText, 'checkdb(','checkdb( ')
- SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember ')
- SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell ')
- RETURN @sText
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ScheduleRunTime] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE Function [dbo].[ScheduleRunTime]
- (
- @RunDate INT,
- @RunTime INT
- )
- RETURNS DATETIME
- AS
- BEGIN
- DECLARE @time AS VARCHAR(30)
- SET @Time = LTRIM(STR(@RunTime / 10000)) + ':'
- SET @RunTime = @RunTime % 10000
- SET @Time = @Time + LTRIM(STR(@RunTime / 100)) + ':00'
- RETURN CONVERT(DATETIME, LTRIM(STR(@RunDate)) + ' ' + @Time)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[schGetCaCvExChange] Script Date: 2018/12/13 18:28:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[schGetCaCvExChange]
- (
- @caMainID INT,
- @DateType TINYINT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @JobID INT,@JobName NVARCHAR(50),@Count INT,@StartTime VARCHAR(20),
- @EndTime VARCHAR(20),@Today VARCHAR(10),@Content VARCHAR(MAX)
- SET @Content = ''
- IF @DateType = 1
- BEGIN
- IF DATEPART(HOUR,GETDATE()) > 0 AND DATEPART(HOUR,GETDATE()) < 9
- BEGIN
- SET @StartTime = CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),120) + ' 00:00:00'
- SET @EndTime = CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),120) + ' 23:59:59'
- SET @Today = CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),120)
- END
- ELSE
- BEGIN
- SET @StartTime = CONVERT(VARCHAR(10),GETDATE(),120) + ' 00:00:00'
- SET @EndTime = CONVERT(VARCHAR(10),GETDATE(),120) + ' 23:59:59'
- SET @Today = CONVERT(VARCHAR(10),GETDATE(),120)
- END
- END
- ELSE
- BEGIN
- SET @StartTime = CONVERT(VARCHAR(10),DATEADD(ww,-1,GETDATE()),120) + ' 00:00:00'
- SET @EndTime = CONVERT(VARCHAR(10),GETDATE(),120) + ' 23:59:59'
- SET @Today = CONVERT(VARCHAR(10),DATEADD(ww,-1,GETDATE()),120)
- END
- SELECT @Count=COUNT(1) FROM exMessageP2C WITH(NOLOCK) WHERE caMainID=@caMainID AND AddDate>CONVERT(SMALLDATETIME,@StartTime) And AddDate<CONVERT(SMALLDATETIME,@EndTime)
- IF @Count > 0
- SET @Content = @Content + '<br> 您总共收到了' + CONVERT(VARCHAR,@Count) + '条求职者发来的新留言;'
-
- DECLARE curJob CURSOR FOR SELECT TOP 10 ID,Name FROM Job WITH(NOLOCK) WHERE Valid=1 AND caMainID=@caMainID
- OPEN curJob
- FETCH NEXT FROM curJob INTO @JobID,@JobName
- IF @@FETCH_STATUS >= 0
- BEGIN
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SELECT @Count=COUNT(1) FROM exJobApply a WITH(NOLOCK),cvMain b WITH(NOLOCK)
- WHERE a.cvMainID=b.ID AND b.VerifyResult=1 AND a.JobID=@JobID
- AND a.AddDate<CONVERT(SMALLDATETIME,@EndTime) AND a.AddDate>CONVERT(SMALLDATETIME,@StartTime)
- IF @Count > 0
- SET @Content = @Content + '<br> 有' + CONVERT(VARCHAR,@Count) + '个求职者给[' + @JobName + ']投递了简历;'
- FETCH NEXT FROM curJob INTO @JobID,@JobName
- END
- END
- CLOSE curJob
- DEALLOCATE curJob
- IF LEN(@Content) > 0
- BEGIN
- IF @DateType = 1
- SET @Content = '在' + @Today + '的24小时内:' + @Content
- ELSE
- SET @Content = '在' + CONVERT(VARCHAR(10),@StartTime) + '至' + CONVERT(VARCHAR(10),@EndTime) + '的一周内:' + @Content
- END
- RETURN @Content
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[schGetOrderDesc] Script Date: 2018/12/13 18:28:30 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[schGetOrderDesc]
- (
- @ServiceType SMALLINT,
- @OrderType SMALLINT,
- @Paid NUMERIC(18,2),
- @TimeTitle VARCHAR(500),
- @ActiveQuota INT,
- @JobNumber INT,
- @UserNumber INT,
- @PayMethod SMALLINT,
- @Receiver VARCHAR(200),
- @Account VARCHAR(200),
- @BankName VARCHAR(200),
- @NeedInvoice BIT,
- @OrderID INT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @tmp@ServiceType NVARCHAR(20)
- DECLARE @strOrderDesc NVARCHAR(500)
- SET @strOrderDesc = ''
- SET @tmp@ServiceType = ''
- If @ServiceType = 100
- SET @tmp@ServiceType = '一周'
- If @ServiceType = 1
- SET @tmp@ServiceType = '一个月'
- If @ServiceType = 2
- SET @tmp@ServiceType = '一个季度'
- If @ServiceType = 3
- SET @tmp@ServiceType = '半年'
- If @ServiceType = 4
- SET @tmp@ServiceType = '一年'
- If @OrderType = 1
- SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为申请' + @tmp@ServiceType + '正式会员,费用为' + convert(varchar(10),@Paid) + '元,'
- If @OrderType = 2
- SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为申请储值会员,加值金额为' + convert(varchar(10),@Paid) + '元,'
- If @OrderType = 4
- SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为申请广告位,费用为' + convert(varchar(10),@Paid) + '元,'
- If @OrderType = 7
- SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为首页知名企业招聘,费用为' + convert(varchar(10),@Paid) + '元,'
- If @OrderType = 8
- SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为申请' + @TimeTitle + 'VIP套餐服务,费用为' + convert(varchar(10),@Paid) + '元,'
- If @OrderType = 9
- SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为购买' + LTRIM(STR(@ActiveQuota)) + '个简历下载数,费用为' + convert(varchar(10),@Paid) + '元,'
- If @OrderType = 10
- SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为购买' + LTRIM(STR(@JobNumber)) + '个职位并发数,费用为' + convert(varchar(10),@Paid) + '元,'
- If @OrderType = 11
- SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为购买' + LTRIM(STR(@UserNumber)) + '个用户数,费用为' + convert(varchar(10),@Paid) + '元,'
- SET @strOrderDesc = @strOrderDesc + '为方便您能够及时开通服务,现将付款流程发送给您,如下:<br>'
- If @PayMethod = 1
- Begin
- SET @strOrderDesc = @strOrderDesc + '1、对公汇款<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 汇款用途:会员服务费,订单号' + LTRIM(STR(@OrderID)) + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、将银行电汇回单传真至:010-65800851 或者 021-61916451<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在银行电汇回单传真、营业执照齐全后',@NeedInvoice)
- End
- If @PayMethod = 2
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、邮局汇款<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 通信地址:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 邮政编码:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 附言:订单号' + LTRIM(STR(@OrderID)) + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、在邮政汇款收据上,标上订单号,并传真至:010-65800851 或者 021-61916451<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在邮政汇款收据传真、营业执照齐全后',@NeedInvoice)
- End
- If @PayMethod = 6
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '请前来付费:<br>'
- SET @strOrderDesc = @strOrderDesc + ' ' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 地址:' + @BankName + '<br>'
- End
- If @PayMethod = 7
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、工商银行<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
- End
- If @PayMethod = 9
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、招商银行<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
- End
- If @PayMethod = 10
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、农业银行<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
- End
- If @PayMethod = 19
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、支付宝支付(支持全国银行卡,网上付款,自动开通)<br>'
- SET @strOrderDesc = @strOrderDesc + ' 请到网站在线支付,'
- If @OrderType = 1 Or @OrderType = 2 Or @OrderType = 5 Or @OrderType = 8 Or @OrderType = 9 Or @OrderType = 10 Or @OrderType = 11
- SET @strOrderDesc = @strOrderDesc + '支付完成后,自动开通。' + '<br>'
- If @OrderType = 4
- SET @strOrderDesc = @strOrderDesc + '支付完成后,工作日10分钟之内我们电话与您联系广告制作与播放。' + '<br>'
- If @OrderType = 7
- SET @strOrderDesc = @strOrderDesc + '4、确认收到汇款后,工作日10分钟之内您的单位名称出现在“首页知名企业招聘”。' + '<br>'
- If @NeedInvoice = 1
- SET @strOrderDesc = @strOrderDesc + '汇款到帐后您可以索取发票,在三个工作日内,将您的发票挂号信寄出。<br>'
- End
- If @PayMethod = 12
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、建设银行<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
- End
- If @PayMethod = 13
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、中国银行<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
- End
- If @PayMethod = 14
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、交通银行<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
- End
-
- If @PayMethod = 17
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、转帐支票<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 汇款用途:会员服务费,订单号' + LTRIM(STR(@OrderID)) + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、您可以将支票快递给我们,也可以将支票亲自送过来。<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在收到转帐支票、营业执照齐全后',@NeedInvoice)
- End
-
- If @PayMethod = 18
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、同城转帐<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 汇款用途:会员服务费,订单号' + LTRIM(STR(@OrderID)) + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、将同城转帐回单传真至:010-65800851 或者 021-61916451<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在同城转帐回单传真、营业执照齐全后',@NeedInvoice)
- End
-
- If @PayMethod = 20
- BEGIN
- SET @strOrderDesc = @strOrderDesc + '1、邮政储蓄银行<br>'
- SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + '<br>'
- SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + '<br>'
- SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
- SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
- End
- RETURN @strOrderDesc
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[schGetPayDesc] Script Date: 2018/12/13 18:28:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[schGetPayDesc]
- (
- @OrderType TINYINT,
- @strDesc NVARCHAR(200),
- @NeedInvoice BIT
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @PayDesc NVARCHAR(500)
- SET @PayDesc = '3、如果贵单位还没有通过营业执照审核,请先上传营业执照,或者传真到 010-65800851 或者 021-61916451<br>'
- If @OrderType = 1 Or @OrderType = 2 Or @OrderType = 8 Or @OrderType = 9 Or @OrderType = 10 Or @OrderType = 11
- SET @PayDesc = @PayDesc + '4、' + @strDesc + ',工作日10分钟之内开通,并通过E-mail通知。<br>'
- If @OrderType = 4
- SET @PayDesc = @PayDesc + '4、' + @strDesc + ',工作日10分钟之内我们电话与您联系广告制作与播放。<br>'
- If @OrderType = 7
- SET @PayDesc = @PayDesc + '4、确认收到汇款后,工作日10分钟之内您的单位名称出现在“首页知名企业招聘”。<br>'
- If @NeedInvoice = 1
- SET @PayDesc = @PayDesc + '5、汇款到帐后您可以索取发票,在三个工作日内,将您的发票挂号信寄出。<br>'
- RETURN @PayDesc
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[SHA1] Script Date: 2018/12/13 18:28:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create FUNCTION [dbo].[SHA1]
- (
- @Str VARCHAR(40)
- )
- RETURNS VARCHAR(40)
- AS
- BEGIN
- RETURN RIGHT(SYS.fn_VarBinToHexStr(hashbytes('SHA1', LOWER(@Str))), 40)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ShowContact] Script Date: 2018/12/13 18:28:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[ShowContact]
- (
- @cvMainID INT,
- @cpMainID INT
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @cpMemeberType INT
- SELECT @cpMemeberType = MemberType FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID
- IF @cpMemeberType IN(3, 10)
- RETURN 1
- ELSE IF @cpMemeberType = 11
- BEGIN
- BEGIN
- IF EXISTS(
- SELECT 'x' FROM caBalanceLog WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID
- AND CONVERT(VARCHAR(100), AddDate, 23) = CONVERT(VARCHAR(100), GETDATE(), 23)
- ) OR EXISTS(
- SELECT 'x' FROM CaCvQuotaLog WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID
- )
- RETURN 1
- END
- END
- ELSE
- BEGIN
- DECLARE @isInActiveLog INT
- DECLARE @isInDailyGiftLog INT
- SELECT @isInDailyGiftLog = count(1) FROM caDailyGiftLog WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID
- IF @isInDailyGiftLog > 0
- RETURN 1
- SELECT @isInActiveLog = count(1) FROM caActiveLog WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID
- If @isInActiveLog > 0
- RETURN 1
- IF EXISTS (
- SELECT 'x' FROM ExJobApply a WITH(NOLOCK),Job b
- WHERE a.JobID = b.ID
- AND a.IsPassed = 1
- AND a.cvMainID = @cvMainID
- AND b.cpMainID = @cpMainID
- )
- RETURN 1
- END
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ShowContactByExcel] Script Date: 2018/12/13 18:28:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[ShowContactByExcel]
- (
- @cvMainID INT,
- @cpMainID INT
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @cpMemeberType INT
- SELECT @cpMemeberType = MemberType FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID
- IF @cpMemeberType = 10 Or @cpMemeberType = 11
- BEGIN
- IF @cpMemeberType = 10
- RETURN 1
- ELSE
- BEGIN
- IF EXISTS(
- SELECT 'x' FROM caBalanceLog WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID
- AND CONVERT(VARCHAR(100), AddDate, 23) = CONVERT(VARCHAR(100), GETDATE(), 23)
- )
- RETURN 1
- END
- END
- ELSE
- BEGIN
- DECLARE @isInActiveLog INT
- DECLARE @isInDailyGiftLog INT
- SELECT @isInDailyGiftLog = count(1) FROM caDailyGiftLog WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID
- IF @isInDailyGiftLog > 0
- RETURN 1
- SELECT @isInActiveLog = count(1) FROM caActiveLog WITH(NOLOCK)
- WHERE cpMainID = @cpMainID
- AND cvMainID = @cvMainID
- If @isInActiveLog > 0
- RETURN 1
- IF EXISTS (
- SELECT 'x' FROM cvMain
- WHERE ID = @cvMainID
- AND IsOpen = 1
- )
- RETURN 1
- END
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[StripHTML] Script Date: 2018/12/13 18:28:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[StripHTML]
- (
- @HTMLText VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @Start INT
- DECLARE @End INT
- DECLARE @Length INT
- SET @HTMLText = REPLACE(@HTMLText, '&nbsp;', ' ')
- SET @HTMLText = REPLACE(@HTMLText, ' ', ' ')
- --SET @HTMLText = REPLACE(LTRIM(RTRIM(@HTMLText)), ' ', ' ')
- SET @HTMLText = REPLACE(@HTMLText, '<br>'+Char(13)+Char(10), Char(13)+Char(10))
- SET @HTMLText = REPLACE(@HTMLText, '<br >'+Char(13)+Char(10), Char(13)+Char(10))
- SET @HTMLText = REPLACE(@HTMLText, '<br />'+Char(13)+Char(10), Char(13)+Char(10))
- --SET @HTMLText = REPLACE(@HTMLText, '</p>'+Char(13)+Char(10), '')
- --SET @HTMLText = REPLACE(@HTMLText, '</div>'+Char(13)+Char(10), '')
- SET @HTMLText = REPLACE(@HTMLText, '<br>'+Char(13), Char(13)+Char(10))
- SET @HTMLText = REPLACE(@HTMLText, '<br >'+Char(13), Char(13)+Char(10))
- SET @HTMLText = REPLACE(@HTMLText, '<br />'+Char(13), Char(13)+Char(10))
- SET @HTMLText = REPLACE(@HTMLText, '<br>'+Char(10), Char(13)+Char(10))
- SET @HTMLText = REPLACE(@HTMLText, '<br >'+Char(10), Char(13)+Char(10))
- SET @HTMLText = REPLACE(@HTMLText, '<br />'+Char(10), Char(13)+Char(10))
- --SET @HTMLText = REPLACE(@HTMLText, '</p>'+Char(10), '')
- --SET @HTMLText = REPLACE(@HTMLText, '</div>'+Char(10), '')
- SET @HTMLText = REPLACE(@HTMLText, '<br>', Char(13)+Char(10))
- SET @HTMLText = REPLACE(@HTMLText, '<br >', Char(13)+Char(10))
- SET @HTMLText = REPLACE(@HTMLText, '<br />', Char(13)+Char(10))
- --SET @HTMLText = REPLACE(@HTMLText, '</p>', '')
- --SET @HTMLText = REPLACE(@HTMLText, '</div>', '')
- /*
- SET @Start = dbo.HtmlPos(@HTMLText)
- SET @End = CHARINDEX('>', @HTMLText, @Start)
- SET @Length = (@End - @Start) + 1
- WHILE @Start > 0 AND @End > 0 AND @Length > 0
- BEGIN
- SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
- SET @Start = dbo.HtmlPos(@HTMLText)
- SET @End = CHARINDEX('>', @HTMLText, @Start)
- SET @Length = (@End - @Start) + 1
- END
- SET @HTMLText = REPLACE(@HTMLText, '<', '<')
- SET @HTMLText = REPLACE(@HTMLText, '>', '>')
- */
- RETURN RTRIM(@HTMLText)
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[TextTrim] Script Date: 2018/12/13 18:28:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#############################333
- --文本框前导4个空格
- --#############################333
- CREATE FUNCTION [dbo].[TextTrim]
- (
- @sText VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- IF LTRIM(@sText) = ''
- RETURN NULL
- SET @sText = REPLACE(RTRIM(@sText), ' ', ' ')
- IF LEFT(@sText , 4) = ' '
- SET @sText = ' ' + LTRIM(@sText)
- RETURN @sText
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[TheMonth] Script Date: 2018/12/13 18:28:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[TheMonth]
- (
- @CountDate INT
- )
- RETURNS INT
- AS
- BEGIN
- DECLARE @Result INT
- IF CAST(RIGHT(STR(@CountDate), 2) AS INT) >= 26
- BEGIN
- DECLARE @dd SMALLDATETIME
- SET @dd = DATEADD(m, 1, LEFT(LTRIM(RTRIM(STR(@CountDate))),4) + '-' + RIGHT(LEFT(LTRIM(RTRIM(STR(@CountDate))),6),2)+'-1 00:00')
- SET @Result=YEAR(@dd)*100+MONTH(@dd)
- END
- ELSE
- BEGIN
- SET @Result=@CountDate/100
- END
- RETURN @Result
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ToJianPin] Script Date: 2018/12/13 18:28:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[ToJianPin](@Words NVARCHAR(2000))
- RETURNS VARCHAR(8000)
- AS
- BEGIN
- DECLARE @Word nchar(1)
- DECLARE @PinYin VARCHAR(8000)
- DECLARE @Index int
- DECLARE @WordLenth int
- DECLARE @Unicode int
- SET @Index = 1
- SET @Words = LTRIM(RTRIM(@Words))
- SET @WordLenth = LEN(@Words)
- WHILE (@Index <= @WordLenth) --循环取字符
- BEGIN
- SET @Word = SUBSTRING(@Words, @Index, 1)
- SET @Unicode = UNICODE(@Word)
- SET @PinYin = ISNULL(@PinYin,'')+
- (
- CASE WHEN UNICODE(@Word) BETWEEN 19968 AND 19968+20901 THEN
- (
- SELECT TOP 1 PinYin FROM
- (
- SELECT 'a' AS PinYin, '驁' AS Word
- UNION ALL SELECT 'b', '簿'
- UNION ALL SELECT 'c', '錯'
- UNION ALL SELECT 'd', '鵽'
- UNION ALL SELECT 'e', '樲'
- UNION ALL SELECT 'f', '猤'
- UNION ALL SELECT 'g', '腂'
- UNION ALL SELECT 'h', '夻'
- UNION ALL SELECT 'j', '攈'
- UNION ALL SELECT 'k', '穒'
- UNION ALL SELECT 'l', '鱳'
- UNION ALL SELECT 'm', '旀'
- UNION ALL SELECT 'n', '桛'
- UNION ALL SELECT 'o', '漚'
- UNION ALL SELECT 'p', '曝'
- UNION ALL SELECT 'q', '囕'
- UNION ALL SELECT 'r', '鶸'
- UNION ALL SELECT 's', '蜶'
- UNION ALL SELECT 't', '籜'
- UNION ALL SELECT 'w', '鶩'
- UNION ALL SELECT 'x', '鑂'
- UNION ALL SELECT 'y', '韻'
- UNION ALL SELECT 'z', '咗'
- ) Temp
- WHERE Word >= @Word COLLATE Chinese_PRC_CS_AS_KS_WS
- ORDER BY Word COLLATE Chinese_PRC_CS_AS_KS_WS ASC
- )
- ELSE @Word END
- )
- SET @Index = @Index + 1
- END
- RETURN @PinYin
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ToPinYin] Script Date: 2018/12/13 18:28:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- create FUNCTION [dbo].[ToPinYin](@Words NVARCHAR(2000))
- RETURNS VARCHAR(8000)
- AS
- BEGIN
- DECLARE @Word nchar(1)
- DECLARE @PinYin VARCHAR(8000)
- DECLARE @Index int
- DECLARE @WordLenth int
- DECLARE @Unicode int
- SET @Index = 1
- SET @Words = LTRIM(RTRIM(@Words))
- SET @WordLenth = LEN(@Words)
- WHILE (@Index <= @WordLenth) --循环取字符
- BEGIN
- SET @Word = SUBSTRING(@Words, @Index, 1)
- SET @Unicode = UNICODE(@Word)
- SET @PinYin = ISNULL(@PinYin,'')+
- (
- CASE WHEN UNICODE(@Word) BETWEEN 19968 AND 19968+20901 THEN
- (
- SELECT TOP 1 PinYin FROM
- (
- SELECT 'a' AS PinYin,N'厑' AS Word
- UNION ALL SELECT 'ai',N'靉'
- UNION ALL SELECT 'an',N'黯'
- UNION ALL SELECT 'ang',N'醠'
- UNION ALL SELECT 'ao',N'驁'
- UNION ALL SELECT 'ba',N'欛'
- UNION ALL SELECT 'bai',N'瓸'
- UNION ALL SELECT 'ban',N'瓣'
- UNION ALL SELECT 'bang',N'鎊'
- UNION ALL SELECT 'bao',N'鑤'
- UNION ALL SELECT 'bei',N'鐾'
- UNION ALL SELECT 'ben',N'輽'
- UNION ALL SELECT 'beng',N'鏰'
- UNION ALL SELECT 'bi',N'鼊'
- UNION ALL SELECT 'bian',N'變'
- UNION ALL SELECT 'biao',N'鰾'
- UNION ALL SELECT 'bie',N'彆'
- UNION ALL SELECT 'bin',N'鬢'
- UNION ALL SELECT 'bing',N'靐'
- UNION ALL SELECT 'bo',N'蔔'
- UNION ALL SELECT 'bu',N'簿'
- UNION ALL SELECT 'ca',N'囃'
- UNION ALL SELECT 'cai',N'乲'
- UNION ALL SELECT 'can',N'爘'
- UNION ALL SELECT 'cang',N'賶'
- UNION ALL SELECT 'cao',N'鼜'
- UNION ALL SELECT 'ce',N'簎'
- UNION ALL SELECT 'cen',N'笒'
- UNION ALL SELECT 'ceng',N'乽' --硛硳岾猠乽
- UNION ALL SELECT 'cha',N'詫'
- UNION ALL SELECT 'chai',N'囆'
- UNION ALL SELECT 'chan',N'顫'
- UNION ALL SELECT 'chang',N'韔'
- UNION ALL SELECT 'chao',N'觘'
- UNION ALL SELECT 'che',N'爡'
- UNION ALL SELECT 'chen',N'讖'
- UNION ALL SELECT 'cheng',N'秤'
- UNION ALL SELECT 'chi',N'鷘'
- UNION ALL SELECT 'chong',N'銃'
- UNION ALL SELECT 'chou',N'殠'
- UNION ALL SELECT 'chu',N'矗'
- UNION ALL SELECT 'chuai',N'踹'
- UNION ALL SELECT 'chuan',N'鶨'
- UNION ALL SELECT 'chuang',N'愴'
- UNION ALL SELECT 'chui',N'顀'
- UNION ALL SELECT 'chun',N'蠢'
- UNION ALL SELECT 'chuo',N'縒'
- UNION ALL SELECT 'ci',N'嗭' --賜嗭
- UNION ALL SELECT 'cong',N'謥'
- UNION ALL SELECT 'cou',N'輳'
- UNION ALL SELECT 'cu',N'顣'
- UNION ALL SELECT 'cuan',N'爨'
- UNION ALL SELECT 'cui',N'臎'
- UNION ALL SELECT 'cun',N'籿'
- UNION ALL SELECT 'cuo',N'錯'
- UNION ALL SELECT 'da',N'橽'
- UNION ALL SELECT 'dai',N'靆'
- UNION ALL SELECT 'dan',N'饏'
- UNION ALL SELECT 'dang',N'闣'
- UNION ALL SELECT 'dao',N'纛'
- UNION ALL SELECT 'de',N'的'
- UNION ALL SELECT 'den',N'扽'
- UNION ALL SELECT 'deng',N'鐙'
- UNION ALL SELECT 'di',N'螮'
- UNION ALL SELECT 'dia',N'嗲'
- UNION ALL SELECT 'dian',N'驔'
- UNION ALL SELECT 'diao',N'鑃'
- UNION ALL SELECT 'die',N'嚸' --眰嚸
- UNION ALL SELECT 'ding',N'顁'
- UNION ALL SELECT 'diu',N'銩'
- UNION ALL SELECT 'dong',N'霘'
- UNION ALL SELECT 'dou',N'鬭'
- UNION ALL SELECT 'du',N'蠹'
- UNION ALL SELECT 'duan',N'叾' --籪叾
- UNION ALL SELECT 'dui',N'譵'
- UNION ALL SELECT 'dun',N'踲'
- UNION ALL SELECT 'duo',N'鵽'
- UNION ALL SELECT 'e',N'鱷'
- UNION ALL SELECT 'en',N'摁'
- UNION ALL SELECT 'eng',N'鞥'
- UNION ALL SELECT 'er',N'樲'
- UNION ALL SELECT 'fa',N'髮'
- UNION ALL SELECT 'fan',N'瀪'
- UNION ALL SELECT 'fang',N'放'
- UNION ALL SELECT 'fei',N'靅'
- UNION ALL SELECT 'fen',N'鱝'
- UNION ALL SELECT 'feng',N'覅'
- UNION ALL SELECT 'fo',N'梻'
- UNION ALL SELECT 'fou',N'鴀'
- UNION ALL SELECT 'fu',N'猤' --鰒猤
- UNION ALL SELECT 'ga',N'魀'
- UNION ALL SELECT 'gai',N'瓂'
- UNION ALL SELECT 'gan',N'灨'
- UNION ALL SELECT 'gang',N'戇'
- UNION ALL SELECT 'gao',N'鋯'
- UNION ALL SELECT 'ge',N'獦'
- UNION ALL SELECT 'gei',N'給'
- UNION ALL SELECT 'gen',N'搄'
- UNION ALL SELECT 'geng',N'堩' --亙堩啹喼嗰
- UNION ALL SELECT 'gong',N'兣' --熕贑兝兣
- UNION ALL SELECT 'gou',N'購'
- UNION ALL SELECT 'gu',N'顧'
- UNION ALL SELECT 'gua',N'詿'
- UNION ALL SELECT 'guai',N'恠'
- UNION ALL SELECT 'guan',N'鱹'
- UNION ALL SELECT 'guang',N'撗'
- UNION ALL SELECT 'gui',N'鱥'
- UNION ALL SELECT 'gun',N'謴'
- UNION ALL SELECT 'guo',N'腂'
- UNION ALL SELECT 'ha',N'哈'
- UNION ALL SELECT 'hai',N'饚'
- UNION ALL SELECT 'han',N'鶾'
- UNION ALL SELECT 'hang',N'沆'
- UNION ALL SELECT 'hao',N'兞'
- UNION ALL SELECT 'he',N'靏'
- UNION ALL SELECT 'hei',N'嬒'
- UNION ALL SELECT 'hen',N'恨'
- UNION ALL SELECT 'heng',N'堼' --堼囍
- UNION ALL SELECT 'hong',N'鬨'
- UNION ALL SELECT 'hou',N'鱟'
- UNION ALL SELECT 'hu',N'鸌'
- UNION ALL SELECT 'hua',N'蘳'
- UNION ALL SELECT 'huai',N'蘾'
- UNION ALL SELECT 'huan',N'鰀'
- UNION ALL SELECT 'huang',N'鎤'
- UNION ALL SELECT 'hui',N'顪'
- UNION ALL SELECT 'hun',N'諢'
- UNION ALL SELECT 'huo',N'夻'
- UNION ALL SELECT 'ji',N'驥'
- UNION ALL SELECT 'jia',N'嗧'
- UNION ALL SELECT 'jian',N'鑳'
- UNION ALL SELECT 'jiang',N'謽'
- UNION ALL SELECT 'jiao',N'釂'
- UNION ALL SELECT 'jie',N'繲'
- UNION ALL SELECT 'jin',N'齽'
- UNION ALL SELECT 'jing',N'竸'
- UNION ALL SELECT 'jiong',N'蘔'
- UNION ALL SELECT 'jiu',N'欍'
- UNION ALL SELECT 'ju',N'爠'
- UNION ALL SELECT 'juan',N'羂'
- UNION ALL SELECT 'jue',N'钁'
- UNION ALL SELECT 'jun',N'攈'
- UNION ALL SELECT 'ka',N'鉲'
- UNION ALL SELECT 'kai',N'乫' --鎎乫
- UNION ALL SELECT 'kan',N'矙'
- UNION ALL SELECT 'kang',N'閌'
- UNION ALL SELECT 'kao',N'鯌'
- UNION ALL SELECT 'ke',N'騍'
- UNION ALL SELECT 'ken',N'褃'
- UNION ALL SELECT 'keng',N'鏗' --巪乬唟厼怾
- UNION ALL SELECT 'kong',N'廤'
- UNION ALL SELECT 'kou',N'鷇'
- UNION ALL SELECT 'ku',N'嚳'
- UNION ALL SELECT 'kua',N'骻'
- UNION ALL SELECT 'kuai',N'鱠'
- UNION ALL SELECT 'kuan',N'窾'
- UNION ALL SELECT 'kuang',N'鑛'
- UNION ALL SELECT 'kui',N'鑎'
- UNION ALL SELECT 'kun',N'睏'
- UNION ALL SELECT 'kuo',N'穒'
- UNION ALL SELECT 'la',N'鞡'
- UNION ALL SELECT 'lai',N'籟'
- UNION ALL SELECT 'lan',N'糷'
- UNION ALL SELECT 'lang',N'唥'
- UNION ALL SELECT 'lao',N'軂'
- UNION ALL SELECT 'le',N'餎'
- UNION ALL SELECT 'lei',N'脷' --嘞脷
- UNION ALL SELECT 'leng',N'睖'
- UNION ALL SELECT 'li',N'瓈'
- UNION ALL SELECT 'lia',N'倆'
- UNION ALL SELECT 'lian',N'纞'
- UNION ALL SELECT 'liang',N'鍄'
- UNION ALL SELECT 'liao',N'瞭'
- UNION ALL SELECT 'lie',N'鱲'
- UNION ALL SELECT 'lin',N'轥' --轥拎
- UNION ALL SELECT 'ling',N'炩'
- UNION ALL SELECT 'liu',N'咯' --瓼甅囖咯
- UNION ALL SELECT 'long',N'贚'
- UNION ALL SELECT 'lou',N'鏤'
- UNION ALL SELECT 'lu',N'氇'
- UNION ALL SELECT 'lv',N'鑢'
- UNION ALL SELECT 'luan',N'亂'
- UNION ALL SELECT 'lue',N'擽'
- UNION ALL SELECT 'lun',N'論'
- UNION ALL SELECT 'luo',N'鱳'
- UNION ALL SELECT 'ma',N'嘛'
- UNION ALL SELECT 'mai',N'霢'
- UNION ALL SELECT 'man',N'蘰'
- UNION ALL SELECT 'mang',N'蠎'
- UNION ALL SELECT 'mao',N'唜'
- UNION ALL SELECT 'me',N'癦' --癦呅
- UNION ALL SELECT 'mei',N'嚜'
- UNION ALL SELECT 'men',N'們'
- UNION ALL SELECT 'meng',N'霥' --霿踎
- UNION ALL SELECT 'mi',N'羃'
- UNION ALL SELECT 'mian',N'麵'
- UNION ALL SELECT 'miao',N'廟'
- UNION ALL SELECT 'mie',N'鱴' --鱴瓱
- UNION ALL SELECT 'min',N'鰵'
- UNION ALL SELECT 'ming',N'詺'
- UNION ALL SELECT 'miu',N'謬'
- UNION ALL SELECT 'mo',N'耱' --耱乮
- UNION ALL SELECT 'mou',N'麰' --麰蟱
- UNION ALL SELECT 'mu',N'旀'
- UNION ALL SELECT 'na',N'魶'
- UNION ALL SELECT 'nai',N'錼'
- UNION ALL SELECT 'nan',N'婻'
- UNION ALL SELECT 'nang',N'齉'
- UNION ALL SELECT 'nao',N'臑'
- UNION ALL SELECT 'ne',N'呢'
- UNION ALL SELECT 'nei',N'内' --嫩焾
- UNION ALL SELECT 'nen',N'嫩'
- UNION ALL SELECT 'neng',N'能' --莻嗯鈪銰啱
- UNION ALL SELECT 'ni',N'嬺'
- UNION ALL SELECT 'nian',N'艌'
- UNION ALL SELECT 'niang',N'釀'
- UNION ALL SELECT 'niao',N'脲'
- UNION ALL SELECT 'nie',N'钀'
- UNION ALL SELECT 'nin',N'拰'
- UNION ALL SELECT 'ning',N'濘'
- UNION ALL SELECT 'niu',N'靵'
- UNION ALL SELECT 'nong',N'齈'
- UNION ALL SELECT 'nou',N'譳'
- UNION ALL SELECT 'nu',N'搙'
- UNION ALL SELECT 'nv',N'衄'
- UNION ALL SELECT 'nue',N'瘧'
- UNION ALL SELECT 'nuan',N'燶' --硸黁燶郍
- UNION ALL SELECT 'nuo',N'桛'
- UNION ALL SELECT 'o',N'鞰' --毮夞乯鞰
- UNION ALL SELECT 'ou',N'漚'
- UNION ALL SELECT 'pa',N'袙'
- UNION ALL SELECT 'pai',N'磗' --鎃磗
- UNION ALL SELECT 'pan',N'鑻'
- UNION ALL SELECT 'pang',N'胖'
- UNION ALL SELECT 'pao',N'礮'
- UNION ALL SELECT 'pei',N'轡'
- UNION ALL SELECT 'pen',N'喯'
- UNION ALL SELECT 'peng',N'喸' --浌巼闏乶喸
- UNION ALL SELECT 'pi',N'鸊'
- UNION ALL SELECT 'pian',N'騙'
- UNION ALL SELECT 'piao',N'慓'
- UNION ALL SELECT 'pie',N'嫳'
- UNION ALL SELECT 'pin',N'聘'
- UNION ALL SELECT 'ping',N'蘋'
- UNION ALL SELECT 'po',N'魄'
- UNION ALL SELECT 'pou',N'哛' --兺哛
- UNION ALL SELECT 'pu',N'曝'
- UNION ALL SELECT 'qi',N'蟿'
- UNION ALL SELECT 'qia',N'髂'
- UNION ALL SELECT 'qian',N'縴'
- UNION ALL SELECT 'qiang',N'瓩' --羻兛瓩
- UNION ALL SELECT 'qiao',N'躈'
- UNION ALL SELECT 'qie',N'籡'
- UNION ALL SELECT 'qin',N'藽'
- UNION ALL SELECT 'qing',N'櫦'
- UNION ALL SELECT 'qiong',N'瓗'
- UNION ALL SELECT 'qiu',N'糗'
- UNION ALL SELECT 'qu',N'覻'
- UNION ALL SELECT 'quan',N'勸'
- UNION ALL SELECT 'que',N'礭'
- UNION ALL SELECT 'qun',N'囕'
- UNION ALL SELECT 'ran',N'橪'
- UNION ALL SELECT 'rang',N'讓'
- UNION ALL SELECT 'rao',N'繞'
- UNION ALL SELECT 're',N'熱'
- UNION ALL SELECT 'ren',N'餁'
- UNION ALL SELECT 'reng',N'陾'
- UNION ALL SELECT 'ri',N'馹'
- UNION ALL SELECT 'rong',N'穃'
- UNION ALL SELECT 'rou',N'嶿'
- UNION ALL SELECT 'ru',N'擩'
- UNION ALL SELECT 'ruan',N'礝'
- UNION ALL SELECT 'rui',N'壡'
- UNION ALL SELECT 'run',N'橍' --橍挼
- UNION ALL SELECT 'ruo',N'鶸'
- UNION ALL SELECT 'sa',N'栍' --櫒栍
- UNION ALL SELECT 'sai',N'虄' --簺虄
- UNION ALL SELECT 'san',N'閐'
- UNION ALL SELECT 'sang',N'喪'
- UNION ALL SELECT 'sao',N'髞'
- UNION ALL SELECT 'se',N'飋' --裇聓
- UNION ALL SELECT 'sen',N'篸'
- UNION ALL SELECT 'seng',N'縇' --閪縇
- UNION ALL SELECT 'sha',N'霎'
- UNION ALL SELECT 'shai',N'曬'
- UNION ALL SELECT 'shan',N'鱔'
- UNION ALL SELECT 'shang',N'緔'
- UNION ALL SELECT 'shao',N'潲'
- UNION ALL SELECT 'she',N'欇'
- UNION ALL SELECT 'shen',N'瘮'
- UNION ALL SELECT 'sheng',N'賸'
- UNION ALL SELECT 'shi',N'瓧' --鰘齛兙瓧
- UNION ALL SELECT 'shou',N'鏉'
- UNION ALL SELECT 'shu',N'虪'
- UNION ALL SELECT 'shua',N'誜'
- UNION ALL SELECT 'shuai',N'卛'
- UNION ALL SELECT 'shuan',N'腨'
- UNION ALL SELECT 'shuang',N'灀'
- UNION ALL SELECT 'shui',N'睡'
- UNION ALL SELECT 'shun',N'鬊'
- UNION ALL SELECT 'shuo',N'鑠'
- UNION ALL SELECT 'si',N'乺' --瀃螦乺
- UNION ALL SELECT 'song',N'鎹'
- UNION ALL SELECT 'sou',N'瘶'
- UNION ALL SELECT 'su',N'鷫'
- UNION ALL SELECT 'suan',N'算'
- UNION ALL SELECT 'sui',N'鐩'
- UNION ALL SELECT 'sun',N'潠'
- UNION ALL SELECT 'suo',N'蜶'
- UNION ALL SELECT 'ta',N'襨' --躢襨
- UNION ALL SELECT 'tai',N'燤'
- UNION ALL SELECT 'tan',N'賧'
- UNION ALL SELECT 'tang',N'燙'
- UNION ALL SELECT 'tao',N'畓' --討畓
- UNION ALL SELECT 'te',N'蟘'
- UNION ALL SELECT 'teng',N'朰' --霯唞朰
- UNION ALL SELECT 'ti',N'趯'
- UNION ALL SELECT 'tian',N'舚'
- UNION ALL SELECT 'tiao',N'糶'
- UNION ALL SELECT 'tie',N'餮'
- UNION ALL SELECT 'ting',N'乭' --濎乭
- UNION ALL SELECT 'tong',N'憅'
- UNION ALL SELECT 'tou',N'透'
- UNION ALL SELECT 'tu',N'鵵'
- UNION ALL SELECT 'tuan',N'褖'
- UNION ALL SELECT 'tui',N'駾'
- UNION ALL SELECT 'tun',N'坉'
- UNION ALL SELECT 'tuo',N'籜'
- UNION ALL SELECT 'wa',N'韤'
- UNION ALL SELECT 'wai',N'顡'
- UNION ALL SELECT 'wan',N'贎'
- UNION ALL SELECT 'wang',N'朢'
- UNION ALL SELECT 'wei',N'躛'
- UNION ALL SELECT 'wen',N'璺'
- UNION ALL SELECT 'weng',N'齆'
- UNION ALL SELECT 'wo',N'齷'
- UNION ALL SELECT 'wu',N'鶩'
- UNION ALL SELECT 'xi',N'衋'
- UNION ALL SELECT 'xia',N'鏬'
- UNION ALL SELECT 'xian',N'鼸'
- UNION ALL SELECT 'xiang',N'鱌'
- UNION ALL SELECT 'xiao',N'斆'
- UNION ALL SELECT 'xie',N'躞'
- UNION ALL SELECT 'xin',N'釁'
- UNION ALL SELECT 'xing',N'臖'
- UNION ALL SELECT 'xiong',N'敻'
- UNION ALL SELECT 'xiu',N'齅'
- UNION ALL SELECT 'xu',N'蓿'
- UNION ALL SELECT 'xuan',N'贙'
- UNION ALL SELECT 'xue',N'瀥'
- UNION ALL SELECT 'xun',N'鑂'
- UNION ALL SELECT 'ya',N'齾'
- UNION ALL SELECT 'yan',N'灩'
- UNION ALL SELECT 'yang',N'樣'
- UNION ALL SELECT 'yao',N'鑰'
- UNION ALL SELECT 'ye',N'岃' --鸈膶岃
- UNION ALL SELECT 'yi',N'齸'
- UNION ALL SELECT 'yin',N'檼'
- UNION ALL SELECT 'ying',N'譍'
- UNION ALL SELECT 'yo',N'喲'
- UNION ALL SELECT 'yong',N'醟'
- UNION ALL SELECT 'you',N'鼬'
- UNION ALL SELECT 'yu',N'爩'
- UNION ALL SELECT 'yuan',N'願'
- UNION ALL SELECT 'yue',N'鸙'
- UNION ALL SELECT 'yun',N'韻'
- UNION ALL SELECT 'za',N'雥'
- UNION ALL SELECT 'zai',N'縡'
- UNION ALL SELECT 'zan',N'饡'
- UNION ALL SELECT 'zang',N'臟'
- UNION ALL SELECT 'zao',N'竈'
- UNION ALL SELECT 'ze',N'稄'
- UNION ALL SELECT 'zei',N'鱡'
- UNION ALL SELECT 'zen',N'囎'
- UNION ALL SELECT 'zeng',N'贈'
- UNION ALL SELECT 'zha',N'醡'
- UNION ALL SELECT 'zhai',N'瘵'
- UNION ALL SELECT 'zhan',N'驏'
- UNION ALL SELECT 'zhang',N'瞕'
- UNION ALL SELECT 'zhao',N'羄'
- UNION ALL SELECT 'zhe',N'鷓'
- UNION ALL SELECT 'zhen',N'黮'
- UNION ALL SELECT 'zheng',N'證'
- UNION ALL SELECT 'zhi',N'豒'
- UNION ALL SELECT 'zhong',N'諥'
- UNION ALL SELECT 'zhou',N'驟'
- UNION ALL SELECT 'zhu',N'鑄'
- UNION ALL SELECT 'zhua',N'爪'
- UNION ALL SELECT 'zhuai',N'跩'
- UNION ALL SELECT 'zhuan',N'籑'
- UNION ALL SELECT 'zhuang',N'戅'
- UNION ALL SELECT 'zhui',N'鑆'
- UNION ALL SELECT 'zhun',N'稕'
- UNION ALL SELECT 'zhuo',N'籱'
- UNION ALL SELECT 'zi',N'漬'
- UNION ALL SELECT 'zong',N'縱'
- UNION ALL SELECT 'zou',N'媰'
- UNION ALL SELECT 'zu',N'謯'
- UNION ALL SELECT 'zuan',N'攥'
- UNION ALL SELECT 'zui',N'欈'
- UNION ALL SELECT 'zun',N'銌'
- UNION ALL SELECT 'zuo',N'咗'
- ) Temp
- WHERE Word >= @Word COLLATE Chinese_PRC_CS_AS_KS_WS
- ORDER BY Word COLLATE Chinese_PRC_CS_AS_KS_WS ASC
- )
- ELSE @Word END
- )
- SET @Index = @Index + 1
- END
- RETURN @PinYin
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[TransAreaNo] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --实现功能 的函数 将037155611698 转换为 0371-155611698
- --######################################################3
- CREATE FUNCTION [dbo].[TransAreaNo]
- (
- @TelephoneNo VARCHAR(50)
- )
- RETURNS VARCHAR(50)
- AS
- BEGIN
- DECLARE @Temp VARCHAR(50)
- set @TelephoneNo=right(@TelephoneNo,len(@TelephoneNo)-1)
- select @Temp=areano from dctelarea with(nolock)
- where areano like left(@TelephoneNo,len(areano))
- set @TelephoneNo=right(@TelephoneNo,len(@TelephoneNo)-len(@Temp))
- RETURN '0'+@Temp+'-'+@TelephoneNo
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[Trim] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#############################333
- --Trim函数
- --#############################333
- create FUNCTION [dbo].[Trim]
- (
- @sText VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- RETURN LTRIM(RTRIM(@sText))
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[TrimNULL] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#############################333
- --Trim函数, 如果=‘’,则为null
- --#############################333
- CREATE FUNCTION [dbo].[TrimNULL]
- (
- @sText VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- SET @sText = LTRIM(RTRIM(@sText))
- IF LEN(@sText) = 0
- SET @sText = NULL
- RETURN @sText
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[TruncJobIDs] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[TruncJobIDs]
- (
- @JobIds AS VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @i INT, @Num INT, @StrSource VARCHAR(MAX), @s VARCHAR(50)
- SELECT @StrSource = RTRIM(LTRIM(@JobIds))
- SELECT @i = CHARINDEX(',', @StrSource), @Num = 0, @s = ''
- IF @i = 0
- RETURN @StrSource
- WHILE @i >= 1 AND @Num < 3
- BEGIN
- IF LEFT(@StrSource, @i - 1) > ''
- SELECT @s = @s + ',' + LEFT(@StrSource, @i - 1), @Num = @Num + 1
- SELECT @StrSource = SUBSTRING(@StrSource, @i + 1, LEN(@StrSource) - @i)
- SELECT @i = CHARINDEX(',', @StrSource)
- END
- IF @Num < 3 AND @StrSource <> '' AND @i = 0
- SELECT @s = @s + ',' + @StrSource
-
- IF LEFT(@s, 1) = ','
- SELECT @s = RIGHT(@s, LEN(@s) - 1)
- RETURN @s
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[v2018_CheckHasCompleteCv] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --####################################################
- --添加人:Peter
- --添加时间:2018-8-23
- --描述:检查个人是否有可以申请职位的家里
- --####################################################
- CREATE FUNCTION [dbo].[v2018_CheckHasCompleteCv](@PaMainID INT)
- RETURNS BIT
- AS
- BEGIN
- IF EXISTS(
- SELECT *
- FROM cvMain WITH(NOLOCK)
- WHERE paMainID = @PaMainID
- AND (
- (cvLevel LIKE '111__1%' AND VerifyResult = 1) OR
- (cvLevelEng LIKE '111__1%' AND VerifyResultEng = 1)
- )
- )
- BEGIN
- RETURN 1
- END
- ELSE
- BEGIN
- RETURN 0
- END
- RETURN 0
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[v2018_SafeParam] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --#######################################
- --添加人:Lucifer
- --添加时间:2018-7-27
- --用途:过滤不安全的SQL语法
- --#######################################
- CREATE FUNCTION [dbo].[v2018_SafeParam]
- (
- @sText VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- IF @sText IS NULL
- RETURN ''
- -- SET @sText = REPLACE(@sText, 'and ','and ')
- -- SET @sText = REPLACE(@sText, 'or ','or ')
- -- SET @sText = REPLACE(@sText, 'exists','exists ')
- -- SET @sText = REPLACE(@sText, 'union ','union ')
- -- SET @sText = REPLACE(@sText, '<','<')
- -- SET @sText = REPLACE(@sText, '>','>')
- -- SET @sText = REPLACE(@sText, 'from ','from ')
- -- SET @sText = REPLACE(@sText, 'select ','select ')
- SET @sText = REPLACE(@sText, '','')
- SET @sText = REPLACE(@sText, 'script','script')
- SET @sText = REPLACE(@sText, 'src','src')
- SET @sText = REPLACE(@sText, 'iframe','iframe')
- SET @sText = REPLACE(@sText, '--','——')
- SET @sText = REPLACE(@sText, 'delete ','delete ')
- SET @sText = REPLACE(@sText, 'update ','update ')
- SET @sText = REPLACE(@sText, 'delete ','delete ')
- SET @sText = REPLACE(@sText, 'update ','update ')
- SET @sText = REPLACE(@sText, 'delete
- ','delete ')
- SET @sText = REPLACE(@sText, 'update
- ','update ')
- SET @sText = REPLACE(@sText, 'create','create ')
- SET @sText = REPLACE(@sText, 'drop','drop ')
- SET @sText = REPLACE(@sText, 'insert','insert ')
- SET @sText = REPLACE(@sText, 'chr(','chr( ')
- SET @sText = REPLACE(@sText, 'master','master ')
- SET @sText = REPLACE(@sText, 'net user','net user')
- SET @sText = REPLACE(@sText, 'declare','declare ')
- SET @sText = REPLACE(@sText, 'char(','char( ')
- SET @sText = REPLACE(@sText, 'mid(','mid( ')
- SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell ')
- SET @sText = REPLACE(@sText, 'exec','exec ')
- SET @sText = REPLACE(@sText, 'truncate','truncate ')
- SET @sText = REPLACE(@sText, 'ascii(','ascii( ')
- SET @sText = REPLACE(@sText, 'substring(','substring( ')
- SET @sText = REPLACE(@sText, 'localgroup ','localgroup ')
- SET @sText = REPLACE(@sText, 'administrators ','administrators ')
- SET @sText = REPLACE(@sText, 'dbcc','dbcc ')
- SET @sText = REPLACE(@sText, 'checkdb(','checkdb( ')
- SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember ')
- SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell ')
- RETURN @sText
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[v2018_SetIntNull] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --选填的空值,需要改成Null
- --######################################################3
- CREATE FUNCTION [dbo].[v2018_SetIntNull]
- (
- @str INT
- )
- RETURNS INT
- AS
- BEGIN
- IF @str = 0
- SET @str = NULL
- RETURN @str
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[v2018_SetVarcharNull] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################################3
- --选填的空值,需要改成Null
- --######################################################3
- CREATE FUNCTION [dbo].[v2018_SetVarcharNull]
- (
- @Str VARCHAR(MAX)
- )
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- SET @Str = dbo.v2018_SafeParam(@Str)
- IF LEN(ISNULL(@Str, '')) = 0
- SET @Str = NULL
- RETURN @Str
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[VipJobNumber] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2011-01-25
- --贵宾会员的职位数量
- --######################################3
- CREATE FUNCTION [dbo].[VipJobNumber]
- (
- @ID INT
- )
- RETURNS INT
- AS
- BEGIN
- DECLARE @Number AS INT, @Sn INT
- SELECT @Number = SUM(JobNumber) FROM caOrder WITH(NOLOCK)
- WHERE cpMainID = @ID
- AND BeginDate <= GETDATE() AND EndDate >= CONVERT(SMALLDATETIME, GETDATE())
- AND OpenDate IS NOT NULL
- SET @Sn = 5
- IF EXISTS(SELECT 'x' FROM cpMain WITH(NOLOCK) WHERE Id = @Id AND RealName = 1)
- SET @Sn = 10
-
- RETURN ISNULL(@Number, 0) + @Sn
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[VipUserNumber] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --######################################3
- --创建:Sean 2011-01-25
- --贵宾会员的用户数量
- --######################################3
- CREATE FUNCTION [dbo].[VipUserNumber]
- (
- @ID INT
- )
- RETURNS INT
- AS
- BEGIN
- DECLARE @Number AS INT
- SELECT @Number = SUM(UserNumber) FROM caOrder WITH(NOLOCK)
- WHERE cpMainID = @ID
- AND BeginDate <= GETDATE() AND EndDate >= CONVERT(SMALLDATETIME, GETDATE())
- AND OpenDate IS NOT NULL
- RETURN ISNULL(@Number, 0) + 1
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[WorkDateAdd] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE Function [dbo].[WorkDateAdd]
- (
- @Date DATETIME,
- @Hours DECIMAL(9, 2)
- )
- RETURNS DATETIME
- AS
- BEGIN
- ----检查输入参数是否正确
- IF ISNULL(@Hours, 0) = 0
- RETURN @Date
- IF @Date IS NULL
- RETURN NULL
- DECLARE @Minutes AS INT, @s AS VARCHAR(8), @StartDate AS DATETIME, @CostMinute AS INT
- ----获取第一个工作日
- IF NOT EXISTS(SELECT 'x' FROM OaDb..WorkDate WITH(NOLOCK) WHERE WorkDate = CONVERT(VARCHAR(8), @Date, 112) AND WorkType = 1)
- BEGIN
- SELECT TOP 1 @S = a.WorkDate
- FROM OaDb..WorkDate a WITH(NOLOCK)
- WHERE a.WorkDate > CONVERT(VARCHAR(8), @Date, 112)
- AND WorkType = 1
- ORDER BY a.WorkDate
-
- SET @StartDate = @S
- END
- ELSE
- SET @StartDate = CONVERT(DATETIME, CONVERT(VARCHAR(10), @Date, 120))
- ----如果当天是工作日,则计算是已运行的工作时间
- IF CONVERT(VARCHAR(8), @Date, 112) = CONVERT(VARCHAR(8), @StartDate, 112)
- BEGIN
- SET @Minutes = DATEPART(HOUR, @Date) * 100 + DATEPART(MINUTE, @Date)
-
- IF @Minutes < 830
- SELECT @CostMinute = 0
- ELSE IF @Minutes < 1201
- SELECT @CostMinute = DATEDIFF(MINUTE, dbo.MergeDate(@Date, 830), @Date)
- ELSE IF @Minutes < 1300
- SELECT @CostMinute = 210
- ELSE IF @Minutes < 1731
- SELECT @CostMinute = DATEDIFF(MINUTE, dbo.MergeDate(@Date, 1300), @Date) + 210
- ELSE
- SELECT @CostMinute = 480
- END
- ELSE
- SET @CostMinute = 0
-
- SET @Minutes = @Hours * 60 + @CostMinute ----从第一个工作日开始,需要工作的分钟数
- DECLARE @DayInterval AS INT, --除8等于天数
- @MinuteLast AS INT,
- @ExpireDate AS DATETIME,
- @TempDate AS DATETIME
- SET @DayInterval = @Minutes / 480
- SELECT @MinuteLast = @Minutes - @DayInterval * 480
- SET @DayInterval = @DayInterval + 1
- DECLARE @t AS TABLE(
- ID INT IDENTITY(1,1) NOT NULL,
- WorkDate VARCHAR(8)
- )
- ----函数中无法使用临时表和(),所以只有使用表变量
- INSERT INTO @t(WorkDate)
- SELECT TOP (@DayInterval) WorkDate
- FROM OaDb..WorkDate WITH(NOLOCK)
- WHERE WorkType = 1
- AND WorkDate >= CONVERT(VARCHAR(8), @StartDate, 112)
- ORDER BY WorkDate
- SELECT TOP 1 @S = WorkDate FROM @t ORDER BY ID DESC
- SET @ExpireDate = @S
- IF @MinuteLast > 210
- SET @MinuteLast = @MinuteLast + 60
- SET @ExpireDate = DATEADD(MINUTE, @MinuteLast, dbo.MergeDate(@ExpireDate, 830))
- RETURN @ExpireDate
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[ZhaopinRegionToOurID] Script Date: 2018/12/13 18:28:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --sean 2013-6-25
- CREATE FUNCTION [dbo].[ZhaopinRegionToOurID]
- (
- @ZhaopinRegin VARCHAR(50)
- )
- RETURNS VARCHAR(6)
- AS
- BEGIN
- DECLARE @Id VARCHAR(6), @Id2 VARCHAR(6), @Des VARCHAR(20)
- SELECT TOP 1 @id = ID, @Des = DESCRIPTION
- FROM dcRegion WHERE Description LIKE LEFT(@ZhaopinRegin, 2) + '%'
- AND GRADE = 1
- ORDER BY ID
-
- SET @Des = REPLACE(@Des, '市', '')
- SET @Des = REPLACE(@Des, '省', '')
- SET @ZhaopinRegin = REPLACE(@ZhaopinRegin, @Des, '')
-
- SELECT TOP 1 @id2 = ID
- FROM dcRegion
- WHERE Description LIKE '%' + RIGHT(@ZhaopinRegin, 2) + '%'
- AND ID LIKE @Id + '%'
- ORDER BY Id
- IF @Id2 > ''
- RETURN @ID2
- ELSE
- RETURN @ID
- RETURN ''
- END
- GO
|