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