";
$httpString .= "\n";
if ($tableName != "") {
$httpString .= getRecords($database, $tableName, $firstCall, $updatedOids);
}
$httpString .= "\n";
if ($compress64) {
$httpString = compress64($httpString);
}
echo "$httpString";
// DEFINITIONS DE FONCTION
/**
*
* @param Database $db
* @param string $tableName
* @return string
*/
function getRecords($db, $tableName, $firstCall=0, $updatedOids="") {
$httpString = '';
$sqlColumnAdd = '';
$sqlColumnComplement = "''::text";
$sqlTableJoin = "";
$sqlNbRefJoin = "";
$sqlSort = "$tableName.code";
$httpString_tmp = "";
if ($tableName == 't_articles') {
$sqlColumnAdd .= ",$tableName.gere_en_stock ";
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref ";
$sqlColumnAdd .= ",$tableName.article_reference_cacic_id AS cacic_id ";
$sqlColumnAdd .= ",$tableName.article_reference_cti_id AS rfcti_id ";
$sqlColumnComplement = "COALESCE(t_types_articles.texte,'')";
$sqlColumnComplement .= " || ' ' || COALESCE('(Ref:' || t_article_reference_cacic.code || ')','')";
$sqlColumnComplement .= " || ' ' || COALESCE('(Ref:' || t_article_reference_cti.code || ')','')";
$sqlColumnComplement .= " || ' ' || CASE WHEN $tableName.gere_en_stock IS DISTINCT FROM '0' THEN ''::text ELSE '(Non Géré en stock)'::text END";
$sqlTableJoin = "LEFT JOIN eco.t_types_articles ON $tableName.type_id = t_types_articles.oid AND $tableName.type_id <> 0 ";
$sqlTableJoin .= "LEFT JOIN eco.t_article_reference_cacic ON $tableName.article_reference_cacic_id = t_article_reference_cacic.oid AND $tableName.article_reference_cacic_id <> 0 ";
$sqlTableJoin .= "LEFT JOIN eco.t_article_reference_cti ON $tableName.article_reference_cti_id = t_article_reference_cti.oid AND $tableName.article_reference_cti_id <> 0 ";
$sqlNbRefJoin = "LEFT JOIN (SELECT article_id AS subview_nbref_id, count(*) AS nbref FROM eco.p_lignes_commandes GROUP BY 1) subview_nbref ON t_articles.oid = subview_nbref_id";
$sqlSort = "$tableName.texte";
}
if ($tableName == 't_types_articles') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlColumnAdd .= ",defaut_selon";
$sqlColumnAdd .= ",defaut_selon_codes";
$sqlNbRefJoin = "LEFT JOIN (SELECT type_id AS subview_nbref_id, count(*) AS nbref FROM eco.t_articles GROUP BY 1) subview_nbref ON t_types_articles.oid = subview_nbref_id";
}
if ($tableName == 't_familles_articles') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlNbRefJoin = "LEFT JOIN (SELECT famille_id AS subview_nbref_id, count(*) AS nbref FROM eco.t_articles GROUP BY 1) subview_nbref ON t_familles_articles.oid = subview_nbref_id";
}
if ($tableName == 't_sous_familles_articles') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlNbRefJoin = "LEFT JOIN (SELECT sous_famille_id AS subview_nbref_id, count(*) AS nbref FROM eco.t_articles GROUP BY 1) subview_nbref ON t_sous_familles_articles.oid = subview_nbref_id";
}
if ($tableName == 't_categories_articles') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlNbRefJoin = "LEFT JOIN (SELECT categorie_id AS subview_nbref_id, count(*) AS nbref FROM eco.t_articles GROUP BY 1) subview_nbref ON t_categories_articles.oid = subview_nbref_id";
}
if ($tableName == 't_sous_categories_articles') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlNbRefJoin = "LEFT JOIN (SELECT sous_categorie_id AS subview_nbref_id, count(*) AS nbref FROM eco.t_articles GROUP BY 1) subview_nbref ON t_sous_categories_articles.oid = subview_nbref_id";
}
if ($tableName == 't_fournisseurs') {
$sqlColumnAdd .= ",COALESCE(nbref,0)+COALESCE(nbref2,0) AS nbref, $tableName.fournisseur_reference_cacic_id AS cacic_id, $tableName.fournisseur_reference_cti_id AS rfcti, $tableName.fusionner_vers_fournisseur_id";
$sqlColumnComplement = "CASE WHEN $tableName.fusionner_vers_fournisseur_id > 0 THEN 'Fusionné vers '|| COALESCE(t_fournisseurs_fusion.texte || ' ('||t_fournisseurs_fusion.code||'/'||t_fournisseurs_fusion.code_original||')','?') ELSE COALESCE('CMD : '||to_char(date_commande_last,'YYYY/MM'),'') || ' ' || COALESCE('MVT : '||to_char(date_mouvement_last,'YYYY/MM'),'')END";
$sqlTableJoin .= "LEFT JOIN eco.t_fournisseur_reference_cacic ON $tableName.fournisseur_reference_cacic_id = t_fournisseur_reference_cacic.oid AND $tableName.fournisseur_reference_cacic_id <> 0 ";
$sqlTableJoin .= "LEFT JOIN eco.t_fournisseur_reference_cti ON $tableName.fournisseur_reference_cti_id = t_fournisseur_reference_cti.oid AND $tableName.fournisseur_reference_cti_id <> 0 ";
$sqlNbRefJoin = "LEFT JOIN (SELECT fournisseur_id AS subview_nbref_id, count(*) AS nbref, MAX(date_commande) AS date_commande_last FROM eco.p_commandes GROUP BY 1) subview_nbref ON t_fournisseurs.oid = subview_nbref_id";
$sqlNbRefJoin .= " LEFT JOIN (SELECT fournisseur_id AS subview_nbref2_id, count(*) AS nbref2, MAX(date) AS date_mouvement_last FROM eco.p_mouvements_articles GROUP BY 1) subview_nbref2 ON t_fournisseurs.oid = subview_nbref2_id";
$sqlTableJoin = "LEFT JOIN eco.t_fournisseurs t_fournisseurs_fusion ON $tableName.fusionner_vers_fournisseur_id = t_fournisseurs_fusion.oid";
$sqlSort = "$tableName.texte";
}
if ($tableName == 't_types_fournisseurs') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlNbRefJoin = "LEFT JOIN (SELECT type_id AS subview_nbref_id, count(*) AS nbref FROM eco.t_fournisseurs GROUP BY 1) subview_nbref ON t_types_fournisseurs.oid = subview_nbref_id";
}
if ($tableName == 't_reference_input') {
$sqlColumnComplement = "CASE WHEN nb_lignes > 0 THEN 'Importé le ' || to_char(import_date,'DD/MM/YYYY') || ' (' || nb_lignes || ' lignes)' ELSE 'Vide' END";
}
if ($tableName == 't_article_reference_cacic') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlColumnComplement = "t_types_articles.texte";
$sqlNbRefJoin = "LEFT JOIN (SELECT article_reference_cacic_id AS subview_nbref_id, count(*) AS nbref FROM eco.t_articles GROUP BY 1) subview_nbref ON t_article_reference_cacic.oid = subview_nbref_id";
$sqlTableJoin = "JOIN eco.t_types_articles ON $tableName.type_id = t_types_articles.oid";
$sqlSort = "$tableName.texte";
}
if ($tableName == 't_article_reference_cti') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlColumnAdd .= ", ','||COALESCE(subview_nbref.rfoids,'') AS rfoids";
$sqlColumnComplement = "CASE WHEN $tableName.type_id <> 0 THEN t_types_articles.texte ELSE '' END";
$sqlNbRefJoin = "LEFT JOIN (SELECT article_reference_cti_id AS subview_nbref_id, count(*) AS nbref, base.cti_group_concat_without_sep(texte || '(' || code || ')' || chr(13)) AS arlist, base.cti_group_concat_without_sep(t_articles.oid||',') AS rfoids FROM eco.t_articles GROUP BY 1) subview_nbref ON t_article_reference_cti.oid = subview_nbref_id";
$sqlTableJoin = "JOIN eco.t_types_articles ON $tableName.type_id = t_types_articles.oid";
$sqlSort = "$tableName.texte";
}
if ($tableName == 't_fournisseur_reference_cacic') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlNbRefJoin = "LEFT JOIN (SELECT fournisseur_reference_cacic_id AS subview_nbref_id, count(*) AS nbref FROM eco.t_fournisseurs GROUP BY 1) subview_nbref ON t_fournisseur_reference_cacic.oid = subview_nbref_id";
$sqlSort = "$tableName.texte";
}
if ($tableName == 't_types_mouvements') {
$sqlColumnAdd .= ",COALESCE(particularite_inverser_signe,'0'::text) AS particularite_inverser_signe";
$sqlColumnAdd .= ",COALESCE(sens_force,''::text) AS sens_force";
$sqlColumnComplement = "CASE WHEN $tableName.particularite_inverser_signe = '1' THEN 'Inversion signe' ELSE '' END || ' ' ||
CASE WHEN $tableName.sens_force = 'E' THEN 'Entrée' WHEN $tableName.sens_force = 'S' THEN 'Sortie' ELSE '' END";
}
if ($tableName == 't_fournisseur_reference_cti') {
$sqlColumnAdd .= ",COALESCE(nbref,0) AS nbref";
$sqlColumnAdd .= ", ','||COALESCE(subview_nbref.rfoids,'') AS rfoids";
$sqlNbRefJoin = "LEFT JOIN (SELECT fournisseur_reference_cti_id AS subview_nbref_id, count(*) AS nbref, base.cti_group_concat_without_sep(texte || '(' || code || ')' || chr(13)) AS frlist, base.cti_group_concat_without_sep(t_fournisseurs.oid||',') AS rfoids FROM eco.t_fournisseurs GROUP BY 1) subview_nbref ON t_fournisseur_reference_cti.oid = subview_nbref_id";
$sqlSort = "$tableName.texte";
}
$sqlcmd = "
SELECT
$tableName.oid,
$tableName.code,
$tableName.texte,
$tableName.texte_court,
$tableName.code_original,
$sqlColumnComplement AS complement
$sqlColumnAdd
FROM eco.$tableName
$sqlTableJoin
$sqlNbRefJoin
WHERE $tableName.oid > 0 ";
if ($updatedOids != "") {
$sqlcmd.= "AND
$tableName.oid in ($updatedOids) ";
}
$sqlcmd.= "
ORDER BY $sqlSort";
if ($tableName == 't_divers') {
$sqlcmd = "
SELECT
code,
texte,
valeur,
valeur_date,
description AS complement,
CASE WHEN show_info_module THEN 1 ELSE 0 END AS show_info_module
FROM eco.$tableName
ORDER BY code
";
}
if ($tableName == 't_type_article_cti_rule') {
$sqlcmd = "
SELECT
t_type_article_cti_rule.oid,
''::text AS code,
t_type_article_cti_rule.texte,
type_article_cti_id,
t_type_article_cti.texte AS type_article_cti_texte,
priorite,
array_to_string(
ARRAY[
CASE WHEN liste_type_article <> '' THEN 'Type:' || liste_type_article ELSE NULL END,
CASE WHEN liste_compte <> '' THEN 'Compte:' || liste_compte ELSE NULL END,
CASE WHEN liste_famille <> '' THEN 'Famille:' || liste_famille ELSE NULL END,
CASE WHEN liste_sous_famille <> '' THEN 'Sous-Famille:' || liste_sous_famille ELSE NULL END,
CASE WHEN liste_categorie <> '' THEN 'Catégorie:' || liste_categorie ELSE NULL END,
CASE WHEN liste_sous_categorie <> '' THEN 'Sous-Catégorie:' || liste_sous_categorie ELSE NULL END,
CASE WHEN liste_article <> '' THEN 'Article:' || liste_article ELSE NULL END
],' ') AS rule_texte,
liste_type_article,
liste_compte,
liste_famille,
liste_sous_famille,
liste_categorie,
liste_sous_categorie,
liste_article
FROM eco.$tableName
JOIN eco.t_type_article_cti ON type_article_cti_id = t_type_article_cti.oid
ORDER BY priorite, texte
";
}
$result = $db->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $db->nextRecordAssoc();
if ($record != FALSE) {
$ok = TRUE;
$httpString_tmp .= '\r\n 64000) {
$httpString .= $httpString_tmp;
$httpString_tmp = "";
}
}
}
}
$httpString .= $httpString_tmp;
if ($firstCall != "0") {
if ($tableName == 't_articles') {
$httpString .= getArray_articles_reference_cacic($db);
$httpString .= getArray_articles_reference_cti($db);
}
if ($tableName == 't_fournisseurs') {
$httpString .= getCombo_fournisseurs($db);
$httpString .= getArray_fournisseurs_reference_cacic($db);
$httpString .= getArray_fournisseurs_reference_cti($db);
}
if ($tableName == 't_article_reference_cti') {
$httpString .= getList_articles($db);
}
if ($tableName == 't_fournisseur_reference_cti') {
$httpString .= getList_fournisseurs($db);
}
if ($tableName == 't_type_article_cti_rule') {
$httpString .= getCombo_type_article_cti($db);
}
}
return $httpString;
}
function getList_articles($db) {
$httpString = "\r\n";
$httpString_tmp = "";
$sqlcmd = "SELECT
t_articles.code,
t_articles.texte,
trim(COALESCE(t_lpp.code,'') || ' ' || COALESCE(t_ucd.code,'')) AS lppucd,
t_articles.oid
FROM eco.t_articles
LEFT JOIN base.t_lpp ON lpp_id = t_lpp.oid AND lpp_id <> 0
LEFT JOIN base.t_ucd ON ucd_id = t_ucd.oid AND ucd_id <> 0
WHERE t_articles.oid <> 0
ORDER BY t_articles.texte";
$result = $db->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $db->nextRecordAssoc();
if ($record != FALSE) {
$ok = TRUE;
$code = toHTML(trim($record['code']));
$texte = toHTML(trim($record['texte']));
$lppucd = toHTML(trim($record['lppucd']));
$oid = toHTML(trim($record['oid']));
$httpString_tmp .= "";
if (strlen($httpString_tmp) > 64000) {
$httpString .= $httpString_tmp;
$httpString_tmp = "";
}
}
}
}
$httpString .= $httpString_tmp;
$httpString .= "\r\n";
return $httpString;
}
function getArray_articles_reference_cacic($db) {
$httpString = "\r\n";
$httpString_tmp = "";
$sqlcmd = "SELECT
t_article_reference_cacic.code,
t_article_reference_cacic.texte,
t_article_reference_cacic.oid
FROM eco.t_article_reference_cacic
WHERE oid <> 0
ORDER BY code";
$result = $db->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $db->nextRecordAssoc();
if ($record != FALSE) {
$ok = TRUE;
$code = toHTML(trim($record['code']));
$texte = toHTML(trim($record['texte']));
$oid = toHTML(trim($record['oid']));
$httpString_tmp .= "";
if (strlen($httpString_tmp) > 64000) {
$httpString .= $httpString_tmp;
$httpString_tmp = "";
}
}
}
}
$httpString .= $httpString_tmp;
$httpString .= "\r\n";
return $httpString;
}
function getArray_articles_reference_cti($db) {
$httpString = "\r\n";
$httpString_tmp = "";
$sqlcmd = "SELECT
t_article_reference_cti.code,
t_article_reference_cti.texte,
t_article_reference_cti.oid
FROM eco.t_article_reference_cti
WHERE oid <> 0
ORDER BY code";
$result = $db->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $db->nextRecordAssoc();
if ($record != FALSE) {
$ok = TRUE;
$code = toHTML(trim($record['code']));
$texte = toHTML(trim($record['texte']));
$oid = toHTML(trim($record['oid']));
$httpString_tmp .= "";
if (strlen($httpString_tmp) > 64000) {
$httpString .= $httpString_tmp;
$httpString_tmp = "";
}
}
}
}
$httpString .= $httpString_tmp;
$httpString .= "\r\n";
return $httpString;
}
function getCombo_fournisseurs($db) {
global $database;
$httpString = "";
$httpString = $httpString . "\r\n";
$httpString = $httpString . "\r\n";
$sqlcmd = "SELECT oid, texte || ' ('||code || '/' || code_original||')' AS texte FROM eco.t_fournisseurs WHERE oid > 0 ORDER BY UPPER(texte)";
$result = $db->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
$httpString_tmp = "";
while ($ok == TRUE) {
$ok = FALSE;
$record = $db->nextRecordAssoc();
if ($record != FALSE) {
$ok = TRUE;
$texte = toHTML(trim($record['texte']));
$oid = toHTML(trim($record['oid']));
$httpString_tmp .= "";
if (strlen($httpString_tmp) > 64000) {
$httpString .= $httpString_tmp;
$httpString_tmp = "";
}
}
}
}
$httpString .= $httpString_tmp;
$httpString = $httpString . "\r\n";
return $httpString;
}
function getList_fournisseurs($db) {
$httpString = "\r\n";
$httpString_tmp = "";
$sqlcmd = "SELECT
t_fournisseurs.code,
t_fournisseurs.texte,
t_fournisseurs.oid
FROM eco.t_fournisseurs
WHERE oid <> 0
ORDER BY texte";
$result = $db->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $db->nextRecordAssoc();
if ($record != FALSE) {
$ok = TRUE;
$code = toHTML(trim($record['code']));
$texte = toHTML(trim($record['texte']));
$oid = toHTML(trim($record['oid']));
$httpString_tmp .= "";
if (strlen($httpString_tmp) > 64000) {
$httpString .= $httpString_tmp;
$httpString_tmp = "";
}
}
}
}
$httpString .= $httpString_tmp;
$httpString .= "\r\n";
return $httpString;
}
function getArray_fournisseurs_reference_cacic($db) {
$httpString = "\r\n";
$httpString_tmp = "";
$sqlcmd = "SELECT
t_fournisseur_reference_cacic.code,
t_fournisseur_reference_cacic.texte,
t_fournisseur_reference_cacic.oid
FROM eco.t_fournisseur_reference_cacic
WHERE oid <> 0
ORDER BY code";
$result = $db->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $db->nextRecordAssoc();
if ($record != FALSE) {
$ok = TRUE;
$code = toHTML(trim($record['code']));
$texte = toHTML(trim($record['texte']));
$oid = toHTML(trim($record['oid']));
$httpString_tmp .= "";
if (strlen($httpString_tmp) > 64000) {
$httpString .= $httpString_tmp;
$httpString_tmp = "";
}
}
}
}
$httpString .= $httpString_tmp;
$httpString .= "\r\n";
return $httpString;
}
function getArray_fournisseurs_reference_cti($db) {
$httpString = "\r\n";
$httpString_tmp = "";
$sqlcmd = "SELECT
t_fournisseur_reference_cti.code,
t_fournisseur_reference_cti.texte,
t_fournisseur_reference_cti.oid
FROM eco.t_fournisseur_reference_cti
WHERE oid <> 0
ORDER BY code";
$result = $db->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $db->nextRecordAssoc();
if ($record != FALSE) {
$ok = TRUE;
$code = toHTML(trim($record['code']));
$texte = toHTML(trim($record['texte']));
$oid = toHTML(trim($record['oid']));
$httpString_tmp .= "";
if (strlen($httpString_tmp) > 64000) {
$httpString .= $httpString_tmp;
$httpString_tmp = "";
}
}
}
}
$httpString .= $httpString_tmp;
$httpString .= "\r\n";
return $httpString;
}
function getCombo_type_article_cti($db) {
global $database;
$httpString = "";
$httpString = $httpString . "\r\n";
$httpString = $httpString . "\r\n";
$sqlcmd = "SELECT oid, texte FROM eco.t_type_article_cti WHERE oid > 0 ORDER BY UPPER(texte)";
$result = $db->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
$httpString_tmp = "";
while ($ok == TRUE) {
$ok = FALSE;
$record = $db->nextRecordAssoc();
if ($record != FALSE) {
$ok = TRUE;
$texte = toHTML(trim($record['texte']));
$oid = toHTML(trim($record['oid']));
$httpString_tmp .= "";
if (strlen($httpString_tmp) > 64000) {
$httpString .= $httpString_tmp;
$httpString_tmp = "";
}
}
}
}
$httpString .= $httpString_tmp;
$httpString = $httpString . "\r\n";
return $httpString;
}
?>