com.save.serviceprovider.ClientServiceImpl.java Source code

Introduction

Here is the source code for com.save.serviceprovider.ClientServiceImpl.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.save.serviceprovider;

import com.save.connection.DBConnection;
import com.save.global.ErrorLoggedNotification;
import com.save.model.Client;
import com.save.model.Province;
import com.save.service.ClientService;
import com.save.utilities.CommonUtilities;
import com.vaadin.ui.Notification;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author jetdario
 */
public class ClientServiceImpl implements ClientService {

    @Override
    public List<Client> getAllClients() {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Client> clientsList = new ArrayList<>();

        try {
            pstmt = conn
                    .prepareStatement("SELECT * FROM clients " + "WHERE Status = 0 " + "ORDER BY ClientName ASC");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Client c = new Client();
                c.setClientId(CommonUtilities.convertStringToInt(rs.getString("ClientID")));
                c.setClientName(rs.getString("ClientName"));
                c.setCityId(CommonUtilities.convertStringToInt(rs.getString("CityID")));
                c.setStreet(rs.getString("Street"));
                c.setLandline(rs.getString("Landline"));
                c.setMobile(rs.getString("Mobile"));
                c.setRowStatus(rs.getString("Status"));
                c.setAsDistributor(CommonUtilities.convertStringToInt(rs.getString("AsDistributor")));
                clientsList.add(c);
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return clientsList;
    }

    @Override
    public String getClientNameById(int clientId) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String name = "";

        try {
            pstmt = conn
                    .prepareStatement("SELECT ClientName FROM clients " + "WHERE Status = 0 " + "AND ClientID = ?");

            pstmt.setInt(1, clientId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                name = rs.getString("ClientName");
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return name;
    }

    @Override
    public int getClientIdByName(String name) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int clientId = 0;

        try {
            pstmt = conn.prepareStatement("SELECT ClientID FROM clients " + "WHERE ClientName = ? ");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                clientId = CommonUtilities.convertStringToInt(rs.getString("ClientID"));
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return clientId;
    }

    @Override
    public String getEmployeeIdByClient(int clientId) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String employeeId = "";

        try {
            pstmt = conn.prepareStatement("SELECT EmployeeID FROM clients " + "WHERE ClientID = ? ");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                employeeId = rs.getString("EmployeeID");
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return employeeId;
    }

    @Override
    public List<String> getClientAsDistributor() {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<String> clientAsDistributorList = new ArrayList<String>();

        try {
            pstmt = conn.prepareStatement(
                    "SELECT ClientName FROM clients " + "WHERE AsDistributor != 0 AND Status = 0");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                clientAsDistributorList.add(rs.getString("ClientName"));
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return clientAsDistributorList;
    }

    @Override
    public boolean insertNewClient(Client c) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        if (c.getAsDistributor() == 1) {
            c.setClientType("distributor");
        }

        try {
            pstmt = conn.prepareStatement(
                    "INSERT INTO clients " + "SET ClientNAme = ?, " + "CityID = ?, " + "Street = ?, "
                            + "Landline = ?, " + "Mobile = ?, " + "AsDistributor = ?, " + "ClientType = ? ");
            pstmt.setString(1, c.getClientName());
            pstmt.setInt(2, c.getCityId());
            pstmt.setString(3, c.getStreet());
            pstmt.setString(4, c.getLandline());
            pstmt.setString(5, c.getMobile());
            pstmt.setInt(6, c.getAsDistributor());
            pstmt.setString(7, c.getClientType());
            pstmt.executeUpdate();

            result = true;
            Notification.show("Added New Client", Notification.Type.TRAY_NOTIFICATION);
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean editClient(Client c) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        if (c.getAsDistributor() == 1) {
            c.setClientType("distributor");
        }

        try {
            pstmt = conn.prepareStatement("UPDATE clients SET " + "ClientName = ?, " + "CityID = ?, "
                    + "Street = ?, " + "Landline = ?, " + "Mobile = ?, " + "AsDistributor = ?, " + "ClientType = ? "
                    + "WHERE ClientID = ? " + "AND Status = 0 ");
            pstmt.setString(1, c.getClientName());
            pstmt.setInt(2, c.getCityId());
            pstmt.setString(3, c.getStreet());
            pstmt.setString(4, c.getLandline());
            pstmt.setString(5, c.getMobile());
            pstmt.setInt(6, c.getAsDistributor());
            pstmt.setString(7, c.getClientType());
            pstmt.setInt(8, c.getClientId());
            pstmt.executeUpdate();

            result = true;
            Notification.show("Update Client Info", Notification.Type.TRAY_NOTIFICATION);
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public Client getClientDataById(int clientId) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Client c = new Client();
        Province p = new Province();

        try {
            pstmt = conn.prepareStatement("SELECT cl.ClientName AS ClientName, " + "pr.ProvinceID AS ProvinceID, "
                    + "cl.CityID AS CityID, " + "cl.Street AS Street, " + "cl.Landline AS Landline, "
                    + "cl.Mobile AS Mobile, " + "cl.AsDistributor AS AsDistributor, "
                    + "cl.ClientType AS ClientType " + "FROM clients cl " + "INNER JOIN city ci "
                    + "ON cl.CityID = ci.CityID " + "INNER JOIN province pr " + "ON ci.ProvinceID = pr.ProvinceID "
                    + "WHERE cl.ClientID = ? " + "AND cl.Status = 0 ");
            pstmt.setInt(1, clientId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                c.setClientName(rs.getString("ClientName"));
                p.setProvinceId(CommonUtilities.convertStringToInt(rs.getString("ProvinceID")));
                c.setProvince(p);
                c.setCityId(CommonUtilities.convertStringToInt(rs.getString("CityID")));
                c.setStreet(rs.getString("Street"));
                c.setLandline(rs.getString("Landline"));
                c.setMobile(rs.getString("Mobile"));
                c.setAsDistributor(CommonUtilities.convertStringToInt(rs.getString("AsDistributor")));
                c.setClientType(rs.getString("ClientType"));
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return c;
    }

    @Override
    public boolean removeAccount(int clientId, String remarks) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            pstmt = conn.prepareStatement("UPDATE clients " + "SET Status = ? " + "WHERE ClientID = ? ");
            pstmt.setInt(1, 1);
            pstmt.setInt(2, clientId);
            pstmt.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean isDistributor(int clientId) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean result = false;

        try {
            pstmt = conn.prepareStatement("SELECT AsDistributor FROM clients " + "WHERE ClientID = ? ");
            pstmt.setInt(1, clientId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                if (rs.getString("asDistributor").equals("1")) {
                    result = true;
                }
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

}