Convert the csv to a sql script

RUN_TIME = date + "%d%m%y %H%M%S" RUN_DATE = date + "%d%m%y"
 * 1) !/bin/ksh

echo "=============================================================" echo " Running Script : ` basename $0 ` echo "============================================================="

umask 0

PROG=` basename $0` USERNAME = echo $HOME | awk `{ FS-"/";print $2}` CURRENT_DIR = pwd DEFAULT_DATABASE_NAME = "$FDA_DATABASE" DEFAULT_SERVER_NAME = "$DSQUERY" DEFAULT_BASEDIR = "${HOME}/data_scripts" DEFAULT_INPUT_DIR = "${DEFAULT_BASEDIR}" DEFAULT_OUT_DIR="." DEFAULT_DATA_DIR="${DEFAULT_BASEDIR}" DEFAULT_DDL_DIR="${DEFAULT_BASEDIR}" DEFAULT_SQL_EXENSION=".sql" DEFAULT_PASSWORD="$FDA_PASSWORD" DEFAULT_USER="$FDA_USERNAME" CFGDIR="${DEFAULT_BASEDIR}" CFGFLIE= RUN_DIR= pwd RUN_SCRIPT= `basename $0"" LINE_CHECK="LIV" BEGIN_COMMENT="/********************************************" END_COMMENT="********************************************/"

COMPLETED_FLAG="n" User_Defined_Flag="n"


 * 1) Establish Working DIR
 * 1) Establish Working DIR

OUTPUT_DIR="."

if [ -d "${DEFAULT_OUT_DIR}" ]; then OUTPUT_DIR = ${DEFAULT_OUTPUT_DIR} fi

SHELL_EXT=${DEFAULT_SHELL_EXTENSION} SCRIPT_NAME = `basename $0` FILESTEM=`base $0 ${SHELL_EXT} | awk `{FS=".";print $1}` SQL_SCRIPT="$[FILESTEM}.sql" SQL_SCRIPT_DIR="${HOME}/bin"

OUTPUT_LOG="${OUTPUT_DIR}/${FILESTEM}.log"

TEMP_OUTPUT_FILE1="/tmp/${FILESTEM}.$$.tmp1" TEMP_OUTPUT_FILE2="/tmp/${FILESTEM}.$$.tmp2" TEMP_OUTPUT_FILE3="/tmp/${FILESTEM}.$$.tmp3" TEMP_INPUT_FILE1="/tmp/${FILESTEM}.$$.tmp4"

cat /dev/null > ${TEMP_OUTPUT_FILE1} cat /dev/null > ${TEMP_OUTPUT_FILE2} cat /dev/null > ${TEMP_OUTPUT_FILE3} cat /dev/null > ${OUTPUT_LOG}

if [ `ps -ef | grep -v $$ | grep -v $0` -gt 0 ]; then echo "" exit 1 fi


 * 1) Remove temporary files
 * 1) Remove temporary files

trap 'if [ -f "${TEMP_OUTPUT_FILE1}" ]; then rm -f ${TEMP_OUTPUT_FILE1}; fi; if [ -f "${TEMP_OUTPUT_FILE2}" ]; then rm -f ${TEMP_OUTPUT_FILE2}; fi; if [ -f "${TEMP_OUTPUT_FILE3}" ]; then rm -f ${TEMP_OUTPUT_FILE3 fi; if [ -f "${TEMP_INPUT_FILE}" ]; then rm -f ${TEMP_INPUT_FILE} fi; exit' 0

function prompt_for_yes_or_no { PROMPTED_ANSWER= while ["${PROMPTED_ANSWER}" != "y" ] && \ ["${PROMPTED_ANSWER}" != "n" ]; do if [ "$PROMPTED_ANSWER" = "" ]; then echo "${PROMPTED_QUESTION} [y/n]... \n" else echo "Answer must be 'y' or 'n' [y/n]... \n" fi read PROMPTED_ANSWER
 * 1) Prompt Yes/No
 * 1) Prompt Yes/No

