# -*-shell-script-*- # This should be sourced by bash (though we welcome changes to make it POSIX sh compliant) # Monkeysphere authentication diagnostics subcommand # # The monkeysphere scripts are written by: # Jameson Rollins # Jamie McClelland # Daniel Kahn Gillmor # # They are Copyright 2008-2009, and are all released under the GPL, # version 3 or later. # check on the status and validity of the key and public certificates diagnostics() { local seckey local keysfound local curdate local warnwindow local warndate local create local expire local uid local fingerprint local badhostkeys local sshd_config local problemsfound=0 report_cruft if ! id monkeysphere >/dev/null ; then echo "! No monkeysphere user found! Please create a monkeysphere system user with bash as its shell." problemsfound=$(($problemsfound+1)) fi if ! [ -d "$SYSDATADIR" ] ; then echo "! no $SYSDATADIR directory found. Please create it." problemsfound=$(($problemsfound+1)) fi echo "Checking for authentication directory..." if ! [ -d "$MADATADIR" ] ; then echo "! No authentication data directory found." echo " - Recommendation: run 'monkeysphere-authentication setup'" exit fi # FIXME: what's the correct, cross-platform way to determine where # sshd_config lives? sshd_config=/etc/ssh/sshd_config seckey=$(gpg_core --list-secret-keys --fingerprint --with-colons --fixed-list-mode) keysfound=$(echo "$seckey" | grep -c ^sec:) curdate=$(date +%s) # warn when anything is 2 months away from expiration warnwindow='2 months' warndate=$(advance_date $warnwindow +%s) echo "Checking core GPG key..." if (( "$keysfound" < 1 )); then echo "! No core key found." echo " - Recommendation: run 'monkeysphere-authentication setup'" problemsfound=$(($problemsfound+1)) elif (( "$keysfound" > 1 )); then echo "! More than one core key found?" # FIXME: recommend a way to resolve this problemsfound=$(($problemsfound+1)) else create=$(echo "$seckey" | grep ^sec: | cut -f6 -d:) expire=$(echo "$seckey" | grep ^sec: | cut -f7 -d:) fingerprint=$(echo "$seckey" | grep ^fpr: | head -n1 | cut -f10 -d:) # check for key expiration: if [ "$expire" ]; then if (( "$expire" < "$curdate" )); then echo "! Core key is expired." echo " - Recommendation: ???" problemsfound=$(($problemsfound+1)) elif (( "$expire" < "$warndate" )); then echo "! Core key expires in less than $warnwindow:" $(advance_date $(( $expire - $curdate )) seconds +%F) echo " - Recommendation: ???" problemsfound=$(($problemsfound+1)) fi fi # and weirdnesses: if [ "$create" ] && (( "$create" > "$curdate" )); then echo "! Core key was created in the future(?!). Is your clock correct?" echo " - Recommendation: Check clock ($(date +%F_%T)); use NTP?" problemsfound=$(($problemsfound+1)) fi fi # FIXME: look at the ownership/privileges of the various keyrings, # directories housing them, etc (what should those values be? can # we make them as minimal as possible?) # FIXME: look to see that the ownertrust rules are set properly on the # sphere keyring # make sure that at least one identity certifier exists echo echo "Checking for Identity Certifiers..." if ! ( monkeysphere-authentication list-identity-certifiers | egrep '^[A-F0-9]{40}:' >/dev/null ) ; then echo "! No Identity Certifiers found!" echo " - Recommendation: once you know who should be able to certify the identities of connecting users, you should add their key, with: monkeysphere-authentication add-identity-certifier" problemsfound=$(($problemsfound+1)) fi # FIXME: look at the timestamps on the monkeysphere-generated # authorized_keys files -- warn if they seem out-of-date. # FIXME: check for a cronjob that updates monkeysphere-generated # authorized_keys? echo echo "Checking for Monkeysphere-enabled public-key authentication for users ..." # Ensure that User ID authentication is enabled: if ! grep -q "^AuthorizedKeysFile[[:space:]]\+${SYSDATADIR}/authorized_keys/%u$" "$sshd_config"; then echo "! $sshd_config does not point to monkeysphere authorized keys." echo " - Recommendation: add a line to $sshd_config: 'AuthorizedKeysFile ${SYSDATADIR}/authorized_keys/%u'" problemsfound=$(($problemsfound+1)) fi if badauthorizedkeys=$(grep -i '^AuthorizedKeysFile' "$sshd_config" | grep -v "^AuthorizedKeysFile[[:space:]]\+${SYSDATADIR}/authorized_keys/%u$") ; then echo "! $sshd_config refers to non-monkeysphere authorized_keys files:" echo "$badauthorizedkeys" echo " - Recommendation: remove the above AuthorizedKeysFile lines from $sshd_config" problemsfound=$(($problemsfound+1)) fi if [ "$problemsfound" -gt 0 ]; then echo "When the above $problemsfound issue"$(if [ "$problemsfound" -eq 1 ] ; then echo " is" ; else echo "s are" ; fi)" resolved, please re-run:" echo " monkeysphere-authentication diagnostics" else echo "Everything seems to be in order!" fi } hor: 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_customer {
  • 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/-+$//;
  • # 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;
  • }
  • if ($form->{id}) {
  • $query = qq|
  • DELETE FROM customertax
  • WHERE customer_id = ?|;
  • $sth = $dbh->prepare($query);
  • $sth->execute($form->{id}) || $form->dberror($query);
  • $query = qq|
  • DELETE FROM shipto
  • WHERE trans_id = ?|;
  • $sth = $dbh->prepare($query);
  • $sth->execute($form->{id}) || $form->dberror($query);
  • $query = qq|
  • SELECT id
  • FROM customer
  • WHERE id = ?|;
  • $sth = $dbh->prepare($query);
  • $sth->execute($form->{id}) || $form->dberror($query);
  • if (! $sth->fetchrow_array) {
  • $query = qq|
  • INSERT INTO customer (id)
  • VALUES (?)|;
  • $sth = $dbh->prepare($query);
  • $sth->execute($form->{id}) || $form->dberror($query);
  • }
  • # retrieve enddate
  • if ($form->{type} && $form->{enddate}) {
  • my $now;
  • $query = qq|
  • SELECT enddate, current_date AS now
  • FROM customer|;
  • ($form->{enddate}, $now) =
  • $dbh->selectrow_array($query);
  • $form->{enddate} = $now if $form->{enddate} lt $now;
  • }
  • } else {
  • my $uid = localtime;
  • $uid .= "$$";
  • $query = qq|INSERT INTO customer (name)
  • VALUES ('$uid')|;
  • $dbh->do($query) || $form->dberror($query);
  • $query = qq|SELECT id
  • FROM customer
  • WHERE name = '$uid'|;
  • ($form->{id}) = $dbh->selectrow_array($query);
  • }
  • my $employee_id;
  • ($null, $employee_id) = split /--/, $form->{employee};
  • $employee_id *= 1;
  • my $pricegroup_id;
  • ($null, $pricegroup_id) = split /--/, $form->{pricegroup};
  • $pricegroup_id *= 1;
  • my $business_id;
  • ($null, $business_id) = split /--/, $form->{business};
  • $business_id *= 1;
  • my $language_code;
  • ($null, $language_code) = split /--/, $form->{language};
  • $form->{customernumber} = $form->update_defaults($myconfig, "customernumber", $dbh) if ! $form->{customernumber};
  • $query = qq|
  • UPDATE customer
  • SET customernumber = ?,
  • name = ?,
  • address1 = ?,
  • address2 = ?,
  • city = ?,
  • state = ?,
  • zipcode = ?,
  • country = ?,
  • contact = ?,
  • phone = ?,
  • fax = ?,
  • email = ?,
  • cc = ?,
  • bcc = ?,
  • notes = ?,
  • discount = ?,
  • creditlimit = ?,
  • terms = ?,
  • taxincluded = ?,
  • business_id = ?,
  • taxnumber = ?,
  • sic_code = ?,
  • iban = ?,
  • bic = ?,
  • employee_id = ?,
  • pricegroup_id = ?,
  • language_code = ?,
  • curr = ?,
  • startdate = ?,
  • enddate = ?
  • WHERE id = ?|;
  • $sth = $dbh->prepare($query);
  • if (!$form->{startdate}){
  • undef $form->{startdate};
  • }
  • if (!$form->{enddate}){
  • undef $form->{enddate};
  • }
  • $sth->execute(
  • $form->{customernumber}, $form->{name}, $form->{address1},
  • $form->{address2}, $form->{city}, $form->{state},
  • $form->{zipcode}, $form->{country}, $form->{contact},
  • $form->{phone}, $form->{fax}, $form->{email}, $form->{cc},
  • $form->{bcc}, $form->{notes}, $form->{discount},
  • $form->{creditlimit}, $form->{terms}, $form->{taxincluded},
  • $business_id, $form->{taxnumber}, $form->{sic_code},
  • $form->{iban}, $form->{bic}, $employee_id, $pricegroup_id,
  • $language_code,
  • $form->{curr}, $form->{startdate}, $form->{enddate},
  • $form->{id})
  • || $form->dberror($query);
  • # save taxes
  • foreach $item (split / /, $form->{taxaccounts}) {
  • if ($form->{"tax_$item"}) {
  • $query = qq|
  • INSERT INTO customertax (customer_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 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});
  • if ($form->{id}) {
  • $query = qq|DELETE FROM vendortax
  • WHERE vendor_id = ?|;
  • $sth = $dbh->prepare($query);
  • $sth->execute($form->{id}) || $form->dberror($query);
  • $query = qq|DELETE FROM shipto
  • WHERE trans_id = ?|;
  • $sth = $dbh->prepare($query);
  • $sth->execute($form->{id}) || $form->dberror($query);
  • $query = qq|SELECT id
  • FROM vendor
  • WHERE id = ?|;
  • $sth = $dbh->prepare($query);
  • $sth->execute($form->{id}) || $form->dberror($query);
  • if (! $sth->fetchrow_array) {
  • $query = qq|INSERT INTO vendor (id)
  • VALUES (?)|;
  • $sth = $dbh->prepare($query) ;
  • $sth->execute($form->{id}) || $form->dberror($query);
  • }
  • # retrieve enddate
  • if ($form->{type} && $form->{enddate}) {
  • my $now;
  • $query = qq|SELECT enddate, current_date AS now FROM vendor|;
  • ($form->{enddate}, $now) = $dbh->selectrow_array($query);
  • $form->{enddate} = $now if $form->{enddate} lt $now;
  • }
  • } else {
  • my $uid = localtime;
  • $uid .= "$$";
  • $query = qq|INSERT INTO vendor (name)
  • VALUES ('$uid')|;
  • $dbh->do($query) || $form->dberror($query);
  • $query = qq|SELECT id
  • FROM vendor
  • WHERE name = '$uid'|;
  • ($form->{id}) = $dbh->selectrow_array($query);
  • }
  • my $employee_id;
  • ($null, $employee_id) = split /--/, $form->{employee};
  • $employee_id *= 1;
  • my $pricegroup_id;
  • ($null, $pricegroup_id) = split /--/, $form->{pricegroup};
  • $pricegroup_id *= 1;
  • my $business_id;
  • ($null, $business_id) = split /--/, $form->{business};
  • $business_id *= 1;
  • my $language_code;
  • ($null, $language_code) = split /--/, $form->{language};
  • $form->{vendornumber} = $form->update_defaults($myconfig, "vendornumber", $dbh) if ! $form->{vendornumber};
  • $form->{startdate} = undef unless $form->{startdate};
  • $form->{enddate} = undef unless $form->{enddate};
  • $query = qq|
  • UPDATE vendor
  • SET vendornumber = ?,
  • name = ?,
  • address1 = ?,
  • address2 = ?,
  • city = ?,
  • state = ?,
  • zipcode = ?,
  • country = ?,
  • contact = ?,
  • phone = ?,
  • fax = ?,
  • email = ?,
  • cc = ?,
  • bcc = ?,
  • notes = ?,
  • discount = ?,
  • creditlimit = ?,
  • terms = ?,
  • taxincluded = ?,
  • gifi_accno = ?,
  • business_id = ?,
  • taxnumber = ?,
  • sic_code = ?,
  • iban = ?,
  • bic = ?,
  • employee_id = ?,
  • language_code = ?,
  • pricegroup_id = ?,
  • curr = ?,
  • startdate = ?,
  • enddate = ?
  • WHERE id = ?|;
  • $sth = $dbh->prepare($query);
  • $sth->execute(
  • $form->{vendornumber}, $form->{name}, $form->{address1},
  • $form->{address2}, $form->{city}, $form->{state},
  • $form->{zipcode}, $form->{country}, $form->{contact},
  • $form->{phone}, $form->{fax}, $form->{email}, $form->{cc},
  • $form->{bcc}, $form->{notes}, $form->{discount},
  • $form->{creditlimit}, $form->{terms}, $form->{taxincluded},
  • $form->{gifi_accno}, $business_id, $form->{taxnumber},
  • $form->{sic_code}, $form->{iban}, $form->{bic}, $employee_id,
  • $language_code, $pricegroup_id,
  • $form->{curr}, $form->{startdate}, $form->{enddate},
  • $form->{id})
  • || $form->dberror($query);
  • # 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 = $form->like(lc $form->{employee});
  • $where .= " AND lower(e.name) LIKE '$var'";
  • }
  • foreach $item (@a) {
  • if ($form->{$item} ne "") {
  • $var = $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) LIKE $var OR lower(ct.address2) LIKE '$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} eq 'customer') {
  • $where .= qq| AND ct.id NOT IN (SELECT a.customer_id
  • FROM ar a, customer vc
  • WHERE vc.id = a.customer_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} = "";
  • }
  • 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)) {
  • $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->($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) LIKE $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 = $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') {