import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.FileNotFoundException;
import java.io.PrintWriter;
import java.util.Calendar;
import java.util.Arrays;
import java.util.Random;
import java.util.Timer;
import java.util.TimerTask;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.sql.*;

import org.jivesoftware.smack.Chat;
import org.jivesoftware.smack.ConnectionConfiguration;
import org.jivesoftware.smack.MessageListener;
import org.jivesoftware.smack.XMPPConnection;
import org.jivesoftware.smack.XMPPException;
import org.jivesoftware.smack.packet.Message;
import org.jivesoftware.smack.proxy.ProxyInfo;
import org.jivesoftware.smack.proxy.ProxyInfo.ProxyType;
import org.jivesoftware.smack.PacketListener;
import org.jivesoftware.smack.filter.MessageTypeFilter;
import org.jivesoftware.smack.filter.PacketFilter;
import org.jivesoftware.smack.packet.Packet;
import org.jivesoftware.smack.util.StringUtils;


public class receive 
{
    private String username;
    private String password;
	private String inipath = "";
    private String[] ToAddress;

    private Timer timer = new Timer();
    
    //接続先の設定情報
    private String SERVER_ADDRSS = "penta-02.ptgw.net";
    private String SERVICE_NAME = "penta-02.ptgw.net";
    private int SERVER_PORT = 5222;

    private ConnectionConfiguration connectionConfiguration ;
    private XMPPConnection xmppConnection;

	private PacketFilter filter = new MessageTypeFilter(Message.Type.chat);	
    private int tct = 0;
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMdd");

	//データベース接続設定情報
	private String url = "jdbc:postgresql://192.168.100.153:5432/ansys";
	private String user = "postgres";
	private String pass = "postgres";

	//受信データ用変数
	private String androidId;
	private String constId;
	private String appId;
	private String loginUser;
	private String lat;
	private String lon;
	private String acc;
	private String mesdate;
	private String spd;
	private String direction;
	private String upddate;
	private String regdate;

	//SQL文用
	private String insHis;
	private String selTs;
	private String upTs;
	private String selOpfCt;
	private String selOpf;
	private String insOpf;
	private String upOpf;
	private String upOpf2;
	
    public static void main( String[] args )
    {
        receive main = new receive(args);
    }
        
    public receive( String[] args )
    {
		if( args.length < 2)
		{
			System.out.println("usage : java XmppTest [UserAddress] [Password]");
		}

        username = args[0];
        password = args[1];

        //■XMPP接続(ログイン)
        try
        {
			connect();
        }
        catch (XMPPException e)
        {
            System.out.print("connection failed. message="+e.getMessage());
            return;
        }

        //■セッション作成
        try
        {
            SessionCreate();
        }
        catch (XMPPException e)
        {
            System.out.print("Failed to send message. message="+e.getMessage());
            return;
        }
        catch (IOException e)
        {
            System.out.print("IOException occured. message="+e.getMessage());
            return;
        }
    }
        
    /*■■XMPP接続
     * 接続する。
     * @throws XMPPException
     */
    private void connect() throws XMPPException
    {
		//接続情報の設定
        connectionConfiguration = new ConnectionConfiguration(SERVER_ADDRSS, SERVER_PORT,SERVICE_NAME);
		
		//XMPP接続準備
        xmppConnection = new XMPPConnection(connectionConfiguration);
        
		//XMPP接続
        xmppConnection.connect();
        
        //ログイン
        xmppConnection.login(username, password);
    }
        
    /*■■XMPP受信
     * メッセージの受信
     * @throws XMPPException
     * @throws IOException 
     */
    public void SessionCreate() throws XMPPException, IOException
    {
	  			new Thread(new Runnable()
	  			{
	  				@Override
	  				public void run()
	  				{
	  			    	xmppConnection.addPacketListener(_MyPacketListener, filter);
	  				}
  				}).start();
    }

