semovy

String TUtilForm::getSQL(int recordCount ,int pageCount, int pageSize,int pageIndex,String tableName,String queryFields,String primaryKey,String ascending,String condition)
{
    int middleIndex = pageCount/2;
    int firstIndex = 0;
    int lastIndex = pageCount - 1;
    String SQLStr = "";
    if( pageIndex <= firstIndex )
    {
        SQLStr = "select top " + IntToStr(pageSize) + " " + queryFields + " from " + tableName;
        if( condition != "")
        SQLStr += " where " + condition ;
        SQLStr += " order by " + primaryKey + " " + ascending;
    }
    else if( pageIndex > firstIndex && pageIndex <= middleIndex )
    {
        SQLStr = "select top " + IntToStr(pageSize) + " " + queryFields + " from " + tableName + " where " + primaryKey ;
        if( ascending == "asc" )
        SQLStr +=  ">(select max(";
        else
        SQLStr += "<(select min(";
        SQLStr += primaryKey + ") from (select top " + IntToStr(pageSize*pageIndex) + " " + primaryKey + " from " + tableName;
        if( condition != "" )
        SQLStr += " where " + condition;
        SQLStr += " order by " + primaryKey + " " + ascending + ") TableA )";
        if( condition != "" )
        SQLStr += " and " + condition;
        SQLStr += " order by " + primaryKey + " " + ascending;
    }
    else if( pageIndex > middleIndex && pageIndex < lastIndex)
    {
        SQLStr = "select " + queryFields + " from (select top " + IntToStr(pageSize) + " " + queryFields + " from " + tableName;
        SQLStr += " where " + primaryKey ;
        if( ascending == "asc" )
        SQLStr += " < ( select min(";
        else
        SQLStr += " > ( select max(";
        SQLStr += primaryKey + ") from (select top " +  IntToStr( recordCount - pageSize * (pageIndex + 1)) + " " + primaryKey + " from " + tableName;
        if( condition != "" )
        SQLStr += " where " + condition;
        SQLStr += " order by " + primaryKey + " desc) TableA)" ;
        if( condition != "" )
        SQLStr += " and " + condition;
        SQLStr += " order by " + primaryKey + " desc) TableB order by )" + primaryKey + " " + ascending ;  
    }
    else
    {
        SQLStr = "select " + queryFields + " from (select top " + IntToStr(recordCount - pageSize*lastIndex) + " " + queryFields + " from " + tableName;
        if( condition != "" )
        SQLStr += " where " + condition;
        SQLStr += " order by " + primaryKey + " desc " + " )TableA order by " + primaryKey + " " + ascending;
    }
    return SQLStr;
}