package com.easyjf.dbo.sql;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.log4j.Logger;

import com.easyjf.dbo.DBObject;
import com.easyjf.dbo.DBTable;

/**
 * MS SqlServer的sql查詢語句處理程序
 * @author 蔡世友
 *
 */
public class MSSqlServerQuery implements ISqlQuery {
  private final static Logger logger = Logger.getLogger(MSSqlServerQuery.class);
   public String getRowCountSql(String sql) {
	   StringBuffer s=new StringBuffer();
	   if(sql==null)return "";
	   String s1=sql.toLowerCase();	   
	   if(s1.indexOf("order by")>0)
	   {		   
		   s1=s1.substring(0,s1.toLowerCase().indexOf("order by"));
	   }
	   s.append("select count(*)");
	   s.append(s1.substring(s1.indexOf(" from ")));   	   
	   return s.toString();
	}

public String getTopSql(String sql,int number) {
	 StringBuffer s=new StringBuffer();
	   if(sql==null)return "";
	   if(sql.toLowerCase().indexOf("select top ")<0)
	   {
		   s.append("select top ").append(number);
		   s.append(sql.substring(sql.toLowerCase().indexOf("select")+6));
	   }	   
	   else
	   {
		   s.append(sql);
	   }
	   return s.toString();
}

	public String getInsertSql(DBObject obj) {
	StringBuffer s=new StringBuffer();
	if(obj.getValue()==null)return "";
	s.append("insert into "+obj.getTable().getName());	
	Iterator it =obj.getValue().keySet().iterator();
	int paraNum=0;
	if(it!=null)
	{
		s.append("(");
		while(it.hasNext())
		{   paraNum++;
			String field=(String)it.next();
			s.append(field);
			if(it.hasNext())s.append(",");
		}
		s.append(")");
	}
	if(paraNum>0){
		s.append(" values(");
	for(int i=0;i<paraNum;i++)
	{
		s.append("?");
		if(i<paraNum-1)s.append(",");
	}
		s.append(")");
	}	
		return s.toString();
	}

public String getUpdateSql(DBObject obj) {
		StringBuffer s=new StringBuffer();
		if(obj.getValue()==null)return "";
		String id=obj.getTable().getId();
		if(obj.getIdValue()==null)return "";
		s.append("update "+obj.getTable().getName());	
		Iterator it =obj.getValue().keySet().iterator();		
		if(it!=null)
		{	
			s.append(" set ");
			while(it.hasNext())
			{ 
				String field=(String)it.next();		
				if(!field.equals(id)){
				s.append(field+"=?");
				s.append(",");	
				}
			}	
			s.deleteCharAt(s.length()-1);
			s.append(" where "+id+"=?");
		}		
			return s.toString();	
	}

public String getDelSql(DBObject obj) {
	StringBuffer s=new StringBuffer();
	if(obj.getValue()==null)return "";
	String id=obj.getTable().getId();
	if(obj.getIdValue()==null)return "";
	s.append("delete from "+obj.getTable().getName());
	s.append(" where "+id+"=?");	
	return s.toString();
	}

public String getQuerySql(DBObject obj) {		
	StringBuffer s=new StringBuffer();
	if(obj.getTable()==null)return "";
	String id=obj.getTable().getId();	
	if(obj.getValue()!=null){
	Iterator it =obj.getValue().keySet().iterator();		
	if(it!=null)
	{	
		s.append("select ");
		while(it.hasNext())
		{ 
			String field=(String)it.next();				
			s.append(field);
			if(it.hasNext())s.append(",");				
		}			
		s.append(" from "+obj.getTable().getName());
	}	
	}
	else
	{
	s.append("select * from "+obj.getTable().getName());
	}
	s.append(" where "+id+"=?");	
	return s.toString();	
	}
public String getQuerySql(DBObject obj,String scope) {		
	StringBuffer s=new StringBuffer();
	if(obj.getTable()==null)return "";
	//String id=obj.getTable().getId();	
	if(obj.getValue()!=null){
	Iterator it =obj.getValue().keySet().iterator();		
	if(it!=null)
	{	
		s.append("select ");
		while(it.hasNext())
		{ 
			String field=(String)it.next();				
			s.append(field);
			if(it.hasNext())s.append(",");				
		}			
		s.append(" from "+obj.getTable().getName());
	}	
	}
	else
	{
	s.append("select * from "+obj.getTable().getName());
	}
	s.append(" where "+scope);	
	return s.toString();	
	}
	/**
	 * @param args
	 */
public static void main(String[] args) {
		DBObject obj=new DBObject();
		obj.setTable(new DBTable("test","cid"));
		Map value=new HashMap();
		obj.setIdValue("dfsdf");
		value.put("cid","cid");
		value.put("tt","tt");
		value.put("content","dksfksdlf");
		value.put("field2","field2");
		obj.setValue(value);
		MSSqlServerQuery sqlQuery=new MSSqlServerQuery();
		System.out.println(sqlQuery.getInsertSql(obj));
		System.out.println(sqlQuery.getUpdateSql(obj));
		System.out.println(sqlQuery.getDelSql(obj));
		System.out.println(sqlQuery.getQuerySql(obj));
		System.out.println(sqlQuery.getRowCountSql("Select cid,tt from test where dkdkdkdkdk order by cid desc"));
		System.out.println(sqlQuery.getTopSql("select * from test where dkdkdkdkdk",10));
		System.out.println(sqlQuery.getTopSql("select Top 5 * from test where dkdkdkdkdk",10));
	}

}