UN 20 Recs: Quartal als gültige Einheit hinzugefügt

[mfinanz.git] / SL / OE.pm

diff --git a/SL/OE.pm b/SL/OE.pm
index 74c9ffdfb874cbe6808fb2ca7d8b9b9df10dad0e..144cb1cfd21c6d1c527915e04f2e65b8bc6cea98 100644 (file)
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -100,6 +100,12 @@ sub transactions {
         FROM record_links rl1
         LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
         WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
         FROM record_links rl1
         LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
         WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'

+        UNION
+        SELECT rl1.from_id, rl3.to_id
+        FROM record_links rl1
+        JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
+        JOIN record_links rl3 ON (rl2.to_table = rl3.from_table AND rl2.to_id = rl3.from_id)
+        WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar' AND rl3.to_table = 'ar'

       ) rl
       LEFT JOIN ar ON ar.id = rl.to_id
 
       ) rl
       LEFT JOIN ar ON ar.id = rl.to_id
 


@@ -116,6 +122,7 @@ sub transactions {
     qq|  o.transaction_description, | .
     qq|  o.marge_total, o.marge_percent, | .
     qq|  o.itime::DATE AS insertdate, | .
     qq|  o.transaction_description, | .
     qq|  o.marge_total, o.marge_percent, | .
     qq|  o.itime::DATE AS insertdate, | .

+    qq|  department.description as department, | .

     qq|  ex.$rate AS exchangerate, | .
     qq|  pt.description AS payment_terms, | .
     qq|  pr.projectnumber AS globalprojectnumber, | .
     qq|  ex.$rate AS exchangerate, | .
     qq|  pt.description AS payment_terms, | .
     qq|  pr.projectnumber AS globalprojectnumber, | .


@@ -134,6 +141,7 @@ sub transactions {
     qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
     qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
     qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
     qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
     qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
     qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .

+    qq|LEFT JOIN department   ON (o.department_id = department.id) | .

     qq|$periodic_invoices_joins | .
     qq|WHERE (o.quotation = ?) |;
   push(@values, $quotation);
     qq|$periodic_invoices_joins | .
     qq|WHERE (o.quotation = ?) |;
   push(@values, $quotation);


@@ -181,7 +189,7 @@ SQL
     push(@values, (like($form->{"cp_name"}))x2);
   }
 
     push(@values, (like($form->{"cp_name"}))x2);
   }
 

-  if (!$main::auth->assert('sales_all_edit', 1)) {
+  if ( !(($vc eq 'customer' && $main::auth->assert('sales_all_edit', 1)) || ($vc eq 'vendor' && $main::auth->assert('purchase_all_edit', 1))) ) {

     $query .= " AND o.employee_id = (select id from employee where login= ?)";
     push @values, $::myconfig{login};
   }
     $query .= " AND o.employee_id = (select id from employee where login= ?)";
     push @values, $::myconfig{login};
   }


@@ -353,6 +361,7 @@ SQL
     "insertdate"              => "o.itime",
     "taxzone"                 => "tz.description",
     "payment_terms"           => "pt.description",
     "insertdate"              => "o.itime",
     "taxzone"                 => "tz.description",
     "payment_terms"           => "pt.description",

+    "department"              => "department.description",

   );
   if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
     $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}"  . ", o.itime ${sortdir}";
   );
   if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
     $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}"  . ", o.itime ${sortdir}";


@@ -368,8 +377,13 @@ SQL
   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     $ref->{billed_amount}    = $billed_amount{$ref->{id}};
     $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     $ref->{billed_amount}    = $billed_amount{$ref->{id}};
     $ref->{billed_netamount} = $billed_netamount{$ref->{id}};

-    $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
-    $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
+    if ($ref->{billed_amount} < 0) { # case: credit note(s) higher than invoices
+      $ref->{remaining_amount} = $ref->{amount} + $ref->{billed_amount};
+      $ref->{remaining_netamount} = $ref->{netamount} + $ref->{billed_netamount};
+    } else {
+      $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
+      $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
+    }

     $ref->{exchangerate} = 1 unless $ref->{exchangerate};
     push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
     $id{ $ref->{id} } = $ref->{id};
     $ref->{exchangerate} = 1 unless $ref->{exchangerate};
     push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
     $id{ $ref->{id} } = $ref->{id};


