package at.irian.jsfatwork.domain;

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

import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;

/*
 * customer profile
 */
@ManagedBean
@SessionScoped
public class Customer {
    private Connection con;
    private Statement ps;
    private ResultSet rs;
    private String SQL_Str;
    private String username;
    private String password;
    private String dbusername;
    private String dbpassword;
    private String datum;
    private List<UserInfo> userInfoAll = new ArrayList<UserInfo>();
    private UserInfo userInfo;

    public String getDatum() {
        return datum;
    }

    public void setDatum(String datum) {
        this.datum = datum;
    }

    public String getDbpassword() {
        return dbpassword;
    }

    public String getDbusername() {
        return dbusername;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String edit(UserInfo userInfo) {
        this.userInfo = userInfo;
        return "/editAddress.xhtml";
    }

    public UserInfo getuserInfo() {
        return userInfo;
    }

    public void setuserInfo(UserInfo userInfo) {
        this.userInfo = userInfo;
    }

    public String ok() {
        getuserInfoAll();
        return "/showCustomer";
    }

    public String save() {
        String updateAC;
        String updateTK;
        String updatePosition;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(
                    "jdbc:oracle:thin:@server:1521:EW62",
                    "user", "pw");
            ps = con.createStatement();
            updateAC = "Update validator_report set ac = '"
                    + this.userInfo.getAc() + "' where sammel_nr='"
                    + this.userInfo.getSammel_Nr() + "' and lfd_nr = '"
                    + this.userInfo.getLfd_Nr() + "' and rez_nr = '"
                    + this.userInfo.getRez_Nr() + "' and pos_nr = '"
                    + this.userInfo.getPos_Nr() + "'";
            updateTK = "Update validator_report set TK = '"
                    + this.userInfo.getTk() + "' where sammel_nr='"
                    + this.userInfo.getSammel_Nr() + "' and lfd_nr = '"
                    + this.userInfo.getLfd_Nr() + "' and rez_nr = '"
                    + this.userInfo.getRez_Nr() + "' and pos_nr = '"
                    + this.userInfo.getPos_Nr() + "'";
            updatePosition = "Update validator_report set POSITION_NR = '"
                    + this.userInfo.getPosition_Nr() + "' where sammel_nr='"
                    + this.userInfo.getSammel_Nr() + "' and lfd_nr = '"
                    + this.userInfo.getLfd_Nr() + "' and rez_nr = '"
                    + this.userInfo.getRez_Nr() + "' and pos_nr = '"
                    + this.userInfo.getPos_Nr() + "'";
            rs = ps.executeQuery(updateAC);
            rs = ps.executeQuery(updateTK);
            rs = ps.executeQuery(updatePosition);
        } catch (Exception e) {
            System.out.println("Error Data : " + e);

        }
        return "/showCustomer.xhtml";
    }

    public String checkValidUser() {
        dbData(username);

        if (username.equalsIgnoreCase(dbusername)) {

            if (password.equals(dbpassword))
                return "/showCustomer.xhtml";
            else {
                return "/loginPage.xhtml";
            }
        } else {
            return "/loginPage.xhtml";
        }
    }

    public String deleteUserInfo(UserInfo userInfo) {
        this.getuserInfoAll().remove(userInfo);
        return null;
    }

    public List<UserInfo> getuserInfoAll() {
        if (datum != null) {
            userInfoAll.clear();

            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                con = DriverManager.getConnection(
                        "jdbc:oracle:thin:@server:1521:EW62",
                        "user", "pw");
                ps = con.createStatement();
                rs = ps.executeQuery("select SAMMEL_NR, LFD_NR, REZ_NR, POS_NR, LEIST_GRUPPE, AC, TK, POSITION_NR "
                        + "from validator_report WHERE sammel_nr LIKE '56%'"
                        + "and daten_korrigiert <> 'N'"
                        + "and buchung_datum = '"
                        + datum
                        + "'"
                        + "AND validator_name in ( 'POSITIONSCHLUESSEL', 'ACZULEISTGRUPPE', 'Fehlender_ac')");
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                    userInfoAll.add(
                    // i,
                            new UserInfo(rs.getString(1), rs.getString(2), rs
                                    .getString(3), rs.getString(4), rs
                                    .getString(5), rs.getString(6), rs
                                    .getString(7), rs.getString(8)));

                }
            } catch (Exception e) {
                System.out.println("Error Data : " + e);
            }
        }
        return userInfoAll;
    }

    public void dbData(String UName) {
        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(
                    "jdbc:oracle:thin:@server:1521:EW62",
                    "user", "pw");
            ps = con.createStatement();
            SQL_Str = "Select * from users where user_name like ('" + UName
                    + "')";
            rs = ps.executeQuery(SQL_Str);
            rs.next();
            dbusername = rs.getString(1).toString();
            dbpassword = rs.getString(2).toString();

        } catch (Exception e) {
            System.out.println("Error Data : " + e);

        }
    }

}