<%-- 

    Document   : mmbr

    Created on : Jan 15, 2011, 11:25:02 PM

    Author     : zamir

--%>

<%@ page import="java.util.StringTokenizer" %>

<%@ page import="java.util.Arrays"%>

<%@ page import="java.util.ArrayList"%>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" %>

<%@ page import="java.sql.PreparedStatement"  %>

<%@ page import="java.sql.ResultSet" %>

<%@ page import="java.sql.Connection" %>

<%@ page import="java.sql.DriverManager" %>


<%!

    String memId = "";

    String cname = "";

    String add1 = "";

    String add2 = "";

    String add3 = "";

    String add4 = "";

    String add5 = "";

    String add = "";

    String country = "";

    String cat = "";

    String contact = "";

    String email = "";

    String website = "";

    String desc = "";


    String searchcountry ="";

    String[] searchcategory = null;

    String searchkeyword ="";

    String keyword="";

    String q2;

    
public int nullIntconv(String str)

{

    int conv=0;

    if(str==null)

    {

        str="0";

    }

    else if((str.trim()).equals("null"))

    {

        str="0";

    }

    else if(str.equals(""))

    {

        str="0";

    }

    try{

        conv=Integer.parseInt(str);

    }

    catch(Exception e)

    {

    }

    return conv;

}

%>

<%  

    searchcountry = request.getParameter("country");

    if (request.getParameter("country")== null){   

        searchcountry="";

        System.out.print("\nCountry3 == >"+searchcountry);

    }


    searchcategory = request.getParameterValues("cate");

    searchkeyword = request.getParameter("keyword");

    if(request.getParameter("keyword")==null){      

        searchkeyword="";

        //System.out.print("\nKeyword3 == >"+searchkeyword); 

}

%>

