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?
Calculated fields Cyrillic characters problem
Re: Calculated fields Cyrillic characters problem
Hi,
can you post the queries you are using? What does
compression in the calculated field
mean?
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.
Re: Calculated fields Cyrillic characters problem
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
Re: Calculated fields Cyrillic characters problem
Just an idea for a different approach using integer id's instead of language-dependent names:
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
- Create a new table named charge_types.
This will be a lookup table for your charge_types having columns id and name - 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. - 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, replacebyCode: Select all
IF (`tasks`.`charge_type` = 'Per Hour' ...
Code: Select all
IF (`tasks`.`charge_type` = 1 ...
- Generate the application
- Insert the necessary charge_types: [id=1, name="Ðа ЧаÑ"], [id=2, name="..."], ...
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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Calculated fields Cyrillic characters problem
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:
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.
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
Re: Calculated fields Cyrillic characters problem
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.
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.