[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SF.net SVN: ledger-smb:[4855] trunk



Revision: 4855
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4855&view=rev
Author:   einhverfr
Date:     2012-06-07 02:31:00 +0000 (Thu, 07 Jun 2012)
Log Message:
-----------
barcode tracking for 1.4.  Barcodes are stored in make/model and looked up via the partnumber field in the invoice screen

Modified Paths:
--------------
    trunk/LedgerSMB/IC.pm
    trunk/LedgerSMB/IR.pm
    trunk/LedgerSMB/IS.pm
    trunk/bin/ic.pl
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Fixes.sql

Modified: trunk/LedgerSMB/IC.pm
===================================================================
--- trunk/LedgerSMB/IC.pm	2012-06-07 02:03:22 UTC (rev 4854)
+++ trunk/LedgerSMB/IC.pm	2012-06-07 02:31:00 UTC (rev 4855)
@@ -146,7 +146,7 @@
 
         if ( $form->{makemodel} ne "" ) {
             $query = qq|
-				SELECT make, model
+				SELECT make, model, barcode
 				  FROM makemodel
 				 WHERE parts_id = ?|;
 
@@ -283,13 +283,12 @@
 
         # get old price
         $query = qq|
-			SELECT id, listprice, sellprice, lastcost, weight, 
-			       project_id
+			SELECT id, listprice, sellprice, lastcost, weight
 			  FROM parts
 			 WHERE id = ?|;
         my $sth = $dbh->prepare($query);
         $sth->execute( $form->{id} );
-        my ( $id, $listprice, $sellprice, $lastcost, $weight, $project_id ) =
+        my ( $id, $listprice, $sellprice, $lastcost, $weight) =
           $sth->fetchrow_array();
 
         if ($id) {
@@ -479,15 +478,15 @@
     # insert makemodel records
     if ( $form->{item} =~ /(part|assembly)/ ) {
         $query = qq|
-			INSERT INTO makemodel (parts_id, make, model)
-			     VALUES (?, ?, ?)|;
+			INSERT INTO makemodel (parts_id, make, model, barcode)
+			     VALUES (?, ?, ?, ?)|;
         $sth = $dbh->prepare($query) || $form->dberror($query);
         for $i ( 1 .. $form->{makemodel_rows} ) {
             if (   ( $form->{"make_$i"} ne "" )
                 || ( $form->{"model_$i"} ne "" ) )
             {
                 $sth->execute( $form->{id}, $form->{"make_$i"},
-                    $form->{"model_$i"} )
+                    $form->{"model_$i"} , $form->{"barcode_$i"})
                   || $form->dberror($query);
             }
         }
@@ -690,8 +689,7 @@
         my $description = $dbh->quote( $form->like( lc $form->{description} ) );
         $where .= " AND lower(p.description) LIKE $description";
     }
-    $where .= qq| AND p.obsolete = '0'
-		AND p.project_id IS NULL|;
+    $where .= qq| AND not p.obsolete |;
 
     my %ordinal = (
         'partnumber'  => 2,

Modified: trunk/LedgerSMB/IR.pm
===================================================================
--- trunk/LedgerSMB/IR.pm	2012-06-07 02:03:22 UTC (rev 4854)
+++ trunk/LedgerSMB/IR.pm	2012-06-07 02:31:00 UTC (rev 4855)
@@ -1282,7 +1282,7 @@
 
     if ( $form->{"partnumber_$i"} ne "" ) {
         $var = $dbh->quote( $form->like( lc $form->{"partnumber_$i"} ) );
-        $where .= " AND lower(p.partnumber) LIKE $var";
+        $where .= " AND lower(p.partnumber) LIKE $var or mm.barcode is not null";
     }
 
     if ( $form->{"description_$i"} ne "" ) {
@@ -1317,6 +1317,8 @@
 		          t1.description AS translation, 
 		          t2.description AS grouptranslation
 		     FROM parts p
+                LEFT JOIN makemodel mm ON (mm.parts_id = p.id AND mm.barcode = |
+                             . $dbh->quote($form->{"partnumber_$i"}) . qq|)
 		LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
 		LEFT JOIN translation t1 
 		          ON (t1.trans_id = p.id AND t1.language_code = ?)

Modified: trunk/LedgerSMB/IS.pm
===================================================================
--- trunk/LedgerSMB/IS.pm	2012-06-07 02:03:22 UTC (rev 4854)
+++ trunk/LedgerSMB/IS.pm	2012-06-07 02:31:00 UTC (rev 4855)
@@ -2145,7 +2145,7 @@
 
     if ( $form->{"partnumber_$i"} ne "" ) {
         $var = $dbh->quote( $form->like( lc $form->{"partnumber_$i"} ) );
-        $where .= " AND lower(p.partnumber) LIKE $var";
+        $where .= " AND (lower(p.partnumber) LIKE $var or mm.barcode is not null)";
     }
     if ( $form->{"description_$i"} ne "" ) {
         $var = $dbh->quote( $form->like( lc $form->{"description_$i"} ) );
@@ -2189,6 +2189,8 @@
 		          t1.description AS translation, 
 		          t2.description AS grouptranslation
                      FROM parts p
+                LEFT JOIN makemodel mm ON (mm.parts_id = p.id AND mm.barcode = |
+                             . $dbh->quote($form->{"partnumber_$i"}) . qq|)
 		LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
 		LEFT JOIN translation t1 
 		          ON (t1.trans_id = p.id AND t1.language_code = ?)

Modified: trunk/bin/ic.pl
===================================================================
--- trunk/bin/ic.pl	2012-06-07 02:03:22 UTC (rev 4854)
+++ trunk/bin/ic.pl	2012-06-07 02:31:00 UTC (rev 4855)
@@ -269,7 +269,7 @@
     # setup make and models
     $i = 1;
     foreach $ref ( @{ $form->{makemodels} } ) {
-        for (qw(make model)) { $form->{"${_}_$i"} = $ref->{$_} }
+        for (qw(make model barcode)) { $form->{"${_}_$i"} = $ref->{$_} }
         $i++;
     }
     $form->{makemodel_rows} = $i - 1;
@@ -2773,6 +2773,7 @@
 	<tr>
 	  <th class="listheading">| . $locale->text('Make') . qq|</th>
 	  <th class="listheading">| . $locale->text('Model') . qq|</th>
+	  <th class="listheading">| . $locale->text('Bar Code') . qq|</th>
 	</tr>
 |;
 
@@ -2781,6 +2782,8 @@
 	<tr>
 	  <td><input name="make_$i" size=30 value="$form->{"make_$i"}"></td>
 	  <td><input name="model_$i" size=30 value="$form->{"model_$i"}"></td>
+          <td><input name="barcode_$i" size=30 value="$form->{"barcode_$i"}">
+          </td>
 	</tr>
 |;
     }

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2012-06-07 02:03:22 UTC (rev 4854)
+++ trunk/sql/Pg-database.sql	2012-06-07 02:31:00 UTC (rev 4855)
@@ -1047,9 +1047,11 @@
 
 --
 CREATE TABLE makemodel (
-  parts_id int PRIMARY KEY,
+  parts_id int,
+  barcode text,
   make text,
-  model text
+  model text,
+  primary key(parts_id, make, model)
 );
 
 COMMENT ON TABLE makemodel IS

Modified: trunk/sql/modules/Fixes.sql
===================================================================
--- trunk/sql/modules/Fixes.sql	2012-06-07 02:03:22 UTC (rev 4854)
+++ trunk/sql/modules/Fixes.sql	2012-06-07 02:31:00 UTC (rev 4855)
@@ -3,89 +3,9 @@
 --
 -- Chris Travers
 
+-- during 1.4m2
+BEGIN; 
 
-BEGIN; -- 1.3.4, fix for menu-- David Bandel
-update menu_attribute set value = 'receive_order' where value  =
-'consolidate_sales_order' and node_id = '65';
+ALTER TABLE makemodel ADD barcode TEXT;
 
-update menu_attribute set id = '149' where value  = 'receive_order'
-and node_id = '65';
-
-update menu_attribute set value = 'consolidate_sales_order' where
-value  = 'receive_order' and node_id = '64';
-
-update menu_attribute set id = '152' where value  =
-'consolidate_sales_order' and node_id = '64';
-
--- fix for bug 3430820
-update menu_attribute set value = 'pricegroup' where node_id = '83' and attribute = 'type';
-update menu_attribute set value = 'partsgroup' where node_id = '82' and attribute = 'type';
-
-UPDATE menu_attribute SET value = 'partsgroup' WHERE node_id = 91 and attribute = 'type';
-UPDATE menu_attribute SET value = 'pricegroup' WHERE node_id = 92 and attribute = 'type';
-
--- Very restrictive because some people still have Asset handling installed from
--- Addons and so the node_id and id values may not match.  Don't want to break
--- what is working! --CT
-UPDATE menu_attribute SET value = 'begin_import' WHERE id = 631 and value = 'import' and node_id = '235'; 
-
--- Getting rid of System/Backup menu since this is broken
-
-DELETE FROM menu_acl       WHERE node_id BETWEEN 133 AND 135;
-DELETE FROM menu_attribute WHERE node_id BETWEEN 133 AND 135;
-DELETE FROM menu_node      WHERE id      BETWEEN 133 AND 135;
-
--- bad batch type for receipt batches
-update menu_attribute set value = 'receipt' where node_id = 203 and attribute='batch_type';
-
 COMMIT;
-
-BEGIN;
-ALTER TABLE entity_credit_account drop constraint "entity_credit_account_language_code_fkey";
-COMMIT;
-
-BEGIN;
-ALTER TABLE entity_credit_account ADD FOREIGN KEY (language_code) REFERENCES language(code);
-COMMIT;
-
-BEGIN;
-UPDATE menu_attribute SET value = 'invoice'
-   WHERE node_id = 117 AND attribute = 'type';
-UPDATE menu_attribute SET value = 'sales_order'
-   WHERE node_id = 118 AND attribute = 'type';
-COMMIT;
-
-BEGIN;
-ALTER TABLE entity_bank_account DROP CONSTRAINT entity_bank_account_pkey;
-ALTER TABLE entity_bank_account ALTER COLUMN bic DROP NOT NULL;
-ALTER TABLE entity_bank_account ADD UNIQUE(bic,iban);
-CREATE UNIQUE INDEX eba_iban_null_bic_u ON entity_bank_account(iban) WHERE bic IS NULL;
-COMMIT;
-
-BEGIN; -- Data fixes for 1.2-1.3 upgrade.  Will fail otherwise --Chris T
-UPDATE parts 
-   SET income_accno_id = (SELECT account.id 
-                            FROM account JOIN lsmb12.chart USING (accno)
-                           WHERE chart.id = income_accno_id),
-       expense_accno_id = (SELECT account.id 
-                            FROM account JOIN lsmb12.chart USING (accno)
-                           WHERE chart.id = expense_accno_id),
-       inventory_accno_id = (SELECT account.id
-                            FROM account JOIN lsmb12.chart USING (accno)
-                           WHERE chart.id = inventory_accno_id)
- WHERE id IN (SELECT id FROM lsmb12.parts op 
-               WHERE op.id = parts.id 
-                     AND (op.income_accno_id = parts.income_accno_id
-                          OR op.inventory_accno_id = parts.inventory_accno_id 
-                          or op.expense_accno_id = parts.expense_accno_id));
-COMMIT; 
-
-BEGIN;
--- Fix menu Shipping -> Ship to actually point to the shipping interface
--- used to point to sales order consolidation
-UPDATE menu_attribute
- SET value = 'ship_order'
- WHERE attribute='type'
-       AND node_id = (SELECT id FROM menu_node WHERE label = 'Ship');
-COMMIT;
-

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.