	private PacketListener _MyPacketListener = new PacketListener()
	{
		@Override
		public void processPacket(Packet packet)
		{
			Message message = (Message) packet;
			
			//Log.d(CommonUtility.TAG, "[XMPP.Recieve]start");
  	        
  	        //■受信メッセージのＮｕｌｌ判定
  	        if (message.getBody() != null)
  	        {
  	            String msgBody = message.getBody();
  	            String msgFrom = message.getFrom();
				String[] sp = msgFrom.split("@");
				Calendar now = Calendar.getInstance(); //現在の時刻

				//●受信情報をデータベースに登録
				//受信データを分割
				String[] ReceiveData = msgBody.split(",");

				//[0] = androidID
				//[1] = 工事ID
				//[2] = アプリID
				//[3] = 緯度
				//[4] = 経度
				//[5] = 精度
				//[6] = 測位時間
				//[7] = 速度
				//[8] = 向き

				androidId = ReceiveData[0];
				constId = ReceiveData[1];
				appId = ReceiveData[2];
				loginUser = sp[0];
				lat = ReceiveData[3];
				lon = ReceiveData[4];
				acc = ReceiveData[5];
				Date mdate = new Date(Long.parseLong(ReceiveData[6]));
				mesdate = sdf.format(mdate);
				spd = ReceiveData[7];
				direction = ReceiveData[8];
				String log = "";
				
				try
				{
					
					//データベースに接続
					Connection con = DriverManager.getConnection(url,user,pass);
					log = "接続成功";
					System.out.println("接続成功");
					
					// SQLコンテナの作成
    				Statement stmt = con.createStatement();

					// 位置管理テーブルへ登録
					insHis = "INSERT INTO qgis.ts_his_measure";
					insHis += " (android_id, const_id, app_id, lat, lon, acc, spd, mesdatetime, regdatetime, direction)";
					insHis += " VALUES";
					insHis += " ('" + androidId + "'," + constId + ","+ appId + "," + lat + "," + lon + "," + acc + "," + spd + ",'" + mesdate + "'," + "current_timestamp" + "," + direction + ")";
					
					stmt.executeUpdate(insHis);

					// 端末管理テーブル更新
					// UPDATE
					upTs = "UPDATE qgis.ts_terminal";
					upTs += " SET lat=" + lat + ",";
					upTs += " lon=" + lon + ",";
					upTs += " acc=" + acc + ",";
					upTs += " spd=" + spd + ",";
					upTs += " direction=" + direction + ",";
					upTs += " mesdatetime='" + mesdate + "',";
					upTs += " upddatetime=current_timestamp";
					upTs += " WHERE android_id='" + androidId + "'";
					upTs += " AND const_id=" + constId;
					upTs += " AND app_id=" + appId;
//					System.out.println("TS_TERMINAL :UPDATE ----" + upTs);
					stmt.executeUpdate(upTs);

					// XMPPログインユーザー照会
					selOpfCt = "SELECT count(*) FROM qgis.opf_user ";
					selOpfCt += " WHERE android_id='" + androidId + "'";
					selOpfCt += " AND const_id=" + constId;
					selOpfCt += " AND app_id=" + appId;
//					System.out.println("opf_user :SELECT ----" + selOpf);
		            ResultSet rs = stmt.executeQuery (selOpfCt);
		            int ct = 0;
		            
		            // テーブル照会結果を出力
		            rs.next();
					ct = rs.getInt("count");

					if (ct == 0)
					{
						//UPDATE
						upOpf = "UPDATE qgis.opf_user";
						upOpf += " SET login_user=null,";
						upOpf += " upddatetime=current_timestamp";
						upOpf += " WHERE login_user='"+ loginUser + "'";
//						System.out.println("opf_user :UPDATE ----");
						stmt.executeUpdate(upOpf);

						//================================================
						//  XMPPログインユーザーを登録
						//	備考		：	
						//	更新		：	2015/01/21 Kikuchi
						//================================================

						// INSERT
						insOpf = "INSERT INTO qgis.opf_user";
						insOpf += " (android_id, const_id, app_id, login_user, upddatetime, regdatetime)";
						insOpf += " VALUES";
						insOpf += " ('" + androidId + "'," + constId+ "," + appId + ",'" + loginUser + "',current_timestamp,current_timestamp)";
//						System.out.println("opf_user :INSERT ----");
						stmt.executeUpdate(insOpf);

					}
					else
					{
						
						// XMPPログインユーザー照会
						selOpf = "SELECT login_user FROM qgis.opf_user ";
						selOpf += " WHERE android_id='" + androidId + "'";
						selOpf += " AND const_id=" + constId;
						selOpf += " AND app_id=" + appId;
//						System.out.println("opf_user :SELECT ----login_user" );
			            ResultSet rs2 = stmt.executeQuery (selOpf);
			            rs2.next();
			            String login_user = rs2.getString("login_user");

						if(!loginUser.equals(login_user))
						{
							//UPDATE
							upOpf = "UPDATE qgis.opf_user";
							upOpf += " SET login_user=null,";
							upOpf += " upddatetime=current_timestamp";
							upOpf += " WHERE login_user='"+ loginUser + "'";
//							System.out.println("opf_user :UPDATE1 ----");
							stmt.executeUpdate(upOpf);

							upOpf2 = "UPDATE qgis.opf_user";
							upOpf2 += " SET login_user='" + loginUser + "',";
							upOpf2 += " upddatetime=current_timestamp";
							upOpf2 += " WHERE android_id='"+ androidId+ "'";
							upOpf2 += " AND const_id="+ constId;
							upOpf2 += " AND app_id="+ appId;
//							System.out.println("opf_user :UPDATE2 ----");
							stmt.executeUpdate(upOpf2);
						}
					}

      				//データベースを切断
					con.close();
				}
				catch (Exception e)
				{
					System.out.println("例外発生：" + e );
					log = "例外発生：" + e;
				}

                //受信結果をログに出力
                try
                {
                	String FileName = sdf2.format(now.getTime());
                	File file = new File("/www/ansys/xmpp/log/receive_" + FileName + ".log");
                	
                	if (!checkBeforeWritefile(file))
                	{
						try
						{
						    file.createNewFile();
						    file.setWritable(true, false);
						}
						catch(IOException e)
						{
						    System.out.println(e);
						}
                	}

					if (checkBeforeWritefile(file))
					{
						PrintWriter pw = new PrintWriter(new BufferedWriter(new FileWriter(file,true)));
						pw.println(sdf.format(now.getTime()) + "===============================" );
						pw.println("CONST_ID->" + constId);
						pw.println("APP_ID->->" + appId);
						pw.println("ADNROID_ID->" + androidId);
						pw.println("LOGIN_USER->" + loginUser);
						pw.println("POSITIONS->" + lat + "," + lon);
						pw.println("ACC->" + acc);
						pw.println("MESURETIME->"  + mesdate);
						pw.println("SPEED->"  + spd);
						pw.println("DIRECTION->"  + direction);
						pw.println(insHis);
						pw.println(upTs);
						pw.println(selOpfCt);
						if(selOpf != null) pw.println(selOpf);
						if(upOpf != null) pw.println(upOpf);
						if(insOpf != null) pw.println(insOpf);
						if(upOpf2 != null) pw.println(upOpf2);
						pw.println(log);
						pw.println("==================================================");
    		        	pw.close();
    		        }
  				}
				catch(IOException e)
  				{
  					System.out.println(e);
				}
  	        	System.out.println(sp[0] + "," + msgBody);
  	        }
		}
	};
        

	//■■ファイル有無をチェック
	private static boolean checkBeforeWritefile(File file)
	{
		if(file.exists())
		{
			if (file.isFile() && file.canWrite())
			{
				return true;
			}
		}
	    return false;
	}

}