Neverejné FAQ

    Browse: Home / Neverejné FAQ

Obrázky

https://www.pexels.com

https://unsplash.com

Ilustrácie

https://undraw.co/search

GPS Súradnice

Reverse geocoding – adresa zo súradníc

https://nominatim.openstreetmap.org/reverse?lat=48.7127729&lon=21.2114205

Súradnice z adresy

https://nominatim.openstreetmap.org/search?q=135+pilkington+avenue,+birmingham&format=json&polygon=1&addressdetails=1

CREATE OR ALTER PROCEDURE [dbo].[hpx_ERPIO_DoplnenieSuradnicKOrganizaciam]
AS

DECLARE @CisloOrg INT
DECLARE @Adresa NVARCHAR(255);

--DECLARE TMP_Cursor CURSOR DYNAMIC 
DECLARE TMP_Cursor CURSOR FAST_FORWARD
--DECLARE TMP_Cursor CURSOR STATIC READ_ONLY FORWARD_ONLY
    FOR SELECT TOP 10 
		CisloOrg,
		TRIM(TRIM(TRIM(Ulice)+' '+TRIM(CASE WHEN OrCislo = '' THEN PopCislo ELSE OrCislo END)) + ',' /*+ REPLACE(PSC, ' ', '') + ' '*/ + TRIM(Misto))
          FROM TabCisOrg
         WHERE ISNULL(GPSZemepisnaDelka, 0) = 0 AND ISNULL(TRIM(CONCAT(Ulice, ' ', CASE WHEN OrCislo = '' THEN PopCislo ELSE OrCislo END) + ',' + PSC + ' ' + Misto), '') <> ''
		 ORDER BY MU DESC

OPEN TMP_Cursor

FETCH NEXT FROM TMP_Cursor INTO @CisloOrg, @Adresa

WHILE @@FETCH_STATUS = 0
BEGIN

	DECLARE @Lat	NUMERIC(10,7);
	DECLARE @Lon	NUMERIC(10,7);	
    DECLARE @url	NVARCHAR(500);
    DECLARE @obj	INT;
    DECLARE @hr		INT;
    DECLARE @msg	VARCHAR(8000);	
    DECLARE @json	NVARCHAR(4000);	--Cannot use MAX with CLR stored procedures

    SELECT @Adresa = CAST(@Adresa AS VARCHAR(255)) Collate SQL_Latin1_General_CP1253_CI_AI
    SET @Adresa = REPLACE(@Adresa, ' ', '%20')

    SET @url = 'https://nominatim.openstreetmap.org/search?q='+@Adresa+'&format=json&polygon=1&addressdetails=0'

	/* SELECT @url */

    EXEC @hr = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @obj OUT  -- Creates an instance of an OLE object
    IF @hr <> 0 BEGIN SET @Msg = 'sp_OACreate WinHttp.WinHttpRequest.5.1 failed. Cannot connect' GOTO Error END

    EXEC @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @Url, false  -- Calls a method of an OLE object.
    IF @hr <> 0 BEGIN SET @msg = 'sp_OAMethod Open failed. Cannot connect' GOTO Error END

    EXEC @hr = sp_OAMethod @obj, 'SetRequestHeader', NULL, 'Content-Type', 'application/json;charset=utf-8'
    IF @hr <> 0 BEGIN SET @msg = 'sp_OAMethod SetRequestHeader failed. Cannot connect' GOTO Error END

    EXEC @hr = sp_OAMethod @obj, SEND, NULL, ''
    IF @hr <> 0 BEGIN SET @msg = 'sp_OAMethod Send failed. Cannot connect' GOTO Error END

	/*
    EXEC @hr = sp_OAGetProperty @Obj, 'ResponseText', @json OUTPUT
    */
	DECLARE @ResponseTable as table(Json_Table nvarchar(max))
	EXEC sp_OAMethod @Obj, 'ResponseText', @ResponseTable OUTPUT
	INSERT into @ResponseTable(Json_Table) EXEC sp_OAGetProperty @Obj, 'ResponseText'
	
	IF @hr <> 0 EXEC sp_OAGetErrorInfo @Obj

	SELECT TOP 1 @json = Json_Table from @ResponseTable

    EXEC @hr = sp_OADestroy @obj

	/* SELECT @url, @json */

	IF LEFT(CAST(@json AS NVARCHAR(MAX)),1) <> '{' AND LEFT(CAST(@json AS NVARCHAR(MAX)),1) <> '['
	    GOTO Error

    SELECT @Lat = ISNULL(TRY_PARSE(JSON_VALUE(@json, '$[0].lat') AS NUMERIC(10,7)), -1.0), 
    @Lon = ISNULL(TRY_PARSE(JSON_VALUE(@json, '$[0].lon') AS NUMERIC(10,7)), -1.0)

	/* SELECT @Adresa, @Lat AS Lat, @Lon AS Lon */

    GOTO FinalEnd

    Error:
    EXEC @hr = sp_OADestroy @obj
	SET @Lat = -1
	SET @Lon = -1

    FinalEnd:
	UPDATE TabCisOrg SET
	GPSZemepisnaSirka = @Lat,
	GPSZemepisnaDelka = @Lon
	WHERE CisloOrg = @CisloOrg

  FETCH NEXT FROM TMP_Cursor INTO @CisloOrg, @Adresa
