$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "facture as f ON f.rowid = fd.fk_facture";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accountingaccount as aa ON aa.rowid = fd.fk_code_ventilation";
$sql .= " WHERE f.datef >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
if (!empty($conf->multicompany->enabled)) {
$sql .= " AND f.entity = '" . $conf->entity . "'";
}
$sql .= " GROUP BY fd.fk_code_ventilation";
dol_syslog("htdocs/accountancy/customer/index.php sql=" . $sql, LOG_DEBUG);
$resql = $db->query($sql);
if ($resql) {
$i = 0;
$num = $db->num_rows($resql);
while ($i < $num) {
$row = $db->fetch_row($resql);
print '<tr><td>' . length_accountg($row[0]) . '</td>';
print '<td align="left">' . $row[1] . '</td>';
print '<td align="right">' . price($row[2]) . '</td>';
print '<td align="right">' . price($row[3]) . '</td>';
print '<td align="right">' . price($row[4]) . '</td>';
print '<td align="right">' . price($row[5]) . '</td>';
print '<td align="right">' . price($row[6]) . '</td>';
print '<td align="right">' . price($row[7]) . '</td>';
print '<td align="right">' . price($row[8]) . '</td>';
print '<td align="right">' . price($row[9]) . '</td>';
print '<td align="right">' . price($row[10]) . '</td>';
print '<td align="right">' . price($row[11]) . '</td>';
print '<td align="right">' . price($row[12]) . '</td>';
print '<td align="right">' . price($row[13]) . '</td>';
print '<td align="right"><b>' . price($row[14]) . '</b></td>';
print '</tr>';
$sql .= " WHERE ff.datef >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND ff.datef <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
$sql .= " AND ff.fk_statut > 0 ";
if (!empty($conf->multicompany->enabled)) {
$sql .= " AND ff.entity IN (" . getEntity("facture_fourn", 1) . ")";
}
$sql .= " GROUP BY ffd.fk_code_ventilation";
dol_syslog('/accountancy/supplier/index.php:: sql=' . $sql);
$resql = $db->query($sql);
if ($resql) {
$i = 0;
$num = $db->num_rows($resql);
while ($i < $num) {
$row = $db->fetch_row($resql);
$var = !$var;
print '<tr ' . $bc[$var] . '><td>' . length_accountg($row[0]) . '</td>';
print '<td align="left">' . $row[1] . '</td>';
print '<td align="right">' . price($row[2]) . '</td>';
print '<td align="right">' . price($row[3]) . '</td>';
print '<td align="right">' . price($row[4]) . '</td>';
print '<td align="right">' . price($row[5]) . '</td>';
print '<td align="right">' . price($row[6]) . '</td>';
print '<td align="right">' . price($row[7]) . '</td>';
print '<td align="right">' . price($row[8]) . '</td>';
print '<td align="right">' . price($row[9]) . '</td>';
print '<td align="right">' . price($row[10]) . '</td>';
print '<td align="right">' . price($row[11]) . '</td>';
print '<td align="right">' . price($row[12]) . '</td>';
print '<td align="right">' . price($row[13]) . '</td>';
print '<td align="right"><b>' . price($row[14]) . '</b></td>';
print '</tr>';
$cpt = 0;
foreach ($mesCasesCochees as $maLigneCochee) {
$maLigneCourante = explode("_", $maLigneCochee);
$monId = $maLigneCourante[0];
$monNumLigne = $maLigneCourante[1];
$monCompte = $mesCodesVentilChoisis[$monNumLigne];
$sql = " UPDATE " . MAIN_DB_PREFIX . "facturedet";
$sql .= " SET fk_code_ventilation = " . $monCompte;
$sql .= " WHERE rowid = " . $monId;
$accountventilated = new AccountingAccount($db);
$accountventilated->fetch($monCompte, '');
dol_syslog("/accountancy/customer/list.php sql=" . $sql, LOG_DEBUG);
if ($db->query($sql)) {
print '<div><font color="green">' . $langs->trans("Lineofinvoice") . ' ' . $monId . ' - ' . $langs->trans("VentilatedinAccount") . ' : ' . length_accountg($accountventilated->account_number) . '</font></div>';
} else {
print '<div><font color="red">' . $langs->trans("ErrorDB") . ' : ' . $langs->trans("Lineofinvoice") . ' ' . $monId . ' - ' . $langs->trans("NotVentilatedinAccount") . ' : ' . length_accountg($accountventilated->account_number) . '<br/> <pre>' . $sql . '</pre></font></div>';
}
$cpt++;
}
} else {
print '<div><font color="red">' . $langs->trans("AnyLineVentilate") . '</font></div>';
}
print '<div><font color="red">' . $langs->trans("EndProcessing") . '</font></div>';
}
/*
* Customer Invoice lines
*/
if (!empty($conf->global->ACCOUNTING_LIMIT_LIST_VENTILATION)) {
$limit = $conf->global->ACCOUNTING_LIMIT_LIST_VENTILATION;
} else {
if ($conf->global->ACCOUNTING_LIMIT_LIST_VENTILATION <= 0) {
// setEventMessages(null, $accounting->errors, 'errors');
$msg .= '<div><font color="red">' . $langs->trans("ErrorDB") . ' : ' . $langs->trans("Product") . ' ' . $productid . ' ' . $langs->trans("NotVentilatedinAccount") . ' : id=' . $accounting_account_id . '<br/> <pre>' . $sql . '</pre></font></div>';
} else {
$sql = " UPDATE " . MAIN_DB_PREFIX . "product";
if ($accounting_product_mode == 'ACCOUNTANCY_BUY') {
$sql .= " SET accountancy_code_buy = " . $accounting->account_number;
}
if ($accounting_product_mode == 'ACCOUNTANCY_SELL') {
$sql .= " SET accountancy_code_sell = " . $accounting->account_number;
}
$sql .= " WHERE rowid = " . $productid;
dol_syslog("/accountancy/admin/productaccount.php sql=" . $sql, LOG_DEBUG);
if ($db->query($sql)) {
$msg .= '<div><font color="green">' . $langs->trans("Product") . ' ' . $productid . ' ' . $langs->trans("VentilatedinAccount") . ' : ' . length_accountg($accounting->account_number) . '</font></div>';
} else {
$msg .= '<div><font color="red">' . $langs->trans("ErrorDB") . ' : ' . $langs->trans("Product") . ' ' . $productid . ' ' . $langs->trans("NotVentilatedinAccount") . ' : ' . length_accountg($accounting->account_number) . '<br/> <pre>' . $sql . '</pre></font></div>';
}
}
$cpt++;
}
} else {
$msg .= '<div><font color="red">' . $langs->trans("AnyLineVentilate") . '</font></div>';
}
$msg .= '<div><font color="red">' . $langs->trans("EndProcessing") . '</font></div>';
}
}
$form = new FormVentilation($db);
// Defaut AccountingAccount RowId Product / Service
// at this time ACCOUNTING_SERVICE_SOLD_ACCOUNT & ACCOUNTING_PRODUCT_SOLD_ACCOUNT are account number not accountingacount rowid
// so we need to get those default value rowid first
$accounting = new AccountingAccount($db);
if ($k != 'type') {
print "<tr " . $bc[$var] . ">";
print "<td>" . $date . "</td>";
print "<td>" . $val["soclib"] . "</td>";
print "<td>" . length_accounta($k) . "</td>";
print "<td>" . $langs->trans('ThirdParty') . " (" . $val['soclib'] . ")</td>";
print "<td>" . $val["type_payment"] . "</td>";
print "<td align='right'>" . ($mt < 0 ? price(-$mt) : '') . "</td>";
print "<td align='right'>" . ($mt >= 0 ? price($mt) : '') . "</td>";
print "</tr>";
}
}
} else {
foreach ($tabbq[$key] as $k => $mt) {
print "<tr " . $bc[$var] . ">";
print "<td>" . $date . "</td>";
print "<td>" . $reflabel . "</td>";
print "<td>" . length_accountg($conf->global->ACCOUNTING_ACCOUNT_SUSPENSE) . "</td>";
print "<td>" . $langs->trans('ThirdParty') . "</td>";
print "<td> </td>";
print "<td align='right'>" . ($mt < 0 ? price(-$mt) : '') . "</td>";
print "<td align='right'>" . ($mt >= 0 ? price($mt) : '') . "</td>";
print "</tr>";
}
}
$var = !$var;
}
print "</table>";
llxFooter();
}
$db->close();
print $formventilation->select_account($line->numero_compte, 'account_number', 0, array(), 1, 1, '');
print '</td>';
print '<td>';
print $formventilation->select_auxaccount($line->code_tiers, 'code_tiers', 1);
print '</td>';
print '<td><input type="text" size="15" name="label_compte" value="' . $line->label_compte . '"/></td>';
print '<td align="right"><input type="text" size="6" name="debit" value="' . price($line->debit) . '"/></td>';
print '<td align="right"><input type="text" size="6" name="credit" value="' . price($line->credit) . '"/></td>';
print '<td align="right">' . price($line->montant) . '</td>';
print '<td align="center">' . $line->sens . '</td>';
print '<td>';
print '<input type="hidden" name="id" value="' . $line->id . '">' . "\n";
print '<input type="submit" class="button" name="update" value="' . $langs->trans("Update") . '">';
print '</td>';
} else {
print '<td>' . length_accountg($line->numero_compte) . '</td>';
print '<td>' . length_accounta($line->code_tiers) . '</td>';
print '<td>' . $line->label_compte . '</td>';
print '<td align="right">' . price($line->debit) . '</td>';
print '<td align="right">' . price($line->credit) . '</td>';
print '<td align="right">' . price($line->montant) . '</td>';
print '<td align="center">' . $line->sens . '</td>';
print '<td align="center">';
print '<a href="./card.php?action=update&id=' . $line->id . '&piece_num=' . $line->piece_num . '">';
print img_edit();
print '</a> ';
print '<a href="./card.php?action=delete&id=' . $line->id . '&piece_num=' . $line->piece_num . '">';
print img_delete();
print '</a>';
print '</td>';
}
$accountingaccount->fetch(null, $k);
if ($mt) {
print "<tr " . $bc[$var] . ">";
print "<td>" . $date . "</td>";
print "<td>" . $invoicestatic->getNomUrl(1) . "</td>";
print "<td>" . length_accountg($k) . "</td>";
print "<td>" . $accountingaccount->label . "</td>";
print "<td align='right'>" . ($mt < 0 ? price(-$mt) : '') . "</td>";
print "<td align='right'>" . ($mt >= 0 ? price($mt) : '') . "</td>";
print "</tr>";
}
}
// VAT
foreach ($tabtva[$key] as $k => $mt) {
if ($mt) {
print "<tr " . $bc[$var] . ">";
print "<td>" . $date . "</td>";
print "<td>" . $invoicestatic->getNomUrl(1) . "</td>";
print "<td>" . length_accountg($k) . "</td>";
print "<td>" . $langs->trans("VAT") . "</td>";
print "<td align='right'>" . ($mt < 0 ? price(-$mt) : '') . "</td>";
print "<td align='right'>" . ($mt >= 0 ? price($mt) : '') . "</td>";
print "</tr>";
}
}
$var = !$var;
}
print "</table>";
}
llxFooter();
$db->close();
$sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=9,bk.montant,0)),2) AS 'Septembre',";
$sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=10,bk.montant,0)),2) AS 'Octobre',";
$sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=11,bk.montant,0)),2) AS 'Novembre',";
$sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=12,bk.montant,0)),2) AS 'Decembre',";
$sql .= " ROUND(SUM(bk.montant),2) as 'Total'";
$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk";
$sql .= " WHERE bk.doc_date >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND bk.doc_date <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
$sql .= " GROUP BY bk.numero_compte";
$resql = $db->query($sql);
if ($resql) {
$i = 0;
$num = $db->num_rows($resql);
while ($i < $num) {
$row = $db->fetch_row($resql);
print '<tr><td width="14%">' . length_accountg($row[0]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[1]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[2]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[3]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[4]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[5]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[6]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[7]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[8]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[9]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[10]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[11]) . '</td>';
print '<td align="right" width="6.5%">' . price($row[12]) . '</td>';
print '<td align="right" width="8%"><b>' . price($row[13]) . '</b></td>';
print '</tr>';
$i++;
print "<td>" . $date . "</td>";
print "<td>" . $invoicestatic->getNomUrl(1) . "</td>";
print "<td>" . length_accountg($k) . "</td>";
print "<td>" . $accountingaccount->label . "</td>";
print '<td align="right">' . ($mt >= 0 ? price($mt) : '') . "</td>";
print '<td align="right">' . ($mt < 0 ? price(-$mt) : '') . "</td>";
print "</tr>";
}
}
// VAT
foreach ($tabtva[$key] as $k => $mt) {
if ($mt) {
print "<tr " . $bc[$var] . " >";
print "<td>" . $date . "</td>";
print "<td>" . $invoicestatic->getNomUrl(1) . "</td>";
print "<td>" . length_accountg($k) . "</td><td>" . $langs->trans("VAT") . "</td>";
print '<td align="right">' . ($mt >= 0 ? price($mt) : '') . "</td>";
print '<td align="right">' . ($mt < 0 ? price(-$mt) : '') . "</td>";
print "</tr>";
}
}
print "<tr " . $bc[$var] . ">";
// Third party
foreach ($tabttc[$key] as $k => $mt) {
print "<td>" . $date . "</td>";
print "<td>" . $invoicestatic->getNomUrl(1) . "</td>";
$companystatic->id = $tabcompany[$key]['id'];
$companystatic->name = $tabcompany[$key]['name'];
print "<td>" . length_accounta($k);
print "</td><td>" . $langs->trans("ThirdParty");
print ' (' . $companystatic->getNomUrl(0, 'supplier', 16) . ')';
print '<table width="100%" class="nobordernopadding"><tr><td class="nowrap">';
print $langs->trans("ProductAccountancySellCode");
print '</td>';
print '</tr></table>';
print '</td><td colspan="2">';
print length_accountg($object->accountancy_code_sell);
print '</td></tr>';
// Accountancy buy code
print '<tr><td class="nowrap">';
print '<table width="100%" class="nobordernopadding"><tr><td class="nowrap">';
print $langs->trans("ProductAccountancyBuyCode");
print '</td>';
print '</tr></table>';
print '</td><td colspan="2">';
print length_accountg($object->accountancy_code_buy);
print '</td></tr>';
// Status (to sell)
/*
print '<tr><td>'.$langs->trans("Status").' ('.$langs->trans("Sell").')</td><td colspan="2">';
if (! empty($conf->use_javascript_ajax) && $user->rights->produit->creer && ! empty($conf->global->MAIN_DIRECT_STATUS_UPDATE)) {
print ajax_object_onoff($object, 'status', 'tosell', 'ProductStatusOnSell', 'ProductStatusNotOnSell');
} else {
print $object->getLibStatut(2,0);
}
print '</td></tr>';
// Status (to buy)
print '<tr><td>'.$langs->trans("Status").' ('.$langs->trans("Buy").')</td><td colspan="2">';
if (! empty($conf->use_javascript_ajax) && $user->rights->produit->creer && ! empty($conf->global->MAIN_DIRECT_STATUS_UPDATE)) {
if ($fieldlist[$field] == 'localtax2') {
$valuetoshow = price($valuetoshow, 0, $langs, 0, 0);
if ($obj->localtax2 == 0) {
$valuetoshow = '';
}
$align = "right";
} else {
if (in_array($fieldlist[$field], array('taux', 'localtax1', 'localtax2'))) {
$valuetoshow = price($valuetoshow, 0, $langs, 0, 0);
$align = "right";
} else {
if (in_array($fieldlist[$field], array('recuperableonly'))) {
$align = "center";
} else {
if ($fieldlist[$field] == 'accountancy_code' || $fieldlist[$field] == 'accountancy_code_sell' || $fieldlist[$field] == 'accountancy_code_buy') {
$valuetoshow = length_accountg($valuetoshow);
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
print '<tr><td>' . $langs->trans("BankAccountDomiciliation") . '</td><td colspan="3">';
print nl2br($account->domiciliation);
print "</td></tr>\n";
print '<tr><td>' . $langs->trans("BankAccountOwner") . '</td><td colspan="3">';
print $account->proprio;
print "</td></tr>\n";
print '<tr><td>' . $langs->trans("BankAccountOwnerAddress") . '</td><td colspan="3">';
print nl2br($account->owner_address);
print "</td></tr>\n";
print '</table>';
print '<br>';
}
print '<table class="border" width="100%">';
// Accountancy code
print '<tr><td width="25%">' . $langs->trans("AccountancyCode") . '</td>';
print '<td colspan="3">' . length_accountg($account->account_number) . '</td></tr>';
// Accountancy journal
if (!empty($conf->accounting->enabled)) {
print '<tr><td>' . $langs->trans("AccountancyJournal") . '</td>';
print '<td colspan="3">' . $account->accountancy_journal . '</td></tr>';
}
print '</table>';
print '</div>';
/*
* Barre d'actions
*/
print '<div class="tabsAction">';
if ($user->rights->banque->configurer) {
print '<a class="butAction" href="' . $_SERVER["PHP_SELF"] . '?action=edit&id=' . $account->id . '">' . $langs->trans("Modify") . '</a>';
}
$canbedeleted = $account->can_be_deleted();
/**
* Return list of accounts with label by chart of accounts
*
* @param string $selectid Preselected chart of accounts
* @param string $htmlname Name of field in html form
* @param int $showempty Add an empty field
* @param array $event Event options
* @param int $select_in $selectid value is a aa.rowid (0 default) or aa.account_number (1)
* @param int $select_out set value returned by select 0=rowid (default), 1=account_number
* @param int $aabase set accounting_account base class to display empty=all or from 1 to 8 will display only account beginning by this number
*
* @return string String with HTML select
*/
function select_account($selectid, $htmlname = 'account', $showempty = 0, $event = array(), $select_in = 0, $select_out = 0, $aabase = '')
{
global $conf;
require_once DOL_DOCUMENT_ROOT . '/core/lib/accounting.lib.php';
$out = '';
$sql = "SELECT DISTINCT aa.account_number, aa.label, aa.rowid, aa.fk_pcg_version";
$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as aa";
$sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
$sql .= " AND asy.rowid = " . $conf->global->CHARTOFACCOUNTS;
$sql .= " AND aa.active = 1";
$sql .= " ORDER BY aa.account_number";
dol_syslog(get_class($this) . "::select_account sql=" . $sql, LOG_DEBUG);
$resql = $this->db->query($sql);
if ($resql) {
$out .= ajax_combobox($htmlname, $event);
$out .= '<select id="' . $htmlname . '" class="flat" name="' . $htmlname . '">';
if ($showempty) {
$out .= '<option value="-1"></option>';
}
$num = $this->db->num_rows($resql);
$trunclength = defined('ACCOUNTING_LENGTH_DESCRIPTION_ACCOUNT') ? $conf->global->ACCOUNTING_LENGTH_DESCRIPTION_ACCOUNT : '50';
$i = 0;
if ($num) {
while ($i < $num) {
$obj = $this->db->fetch_object($resql);
$label = length_accountg($obj->account_number) . ' - ' . $obj->label;
$label = dol_trunc($label, $trunclength);
if ($select_in == 0) {
$select_value_in = $obj->rowid;
}
if ($select_in == 1) {
$select_value_in = $obj->account_number;
}
if ($select_out == 0) {
$select_value_out = $obj->rowid;
}
if ($select_out == 1) {
$select_value_out = $obj->account_number;
}
// Remember guy's we store in database llx_facturedet the rowid of accounting_account and not the account_number
// Because same account_number can be share between different accounting_system and do have the same meaning
if ($selectid != '' && $selectid == $select_value_in) {
// $out .= '<option value="' . $obj->account_number . '" selected>' . $label . '</option>';
$out .= '<option value="' . $select_value_out . '" selected>' . $label . '</option>';
} else {
// $out .= '<option value="' . $obj->account_number . '">' . $label . '</option>';
$out .= '<option value="' . $select_value_out . '">' . $label . '</option>';
}
$i++;
}
}
$out .= '</select>';
} else {
$this->error = "Error " . $this->db->lasterror();
dol_syslog(get_class($this) . "::select_account " . $this->error, LOG_ERR);
return -1;
}
$this->db->free($resql);
return $out;
}