<?php
	require('config.php');
	dol_include_once('/projet/class/project.class.php');
	dol_include_once('/projet/class/task.class.php');
	dol_include_once('/user/class/user.class.php');
	dol_include_once('/core/lib/usergroups.lib.php');
	dol_include_once('/compta/facture/class/facture.class.php');
	
	
	
	llxHeader('',$langs->trans('TopVieillesImpayees'));

	print load_fiche_titre($langs->trans("Top5VieillesImpayees"));
	dol_fiche_head(array(), 0, '', -1);
	_print_rapport_top();
    dol_fiche_end(-1);

    print '<br />';

//    print load_fiche_titre($langs->trans("RecouvrementPaymentLateByAgent"));
    dol_fiche_head(array(), 0, '', -1);
    _print_rapport_by_agent();
    dol_fiche_end(-1);

	llxFooter();
	
	function _get_factures($date){
		global $db;
		
		$sql = 'SELECT f.rowid AS rowid FROM '.MAIN_DB_PREFIX.'facture f ';
		$sql .= 'WHERE f.fk_statut=1 AND "'.$date.'">f.date_lim_reglement ';
		$sql .= 'ORDER BY f.date_lim_reglement ASC ';
		$sql .= 'LIMIT 0, 5';
		
		$resql = $db->query($sql);
		
		$TData = array();
		if ($resql){
			while ($line = $db->fetch_object($resql)){
				$TData[] = array(
					"rowid"          => $line->rowid
				);
			}
		}
		return $TData;
	}
	
	
	function _print_rapport_top(){
		global $db;
		
		$date = date('Y-m-d');
		$TData = _get_factures($date);
		
		?>
		<style type="text/css">
		table#rapport_depassement td,table#rapport_depassement th {
			white-space: nowrap;
			border-right: 1px solid #D8D8D8;
			border-bottom: 1px solid #D8D8D8;
		}
		</style>

			<table id="rapport_depassement" class="noborder" width="100%">
				<thead>
					<tr style="text-align:left;" class="liste_titre nodrag nodrop">
						<th class="liste_titre">Facture</th>
						<th class="liste_titre">Société</th>
						<th class="liste_titre">Date limite de paiement</th>
						<th class="liste_titre">Retard en jours</th>
						<th style="text-align: right" class="liste_titre">Total HT</th>
					</tr>
				</thead>
				<tbody>
					<?php
					$montant_total=0;
					foreach ($TData as $line){
						$facture = new Facture($db);
						$facture->fetch($line['rowid']);
						$montant_total += $facture->total_ht;
						$interval = strtotime(date('Y-m-d'))-$facture->date_lim_reglement;
						$retard = $interval/3600/24;
	
						$societe = new Societe($db);
						$societe->fetch($facture->socid);
						?>
						<tr>
							<td><?php echo $facture->getNomUrl(1) ?></td>
							<td><?php echo $societe->getNomUrl(1) ?></td>
							<td><?php echo (!empty($facture->date_lim_reglement)) ? date('d/m/Y', $facture->date_lim_reglement) : '' ?></td>
							<td><?php echo round($retard) ?></td>
							<td style="text-align: right"><?php echo price($facture->total_ht) ;?></td>
						</tr>
					<?php	
					}
					?>
					<tfoot>
		                <tr style="font-weight: bold;">
		                    <td>Total</td>
		                    <td></td>
		                    <td></td>
		                    <td></td>
		                    <td style="text-align: right"><?php echo price($montant_total) ?></td>
		            	</tr>
		            </tfoot>
				</tbody>
			</table>
		
		<?php 
		
		$total_impayes = somme_impayes();
		$total_retard = somme_impayes_retard();
		$CALastMonths = _get_CA_trois_mois();
		
		if (empty($CALastMonths)) $delaiPayement = 0;
		else $delaiPayement = ($total_impayes+$total_retard)/$CALastMonths*90;
		print load_fiche_titre('Impayés', '', '');
		?>
		<table>
			<tr>
				<td>
					Somme des impayés : 
				</td>
				<td style="font-weight: bold">
					<?php echo price($total_impayes) ?>
				</td>
			</tr>
			<tr>
				<td>
					Somme des impayés en retard :
				</td>
				<td style="font-weight: bold">
					<?php echo price($total_retard) ?>
				</td>
			</tr>
			<tr>
				<td>
					Délai moyen de paiement :
				</td>
				<td style="font-weight: bold">
					<?php echo round($delaiPayement, 1) ?> jours
				</td>
			</tr>
		</table>
		<?php
		
		
	}

	function somme_impayes(){
		global $db;
		
		$date = date('Y-m-d');
		$TData = array();
		
		$sql = 'SELECT f.rowid AS rowid, SUM(f.total) AS total FROM '.MAIN_DB_PREFIX.'facture f ';
		$sql .= 'WHERE f.fk_statut=1 AND "'.$date.'"<f.date_lim_reglement ';
		
		$resql = $db->query($sql);
		
		if ($resql){
			while ($line = $db->fetch_object($resql)){
						$total = $line->total;
			}
		}
		return $total;
	}
	
	function somme_impayes_retard(){
		global $db;
		
		$date = date('Y-m-d');
		$TData = array();
		
		$sql = 'SELECT f.rowid AS rowid, SUM(f.total) AS total FROM '.MAIN_DB_PREFIX.'facture f ';
		$sql .= 'WHERE f.fk_statut=1 AND "'.$date.'">f.date_lim_reglement ';
		
		$resql = $db->query($sql);
		
		if ($resql){
			while ($line = $db->fetch_object($resql)){
						$total = $line->total;
			}
		}
		return $total;
	}
	

	function _get_CA_trois_mois(){
		global $db;
		
		$TData = array();
		$date_deb = date('Y-m-d', strtotime("-3 month"));
		
		$sql = 'SELECT SUM(f.total) AS montant FROM '.MAIN_DB_PREFIX.'facture f ';
		$sql .= 'WHERE (f.fk_statut=2) AND (f.paye=1) AND (f.datef BETWEEN "'.$date_deb.'" AND "'.date('Y-m-d').'") ';
		
		$resql = $db->query($sql);
		
		if ($resql){
			while ($line = $db->fetch_object($resql)){
						$total = $line->montant;
			}
		}
		return $total;
	}

