";
$httpString = $httpString . "\n";
if ($tableName == "t_sites") {
$httpString = $httpString . getCombo_finess();
$httpString = $httpString . getRecords_sites();
}
if ($tableName == "t_journaux") {
$httpString = $httpString . getRecords_journaux();
}
if ($tableName == "t_types_compta") {
$httpString = $httpString . getRecords_types_compta();
}
if ($tableName == "t_classes_comptes") {
$httpString = $httpString . getRecords_classes_comptes();
}
if ($tableName == "t_comptes") {
$httpString = $httpString . getRecords_comptes();
if ($getOption != "refresh") {
$httpString = $httpString . getCombo_types_compta();
}
}
if ($tableName == "t_comptes_extra") {
$httpString = $httpString . getRecords_comptes_extra();
}
if ($tableName == "t_partenaires") {
$httpString = $httpString . getRecords_partenaires();
}
if ($tableName == "t_ecritures_ajustement") {
$httpString = $httpString . getRecords_ecritures_ajustement();
if ($getOption != "refresh") {
$httpString = $httpString . getCombo_sites();
$httpString = $httpString . getCombo_journaux();
$httpString = $httpString . getCombo_sections();
$httpString = $httpString . getCombo_comptes();
$httpString = $httpString . getCombo_mois();
}
}
if ($tableName == "t_budget") {
$httpString = $httpString . getRecords_budget();
if ($getOption != "refresh") {
$httpString = $httpString . getCombo_sites();
}
}
if ($tableName == "t_divers") {
$httpString .= getRecords_divers();
}
$httpString = $httpString . "\n";
$httpString = compress64($httpString);
echo "$httpString";
function getRecords_journaux() {
global $database;
$httpString = "";
$sqlcmd = "SELECT oid, code, texte, code_original, clinique_honoraire, a_nouveaux, ";
$sqlcmd = $sqlcmd . "subview.count ";
$sqlcmd = $sqlcmd . "FROM compta.t_journaux ";
$sqlcmd = $sqlcmd . "LEFT JOIN ";
$sqlcmd = $sqlcmd . "(SELECT journal_id, sum(nombre_ecritures) as count ";
$sqlcmd = $sqlcmd . "FROM compta.p_historique_ecritures_total ";
$sqlcmd = $sqlcmd . "GROUP BY journal_id) subview ";
$sqlcmd = $sqlcmd . "ON (t_journaux.oid = subview.journal_id) ";
$sqlcmd = $sqlcmd . "WHERE oid > 0 AND subview.count > 0 ORDER BY code";
$result = $database->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = $record[0];
$code = toHTML(trim($record[1]));
$texte = toHTML(trim($record[2]));
$code_original = toHTML(trim($record[3]));
$clinique_honoraire = toHTML(trim($record[4]));
$a_nouveaux = toHTML(trim($record[5]));
$nbref = $record[6] + 0;
$httpString = $httpString . "\r\n";
}
}
}
return $httpString;
}
function getRecords_sites() {
global $database;
$httpString = "";
$sqlcmd = "SELECT t_sites.oid,
t_sites.code,
t_sites.texte,
t_sites.code_original,
t_sites.clinique_honoraire,
COALESCE(t_sites.finess_id,0),
COALESCE(t_finess.code || ' ' || t_finess.texte,''),
subview.count
FROM compta.t_sites
LEFT JOIN base.t_finess ON finess_id = t_finess.oid
LEFT JOIN
(SELECT site_id, SUM(nombre_ecritures) as count
FROM compta.p_historique_ecritures_total
WHERE site_id > 0
GROUP BY site_id) subview
ON (t_sites.oid = subview.site_id)
WHERE t_sites.oid > 0 AND
subview.count > 0
ORDER BY t_sites.code";
$result = $database->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = $record[0];
$code = toHTML(trim($record[1]));
$texte = toHTML(trim($record[2]));
$code_original = toHTML(trim($record[3]));
$clinique_honoraire = toHTML(trim($record[4]));
$finess_id = toHTML(trim($record[5]));
$finess_texte = toHTML(trim($record[6]));
$nbref = $record[7] + 0;
$httpString = $httpString . "\r\n";
}
}
}
return $httpString;
}
function getRecords_types_compta() {
global $database;
$httpString = "";
$sqlcmd = "SELECT oid, code, texte, code_original, ";
$sqlcmd = $sqlcmd . "subview.count ";
$sqlcmd = $sqlcmd . "FROM compta.t_types_compta ";
$sqlcmd = $sqlcmd . "LEFT JOIN ";
$sqlcmd = $sqlcmd . "(SELECT type_compta_id, count(*) as count ";
$sqlcmd = $sqlcmd . "FROM compta.t_comptes ";
$sqlcmd = $sqlcmd . "GROUP BY type_compta_id) subview ";
$sqlcmd = $sqlcmd . "ON (t_types_compta.oid = subview.type_compta_id) ";
$sqlcmd = $sqlcmd . "WHERE oid > 0 AND subview.count > 0 ORDER BY code";
$result = $database->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = $record[0];
$code = toHTML(trim($record[1]));
$texte = toHTML(trim($record[2]));
$code_original = toHTML(trim($record[3]));
$nbref = $record[4] + 0;
$httpString = $httpString . "\r\n";
}
}
}
return $httpString;
}
function getRecords_classes_comptes() {
global $database;
$httpString = "";
$sqlcmd = "SELECT oid, code, texte, clinique, honoraire, ";
$sqlcmd = $sqlcmd . "CASE WHEN subview_1.count IS NOT null THEN subview_1.count ";
$sqlcmd = $sqlcmd . "WHEN subview_2.count IS NOT null THEN subview_2.count ";
$sqlcmd = $sqlcmd . "WHEN subview_3.count IS NOT null THEN subview_3.count ";
$sqlcmd = $sqlcmd . "WHEN subview_4.count IS NOT null THEN subview_4.count ";
$sqlcmd = $sqlcmd . "WHEN subview_5.count IS NOT null THEN subview_5.count ";
$sqlcmd = $sqlcmd . "WHEN subview_6.count IS NOT null THEN subview_6.count ";
$sqlcmd = $sqlcmd . "ELSE 0 ";
$sqlcmd = $sqlcmd . "END ";
$sqlcmd = $sqlcmd . "FROM compta.t_classes_comptes ";
$sqlcmd = $sqlcmd . "LEFT JOIN ";
$sqlcmd = $sqlcmd . "(SELECT classe_1_id, count(*) as count ";
$sqlcmd = $sqlcmd . "FROM compta.t_comptes ";
$sqlcmd = $sqlcmd . "GROUP BY classe_1_id) subview_1 ";
$sqlcmd = $sqlcmd . "ON (t_classes_comptes.oid = subview_1.classe_1_id) ";
$sqlcmd = $sqlcmd . "LEFT JOIN ";
$sqlcmd = $sqlcmd . "(SELECT classe_2_id, count(*) as count ";
$sqlcmd = $sqlcmd . "FROM compta.t_comptes ";
$sqlcmd = $sqlcmd . "GROUP BY classe_2_id) subview_2 ";
$sqlcmd = $sqlcmd . "ON (t_classes_comptes.oid = subview_2.classe_2_id) ";
$sqlcmd = $sqlcmd . "LEFT JOIN ";
$sqlcmd = $sqlcmd . "(SELECT classe_3_id, count(*) as count ";
$sqlcmd = $sqlcmd . "FROM compta.t_comptes ";
$sqlcmd = $sqlcmd . "GROUP BY classe_3_id) subview_3 ";
$sqlcmd = $sqlcmd . "ON (t_classes_comptes.oid = subview_3.classe_3_id) ";
$sqlcmd = $sqlcmd . "LEFT JOIN ";
$sqlcmd = $sqlcmd . "(SELECT classe_4_id, count(*) as count ";
$sqlcmd = $sqlcmd . "FROM compta.t_comptes ";
$sqlcmd = $sqlcmd . "GROUP BY classe_4_id) subview_4 ";
$sqlcmd = $sqlcmd . "ON (t_classes_comptes.oid = subview_4.classe_4_id) ";
$sqlcmd = $sqlcmd . "LEFT JOIN ";
$sqlcmd = $sqlcmd . "(SELECT classe_5_id, count(*) as count ";
$sqlcmd = $sqlcmd . "FROM compta.t_comptes ";
$sqlcmd = $sqlcmd . "GROUP BY classe_5_id) subview_5 ";
$sqlcmd = $sqlcmd . "ON (t_classes_comptes.oid = subview_5.classe_5_id) ";
$sqlcmd = $sqlcmd . "LEFT JOIN ";
$sqlcmd = $sqlcmd . "(SELECT classe_6_id, count(*) as count ";
$sqlcmd = $sqlcmd . "FROM compta.t_comptes ";
$sqlcmd = $sqlcmd . "GROUP BY classe_6_id) subview_6 ";
$sqlcmd = $sqlcmd . "ON (t_classes_comptes.oid = subview_6.classe_6_id) ";
$sqlcmd = $sqlcmd . "WHERE oid > 0 ORDER BY code";
$result = $database->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = $record[0];
$code = toHTML(trim($record[1]));
$texte = toHTML(trim($record[2]));
$clinique = toHTML(trim($record[3]));
$honoraire = toHTML(trim($record[4]));
$nbref = $record[5] + 0;
$httpString = $httpString . "\r\n";
}
}
}
return $httpString;
}
function getRecords_comptes() {
global $database;
$httpString = "";
$sqlcmd = "SELECT oid, numero, texte, clinique_honoraire, banque, solde_initial, inter_site,
collectif, type_compta_extra_id, numero_extra, texte_extra,
subview.count
FROM compta.t_comptes
JOIN
(SELECT COALESCE(compte_nonsigne_id,compte_id) AS compte_id, sum(nombre_ecritures) as count
FROM compta.p_historique_ecritures_total
JOIN compta.t_comptes ON compte_id = t_comptes.oid
GROUP BY 1) subview
ON t_comptes.oid = subview.compte_id
WHERE oid > 0 and oid = compte_general_id
ORDER BY numero";
$result = $database->exec($sqlcmd);
$httpString_tmp = "";
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = $record[0];
$numero = toHTML(trim($record[1]));
$texte = toHTML(trim($record[2]));
$clinique_honoraire = toHTML(trim($record[3]));
$banque = toHTML(trim($record[4]));
$solde_initial = toHTML(trim($record[5]));
$inter_site = toHTML(trim($record[6]));
$collectif = toHTML(trim($record[7]));
$type_compta_extra_id = $record[8] + 0;
$numero_extra = toHTML(trim($record[9]));
$texte_extra = toHTML(trim($record[10]));
$nbref = $record[11] + 0;
$comment = "";
if ($banque == "1") {
$comment = "BANQUE ";
}
if ($collectif == "1") {
$comment = "COLLECTIF ";
}
if ($inter_site == "1") {
$comment = $comment . "INTER-SITE ";
}
$httpString_tmp = $httpString_tmp . "\r\n";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
return $httpString;
}
function getRecords_comptes_extra() {
global $database;
$httpString = "";
$sqlcmd = "SELECT t_comptes.oid, t_comptes.numero, t_comptes.texte, t_comptes.clinique_honoraire,
t_types_compta.texte, inter_site,
subview.count
FROM compta.t_comptes
LEFT JOIN
(SELECT compte_extra_id, SUM(nombre_ecritures) as count
FROM compta.p_historique_ecritures_total
GROUP BY compte_extra_id) subview
ON (t_comptes.oid = subview.compte_extra_id)
, compta.t_types_compta
WHERE t_comptes.oid > 0 and t_comptes.oid <> t_comptes.compte_general_id
AND t_comptes.type_compta_id = t_types_compta.oid
AND subview.count > 0
ORDER BY t_comptes.numero";
$result = $database->exec($sqlcmd);
$httpString_tmp = "";
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = $record[0];
$numero = toHTML(trim($record[1]));
$texte = toHTML(trim($record[2]));
$clinique_honoraire = toHTML(trim($record[3]));
$type_compta = toHTML(trim($record[4]));
$inter_site = toHTML(trim($record[5]));
$nbref = $record[6] + 0;
$comment = "";
if ($banque == "1") {
$comment = "BANQUE";
}
if ($inter_site == "1") {
$comment = "INTER-SITE";
}
$httpString_tmp = $httpString_tmp . "\r\n";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
return $httpString;
}
function getRecords_partenaires() {
global $database;
$httpString = "";
$sqlcmd = "SELECT oid, code, texte, texte_court, code_original, intra_groupe,
subview.comptes, subview.count
FROM compta.t_partenaires
LEFT JOIN
(SELECT partenaire_id, SUM(nombre_ecritures) as count, base.cti_group_concat(DISTINCT compte_numero || ' ' || SUBSTR(compte_texte, 1 , 15)) as comptes
FROM compta.v_historique_ecritures_total_1
GROUP BY partenaire_id) subview
ON (t_partenaires.oid = subview.partenaire_id)
WHERE oid > 0 AND subview.count > 0 ORDER BY code";
$result = $database->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = $record[0];
$code = toHTML(trim($record[1]));
$texte = toHTML(trim($record[2]));
$texte_court = toHTML(trim($record[3]));
$code_original = toHTML(trim($record[4]));
$intra_groupe = toHTML(trim($record[5]));
$comptes = toHTML(trim($record[6]));
$nbref = $record[5] + 0;
$comment = "";
if ($intra_groupe == "1") {
$comment = "INTRA-GROUPE";
}
$httpString = $httpString . "\r\n";
}
}
}
return $httpString;
}
function getRecords_ecritures_ajustement() {
global $database;
$httpString = "";
$httpString_tmp = "";
$sqlcmd = "
DROP TABLE IF EXISTS w_calendrier_mois;
CREATE TEMP TABLE w_calendrier_mois AS
SELECT p_calendrier_mois.mois,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (0) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_1,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (1,89) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_2,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (2,90) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_3,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (3,91) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_4,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (4,92) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_5,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (5,93) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_6,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (6,94) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_7,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (7,95) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_8,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (8,96) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_9,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (9,97) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_10,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (10,98) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_11,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (11,99) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_12
FROM base.p_calendrier_mois
JOIN base.p_calendrier_mois p_calendrier_mois_exercice ON p_calendrier_mois_exercice.exercice_comptable = p_calendrier_mois.exercice_comptable
WHERE p_calendrier_mois.exercice_comptable IN (SELECT DISTINCT exercice_comptable FROM compta.p_mois_comptables)
GROUP BY 1
ORDER BY 1";
$result = $database->exec($sqlcmd);
$sqlcmd = "SELECT
-1 AS oid,
date(now()) AS date_ecriture,
MAX(mois_comptable) AS mois_comptable,
'' AS clinique_honoraire,
'' AS journal_code,
'' AS section_analytique_code,
0 AS site_id,
'' AS compte_numero,
'' AS texte,
'0' AS report_automatique_mois_suivant,
'' AS est_balance,
'0' AS est_import_data,
'' AS import_data,
0.00 AS montant_debit,
0.00 AS montant_credit,
0.00,
0.00,
0.00,
0.00,
0.00,
0.00,
0.00,
0.00,
0.00,
0.00,
0.00,
0.00
FROM compta.p_historique_ecritures_total
UNION ALL
SELECT
t_ecritures_ajustement.oid,
t_ecritures_ajustement.date_ecriture,
t_ecritures_ajustement.mois_comptable,
t_ecritures_ajustement.clinique_honoraire,
t_ecritures_ajustement.journal_code,
t_ecritures_ajustement.section_analytique_code,
t_ecritures_ajustement.site_id,
CASE WHEN t_ecritures_ajustement.est_import_data = '1' THEN 'TABLEUR' ELSE t_ecritures_ajustement.compte_numero END,
t_ecritures_ajustement.texte,
t_ecritures_ajustement.report_automatique_mois_suivant,
t_ecritures_ajustement.est_balance,
t_ecritures_ajustement.est_import_data,
t_ecritures_ajustement.import_data,
t_ecritures_ajustement.montant_debit,
t_ecritures_ajustement.montant_credit,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_1 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_1 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_2 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_2 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_3 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_3 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_4 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_4 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_5 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_5 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_6 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_6 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_7 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_7 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_8 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_8 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_9 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_9 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_10 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_10 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_11 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_11 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
,
MAX(
CASE
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_12 AND t_ecritures_ajustement.montant_debit <> 0 THEN t_ecritures_ventilees.montant_credit
WHEN t_ecritures_ventilees.mois_comptable = mois_exercice_12 AND t_ecritures_ajustement.montant_credit <> 0 THEN t_ecritures_ventilees.montant_debit
ELSE 0 END
)
FROM compta.t_ecritures_ajustement
JOIN w_calendrier_mois ON t_ecritures_ajustement.mois_comptable = w_calendrier_mois.mois
LEFT JOIN compta.t_ecritures_ajustement t_ecritures_ventilees ON t_ecritures_ventilees.ecriture_maitre_id = t_ecritures_ajustement.oid
WHERE t_ecritures_ajustement.oid > 0 AND COALESCE(t_ecritures_ajustement.ecriture_maitre_id,0) = 0
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY 3 DESC,2";
$result = $database->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$nodeName = "RECORD";
$oid = $record[0] + 0;
if ($oid < 0) {
$nodeName = "ADDRECORD";
}
$date_ecriture = toHTML(trim($record[1]));
$mois_comptable = toHTML(trim($record[2]));
$clinique_honoraire = toHTML(trim($record[3]));
$journal_code = toHTML(trim($record[4]));
$section_code = toHTML(trim($record[5]));
$site_id = toHTML(trim($record[6]));
$compte_numero = toHTML(trim($record[7]));
$texte = toHTML(trim($record[8]));
$report_automatique_mois_suivant = toHTML(trim($record[9]));
$est_balance = toHTML(trim($record[10]));
$est_import_data = toHTML(trim($record[11]));
$import_data = toHTML(trim($record[12]));
$montant_debit = $record[13] + 0;
$montant_credit = $record[14] + 0;
$montant_ventile_1 = $record[15] + 0;
$montant_ventile_2 = $record[16] + 0;
$montant_ventile_3 = $record[17] + 0;
$montant_ventile_4 = $record[18] + 0;
$montant_ventile_5 = $record[19] + 0;
$montant_ventile_6 = $record[20] + 0;
$montant_ventile_7 = $record[21] + 0;
$montant_ventile_8 = $record[22] + 0;
$montant_ventile_9 = $record[23] + 0;
$montant_ventile_10 = $record[24] + 0;
$montant_ventile_11 = $record[25] + 0;
$montant_ventile_12 = $record[26] + 0;
$nbref = 0;
$httpString_tmp = $httpString_tmp . "\r\n
<$nodeName
oid=\"$oid\"
date_ecriture=\"$date_ecriture\"
mois_comptable=\"$mois_comptable\"
clinique_honoraire=\"$clinique_honoraire\"
journal_code=\"$journal_code\"
section_code=\"$section_code\"
site_id=\"$site_id\"
compte_numero=\"$compte_numero\"
texte=\"$texte\"
report_automatique_mois_suivant=\"$report_automatique_mois_suivant\"
est_balance=\"$est_balance\"
import_data=\"$import_data\"
est_import_data=\"$est_import_data\"
montant_debit=\"+$montant_debit\"
montant_credit=\"+$montant_credit\"
montant_ventile_1=\"+$montant_ventile_1\"
montant_ventile_2=\"+$montant_ventile_2\"
montant_ventile_3=\"+$montant_ventile_3\"
montant_ventile_4=\"+$montant_ventile_4\"
montant_ventile_5=\"+$montant_ventile_5\"
montant_ventile_6=\"+$montant_ventile_6\"
montant_ventile_7=\"+$montant_ventile_7\"
montant_ventile_8=\"+$montant_ventile_8\"
montant_ventile_9=\"+$montant_ventile_9\"
montant_ventile_10=\"+$montant_ventile_10\"
montant_ventile_11=\"+$montant_ventile_11\"
montant_ventile_12=\"+$montant_ventile_12\"
nbref=\"+0\"
/>";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
return $httpString;
}
function getRecords_budget() {
global $database;
$httpString = "";
$httpString = $httpString . "\r\n";
$sqlcmd = "SELECT oid, code, texte, site_id, is_actif, exercice_comptable, import_data
FROM compta.t_budget
WHERE oid > 0
ORDER BY code";
$result = $database->exec($sqlcmd);
if ($result != false) {
// lignes
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = $record[0];
$code = toHTML(trim($record[1]));
$texte = toHTML(trim($record[2]));
$site_id = toHTML(trim($record[3]));
$is_actif = toHTML(trim($record[4]));
$exercice_comptable = toHTML(trim($record[5]));
$import_data = toHTML(trim($record[6]));
$comment = "Exercice $exercice_comptable";
if ($is_actif == "1") {
$comment = "$comment (ACTIF)";
}
else {
$comment = "$comment (INACTIF)";
$is_actif = "0";
}
$httpString = $httpString . "\r\n";
}
}
}
return $httpString;
}
function getRecords_divers() {
global $database;
$httpString = "";
$sqlcmd = "
SELECT
code,
texte,
valeur,
valeur_date,
description,
CASE WHEN show_info_module THEN 1 ELSE 0 END AS show_info_module
FROM compta.t_divers
ORDER BY code";
$result = $database->exec($sqlcmd);
if ($result !== false) {
$ok = TRUE;
while ($ok === TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record !== FALSE) {
$ok = TRUE;
$code = toHTML(trim($record[0]));
$texte = toHTML(trim($record[1]));
$valeur = toHTML(trim($record[2]));
$valeur_date = toHTML(trim($record[3]));
$description = toHTML(trim($record[4]));
$show_info_module = toHTML(trim($record[5]));
$httpString .= "\r\n";
$sqlcmd = "SELECT code, code || ' ' || texte,
CASE WHEN oid NOT IN (SELECT p_oids.oid FROM compta.p_oids WHERE p_oids.code_table::text = 'journaux_h'::text) THEN '1' ELSE '0' END,
CASE WHEN oid IN (SELECT p_oids.oid FROM compta.p_oids WHERE p_oids.code_table::text = 'journaux_h'::text) THEN '1' ELSE '0' END
FROM compta.t_journaux
WHERE oid <> 0
ORDER BY code ";
$result = $database->exec($sqlcmd);
if ($result != false) {
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$code = toHTML(trim($record[0]));
$texte = toHTML(trim($record[1]));
$clinique = toHTML(trim($record[2]));
$honoraire = toHTML(trim($record[3]));
$httpString_tmp = $httpString_tmp . "\r\n";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
$httpString = $httpString . "\r\n";
return $httpString;
}
function getCombo_sections() {
global $database;
$httpString = "";
$httpString_tmp = "";
$httpString = $httpString . "\r\n";
$sqlcmd = "SELECT code, code || ' ' || texte
FROM compta.t_sections_analytiques
ORDER BY code ";
$result = $database->exec($sqlcmd);
if ($result != false) {
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$code = toHTML(trim($record[0]));
$texte = toHTML(trim($record[1]));
$httpString_tmp = $httpString_tmp . "\r\n";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
$httpString = $httpString . "\r\n";
return $httpString;
}
function getCombo_sites() {
global $database;
$httpString = "";
$httpString_tmp = "";
$httpString = $httpString . "\r\n";
$sqlcmd = "SELECT oid, texte,
CASE WHEN oid IN (SELECT p_oids.oid FROM compta.p_oids WHERE p_oids.code_table::text = 'sites_c'::text group by 1) THEN '1' ELSE '0' END,
CASE WHEN oid IN (SELECT p_oids.oid FROM compta.p_oids WHERE p_oids.code_table::text = 'sites_h'::text group by 1) THEN '1' ELSE '0' END
FROM compta.t_sites
WHERE oid <> 0
ORDER BY code ";
$result = $database->exec($sqlcmd);
if ($result != false) {
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = toHTML(trim($record[0]));
$texte = toHTML(trim($record[1]));
$clinique = toHTML(trim($record[2]));
$honoraire = toHTML(trim($record[3]));
$httpString_tmp = $httpString_tmp . "\r\n";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
$httpString = $httpString . "\r\n";
return $httpString;
}
function getCombo_finess() {
global $database;
$httpString = "";
$httpString_tmp = "";
$httpString = $httpString . "\r\n";
$httpString_tmp = $httpString_tmp . "\r\n";
$sqlcmd = "SELECT oid, code || ' ' || texte
FROM base.t_finess
WHERE oid <> 0
ORDER BY code ";
$result = $database->exec($sqlcmd);
if ($result != false) {
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = toHTML(trim($record[0]));
$texte = toHTML(trim($record[1]));
$httpString_tmp = $httpString_tmp . "\r\n";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
$httpString = $httpString . "\r\n";
return $httpString;
}
function getCombo_types_compta() {
global $database;
$httpString = "";
$httpString_tmp = "";
$httpString = $httpString . "\r\n";
$sqlcmd = "SELECT oid, code || ' ' || texte
FROM compta.t_types_compta WHERE specialite NOT IN ('BIL', 'GES')
ORDER BY code";
$result = $database->exec($sqlcmd);
if ($result != false) {
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$oid = $record[0] + 0;
$texte = toHTML(trim($record[1]));
$httpString_tmp = $httpString_tmp . "\r\n";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
$httpString = $httpString . "\r\n";
return $httpString;
}
function getCombo_mois() {
global $database;
$httpString = "";
$httpString_tmp = "";
$httpString = $httpString . "\r\n";
$sqlcmd = "SELECT p_calendrier_mois.mois,
p_calendrier_mois.texte,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (0) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_1,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (1,89) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_2,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (2,90) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_3,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (3,91) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_4,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (4,92) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_5,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (5,93) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_6,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (6,94) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_7,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (7,95) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_8,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (8,96) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_9,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (9,97) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_10,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (10,98) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_11,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (11,99) THEN p_calendrier_mois_exercice.mois ELSE 0 END) AS mois_exercice_12,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (0) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_1,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (1,89) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_2,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (2,90) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_3,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (3,91) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_4,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (4,92) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_5,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (5,93) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_6,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (6,94) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_7,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (7,95) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_8,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (8,96) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_9,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (9,97) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_10,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (10,98) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_11,
MAX(CASE WHEN p_calendrier_mois_exercice.mois - p_calendrier_mois_exercice.exercice_mois_comptable_1 IN (11,99) THEN p_calendrier_mois_exercice.texte ELSE '' END) AS mois_exercice_texte_12
FROM base.p_calendrier_mois
JOIN base.p_calendrier_mois p_calendrier_mois_exercice ON p_calendrier_mois_exercice.exercice_comptable = p_calendrier_mois.exercice_comptable
WHERE p_calendrier_mois.exercice_comptable IN (SELECT DISTINCT exercice_comptable FROM compta.p_mois_comptables)
GROUP BY 1,2
ORDER BY p_calendrier_mois.mois DESC ";
$result = $database->exec($sqlcmd);
if ($result != false) {
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$code = toHTML(trim($record[0]));
$texte = toHTML(trim($record[1]));
$mois_exercice_1 = toHTML(trim($record[2]));
$mois_exercice_2 = toHTML(trim($record[3]));
$mois_exercice_3 = toHTML(trim($record[4]));
$mois_exercice_4 = toHTML(trim($record[5]));
$mois_exercice_5 = toHTML(trim($record[6]));
$mois_exercice_6 = toHTML(trim($record[7]));
$mois_exercice_7 = toHTML(trim($record[8]));
$mois_exercice_8 = toHTML(trim($record[9]));
$mois_exercice_9 = toHTML(trim($record[10]));
$mois_exercice_10 = toHTML(trim($record[11]));
$mois_exercice_11 = toHTML(trim($record[12]));
$mois_exercice_12 = toHTML(trim($record[13]));
$mois_exercice_texte_1 = toHTML(trim($record[14]));
$mois_exercice_texte_2 = toHTML(trim($record[15]));
$mois_exercice_texte_3 = toHTML(trim($record[16]));
$mois_exercice_texte_4 = toHTML(trim($record[17]));
$mois_exercice_texte_5 = toHTML(trim($record[18]));
$mois_exercice_texte_6 = toHTML(trim($record[19]));
$mois_exercice_texte_7 = toHTML(trim($record[20]));
$mois_exercice_texte_8 = toHTML(trim($record[21]));
$mois_exercice_texte_9 = toHTML(trim($record[22]));
$mois_exercice_texte_10 = toHTML(trim($record[23]));
$mois_exercice_texte_11 = toHTML(trim($record[24]));
$mois_exercice_texte_12 = toHTML(trim($record[25]));
$httpString_tmp = $httpString_tmp . "\r\n";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
$httpString = $httpString . "\r\n";
return $httpString;
}
function getCombo_comptes() {
global $database;
$httpString = "";
$httpString_tmp = "";
$httpString = $httpString . "\r\n";
$sqlcmd = "(SELECT numero, texte, CASE WHEN clinique_honoraire = 'C' THEN '1' ELSE '0' END, CASE WHEN clinique_honoraire = 'H' THEN '1' ELSE '0' END, '0' ";
$sqlcmd = $sqlcmd . "FROM compta.t_comptes WHERE t_comptes.compte_general_id = t_comptes.oid) ";
$sqlcmd = $sqlcmd . "UNION ALL (SELECT numero, texte, CASE WHEN clinique_honoraire = 'C' THEN '1' ELSE '0' END, CASE WHEN clinique_honoraire = 'H' THEN '1' ELSE '0' END, '1' ";
$sqlcmd = $sqlcmd . "FROM compta.t_comptes WHERE t_comptes.compte_general_id <> t_comptes.oid) ";
$sqlcmd = $sqlcmd . "ORDER BY 1, 5 ";
$result = $database->exec($sqlcmd);
if ($result != false) {
$ok = TRUE;
while ($ok == TRUE) {
$ok = FALSE;
$record = $database->nextRecordInto();
if ($record != FALSE) {
$ok = TRUE;
$numero = toHTML(trim($record[0]));
$texte = toHTML(trim($record[1]));
$clinique = toHTML(trim($record[2]));
$honoraire = toHTML(trim($record[3]));
$httpString_tmp = $httpString_tmp . "\r\n";
if (strlen($httpString_tmp) > 64000) {
$httpString = "$httpString$httpString_tmp";
$httpString_tmp = "";
}
}
}
}
$httpString = "$httpString$httpString_tmp";
$httpString = $httpString . "\r\n";
return $httpString;
}
?>