Skip to content
logo
platformaerp.com

Przygotowanie danych testowych

Kontrahent

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- update phone 
update firm.firm set 
phoneNumber = cast([dbo].[fn_getRandomInteger](11,59) as varchar) + ' ' +  cast([dbo].[fn_getRandomInteger](111111111,999999999) as varchar)
where phonenumber is not null

-- update tax
update firm.firm set 
taxcode = cast([dbo].[fn_getRandomInteger](1111111111,9111111111) as varchar) 
where taxcode is not null

-- update street

declare @randomstreets nvarchar(max)='Aleja Jana Pawła II,Marszałkowska,Nowy Świat,Krakowskie Przedmieście,Chmielna,Świętokrzyska,Mokotowska,Puławska,Żwirki i Wigury,Grójecka,Targowa,Ząbkowska,Grochowska,Francuska,Wileńska,Radzymińska,Modlińska,Marymoncka,Słowackiego,Broniewskiego,Powstańców Śląskich,Górczewska,Wolska,Kasprzaka,Towarowa,Prosta,Grzybowska,Emilii Plater,Krucza,Hoża,Wilcza,Piękna,Koszykowa,Polna,Waryńskiego,Batorego,Rakowiecka,Wołoska,Racławicka,Żwirki i Wigury,Banacha,Bitwy Warszawskiej 1920 r.,Opaczewska,Szczęśliwicka,Grójecka,Dickensa,Korotyńskiego,Bohaterów Września,Drawska,Włodarzewska,Aleje Jerozolimskie,Raszyńska,Żelazna,Chłodna,Wronia,Łucka,Pańska,Sienna,Złota,Chmielna,Aleje Jerozolimskie,Nowogrodzka,Wspólna,Hoża,Wilcza,Piękna,Koszykowa,Polna,Waryńskiego,Batorego,Rakowiecka,Wołoska,Racławicka,Żwirki i Wigury,Banacha,Bitwy Warszawskiej 1920 r.,Opaczewska,Szczęśliwicka,Grójecka,Dickensa,Korotyńskiego,Bohaterów Września,Drawska,Włodarzewska,Aleje Jerozolimskie,Raszyńska,Żelazna,Chłodna,Wronia,Łucka,Pańska,Sienna,Złota,Chmielna,Aleje Jerozolimskie,Nowogrodzka,Wspólna'

update firm.firm set streetName = dbo.fn_getRandomString(@randomstreets) 

update firm.firm set houseNumber = cast([dbo].[fn_getRandomInteger](10,300) as varchar) 

declare @randomcities nvarchar(max)='Warszawa,Kraków,Łódź,Wrocław,Poznań,Gdańsk,Szczecin,Bydgoszcz,Lublin,Katowice'

update firm.firm set city  = dbo.fn_getRandomString(@randomcities) 

-- update name

declare @firmname_1 nvarchar(max)='Space,Nature,Alpine,Icon,222,333,444,555,999,888,345,678,123,456,789,234,567,890,256,255,111,Steel,Tech,Innovative,Global,NextGen,Prime,Future,Dynamic,Creative,Visionary,Elite,Fast,Slow,Prime,Best,Worst,Pioneer,Agile,Forward,Quantum,Alpha,Elevate,Nexus,Apex,Cutting-edge,Impact,Pulse,Transform,Legacy,Thrive,Radiant,Cosmos,Urban,Zenith,Modern,Trend,Future proof,Streamline,Tech-savvy,Iconic,Advantage,Revolutionary,State-of-the-art,Next-level,Eminent,Exponential,Accelerate,Innovate,Top-tier,World-class,Advanced,Peak,Frontline,Visionary,Progressive,Hyper,Elevated,Premier,Revolution,Brilliant,Excellence,Trailblazer,REMOVE'
declare @firmname_2 nvarchar(max)='Console,Last,Zero,Red,Blue,Green,Yellow,Orange,Purple,Pink,Brown,Gray,Black,White,Magenta,Cyan,Maroon,Lime,Olive,Teal,Navy,Gold,Silver,Lion,Tiger,Elephant,Giraffe,Zebra,Kangaroo,Panda,Dolphin,Koala,Penguin,Epic,Surge,Momentum,Core,Essence,Catalyst,Vanguard,Ascend,Horizon,Evolve,Luminary,Zenith,Paradigm,Infinite,Bold,Apex,Insight,Ignite,Amplify,Drive,Dynamics,Ventures,Elements,Intelligence,Data,Quantum,Pulse,Nexus,Forge,Craft,Insight,Solutions,Genesis,Platforms,Engineering,Logic,Fabric,Constructs,Matrix,Exchange,Cyberspace,Hubs,Networks,Collaborations,Vision,Mechanisms,Structures,Enterprises,Frontiers,Paradigm,Engines,Circuits,Legion,Galaxy,REMOVE'--empty
declare @firmname_3 nvarchar(max)='Jet,Machinery,Engines,Production,Gaming,Solutions,Systems,Enterprises,Technologies,Software,Innovations,Minds,Corporation,Industries,Networks,Dynamics,Ventures,Platforms,Concepts,Labs,Developments,Analytics,Services,Applications,Mechanics,Automations,Interfaces,Strategies,Intelligence,Studios,Creations,Constructs,Frameworks,Enterprise,Holding,Partner,Innovation,System,Network,Integration,Venture,Service,Strategy,Dynamic,Mechanic,Concept,Design,Construct,Application,Automation,Creation,Development,Engineering'
declare @firmname_4 nvarchar(max)='Lab,GmbH,Ltd.,Sp.zo.o.,Inc.,LLC,PLC,S.A.'


update firm.firm set firmname = 
dbo.fn_getRandomString(@firmname_1)  + ' ' + 
dbo.fn_getRandomString(@firmname_2)  + ' ' +
dbo.fn_getRandomString(@firmname_3) + ' ' +
dbo.fn_getRandomString(@firmname_4)

update firm.firm set firmname = replace(firmname,'REMOVE ','')
select firmname,count(*) from firm.firm group by firmname 
having count(*) >1

--POST OPERATIONS

update firm.firm set firmNameShort = firmname, fulltextcat=0

update common.address set 
    streetName = f.streetname,
    city = f.city,
    postCode = f.postCode,
    houseNumber =f.houseNumber,
    flatnumber =f.flatnumber,
    countryState = f.countryState
from firm.firm f inner join  common.address a on  f.firmid = a.remoteID