Fixing error searching by address

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1263 4979c152-3d1c-0410-bac9-87ea11338e46

12 files changed, 53 insertions, 73 deletions

diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm
index ed49c986..2edcc44a 100644
--- a/LedgerSMB/AA.pm
+++ b/LedgerSMB/AA.pm
@@ -766,7 +766,7 @@ sub transactions {
 
     my $where = "1 = 1";
     if ( $form->{"$form->{vc}_id"} ) {
-        $where .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
+        $where .= qq| AND a.entity_id = $form->{entity_id}|;
     }
     else {
         if ( $form->{ $form->{vc} } ) {
diff --git a/LedgerSMB/AM.pm b/LedgerSMB/AM.pm
index 74034fd3..618c4d0b 100644
--- a/LedgerSMB/AM.pm
+++ b/LedgerSMB/AM.pm
@@ -969,7 +969,7 @@ sub recurring_transactions {
                           AS expired
 		     FROM recurring s
 		     JOIN ar a ON (a.id = s.id)
-		     JOIN customer n ON (n.id = a.customer_id)
+		     JOIN customer n USING (entity_id)
 		LEFT JOIN recurringemail se ON (se.id = s.id)
 		LEFT JOIN recurringprint sp ON (sp.id = s.id)
 		LEFT JOIN exchangerate ex 
@@ -1024,7 +1024,7 @@ sub recurring_transactions {
 		          AS expired
 		     FROM recurring s
 		     JOIN oe a ON (a.id = s.id)
-		     JOIN customer n ON (n.id = a.customer_id)
+		     JOIN customer USING (entity_id)
 		LEFT JOIN recurringemail se ON (se.id = s.id)
 		LEFT JOIN recurringprint sp ON (sp.id = s.id)
 		LEFT JOIN exchangerate ex ON 
diff --git a/LedgerSMB/BP.pm b/LedgerSMB/BP.pm
index f745f62d..4eaade6b 100644
--- a/LedgerSMB/BP.pm
+++ b/LedgerSMB/BP.pm
@@ -67,7 +67,7 @@ sub get_vc {
 			SELECT count(*)
 			  FROM (SELECT DISTINCT vc.id
 				  FROM $form->{vc} vc, $item a, status s
-				 WHERE a.$form->{vc}_id = vc.id
+				 WHERE a.entity_id = vc.entity_id
 			               AND s.trans_id = a.id
 			               AND s.formname = ?
 			               AND s.spoolfile IS NOT NULL) AS total|;
@@ -91,7 +91,7 @@ sub get_vc {
 				SELECT DISTINCT vc.id, vc.name
 				  FROM $item a
 				  JOIN $form->{vc} vc 
-				       ON (a.$form->{vc}_id = vc.id)
+				       USING (entity_id)
 				  JOIN status s ON (s.trans_id = a.id)
 				 WHERE s.formname = ?
 				       AND s.spoolfile IS NOT NULL|;
@@ -207,11 +207,11 @@ sub get_spoolfiles {
 				 WHERE s.trans_id = a.id
 				       AND s.spoolfile IS NOT NULL
 				       AND s.formname = ?
-				       AND a.$form->{vc}_id = vc.id|;
+				       AND a.entity_id = vc.entity_id|;
 
             push( @queryargs, $form->{type} );
             if ( $form->{"$form->{vc}_id"} ) {
-                $query .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
+                $query .= qq| AND a.entity_id = $form->{"entity_id"}|;
             }
             else {
 
diff --git a/LedgerSMB/CP.pm b/LedgerSMB/CP.pm
index 6dd6df43..72567a7b 100644
--- a/LedgerSMB/CP.pm
+++ b/LedgerSMB/CP.pm
@@ -129,7 +129,7 @@ sub get_openvc {
     my $arap = ( $form->{vc} eq 'customer' ) ? 'ar' : 'ap';
     my $query = qq|SELECT count(*)
 					 FROM $form->{vc} ct, $arap a
-					WHERE a.$form->{vc}_id = ct.id
+					WHERE a.entity_id = ct.entity_id
 					  AND a.amount != a.paid|;
 
     my ($count) = $dbh->selectrow_array($query);
@@ -138,7 +138,7 @@ sub get_openvc {
     my $ref;
     my $i = 0;
 
-    my $where = qq|WHERE a.$form->{vc}_id = ct.id
+    my $where = qq|WHERE a.entity_id = ct.entity_id
 					 AND a.amount != a.paid|;
 
     if ( $form->{ $form->{vc} } ) {
@@ -208,8 +208,8 @@ sub get_openinvoices {
     # connect to database
     my $dbh = $form->{dbh};
 
-    $vc_id = $dbh->quote( $form->{"$form->{vc}_id"} );
-    my $where = qq|WHERE a.$form->{vc}_id = $vc_id
+    $vc_id = $dbh->quote( $form->{"entity_id"} );
+    my $where = qq|WHERE a.entity__id = $vc_id
 					 AND a.amount != a.paid|;
 
     $curr = $dbh->quote( $form->{currency} );
@@ -251,9 +251,9 @@ sub get_openinvoices {
     }
 
     my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid,
-						  a.curr, c.name, a.$form->{vc}_id, c.language_code
+						  a.curr, c.name, a.entity_id, c.language_code
 					 FROM $form->{arap} a
-					 JOIN $form->{vc} c ON (c.id = a.$form->{vc}_id)
+					 JOIN $form->{vc} c ON (c.entity_id = a.entity_id)
 				   $where
 				 ORDER BY $sortorder|;
 
diff --git a/LedgerSMB/CT.pm b/LedgerSMB/CT.pm
index 7c1c3bf3..b64265ae 100644
--- a/LedgerSMB/CT.pm
+++ b/LedgerSMB/CT.pm
@@ -608,7 +608,7 @@ sub search {
     if ( $form->{address} ne "" ) {
         $var = $dbh->quote( $form->like( lc $form->{address} ) );
         $where .=
-" AND (lower(ct.address1) LIKE $var OR lower(ct.address2) LIKE '$var')";
+" AND (lower(ct.address1) ILIKE $var)";
     }
 
     if ( $form->{startdatefrom} ) {
@@ -634,17 +634,12 @@ sub search {
 			                    FROM oe o, $form->{db} vc
 			                   WHERE vc.id = o.$form->{db}_id)|;
 
-        if ( $form->{db} eq 'customer' ) {
-            $where .= qq| AND ct.id NOT IN (SELECT a.customer_id
+        if ( $form->{db} =~ /(^customer$|^vendor$)/ ) {
+            $where .= qq| AND ct.id NOT IN (SELECT a.entity_id
 											  FROM ar a, customer vc
-											 WHERE vc.id = a.customer_id)|;
+											 WHERE vc.entity_id = a.entity_id)|;
         }
 
-        if ( $form->{db} eq 'vendor' ) {
-            $where .= qq| AND ct.id NOT IN (SELECT a.vendor_id
-											  FROM ap a, vendor vc
-											 WHERE vc.id = a.vendor_id)|;
-        }
 
         $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
     }
@@ -888,7 +883,7 @@ sub get_history {
 
     if ( $form->{address} ne "" ) {
         $var = $dbh->quote( $form->like( lc $form->{address} ) );
-        $where .= " AND lower(ct.address1) LIKE $var";
+        $where .= " AND lower(ct.address1) ILIKE $var";
     }
 
     for (qw(name contact email phone notes city state zipcode country)) {
diff --git a/LedgerSMB/GL.pm b/LedgerSMB/GL.pm
index 2148050b..322e08b1 100644
--- a/LedgerSMB/GL.pm
+++ b/LedgerSMB/GL.pm
@@ -416,7 +416,7 @@ sub all_transactions {
 					 FROM ar a
 					 JOIN acc_trans ac ON (a.id = ac.trans_id)
 					 JOIN chart c ON (ac.chart_id = c.id)
-					 JOIN customer ct ON (a.customer_id = ct.id)
+					 JOIN customer ct USING (entity_id)
 				LEFT JOIN department d ON (d.id = a.department_id)
 					WHERE $arwhere
 
diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm
index 89f7d1cd..77736e11 100644
--- a/LedgerSMB/IC.pm
+++ b/LedgerSMB/IC.pm
@@ -1239,11 +1239,11 @@ sub all_parts {
 					          ON (p.id = i.parts_id)
 					     JOIN ar a ON (a.id = i.trans_id)
 					     JOIN customer ct 
-					          ON (a.customer_id = ct.id)
+					          USING (entity_id)
 					LEFT JOIN partsgroup pg 
 					          ON (p.partsgroup_id = pg.id)
 					LEFT JOIN employees e 
-					          ON (a.employee_id = e.id)
+					          ON (a.person_id = e.entity_id)
 					$makemodeljoin
 					    WHERE $invwhere|;
                 $union = "
@@ -1305,14 +1305,14 @@ sub all_parts {
 					     JOIN parts p ON (i.parts_id = p.id)
 					     JOIN oe a ON (i.trans_id = a.id)
 					     JOIN customer ct 
-					          ON (a.customer_id = ct.id)
+					          USING (entity_id)
 					LEFT JOIN partsgroup pg 
 					          ON (p.partsgroup_id = pg.id)
 					LEFT JOIN employees e 
 					          ON (a.employee_id = e.id)
 					$makemodeljoin
 					    WHERE $ordwhere 
-					          AND a.customer_id > 0|;
+					          AND a.entity_id IS NOT NULL|;
                 $union = "
 					UNION ALL";
             }
@@ -1411,14 +1411,14 @@ sub all_parts {
 					     JOIN parts p ON (i.parts_id = p.id)
 					     JOIN oe a ON (i.trans_id = a.id)
 					     JOIN customer ct 
-					          ON (a.customer_id = ct.id)
+					          USING (entity_id)
 					LEFT JOIN partsgroup pg 
 					          ON (p.partsgroup_id = pg.id)
 					LEFT JOIN employees e 
 					          ON (a.employee_id = e.id)
 					$makemodeljoin
 					    WHERE $quowhere
-					          AND a.customer_id > 0|;
+					          AND a.entity_id IS NOT NULL|;
                 $union = "
 					UNION ALL";
             }
diff --git a/LedgerSMB/IS.pm b/LedgerSMB/IS.pm
index e3b8d03c..f737e5d3 100644
--- a/LedgerSMB/IS.pm
+++ b/LedgerSMB/IS.pm
@@ -1742,10 +1742,10 @@ sub retrieve_invoice {
 			          a.intnotes,
 			          a.duedate, a.taxincluded, a.curr AS currency,
 			          a.employee_id, e.name AS employee, a.till, 
-			          a.customer_id,
+			          a.entity_id,
 			          a.language_code, a.ponumber
 			     FROM ar a
-			LEFT JOIN employees e ON (e.id = a.employee_id)
+			LEFT JOIN employees e ON (e.entity_id = a.employee_id)
 			    WHERE a.id = ?|;
 
         $sth = $dbh->prepare($query);
diff --git a/LedgerSMB/OE.pm b/LedgerSMB/OE.pm
index 2e074359..6294236b 100644
--- a/LedgerSMB/OE.pm
+++ b/LedgerSMB/OE.pm
@@ -248,7 +248,7 @@ sub save {
     my ( $self, $myconfig, $form ) = @_;
 
     $form->db_prepare_vars(
-        "quonumber", "transdate",     "vendor_id",     "customer_id",
+        "quonumber", "transdate",     "vendor_id",     "entity_id",
         "reqdate",   "taxincluded",   "shippingpoint", "shipvia",
         "currency",  "department_id", "employee_id",   "language_code",
         "ponumber",  "terms"
@@ -346,10 +346,10 @@ sub save {
         # $form->{id} is safe because it is only pulled *from* the db.
         $query = qq|
 			INSERT INTO oe 
-				(id, ordnumber, quonumber, transdate, vendor_id,
-				customer_id, reqdate, shippingpoint, shipvia,
+				(id, ordnumber, quonumber, transdate, 
+				entity_id, reqdate, shippingpoint, shipvia,
 				notes, intnotes, curr, closed, department_id,
-				employee_id, language_code, ponumber, terms,
+				person_id, language_code, ponumber, terms,
 				quotation)
 			VALUES 
 				($form->{id}, ?, ?, ?, ?,
@@ -358,12 +358,11 @@ sub save {
 				?, ?, ?, ?, ?)|;
         @queryargs = (
             $form->{ordnumber},     $form->{quonumber},
-            $form->{transdate},     $form->{vendor_id},
-            $form->{customer_id},   $form->{reqdate},
+            $form->{transdate},     $form->{entity_id}, $form->{reqdate},
             $form->{shippingpoint}, $form->{shipvia},
             $form->{notes},         $form->{intnotes},
             $form->{currency},      $form->{closed},
-            $form->{department_id}, $form->{employee_id},
+            $form->{department_id}, $form->{person_id},
             $form->{language_code}, $form->{ponumber},
             $form->{terms},         $quotation
         );
@@ -508,7 +507,7 @@ sub save {
     }
 
     # set values which could be empty
-    for (qw(vendor_id customer_id taxincluded closed quotation)) {
+    for (qw(entity_id taxincluded closed quotation)) {
         $form->{$_} *= 1;
     }
 
@@ -553,8 +552,7 @@ sub save {
 				ordnumber = ?, 
 				quonumber = ?,
 				transdate = ?,
-				vendor_id = ?,
-				customer_id = ?, 
+				entity_id = ?, 
 				amount = ?, 
 				netamount = ?,
 				reqdate = ?,
@@ -579,8 +577,7 @@ sub save {
 
         @queryargs = (
             $form->{ordnumber},     $form->{quonumber},
-            $form->{transdate},     $form->{vendor_id},
-            $form->{customer_id},   $amount,
+            $form->{transdate},     $form->{entity_id},   $amount,
             $netamount,             $form->{reqdate},
             $form->{taxincluded},   $form->{shippingpoint},
             $form->{shipvia},       $form->{notes},
@@ -2416,8 +2413,7 @@ sub generate_orders {
 			UPDATE oe SET
 				ordnumber = ?,
 				transdate = current_date,
-				vendor_id = ?,
-				customer_id = 0,
+				entity_id = ?
 				amount = ?,
 				netamount = ?,
 				taxincluded = ?,
@@ -2516,8 +2512,7 @@ sub consolidate_orders {
 
             # the orders
             @orderitems = ();
-            $form->{customer_id} = $form->{vendor_id} = 0;
-            $form->{"$form->{vc}_id"} = $vc_id;
+            $form->{entity_id} = $vc_id;
             $amount                   = 0;
             $netamount                = 0;
 
@@ -2570,8 +2565,8 @@ sub consolidate_orders {
 				UPDATE oe SET
 					ordnumber = | . $dbh->quote($ordnumber) . qq|,
 					transdate = current_date,
-					vendor_id = $form->{vendor_id},
-					customer_id = $form->{customer_id},
+					entity_id = | . 
+				            $dbh->quote($form->{entity_id}).qq|,
 					amount = $amount,
 					netamount = $netamount,
 					reqdate = | . $form->dbquote( $ref->{reqdate}, SQL_DATE ) . qq|,
diff --git a/LedgerSMB/PriceMatrix.pm b/LedgerSMB/PriceMatrix.pm
index 2c6cc332..bfebde4a 100644
--- a/LedgerSMB/PriceMatrix.pm
+++ b/LedgerSMB/PriceMatrix.pm
@@ -41,12 +41,12 @@ sub price_matrix_query {
     my $sth;
 
     my @queryargs;
+    my $entity_id     = $form->{dbh}->quote( $form->{entity_id} );
 
     if ( $form->{customer_id} ) {
         my $defaultcurrency = $form->{dbh}->quote( $form->{defaultcurrency} );
-        my $customer_id     = $form->{dbh}->quote( $form->{customer_id} );
         $query = qq|
-			SELECT p.id AS parts_id, 0 AS customer_id, 
+			SELECT p.id AS parts_id, 0 AS entity_id, 
 				0 AS pricegroup_id, 0 AS pricebreak, 
 				p.sellprice, NULL AS validfrom, NULL AS validto,
 				(SELECT substr(value,1,3) FROM defaults WHERE 
@@ -57,50 +57,49 @@ sub price_matrix_query {
 
 			UNION
 
-    			SELECT p.parts_id, p.customer_id, p.pricegroup_id, 
+    			SELECT p.parts_id, p.entity_id, p.pricegroup_id, 
 				p.pricebreak, p.sellprice, p.validfrom,
 				p.validto, p.curr, g.pricegroup
 			FROM partscustomer p
 			LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
 			WHERE p.parts_id = ?
-			AND p.customer_id = $customer_id
+			AND p.entity_id = $entity_id
 
 			UNION
 
-    			SELECT p.parts_id, p.customer_id, p.pricegroup_id, 
+    			SELECT p.parts_id, p.entity_id, p.pricegroup_id, 
 				p.pricebreak, p.sellprice, p.validfrom,
 				p.validto, p.curr, g.pricegroup
 			FROM partscustomer p
 			LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
 			JOIN customer c ON (c.pricegroup_id = g.id)
 			WHERE p.parts_id = ?
-			AND c.id = $customer_id
+			AND c.id = $entity_id
 
 			UNION
 
-    			SELECT p.parts_id, p.customer_id, p.pricegroup_id, 
+    			SELECT p.parts_id, p.entity_id, p.pricegroup_id, 
 				p.pricebreak, p.sellprice, p.validfrom,
 				p.validto, p.curr, g.pricegroup
 			FROM partscustomer p
 			LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
-			WHERE p.customer_id = 0
+			WHERE p.entity_id = 0
 			AND p.pricegroup_id = 0
 			AND p.parts_id = ?
 
-			ORDER BY customer_id DESC, pricegroup_id DESC, 
+			ORDER BY entity_id DESC, pricegroup_id DESC, 
 				pricebreak
 			|;
         $sth = $dbh->prepare($query) || $form->dberror($query);
     }
     elsif ( $form->{vendor_id} ) {
-        my $vendor_id = $form->{dbh}->quote( $form->{vendor_id} );
 
         # price matrix and vendor's partnumber
         $query = qq|
 			SELECT partnumber
 			FROM partsvendor
 			WHERE parts_id = ?
-			AND vendor_id = $vendor_id|;
+			AND entity_id = $entity_id|;
         $sth = $dbh->prepare($query) || $form->dberror($query);
     }
 
diff --git a/LedgerSMB/RC.pm b/LedgerSMB/RC.pm
index 6ffc16bf..e3517044 100644
--- a/LedgerSMB/RC.pm
+++ b/LedgerSMB/RC.pm
@@ -300,7 +300,7 @@ sub payment_transactions {
 			  FROM acc_trans ac
 			  JOIN chart ch ON (ac.chart_id = ch.id)
 			  JOIN ar a ON (a.id = ac.trans_id)
-			  JOIN customer n ON (n.id = a.customer_id)
+			  JOIN customer n USING (entity_id)
 			 WHERE ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
 			       $fx_transaction $cleared|;
         $query .= " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} )
@@ -335,7 +335,7 @@ sub payment_transactions {
         $query = qq|
 			SELECT c.name
 			  FROM customer c
-			  JOIN ar a ON (c.id = a.customer_id)
+			  JOIN ar a using (entity_id)
 			  JOIN acc_trans ac ON (a.id = ac.trans_id)
 			  JOIN chart ch ON (ac.chart_id = ch.id)
 			 WHERE ac.transdate = ?
@@ -368,7 +368,7 @@ sub payment_transactions {
         $query = qq|
 		SELECT c.name
 		  FROM customer c
-		  JOIN ar a ON (c.id = a.customer_id)
+		  JOIN ar a USING (entity_id)
 		  JOIN acc_trans ac ON (a.id = ac.trans_id)
 		  JOIN chart ch ON (ac.chart_id = ch.id)
 		 WHERE ac.transdate = ?
diff --git a/doc/faq.html b/doc/faq.html
index 1e3c3fb8..2665f72d 100644
--- a/doc/faq.html
+++ b/doc/faq.html
@@ -295,15 +295,6 @@ latex code.
 superior environment to produce professional-looking forms in
 postscript and PDF format.
 
-<p><li><h4>W3M</h4>
-pass terminal=mozilla when you start w3m
-<pre>
-  $ w3m -F http://localhost/ledger-smb/login.pl?terminal=mozilla</pre>
-
-To use without frames
-<pre>
-  $ w3m http://localhost/ledger-smb/login.pl?terminal=lynx</pre>
-
 <p><li><h4>PDF option disappeared</h4>
 Edit ledger-smb.conf and set:
 latex = 1