import java.sql.*;
import java.io.*;
import java.util.*;
import java.security.*;
import java.text.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.*;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import org.xml.sax.*;


public class JavaProcessing {


	public static void writeFile(String filename,String content) {

		BufferedWriter bw = null;
		FileWriter fw = null;

		try {

			//String content = "This is the content to write into file\n";

			fw = new FileWriter(filename);
			bw = new BufferedWriter(fw);
			bw.write(content);

			System.out.println("Done");

		} catch (IOException e) {

			e.printStackTrace();

		} finally {

			try {

				if (bw != null)
					bw.close();

				if (fw != null)
					fw.close();

			} catch (IOException ex) {

				ex.printStackTrace();

			}

		}

	}


	public static String readFile(String filename)
	{
		String content = null;
		File file = new File(filename); //for ex foo.txt
		FileReader reader = null;
		try {
			reader = new FileReader(file);
			char[] chars = new char[(int) file.length()];
			reader.read(chars);
			content = new String(chars);
			reader.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if(reader !=null){
					reader.close();
				}
			} catch (IOException e) {
			e.printStackTrace();
			}
		}
		return content;
	}

	public static String replaceString(String ConnectionString,String pass_new,String f1,String f2 ){
            try
            {                
                int p1 = ConnectionString.indexOf(f1);
                 //System.out.println(p1);
                int p2 = (ConnectionString.substring(p1)).indexOf(f2);
                 //System.out.println(p2);
                String pass = ConnectionString.substring(p1, p1+p2).replaceAll (f1, "") ;
                 //System.out.println(pass);
                 //System.out.println(ConnectionString.replaceAll ("Password=" + pass, "Password=" + pass_new));
                return ConnectionString.replaceAll ("Password=" + pass, "Password=" + pass_new);
            }
            catch (Exception e) {
				e.printStackTrace();
                return ConnectionString ;
            }
        }

	public static String MD5(String md5) {
	   try {
			java.security.MessageDigest md = java.security.MessageDigest.getInstance("MD5");
			byte[] array = md.digest(md5.getBytes());
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < array.length; ++i) {
			  sb.append(Integer.toHexString((array[i] & 0xFF) | 0x100).substring(1,3));
		   }
			return sb.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

    Connection connection = null;
	
	public void init(String[] argv) {
	    //com.mysql.jdbc.Driver jdbc:mysql://192.168.10.149:3306/iscocukj iscocukj iscocukj DIV 2015/03/20 2016/12/31 0.03 YR_2559_3.0 2016
		String connString="";
        String usr=argv[2],pass=argv[3];
		
        System.out.println("--------  JDBC Connection Testing ------");

        try {

            //Class.forName("oracle.jdbc.driver.OracleDriver");
			//Class.forName("com.mysql.jdbc.Driver");
			Class.forName(argv[0]);

        } catch (ClassNotFoundException e) {

            System.out.println("Where is your JDBC Driver?");
            e.printStackTrace();
            return;

        }

        System.out.println(" JDBC Driver Registered!");

        try {

			//connString="jdbc:oracle:thin:@localhost:1521:icoop";
			
		    connString=argv[1];//"jdbc:mysql://192.168.10.149:3306/iscocukj";
		
            connection = DriverManager.getConnection(connString, usr, pass);
					
			String process = argv[4];	
					
			if(process.equals("DIV")){
			
			   processDIV(argv) ;
			   
			}else if(process.equals("EXP_IMP_DATA_BRANCH")){
			
			   processEXPIMP(argv) ;
			   
			}
		
        connection.close();
			
        } catch (Exception e) {

            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return;

        }

        if (connection != null) {
		  
            System.out.println("You made it, take control your database now!");
        } else {
            System.out.println("Failed to make connection!");
        }
		
		/*
		try{
		
			String filename=new File("").getAbsolutePath()+"\\server.connection_string.xml";	
			File xmlFile=new File(filename);
			XmlParserFile xml =new XmlParserFile(xmlFile);
			String connection_string = xml.getItemString(index, "connection_string");
            System.out.println("Read "+filename+" successful");
            System.out.println("Read data "+index+":"+connection_string+" ");
			
			String f1="Password=";
			String f2=";";
			connection_string=replaceString(connection_string,target_userpass_new,f1,f2);
            System.out.println("data "+connection_string+" ");
			boolean status=xml.setItemString(index, "connection_string",connection_string);
			connection_string = xml.getItemString(index, "connection_string");
            System.out.println("write data "+index+":"+connection_string+" successful");	
					xml.zerialize();			
            System.out.println("write "+filename+" successful");			
		
		}catch(Exception e){
				e.printStackTrace();
		}
		*/
		
    }
		
    public static void main(String[] argv) {
	  JavaProcessing p = new JavaProcessing();
	   p.init(argv) ;
	}

	public void processEXPIMP(String[] argv) throws Exception {
	   
	   
	   
	}    
	
	public void processDIV(String[] argv) throws Exception {
	    
		String filter =" order by m.member_no asc ";
        Statement  stmtM  = null;
        Statement  stmtE  = null;
		String sql="";
		
			stmtM = connection.createStatement();
			stmtE = connection.createStatement();
			
			/*
					drop table yrcfrate cascade ;

					CREATE TABLE yrcfrate (
					DIV_YEAR char(5) NOT NULL, 
					COOP_ID char(6) NOT NULL, 
					AVGPERCENT_RATE decimal(8,6) , 
					LOCKPROC_FLAG decimal(1,0) , 
					DIVPERCENT_RATE decimal(8,6) ,
					startdate datetime, enddate datetime ,bfdate datetime ) ;
					ALTER TABLE yrcfrate ADD PRIMARY KEY (DIV_YEAR , COOP_ID,startdate,enddate,DIVPERCENT_RATE,AVGPERCENT_RATE);

					delete from yrcfrate ;
					insert into yrcfrate
					 (DIV_YEAR,COOP_ID,AVGPERCENT_RATE,LOCKPROC_FLAG,DIVPERCENT_RATE,startdate , enddate  ,bfdate  ) 
					 values('2559','500001',0.03,0,0.03, str_to_date('2015/03/20','%Y/%m/%d'),str_to_date('2016/12/31','%Y/%m/%d'),str_to_date('2007/01/01','%Y/%m/%d'));
					insert into yrcfrate 
					 (DIV_YEAR,COOP_ID,AVGPERCENT_RATE,LOCKPROC_FLAG,DIVPERCENT_RATE,startdate , enddate  ,bfdate  ) 
					values('2559','500001',0.02,0,0.02, str_to_date('2008/01/01','%Y/%m/%d'),str_to_date('2015/03/19','%Y/%m/%d'),str_to_date('2007/01/01','%Y/%m/%d'));
					commit;

					drop table YRDIVMAS cascade ;
					create table YRDIVMAS (
					 coop_id varchar(8) not null,
					 div_year varchar(5) not null,
					 member_no varchar(15) not null,
					 ref_memberno varchar(15) not null,
					 seq_no decimal(15,0) not null,
					operate_date datetime,startdate datetime,enddate datetime,
					yr_rate decimal(5,2) default 0.00 not null ,
					share_amtnet decimal(15,4) default 0 not null,
					period_months decimal(4,2) default 0 not null,
					div_months decimal(4,2) default 0 not null,
					precision_months decimal(4,2) default 0 not null,
					precision_months_div decimal(4,2) default 0 not null,
					div_amt decimal(20,10) default 0 not null,
					post_flag decimal(1,0) default 0 not null,
					item_status  decimal(1,0) default 1 not null
					);
					ALTER TABLE YRDIVMAS ADD PRIMARY KEY (COOP_ID,div_year,member_no , seq_no,startdate,enddate,yr_rate);
					
			*/
							
			try{
			
			   // sql="drop table YRDIVMAS cascade;";				
			   // stmtM.execute(sql);
				
				/*
			    sql="create table YRDIVMAS (  "+
				" member_no varchar(15) not null, "+
				" ref_memberno varchar(15) not null, "+
				" yr_ratecode varchar(15) not null, "+
				" yr_year decimal(4,0) default 0 not null , "+
				" yr_rate decimal(10,2) default 0.00 not null , "+
				" startdate datetime, "+
				" enddate datetime , "+
				" yrbfshare_amt decimal(15,2) default 0.00 not null , "+
				" yrshare_sum decimal(15,2) default 0.00 not null , "+
				" yrtotal_amt decimal(15,2) default 0.00 not null , "+
				" post_flag decimal(1,0) default 0 not null  , "+
				" item_status decimal(1,0) default 1 not null )";
				
				stmtE.execute(sql);
				
			    System.out.println("Create TABLE YRDIVMAS ");
				*/
			}catch(Exception e1) {
			
			} 		
			
			try{
			
			    /*
				sql="ALTER TABLE YRDIVMAS ADD PRIMARY KEY (member_no , ref_memberno,yr_rate,startdate,enddate,yr_ratecode,yr_year)";
				
				stmtE.execute(sql);
				
			    System.out.println("Create PK YRDIVMAS ");
				*/
				
			}catch(Exception e1) {
			
			} 	
				
			String[] arg=argv[5].split("-");	
			
			sql="select m.member_no,m.ref_memberno from mbmembmaster m ";
			String memno="";
			String yr_year=arg[0];//,member_no="01105680";
			
			try{
			  if(arg[1].length()>0){
			   sql+=" where member_no ='"+arg[1]+"' ";
			   memno=arg[1];
			   }
			}catch(Exception e1) {
			
			} 	
			
			ResultSet rsM = stmtM.executeQuery(sql);
					
			long i=0;	
					
			System.out.println("DIV : YEAR "+yr_year);
			System.out.println("============");
						
						
			while (rsM.next()) {
			
			  String member_no = rsM.getString("member_no");
			  String ref_memberno = rsM.getString("ref_memberno");
			
			   System.out.println((++i)+". MEMBER_NO="+member_no+", MEMBER_NO_REF="+ref_memberno);
			
					//System.out.println("sql : Successfull");
					
					    try{
							sql="delete from YRDIVMAS where member_no='"+member_no+"' and div_year='"+yr_year+"' and post_flag=0";
							stmtE.execute(sql);				
							//System.out.println("sql : "+sql);	
					 
						}catch(Exception esx ){
							 esx.printStackTrace();
						}	

					sql="insert into YRDIVMAS ( coop_id ,div_year,member_no, ref_memberno ,seq_no ,operate_date ,startdate ,enddate , "+
							" yr_rate ,share_amtnet,period_months,div_months,precision_months,precision_months_div,div_amt,post_flag,item_status)( "+
							" select  "+
							" y.coop_id ,y.div_year,y.member_no, y.ref_memberno ,y.seq_no ,y.operate_date ,y.startdate ,y.enddate , "+
							" y.yr_rate ,y.share_amtnet,y.period_months,y.div_months,y.precision_months,y.precision_months_div, "+
							" y.share_amtnet*(y.precision_months/y.precision_months_div)*y.yr_rate as div_amt ,0 as post_flag, 1 as item_status "+
							" from ( "+
							" select  m.coop_id,d.div_year,m.member_no,m.ref_memberno,st.seq_no ,st.operate_date  "+
							" ,d.startdate,d.enddate ,d.divpercent_rate as yr_rate "+
							" ,st.share_amount*sh.unitshare_value as share_amtnet  "+
							" ,period_diff(date_format( d.enddate  , '%Y%m'), date_format(st.operate_date, '%Y%m')) as period_months  "+
							" ,period_diff(date_format( d.enddate  , '%Y%m'), date_format(d.startdate, '%Y%m')) as div_months  "+
							" ,round( "+
							" TIMESTAMPDIFF(MONTH, st.operate_date, d.enddate ) + "+
  							" DATEDIFF( "+
    							" d.enddate , "+
    							" st.operate_date + INTERVAL "+
      							" TIMESTAMPDIFF(MONTH, st.operate_date,d.enddate ) "+
    							" MONTH "+
  							" ) / DATEDIFF( "+
    							" st.operate_date + INTERVAL "+
      							" TIMESTAMPDIFF(MONTH, st.operate_date, d.enddate ) + 1 "+
    							" MONTH, "+
    							" st.operate_date + INTERVAL "+
      							" TIMESTAMPDIFF(MONTH, st.operate_date, d.enddate ) "+
    							" MONTH "+
  							" ) , 0) as precision_months   "+
							" ,round( "+
							" TIMESTAMPDIFF(MONTH,  d.startdate, d.enddate ) + "+
  							" DATEDIFF( "+
    							" d.enddate , "+
     							" d.startdate + INTERVAL "+
      							" TIMESTAMPDIFF(MONTH,  d.startdate,d.enddate ) "+
    							" MONTH "+
  							" ) / DATEDIFF( "+
     							" d.startdate + INTERVAL "+
      							" TIMESTAMPDIFF(MONTH,  d.startdate, d.enddate ) + 1 "+
    							" MONTH, "+
    							" d.startdate + INTERVAL "+
      							" TIMESTAMPDIFF(MONTH,  d.startdate, d.enddate ) "+
    							" MONTH "+
  							" ) , 0) as precision_months_div   "+
							" from mbmembmaster m ,shsharemaster s,shsharestatement st,shsharetype sh  "+
							" , yrcfrate as d   "+
							" where s.sharemaster_status = '1'  "+
							" and m.member_no='"+member_no+"'  "+
							" and d.div_year='"+yr_year+"'  "+
							" and st.operate_date between d.startdate  and d.enddate  "+
							" and s.member_no=m.member_no and s.member_no=st.member_no   "+
							" order by  m.member_no asc,st.seq_no asc "+
							" ) y  "+
							" ) ";
					/*
					if(mode.equals("A")){ 
						java.util.Date date = new java.util.Date(); 
						SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); 
						target_userpass_new=JavaProcessing.MD5(sdf.format(date)).substring(0,target_user.length());
						sql="alter user "+target_user+" identified by \""+ target_userpass_new+"\"";
					}
					*/
					
					stmtE.execute(sql);	
					
					//System.out.println("sql : DIV success member_no='"+member_no+"' and div_year='"+yr_year+"' ");
					
					Statement  stmt  = connection.createStatement();
					
					sql="select member_no,div_year,yr_rate,sum(share_amtnet) as share_amtnet,sum(div_amt) as div_amt ,startdate,enddate"+
					" from yrdivmas where member_no='"+member_no+"' and div_year='"+yr_year+"' group by member_no,div_year,yr_rate,startdate,enddate";
					
					ResultSet rs = stmt.executeQuery(sql);
						//System.out.println("sql : "+sql);
					
					boolean found=false;			
					
					while (rs.next()) {
						found=true;
						float yrrate = rs.getFloat("yr_rate");
						double div_amt = rs.getDouble("div_amt");
						double share_amtnet = rs.getDouble("share_amtnet");
						String startdate = rs.getString("startdate").substring(0,10);
						String enddate = rs.getString("enddate").substring(0,10);
						
						System.out.println(" \t RATE="+yrrate+" ,startdate="+startdate+" ,enddate="+enddate+" ,DIV="+div_amt+" ,SHARE="+share_amtnet);
					
						System.out.print(" ");
							
						
					}
					
					if(memno.length()>0){
						
						System.out.println(" ");
						
						sql="select member_no,div_year,yr_rate,share_amtnet,div_amt,startdate,enddate,seq_no,operate_date "+
						" from yrdivmas where member_no='"+member_no+"' and div_year='"+yr_year+"' order by member_no,div_year,yr_rate,startdate,enddate,seq_no,operate_date ";
						
						rs = stmt.executeQuery(sql);
							//System.out.println("sql : "+sql);
						
						found=false;			
						
						while (rs.next()) {
							found=true;
							long seq_no = rs.getLong("seq_no");
							float yrrate = rs.getFloat("yr_rate");
							double div_amt = rs.getDouble("div_amt");
							double share_amtnet = rs.getDouble("share_amtnet");
							String operate_date = rs.getString("operate_date").substring(0,10);
							String startdate = rs.getString("startdate").substring(0,10);
							String enddate = rs.getString("enddate").substring(0,10);
							
							System.out.println(" \t NO.="+seq_no+" ,operate_date="+operate_date+" ,RATE="+yrrate+" ,startdate="+startdate+" ,enddate="+enddate+" ,DIV="+div_amt+" ,SHARE="+share_amtnet);
						
							System.out.print(" ");
								
							
						}
					}
					rs.close();
				    stmt.close();
				
				System.out.println(" "+(found?"":" No Data "));
			}
			rsM.close();			
			stmtM.close();	
			stmtE.close();
	}
	
	
}

