/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.gd.sdr.dao;
import com.gd.sdr.mail.SendAttachMail;
import com.gd.sdr.object.Report;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import org.apache.commons.lang.time.DateUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author naisiong.yap
*/
public class UserDAO1 {
private static Logger logger = Logger.getLogger(UserDAO1.class);
public static void main(String[] args) throws Exception {
UserDAO1.getDate();
}
public static String getDate() throws Exception{
String sql="SELECT dateAdded FROM cms.users WHERE userType = ?";
Connection conn = null;
conn = DriverManager.getConnection("jdbc:mysql://192.95.29.8:2303/cms", "root", "password");
Report report = null;
String str = "admin";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,str);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
report = new Report();
report.setDate(rs.getString("dateAdded"));
System.out.println(" "+report.getDate( ));
}
return null;
}
public static String getAllDate(String startdate, String enddate) throws Exception{
Connection conn = null;
Report report = null;
conn = DriverManager.getConnection("jdbc:mysql://192.95.29.8:2303/cms", "root", "password");
String sql = "SELECT DATEDIFF('"+enddate+"', '"+startdate+"') AS Count";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
report = new Report();
report.setCount(rs.getInt("Count"));
}
int countdays = report.getCount( );
return generateSheet1_Summary(startdate, countdays+1) ;
}
public static String generateSheet1_Summary(String strdate, int totaldays) throws Exception {
Connection conn = null;
Report report = null;
conn = DriverManager.getConnection("jdbc:mysql://192.95.29.8:2303/cms", "root", "password");
//create sheet1- Row, Cell
Workbook wb = new XSSFWorkbook();
Sheet sheet1 = wb.createSheet("Summary");
sheet1.setColumnWidth(0, 3000);
sheet1.setColumnWidth(1, 3000);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
//-------------------------------sheet1-----------------------------------------------------
Row firstrow = sheet1.createRow((short) 0);
CellStyle firstrow_style = wb.createCellStyle();
firstrow_style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
firstrow_style.setFillPattern(CellStyle.SOLID_FOREGROUND);
firstrow_style.setFont(font);
//Border for first row
firstrow_style.setBorderBottom( CellStyle.BORDER_THIN );
firstrow_style.setBorderLeft( CellStyle.BORDER_THIN );
firstrow_style.setBorderRight( CellStyle.BORDER_THIN );
firstrow_style.setBorderTop( CellStyle.BORDER_THIN );
firstrow_style.setBorderLeft( CellStyle.BORDER_THIN );
Cell firstRowCell0 = firstrow.createCell((short) 0);
firstRowCell0.setCellValue("Date");
firstRowCell0.setCellStyle(firstrow_style);
Cell firstRowCell1 = firstrow.createCell((short) 1);
firstRowCell1.setCellValue("Count");
firstRowCell1.setCellStyle(firstrow_style);
//Border for row
CellStyle row_style = wb.createCellStyle();
row_style.setBorderBottom( CellStyle.BORDER_THIN );
row_style.setBorderLeft( CellStyle.BORDER_THIN );
row_style.setBorderRight( CellStyle.BORDER_THIN );
row_style.setBorderTop( CellStyle.BORDER_THIN );
row_style.setBorderLeft( CellStyle.BORDER_THIN );
row_style.setAlignment(CellStyle.ALIGN_RIGHT);
//-------------------------------end sheet1-------------------------------------------------
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date setdate = sdf.parse(strdate);
String []startdate = new String[totaldays];
String []enddate = new String[totaldays];
int totalcount =0;
try {
logger.info("<<start get Date and Count>>");
//--------------------Query sheet 1--------------------------------
for(int i =0; i < totaldays;i++){
startdate[i]= queryDateFormat(DateUtils.addDays(setdate, i)).toString();
enddate[i]= queryDateFormat(DateUtils.addDays(setdate, i+1)).toString();
String sql = "SELECT COUNT(*) as count FROM cms.users WHERE '"+startdate[i]+"' <= dateAdded AND dateAdded <'"+enddate[i]+"'";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
startdate[i]= defineContentDateFormat(DateUtils.addDays(setdate, i)).toString();
enddate[i]= defineContentDateFormat(DateUtils.addDays(setdate, i+1)).toString();
while (rs.next()) {
report = new Report();
report.setCount(rs.getInt("count"));
}
Row row = sheet1.createRow((short) i+1);
Cell RowCell0 = row.createCell((short) 0);
row.createCell(0).setCellValue(startdate[i]);
RowCell0.setCellStyle(row_style);
Cell RowCell1 = row.createCell((short) 1);
row.createCell(1).setCellValue(report.getCount( ));
RowCell1.setCellStyle(row_style);
logger.info("Date :" + startdate[i] +" ,Count:" + report.getCount( ));
totalcount += report.getCount( );
}
logger.info("<<end get Date and Count>> \n");
//Space row
sheet1.createRow((short) startdate.length+1).createCell((short) 0).setCellStyle(row_style);
sheet1.createRow((short) startdate.length+1).createCell((short) 1).setCellStyle(row_style);
//Last row: Count Total
Row lastrow = sheet1.createRow((short) startdate.length+2);
Cell lastRowCell0 = lastrow.createCell((short) 0);
Cell lastRowCell1 = lastrow.createCell((short) 1);
lastrow.createCell(1).setCellValue(totalcount);
lastRowCell0.setCellStyle(row_style);
lastRowCell1.setCellStyle(row_style);
//--------------------end Query sheet 1--------------------------------
} catch (Exception e) {
logger.error("file generation Exception[" + e + "]");
}
return generateSheet2_AllDetails(conn,setdate,totaldays,wb,font) ;
}
public static String generateSheet2_AllDetails(Connection conn, Date setdate, int totaldays, Workbook wb, Font font) throws Exception {
Sheet sheet2 = wb.createSheet("Details");
sheet2.setColumnWidth(0, 4000);
sheet2.setColumnWidth(1, 7000);
sheet2.setColumnWidth(2, 6000);
Row sheet2firstrow = sheet2.createRow((short) 0);
CellStyle sheet2firstrow_style = wb.createCellStyle();
sheet2firstrow_style.setFont(font);
Cell sheet2firstRowCell0 = sheet2firstrow.createCell((short) 0);
sheet2firstRowCell0.setCellValue("UserName");
sheet2firstRowCell0.setCellStyle(sheet2firstrow_style);
Cell sheet2firstRowCell1 = sheet2firstrow.createCell((short) 1);
sheet2firstRowCell1.setCellValue("Email");
sheet2firstRowCell1.setCellStyle(sheet2firstrow_style);
Cell sheet2firstRowCell2 = sheet2firstrow.createCell((short) 2);
sheet2firstRowCell2.setCellValue("DateAdded");
sheet2firstRowCell2.setCellStyle(sheet2firstrow_style);
//Border for row
CellStyle sheet2row_style = wb.createCellStyle();
sheet2row_style.setAlignment(CellStyle.ALIGN_RIGHT);
Report report = null;
String []startdate = new String[totaldays];
String []enddate = new String[totaldays];
int count =1;
logger.info("<<start select UserName, Email, DateAdded>>");
try {
//--------------------Query sheet 2--------------------------------
for(int i =0; i < startdate.length;i++){
startdate[i]= queryDateFormat(DateUtils.addDays(setdate, i)).toString();
enddate[i]= queryDateFormat(DateUtils.addDays(setdate, i+1)).toString();
String sql = "SELECT username as username, email as email, dateAdded as dateAdded FROM cms.users WHERE '"+startdate[i]+"' <= dateAdded AND dateAdded <='"+enddate[i]+"'";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next() && rs.getString("username")!=null && rs.getString("email")!=null && rs.getString("dateAdded")!=null) {
report = new Report();
report.setName(rs.getString("userName"));
report.setEmail(rs.getString("email"));
report.setDate(rs.getString("dateAdded"));
Row row = sheet2.createRow((short) count);
row.createCell(0).setCellValue(report.getName());
row.createCell(1).setCellValue(report.getEmail());
row.createCell(2).setCellValue(report.getDate());
logger.info("User name= " +report.getName()+" email= "+report.getEmail()+" CreateDate= "+report.getDate());
count++;
}
}
//--------------------end Query sheet 2--------------------------------
logger.info("<<end select UserName, email, CreateDate>>");
logger.info("");
} catch (Exception e) {
logger.error("file generation Exception[" + e + "]");
}
//define file name date
String []fileDate = new String[totaldays];
fileDate[totaldays-1]= defineFileNameDateFormat(DateUtils.addDays(setdate,totaldays-1)).toString();
return generateExcelFile(wb,fileDate[totaldays-1]);
}
public static String defineContentDateFormat(Date day){
SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yy");
String date = formatter.format(day);
return date;
}
public static String queryDateFormat(Date day){
SimpleDateFormat formatter = new SimpleDateFormat("yy-MM-dd");
String date = formatter.format(day);
return date;
}
public static String defineFileNameDateFormat(Date day){
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
String date = formatter.format(day);
return date;
}
private static String generateExcelFile(Workbook wb, String fileDate){
FileOutputStream fileOut;
String strfile = "Report for "+fileDate+".xlsx";
try {
fileOut = new FileOutputStream(strfile);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println( strfile +" created!" );
logger.info("<<end generate Excel file>> \n");
return SendAttachMail.SendMail(strfile);
}
}