END

CLOSE TMP_Cursor
DEALLOCATE TMP_Cursor

/* KONFIGURACIA

EXEC sp_configure 'Ole Automation Procedures';  
GO 
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'Ole Automation Procedures';  
GO 
*/

Jak zadat staticky mkey?

  • Vypnout GW
  • Editova config soubor
  • Pridat radek: „SvC_MKey=BlaBla_data1“
  • Ulozit
  • Spustit GW

POZOR SvC_MKey musí byť jedinečný, generuje sa podľa neho GUID

Sifrovanie connection stringu v configu GW

sifrovani ConnectionString(nutno editovat rucne v CFG) SQL_connstr=
**** – zacinajici znaky, pozadavek na zasifrovani connstring

**## – zacinajici znaky, jiz zasifrovany connstring

priklad, chci aby mi GW zasifrovala:
SQL_connstr=tady dale je muj bezny connection, jako datasource=sql/db… SQL_connstr=Data Source=.\SQLHEO;Initial Catalog=Helios666;Persist Security Info=True;User
ID=e1user;Password=strongxxx
priklad, ze connection string je zasifrovany:
SQL_connstr=##day dale je pak necitelny “bordel” SQL_connstr=##fxgqMYGIY1fomYGuLYnKgPekjSvr5agAo+TSrr7q95iadOt6LLzIdmRoDjly5YptxTaJ1nzmm
CCdryVsgBROvf8p1KifHBtrxj2G853oG8h5oQV0UgvFRRsWE/LJpBDXA915How==
Postupoval bych:

  1. Standardne nastavit celou GW (nebo si pripravil cely CFG soubor bokem)
  2. Ulozit + stopnout gw službu
  3. Editovat EGWsvc.cfg – Pridat do radku connectionstringu ****
  4. Spustit službu gw
  5. Okem prekontrolovat, ze je zasifrovano v EGWsvc.cfg a spojeni funguje. Predal zakaznikovi, tady
    to mate a nehrabte na to 🙂

SWAGGER

https://my.erpio.one/swagger/ui/index

Ako použiť API?