@@ -596,6 +610,9 @@ sub _save {
         require SL::DB::Customer;
         my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
         die "Can't find customer" unless $customer;
         require SL::DB::Customer;
         my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
         die "Can't find customer" unless $customer;

+        die $main::locale->text("Error while creating project with project number of new order number, project number #1 already exists!", $form->{ordnumber})
+          if SL::DB::Manager::Project->find_by(projectnumber => $form->{ordnumber});
+

         my $new_project = SL::DB::Project->new(
           projectnumber     => $form->{ordnumber},
           description       => $customer->name,
         my $new_project = SL::DB::Project->new(
           projectnumber     => $form->{ordnumber},
           description       => $customer->name,


@@ -1080,6 +1097,7 @@ sub _retrieve {
     my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
 
     $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
     my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
 
     $form->{taxzone_id} = 0 unless ($form->{taxzone_id});

+    unshift @values, ($form->{taxzone_id}) x 2;

 
     # retrieve individual items
     # this query looks up all information about the items
 
     # retrieve individual items
     # this query looks up all information about the items


@@ -1102,8 +1120,8 @@ sub _retrieve {
          JOIN parts p ON (o.parts_id = p.id)
          JOIN oe ON (o.trans_id = oe.id)
          LEFT JOIN chart c1 ON ((SELECT inventory_accno_id                   FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
          JOIN parts p ON (o.parts_id = p.id)
          JOIN oe ON (o.trans_id = oe.id)
          LEFT JOIN chart c1 ON ((SELECT inventory_accno_id                   FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)

-         LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
-         LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
+         LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id  FROM taxzone_charts tc WHERE tc.taxzone_id = ? and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
+         LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = ? and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)

          LEFT JOIN project pr ON (o.project_id = pr.id)
          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
       ($form->{id}
          LEFT JOIN project pr ON (o.project_id = pr.id)
          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
       ($form->{id}


@@ -1177,8 +1195,9 @@ sub _retrieve {
       # get tax rates and description
       my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
       $query =
       # get tax rates and description
       my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
       $query =

-        qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
-        qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
+        qq|SELECT c.accno, t.taxdescription, t.rate, t.id as tax_id, c.accno as taxnumber | .
+        qq|FROM tax t | .
+        qq|LEFT JOIN chart c on (c.id = t.chart_id) | .

         qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
         qq|               WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
         qq|                 AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
         qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
         qq|               WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
         qq|                 AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .


@@ -1196,6 +1215,7 @@ sub _retrieve {
           $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
           $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
           $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};
           $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
           $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
           $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};

+          $form->{"$ptr->{accno}_tax_id"}      = $ptr->{tax_id};

           $form->{taxaccounts} .= "$ptr->{accno} ";
         }
 
           $form->{taxaccounts} .= "$ptr->{accno} ";
         }
 


@@ -1512,9 +1532,9 @@ sub order_details {
         # get parts and push them onto the stack
         my $sortorder = "";
         if ($form->{groupitems}) {
         # get parts and push them onto the stack
         my $sortorder = "";
         if ($form->{groupitems}) {

-          $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
+          $sortorder = qq|ORDER BY pg.partsgroup, a.position|;

         } else {
         } else {

-          $sortorder = qq|ORDER BY a.oid|;
+          $sortorder = qq|ORDER BY a.position|;

         }
 
         $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
         }
 
         $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .


@@ -1573,10 +1593,13 @@ sub order_details {
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} },        $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} },  $form->{"${item}_rate"} * 100);
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} },      $form->{"${item}_taxnumber"});
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} },        $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} },  $form->{"${item}_rate"} * 100);
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} },      $form->{"${item}_taxnumber"});

+    push(@{ $form->{TEMPLATE_ARRAYS}->{tax_id} },         $form->{"${item}_tax_id"});

 
 

-    my $tax_obj     = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
-    my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription',  $form->{language_id}, 0) : '';
-    push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
+    if ( $form->{"${item}_tax_id"} ) {
+      my $tax_obj = SL::DB::Manager::Tax->find_by(id => $form->{"${item}_tax_id"}) or die "Can't find tax with id " . $form->{"${item}_tax_id"};
+      my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription',  $form->{language_id}, 0) : '';
+      push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
+    }

   }
 
   $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
   }
 
   $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);