summaryrefslogtreecommitdiff
path: root/LedgerSMB
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB')
-rw-r--r--LedgerSMB/AA.pm2
-rw-r--r--LedgerSMB/CT.pm1084
2 files changed, 1 insertions, 1085 deletions
diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm
index 82ea62a1..3a4d8965 100644
--- a/LedgerSMB/AA.pm
+++ b/LedgerSMB/AA.pm
@@ -827,7 +827,7 @@ sub transactions {
a.invoice, a.datepaid, a.terms, a.notes,
a.shipvia, a.shippingpoint, ee.name AS employee,
vce.name, vc.meta_number,
- a.entity_id, a.till, me.name AS manager, a.curr,
+ vc.entity_id, a.till, me.name AS manager, a.curr,
ex.$buysell AS exchangerate,
d.description AS department,
a.ponumber $acc_trans_flds
diff --git a/LedgerSMB/CT.pm b/LedgerSMB/CT.pm
deleted file mode 100644
index fcc0b32c..00000000
--- a/LedgerSMB/CT.pm
+++ /dev/null
@@ -1,1084 +0,0 @@
-#=====================================================================
-# LedgerSMB
-# Small Medium Business Accounting software
-# http://www.ledgersmb.org/
-#
-# Copyright (C) 2006
-# This work contains copyrighted information from a number of sources all used
-# with permission.
-#
-# This file contains source code included with or based on SQL-Ledger which
-# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
-# under the GNU General Public License version 2 or, at your option, any later
-# version. For a full list including contact information of contributors,
-# maintainers, and copyright holders, see the CONTRIBUTORS file.
-#
-# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
-# Copyright (C) 2000
-#
-# Author: DWS Systems Inc.
-# Web: http://www.sql-ledger.org
-#
-# Contributors:
-#
-#
-#======================================================================
-#
-# This file has undergone whitespace cleanup.
-#
-#======================================================================
-#
-# backend code for customers and vendors
-#
-#======================================================================
-
-package CT;
-
-sub create_links {
-
- my ( $self, $myconfig, $form ) = @_;
-
- my $dbh = $form->{dbh};
- my $query;
- my $sth;
- my $ref;
- my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
- my $ARAP = uc $arap;
-
- if ( $form->{id} ) {
- $query = qq|
- SELECT ct.*, b.description AS business, s.*,
- e.name AS employee,
- g.pricegroup AS pricegroup,
- l.description AS language, ct.curr
- FROM $form->{db} ct
- LEFT JOIN business b ON (ct.business_id = b.id)
- LEFT JOIN shipto s ON (ct.id = s.trans_id)
- LEFT JOIN employee e ON (ct.employee_id = e.id)
- LEFT JOIN pricegroup g ON (g.id = ct.pricegroup_id)
- LEFT JOIN language l ON (l.code = ct.language_code)
- WHERE ct.id = ?|;
-
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{id} ) || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
- for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
- $sth->finish;
-
- # check if it is orphaned
- $query = qq|
- SELECT a.id
- FROM $arap a
- JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
- WHERE ct.id = ?
-
- UNION
-
- SELECT a.id
- FROM oe a
- JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
- WHERE ct.id = ?|;
-
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{id}, $form->{id} )
- || $form->dberror($query);
-
- unless ( $sth->fetchrow_array ) {
- $form->{status} = "orphaned";
- }
-
- $sth->finish;
-
- # get taxes for customer/vendor
- $query = qq|
- SELECT c.accno
- FROM chart c
- JOIN $form->{db}tax t ON (t.chart_id = c.id)
- WHERE t.$form->{db}_id = ?|;
-
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{id} ) || $form->dberror($query);
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $form->{tax}{ $ref->{accno} }{taxable} = 1;
- }
-
- $sth->finish;
-
- }
- else {
-
- ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);
-
- $query = qq|SELECT current_date|;
- ( $form->{startdate} ) = $dbh->selectrow_array($query);
-
- }
-
- # get tax labels
- $query = qq|
- SELECT DISTINCT c.accno, c.description
- FROM chart c
- JOIN tax t ON (t.chart_id = c.id)
- WHERE c.link LIKE ?
- ORDER BY c.accno|;
-
- $sth = $dbh->prepare($query);
- $sth->execute("%${ARAP}_tax%") || $form->dberror($query);
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $form->{taxaccounts} .= "$ref->{accno} ";
- $form->{tax}{ $ref->{accno} }{description} = $ref->{description};
- }
-
- $sth->finish;
- chop $form->{taxaccounts};
-
-# get business types ## needs fixing, this is bad (SELECT * ...) with order by 2. Yuck
- $query = qq|
- SELECT *
- FROM business
- ORDER BY 2|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- push @{ $form->{all_business} }, $ref;
- }
-
- $sth->finish;
-
- # employees/salespersons
- $form->all_employees( $myconfig, $dbh, undef,
- ( $form->{vc} eq 'customer' )
- ? 1
- : 0 );
-
-# get language ## needs fixing, this is bad (SELECT * ...) with order by 2. Yuck
- $query = qq|
- SELECT *
- FROM language
- ORDER BY 2|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- push @{ $form->{all_language} }, $ref;
- }
-
- $sth->finish;
-
-# get pricegroups ## needs fixing, this is bad (SELECT * ...) with order by 2. Yuck
- $query = qq|
- SELECT *
- FROM pricegroup
- ORDER BY 2|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- push @{ $form->{all_pricegroup} }, $ref;
- }
-
- $sth->finish;
-
- # get currencies
- $query = qq|
- SELECT value AS currencies
- FROM defaults
- WHERE setting_key = 'curr'|;
-
- ( $form->{currencies} ) = $dbh->selectrow_array($query);
-
- $dbh->commit;
-
-}
-
-sub _save_vc {
- ($form) = @_;
- my $dbh = $form->{dbh};
- my $updated = 0;
- if ($form->{vc} eq 'customer'){
- $form->{vc} = 'customer';
- $form->{entity_class} = 2;
- } else {
- $form->{vc} = 'vendor';
- $form->{entity_class} = 1;
- }
-
- # this should really all be replaced by an upsert.
- if ( $form->{id} ) {
- # This module is depricated, so we are just going to throw an error here
- $form->error("Updating $form->{vc} not supported. ".
- "Please wait for us to move to the new codebase.");
-
- }
- if (!$updated){
- # Creating Entity
- ($form->{entity_id}) = $dbh->selectrow_array("SELECT nextval('entity_id_seq')");
- $query = qq|INSERT INTO entity (id, name, entity_class) VALUES (?, ?,?)|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{entity_id}, $form->{name}, $form->{entity_class});
- $sth->finish;
-
- # Creating LOCATION
- ($form->{location_id}) =
- $dbh->selectrow_array("SELECT nextval('location_id_seq')");
- $query = qq|
- INSERT INTO location
- (id, line_one, line_two, city_province, mail_code,
- country_id, location_class, created)
- VALUES
- (?, ?, ?, ?, ?,
- (SELECT id FROM country
- WHERE short_name = ? OR name = ?),
- 1, current_date)
- |; # location class 1 is Billing. This is a sensible default.
- $sth = $dbh->prepare($query);
- $sth->execute($form->{location_id}, $form->{address1}, $form->{address2},
- "$form->{city}, $form->{state}", $form->{zipcode},
- $form->{country}, $form->{country}
- ) || $form->dberror($query);
-
-
- #Creating company
- # Removed entity_class_id,
- # removed primary_location_id,
- # added sic_code ~Aurynn
- $query = qq|
- INSERT INTO company
- (entity_id, legal_name, tax_id, sic_code)
- VALUES
- (?, ?, ?, ?)
- |;
- $sth = $dbh->prepare($query) || $form->dberror($query);
- $sth->execute($form->{entity_id}, # $form->{entity_class}, # removed entity_class_id ~Aurynn
- $form->{name},
- # $form->{location_id}, # removed by ~aurynn
- $form->{taxnumber},
- $form->{sic_code});
- # Creating entity_metadata record, replacing customer and vendor.
- $query = qq|
- INSERT INTO entity_credit_account
- (entity_id, entity_class, discount, taxincluded, creditlimit,
- terms, meta_number, cc, bcc, business_id,
- language_code, pricegroup_id, curr, startdate,
- enddate)
-
- VALUES (?, ?, ?, ?, ?,
- ?, ?, ?, ?,
- ?, ?, ?, ?,
- ?, ?)|;
-
- $sth = $dbh->prepare($query);
- $sth->execute(
- $form->{entity_id}, $form->{entity_class}, $form->{discount},
- $form->{taxincluded}, $form->{creditlimit},
- $form->{terms}, $form->{"$form->{db}number"}, $form->{cc},
- $form->{bcc}, $form->{business_id},
- $form->{language_code}, $form->{pricegroup_id}, $form->{curr},
- $form->{startdate} || undef, $form->{enddate} || undef,
- ) || $form->dberror($query);
-
- $query = qq|
- INSERT INTO entity_bank_account (entity_id, bic, iban)
- VALUES (?,?,?)
- |;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{entity_id}, $form->{bic}, $form->{iban}) ||
- $form->dberror($query);
-
- }
-}
-
-sub save_customer {
-
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to databaseµ
- my $dbh = $form->{dbh};
- my $query;
- my $sth;
- my $null;
- $form->{vc} = 'customer';
- # remove double spaces
- $form->{name} =~ s/ / /g;
-
- # remove double minus and minus at the end
- $form->{name} =~ s/--+/-/g;
- $form->{name} =~ s/-+$//;
-
- # assign value discount, terms, creditlimit
- $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
- $form->{discount} /= 100;
- $form->{terms} *= 1;
- $form->{taxincluded} *= 1;
- $form->{creditlimit} =
- $form->parse_amount( $myconfig, $form->{creditlimit} );
- if ( !$form->{creditlimit} ) {
- $form->{creditlimit} = 0;
- }
- &_save_vc($form);
- # save taxes
- foreach $item ( split / /, $form->{taxaccounts} ) {
-
- if ( $form->{"tax_$item"} ) {
- $query = qq|
- INSERT INTO customertax (customer_id, chart_id)
- VALUES (currval('entity_credit_account_id_seq'),
- (SELECT id
- FROM chart
- WHERE accno = ?))|;
-
- $sth = $dbh->prepare($query);
- $sth->execute( $item )
- || $form->dberror($query);
- }
- }
-
- # add shipto
- $form->add_shipto( $dbh, $form->{id} );
-
- $dbh->commit;
-}
-
-sub save_vendor {
-
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
-
- my $query;
- my $sth;
- my $null;
-
- # remove double spaces
- $form->{name} =~ s/ / /g;
-
- # remove double minus and minus at the end
- $form->{name} =~ s/--+/-/g;
- $form->{name} =~ s/-+$//;
-
- $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
- $form->{discount} /= 100;
- $form->{terms} *= 1;
- $form->{taxincluded} *= 1;
- $form->{creditlimit} =
- $form->parse_amount( $myconfig, $form->{creditlimit} );
- &_save_vc($form);
-
- # save taxes
- foreach $item ( split / /, $form->{taxaccounts} ) {
- if ( $form->{"tax_$item"} ) {
- $query = qq|
- INSERT INTO vendortax (vendor_id, chart_id)
- VALUES (?, (SELECT id
- FROM chart
- WHERE accno = ?))|;
-
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{id}, $item )
- || $form->dberror($query);
- }
- }
-
- # add shipto
- $form->add_shipto( $dbh, $form->{id} );
-
- $dbh->commit;
-
-}
-
-sub delete {
-
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
-
- # delete customer/vendor
- my $query = qq|DELETE FROM $form->{db}
- WHERE id = ?|;
-
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{id} ) || $form->dberror($query);
-
- $dbh->commit;
-
-}
-
-sub search {
-
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
-
- my $where = "1 = 1";
- $form->{sort} = ( $form->{sort} ) ? $form->{sort} : "name";
- my @a = qw(name);
- my $sortorder = $form->sort_order( \@a );
-
- my $var;
- my $item;
-
- @a = ("$form->{db}number");
- push @a, qw(name contact city state zipcode country notes phone email);
-
- if ( $form->{employee} ) {
- $var = $dbh->quote($form->like(lc $form->{employee}));
- $where .= " AND lower(e.name) LIKE $var";
- }
-
- foreach $item (@a) {
-
- if ( $form->{$item} ne "" ) {
- $var = $dbh->quote($form->like( lc $form->{$item}) );
- $where .= " AND lower(ct.$item) LIKE $var";
- }
- }
-
- if ( $form->{address} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{address} ) );
- $where .=
-" AND (lower(ct.address1) ILIKE $var)";
- }
-
- if ( $form->{startdatefrom} ) {
- $where .=
- " AND ct.startdate >= " . $dbh->quote( $form->{startdatefrom} );
- }
-
- if ( $form->{startdateto} ) {
- $where .= " AND ct.startdate <= " . $dbh->quote( $form->{startdateto} );
- }
-
- if ( $form->{status} eq 'active' ) {
- $where .= " AND ct.enddate IS NULL";
- }
-
- if ( $form->{status} eq 'inactive' ) {
- $where .= " AND ct.enddate <= current_date";
- }
-
- if ( $form->{status} eq 'orphaned' ) {
- $where .= qq|
- AND ct.id NOT IN (SELECT o.$form->{db}_id
- FROM oe o, $form->{db} vc
- WHERE vc.id = o.$form->{db}_id)|;
-
- if ( $form->{db} =~ /(^customer$|^vendor$)/ ) {
- $where .= qq| AND ct.id NOT IN (SELECT a.entity_id
- FROM ar a, customer vc
- WHERE vc.entity_id = a.entity_id)|;
- }
-
-
- $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
- }
-
- my $query = qq|
- SELECT ct.*, b.description AS business,
- e.name AS employee, g.pricegroup,
- l.description AS language, m.name AS manager
- FROM $form->{db} ct
- LEFT JOIN business b ON (ct.business_id = b.id)
- LEFT JOIN employee e ON (ct.employee_id = e.id)
- LEFT JOIN employee m ON (m.id = e.managerid)
- LEFT JOIN pricegroup g ON (ct.pricegroup_id = g.id)
- LEFT JOIN language l ON (l.code = ct.language_code)
- WHERE $where|;
-
- # redo for invoices, orders and quotations
- if ( $form->{l_transnumber}
- || $form->{l_invnumber}
- || $form->{l_ordnumber}
- || $form->{l_quonumber} )
- {
-
- my ( $ar, $union, $module );
- $query = "";
- my $transwhere;
- my $openarap = "";
- my $openoe = "";
-
- if ( $form->{open} || $form->{closed} ) {
- unless ( $form->{open} && $form->{closed} ) {
- $openarap = " AND a.amount != a.paid"
- if $form->{open};
- $openarap = " AND a.amount = a.paid"
- if $form->{closed};
- $openoe = " AND o.closed = '0'"
- if $form->{open};
- $openoe = " AND o.closed = '1'"
- if $form->{closed};
- }
- }
-
- if ( $form->{l_transnumber} ) {
-
- $ar = ( $form->{db} eq 'customer' ) ? 'ar' : 'ap';
- $module = $ar;
-
- $transwhere = "";
- $transwhere .=
- " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
- if $form->{transdatefrom};
- $transwhere .=
- " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
- if $form->{transdateto};
-
- $query = qq|
- SELECT ct.*, b.description AS business,
- a.invnumber, a.ordnumber,
- a.quonumber,
- a.id AS invid, '$ar' AS module,
- 'invoice' AS formtype,
- (a.amount = a.paid) AS closed,
- a.amount,
- a.netamount, e.name AS employee,
- m.name AS manager
- FROM $form->{db} ct
- JOIN $ar a ON (a.$form->{db}_id = ct.id)
- LEFT JOIN business b ON (ct.business_id = b.id)
- LEFT JOIN employee e ON (a.employee_id = e.id)
- LEFT JOIN employee m ON (m.id = e.managerid)
- WHERE $where
- AND a.invoice = '0'
- $transwhere
- $openarap |;
-
- $union = qq| UNION |;
-
- }
-
- if ( $form->{l_invnumber} ) {
- $ar = ( $form->{db} eq 'customer' ) ? 'ar' : 'ap';
- $module = ( $ar eq 'ar' ) ? 'is' : 'ir';
-
- $transwhere = "";
- $transwhere .=
- " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
- if $form->{transdatefrom};
- $transwhere .=
- " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
- if $form->{transdateto};
-
- $query .= qq|
- $union
- SELECT ct.*, b.description AS business,
- a.invnumber, a.ordnumber, a.quonumber,
- a.id AS invid,
- '$module' AS module,
- 'invoice' AS formtype,
- (a.amount = a.paid) AS closed,
- a.amount, a.netamount,
- e.name AS employee, m.name AS manager
- FROM $form->{db} ct
- JOIN $ar a ON (a.$form->{db}_id = ct.id)
- LEFT JOIN business b ON (ct.business_id = b.id)
- LEFT JOIN employee e ON (a.employee_id = e.id)
- LEFT JOIN employee m ON (m.id = e.managerid)
- WHERE $where
- AND a.invoice = '1'
- $transwhere
- $openarap |;
-
- $union = qq| UNION|;
-
- }
-
- if ( $form->{l_ordnumber} ) {
-
- $transwhere = "";
- $transwhere .=
- " AND o.transdate >= " . $dbh->quote( $form->{transdatefrom} )
- if $form->{transdatefrom};
- $transwhere .=
- " AND o.transdate <= " . $dbh->quote( $form->{transdateto} )
- if $form->{transdateto};
-
- $query .= qq|
- $union
- SELECT ct.*, b.description AS business,
- ' ' AS invnumber, o.ordnumber,
- o.quonumber, o.id AS invid,
- 'oe' AS module, 'order' AS formtype,
- o.closed, o.amount, o.netamount,
- e.name AS employee, m.name AS manager
- FROM $form->{db} ct
- JOIN oe o ON (o.$form->{db}_id = ct.id)
- LEFT JOIN business b ON (ct.business_id = b.id)
- LEFT JOIN employee e ON (o.employee_id = e.id)
- LEFT JOIN employee m ON (m.id = e.managerid)
- WHERE $where
- AND o.quotation = '0'
- $transwhere
- $openoe |;
-
- $union = qq| UNION|;
-
- }
-
- if ( $form->{l_quonumber} ) {
-
- $transwhere = "";
- $transwhere .=
- " AND o.transdate >= " . $dbh->quote( $form->{transdatefrom} )
- if $form->{transdatefrom};
- $transwhere .=
- " AND o.transdate <= " . $dbh->quote( $form->{transdateto} )
- if $form->{transdateto};
-
- $query .= qq|
- $union
- SELECT ct.*, b.description AS business,
- ' ' AS invnumber, o.ordnumber,
- o.quonumber, o.id AS invid,
- 'oe' AS module,
- 'quotation' AS formtype,
- o.closed, o.amount, o.netamount,
- e.name AS employee, m.name AS manager
- FROM $form->{db} ct
- JOIN oe o ON (o.$form->{db}_id = ct.id)
- LEFT JOIN business b ON (ct.business_id = b.id)
- LEFT JOIN employee e ON (o.employee_id = e.id)
- LEFT JOIN employee m ON (m.id = e.managerid)
- WHERE $where
- AND o.quotation = '1'
- $transwhere
- $openoe |;
-
- }
-
- $sortorder .= ", invid";
- }
-
- $query .= qq| ORDER BY $sortorder|;
-
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- # accounts
- $query = qq|
- SELECT c.accno
- FROM chart c
- JOIN $form->{db}tax t ON (t.chart_id = c.id)
- WHERE t.$form->{db}_id = ?|;
-
- my $tth = $dbh->prepare($query);
-
- while ( my $ref = $sth->fetchrow_hashref('NAME_lc') ) {
- $form->db_parse_numeric(sth => $sth, hashref => $ref);
- $tth->execute( $ref->{id} );
-
- while ( ($item) = $tth->fetchrow_array ) {
- $ref->{taxaccount} .= "$item ";
- }
-
- $tth->finish;
- chop $ref->{taxaccount};
-
- $ref->{address} = "";
-
- for (qw(address1 address2 city state zipcode country)) {
- $ref->{address} .= "$ref->{$_} ";
- }
- push @{ $form->{CT} }, $ref;
- }
-
- $sth->finish;
- $dbh->commit;
-
-}
-
-sub get_history {
-
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
-
- my $query;
- my $where = "1 = 1";
- $form->{sort} = "partnumber" unless $form->{sort};
- my $sortorder = $form->{sort};
- my %ordinal = ();
- my $var;
- my $table;
-
- # setup ASC or DESC
- $form->sort_order();
-
- if ( $form->{"$form->{db}number"} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{"$form->{db}number"} ) );
- $where .= " AND lower(ct.$form->{db}number) LIKE $var";
- }
-
- if ( $form->{address} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{address} ) );
- $where .= " AND lower(ct.address1) ILIKE $var";
- }
-
- for (qw(name contact email phone notes city state zipcode country)) {
-
- if ( $form->{$_} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{$_} ) );
- $where .= " AND lower(ct.$_) LIKE $var";
- }
- }
-
- if ( $form->{employee} ne "" ) {
- $var = $dbh->quote($form->like(lc $form->{employee}));
- $where .= " AND lower(e.name) LIKE $var";
- }
-
- $transwhere .=
- " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
- if $form->{transdatefrom};
- $transwhere .= " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
- if $form->{transdateto};
-
- if ( $form->{open} || $form->{closed} ) {
-
- unless ( $form->{open} && $form->{closed} ) {
-
- if ( $form->{type} eq 'invoice' ) {
- $where .= " AND a.amount != a.paid"
- if $form->{open};
- $where .= " AND a.amount = a.paid"
- if $form->{closed};
- }
- else {
- $where .= " AND a.closed = '0'"
- if $form->{open};
- $where .= " AND a.closed = '1'"
- if $form->{closed};
- }
- }
- }
-
- my $invnumber = 'invnumber';
- my $deldate = 'deliverydate';
- my $buysell;
- my $sellprice = "sellprice";
-
- if ( $form->{db} eq 'customer' ) {
- $buysell = "buy";
-
- if ( $form->{type} eq 'invoice' ) {
- $where .= qq|
- AND a.invoice = '1' AND i.assemblyitem = '0'|;
- $table = 'ar';
- $sellprice = "fxsellprice";
- }
- else {
- $table = 'oe';
-
- if ( $form->{type} eq 'order' ) {
- $invnumber = 'ordnumber';
- $where .= qq| AND a.quotation = '0'|;
- }
- else {
- $invnumber = 'quonumber';
- $where .= qq| AND a.quotation = '1'|;
- }
-
- $deldate = 'reqdate';
- }
- }
-
- if ( $form->{db} eq 'vendor' ) {
-
- $buysell = "sell";
-
- if ( $form->{type} eq 'invoice' ) {
-
- $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
- $table = 'ap';
- $sellprice = "fxsellprice";
-
- }
- else {
-
- $table = 'oe';
-
- if ( $form->{type} eq 'order' ) {
- $invnumber = 'ordnumber';
- $where .= qq| AND a.quotation = '0'|;
- }
- else {
- $invnumber = 'quonumber';
- $where .= qq| AND a.quotation = '1'|;
- }
-
- $deldate = 'reqdate';
- }
- }
-
- my $invjoin = qq| JOIN invoice i ON (i.trans_id = a.id)|;
-
- if ( $form->{type} eq 'order' ) {
- $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
- }
-
- if ( $form->{type} eq 'quotation' ) {
- $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
- $where .= qq| AND a.quotation = '1'|;
- }
-
- %ordinal = (
- partnumber => 9,
- description => 12,
- "$deldate" => 16,
- serialnumber => 17,
- projectnumber => 18
- );
-
- $sortorder =
- "2 $form->{direction}, 1, 11, $ordinal{$sortorder} $form->{direction}";
-
- $query = qq|
- SELECT ct.id AS ctid, ct.name, ct.address1,
- ct.address2, ct.city, ct.state,
- p.id AS pid, p.partnumber, a.id AS invid,
- a.$invnumber, a.curr, i.description,
- i.qty, i.$sellprice AS sellprice, i.discount,
- i.$deldate, i.serialnumber, pr.projectnumber,
- e.name AS employee, ct.zipcode, ct.country, i.unit,
- (SELECT $buysell
- FROM exchangerate ex
- WHERE a.curr = ex.curr
- AND a.transdate = ex.transdate) AS exchangerate
- FROM $form->{db} ct
- JOIN $table a ON (a.$form->{db}_id = ct.id)
- $invjoin
- JOIN parts p ON (p.id = i.parts_id)
- LEFT JOIN project pr ON (pr.id = i.project_id)
- LEFT JOIN employee e ON (e.id = a.employee_id)
- WHERE $where
- ORDER BY $sortorder|;
-
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $ref->{address} = "";
- $ref->{exchangerate} ||= 1;
- for (qw(address1 address2 city state zipcode country)) {
- $ref->{address} .= "$ref->{$_} ";
- }
- $ref->{id} = $ref->{ctid};
- push @{ $form->{CT} }, $ref;
- }
-
- $sth->finish;
- $dbh->commit;
-
-}
-
-sub pricelist {
-
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
-
- my $query;
-
- if ( $form->{db} eq 'customer' ) {
- $query = qq|SELECT p.id, p.partnumber, p.description,
- p.sellprice, pg.partsgroup, p.partsgroup_id,
- m.pricebreak, m.sellprice,
- m.validfrom, m.validto, m.curr
- FROM partscustomer m
- JOIN parts p ON (p.id = m.parts_id)
- LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
- WHERE m.customer_id = ?
- ORDER BY partnumber|;
- }
-
- if ( $form->{db} eq 'vendor' ) {
- $query = qq|SELECT p.id, p.partnumber AS sku, p.description,
- pg.partsgroup, p.partsgroup_id,
- m.partnumber, m.leadtime, m.lastcost, m.curr
- FROM partsvendor m
- JOIN parts p ON (p.id = m.parts_id)
- LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
- WHERE m.vendor_id = ?
- ORDER BY p.partnumber|;
- }
-
- my $sth;
- my $ref;
-
- if ( $form->{id} ) {
-
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{id} ) || $form->dberror($query);
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- push @{ $form->{all_partspricelist} }, $ref;
- }
-
- $sth->finish;
- }
-
- $query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
- ( $form->{currencies} ) = $dbh->selectrow_array($query);
-
- $query = qq|SELECT id, partsgroup
- FROM partsgroup
- ORDER BY partsgroup|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $self->dberror($query);
-
- $form->{all_partsgroup} = ();
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- push @{ $form->{all_partsgroup} }, $ref;
- }
-
- $sth->finish;
-
- $dbh->commit;
-
-}
-
-sub save_pricelist {
-
- my ( $self, $myconfig, $form ) = @_;
-
- my $dbh = $form->{dbh};
-
- my $query = qq|
- DELETE FROM parts$form->{db}
- WHERE $form->{db}_id = ?|;
-
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{id} ) || $form->dberror($query);
-
- foreach $i ( 1 .. $form->{rowcount} ) {
-
- if ( $form->{"id_$i"} ) {
-
- if ( $form->{db} eq 'customer' ) {
-
- for (qw(pricebreak sellprice)) {
- $form->{"${_}_$i"} =
- $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
- }
-
- $query = qq|
- INSERT INTO parts$form->{db}
- (parts_id, customer_id,
- pricebreak, sellprice,
- validfrom, validto, curr)
- VALUES (?, ?, ?, ?, ?, ?, ?)|;
- @queryargs = (
- $form->{"id_$i"}, $form->{id},
- $form->{"pricebreak_$i"}, $form->{"sellprice_$i"},
- $form->{"validfrom_$i"}, $form->{"validto_$i"},
- $form->{"curr_$i"}
- );
- }
- else {
-
- for (qw(leadtime lastcost)) {
- $form->{"${_}_$i"} =
- $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
- }
-
- $query = qq|
- INSERT INTO parts$form->{db}
- (parts_id, vendor_id,
- partnumber, lastcost,
- leadtime, curr)
- VALUES (?, ?, ?, ?, ?, ?)|;
- @queryargs = (
- $form->{"id_$i"}, $form->{id},
- $form->{"partnumber_$i"}, $form->{"lastcost_$i"},
- $form->{"leadtime_$i"}, $form->{"curr_$i"}
- );
-
- }
- $sth = $dbh->prepare($query);
- $sth->execute(@queryargs) || $form->dberror($query);
- }
-
- }
-
- $_ = $dbh->commit;
-
-}
-
-sub retrieve_item {
-
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
-
- my $i = $form->{rowcount};
- my $var;
- my $null;
-
- my $where = "WHERE p.obsolete = '0'";
-
- if ( $form->{db} eq 'vendor' ) {
-
- # parts, services, labor
- $where .= " AND p.assembly = '0'";
- }
-
- if ( $form->{db} eq 'customer' ) {
-
- # parts, assemblies, services
- $where .= " AND p.income_accno_id > 0";
- }
-
- if ( $form->{"partnumber_$i"} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{"partnumber_$i"} ) );
- $where .= " AND lower(p.partnumber) LIKE $var";
- }
-
- if ( $form->{"description_$i"} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{"description_$i"} ) );
- $where .= " AND lower(p.description) LIKE $var";
- }
-
- if ( $form->{"partsgroup_$i"} ne "" ) {
- ( $null, $var ) = split /--/, $form->{"partsgroup_$i"};
- $var = $dbh->quote($var);
- $where .= qq| AND p.partsgroup_id = $var|;
- }
-
- my $query = qq|
- SELECT p.id, p.partnumber, p.description, p.sellprice,
- p.lastcost, p.unit, pg.partsgroup, p.partsgroup_id
- FROM parts p
- LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
- $where
- ORDER BY partnumber|;
-
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- my $ref;
- $form->{item_list} = ();
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- push @{ $form->{item_list} }, $ref;
- }
-
- $sth->finish;
- $dbh->commit;
-}
-
-1;
-
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-22 08:35:48 +0000