import java.sql.*;
//import com.microsoft.jdbc.*; // 加載類庫

public class deletenode{
  //定義全局變量

  //構造函數，初始化
  public deletenode()
  {
    System.out.println("開始運行遞歸刪除程序");
  }
  //總循環
  public void dodelete() throws SQLException
    {
        while(true)
        {
            System.out.println("start....");
            dgdelete();        //遞歸減分處理函數
            System.out.println("等待下次....waiting");
            try
            {
                Thread.sleep(1000*60*10);  //timeInterval 由Administrator根據流量設置
            }
            catch (Exception ex)
            {
                System.out.println("Waked up "+ex);
            }
         }
    }
   //遞歸減分處理函數
   private void dgdelete() throws SQLException
   {

     String deletenode = null;
     String deletesbh = null;

     String dgbh = null;
     String dgsbh = null;
     String dglnbh = null;
     String dgrnbh = null;
     String zlnbh = null;
     String zrnbh = null;

     String[][] delete = new String[50][3];

     try {
       Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
       Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;User=sa;Password=sa;DatabaseName=goal");
       System.out.println("connected success!!!!");
       System.out.println("node delete,please wait......");
       Statement stmt = conn.createStatement();

       //從partroot開始，遞歸向上加分直到boss
       String sql2 = "SELECT BH,SBH FROM deletenodetable"; // ?問號就是要傳遞的參數，通過setInt()方法來設置需要的參數
       ResultSet rs2 = stmt.executeQuery(sql2);
       //將查詢結果存入臨時多維數組中
       int rowcount = 0; //查詢結果的行數
       for (int i = 0; rs2.next(); i++) {
         delete[i][0] = rs2.getString("BH");
         delete[i][1] = rs2.getString("SBH");
         rowcount++;
       }
       //show a[][]
       for (int i = 0; i<rowcount; i++) {
         System.out.println(delete[i][0]+"\t"+delete[i][1]);
       }

       for(int j=0;j<rowcount;j++)
       {
         deletenode = delete[j][0];
         deletesbh = delete[j][1];
             //遞歸減分處理直到boss
             dgbh = deletenode; //賦初值
             for(;;){
               String sql4 = "SELECT SBH FROM net WHERE BH=?";
               PreparedStatement ps4 = conn.prepareStatement(sql4);
               ps4.setString(1, dgbh); //為dgbh遞歸編號的值
               ResultSet rs4 = ps4.executeQuery();
               while (rs4.next()) {
                 dgsbh = rs4.getString("SBH");
               }
               System.out.println("dgsbh:" + dgsbh);


               String sql5 = "SELECT LNBH,RNBH FROM net WHERE BH=?";
               PreparedStatement ps5 = conn.prepareStatement(sql5);
               ps5.setString(1, dgsbh); //為dgbh遞歸編號的值
               ResultSet rs5 = ps5.executeQuery();
               while (rs5.next()) {
                 dglnbh = rs5.getString("LNBH");
                 dgrnbh = rs5.getString("RNBH");
               }
               System.out.println("dglnbh:" + dglnbh);
               System.out.println("dgrnbh:" + dgrnbh);

               if (dglnbh != null && dglnbh.equals(dgbh)) {
                 //是其上線的左端子
                 String updatesql = "update net set LPV = LPV - 1000,LMAN = LMAN - 1 where BH='" + dgsbh + "'";
                 stmt.executeUpdate(updatesql);
                 System.out.println("是其上線的左端子");
               }
               if (dgrnbh != null && dgrnbh.equals(dgbh)) {
                 //是其上線的右端子
                 String updatesql2 = "update net set RPV = RPV - 1000,RMAN = RMAN - 1 where BH='" + dgsbh + "'";
                 stmt.executeUpdate(updatesql2);
                 System.out.println("是其上線的右端子");
               }
               if(dgsbh.equals("公司") || dgsbh==null) break; //boss 已加完積分，退出
               dgbh = dgsbh; //指針移到其上線
               System.out.println("指針移到其上線" + dgbh);
             }

             //刪除deletenode在net表中的信息，且置其上線的LNBH,RNBH為NULL
               String sql6 = "SELECT LNBH,RNBH FROM net WHERE BH=?";
               PreparedStatement ps6 = conn.prepareStatement(sql6);
               ps6.setString(1, deletesbh); //為deletesbh編號的值
               ResultSet rs6 = ps6.executeQuery();
               while (rs6.next()) {
                 zlnbh = rs6.getString("LNBH");
                 zrnbh = rs6.getString("RNBH");
               }
               if (zlnbh != null && zlnbh.equals(deletenode)) {
                 //置其上線的左端子為NULL
                 String updatesql3 = "update net set LNBH=NULL where BH='" + deletesbh + "'";
                 stmt.executeUpdate(updatesql3);
                 System.out.println("置其上線的左端子為NULL");
               }
               if (zrnbh != null && zrnbh.equals(deletenode)) {
                 //置其上線的右端子為NULL
                 String updatesql4 = "update net set RNBH=NULL where BH='" + deletesbh + "'";
                 stmt.executeUpdate(updatesql4);
                 System.out.println("置其上線的右端子為NULL");
               }


        }


         System.out.println("Before delete");
         System.out.println("rowcount:" + rowcount);

         String deletesql = "DELETE FROM net WHERE BH=?";
         PreparedStatement ps7 = conn.prepareStatement(deletesql);
         for (int k = 0; k < rowcount; k++) {
           ps7.setString(1, delete[k][0]); //deletenode
           ps7.executeUpdate();
         }

         //刪除的已經減完分的紀錄
         String deletecmd = "DELETE FROM deletenodetable WHERE BH=?";
         PreparedStatement ps5 = conn.prepareStatement(deletecmd);
         for (int k = 0; k < rowcount; k++) {
           ps5.setString(1, delete[k][0]); //deletenode
           ps5.executeUpdate();
         }



     }
     catch (Exception ex) {
       System.err.println(ex.getMessage());
     }

   }

  public static void main(String[] args) throws SQLException{
    //定義變量
    deletenode var = new deletenode(); //定義一個對象,并初始化
    try
       {
            Thread.sleep(1000);  //系統啟動之后5分鐘后才運行后臺程序，要等SQLServer啟動之后
       }
    catch (Exception ex)
       {
                System.out.println("Waked up "+ex);
       }

    var.dodelete();     //開始

  }
}