#局方一共有200多条用户信息需要添加在web界面，写此脚本目的是为了给本地外籍员工减少工作量。后因据说售后技术支持没权限写脚本操作数据库，尤其是增减操作，于是当作练手，并记录于此。
#需将客户提供的excel文件转化为.csv格式
#需切换到postgres用户进行脚本运行
#!/bin/bash
declare -i row_num=1
cat usr_info_list.csv | while read line
do
if test $row_num -eq 1
then
row_num+=1
else
user_name=$(awk -F "\"*,\"*" ‘{print $1}‘ usr_info_list.csv | sed -n $row_num‘p‘)
email=$(awk -F "\"*,\"*" ‘{print $2}‘ usr_info_list.csv | sed -n $row_num‘p‘)
phone_number=$(awk -F "\"*,\"*" ‘{print $3}‘ usr_info_list.csv | sed -n $row_num‘p‘)
operatorid=$(psql -U zxdbm_830 -d zxin -c "select operatorid from oper_information where opername=‘$user_name‘;" )
ARR_OPERATORID=($operatorid)
#judge whether username is valid
if test ${ARR_OPERATORID[2]} = ‘(0‘
then
echo "user:$user_name is not exist"
else
echo "${ARR_OPERATORID[2]}"
psql -U zxdbm_830 -d zxin -c "update oper_information set telephone=$phone_number where opername=‘$user_name‘;"
# judge whether the email exist,if exist then update,else insert.
mailbox=$(psql -U zxdbm_830 -d zxin -c "select mailbox from oper_mail where operatorid=${ARR_OPERATORID[2]};" )
ARR_MAILBOX=($mailbox)
if test ${ARR_MAILBOX[2]} = ‘(0‘
then
psql -U zxdbm_830 -d zxin -c "insert into oper_mail values(${ARR_OPERATORID[2]},‘$email‘);"
else
psql -U zxdbm_830 -d zxin -c "update oper_mail set mailbox=‘$email‘ where operatorid=${ARR_OPERATORID[2]};"
fi
fi
row_num+=1
fi
done