function _print_rapport_by_agent()
{
    global $db, $langs, $conf, $user;

    $TData = array();

    _getSumInvoices($TData, $db, '3', 'AND f3.date_lim_reglement >= (NOW() - INTERVAL 3 MONTH)');
    _getSumInvoices($TData, $db, '6', 'AND f6.date_lim_reglement >= (NOW() - INTERVAL 6 MONTH) AND f6.date_lim_reglement < (NOW() - INTERVAL 3 MONTH)');
    _getSumInvoices($TData, $db, '12', 'AND f12.date_lim_reglement >= (NOW() - INTERVAL 12 MONTH) AND f12.date_lim_reglement < (NOW() - INTERVAL 6 MONTH)');
    _getSumInvoices($TData, $db, '12p', 'AND f12p.date_lim_reglement < (NOW() - INTERVAL 12 MONTH)');
    _getSumInvoices($TData, $db, 'tot', '');

    $r = new Listview($db, 'recouvrement_list_by_agent');
    $formcore = new TFormCore($_SERVER['PHP_SELF'], 'form_recouvrement_list_by_agent', 'GET');

    $nbLine = GETPOST('limit');
    if (empty($nbLine)) $nbLine = !empty($user->conf->MAIN_SIZE_LISTE_LIMIT) ? $user->conf->MAIN_SIZE_LISTE_LIMIT : $conf->global->MAIN_SIZE_LISTE_LIMIT;

    $TData = array_values($TData); // Permet d'écraser les clés pour repartir de 0 (nécessaire pour la ligne de total, un test est fait sur l'indice [0])

    echo $r->renderArray($db, $TData, array(
        'view_type' => 'list' // default = [list], [raw], [chart]
        ,'allow-fields-select' => false
        ,'limit'=>array(
            'nbLine' => 0
        )
        ,'list' => array(
            'title' => $langs->trans('RecouvrementPaymentLateByAgent')
            ,'image' => 'title_generic.png'
            ,'picto_precedent' => '<'
            ,'picto_suivant' => '>'
            ,'noheader' => 0
            ,'messageNothing' => ' ' //$langs->trans('NoMyModule')
            ,'picto_search' => img_picto('', 'search.png', '', 0)
            ,'haveTotal' => 1
        )
        ,'subQuery' => array()
        ,'link' => array()
        ,'type' => array(
            'total_f3' => 'money' // [datetime], [hour], [money], [number], [integer]
            ,'total_f6' => 'money' // [datetime], [hour], [money], [number], [integer]
            ,'total_f12' => 'money' // [datetime], [hour], [money], [number], [integer]
            ,'total_f12p' => 'money' // [datetime], [hour], [money], [number], [integer]
            ,'total_ftot' => 'money' // [datetime], [hour], [money], [number], [integer]
        )
        ,'math' => array(
            'total_f3' => 'sum'
            ,'total_f6' => 'sum'
            ,'total_f12' => 'sum'
            ,'total_f12p' => 'sum'
            ,'total_ftot' => 'sum'
        )
        ,'position' => array(
            'text-align' => array(
                'total_f3' => 'right'
                ,'total_f6' => 'right'
                ,'total_f12' => 'right'
                ,'total_f12p' => 'right'
                ,'total_ftot' => 'right'
            )
        )
        ,'translate' => array()
        ,'hide' => array()
        ,'title'=>array(
            'fk_user' => $langs->trans('DossUserAttached')
            ,'total_f3' => $langs->transnoentitiesnoconv('AmountUnpaidFrom3Months')
            ,'total_f6' => $langs->transnoentitiesnoconv('AmountUnpaidFrom6Months')
            ,'total_f12' => $langs->transnoentitiesnoconv('AmountUnpaidFrom12Months')
            ,'total_f12p' => $langs->transnoentitiesnoconv('AmountUnpaidFrom12pMonths')
            ,'total_ftot' => $langs->transnoentitiesnoconv('AmountUnpaidFromTotMonths')
        )
        ,'eval'=>array(
    		'fk_user' => '_getUserNomUrl(@val@)' // Si on a un fk_user dans notre requête
        )
    ));

    $formcore->end_form();
}

