Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Tree: 94f865a566
Fetching contributors…

Cannot retrieve contributors at this time

executable file 390 lines (340 sloc) 13.654 kB
#!/usr/bin/env perl
use warnings;
use strict;
use Spreadsheet::ParseExcel;
use Getopt::Std;
use Data::Dumper;
$Data::Dumper::Indent = 2;
$|++;
BEGIN {
use lib qq{$ENV{MIS}/lib};
use X_Db;
use Schema;
}
my $schema = Schema->connect ( sub {X_Db::connect('MIS') });
my %opts;
getopts('df:', \%opts);
if ($opts{d}) { $schema->deploy({ add_drop_table => 1}); exit 0}
sub assign_cols {
my ( $sheet, $lkps_ref, $side ) = @_;
my ( $col_min, $col_max ) = $sheet->col_range();
my ( $row_min, $row_max ) = $sheet->row_range();
my $lkps = $$lkps_ref;
for my $col ( $col_min .. $col_max ) {
my $val;
eval {
($val) = $sheet->get_cell( $row_min, $col )->value
=~ /\s*(.*)\s*/s;
};
print $@, ",", $col && next if $@;
for my $table ( keys %{ $lkps->{$side} } ) {
for my $attr ( keys %{ $lkps->{$side}{$table} } ) {
if ( $lkps->{$side}{$table}{$attr}{Column} =~m/$val/i ) {
$lkps->{$side}{$table}{$attr}{Num} = $col;
$ENV{DEBUG} &&
print "Assigning column $col for $side,$table,"
. "$attr:$val";
}
}
}
}
}
sub assign_val {
my ( $row, $sheet, $lkps_ref, $side ) = @_;
my $lkps = $$lkps_ref;
for my $table ( keys %{ $lkps->{$side} } ) {
for my $attr ( keys %{ $lkps->{$side}{$table} } ) {
undef $lkps->{$side}{$table}{$attr}{value};
my $cell =
$sheet->get_cell( $row, $lkps->{$side}{$table}{$attr}{Num} );
next unless $cell;
my $cval = $cell->value;
next
unless ( $cval
&& $cval !~ m|n/a|ims
&& $cval !~ /\A\s*\z/s );
$cval =~ s/'/\x27\x27/gs;
$cval =~ s/[^[:print:]]+/ /;
$lkps->{$side}{$table}{$attr}{value} = $cval;
}
}
}
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($opts{f});
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
my ( @lkp_sheets, %meta_cols, %sheets );
my $worksheet = $workbook->worksheet('_map');
die "Cannot find _map sheet" unless $worksheet;
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
my $irow=0;
my %col;
while (my $em_attr=$worksheet->get_cell($irow,0)->value)
{
$col{$em_attr}=$worksheet->get_cell($irow,1)->value;
$irow++;
}
#-----------------------------------------------------------------
# Delete current entity master and set id for db access
#
$schema->resultset("EntityMaster")->search({ name => $col{name},
version => $col{version}})->delete;
my ( $sec, $min, $hr, $dd, $mm, $yy ) = (localtime)[ 0, 1, 2, 3, 4, 5 ];
my $dt= sprintf( "%4.4d-%2.2d-%2.2d %02d:%02d:%02d.000000",
1900 + $yy, $mm + 1, $dd, $hr, $min, $sec );
$schema->txn_begin;
my $dbix_em=$schema->resultset('EntityMaster')->create({
version=>$col{version},name=>$col{name},
uploaded=>$col{uploaded}, author=>$col{author},
rel_notes=>$col{rel_notes}});
$irow++;
for my $col ( $col_min .. $col_max ) {
my $val = $worksheet->get_cell( $irow, $col )->value;
$meta_cols{ ( $val =~ m/\s*(.*)\s*/ )[0] } = $col;
}
$irow++;
for my $row ( $irow .. $row_max ) {
my $sheet_name = $worksheet->get_cell( $row, $meta_cols{Sheet} )->value;
push @lkp_sheets, $sheet_name unless $sheets{$sheet_name};
$sheets{$sheet_name}{
(
$worksheet->get_cell( $row, $meta_cols{Side} )->value =~
/\s*(.*?)\s*$/
)[0]
}{
(
$worksheet->get_cell( $row, $meta_cols{Table} )->value =~
/\s*(.*?)\s*$/
)[0]
}{
(
$worksheet->get_cell( $row, $meta_cols{Attribute} )->value =~
/\s*(.*?)\s*$/
)[0]
}
= {
'Column' => (
$worksheet->get_cell( $row, $meta_cols{Column} )->value =~
/\s*(.*?)\s*$/
)[0],
'Num' => 0,
};
}
#---------------------------------------------
# hash used to keep track of max seq number in an entity
#
my %db;
print "Looking at: Targets\n";
for my $this_sheet (@lkp_sheets) {
print $this_sheet, "\n";
my $worksheet = $workbook->worksheet(qq{$this_sheet});
my $wksht_lkps = $sheets{$this_sheet};
my ( $row_min, $row_max ) = $worksheet->row_range();
assign_cols( $worksheet, \$wksht_lkps, 'tgt' );
#-------------------------------------------------------
# Sheet Consistency checks
my $tgt = $wksht_lkps->{tgt};
die "$this_sheet:No tgt" unless $tgt;
die "$this_sheet:No entity name" if !exists $tgt->{entity}{ds_name};
die "$this_sheet:No entity" if !exists $tgt->{entity};
die "$this_sheet:No Item Name"
if exists $tgt->{item}
&& !exists $tgt->{item}{name};
#-------------------------------------------------
# Hash used to keep track of current target item
# in case there are rows where target is empty
# indicating multiple source items
#
my %curr_itm;
for my $row ( $row_min + 1 .. $row_max ) {
assign_val( $row, $worksheet, \$wksht_lkps, 'tgt' );
next unless $tgt->{entity}{ds_name}{value};
#-----------------------------------------------------
# Row consistency checks
my $tgt_ent_nm = $tgt->{entity}{ds_name}{value};
die "$this_sheet:No entity name" unless $tgt_ent_nm;
if ( !exists $db{$tgt_ent_nm} ) {
$ENV{DEBUG} && print "$row:Adding $tgt->{entity}{ds_name}{value}\n";
$ENV{DEBUG} && print "Going to insert into entity:$tgt_ent_nm";
$db{$tgt_ent_nm}{dbix}=$schema->resultset('Entity')->create({
ds_name=>$tgt_ent_nm,ds_no=>$tgt->{entity}{ds_no}{value},
em_id=>$dbix_em->id});
$db{$tgt_ent_nm}{seq} = 1;
}
$tgt_ent_nm = $tgt->{entity}{ds_name}{value};
for my $attr ( keys %{ $tgt->{entity} } ) {
next if $attr eq 'ds_name';
my $attr_val = $tgt->{entity}{$attr}{value};
next unless defined $attr_val;
$ENV{DEBUG} && print "Updating entity $tgt_ent_nm:$attr"
. " with $attr_val\n";
$db{$tgt_ent_nm}{dbix}->$attr($attr_val) &&
$db{$tgt_ent_nm}{dbix}->update if defined $attr_val;
}
my ( $tgt_itm_nm, $src_itm_id, $src_itm_nm );
if ( exists $tgt->{item}
&& defined $tgt->{item}{name}{value}
&& $tgt->{item}{name}{value} !~ /^\s*$/ )
{
$tgt_itm_nm = $tgt->{item}{name}{value};
$tgt_itm_nm =~ s/[^[:print:]]+/ /;
print "Inserting item $tgt_itm_nm in $tgt_ent_nm\n" if $ENV{DEBUG};
my $dbix_it=$schema->resultset('Item')->create({
name=>$tgt_itm_nm,et_id=> $db{$tgt_ent_nm}{dbix}->id,
seq=> $db{$tgt_ent_nm}{seq}++ });
$db{$tgt_ent_nm}{items}{$tgt_itm_nm}{id} =$dbix_it->id;
for my $attr ( keys %{ $tgt->{item} } ) {
next if $attr eq 'name';
my $attr_val = $tgt->{item}{$attr}{value};
print "Updating item $tgt_itm_nm:$attr with $attr_val\n"
if $ENV{DEBUG} && defined $attr_val;
$dbix_it->$attr($attr_val) &&
$dbix_it->update if defined $attr_val;
}
}
}
}
print "Looking at: Sources\n";
for my $this_sheet (@lkp_sheets) {
print $this_sheet, "\n";
my $worksheet = $workbook->worksheet(qq{$this_sheet});
my $wksht_lkps = $sheets{$this_sheet};
if ( !exists $wksht_lkps->{src} ) {
print "No sources, skipping...\n";
next;
}
my $tgt = $wksht_lkps->{tgt};
my $src = $wksht_lkps->{src};
my ( $row_min, $row_max ) = $worksheet->row_range();
assign_cols( $worksheet, \$wksht_lkps, 'src' );
assign_cols( $worksheet, \$wksht_lkps, 'tgt' );
my %curr_itm;
for my $row ( $row_min + 1 .. $row_max ) {
assign_val( $row, $worksheet, \$wksht_lkps, 'tgt' );
assign_val( $row, $worksheet, \$wksht_lkps, 'src' );
my ( $tgt_itm_nm, $tgt_itm_id, $dbix_src_itm,$src_itm_nm );
#----------------------------------------------
# Only look at this row if we have a tgt
next unless (exists $curr_itm{ent_nm} ||$tgt->{entity}{ds_name}{value})
&& (exists $curr_itm{itm_nm} || $tgt->{item}{name}{value});
if ( $tgt->{entity}{ds_name}{value}
&& $tgt->{item}{name}{value}) {
$curr_itm{ent_nm} = $tgt->{entity}{ds_name}{value};
$curr_itm{itm_nm}=$tgt->{item}{name}{value};
}
$ENV{DEBUG} && print "Current tgt ent: $curr_itm{ent_nm}
item: $curr_itm{itm_nm}\n";
#-----------------------------------------------------
next unless $db{$curr_itm{ent_nm}}{dbix}->id;
next unless $src->{entity}{ds_name}{value}
&& $src->{entity}{ds_name}{value} !~m{n/a}ims;
my $src_ent_nm = $src->{entity}{ds_name}{value};
my $dbix_src_et;
if ( !exists $db{$src_ent_nm}) {
$ENV{DEBUG} && print "Inserting source entity $src_ent_nm\n";
$dbix_src_et=$schema->resultset('Entity')->create({
em_id=>$dbix_em->id,ds_name=>$src_ent_nm});
$db{$src_ent_nm}{dbix} = $dbix_src_et;
$db{$src_ent_nm}{seq} = 1;
}
#-------------------------------------------------
# Tgt and src items
next unless $src->{item}{name}{value}
&& $src->{item}{name}{value} !~m{n/a}ims;
$ENV{DEBUG} && print "Source item:",$src->{item}{name}{value},"\n";
$src_itm_nm = $src->{item}{name}{value};
for my $itm ($src_itm_nm=~/\s*([A-Za-z0-9_ .]+)\s*/gms)
{
if ( !exists $db{$src_ent_nm}{items}{$itm}) {
$ENV{DEBUG} && print "Inserting item $src_itm_nm\n";
my $dbix_src_itm=$schema->resultset('Item')->create({
name=>$itm,et_id=> $db{$src_ent_nm}{dbix}->id,
seq=> $db{$src_ent_nm}{seq}++ });
die "$itm item in $src_ent_nm "
. "did not produce id" unless $dbix_src_itm;
for my $attr ( keys %{ $src->{item} } ) {
next if $attr eq 'name' || $attr eq 'bus_rule';
$dbix_src_itm->$attr($src->{item}{$attr}{value})
&& $dbix_src_itm->update
if defined $src->{item}{$attr}{value};
}
$db{$src_ent_nm}{items}{$itm}{id} = $dbix_src_itm->id;
}
my $dbix_i2i_itm=$schema->resultset('I2i')->create({
tgt_id=>$db{$curr_itm{ent_nm}}{items}{$curr_itm{itm_nm}}{id},
src_id=>$db{$src_ent_nm}{items}{$itm}{id}});
$ENV{DEBUG} && print "Inserting item $src_itm_nm\n
for target item $curr_itm{itm_nm} and ent:
$curr_itm{ent_nm}";
}
}
}
my $cdrs = $schema->resultset('EntityMaster')->search({name=>$col{name}});
$cdrs->update({max_version=>0});
my $rs = $cdrs->search({
version => {
'=' => $cdrs->search({ name=>$col{name}})
->get_column('version')->max_rs->as_query } });
$rs->update({max_version=>1});
$schema->txn_commit;
system 'sync';
__END__
=head1 Rules
- delete from entity_master,entity and item for this version cascading deletes
- Go Through targets
insert db and corresponding items
- Go Through sheets again looking for src -> tgt mappings
insert db and their items which only show as sources
IF entity shows as target
AND entity is listed as source
AND an item for that entity is shown as a source entity item
but not target item
THEN error
insert src entity items
insert i2i
=head1 Spreadsheet conventions
- If more than one item for a source entity they can be in one cell
- If more then one source entity and item then the rows following the first
row can be blank for the target details but each entity with items must
be on its own row
=head1 Testing
select c.name ,count(*)
from entity c, entity_master d
where c.em_id=d.em_id
group by c.name
order by c.name;
select * from entity;
select d.name,d.version,count(distinct c.name)
from entity c, entity_master d
where c.em_id=d.em_id group by d.name,d.version;
=head1 Items per entity master
select d.name,d.version,count(distinct b.name)
from item b, entity c, entity_master d
where b.et_id=c.et_id and c.em_id=d.em_id group by d.name,d.version;
select name,uploaded,version,max_version from entity_master;
select distinct type,scale from item;
select b.name,b.bus_rule
from item b, entity c, entity_master d
where b.et_id=c.et_id and c.em_id=d.em_id
and c.ds_name like '%BASE_ACCT_A%';
select name, version, max_version from entity_master;
select a.* from item a, entity b
where a.et_id=b.et_id and b.name like '%MI_STB_REWARD_ALLOCATION_T%'
order by a.seq;
SELECT me.scope, me.type, me.description
FROM entity me JOIN entity_master em ON em.em_id = me.em_id
WHERE ( ( em.name = 'SMART Rewards'
AND me.name = 'IDMI01.MI_STB_REWARD_ACCT_PURCH_T' ) );
select * from entity
where ds_name like '%BASE_ACCT_T%';
SELECT me.em_id, me.name, me.rel_notes, me.author, me.uploaded, me.version
FROM entity_master me
WHERE version = (
SELECT MAX( iner.version )
FROM entity_master iner
WHERE iner.name = 'FEWR dfsdfs'
)
AND name = 'FEWR dfsdfs';
update entity_master set name = 'CBS Rewards' ;
Jump to Line
Something went wrong with that request. Please try again.