/*Main Activity*/
package user.example.com.roadworks;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.annotation.SuppressLint;
import android.graphics.Typeface;
import android.os.Bundle;
import android.os.StrictMode;
import android.support.v7.app.ActionBarActivity;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;
import android.widget.Toast;
import android.support.v7.app.AppCompatActivity;


public class MainActivity extends AppCompatActivity {

    TextView lblheader;
    Typeface font;
    Button btn;
    ListView lstroadWorks;
    SimpleAdapter ADAhere;

    /****************************Database Connection Variables*************************************/

    ConnectionClass connectionclass;
    String usernameS;
    String datets;
    String call, db, un, passwords;
    Connection connect;
    ResultSet rs;

    @SuppressLint("NewApi")
    private Connection CONN(String _user, String _pass, String _DB, String _server)
    {
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);
        Connection conn = null;
        String ConnURL = null;
        try{
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            ConnURL = "jdbc:jtds:sqlserver://" + _server + ";"
                    + "databaseName=" + _DB + ";user=" + _user + ";password=" + _pass + ";";
            conn = DriverManager.getConnection(ConnURL);
        }catch (SQLException se){
            Log.e("ERROR", se.getMessage());
        }catch (ClassNotFoundException e){
            Log.e("ERROR", e.getMessage());
        }catch (Exception e){
            Log.e("ERROR", e.getMessage());
        }
        return conn;
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        lblheader = (TextView) findViewById(R.id.lblheader);
        lstroadWorks = (ListView) findViewById(R.id.lstRoadWorks);
        btn = (Button) findViewById(R.id.btnview);

        /***********************DATABASE CONNECTION VARIABLE***************************************/
        connectionclass = new ConnectionClass();
        call = connectionclass.getIp();
        un = connectionclass.getUn();
        passwords = connectionclass.getPassword();
        db = connectionclass.getDb();
        connect = CONN(un, passwords, db, call);
        btn.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v){
                //TODO Auto-generated method stub
                String querycmd = "select * from trafficNews2";
                try{
                    Statement statement = connect.createStatement();
                    rs = statement.executeQuery(querycmd);
                    List<Map<String, String>> data = null;
                    data = new ArrayList<Map<String, String>>();

                    while (rs.next())
                    {
                        Map<String, String> datanum = new HashMap<String, String>();
                        datanum.put("A", rs.getString("streetName"));

                        data.add(datanum);
                    }
                    String[] fromwhere = {"A"};
                    int[] viewswhere = {R.id.lblcountryname};
                    ADAhere = new SimpleAdapter(MainActivity.this, data, R.layout.listtemplate, fromwhere, viewswhere);
                    lstroadWorks.setAdapter(ADAhere);
                }catch (SQLException e){
                    Toast.makeText(MainActivity.this, e.getMessage().toString(), Toast.LENGTH_LONG).show();
                }
            }
        });

        lstroadWorks.setOnItemClickListener(new OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                //TODO Auto-generated method stub
                HashMap<String, Object> obj = (HashMap<String, Object>) ADAhere.getItem(position);
                String VechicleId = (String) obj.get("A");
                Toast.makeText(MainActivity.this, VechicleId, Toast.LENGTH_LONG).show();
            }
        });
    }
}

/*Connection Class*/
package user.example.com.roadworks;

/**
 * Created by User on 18/3/2017.
 */

public class ConnectionClass {
    String classs;
    String db;
    String un;
    String password;
    String ip;

    public ConnectionClass()
    {
        classs = "net.sourceforge.jtds.jdbc.Driver";
        db = "softwareEngineering";
        un = "giannis";
        password = "22554477";
        ip = "softwareengineering.database.windows.net:1433";
    }

    public ConnectionClass(String Ip, String Classs, String Db, String Un, String Password)
    {
        ip = Ip;
        classs = Classs;
        db = Db;
        un = Un;
        password = Password;
    }

    public String getIp()
    {
        return ip;
    }

    public String getClasss(){
        return classs;
    }

    public String getDb(){
        return db;
    }

    public String getUn(){
        return un;
    }
    public String getPassword(){
        return password;
    }

    public void setip(String Ip){
        ip = Ip;
    }

    public void setdb(String Db)
    {
        db = Db;
    }

    public void setclasss(String Classs){
        classs = Classs;
    }

    public void setun(String Un){
        un = Un;
    }

    public void setpassword(String Password){
        password = Password;
    }
}
/*activity main .xml*/
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#c0392b"
    android:orientation="vertical"
    android:weightSum="10" >

    <TextView
        android:id="@+id/lblheader"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:padding="10dp"
        android:text="ROADWORKS : "
        android:textColor="#fff"
        android:textSize="15sp" />

    <ListView
        android:id="@+id/lstRoadWorks"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="8"
        android:divider="#252525"
        android:dividerHeight="1dp" >
    </ListView>

    <Button
        android:id="@+id/btnview"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:text="VIEW"
        android:background="#fff"
        android:layout_marginLeft="10dp"
        android:layout_marginRight="10dp"
        android:textSize="20sp"
        android:textColor="#c0392b" >
    </Button>

</LinearLayout>
/*listtemplate.xml*/
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#c0392b"
    android:orientation="vertical" >

    <TextView
        android:id="@+id/lblcountryname"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="#fff"
        android:padding="10dp"
        android:text="RoadWorks"
        android:textColor="#252525"
        android:textSize="20sp" />

</LinearLayout>
/*manifest*/
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="user.example.com.roadworks">

    <uses-permission android:name="android.permission.INTERNET"/>
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE"/>

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>