This task counts the top 500 entries in a week.
Data source: from the Oracle database.
Requirement: generate an Excel file and send an email to the relevant person.
Because it was the first time that I was engaged in such a task (writing scripts on the server and processing data), I encountered many problems and learned a lot about it, including using shell scripts, related Linux commands, shell scripts to send emails, Perl scripts to use, Perl scripts to connect to the database, complex SQL statements, Perl scripts to process data, generate EXCEL (format design ), perl scripts cannot escape characters that are identifiable Chinese characters. Therefore, you need to use Java programs to process generated Excel files, decode utf8 as identifiable Chinese characters, and read and write Excel files in Java. This process suddenly touched many things that may be easy for others and enriched themselves. Like yourself! This part of knowledge is summarized in the following parts:
I. Shell-related
Ii. Perl Problems
Iii. Java-related
4. complex SQL statements
The content involved in these parts may be further subdivided.
I. Shell correlation 1. Date: Date = 'date "+ % Y % m % d" '// today's date
Date2 = 'date-d "7 days ago" "+ % Y % m % d" '// Date year = 'date-d yesterday "+ % Y" seven days ago"'
MON = 'date-d yesterday "+ % m "'
Day = 'date-d yesterday "+ % d "'
Weekday = 'date-d yesterday "+ % u" '// The date represented by a number (from Monday to Monday) Linux
Shell Date: Write a date directly in one row. The current date is displayed in the command line output. 2. send an email: echo "$ {date} top500 in a week report" |/usr/bin/mutt-A top500.xls-s "top500 in a week report $ {date }"
The xxx@163.com line runs as follows: send a message to the xxx@163.com titled "top500
In a week report, the attachment is top500.xls, and the body of the email is "20111129 top500 in a week report ".
3. Call other scripts: Call shell scripts: sh/shellpath/xx. sh calls the Perl script: CD/perlpath Perl xx. PL $ parameter 1 $ parameter 2 $ parameter 34. Call the Java program:
Export java_home =/application/jdk1.6.0 _ 10 Export Path = $ java_home/bin: $ pathexport sh_home =/home/loganalysis/import/webapps/root/wikiexport Lib = $ sh_home/WEB-INF/libexport classpath =/home/loganalysis/import/webapps/root/Shell/extlib /javax. servlet. jar: $ lib /.. /classes: $ lib/jxl. jar: $ classpathexport lang = zh_cn.gb18030export Language = zh_cn.gb18030: zh_cn.gb2312: zh_cnargs = "-xms32m-xmx512m-djava. AWT. headless = true "year = 'date" + % Y "'mon = 'date" + % m "'day = 'date" + % d "'file =/home/userx /top500/top500.xls // parameters are Java $ ARGs. b. c. classpath. decodeexceljxl $ {file }$ {year}-$ {mon}-$ {day} // (because the class path is written in the classpath above, you can call the class here)
5. Others:
Echo "the content to be displayed, for example, the time is $ {date }"
Printf "Time: % s \ n", $ {date}; // formatted. Press enter and add a plus sign at the end.
