Notes

package bbsr.highradius.training.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.cxf.common.util.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.orm.hibernate5.HibernateTemplate;
import com.highradius.common.util.HRCLog;
import com.highradius.common.util.HRCLogFactory;
import bbsr.highradius.training.dao.InvoiceDetailsDao;
import bbsr.highradius.training.model.employee.EmployeeDetails;
import bbsr.highradius.training.model.invoice.trn_customer;
import bbsr.highradius.training.model.invoice.trn_invoice;
import bbsr.highradius.training.model.invoice.trn_invoice_type;
import bbsr.highradius.training.model.invoice.trn_invoicedup;
import bbsr.highradius.training.model.invoice.trn_posting_key;
import net.sf.ehcache.hibernate.HibernateUtil;
import org.hibernate.Transaction;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.Restrictions;
import org.hibernate.HibernateException;
import java.util.Iterator;
import java.sql.Types;
import java.util.ArrayList;

import org.hibernate.criterion.Property;

public class InvoiceDetailsDaoImpl implements InvoiceDetailsDao{

private static final HRCLog LOGGER = HRCLogFactory.getLog(InvoiceDetailsDaoImpl.class);
private SessionFactory sessionFactory;
private HibernateTemplate hibernateTemplate;

public SessionFactory getSessionFactory() {
return sessionFactory;
}

public void setSessionFactory(SessionFactory sessionFactory) {
this.sessionFactory = sessionFactory;
this.hibernateTemplate = new HibernateTemplate(sessionFactory);
}


@Override
public Map<String, Object> getInvoiceDetails(String advanceSearchWhereQuery,Integer startIndex, Integer rowsToFetch) {
LOGGER.debug("Inside getInvoiceDetails() method called from InvoiceDetailsDaoImpl");
StringBuffer query = new StringBuffer("FROM trn_invoice WHERE ");

if (!StringUtils.isEmpty(advanceSearchWhereQuery)) {
query.append(advanceSearchWhereQuery);
} else {
query.append("is_open = 1 AND (approval_status='approved' OR approval_status='pending')");
}

LOGGER.debug("Query : " + query.toString());
try {
return getRowsAndCountForQuery(query.toString(), startIndex, rowsToFetch, true);
} catch (Exception e) {
LOGGER.error("Error in getInvoiceDetails()", e);
}
return null;
}

@Override
public Map<String, Object> getClosedInvoiceDetails(String advanceSearchWhereQuery,Integer startIndex, Integer rowsToFetch) {
LOGGER.debug("Inside getInvoiceDetails() method called from InvoiceDetailsDaoImpl");
StringBuffer query = new StringBuffer("FROM trn_invoice WHERE ");

if (!StringUtils.isEmpty(advanceSearchWhereQuery)) {
query.append(advanceSearchWhereQuery);
} else {
query.append("is_open = 0 AND (approval_status='approved' OR approval_status='pending')");
}

LOGGER.debug("Query : " + query.toString());
try {
return getRowsAndCountForQuery(query.toString(), startIndex, rowsToFetch, true);
} catch (Exception e) {
LOGGER.error("Error in getInvoiceDetails()", e);
}
return null;
}

@Override
public Map<String, Object> getInvoicePendingDetails(String advanceSearchWhereQuery,Integer startIndex, Integer rowsToFetch) {
LOGGER.debug("Inside getInvoiceDetails() method called from InvoiceDetailsDaoImpl");
StringBuffer query = new StringBuffer("FROM trn_invoice WHERE ");

if (!StringUtils.isEmpty(advanceSearchWhereQuery)) {
query.append(advanceSearchWhereQuery);
} else {
query.append("approval_status='pending'");
}

LOGGER.debug("Query : " + query.toString());
try {
return getRowsAndCountForQuery(query.toString(), startIndex, rowsToFetch, true);
} catch (Exception e) {
LOGGER.error("Error in getInvoiceDetails()", e);
}
return null;
}

@Override
public boolean addNewInvoiceDetails(trn_invoice invoiceObj,String customer_name ,String description, String invoice_type) {
LOGGER.debug("Inside addNewEmployeeDetails() method called from EmployeeDetailsDaoImpl");
String hql="FROM trn_customer WHERE customerName LIKE '"+customer_name+"'";
String hql2="FROM trn_posting_key WHERE description LIKE '"+description+"'";
String hql3="FROM trn_invoice_type WHERE invoiceType LIKE '"+invoice_type+"'";
ArrayList<trn_customer> customer=new ArrayList<trn_customer>();
trn_customer cust=new trn_customer();
ArrayList<trn_posting_key> postkey=new ArrayList<trn_posting_key>();
trn_posting_key post=new trn_posting_key();
ArrayList<trn_invoice_type> invtype=new ArrayList<trn_invoice_type>();
trn_invoice_type inv=new trn_invoice_type();


try {
if(invoiceObj.getPk_invoice_id()!=null) {
duplicate(invoiceObj.getPk_invoice_id());
}
Session session=getSession();
Query q1=session.createQuery(hql.toString());
customer=(ArrayList<trn_customer>) q1.list();
Iterator it1=customer.iterator();
cust=(trn_customer) it1.next();
System.out.println("Customer ID:"+cust.getPk_customer_map_id());
if(cust.getPk_customer_map_id()!=null && String.valueOf(cust.getPk_customer_map_id()).length()!=0) {
invoiceObj.setCustid(cust.getPk_customer_map_id());
}
else {
invoiceObj.setCustid(Types.NULL);
}

Query q2=session.createQuery(hql2.toString());
postkey=(ArrayList<trn_posting_key>) q2.list();
Iterator it2=postkey.iterator();
post=(trn_posting_key) it2.next();
System.out.println("Posting ID:"+post.getPk_posting_key_id());
if(post.getPk_posting_key_id()!=null && String.valueOf(post.getPk_posting_key_id()).length()!=0) {
invoiceObj.setPostid(post.getPk_posting_key_id());
}
else {
invoiceObj.setCustid(Types.NULL);
}

Query q3=session.createQuery(hql3.toString());
invtype=(ArrayList<trn_invoice_type>) q3.list();
Iterator it3=invtype.iterator();
inv=(trn_invoice_type) it3.next();
System.out.println("Invoice ID:"+inv.getPk_invoice_type_map_id());
if(inv.getPk_invoice_type_map_id()!=null && String.valueOf(inv.getPk_invoice_type_map_id()).length()!=0)
{
invoiceObj.setInvid(inv.getPk_invoice_type_map_id());
}
else {
invoiceObj.setCustid(Types.NULL);
}

Transaction transaction=null;
transaction=session.beginTransaction();

session.saveOrUpdate(invoiceObj);
transaction.commit();
session.close();
return true;
} catch (Exception e) {
LOGGER.error("Error while saving entry : ",e);
}
return false;
}

@Override
public boolean deleteInvoiceDetails(trn_invoice invoiceObj) {
LOGGER.debug("Inside deleteInvoiceDetails() method called from EmployeeDetailsDaoImpl");
Session session=getSession();
Transaction tx=null;


try {
tx=session.beginTransaction();
Query query=session.createQuery("update trn_invoice set is_open=0, approval_status='pending' where pk_invoice_id=?");
query.setParameter(0,invoiceObj.getPk_invoice_id());
query.executeUpdate();

tx.commit();
session.close();
return true;
} catch (Exception e) {
LOGGER.error("Error while saving entry : ",e);
}
return false;
}

@Override
public boolean approveInvoicePendingDetails(trn_invoice invoiceObj) {
LOGGER.debug("Inside deleteInvoiceDetails() method called from EmployeeDetailsDaoImpl");
Session sess=getSession();
Transaction tx=null;


try {
tx=sess.beginTransaction();
Query query=sess.createQuery("update trn_invoice set approval_status='approved' where pk_invoice_id=?");
query.setParameter(0,invoiceObj.getPk_invoice_id());
query.executeUpdate();

tx.commit();
sess.close();
return true;
} catch (Exception e) {
LOGGER.error("Error while saving entry : ",e);
}
return false;
}

@Override
public boolean rejectInvoicePendingDetails(trn_invoice invoiceObj) {
LOGGER.debug("Inside deleteInvoiceDetails() method called from EmployeeDetailsDaoImpl");
Session session=getSession();
Transaction tx=null;


try {
Query query=session.createQuery("SELECT pk_invoice_id FROM trn_invoicedup inv WHERE inv.fk_invoice_id=:fk_invoice_id");
query.setParameter("fk_invoice_id",invoiceObj.getPk_invoice_id());
query.setMaxResults(1);
Integer pk_invoice_id=(Integer)query.uniqueResult();
if(pk_invoice_id!=null) {
query=session.createQuery("FROM trn_invoicedup inv WHERE inv.pk_invoice_id=:pk_invoice_id");
query.setParameter("pk_invoice_id", pk_invoice_id);
query.setMaxResults(1);
List<trn_invoicedup> list=query.list();
trn_invoice trnInvoiceObj=new trn_invoice();
trnInvoiceObj.setPk_invoice_id(list.get(0).getFk_invoice_id());
trnInvoiceObj.setAccid(75);
trnInvoiceObj.setInvoice_number(list.get(0).getInvoice_number());
trnInvoiceObj.setCompany_code(list.get(0).getCompany_code());
trnInvoiceObj.setFiscal_year(list.get(0).getFiscal_year());
trnInvoiceObj.setItem_number(list.get(0).getItem_number());
trnInvoiceObj.setInvid(list.get(0).getInvid());
trnInvoiceObj.setPostid(list.get(0).getPostid());
trnInvoiceObj.setCustid(list.get(0).getCustid());
trnInvoiceObj.setInvoice_total_amount(list.get(0).getInvoice_total_amount());
trnInvoiceObj.setInvoice_due_amount(list.get(0).getInvoice_due_amount());
trnInvoiceObj.setInvoice_created_date(list.get(0).getInvoice_created_date());
trnInvoiceObj.setDue_date(list.get(0).getDue_date());
trnInvoiceObj.setDiscount_1_percentage(list.get(0).getDiscount_1_percentage());
trnInvoiceObj.setDiscount_2_percentage(list.get(0).getDiscount_2_percentage());
trnInvoiceObj.setDiscount_3_percentage(list.get(0).getDiscount_3_percentage());
trnInvoiceObj.setDebit_credit_indicator(list.get(0).getDebit_credit_indicator());
trnInvoiceObj.setIs_open(list.get(0).getIs_open());
trnInvoiceObj.setApproval_status("approved");
session.close();

Session ses1=getSession();
tx=ses1.beginTransaction();
ses1.update(trnInvoiceObj);
tx.commit();

Transaction tx1=null;

Session s2=getSession();
tx1=s2.beginTransaction();
query=s2.createQuery("DELETE FROM trn_invoicedup inv WHERE pk_invoice_id=:pk_invoice_id");
query.setParameter("pk_invoice_id", pk_invoice_id);
query.executeUpdate();
tx1.commit();
return true;

}else {
Transaction tx2=null;
Session s3=getSession();
tx2=s3.beginTransaction();
Query q=s3.createQuery("update trn_invoice set approval_status='rejected' where pk_invoice_id=?");
q.setParameter(0,invoiceObj.getPk_invoice_id());
q.executeUpdate();
tx2.commit();
s3.close();
return true;

}

} catch (Exception e) {
LOGGER.error("Error while saving entry : ",e);
}
return false;
}

public void duplicate(Integer pk_invoice_id) {
Query query=null;
Session session=getSession();
query=session.createQuery("FROM trn_invoice inv WHERE inv.pk_invoice_id=:pk_invoice_id");
query.setParameter("pk_invoice_id", pk_invoice_id);
List<trn_invoice> list=query.list();
trn_invoicedup dup=new trn_invoicedup();
dup.setFk_invoice_id(list.get(0).getPk_invoice_id());
dup.setAccid(75);

dup.setInvoice_number(list.get(0).getInvoice_number());
dup.setCompany_code(list.get(0).getCompany_code());
dup.setFiscal_year(list.get(0).getFiscal_year());
dup.setItem_number(list.get(0).getItem_number());

dup.setInvid(list.get(0).getInvid());
dup.setPostid(list.get(0).getPostid());
dup.setCustid(list.get(0).getCustid());
dup.setInvoice_total_amount(list.get(0).getInvoice_total_amount());
dup.setInvoice_due_amount(list.get(0).getInvoice_due_amount());
dup.setInvoice_created_date(list.get(0).getInvoice_created_date());
dup.setDue_date(list.get(0).getDue_date());
dup.setDiscount_1_percentage(list.get(0).getDiscount_1_percentage());
dup.setDiscount_2_percentage(list.get(0).getDiscount_2_percentage());
dup.setDiscount_3_percentage(list.get(0).getDiscount_3_percentage());
dup.setDebit_credit_indicator(list.get(0).getDebit_credit_indicator());
dup.setIs_open(list.get(0).getIs_open());
dup.setApproval_status(list.get(0).getApproval_status());
session.close();

Session ses1=getSession();
Transaction tx=null;
tx=ses1.beginTransaction();
ses1.save(dup);
tx.commit();




}


protected Map<String, Object> getRowsAndCountForQuery(String intialQuery, int startIndex, int rowsToFetch, boolean isPaginated) {

List rows = getRowsForQuery(intialQuery, startIndex, rowsToFetch, isPaginated);
int count = getRowCountForQuery(intialQuery);

Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("rows", rows);
resultMap.put("count", count);
return resultMap;
}


protected List getRowsForQuery(String intialQuery, int startIndex, int rowsToFetch, boolean isPaginated) {
LOGGER.debug("Query is: " + intialQuery);
Query query = getSession().createQuery(intialQuery);

if (isPaginated) {
LOGGER.debug(
"limiting rows as isPaginated is true. startIndex: " + startIndex + " rowsToFetch: " + rowsToFetch);
query.setFirstResult(startIndex);
query.setMaxResults(rowsToFetch);
}
LOGGER.debug("Before Executing Query " + query.toString());
List list = query.list();
// AcctDocHeader acc1 = (AcctDocHeader)list.get(0);
LOGGER.debug("After Executing Query ");
return list;
}

private Session getSession() {
return hibernateTemplate.getSessionFactory().openSession();
}

protected int getRowCountForQuery(String intialQuery) {
int rowCount = 0;
try {
String countQuery = "select count(*) as count " + removeSelectNOrderByClause(intialQuery);
LOGGER.debug("CountQuery is: " + countQuery);

Query query = getSession().createQuery(countQuery);
Object resultCount = (Object) query.list().get(0);
rowCount = Integer.parseInt(String.valueOf(resultCount));
}
catch (Exception e){
LOGGER.error(e);
}

LOGGER.debug("Count returned is: " + rowCount);
return rowCount;
}

protected static String removeSelectNOrderByClause(String initialQuery){
String finalQuery=initialQuery;
finalQuery = removeSelectQuery(finalQuery);
finalQuery = removeOrderByClause(finalQuery);
return finalQuery;
}

private static String removeSelectQuery(String initialQuery){
String finalQuery = initialQuery;
Pattern p = Pattern.compile("\s*select.*\s+from\s+(.*)", Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(finalQuery);
if(m.matches()){
if(m.group(1).length()!=0){
finalQuery = " from " + m.group(1);
}
}
return finalQuery;
}

private static String removeOrderByClause(String intialQuery) {
int index = intialQuery.toLowerCase().indexOf("order by");
if(index != -1){
return intialQuery.substring(0, index);
}
return intialQuery;
}

}