diff options
author | christopherm <christopherm@4979c152-3d1c-0410-bac9-87ea11338e46> | 2006-09-01 01:16:38 +0000 |
---|---|---|
committer | christopherm <christopherm@4979c152-3d1c-0410-bac9-87ea11338e46> | 2006-09-01 01:16:38 +0000 |
commit | ac5b087ea2d9ba7428d367aaeb288534158fee9a (patch) | |
tree | 2dbe0bdea0b653a215ba9ddfdf627cb57855050d /LedgerSMB/JC.pm |
Initial Import
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/ledger-smb@1 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'LedgerSMB/JC.pm')
-rwxr-xr-x | LedgerSMB/JC.pm | 582 |
1 files changed, 582 insertions, 0 deletions
diff --git a/LedgerSMB/JC.pm b/LedgerSMB/JC.pm new file mode 100755 index 00000000..af1ffd40 --- /dev/null +++ b/LedgerSMB/JC.pm @@ -0,0 +1,582 @@ +#===================================================================== +# LedgerSMB +# Small Medium Business Accounting software +# +# See COPYRIGHT file for copyright information +#====================================================================== +# +# This file has NOT undergone whitespace cleanup. +# +#====================================================================== +# +# Job Costing +# +#====================================================================== + + +package JC; + +use LedgerSMB::IS; + +sub get_jcitems { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT current_date FROM defaults|; + ($form->{transdate}) = $dbh->selectrow_array($query); + + ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh); + + my $dateformat = $myconfig->{dateformat}; + $dateformat =~ s/yy/yyyy/; + $dateformat =~ s/yyyyyy/yyyy/; + + if ($form->{id}) { + # retrieve timecard/storescard + $query = qq|SELECT j.*, to_char(j.checkedin, 'HH24:MI:SS') AS checkedina, + to_char(j.checkedout, 'HH24:MI:SS') AS checkedouta, + to_char(j.checkedin, '$dateformat') AS transdate, + e.name AS employee, p.partnumber, + pr.projectnumber, pr.description AS projectdescription, + pr.production, pr.completed, pr.parts_id AS project + FROM jcitems j + JOIN employee e ON (e.id = j.employee_id) + JOIN parts p ON (p.id = j.parts_id) + JOIN project pr ON (pr.id = j.project_id) + WHERE j.id = $form->{id}|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + $ref = $sth->fetchrow_hashref(NAME_lc); + + for (keys %$ref) { $form->{$_} = $ref->{$_} } + $sth->finish; + $form->{project} = ($form->{project}) ? "job" : "project"; + for (qw(checkedin checkedout)) { + $form->{$_} = $form->{"${_}a"}; + delete $form->{"${_}a"}; + } + + $query = qq|SELECT s.printed, s.spoolfile, s.formname + FROM status s + WHERE s.formname = '$form->{type}' + AND s.trans_id = $form->{id}|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $form->{printed} .= "$ref->{formname} " if $ref->{printed}; + $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile}; + } + $sth->finish; + for (qw(printed queued)) { $form->{$_} =~ s/ +$//g } + } + + JC->jcitems_links($myconfig, $form, $dbh); + + # get language codes + $query = qq|SELECT * + FROM language + ORDER BY 2|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + $form->{all_language} = (); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_language} }, $ref; + } + $sth->finish; + + $dbh->disconnect; + +} + + +sub jcitems_links { + my ($self, $myconfig, $form, $dbh) = @_; + + my $disconnect = 0; + + if (! $dbh) { + $dbh = $form->dbconnect($myconfig); + $disconnect = 1; + } + + my $query; + + if ($form->{project_id}) { + $form->{orphaned} = 1; + $query = qq|SELECT parts_id + FROM project + WHERE id = $form->{project_id}|; + if ($dbh->selectrow_array($query)) { + $form->{project} = 'job'; + $query = qq|SELECT id + FROM project + WHERE parts_id > 0 + AND production > completed + AND id = $form->{project_id}|; + ($form->{orphaned}) = $dbh->selectrow_array($q); + } else { + $form->{project} = 'project'; + } + } + + JC->jcparts($myconfig, $form, $dbh); + + $form->all_employees($myconfig, $dbh, $form->{transdate}); + + my $where; + + if ($form->{transdate}) { + $where .= qq| AND (enddate IS NULL + OR enddate >= '$form->{transdate}') + AND (startdate <= '$form->{transdate}' + OR startdate IS NULL)|; + } + + if ($form->{project} eq 'job') { + $query = qq| + SELECT pr.* + FROM project pr + WHERE pr.parts_id > 0 + AND pr.production > pr.completed + $where|; + } elsif ($form->{project} eq 'project') { + $query = qq| + SELECT pr.* + FROM project pr + WHERE pr.parts_id IS NULL + $where|; + } else { + $query = qq| + SELECT pr.* + FROM project pr + WHERE 1=1 + $where + EXCEPT + SELECT pr.* + FROM project pr + WHERE pr.parts_id > 0 + AND pr.production = pr.completed|; + } + + if ($form->{project_id}) { + $query .= qq| + UNION + SELECT * + FROM project + WHERE id = $form->{project_id}|; + } + + $query .= qq| + ORDER BY projectnumber|; + + $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 if $disconnect; + +} + + +sub jcparts { + my ($self, $myconfig, $form, $dbh) = @_; + + my ($null, $project_id) = split /--/, $form->{projectnumber}; + $project_id *= 1; + + my $query = qq|SELECT customer_id + FROM project + WHERE id = $project_id|; + my ($customer_id) = $dbh->selectrow_array($query); + $customer_id *= 1; + + my $where; + + if ($form->{project} eq 'job') { + $where = " AND p.income_accno_id IS NULL"; + if ($form->{type} eq 'storescard') { + $where = " AND p.inventory_accno_id > 0 + AND p.income_accno_id > 0"; + } + + $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice, + p.unit, t.description AS translation + FROM parts p + LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}') + WHERE p.obsolete = '0' + $where|; + } elsif ($form->{project} eq 'project') { + $where = " AND p.inventory_accno_id IS NULL"; + if ($form->{type} eq 'storescard') { + $where = " AND p.inventory_accno_id > 0"; + } + + $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice, + p.unit, t.description AS translation + FROM parts p + LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}') + WHERE p.obsolete = '0' + AND p.assembly = '0' + $where|; + } else { + + $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice, + p.unit, t.description AS translation + FROM parts p + LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}') + WHERE p.obsolete = '0' + AND p.income_accno_id IS NULL + UNION + SELECT p.id, p.partnumber, p.description, p.sellprice, + p.unit, t.description AS translation + FROM parts p + LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}') + WHERE p.obsolete = '0' + AND p.assembly = '0' + AND p.inventory_accno_id IS NULL|; + } + + $query .= qq| + ORDER BY 2|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $pmh = price_matrix_query($dbh, $project_id, $customer_id); + IS::exchangerate_defaults($dbh, $form); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + $ref->{description} = $ref->{translation} if $ref->{translation}; + IS::price_matrix($pmh, $ref, $form->datetonum($form->{transdate}), 4, $form, $myconfig); + push @{ $form->{all_parts} }, $ref; + } + $sth->finish; + +} + + +sub delete_timecard { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + my %audittrail = ( tablename => 'jcitems', + reference => $form->{id}, + formname => $form->{type}, + action => 'deleted', + id => $form->{id} ); + + $form->audittrail($dbh, "", \%audittrail); + + my $query = qq|DELETE FROM jcitems + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + # delete spool files + $query = qq|SELECT spoolfile FROM status + WHERE formname = '$form->{type}' + AND trans_id = $form->{id} + AND spoolfile IS NOT NULL|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $spoolfile; + my @spoolfiles = (); + + while (($spoolfile) = $sth->fetchrow_array) { + push @spoolfiles, $spoolfile; + } + $sth->finish; + + # delete status entries + $query = qq|DELETE FROM status + WHERE formname = '$form->{type}' + AND trans_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + my $rc = $dbh->commit; + + if ($rc) { + foreach $spoolfile (@spoolfiles) { + unlink "$spool/$spoolfile" if $spoolfile; + } + } + + $dbh->disconnect; + + $rc; + +} + + +sub jcitems { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query; + my $where = "1 = 1"; + my $null; + my $var; + + if ($form->{projectnumber}) { + ($null, $var) = split /--/, $form->{projectnumber}; + $where .= " AND j.project_id = $var"; + + $query = qq|SELECT parts_id + FROM project + WHERE id = $var|; + my ($job) = $dbh->selectrow_array($query); + $form->{project} = ($job) ? "job" : "project"; + + } + if ($form->{partnumber}) { + ($null, $var) = split /--/, $form->{partnumber}; + $where .= " AND j.parts_id = $var"; + + $query = qq|SELECT inventory_accno_id + FROM parts + WHERE id = $var|; + my ($job) = $dbh->selectrow_array($query); + $form->{project} = ($job) ? "job" : "project"; + + } + if ($form->{employee}) { + ($null, $var) = split /--/, $form->{employee}; + $where .= " AND j.employee_id = $var"; + } + if ($form->{open} || $form->{closed}) { + unless ($form->{open} && $form->{closed}) { + $where .= " AND j.qty != j.allocated" if $form->{open}; + $where .= " AND j.qty = j.allocated" if $form->{closed}; + } + } + + ($form->{startdatefrom}, $form->{startdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; + + $where .= " AND j.checkedin >= '$form->{startdatefrom}'" if $form->{startdatefrom}; + $where .= " AND j.checkedout < date '$form->{startdateto}' + 1" if $form->{startdateto}; + + my %ordinal = ( id => 1, + description => 2, + transdate => 7, + partnumber => 9, + projectnumber => 10, + projectdescription => 11, + ); + + my @a = (transdate, projectnumber); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $dateformat = $myconfig->{dateformat}; + $dateformat =~ s/yy$/yyyy/; + $dateformat =~ s/yyyyyy/yyyy/; + + if ($form->{project} eq 'job') { + if ($form->{type} eq 'timecard') { + $where .= " AND pr.parts_id > 0 + AND p.income_accno_id IS NULL"; + } + + if ($form->{type} eq 'storescard') { + $where .= " AND pr.parts_id > 0 + AND p.income_accno_id > 0"; + } + } + if ($form->{project} eq 'project') { + $where .= " AND pr.parts_id IS NULL"; + } + + $query = qq|SELECT j.id, j.description, j.qty, j.allocated, + to_char(j.checkedin, 'HH24:MI') AS checkedin, + to_char(j.checkedout, 'HH24:MI') AS checkedout, + to_char(j.checkedin, 'yyyymmdd') AS transdate, + to_char(j.checkedin, '$dateformat') AS transdatea, + to_char(j.checkedin, 'D') AS weekday, + p.partnumber, + pr.projectnumber, pr.description AS projectdescription, + e.employeenumber, e.name AS employee, + to_char(j.checkedin, 'WW') AS workweek, pr.parts_id, + j.sellprice + FROM jcitems j + JOIN parts p ON (p.id = j.parts_id) + JOIN project pr ON (pr.id = j.project_id) + JOIN employee e ON (e.id = j.employee_id) + WHERE $where + ORDER BY employee, employeenumber, $sortorder|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $ref->{project} = ($ref->{parts_id}) ? "job" : "project"; + $ref->{transdate} = $ref->{transdatea}; + delete $ref->{transdatea}; + push @{ $form->{transactions} }, $ref; + } + $sth->finish; + + $dbh->disconnect; + +} + + +sub save { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + my $query; + my $sth; + + my ($null, $project_id) = split /--/, $form->{projectnumber}; + + if ($form->{id}) { + # check if it was a job + $query = qq|SELECT pr.parts_id, pr.production - pr.completed + FROM project pr + JOIN jcitems j ON (j.project_id = pr.id) + WHERE j.id = $form->{id}|; + my ($job_id, $qty) = $dbh->selectrow_array($query); + + if ($job_id && $qty == 0) { + $dbh->disconnect; + return -1; + } + + # check if new one belongs to a job + if ($project_id) { + $query = qq|SELECT pr.parts_id, pr.production - pr.completed + FROM project pr + WHERE pr.id = $project_id|; + my ($job_id, $qty) = $dbh->selectrow_array($query); + + if ($job_id && $qty == 0) { + $dbh->disconnect; + return -2; + } + } + + } else { + my $uid = localtime; + $uid .= "$$"; + + $query = qq|INSERT INTO jcitems (description) + VALUES ('$uid')|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|SELECT id FROM jcitems + WHERE description = '$uid'|; + ($form->{id}) = $dbh->selectrow_array($query); + } + + for (qw(inhour inmin insec outhour outmin outsec)) { $form->{$_} = substr("00$form->{$_}", -2) } + for (qw(qty sellprice allocated)) { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) } + + my $checkedin = "$form->{inhour}$form->{inmin}$form->{insec}"; + my $checkedout = "$form->{outhour}$form->{outmin}$form->{outsec}"; + + my $outdate = $form->{transdate}; + if ($checkedout < $checkedin) { + $outdate = $form->add_date($myconfig, $form->{transdate}, 1, 'days'); + } + + ($null, $form->{employee_id}) = split /--/, $form->{employee}; + unless ($form->{employee_id}) { + ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh); + } + + my $parts_id; + ($null, $parts_id) = split /--/, $form->{partnumber}; + + $query = qq|UPDATE jcitems SET + project_id = $project_id, + parts_id = $parts_id, + description = |.$dbh->quote($form->{description}).qq|, + qty = $form->{qty}, + allocated = $form->{allocated}, + sellprice = $form->{sellprice}, + fxsellprice = $form->{sellprice}, + serialnumber = |.$dbh->quote($form->{serialnumber}).qq|, + checkedin = timestamp '$form->{transdate} $form->{inhour}:$form->{inmin}:$form->{insec}', + checkedout = timestamp '$outdate $form->{outhour}:$form->{outmin}:$form->{outsec}', + employee_id = $form->{employee_id}, + notes = |.$dbh->quote($form->{notes}).qq| + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + # save printed, queued + $form->save_status($dbh); + + my %audittrail = ( tablename => 'jcitems', + reference => $form->{id}, + formname => $form->{type}, + action => 'saved', + id => $form->{id} ); + + $form->audittrail($dbh, "", \%audittrail); + + my $rc = $dbh->commit; + + $rc; + +} + + +sub price_matrix_query { + my ($dbh, $project_id, $customer_id) = @_; + + my $query = qq|SELECT p.id AS parts_id, 0 AS customer_id, 0 AS pricegroup_id, + 0 AS pricebreak, p.sellprice, NULL AS validfrom, NULL AS validto, + (SELECT substr(curr,1,3) FROM defaults) AS curr, '' AS pricegroup + FROM parts p + WHERE p.id = ? + + UNION + + SELECT p.*, g.pricegroup + FROM partscustomer p + LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id) + WHERE p.parts_id = ? + AND p.customer_id = $customer_id + + UNION + + SELECT p.*, g.pricegroup + FROM partscustomer p + LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id) + JOIN customer c ON (c.pricegroup_id = g.id) + WHERE p.parts_id = ? + AND c.id = $customer_id + + UNION + + SELECT p.*, '' AS pricegroup + FROM partscustomer p + WHERE p.customer_id = 0 + AND p.pricegroup_id = 0 + AND p.parts_id = ? + + ORDER BY customer_id DESC, pricegroup_id DESC, pricebreak + + |; + my $sth = $dbh->prepare($query) || $form->dberror($query); + + $sth; + +} + + +1; + |