<%@ page  contentType="text/html;charset=utf-8" 
        import="java.sql.DriverManager,
                   java.sql.Connection,
                   java.sql.Statement,
                   java.sql.ResultSet,
                   java.sql.SQLException" %>
<%
  response.setContentType("text/html;charset=utf-8;");
  request.setCharacterEncoding("utf-8");    
  Class.forName("com.mysql.jdbc.Driver");  
  String DB_URL = 
          "jdbc:mysql://27.125.207.9:3306/idc?useUnicode=true&characterEncoding=utf-8";

  String DB_USER = "idc";
  String DB_PASSWORD= "12clqkidc@!";
  Connection conn= null;
  Statement stmt = null;
  Statement stmt1 = null;
  Statement stmt2 = null;
  Statement stmt3 = null;
  ResultSet rs   = null;
  ResultSet rs1   = null;
  ResultSet rs2   = null;
  ResultSet rs3   = null;

  try {
      conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
      stmt = conn.createStatement();
      stmt1 = conn.createStatement();
      stmt2 = conn.createStatement();
      stmt3 = conn.createStatement();
      

      //String query = "SELECT clientdb.Client_Code,Client_Name,service_line,server_code,service_ip,service_payment_date,service_amount,service_nonpayment,service_note,adddb_case,adddb.client_code,adddb.service_code,adddb_nonpayment,adddb_payment,adddb_accountStatus,addDB_ip,addDB_payment_date FROM clientdb INNER JOIN servicedb ON clientdb.client_code = servicedb.client_code INNER JOIN adddb ON servicedb.service_code = adddb.service_code WHERE servicedb.service_code = adddb.service_code AND adddb_accountStatus='incomplete' AND adddb.client_code not in ('C116','C219') ORDER BY service_payment_date,Client_Name ASC ";
      //전체 과금
      //String query = "SELECT clientdb.Client_Code,Client_Name,service_line,server_code,service_ip,service_payment_date,service_amount,service_nonpayment,service_note,adddb_case,adddb.client_code,adddb.service_code,adddb_nonpayment,adddb_payment,adddb_accountStatus,addDB_ip,addDB_payment_date FROM clientdb INNER JOIN servicedb ON clientdb.client_code = servicedb.client_code INNER JOIN adddb ON servicedb.service_code = adddb.service_code WHERE servicedb.service_code = adddb.service_code AND  adddb.client_code not in ('C116','C219') ORDER BY service_payment_date,Client_Name,adddb_accountStatus ASC ";
      // 전체미납
      //String query = "SELECT clientdb.Client_Code,Client_Name,service_line,server_code,service_ip,service_payment_date,service_amount,service_nonpayment,service_note,adddb_case,adddb.client_code,adddb.service_code,adddb_nonpayment,adddb_payment,adddb_accountStatus,addDB_ip,addDB_payment_date FROM clientdb INNER JOIN servicedb ON clientdb.client_code = servicedb.client_code INNER JOIN adddb ON servicedb.service_code = adddb.service_code WHERE servicedb.service_code = adddb.service_code AND  adddb.client_code not in ('C116','C219') AND adddb.adddb_accountStatus not in ('complete') ORDER BY service_payment_date,Client_Name,adddb_accountStatus ASC ";
      //당일 미납 쿼리
      String query = "SELECT clientdb.Client_Code,Client_Name,service_line,server_code,service_ip,service_payment_date,service_amount,service_nonpayment,service_note,adddb_case,adddb.client_code,adddb.service_code,adddb_nonpayment,adddb_payment,adddb_accountStatus,addDB_ip,addDB_payment_date FROM clientdb INNER JOIN servicedb ON clientdb.client_code = servicedb.client_code INNER JOIN adddb ON servicedb.service_code = adddb.service_code WHERE servicedb.service_code = adddb.service_code AND  adddb.client_code not in ('C116','C219') AND adddb.adddb_accountStatus not in ('complete') AND service_payment_date = CURDATE() ORDER BY service_payment_date,Client_Name,adddb_accountStatus ASC";
      rs = stmt.executeQuery(query);
      //1일전 쿼리
      String query1 = "SELECT clientdb.Client_Code,Client_Name,service_line,server_code,service_ip,service_payment_date,service_amount,service_nonpayment,service_note,adddb_case,adddb.client_code,adddb.service_code,adddb_nonpayment,adddb_payment,adddb_accountStatus,addDB_ip,addDB_payment_date FROM clientdb INNER JOIN servicedb ON clientdb.client_code = servicedb.client_code INNER JOIN adddb ON servicedb.service_code = adddb.service_code WHERE servicedb.service_code = adddb.service_code AND  adddb.client_code not in ('C116','C219') AND adddb.adddb_accountStatus not in ('complete') AND service_payment_date = Date_Add(curdate(),INTERVAL 1 DAY) ORDER BY service_payment_date,Client_Name,adddb_accountStatus ASC ";
      rs1 = stmt1.executeQuery(query1);
      //2일전 쿼리
      String query2 = "SELECT clientdb.Client_Code,Client_Name,service_line,server_code,service_ip,service_payment_date,service_amount,service_nonpayment,service_note,adddb_case,adddb.client_code,adddb.service_code,adddb_nonpayment,adddb_payment,adddb_accountStatus,addDB_ip,addDB_payment_date FROM clientdb INNER JOIN servicedb ON clientdb.client_code = servicedb.client_code INNER JOIN adddb ON servicedb.service_code = adddb.service_code WHERE servicedb.service_code = adddb.service_code AND  adddb.client_code not in ('C116','C219') AND adddb.adddb_accountStatus not in ('complete') AND service_payment_date = Date_Add(curdate(),INTERVAL 2 DAY) ORDER BY service_payment_date,Client_Name,adddb_accountStatus ASC ";
      rs2 = stmt2.executeQuery(query2);
      //3일전 쿼리
      String query3 = "SELECT clientdb.Client_Code,Client_Name,service_line,server_code,service_ip,service_payment_date,service_amount,service_nonpayment,service_note,adddb_case,adddb.client_code,adddb.service_code,adddb_nonpayment,adddb_payment,adddb_accountStatus,addDB_ip,addDB_payment_date FROM clientdb INNER JOIN servicedb ON clientdb.client_code = servicedb.client_code INNER JOIN adddb ON servicedb.service_code = adddb.service_code WHERE servicedb.service_code = adddb.service_code AND  adddb.client_code not in ('C116','C219') AND adddb.adddb_accountStatus not in ('complete') AND service_payment_date = Date_Add(curdate(),INTERVAL 3 DAY) ORDER BY service_payment_date,Client_Name,adddb_accountStatus ASC ";
      rs3 = stmt3.executeQuery(query3);
 %>
<a href=/idcProject.main>돌아가기</a>
* 당일미납
<table border="1" cellspacing="0" text-align="center" align="center">
<tr>
<td>체크</td>
<td>코드</td>
<td>고객명</td>
<td>장비번호</td>
<td>서비스코드</td>
<td>아이피</td>
<td>결제일1</td>
<td>결제일2</td>
<td>서비스가</td>
<td>결제금액</td>
<td>미납</td>
<td>미납2</td>
<td>종류</td>
<td>완료</td>

</tr>
<%
    while(rs.next()) { //rs 를 통해 테이블 객체들의 필드값을 넘겨볼 수 있다.
%><tr>
<td><input type="radio"></td>
<td><%=rs.getString(1)%></td>
<td><a href="/idcProject/IdcDepositNonPaymentList.dep?searchContent=<%=rs.getString("Client_Name")%>"><%=rs.getString("Client_Name")%></a></td>
<td><%=rs.getString(4)%></td>
<td><%=rs.getString("service_code")%></td>
<td><%=rs.getString(5)%></td>

<td><%=rs.getString(6)%></td>
<td><%=rs.getString(17)%></td>
<td><%=rs.getString(7)%></td>
<td><%=rs.getString(14)%></td>
<td><%=rs.getString(8)%></td>
<td><%=rs.getString(13)%></td>
<td><%=rs.getString(10)%></td>

<td><%=rs.getString(15)%></td>
</td>
</tr>
<%
    } // end while
%></table>

* 과금 1일전
<table border="1" cellspacing="0" text-align="center" align="center">
<tr>
<td>체크</td>
<td>코드</td>
<td>고객명</td>
<td>장비번호</td>
<td>서비스코드</td>
<td>아이피</td>
<td>결제일1</td>
<td>결제일2</td>
<td>서비스가</td>
<td>결제금액</td>
<td>미납</td>
<td>미납2</td>
<td>종류</td>
<td>완료</td>

</tr>
<%
    while(rs1.next()) { //rs 를 통해 테이블 객체들의 필드값을 넘겨볼 수 있다.
%><tr>
<td><input type="radio"></td>
<td><%=rs1.getString(1)%></td>
<td><a href="/idcProject/IdcDepositNonPaymentList.dep?searchContent=<%=rs1.getString("Client_Name")%>"><%=rs1.getString("Client_Name")%></a></td>
<td><%=rs1.getString(4)%></td>
<td><%=rs1.getString("service_code")%></td>
<td><%=rs1.getString(5)%></td>

<td><%=rs1.getString(6)%></td>
<td><%=rs1.getString(17)%></td>
<td><%=rs1.getString(7)%></td>
<td><%=rs1.getString(14)%></td>
<td><%=rs1.getString(8)%></td>
<td><%=rs1.getString(13)%></td>
<td><%=rs1.getString(10)%></td>

<td><%=rs1.getString(15)%></td>
</td>
</tr>
<%
    } // end while
%></table>

* 과금 2일전
<table border="1" cellspacing="0" text-align="center" align="center">
<tr>
<td>체크</td>
<td>코드</td>
<td>고객명</td>
<td>장비번호</td>
<td>서비스코드</td>
<td>아이피</td>
<td>결제일1</td>
<td>결제일2</td>
<td>서비스가</td>
<td>결제금액</td>
<td>미납</td>
<td>미납2</td>
<td>종류</td>
<td>완료</td>

</tr>
<%
    while(rs2.next()) { //rs 를 통해 테이블 객체들의 필드값을 넘겨볼 수 있다.
%><tr>
<td><input type="radio"></td>
<td><%=rs2.getString(1)%></td>
<td><a href="/idcProject/IdcDepositNonPaymentList.dep?searchContent=<%=rs2.getString("Client_Name")%>"><%=rs2.getString("Client_Name")%></a></td>
<td><%=rs2.getString(4)%></td>
<td><%=rs2.getString("service_code")%></td>
<td><%=rs2.getString(5)%></td>

<td><%=rs2.getString(6)%></td>
<td><%=rs2.getString(17)%></td>
<td><%=rs2.getString(7)%></td>
<td><%=rs2.getString(14)%></td>
<td><%=rs2.getString(8)%></td>
<td><%=rs2.getString(13)%></td>
<td><%=rs2.getString(10)%></td>

<td><%=rs2.getString(15)%></td>
</td>
</tr>
<%
    } // end while
%></table>

* 과금 3일전
<table border="1" cellspacing="0" text-align="center" align="center">
<tr>
<td>체크</td>
<td>코드</td>
<td>고객명</td>
<td>장비번호</td>
<td>서비스코드</td>
<td>아이피</td>
<td>결제일1</td>
<td>결제일2</td>
<td>서비스가</td>
<td>결제금액</td>
<td>미납</td>
<td>미납2</td>
<td>종류</td>
<td>완료</td>

</tr>
<%
    while(rs3.next()) { //rs 를 통해 테이블 객체들의 필드값을 넘겨볼 수 있다.
%><tr>
<td><input type="radio"></td>
<td><%=rs3.getString(1)%></td>
<td><a href="/idcProject/IdcDepositNonPaymentList.dep?searchContent=<%=rs3.getString("Client_Name")%>"><%=rs3.getString("Client_Name")%></a></td>
<td><%=rs3.getString(4)%></td>
<td><%=rs3.getString("service_code")%></td>
<td><%=rs3.getString(5)%></td>

<td><%=rs3.getString(6)%></td>
<td><%=rs3.getString(17)%></td>
<td><%=rs3.getString(7)%></td>
<td><%=rs3.getString(14)%></td>
<td><%=rs3.getString(8)%></td>
<td><%=rs3.getString(13)%></td>
<td><%=rs3.getString(10)%></td>

<td><%=rs3.getString(15)%></td>
</td>
</tr>
<%
    } // end while
%></table>


<%
  rs.close();        // ResultSet exit
  rs1.close();        // ResultSet exit
  rs2.close();        // ResultSet exit
  rs3.close();        // ResultSet exit
  stmt.close();     // Statement exit
  stmt1.close();     // Statement exit
  stmt2.close();     // Statement exit
  stmt3.close();     // Statement exit
  conn.close();    // Connection exit
}
catch (SQLException e) {
      out.println("err:"+e.toString());
} 
%>