dbuser=SCOTT/TIGER
dbname=PRODDB
connect_string=$dbuser@$dbname
tableowner="DEVS"
TABLEOWNER=$(echo $tableowner | tr '[a-z]' '[A-Z]')

#. ${MMHOME}/config.env
OUT_DIR=/inbox/data

# Set it to number of CPU on the server
MAX_THREAD=12
TASK_RUNNING=0
PROGRAM_NAME='Store_Source'

get_partname()
{
  sqlplus -s $connect_string <<!
  set pages 0
  set feedback off
  set heading off
  SELECT PARTITION_NAME, PARTITION_POSITION
      FROM  DBA_TAB_PARTITIONS
       WHERE TABLE_NAME='ITEM_LOC'
      ORDER BY PARTITION_POSITION;
  exit;
!
}

create_part_flow()
{
cat > sorc/outFlow_${PROGRAM_NAME}_$1.xml - <<EOF1

export PROGRAM_NAME="item_sale_$1"

. \${MMHOME}config.env
. \${LIB_DIR}/lib.ksh


message "Program started ..."

\${RE_EXE} \${RE_OPTIONS} << EOF

<FLOW name="output.flow">
  \${DBREAD}
 <PROPERTY name="query">
      <![CDATA[
SELECT <Stmt>
   ]]>
    </PROPERTY>
    <OPERATOR type="convert">
         <PROPERTY name="convertspec">
            <![CDATA[
               <CONVERTSPECS>
                  <CONVERT destfield="Item_no" sourcefield="Item_no" newtype="int16">
                     <CONVERTFUNCTION name="int16_from_dfloat"/>
                     <TYPEPROPERTY name="nullable" value="false"/>
                  </CONVERT>
               </CONVERTSPECS>
            ]]>
         </PROPERTY>
         <OUTPUT name="temp.v"/>
   </OPERATOR>
   </OPERATOR>
   <OPERATOR type="export">
    <INPUT name="temp.v"/>
    <PROPERTY name="outputfile" value="\${DATA_DIR}/src_i_$1.dat"/>
    <PROPERTY name="schemafile" value="\${SCHEMA_DIR}/item_on_off_sale.schema"/>
  </OPERATOR>
</FLOW>
EOF

checkerror -e $? -m "Program failed - check \${ERR_FILE}"

# Remove the status file
if [[ -f \${STATUS_FILE} ]]; then rm \${STATUS_FILE}; fi

message "Program completed successfully"

dbuser=SCOTT/TIGER
dbname=ORACLE_DEV
connect_string=$dbuser@$dbname
tableowner="PRODDB"
TABLEOWNER=$(echo $tableowner | tr '[a-z]' '[A-Z]') 

#. ${MMHOME}/config.env
OUT_DIR=/inbox/data

# Set it to number of CPU on the server
MAX_THREAD=12
TASK_RUNNING=0
PROGRAM_NAME='Store_Source'

get_partname()
{
  sqlplus -s $connect_string <<!
  set pages 0
  set feedback off
  set heading off
  SELECT PARTITION_NAME, PARTITION_POSITION
      FROM  DBA_TAB_PARTITIONS
       WHERE TABLE_NAME='ITEM_LOCATION'
      ORDER BY PARTITION_POSITION;
  exit;
!
}

create_part_flow()
{
cat > source/outFlow_${PROGRAM_NAME}_$1.xml - <<EOF1

export PROGRAM_NAME="item_sale_$1"

. \${MMHOME}config.env
. \${LIB_DIR}/lib.ksh


message "Program started ..."

\${RETAIL_EXE} \${RETAIL_OPTIONS} << EOF

<FLOW name="output.flow">
  \${DBREAD}
    <PROPERTY name="query">
      <![CDATA[
SELECT 
          itl.loc                        store,
          itl.item                       sku,
          v.case_size                    order_pack,
          itl.onsale_date                onsale_date,
          itl.offsale_date               offsale_date,
          iem.status                     status
 FROM     item_mas       iem,
          (SELECT * from ITEM_LOCATION partition ($2)) itl,
          store             s,
          v_case_size       v   
 WHERE    iem.pack_ind                   = 'N'
   AND    iem.item_level                 = iem.tran_level
   AND    iem.status                     = 'A'
   AND    iem.forecast_ind               = 'Y'
   AND    iem.item                       = itl.item
   AND    itl.loc_type                   = 'S'
   AND    iem.item                       = vcs.item
   
   AND NVL(s.STORE_CLOSE_DATE,'01-JAN-9999')>=TO_DATE('\${ITEMDATE}', 'YYYYMMDD')       
      ]]>
    </PROPERTY>
    <OPERATOR type="convert">
         <PROPERTY name="convertspec">
            <![CDATA[
               <CONVERTSPECS>
                  <CONVERT destfield="ORDER_PACK" sourcefield="ORDER_PACK" newtype="int16">
                     <CONVERTFUNCTION name="int16_from_dfloat"/>
                     <TYPEPROPERTY name="nullable" value="false"/>
                  </CONVERT>
               </CONVERTSPECS>
            ]]>
         </PROPERTY>
         <OUTPUT name="temp.v"/>
   </OPERATOR>
   </OPERATOR>
   <OPERATOR type="export">
    <INPUT name="temp.v"/>
    <PROPERTY name="outputfile" value="\${DATA_DIR}/dm0_src_i_$1.dat"/>
    <PROPERTY name="schemafile" value="\${SCHEMA_DIR}/item_on_off_sale.schema"/>
  </OPERATOR>
</FLOW>
EOF

checkerror -e $? -m "Program failed - check \${ERR_FILE}"

# Remove the status file
if [[ -f \${STATUS_FILE} ]]; then rm \${STATUS_FILE}; fi

message "Program completed successfully"

# cleanup and exit
items_terminate 0

EOF1
}

create_nopart_flow()
{
cat > outFlow_${PROGRAM_NAME}.xml - <<EOF
<FLOW name="output.flow">
  <OPERATOR type="oraread">
    <PROPERTY name="query">
      <![CDATA[
           <![CDATA[
SELECT 
          itl.loc                        store,
          itl.item                       sku,
          v.case_size                    order_pack,
          itl.onsale_date                onsale_date,
          itl.offsale_date               offsale_date,
          iem.status                     status
 FROM     item_mas       iem,
          (SELECT * from ITEM_LOCATION partition ($2)) itl,
          store             s,
          v_case_size       v   
 WHERE    iem.pack_ind                   = 'N'
   AND    iem.item_level                 = iem.tran_level
   AND    iem.status                     = 'A'
   AND    iem.forecast_ind               = 'Y'
   AND    iem.item                       = itl.item
   AND    itl.loc_type                   = 'S'
   AND    iem.item                       = vcs.item
   
   AND NVL(s.STORE_CLOSE_DATE,'01-JAN-9999')>=TO_DATE('\${ITEMDATE}', 'YYYYMMDD')
            ]]>
    </PROPERTY>
    <OUTPUT name="temp.v"/>
  </OPERATOR>

  <OPERATOR type="export">
    <INPUT name="temp.v"/>
    <PROPERTY name="outputfile" value="dm0_src_i_$1.dat"/>
  </OPERATOR>
</FLOW>
EOF
}

create_full_flow()
{
cat > source/item_sale_multi.xml << EOF1

export PROGRAM_NAME="item_sale_multi"

. \${MMHOME}/config.env
. \${LIB_DIR}/lib.ksh

ON_SALE_OUTPUT_FILE=\${DATA_DIR}/dm0_onseffdt_.txt
ON_SALE_OUTPUT_SCHEMA=\${SCHEMA_DIR}/item_on_sale.schema
OFF_SALE_OUTPUT_FILE=\${DATA_DIR}/dm0_ofseffdt_.txt
OFF_SALE_OUTPUT_SCHEMA=\${SCHEMA_DIR}/item_off_sale.schema

#export RFX_DEBUG=1

# input files:

ONOFF_DATE=\${DATA_DIR}/dm0_onseffdt_ofseffdt.dat
ONOFF_DATE_SCHEMA=\${SCHEMA_DIR}/item_on_off_sale.schema

message "Program started ..."

\${RETAIL_EXE} \${RETAIL_OPTIONS} << EOF

<FLOW name="Store_Source.flw">

   <OPERATOR  type="import">
      <PROPERTY  name="inputfile" value="\${ONOFF_DATE}"/>
      <PROPERTY  name="schemafile" value="\${ONOFF_DATE_SCHEMA}"/>
       <OPERATOR type="copy">
         <OUTPUT name="on_off_sale.v"/>
       </OPERATOR>
   </OPERATOR>

<!--START_INS_C2.1-->
   <OPERATOR type="copy">
      <INPUT  name="on_off_sale.v"/>
      <OUTPUT name="on_sale_out.v"/>
      <OUTPUT name="off_sale_out.v"/>
   </OPERATOR>
<!--END_INS_C2.1-->

   <OPERATOR type="fieldmod">
      <INPUT name="on_sale_out.v"/>
<!--START_DIS_C2.1
      <PROPERTY name="rename" value="ON_SALE_EFFECTIVE_DATE=STATUS_UPDATE_DATE"/>
END_DIS_C2.1-->

<!--START_INS_C2.1-->
      <PROPERTY name="rename" value="ON_SALE_EFFECTIVE_DATE=ONSALE_DATE"/>
<!--END_INS_C2.1-->

      <OPERATOR type="export">
         <PROPERTY name="outputfile" value="\${ON_SALE_OUTPUT_FILE}"/>
         <PROPERTY name="schemafile" value="\${ON_SALE_OUTPUT_SCHEMA}"/>
      </OPERATOR>
   </OPERATOR>

   <OPERATOR type="fieldmod">
      <INPUT    name="off_sale_out.v"/>
<!--START_DIS_C2.1
      <PROPERTY name="rename" value="OFF_SALE_EFFECTIVE_DATE=STATUS_UPDATE_DATE"/>
END_DIS_C2.1-->

<!--START_INS_C2.1-->
      <PROPERTY name="rename" value="OFF_SALE_EFFECTIVE_DATE=OFFSALE_DATE"/>
<!--END_INS_C2.1-->
      <OPERATOR type="export">
         <PROPERTY name="outputfile" value="\${OFF_SALE_OUTPUT_FILE}"/>
         <PROPERTY name="schemafile" value="\${OFF_SALE_OUTPUT_SCHEMA}"/>
      </OPERATOR>
   </OPERATOR>
</FLOW>
EOF

checkerror -e 0 -m "Program failed - check ${ERR_FILE}"

# Remove the status file
if [[ -f \${STATUS_FILE} ]]; then rm \${STATUS_FILE}; fi

message "Program completed successfully"

# cleanup and exit
items_terminate 0
EOF1
}

# Remove all individual xml files
rm outFlow_${PROGRAM_NAME}*.xml


NO_OF_PART=`get_partname $TABLEOWNER |wc -l`
if [ ${NO_OF_PART} -eq 0 ]; then
  echo Table not partitioned
  create_nopart_flow 
  time retail -c ${1} -f outFlow_${PROGRAM_NAME}.xml
else
  echo Table partitioned
  get_partname $TABLEOWNER |while read PARTNAME THREAD_SEQ 
  do
     echo PartName: $PARTNAME   POS: $THREAD_SEQ
     create_part_flow $THREAD_SEQ  ${PARTNAME} 
     time /scripts/retail_aim.sh outFlow_${PROGRAM_NAME}_${THREAD_SEQ}.xml&
     echo Running outFlow_${PROGRAM_NAME}_${THREAD_SEQ}.xml ....
     sleep 1
     #TASKS_RUNNING=`ps -ef|grep retail|grep -v java|grep -v grep|wc -l`
     TASKS_RUNNING=`ps -ef |grep ${PROGRAM_NAME} |grep -v java|grep -v grep |wc -l`
    echo Running: ${TASKS_RUNNING} Max: ${MAX_THREAD}
     while [ "${TASKS_RUNNING}" -ge "${MAX_THREAD}" ]; do
        sleep 2
        TASKS_RUNNING=`ps -ef |grep ${PROGRAM_NAME} |grep -v java|grep -v grep |wc -l`
     done
  done
  wait
  #Concatenate all files
  cat ${OUT_DIR}/dm0_src_i_*.dat > ${OUT_DIR}/dm0_onseffdt_ofseffdt.dat
  if [ $? -eq 0 ]; then
     rm  ${OUT_DIR}/dm0_src_i_*.dat
  fi
  create_full_flow

  time scripts/retil_aim.sh item_sale_multi.xml 
fi

# Set it to number of CPU on the server
MAX_THREAD=12