[Fmsystem-commits] [10700] Property: update accounting reporting

[Sigurd Nes]

Revision: 10700
          http://svn.sv.gnu.org/viewvc/?view=rev&root=fmsystem&revision=10700
Author:   sigurdne
Date:     2013-01-21 19:16:28 +0000 (Mon, 21 Jan 2013)
Log Message:
-----------
Property: update accounting reporting

Modified Paths:
--------------
    trunk/property/inc/class.soproject.inc.php
    trunk/property/inc/class.soworkorder.inc.php

Modified: trunk/property/inc/class.soproject.inc.php
===================================================================
--- trunk/property/inc/class.soproject.inc.php  2013-01-21 14:12:00 UTC (rev 
10699)
+++ trunk/property/inc/class.soproject.inc.php  2013-01-21 19:16:28 UTC (rev 
10700)
@@ -731,11 +731,12 @@
                                                . " billable_hours, closed, 
sum(fm_orders_paid_or_pending_view.amount) AS actual_cost"
                                                . " FROM fm_workorder"
                                                . " {$this->join} 
fm_workorder_status ON fm_workorder.status  = fm_workorder_status.id"
-                                       //      . " {$this->join} 
fm_workorder_budget ON (fm_workorder.id = fm_workorder_budget.order_id AND year 
= '{$filter_year}')"
+                                               . " {$this->join} 
fm_workorder_budget ON (fm_workorder.id = fm_workorder_budget.order_id)"// AND 
year = '{$filter_year}')"
 //                                             . " {$this->join} 
fm_workorder_budget ON (fm_workorder.id = fm_workorder_budget.order_id )"
-                                               . " {$this->left_join} 
fm_orders_paid_or_pending_view ON (fm_workorder.id = 
fm_orders_paid_or_pending_view.order_id {$sql_filter_period})"
-//                                             . " {$this->left_join} 
fm_orders_paid_or_pending_view ON (fm_workorder.id = 
fm_orders_paid_or_pending_view.order_id AND( periode < {$filter_year}13 OR 
periode IS NULL))"
-                                               . " WHERE project_id = 
'{$project['project_id']}' {$sql_workder_date_filter} OR (project_id = 
'{$project['project_id']}' AND fm_workorder_status.closed IS NULL)"
+//                                             . " {$this->left_join} 
fm_orders_paid_or_pending_view ON (fm_workorder.id = 
fm_orders_paid_or_pending_view.order_id {$sql_filter_period})"
+                                               . " {$this->left_join} 
fm_orders_paid_or_pending_view ON (fm_workorder.id = 
fm_orders_paid_or_pending_view.order_id AND( periode < {$filter_year}13 OR 
periode IS NULL))"
+//                                             . " WHERE project_id = 
'{$project['project_id']}' {$sql_workder_date_filter} OR (project_id = 
'{$project['project_id']}' AND fm_workorder_status.closed IS NULL)"
+                                               . " WHERE (project_id = 
'{$project['project_id']}' AND fm_workorder_budget.year = {$filter_year} ) OR 
(project_id = '{$project['project_id']}' AND fm_workorder_status.closed IS 
NULL)"
                                                . " GROUP BY fm_workorder.id, 
billable_hours, closed";
 //_debug_array($sql_workder);
 
@@ -748,13 +749,13 @@
                                                $project['budget'] =  
$this->db->f('budget');
 
                                                $sql_workder  = "SELECT 
fm_workorder.id, sum(fm_workorder_budget.budget) AS budget, 
sum(fm_workorder_budget.combined_cost) AS combined_cost,"
-                                               . " billable_hours, closed, 
actual_cost, pending_cost"
+                                               . " billable_hours, closed, 
sum(fm_orders_paid_or_pending_view.amount) as actual_cost"
                                                . " FROM fm_workorder"
                                                . " {$this->join} 
fm_workorder_status ON fm_workorder.status  = fm_workorder_status.id"
                                                . " {$this->left_join} 
fm_workorder_budget ON (fm_workorder.id = fm_workorder_budget.order_id AND 
active = 1)"
-                                               . " {$this->left_join} 
fm_orders_pending_cost_view ON fm_workorder.id = 
fm_orders_pending_cost_view.order_id"
+                                               . " {$this->left_join} 
fm_orders_paid_or_pending_view ON fm_workorder.id = 
fm_orders_paid_or_pending_view.order_id"
                                                . " WHERE project_id = 
'{$project['project_id']}' {$sql_workder_date_filter}"
-                                               . " GROUP BY fm_workorder.id, 
billable_hours, closed, actual_cost, pending_cost";
+                                               . " GROUP BY fm_workorder.id, 
billable_hours, closed";
 
                                        }
 //~ _debug_array($sql_workder);

Modified: trunk/property/inc/class.soworkorder.inc.php
===================================================================
--- trunk/property/inc/class.soworkorder.inc.php        2013-01-21 14:12:00 UTC 
(rev 10699)
+++ trunk/property/inc/class.soworkorder.inc.php        2013-01-21 19:16:28 UTC 
(rev 10700)
@@ -767,16 +767,15 @@
                                }
 
 
-                               $sql_cost = "SELECT fm_workorder.id as 
order_id,closed, sum(fm_orders_paid_or_pending_view.amount) as actual_cost, 
fm_location1.mva,"
+                               $sql_cost = "SELECT fm_workorder.id as 
order_id,closed, fm_location1.mva,"
                                . " sum(fm_workorder_budget.budget) AS budget, 
sum(fm_workorder_budget.combined_cost) AS combined_cost"
-                               . " FROM fm_workorder {$this->left_join} 
fm_orders_paid_or_pending_view ON fm_workorder.id = 
fm_orders_paid_or_pending_view.order_id"
+                               . " FROM fm_workorder" // {$this->left_join} 
fm_orders_paid_or_pending_view ON fm_workorder.id = 
fm_orders_paid_or_pending_view.order_id"
                                . " {$this->join} fm_workorder_status ON 
fm_workorder.status = fm_workorder_status.id"
-                               . " {$this->join} fm_workorder_budget ON 
(fm_workorder.id = fm_workorder_budget.order_id)"
+                               . " {$this->left_join} fm_workorder_budget ON 
(fm_workorder.id = fm_workorder_budget.order_id AND fm_workorder_budget.active 
= 1)"
                                . " {$_join_district}"
                                . ' WHERE fm_workorder.id IN (' . implode(',', 
$_order_list ) .')'
                                . " GROUP BY fm_workorder.id,closed, 
fm_location1.mva";
 
-                               unset($_order_list);
                                $this->db->query($sql_cost,__LINE__,__FILE__);
                                while ($this->db->next_record())
                                {
@@ -788,8 +787,16 @@
                                                'closed'                => 
!!$this->db->f('closed'),
                                                'mva'                   => 
(int)$this->db->f('mva'),
                                        );
+
+                                       $this->db2->query('SELECT 
sum(fm_orders_paid_or_pending_view.amount) as actual_cost'
+                                       . ' FROM fm_orders_paid_or_pending_view 
WHERE order_id = '. $this->db->f('order_id'),__LINE__,__FILE__);
+                                       $this->db2->next_record();
+                                       
$_actual_cost_arr[$this->db->f('order_id')]['actual_cost'] = 
$this->db2->f('actual_cost');
                                }
 
+                               unset($_order_list);
+
+
                        }
 
                        $this->db->query('SELECT id, percent FROM 
fm_ecomva',__LINE__,__FILE__);