/Common_Classes/lwt/src/za/co/ucs/accsys/webmodule/WebProcess_FamilyRequisition.java

package za.co.ucs.accsys.webmodule;

import za.co.ucs.accsys.peopleware.*;
import za.co.ucs.lwt.db.*;
import java.sql.*;

/**
 * <p>
 * A WebProcess_FamilyRequisition is an extension of WebProcess that specializes
 * in Family Detail Requests
 * </p>
 */
public final class WebProcess_FamilyRequisition extends WebProcess {

    /**
     * To change Family information, FamilyRequisition requires
     *
     * @param processDefinition - details of reporting structure, etc
     * @param owner - who created/is responsible for this process?
     * @param employee - which employee is requesting leave?
     * @param familyID - which family member? if Null, a new family member will
     * be created
     * @param firstName - String
     * @param secondName - String
     * @param surname
     * @param gender - String ('M' / 'F')
     * @param relation - String
     * @param birthDate - String ('yyyy/mm/dd')
     * @param medAidDependent - String ('Y','N');
     * @param commitType - ctInsert, ctUpdate, ctDelete
     * @param telHome - String
     * @param telWork - String
     * @param telCell - String
     * @param EMail - String
     */
    public WebProcess_FamilyRequisition(WebProcessDefinition processDefinition, Employee owner,
            Employee employee, Integer familyID, String firstName, String secondName, String surname,
            String gender, String relation, String birthDate, String telHome, String telWork,
            String telCell, String EMail, String medAidDependent, int commitType) {
        super(processDefinition, owner, employee);

        this.familyID = familyID;
        this.firstName = firstName;
        this.secondName = secondName;
        this.surname = surname;
        setGender(gender);
        this.relation = relation;
        this.birthDate = birthDate;
        this.medAidDependent = medAidDependent;
        this.telHome = telHome;
        this.telWork = telWork;
        this.telCell = telCell;
        this.EMail = EMail;
        this.commitType = commitType;

        // Is this a valid leave request?
        ValidationResult valid = validate();
        if (!valid.isValid()) {
            this.cancelProcess(valid.getInvalidReason());
        } else {
            this.escalateProcess(et_INITIAL, "");
        }
    }