<%

    Connection conn = null;

    Class.forName("com.mysql.jdbc.Driver").newInstance();

    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/itfnet_itfnetportal","itfnet_itfnet", "qwerty");

    //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/itfnet_itfnetportal","root", "password");


    ResultSet rsPagination = null;

    ResultSet rsRowCnt = null;

    ResultSet rsCatogeries = null;


    PreparedStatement psPagination=null;

    PreparedStatement psRowCnt=null;

    PreparedStatement psCatogeries = null;


    int iShowRows=5;  // Number of records show on per page

    int iTotalSearchRecords=10;  // Number of pages index shown


    int iTotalRows=nullIntconv(request.getParameter("iTotalRows"));

    int iTotalPages=nullIntconv(request.getParameter("iTotalPages"));

    int iPageNo=nullIntconv(request.getParameter("iPageNo"));

    int cPageNo=nullIntconv(request.getParameter("cPageNo"));


    int iStartResultNo=0;

    int iEndResultNo=0;


    if(iPageNo==0)

    {

        iPageNo=0;

    }

    else{

        iPageNo=Math.abs((iPageNo-1)*iShowRows);

    }

    
    System.out.print("\n===> 1");

    
   String sqlPagination = "SELECT distinct(`tradeinfo_id`), `tradeinfo_name`, `tradeinfo_address`, "

                            +"`tradeinfo_address2`, `tradeinfo_city`, `tradeinfo_state`, `tradeinfo_country`, "

                            +"`tradeinfo_postal`, `tradeinfo_desc`, `tradeinfo_contact`, `tradeinfo_fax`, "

                            +"`tradeinfo_email`, `tradeinfo_web`, `tradeinfo_cat` "

                            +"FROM tradeinfo " ;                          

 
   if(searchcountry!=null && !searchcountry.equals("0") && !searchcountry.equals("")){

        sqlPagination += " WHERE tradeinfo_country ='"+searchcountry+"'";			

    }

   System.out.print("\n===> 2"); 

   if (searchcategory != null && searchcategory.length > 0) {

        if (searchcountry==null || searchcountry.equals("") || searchcountry.equals("0")) {

            sqlPagination += " WHERE (";           

        }else{

            sqlPagination += " AND (";   

        }


        for (int i = 0; i < searchcategory.length; i++) {

            sqlPagination += "tradeinfo_cat LIKE '%"+searchcategory[i]+"%'";             

            
            if (i < searchcategory.length-1 ){                            

                sqlPagination += " OR ";

            }                

        }

        sqlPagination += ")";  

    }

    System.out.print("\n===> 3");

    if (searchkeyword!=null && searchkeyword != ""){

        if (searchcountry == null || searchcountry =="" || searchcountry =="0" ){

            if (searchcategory == null || searchcategory.length < 1) {

                sqlPagination += " WHERE tradeinfo_desc LIKE '%"+searchkeyword+"%'";              

            }else{

                sqlPagination += " AND tradeinfo_desc LIKE '%"+searchkeyword+"%'";

            }            

        }else{

            sqlPagination += " AND tradeinfo_desc LIKE '%"+searchkeyword+"%'";

        }          

    }

 
    sqlPagination += " ORDER BY tradeinfo_country";

    sqlPagination += " LIMIT "+iPageNo+","+iShowRows+"";

    psPagination=conn.prepareStatement(sqlPagination);

    rsPagination=psPagination.executeQuery();

    System.out.print("\n"+sqlPagination);

    System.out.print("\n===> 4");

    //==========================================================================

    //this will count total number of rows

    
    String sqlRowCnt =  "SELECT count(distinct(`tradeinfo_id`)) as cnt "

                       +"FROM tradeinfo ";

      
     if(searchcountry!=null && !searchcountry.equals("0") && !searchcountry.equals("")){

        sqlRowCnt += " WHERE tradeinfo_country ='"+searchcountry+"'";			

    }

    System.out.print("\n===> 5");

   if (searchcategory != null && searchcategory.length > 0) {

        if (searchcountry==null || searchcountry.equals("") || searchcountry.equals("0")) {

            sqlRowCnt += " WHERE (";           

        }else{

            sqlRowCnt += " AND (";   

        }


        for (int i = 0; i < searchcategory.length; i++) {

            sqlRowCnt += "tradeinfo_cat LIKE '%"+searchcategory[i]+"%'";             

            
            if (i < searchcategory.length-1 ){                            

                sqlRowCnt += " OR ";

            }                

        }

        sqlRowCnt += ")";  

    }

    System.out.print("\n===> 6");

    if (searchkeyword!=null && searchkeyword != ""){

        if (searchcountry == null || searchcountry =="" || searchcountry =="0" ){

            if (searchcategory == null || searchcategory.length < 1) {

                sqlRowCnt += " WHERE tradeinfo_desc LIKE '%"+searchkeyword+"%'";              

            }else{

                sqlRowCnt += " AND tradeinfo_desc LIKE '%"+searchkeyword+"%'";

            }            

        }else{

            sqlRowCnt += " AND tradeinfo_desc LIKE '%"+searchkeyword+"%'";

        }          

    }

    System.out.print("\n===> 7");

    psRowCnt=conn.prepareStatement(sqlRowCnt);

    rsRowCnt=psRowCnt.executeQuery();


    if(rsRowCnt.next())

    {

        iTotalRows=rsRowCnt.getInt("cnt");

    }

    //System.out.print("\nErr-9");


%>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

   "http://www.w3.org/TR/html4/loose.dtd">


