/**
* Gets VAT to collect for the given year (and given quarter or month)
* The function gets the VAT in split results, as the VAT declaration asks
* to report the amounts for different VAT rates as different lines.
* This function also accounts recurrent invoices.
*
* @param DoliDB $db Database handler object
* @param int $y Year
* @param int $q Quarter
* @param string $date_start Start date
* @param string $date_end End date
* @param int $modetax 0 or 1 (option vat on debit)
* @param int $direction 'sell' (customer invoice) or 'buy' (supplier invoices)
* @param int $m Month
* @return array List of quarters with vat
*/
function vat_by_date($db, $y, $q, $date_start, $date_end, $modetax, $direction, $m = 0)
{
global $conf;
$list = array();
if ($direction == 'sell') {
$invoicetable = 'facture';
$invoicedettable = 'facturedet';
$fk_facture = 'fk_facture';
$fk_facture2 = 'fk_facture';
$fk_payment = 'fk_paiement';
$total_tva = 'total_tva';
$total_localtax1 = 'total_localtax1';
$total_localtax2 = 'total_localtax2';
$paymenttable = 'paiement';
$paymentfacturetable = 'paiement_facture';
$invoicefieldref = 'facnumber';
}
if ($direction == 'buy') {
$invoicetable = 'facture_fourn';
$invoicedettable = 'facture_fourn_det';
$fk_facture = 'fk_facture_fourn';
$fk_facture2 = 'fk_facturefourn';
$fk_payment = 'fk_paiementfourn';
$total_tva = 'tva';
$total_localtax1 = 'total_localtax1';
$total_localtax2 = 'total_localtax2';
$paymenttable = 'paiementfourn';
$paymentfacturetable = 'paiementfourn_facturefourn';
$invoicefieldref = 'ref';
}
// CAS DES BIENS
// Define sql request
$sql = '';
if ($modetax == 1) {
// Count on delivery date (use invoice date as delivery is unknown)
$sql = "SELECT d.rowid, d.product_type as dtype, d." . $fk_facture . " as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d." . $total_tva . " as total_vat, d.description as descr,";
$sql .= " d." . $total_localtax1 . " as total_localtax1, d." . $total_localtax2 . " as total_localtax2, ";
$sql .= " d.date_start as date_start, d.date_end as date_end,";
$sql .= " f." . $invoicefieldref . " as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
$sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql .= " 0 as payment_id, 0 as payment_amount";
$sql .= " FROM " . MAIN_DB_PREFIX . $invoicetable . " as f,";
$sql .= " " . MAIN_DB_PREFIX . "societe as s,";
$sql .= " " . MAIN_DB_PREFIX . $invoicedettable . " as d";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product as p on d.fk_product = p.rowid";
$sql .= " WHERE f.entity = " . $conf->entity;
$sql .= " AND f.fk_statut in (1,2)";
// Validated or paid (partially or completely)
if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
$sql .= " AND f.type IN (0,1,2,5)";
} else {
$sql .= " AND f.type IN (0,1,2,3,5)";
}
$sql .= " AND f.rowid = d." . $fk_facture;
$sql .= " AND s.rowid = f.fk_soc";
if ($y && $m) {
$sql .= " AND f.datef >= '" . $db->idate(dol_get_first_day($y, $m, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, $m, false)) . "'";
} else {
if ($y) {
$sql .= " AND 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 ($q) {
$sql .= " AND (date_format(f.datef,'%m') > " . ($q - 1) * 3 . " AND date_format(f.datef,'%m') <= " . $q * 3 . ")";
}
if ($date_start && $date_end) {
$sql .= " AND f.datef >= '" . $db->idate($date_start) . "' AND f.datef <= '" . $db->idate($date_end) . "'";
}
$sql .= " AND (d.product_type = 0";
// Limit to products
$sql .= " AND d.date_start is null AND d.date_end IS NULL)";
// enhance detection of service
$sql .= " ORDER BY d.rowid, d." . $fk_facture;
} else {
// Count on delivery date (use invoice date as delivery is unknown)
$sql = "SELECT d.rowid, d.product_type as dtype, d." . $fk_facture . " as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d." . $total_tva . " as total_vat, d.description as descr,";
$sql .= " d." . $total_localtax1 . " as total_localtax1, d." . $total_localtax2 . " as total_localtax2, ";
$sql .= " d.date_start as date_start, d.date_end as date_end,";
$sql .= " f." . $invoicefieldref . " as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef as date_f, s.nom as company_name, s.rowid as company_id,";
$sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql .= " 0 as payment_id, 0 as payment_amount";
$sql .= " FROM " . MAIN_DB_PREFIX . $invoicetable . " as f,";
$sql .= " " . MAIN_DB_PREFIX . "societe as s,";
$sql .= " " . MAIN_DB_PREFIX . $invoicedettable . " as d";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product as p on d.fk_product = p.rowid";
$sql .= " WHERE f.entity = " . $conf->entity;
$sql .= " AND f.fk_statut in (1,2)";
// Validated or paid (partially or completely)
if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
$sql .= " AND f.type IN (0,1,2,5)";
} else {
$sql .= " AND f.type IN (0,1,2,3,5)";
}
$sql .= " AND f.rowid = d." . $fk_facture;
$sql .= " AND s.rowid = f.fk_soc";
if ($y && $m) {
$sql .= " AND f.datef >= '" . $db->idate(dol_get_first_day($y, $m, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, $m, false)) . "'";
} else {
if ($y) {
$sql .= " AND 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 ($q) {
$sql .= " AND (date_format(f.datef,'%m') > " . ($q - 1) * 3 . " AND date_format(f.datef,'%m') <= " . $q * 3 . ")";
}
if ($date_start && $date_end) {
$sql .= " AND f.datef >= '" . $db->idate($date_start) . "' AND f.datef <= '" . $db->idate($date_end) . "'";
}
$sql .= " AND (d.product_type = 0";
// Limit to products
$sql .= " AND d.date_start is null AND d.date_end IS NULL)";
// enhance detection of service
$sql .= " ORDER BY d.rowid, d." . $fk_facture;
//print $sql;
}
//print $sql.'<br>';
if (!$sql) {
return -1;
}
if ($sql == 'TODO') {
return -2;
}
if ($sql != 'TODO') {
dol_syslog("Tax.lib.php::vat_by_date", LOG_DEBUG);
$resql = $db->query($sql);
if ($resql) {
$rate = -1;
$oldrowid = '';
while ($assoc = $db->fetch_array($resql)) {
if (!isset($list[$assoc['rate']]['totalht'])) {
$list[$assoc['rate']]['totalht'] = 0;
}
if (!isset($list[$assoc['rate']]['vat'])) {
$list[$assoc['rate']]['vat'] = 0;
}
if (!isset($list[$assoc['rate']]['localtax1'])) {
$list[$assoc['rate']]['localtax1'] = 0;
}
if (!isset($list[$assoc['rate']]['localtax2'])) {
$list[$assoc['rate']]['localtax2'] = 0;
}
if ($assoc['rowid'] != $oldrowid) {
$oldrowid = $assoc['rowid'];
$list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
$list[$assoc['rate']]['vat'] += $assoc['total_vat'];
$list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
$list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
}
$list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
$list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
$list[$assoc['rate']]['datef'][] = $assoc['datef'];
$list[$assoc['rate']]['company_name'][] = $assoc['company_name'];
$list[$assoc['rate']]['company_id'][] = $assoc['company_id'];
$list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
$list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
$list[$assoc['rate']]['facid'][] = $assoc['facid'];
$list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
$list[$assoc['rate']]['type'][] = $assoc['type'];
$list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
$list[$assoc['rate']]['descr'][] = $assoc['descr'];
$list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
$list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
$list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
$list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
$list[$assoc['rate']]['pid'][] = $assoc['pid'];
$list[$assoc['rate']]['pref'][] = $assoc['pref'];
$list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
$list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
$list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
$rate = $assoc['rate'];
}
} else {
dol_print_error($db);
return -3;
}
}
// CAS DES SERVICES
// Define sql request
$sql = '';
if ($modetax == 1) {
// Count on invoice date
$sql = "SELECT d.rowid, d.product_type as dtype, d." . $fk_facture . " as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d." . $total_tva . " as total_vat, d.description as descr,";
$sql .= " d." . $total_localtax1 . " as total_localtax1, d." . $total_localtax2 . " as total_localtax2, ";
$sql .= " d.date_start as date_start, d.date_end as date_end,";
$sql .= " f." . $invoicefieldref . " as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
$sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql .= " 0 as payment_id, 0 as payment_amount";
$sql .= " FROM " . MAIN_DB_PREFIX . $invoicetable . " as f,";
$sql .= " " . MAIN_DB_PREFIX . "societe as s,";
$sql .= " " . MAIN_DB_PREFIX . $invoicedettable . " as d";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product as p on d.fk_product = p.rowid";
$sql .= " WHERE f.entity = " . $conf->entity;
$sql .= " AND f.fk_statut in (1,2)";
// Validated or paid (partially or completely)
if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
$sql .= " AND f.type IN (0,1,2,5)";
} else {
$sql .= " AND f.type IN (0,1,2,3,5)";
}
$sql .= " AND f.rowid = d." . $fk_facture;
$sql .= " AND s.rowid = f.fk_soc";
if ($y && $m) {
$sql .= " AND f.datef >= '" . $db->idate(dol_get_first_day($y, $m, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, $m, false)) . "'";
} else {
if ($y) {
$sql .= " AND 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 ($q) {
$sql .= " AND (date_format(f.datef,'%m') > " . ($q - 1) * 3 . " AND date_format(f.datef,'%m') <= " . $q * 3 . ")";
}
if ($date_start && $date_end) {
$sql .= " AND f.datef >= '" . $db->idate($date_start) . "' AND f.datef <= '" . $db->idate($date_end) . "'";
}
$sql .= " AND (d.product_type = 1";
// Limit to services
$sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
// enhance detection of service
$sql .= " ORDER BY d.rowid, d." . $fk_facture;
} else {
// Count on payments date
$sql = "SELECT d.rowid, d.product_type as dtype, d." . $fk_facture . " as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d." . $total_tva . " as total_vat, d.description as descr,";
$sql .= " d." . $total_localtax1 . " as total_localtax1, d." . $total_localtax2 . " as total_localtax2, ";
$sql .= " d.date_start as date_start, d.date_end as date_end,";
$sql .= " f." . $invoicefieldref . " as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
$sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql .= " pf." . $fk_payment . " as payment_id, pf.amount as payment_amount";
$sql .= " FROM " . MAIN_DB_PREFIX . $invoicetable . " as f,";
$sql .= " " . MAIN_DB_PREFIX . $paymentfacturetable . " as pf,";
$sql .= " " . MAIN_DB_PREFIX . $paymenttable . " as pa,";
$sql .= " " . MAIN_DB_PREFIX . "societe as s,";
$sql .= " " . MAIN_DB_PREFIX . $invoicedettable . " as d";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product as p on d.fk_product = p.rowid";
$sql .= " WHERE f.entity = " . $conf->entity;
$sql .= " AND f.fk_statut in (1,2)";
// Paid (partially or completely)
if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
$sql .= " AND f.type IN (0,1,2,5)";
} else {
$sql .= " AND f.type IN (0,1,2,3,5)";
}
$sql .= " AND f.rowid = d." . $fk_facture;
$sql .= " AND s.rowid = f.fk_soc";
$sql .= " AND pf." . $fk_facture2 . " = f.rowid";
$sql .= " AND pa.rowid = pf." . $fk_payment;
if ($y && $m) {
$sql .= " AND pa.datep >= '" . $db->idate(dol_get_first_day($y, $m, false)) . "'";
$sql .= " AND pa.datep <= '" . $db->idate(dol_get_last_day($y, $m, false)) . "'";
} else {
if ($y) {
$sql .= " AND pa.datep >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND pa.datep <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
}
}
if ($q) {
$sql .= " AND (date_format(pa.datep,'%m') > " . ($q - 1) * 3 . " AND date_format(pa.datep,'%m') <= " . $q * 3 . ")";
}
if ($date_start && $date_end) {
$sql .= " AND pa.datep >= '" . $db->idate($date_start) . "' AND pa.datep <= '" . $db->idate($date_end) . "'";
}
$sql .= " AND (d.product_type = 1";
// Limit to services
$sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
// enhance detection of service
$sql .= " ORDER BY d.rowid, d." . $fk_facture . ", pf.rowid";
}
if (!$sql) {
dol_syslog("Tax.lib.php::vat_by_date no accountancy module enabled" . $sql, LOG_ERR);
return -1;
// -1 = Not accountancy module enabled
}
if ($sql == 'TODO') {
return -2;
}
// -2 = Feature not yet available
if ($sql != 'TODO') {
dol_syslog("Tax.lib.php::vat_by_date", LOG_DEBUG);
$resql = $db->query($sql);
if ($resql) {
$rate = -1;
$oldrowid = '';
while ($assoc = $db->fetch_array($resql)) {
if (!isset($list[$assoc['rate']]['totalht'])) {
$list[$assoc['rate']]['totalht'] = 0;
}
if (!isset($list[$assoc['rate']]['vat'])) {
$list[$assoc['rate']]['vat'] = 0;
}
if (!isset($list[$assoc['rate']]['localtax1'])) {
$list[$assoc['rate']]['localtax1'] = 0;
}
if (!isset($list[$assoc['rate']]['localtax2'])) {
$list[$assoc['rate']]['localtax2'] = 0;
}
if ($assoc['rowid'] != $oldrowid) {
$oldrowid = $assoc['rowid'];
$list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
$list[$assoc['rate']]['vat'] += $assoc['total_vat'];
$list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
$list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
}
$list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
$list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
$list[$assoc['rate']]['datef'][] = $assoc['datef'];
$list[$assoc['rate']]['company_name'][] = $assoc['company_name'];
$list[$assoc['rate']]['company_id'][] = $assoc['company_id'];
$list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
$list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
$list[$assoc['rate']]['facid'][] = $assoc['facid'];
$list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
$list[$assoc['rate']]['type'][] = $assoc['type'];
$list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
$list[$assoc['rate']]['descr'][] = $assoc['descr'];
$list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
$list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
$list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
$list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
$list[$assoc['rate']]['pid'][] = $assoc['pid'];
$list[$assoc['rate']]['pref'][] = $assoc['pref'];
$list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
$list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
$list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
$rate = $assoc['rate'];
}
} else {
dol_print_error($db);
return -3;
}
}
return $list;
}
/**
* Gets VAT to pay for the given month of the given year
* The function gets the VAT in split results, as the VAT declaration asks
* to report the amounts for different VAT rates as different lines
*
* @param DoliDB $db Database handler object
* @param int $y Year
* @param int $q Year quarter (1-4)
* @return array
*/
function tva_paye($db, $y, $q)
{
global $conf;
if ($conf->global->ACCOUNTING_MODE == "CREANCES-DETTES") {
// Si on paye la tva sur les factures dues (non brouillon)
$sql = "SELECT d.fk_facture_fourn as facid, f.ref_supplier as facnum, d.tva_tx as rate, d.total_ht as totalht, d.tva as amount";
$sql .= " FROM " . MAIN_DB_PREFIX . "facture_fourn as f";
$sql .= ", " . MAIN_DB_PREFIX . "facture_fourn_det as d";
$sql .= ", " . MAIN_DB_PREFIX . "societe as s";
$sql .= " WHERE f.fk_soc = s.rowid";
$sql .= " AND f.entity = " . $conf->entity;
$sql .= " AND f.fk_statut = 1 ";
$sql .= " AND f.rowid = d.fk_facture_fourn ";
$sql .= " AND date_format(f.datef,'%Y') = '" . $y . "'";
$sql .= " AND (round(date_format(f.datef,'%m')) > " . ($q - 1) * 3;
$sql .= " AND round(date_format(f.datef,'%m')) <= " . $q * 3 . ")";
$sql .= " ORDER BY rate, facid ";
} else {
// Si on paye la tva sur les payments
}
$resql = $db->query($sql);
if ($resql) {
$list = array();
$rate = -1;
while ($assoc = $db->fetch_array($resql)) {
if ($assoc['rate'] != $rate) {
//new rate
$list[$assoc['rate']]['totalht'] = $assoc['totalht'];
$list[$assoc['rate']]['vat'] = $assoc['amount'];
$list[$assoc['rate']]['facid'][] = $assoc['facid'];
$list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
} else {
$list[$assoc['rate']]['totalht'] += $assoc['totalht'];
$list[$assoc['rate']]['vat'] += $assoc['amount'];
if (!in_array($assoc['facid'], $list[$assoc['rate']]['facid'])) {
$list[$assoc['rate']]['facid'][] = $assoc['facid'];
$list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
}
}
$rate = $assoc['rate'];
}
return $list;
} else {
dol_print_error($db);
}
}
