Re: [WebERP-developers] long description translation

[Rafael C. <raf...@gm...>]

Hi,

Sorry by the delay.

* Ok. Modifications in stockdescriptiontranslations may or may not be used
to store the item descriptions in the default language. We can work on any
of both codes.

* If we concentrate all descriptions in a single table, to prevent a script
fails (because we forgot to update it), we can leave the fields (in
stockmaster) without dropping in this version (Comment). In both cases
(concentrated or not), it is one query. E.g. (changes in bold):

$Query = "
    SELECT
        stockmaster.stockid,

*        stockdescriptiontranslations.descriptiontranslation,
stockdescriptiontranslations.longdescription,*
        stockmaster.categoryid,
        stockmaster.units,
        stockmaster.mbflag,
        stockmaster.discontinued,
        stockmaster.controlled,
        stockmaster.serialised,
        stockmaster.perishable,
        stockmaster.eoq,
        stockmaster.volume,
        stockmaster.grossweight,
        stockmaster.netweight,
        stockmaster.barcode,
        stockmaster.discountcategory,
        stockmaster.taxcatid,
        stockmaster.decimalplaces,
        stockmaster.nextserialno,
        stockmaster.pansize,
        stockmaster.shrinkfactor
    FROM
        stockmaster

*    INNER JOIN        stockdescriptiontranslations ON (stockmaster.stockid
= stockdescriptiontranslations.**stockid)*
    WHERE
        stockmaster.stockid = '".$StockID."'"
    ;

if we do not want a "fall-back" to default language, the INNER JOIN will be:
*        stockdescriptiontranslations ON (stockmaster.stockid =
stockdescriptiontranslations.stockid AND '".$Language."'=
stockdescriptiontranslations.language_id)*

Advantages: In multi-languages companies, users can have the description in
UserSettings' language. Or if they switch to customers' language, see all
the system as the customer point of view.

* About Google Translate API:

Yes, I agree. I prefer Google Translate as the first step, not the last one.

I do not trust on any automatic translator (Babylon, Systrans, Word Magic,
Google Translate, etc.). They are helpful but inaccurate and imprecise. For
one word in a language, other language has several words, each one with
accurate and precise meanings. Also, they have other mistakes: frequently
lack of agreement in gender and number (when translate to French),
frequently wrong adjective order (when translate to Spanish), etc.
Recently, we saw the effect of an automatic translation of "Pan size":
"Panoramic size" in Spanish, "Dish size" in French, "Round size" in German
(all no sense).

Also, other reason: company "scope" may be variable. I explain myself:
+ In marketing, the best is a "polished" description in the customer's
language. If you do not have it, it is better to have a "not so good"
translation than nothing ("Not available").
+ But in some countries, for legal purposes, invoices and other documents
with descriptions in languages other than country's language are not valid.
Also, in some countries, local language is "mandatory" over other languages
(if there is a mistake in automatic translation to local language, company
will be in troubles).

I prefer to have an human-translated description and the choice between:
(1) fall back to system default language; (2) automatic translation (Google
Translate API); and (3) say "Not available".

* About description in stockmaster

Depending on the way we choose (coding), we can
(1) Leave stockmaster.description and stockmaster.longdescription as a
backup, in case of we concentrate descriptions.
(2) Leave stockmaster.description and stockmaster.longdescription as is, in
case of we do not concentrate descriptions.


