insertProducts

#!/usr/bin/perl

#usage: perl insertProducts.pl inputPsaFileName cfgFilePathpPaddingRequired pPrefix pLength pPadChar numOfCategories

#for example: perl insertProducts.pl prd.txt pog.cfg 1 TGT 140 3

#Input file format: raw .psa file exported from Prospacesoftware.

#    while all the fieldsare separated by comma: entry type,field1,field2,...

#Output format: rowid,required fields separated by comma.like

#Description: This script is used to pre-process the raw .psafile before loading in DataStage jobs.

# The script splits the file into several files based on theentry type (Planogram, Product, Fixture and Position).

#          All the other types ofrecords will be discarded.  

# The pieces of smaller files will be further processed by theDataStage jobs and loaded into Database.

use strict;

use File::Basename;

use File::Spec;

use File::stat;

use DBD::DB2;

my $db_database = 'dbname';

my $db_user = 'dbuser';

my $db_password = 'passwd';

my $dbhDATABASE;

# Expected number of mapping fields in config file

my $numOfPrdCols = 9;

# Check the input parameters

my $requiredParameterNumber = 7; # Please modify thisparameter in case new parameter is added.

my $actualInputParameterNumber = scalar @ARGV;

print "Input parameters: ", (join ' ', @ARGV), "\n"if($requiredParameterNumber == $actualInputParameterNumber) or die"Required number of parameters: $requiredParameterNumber; Actualnumber of parameters: $actualInputParameterNumber.";

# Check the files and directories

my $inFile = shift;

print "Input file: $inFile\n" if(-r $inFile) or die "The$inFile is not readable. <$!>";

my $fileName = basename $inFile;

print "Basename: $fileName\n";

my $cfgFile = shift;

print "Config File: $cfgFile\n" if (-r $cfgFile) or die "The$cfgFile is not readable.<$!>";

# DPCI Prefix string. This is required to prefixing andpadding the DPCI before matching the UPC column in DTOPT.Producttable.

my $pPaddingRequired = shift;

my $pPrefix = shift;

my $pLength = shift;

my $pPadChar = shift;

my $numOfCategories = shift;

if ($numOfCategories < 1) {

$numOfCategories = 1

}

# Start - Read fields Mapping info from config file

my %typesMap = ();

my $type;

my (%pogMap, %fixMap, %posMap, %prdMap);

# Hash key: Record Type; Hash value: Reference of fieldmapping hash.

$typesMap{'Planogram'} = \%pogMap;

$typesMap{'Fixture'} = \%fixMap;

$typesMap{'Position'} = \%posMap;

$typesMap{'Product'} = \%prdMap;

open(FH_CONFIG, "<$cfgFile") || die "Unable toopen the field map config file : $cfgFile<$!>";

while(<FH_CONFIG>) {

    chomp;

    next if /^#/;      # skip comments

    next if /^\s*$/;    # skip empty lines

if(/^\s*\[\s*(\w+)\s*\]\s*$/){# This is the record type block,like [Planogram]

$type = $1;

next;

}

    # Store field name askey, index as value.

if (/^\s*(\w+)\s*=\s*(\w+)\s*$/){ # Format: FieldName =Index

$typesMap{$type}->{$1} = $2;

} 

}

close CONFIG;

# End - Read fields Mapping info from config file

# Index of DPCI field in the array.

my $indDPCIProductInArray; # To be init in functioninitPrdColList();

# Init the array of field index keys

my @prdColList = initPrdColList();

# Get the field indices array of each type of records.

my @prdCol = fieldMap2IndArray(\%prdMap, \@prdColList,$numOfPrdCols, 'Product');

my($PRODUCTBRANDID,$PRODUCTCATEGORYID,$DTCATEGORYID,$PRODUCTDEMANDGROUPID,$NAME,$UPC,$ISPREPRICED,$ISPRIVATELABEL,$SEASONAL,$CASEPACK,$ALLOWBACKHAUL,$ISAPPROVED,$STATUS,$ISACTIVE,$ISSTUB,$EQCFACTOR)=(20256,2,2,49,"InsertForPOGTest","",0,0,0,1,0,1,1,1,0,1);