class XmlParserFile {
    
	private File xml;
    private Document doc;
    private NodeList nList;

    public XmlParserFile(String xml) throws Exception {
        this.doc = loadXMLFromString(xml);
        String tagRowName = doc.getFirstChild().getChildNodes().item(1).getNodeName();
        nList = doc.getElementsByTagName(tagRowName);
    }

    public XmlParserFile(File xml) throws Exception {
	    this.xml=xml;
        this.doc = loadXMLFromFile(xml);
        String tagRowName = doc.getFirstChild().getChildNodes().item(1).getNodeName();
        nList = doc.getElementsByTagName(tagRowName);
    }
		
	public void zerialize() {
	 try{
	  // write the content into xml file
            TransformerFactory transformerFactory = TransformerFactory.newInstance();
            Transformer transformer = transformerFactory.newTransformer();
            DOMSource source = new DOMSource(this.doc);
            StreamResult result = new StreamResult(this.xml);
            transformer.transform(source, result);
     }catch(Exception e){
				e.printStackTrace();
		}
	  //return data.toString();
	}
	
    public String getItemString(int row, String column) {
        try {
            Node n = nList.item(row);
            for (int i = 0; i < n.getChildNodes().getLength(); i++) {
                if (n.getChildNodes().item(i).getNodeName().toLowerCase().trim().equals(column.trim())) {
                    return n.getChildNodes().item(i).getTextContent();
                }
            }
        } catch (Exception ex) {
        }
        return "";
    }
	
    public boolean setItemString(int row, String column,String value) {
        try {
            Node n = nList.item(row);
            for (int i = 0; i < n.getChildNodes().getLength(); i++) {
                if (n.getChildNodes().item(i).getNodeName().toLowerCase().trim().equals(column.trim())) {
                     n.getChildNodes().item(i).setTextContent(value);
					 doc.getFirstChild().getChildNodes().item(i).setTextContent(value);                     
                     return true;
                }
            }
        } catch (Exception ex) {
        }
        return false;
    }
	
    public int getRowCount() {
        return nList.getLength();
    }

    private Document loadXMLFromString(String xml) throws Exception {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        InputSource is = new InputSource(new StringReader(xml));
        return builder.parse(is);
    }

    private Document loadXMLFromFile(File xml) throws Exception {
        DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
        return dBuilder.parse(xml);
    }
	
}