(Comment): It is also the same problem if we "standardise" title, label,
id, field-name and variable-name of shortdescription and longdescription to
an information exchange standard name
(e.g. Today we have label="Part Description (short)" and fieldname="
descriptiontranslation" (in stockdescriptiontranslations); label="Part
Description (long):" and fieldname="longdescription").
Best regards, Rafael.


2014-12-19 14:43 GMT-06:00 Phil Daintree <ph...@lo...>:

>  Hi Rafael,
>
> I see what you did to stockdescriptiontranslations - adding the new field
> for longdescription and a field for last update.
> I am not sure about having the main item description also in that table.
> There are a LOT of places where we refer to stockmaster.description and
> everywhere that would need to add another query to get the description or
> the script would fail. My idea with the stockdescriptiontranslations was to
> have the description presented to customers in their language. The business
> operates in only the one adopted language.
> The google translate API sounds good - but I wonder about the response if
> we try to use it to print a long invoice where the item descriptions are
> not translated? Better I think to populate the table appropriately using
> the google translate API so that the data is available when we need it.
> Maybe when we set up the item to start with the google traslate API gives a
> first stab at the translated description if you use a button or something
> like that would then avoid empty translations from the beginning.
>
> We use stockmaster.description and stockmaster.longdescription I think it
> best to keep the fields in descriptiontranslation table to match.
>
> Phil
>
> Phil Daintree
> Logic Works Ltd - +64 (0)275 567890http://www.logicworks.co.nz
>
> On 19/12/14 07:08, Rafael Chacón wrote:
>
>  Hi,
>
> * I agree that removing fields is risky. That is why I recommended add
> fields rather than change them. If we forget one, only one, instance that
> can cause problems. I suggestion to leave them one generation as backup (
> (to be dropped in the version after the version with the change).
>
> * I think we can improve code speed and its readability merging ALL item
> descriptions in ONE table, instead of penalizing everybody with this code.
> I think this because:
> + One place to store description improves code in: no recall to the
> default-languge, one IF() less {the if() that is used to define which table
> to select}.
> + More simple code: only one query (instead of two): same query for the
> default language, same query for no-default language.
>
> * When no description in selected language, I am adding the option of to
> use the Google Translate API (by the way, nice job!). For manual purposes:
> + $text = text to trasnlate.
> + $target = target language, ISO language code of the language to which
> the text will be translated.
>  Is it correct?
>
> * Database changes:
> + I leave descriptiontranslation.descriptiontranslation for
> short_description translation (now is in use for this). Probably is better
> to rename it to shortdescription for readability. (?)
> + I added the field longdescription. I used NULL for "not available";
> reasons: saves space/transmission and use the IS NULL and IS NOT NULL
> operators.
> + I added a field for the version control of the record.
>
>  Best regards, Rafael.
>
> 2014-12-18 2:26 GMT-06:00 Phil Daintree <ph...@lo...>:
>>
>>  Looks like there is a bug there - I thought I was only creating a
>> record where there was a non-empty translation - I am committing what I
>> think will fix it now.
>> What a cool idea with google translate!! Great stuff :-)
>>
>> Phil
>>
>> Phil Daintree
>> Logic Works Ltd - +64 (0)275 567890http://www.logicworks.co.nz
>>
>>   On 18/12/14 13:35, Pak Ricard wrote:
>>
>> Hi:
>>
>>  I think we are OK with existing table and adding some fields will be
>> OK, but there are some issues I would like to point out,
>>
>>  Why, on items without traslation, do we keep a row in that table? Why
>> do we have rows without any useful information? I think it makes SQL
>> queries more complicated, and I can't see the advantage of this. Please
>> check capture01 attached file from phpMyAdmin. For all items without
>> translation, there's a row with stockid, and "Array" as language_id, and no
>> descriptiontranslation. No clue why. Is it a bug or serves some needs I
>> can't see?
>>
>>  I agree with Phil that the long description addition should be a "copy
>> - paste" of the short description one. At first glance should not be
>> difficult. That's why I supposed it was already coded or not coded for some
>> reason.
>>
>>  I think we should add a field called revisionneeded set to TRUE when
>> description or long description have been modified or FALSE otherwise.
>> Then, some script to list the items needing revision of translation.
>> Usually the person translating is not the same as the person in charge of
>> maintaining the item info in original language.
>>
>>  I am working with automatic translation API from Google
>> https://console.developers.google.com. It's a Google paid service, and I
>> think it's worth it (20 USD for 1.000.000 characters translated), as it
>> does the rough job and humans only need to revise and correct here and
>> there.
>>
>>  First tests have been succesful, so once we have this table stockdescriptiontranslations
>> up and running OK I will commit some scripts, mainly:
>> script automatically translating the descriptions without translation or
>> recently modified.
>> script to show automatic translations needing human supervision (probably
>> the same or a mod of the script mentioned in previous paragraph)
>>
>>  Specially to Rafael: I add / modify fields quite often, and never had
>> an issue, except when I shortened a text field :-( . Do a table backup just
>> before start messing around, just in case.
>>
>>  Hope it helps and we all find a clear way to move forward on this.
>>
>>
>>  Regards,
>> Ricard
>>
>> 2014-12-18 6:47 GMT+08:00 Rafael Chacón <raf...@gm...>:
>>
>>>
>>> Hi,
>>>
>>> My arguments:
>>>
>>> * About tables: IMHO (but I am not a PHP programmer),
>>>
>>> CREATE TABLE IF NOT EXISTS `stockdescriptiontranslations` (
>>>   `stockid` varchar(20) NOT NULL DEFAULT '',
>>>   `language_id` varchar(10) NOT NULL DEFAULT 'en_GB.utf8',
>>>   `descriptiontranslation` varchar(50) NOT NULL,
>>>   PRIMARY KEY (`stockid`,`language_id`)
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>>
>>> and
>>>
>>> CREATE TABLE IF NOT EXISTS `stockdescription` (
>>>   `stockid` varchar(20) NOT NULL DEFAULT '' COMMENT 'Item code',
>>>   `language` varchar(10) NOT NULL DEFAULT '' COMMENT 'Item language
>>> code',
>>>   `short` varchar(50) DEFAULT NULL COMMENT 'Item short description',
>>>   `long` text COMMENT 'Item long description',
>>>   PRIMARY KEY (`stockid`,`language`)
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Item descriptions';
>>>
>>> look very similar. I suppose it will be easy to "update" the first
>>> table: add new fields, modify others (I have the code to create the second
>>> table, but not to update de first table).
>>>
>>> * About "hot plug-in": I am not sure if that is a common need, but I am
>>> worry that if we modify fields instead of adding fields --if there is any
>>> error or oversight-- we could affect the company operation. My suggestion
>>> is to add fields of the second table inside de first table (if use old
>>> table).
>>>
>>> * About compatibility with CARTwebERP: Months before Mo.Kelly dead, we
>>> were working on this. I do not know how widespread is this. I saw the code
>>> in Joomla! Extensions Directory (http://extensions.joomla.org). It has
>>> my translation to Spanish and to French, but I can not see anything related
>>> with additional tables.
>>>
>>>  * About speed and readability of the code: I prefer the argument of
>>> someone who knows better this topic.
>>>
>>>  Best regards, Rafael.
>>>
>>>
>>> 2014-12-17 15:39 GMT-06:00 <ph...@lo...>:
>>>
>>>> I wasn't thinking it would be that difficult... we don't need a new
>>>> table do we?
>>>> Why can't we use the existing table and just add a field for the
>>>> translated long description ... applying similar logic for the
>>>> translation was we do for the short description?
>>>>
>>>> Phil
>>>>
>>>> On 2014-12-17 13:58, Rafael Chacón wrote:
>>>> > Hi Ricard,
>>>> >
>>>> > I will be glad to commit changes for short and long description
>>>> > translation. but I need help from PHP programmers. I explain myself:
>>>> >
>>>> > * We use a new table (not an exist table):
>>>> >  CREATE TABLE IF NOT EXISTS `stockdescription` (
>>>> >    `stockid` varchar(20) NOT NULL DEFAULT '' COMMENT 'Item code',
>>>> >    `language` varchar(10) NOT NULL DEFAULT '' COMMENT 'Item language
>>>> > code',
>>>> >    `short` varchar(50) DEFAULT NULL COMMENT 'Item short description',
>>>> >    `long` text COMMENT 'Item long description',
>>>> >    PRIMARY KEY (`stockid`,`language`)
>>>> >  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Item descriptions';
>>>> >
>>>> > This is for: (1) "Hot add" to a working copy of webERP and "secure
>>>> > backup" of old operation; (2) compatibility with a Joomla extension
>>>> > e-Cart;"Minor" speed and readability (?).
>>>> >
>>>> > --> To commit changes, Do we use a new table or modify an existing
>>>> > one?
>>>> >
>>>> > * We have to modify several scripts (e.g. Stocks.php,
>>>> > PrintCustTrans.php, PrintCustTransportrait.php, etc.). When there is
>>>> > no description in the selected language, he have two code branches:
>>>> >
>>>> > (1) "Fall back" to system language (defined in ~/config.php,
>>>> > $DefaultLanguage = 'xx_XX.utf8';). If is null or empty, _('Not
>>>> > available'). Useful when we forgot to setup a translation; but
>>>> > customer sees a description in other language.
>>>> >
>>>> > (2) Directly sets to _('Not available'). A little bit faster, but no
>>>> > information is shown to the customer (only "not available").
>>>> >
>>>> > --> To commit changes, Do we use code with or without the "fall back"
>>>> > to system language?
>>>> >
>>>> > * Input window for long description translation.
>>>> >  It is recommended to have "side-by-side" long description
>>>> > translations, but in this case it is a big and uncomfortable window.
>>>> >
>>>> > --> Ideas? Suggestions?
>>>> >
>>>> > Best regards, Rafael.
>>>> >
>>>> > Hi Rafael:
>>>> >
>>>> > i'm also needing the short and long description to use on the shop
>>>> > online. It would be great if you could commit (or send via email) the
>>>> > work done to maintain the translations of long descriptions in webERP.
>>>> > I could help finish it :-)
>>>> >
>>>> > Regards,
>>>> > Ricard
>>>> >
>>>> > 2014-12-17 14:00 GMT+08:00 Rafael Chacón
>>>> > <raf...@gm...>:
>>>> >
>>>> >> Hi,
>>>> >>
>>>> >> I am working with stockdescription table for translation of short
>>>> >> and long description to use in a e-cart. It is not fully tested and
>>>> >> the main problem is the usability (easy to maintain the
>>>> >> translations).
>>>> >> Also, the field unit of mesure needs translation, except for unit
>>>> >> of mesure symbols (m, m2, L, kg, ...).
>>>> >>
>>>> >> Best regards, Rafael.
>>>> >> El 16/12/2014 23:02, "Phil Daintree" <ph...@lo...>
>>>> >> escribió:
>>>> >>
>>>> >> I have not coded that, but no reason why we couldn't add a text
>>>> >> field to stockdescriptiontranslations for longdescriptiontranslation
>>>> >> and add in a field to the stock form to allow the field to be
>>>> >> translated to the maintained language(s)
>>>> >>
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
>>>> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
>>>> with Interactivity, Sharing, Native Excel Exports, App Integration &
>>>> more
>>>> Get technology previously reserved for billion-dollar corporations, FREE
>>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
>>>> _______________________________________________
>>>> Web-erp-developers mailing list
>>>> Web...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
>>> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
>>> with Interactivity, Sharing, Native Excel Exports, App Integration & more
>>> Get technology previously reserved for billion-dollar corporations, FREE
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Web-erp-developers mailing list
>>> Web...@li...
>>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>>
>>>
>>
>> ------------------------------------------------------------------------------
>> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
>> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
>> with Interactivity, Sharing, Native Excel Exports, App Integration & more
>> Get technology previously reserved for billion-dollar corporations, FREEhttp://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
>>
>>
>>
>> _______________________________________________
>> Web-erp-developers mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
>> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
>> with Interactivity, Sharing, Native Excel Exports, App Integration & more
>> Get technology previously reserved for billion-dollar corporations, FREE
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Web-erp-developers mailing list
>> Web...@li...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREEhttp://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
>
>
>
> _______________________________________________
> Web-erp-developers mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>
>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
>
> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>