&dbConnect();

my $stmt;

my $maxProductID;

my @row;

my $dbhSTATEMENT;

my $productID;

my @categoryNames=();

my @categoryIDs=();

$stmt = "select name from dtopt.productcategory fetch first$numOfCategories rows only";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

while(@row =$dbhSTATEMENT->fetchrow_array()){

push @categoryNames, $row[0];

}

$numOfCategories = scalar @categoryNames;

foreach my $categoryName (@categoryNames){

$stmt = "MERGE 

INTO        DTCTASM.ASSORTMENTMODELINGCATEGORY ASAMC 

USING       TABLE (

values  (\'$categoryName\',0,0,'TestingCategory', current date)

) AMCNEW ( NAME, STATUS, ISMODELED, DESCRIPTION,LASTMODELINGDATE )

ON         AMC.NAME   =AMCNEW.NAME 

WHEN MATCHED THEN 

UPDATE 

SET 

(

NAME, STATUS, ISMODELED, DESCRIPTION, LASTMODELINGDATE

)

= ( AMCNEW.NAME, AMCNEW.STATUS, AMCNEW.ISMODELED,AMCNEW.DESCRIPTION, AMCNEW.LASTMODELINGDATE )

WHEN NOT MATCHED THEN 

INSERT 

  (

NAME, STATUS, ISMODELED, DESCRIPTION, LASTMODELINGDATE

  ) 

  VALUES 

  (

AMCNEW.NAME, AMCNEW.STATUS, AMCNEW.ISMODELED,AMCNEW.DESCRIPTION, AMCNEW.LASTMODELINGDATE 

  )";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

$stmt = "select ASSORTMENTMODELINGCATEGORYID fromdtctasm.ASSORTMENTMODELINGCATEGORY whereNAME=\'$categoryName\'";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

@row = $dbhSTATEMENT->fetchrow_array();

push @categoryIDs, $row[0];

}

$numOfCategories = scalar @categoryIDs;

$stmt = "select DTCATEGORYID from DTOPT.DTCATEGORY fetch first1 row only";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

@row = $dbhSTATEMENT->fetchrow_array();

$DTCATEGORYID = $row[0];

$stmt = "select PRODUCTBRANDID from DTOPT.PRODUCTBRAND fetchfirst 1 row only";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

@row = $dbhSTATEMENT->fetchrow_array();

$PRODUCTBRANDID = $row[0];

$stmt = "select PRODUCTCATEGORYID from DTOPT.PRODUCTCATEGORYfetch first 1 row only";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

@row = $dbhSTATEMENT->fetchrow_array();

$PRODUCTCATEGORYID = $row[0];

$stmt = "select PRODUCTDEMANDGROUPID fromDTOPT.PRODUCTDEMANDGROUP fetch first 1 row only";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

@row = $dbhSTATEMENT->fetchrow_array();

$PRODUCTDEMANDGROUPID = $row[0];

# Open the files

open(FH, "<$inFile") || die "Unable to open thefile : $inFile <$!>";

print "Start processing the file: $fileName..\n";

my @products=();

while(<FH>){

chomp;

if (/^Product,.*/){

my @productArray = (split /,/)[@prdCol[0..$#prdCol]];

# Prefix the DPCI(ID) field with $pPrefix

if($productArray[$indDPCIProductInArray]){

$UPC = prefix($productArray[$indDPCIProductInArray]);

$stmt = "select max(productid) from dtopt.product";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

@row = $dbhSTATEMENT->fetchrow_array();

$maxProductID= $row[0];

$productID = $maxProductID +1;

$stmt = "select productid from dtopt.product whereupc=\'$UPC\'";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

@row = $dbhSTATEMENT->fetchrow_array();

my $existingProductID= $row[0];

#print "Found an existing product with UPC: $UPC, ProductID:$existingProductID.\n" if($existingProductID);

$productID = $maxProductID +1;

if(!$existingProductID){

$stmt = "insert intodtopt.product(PRODUCTID,PRODUCTBRANDID,PRODUCTCATEGORYID,DTCATEGORYID,PRODUCTDEMANDGROUPID,NAME,UPC,ISPREPRICED,ISPRIVATELABEL,SEASONAL,CASEPACK,ALLOWBACKHAUL,ISAPPROVED,DATERECEIVED,STATUS,ISACTIVE,ISSTUB,EQCFACTOR)values($productID,$PRODUCTBRANDID,$PRODUCTCATEGORYID,$DTCATEGORYID,$PRODUCTDEMANDGROUPID,\'$NAME\',\'$UPC\',$ISPREPRICED,$ISPRIVATELABEL,$SEASONAL,$CASEPACK,$ALLOWBACKHAUL,$ISAPPROVED,currentdate,$STATUS,$ISACTIVE,$ISSTUB,$EQCFACTOR)";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

print "Inserting UPC: $UPC..\n" ;

$dbhSTATEMENT->execute();

$stmt = "select productid from dtopt.product whereupc=\'$UPC\'";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

@row = $dbhSTATEMENT->fetchrow_array();

$existingProductID = $row[0];

push @products, $UPC;

}

$stmt = "select ASSORTMENTMODELINGCATEGORYID fromdtctasm.ASSORTMENTMODELINGCATEGORYDETAIL whereproductid=$existingProductID";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

@row = $dbhSTATEMENT->fetchrow_array();

my $existingASSORTMENTMODELINGCATEGORYID = $row[0];

if(!$existingASSORTMENTMODELINGCATEGORYID){

#print "Inserting intodtctasm.ASSORTMENTMODELINGCATEGORYDETAIL..\n";

my $categoryID = $categoryIDs[int(rand(@categoryIDs))%(scalar@categoryIDs)];

$stmt = "insert intodtctasm.ASSORTMENTMODELINGCATEGORYDETAIL(ASSORTMENTMODELINGCATEGORYID,productid)values ($categoryID,$existingProductID)";

$dbhSTATEMENT =$dbhDATABASE->prepare($stmt);

$dbhSTATEMENT->execute();

}

}

}

# Ignore all other type of records.

}

print "Complete processing the file: $fileName.\n";

# Config the final Output field list here. The fields ordermust be the same as the DataStage input file's.

# Any changes in the DataStage input interface should bereflected here.

sub initPrdColList{

$indDPCIProductInArray = 0; # The index of DPCI field. Indexstarts from 0.

return ('DPCI', 'Width', 'Height', 'Depth', 'TrayWidth','TrayHeight', 'TrayDepth', 'TrayPack', 'CasePack');

}

# Convert Field Mapping Index to Array. This function willaccept 4 parameters.

# 1. scalar reference to a field mapping hash

# 2. scalar reference to a field keys array

# 3. expected number of field

# 4. record type: 'Planogram', 'Fixture', 'Position' or'Product'.

sub fieldMap2IndArray {

my ($colMap, $colList, $expectedNum, $recType) = @_;

# To do: check the total count of the fields of each type ofrecords, if the count is not equal to the expected number, thenquit with error.

$expectedNum == (keys %$colMap) or die "Number of $recTypeMapping fields in config file<$cfgFile> is not as expected!Expected: $expectedNum; Actual: ", scalar(keys %$colMap),".";

my @indArray;

foreach my $key (@$colList){

push @indArray, $colMap->{$key};

}

@indArray;

}

# Prefix the passed-in string

sub prefix{

my $str = shift;

$str = trim($str);

if ($pPaddingRequired) {

if ($pLength - length($str) > 0) {  

$str = $pPrefix.($pPadChar x ($pLength- length($str) -length($pPrefix))).$str;

}

} 

else { 

$str = $pPrefix.$str;

}

$str;

}

sub dbConnect(){

my $sthDB2;

my $dbhDB2;

my $dbhSTATEMENT;

my @row;

print "\nConnecting to $db_database user $db_user using$db_password\n";

$dbhDATABASE = DBI->connect("dbi:DB2:$db_database","$db_user","$db_password" ) || die "Cannotconnect to DB2.\n";

}

sub trim{

my $string = shift;

$string =~ s/^\s+//;

$string =~ s/\s+$//;

return $string;

}