summaryrefslogtreecommitdiff
path: root/LedgerSMB/PriceMatrix.pm
blob: 41226ee66e1d16410118ed3f39f6e12f36a13600 (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 $entity_id = $form->{dbh}->quote( $form->{entity_id} );
  41. if ( $form->{customer_id} ) {
  42. my $defaultcurrency = $form->{dbh}->quote( $form->{defaultcurrency} );
  43. $query = qq|
  44. SELECT p.id AS parts_id, 0 AS entity_id,
  45. 0 AS pricegroup_id, 0 AS pricebreak,
  46. p.sellprice, NULL AS validfrom, NULL AS validto,
  47. (SELECT substr(value,1,3) FROM defaults WHERE
  48. setting_key = 'curr') AS curr,
  49. '' AS pricegroup
  50. FROM parts p
  51. WHERE p.id = ?
  52. UNION
  53. SELECT p.parts_id, p.customer_id AS entity_id,
  54. p.pricegroup_id,
  55. p.pricebreak, p.sellprice, p.validfrom,
  56. p.validto, p.curr, g.pricegroup
  57. FROM partscustomer p
  58. LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
  59. WHERE p.parts_id = ?
  60. AND p.customer_id = $entity_id
  61. UNION
  62. SELECT p.parts_id, p.customer_id AS entity_id,
  63. p.pricegroup_id,
  64. p.pricebreak, p.sellprice, p.validfrom,
  65. p.validto, p.curr, g.pricegroup
  66. FROM partscustomer p
  67. LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
  68. JOIN entity_credit_account c ON (c.pricegroup_id = g.id)
  69. WHERE p.parts_id = ?
  70. AND c.entity_id = $entity_id
  71. UNION
  72. SELECT p.parts_id, p.customer_id AS entity_id,
  73. p.pricegroup_id,
  74. p.pricebreak, p.sellprice, p.validfrom,
  75. p.validto, p.curr, g.pricegroup
  76. FROM partscustomer p
  77. LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
  78. WHERE p.customer_id = 0
  79. AND p.pricegroup_id = 0
  80. AND p.parts_id = ?
  81. ORDER BY entity_id DESC, pricegroup_id DESC,
  82. pricebreak
  83. |;
  84. $sth = $dbh->prepare($query) || $form->dberror($query);
  85. }
  86. elsif ( $form->{vendor_id} ) {
  87. # price matrix and vendor's partnumber
  88. $query = qq|
  89. SELECT partnumber
  90. FROM partsvendor
  91. WHERE parts_id = ?
  92. AND entity_id = $entity_id|;
  93. $sth = $dbh->prepare($query) || $form->dberror($query);
  94. }
  95. $sth;
  96. }
  97. sub price_matrix {
  98. my ( $pmh, $ref, $transdate, $decimalplaces, $form, $myconfig ) = @_;
  99. $ref->{pricematrix} = "";
  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}, $ref->{id} );
  108. while ( $mref = $pmh->fetchrow_hashref('NAME_lc') ) {
  109. $form->db_parse_numeric(sth=>$pmh, hashref=>$mref);
  110. # check date
  111. if ( $mref->{validfrom} ) {
  112. next
  113. if $transdate <
  114. $form->datetonum( $myconfig, $mref->{validfrom} );
  115. }
  116. if ( $mref->{validto} ) {
  117. next
  118. if $transdate >
  119. $form->datetonum( $myconfig, $mref->{validto} );
  120. }
  121. # convert price
  122. $sellprice =
  123. $form->round_amount(
  124. $mref->{sellprice} * $form->{ $mref->{curr} },
  125. $decimalplaces );
  126. if ( $mref->{customer_id} ) {
  127. $ref->{sellprice} = $sellprice
  128. if !$mref->{pricebreak};
  129. $p{ $mref->{pricebreak} } = $sellprice;
  130. $customerprice = 1;
  131. }
  132. if ( $mref->{pricegroup_id} ) {
  133. if ( !$customerprice ) {
  134. $ref->{sellprice} = $sellprice
  135. if !$mref->{pricebreak};
  136. $p{ $mref->{pricebreak} } = $sellprice;
  137. }
  138. $pricegroupprice = 1;
  139. }
  140. if ( !$customerprice && !$pricegroupprice ) {
  141. $p{ $mref->{pricebreak} } = $sellprice;
  142. }
  143. }
  144. $pmh->finish;
  145. if (%p) {
  146. if ( $ref->{sellprice} ) {
  147. $p{0} = $ref->{sellprice};
  148. }
  149. for ( sort { $a <=> $b } keys %p ) {
  150. $ref->{pricematrix} .= "${_}:$p{$_} ";
  151. }
  152. }
  153. else {
  154. if ($init) {
  155. $ref->{sellprice} =
  156. $form->round_amount( $ref->{sellprice}, $decimalplaces );
  157. }
  158. else {
  159. $ref->{sellprice} =
  160. $form->round_amount(
  161. $ref->{sellprice} * ( 1 - $form->{tradediscount} ),
  162. $decimalplaces );
  163. }
  164. $ref->{pricematrix} = "0:$ref->{sellprice} "
  165. if $ref->{sellprice};
  166. }
  167. chop $ref->{pricematrix};
  168. }
  169. if ( $form->{vendor_id} ) {
  170. $pmh->execute( $ref->{id} );
  171. $mref = $pmh->fetchrow_hashref('NAME_lc');
  172. $form->db_parse_numeric(sth=>$pmh, hashref=>$mref);
  173. if ( $mref->{partnumber} ne "" ) {
  174. $ref->{partnumber} = $mref->{partnumber};
  175. }
  176. if ( $mref->{lastcost} ) {
  177. # do a conversion
  178. $ref->{sellprice} =
  179. $form->round_amount( $mref->{lastcost} * $form->{ $mref->{curr} },
  180. $decimalplaces );
  181. }
  182. $pmh->finish;
  183. $ref->{sellprice} *= 1;
  184. # add 0:price to matrix
  185. $ref->{pricematrix} = "0:$ref->{sellprice}";
  186. }
  187. }
  188. 1;