1use utf8;
2
3package Interchange6::Schema::Result::Tax;
4
5=head1 NAME
6
7Interchange6::Schema::Result::Tax
8
9=cut
10
11use strict;
12use warnings;
13use DateTime;
14use POSIX qw/ceil floor/;
15
16use Interchange6::Schema::Candy -components => [
17    qw(InflateColumn::DateTime TimeStamp
18      +Interchange6::Schema::Component::Validation)
19];
20
21=head1 DESCRIPTION
22
23The taxes table contains taxes such as sales tax and VAT. Each tax has a unique tax_name but can contain multiple rows for each tax_name to allow for changes in tax rates over time. When there is more than one row for a single tax_name then the valid_from and valid_to periods may not overlap.
24
25=head1 ACCESSORS
26
27=head2 taxes_id
28
29Primary key.
30
31=cut
32
33primary_column taxes_id => {
34    data_type         => "integer",
35    is_auto_increment => 1,
36    sequence          => "taxes_id_seq"
37};
38
39=head2 tax_name
40
41Name of tax, e.g.: vat_full
42
43=cut
44
45column tax_name => { data_type => "varchar", size => 64 };
46
47=head2 description
48
49Description of tax, e.g.: New York sales tax
50
51=cut
52
53column description => { data_type => "varchar", size => 64 };
54
55=head2 percent
56
57Percent rate of tax, e.g.: 19.9775
58
59=cut
60
61column percent =>
62  { data_type => "numeric", size => [ 7, 4 ] };
63
64=head2 decimal_places
65
66Number of decimal_places of precision required for tax cost and reporting.
67
68Defaults to 2.
69
70=cut
71
72column decimal_places =>
73  { data_type => "integer", default_value => 2 };
74
75=head2 rounding
76
77Default rounding is half round up to the number of decimal_places. To use floor or ceiling set rounding to 'f' or 'c' as appropriate. The rounding value is automatically converted to lower case and any invalid value passed in will cause an exception to be thrown.
78
79Is nullable.
80
81=cut
82
83column rounding =>
84  { data_type => "char", is_nullable => 1, size => 1 };
85
86=head2 valid_from
87
88Date from which tax is valid. Defaults to time record is created.
89
90=cut
91
92column valid_from =>
93  { data_type => "date", set_on_create => 1 };
94
95=head2 valid_to
96
97Final date on which tax is valid.
98
99Is nullable.
100
101=cut
102
103column valid_to => { data_type => "date", is_nullable => 1 };
104
105=head2 country_iso_code
106
107FK on L<Interchange6::Schema::Result::Country/country_iso_code>.
108
109Is nullable.
110
111=cut
112
113column country_iso_code =>
114  { data_type => "char", is_nullable => 1, size => 2 };
115
116=head2 states_id
117
118FK on L<Interchange6::Schema::Result::State/states_id>.
119
120Is nullable.
121
122=cut
123
124column states_id =>
125  { data_type => "integer", is_nullable => 1 };
126
127=head2 created
128
129Date and time when this record was created returned as L<DateTime> object.
130Value is auto-set on insert.
131
132=cut
133
134column created =>
135  { data_type => "datetime", set_on_create => 1 };
136
137=head2 last_modified
138
139Date and time when this record was last modified returned as L<DateTime> object.
140Value is auto-set on insert and update.
141
142=cut
143
144column last_modified => {
145    data_type     => "datetime",
146    set_on_create => 1,
147    set_on_update => 1,
148};
149
150=head1 RELATIONS
151
152=head2 state
153
154Type: belongs_to
155
156Related object: L<Interchange6::Schema::Result::State>
157
158=cut
159
160belongs_to
161  state => "Interchange6::Schema::Result::State",
162  'states_id',
163  {
164    is_deferrable => 1,
165    on_delete     => "CASCADE",
166    on_update     => "CASCADE",
167    order_by      => 'name',
168    join_type     => 'left',
169  };
170
171=head2 country
172
173Type: belongs_to
174
175Related object: L<Interchange6::Schema::Result::Country>
176
177=cut
178
179belongs_to
180  country => "Interchange6::Schema::Result::Country",
181  'country_iso_code',
182  {
183    is_deferrable => 1,
184    on_delete     => "CASCADE",
185    on_update     => "CASCADE",
186    order_by      => 'name',
187    join_type     => 'left',
188  };
189
190=head1 METHODS
191
192=head2 calculate
193
194Calculate tax
195
196Arguments should be a hash ref of the following arguments:
197
198=over 4
199
200=item * price
201
202Price of product either inclusive or exclusive of tax - required.
203
204=item * tax_included
205
206Boolean indicating whether price is inclusive of tax or not. Defaults to 0 which means exclusive of tax.
207
208Will throw an exception if the price us not numeric.
209
210=back
211
212Usage example:
213
214    my $tax = $taxrecord->caclulate({ price => 13.47, tax_included => 1 });
215
216    # with percentage 18 our tax is 2.05
217
218=cut
219
220sub calculate {
221    my $self = shift;
222    my $args = shift;
223
224    my $schema = $self->result_source->schema;
225    my $dtf    = $schema->storage->datetime_parser;
226    my $dt     = DateTime->today;
227    my $tax;
228
229    $schema->throw_exception("argument price is missing")
230      unless defined $args->{price};
231
232    $schema->throw_exception(
233        "argument price is not a valid numeric: " . $args->{price} )
234      unless $args->{price} =~ m/^(\d+)*(\.\d+)*$/;
235
236    if ( $args->{tax_included} ) {
237        my $nett = $args->{price} / ( 1 + ( $self->percent / 100 ) );
238        $tax = $args->{price} - $nett;
239    }
240    else {
241        $tax = $args->{price} * $self->percent / 100;
242    }
243
244    # round & return
245
246    my $decimal_places = $self->decimal_places;
247
248    unless ( $self->rounding ) {
249
250        return sprintf( "%.${decimal_places}f", $tax );
251    }
252    else {
253
254        $tax *= 10**$decimal_places;
255
256        if ( $self->rounding eq 'c' ) {
257            $tax = ceil($tax) / ( 10**$decimal_places );
258        }
259        elsif ( $self->rounding eq 'f' ) {
260            $tax = floor($tax) / ( 10**$decimal_places );
261        }
262        else {
263
264            # should not be possible to get here
265            $schema->throw_exception(
266                "rounding value from database is invalid: " . $self->rounding );
267        }
268
269        return sprintf( "%.${decimal_places}f", $tax );
270    }
271}
272
273=head1 INHERITED METHODS
274
275=head2 new
276
277We overload the new method to set default values on certain rows at create time.
278
279=cut
280
281sub new {
282    my ( $class, $attrs ) = @_;
283
284    my %attrs = %$attrs;
285
286    $attrs->{decimal_places} = 2 unless defined $attrs->{decimal_places};
287
288    my $new = $class->next::method( \%attrs );
289
290    return $new;
291}
292
293=head2 sqlt_deploy_hook
294
295Called during table creation to add indexes on the following columns:
296
297=over 4
298
299=item * tax_name
300
301=item * valid_from
302
303=item * valid_to
304
305=back
306
307=cut
308
309sub sqlt_deploy_hook {
310    my ( $self, $table ) = @_;
311
312    $table->add_index( name => 'taxes_idx_tax_name', fields => ['tax_name'] );
313    $table->add_index(
314        name   => 'taxes_idx_valid_from',
315        fields => ['valid_from']
316    );
317    $table->add_index(
318        name   => 'taxes_idx_valid_to',
319        fields => ['valid_to']
320    );
321}
322
323=head2 validate
324
325Validity checks that cannot be enforced using primary key, unique or other database methods using L<Interchange6::Schema::Component::Validation>. The validity checks enforce the following rules:
326
327=over 4
328
329=item * Check country_iso_code is valid
330
331=item * If both valid_from and valid_to are defined then valid_to must be a later date than valid_from.
332
333=item * A single tax_name may appear more than once in the table to allow for changes in tax rates but valid_from/valid_to date ranges must not overlap.
334
335=back
336
337=cut
338
339sub validate {
340    my $self   = shift;
341    my $schema = $self->result_source->schema;
342    my $dtf    = $schema->storage->datetime_parser;
343    my $rset;
344
345    # country iso code
346
347    if ( defined $self->country_iso_code ) {
348        $rset =
349          $schema->resultset('Country')
350          ->search( { country_iso_code => $self->country_iso_code } );
351        if ( $rset->count == 0 ) {
352            $schema->throw_exception(
353                'country_iso_code not valid: ' . $self->country_iso_code );
354        }
355    }
356
357    # rounding
358
359    if ( defined $self->rounding ) {
360
361        # set lower case
362
363        my $rounding = lc( $self->rounding );
364        $self->rounding($rounding);
365
366        unless ( $self->rounding =~ /^(c|f)$/ ) {
367            $self->rounding(undef);
368            $schema->throw_exception(
369                'value for rounding not c, f or undef: ' . $rounding );
370        }
371    }
372
373    # check that valid_to is later than valid_from (if it is defined)
374
375    $self->valid_from->truncate( to => 'day' );
376
377    if ( defined $self->valid_to ) {
378
379        # remove time - we only want the date
380        $self->valid_to->truncate( to => 'day' );
381
382        unless ( $self->valid_to > $self->valid_from ) {
383            $schema->throw_exception("valid_to is not later than valid_from");
384        }
385    }
386
387    # grab our resultset
388
389    $rset = $self->result_source->resultset;
390
391    if ( $self->in_storage ) {
392
393        # this is an update so we must exclude our existing record from
394        # the resultset before range overlap checks are performed
395
396        $rset = $rset->search( { taxes_id => { '!=', $self->taxes_id } } );
397    }
398
399    # multiple entries for a single tax code do not overlap dates
400
401    if ( defined $self->valid_to ) {
402        $rset = $rset->search(
403            {
404                tax_name => $self->tax_name,
405                -or      => [
406                    valid_from => {
407                        -between => [
408                            $dtf->format_datetime( $self->valid_from ),
409                            $dtf->format_datetime( $self->valid_to ),
410                        ]
411                    },
412                    valid_to => {
413                        -between => [
414                            $dtf->format_datetime( $self->valid_from ),
415                            $dtf->format_datetime( $self->valid_to ),
416                        ]
417                    },
418                ],
419            }
420        );
421
422        if ( $rset->count > 0 ) {
423            $schema->throw_exception(
424                'tax overlaps existing date range: ' . $self->tax_name );
425        }
426    }
427    else {
428        $rset = $rset->search(
429            {
430                tax_name => $self->tax_name,
431                -or      => [
432                    {
433                        valid_to => undef,
434                        valid_from =>
435                          { '<=', $dtf->format_datetime( $self->valid_from ) },
436                    },
437                    {
438                        valid_to => { '!=', undef },
439                        valid_to =>
440                          { '>=', $dtf->format_datetime( $self->valid_from ) },
441                    },
442                ],
443            }
444        );
445    }
446    if ( $rset->count > 0 ) {
447        $schema->throw_exception('tax overlaps existing date range');
448    }
449}
450
4511;
452