[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpcompta-dev] r5487 - in phpcompta/trunk: include sql
|
From: |
phpcompta-dev |
|
Subject: |
[Phpcompta-dev] r5487 - in phpcompta/trunk: include sql |
|
Date: |
Sat, 12 Oct 2013 16:27:49 +0200 (CEST) |
Author: danydb
Date: 2013-10-12 16:27:48 +0200 (Sat, 12 Oct 2013)
New Revision: 5487
Modified:
phpcompta/trunk/include/class_acc_ledger_purchase.php
phpcompta/trunk/include/class_acc_ledger_sold.php
phpcompta/trunk/include/class_print_ledger.php
phpcompta/trunk/include/class_print_ledger_detail_item.php
phpcompta/trunk/include/export_ledger_csv.php
phpcompta/trunk/include/impress_jrn.inc.php
phpcompta/trunk/sql/upgrade.sql
Log:
task #0000890: PRINTJRN : export PDF et CSV des operations detaillees
Modified: phpcompta/trunk/include/class_acc_ledger_purchase.php
===================================================================
--- phpcompta/trunk/include/class_acc_ledger_purchase.php 2013-10-11
21:30:56 UTC (rev 5486)
+++ phpcompta/trunk/include/class_acc_ledger_purchase.php 2013-10-12
14:27:48 UTC (rev 5487)
@@ -1629,6 +1629,33 @@
}
+ /**
+ * Retrieve data from the view v_detail_purchase
+ * @global $g_user connected user
+ * @param $p_from jrn.jr_tech_per from
+ * @param type $p_end jrn.jr_tech_per to
+ * @return type
+ */
+ function get_detail_purchase($p_from,$p_end)
+ {
+ global $g_user;
+ // Journal valide
+ if ( $this->id == 0 ) die (__FILE__.":".__LINE__." Journal invalide");
+
+ // Securite
+ if ( $g_user->get_ledger_access($this->id) == 'X' ) return null;
+
+ // get the data from the view
+ $sql = "select *
+ from v_detail_purchase
+ where
+ jr_def_id = $1
+ and jr_date >= (select p_start from parm_periode where p_id =
$2)
+ and jr_date <= (select p_end from parm_periode where p_id =
$3) "
+ .' order by
jr_date,substring(jr_pj_number,\'[0-9]+$\')::numeric asc ';
+ $ret = $this->db->exec_sql($sql, array($this->id,$p_from, $p_end));
+ return $ret;
+ }
}
Modified: phpcompta/trunk/include/class_acc_ledger_sold.php
===================================================================
--- phpcompta/trunk/include/class_acc_ledger_sold.php 2013-10-11 21:30:56 UTC
(rev 5486)
+++ phpcompta/trunk/include/class_acc_ledger_sold.php 2013-10-12 14:27:48 UTC
(rev 5487)
@@ -1271,7 +1271,33 @@
$r.= create_script("$('" . $Date->id . "').focus()");
return $r;
}
-
+ /**
+ * Retrieve data from the view v_detail_sale
+ * @global $g_user connected user
+ * @param $p_from jrn.jr_tech_per from
+ * @param type $p_end jrn.jr_tech_per to
+ * @return type
+ */
+ function get_detail_sale($p_from,$p_end)
+ {
+ global $g_user;
+ // Journal valide
+ if ( $this->id == 0 ) die (__FILE__.":".__LINE__." Journal invalide");
+
+ // Securite
+ if ( $g_user->get_ledger_access($this->id) == 'X' ) return null;
+
+ // get the data from the view
+ $sql = "select *
+ from v_detail_sale
+ where
+ jr_def_id = $1
+ and jr_date >= (select p_start from parm_periode where p_id =
$2)
+ and jr_date <= (select p_end from parm_periode where p_id =
$3) "
+ .' order by
jr_date,substring(jr_pj_number,\'[0-9]+$\')::numeric asc ';
+ $ret = $this->db->exec_sql($sql, array($this->id,$p_from, $p_end));
+ return $ret;
+ }
/* !\brief test function
*/
Modified: phpcompta/trunk/include/class_print_ledger.php
===================================================================
--- phpcompta/trunk/include/class_print_ledger.php 2013-10-11 21:30:56 UTC
(rev 5486)
+++ phpcompta/trunk/include/class_print_ledger.php 2013-10-12 14:27:48 UTC
(rev 5487)
@@ -29,6 +29,7 @@
require_once('class_print_ledger_simple_without_vat.php');
require_once('class_print_ledger_fin.php');
require_once('class_print_ledger_misc.php');
+require_once 'class_print_ledger_detail_item.php';
/**
* @brief Strategie class for the print_ledger class
Modified: phpcompta/trunk/include/class_print_ledger_detail_item.php
===================================================================
--- phpcompta/trunk/include/class_print_ledger_detail_item.php 2013-10-11
21:30:56 UTC (rev 5486)
+++ phpcompta/trunk/include/class_print_ledger_detail_item.php 2013-10-12
14:27:48 UTC (rev 5487)
@@ -5,9 +5,11 @@
*
* @author danydb
*/
-class Print_Ledger_Detail_Item extends PDF
+require_once 'class_acc_ledger_sold.php';
+require_once 'class_acc_ledger_purchase.php';
+class Print_Ledger_Detail_Item extends PDFLand
{
- public function __construct ($p_cn,$p_jrn)
+ public function __construct (Database $p_cn,Acc_Ledger $p_jrn)
{
if($p_cn == null) die("No database connection. Abort.");
@@ -39,39 +41,8 @@
*/
function Footer()
{
- //Position at 3 cm from bottom
- $this->SetY(-20);
- /* write reporting */
- $this->Cell(143,6,'Total page ','T',0,'R'); /* HTVA */
- $this->Cell(15,6,nbm($this->tp_htva),'T',0,'R'); /* HTVA */
- if ( $this->jrn_type !='VEN')
- {
- $this->Cell(15,6,nbm($this->tp_priv),'T',0,'R'); /* prive */
- $this->Cell(15,6,nbm($this->tp_nd),'T',0,'R'); /* Tva ND */
- }
- foreach($this->a_Tva as $line_tva)
- {
- $l=$line_tva['tva_id'];
- $this->Cell(15,6,nbm($this->tp_tva[$l]),'T',0,'R');
- }
- $this->Cell(15,6,nbm($this->tp_tvac),'T',0,'R'); /* Tvac */
$this->Ln(2);
-
- $this->Cell(143,6,'report',0,0,'R'); /* HTVA */
- $this->Cell(15,6,nbm($this->rap_htva),0,0,'R'); /* HTVA */
- if ( $this->jrn_type !='VEN')
- {
- $this->Cell(15,6,nbm($this->rap_priv),0,0,'R'); /* prive */
- $this->Cell(15,6,nbm($this->rap_nd),0,0,'R'); /* Tva ND */
- }
- foreach($this->a_Tva as $line_tva)
- {
- $l=$line_tva['tva_id'];
- $this->Cell(15,6,nbm($this->rap_tva[$l]),0,0,'R');
- }
- $this->Cell(15,6,nbm($this->rap_tvac),0,0,'R'); /* Tvac */
- $this->Ln(2);
-
+ $this->Cell(0,8,' Journal '.$this->ledger->get_name(),0,0,'C');
//Arial italic 8
$this->SetFont('Arial', 'I', 8);
//Page number
@@ -91,12 +62,69 @@
function export()
{
bcscale(2);
- $a_jrn=$this->ledger->get_operation($_GET['from_periode'],
- $_GET['to_periode']);
-
- if ( $a_jrn == null ) return;
- for ( $i=0;$i<count($a_jrn);$i++)
+ $jrn_type=$this->ledger->get_type();
+ switch ($jrn_type)
+ {
+ case 'VEN':
+ $ledger=new Acc_Ledger_Sold($this->cn,
$this->ledger->jrn_def_id);
+
$ret_detail=$ledger->get_detail_sale($_GET['from_periode'],$_GET['to_periode']);
+ break;
+ case 'ACH':
+ $ledger=new Acc_Ledger_Purchase($this->cn,
$this->ledger->jrn_def_id);
+
$ret_detail=$ledger->get_detail_purchase($_GET['from_periode'],$_GET['to_periode']);
+ break;
+ default:
+ die (__FILE__.":".__LINE__.'Journal invalide');
+ break;
+ }
+ if ( $ret_detail == null ) return;
+ $nb=Database::num_row($ret_detail);
+ $this->SetFont('DejaVu', '', 6);
+ $internal="";
+ $this->SetFillColor(220,221,255);
+ $high=4;
+ for ( $i=0;$i< $nb ;$i++)
{
+ $row=Database::fetch_array($ret_detail, $i);
+ if ($internal != $row['jr_internal'])
+ {
+ // Print the general info line width=270mm
+ $this->LongLine(20, $high, $row['jr_date'],1, 'L', true);
+ $this->Cell(20, $high, $row['jr_internal'], 1, 0, 'L', true);
+ $this->LongLine(70, $high, $row['quick_code']."
".$row['tiers_name'],1,'L',true);
+ $this->LongLine(100, $high, $row['jr_comment'],1,'L',true);
+ $this->Cell(20, $high, nbm($row['htva']), 1, 0, 'R', true);
+ $this->Cell(20, $high, nbm($row['tot_vat']), 1, 0, 'R', true);
+ $sum=bcadd($row['htva'],$row['tot_vat']);
+ $this->Cell(20, $high, nbm($sum), 1, 0, 'R', true);
+ $internal=$row['jr_internal'];
+ $this->Ln(6);
+ //
+ // Header detail
+ $this->LongLine(30,$high,'QuickCode');
+ $this->Cell(30,$high,'Poste');
+ $this->LongLine(90,$high,'Libellé');
+ $this->Cell(20,$high,'Prix/Unit',0,0,'R');
+ $this->Cell(20,$high,'Quant.',0,0,'R');
+ $this->Cell(20,$high,'HTVA',0,0,'R');
+ $this->Cell(20,$high,'Code TVA');
+ $this->Cell(20,$high,'TVA',0,0,'R');
+ $this->Cell(20,$high,'TVAC',0,0,'R');
+ $this->Ln(6);
+ }
+ // Print detail sale / purchase
+ $this->LongLine(30,$high,$row['j_qcode']);
+ $this->Cell(30,$high,$row['j_poste']);
+ $comment=($row['j_text']=="")?$row['item_name']:$row['j_text'];
+ $this->LongLine(90,$high,$comment);
+ $this->Cell(20,$high,nbm($row['price_per_unit']),0,0,'R');
+ $this->Cell(20,$high,nbm($row['quantity']),0,0,'R');
+ $this->Cell(20,$high,nbm($row['price']),0,0,'R');
+ $this->Cell(20,$high,$row['vat_code']." ".$row['tva_label']);
+ $this->Cell(20,$high,nbm($row['vat']),0,0,'R');
+ $sum=bcadd($row['price'],$row['vat']);
+ $this->Cell(20,$high,nbm($sum),0,0,'R');
+ $this->Ln(6);
}
}
Modified: phpcompta/trunk/include/export_ledger_csv.php
===================================================================
--- phpcompta/trunk/include/export_ledger_csv.php 2013-10-11 21:30:56 UTC
(rev 5486)
+++ phpcompta/trunk/include/export_ledger_csv.php 2013-10-12 14:27:48 UTC
(rev 5487)
@@ -27,7 +27,8 @@
header('Content-Disposition: attachment;filename="jrn.csv"',FALSE);
include_once ("ac_common.php");
require_once('class_own.php');
-
+require_once 'class_acc_ledger_sold.php';
+require_once 'class_acc_ledger_purchase.php';
require_once('class_dossier.php');
$gDossier=dossier::id();
@@ -50,6 +51,54 @@
$Jrn->get_name();
$jrn_type=$Jrn->get_type();
+//
+// With Detail per item which is possible only for VEN or ACH
+//
+if ($_GET['p_simple'] == 2)
+{
+ if ($jrn_type != 'ACH' && $jrn_type != 'VEN' || $Jrn->id == 0)
+ {
+ $_GET['p_simple'] = 0;
+ }
+ else
+ {
+ switch ($jrn_type)
+ {
+ case 'VEN':
+ $ledger = new Acc_Ledger_Sold($cn, $_GET['jrn_id']);
+ $ret_detail = $ledger->get_detail_sale($_GET['from_periode'],
$_GET['to_periode']);
+ break;
+ case 'ACH':
+ $ledger = new Acc_Ledger_Purchase($cn, $_GET['jrn_id']);
+ $ret_detail =
$ledger->get_detail_purchase($_GET['from_periode'], $_GET['to_periode']);
+
+ break;
+ default:
+ die(__FILE__ . ":" . __LINE__ . 'Journal invalide');
+ break;
+ }
+ if ($ret_detail == null)
+ return;
+ $nb = Database::num_row($ret_detail);
+ $output=fopen("php://output","w");
+
+ for ($i = 0;$i < $nb ; $i++) {
+ $row=Database::fetch_array($ret_detail, $i);
+ if ( $i == 0 ) {
+ foreach ($row as $key=>$value) {
+ if (isNumber($key) == 0 )$array_key[]=$key;
+ }
+ fputcsv($output,$array_key,';');
+ }
+ $a_row=array();
+ for ($j=0;$j < count($row) / 2;$j++) {
+ $a_row[]=$row[$j];
+ }
+ fputcsv($output,$a_row,';');
+ unset($a_row);
+ }
+ }
+}
// Detailled printing
//---
if ( $_GET['p_simple'] == 0 )
@@ -85,7 +134,7 @@
}
exit;
}
-else
+else if ($_GET['p_simple'] == 0)
{
$Row=$Jrn->get_rowSimple($_GET['from_periode'],
$_GET['to_periode'],
Modified: phpcompta/trunk/include/impress_jrn.inc.php
===================================================================
--- phpcompta/trunk/include/impress_jrn.inc.php 2013-10-11 21:30:56 UTC (rev
5486)
+++ phpcompta/trunk/include/impress_jrn.inc.php 2013-10-12 14:27:48 UTC (rev
5487)
@@ -172,33 +172,31 @@
echo '<h2 class="info">' . h($Jrn->name) . '</h2>';
echo "<table>";
echo '<TR>';
- if ($_GET['p_simple'] == 0 || $_GET['p_simple'] == 1)
- {
- echo '<TD><form method="GET" ACTION="?">' . dossier::hidden() .
- $hid->input("type", "jrn") . $hid->input('p_action', 'impress')
. "</form></TD>";
+ echo '<TD><form method="GET" ACTION="?">' . dossier::hidden() .
+ $hid->input("type", "jrn") . $hid->input('p_action', 'impress') .
"</form></TD>";
- echo '<TD><form method="GET" ACTION="export.php">' .
dossier::hidden() .
- HtmlInput::submit('bt_pdf', "Export PDF") .
- HtmlInput::hidden('act', 'PDF:ledger') .
- $hid->input("type", "jrn") .
- $hid->input("jrn_id", $Jrn->id) .
- $hid->input("from_periode", $_GET['from_periode']) .
- $hid->input("to_periode", $_GET['to_periode']);
- echo $hid->input("p_simple", $_GET['p_simple']);
- echo HtmlInput::get_to_hidden(array('ac', 'type'));
- echo "</form></TD>";
+ echo '<TD><form method="GET" ACTION="export.php">' . dossier::hidden()
.
+ HtmlInput::submit('bt_pdf', "Export PDF") .
+ HtmlInput::hidden('act', 'PDF:ledger') .
+ $hid->input("type", "jrn") .
+ $hid->input("jrn_id", $Jrn->id) .
+ $hid->input("from_periode", $_GET['from_periode']) .
+ $hid->input("to_periode", $_GET['to_periode']);
+ echo $hid->input("p_simple", $_GET['p_simple']);
+ echo HtmlInput::get_to_hidden(array('ac', 'type'));
+ echo "</form></TD>";
- echo '<TD><form method="GET" ACTION="export.php">' .
dossier::hidden() .
- HtmlInput::submit('bt_csv', "Export CSV") .
- HtmlInput::hidden('act', 'CSV:ledger') .
- $hid->input("type", "jrn") .
- $hid->input("jrn_id", $Jrn->id) .
- $hid->input("from_periode", $_GET['from_periode']) .
- $hid->input("to_periode", $_GET['to_periode']);
- echo $hid->input("p_simple", $_GET['p_simple']);
- echo HtmlInput::get_to_hidden(array('ac', 'type'));
- echo "</form></TD>";
- }
+ echo '<TD><form method="GET" ACTION="export.php">' . dossier::hidden()
.
+ HtmlInput::submit('bt_csv', "Export CSV") .
+ HtmlInput::hidden('act', 'CSV:ledger') .
+ $hid->input("type", "jrn") .
+ $hid->input("jrn_id", $Jrn->id) .
+ $hid->input("from_periode", $_GET['from_periode']) .
+ $hid->input("to_periode", $_GET['to_periode']);
+ echo $hid->input("p_simple", $_GET['p_simple']);
+ echo HtmlInput::get_to_hidden(array('ac', 'type'));
+ echo "</form></TD>";
+
echo '<td style="vertical-align:top">';
echo HtmlInput::print_window();
echo '</td>';
Modified: phpcompta/trunk/sql/upgrade.sql
===================================================================
--- phpcompta/trunk/sql/upgrade.sql 2013-10-11 21:30:56 UTC (rev 5486)
+++ phpcompta/trunk/sql/upgrade.sql 2013-10-12 14:27:48 UTC (rev 5487)
@@ -107,3 +107,84 @@
update menu_ref set me_description_etendue='Suivi des administrations :
courrrier, déclarations.' where me_code='ADM' ;
update menu_ref set me_description_etendue='Prévision de vos achats, revenus,
permet de suivre l''évolution de votre société. Vos prévisions sont des
formules sur les postes comptables et vous permettent aussi vos marges brutes.'
where me_code='FORECAST' ;
+CREATE OR REPLACE VIEW v_detail_sale AS
+with m as
+ (select sum(qs_price) as htva, sum(qs_vat) as tot_vat,jr_id from
quant_sold join jrnx using (j_id) join jrn on (j_grpt=jr_grpt_id) group by
jr_id)
+ SELECT jrn.jr_id,
+ jrn.jr_date,
+ jrn.jr_tech_per,
+ jrn.jr_comment,
+ jrn.jr_pj_number,
+ jrn.jr_internal,
+ jrn.jr_def_id,
+ jrnx.j_poste,
+ jrnx.j_text,
+ jrnx.j_qcode,
+ quant_sold.qs_fiche as item_card,
+ a.name AS item_name,
+ quant_sold.qs_client,
+ b.vw_name AS tiers_name,
+ b.quick_code,
+ tva_rate.tva_label,
+ tva_rate.tva_comment,
+ tva_rate.tva_both_side,
+ quant_sold.qs_vat_sided as vat_sided,
+ quant_sold.qs_vat_code as vat_code,
+ quant_sold.qs_vat as vat,
+ quant_sold.qs_price as price,
+ quant_sold.qs_quantite as quantity,
+ quant_sold.qs_price / quant_sold.qs_quantite AS price_per_unit
+ , htva
+ ,tot_vat
+ FROM jrn
+ JOIN jrnx ON jrn.jr_grpt_id = jrnx.j_grpt
+ JOIN quant_sold USING (j_id)
+ JOIN vw_fiche_name a ON quant_sold.qs_fiche = a.f_id
+ JOIN vw_fiche_attr b ON quant_sold.qs_client = b.f_id
+ JOIN tva_rate ON quant_sold.qs_vat_code = tva_rate.tva_id
+ join m on (m.jr_id=jrn.jr_id)
+ ;
+
+CREATE OR REPLACE VIEW v_detail_purchase AS
+ WITH m AS (
+ SELECT sum(quant_purchase.qp_price) AS htva,
sum(quant_purchase.qp_vat) AS tot_vat, jrn.jr_id
+ FROM quant_purchase
+ JOIN jrnx USING (j_id)
+ JOIN jrn ON jrnx.j_grpt = jrn.jr_grpt_id
+ GROUP BY jrn.jr_id
+ )
+ SELECT jrn.jr_id,
+ jrn.jr_date
+ , jrn.jr_tech_per
+ , jrn.jr_comment
+ , jrn.jr_pj_number
+ , jrn.jr_internal
+ , jrn.jr_def_id
+ , jrnx.j_poste
+ , jrnx.j_text
+ , jrnx.j_qcode
+ , quant_purchase.qp_fiche AS item_card
+ , a.name AS item_name
+ , quant_purchase.qp_supplier
+ , b.vw_name AS tiers_name
+ , b.quick_code
+ , tva_rate.tva_label
+ , tva_rate.tva_comment
+ , tva_rate.tva_both_side
+ , quant_purchase.qp_vat_sided AS vat_sided
+ , quant_purchase.qp_vat_code AS vat_code
+ , quant_purchase.qp_vat AS vat
+ , quant_purchase.qp_price AS price
+ , quant_purchase.qp_quantite AS quantity
+ , quant_purchase.qp_price / quant_purchase.qp_quantite AS
price_per_unit
+ , quant_purchase.qp_nd_amount AS non_ded_amount
+ , quant_purchase.qp_nd_tva AS non_ded_tva
+ , quant_purchase.qp_nd_tva_recup AS non_ded_tva_recup
+ , m.htva, m.tot_vat
+ FROM jrn
+ JOIN jrnx ON jrn.jr_grpt_id = jrnx.j_grpt
+ JOIN quant_purchase USING (j_id)
+ JOIN vw_fiche_name a ON quant_purchase.qp_fiche = a.f_id
+ JOIN vw_fiche_attr b ON quant_purchase.qp_supplier = b.f_id
+ JOIN tva_rate ON quant_purchase.qp_vat_code = tva_rate.tva_id
+ JOIN m ON m.jr_id = jrn.jr_id;
---
PhpCompta est un logiciel de comptabilité libre en ligne (full web)
Projet opensource http://www.phpcompta.eu
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpcompta-dev] r5487 - in phpcompta/trunk: include sql,
phpcompta-dev <=