{"id":1257,"date":"2024-10-03T22:53:22","date_gmt":"2024-10-03T20:53:22","guid":{"rendered":"https:\/\/www.cdorde.com\/?p=1257"},"modified":"2024-10-03T22:59:32","modified_gmt":"2024-10-03T20:59:32","slug":"mysql-funkcija-koja-pretvara-iznos-u-tekst","status":"publish","type":"post","link":"https:\/\/www.cdorde.com\/index.php\/2024\/10\/03\/mysql-funkcija-koja-pretvara-iznos-u-tekst\/","title":{"rendered":"MYSQL funkcija koja pretvara iznos u tekst"},"content":{"rendered":"\n

Pri\u010da ide ovako, treba mi nabrzaka funkcija a imam je u plsql-u na Oracle-u. Odem na chatGpt on napravi kod ali sa ARRAY tipom koji moj MySQL 8 ne pozna. Onda zamijenim ARRAY sa JSON stringom i iskoristim funkcije JSON_EXTRACT i JSON_UNQUOTE. Zbog indekas mora sam dodati na po\u010detku JSON stringa po jedan prazan string (mogao sam to uraditi i uklanjaju\u0107i 1+ u kodu). E kod priloga je malo druga\u010dije jer kod koristi jednodimenzioni din kao dvodimenzioni. Pa sam na svakih sedam stringova morao dodati po jedan prazan. I tralala … kod radi<\/p>\n\n\n\n

CREATE DEFINER=`root`@`%` FUNCTION `BROJUSRPSKI`(\n    Broj DECIMAL(12, 2)\n) RETURNS varchar(255) CHARSET utf8mb4\n    NO SQL\nBEGIN\n  DECLARE MaxVal INT DEFAULT 99999999;\n  DECLARE sZeroBroj VARCHAR(100) DEFAULT \"nula dinara\";\n  DECLARE Result VARCHAR(255) DEFAULT \"\";\n  DECLARE Temp VARCHAR(255) DEFAULT \"\";\n  DECLARE Trojka VARCHAR(255) DEFAULT \"\";\n  DECLARE S VARCHAR(255);\n  DECLARE brojac INT DEFAULT 0;\n  DECLARE eCeo INT;\n  DECLARE eStoti INT;\n  DECLARE i INT;\n  DECLARE k INT;\n  \n  SET @Jedinice =\n    '[\"\", \" \", \"jedna\", \"dve\", \"tri\", \"\u010detiri\", \"pet\", \"\u0161est\", \"sedam\", \"osam\", \"devet\", \n     \"deset\", \"jedanaest\", \"dvanaest\", \"trinaest\", \"\u010detrnaest\", \"petnaest\", \"\u0161esnaest\", \"sedamnaest\", \"osamnaest\", \"devetnaest\",\n     \" \", \"jedan\", \"dva\", \"tri\", \"\u010detiri\", \"pet\", \"\u0161est\", \"sedam\", \"osam\", \"devet\", \n     \"deset\", \"jedanaest\", \"dvanaest\", \"trinaest\", \"\u010detrnaest\", \"petnaest\", \"\u0161esnaest\", \"sedamnaest\", \"osamnaest\", \"devetnaest\"]';\n\n  SET @Desetice =\n    '[\"\", \" \", \" \", \"dvadeset\", \"trideset\", \"\u010detrdeset\", \"pedeset\", \"\u0161ezdeset\", \"sedamdeset\", \"osamdeset\", \"devedeset\"]';\n\n  SET @Stotine  =\n\t'[\"\", \"\", \"sto\", \"dvjesta\", \"trista\", \"\u010detristo\", \"petsto\", \"\u0161esto\", \"sedamsto\", \"osamsto\", \"devetsto\"]';\n\n  SET @Prilozi  = \n    '[\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\", \n      \"\", \" KM\", \"\", \"\", \"\", \"\", \"\", \"\", \n      \"\", \"hiljad\", \"a\", \"a\", \"e\", \"e\", \"e\", \"a\",\n      \"\", \"milion\", \"a\", \"\", \"a\", \"a\", \"a\", \"a\", \n      \"\", \"milijard\", \"i\", \"a\", \"e\", \"e\", \"e\", \"i\"]';\n\n  IF Broj = 0 THEN\n    RETURN sZeroBroj;\n  ELSE\n    SET eCeo = FLOOR(ABS(Broj));\n    IF eCeo > MaxVal THEN\n      RETURN \"\";\n    END IF;\n\n    SET eStoti = (ABS(Broj - eCeo) * 100);\n    \n    IF eCeo = 0 THEN\n      SET Result = sZeroBroj;\n    ELSE\n      SET S = LPAD(CAST(eCeo AS CHAR), (CEIL(LENGTH(CAST(eCeo AS CHAR)) \/ 3) * 3), \"0\");\n      \n      SET brojac = 1;\n      WHILE LENGTH(S) > 0 DO\n        SET Trojka = SUBSTRING(S, LENGTH(S) - 2, 3);\n        SET S = LEFT(S, LENGTH(S) - 3);\n\n        IF Trojka <> \"000\" THEN\n          IF SUBSTRING(Trojka, 2, 1) = \"0\" OR SUBSTRING(Trojka, 2, 1) = \"1\" THEN\n            SET i = brojac MOD 2;\n            SET i = 20 * i + CAST(SUBSTRING(Trojka, 2, 2) AS UNSIGNED);\n            SET Temp = JSON_UNQUOTE(JSON_EXTRACT(@Jedinice, concat('$[',1+i,']')));\n          ELSE\n            SET i = brojac MOD 2;\n            SET i = 20 * i + CAST(SUBSTRING(Trojka, 3, 1) AS UNSIGNED);\n            SET k = CAST(SUBSTRING(Trojka, 2, 1) AS UNSIGNED);\n            SET Temp = CONCAT(JSON_UNQUOTE(JSON_EXTRACT(@Desetice, concat('$[',1 + k,']'))), JSON_UNQUOTE(JSON_EXTRACT(@Jedinice, concat('$[',1+i,']'))));\n          END IF;\n\n          SET i = CAST(SUBSTRING(Trojka, 1, 1) AS UNSIGNED);\n          SET Temp = CONCAT(JSON_UNQUOTE(JSON_EXTRACT(@Stotine, concat('$[',1 + i,']'))), Temp);\n          \n          SET i = brojac * 8;\n          SET k = 1 + CAST(SUBSTRING(Trojka, 2, 2) AS UNSIGNED);\n\n          IF k <= 5 THEN\n            SET Temp = CONCAT(Temp, JSON_UNQUOTE(JSON_EXTRACT(@Prilozi, concat('$[',1 + i,']'))), JSON_UNQUOTE(JSON_EXTRACT(@Prilozi, concat('$[',1 + i + k,']'))));\n          ELSE\n            SET Temp = CONCAT(Temp, JSON_UNQUOTE(JSON_EXTRACT(@Prilozi, concat('$[',1 + i,']'))), JSON_UNQUOTE(JSON_EXTRACT(@Prilozi, concat('$[',1 + i + 6,']'))));\n          END IF;\n          \n          SET Result = CONCAT(Temp, Result);\n        END IF;\n        \n        SET brojac = brojac + 1;\n      END WHILE;\n    END IF;\n  END IF;\n\n  RETURN CONCAT(Result, \" i \", LPAD(CAST(eStoti AS CHAR), 2, '0'), '\/100');\nEND<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Pri\u010da ide ovako, treba mi nabrzaka funkcija a imam je u plsql-u na Oracle-u. Odem na chatGpt on napravi kod ali sa ARRAY tipom koji moj MySQL 8 ne pozna. Onda zamijenim ARRAY sa JSON stringom i iskoristim funkcije JSON_EXTRACT i JSON_UNQUOTE. Zbog indekas mora sam dodati na po\u010detku JSON stringa po jedan prazan string … <\/p>\n
Continue reading “MYSQL funkcija koja pretvara iznos u tekst”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[5],"tags":[15,14],"class_list":["post-1257","post","type-post","status-publish","format-standard","hentry","category-it","tag-function","tag-mysql"],"_links":{"self":[{"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/posts\/1257","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/comments?post=1257"}],"version-history":[{"count":6,"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/posts\/1257\/revisions"}],"predecessor-version":[{"id":1263,"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/posts\/1257\/revisions\/1263"}],"wp:attachment":[{"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/media?parent=1257"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/categories?post=1257"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cdorde.com\/index.php\/wp-json\/wp\/v2\/tags?post=1257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}