summaryrefslogtreecommitdiff
path: root/LedgerSMB/PriceMatrix.pm
blob: a1e5ee742ebf81d2f868c0a28739ab371c4e021e (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://www.ledgersmb.org/
  5. #
  6. # Copyright (C) 2006
  7. # This work contains copyrighted information from a number of sources all used
  8. # with permission.
  9. #
  10. # This file contains source code included with or based on SQL-Ledger which
  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  12. # under the GNU General Public License version 2 or, at your option, any later
  13. # version. For a full list including contact information of contributors,
  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  15. #
  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  17. # Copyright (C) 2001
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has undergone whitespace cleanup
  27. #
  28. #======================================================================
  29. #
  30. # Price Matrix module
  31. #
  32. #
  33. #======================================================================
  34. package PriceMatrix;
  35. sub price_matrix_query {
  36. my ( $dbh, $form ) = @_;
  37. my $query;
  38. my $sth;
  39. my @queryargs;
  40. my $transdate = $form->{dbh}->quote( $form->{transdate} );
  41. if ( $form->{customer_id} ) {
  42. my $defaultcurrency = $form->{dbh}->quote( $form->{defaultcurrency} );
  43. my $customer_id = $form->{dbh}->quote( $form->{customer_id} );
  44. $query = qq|
  45. SELECT p.parts_id, p.customer_id, NULL AS pricegroup_id,
  46. p.pricebreak, p.sellprice, p.validfrom,
  47. p.validto, p.curr, NULL AS pricegroup,
  48. 1 as priority
  49. FROM partscustomer p
  50. WHERE p.parts_id = ?
  51. AND coalesce(p.validfrom, $transdate) <=
  52. $transdate
  53. AND coalesce(p.validto, $transdate) >=
  54. $transdate
  55. AND p.customer_id = $customer_id
  56. UNION
  57. SELECT p.parts_id, p.customer_id, p.pricegroup_id,
  58. p.pricebreak, p.sellprice, p.validfrom,
  59. p.validto, p.curr, g.pricegroup, 2 AS priority
  60. FROM partscustomer p
  61. JOIN pricegroup g ON (g.id = p.pricegroup_id)
  62. JOIN customer c ON (c.pricegroup_id = g.id)
  63. WHERE p.parts_id = ?
  64. AND coalesce(p.validfrom, $transdate) <=
  65. $transdate
  66. AND coalesce(p.validto, $transdate) >=
  67. $transdate
  68. AND c.id = $customer_id
  69. UNION
  70. SELECT p.parts_id, p.customer_id, p.pricegroup_id,
  71. p.pricebreak, p.sellprice, p.validfrom,
  72. p.validto, p.curr, g.pricegroup, 3 AS priority
  73. FROM partscustomer p
  74. LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
  75. WHERE p.customer_id = 0
  76. AND p.pricegroup_id = 0
  77. AND coalesce(p.validfrom, $transdate) <=
  78. $transdate
  79. AND coalesce(p.validto, $transdate) >=
  80. $transdate
  81. AND p.parts_id = ?
  82. ORDER BY priority LIMIT 1;
  83. |;
  84. $sth = $dbh->prepare($query) || $form->dberror($query);
  85. }
  86. elsif ( $form->{vendor_id} ) {
  87. my $vendor_id = $form->{dbh}->quote( $form->{vendor_id} );
  88. # price matrix and vendor's partnumber
  89. $query = qq|
  90. SELECT partnumber, lastcost
  91. FROM partsvendor
  92. WHERE parts_id = ?
  93. AND vendor_id = $vendor_id|;
  94. $sth = $dbh->prepare($query) || $form->dberror($query);
  95. }
  96. $sth;
  97. }
  98. sub price_matrix {
  99. my ( $pmh, $ref, $transdate, $decimalplaces, $form, $myconfig ) = @_;
  100. my $customerprice;
  101. my $pricegroupprice;
  102. my $sellprice;
  103. my $mref;
  104. my %p = ();
  105. # depends if this is a customer or vendor
  106. if ( $form->{customer_id} ) {
  107. $pmh->execute( $ref->{id}, $ref->{id}, $ref->{id} );
  108. } elsif ( $form->{vendor_id} ) {
  109. $pmh->execute( $ref->{id} );
  110. } else {
  111. return;
  112. }
  113. if ( $mref = $pmh->fetchrow_hashref(NAME_lc) ) {
  114. if ($form->{customer_id}){
  115. $form->db_parse_numeric(sth=>$sth, hashref=>$mref);
  116. $sellprice = $mref->{sellprice} || $ref->{sellprice};
  117. if ($mref->{pricebreak}){
  118. $sellprice = $sellprice
  119. - ($sellprice * ($mref->{pricebreak} / 100));
  120. }
  121. $ref->{sellprice} = $sellprice;
  122. } elsif ($form->{vendor_id}){
  123. $sellprice = $mref->{lastcost} || $ref->{sellprice};
  124. $ref->{sellprice} = $sellprice;
  125. }
  126. }
  127. }
  128. 1;