Calculated fields Cyrillic characters problem

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
kerelov
Veteran Member
Posts: 42
Joined: 2020-04-17 21:20
Location: Bulgaria

Calculated fields Cyrillic characters problem

Post by kerelov » 2020-05-11 16:59

Hi Guys,
I have the following problem: When I have a compression in the calculated field (`tasks`.`value`) in Appgini with a word with cyrillic characters (example `tasks`.`charge_type` = 'Фиксирана') the query fails. The field `charge_type` is a simple list defined also in Appgini like this: Фиксирана;;На Час;;Комбинирана. If I change the list with latin characters all works fine. Also when I execute the query in phpmyadmin it also works fine. My project file is set to UTF8, tables are also defined UTF8_general_ci, the main setting in Appgini is also set to UTF8. How I can SET NAMES UTF8 for this single query in the calculated filed or for the entire project?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated fields Cyrillic characters problem

Post by pbottcher » 2020-05-16 17:22

Hi,

can you post the queries you are using? What does

compression in the calculated field

mean?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

kerelov
Veteran Member
Posts: 42
Joined: 2020-04-17 21:20
Location: Bulgaria

Re: Calculated fields Cyrillic characters problem

Post by kerelov » 2020-05-19 08:06

This is the SQL, it works fine when using English for example `tasks`.`charge_type` = 'Per Hour'. When I change 'Per Hour' with 'На Час', it does not work. `tasks`.`charge_type` is a simple list defined in Appgini like: Per Hour;;Fixed;;Combined. When I defined it as На час;;Фиксирана;;Комбинирана and change the comparisons in the SQL it does not work. It works in phpmyadmin both English and Cyrillic characters. I think I must somehow send SET NAMES UTF8 command trough the calculated field but I don't know if it is possible together with this SELECT statement.

Code: Select all

SELECT ROUND 
(
	IF (`tasks`.`free` = 1, 0,   
		IF (`tasks`.`charge_type` = 'Fixed', `task_types`.`fee`, 
			IF(`tasks`.`charge_type` = 'Per Hour', IFNULL(`parties_tariff`.`per_hour`*`tasks`.`hours`, 0), 
				IF(`tasks`.`charge_type` = 'Combined', IFNULL(`parties_tariff`.`per_hour`*`tasks`.`hours`, 0) + IFNULL(`task_types`.`fee`, 0), 0
				)
			)
		)
	)*IF(`tasks`.`surcharge` = NULL, 1, `tasks`.`surcharge`/100), 
2) AS task_value FROM `tasks` 
LEFT JOIN `task_types` ON `tasks`.`task_type` = `task_types`.`id`
LEFT JOIN `parties_tariff` ON `tasks`.`party` = `parties_tariff`.`party` 
LEFT JOIN `fees` ON `tasks`.`case` = `fees`.`case`
WHERE `tasks`.`id` = '%ID%' LIMIT 1

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Calculated fields Cyrillic characters problem

Post by jsetzer » 2020-05-19 10:23

Just an idea for a different approach using integer id's instead of language-dependent names:
  1. Create a new table named charge_types.
    This will be a lookup table for your charge_types having columns id and name
  2. Change tasks.charge_type into a lookup, refering the new charge_types table.
    Caption for tasks.charge_type: "name"-column of charge-types table.
    At this stage you will have a new table for charge-types and every task can refer to a single charge type, configured in charge_types table.
  3. Change the SQL in your calculated field of tasks-table. In your SQL, do NOT use the charge_type-name any longer, but use the charge_type-id instead for your comparisons.

    For example, replace

    Code: Select all

    IF (`tasks`.`charge_type` = 'Per Hour' ...
    by

    Code: Select all

    IF (`tasks`.`charge_type` = 1 ... 
  4. Generate the application
  5. Insert the necessary charge_types: [id=1, name="На Час"], [id=2, name="..."], ...
This is a very common pattern which I use in many, many scenrarios. It is a bit more complex than just having the options-list, but it gives you full control.

Next to solving your problem, this solution will additionally allow you to rename your charge types later on without any problems and without new generation and deployment of your application.

Also, this solution will allow you to add additional fields to charge_types table, like factors or rates, which could have an effect on the calculation itself. This would give more flexibility, because in the future you are able to adjust factors or rates just by data-entry without touching SQL not re-deploying.

I really hope this helps you and others for modeling such typed-fields.

Best,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

kerelov
Veteran Member
Posts: 42
Joined: 2020-04-17 21:20
Location: Bulgaria

Re: Calculated fields Cyrillic characters problem

Post by kerelov » 2020-05-19 10:53

Well, I did it kind of like that. I created a new table service_fields with just two columns - bg and en - for the two languages. And now the SQL looks like this:

Code: Select all

SELECT ROUND 
(
	IF (`tasks`.`free` = 1, 0,   
		IF (`tasks`.`charge_type` = `service_fields`.`bg` AND `service_fields`.`en` = 'Fixed', `task_types`.`fee`, 
			IF(`tasks`.`charge_type`  = `service_fields`.`bg` AND `service_fields`.`en` = 'Per Hour', IFNULL(`parties_tariff`.`per_hour`*`tasks`.`hours`, 0), 
				IF(`tasks`.`charge_type` = `service_fields`.`bg` AND  `service_fields`.`en` = 'Combined', IFNULL(`parties_tariff`.`per_hour`*`tasks`.`hours`, 0) + IFNULL(`task_types`.`fee`, 0), 0
				)
			)
		)
	)*IF(`tasks`.`surcharge` = NULL, 1, `tasks`.`surcharge`/100), 
2) AS task_value FROM `tasks` 
LEFT JOIN `service_fields` ON `service_fields`.`bg` = `tasks`.`charge_type` 
LEFT JOIN `task_types` ON `tasks`.`task_type` = `task_types`.`id`
LEFT JOIN `parties_tariff` ON `tasks`.`party` = `parties_tariff`.`party` 
LEFT JOIN `fees` ON `tasks`.`case` = `fees`.`case`
WHERE `tasks`.`id` = '%ID%' LIMIT 1
It works fine now and I prefer to keep the comparison not with ID but with English word just for easy reading the code. I really don't like that I have to practically extend the language support of Appgini like this but it seems that there is no other practical solution. The file incFunctions.php in Admin directory that handles calculated fields SQL is not UTF8 encoded by default and also due to some kind of escape function or whatever it deletes the capital Cyrillic characters in the SQL. I tried N'Фиксирана' for UNICODE but it does not work too. So If this is a bug I hope it is solved in the future releases.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated fields Cyrillic characters problem

Post by pbottcher » 2020-05-19 20:43

Hi,

I agrre that this is a bug in AppGini and you shall put it on the bug page. You can add cyrillic letters to the dropdown and lookups but it is not working with the calculated fields. You can verify that if you switch the view within Appgini to edit the calculated field. Here you see already that the cyrillic letters are not handled correctly.

If you still want to use it, you could edit the ajax_calculate... php file and put your cyrillic letters into the sql statement.
But remeber that those changes will be overwritten when you recreate your app.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

Post Reply