function _getUserNomUrl($fk_user)
{
    global $db;

    $u = new User($db);
    $u->fetch($fk_user);
    return $u->getNomUrl(1);
}

function _getSumInvoices(&$TData, $db, $suffix_table, $sql_clause_date)
{
    $sql = 'SELECT rd.fk_user';
    $sql.= ', (SUM(f'.$suffix_table.'.total_ttc)
        - SUM( (SELECT IFNULL(SUM(p'.$suffix_table.'.amount), 0) FROM '.MAIN_DB_PREFIX.'paiement_facture p'.$suffix_table.' WHERE p'.$suffix_table.'.fk_facture = f'.$suffix_table.'.rowid) )
        - SUM( (SELECT IFNULL(SUM(sr'.$suffix_table.'.amount_ttc), 0) FROM '.MAIN_DB_PREFIX.'societe_remise_except sr'.$suffix_table.' WHERE sr'.$suffix_table.'.fk_facture = f'.$suffix_table.'.rowid) )
    ) AS total_f'.$suffix_table;

    $sql.= ' FROM '.MAIN_DB_PREFIX.'rec_dossier rd ';
    $sql.= ' INNER JOIN '.MAIN_DB_PREFIX.'rec_dossier_facture rdf ON (rdf.fk_dossier = rd.rowid) ';
    $sql.= ' INNER JOIN '.MAIN_DB_PREFIX.'facture f'.$suffix_table.' ON (f'.$suffix_table.'.rowid = rdf.fk_facture AND f'.$suffix_table.'.fk_statut = 1 '.(!empty($sql_clause_date) ? $sql_clause_date : '').' )';

    $sql.= ' GROUP BY rd.fk_user';

    $resql = $db->query($sql);
    if ($resql)
    {
        while ($obj = $db->fetch_object($resql))
        {
            if (!isset($TData[$obj->fk_user]))
            {
                $TData[$obj->fk_user] = array(
                    'fk_user' => $obj->fk_user
                    ,'total_f'.$suffix_table => 0
                );
            }

            $TData[$obj->fk_user]['total_f'.$suffix_table] += $obj->{'total_f'.$suffix_table};
        }
    }
    else dol_print_error($db);
}