package connDB;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import org.apache.jasper.tagplugins.jstl.core.Set;
import edu.lib.common.primitive.StringTK;
import au.com.bytecode.opencsv.CSVReader;
public class dbconnection implements Serializable{
/**
*
*/
private static final long serialVersionUID = 7980231231515363944L;
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String DBDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
private String connstr ="jdbc:sqlserver://220.130.36.35:1433;databaseName=�i�P�s���2";
private String user="pos";
private String password="681116Tw";
private HashMap<String, String[]> raw = new HashMap<String, String[]>();
private HashMap<String, String[]> filterData = new HashMap<String, String[]>();
private ArrayList<String[]> mobileData = new ArrayList<String[]>();
private ArrayList<String[]> revisedMobileData = new ArrayList<String[]>();
private ArrayList<String[]> smartMobileData = new ArrayList<String[]>();
private ArrayList<String[]> revisedSmartMobileData = new ArrayList<String[]>();
private ArrayList<String[]> tabletData = new ArrayList<String[]>();
private ArrayList<String[]> revisedTabletData = new ArrayList<String[]>();
private List<String[]> allCols = new ArrayList<String[]>();
public dbconnection()
{}
/*public HashMap<String, String[]> getRaw() {
return raw;
}*/
public HashMap<String, String[]> getFilterData() {
return filterData;
}
private void getConnection(){
try{
Class.forName(DBDriver);
System.out.println("�ƾڸ��J���\");
}
catch(ClassNotFoundException e){
System.err.print(e.getMessage());
System.err.print(e.toString());
}
}
private String buildSql(String startTime, String endTime) {
getConnection();
StringBuilder sb = new StringBuilder();
sb.append("select a.�ӫ~�s��")
.append(",dbo.get�t�P(a.�ӫ~�s��) �t�P")
.append(",dbo.get����(a.�ӫ~�s��) ����")
.append(",isnull(�i�f�q,0) �i�f�q")
.append(",isnull(�P�f�q,0)-isnull(�P�f�h�^�q,0) �P�f�q")
.append(",isnull(�w���q,0) �w���q")
.append(",dbo.get�w�s�q2(-1,a.�ӫ~�s��,getdate(),1) �w�s�q")
.append(",a.�`��Q/a.�P�f�q ������Q")
.append(",a.�`��Q")
.append(",a.�٭���`��Q/a.�P�f�q �٭�ᥭ����Q")
.append(",a.�٭���`��Q")
.append(",b.�̷s�Ѧһ�")
.append(",b.��ij���")
.append(" from")
.append(" (select �ӫ~�s��,sum(��Q) �`��Q, sum(isnull(�٭���Q,0)) �٭���`��Q ,sum(�ƶq) �P�f�q")
.append(" from ��Q���R ")
.append(" where (��ڤ�� between convert(nvarchar(20),'" + startTime+ " 00:00:00') and convert(nvarchar(20),'" +endTime+ " 00:00:00'))")
.append(" and ��ڽs�� like 'P%'")
.append(" group by �ӫ~�s��) a")
.append(" left join")
.append(" (select �ӫ~�s��,�t�P+' '+���� ����,�̷s�Ѧһ�,��ij���")
.append(" from �ӫ~ ) b on a.�ӫ~�s�� =b.�ӫ~�s��")
.append(" left join")
.append(" (select �ӫ~�s��,sum(�ƶq)�i�f�q from �i�f���~")
.append(" inner join �i�f�� on �i�f��.��ڽs��=�i�f���~.��ڽs�� ")
.append(" where (��ڤ�� between convert(nvarchar(20),'" + startTime+ " 00:00:00') and convert(nvarchar(20),'" +endTime+ " 00:00:00') )")
.append(" group by �ӫ~�s��) c on a.�ӫ~�s��=c.�ӫ~�s��")
.append(" left join")
.append(" (select �ӫ~�s��,sum(�ƶq)�w���q from �w�����~")
.append(" inner join �w���� on �w����.��ڽs��=�w�����~.��ڽs��")
.append(" where (��ڤ�� between convert(nvarchar(20),'" + startTime+ " 00:00:00') and convert(nvarchar(20),'" +endTime+ " 00:00:00') )")
.append(" group by �ӫ~�s��) d on a.�ӫ~�s��=d.�ӫ~�s��")
.append(" left join")
.append(" (select �ӫ~�s��,sum(�ƶq)�P�f�h�^�q from �P�f�h�^")
.append(" inner join �P�f�h�^���~ on �P�f�h�^.��ڽs��=�P�f�h�^���~.��ڽs��")
.append(" where(��ڤ�� between convert(nvarchar(20),'" + startTime+ " 00:00:00') and convert(nvarchar(20),'" +endTime+ " 00:00:00') )")
.append(" group by �ӫ~�s��) e on a.�ӫ~�s��=e.�ӫ~�s�� ")
.append(" left join ")
.append(" (select �ӫ~�s��,sum(isnull(�ӫ~�ƶq,0)) �w�s�_�l from �w�s group by �ӫ~�s��) f on a.�ӫ~�s��=f.�ӫ~�s��" )
.append(" left join ")
.append(" (select �ӫ~�s��,sum(isnull(�ק�ƶq,0)) �w�s�ק�q ")
.append(" from �w�s�ק�O�� group by �ӫ~�s��) g on a.�ӫ~�s��=g.�ӫ~�s��" )
.append(" order by �̷s�Ѧһ� asc");
//System.out.print(sb.toString());
return sb.toString();
}
public String buildFilterSqlByTwoColumn(String sqlState, String key, String columnName1, String columnName2) {
String fixSql=sqlState.replace("order by �̷s�Ѧһ� asc", "");
StringBuilder sb = new StringBuilder();
sb.append("with da as ( ")
.append(fixSql)
.append(") ")
.append("select 'TY' as TY, �ӫ~�s�� ")
.append("from da ")
.append("where " + columnName1 + " like '%" + key + "%' or " + columnName2 + " like '%" + key + "%'");
return sb.toString();
}
public String buildFilterSqlBySingalcolumn(String sqlState, String key, String columnName) {
String fixSql=sqlState.replace("order by �̷s�Ѧһ� asc", "");
StringBuilder sb = new StringBuilder();
sb.append("with da as ( ")
.append(fixSql)
.append(") ")
.append("select 'TY' as TY, �ӫ~�s�� ")
.append("from da ")
.append("where " + columnName + " like '%" + key + "%' ");
return sb.toString();
}
public ResultSet executeQuery(String startTime, String endTime) throws Exception{
try {
String sql = buildSql(startTime,endTime);
System.out.println("sql:" + sql);
conn=DriverManager.getConnection(connstr,user,password);
conn.setAutoCommit(true);
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
System.out.print("���\�s����Ʈw");
rs=stmt.executeQuery(sql);
while(rs.next()){
String[] fields = new String[13];
fields[0] = rs.getString("�ӫ~�s��");
fields[1] = rs.getString("�t�P");
fields[2] = rs.getString("����");
fields[3] = rs.getString("�i�f�q");
fields[4] = rs.getString("�P�f�q");
fields[5] = rs.getString("�w���q");
fields[6] = rs.getString("�w�s�q");
fields[7] = rs.getString("������Q");
fields[8] = rs.getString("�`��Q");
fields[9] = rs.getString("�٭�ᥭ����Q");
fields[10] = rs.getString("�٭���`��Q");
fields[11] = rs.getString("�̷s�Ѧһ�");
fields[12] = rs.getString("��ij���");
//System.out.println("�~�P: " + fields[1]);
//System.out.println("�t�P: " + fields[2]);
raw.put(fields[0], fields);
}
//doProductClassification(sql,generalPhoneMark);
//Delect unnecessary prodcut in array by ����
String[] filterNameByProductName={"�i��","�֧Q","�]QS�^","(QS)","�Ȥ�"};
doSpecialFilterByProductName(sql,filterNameByProductName,"����","�t�P");
//String[] filterNameByProductMark={"�Ȥ�"};
//doSpecialFilterByProductName(sql,filterNameByProductMark,"�t�P");
// cluster different product
String[] generalPhoneMark={"(A)","(B)"};
String[] smartPhoneMark={"(S)","(BS)"};
String[] tabletMark={"(P)"};
doProductClassification(sql,"generalPhone",generalPhoneMark);
doProductClassification(sql,"smartPhone",smartPhoneMark);
doProductClassification(sql,"tablet",tabletMark);
importCSV("E:/workspace/reportss3c/productID.csv");
doMerge();
} catch (Exception ex1) {
System.out.println(ex1.getMessage());
throw ex1;
} finally{
closeConnection();
}
return rs;
}
private void doSpecialFilterByProductName(String sqlState,String[] filterName,String columnName1,String columnName2) throws Exception {
try{
System.out.println("JumpIntodoSpecialFilter");
int rawSize=raw.size();
System.out.println("rawSize =" +rawSize);
//filter the column within filterName array
for(int i=0;i<filterName.length;i++){
String tmpSql=buildFilterSqlByTwoColumn(sqlState,filterName[i],columnName1.toString(),columnName2.toString());
//System.out.println("filterName =" +filterName[i]);
rs=stmt.executeQuery(tmpSql);
rs.last();
int rowcount = rs.getRow();
rs.beforeFirst();
//System.out.println("rowcount: " +rowcount);
if(rs.wasNull()==false){
while(rs.next()){
if(raw.get(rs.getString("�ӫ~�s��")) != null){
raw.remove(rs.getString("�ӫ~�s��"));
//String[] tmpData=raw.remove(rs.getString("�ӫ~�s��"));
//System.out.println("Remove :" +tmpData[0]+ " " + tmpData[1]+" " + tmpData[2]);
}
}
}
}
rawSize=raw.size();
//System.out.println("After filter => rawSize =" +rawSize);
}catch (Exception ex1) {
System.out.println(ex1.getMessage());
throw ex1;
} finally{
//closeConnection();
}
}
public void importCSV(String file) throws Exception {
try{
BufferedReader in = new BufferedReader(new FileReader(file));
CSVReader reader = new CSVReader(in, ',', '"');
allCols = reader.readAll();
reader.close();
//String[] test=allCols.get(0);
//String flag=test[0];
//System.out.println(flag);
//System.out.println(Arrays.deepToString(allCols.get(0)));
//System.out.println(Arrays.deepToString(allCols.get(1)));
//System.out.println(Arrays.deepToString(allCols.));
//System.out.println("���~���زM�� :" +allCols.size());
//System.out.println(allCols.get(0));
}catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
}
}
private void calcateMerge(String mergeProductName,String productStyle) throws Exception{
try{
System.out.println("calcateMerge process");
boolean token=false;
int sampleCount=0;
String[] revisedProductData=new String[mobileData.get(0).length];
int buyNum=0,saleNum=0,orderNum=0,stockNum=0;
float avgProfit=0, totalProfit = 0, realAvgTotalProfit=0,realTotalProfit=0f;
int arraySize=0;
String logoName = null,productMark = null;
if(productStyle.equals("mobileData")){
arraySize=mobileData.size();
}else if(productStyle.equals("smartMobileData")){
arraySize=smartMobileData.size();
}else if(productStyle.equals("tabletData")){
arraySize=tabletData.size();
}
for(int j=0; j<arraySize;j++){
String[] tmpProductData=null;
if(productStyle.equals("mobileData")){
tmpProductData=mobileData.get(j);
}else if(productStyle.equals("smartMobileData")){
tmpProductData=smartMobileData.get(j);
}else if(productStyle.equals("tabletData")){
tmpProductData=tabletData.get(j);
}
String selectProductName=tmpProductData[1]+" "+ tmpProductData[2];
//System.out.println("j: " +j);
if(StringTK.contains(selectProductName,mergeProductName)){
logoName=tmpProductData[1];
productMark=tmpProductData[2];
sampleCount++;
buyNum=buyNum+Integer.valueOf(tmpProductData[3]); //("�i�f�q")
saleNum=saleNum+Integer.valueOf(tmpProductData[4]); //("�P�f�q");
orderNum=orderNum+Integer.valueOf(tmpProductData[5]); //("�w���q");
stockNum=stockNum+Integer.valueOf(tmpProductData[6]); //("�w�s�q");
avgProfit=avgProfit+Float.valueOf(tmpProductData[7]); //("������Q");
totalProfit=totalProfit+Float.valueOf(tmpProductData[8]); //("�`��Q");
realAvgTotalProfit=realAvgTotalProfit+Float.valueOf(tmpProductData[9]); //("�٭�ᥭ����Q");
realTotalProfit=realTotalProfit+Float.valueOf(tmpProductData[10]); //("�٭���`��Q");
if(productStyle.equals("mobileData")){
mobileData.remove(j);
arraySize=mobileData.size();
j--;
}else if(productStyle.equals("smartMobileData")){
smartMobileData.remove(j);
arraySize=smartMobileData.size();
j--;
}else if(productStyle.equals("tabletData")){
tabletData.remove(j);
arraySize=tabletData.size();
j--;
}
token=true;
System.out.println("Merge Initial");
//System.out.println("mobileData.size(): " +mobileData.size());
}
if(token && j==arraySize-1){
System.out.println("Final calcu");
//System.out.println("�ӫ~�s��: " +rs.getString("�ӫ~�s��"));
System.out.println("saleNum: " +saleNum);
System.out.println("avgProfit: " +avgProfit);
System.out.println("logoName: " +logoName);
System.out.println("productMark: " +productMark);
revisedProductData[0]="hadMerge"; //�ӫ~�s��
revisedProductData[1]=logoName; //�t�P
revisedProductData[2]=productMark; //����
revisedProductData[3]=buyNum+Integer.valueOf(tmpProductData[3]).toString(); //�i�f�q
revisedProductData[4] = saleNum+Integer.valueOf(tmpProductData[4]).toString(); //�P�f�q
revisedProductData[5] = orderNum+Integer.valueOf(tmpProductData[5]).toString(); //�w���q
revisedProductData[6] = stockNum+Integer.valueOf(tmpProductData[6]).toString(); //�w�s�q
revisedProductData[7] = String.valueOf(((avgProfit+Float.valueOf(tmpProductData[7]))/sampleCount)); //������Q
revisedProductData[8] = totalProfit+Float.valueOf(tmpProductData[8]).toString(); //�`��Q
revisedProductData[9] = String.valueOf(((realAvgTotalProfit+Float.valueOf(tmpProductData[9]))/sampleCount)); //�٭�ᥭ����Q
revisedProductData[10] = realTotalProfit+Float.valueOf(tmpProductData[10]).toString(); //�٭���`��Q
revisedProductData[11]=tmpProductData[11]; //�̷s�Ѧһ�
revisedProductData[12]=tmpProductData[12]; //��ij���
if(productStyle.equals("mobileData")){
revisedMobileData.add(revisedProductData);
}else if(productStyle.equals("smartMobileData")){
revisedSmartMobileData.add(revisedProductData);
}else if(productStyle.equals("tabletData")){
revisedTabletData.add(revisedProductData);
}
}
} // end for
}catch(Exception ex1){
System.out.println(ex1.getMessage());
throw ex1;
}finally{
}
}
private void doMerge() throws Exception {
try {
System.out.println("doMerge process");
//System.out.println("mobileData.size(): " +mobileData.size());
for(int i=0;i<allCols.size();i++){
String productName=Arrays.deepToString((Object[]) allCols.get(i));
String[] productListData=allCols.get(i);
String mark=productListData[0];
//System.out.println("conduct productName: " +mark);
if(StringTK.contains(mark, "�]A�^")|| StringTK.contains(mark, "�]B�^")){
System.out.println("conduct productName: " +mark);
calcateMerge(mark,"mobileData");
}else if(StringTK.contains(mark, "�]S�^")|| StringTK.contains(mark, "�]BS�^")){
calcateMerge(mark,"smartMobileData");
}else if(StringTK.contains(mark, "�]P�^")){
calcateMerge(mark,"tabletData");
}else{
System.out.println("conduct productName: " +mark);
System.out.println("something never considered ");
}//end if
}// end for
for(int k=0;k<mobileData.size();k++){
revisedMobileData.add(mobileData.get(k));
}
for(int k=0;k<smartMobileData.size();k++){
revisedSmartMobileData.add(smartMobileData.get(k));
}
for(int k=0;k<tabletData.size();k++){
revisedTabletData.add(tabletData.get(k));
}
System.out.println("Finish doMerge process");
} catch (Exception ex1) {
System.out.println(ex1.getMessage());
throw ex1;
} finally{
//closeConnection();
}
}
private void doProductClassification(String sqlState,String productType, String[] productData) throws Exception {
try {
System.out.println("Jump into doProductClassification");
//System.out.println("productData.length :" +productData.length);
for(int i=0;i<productData.length;i++){
String tmpSql=buildFilterSqlBySingalcolumn(sqlState,productData[i],"�t�P");
//System.out.println("productData : " +productData[i]);
rs=stmt.executeQuery(tmpSql);
rs.last();
//get the number of data in the resultset
int rowcount = rs.getRow();
rs.beforeFirst();
//System.out.println("rowcount: " +rowcount);
String[] tmpValue = new String[13];
if(rs.wasNull()==false ){
int count=1;
while (rs.next()){
//System.out.println("NO " +count);
if(raw.get(rs.getString("�ӫ~�s��")) != null){
if(raw.containsKey(rs.getString("�ӫ~�s��"))==true){
String[] tmpData=raw.get(rs.getString("�ӫ~�s��"));
if(productType.equals("generalPhone")){
mobileData.add(tmpData);
}else if(productType.equals("smartPhone")){
smartMobileData.add(tmpData);
}else if(productType.equals("tablet")){
tabletData.add(tmpData);
}
//System.out.println("�ӫ~�s��: " +tmpData[0]);
//System.out.println("�t�P: " +tmpData[1]);
//System.out.println("����: " +tmpData[2]);
}else{
System.out.println("do Special Filter By ProductName");
}
}
count++;
}
}
}
} catch (Exception ex1) {
System.out.println(ex1.getMessage());
throw ex1;
} finally{
//closeConnection();
}
}
public void closeConnection()
{
try{
stmt.close();
conn.close();
System.out.print("close DB connection");
}
catch(SQLException e)
{
e.printStackTrace();
}
}
public static void main(String args[]) throws Exception{
dbconnection connDB= new dbconnection();
ResultSet tmp= connDB.executeQuery("2013-04-01 00:00:00", "2013-04-03 00:00:00");
}
}