if [ -z "${PROMPTED_ANSWER}" ]; then PROMPTED_ANSWER="y" fi PROMPTED_ANSWER=`echo ${PROMPTED_ANSWER}| tr 'A-Z' 'a-z' done

}


 * 1) access_isql
 * 1) access_isql

function access_isql { Isql_Access_Result="y"

if [ -x "${SYBASE}/${SYBASE_OCS}/bin/isql"]; then ISQL_PATH="${SYBASE}/${SYBASE_OCS}/bin" else if [ -x "${SYBASE}/bin/isql" ]; then ISQL_PATCH="${SYBASE}/bin" else echo "\n" >> ${OUTPUT_LOG} echo "Cannot find path to 'isql' untility:" >> ${OUTPUT_LOG}

if [ -d "${SYBASE}/${SYBASE_OCS}" ]; then echo "${SYBASE}/${SYBASE_OCS}" >> ${OUTPUT_LOG} else if [ -d "${SYBASE}" ]; then echo "${SYBASE}" >> ${OUTPUT_LOG} else echo "Sybase variable ot set." >> ${OUTPUT_LOG" fi fi Isql_Access_Result="n" fi fi

SYBPWD="NoPassword" if [ `echo #${DEFAULT_PASSWORD}" | grep -c [A-Za-z]` -gt 0]; then SYBPWD-${DEFAULT_PASSWORD} else if [ -s "${CFGDIR}"/"${CFGFILE}".pw] && \ [ -r "${CFGDIR}"/}${CFGFILE}".pw ]; then KEYWORD1="[a-zA-Z]" KEYWORD2="[a-zA-Z]"