Login 

            var url = ERPIO.E1.E1BaseAddress + "/oauth/token";
            result = $.ajax({
                type: 'POST',
                async:false, 
                url: url,
                data: { 
                    "grant_type": "password",
                    "username": username ,
                    "password": password
                }
            });

            if (result.status == 200) {

Na spúšťanie akcií a získanie setov by sa mala použiť:

POST /api/ytcpmsg/GetWaitedObjectMSG/{id}

https://my.erpio.one/pub/Help/Api/POST-api-ytcpmsg-GetWaitedObjectMSG-id_waitsec

zatiaľ som nevyskúšal

Ako rochodziť bránu na macos?

balíček je self-containded, takze postup:

  • rozbalit zip
  • nakopirovat cfg s konfiguraciou
  • otvoriť terminál a tam príkazy:
    • cd [umiestnenie kam som rozbalil zip]
    • sudo chmod a+x ./ERPIO.TCP.Client.Console
    • ./ERPIO.TCP.Client.Console

Ako používať HttpClient v C#

https://www.aspnetmonsters.com/2016/08/2016-08-27-httpclientwrong/

private static HttpClient client = new HttpClient().....


var request = new HttpRequestMessage(HttpMethod.Post, baseAddress + "/Account/GetToken");
				request.Headers.Accept.Clear();
				request.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
				request.Content = content;

				
//using (var tokenResponse = await clientloc.PostAsync(baseAddress + "/Account/GetToken", content))
using (var tokenResponse = await clientloc.SendAsync(request))
{
    bla bla bla
}

CURL na MS SQL cez CLR

https://github.com/microsoft/sql-server-samples/tree/master/samples/features/sql-clr/Curl

sp_configure @configname=clr_enabled, @configvalue=1
GO
RECONFIGURE
GO

EXEC sp_add_trusted_assembly 0x71BF52A56DF5F8049B4DA9858F19F22A9A363B23DF6A19DF5ED6A91300574E3EA8531A6B71D70A02E53767BDC2244FA175B1C2A6FEE15927B24C14D6CD338903, N'SqlClrCurl'
GO

CREATE ASSEMBLY [SqlClrCurl]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300BBAC355D0000000000000000E00022200B013000000E00000008000000000000FA2D0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000A82D00004F000000004000002C04000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000000E000000200000000E000000020000000000000000000000000000200000602E727372630000002C040000004000000006000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001600000000000000000000000000004000004200000000000000000000000000000000DC2D00000000000048000000020005006C2200003C0B00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002003100000001000011731200000A0A0206280400000606036F1300000A0B1201281400000A281500000A6F1600000A6F1700000A731800000A2A000000133003006300000002000011731200000A0A02062804000006036F1900000A2C1072010000707271000070731A00000A7A06046F1300000A0C1202281400000A281500000A036F1300000A0C1202281400000A6F1B00000A0B281C00000A727700007007281D00000A6F1E00000A2A001B300500D500000003000011731200000A0A02062804000006036F1900000A2C1072010000707271000070731A00000A7A7E010000040B72A30000700C0006046F1300000A0D1203281400000A281500000A036F1300000A0D1203281400000A6F1B00000A0C150BDE5D1304281C00000A1B8D10000001251672A5000070A2251711046F1F00000AA2251872B5000070A225197E020000048C1D000001A2251A72CB000070A2282000000A6F1E00000A0717590B7E02000004282100000ADE0007163D76FFFFFF07153315281C00000A72D300007008281D00000A6F1E00000A2A00000001100000000032002C5E005617000001133005005200000004000011026F1900000A2D49026F1300000A0B1201281400000A0A06282200000A2D3206178D1F00000125161F3B9D6F2300000A0C160D2B1608099A1304036F2400000A11046F2500000A0917580D09088E6932E42A1E02282600000A2A3A1980010000041F3280020000042A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000078030000237E0000E40300002004000023537472696E67730000000004080000000100002355530004090000100000002347554944000000140900002802000023426C6F620000000000000002000001571502000900000000FA01330016000001000000200000000200000002000000060000000B0000002600000012000000040000000100000003000000000095020100000000000600870168030600060268030600900036030F00880300000600B800C10206006A01C10206003601C1020600ED01C1020600A701C1020600D201C1020600E500C1020600A400490306008200490306001901C102060000013F020600C803BA020A0055010E030A00C0010E030A00AC0397030E00E403D3030A00870297030A00CF000E030600EF02BA020E009102BA0206008A02BA020600E702BA020A00FA030E030A007A000E0306000100BA02060042002E020600FF02BA020E00D302D303000000000700000000000100010001001000B5020000410001000100310021009D00310012009D005020000000009600CF03A00001009020000000009600F503A900040000210000000096001104A9000700F4210000000091000403B3000A005222000000008618290306000C005A220000000091182F03BB000C0000000000000000000100100000000200B60200000100100000000200500000000300B60200000100100000000200500000000300B60200000100100000000200EE03090029030100110029030600190029030A00290029031000310029031000390029031000410029031000490029031000510029031000590029031000610029031500690029031000710029031000790029031000890029030600910029030600B10029030600A10029030600990085022100A90024022600C10075022A00A10059022F00C900050434009900290339009900A4024700D10029034B00A10068025100D90071005700C900C1035C00E1004D001000B90065002600C900C1036D00F100F9027300C90052008200C900DE038700A100B5038E0001014900100081002903060020007B000F022400830014022E000B00BF002E001300C8002E001B00E7002E002300F0002E002B0000012E00330042012E003B0048012E00430057012E004B0076012E00530042012E005B0042012E0063008E012E006B00B8012E007300C50140008B000F0260008B000F021A003F0062007800048000000100000000000000000000000000AF020000040000000000000000000000940039000000000004000000000000000000000094002D00000000000400000000000000000000009400BA0200000000000000496E743332003C4D6F64756C653E00480044454C41595F4F4E5F4552524F520052455452595F434F554E540053797374656D2E44617461006D73636F726C696200546872656164004164640053656E640049734E756C6C4F7257686974655370616365006765745F4D657373616765006765745F506970650053716C5069706500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C654174747269627574650053716C50726F63656475726541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F647563744174747269627574650053716C466163657441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053797374656D2E546872656164696E670053797374656D2E52756E74696D652E56657273696F6E696E6700446F776E6C6F6164537472696E670055706C6F6164537472696E6700457363617065557269537472696E6700546F53716C537472696E67005572690053716C436C724375726C2E646C6C006765745F49734E756C6C0053716C436C724375726C0053797374656D0053797374656D2E5265666C656374696F6E00576562486561646572436F6C6C656374696F6E00417267756D656E74457863657074696F6E00536C656570004368617200416464486561646572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053716C4368617273006765745F4865616465727300436F6E636174004F626A656374004765740053797374656D2E4E65740053706C697400576562436C69656E7400636C69656E7400506F73740053716C436F6E7465787400546F43686172417272617900506F73745769746852657472790000006F59006F00750020006D007500730074002000730070006500630069006600790020006400610074006100200074006800610074002000770069006C006C002000620065002000730065006E007400200074006F002000740068006500200065006E00640070006F0069006E007400000540006400002B52006500710075006500730074002000690073002000650078006500630075007400650064002E0020000001000F4500720072006F0072003A00090000152E002000570061006900740069006E006700200000076D0073002E00002952006500710075006500730074002000690073002000650078006500630075007400650064002E000000000048478248D6CBC6478D18D459D8F01E0A00042001010803200001052001011111042001010E04200101020607021251115504200011550320000E0400010E0E0420010E0E0420001D03052001011D0307070312510E115503200002052002010E0E0520020E0E0E04000012710500020E0E0E0A07051251080E1155125D0500010E1D1C04000101080907050E11551D0E080E040001020E0620011D0E1D0305200012808108B77A5C561934E089020608080002124D124D124D09000301124D124D124D07000201124D1251030000010801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000000F01000A53716C436C724375726C00004101003C496D706C656D656E746174696F6E206F66204355524C20636F6D6D616E6420696E2053514C2053657276657220446174616261736520456E67696E6500000501000000000E0100094D6963726F736F667400001E01001953716C20536572766572204769744875622053616D706C6573000017010012436F7079726967687420C2A920203230313800002901002432363565306263332D616435662D343466332D626231372D63363166373762393834376600000C010007312E302E302E3000004901001A2E4E45544672616D65776F726B2C56657273696F6E3D76342E350100540E144672616D65776F726B446973706C61794E616D65122E4E4554204672616D65776F726B20342E35040100000012010001005408074D617853697A65FFFFFFFF00D02D00000000000000000000EA2D0000002000000000000000000000000000000000000000000000DC2D0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00300000000000000000000D00334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430030000010053007400720069006E006700460069006C00650049006E0066006F0000000C030000010030003000300030003000340062003000000092003D00010043006F006D006D0065006E0074007300000049006D0070006C0065006D0065006E0074006100740069006F006E0020006F00660020004300550052004C00200063006F006D006D0061006E006400200069006E002000530051004C002000530065007200760065007200200044006100740061006200610073006500200045006E00670069006E0065000000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003E000B000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0043006C0072004300750072006C0000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003E000F00010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C0072004300750072006C002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100380000002A00010001004C006500670061006C00540072006100640065006D00610072006B007300000000000000000046000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C0072004300750072006C002E0064006C006C000000000054001A000100500072006F0064007500630074004E0061006D00650000000000530071006C00200053006500720076006500720020004700690074004800750062002000530061006D0070006C00650073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000FC3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

--Create the schema where CURL modules will be placed.
CREATE SCHEMA CURL;
GO

--Create the function/procedure
CREATE FUNCTION CURL.XGET (@H NVARCHAR(MAX), @url NVARCHAR(4000))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrCurl.Curl.Get;
GO

CREATE PROCEDURE CURL.XPOST (@H NVARCHAR(MAX), @d NVARCHAR(MAX), @url NVARCHAR(4000))
AS EXTERNAL NAME SqlClrCurl.Curl.Post;
GO

select curl.xget(null, 'https://api.iextrading.com/1.0/stock/msft/earnings')

OnChange nahrávanie parametrov v editore

1) mw-akce na parametru @Par1 cBarCode ->Extended config, pridat sysname-string: EvOnChanged
2) v libovolnych “dalsich v poradi” parametrch, si napsat SELECT na ziskani hodnoty- napr: SELECT @Par1;
hotovo
funkcni test v one.erpio->aldemo->dlazdice zakaznici->edit 2
https://one.erpio.cloud/Pages/yDAct/ydactedit.aspx?id=2797&

Prihlásenie pomocou tokenu

=> Token.cs byl rozsireny v pripade, ze se prihlasujes pomoci apiketyokenu tak v nem vrati username

  • API: Moznost prihlaseni pres APIKEyToken(misto jmena a hesla). vola se stejna metoda jako pri beznem prihlaseni “oauth\token” s tim ze vyplnim: username=e1key(konstanta, obdobne jako demo ucet),password=muj vlastni apikeytoken. Zpet se vrati model TOKEN, rozsireni o udaj “e1key_username”=z nej zjistim pod kym jsmem se vlastn prihlasil
  • plati i co jsme se bavili vcera, ze kdyz pouzijes ten “vygenerovany”, tak te zautorizuje proti API volani, ale nevymlatis z neho nic vic(uz je to hotovy token).

tim novym popsanym zpusobem pak jde napsat cokoli delsiho>10 znaku jako prihlasovaci udaj(beztak to nakonec bude 1:N)

jinak receno, z klienta leze/se prihlasujes “username” konstanta=e1key a “heslo”= ten zadany klic na uzivateli

RegExp na číslo účtu

\d{0,6}-{0,1}\d{10,16}\/\d{4}