Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
tree: 82e4d68cb4
Fetching contributors…

Cannot retrieve contributors at this time

372 lines (327 sloc) 13.152 kb
#!/usr/bin/env perl
use warnings;
use strict;
use Spreadsheet::ParseExcel;
use Data::Dumper;
$Data::Dumper::Indent = 2;
$|++;
BEGIN {
use lib qq{$ENV{MIS}/lib};
use X_Db;
use X_Db::Schema;
}
my $schema = X_Db::Schema->connect ( sub {X_Db::connect('MIS') });
# $schema->deploy({ add_drop_table => 1});
# __END__
# print Dumper $schema->resultset('EntDetVw')
# ->search({}, { bind => [ 'IDMI01.MI_STB_REWARD_ALLOCATION_T' ] })
# ->all();
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} eq $val ) {
$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( $ARGV[0] );
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}{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}{name}{value};
#-----------------------------------------------------
# Row consistency checks
my $tgt_ent_nm = $tgt->{entity}{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}{name}{value}\n";
$ENV{DEBUG} && print "Going to insert into entity:$tgt_ent_nm";
$db{$tgt_ent_nm}{dbix}=$schema->resultset('Entity')->create({
name=>$tgt_ent_nm,em_id=>$dbix_em->id});
$db{$tgt_ent_nm}{seq} = 1;
}
$tgt_ent_nm = $tgt->{entity}{name}{value};
for my $attr ( keys %{ $tgt->{entity} } ) {
next if $attr eq '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}
&& $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}{name}{value})
&& (exists $curr_itm{itm_nm} || $tgt->{item}{name}{value});
if ( $tgt->{entity}{name}{value}
&& $tgt->{item}{name}{value}) {
$curr_itm{ent_nm} = $tgt->{entity}{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}{name}{value}
&& $src->{entity}{name}{value} !~m{n/a}ims;
my $src_ent_nm = $src->{entity}{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,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 and d.version=0.04
group by c.name
order by c.name;
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;
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 from entity_master;
select count(*) from item where name='MAN_FUEL_PURCH_AMT';
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.name = 'CAMPAIGN_STB_REWARDS_YYYYMMDDHHMM.txt';
select name,version,max_version from entity_master;
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';
Jump to Line
Something went wrong with that request. Please try again.