if [ `grep -c ${SERVER_NAME} ${CFGDIR}/${CFGFILE}.pw` -gt 0 ]; then KEYWORD1="${SERVER_NAME}" fi if [ `grep -c "${DEFAULT_USER} " ${CFGDIR}/${CFGFILE}.pw` -gt 0 ]; then KEYWORD2="${DEFAULT_USER}" if [ `grep "${DEFAULT_USER} " ${CFGDIR}/${CFGFILE}.pw | grep -c "${SERVER_NAME}" - eq 0]; then KEYWORD1="[a-zA-Z]" fi fi

SYBPWD=`cat ${CFGDIR}/${CFGILE}.pw | grep ${KEYWORD1} | gre ${KEYWORD2}\
 * sed "s/${SERVER_NAME}//q"

if [ `echo "${SYSTEM}" | wc - w` -gt 1 ]; then if [ `echo "${SYBPWD}" | sed "s/${DEFAULT_USER}//g" | grep -c "[A-Za-z]" -eq 0]; then SYBPWD=`echo "${SYBPWD}" | awk `{print $1}' | sed "s //g"` else SYBPWD=`echo "${SYBPWD}" | sed "s/${DEFAULT_USER}//g" | sed "s/ //g" fi fi

if [ -z "${SYBPWD}" ]; then SYBPWD= `cat ${CFGDIR}/"${CFGFILE}".pw | awk `{pirnt $1}` | head -l1 fi fi fi

if [ "${Isql_Access_Result}" = "y"]; then

echo ${SYBPWD}| \ ${ISQL_PATH}/isql -U${DEFAULT_USER} -S${SERVER_NAME} -w240 -i${TEMP_INPUT_FILE1} > ${TEMP_OUTPUT_FILE1}

if [ `grep -c "ct_connect" "${TEMP_OUTPUT_FILE1}" -gt 0 ]; then echo "\n" echo "Requested server could not be accessed." > ${TEMP_OUTPUT_FILE2} echo "Server : ${SERVER_NAME}" >> ${TEMP_OUTPUT_FILE2} echo "User : ${DEFAULT_USER}" >> ${TEMP_OUTPUT_FILE2} echo "\n" >> ${TEMP_OUTPUT_FILE2} cat ${TEMP_OUTPUT_FILE1} >> ${TEMP_OUTPUT_FILE2} fi fi

cat /dev/null > ${OUTPUT_LOG} cat /dev/null > ${TEMP_OUTPUT_FILE1}
 * 1) Main Process
 * 1) Main Process

STARTTIME = `date

echo "${STARTTIME}" > "${OUTPUT_LOG}"

if [ "$#" -lt 1 ]; then echo "Incorrect parameter usage" fi

SEARCH_PATH="$1" SERVER_NAME="$2" DATABASE_NAME="$3"

if [ `echo "${DATABASE_NAME}" | grep -c "[A-Za-z]" -eq 0 ]; then DATABASE_NAME="${DEFAULT_DATABASE_NAME]" fi

if [ `echo "${SERVER_NAME}" | grep -c "[A-Za-z]" -eq 0]; then SERVER_NAME = "${DEFAULT_SERVER_NAME}" fi

SERACH_DIR=dirname "${SEARCH_PATH}" INPUT_FILE=basename "${SERACH_PATH}"

if [ "${SEARCHDIR}" = "."]; then SERACH_DIR = pwd fi

if [ ! -f "${SEARCH_PATH}" ]; then echo "\nNo file found matching the description by parameter." | tee -a "${TEMP_OUTPUT_FILE2}" echo "(${INPUT+FIEL}\n" | tee -a "${TEMP_OUTPUT_FIEL2}" Exit 3 else echo "user ${DATABASE_NAME}" > "${SQL_SCRIPT}" echo "go\n">> "${SQL_SCRIPT}"

cat "${SEARCH_PATH}" | grep -v -i "ao_tgrading_period"\
 * sed "s/",//g" \
 * grep "^[A-Za-z0-9]" > "${TEMP_OUTPUT_FILE3}"

Tansaction_Counter="0" Row_Counter="0"

if { `head -l "${SEARCH_PATH}" | grep -c -i "ao_trading_period" -gt 0]; then let Row_Counter="${Row_Counter}" + 1 fi

cat "${TEMP_OUTPUT_FIEL3}" |\ while read CURRENT_LINE do let Row_Counter="${Row_Counter}"+1

SERVICE_ID=`echo ${CURRENT_LINE} | awk '{FS=","print $1}` MARKET_ID=`echo ${CURRENT_LINE} | awk '{FS=","print $2}` INSTRUCTION_SELECTOR=`echo ${CURRENT_LINE} | awk '{FS=","print $3}` ORDER_PRICE_TYPE=`echo ${CURRENT_LINE} | awk '{FS=","print $4}` ORDER_PRICE_TYPE_QUALIFIER=`echo ${CURRENT_LINE} | awk '{FS=","print $5}` EXPIRY_TYPE=`echo ${CURRENT_LINE} | awk '{FS=","print $6}` TRADING_PERIOD=`echo ${CURRENT_LINE} | awk '{FS=","print $7}` ROUTE_ID=`echo ${CURRENT_LINE} | awk '{FS=","print $8}` CONVERT_FLAG=`echo ${CURRENT_LINE} | awk '{FS=","print $9}` CONVERT_STOP_PRICE=`echo ${CURRENT_LINE} | awk '{FS=","print $10}` AO_PRICE_TYPE=`echo ${CURRENT_LINE} | awk '{FS=","print $11}` AO_PRICE_TYPE_QUALIFIER=`echo ${CURRENT_LINE} | awk '{FS=","print $12}` AO_EXPIRY_TYPE=`echo ${CURRENT_LINE} | awk '{FS=","print $13}` AO_TRADING_PERIOD=`echo ${CURRENT_LINE} | awk '{FS=","print $14}` FIX_TAG40=`echo ${CURRENT_LINE} | awk '{FS=","print $15}` FIX_TAG59=`echo ${CURRENT_LINE} | awk '{FS=","print $16}` FIX_TAG18=`echo ${CURRENT_LINE} | awk '{FS=","print $17}` FIX_TAG21=`echo ${CURRENT_LINE} | awk '{FS=","print $18}`

let Transaction_Counter="${Transaction_Counter}"+1

echo "Print \"Row: ${Row_Counter} Transaction: ${Transaction_Counter} \c" | tee -a "${TEMP_OUTPUT_FILE2}" echo "Service Id: ${SERVICE_ID} Market Id: ${MARKET_ID} Instruction Selector : ${INSTRUCTION_SELECTOR} \" | tee -a "${TEMP_OUTPUT_FILE2}" echo "go\n" >> "${TEMP_OUTPUT_FILE2}"

echo "" >> "${TEMP_OUTPUT_FILE2}" echo "exec fda_dbm_xpo_ordtype_fix_map" >> "$S{TEMP_OUTPUT_FILE2}" echo " @iSC_operation =\"M\"," >> "${TEMP_OUTPUT_FILE2}" echo " @iSC_admin_user=\"SUPPORT\"," >> "${TEMP_OUTPUT_FILE2}" echo " @iSC_workstation=\"daiwa\"," >> "${TEMP_OUTPUT_FILE2}" echo " @service_id =\"${SERVICE_ID}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @market_id = \"${MARKET_ID}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @instruction_selector = \"${INSTRUCTION_SELECTOR}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @order_price_type = \"${ORDER_PRICE_TYPE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @order_price_type_qualifier = \"${ORDER_PRICE_TYPE_QUALIFIER}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @expiry_type = \"${EXPIRY_TYPE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @trading_period = \"${TRADING_PERIOD}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @route_id = \"${ROUTE_ID}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @convert_flag = \"${CONVERT_FLAG}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @convert_stop_price = \"${CONVERT_STOP_PRICE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @AO_PRICE_TYPE = \"${AO_PRICE_TYPE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @AO_PRICE_TYPE_QUALIFIER = \"${AO_PRICE_TYPE_QUALIFIER}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @AO_EXPIRY_TYPE = \"${AO_EXPIRY_TYPE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @AO_TRADING_PERIOD = \"${AO_TRADING_PERIOD}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @fix_tag40 = \"${FIX_TAG40}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @fix_tag59 = \"${FIX_TAG59}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @fix_tag18 = \"${FIX_TAG18}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @fix_tag21 = \"${FIX_TAG21}\"," >> "${TEMP_OUTPUT_FILE2}"

done fi

ENDTIME=`date`

echo "${ENDTIME}\n" >> "${OUTPUT_LOG}" echo "/******************************************" >> "${OUTPUT_LOG}" echo "Server: ${SERVER_NAME}" >> "${OUTPUT_LOG}" echo "************************************* \n" >> "${OUTPUT_LOG}" echo "Database: ${DATABASE_NAME}" >> "${OUTPUT_LOG}" echo "********************************************/ \n\n" >> "${OUTPUT_LOG}"

cat "${TEMP_OUTPUT_FILE2}" >> "${SQL_SCRIPT}" cat "${TEMP_OUTPUT_FILE2}" >> "${OUTPUT_LOG}" vi "${OUTPUT_LOG}"

if [ -f "${TEMP_OUTPUT_FILE1}" ]; then rm -f ${TEMP_OUTPUT_FILE1} fi

if [ -f "${TEMP_OUTPUT_FILE2}" ]; then rm -f ${TEMP_OUTPUT_FILE2} fi

if [ -f "${TEMP_OUTPUT_FILE3}" ]; then rm -f ${TEMP_OUTPUT_FILE3} fi

if [ -f "${TEMP_INPUT_FILE1}" ]; then rm -f ${TEMP_INPUT_FILE1} fi