    /**
     * This method should be implemented by each and every descendent of
     * WebProcess. It returns an instance of ValidationResult which contains
     * both the isValid() property as well as a reason in case of a non-valid
     * result.
     */
    @Override
    public ValidationResult validate() {
        if (isActive()) {

            Connection con = null;
            try {
                con = DatabaseObject.getNewConnectionFromPool();

                // If this is an update or delete, make sure the familyID is not null
                if ((commitType == ctUpdate) || (commitType == ctDelete)) {
                    if (familyID == null) {
                        DatabaseObject.releaseConnection(con);
                        return (new ValidationResult(false, "Cannot update/delete family member. No member_id provided."));
                    }
                }
                // If this is an update or delete, make sure there is a familyID and that it exists
                // in the database
                if (((commitType == ctUpdate) || (commitType == ctDelete)) && (familyID != null)) {
                    ResultSet rs1 = DatabaseObject.openSQL(getFamilyMemberExistsStatement(), con);
                    if (rs1.next()) {
                        if (rs1.getInt(1) <= 0) {
                            rs1.close();
                            DatabaseObject.releaseConnection(con);
                            return (new ValidationResult(false, "Family Member (member_id=" + familyID + ") does not exist in database."));
                        }
                    }
                    rs1.close();
                }
                // If this is an insert, make sure the familyID has not yet been used
                // in the database
                if ((commitType == ctInsert) && (familyID != null)) {
                    ResultSet rs1 = DatabaseObject.openSQL(getFamilyMemberExistsStatement(), con);
                    if (rs1.next()) {
                        if (rs1.getInt(1) > 0) {
                            rs1.close();
                            DatabaseObject.releaseConnection(con);
                            return (new ValidationResult(false, "Family Member (member_id=" + familyID + ") already exist in database."));
                        }
                    }
                    rs1.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DatabaseObject.releaseConnection(con);
            }
        }
        return (new ValidationResult(true, ""));
    }

    @Override
    public String toString() {
        StringBuilder result = new StringBuilder();
        if (commitType == ctDelete) {
            result.append("Delete Family Member: ").append(getSurname()).append(", ").append(getFirstName());
        }
        if (commitType == ctUpdate) {
            // Explanation of what changes were requested
            String detail = getChanges(new Family(getEmployee(), familyID), false);
            result.append("Change Detail Of Family Member  ").append(getSurname()).append(", ").append(getFirstName()).append(detail);
        }
        if (commitType == ctInsert) {
            result.append("Add Family Member: ").append(getSurname()).append(", ").append(getFirstName());
        }

        return result.toString();
    }

    @Override
    public String toHTMLString() {
        StringBuilder result = new StringBuilder();
        result.append("Process Number: [").append(this.hashCode()).append("]<br>");

        if (commitType == ctDelete) {
            result.append("Delete Family Member: ").append(getSurname()).append(", ").append(getFirstName());
        }
        if (commitType == ctUpdate) {
            // Explanation of what changes were requested
            String detail = getChanges(new Family(getEmployee(), familyID), true);
            result.append("Change Detail Of Family Member  ").append(getSurname()).append(", ").append(getFirstName()).append("<br>").append(detail);
        }
        if (commitType == ctInsert) {
            // Explanation of what changes were requested
            String detail = getChanges(new Family(getEmployee(), familyID), true);
            result.append("Add Family Member: ").append(getSurname()).append(", ").append(getFirstName()).append("<br>").append(detail);
        }

        return result.toString();
    }

    /**
     * Returns a String representation of the differences between the instance
     * of Family and the current properties in this WebProcess
     */
    private String getChanges(Family family, boolean formatInHTML) {
        StringBuffer result = new StringBuffer();

        if (formatInHTML) {
            try {
                if (isDifferent(getSurname(), family.getSurname())) {
                    result.append("<tr><td>Surname:</td><td>From '").append(family.getSurname()).append("'</td><td>To '").append(getSurname()).append("'</td></tr>");
                }
                if (isDifferent(getFirstName(), family.getFirstName())) {
                    result.append("<tr><td>First Name:</td><td>From '").append(family.getFirstName()).append("'</td><td>To '").append(getFirstName()).append("'</td></tr>");
                }
                if (isDifferent(getSecondName(), family.getSecondName())) {
                    result.append("<tr><td>Second Name:</td><td>From '").append(family.getSecondName()).append("'</td><td>To '").append(getSecondName()).append("'</td></tr>");
                }
                if (isDifferent(getBirthDate(), family.getBirthDate())) {
                    result.append("<tr><td>Birth Date:</td><td>From '").append(family.getBirthDate()).append("'</td><td>To '").append(getBirthDate()).append("'</td></tr>");
                }
                if (isDifferent(getGender(), family.getGender())) {
                    String displayGender = "Unselected";
                    String familyDisplayGender = "Unselected";
                    if (!getGender().equals("")) {
                        displayGender = getGender(); 
                    }
                    if (!family.getGender().equals("")) {
                        familyDisplayGender = family.getGender(); 
                    }
                    result.append("<tr><td>Gender:</td><td>From '").append(familyDisplayGender).append("'</td><td>To '").append(displayGender).append("'</td></tr>");
                }
                if (isDifferent(getMedAidDependent(), family.getMedDependent())) {
                    result.append("<tr><td>Med. Aid Dependent:</td><td>From '").append(family.getMedDependent()).append("'</td><td>To '").append(getMedAidDependent()).append("'</td></tr>");
                }
                if (isDifferent(getRelation(), family.getRelation())) {
                    result.append("<tr><td>Relation:</td><td>From '").append(family.getRelation()).append("'</td><td>To '").append(getRelation()).append("'</td></tr>");
                }
                if (isDifferent(getTelHome(), family.getTelHome())) {
                    result.append("<tr><td>Tel Home:</td><td>From '").append(family.getTelHome()).append("'</td><td>To '").append(getTelHome()).append("'</td></tr>");
                }
                if (isDifferent(getTelWork(), family.getTelWork())) {
                    result.append("<tr><td>Tel Work:</td><td>From '").append(family.getTelWork()).append("'</td><td>To '").append(getTelWork()).append("'</td></tr>");
                }
                if (isDifferent(getTelCell(), family.getTelCell())) {
                    result.append("<tr><td>Cell:</td><td>From '").append(family.getTelCell()).append("'</td><td>To '").append(getTelCell()).append("'</td></tr>");
                }
                if (isDifferent(getEMail(), family.getEMail())) {
                    result.append("<tr><td>E-mail:</td><td>From '").append(family.getEMail()).append("'</td><td>To '").append(getEMail()).append("'</td></tr>");
                }
            } catch (java.lang.NullPointerException e) {
                result = new StringBuffer();
                result.append("<tr><td>The system is unable to determine the detail of this process.</td></tr>");
            }
        } else {
            try {
                String lineBreak = "\n";
                if (isDifferent(getSurname(), family.getSurname())) {
                    result.append(lineBreak).append("Surname: From '").append(family.getSurname()).append("' To '").append(getSurname()).append("'");
                }
                if (isDifferent(getFirstName(), family.getFirstName())) {
                    result.append(lineBreak).append("First Name: From '").append(family.getFirstName()).append("' To '").append(getFirstName()).append("'");
                }
                if (isDifferent(getSecondName(), family.getSecondName())) {
                    result.append(lineBreak).append("Second Name: From '").append(family.getSecondName()).append("' To '").append(getSecondName()).append("'");
                }
                if (isDifferent(getBirthDate(), family.getBirthDate())) {
                    result.append(lineBreak).append("Birth Date: From '").append(family.getBirthDate()).append("' To '").append(getBirthDate()).append("'");
                }
                if (isDifferent(getGender(), family.getGender())) {
                    String displayGender = "Unselected";
                    String familyDisplayGender = "Unselected";
                    if (!getGender().equals("")) {
                        displayGender = getGender(); 
                    }
                    if (!family.getGender().equals("")) {
                        familyDisplayGender = family.getGender(); 
                    }
                    result.append(lineBreak).append("Gender: From '").append(familyDisplayGender).append("' To '").append(displayGender).append("'");
                }
                if (isDifferent(getMedAidDependent(), family.getMedDependent())) {
                    result.append(lineBreak).append("Med. Aid Dependent: From '").append(family.getMedDependent()).append("' To '").append(getMedAidDependent()).append("'");
                }
                if (isDifferent(getRelation(), family.getRelation())) {
                    result.append(lineBreak).append("Relation: From '").append(family.getRelation()).append("' To '").append(getRelation()).append("'");
                }
                if (isDifferent(getTelHome(), family.getTelHome())) {
                    result.append(lineBreak).append("Tel Home: From '").append(family.getTelHome()).append("' To '").append(getTelHome()).append("'");
                }
                if (isDifferent(getTelWork(), family.getTelWork())) {
                    result.append(lineBreak).append("Tel Work: From '").append(family.getTelWork()).append("' To '").append(getTelWork()).append("'");
                }
                if (isDifferent(getTelCell(), family.getTelCell())) {
                    result.append(lineBreak).append("Cell: From '").append(family.getTelCell()).append("' To '").append(getTelCell()).append("'");
                }
                if (isDifferent(getEMail(), family.getEMail())) {
                    result.append(lineBreak).append("E-mail: From '").append(family.getEMail()).append("' To '").append(getEMail()).append("'");
                }
            } catch (java.lang.NullPointerException e) {
                result = new StringBuffer();
                result.append("The system is unable to determine the detail of this process.");
            }
        }

        if (formatInHTML) {
            result.insert(0, "<table class=process_detail><font size='-3'>");
            result.append("</font></table>");
        }
        return result.toString();
    }

    private boolean isMedDependent() {
        return (getMedAidDependent().compareTo("Y") == 0);
    }

    /**
     * This statement gets executed when the process is to be written to the
     * database.
     */
    @Override
    String getCommitStatement() {
        String sqlString = "";
        if (commitType == ctInsert) {
            sqlString = getCommitAddCONSTANTStatement();
        }
        if (commitType == ctDelete) {
            sqlString = getCommitDeleteCONSTANTStatement();
        }
        if (commitType == ctUpdate) {
            sqlString = getCommitEditCONSTANTStatement();
        }

        return (prepareStatement(sqlString));
    }

    /**
     * Performs a ReplaceAll call without generating exceptions on NULL values
     *
     * @param regex - The regex search string
     * @param replacement - replacement string
     * @param string
     * @return
     */
    private String replaceAll(String regex, String replacement, String string) {
        if (string == null) {
            return ("");
        } else {
            return (string.replaceAll(regex, replacement));
        }

    }

    /**
     * Internal method to perform parameter replacements
     */
    private String prepareStatement(String aStatementWithParameters) {
        String sqlString = aStatementWithParameters;

        sqlString = sqlString.replaceAll(":companyID", getEmployee().getCompany().getCompanyID().toString());
        sqlString = sqlString.replaceAll(":employeeID", getEmployee().getEmployeeID().toString());
        sqlString = sqlString.replaceAll(":familyID", familyID.toString());
        sqlString = sqlString.replaceAll(":firstname", replaceAll("'", "`", firstName));
        sqlString = sqlString.replaceAll(":secondname", replaceAll("'", "`", secondName));
        sqlString = sqlString.replaceAll(":surname", replaceAll("'", "`", surname));
        if (birthDate.trim().length() == 0) {
            sqlString = sqlString.replaceAll(":birthdate", "null");
        } else {
            sqlString = sqlString.replaceAll(":birthdate", "'" + birthDate + "'");
        }
        sqlString = sqlString.replaceAll(":relation", replaceAll("'", "`", relation));
        sqlString = sqlString.replaceAll(":gender", replaceAll("'", "`", gender));
        sqlString = sqlString.replaceAll(":med_dependent_yn", medAidDependent);
        sqlString = sqlString.replaceAll(":telHome", replaceAll("'", "`", telHome));
        sqlString = sqlString.replaceAll(":telWork", replaceAll("'", "`", telWork));
        sqlString = sqlString.replaceAll(":telCell", replaceAll("'", "`", telCell));
        sqlString = sqlString.replaceAll(":EMail", replaceAll("'", "`", EMail));
        return sqlString;
    }

    /**
     * SQL Statement to be used to check weather the process is still valid.
     * These statements contain replacement characters that will be 'replaced'
     * before being fed to the DatabaseComponent.
     *
     * @COMPANY_ID, @EMPLOYEE_ID, etc.
     */
    private String getFamilyMemberExistsStatement() {
        return prepareStatement(getFamilyMemberExistsCONSTANTStatement());
    }

    /**
     * Getter for property birthDate.
     *
     * @return Value of property birthDate.
     *
     */
    public java.lang.String getBirthDate() {
        return birthDate;
    }

    /**
     * Setter for property birthDate.
     *
     * @param birthDate New value of property birthDate.
     *
     */
    public void setBirthDate(java.lang.String birthDate) {
        this.birthDate = birthDate;
    }

    /**
     * Getter for property familyID.
     *
     * @return Value of property familyID.
     *
     */
    public java.lang.Integer getFamilyID() {
        return familyID;
    }

    /**
     * Setter for property familyID.
     *
     * @param familyID New value of property familyID.
     *
     */
    public void setFamilyID(java.lang.Integer familyID) {
        this.familyID = familyID;
    }

    /**
     * Getter for property firstName.
     *
     * @return Value of property firstName.
     *
     */
    public java.lang.String getFirstName() {
        return firstName;
    }

    /**
     * Setter for property firstName.
     *
     * @param firstName New value of property firstName.
     *
     */
    public void setFirstName(java.lang.String firstName) {
        this.firstName = firstName;
    }

    /**
     * Getter for property gender.
     *
     * @return Value of property gender.
     *
     */
    public java.lang.String getGender() {
        return gender;
    }

    /**
     * Setter for property gender.
     *
     * @param gender New value of property gender.
     *
     */
    public void setGender(java.lang.String gender) {
        if (gender.contains("Unselected")) {
            gender = "";
        }
        this.gender = gender;
    }

    /**
     * Getter for property medAidDependent.
     *
     * @return Value of property medAidDependent.
     *
     */
    public java.lang.String getMedAidDependent() {
        return medAidDependent;
    }

    /**
     * Setter for property medAidDependent.
     *
     * @param medAidDependent New value of property medAidDependent.
     *
     */
    public void setMedAidDependent(java.lang.String medAidDependent) {
        this.medAidDependent = medAidDependent;
    }

    /**
     * Getter for property relation.
     *
     * @return Value of property relation.
     *
     */
    public java.lang.String getRelation() {
        return relation;
    }

    /**
     * Setter for property relation.
     *
     * @param relation New value of property relation.
     *
     */
    public void setRelation(java.lang.String relation) {
        this.relation = relation;
    }

    /**
     * Getter for property secondName.
     *
     * @return Value of property secondName.
     *
     */
    public java.lang.String getSecondName() {
        return secondName;
    }

    /**
     * Setter for property secondName.
     *
     * @param secondName New value of property secondName.
     *
     */
    public void setSecondName(java.lang.String secondName) {
        this.secondName = secondName;
    }

    /**
     * Getter for property surname.
     *
     * @return Value of property surname.
     *
     */
    public java.lang.String getSurname() {
        return surname;
    }

    /**
     * Setter for property surname.
     *
     * @param surname New value of property surname.
     *
     */
    public void setSurname(java.lang.String surname) {
        this.surname = surname;
    }

    public String getTelHome() {
        return telHome;
    }

    public void setTelHome(String telHome) {
        this.telHome = telHome;
    }

    public String getTelWork() {
        return telWork;
    }

    public void setTelWork(String telWork) {
        this.telWork = telWork;
    }

    public String getTelCell() {
        return telCell;
    }

    public void setTelCell(String telCell) {
        this.telCell = telCell;
    }

    public String getEMail() {
        return EMail;
    }

    public void setEMail(String EMail) {
        this.EMail = EMail;
    }

    // Fingerprint of previously compatible version
    /* In order to extract this UID, run the following command in the
     * folder where the current jwt.jar file resides BEFORE changing
     * the contents of the class.
     C:\Apache5\shared\lib>c:\j2sdk1.4.2_03\bin\serialver -classpath lwt.jar za.co.ucs.accsys.webmodule.WebProcess_FamilyRequisition
     za.co.ucs.accsys.webmodule.WebProcess_EmployeeContact:    static final long serialVersionUID = 9094426540099516746L;

     */
    static final long serialVersionUID = -4053465466338838752L;

    private Integer familyID;
    private String firstName = "";
    private String secondName = "";
    private String surname = "";
    private String gender = "";
    private String relation = "";
    private String birthDate = "";
    private String medAidDependent = "N";
    private String telHome = "";
    private String telWork = "";
    private String telCell = "";
    private String EMail = "";

    public static int ctInsert = 0;
    public static int ctUpdate = 1;
    public static int ctDelete = 2;

    private int commitType = ctInsert;
    private EmployeeSelection initialEmployeeSelection;

    private String getFamilyMemberExistsCONSTANTStatement() {
        return ("select count(*) from e_family  with (NOLOCK) where company_id=:companyID "
                + "and employee_id=:employeeID and family_id=:familyID");
    }

    private String getNextFamilyIDCONSTANTStatement() {
        return ("if (select count(*) from e_family  with (NOLOCK) where company_id=:companyID "
                + "and employee_id=:employeeID )>0 then "
                + "select max(family_id)+1 from e_family where company_id=:companyID "
                + "and employee_id=:employeeID else select 1; end if; ");
    }

    private String getCommitAddCONSTANTStatement() {
        return ("if not exists(select * from e_family where company_id=:companyID "
                + "and employee_id=:employeeID and family_id=:familyID) then"
                + " insert into e_family (company_id, employee_id, family_id, firstname, secondname,"
                + " surname, gender, relation, birthdate, med_dependent_yn, Tel_Home, Tel_Work, Tel_Cell, E_Mail)"
                + " values (:companyID,:employeeID,:familyID,':firstname',':secondname',"
                + " ':surname',':gender',':relation',:birthdate,':med_dependent_yn', ':telHome', ':telWork', ':telCell', ':EMail');"
                + " end if;");
    }

    private String getCommitEditCONSTANTStatement() {
        return (" update e_family set firstname=':firstname', secondname=':secondname', "
                + " surname=':surname', gender=':gender',relation=':relation', "
                + " birthdate=:birthdate, med_dependent_yn=':med_dependent_yn', Tel_Home=':telHome', Tel_Work=':telWork', Tel_Cell=':telCell', E_Mail=':EMail' "
                + " where company_id=:companyID and employee_id=:employeeID and family_id=:familyID");
    }

    private String getCommitDeleteCONSTANTStatement() {
        return (" delete from  e_family where company_id=:companyID and employee_id=:employeeID "
                + " and family_id=:familyID");
    }

    @Override
    public String getProcessName() {
        return "Family Detail Request";
    }

} // end LeaveRequisitionProcess