From ac5b087ea2d9ba7428d367aaeb288534158fee9a Mon Sep 17 00:00:00 2001 From: christopherm Date: Fri, 1 Sep 2006 01:16:38 +0000 Subject: Initial Import git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/ledger-smb@1 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/PE.pm | 1499 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 1499 insertions(+) create mode 100755 LedgerSMB/PE.pm (limited to 'LedgerSMB/PE.pm') diff --git a/LedgerSMB/PE.pm b/LedgerSMB/PE.pm new file mode 100755 index 00000000..d85f4cc3 --- /dev/null +++ b/LedgerSMB/PE.pm @@ -0,0 +1,1499 @@ +#===================================================================== +# LedgerSMB +# Small Medium Business Accounting software +# +# See COPYRIGHT file for copyright information +#====================================================================== +# +# This file has NOT undergone whitespace cleanup. +# +#====================================================================== +# +# Project module +# also used for partsgroups +# +#====================================================================== + +package PE; + + +sub projects { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{sort} = "projectnumber" unless $form->{sort}; + my @a = ($form->{sort}); + my %ordinal = ( projectnumber => 2, + description => 3, + startdate => 4, + enddate => 5, + ); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $query; + my $where = "WHERE 1=1"; + + $query = qq|SELECT pr.*, c.name + FROM project pr + LEFT JOIN customer c ON (c.id = pr.customer_id)|; + + if ($form->{type} eq 'job') { + $where .= qq| AND pr.id NOT IN (SELECT DISTINCT id + FROM parts + WHERE project_id > 0)|; + } + + my $var; + if ($form->{projectnumber} ne "") { + $var = $form->like(lc $form->{projectnumber}); + $where .= " AND lower(pr.projectnumber) LIKE '$var'"; + } + if ($form->{description} ne "") { + $var = $form->like(lc $form->{description}); + $where .= " AND lower(pr.description) LIKE '$var'"; + } + + ($form->{startdatefrom}, $form->{startdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; + + if ($form->{startdatefrom}) { + $where .= " AND (pr.startdate IS NULL OR pr.startdate >= '$form->{startdatefrom}')"; + } + if ($form->{startdateto}) { + $where .= " AND (pr.startdate IS NULL OR pr.startdate <= '$form->{startdateto}')"; + } + + if ($form->{status} eq 'orphaned') { + $where .= qq| AND pr.id NOT IN (SELECT DISTINCT project_id + FROM acc_trans + WHERE project_id > 0 + UNION + SELECT DISTINCT project_id + FROM invoice + WHERE project_id > 0 + UNION + SELECT DISTINCT project_id + FROM orderitems + WHERE project_id > 0 + UNION + SELECT DISTINCT project_id + FROM jcitems + WHERE project_id > 0) + |; + + } + if ($form->{status} eq 'active') { + $where .= qq| AND (pr.enddate IS NULL OR pr.enddate >= current_date)|; + } + if ($form->{status} eq 'inactive') { + $where .= qq| AND pr.enddate <= current_date|; + } + + $query .= qq| + $where + ORDER BY $sortorder|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $i = 0; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_project} }, $ref; + $i++; + } + + $sth->finish; + $dbh->disconnect; + + $i; + +} + + +sub get_project { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query; + my $sth; + my $ref; + my $where; + + if ($form->{id}) { + + $where = "WHERE pr.id = $form->{id}" if $form->{id}; + + $query = qq|SELECT pr.*, + c.name AS customer + FROM project pr + LEFT JOIN customer c ON (c.id = pr.customer_id) + $where|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + $ref = $sth->fetchrow_hashref(NAME_lc); + + for (keys %$ref) { $form->{$_} = $ref->{$_} } + + $sth->finish; + + # check if it is orphaned + $query = qq|SELECT count(*) + FROM acc_trans + WHERE project_id = $form->{id} + UNION + SELECT count(*) + FROM invoice + WHERE project_id = $form->{id} + UNION + SELECT count(*) + FROM orderitems + WHERE project_id = $form->{id} + UNION + SELECT count(*) + FROM jcitems + WHERE project_id = $form->{id} + |; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $count; + while (($count) = $sth->fetchrow_array) { + $form->{orphaned} += $count; + } + $sth->finish; + $form->{orphaned} = !$form->{orphaned}; + } + + PE->get_customer($myconfig, $form, $dbh); + + $dbh->disconnect; + +} + + +sub save_project { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{customer_id} ||= 'NULL'; + + $form->{projectnumber} = $form->update_defaults($myconfig, "projectnumber", $dbh) unless $form->{projectnumber}; + + if ($form->{id}) { + + $query = qq|UPDATE project SET + projectnumber = |.$dbh->quote($form->{projectnumber}).qq|, + description = |.$dbh->quote($form->{description}).qq|, + startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|, + enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|, + customer_id = $form->{customer_id} + WHERE id = $form->{id}|; + } else { + + $query = qq|INSERT INTO project + (projectnumber, description, startdate, enddate, customer_id) + VALUES (| + .$dbh->quote($form->{projectnumber}).qq|, | + .$dbh->quote($form->{description}).qq|, | + .$form->dbquote($form->{startdate}, SQL_DATE).qq|, | + .$form->dbquote($form->{enddate}, SQL_DATE).qq|, + $form->{customer_id} + )|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub list_stock { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $var; + my $where = "1 = 1"; + + if ($form->{status} eq 'active') { + $where = qq|(pr.enddate IS NULL + OR pr.enddate >= current_date) + AND pr.completed < pr.production|; + } + if ($form->{status} eq 'inactive') { + $where = qq|pr.completed = pr.production|; + } + + if ($form->{projectnumber}) { + $var = $form->like(lc $form->{projectnumber}); + $where .= " AND lower(pr.projectnumber) LIKE '$var'"; + } + + if ($form->{description}) { + $var = $form->like(lc $form->{description}); + $where .= " AND lower(pr.description) LIKE '$var'"; + } + + $form->{sort} = "projectnumber" unless $form->{sort}; + my @a = ($form->{sort}); + my %ordinal = ( projectnumber => 2, + description => 3 + ); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $query = qq|SELECT pr.*, p.partnumber + FROM project pr + JOIN parts p ON (p.id = pr.parts_id) + WHERE $where + ORDER BY $sortorder|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_project} }, $ref; + } + $sth->finish; + + $query = qq|SELECT current_date FROM defaults|; + ($form->{stockingdate}) = $dbh->selectrow_array($query) if !$form->{stockingdate}; + + $dbh->disconnect; + +} + + +sub jobs { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{sort} = "projectnumber" unless $form->{sort}; + my @a = ($form->{sort}); + my %ordinal = ( projectnumber => 2, + description => 3, + startdate => 4, + ); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $query = qq|SELECT pr.*, p.partnumber, p.onhand, c.name + FROM project pr + JOIN parts p ON (p.id = pr.parts_id) + LEFT JOIN customer c ON (c.id = pr.customer_id) + WHERE 1=1|; + + if ($form->{projectnumber} ne "") { + $var = $form->like(lc $form->{projectnumber}); + $query .= " AND lower(pr.projectnumber) LIKE '$var'"; + } + if ($form->{description} ne "") { + $var = $form->like(lc $form->{description}); + $query .= " AND lower(pr.description) LIKE '$var'"; + } + + ($form->{startdatefrom}, $form->{startdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; + + if ($form->{startdatefrom}) { + $query .= " AND pr.startdate >= '$form->{startdatefrom}'"; + } + if ($form->{startdateto}) { + $query .= " AND pr.startdate <= '$form->{startdateto}'"; + } + + if ($form->{status} eq 'active') { + $query .= qq| AND NOT pr.production = pr.completed|; + } + if ($form->{status} eq 'inactive') { + $query .= qq| AND pr.production = pr.completed|; + } + if ($form->{status} eq 'orphaned') { + $query .= qq| AND pr.completed = 0 + AND (pr.id NOT IN SELECT DISTINCT project_id + FROM invoice + WHERE project_id > 0) + UNION + SELECT DISTINCT project_id + FROM orderitems + WHERE project_id > 0 + SELECT DISTINCT project_id + FROM jcitems + WHERE project_id > 0 + )|; + } + + $query .= qq| + ORDER BY $sortorder|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_project} }, $ref; + } + + $sth->finish; + + $dbh->disconnect; + +} + + +sub get_job { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query; + my $sth; + my $ref; + + if ($form->{id}) { + $query = qq|SELECT weightunit + FROM defaults|; + ($form->{weightunit}) = $dbh->selectrow_array($query); + + $query = qq|SELECT pr.*, + p.partnumber, p.description AS partdescription, p.unit, p.listprice, + p.sellprice, p.priceupdate, p.weight, p.notes, p.bin, + p.partsgroup_id, + ch.accno AS income_accno, ch.description AS income_description, + pr.customer_id, c.name AS customer, + pg.partsgroup + FROM project pr + LEFT JOIN parts p ON (p.id = pr.parts_id) + LEFT JOIN chart ch ON (ch.id = p.income_accno_id) + LEFT JOIN customer c ON (c.id = pr.customer_id) + LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) + WHERE pr.id = $form->{id}|; + } else { + $query = qq|SELECT weightunit, current_date AS startdate FROM defaults|; + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + $ref = $sth->fetchrow_hashref(NAME_lc); + + for (keys %$ref) { $form->{$_} = $ref->{$_} } + + $sth->finish; + + if ($form->{id}) { + # check if it is orphaned + $query = qq|SELECT count(*) + FROM invoice + WHERE project_id = $form->{id} + UNION + SELECT count(*) + FROM orderitems + WHERE project_id = $form->{id} + UNION + SELECT count(*) + FROM jcitems + WHERE project_id = $form->{id} + |; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $count; + while (($count) = $sth->fetchrow_array) { + $form->{orphaned} += $count; + } + $sth->finish; + + } + + $form->{orphaned} = !$form->{orphaned}; + + $query = qq|SELECT accno, description, link + FROM chart + WHERE link LIKE '%IC%' + ORDER BY accno|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + for (split /:/, $ref->{link}) { + if (/IC/) { + push @{ $form->{IC_links}{$_} }, { accno => $ref->{accno}, + description => $ref->{description} }; + } + } + } + $sth->finish; + + if ($form->{id}) { + $query = qq|SELECT ch.accno + FROM parts p + JOIN partstax pt ON (pt.parts_id = p.id) + JOIN chart ch ON (pt.chart_id = ch.id) + WHERE p.id = $form->{id}|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $form->{amount}{$ref->{accno}} = $ref->{accno}; + } + $sth->finish; + } + + PE->get_customer($myconfig, $form, $dbh); + + $dbh->disconnect; + +} + + +sub get_customer { + my ($self, $myconfig, $form, $dbh) = @_; + + my $disconnect = 0; + + if (! $dbh) { + $dbh = $form->dbconnect($myconfig); + $disconnect = 1; + } + + my $query; + my $sth; + my $ref; + + if (! $form->{startdate}) { + $query = qq|SELECT current_date FROM defaults|; + ($form->{startdate}) = $dbh->selectrow_array($query); + } + + my $where = qq|(startdate >= '$form->{startdate}' OR startdate IS NULL OR enddate IS NULL)|; + + if ($form->{enddate}) { + $where .= qq| AND (enddate >= '$form->{enddate}' OR enddate IS NULL)|; + } else { + $where .= qq| AND (enddate >= current_date OR enddate IS NULL)|; + } + + $query = qq|SELECT count(*) + FROM customer + WHERE $where|; + my ($count) = $dbh->selectrow_array($query); + + if ($count < $myconfig->{vclimit}) { + $query = qq|SELECT id, name + FROM customer + WHERE $where|; + + if ($form->{customer_id}) { + $query .= qq| + UNION SELECT id,name + FROM customer + WHERE id = $form->{customer_id}|; + } + + $query .= qq| + ORDER BY name|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + @{ $form->{all_customer} } = (); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_customer} }, $ref; + } + $sth->finish; + } + + $dbh->disconnect if $disconnect; + +} + + +sub save_job { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + my ($income_accno) = split /--/, $form->{IC_income}; + + my ($partsgroup, $partsgroup_id) = split /--/, $form->{partsgroup}; + $partsgroup_id ||= 'NULL'; + + if ($form->{id}) { + $query = qq|SELECT id FROM project + WHERE id = $form->{id}|; + ($form->{id}) = $dbh->selectrow_array($query); + } + + if (!$form->{id}) { + my $uid = localtime; + $uid .= "$$"; + + $query = qq|INSERT INTO project (projectnumber) + VALUES ('$uid')|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|SELECT id FROM project + WHERE projectnumber = '$uid'|; + ($form->{id}) = $dbh->selectrow_array($query); + } + + $form->{projectnumber} = $form->update_defaults($myconfig, "projectnumber", $dbh) unless $form->{projectnumber}; + + $query = qq|UPDATE project SET + projectnumber = |.$dbh->quote($form->{projectnumber}).qq|, + description = |.$dbh->quote($form->{description}).qq|, + startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|, + enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|, + parts_id = $form->{id}, + production = |.$form->parse_amount($myconfig, $form->{production}).qq|, + customer_id = $form->{customer_id} + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + + #### add/edit assembly + $query = qq|SELECT id FROM parts + WHERE id = $form->{id}|; + my ($id) = $dbh->selectrow_array($query); + + if (!$id) { + $query = qq|INSERT INTO parts (id) + VALUES ($form->{id})|; + $dbh->do($query) || $form->dberror($query); + } + + my $partnumber = ($form->{partnumber}) ? $form->{partnumber} : $form->{projectnumber}; + + $query = qq|UPDATE parts SET + partnumber = |.$dbh->quote($partnumber).qq|, + description = |.$dbh->quote($form->{partdescription}).qq|, + priceupdate = |.$form->dbquote($form->{priceupdate}, SQL_DATE).qq|, + listprice = |.$form->parse_amount($myconfig, $form->{listprice}).qq|, + sellprice = |.$form->parse_amount($myconfig, $form->{sellprice}).qq|, + weight = |.$form->parse_amount($myconfig, $form->{weight}).qq|, + bin = '$form->{bin}', + unit = |.$dbh->quote($form->{unit}).qq|, + notes = |.$dbh->quote($form->{notes}).qq|, + income_accno_id = (SELECT id FROM chart + WHERE accno = '$income_accno'), + partsgroup_id = $partsgroup_id, + assembly = '1', + obsolete = '1', + project_id = $form->{id} + WHERE id = $form->{id}|; + + $dbh->do($query) || $form->dberror($query); + + $query = qq|DELETE FROM partstax + WHERE parts_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + for (split / /, $form->{taxaccounts}) { + if ($form->{"IC_tax_$_"}) { + $query = qq|INSERT INTO partstax (parts_id, chart_id) + VALUES ($form->{id}, + (SELECT id + FROM chart + WHERE accno = '$_'))|; + $dbh->do($query) || $form->dberror($query); + } + } + + $dbh->commit; + $dbh->disconnect; + +} + + +sub stock_assembly { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + my $ref; + + my $query = qq|SELECT * + FROM project + WHERE id = ?|; + my $sth = $dbh->prepare($query) || $form->dberror($query); + + $query = qq|SELECT COUNT(*) + FROM parts + WHERE project_id = ?|; + my $rvh = $dbh->prepare($query) || $form->dberror($query); + + if (! $form->{stockingdate}) { + $query = qq|SELECT current_date FROM defaults|; + ($form->{stockingdate}) = $dbh->selectrow_array($query); + } + + $query = qq|SELECT * + FROM parts + WHERE id = ?|; + my $pth = $dbh->prepare($query) || $form->dberror($query); + + $query = qq|SELECT j.*, p.lastcost FROM jcitems j + JOIN parts p ON (p.id = j.parts_id) + WHERE j.project_id = ? + AND j.checkedin <= '$form->{stockingdate}' + ORDER BY parts_id|; + my $jth = $dbh->prepare($query) || $form->dberror($query); + + $query = qq|INSERT INTO assembly (id, parts_id, qty, bom, adj) + VALUES (?, ?, ?, '0', '0')|; + my $ath = $dbh->prepare($query) || $form->dberror($query); + + my $i = 0; + my $sold; + my $ship; + + while (1) { + $i++; + last unless $form->{"id_$i"}; + + $stock = $form->parse_amount($myconfig, $form->{"stock_$i"}); + + if ($stock) { + $sth->execute($form->{"id_$i"}); + $ref = $sth->fetchrow_hashref(NAME_lc); + + if ($stock > ($ref->{production} - $ref->{completed})) { + $stock = $ref->{production} - $ref->{completed}; + } + if (($stock * -1) > $ref->{completed}) { + $stock = $ref->{completed} * -1; + } + + $pth->execute($form->{"id_$i"}); + $pref = $pth->fetchrow_hashref(NAME_lc); + + my %assembly = (); + my $lastcost = 0; + my $sellprice = 0; + my $listprice = 0; + + $jth->execute($form->{"id_$i"}); + while ($jref = $jth->fetchrow_hashref(NAME_lc)) { + $assembly{qty}{$jref->{parts_id}} += ($jref->{qty} - $jref->{allocated}); + $assembly{parts_id}{$jref->{parts_id}} = $jref->{parts_id}; + $assembly{jcitems}{$jref->{id}} = $jref->{id}; + $lastcost += $form->round_amount(($jref->{lastcost} * ($jref->{qty} - $jref->{allocated})), 2); + $sellprice += $form->round_amount(($jref->{sellprice} * ($jref->{qty} - $jref->{allocated})), 2); + $listprice += $form->round_amount(($jref->{listprice} * ($jref->{qty} - $jref->{allocated})), 2); + } + $jth->finish; + + $uid = localtime; + $uid .= "$$"; + + $query = qq|INSERT INTO parts (partnumber) + VALUES ('$uid')|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|SELECT id + FROM parts + WHERE partnumber = '$uid'|; + ($uid) = $dbh->selectrow_array($query); + + $lastcost = $form->round_amount($lastcost / $stock, 2); + $sellprice = ($pref->{sellprice}) ? $pref->{sellprice} : $form->round_amount($sellprice / $stock, 2); + $listprice = ($pref->{listprice}) ? $pref->{listprice} : $form->round_amount($listprice / $stock, 2); + + $rvh->execute($form->{"id_$i"}); + my ($rev) = $rvh->fetchrow_array; + $rvh->finish; + + $query = qq|UPDATE parts SET + partnumber = '$pref->{partnumber}-$rev', + description = '$pref->{partdescription}', + priceupdate = '$form->{stockingdate}', + unit = '$pref->{unit}', + listprice = $listprice, + sellprice = $sellprice, + lastcost = $lastcost, + weight = $pref->{weight}, + onhand = $stock, + notes = '$pref->{notes}', + assembly = '1', + income_accno_id = $pref->{income_accno_id}, + bin = '$pref->{bin}', + project_id = $form->{"id_$i"} + WHERE id = $uid|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|INSERT INTO partstax (parts_id, chart_id) + SELECT '$uid', chart_id FROM partstax + WHERE parts_id = $pref->{id}|; + $dbh->do($query) || $form->dberror($query); + + + $pth->finish; + + for (keys %{$assembly{parts_id}}) { + if ($assembly{qty}{$_}) { + $ath->execute($uid, $assembly{parts_id}{$_}, $form->round_amount($assembly{qty}{$_} / $stock, 4)); + $ath->finish; + } + } + + $form->update_balance($dbh, + "project", + "completed", + qq|id = $form->{"id_$i"}|, + $stock); + + $query = qq|UPDATE jcitems SET + allocated = qty + WHERE allocated != qty + AND checkedin <= '$form->{stockingdate}' + AND project_id = $form->{"id_$i"}|; + $dbh->do($query) || $form->dberror($query); + + $sth->finish; + + } + + } + + my $rc = $dbh->commit; + $dbh->disconnect; + + $rc; + +} + + +sub delete_project { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + $query = qq|DELETE FROM project + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|DELETE FROM translation + WHERE trans_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + my $rc = $dbh->commit; + $dbh->disconnect; + + $rc; + +} + + +sub delete_partsgroup { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + $query = qq|DELETE FROM partsgroup + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|DELETE FROM translation + WHERE trans_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + my $rc = $dbh->commit; + $dbh->disconnect; + + $rc; + +} + + +sub delete_pricegroup { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + $query = qq|DELETE FROM pricegroup + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + my $rc = $dbh->commit; + $dbh->disconnect; + + $rc; + +} + + +sub delete_job { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + my %audittrail = ( tablename => 'project', + reference => $form->{id}, + formname => $form->{type}, + action => 'deleted', + id => $form->{id} ); + + $form->audittrail($dbh, "", \%audittrail); + + my $query = qq|DELETE FROM project + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|DELETE FROM translation + WHERE trans_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + # delete all the assemblies + $query = qq|DELETE FROM assembly a + JOIN parts p ON (a.id = p.id) + WHERE p.project_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|DELETE FROM parts + WHERE project_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + my $rc = $dbh->commit; + $dbh->disconnect; + + $rc; + +} + + +sub partsgroups { + my ($self, $myconfig, $form) = @_; + + my $var; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{sort} = "partsgroup" unless $form->{partsgroup}; + my @a = (partsgroup); + my $sortorder = $form->sort_order(\@a); + + my $query = qq|SELECT g.* + FROM partsgroup g|; + + my $where = "1 = 1"; + + if ($form->{partsgroup} ne "") { + $var = $form->like(lc $form->{partsgroup}); + $where .= " AND lower(partsgroup) LIKE '$var'"; + } + $query .= qq| + WHERE $where + ORDER BY $sortorder|; + + if ($form->{status} eq 'orphaned') { + $query = qq|SELECT g.* + FROM partsgroup g + LEFT JOIN parts p ON (p.partsgroup_id = g.id) + WHERE $where + EXCEPT + SELECT g.* + FROM partsgroup g + JOIN parts p ON (p.partsgroup_id = g.id) + WHERE $where + ORDER BY $sortorder|; + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $i = 0; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{item_list} }, $ref; + $i++; + } + + $sth->finish; + $dbh->disconnect; + + $i; + +} + + +sub save_partsgroup { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + if ($form->{id}) { + $query = qq|UPDATE partsgroup SET + partsgroup = |.$dbh->quote($form->{partsgroup}).qq| + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO partsgroup + (partsgroup) + VALUES (|.$dbh->quote($form->{partsgroup}).qq|)|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub get_partsgroup { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT * + FROM partsgroup + WHERE id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + for (keys %$ref) { $form->{$_} = $ref->{$_} } + + $sth->finish; + + # check if it is orphaned + $query = qq|SELECT count(*) + FROM parts + WHERE partsgroup_id = $form->{id}|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + ($form->{orphaned}) = $sth->fetchrow_array; + $form->{orphaned} = !$form->{orphaned}; + + $sth->finish; + + $dbh->disconnect; + +} + + +sub pricegroups { + my ($self, $myconfig, $form) = @_; + + my $var; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{sort} = "pricegroup" unless $form->{sort}; + my @a = (pricegroup); + my $sortorder = $form->sort_order(\@a); + + my $query = qq|SELECT g.* + FROM pricegroup g|; + + my $where = "1 = 1"; + + if ($form->{pricegroup} ne "") { + $var = $form->like(lc $form->{pricegroup}); + $where .= " AND lower(pricegroup) LIKE '$var'"; + } + $query .= qq| + WHERE $where + ORDER BY $sortorder|; + + if ($form->{status} eq 'orphaned') { + $query = qq|SELECT g.* + FROM pricegroup g + WHERE $where + AND g.id NOT IN (SELECT DISTINCT pricegroup_id + FROM partscustomer + WHERE pricegroup_id > 0) + ORDER BY $sortorder|; + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $i = 0; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{item_list} }, $ref; + $i++; + } + + $sth->finish; + $dbh->disconnect; + + $i; + +} + + +sub save_pricegroup { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + if ($form->{id}) { + $query = qq|UPDATE pricegroup SET + pricegroup = |.$dbh->quote($form->{pricegroup}).qq| + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO pricegroup + (pricegroup) + VALUES (|.$dbh->quote($form->{pricegroup}).qq|)|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub get_pricegroup { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT * + FROM pricegroup + WHERE id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + for (keys %$ref) { $form->{$_} = $ref->{$_} } + + $sth->finish; + + # check if it is orphaned + $query = qq|SELECT count(*) + FROM partscustomer + WHERE pricegroup_id = $form->{id}|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + ($form->{orphaned}) = $sth->fetchrow_array; + $form->{orphaned} = !$form->{orphaned}; + + $sth->finish; + + $dbh->disconnect; + +} + + +sub description_translations { + my ($self, $myconfig, $form) = @_; + + my $where = "1 = 1"; + my $var; + my $ref; + + for (qw(partnumber description)) { + if ($form->{$_}) { + $var = $form->like(lc $form->{$_}); + $where .= " AND lower(p.$_) LIKE '$var'"; + } + } + + $where .= " AND p.obsolete = '0'"; + $where .= " AND p.id = $form->{id}" if $form->{id}; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my %ordinal = ( 'partnumber' => 2, + 'description' => 3 + ); + + my @a = qw(partnumber description); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $query = qq|SELECT l.description AS language, t.description AS translation, + l.code + FROM translation t + JOIN language l ON (l.code = t.language_code) + WHERE trans_id = ? + ORDER BY 1|; + my $tth = $dbh->prepare($query); + + $query = qq|SELECT p.id, p.partnumber, p.description + FROM parts p + WHERE $where + ORDER BY $sortorder|; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $tra; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{translations} }, $ref; + + # get translations for description + $tth->execute($ref->{id}) || $form->dberror; + + while ($tra = $tth->fetchrow_hashref(NAME_lc)) { + $form->{trans_id} = $ref->{id}; + $tra->{id} = $ref->{id}; + push @{ $form->{translations} }, $tra; + } + $tth->finish; + + } + $sth->finish; + + &get_language("", $dbh, $form) if $form->{id}; + + $dbh->disconnect; + +} + + +sub partsgroup_translations { + my ($self, $myconfig, $form) = @_; + + my $where = "1 = 1"; + my $ref; + my $var; + + if ($form->{description}) { + $var = $form->like(lc $form->{description}); + $where .= " AND lower(p.partsgroup) LIKE '$var'"; + } + $where .= " AND p.id = $form->{id}" if $form->{id}; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT l.description AS language, t.description AS translation, + l.code + FROM translation t + JOIN language l ON (l.code = t.language_code) + WHERE trans_id = ? + ORDER BY 1|; + my $tth = $dbh->prepare($query); + + $form->sort_order(); + + $query = qq|SELECT p.id, p.partsgroup AS description + FROM partsgroup p + WHERE $where + ORDER BY 2 $form->{direction}|; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $tra; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{translations} }, $ref; + + # get translations for partsgroup + $tth->execute($ref->{id}) || $form->dberror; + + while ($tra = $tth->fetchrow_hashref(NAME_lc)) { + $form->{trans_id} = $ref->{id}; + push @{ $form->{translations} }, $tra; + } + $tth->finish; + + } + $sth->finish; + + &get_language("", $dbh, $form) if $form->{id}; + + $dbh->disconnect; + +} + + +sub project_translations { + my ($self, $myconfig, $form) = @_; + + my $where = "1 = 1"; + my $var; + my $ref; + + for (qw(projectnumber description)) { + if ($form->{$_}) { + $var = $form->like(lc $form->{$_}); + $where .= " AND lower(p.$_) LIKE '$var'"; + } + } + + $where .= " AND p.id = $form->{id}" if $form->{id}; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my %ordinal = ( 'projectnumber' => 2, + 'description' => 3 + ); + + my @a = qw(projectnumber description); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $query = qq|SELECT l.description AS language, t.description AS translation, + l.code + FROM translation t + JOIN language l ON (l.code = t.language_code) + WHERE trans_id = ? + ORDER BY 1|; + my $tth = $dbh->prepare($query); + + $query = qq|SELECT p.id, p.projectnumber, p.description + FROM project p + WHERE $where + ORDER BY $sortorder|; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $tra; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{translations} }, $ref; + + # get translations for description + $tth->execute($ref->{id}) || $form->dberror; + + while ($tra = $tth->fetchrow_hashref(NAME_lc)) { + $form->{trans_id} = $ref->{id}; + $tra->{id} = $ref->{id}; + push @{ $form->{translations} }, $tra; + } + $tth->finish; + + } + $sth->finish; + + &get_language("", $dbh, $form) if $form->{id}; + + $dbh->disconnect; + +} + + +sub get_language { + my ($self, $dbh, $form) = @_; + + # get language + my $query = qq|SELECT * + FROM language + ORDER BY 2|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_language} }, $ref; + } + $sth->finish; + +} + + +sub save_translation { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + my $query = qq|DELETE FROM translation + WHERE trans_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|INSERT INTO translation (trans_id, language_code, description) + VALUES ($form->{id}, ?, ?)|; + my $sth = $dbh->prepare($query) || $form->dberror($query); + + foreach my $i (1 .. $form->{translation_rows}) { + if ($form->{"language_code_$i"} ne "") { + $sth->execute($form->{"language_code_$i"}, $form->{"translation_$i"}); + $sth->finish; + } + } + $dbh->commit; + $dbh->disconnect; + +} + + +sub delete_translation { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|DELETE FROM translation + WHERE trans_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub project_sales_order { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT current_date FROM defaults|; + my ($transdate) = $dbh->selectrow_array($query); + + $form->all_years($myconfig, $dbh); + + $form->all_projects($myconfig, $dbh, $transdate); + + $form->all_employees($myconfig, $dbh, $transdate); + + $dbh->disconnect; + +} + + +sub get_jcitems { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $null; + my $var; + my $where; + + if ($form->{projectnumber}) { + ($null, $var) = split /--/, $form->{projectnumber}; + $where .= " AND j.project_id = $var"; + } + + if ($form->{employee}) { + ($null, $var) = split /--/, $form->{employee}; + $where .= " AND j.employee_id = $var"; + } + + ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; + + if ($form->{transdatefrom}) { + $where .= " AND j.checkedin >= '$form->{transdatefrom}'"; + } + if ($form->{transdateto}) { + $where .= " AND j.checkedout <= (date '$form->{transdateto}' + interval '1 days')"; + } + + my $query; + my $ref; + + $query = qq|SELECT j.id, j.description, j.qty - j.allocated AS qty, + j.sellprice, j.parts_id, pr.$form->{vc}_id, j.project_id, + j.checkedin::date AS transdate, j.notes, + c.name AS $form->{vc}, pr.projectnumber, p.partnumber + FROM jcitems j + JOIN project pr ON (pr.id = j.project_id) + JOIN employee e ON (e.id = j.employee_id) + JOIN parts p ON (p.id = j.parts_id) + LEFT JOIN $form->{vc} c ON (c.id = pr.$form->{vc}_id) + WHERE pr.parts_id IS NULL + AND j.allocated != j.qty + $where + ORDER BY pr.projectnumber, c.name, j.checkedin::date|; + + if ($form->{summary}) { + $query =~ s/j\.description/p\.description/; + $query =~ s/c\.name,/c\.name, j\.parts_id, /; + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + # tax accounts + $query = qq|SELECT c.accno + FROM chart c + JOIN partstax pt ON (pt.chart_id = c.id) + WHERE pt.parts_id = ?|; + my $tth = $dbh->prepare($query) || $form->dberror($query); + my $ptref; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + + $tth->execute($ref->{parts_id}); + $ref->{taxaccounts} = ""; + while ($ptref = $tth->fetchrow_hashref(NAME_lc)) { + $ref->{taxaccounts} .= "$ptref->{accno} "; + } + $tth->finish; + chop $ref->{taxaccounts}; + + $ref->{amount} = $ref->{sellprice} * $ref->{qty}; + + push @{ $form->{jcitems} }, $ref; + } + + $sth->finish; + + $query = qq|SELECT curr + FROM defaults|; + ($form->{currency}) = $dbh->selectrow_array($query); + $form->{currency} =~ s/:.*//; + $form->{defaultcurrency} = $form->{currency}; + + $query = qq|SELECT c.accno, t.rate + FROM tax t + JOIN chart c ON (c.id = t.chart_id)|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $form->{taxaccounts} .= "$ref->{accno} "; + $form->{"$ref->{accno}_rate"} = $ref->{rate}; + } + chop $form->{taxaccounts}; + $sth->finish; + + $dbh->disconnect; + +} + + +sub allocate_projectitems { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + for my $i (1 .. $form->{rowcount}) { + for (split / /, $form->{"jcitems_$i"}) { + my ($id, $qty) = split /:/, $_; + $form->update_balance($dbh, + 'jcitems', + 'allocated', + "id = $id", + $qty); + } + } + + $rc = $dbh->commit; + $dbh->disconnect; + + $rc; + +} + + +1; + -- cgit v1.2.3