From e22738400723dcedbca631d5789d171e078876b2 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Wed, 20 Aug 2008 22:59:39 +0000 Subject: Removing the last references to CT.pm and ct.pl, removing these files. git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2288 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/AA.pm | 2 +- LedgerSMB/CT.pm | 1084 ------------------------------------------------------- 2 files changed, 1 insertion(+), 1085 deletions(-) delete mode 100644 LedgerSMB/CT.pm (limited to 'LedgerSMB') 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; - -- cgit v1.2.3