<html>

    <head>

        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

        <title>Trade Information Directory</title>

        <link rel="stylesheet" type="text/css" href="../css/itfnet.css" /> 

        <link rel="shortcut icon" href="../images/favicon.ico" />

        <style type="text/css">

        .pre-wrap {

         /*overflow-x: auto;*/ /* Use horizontal scroller if needed; for Firefox 2, not needed in Firefox 3 */

         white-space: pre-wrap; /* css-3 */

         white-space: -moz-pre-wrap !important; /* Mozilla, since 1999 */

         white-space: -pre-wrap; /* Opera 4-6 */

         white-space: -o-pre-wrap; /* Opera 7 */

         /* width: 99%; */

         word-wrap: break-word; /* Internet Explorer 5.5+ */

         font: normal 11px Verdana, Arial, Helvetica, sans-serif;

        }

        </style>

        <style type="text/css">

        #xsnazzy h1, #xsnazzy h2, #xsnazzy p {margin:0 10px; letter-spacing:1px; padding:0; }

        #xsnazzy h1 {font-size:2.5em; color:#fff;}

        #xsnazzy h2 {font-size:2em;color:#06a; border:0;}

        #xsnazzy p {padding-bottom:0.5em;}

        #xsnazzy h2 {padding-top:0.5em;}

        #xsnazzy {background: transparent; margin:25px 1em 100px 1em;}


        .xtop, .xbottom {display:block; background:transparent; font-size:1px;}

        .xb1, .xb2, .xb3, .xb4 {display:block; overflow:hidden;}

        .xb1, .xb2, .xb3 {height:1px;}

        .xb2, .xb3, .xb4 {background:#e1e1e1; border-left:1px solid #08c; border-right:1px solid #08c;}

        .xb1 {margin:0 5px; background:#08c;}

        .xb2 {margin:0 3px; border-width:0 2px;}

        .xb3 {margin:0 2px;}

        .xb4 {height:2px; margin:0 1px;}


        .xboxcontent {display:block; background:#e1e1e1; border:0 solid #08c; border-width:0 1px;font: normal 11px Verdana, Arial, Helvetica, sans-serif;}

        .clear {display:block; height:0; overflow:hidden; clear:both;}


        .tabletrade { margin: 1em; border-collapse: collapse; }

        .tdtrade, .thtrade { padding: .3em; border: 1px #ccc solid; }

     </style>


    </head>

    <body>

        <div align="center" style="margin-top:0px;"><img src="../images/contents/tradeinfo/trade_directory.jpg" width="910" height="170" /></div>

        <div>

            <div id="TradeContents" class="TradeContents">

            <!--img style="width:600px; height:50px; padding-left:5px;" src="../images/header/title/trade_ListOfTradeAndPromotion.jpg" /-->

            <br>Username :<% out.print(session.getAttribute("xname")); %>

            <div align="right" style="padding-right:35px;"><a href="http://www.itfnet.org">Back to TFNet Website</a>&nbsp;|&nbsp;<a href="about.html">About</a>

                    <%  if(session.getAttribute("xname")!=null && session.getAttribute("xname")!=""){%>

                        &nbsp;|&nbsp;<a href="logout.jsp">Logout</a>

                    <% }%>

            <br><br></div><hr>

            <br>

            <jsp:include flush="true" page="search.jsp"/>             

<%

        //// calculate next record start record  and end record

        System.out.print("\niPageNo :"+iPageNo);

        System.out.print("\niTotalRows :"+iTotalRows);

        try{

            if(iTotalRows<(iPageNo+iShowRows))

            {

                iEndResultNo=iTotalRows;

            }

            else

            {

                iEndResultNo=(iPageNo+iShowRows);

            }


            iStartResultNo=(iPageNo+1);

            iTotalPages=((int)(Math.ceil((double)iTotalRows/iShowRows)));


        }

        catch(Exception e)

        {

            e.printStackTrace();

        }


%><br><div align="center">

<% out.println("<strong>"+iTotalRows +"</strong> record(s) found."); %>

    <% if (iTotalRows != 0) { %>Page:&nbsp; <% } %>

<%

        //// index of pages


        int i=0;

        int cPage=0;

        if(iTotalRows!=0)

        {

            cPage=((int)(Math.ceil((double)iEndResultNo/(iTotalSearchRecords*iShowRows))));


            int prePageNo=(cPage*iTotalSearchRecords)-((iTotalSearchRecords-1)+iTotalSearchRecords);

            if((cPage*iTotalSearchRecords)-(iTotalSearchRecords)>0)

            {

%>

              <a href="index.jsp?page=0&process=1&iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>&country=<%=searchcountry%>

                   <% 

                if (searchcategory != null && searchcategory.length > 0) {

                    out.print("&");

System.out.print("\n=======> Err 3f");   

                    for(int f=0;f<searchcategory.length;f++){

                            out.print("cate="+searchcategory[f]);

                            if(f<searchcategory.length-1){

                                out.print("&");

                            }

                        }                                

                }                   

                   %>  

                  "> << Previous</a>  

<%

            }


            for(i=((cPage*iTotalSearchRecords)-(iTotalSearchRecords-1));i<=(cPage*iTotalSearchRecords);i++)

            {

              if(i==((iPageNo/iShowRows)+1))

              {

              %>

                
              <a href="index.jsp?page=0&process=1&iPageNo=<%=i%>&country=<%=searchcountry%><%    

                if (searchcategory != null && searchcategory.length > 0) {

                    out.print("&");

                    for(int d=0;d<searchcategory.length;d++){

                            out.print("cate="+searchcategory[d]);

                            if(d<searchcategory.length-1){

                                out.print("&");

                            }

                        }                                

                }                   

                    %> 

                &keyword=<%=searchkeyword%>" style="cursor:pointer;color: red"><b><%=i%></b></a>

              <%

              }

              else if(i<=iTotalPages)

              {

              %>

                 <a href="index.jsp?page=0&process=1&iPageNo=<%=i%>&country=<%=searchcountry%><%

                if (searchcategory != null && searchcategory.length > 0) {

                    out.print("&");

                        for(int c=0;c<searchcategory.length;c++){

                            out.print("cate="+searchcategory[c]);

                            if(c<searchcategory.length-1){

                                out.print("&");

                            }

                        }                                

                }                   

                    %>&keyword=<%=searchkeyword%>"><%=i%></a>

              <%

              }

            }

            if(iTotalPages>iTotalSearchRecords && i<iTotalPages)

            {

             %>

                <a href="index.jsp?page=0&process=1&iPageNo=<%=i%>&cPageNo=<%=i%>&country=<%=searchcountry%>&keyword=<%=searchkeyword%>"> >> Next</a>

             <%

            }

        }

      %>


</div>


  <div id="info">

    <div id="xsnazzy">


    <%

     while(rsPagination.next()) {

        memId = rsPagination.getString("tradeinfo_id");

        cname = rsPagination.getString("tradeinfo_name");

        add1 = rsPagination.getString("tradeinfo_address");

        add2 = rsPagination.getString("tradeinfo_address2");

        add3 = rsPagination.getString("tradeinfo_city");

        add4 = rsPagination.getString("tradeinfo_state");

        country = rsPagination.getString("tradeinfo_country");

        add5 = rsPagination.getString("tradeinfo_postal");

        add = add1 + "," + add2 + "," + add5 + "," + add3 + "," + add4;

        cat = rsPagination.getString("tradeinfo_cat");

        contact = rsPagination.getString("tradeinfo_contact");

        email = rsPagination.getString("tradeinfo_email");

        website = rsPagination.getString("tradeinfo_web");

        desc = rsPagination.getString("tradeinfo_desc");

    %>


<b class="xtop">

        <b class="xb1"></b>

        <b class="xb2"></b>

        <b class=xb3></b>

        <b class="xb4"></b>

    </b>

<div class="xboxcontent" align="center">


    <table width="100%" align="center">

        <tbody align="left">

            <tr valign="top">

                <td width="30%" valign="top">Company Name</td>

                <td valign="top">:</td>

                <td valign="top"><% out.println(cname);%></td></tr>

            <tr valign="top">

                <td valign="top">Category</td>

                <td valign="top">:</td>

                <td valign="top">

                    <% 

                    String categories = "";

                    StringTokenizer st=new StringTokenizer(cat,",");

                    
                    while(st.hasMoreTokens())

                    {

                        String token=st.nextToken();

                        //out.print(token);

                        int t = Integer.parseInt(token);

                        String sqlCat   = "SELECT category FROM tradeinfocategory WHERE id="+t;

                        //out.print(sqlCat);

                        psCatogeries    = conn.prepareStatement(sqlCat);

                        rsCatogeries    = psCatogeries.executeQuery();

                        out.print("<div align='left'><ul>");

                        while(rsCatogeries.next()){

                            //System.out.print("\nErr-Cat-4");

                            categories      = rsCatogeries.getString("category");

                            out.print("<li>"+categories+"</li>");                            

                        }

                        out.print("</ul></div>");

                    }

                    
                   // out.print(categories);

                    psCatogeries.close();

                    rsCatogeries.close();

                    %>

                </td></tr>

            <tr valign="top">

                <td>Country</td>

                <td valign="top">:</td>

                <td><% out.println(country);%></td></tr>

            <tr valign="top">

                <td>Main Product &amp; Description</td>

                <td valign="top">:</td>

                <td valign="top" class="pre-wrap"><% out.println(desc);%></td>

	     </tr>

            <tr valign="top">

                <td></td>

                <td valign="top"></td>

                <td><div align="right">

                        <a href="member_detail.jsp?page=0&process=2&id=<%=memId%>">

                        <img src="../images/moreInfo.gif" border="0">

                        </a>

                    </div>


                </td>

            </tr>

        </tbody>

    </table>

</div>

<b class="xbottom">

    <b class="xb4"></b>

    <b class=xb3></b>

    <b class="xb2"></b>

    <b class="xb1"></b>

</b> <br> 

    
<% } %>

    </div>

            </div>

  </div>

        </div>


    </body>

</html>